Excel 2010: зв’язуємо бази даних
 | Замість вступу до цієї статті я процитую фрагмент листа нашого постійного читача, де він пише: «Шановні працівники редакції «Б & К»! У мене така проблема. Є дві таблиці Excel, обидві з яких — бази даних. У першій таблиці зберігаються відомості про замовлення та контрагентів, які зробили ці замовлення. Друга таблиця — це довідник, де знаходяться докладні дані про кожного контрагента. Чи можна в Excel якось зв’язати ці таблиці за загальною ознакою в одне ціле? Наприклад, щоб із таблиці замовлень одним клацанням переходити до довідника контрагентів, причому відразу до того підприємства, яке зробило конкретне замовлення? Думаю, що спосіб розв’язання такого завдання зацікавить багатьох моїх колег. Принаймні у моїй практиці така функція була б дуже корисна. Наперед завдячую. Вадим Донець, головний бухгалтер, м. Харків». Запитання нам теж здалося цікавим, тож одному зі способів його розв’язання присвятимо цю статтю. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Перше й найголовніше: зв’язування таблиць за загальними ознаками — не типова функція для MS Excel. Для таких завдань є спеціальні програми — так звані бази даних. Хоча правильніше називати їх «системи управління базами даних» або СУБД. До них належать, наприклад, MS Access, MS SQL Server, Oracle та багато інших. У цих системах об’єднання таблиць за загальними ключовими полями — чи не найпоширеніша операція. І для її вирішення там є потужні та зручні інструменти. Вони дозволяють організувати найрізноманітніші типи зв’язків між таблицями, контролювати цілісність даних, виконувати каскадне видалення записів тощо. Однак для повноцінного використання СУБД потрібні певна підготовка, знання мови запитів тощо. Тому у практиці бухгалтера СУБД у чистому вигляді застосовують нечасто і всю обробку даних зазвичай виконують у програмі Excel. Що ж стосується MS Excel, то тут можливості зв’язування таблиць обмежені. Цей зв’язок переважно полягає в написанні формул, що посилаються на різні листи або робочі книги. Ви можете створити друкований звіт або зведену таблицю, що оперує декількома базами даних. Але спеціальних інструментів для повноцінної організації зв’язків між наборами даних в Excel немає, та й бути не повинно.
Однак не все так погано. Справа в тому, що в реальній роботі всі варіанти зв’язування таблиць бухгалтеру зазвичай не потрібні. Та й без каскадного видалення записів він теж якось обійдеться. А от зв’язати одну велику таблицю з довідником — завдання цілком актуальне та практично застосовне. І, що найголовніше(!), така функція цілком реалізується в MS Excel. Причому єдине, що для цього буде потрібно, — це кілька нескладних трюків та система гіперпосилань.
Тепер подивимося, який вигляд це має практично. Почнемо, звичайно ж, із вихідних даних.
Що ми маємо у своєму розпорядженні
Для прикладу я скористаюся двома таблицями, форму яких показано на рис. 1 і 2. Перша таблиця (розташована на листі «Заказ») складається із чотирьох полів: «Заказ», «Дата», «Клиент», «Сумма» і поки що пустого поля «Коммент.». Це спрощений варіант бази даних про замовлення. У полі «Коммент.» ми зрештою й поставимо посилання на елемент довідника. Зрозуміло, що в базі «Заказы» один контрагент може фігурувати багато разів — це залежить від кількості замовлень, зроблених цим клієнтом.
Друга таблиця називається «Клиенты». У ній я залишив тільки п’ять колонок: «Название» (найменування підприємства), далі йдуть прізвище директора, юридична адреса, телефон та e-mail.
Важливо! У довіднику «Клиенты» один рядок містить дані про одне підприємство. Повторення тут недопустимі, кожен клієнт наявний у базі лише один раз.
Тепер повернемося безпосередньо до завдання.
Що потрібно зробити
Отже, ми збираємося організувати зв’язок між таблицями «Заказы» та «Клиенты». Для цього в полі «Коммент.» таблиці «Заказы» ми поставимо гіперпосилання, що вказуватиме на певний рядок таблиці «Клиенты». Алгоритм роботи гіперпосилання буде таким. Спочатку вона має проаналізувати вміст поля «НаимПредпр» у поточному рядку таблиці «Заказы» — інакше кажучи, запам’ятати назву підприємства, яке оформило конкретне замовлення. Потім посилання має виконати перехід на лист «Клиенты», на цьому листі — знайти рядок, що описує параметри підприємства, яке зробило замовлення. І нарешті — установити покажчик поточної комірки на відповідний рядок у довіднику «Клиенты». Такий вигляд це має в загальних рисах.
До речі, у термінології баз даних такий зв’язок називається «один ко многим» (якщо дивитися з боку таблиці «Клиенты»), тобто один елемент довідника про клієнтів посилається на декілька записів у базі замовлень. У MS Excel для створення такого зв’язку спеціальних інструментів немає. Ми мусимо побудувати їх самостійно. Для цього, виходячи з алгоритму, нам знадобляться: вбудована функція для створення гіперпосилання (така функція називається «ГИПЕРССЫЛКА()») та функція для пошуку даних у таблиці. Я пропоную для пошуку використовувати функцію «ПОИСКПОЗ()». Але це не все. Гіперпосиланню потрібно вказати точну адресу для переходу: ім’я файлу, назву робочого листа і комірку, яку потрібно зробити активною. Для отримання імені файлу, листа й комірки ми скористаємося функцією «ЯЧЕЙКА()». Крім того, для формування рядка з адресою переходу нам доведеться звернутися до вбудованих функцій роботи з текстом — «ПСТР()» та «ПОИСК()». Ось, власне, і всі основні інструменти. Можна переходити до практичної реалізації.
Зв’язуємо дві таблиці
Спочатку виконаємо кілька попередніх дій. Почнемо з функції «ГИПЕРССЫЛКА()», що буде ключовою при організації зв’язків між таблицями. У неї два параметри. Перший — це адреса, тобто ім’я файлу та робочого листа, на який указує посилання. Другий параметр — адреса конкретної комірки на цьому листі, куди вказуватиме гіперпосилання. У нас гіперпосилань буде багато. І кожного разу для їх створення доведеться зазначати в параметрах функції ім’я файлу та назву листа. Це довго і незручно. Тому краще один раз створити змінну з назвою листа і файлу, а потім зазначати цю змінну у функції «ГИПЕРССЫЛКА()» за необхідності. Стосовно MS Excel як таку змінну зручно використовувати іменований діапазон комірок. Із цього ми й почнемо.
ФОРМУЄМО ЗМІННУ З НАЗВОЮ ЛИСТА
Отже, нам потрібно створити змінну, щоб зберегти в ній ім’я файлу та назву робочого листа для майбутніх гіперпосилань. Ми вирішили, що такою змінною буде іменований діапазон. Щоб створити його в MS Excel 2010, виконуємо такі дії:
1. Викликаємо Excel, завантажуємо документ і переходимо до меню «Формулы». Стрічка набуде вигляду, як показано на рис. 3.
2. У групі «Определенные имена» клацаємо по іконці «Диспетчер имен». Відкриється вікно, зображене на рис. 4.
3. У цьому вікні натискуємо на кнопку «Создать». Відкриється вікно «Создание имени», зображене на рис. 5.
4. У цьому вікні в полі «Имя:» вводимо текст «Мой_Лист». У полі «Диапазон:» друкуємо формулу «=ПСТР(ЯЧЕЙКА("имяфайла";Клиенты!$A$1);ПОИСК("[";ЯЧЕЙКА("имяфайла";Клиенты!$A$1));256)&"!"» (рис. 3).
5. У вікні «Создание имени» натискуємо на «ОК».
6. У вікні «Диспетчер имен» натискуємо на «ОК».
Тепер розберемося, що ж ми зробили насправді? У робочій книзі з’явився новий іменований діапазон «Мой_Лист».
Важливо! У нашому випадку назва іменованого діапазону має бути БЕЗ пробілів. Інакше формули адресації працюватимуть неправильно.
Як і будь-який іменований діапазон, він указує на комірку чи групу комірок робочої книги Excel. Але в нашому випадку адреса цієї групи не постійна, а динамічна. Її формує формула, яку ми ввели до поля «Диапазон:». Стисло розповімо про роботу цієї формули. Почнемо зсередини — з функції «ЯЧЕЙКА()».
Вираз «ЯЧЕЙКА("имяфайла";Клиенты!$A$1)» звертається до комірки «A1» на листі «Клиенты» та повертає для цієї комірки її повну адресу, тобто шлях до файлу, ім’я файлу та назву листа, де цю комірку розташовано. Наприклад, документ із таблицями в мене називається «ДинСсылкиExcel_.xls». Він зберігається на диску «D:» у папці «!Фактор». Тоді результат роботи формули буде таким: «D:\!Фактор\[ДинСсылкиExcel_.xls]Клиенты».
Із цього рядка нам потрібно взяти лише імена файлу та листа робочої книги — літера диска та назва папки при створенні гіперпосилання не знадобляться. Для вирішення цього завдання ми скористаємося стандартними функціями Excel для роботи з текстом.
Вирізати частину рядка можна функцією «ПСТР()». Але їй потрібно вказати такі дані: вихідний текст (у нас це повний шлях до комірки), початкову позицію та кількість знаків, яку потрібно вирізати з вихідного рядка.
Початкову позицію визначити просто. Для цього за допомогою функції «ПОИСК()» ми знаходимо перше входження квадратної відкриваючої дужки («[») до тексту, де зберігається шлях до комірки. Фрагмент формули, що виконує цю операцію, має такий вигляд: «ПОИСК("[";ЯЧЕЙКА("имяфайла"; Клиенты!$A$1))». Для рядка «D:\!Фактор [ДинСсылкиExcel_.xls]Клиенты» ця формула поверне «12». Я вибрав максимальну кількість знаків, яку потрібно вирізати з вихідного тексту, — «256».
Залишається підставити отримані значення до функції «ПСТР()». Першим її параметром буде текст з адресою до комірки. Другий параметр — номер початкової позиції для вирізання частини рядка. Останній параметр — максимально допустима кількість символів у рядку. Формула для цієї операції буде такою: «=ПСТР(ЯЧЕЙКА ("имяфайла";Клиенты!$A$1);ПОИСК("[";ЯЧЕЙ- КА("имяфайла"; Клиенты!$A$1));256)». У нашому прикладі така формула поверне результат у вигляді «[ДинСсылкиExcel_.xls]Клиенты». По суті, це посилання на лист «Клиенты» робочої книги «ДинСсылкиExcel_.xls». Далі може йти адреса комірки. Але поки що не вистачає одного елемента — між ім’ям листа і адресою комірки має стояти знак оклику («!»). Цей символ ми можемо приєднати до формули за допомогою операції «&», і в остаточній редакції вираз матиме такий вигляд: «=ПСТР(ЯЧЕЙКА("имяфайла";Клиенты!$A$1);ПОИСК("[";ЯЧЕЙКА("имяфайла"; Клиенты!$A$1)); 256)&"!"». А результат її роботи буде таким: «[ДинСсылкиExcel_.xls]Клиенты!».
Порада Акуратно скопіюйте фрагменти формули до окремих комірок робочого листа. І тоді робота кожної її частини буде у вас як на долоні.
До речі, роботу формули всередині іменованого діапазону легко перевірити. Уведіть до будь-якої вільної комірки листа вираз «=Мой_Лист» і натисніть на клавішу «Enter». У комірці має з’явитися результат: «[ДинСсылкиExcel_.xls]Клиенты!».
Важливо! Зверніть увагу, що для правильної роботи формули імена листів не повинні містити пробілів.
ФОРМУЄМО ГІПЕРПОСИЛАННЯ
Посилання на лист «Клиенты» ми розставимо в колонці «Коммент.» таблиці «Заказы». Для цього виконуємо такі дії.
1. Переходимо на лист «Заказы», стаємо на комірку «E2».
2. Уводимо формулу «=ГИПЕРССЫЛКА(Мой_ Лист&АДРЕС(ПОИСКПОЗ(C2;Клиенты!$A:$A;0);1);">")».
3. Копіюємо цю формулу на всю висоту таблиці. Результат нашої роботи показано на рис. 6.
Перевіряємо, що в нас вийшло. У таблиці «Заказы» клацаємо лівою кнопкою, наприклад, по комірці «E6». У цьому рядку розташовано відомості про замовлення з номером «5» від фірми «ЧП "Коло"». Після клацання Excel перемкнеться на лист «Клиенты», а покажчик активної комірки стане на адресу «A6». Саме в цій позиції довідника записано інформацію про «ЧП "Коло"» (рис. 7).
Розглянемо стисло алгоритм роботи формули. Вираз «=ПОИСКПОЗ(C2;Клиенты!$A:$A;0)» знаходить комірку в колонці «А» на листі «Клиенты», у якій зустрічається назва контрагента з комірки «С2» листа «Заказы». Останній параметр функції «ПОИСКПОЗ()» дорівнює «0». Це означає, що вона шукатиме значення за принципом точного збігу. Повернемося до нашого прикладу. Припустимо, що ми працюємо з рядком «6» таблиці «Заказы» (рис. 6). У цьому рядку знаходиться замовлення з номером «5» від фірми «ЧП "Коло"». Після копіювання гіперпосилання з комірки «E2» вниз по колонці «E» в комірці «E6» формула з функцією пошуку вийде такою: «ПОИСКПОЗ(C6;Клиенты!$A:$A;0)». А результат роботи цього виразу дорівнюватиме «6». Це означає, що в таблиці «Клиенты» опис фірми «ЧП "Коло"» розташовано в шостому рядку робочого листа.
Ідемо далі. Результат роботи цього виразу ми підставимо як перший параметр функції адреси. Другим параметром укажемо «1». Тоді вираз «АДРЕС(ПОИСКПОЗ (C2;Клиенты!$A:$A;0);1)» (другий рядок таблиці «Заказы»), по суті, означає «АДРЕС(2;1)». Ця функція поверне у вигляді тексту адресу комірки, що знаходиться у другому рядку та першій колонці робочого листа. Для замовлення «1» (другий рядок) це буде адреса «$A$2». Для замовлення в шостому рядку таблиці (фірма «ЧП "Коло"») вираз поверне рядок «$A$6» тощо.
З адресацією комірки зрозуміло. Тепер потрібно використати отриманий результат у функції «ГИПЕРССЫЛКА()».
Тут ситуація така. Нагадаю, що у функції «ГИПЕРССЫЛКА()» два параметри. Перший — це адреса, куди вказує гіперпосилання. Вона включає назву файлу, листа та адресу комірки для переходу. Імена файлу і листа у нас уже є, ця інформація зберігається у змінній «Мой_Лист». Адресу комірки для посилання на контрагента за конкретним замовленням ми отримали. Залишається об’єднати ці дві частини операцією «&». І тоді остаточний вираз для адреси переходу буде таким: «Мой_Лист&АДРЕС (ПОИСКПОЗ(C2;Клиенты!$A:$A;0);1)».
Подивимося, що в нас вийшло. Для комірки «С2» така формула поверне результат «[ДинСсылкиExcel_.xls]Клиенты!$A$2», тобто посилання на комірку «A2» в довіднику «Клиенты». Усе правильно — для замовлення з номером «1» посилання вказує на контрагента «ТОВ "Смит"». Якщо звернутися до комірки «С6» (замовлення з номером «5» від «ЧП "Коло"»), то в цьому рядку таблиці «Заказы» вираз для адреси переходу матиме такий вигляд: «Мой_Лист&АДРЕС (ПОИСКПОЗ(C6;Клиенты!$A:$A;0);1)». А результат формули буде таким: «[ДинСсылкиExcel_.xls]Клиенты!$A$6».
Переходимо до другого параметра функції «ГИПЕРССЫЛКА()». Тут має знаходитися текст, який Excel покаже на місці гіперпосилання. Інакше кажучи, це назва самого гіперпосилання. Я вибрав як таку назву символ «>» (знак «больше»). І тоді остаточна формула для створення гіперпосилання буде такою: «=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(C2; Клиенты!$A:$A; 0);1);">")».
Ось, власне, і все щодо створення зв’язків між таблицями Excel. Із завданням ми впоралися, усі посилання працюють правильно. Єдине, що хотілося б додати, — кілька слів щодо оформлення таблиці. Зараз у колонці «Коммент.» на місці гіперпосилань (лист «Заказы») Excel відображає символ «>». Цілком можливо, ви захочете його змінити. У цьому випадку я б порадив скористатися вбудованою функцією «СИМВОЛ()». Параметром цієї функції є код символу, тобто число в інтервалі від «1» до «255». А результат її роботи — це символ із зазначеним кодом з таблиці Windows. Таким чином, за допомогою функції «СИМВОЛ()» ви можете вставити до формули або на робочий лист Excel будь-який знак, навіть якщо його немає на клавіатурі комп’ютера. Для гіперпосилань такими знаками можуть бути зображення стрілок, трикутних маркерів тощо. До речі, найбільше таких символів є у спеціальних шрифтах, наприклад у стандартній гарнітурі «Wingdings 3». Щоб задіяти будь-який символ із цієї гарнітури для відображення гіперпосилання, виконайте такі дії:
1. Відкоригуйте формулу, замінивши текст «">")» зверненням до функції «СИМВОЛ()». Як код символу зазначте, наприклад, «117». І тоді в кінцевому підсумку у вас має вийти формула: «=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ (C2;Клиенты!$A:$A;0);1); СИМВОЛ(117))».
2. Скопіюйте оновлену формулу на всю висоту таблиці.
3. Виділіть усі комірки в колонці «Коммент.», крім її заголовка.
4. Через меню «Главная» змініть шрифт для виділених комірок, указавши гарнітуру «Wingdings 3». Тепер усі гіперпосилання Excel покаже у вигляді значка «
».
Вибраний символ — не єдиний спосіб зображення гіперпосилань. Кілька варіантів оформлення з використанням гарнітури «Wingdings 3» наведено в таблиці. Вважаю, що їх більш ніж досить для практичної роботи бухгалтера.
Символи для оформлення гіперпосилань (гарнітура «Wingdings 3»)
| Код символу | Функція Excel | Результат |
| 1 | 2 | 3 |
| 101 | СИМВОЛ(101) | |
| 103 | СИМВОЛ(103) | |
| 117 | СИМВОЛ(117) | |
| 125 | СИМВОЛ(125) | |
| 132 | СИМВОЛ(132) | |
| 134 | СИМВОЛ(134) | |
| 138 | СИМВОЛ(138) | |
| 142 | СИМВОЛ(142) | |
| 042 | СИМВОЛ(042) | |
| 052 | СИМВОЛ(052) | |
| 065 | СИМВОЛ(065) | |
| 073 | СИМВОЛ(073) | |
| 091 | СИМВОЛ(091) | |
| 093 | СИМВОЛ(093) | |
| 095 | СИМВОЛ(095) | |
| 099 | СИМВОЛ(099) | |
?
І останнє. За допомогою гіперпосилань можна організувати зв’язки між таблицями, які розташовано в різних робочих книгах. Жодних обмежень тут немає. Усе, що для цього буде потрібно, — відкоригувати формулу, яка формує адресу переходу для гіперпосилання. У принципі ви можете зробити це самостійно або скачати готовий документ на форумі редакції «Б & K».
На цьому все. Сподіваюся, що матеріал цієї статті допоможе вам створювати зручні бази даних, об’єднуючи в них декілька зв’язаних таблиць у форматі MS Excel.
Успішної роботи! Чекаю ваших запитань, зауважень та пропозицій на bk@id.factor.ua , nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum .
