Замість введення до цієї статті я процитую фрагмент листа нашого постійного Новомосковсктеля, де він пише: «Шановні співробітники« Б К »! У мене така проблема. Є дві таблиці Excel, обидві є базами даних. У першій таблиці зберігаються відомості про замовлення і контрагентів, які зробили ці замовлення. Друга таблиця - це довідник, де знаходяться докладні дані про кожного контрагента. Чи можна в Excel якось пов'язати ці таблиці за спільною ознакою в одне ціле? Наприклад, щоб з таблиці замовлень одним клацанням переходити в довідник контрагентів, причому відразу до того підприємства, яке зробило конкретне замовлення? Думаю, що спосіб вирішення такого завдання буде цікавий багатьом моїм колегам. По крайней мере, в моїй практиці така функція була б дуже корисна. Наперед дякую. Вадим Донець, глав. бухгалтер, г. Харьков ». Питання нам теж видався цікавим. Одному із способів вирішення даного завдання ми вирішили присвятити цю статтю.
Перше і найголовніше: зв'язування таблиць за загальними ознаками - не типова функція для MS Excel. Для таких завдань є спеціальні програми - так звані бази даних. Хоча правильніше їх називати «системи управління базами даних», або СУБД. До них відносяться, наприклад, MS Access, MS SQL Server, Oracle та багато інших. У цих системах об'єднання таблиць із загальних ключових полях - чи не найпоширеніша операція. І для її вирішення там є потужні та зручні інструменти. Вони дозволяють організувати найрізноманітніші типи зв'язків між таблицями, контролювати цілісність даних, виконувати каскадне видалення записів і т. П. Однак для повноцінного використання СУБД потрібна певна підготовка, знання мови запитів і т. П. Тому в практиці бухгалтера СУБД в чистому вигляді застосовують рідко , і всю обробку даних роблять зазвичай в програмі Excel. Що ж стосується MS Excel, то тут можливості зв'язування таблиць обмежені. В основному цей зв'язок полягає в написанні формул, які посилаються на різні листи або робочі книги. Ви можете створити друкований звіт або зведену таблицю, яка оперує кількома базами даних. Але спеціальних інструментів для повноцінної організації зв'язків між наборами даних в Excel немає, та й бути не повинно.
Однак не все так погано ... Справа в тому, що в реальній роботі всі варіанти зв'язування таблиць бухгалтеру зазвичай не потрібні. Та й без каскадного видалення записів він теж якось обійдеться. А ось зв'язати одну велику таблицю з довідником - завдання цілком актуальна і практично застосовна. І, що найголовніше (!), Така функція цілком можна реалізувати в MS Excel. Причому єдине, що для цього буде потрібно, - це пара нескладних трюків і система гіперпосилань.
Тепер подивимося, як це виглядає на практиці. Почнемо, звичайно ж, з вихідних даних.
Чим ми маємо в своєму розпорядженні
Важливо! У довіднику «Клієнти» один рядок містить дані про одному підприємстві. Повтори тут неприпустимі, кожен клієнт присутній в базі тільки один раз.
Тепер повернемося безпосередньо до самої задачі.
Що потрібно зробити
Пов'язуємо дві таблиці
Формуємо змінну з назвою листа
1. Викликаємо Excel, завантажуємо документ і переходимо в меню «Формули». Стрічка набуде вигляду, як на рис. 3.
2. У групі «Певні імена» клацаємо на іконці «Диспетчер імен». Відкриється вікно, зображене на рис. 4.
3. У цьому вікні натискаємо кнопку «Створити». Відкриється вікно «Створення імені», зображене на рис. 5.
4. У цьому вікні в полі «Ім'я:» вводимо текст «Мой_Ліст». В поле «Діапазон:» друкуємо формулу «= ПСТР (ОСЕРЕДОК (" имяфайла "; Клієнти! $ A $ 1); ПОШУК (" [ "; ОСЕРЕДОК (" имяфайла "; Клієнти! $ A $ 1)); 256) "." »(Рис. 3).
5. У вікні «Створення імені» натискаємо «ОК».
6. У вікні «Диспетчер імен» натискаємо «ОК».
Тепер розберемося, що ж ми зробили насправді? У робочій книзі з'явився новий іменований діапазон «Мой_Ліст».
З цього рядка нам потрібно взяти тільки імена файлу і листа робочої книги - буква диска і назва папки при створенні гіперпосилання не знадобляться. Для вирішення цього завдання ми скористаємося стандартними функціями Excel для роботи з текстом.
Вирізати частину рядка можна функцією «ПСТР ()». Але їй потрібно вказати: вихідний текст (у нас це повний шлях до осередку), початкову позицію і кількість знаків. яке потрібно вирізати з початкового рядка.
Початкову позицію визначити просто. Для цього за допомогою функції «ПОШУК ()» ми знаходимо перше входження квадратної відкриває дужки ( «[») в текст, де зберігається шлях до осередку. Фрагмент формули, який виконує цю операцію, виглядає так: «ПОШУК (" [ "; ОСЕРЕДОК (" имяфайла "; Клієнти! $ A $ 1))». Для рядка «D: \! Фактор [ДінСсилкіExcel_.xls] Клієнти» ця формула поверне «12». Кількість знаків, які потрібно вирізати з вихідного тексту, я вибрав максимальне - «256».
Рада Акуратно скопіюйте фрагменти формули в окремі осередки робочого аркуша, і тоді робота кожної її частини буде у вас як на долоні.
До речі, роботу формули всередині іменованого діапазону легко перевірити. Введіть в будь-яку вільну комірку аркуша вираз «= Мой_Ліст» і натисніть клавішу «Enter». В осередку повинен з'явитися результат: «[ДінСсилкіExcel_.xls] Клієнти! ».
Важливо! Зверніть увагу, що для правильної роботи формули імена аркушів не повинні містити пробілів.
1. Переходимо на лист «Замовлення», стаємо на осередок «E2».
3. Копіюємо цю формулу на всю висоту таблиці. Результат нашої роботи показаний на рис. 6.
Розглянемо коротко алгоритм роботи формули. Вираз «= ПОИСКПОЗ (C2; Клієнти! $ A: $ A; 0)» знаходить осередок в колонці «А» на аркуші «Клієнти», в якій зустрічається назва контрагента з осередку «С2» листа «Замовлення». Останній параметр функції «ПОИСКПОЗ ()» дорівнює «0». Це означає, що вона буде шукати значення за принципом точного збігу. Повернемося до нашого прикладу. Припустимо, що ми працюємо з рядком «6» таблиці «Замовлення» (рис. 6). У цьому рядку знаходиться замовлення з номером «5» від фірми «ПП" Коло "». Після копіювання гіперпосилання з осередку «E2» вниз по колонці «E» у клітинці «E6» формула з функцією пошуку вийде такий: «ПОИСКПОЗ (C6; Клієнти! $ A: $ A; 0)». А результат роботи цього виразу буде дорівнює «6». Це означає, що в таблиці «Клієнти» опис фірми «ПП« Коло »» розташоване в шостому рядку робочого листа.
2. Скопіюйте оновлену формулу на всю висоту таблиці.
4. Через меню «Головна» змініть шрифт для виділених осередків, вказавши гарнітуру «Wingdings 3». Тепер все гіперпосилання Excel покаже у вигляді значка «».
Обраний символ - не єдиний спосіб зображення гіперпосилань. Кілька варіантів оформлення з використанням гарнітури «Wingdings 3» наведені в табліце.Думаю, що їх більш ніж достатньо для практичної роботи бухгалтера.
Символи для оформлення гіперпосилань (гарнітура «Wingdings 3»)
На цьому все. Сподіваюся, що матеріал цієї статті допоможе вам створювати зручні бази даних, об'єднуючи в них кілька пов'язаних таблиць в форматі MS Excel.
Поділитися з колегами
Помітілі помилку? Віділіть ее та натісніть Ctrl + Enter, щоб повідоміті нас про це
Підпішіться на розсилку
и безкоштовно отримайте на пошту електронні Версії свіжіх статей, новини и Акції
Обговорюємо на форумі
Головний офіс:
м. Харків, вул. Сумська, 106-а
тел. / факс: (057) 76-500-76
Приєднуйтесь до нас
Вініклі питання?
Використання будь-якіх матеріалів, розміщеніх на сайті, дозволяється за умови посилання на агентство портал buhgalter.com.ua.
При копіюванні матеріалів з розділу «Статті», для інтернет-видань - обов'язкове Пряме Відкрите для пошукових систем гіперпосілання. ПОСИЛАННЯ має буті розміщене в незалежності від полного або часткового использование матеріалів. Гіперпосілання (для інтернет-видань) - повинність буті розміщене в підзаголовку або в Першому абзаці матеріалу.