Удалите дубликаты в Excel без потери данных

1. Что на самом деле делает диалог

Данные, удалить дубликаты. Откроется панель со списком ваших столбцов, каждый из которых отмечен флажком. Нажмите «ОК», и Excel сравнит каждую строку с строками над ней, используя только отмеченные столбцы, удалит любую строку, отмеченные значения которой он уже видел, и выдаст сообщение вида «Найдено и удалено 3 повторяющихся значения; осталось 5 уникальных значений».

Три из этих свойств стоит указать прямо, потому что ни одного из них нет на экране.

Он редактирует данные на месте. Строки исчезли с листа. Ctrl+Z работает прямо сейчас, а завтра ничего не работает.

Он никогда не показывает вам, что он удалил. Вы получаете счет. Сравнение этого результата с тем, что вы ожидали, — единственная доступная проверка, и она доступна только в том случае, если вы сначала сформировали ожидание.

Счет идет на количество строк, а не на проблемы. Три удаления могут быть тремя копиями одного неправильного импорта или тремя разными реальными заказами, и в любом случае сообщение читается одинаково.

2. Столбцы, которые вы отметьте, являются определением.

Перед объяснением: входит восемь строк. Предскажите, сколько из них выйдет со всеми четырьмя отмеченными столбцами и сколько с отмеченным только ID заказа.

Оба были запущены. С все четыре столбца отмечено, остается шесть строк:

1001 | Dana Reyes | North | 4
1002 | Owen Park  | South | 10
1003 | Priya Shah | East  | 3
1003 | Priya Shah | East  | 5
1004 | Dana Reyes | North | 6
1005 | Sam Okafor | West  | 12

Только с ID заказа отмечено, остается пять строк:

1001 | Dana Reyes | North | 4
1002 | Owen Park  | South | 10
1003 | Priya Shah | East  | 3
1004 | Dana Reyes | North | 6
1005 | Sam Okafor | West  | 12

Те же данные, та же команда, разница на один тик и уничтожена одна дополнительная строка. Исчезнувшая строка — это заказ 1003 с 5 единицами, который не являлся копией чего-либо. Он имеет общий номер заказа с другой строкой и отличается единственным столбцом, который сравнение было указано игнорировать.

Таким образом, галочки не являются настройкой для удобства. Это ты отвечаешь на вопрос что значит, что две строки — одно и то же, и у Excel нет мнения по этому поводу. Все четыре галочки означают «точную копию всей строки». Один отмеченный столбец означает, что «этот столбец является идентификатором строки, и все остальное в нем можно отбросить». Это совершенно разные утверждения о ваших данных, и в диалоговом окне они представлены как один и тот же жест.

Прежде чем поставить галочку, скажите вслух, что из этих двух вы на самом деле имеете в виду. Если ваш ответ: «Я хочу удалить случайные копии», вам нужны все столбцы. Если это «каждый заказ должен появляться один раз», вам нужен ключ, и вам следует сначала просмотреть разногласия, потому что они представляют собой интересные строки в файле.

3. 21 юнит, покинувший таблицу

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

Total units, all eight rows                          56
Total units, keeping the first row of each OrderID   35

21 единица ушла. Разбивая его, появляется форма задачи: 10 единиц от точного дубликата заказа 1002, который правильно удалить; 6 единиц из заглавного варианта 1004, что, вероятно, правильно; и 5 единиц из заказа 1003, который был настоящей, другой строкой и которого сейчас нет в деле.

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

4. Отметьте перед удалением: две формулы флажка

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

Этот ключ повторяется? Текущий счетчик с использованием ссылки, которая заблокирована вверху и открыта внизу, поэтому она растет по мере заполнения.

=COUNTIF($A$2:$A2, $A2)

Весь этот ряд повторяется? Одна и та же идея сразу во всех столбцах.

=COUNTIFS($A$2:$A2,$A2, $B$2:$B2,$B2, $C$2:$C2,$C2, $D$2:$D2,$D2)

Заполните оба поля, и в таблице будет указано все, чего нет в диалоговом окне.

Row  OrderID  Region  Units   Key repeat  Row repeat
 2   1001     North     4          1           1
 3   1002     South    10          1           1
 4   1002     South    10          2           2
 5   1003     East      3          1           1
 6   1003     East      5          2           1
 7   1004     North     6          1           1
 8   1004     north     6          2           2
 9   1005     West     12          1           1

Rows where the key is a repeat:       3
Rows where the whole row is a repeat: 2

Весь смысл этого заключается в 6-м ряду. Его количество ключей равно 2, а количество строк равно 1, что означает: этот номер заказа был замечен раньше, а эта строка — нет. Это единственное разногласие является фактом, заслуживающим внимания, и это именно та строка, которую молча уничтожает дедупликация в один столбец.

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

5. Какой ряд выживет и как его выбрать

Excel сохраняет первый появление каждой группы в текущем порядке строк листа и удаляет все, что находится под ней. В этом все правило, и именно поэтому сохранившийся ряд 1003 — это ряд с 3 единицами, а не с 5: он был выше.

