Порівнюємо бази даних
Шановна редакціє! Мені часто доводиться порівнювати великі бази даних в MS Excel. Ця робота забирає багато часу й сил. Хотілося б дізнатися, які прийоми існують для її автоматизації? Якщо можна, опублікуйте такий матеріал в одному з номерів вашої газети. Наперед завдячую.
Валентин Матвєєв, головний бухгалтер, м. Харків
Відповідає
Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
З необхідністю порівнювати бази даних рано чи пізно стикається будь-який бухгалтер, і це не дивно. Пошук помилок в обліку, проведення різних звірянь, виявлення змін у бухгалтерських реєстрах так або інакше пов’язані з цим завданням. І що цікаво: само по собі завдання вкрай зрозуміле, а от його вирішення часто є серйозною проблемою. Адже в реальному житті бухгалтерські бази можуть бути дуже великими — тисячі, десятки, навіть сотні тисяч записів. Щоб порівняти їх, знадобиться багато дорогоцінного часу. Водночас процес можна істотно прискорити. І все, що для цього потрібне, — опанувати кілька нескладних прийомів порівняння баз даних. Про те, як це зробити в програмі Excel, ми поговоримо в нашій статті. А почнемо з елементарних речей.
Поелементне порівняння баз даних
Незважаючи на свою простоту, цей варіант часто зустрічається у практичній роботі. Отже, наше завдання таке. Є дві бази даних, обидві однаково відсортовані. Ці бази ми хочемо порівняти і всі записи, що в них відрізняються, якимсь чином позначити на робочому листі. Для прикладу скористаємося базою, зображеною на рис. 1 (ліворуч). Це — фрагмент звіту «
Карточка счета», отриманий із програми «1С:Бухгалтерия». У ньому частину інформації я видалив, залишивши тільки такі дані: дата операції (колонка «Дата»), назва документа (колонка «Документ»), рахунок дебету проводки (колонка «СчД»), сума по дебету (колонка «СуммаД»), рахунок кредиту проводки (колонка «СчК») і сума кредиту проводки (колонка «СуммаК»). Усього таких звітів у нас два. Вони відрізняються колонкою «Документ». Наше завдання — виявити ці відмінності. Виконуємо такі дії:1) відкриваємо обидва документи, через буфер обміну копіюємо їх вміст на один робочий лист, як показано на рис. 1;
2) до комірки «
N3» уводимо формулу «=B3=I3». Тут перший символ «=» — це ознака початку формули. Другий символ «=» — логічна операція. Формула порівнює комірки «B3» і «I3». Якщо вони рівні, результатом буде значення «ИСТИНА», інакше формула поверне значення «ЛОЖЬ»;3) копіюємо формулу на всю висоту бази даних. Результат показано на рис. 1.
Дивимося на колонку «
N». Деякі комірки в ній позначено значенням «ЛОЖЬ». Це й є ті рядки, що відрізняються вмістом у колонці «Документ» (стовпці «B» та «I»).Результат ми отримали, спробуємо його поліпшити. Найперше відмовимося від коментарів «
ИСТИНА» і «ЛОЖЬ», щоб полегшити знаходження відмінностей у базах. Але це не все. У таблиці ми позначимо лише ті рядки, в яких у колонках «B» і «I» записано різні назви документів. Для однакових рядків жодних коментарів не проставлятимемо. Тоді всі відмінності між базами будуть добре видні. Переглядаючи результат порівняння баз даних з великою кількістю однакових рядків, ми нічого не пропустимо. Для вирішення завдання нам доведеться змінити формулу для порівняння. Виконуємо такі дії:1) стаємо на комірку «
N3». Замість формули «=B3=I3» записуємо вираз: «=ЕСЛИ (B3=I3;””;”Нет”)»;2) копіюємо його на всю висоту таблиці. Тепер праворуч від рядків, що відрізняються колонкою «
Документ», з’явиться напис «Нет» (рис. 2). 
Описаним способом можна порівняти й декілька списків. Для цього у формулі доведеться використовувати логічні операції, наприклад функцію «И()». Ця функція може містити кілька параметрів, кожен з яких має бути логічним виразом. Коли всі параметри мають значення «ИСТИНА», функція «И()» теж поверне значення «ИСТИНА». Якщо хоча б один параметр дорівнює «ЛОЖЬ», результат роботи функції теж буде «ЛОЖЬ». Для прикладу я напишу формулу для порівняння трьох значень, розташованих у комірках «A1», «B1», «C1». Ця формула має такий вигляд: «=И(A1=B1;B1=C1)». Якщо всі вихідні комірки однакові, вона поверне значення «ИСТИНА», інакше результатом роботи формули буде «ЛОЖЬ». Використовуючи такий принцип, ви зможете порівняти відразу декілька баз даних. І ще. У нашому прикладі я помістив бази на одному листі з єдиною метою — забезпечити наочність зроблених змін. На практиці ви можете порівнювати дані, розташовані на різних листах робочої книги. Усе, що для цього потрібне, — задіювати формули з тривимірними посиланнями.
Порівняння баз даних інструментом «Выделить…»
Інколи робота з формулами при порівнянні баз даних небажана. У цьому випадку можна скористатися інструментом виділення групи комірок за умовою. Ще раз нагадаю, що зараз ідеться про поелементне порівняння двох списків. Поки що ми не розглядаємо питання про кількість входжень елементів з однієї бази до іншої, про виявлення загальних записів у базах даних тощо. Про це ми поговоримо трохи пізніше. Зараз нас цікавить звичайне порівняння списків, причому здійснити його ми хочемо без допомоги формул. Виконуємо такі дії:
1) відкриваємо документ, зображений на рис. 1. Зверніть увагу, що в ньому на одному листі зібрано дві бази даних;
2) утримуючи натисненою клавішу «
Ctrl», послідовно клацаємо по колонках «B», «I» (виділяємо їх);3) викликаємо меню «
Правка → Перейти…» або натискуємо на комбінацію «Ctrl+G». З’явиться вікно «Переход», як показано на рис. 3; 
4) у цьому вікні натискуємо на кнопку «Выделить…». Відкриється вікно «Выделение группы ячеек», як на рис. 4;
5) у вікні для виділення комірок ставимо перемикач «Выделить» у положення «отличия по строкам»;
6) натискуємо на «ОК». У колонці «B» Excel виділить блок із декількох комірок. Це ті назви документів, які є у стовпці «B», але відсутні в колонці «I».
Важливо!
Результат роботи інструменту залежить від послідовності виділення колонок. Якщо спочатку вказати стовпець «I», а потім клацнути на колонці «B», то відмінності будуть відмічені в колонці «I».Зверніть увагу, що за допомогою інструменту «
Выделить…» можна знайти відмінності не лише в рядках, а і в колонках у декількох списках.У процесі роботи інструмент «
Выделить…» оперує вмістом комірок. Це потрібно враховувати, коли в даних використовуються розрахункові формули. Може виявитися, що в різних базах є два однакові значення. Але одне з них введене як текст (або число), а інше — підраховане за формулою. У цьому випадку інструмент виділення покаже, що в комірках знаходяться різні значення.Порада
Перш ніж застосувати інструмент виділення для порівняння баз даних, перетворіть усі формули у значення (через меню «Правка → Специальная вставка…»).Інструмент «
Выделить…» зручно використовувати спільно з форматуванням комірок. Наприклад, можна виконати такі дії:1) за допомогою інструменту «
Выделить…» порівняти дві бази даних. Результат Excel виділить окремим блоком;2) не знімаючи виділення, змінити формат цього блока. Наприклад, присвоїти цим коміркам добре помітний колір фону. Тепер усі відмінності між базами будуть як на долоні.
Головний недолік інструменту «
Выделить…» полягає в тому, що він не працює з різними листами робочої книги. Щоб використовувати його для порівняння баз даних, обидва списки повинні знаходитися в межах одного листа.
Порівняння баз даних умовним форматуванням
Для порівняння баз даних можна звернутися до інструменту умовного форматування. Нагадаю, що при визначенні параметрів умовного форматування можна використовувати не лише фіксований набір значень, а й формули. Подивимося, як це застосувати на практиці:
1) відкриваємо базу даних, як показано на рис. 1. До речі, списки для порівняння в цьому випадку можуть знаходитися на різних листах робочої книги. Просто коли видно обидва списки, робота інструменту буде наочнішою;
2) робимо активною комірку «
B3»;3) викликаємо меню «
Формат → Условное форматирование». З’явиться вікно, як на рис. 5; 
4) клацаємо по значку випадаючого списку «Условие 1». Із запропонованих варіантів вибираємо значення «Формула»;
5) у полі для формули вводимо вираз: «=ЕСЛИ($B3=$I3;0;1)»;
6) клацаємо по кнопці «Формат» (рис. 5). Відкриється вікно форматування комірок;
7) у цьому вікні задаємо параметри форматування. Наприклад, вибираємо сірий колір фону;
8) у вікні форматування натискуємо на «ОК»;
9) у вікні «Условное форматирование» натискуємо на «ОК». Ми призначили параметри умовного форматування для комірки «B3».
Згідно з цими параметрами, якщо вміст «B3» не збігається з «I3», то комірку «B3» буде виділено сірим фоном. Використовуючи інструмент «Формат по образцу», параметри умовного форматування для «B3» можна перенести на інші комірки робочого листа. При цьому адреси у формулі Excel відкоригує автоматично, а саме: при копіюванні формату вниз зміняться адреси рядків. При копіюванні вбік Excel відкоригує адреси колонок. Саме тому в формулі задіяно абсолютну адресацію комірок. Тепер виконуємо такі дії:
1) залишаючись на комірці «B3», двічі клацаємо по іконці «Формат по образцу» панелі інструментів «Форматирование»;
2) утримуючи натисненою ліву кнопку миші, обводимо всі комірки колонки «B», а потім — усі комірки колонки «I»;
3) натискуємо на клавішу «Ecs» (завершуємо роботу з інструментом «Формат по образцу»). Результат нашої роботи показано на рис. 6. Тепер усі невідповідності в базі виділено сірим кольором.
Використання умовного форматування для порівняння баз даних має свої переваги. Цей спосіб є дуже наочним. Його можна застосувати відразу до декількох листів робочої книги. Крім того, умовне форматування працює динамічно, подібно до формул Excel. При кожній зміні даних відразу ж оновлюється й форматування комірок.
Перевірка входження елементів одного списку до іншого
Досі ми порівнювали бази даних поелементно, коли порядок дотримання записів у списках був ідентичним. На практиці часто потрібно не просто порівняти бази, а й проаналізувати входження одного списку до іншого. Типовий приклад — формування акта звіряння, зіставлення даних про оплати і надходження тощо. Для вирішення таких завдань є кілька способів, і всі вони побудовані на використанні вбудованих функцій. Ми розглянемо два найпоширеніші варіанти — роботу з функціями «
СЧЁТЕСЛИ()» та «ВПР()» і зробимо це на такому прикладі.Є дві бази даних (два списки), кожна з яких є реєстром із касових документів (прибуткові та видаткові ордери). Склад списків показано на рис. 7, 8. Розташовано їх на листах з іменами «
БД1» і «БД2». Документи у списках записано в довільному порядку. Наше завдання — порівняти обидва списки за полем «Документ», тобто ми хочемо з’ясувати, які документи з одного списку відсутні в іншому. 
СПОСІБ 1. Використовуємо функцію «СЧЕТЕСЛИ()»
Ідея порівняння в цьому випадку проста. Для прикладу почнемо з листа «
БД1». Ми повинні взяти кожен елемент цього списку та підрахувати, скільки разів він наявний у базі на листі «БД2». Якщо для якогось елемента з бази «БД1» це значення дорівнює нулю, отже, він відсутній у базі «БД2». Тепер виконуємо такі дії:1) відкриваємо документ, як показано на рис. 7;
2) переходимо на лист «
БД1»;3) до комірки «
С1» записуємо заголовок «Пр», до комірки «С2» уводимо формулу «=СЧЁТЕСЛИ(БД2!B:B;БД1!B2)»;4) копіюємо формулу на всю висоту таблиці. Результат нашої роботи показано на рис. 9.
Проаналізувавши колонку «
C» (рис. 9), ми виявимо, що в базі «БД2» немає документів «ПКО-000001», «РКО-000003», «РКО-000005». Що стосується документа «РКО-000005», тут усе зрозуміло. Уважно проглянувши список «БД2», ми побачимо, що такого документа дійсно немає в списку. А от з елементами «ПКО-000001», «РКО-000003» ситуація інша. Ці документи в обох базах є, але внаслідок перетворення даних у базах «БД1» і «БД2» їх назви відрізняються. Щоб побачити, у чому полягають ці відмінності, виконуємо такі дії:1) переходимо на лист «
БД2»;2) стаємо на комірку «
B3» (елемент «ПКО-000001»);3) натискуємо на клавішу «
F2» (редагування комірки) та дивимося на рядок формул. У нашому прикладі в кінці тексту «ПКО-000001» стоїть зайвий пробіл. Візуально така відмінність непомітна. Але для функції «=СЧЁТЕСЛИ()» тексти «ПКО-000001» і «ПКО-000001» — це різні речі.Схожа ситуація виникла і для елемента «
РКО-000003». Щоправда, додаткового пробілу в цьому рядку немає, зате є латинська літера «Р»… Нам достатньо просто передрукувати назву документа, і Excel знайде елемент «РКО-000003» у базі «БД2». Відповідно в комірці «C7» з’явиться результат «1».СПОСІБ 2. Використовуємо функцію «ВПР()»
При порівнянні баз даних замість «
СЧЕТЕСЛИ()» часто використовують функцію «ВПР()». У неї три параметри: шукане значення «Знач», блок таблиці «Бл», номер результуючого стовпця «Ном» та ознака інтервального перегляду «Приз». Синтаксис функції має такий вигляд: «ВПР(Знач;Бл;Ном;Приз)». Тут параметр «Бл» — це таблиця з інформацією, де здійснюється пошук даних. Параметр «Знач» указує на елемент, який потрібно знайти у блоці «Бл». Параметр «Ном» — це номер стовпця в таблиці «Бл», з якого Excel візьме результат пошуку. Параметр «Приз» визначає спосіб пошуку даних.Функція «
ВПР()» переглядає крайній лівий стовпець в області «Бл» та намагається знайти в ньому елемент «Знач». Якщо пошук успішний, функція повертає як результат значення зі стовпця з номером «Ном» області «Бл». Інакше функція поверне повідомлення про помилку. Зазвичай як таке повідомлення фігурує значення «#Н/Д» (немає даних).Якщо значення інтервального перегляду «
Приз» дорівнює «ИСТИНА», значення в першому стовпці аргументу «Бл» мають бути відсортовані за збільшенням. Інакше функція «ВПР()» поверне неправильний результат. Щоб шукати дані в невідсортованій таблиці, потрібно присвоїти параметру «Приз» значення «ЛОЖЬ». Аргументами функції «ВПР()» можуть бути текстові рядки, числа, посилання на комірки з даними для пошуку.Важливо!
Функція «ВПР()» порівнює текстові рядки без урахування регістру.Тепер подивимося, як застосувати функцію «
ВПР()» для вирішення нашого завдання. Порівнюємо список «БД2» з базою «БД1», для цього виконуємо такі дії:1) відкриваємо документ, переходимо на лист «
БД2»;2) до комірки «
C1» уводимо заголовок «Пр»;3) переходимо на комірку «
C2», уводимо формулу «=ВПР(B2;БД1!B:B;1;0)»;4) копіюємо формулу вниз до кінця таблиці з базою «БД2». Результат показано на рис. 10. Усі елементи бази «БД2», відсутні у списку «БД1», позначено текстом «#Н/Д».
Судячи з результату (рис. 10), у базі «БД1» немає документів «ПКО-000001», «PКО-000003» і «ПКО-000006». Результат правильний — у назві «ПКО-000001 » стоїть зайвий пробіл, «PКО-000003» надруковано з латинською літерою «P», а документа «ПКО-000006» дійсно немає у списку «БД1».
Скажемо кілька слів про роботу самої формули. Для прикладу виберемо комірку «С2», де записано вираз: «=ВПР(B2;БД1!B:B;1;0)». Ця формула переглядає колонку «B» на листі «БД1». У цій колонці вона шукає назву документа, яку записано в комірці «B2» бази «БД2».
Таблиця для пошуку в нас складається з однієї колонки (стовпець «B» у базі «БД1»). Номер результуючого стовпця (третій параметр функції «=ВПР()») дорівнює «1». Це означає, що при успішному пошуку «ВПР()» поверне як результат назву документа з бази «БД1». Щоб функція «ВПР()» шукала значення в невідсортованому списку, я присвоїв параметру інтервального перегляду значення «0» (або «ЛОЖЬ»). Після копіювання формули вниз таблиця для пошуку залишиться без змін — «БД1!B:B» (адреси рядків ми не вказували). А от значення для пошуку для кожного рядка змінюватиметься: «B3», «B4», «B5» тощо. Таким чином, аргументами для пошуку у функції «ВПР()» будуть усі значення колонки «B» з бази «БД2», а шукатиме їх вона в колонці «B» бази «БД1».
У цілому із завданням ми впоралися. Але значення «#Н/Д» у колонці «С», відверто кажучи, дратують. Спробуємо поліпшити формулу, застосувавши для цього функцію «ЕНД()». Вона належить до групи функцій перевірки властивостей та значень. Її аргументами можуть бути комірки чи формули. Функція перевіряє значення аргументу. Якщо воно дорівнює «#Н/Д», результатом роботи «ЕНД()» буде «ИСТИНА», інакше функція поверне значення «ЛОЖЬ». Щоб перевірити результат «ЕНД()», можна скористатися функцією «ЕСЛИ()». У цілому логіку роботи вдосконаленої формули можна описати таким чином:
— знаходимо документ функцією «ВПР()»;
— аналізуємо результат пошуку функцією «ЕНД()»;
— якщо результат «ЕНД()» дорівнює «ЛОЖЬ» (пошук успішний), повертаємо до комірки порожній рядок;
— інакше повертаємо до комірки текст «Нет» (пошук неуспішний).
Залишилося реалізувати цей алгоритм у вигляді формули. Виконуємо такі дії:
1) ставимо покажчик активної комірки на «C2»;
2) уводимо формулу «=ЕСЛИ(ЕНД(ВПР(B2; БД1!B:B;1;0))=ЛОЖЬ;””;”Нет”)»;
3) копіюємо цю формулу вниз до кінця таблиці. Результат нашої роботи показано на рис. 11.
Залишається застосувати до таблиці автофільтр та відібрати записи, у колонці «Пр» яких стоїть значення «Нет». У результаті побачимо, що в першому списку відсутні документи з номерами «ПКО-000001 », «PКО-000003» і «ПКО-000006».
І останнє. Формули з функціями «СЧЕТЕСЛИ()» і «ВПР()», які ми застосовували для порівняння даних, можна використовувати в параметрах умовного форматування. На мій погляд, це один з найпотужніших прийомів для перевірки баз даних, особливо на етапі їх заповнення.
Успішної роботи! Чекаю ваших запитань, зауважень та пропозицій на
bk@id.factor.ua , nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum .
