Как найти пропущенные числа в экселе - Исправление недочетов и поиск решений вместе с Examum.ru

Предположим, у вас есть длинный список порядковых номеров для маркировки элементов, таких как номера чеков в банковских выписках, обычно мы прокручиваем и находим недостающие порядковые номера вручную. Иногда это довольно сложно и требует много времени. Вы можете придумать хитрые способы справиться с этим. Да, есть несколько простых способов быстро и удобно определить и найти последовательность отсутствующих чисел в 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 быстро

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

Допустим, эти числа, обозначающие порядковые номера, расположены в столбце А, например, в ячейках с А6 по А135. Тогда вы в каком либо столбце, например в столбце F, начиная с 7 строки, т.е. в ячейке F7 пишете такую формулу: ЕСЛИ(A7-A6>1;»*»;»»).

В формуле обозначения ячеек записываются латинскими буквами. Теперь выделяете ячейку F7, подводите курсор к правому нижнему углу ячейки (там черный квадратик). Тогда курсор из обычного вида перейдёт в вид прямой чёрный крестик (+). Теперь, нажимаете левую клавишу мыши и удерживая её нажатой, протягиваете курсор по всему столбцу F, включая ячейку F135, и после этого отпускаете клавишу мышки. Тем самым Вы скопируете формулу на весь столбец F, точнее на диапазон ячеек F7-F135. В тех строках, в которых разность между значениями в текущей и вышестоящей строке больше 1, появятся звёздочки (*).

Источник

	В столбце А записаны числа от 1 до 1000 с произвольными пропусками. За одну формулу, вывести в столбец В все пропущенные значения. Помогите сделать, пожалуйста, ничего не получается.

Jack Famous

Пользователь

Сообщений: 11035
Регистрация: 07.11.2014

OS: Win 8.1 Корп. x64 | Excel 2016 x64: | Browser: Chrome

26.10.2020 18:15:47

Цитата
Андрей Картошкин: ничего не получается

не видно, чтобы вы пытались…

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

Пробовал в гугл таблицах вот такое
=arrayformula(ЕСЛИ((arrayformula(A2:A-A1:A))>2;ЕСЛИ(arrayformula(A2:A-A1:A)>2;SEQUENCE(arrayformula(A2+A1+1);1;arrayformula(A1:A+1);1);0);ЕСЛИ((arrayformula(A2:A-A1:A))>1;arrayformula(A1:A+1);0)))

И различные способы предложенные в интернете для Excel, но ничего не работает.

Изменено: Андрей Картошкин — 26.10.2020 18:26:04

rud.666 Пользователь Сообщений: 85 Регистрация: 07.03.2020

	Макросом очень хорошая идея, но к большому сожалению требуют именно формулой((

buchlotnik

Пользователь

Сообщений: 3863
Регистрация: 31.10.2014

Excel 365 Бета-канал

26.10.2020 18:42:03

Цитата
Андрей Картошкин написал: требуют именно формулой

версия эксель какая?

Соблюдение правил форума не освобождает от модераторского произвола
<#0>

	2019 Или в google sheets Изменено: Андрей Картошкин — 26.10.2020 18:45:08

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$1:$A$9;СТРОКА(A1:ИНДЕКС(A:A;МАКС($A$1:$A$9))))=0;СТРОКА(A1:ИНДЕКС(A:A;МАКС($A$1:$A$9))));СТРОКА(A1)) Формула массива, ввод тремя клавишами

БМВ Модератор Сообщений: 21633 Регистрация: 28.12.2016 Excel 2013, 2016	ну можно так =IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX(A:A;MIN(A:A)):INDEX(A:A;MAX(A:A)));A:A;));ROW(INDEX(A:A;MIN(A:A)):INDEX(A:A;MAX(A:A))));ROW(A1));»») По вопросам из тем форума, личку не читаю.

	Эти формулы работают, но есть вариант, чтобы не копировать эту формулу в каждую ячейку столбца или растягивать, а так, чтобы ввести в одну клетку, а все значения автоматически выводились в столбец?

buchlotnik

Пользователь

Сообщений: 3863
Регистрация: 31.10.2014

Excel 365 Бета-канал

#11

26.10.2020 19:14:59

если именно одной формулой, то

Код
=ФИЛЬТР(ПОСЛЕД(МАКС(A1:A9));ЕНД(ПОИСКПОЗ(ПОСЛЕД(МАКС(A1:A9));A1:A9;)))

но это 365, а не 2019; если же формулу можно протягивать — то варианты предложены выше

Прикрепленные файлы

Задание.xlsx (30.85 КБ)

Соблюдение правил форума не освобождает от модераторского произвола
<#0>

rud.666

Пользователь

Сообщений: 85
Регистрация: 07.03.2020

Вот еще интересный вариант, но, правда, тоже макросами (. Что делать: откройте этот файл, разрешите макросы, сверните файл. Перейдите в книгу, где нужно работать. Нажмите Ctrl+Q. Следуйте инструкциям.

Правда здесь неудобно то, что если вы добавите чисел в диапазон с пропущенными числами, то придется заново макрос запускать. Хотя можно написать макрос, который будет запускаться сам при изменении значений в определенном диапазоне)

БМВ

Модератор

Сообщений: 21633
Регистрация: 28.12.2016

Excel 2013, 2016

#13

26.10.2020 20:54:28

