Пропуск определенных чисел в последовательности

В Excel 2010 у меня есть список порядковых номеров, которые использовались ранее, которые я хочу зарезервировать, и мне нужно создать формулу, которая будет начинаться с номера, который я указываю в качестве начального, но обхожу те, которые находятся в моем зарезервированном список. И порядковый номер, и номера резервного списка могут состоять из нескольких тысяч записей каждый. Мои зарезервированные порядковые номера:

Зарезервированные номера

Если я дам своей последовательности начальный номер 150, я бы хотел, чтобы мои порядковые номера были назначены следующим образом:

Порядковые номера, пропускающие зарезервированные

где последовательность пропускает числа 156 и 231.

Я видел способ создать собственный список, но это как раз наоборот.

Как я могу этого добиться?


excel excel-formula sequence excel-2010 skip
person steve_o    schedule 05.11.2015    source источник
comment
Вы действительно хотите, чтобы текст SEQ был частью выходных чисел?   -  person Excel Hero    schedule 05.11.2015
comment
@ExcelHero - да, но это не обязательно. Поскольку это буквальное значение, я могу просто добавить его после того, как получу номер, который пропускает зарезервированные.   -  person steve_o    schedule 05.11.2015

Ответы (2)


arrow_upward
1
arrow_downward

В ячейку M2 введите следующую формулу:

=MAX(start,M1)+1+(IFERROR(MATCH(MAX(start,M1)+1,rsv,),0)>0)

и скопируйте вниз.

start - это ячейка с начальным номером (K2 в вашем примере).

rsv — это список зарезервированных номеров.

.

ОБНОВЛЕНИЕ

Вот еще одна формула, которая решает проблему с последовательными зарезервированными номерами:

=INDEX(ROW(OFFSET($A$1,start-1,,999)),MATCH(1,ISERROR(MATCH(ROW(OFFSET($A$1,start-1,,999)),rsv,))*(ROW(OFFSET($A$1,start-1,,999))>MAX(M1,start)),))

Это формула массива, которую необходимо подтвердить нажатием Ctrl+Shift+Enter.

person Excel Hero    schedule 05.11.2015
comment
С этим есть небольшая проблема — он отлично работает, если зарезервированные номера не являются последовательными. Если они последовательные, то первое выполняется правильно (и пропускается), а второе использует один из зарезервированных номеров. - person steve_o; 05.11.2015
comment
А, понятно. Это сильно усложняет дело. Какая максимальная последовательность? - person Excel Hero; 05.11.2015
comment
Количество последовательных чисел везде разное, но на первый взгляд я не вижу более 8-10 последовательных чисел. - person steve_o; 05.11.2015
comment
Я считаю, что формула массива сделала свое дело. Я привел несколько примеров, и все они сработали! Большое спасибо за Вашу помощь!! :) - person steve_o; 05.11.2015
comment
Большой! Добро пожаловать. Это было непросто. ;) - person Excel Hero; 05.11.2015
comment
Одна вещь. Измените число вхождений 20 в формуле на гораздо большее число, скажем, 999. Сейчас я вношу это редактирование. - person Excel Hero; 05.11.2015
comment
Я изменил его, когда ввел его. Поставил несколько тысяч, и проблем не было. :) - person steve_o; 05.11.2015

arrow_upward
0
arrow_downward

Предполагая, что ваши пропущенные числа сами по себе не являются последовательными, эта формула будет работать:

=IF(COUNTIF(H2:H4,K2+1)>0,K2+2,K2+1)

Это предполагает, что ваш начальный номер находится в K2, а диапазон, которого вы избегаете, — H2:H4.

person Marc    schedule 05.11.2015
comment
это смесь. Некоторые последовательности, которые я хочу зарезервировать, содержат несколько десятков порядковых номеров. В остальных случаях они не являются последовательными. - person steve_o; 05.11.2015