Как определить пропущенные номера последовательности в 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, а затем быстро и легко применить эту функцию.
После установки Kutools для Excel сделайте следующее:
1. Выберите последовательность данных, в которой вы хотите найти пропущенную последовательность.
2. Нажмите Kutools > Вставить > Найти пропущенную последовательность номеров, см. скриншот:
3. В диалоговом окне Найти пропущенную последовательность номеров:
(1.) Если вы выберете опцию Создать новый столбец для заполнения указанных маркеров содержимого , все пропущенные номера последовательности будут отмечены текстом Пропущено в новом столбце рядом с вашими данными. Смотрите скриншот:
(2.) Если вы выберете опцию Вставить пропущенные номера последовательности, все пропущенные номера будут вставлены в список последовательности. Смотрите скриншот:
(3.) Если вы выберете опцию Вставить пустые строки при обнаружении пропущенных номеров последовательности, все пустые строки будут вставлены там, где есть пропущенные номера. Смотрите скриншот:
(4.) Если вы выберете опцию Заполнить цвет фона, местоположение пропущенных номеров будет сразу выделено. Смотрите скриншот:
Демонстрация: Быстрое определение пропущенных номеров последовательности с помощью Kutools для Excel
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек