Excel: перевіряємо текст за маскою
До пошти нашої редакції періодично надходять запитання щодо роботи з текстом у програмі Excel. Наведу цитату з одного такого листа: «Шановні працівники «Б & К»! У більшості офісних прикладних рішень є зручна функція для перевірки тексту на відповідність шаблону. При роботі з документом у програмі Word така функція дозволяє знайти слова, що містять певну комбінацію символів. У програмі Excel при обробці баз даних інструментом «Автофильтр» використання шаблонів дозволяє відібрати записи за ознакою «Содержит». Але всі ці функції викликаються через меню. А хотілося б мати можливість аналізувати текст за шаблоном за допомогою формул. На жаль, серед вбудованих функцій роботи з текстом в MS Excel я відповідного варіанта не знайшов. Підкажіть, як можна вирішити цю проблему?». Що ж, завдання поставлено, залишається тільки знайти його рішення. Саме цим ми й займемось.
Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
Робота з шаблонами (або так званими масками) — одна з найкорисніших функцій, коли йдеться про роботу з великими базами даних, а критерій пошуку інформації точно не відомий. На практиці така ситуація трапляється часто-густо. Елементарний приклад. У мене є реєстр виданих (чи отриманих) податкових накладних. Реєстр великий, кілька тисяч записів. І завдання полягає в тому, щоб знайти у цьому реєстрі всі документи щодо певної фірми. Точну назву фірми (як вона фігурує в реєстрі) я не пам’ятаю — може, це «ООО "Фора"», може, «ТОВ "Фора"», якось так. Але те, що в назві є слово «Фора», я знаю напевно. А це означає, що для пошуку інформації про фірму «Фора» мені потрібно використовувати не точну назву підприємства, а певний шаблон. У цьому випадку він може мати вигляд: «*Фора*». У цьому шаблоні на місці символу «*» може стояти будь-який рядок довільної довжини. А сам шаблон описує назви фірм, де спочатку йде будь-який текст, потім є слово «Фора», а за ним — знову довільний текст. Під цей шаблон підпадають, наприклад, такі назви: «ТОВ "Фора"», «ООО "Фора"», «Фирма "Фора"», «"Фора", ltd» тощо. Інакше кажучи, шаблон «*Фора*» задає не точну, а приблизну назву підприємства для пошуку його в реєстрі. А це саме те, що нам потрібно. Залишається реалізувати такий пошук на практиці.
Тут ситуація така. У програмі Excel (а йдеться саме про неї!) є різні способи вирішення цього завдання. Наприклад, пошук та вибір даних можна організувати за допомогою автофільтра. У цьому випадку шаблон для пошуку потрібно ввести до настройок фільтра, і таку можливість у програмі Excel передбачено. Інша справа, коли потрібно не просто вибрати дані, а ще й обробити їх. Для прикладу звернемося до бази даних, фрагмент якої показано на рис. 1. Припустимо, ми хочемо написати формулу, що підсумує всі значення з колонки «Сумма с НДС» для контрагента «ТОВ "Фора"». Нам відомо, що назва цього контрагента в базі може відрізнятися (наприклад, у назві може бути зайвий пробіл). Але слово «Фора» у назві є завжди, і воно є ключовим для організації пошуку.
У принципі, упоратися із завданням ми могли б за допомогою зведених таблиць. Але проблема полягає в тому, що в них не можна використовувати шаблони для аналізу текстових рядків. Другий варіант — скористатися формулами. Але й тут проблема залишається, оскільки із 24 вбудованих текстових функцій MS Excel жодна не працює із шаблонами. Причому ця ситуація характерна для всіх версій програми — починаючи від Excel 95 і аж до останньої редакції Excel 2010.
Тому для вирішення завдання нам знадобиться функція, що вміє порівнювати текстові рядки за заданим шаблоном. І тоді, за наявності такої функції, застосування будь-якого інструменту обробки даних уже не викличе труднощів.
Створити функцію для порівняння рядків зовсім нескладно, якщо скористатися можливостями мови Visual Basic for Application (VBA) програми MS Excel. Усе, що нам доведеться зробити, — це відкрити редактор Visual Basic і написати в ньому буквально десяток рядків. А в результаті ми зможемо істотно розширити свої можливості щодо обробки даних і заразом закрити суттєву лакуну в роботі з текстом, наявну у стандартних засобах MS Excel.
Створюємо функцію перевірки тексту за шаблоном
Отже, ми вирішили, що для створення функції порівняння рядків нам потрібно написати невелику програму мовою Visual Basic, що входить до складу MS Excel. Зрозуміло, програмування на VBA не є профільним завданням бухгалтера. Тому ми не заглиблюватимемося в нюанси роботи програми. Нам достатньо знати в загальних рисах, який вигляд вона має та як вставити її в документ MS Excel. Крім того, повний текст функції ми викладемо на сайті редакції, де ви зможете скачати його без жодних обмежень. Почнемо.
Якщо ви працюєте з Excel 2010, то перше, що потрібно зробити для роботи з VBA, — це включити вкладку «Разработчик» (за умовчанням цю можливість відключено). Для цього виконуємо такі дії:
1. Відкриваємо програму Excel. Завантажуємо файл із базою даних, як на рис. 1. У нашому прикладі вона називається «РеестрНН.xls».
2. Викликаємо меню «Файл → Параметры» та вибираємо розділ «Настройка ленты». Відкриється вікно, зображене на рис. 2.
3. У групі параметрів «Настройка ленты:» включаємо прапорець ліворуч від рядка «Разработчик» (рис. 2).
4. У вікні настройок натискуємо на «ОК». У складі основного меню Excel 2010 з’явиться пункт «Разработчик» (рис. 3).
5. Клацаємо лівою кнопкою по розділу «Разработчик» головного меню. Стрічка набуде вигляду, як на рис. 3.
6. У групі «Код» клацаємо по іконці «Visual Basic» (рис. 3). Відкриється вікно, зображене на рис. 4. Це і є редактор Visual Basic.
У лівій частині вікна показано структуру проекту для поточної робочої книги. До цього проекту нам і потрібно додати текст своєї функції.
Важливо! В Excel 2003 для виклику Visual Basic увійдіть до меню «Сервис → Макрос → Редактор Visual Basic». На екрані з’явиться вікно, зображене на рис. 4.
Тепер наше завдання — вставити в документ «РеестрНН.xls» текст функції для порівняння рядків. Виконуємо такі дії:
1. Заходимо на сайт за адресою www.bk.factor.ua/ru/publications/14/8/1106.html , скачуємо файл із текстом програми.
2. Зберігаємо результат на жорсткий диск.
3. Відкриваємо скачаний файл у будь-якому текстовому редакторі. Можна скористатися програмою «Блокнот» або процесором MS Word. Перед вами повинен з’явитися такий текст:
‘ Функция для сравнения текстовых строк по маске
Function MskCmp(Tekct As String, Maska As String, Reg As Boolean)
If Not Reg Then
Tekct = UCase(Tekct)
Maska = UCase(Maska)
End If
If Tekct Like Maska Then
MskCmp = True
Else
MskCmp = False
End If
End Function
4. Натискуємо на комбінацію клавіш «Ctrl+A» (виділити все).
5. Комбінацією «Ctrl+C» копіюємо текст до буфера обміну.
6. Переходимо до вікна Visual Basic (рис. 4).
7. Викликаємо меню «Insert → Module». Відкриється вікно для додавання тексту програми.
8. Натискуємо на комбінацію клавіш «Ctrl+V» (вставляємо текст із буфера обміну).
9. Натискуємо на кнопку «Сохранить» та закриваємо редактор Visual Basic. Функцію додано.
Перш ніж рухатися далі, подивимося, що ми отримали насправді. У результаті роботи з Visual Basic у нас з’явиться додаткова функція з іменем «MskCmp()». У неї три параметри: текст (або комірка з текстом), який ми перевірятимемо на відповідність шаблону, другий параметр — це сам шаблон. І, нарешті, останній, третій параметр — це ознака контролю регістру символів «Reg». Якщо цей параметр дорівнює «0», то функція не контролюватиме регістр. Якщо параметр дорівнює «1», то пошук виконуватиметься з урахуванням регістру. Тож синтаксис нашої функції має такий вигляд: «=MaskCompare(Tekct; Maska; Reg), де «Tekct» — аналізований текст, «Maska» — шаблон, «Reg» — ознака контролю регістру символів. Головним та єдиним критерієм для пошуку даних у функції «MskCmp()» є шаблон «Maska». Він може складатися з будь-яких символів та спеціальних знаків (або так званих символів підстановки). До таких символів належать:
«*» — означає, що на місці цього знака може знаходитися довільна кількість будь-яких символів;
«?» — на місці цього знака може стояти один символ;
«#» — на місці цього символу може бути будь-яка цифра (від «0» до «9»);
«[Список_символов]» — такий запис у шаблоні замінює будь-який символ із зазначеного списку;
«[!Список_символов]» — означає, що в рядку можуть знаходитися будь-які символи, крім перелічених у списку.
Ось приклади декількох шаблонів, які можна використовувати у функції «MskCmp()»:
«###» — числа від 0 до 999;
«?????» — слова з 5 літер;
«м*н» — слова, що починаються на «м» та закінчуються на «н»;
«*[аостр]*» — усі слова, що містять хоча б одну з літер «а», «о», «с», «т», «р»;
«*[abcdefghijklmnopqrstuvwxyz]*» — усі слова, в яких є англійські літери.
Застосовуємо функцію MskCmp() для обробки бази даних
Функція для перевірки тексту в нас є. Можна використовувати її для вирішення нашого завдання. Нагадаю, що ми хочемо в базі даних на рис. 1 підрахувати підсумкові значення для контрагентів, у назві яких є слово «Фора». Виконуємо такі дії:
1. Відкриваємо документ з базою «РеестрНН.xls» (рис. 1).
2. Перед початком бази даних додаємо робочий рядок. Він буде першим на робочому листі.
3. Заповнюємо комірки робочого рядка, а саме: до «B1» уводимо текст «Фирма:». До комірки «C1» уводимо назву контрагента, для якого потрібно підрахувати підсумкові значення в базі даних. У нашому прикладі — це фірма «Фора».
4. Стаємо на комірку «J2», уводимо назву заголовка «Пр». У цій колонці ми напишемо ознаку підсумовування.
5. Стаємо на комірку «J4», клацаємо по значку «fx». Відкриється вікно «Мастер функций», зображене на рис. 5.
6. У цьому вікні клацаємо по списку «Категория:», вибираємо значення «Определенные пользователем». У списку «Выберите функцию:» з’явиться список доступних функцій користувача для поточного документа.
7. У цьому списку вибираємо варіант «MskCmp» і натискуємо на «ОК». Відкриється вікно «Аргументы функции», зображене на рис. 6.
8. У ньому заповнюємо параметри, як показано на цьому рисунку. Тут адреса «E4» — це посилання на комірку з найменуванням підприємства. Формула «"*"&$C$1&"*"» формує маску для пошуку, використовуючи назву контрагента з комірки «C1». Зараз у комірці «C1» записано значення «Фора». Тому формула «"*"&$C$1&"*"» сформує маску «*Фора*», що вибере всі підприємства, в назві яких зустрічається текст «Фора». Регістр при пошуку ми не перевіряємо, тому параметр «Reg» уводимо рівним «0».
9. У вікні «Аргументы функции» натискуємо на «ОК». У комірці «J4» з’явиться формула «=MskCmp(E4;"*"&$C$1&"*";0)», а її значення в комірці «J4» буде «истина». Усе правильно: у комірці «E4» записано назву «ТОВ "Фора"», і функція «MskCmp()» це правильно визначила.
10. Копіюємо формулу вниз по колонці «J» на всю висоту бази даних.
Усе, що нам залишається, — додати формули підсумовування з урахуванням значень у колонці «J». Зробити це можна за допомогою функції «Суммесли()». Виконуємо такі дії:
1. Стаємо на комірку «G1».
2. Уводимо формулу вибіркового підсумовування: «=Суммесли($J4:$J10000;ИСТИНА;G4:G10000)». Ця формула переглядає значення блока «$J4:$J1000» (вважаємо, що в базі буде не більше 1000 записів). Якщо в якійсь комірці блока формула знаходить значення «истина», вона бере вміст із відповідної комірки блока «G4:G10000» і додає його до загальної суми. У результаті ця формула підрахує суму значень з колонки «G», позначених у стовпці «J» як «истина». Для прикладу на рис. 7 ми в такий спосіб отримаємо підсумки щодо контрагента «Фора».
3. Копіюємо вміст «G1» до буфера обміну і вставляємо вміст із буфера до комірок «H1» та «I1». Результат нашої роботи показано на рис. 7.
Що ми отримали насправді? У комірці «C1» нашої таблиці записано приблизну назву контрагента. Ми використовували цю назву для формування маски. У колонці «Пр» записано функцію «MskCmp()», що звіряє назви підприємств із колонки «E» з маскою для пошуку. Якщо текст із комірки «C1» міститься в назві підприємства, у відповідній комірці колонки «Пр» з’явиться значення «ИСТИНА», інакше там буде «ЛОЖЬ». Тепер, маючи таку ознаку, отримання підсумків уже не викличе труднощів.
Функцію «MskCmp()» можна успішно використовувати для аналізу та перевірки правильності даних. Таке завдання дуже актуальне при роботі з великими таблицями, до яких планується застосувати інструменти обробки даних та підбиття підсумків. Ось лише кілька ситуацій, що часто зустрічаються у практичній роботі.
Приклад 1. У реєстрі, зображеному на рис. 7, потрібно перевірити правильність найменувань в колонці «E». Ми хочемо переконатися, що всі назви підприємств набрано кирилицею. Справа в тому, що візуально текст «ТОВ "Фора"» і «ТОВ "Фopа"» мають однаковий вигляд. Але у другому випадку літери «o» та «p» уведено латиницею. Тож зрозуміло, що при підбитті підсумків, сортуванні чи іншій обробці даних ці відмінності комп’ютер «побачить» і буде отримано неправильний результат обробки бази. Вирішити проблему можна так:
1. Знаходимо вільний стовпець. Нехай це буде колонка «К», що означатиме «Контроль».
2. До комірки «K2» вводимо формулу «=MaskCompare(E4;"*[A-z]*";0)» та копіюємо її на всю висоту таблиці. Тепер усе як на долоні. Усі рядки, позначені в колонці «K» значенням «истина», містять у назві підприємства латинські символи.
Приклад 2. У базі даних на рис. 2 потрібно перевірити, щоб усі коди ІПН містили тільки числа. Зробити таку перевірку для вмісту комірки «F4» допоможе формула «=MaskCompare(F4;"*[0123456789]";0)». Далі копіюємо її на всю висоту таблиці — і завдання вирішено. Усі рядки, що буде позначено значенням «ЛОЖЬ», містять помилку в коді ІПН.
Приклад 3. Зайві пробіли в назвах та інших ключових полях бази даних завжди є джерелом помилок. Щоб перевірити наявність двох пробілів у колонці «Наименование» реєстру податкових накладних, можна скористатися формулою «=MaskCompare(E4;"* *";0)». У цій формулі маска «"* *"» — це символ «*», потім ідуть два пробіли, а після них — знову символ «*». Заповнюємо цією формулою робочу колонку та дивимося на результат. Рядки, де в робочій колонці з’являться значення «истина», містять у назві підприємства зайвий пробіл.
Як бачите, використання масок та шаблонів зі спеціальними символами відкриває найширші можливості щодо обробки даних у програмі Excel. Усе залежить від фантазії та творчого підходу до вирішення проблеми.
А в мене на сьогодні все. Успішної роботи! Чекаю ваших листів, пропозицій та зауважень на bk@id.factor.ua , nictomkar@rambler.ru або на форумі редакції.
