Как определить пропущенные номера последовательности в Excel?

Предположим, у вас есть длинный список порядковых номеров для маркировки элементов, таких как номера чеков в банковских выписках. Обычно мы прокручиваем список и вручную находим пропущенные номера последовательности. Иногда это довольно утомительно и отнимает много времени. Вы можете подумать о хитрых способах справиться с этим. Да, существует несколько простых способов быстро и удобно определить и найти пропущенные номера последовательности в Excel 2007, Excel 2010 и Excel 2013.

Определение пропущенных номеров последовательности с помощью формулы ЕСЛИ

Определение пропущенных номеров последовательности с помощью формулы массива

Быстрое определение пропущенных номеров последовательности с помощью Kutools для Excel

Определение пропущенных номеров последовательности с помощью формулы ЕСЛИ

Как всем известно, большинство порядковых номеров имеют фиксированный инкремент равный 1, например, 1, 2, 3, …, N. Следовательно, если вы сможете определить, что число не меньше на 1, чем следующее за ним число, значит, есть пропущенный номер.

Мы покажем вам учебное пособие на примере, как показано на следующем скриншоте:

1. В пустой ячейке введите формулу =ЕСЛИ(A3-A2=1;"";"Пропущено"), и нажмите клавишу Enter. В данном случае мы вводим формулу в ячейку B2.

Если пропущенных номеров нет, эта формула ничего не вернет; если пропущенные номера существуют, она вернет текст "Пропущено" в активной ячейке.

2. Выберите ячейку B2 и перетащите маркер заполнения по диапазону ячеек, который вы хотите заполнить этой формулой. Теперь она определяет пропущенные номера с текстом "Пропущено" в соответствующих ячейках столбца B. Смотрите следующий скриншот:

Определение пропущенных номеров последовательности с помощью формулы массива

Иногда требуется не только определить пропущенные номера последовательности, но и перечислить их. Вы можете справиться с этим, выполнив следующие шаги:

1. В соседней ячейке введите формулу =НАИМЕНЬШИЙ(ЕСЛИ(ЕОШ(ПОИСКПОЗ(СТРОКА(A$1:A$30);A$1:A$30;0));СТРОКА(A$1:A$30));СТРОКА(A1))

A1:A30 = диапазон чисел, последовательность для проверки от 1 до 30

2. Нажмите клавиши Ctrl + Shift + Enter вместе, чтобы завершить формулу. Скопируйте формулу вниз, пока не получите ошибки #ЧИСЛО!, что будет означать, что все пропущенные номера были перечислены. Смотрите скриншот:

Быстрое определение пропущенных номеров последовательности с помощью Kutools для Excel

Вышеупомянутые методы могут определить только пропущенную последовательность чистых чисел, если у вас есть последовательность, такая как AA-1001-BB, AA-1002-BB, они могут не работать успешно. Но не волнуйтесь, мощная функция Kutools для Excel – Найти пропущенную последовательность номеров может помочь вам быстро определить пропущенную последовательность.

После установки Kutools для Excel сделайте следующее:

1. Выберите последовательность данных, в которой вы хотите найти пропущенную последовательность.

2. Нажмите Kutools > Вставить > Найти пропущенную последовательность номеров, см. скриншот:

3. В диалоговом окне Найти пропущенную последовательность номеров:

(1.) Если вы выберете опцию Создать новый столбец для заполнения указанных маркеров содержимого , все пропущенные номера последовательности будут отмечены текстом Пропущено в новом столбце рядом с вашими данными. Смотрите скриншот:

(2.) Если вы выберете опцию Вставить пропущенные номера последовательности, все пропущенные номера будут вставлены в список последовательности. Смотрите скриншот:

(3.) Если вы выберете опцию Вставить пустые строки при обнаружении пропущенных номеров последовательности, все пустые строки будут вставлены там, где есть пропущенные номера. Смотрите скриншот:

(4.) Если вы выберете опцию Заполнить цвет фона, местоположение пропущенных номеров будет сразу выделено. Смотрите скриншот:

Демонстрация: Быстрое определение пропущенных номеров последовательности с помощью Kutools для Excel