Excel: пошук дублікатів за кількома колонками

Excel: пошук дублікатів за кількома колонками

Шановна редакціє! Допоможіть вирішити таку проблему. Є база даних в Excel. У ній потрібно знайти записи з однаковими значеннями в окремих колонках, що повторюються. Номери цих колонок завчасно відомі. На жаль, ні автоматичний, ні розширений фільтри з таким завданням не впоралися. Чи є інший варіант його вирішення? Наперед завдячую.

А. Фролов, м. Харків

Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

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

Отже, є база даних, зображена на рис. 1. Це фрагмент журналу проводок з кореспонденцією «

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

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

2) до комірки «M2» уводимо формулу «=E2&”-”&G2&”-”&I2&”-”&K2». Цей вираз об’єднує дані з колонок «E», «G», «I», «K» в один робочий ключ. Роздільник «-» я поставив виключно для того, щоб зручніше було контролювати результат роботи формули. Жодної функціональної ролі цей роздільник не відіграє;

3) копіюємо формулу на всю висоту таблиці. Результат у колонці «Ключ» виглядатиме трохи моторошно, але нам зараз краса ні до чого. Тепер наше завдання — знайти в колонці значення «M», що повторюються. Найпростіше це зробити функцією «СЧЕТЕСЛИ()». За її допомогою ми підрахуємо, скільки разів поточне значення робочого ключа зустрічалося в колонці «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) клацаємо по списку «Условие 1», вибираємо варіант «Формула»;

5) у полі формули вводимо вираз «

6) клацаємо по кнопці «

7) у цьому вікні переходимо на закладку «

8) у вікні «

9) у вікні «

10) не знімаючи виділення з блока «

11) утримуючи натисненою ліву кнопку миші, обводимо всю область нашої бази даних.

Усі записи, що повторюються, підсвічуватимуться сірим фоном, як показано на рис. 5. Як бачите, завдання вибору записів-дублікатів у базах даних Excel цілком до снаги!

І останній нюанс. Ви, напевно, звернули увагу, що параметри умовного форматування я присвоїв відразу цілому блоку комірок «

Чекаю ваших запитань, зауважень та пропозицій на