Перейти до основного матеріалу

Як визначити послідовність пропущених чисел у Excel?

Скажімо, у вас є довгий список порядкових номерів для позначення предметів, наприклад номерів чеків у виписках з банківських рахунків, як правило, ми прокручуємо та знаходимо відсутні номери послідовності вручну. Іноді це досить важко і трудомістко. Ви можете подумати про хитрі способи боротьби з цим. Так, існує кілька простих способів швидко та зручно визначити та знайти послідовність пропущених чисел у програмах Excel 2007, Excel 2010 та Excel 2013.

Визначте послідовність пропущених чисел за формулою IF

Визначте послідовність пропущених чисел за допомогою формули масиву

Швидко визначте послідовність відсутніх чисел за допомогою Kutools для Excel


стрілка синя права міхур Визначте послідовність пропущених чисел за формулою IF

Як ми всі знаємо, більшість послідовних номерів мають фіксований приріст 1, наприклад 1, 2, 3, ..., N. Отже, якщо ви можете ідентифікувати число не менше 1, ніж його наступне число, число відсутнє .

Ми покажемо вам підручники з прикладом, як показано на наступному знімку екрана:

doc визначити відсутні числа 1

1. У порожню комірку введіть формулу = IF (A3-A2 = 1, "", "Відсутній"), і натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ. У цьому випадку ми вводимо формулу в клітинку B2.

doc-identify-missing-numbers2

Якщо відсутні відсутні числа, ця формула нічого не поверне; якщо відсутні числа, це поверне текст "Відсутній" в активній комірці.

2. Виділіть комірку B2 і перетягніть маркер заповнення на діапазон комірок, які ви хочете містити у цій формулі. Тепер він ідентифікує відсутні числа з текстом "Відсутній" у відповідних клітинках стовпця B. Дивіться наступний знімок екрана:

doc-identify-missing-numbers3


стрілка синя права міхур Визначте послідовність пропущених чисел за допомогою формули масиву

Іноді це вимагає не тільки ідентифікації послідовності пропущених чисел, але й переліку відсутніх чисел. Ви можете впоратися з цим, виконавши такі дії:

1. у сусідній комірці введіть формулу = SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1))

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

2 Натисніть кнопку Ctrl + Shift + Enter Клавіші разом, щоб закінчити формулу. Копіюйте формулу, поки не отримаєте #NUM! помилки, що означає, що всі відсутні числа були перераховані. Дивіться знімок екрана:

doc-identify-missing-numbers4


стрілка синя права міхур Швидко визначте послідовність відсутніх чисел за допомогою Kutools для Excel

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

Примітка:Щоб застосувати це Знайдіть відсутній порядковий номер, по-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть послідовність даних, за якою ви хочете знайти відсутню послідовність.

2. клацання Кутулс > Insert > Знайдіть відсутній порядковий номер, див. скріншот:

3 В Знайдіть відсутній порядковий номер діалогове вікно:

(1.) Якщо ви вирішите Вставка нового стовпця з наступним відсутнім маркером параметр, усі відсутні порядкові номери позначені текстом Відсутній у новому стовпці поруч із вашими даними. Дивіться знімок екрана:

doc-ідентифікуйте-відсутні-числа 6

(2.) Якщо ви вирішите Вставка відсутнього порядкового номера опція, усі відсутні числа були вставлені в список послідовностей. Дивіться знімок екрана:

doc-ідентифікуйте-відсутні-числа 7

(3.) Якщо ви вирішите Вставка порожніх рядків під час підрахунку відсутніх порядкових номерів опція, всі порожні рядки вставляються, коли відсутні цифри. Дивіться знімок екрана:

doc-ідентифікуйте-відсутні-числа 8

(4.) Якщо ви вирішите Колір фону заливки параметр, розташування пропущених номерів буде відразу виділено. Дивіться знімок екрана:

doc-ідентифікуйте-відсутні-числа 9


стрілка синя права міхур Швидко визначте послідовність відсутніх чисел за допомогою Kutools для Excel

Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!

Найкращі інструменти продуктивності офісу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!

Comments (43)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I am not able to find missing numbers from excel..
This comment was minimized by the moderator on the site
I want to find a missing receipts using a range in columns, i.e. B2:C2 (receipt book # 679201/B2:679400/C2) meaning 200 receipt pages in one receipt book. Then accounted receipts of 679305/D2:679386/E2. Then what is the best formula for this?
This comment was minimized by the moderator on the site
Thank you. This is awesome
This comment was minimized by the moderator on the site
Doesn't work
This comment was minimized by the moderator on the site
i wanted to identify the missing range in sequence rather than a missing number. example if there is range missing from 1,2,3,8,9,10,11,18. the missing range is 4-7 & 12-17.
This comment was minimized by the moderator on the site
Thankyou... It helped a lot... God bless you
This comment was minimized by the moderator on the site
I have a list and this has been extremely helpful. I am trying to list the existing numbers in a sequence and then skip the missing (ext. 1, 2, 3, 5, 7 to show as 1 - 3, 5, 7). Is this possible to formulate?
This comment was minimized by the moderator on the site
Hello Community and excel Gurus; I wan to search missing numbers in a row of excel ,I tried to use the Kutool for excel ,After selecting the option to find missing numbers ,i am getting error the range can only contain number or text ?Though the range or row i had selected contains only number. Any expert advice ? Best, Sanchit Bhrdwaj
This comment was minimized by the moderator on the site
May be your numbers are in text format so first you have to convert it numeber by multiply all with Number 1 , the format will change in number then your range will be in number and it will change
This comment was minimized by the moderator on the site
Thank u Mannnnnn . Cheers
This comment was minimized by the moderator on the site
its good working but this formula very big how can remember this = SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1))
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations