Завантажте зразок робочої книги
Цей підручник покаже, як перелічити пропущені числа в діапазоні.
Список пропущених номерів у послідовності
Щоб знайти відсутні числа у списку, ми можемо скористатися такою формулою:
| 1 | = МАЛИЙ (ЯКЩО (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6)) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Примітки:
- Це формула масиву! Під час використання Excel 2022 та попередніх версій потрібно ввести формулу, натиснувши CTRL + SHIFT + ENTER. Ви будете знати, що формула була введена належним чином, коли ви побачите фігурні дужки навколо формули.
- Ця формула працює тільки з позитивними цілими числами (цілими числами).
Як працює формула?
По -перше, функція ROW використовується для створення масиву чисел:
| 1 | {1;2;3;4;5;6} |
Фактично, ви навіть можете ввести цей масив цифр вручну:
| 1 | = МАЛИЙ (ЯКЩО (COUNTIF ($ 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 | = МАЛИЙ (ЯКЩО (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1)) |
Далі ми виконаємо операцію COUNTIF:
| 1 | = МАЛЕНЬКИЙ (ЯКЩО ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), ROW (B1)) |
Якщо значення не знайдено, виводиться 0. Функція IF перевіряє, чи значення НЕ знайдено, і якщо так, повертає значення з масиву, інакше виводить порожнє значення:
| 1 | = МАЛЕНЬКИЙ ({1; ""; ""; 4; ""; ""}, ROW (B1)) |
Нарешті, функція SMALL повертає найменше пропущене значення у вибраній комірці.
| 1 | = МАЛЕНЬКИЙ ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Порада:
Щоб уникнути #NUM! помилка, оберніть формулу функцією IFERROR:
| 1 | = IFERROR (МАЛЕНЬКИЙ (IF (COUNTIF (B3: B8, ROW (1: 6)) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Список пропущених номерів у послідовності в Таблицях Google
Усі наведені вище приклади працюють так само в аркушах Google, як і в Excel.
