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

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

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

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

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

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

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

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

20
задан kopischke
16.03.2023 1:31 Количество просмотров материала 2461
Распечатать страницу

3 ответа

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

=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:

Screenshot of the formulaic listing of missing values in a sequence

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

  1. вы можете допустить ошибку на щедрой стороне при выборе диапазона для применения до тех пор, как ROW(A1:AXXXXX) ссылка включает в себя ровно несколько строк, равных вашей общей последовательности (66000 строк в вашем примере, следовательно A1:A66000).
5
отвечен kopischke 2023-03-17 09:19

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

=A2=A1+1

поиск в столбце Z на FALSE. найдешь тест в строке после пропущенного числа

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

=IF (A2=A1+1,"", A1+1)

3
отвечен chris neilsen 2023-03-17 11:36

для этого решения требуются два вспомогательных столбца. В столбце B впишите числа подряд от 1 до 66000. В C2 (в первой строке данных) введите

=IF(A2=B2,0,1)

в C3 введите

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

заполните вниз C3 к C66001.

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

или

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

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

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

2
отвечен Excellll 2023-03-17 13:53

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Мы используем cookie. Это позволяет нам анализировать взаимодействие посетителей с сайтом и делать его лучше. Продолжая пользоваться сайтом, вы соглашаетесь с использованием файлов cookie. Политика конфиденциальности
Согласен
Вверх