Загрузите образец книги
В этом руководстве будет показано, как составить список отсутствующих чисел в диапазоне.
Список пропущенных номеров по порядку
Чтобы найти недостающие числа в списке, мы можем использовать следующую формулу:
| 1 | = МАЛЕНЬКИЙ (ЕСЛИ (СЧЁТЕСЛИ ($ B $ 3: $ B $ 8; СТРОКА ($ 1: $ 6)) = 0; СТРОКА ($ 1: $ 6); ""), СТРОКА (B1)) |
Примечания:
- Это формула массива! При использовании Excel 2022 и более ранних версий необходимо ввести формулу, нажав CTRL + SHIFT + ENTER. Вы узнаете, что формула была введена правильно, когда увидите фигурные скобки вокруг формулы.
- Эта формула работает только с положительными целыми числами (целыми числами).
Как работает формула?
Во-первых, функция СТРОКА используется для генерации массива чисел:
| 1 | {1;2;3;4;5;6} |
Фактически, вы даже можете ввести этот массив чисел вручную:
| 1 | = МАЛЕНЬКИЙ (ЕСЛИ (СЧЁТЕСЛИ ($ B $ 3: $ B $ 8; {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), СТРОКА (B1)) |
Затем функция COUNTIF подсчитает значения, «если» они соответствуют значению, найденному в массиве.
Чтобы визуализировать это, сначала мы преобразуем диапазон $ B $ 3: $ B $ 8 в его массив значений {3; 2; 0; 6; 0; 5}:
| 1 | = МАЛЕНЬКИЙ (ЕСЛИ (СЧЁТЕСЛИ ({3; 2; 0; 6; 0; 5}; {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), СТРОКА (B1)) |
Далее мы выполним операцию СЧЁТЕСЛИ:
| 1 | = МАЛЕНЬКИЙ (ЕСЛИ ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), СТРОКА (B1)) |
Если значение не найдено, выводится 0. Функция ЕСЛИ проверяет, не найдено ли значение, и если да, возвращает значение из массива, в противном случае выдает пустое значение:
| 1 | = МАЛЕНЬКИЙ ({1; ""; ""; 4; ""; ""}, СТРОКА (B1)) |
Наконец, функция МАЛЕНЬКАЯ возвращает наименьшее отсутствующее значение в выбранной ячейке.
| 1 | = МАЛЕНЬКИЙ ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Кончик:
Чтобы избежать #NUM! оберните формулу функцией ЕСЛИОШИБКА:
| 1 | = ЕСЛИОШИБКА (МАЛЫЙ (ЕСЛИ (СЧЁТЕСЛИ (B3: B8; СТРОКА (1: 6)) = 0; СТРОКА (1: 6); ""); СТРОКА (B1)); "") |
Список пропущенных номеров по порядку в Google Таблицах
Все приведенные выше примеры работают в таблицах Google так же, как и в Excel.