Это означает, что вы выбираете выжившего, сначала сортируя его. Сортируйте по дате по убыванию, и самая новая версия каждой записи остается актуальной. Сортировка по убыванию суммы и наибольшему выигрышу. Сортировка по вспомогательному столбцу полноты, что-то вроде =COUNTA(B2:F2), по убыванию, и побеждает строка с наименьшим количеством пробелов, которая обычно является той, которая вам нужна, когда один и тот же клиент приходит дважды с разным количеством заполненных подробностей.

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

6. Регистр и пробелы: что считается одинаковым

Перед пояснением: строки 7 и 8 идентичны, за исключением того, что один регион — «Север», а другой — «север». Предскажите, будет ли функция «Удалить дубликаты» рассматривать их как дубликаты.

Это так. Если отмечены все четыре столбца, один из них был удален, а сохранившейся является строка, которая находилась выше. При сравнении Excel здесь игнорируются заглавные буквы, как и соответствующие инструменты:

=COUNTIF(Region, "North")           3    counts "north" as well
=SUMPRODUCT(--EXACT(Region,"North")) 2    the literal spelling only
=COUNTA(UNIQUE(Region))              4    "north" folds into "North"

Обычно это полезно, иногда нет, и в любом случае об этом стоит знать, а не предполагать. Если капиталы действительно различают два значения в ваших данных, коды продуктов являются обычным случаем. EXACT — это функция, которая уважает их, чего не делает ни один из обычных инструментов.

Пространства — это противоположная история и гораздо более опасная. Конечный пробел делает два значения разными, поэтому «Север» и «Север» — это две отдельные группы, и ни одна из них не удаляется. Строки остаются, в отчете увеличивается область, а отчеты «Удалить дубликаты» ничего не обнаруживают. Очищайте ключевой столбец перед дедупликацией и никогда после: весь Процедура TRIM и CODE является обязательным условием для этой страницы, а не альтернативой ей.

А когда значения различаются так, что ни одна функция не может согласовать их между «ООО "Акме"» и «АКМЕ ЛИМИТЕД», вы больше не выполняете дедупликацию. То есть Разрешение объекта, и нажатие этой кнопки не приведет к этому.

7. Отфильтрованные строки небезопасны.

Перед пояснением: вы фильтруете таблицу так, чтобы были скрыты две строки, затем запускаете «Удалить дубликаты». Предскажите, можно ли удалить скрытые строки.

Они могут, и они были. Та же таблица была отфильтрована так, что две 1003 строки были вне поля зрения, затем было выполнено удаление дубликатов только для ID заказа. Результат был идентичен запуску без фильтрации: пять строк и строка 1003 с 5 удаленными единицами.

unfiltered, OrderID only          5 rows remain
rows 5 and 6 hidden, OrderID only 5 rows remain, same rows

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

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

8. Версии, которые ничего не удаляют

Три альтернативы, в порядке возрастания суммы, которую они вам покупают.

UNIQUE. Одна формула, живой дедуплицированный список в другом месте листа и нетронутый оригинал.

=UNIQUE(A2:D9)             the distinct whole rows, spilled out
=ROWS(UNIQUE(A2:D9))       6
=COUNTA(UNIQUE(A2:A9))     5     distinct OrderIDs

Эти два числа, 6 и 5, представляют собой ответы, которые дал бы диалог, полученные без удаления чего-либо примерно за десять секунд. Это сравнение само по себе является диагнозом: разные строки и разные ключи, несовпадающие по одному, означает, что ровно одна строка имеет общий ключ с другой, а не является его копией.

Расширенный фильтр. Данные, Дополнительно, выберите Скопировать в другое место, галочка Только уникальные записи. Это ответ до динамического массива, и он по-прежнему работает везде, в том числе в книге, которую приходится открывать в более старой версии Эксель. Он выдает значения, а не живую формулу, поэтому не обновляется при изменении источника.

Мощность запроса. Загрузите таблицу в редактор запросов, щелкните правой кнопкой мыши ключевой столбец, выберите «Удалить дубликаты» и загрузите результат на новый лист. Его следует использовать для всего, что происходит более одного раза, поскольку шаги записываются и повторно выполняются в файле следующего месяца, а исходные данные никогда не изменяются. Это также делает определение дубликата видимым и доступным для редактирования в виде именованного шага в списке, а не в виде флажка, который кто-то выбрал в закрытом диалоговом окне.

Пограничные случаи, которые выдерживают дедупликацию

Шесть, которые пройдут.

Числа, хранящиеся в виде текста. 1002 количество и "1002" текст имеет разные значения, поэтому оба сохраняются, и пара выглядит одинаково на экране. Исправьте типы перед дедупликацией.

Даты со скрытым временем. Две строки в один и тот же день представляют собой два разных значения, если в одной указано 09:14, а в другой — 14:02. Разделите время с помощью INT во-первых, если день для вас ключевой.

Разницы округления. 10,00 и 10,004 — это разные числа, и в денежном формате оба отображаются как 10,00. Если числовой столбец является частью ключа, округлите его явно, а не форматируйте.

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

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

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