Цитата
rud.666 написал: Вот еще ~~интересный~~ вариант

По вопросам из тем форума, личку не читаю.

отличный макрос. одно неудобство, если хоть что-то поменяется — нужно переписывать
но в целом — интересный вариант

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Mershik

Пользователь

Сообщений: 8277
Регистрация: 03.12.2015

#15

26.10.2020 21:17:52

БМВ, Ігор Гончаренко,

пусть еще будет

~~интересный~~

вариант

Код

Sub ads()
Dim i As Long, lr As Long, arr, cell As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
k = 0
Z = Application.WorksheetFunction.Max(Range("A1:A" & lr))
ReDim arr(Z - lr, 0)
For i = 1 To Z
    Set cell = Range("A1:A" & lr).Find(What:=i, LookIn:=xlFormulas, LookAt:=xlWhole)
    If cell Is Nothing Then
        arr(k, 0) = i
        k = k + 1
    End If
Next i
Range("B1:B" & UBound(arr)) = arr
End Sub

Изменено: Mershik — 26.10.2020 21:30:07

Не бойтесь совершенства. Вам его не достичь.

buchlotnik

Пользователь

Сообщений: 3863
Регистрация: 31.10.2014

Excel 365 Бета-канал

#16

27.10.2020 00:43:21

в Гуглах:

Код
=FILTER(ARRAYFORMULA(СТРОКА(A1:A1000));ARRAYFORMULA(ЕНД(ПОИСКПОЗ(СТРОКА(A1:A1000);A1:A9;0))))

Соблюдение правил форума не освобождает от модераторского произвола
<#0>

Источник

Найти недостающие значения

На чтение 2 мин. Просмотров 7k.

= ЕСЛИ( СЧЕТЕСЛИ ( список ; значение ); «OK» ; «Отсутствует» )

Если вы хотите выяснить, какие значения в одном списке отсутствуют из другого списка, вы можете использовать простую формулу, основанную на функции СЧЕТЕСЛИ.

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

В показанном примере, формула в G5 является:

=ЕСЛИ(СЧЁТЕСЛИ($B$5:$B$10;F5);»ОК»; «Отсутствует»)

Где «список» является именованный диапазон, что соответствует диапазону B6: B11.

Функция ЕСЛИ требует логического теста, чтобы вернуть значение ИСТИНА или ЛОЖЬ. В этом случае, если значение найдено, положительное число возвращается СЧЕТЕСЛИ, который имеет значение ИСТИНА, в результате чего, если вернуть «ОК». Если значение не найдено, возвращается ноль, который имеет значение ЛОЖЬ, и ЕСЛИ возвращает «Отсутствует».

Количество пропущенных значений

=СУММПРОИЗВ(—(СЧЁТЕСЛИ(B5:B10;F5:F7)=0))

Для подсчета значений в одном списке, которые отсутствуют в другом списке, вы можете использовать формулу, основанную на функциях СЧЕТЕСЛИ и СУММПРОИЗВ.

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

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

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

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

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

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

Источник

Поиск значений в списке данных

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

Предположим, что вы хотите найти расширение телефона сотрудника, используя его номер эмблемы или правильную ставку комиссионных за объем продаж. Вы можете искать данные для быстрого и эффективного поиска определенных данных в списке, а также для автоматической проверки правильности данных. После поиска данных можно выполнить вычисления или отобразить результаты с возвращаемой величиной. Существует несколько способов поиска значений в списке данных и отображения результатов.

Что необходимо сделать

Точное совпадение значений по вертикали в списке
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Точное совпадение значений по горизонтали в списке
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Создание формулы подступа с помощью мастера подметок (только в Excel 2007)

Точное совпадение значений по вертикали в списке

Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.

Примеры ВРОТ

Дополнительные сведения см. в этой информации.

Примеры индексов и совпадений

Что означает:

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет в C2:C10 первое значение, соответствующее значению «Ольга» (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению «Ольга».

Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.

К началу страницы

Подыыывка значений по вертикали в списке с помощью приблизительного совпадения

Для этого используйте функцию ВЛВП.

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.

Дополнительные сведения см. в этой информации.

К началу страницы

Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения

Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.

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

C1 — это левые верхние ячейки диапазона (также называемые начальной).

MATCH(«Оранжевая»;C2:C7;0) ищет «Оранжевые» в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.

1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.

К началу страницы

Точное совпадение значений по горизонтали в списке

Для этого используйте функцию ГГПУ. См. пример ниже.

Г ПРОСМОТР ищет столбец «Продажи» и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

К началу страницы

Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения

Для этого используйте функцию ГГПУ.

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

К началу страницы

Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )

Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).

В Excel 2007 создается формула подытов на основе данных на основе данных на основе строк и столбцов. Если вы знаете значение в одном столбце и наоборот, мастер под поисков помогает находить другие значения в строке. В формулах, которые он создает, используются индекс и MATCH.

Щелкните ячейку в диапазоне.
На вкладке Формулы в группе Решения нажмите кнопку Под поиск.
Если команда Подытов недоступна, вам необходимо загрузить мастер под надстройка подытогов.

Загрузка надстройки «Мастер подстройок»
Нажмите кнопку Microsoft Office , выберите Параметры Excel и щелкните категорию Надстройки.
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
Следуйте инструкциям мастера.

К началу страницы

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.

Источник