Поиск пропущенных чисел
Поиск недостающих (пропущенных) значений в числовой последовательности - это, хоть и банальная, но, на самом деле, весьма распространённая задача при работе в Microsoft Excel. Вы можете столкнуться с ней при поиске пропущенных документов с последовательной нумерацией; выставленных, но неоплаченных счетов; невыпавших номеров в тираже лотереи и т.д. А ещё эту задачу очень любят давать на собеседованиях программистам и аналитикам.
Как обычно, в Excel есть несколько способов решить эту задачу - весьма различающихся по изяществу и сложности.
В качестве примера давайте возьмем предельно простой кейс - нужно найти пропущенные числа 3,7,8 в последовательности 1...10.
Способ 1. Динамические массивы в Excel 2021 и новее
Если вы работаете на версии Microsoft Excel, которая поддерживает динамические массивы (Excel 2021, 2024, 365 и новее), то наша задача решается просто и красиво одной формулой:
=УНИК(ВСТОЛБИК(ПОСЛЕД(10;1;1);B3:B9);;1)
в английской версии это будет соответственно:
=UNIQUE(VSTACK(SEQUENCE(10;1;1);B3:B9);;1)
Идея тут простая:
- Сначала мы генерим эталонную (полную) числовую последовательность от 1 до 10 с помощью функции ПОСЛЕД (SEQUENCE), о которой я уже писал.
- Затем подклеиваем к получившейся последовательности 1,2,3...10 наши исходные числа с помощью функции ВСТОЛБИК (VSTACK).
- После чего с помощью функции УНИК (UNIQUE) извлекаем из получившегося набора только те значения, которые встречаются там только один раз (за это отвечает последний необязательный аргумент функции). Таким образом, все числа, которых не было в полной эталонной последовательности, выводятся как результат.
Создаём лямбда-функцию для поиска пропущенных чисел
Если есть ощущение, что такую задачу вам придётся решать часто, то имеет смысл создать пользовательскую лямбда-функцию на основе предыдущей формулы, чтобы не вводить её вручную каждый раз.
Для этого идём на вкладку Формулы - Диспетчер имён (Formulas - Name manager) и создаём именованный диапазон кнопкой Создать (New). Даём имя нашей функции (например, MissingNumbers) и вставляем ншу формулу в поле Диапазон (Reference), в функцию LAMBDA, заменив жёстко прописанные в формуле константы (начальное значение, конечное значение и диапазон списка) на переменные с любыми подходящими именами (например, start, finish и myNumbers):
=LAMBDA(start; finish; myNumbers; УНИК(ВСТОЛБИК(ПОСЛЕД(finish;1;start);myNumbers);;1) )
После нажатия на ОК созданной функцией можно пользоваться на любом листе текущей книги как будто это встроенная функция Excel:
Способ 2. Формула массива для любой версии Excel
Если созданным файлом вам надо делиться с людьми, работающими на старых версиях Excel или вы сами работаете в такой версии, где ещё нет динамических массивов и упомянутых в предыдущем способе функций, то можно решить нашу задачу другим способом - с помощью формулы массива. Это заметно более громоздкий, но зато универсальный подход, работающий в абсолютно любой версии Excel:
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА($A$1:$A$10);$B$3:$B$9;0));СТРОКА($A$1:$A$10));СТРОКА(A1));"")
Логика её работы следующая.
- Начинаем опять с генерации полной эталонной последовательности от 1 до 10. Без новой функции ПОСЛЕД это можно сделать с помощью старой доброй функции СТРОКА (ROW), которая выводит номер строки для каждой ячейки исходного диапазона.
- Затем с помощью функции ПОИСКПОЗ (MATCH) проверим совпадение исходного и эталонного списков - там, где число пропущено, мы увидим ошибку #Н/Д (#N/A).
- Теперь с помощью функций ЕСЛИ (IF) и ЕНД (ISNA) выведем только те значения эталонного списка, для которых получились ошибки, т.е. пропущенные элементы.
- И, наконец, чтобы вывести только числа без промежуточных логических значений ЛОЖЬ, используем функцию НАИМЕНЬШИЙ (SMALL), а чтобы скрыть ошибки - функцию ЕСЛИОШИБКА (IFERROR).
В английской версии итоговая формула будет выглядеть как:
=IFERROR(SMALL(IF(ISNA(MATCH(ROW($A$1:$A$10);$B$3:$B$9;0));ROW($A$1:$A$10));ROW(A1));"")
Не забудьте, что итоговую формулу нужно вводить как формулу массива, т.е. нажать сочетание клавиш Ctrl+Shift+Enter. и только потом уже копировать из первой ячейки жёлтого диапазона на все остальные (с запасом, т.к. мы заранее не знаем сколько элементов пропущено, ясное дело).
Ссылки по теме
- Обманчивая простота функции ПОСЛЕД (SEQUENCE)
- Что такое динамические массивы в Microsoft Excel
- Новые функции динамических массивов: ФИЛЬТР, СОРТ и УНИК
