Как найти недостающие значения в excel

Функции СЧЕТЕСЛИ проверяет значения в диапазоне от критериев. Часто, только один критерий подается, но в этом случае мы поставляем больше чем один критерий.

Для диапазона, мы даем СЧЕТЕСЛИ именованному диапазону лист1 (B6: B11) и критериям мы обеспечиваем именованный диапазон лист2 (F6: F8).

Потому что мы даем СЧЕТЕСЛИ более чем один критерий, мы получим более одного результата в массиве, который выглядит следующим образом: {2; 1; 0}

Мы хотим, чтобы рассчитывались только те значения, которые отсутствуют, которые по определению имеют счетчик, равный нулю, поэтому мы преобразуем эти значения ИСТИНА и ЛОЖЬ с “= 0” заявлением, что дает: {ЛОЖЬ; ЛОЖЬ; ИСТИНА}

Тогда мы изменим значения ИСТИНА/ЛОЖЬ в 1 и 0 с двойным отрицательным оператором (-), который производит: {0, 0, 1}

Наконец, мы используем СУММПРОИЗВ, чтобы сложить элементы в массиве и получить общее количество пропущенных значений.

Предположим, у вас есть длинный список порядковых номеров для маркировки элементов, таких как номера чеков в банковских выписках, обычно мы прокручиваем и находим недостающие порядковые номера вручную. Иногда это довольно сложно и требует много времени. Вы можете придумать хитрые способы справиться с этим. Да, есть несколько простых способов быстро и удобно определить и найти последовательность отсутствующих чисел в Excel 2007, Excel 2010 и Excel 2013.

Определите последовательность отсутствующих чисел с помощью формулы ЕСЛИ

Определите последовательность отсутствующих чисел с помощью формулы массива

Определите последовательность пропущенных чисел с помощью Kutools for Excel быстро

Определите последовательность отсутствующих чисел с помощью формулы ЕСЛИ

Как мы все знаем, большинство порядковых номеров имеют фиксированное приращение 1, например, 1, 2, 3,…, N. Следовательно, если вы можете определить, что число не меньше 1, чем его следующее число, это означает, что число отсутствует. .

Мы покажем вам руководства с примером, как показано на следующем скриншоте:

1. В пустой ячейке введите формулу = ЕСЛИ (A3-A2 = 1; “”; “Отсутствует”), и нажмите Enter ключ. В этом случае мы вводим формулу в ячейку B2.

Если нет пропущенных чисел, эта формула ничего не вернет; если пропущенные числа существуют, он вернет текст «Отсутствует» в активной ячейке.

2. Выберите ячейку B2 и перетащите маркер заполнения над диапазоном ячеек, который вы хотите содержать эту формулу. Теперь он идентифицирует отсутствующие числа с текстом «Отсутствует» в соответствующих ячейках столбца B. См. Следующий снимок экрана:

Определите последовательность отсутствующих чисел с помощью формулы массива

Иногда требуется не только определить последовательность отсутствующих номеров, но и перечислить отсутствующие номера. Вы можете справиться с этим, выполнив следующие шаги:

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. нажмите Shift + Ctrl + Enter Ключи вместе, чтобы закончить формулу. Скопируйте формулу, пока не получите # ЧИСЛО! ошибки, означающие, что были перечислены все отсутствующие числа. Смотрите скриншот:

Определите последовательность пропущенных чисел с помощью Kutools for Excel быстро

Вышеупомянутые методы могут идентифицировать только отсутствующую чистую числовую последовательность, если у вас есть такая последовательность, как AA-1001-BB, AA-1002-BB, они могут не работать успешно. Но не волнуйся, Kutools for Excelмощная функция – Найти отсутствующий порядковый номер может помочь вам быстро определить недостающую последовательность.

Примечание:Чтобы применить это Найти отсутствующий порядковый номер, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите последовательность данных, в которой вы хотите найти недостающую последовательность.

2. Нажмите Кутулс > Вставить > Найти отсутствующий порядковый номер, см. снимок экрана:

3. в Найти отсутствующий порядковый номер диалоговое окно:

(1.) Если вы выберете Вставка нового столбца со следующим отсутствующим маркером вариант, все недостающие порядковые номера отмечены текстом Отсутствующий в новом столбце рядом с вашими данными. Смотрите скриншот:

(2.) Если вы выберете Вставка отсутствующего порядкового номера вариант, все недостающие числа были вставлены в список последовательностей. Смотрите скриншот:

