Як перевірити базу даних
 | Достовірна інформація — найважливіший елемент у роботі будь-якого бухгалтера. Жоден комп’ютер, жоден найдосконаліший інструмент не допоможуть вирішити бухгалтерське завдання, якщо воно спирається на неправильні дані. Тому практичні прийоми пошуку та усунення помилок у базах даних для бухгалтера надзвичайно актуальні. І від того, наскільки різноманітними та ефективними будуть ці прийоми, багато в чому залежить успішність усієї його роботи. Один із таких прийомів, спрямованих на виявлення можливих помилок, я пропоную розглянути в цій статті. А точніше, ми поговоримо про пошук записів-дублікатів у базі даних. Тих самих записів, які так часто стають причиною прикрих непорозумінь та головного болю при обробці великих обсягів інформації. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Думаю, що з проблемою пошуку дублікатів так чи інакше стикався будь-який бухгалтер. Принаймні за моїми спостереженнями — це одна з найпоширеніших та найпідступніших помилок у бухгалтерській практиці. Поясню детальніше, про що йдеться. Є великий реєстр (база даних) із кількасот, тисяч, а то й десятків тисяч записів. Із цим реєстром ми збираємося працювати у програмі Excel. Зрозуміло, перш ніж щось робити, дані потрібно перевірити. Загальні цифри нам відомі. Тому насамперед ми порахували підсумки за реєстром, проаналізували, — а вони «не йдуть». При цьому є обґрунтована підозра, що десь щось «почало» двоїтися, до реєстру потрапили декілька зайвих записів, і вони зіпсували всю картину. Якщо помилковий запис один, а сума відхилення за підсумками нам відома, проблему вирішити нескладно. Для цього можна включити автофільтр, відібрати дані за умовою, і помилку, найімовірніше, вдасться знайти. Але на практиці така ситуація трапляється рідко. Згідно з усім відомим законом легке рішення зазвичай неможливе. І порівняти суму відхилення з конкретним записом не вдасться. У цьому випадку потрібно буде застосувати інший, більш загальний підхід. Суть його дуже проста. Потрібно уважно подивитись на дані, виявити в них ті ознаки, які роблять кожен запис унікальним, і після цього скористатися цими ознаками для пошуку зайвих записів у реєстрі. Але це, так би мовити, загальний підхід. А нам важливіше зрозуміти, як він виглядає на практиці. Цим ми зараз і займемося.
