Пошук дублікатів у базі даних

Пошук дублікатів у базі даних

Шановні співробітники «Б & К»! Підкажіть, як у базі даних Excel швидко знайти значення, що повторюються? Таке завдання мені доводиться вирішувати, наприклад, при складанні різного виду довідників. Або просто для контролю правильності даних. Думаю, що це питання буде цікавим не лише мені, а й іншим вашим читачам. Наперед завдячую.

Олексій Пархоменко, м. Харків

Відповідає

Для бухгалтера контроль правильності даних дуже важливий. Адже обсяг інформації в бухгалтерських розрахунках зазвичай великий. Цю інформацію доводиться багато разів копіювати, сортувати, переносити з одного листа на інший. І кожного разу є ймовірність помилитися! Наприклад, випадково продублювати декілька зайвих записів. У результаті дані будуть перекручені, подальші розрахунки робити вже безглуздо. Своєчасно виявити таку помилку серед декількох тисяч рядків без спеціальних прийомів практично неможливо. І в цьому полягає основна проблема. Прикро витратити кілька годин дорогоцінного часу, а потім побачити в базі декілька зайвих значень і всі розрахунки починати з нуля. Сьогодні поговоримо про деякі практичні прийоми, які допоможуть уникнути такої ситуації. Ці прийоми я покажу на прикладі бази даних, зображеної на рис. 1. У ній я залишив усього три поля — найменування товару, одиницю виміру та залишок на поточну дату. Наше завдання — знайти в базі найменування, що повторюються, та перевірити: потрібні вони насправді чи ні. Вирішити таке завдання можна різними способами. На мій погляд, найбільш ефективними є використання сортування, формул та умовного форматування.

Пошук дублікатів за допомогою формул

Це найбільш простий варіант. Щоб скористатися ним, робимо так:

1) відкриваємо документ, ставимо покажчик активної комірки всередину бази даних;

2) викликаємо меню «

3) у цьому вікні клацаємо по полю «Сортировать по», із запропонованого списку вибираємо «Наименование». Перемикач напряму сортування можна залишити за умовчанням у положенні «по возрастанию». У нашій базі є заголовки, тому перемикач «Идентифицировать диапазон данных по» залишаємо в положенні «подписям (первая строка диапазона)»;

4) у вікні «Сортировка диапазона» натискуємо «ОК». Excel відсортує базу за збільшенням значень у полі «Наименование». У результаті всі однакові записи буде зібрано разом, побачити їх буде вже легко. Але ми підемо далі;

5) у комірку «D1» вводимо будь-який заголовок (у прикладі на рис. 3 ця колонка називається «Пр»);

6) у комірку «D2» вводимо формулу «=A2=A1»;

7) копіюємо формулу на всю висоту таблиці. Остаточний вигляд документа з формулою зображено на рис. 3.

Нам залишається нашвидку переглянути таблицю. Усі найменування, що повторюються, буде відмічено значенням «ИСТИНА» у колонці «Пр».

Стисло з приводу формули. У виразі «=A2=A1» перший символ «=» — це ознака початку формули. Другий символ «=» — це логічний оператор (перевірка на рівність). Формула порівнює значення в комірках «A1» і «A2» (тобто поточної комірки та тієї, що розташована на одну позицію вище). Якщо вміст збігається, формула поверне значення «ИСТИНА». В іншому разі результатом роботи формули буде «ЛОЖЬ». Це й є наочна ознака записів, що повторюються. За бажання для порівняння можна скористатися функцією «ЕСЛИ()». У цьому випадку формула для комірки «D1» могла б виглядати так: «=ЕСЛИ(A2=A1;”*”;””)». Після копіювання формули на всю висоту таблиці значення, що повторюються, буде позначено символом «*».

Пошук дублікатів умовним форматуванням

У деяких випадках робота з формулою для виявлення записів, що повторюються, може бути небажаною. Тоді можна звернутися до інструменту умовного форматування. Робимо так:

1) відкриваємо документ з базою даних (рис. 1);

2) через меню «

3) клацаємо по комірці «

4) викликаємо меню «

5) в однойменному вікні клацаємо по значку випадного списку «

6) у полі для введення формули друкуємо вираз «=A2=A1» (рис. 4);

7) клацаємо по кнопці «Формат…». Відкриється вікно «Формат ячеек» (рис. 5);

8) у вікні форматування вибираємо оформлення для комірок, які потрапляють під дію заданої умови. Наприклад — червоний шрифт напівжирного зображення й світло-жовтий колір фону;

9) у вікні «Формат ячеек» натискуємо «ОК»;

10) у вікні «Условное форматирование» натискуємо «ОК»;

11) залишаючись на комірці «A2», клацаємо по інструменту «Формат по образцу» (він розташований на панелі інструментів «Форматирование», рис. 6);

12) утримуючи ліву кнопку миші, виділяємо заповнені комірки в колонці «А». Усі значення, що повторюються, у стовпці «Наименование» буде оформлено червоним кольором на жовтому фоні (рис. 7). Усе, що залишається зробити, — переглянути фрагменти таблиці, виділені кольором, та проаналізувати їх.

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