(3.) Если вы выберете Вставка пустых строк при включении отсутствующих порядковых номеров вариант, все пустые строки вставляются, когда отсутствуют числа. Смотрите скриншот:

(4.) Если вы выберете Цвет заливки фона вариант, расположение недостающих номеров будет выделено сразу. Смотрите скриншот:

Определите последовательность пропущенных чисел с помощью Kutools for Excel быстро

Предположим, у вас есть длинный список порядковых номеров для маркировки элементов, таких как номера чеков в выписках по счету, обычно мы прокручиваем и находим недостающие порядковые номера вручную. Иногда это довольно сложно и требует много времени. Вы можете придумать хитрые способы справиться с этим. Да, есть несколько простых способов быстро и удобно определить и найти последовательность отсутствующих чисел в Excel 2007, Excel 2010 и Excel 2013.

Определите последовательность отсутствующих чисел с помощью формулы ЕСЛИ

Определить последовательность отсутствующих чисел с помощью формулы массива

Определить последовательность отсутствующих чисел с помощью Kutools for Быстро Excel

->

Определите последовательность пропущенных чисел с помощью формулы ЕСЛИ

Как мы все знаем, большинство порядковых номеров имеют фиксированное приращение из 1, например 1, 2, 3,…, N. Следовательно, если вы можете определить, что число не меньше 1, чем следующее за ним число, это означает отсутствие числа.

Мы покажем вам представлены учебные пособия с примером, показанным на следующем снимке экрана:

1. В пустой ячейке введите формулу = IF (A3-A2 = 1, “”, “Отсутствует”) и нажмите клавишу Enter . В этом случае мы вводим формулу в ячейку B2.

Если нет пропущенных чисел, эта формула ничего не вернет; если пропущенные числа существуют, он вернет текст «Отсутствует» в активной ячейке.

2. Выделите ячейку B2 и перетащите маркер заполнения над диапазоном ячеек, который вы хотите содержать эту формулу. Теперь он идентифицирует отсутствующие числа с текстом «Отсутствует» в соответствующих ячейках столбца B. См. Следующий снимок экрана:

С помощью формула массива

Иногда требуется не только определить последовательность пропущенных чисел, но и перечислить отсутствующие числа. Вы можете справиться с этим, выполнив следующие шаги:

1. в соседней ячейке введите формулу = SMALL (IF (ISNA (MATCH (ROW (A $ 1: A $ 30), A $ 1: A $ 30,0)), ROW (A $ 1: A $ 30) ), СТРОКА (A1))

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

2. Одновременно нажмите клавиши Ctrl + Shift + Enter , чтобы завершить формулу. Скопируйте формулу, пока не получите # ЧИСЛО! ошибки, означающие, что были перечислены все отсутствующие числа. См. Снимок экрана:

Быстрое определение последовательности отсутствующих чисел с помощью Kutools for Excel

Только указанные выше методы могут Определите отсутствующую чистую числовую последовательность, если у вас есть такая последовательность, как AA-1001-BB, AA-1002-BB, они могут не работать успешно. Но не волнуйтесь, мощная функция Kutools for Excel – Найти отсутствующий порядковый номер может помочь вам быстро определить недостающую последовательность.

Примечание. Чтобы применить этот Найти отсутствующий порядковый номер , во-первых, вы должны загрузить Kutools для Excel , а затем быстро и легко примените эту функцию.

После установки Kutools for Excel сделайте следующее:

1. Выберите последовательность данных, в которой вы хотите найти недостающую последовательность.

2. Нажмите Kutools > Вставить > Найти отсутствующий порядковый номер , см. Снимок экрана:

3. В диалоговом окне Найти отсутствующий порядковый номер :

(1.) Если вы выбрали Вставить новый столбец со следующим отсутствующим маркером вариант, все отсутствующие порядковые номера были отмечены текстом Missing в новом столбце рядом с вашими данными. См. Снимок экрана:

(2.) Если вы выберете Вставка отсутствующего порядкового номера , все недостающие номера были вставлены в список последовательностей. См. Снимок экрана:

(3.) Если вы выберете Вставка пустых строк при включении отсутствующих порядковых номеров , все пустые строки вставляются, если отсутствуют номера. См. Снимок экрана:

(4.) Если вы выберете Заполнить цвет фона , расположение недостающих чисел будет выделено сразу. См. Снимок экрана:

Быстрое определение последовательности отсутствующих чисел с помощью Kutools for Excel

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