Как найти пропущенное число в таблице

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

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

Время на прочтение
2 мин

Количество просмотров 32K

Разработчики и администраторы систем основанных на sql данных, наверняка, сталкивались с задачей — получить отсутствующее (пропущенное) значение в ряде id записей таблицы. Например номер договора, порядковый номер документа, телефонный номер, айпи-адрес и т.п. При работе с MySQL эта тривиальная задача непропорционально ресурсоемка.

Например, у нас есть пул внутренних телефонных номеров компании от 2001 до 2999 и таблица с выданными из них номерами для сотрудников:

• t1.phone
• 2001
• 2002
• 2003
• 2004
• 2005
• 2009
• 2015
• 2016

Нам нужно найти первое свободное значение (в данном случае 2006) чтобы выделить очередной номер очередному сотруднику. Если свободных значений нет, то нужно выделить следующий из диапазона. Знакомая задача? Решения, которыми изобилует интернет, сводятся к двум принципам:

1) Делать перебор в цикле: например в SQL создать курсор CUR i+1 c 2001 до 2999 и делать запросы

SELECT t1.phone FROM t1 WHERE phone = i

до пустого значения. Цикл можно делать и любым внешним софтом, смысл принципа не меняется.

2) Второй принцип, использовать LEFT (OUTER) JOIN последовательности 2001…2009 с таблицей t1 (WHERE t1.phone IS NULL конечно же), либо таблицы t1 с собой же со сдвигом на шаг:

SELECT MIN(t1.phone)+1 FROM t1 LEFT JOIN t1 AS diff ON (t1.phone = diff.phone+1) WHERE diff.phone IS NULL

Еще один вариант с использованием IN

SELECT ... WHERE phone NOT IN (....)

вообще не рассматриваю из-за громоздкости.

На маленьких объемах данных оба решения (и даже с IN) прекрасно работают, а при большом количестве записей эти решения либо ресурсоемки, либо продолжительны во времени, либо и то и другое.
Зависит от мощностей сервера и настроек базы, но в любом случае если перебирать миллион записей или приджойнить такую таблицу, даже на мощном сервере выполнение займет заметное время.

Я же захотел решить задачу быстро, не напрягая сервер, и, желательно, в один запрос. В один, не в один, но вот что получилось:

	/*выбираю граничные значения*/
	select 2000,2999 into @num,@maxid;

	select min(f.id) /* внешний селект дополнительный для "очистки" результата union */
	from
		(select s.num, min(s.num) /* первое расхождение рядов */ id
		from (
				select
					/* формирую ряд, в данном случае арифм.прогрессия, но может быть с любым шагом, или вычисляемым */
					@num:=@num+1 num,
					/* выбираю занятые значения */
					r.id 
				from t1 as r
				order by id
			) as s
		where 
			/* применяю условие расхождения рядов */ s.id != s.num /* причем ряды разойдутся и расхождение будет постоянным, поэтому во внешнем селекте использую только первое расхождение - min */
		/* если расхождения не было, добавляю еще одно возможное значение из диапазона граничных значений либо null, если значения закончились */
		union
		select @num+1 num, if(@num+1<@maxid,@num+1,null) id
		) as f
	where /* очищаю лишние результаты union */
		f.id is not null
	limit 1;

По сравнению с джойнами простые селекты выполняются в сотни раз быстрее.

Понятное дело, что такое решение известно, но в интернетах, как ни странно, с наскока его найти не удалось, вот и хочу поделиться эти простым «велосипедом».

UPD.
Прекрасное решение подсказал в личке участник stepmex
Без всякого дополнительного построения нумерации и сравнений рядов, изящно решил задачу через (SELECT 1 …..) IS NULL
Великолепная находка, я считаю:

SELECT (`t1`.`phone`+1) as `empty_phone`
FROM `t1`
WHERE (
	SELECT 1 FROM `t1` as `st` WHERE `st`.`phone` = (`t1`.`phone` + 1)
) IS NULL
ORDER BY `t1`.`phone`
LIMIT 1