Как найти недостающее число в таблице

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

Fine, if this question isn’t going to be closed, I may as well Copy and paste my answer from the other one:

I called my table Blank, and used the following:

declare @StartOffset int = 2
; With Missing as (
    select @StartOffset as N where not exists(select * from Blank where ID = @StartOffset)
), Sequence as (
    select @StartOffset as N from Blank where ID = @StartOffset
    union all
    select b.ID from Blank b inner join Sequence s on b.ID = s.N + 1
)
select COALESCE((select N from Missing),(select MAX(N)+1 from Sequence))

You basically have two cases – either your starting value is missing (so the Missing CTE will contain one row), or it’s present, so you count forwards using a recursive CTE (Sequence), and take the max from that and add 1

Tables:

create table Blank (
    ID int not null,
    Name varchar(20) not null
)
insert into Blank(ID,Name)
select 2 ,'Fred' union all
select 3 ,'Fred' union all
select 4 ,'Fred' union all
select 6  ,'Fred' union all
select 7 ,'Fred' union all
select 8 ,'Fred' union all
select 11 ,'Fred'
go

Пересказ статьи Brent Ozar. How to Find Missing Rows in a Table

Когда кто-то говорит: “Найдите все строки, которые был удалены”, – это много проще сделать, когда таблица имеет столбец identity (id). Давайте возьмем таблицу Users в базе данных Stack Overflow:

Имеются id: -1, 1, 2, 3, 4, 5…, но нет 6 или 7. (Или 0). Если кто-нибудь попросит вас найти все id, которые были удалены или пропущены, как это сделать?

Использование GENERATE_SERIES в SQL Server 2022 и новее

Новая функция GENERATE_SERIES делает то, о чем говорит ее название: генерирует последовательность чисел. Мы можем выполнить соединение таблицы Users с этой последовательностью, чтобы найти все значения последовательности, для которых нет совпадающих строк в таблице Users:

DECLARE @FirstId INT, @LastId INT;

SELECT @FirstId = MIN(Id),

	@LastId = MAX(Id)

	FROM dbo.Users;

SELECT gs.value

FROM GENERATE_SERIES(@FirstId, @LastId, 1) gs

LEFT OUTER JOIN dbo.Users u ON gs.value = u.Id

WHERE u.Id IS NULL;

Поначалу LEFT OUTER JOIN может вам показаться несколько неестественным, но работает отлично:

Что такое, спросите вы? Почему GENERATE_SERIES подчеркнуто красным? Ну, SQL Server Management Studio не обновилась синтаксисом T-SQL, который был в последнем релизе.

К счастью, Microsoft разделила установки приложений для SSMS и самого ядра SQL Server именно по этой причине – долгое время выпуска SSMS не позволяло команде разработчиков ядра ускорить выпуск, поэтому они поместили менее часто обновляемую SSMS в свой собственный установщик.

(Я правильно понял? Простите меня, если это не так.)

Использование таблицы чисел в более старых версиях

Если вы еще не работаете на SQL Server 2022, то можете создать собственную таблицу чисел, используя следующие примеры. Только убедитесь, что ваша таблица чисел содержит по крайней мере столько строк, сколько номеров id вы ищете. Вот пример с таблицей на 100000000 строк:

DROP TABLE IF EXISTS dbo.Numbers;

CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY CLUSTERED);  

INSERT INTO dbo.Numbers(Number)

SELECT TOP 10000000 row_number() over(order by t1.number) as N

FROM master..spt_values t1 

    CROSS JOIN master..spt_values t2

    CROSS JOIN master..spt_values t3

GO

Затем мы будем использовать это в том же ключе, что и GENERATE_SERIES:

DECLARE @FirstId INT, @LastId INT;

SELECT @FirstId = MIN(Id),

	@LastId = MAX(Id)

	FROM dbo.Users;

SELECT n.Number

FROM dbo.Numbers n

LEFT OUTER JOIN dbo.Users u ON n.Number = u.Id

WHERE u.Id IS NULL

  AND n.Number > @FirstId

  AND n.Number < @LastId

ORDER BY n.Number;

Это дает похожие результаты, но не идентичные:

В чем отличие? Да, этот метод не включает 0! Когда я наполнял мою таблицу чисел, то строил список только положительных целых. Единственной наиболее часто встречающейся мне ошибкой при использовании таблиц чисел является то, что они не охватывают всех необходимых нам чисел. Убедитесь, что они содержат как самое меньшее, так и самое большее из требуемых вам значений – проблема, которой мы не имеем с GENERATE_SERIES, поскольку там мы просто указываем начальное и конечное значения, а SQL Server делает все остальное.

Я не хочу погружаться глубже в другие способы решения этой проблемы, Itzik Ben-Gan введет вас в курс дела. Что до меня, мне нравится решать эту проблему быстро и легко с помощью GENERATE_SERIES. Кроме того, я ленив.

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