Excel: поиск дубликатов по нескольким колонкам

Excel: поиск дубликатов по нескольким колонкам

Уважаемая редакция! Помогите решить такую задачу. Есть база данных в Excel, в которой нужно найти повторяющиеся записи с одинаковыми значениями в отдельных колонках. Номера этих колонок заранее известны. К сожалению, ни автоматический, ни расширенный фильтры с такой задачей не справляются. Есть ли другой вариант ее решения? Спасибо.

А. Фролов, г. Харьков

Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

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

Итак, есть база данных, изображенная на рис. 1. Это фрагмент журнала проводок с корреспонденцией «

Наша задача — отыскать эту накладную. То есть мы должны найти в базе данных все записи с повторяющимися значениями в колонках «Сбк2», «Сбк3», «Дебет» и «Сумма» и среди них попытаться обнаружить причину ошибки. Делаем так:

1) открываем файл с базой данных, справа от основной таблицы вводим две колонки. Одна будет называться «Ключ» (это значение мы пишем в ячейку «M1»), вторая — «Пр» (этот текст пишем в «N1»);

2) в ячейку «M2» вводим формулу «=E2&"-"&G2&"-"&I2&"-"&K2». Это выражение объединяет данные из колонок «E», «G», «I», «K» в один рабочий ключ. Разделитель «-» я поставил исключительно для того, чтобы удобнее было контролировать результат работы формулы, никакой функциональной роли он не играет;

3) копируем формулу на всю высоту таблицы. Результат в колонке «Ключ» будет выглядеть жутковато, но нам сейчас красота ни к чему. Теперь наша задача — найти повторяющиеся значения в колонке «M». Проще всего это сделать функцией «СЧЁТЕСЛИ( )». C ее помощью мы посчитаем, сколько раз текущее значение рабочего ключа встречалось в колонке «I». Для этого делаем так:

4) в ячейку «N2» вводим формулу «=СЧЁТЕСЛИ(M:M;M2)»;

5) копируем формулу на всю высоту колонки «Пр». Результат нашей работы показан на рис. 2 (часть колонок на этом рисунке скрыта).

В принципе задача решена, но есть подозрительные моменты, например нулевые значения в колонке «Пр» (ячейка «N3» на рис. 2). Конечно, на решение задачи она не повлияет, но порядок есть порядок, и разобраться нужно.

Причина такого поведения нашей таблицы в функции «=СЧЁТЕСЛИ(M:M;M2)». Она не воспринимает текст «<…>» (пустые значения субконто в отдельных счетах проводок). Чтобы расчеты приобрели более наглядный вид, делаем так:

1) выделяем колонку «E» (она участвует в расчетах, и в ней есть текст «<…>»);

2) нажимаем «Ctrl+H» (меню «Правка → Заменить»);

3) в поле «Найти» вводим текст «<…>»;

4) в поле «Заменить на:» вводим пустую строку;

5) нажимаем «Заменить все». Нулевые значения в колонке «Пр» исчезли.

Все, что нам остается, — выбрать повторяющиеся значения, ориентируясь на единственный столбец «Пр». С этой задачей успешно справится и автофильтр. Делаем так:

1) вызываем «Данные → Фильтр → Автофильтр»;

2) щелкаем на значке выбора в колонке «Пр»;

3) из раскрывшегося меню выбираем «Условие…». Появится окно «Пользовательский автофильтр»;

4) в этом окне выбираем тип условия «Больше или равно», а в поле значения вводим «2»;

5) в окне «Пользовательский автофильтр» нажимаем «ОК».

На экране останутся четыре записи (рис. 3). Среди них легко обнаружить причину ошибки: две одинаковые приходные накладные от «Барт и КО» от «06/12/2009» и от «13/12/2009».

Лично мне нравится другой способ выбора повторяющихся значений. В нем не нужно использовать автофильтр. Делаем так:

1) через меню «Данные → Фильтр → Автофильтр» отменяем действие автофильтра;

2) выделяем блок ячеек «A2:N2»;

3) вызываем меню «Формат → Условное форматирование». Появится окно, как на рис. 4;

4) щелкаем на списке «Условие 1», выбираем вариант «Формула»;

5) в поле формулы вводим выражение «=$N2>1»;

6) щелкаем на кнопке «Формат». Откроется окно «Формат ячеек»;

7) в этом окне переходим на закладку «Вид». Выбираем серый цвет для заливки ячеек. Разумеется, вы можете остановить свой выбор на других параметрах форматирования;

8) в окне «Формат ячеек» нажимаем «ОК»;

9) в окне «Условное форматирование» нажимаем «ОК»;

10) не снимая выделения с блока «A2:N2», щелкаем на кнопке «Формат по образцу» панели инструментов «Стандартная»;

11) удерживая левую кнопку мыши, обводим всю область нашей базы данных.

Все повторяющиеся записи будут подсвечены серым фоном, как показано на рис. 5. Как видите, задача выбора записей-дубликатов в базах данных Excel вполне по плечу!

И последняя тонкость. Вы, наверное, обратили внимание, что параметры условного форматирования я присвоил сразу целому блоку ячеек «A2:N2» и затем копировал формат этого блока на всю высоту таблицы. Причина такого действия находится в колонке «Дата». В принципе мы могли бы определить условный формат для ячейки «A2», затем вызвать инструмент «Формат по образцу» и перенести параметры форматирования с «A2» на всю остальную таблицу. Но тогда произойдет следующее. В колонке «A» записаны даты, причем представлены они в соответствующем формате — «дд/мм/гг». Этот формат вместе с параметрами условного форматирования распространится при копировании на все ячейки таблицы, в том числе и на колонку «Кол», в которой записаны числа. Разумеется, что представление этих чисел будет нарушено и для правильного отображения таблицы формат в колонке «L» придется восстановить. Чтобы не делать лишнюю работу, мы запомнили формат сразу целой строки «A2:N2» и скопировали его вниз. При этом представление каждой колонки, разумеется, сохранилось.

Жду ваших вопросов, замечаний и предложений на