Найти пропущенные записи из списка последовательных чисел в столбце?

Question

У меня есть лист Excel, содержащий результаты 66000 тестов. Или, по крайней мере, так должно быть ...

Теперь, поскольку я выполнял тесты асинхронно и продолжал останавливаться и запускаться, у меня была предвидение, чтобы убедиться, что номера тестов были введены в вывод. Теперь, воспользовавшись функцией удаления дубликатов в Excel 2007 на основе этих тестовых номеров, я обнаружил, что у меня 65997 строк данных. Так что трое из них отсутствуют.

Работа здесь состоит в том, чтобы найти недостающие номера задач.

Номера тестов находятся в столбце A в порядке возрастания, и их дубликатов гарантированно не будет. Другие данные помещаются в другие столбцы, и они должны оставаться с номером теста.

  |    A
--+---------
1 | testNum
2 | 1
3 | 2
4 | 3
5 | ...

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

Я мог бы решить это с VBA, но интересно, есть ли более простое решение, которое мне не хватает?

Answer 1

Предполагая, что ваши данные находятся в столбце А и начинаются со строки 2, выберите диапазон в свободном столбце, начиная со строки 2 и включающий в себя как минимум столько строк, сколько вы должны иметь результатов (это важно: если ваш диапазон включает меньше ячеек, чем ваш общий объем последовательность пропущенных результатов будет усечена)¹. В строке формул - не прямо в клетках! - вставить следующую формулу:

=IFERROR(
   SMALL(
     IF(
       ISNA(MATCH(ROW(A1:A66000)-1,A2:A66001,0)),
       ROW(A1:A66000)-1,
     ""),
   ROW()-1),
 "")

- обратите внимание, что это должно быть написано в одну строку ; отступ здесь только для удобства чтения. Сохраните его как формулу массива, нажав Ctrl+Shift+Enter. Если вы сделали это правильно, формула будет отображаться в скобках, а Excel будет жаловаться, когда вы пытаетесь редактировать отдельные ячейки в диапазоне массива.

Для значений в столбце A на снимке экрана ниже формула, сохраненная как формула массива в B2:B21 (20 строк, поскольку полная последовательность будет от 1 до 20), вернет следующие значения в столбце B:

Как видите, это числа, отсутствующие в последовательности (выделено на скриншоте).

---

1. вы можете ошибиться при выборе диапазона для применения, если ссылка ROW(A1:AXXXXX) содержит ровно столько строк, сколько ваша общая последовательность (66000 строк в вашем примере, следовательно, A1:A66000).

Answer 2

В пустом столбце (предположим, Z), начиная со строки 2, поместите формулу и скопируйте все использованные строки.

=A2=A1+1

Искать в столбце Z FALSE . найдет тест по ряду после пропущенного номера

Также вы можете попробовать

= ЕСЛИ (А2 = А1+1 "" А1+1)

Answer 3

Это решение требует двух вспомогательных столбцов. В столбце B заполните последовательные числа от 1 до 66 000. В C2 (в первой строке данных) введите

=IF(A2=B2,0,1)

В C3 введите

=IF(B3=OFFSET(A3,-1*SUM(C$2:C2),0),0,1)

Заполните C3 до C66001.

Отсюда вы можете отфильтровать B:C для записей со значением 1 в столбце C. Это покажет пропущенные числа.

ИЛИ ЖЕ

Если вы хотите получить представление о последнем шаге, вы можете использовать эту формулу массива и заполнить ее так, как вам нужно:

=IFERROR(SMALL(IF($C$2:C$32<>0,$B$2:$B$32),ROW()-1),"")

Введите формулу с помощью Ctrl+Shift+Enter. Этот пример был введен в строке 2. Вам нужно будет настроить ROW()-1 в формуле так, чтобы это равнялось 1 для первой записи. Например, если вы начнете свой список пропущенных номеров на E5, вам нужно изменить его на ROW()-4 . [Спасибо @kopischke за этот метод: ссылка ]