Отже, є база даних, фрагмент якої наведено на рис. 1. Ця база є звітом про продажі, який буде використано для формування актів звірок за кожним контрагентом. Структура бази, думаю, зрозуміла. Вона складається із семи полів: дата операції (поле «Дата»), номер накладної (поле «Документ»), назва підприємства (поле «НаимПредпр»), найменування ТМЦ (поле «Наименование»), далі йдуть кількість придбаного товару (поле «Кол-во»), його ціна (поле «Цена») і сума придбання (поле «Сумма»).
Базу даних отримано із системи автоматизації бухгалтерського обліку. А це означає, що підсумкові цифри для цього звіту нам відомі: з аналізу рахунка ми знаємо, що сумарний оборот за базою повинен скласти 12720,00 грн. Тоді як після імпортування даних у MS Excel підсумок дорівнює 12968,00 грн., тобто він відрізняється у більший бік на 248,00 грн. Звідси наша підозра, що до бази якимсь чином потрапили зайві записи, а наше завдання — виявити їх та видалити.
Шукати записи, що повторюються, за назвою контрагента або за товарами в цьому випадку марно. Тут ситуація така. Порахувати кількість записів, що повторюються, ми можемо за допомогою функції «Счетесли()». Але що вибрати як критерій для відбору? Якщо це буде назва підприємства, то таких записів у базі виявиться чимало, і результату ми не отримаємо. Інша річ, якщо умову ускладнити та порахувати кількість рядків, що повторюються, де збігається найменування підприємства та назва товару. Імовірність точного збігу в цьому випадку буде набагато менша, але все одно вона є. Дотримуючись тієї ж логіки, можна до умови відбору додати ще й поле «Сумма». Записів, де збігаються значення відразу за трьома колонками, у базі даних буде небагато. І цілком можливо, що такий складений критерій відразу дозволить нам вирішити поставлене завдання. У будь-якому разі, послідовно доповнюючи критерій новими умовами, ми завжди зможемо звузити зону пошуку так, щоб пошук помилки в базі даних не склав труднощів. Подивимось, як це працює на прикладі таблиці, наведеної на рис. 1. Я пропоную обмежити критерій для пошуку дублікатів трьома значеннями: ми аналізуватимемо назву підприємства, найменування товару та суму продажу. Робимо так:
1) відкриваємо документ, наведений на рис. 1;
2) стаємо на комірку «H1» та вводимо заголовок «Ключ»;
3) у комірку «H2» вводимо формулу: «=C2&D2&G2». Ця формула об’єднає дані з колонок «C», «D» і «G» в один рядок;
4) копіюємо формулу на всю висоту таблиці. Результат вийшов непривабливий, але краса нам зараз ні до чого. Ми використовуватимемо дані з колонки «Ключ» тільки для підрахунку значень, що повторюються;
5) стаємо на комірку «I1», пишемо довільний заголовок. У нашому прикладі я назвав цю колонку «Пр»;
6) у комірку «I2» вводимо формулу «=Счетесли(H:H;H2)». Ця формула порахує кількість значень, що повторюються, із комірки «H2» у колонці «H». Отже, у стовпці «H» ми отримаємо кількість записів, в яких збігається назва підприємства, придбаний товар та сума покупки. Якщо отримане значення дорівнює «2» або більше, то для нас це сигнал про можливу помилку;
7) копіюємо формулу на всю висоту таблиці. Результат нашої роботи наведено на рис. 2. Подивимось, що в нас вийшло;
8) у меню «Главная» клацаємо по іконці «Сортировка и фильтр» (група «Редактирование»);
9) із меню, що відкрилося, вибираємо варіант «Фильтр». У заголовках колонок з’являться значки для фільтрації даних;
10) клацаємо лівою кнопкою по такому значку в колонці «Пр». Відкриється вікно настройок, наведене на рис. 3;
11) у цьому вікні вводимо параметри, як зображено на рис. 3, — нас цікавлять записи, де число повторів «2» або більше;
12) у вікні параметрів фільтру натискуємо «ОК». Усе, записи, що повторюються, у нас як на долоні (рис. 4).
У результаті роботи автофільтру на екрані залишилися чотири записи: це рядки з номерами «4», «9», «10» і «13». Це означає, що у вихідній базі вони повторюються рівно по два рази, про що свідчать значення в колонці «I». Насправді ми бачимо, що в базі даних у записів «4» і «10» (контрагент «ЧП "Матадор"») повністю збігаються значення в колонках «НаимПред», «Наименование» та «Сумма». Та сама картина характерна й для записів з номерами «9» і «10» (підприємство «ТОВ "Vision"»);
13) видаляємо зайві рядки (у базі потрібно залишити тільки один примірник із відібраних записів). Хай це будуть рядки з номерами «4» і «9»;
14) відключаємо автофільтр та підсумовуємо значення за колонкою «Сумма». Тепер результат дорівнює 12720,00 грн., що і потрібно було отримати.
Замість автофільтру значення, що повторюються, зручно шукати за допомогою умовного форматування. У нашому прикладі це можна зробити так:
1) відкриваємо таблицю, наведену на рис. 2, клацаємо по іконці «Сортировка и фильтр» та вибираємо варіант «Фильтр» (відключаємо роботу автофільтру);
2) стаємо на комірку «I2»;
3) натискуємо клавішу «F2» (або клацаємо лівою кнопкою в рядку формул);
4) виділяємо текст формули та копіюємо його в буфер обміну (комбінація «Ctrl+C»);
5) натискуємо «Esc» — завершуємо роботу в рядку формул та повертаємося на робочий лист;
6) на стрічці меню «Главная» знаходимо групу «Стили», клацаємо по іконці «Условное форматирование», із запропонованого меню вибираємо пункт «Создать правило…» (рис. 5). Відкриється вікно, як наведено на рис. 6;
7) у цьому вікні вибираємо варіант «Использовать формулу для определения форматируемых ячеек». Вікно набере вигляду, як зображено на рис. 6, — під рядком «Форматировать значения, для которых следующая формула является истинной:» у ньому з’явиться область для введення формули;
8) клацаємо лівою кнопкою миші усередині цієї області та натискуємо «Ctrl+V» — вставляємо до неї вміст із буфера обміну. На цей момент у буфері у нас знаходиться текст формули, скопійований із комірки «I2»;
9) клацаємо по кнопці «Формат…». Відкриється вікно «Формат ячеек», зображене на рис. 7;
10) у цьому вікні переходимо на закладку «Шрифт», клацаємо по списку «Цвет:» та на палітрі вибираємо «Красный»;
11) переходимо на закладку «Заливка» і параметр «Цвет фона:» встановлюємо в положення «Желтый»;
12) у вікні «Формат ячеек» натискуємо «ОК».
Умовний формат для комірки «I2» ми створили. Залишається поширити його на всі комірки в колонці «I». Робимо так:
1) стаємо на комірку «I2», де вже задано параметри умовного форматування;
2) на стрічці меню «Главная» у групі «Буфер обмена». Клацаємо по іконці «Формат по образцу» (рис. 8);
3) утримуючи ліву кнопку миші, обводимо блок значень у колонці «I» для кінця таблиці.
Остаточна таблиця залишилася такою ж, як і на рис. 2. Але форматування колонки «I» у ній змінилося: тепер значення в комірках «I4», «I9», «I10», «I13» виділено червоним шрифтом на жовтому фоні. Це означає, що записи, які повторюються, потрібно шукати серед рядків «4», «9», «10» і «13» нашої таблиці.
І ще. На практиці трапляються ситуації, коли просте об’єднання значень з декількох колонок операцією «&» не дозволяє отримати унікальний робочий ключ. Суто гіпотетичний приклад: об’єднання значень «Бухгалтер», «&Компьютер», а також «Бухгалтер&», «Компьютер» дасть однаковий результат. У деяких випадках (хоча це буває вкрай рідко!) таку ситуацію потрібно відстежити та отримати різний робочий ключ. Рецепт у цьому випадку простий. При об’єднанні формул операцією «&» потрібно між значеннями вставити довільний символ. Бажано такий, якого немає у вихідній таблиці. Для нашого прикладу ми могли б використати знак «\». І тоді вирази для робочого ключа «="Бухгалтер" &"\"& "&Компьютер"» и «="Бухгалтер&" &"\"& "Компьютер»» дадуть різний результат. У першому випадку вийде «Бухгалтер\&Компьютер», а в другому — «Бухгалтер&\Компьютер». І проблему буде вирішено.
І останній момент. Ми розглянули універсальний прийом для пошуку значень, що повторюються, у базі даних. Його можна використовувати не лише в усіх версіях MS Excel, а взагалі для будь-якої електронної таблиці. Якщо ж говорити про програми Excel 2007(2010), у них є інші, спеціальні інструменти для вирішення нашого завдання. Про те, що це за інструменти, які їх переваги та недоліки, ми поговоримо в одній із наших наступних статей. А на сьогодні все.
Успішної роботи! Чекаю ваших листів, пропозицій та зауважень на bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції.
