Робота зі зв'язаними таблицями в Microsoft Excel

При виконанні певних завдань в Excel іноді доводиться мати справу з декількома таблицями, які до того ж пов'язані між собою. Тобто, дані з однієї таблиці підтягуються в інші і при їх зміні перераховуються значення у всіх пов'язаних табличних діапазонах.

Пов'язані таблиці дуже зручно використовувати для обробки великого обсягу інформації. Розташовувати всю інформацію в одній таблиці, до того ж, якщо вона не однорідна, не дуже зручно. З подібними об'єктами важко працювати і виробляти по ним пошук. Зазначену проблему якраз покликані усунути пов'язані таблиці, інформація між якими розподілена, але в той же час є взаємопов'язаної. Пов'язані табличні діапазони можуть перебувати не тільки в межах одного аркуша або однієї книги, але і розташовуватися в окремих книгах (файлах). Останні два варіанти на практиці використовують найчастіше, тому що метою зазначеної технології є як раз піти від скупчення даних, а нагромадження їх на одній сторінці принципово проблему не вирішує. Давайте дізнаємося, як створювати і як працювати з таким видом управління даними.

Створення пов'язаних таблиць

Перш за все, давайте зупинимося на питанні, якими способами існує можливість створити зв'язок між різними табличними діапазонами.

Спосіб 1: пряме зв'язування таблиць формулою

Найпростіший спосіб зв'язування даних - це використання формул, в яких є посилання на інші табличні діапазони. Він називається прямим зв'язуванням. Цей спосіб інтуїтивно зрозумілий, так як при ньому зв'язування виконується практично точно так же, як створення посилань на дані в одному табличному масиві.

Подивимося, як на прикладі можна утворити зв'язок шляхом прямого зв'язування. Маємо дві таблиці на двох аркушах. На одній таблиці проводиться розрахунок заробітної плати за допомогою формули шляхом множення ставки працівників на єдиний для всіх коефіцієнт.

На другому аркуші розташований табличний діапазон, в якому знаходиться перелік співробітників з їх окладами. Список співробітників в обох випадках представлений в одному порядку.

Потрібно зробити так, щоб дані про ставки з другого листа підтягувалися до відповідних осередку першого.

1. На першому аркуші виділяємо перший осередок шпальти «Ставка». Ставимо в ній знак «=». Далі натискаємо по ярличку «Лист 2», який розміщується в лівій частині інтерфейсу Excel над рядком стану.
2. Відбувається переміщення в другу область документа. Клацаємо по першій клітинці в стовпці «Ставка». Потім натискаємо на кнопку Enter на клавіатурі, щоб зробити введення даних в осередок, в якій раніше встановили знак «дорівнює».
3. Потім відбувається автоматичний перехід на перший лист. Як бачимо, в відповідному полі підтягується величина ставки першого співробітника з другої таблиці. Встановивши курсор на клітинку, яка містить ставку, бачимо, що для виведення даних на екран застосовується звичайна формула. Але перед координатами осередки, звідки виводяться дані, варто вираз «Аркуш2!», Яке вказує найменування області документа, де вони розташовані. Загальна формула в нашому випадку виглядає так:

   =Лист2!B2

4. Тепер потрібно перенести дані про ставки всіх інших працівників підприємства. Звичайно, це можна зробити тим же шляхом, яким ми виконали поставлене завдання для першого працівника, але з огляду на, що обидва списки співробітників розташовані в однаковому порядку, завдання можна істотно спростити і прискорити її рішення. Це можна зробити, просто скопіювавши формулу на діапазон нижче. Завдяки тому, що посилання в Excel за замовчуванням є відносними, при їх копіюванні відбувається зрушення значень, що нам і потрібно. Саму процедуру копіювання можна зробити за допомогою маркера заповнення.

   Отже, ставимо курсор в нижню праву область елемента з формулою. Після цього курсор повинен перетворитися в маркер заповнення у вигляді чорного хрестика. Виконуємо затиск лівої кнопки миші і тягнемо курсор до самого низу стовпця.

5. Всі дані з аналогічного стовпчика на Листі 2 були підтягнуті в таблицю на Листі 1. При зміні даних на Листі 2 вони автоматично будуть змінюватися і на першому.

Спосіб 2: використання зв'язки операторів ІНДЕКС - ПОИСКПОЗ

Але що робити, якщо перелік співробітників в табличних масивах розташований не в однаковому порядку? У цьому випадку, як говорилося раніше, одним з варіантів є установка зв'язку між кожною з тих осередків, які слід пов'язати, вручну. Але це підійде хіба що для невеликих таблиць. Для масивних діапазонів подібний варіант в кращому випадку відніме дуже багато часу на реалізацію, а в гіршому - на практиці взагалі буде неможливий. Але вирішити цю проблему можна за допомогою зв'язки операторів ІНДЕКС - ПОИСКПОЗ. Подивимося, як це можна здійснити, зв'язавши дані в табличних діапазонах, про які йшла мова в попередньому способі.

1. Виділяємо перший елемент стовпця «Ставка». Переходимо в Майстер функцій, клікнувши по піктограмі «Вставити функцію».
2. У Майстрі функцій в групі «Посилання та масиви» знаходимо і виділяємо найменування «ІНДЕКС».
3. Даний оператор має дві форми: форму для роботи з масивами і кількість посилань. У нашому випадку потрібно перший варіант, тому в наступному віконці вибору форми, яке відкриється, вибираємо саме його і тиснемо на кнопку «OK».
4. Виконано запуск віконця аргументів оператора ІНДЕКС. Завдання зазначеної функції - висновок значення, що знаходиться в обраному діапазоні в рядку з зазначеним номером. Загальна формула оператора ІНДЕКС така:

   =ИНДЕКС(массив;номер_строки;[номер_столбца])

   «Масив» - аргумент, що містить адресу діапазону, з якого ми будемо отримувати інформацію за номером зазначеного рядка.

   «Номер рядка» - аргумент, який є номером цієї самої рядки. При цьому важливо знати, що номер рядка слід вказувати не щодо всього документа, а тільки щодо виділеного масиву.

   «Номер стовпця» - аргумент, що носить необов'язковий характер. Для вирішення конкретно нашої задачі ми його використовувати не будемо, а тому описувати його суть окремо не потрібно.

   Ставимо курсор в поле «Масив». Після цього переходимо на Лист 2 і, затиснувши ліву кнопку миші, виділяємо весь вміст стовпчика «Ставка».

5. Після того, як координати відобразилися в віконці оператора, ставимо курсор в полі «Номер рядка». Даний аргумент ми будемо виводити за допомогою оператора ПОИСКПОЗ. Тому натискаємо по трикутнику, який розташований зліва від рядка функцій. Відкривається перелік недавно використаних операторів. Якщо ви серед них знайдете найменування «ПОИСКПОЗ», то можете клікати по ньому. У зворотному випадку клікайте по самому останньому пункту переліку - «Інші функції ...».
6. Запускається стандартне вікно Майстра функцій. Переходимо в ньому в ту ж саму групу «Посилання та масиви». На цей раз в переліку вибираємо пункт «ПОИСКПОЗ». Виконуємо клацання по кнопці «OK».
7. Проводиться активація віконця аргументів оператора ПОИСКПОЗ. Зазначена функція призначена для того, щоб виводити номер значення в певному масиві по його найменуванню. Саме завдяки цій можливості ми обчислимо номер рядка певного значення для функції ІНДЕКС. Синтаксис ПОИСКПОЗ представлений так:

   =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

   «Шукане значення» - аргумент, який містить найменування або адреса комірки стороннього діапазону, в якій воно знаходиться. Саме позицію даного найменування в цільовому діапазоні і слід обчислити. У нашому випадку в ролі першого аргументу виступатимуть посилання на осередки на Листі 1, в яких розташовані імена співробітників.

   «Проглядається масив» - аргумент, який представляє собою посилання на масив, в якому виконується пошук вказаного значення для визначення його позиції. У нас цю роль виконуватиме адреса стовпця «Ім'я» на Листі 2.

   «Тип зіставлення» - аргумент, який є необов'язковим, але, на відміну від попереднього оператора, цей необов'язковий аргумент нам буде потрібен. Він вказує на те, як буде зіставляти оператор шукане значення з масивом. Цей аргумент може мати одне з трьох значень: -1; 0; 1. Для невпорядкованих масивів слід вибрати варіант «0». Саме цей варіант підійде для нашого випадку.

   Отже, приступимо до заповнення полів вікна аргументів. Ставимо курсор в поле «Шукане значення», натискаємо по першій клітинці стовпця «Ім'я» на Листі 1.

8. Після того, як координати відобразилися, встановлюємо курсор в поле «проглядається масив» і переходимо по ярлику «Лист 2», який розміщений внизу вікна Excel над рядком стану. Затискаємо ліву кнопку миші і виділяємо курсором все комірок стовпчика «Ім'я».
9. Після того, як їх координати відобразилися в поле «проглядається масив», переходимо до поля «Тип зіставлення» і з клавіатури встановлюємо там число «0». Після цього знову повертаємося до поля «проглядається масив». Справа в тому, що ми будемо виконувати копіювання формули, як ми це робили в попередньому способі. Відбуватиметься зміщення адрес, але ось координати проглядається масиву нам потрібно закріпити. Він не повинен зміщуватися. Виділяємо координати курсором і тиснемо на функціональну клавішу F4. Як бачимо, перед координатами з'явився знак долара, що означає те, що посилання з відносною перетворилася в абсолютну. Потім тиснемо на кнопку «OK».
10. Результат виведений на екран в перший осередок шпальти «Ставка». Але перед тим, як проводити копіювання, нам потрібно закріпити ще одну область, а саме перший аргумент функції ІНДЕКС. Для цього виділяємо елемент колонки, який містить формулу, і рухаємось в рядок формул. Виділяємо перший аргумент оператора ІНДЕКС (B2: B7) і клацаємо по кнопці F4. Як бачимо, знак долара з'явився близько обраних координат. Клацаємо по клавіші Enter. В цілому формула прийняла такий вигляд:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

11. Тепер можна зробити копіювання за допомогою маркера заповнення. Викликаємо його тим же способом, про який ми говорили раніше, і простягаємо до кінця табличного діапазону.
12. Як бачимо, незважаючи на те, що порядок рядків у двох пов'язаних таблиць не збігається, проте, все значення підтягуються відповідно прізвищами працівників. Цього вдалося досягти завдяки застосуванню поєднання операторів ІНДЕКС - ПОИСКПОЗ.

Читайте також:
Функція ІНДЕКС в Ексель
Функція ПОИСКПОЗ в Ексель

Спосіб 3: виконання математичних операцій з пов'язаними даними

Пряме зв'язування даних добре ще тим, що дозволяє не тільки виводити в одну з таблиць значення, які відображаються в інших табличних діапазонах, але і проводити з ними різні математичні операції (складання, розподіл, віднімання, множення і т.д.).

Подивимося, як це здійснюється на практиці. Зробимо так, що на Листі 3 виводитимуться загальні дані заробітної плати по підприємству без розбивки по співробітниках. Для цього ставки співробітників будуть підтягуватися з Ліста 2, підсумовуватися (за допомогою функції СУММ) і множитися на коефіцієнт за допомогою формули.

1. Виділяємо осередок, де буде виводитися підсумок розрахунку заробітної плати на Листі 3. Виробляємо клік по кнопці «Вставити функцію».
2. Слід запуск вікна Майстра функцій. Переходимо до групи «Математичні» і вибираємо там найменування «СУМ». Далі тиснемо на кнопку «OK».
3. Проводиться переміщення в вікно аргументів функції СУММ, яка призначена для розрахунку суми обраних чисел. Вона має нижче вказаному синтаксис:

   =СУММ(число1;число2;…)

   Поля в вікні відповідають аргументам зазначеної функції. Хоча їх число може досягати 255 штук, але для нашої мети досить буде всього одного. Ставимо курсор в поле «Число1». Кількома по ярлику «Лист 2» над рядком стану.

4. Після того, як ми перемістилися в потрібний розділ книги, виділяємо стовпець, який слід підсумувати. Робимо це курсором, затиснувши ліву кнопку миші. Як бачимо, координати виділеної області тут же відображаються в поле вікна аргументів. Потім клацаємо по кнопці «OK».
5. Після цього ми автоматично переміщаємося на Лист 1. Як бачимо, загальна сума розміру ставок працівників вже відображається у відповідному елементі.
6. Але це ще не все. Як ми пам'ятаємо, зарплата обчислюється шляхом множення величини ставки на коефіцієнт. Тому знову виділяємо клітинку, в якій знаходиться підсумовувана величина. Після цього переходимо до рядка формул. Дописуємо до наявної в ній формулі знак множення (*), а потім клацаємо по елементу, в якому розташовується показник коефіцієнта. Для виконання обчислення клацаємо по клавіші Enter на клавіатурі. Як бачимо, програма розрахувала загальну заробітну плату по підприємству.
7. Повертаємося на Лист 2 і змінюємо розмір ставки будь-якого працівника.
8. Після цього знову переміщаємося на сторінку із загальною сумою. Як бачимо, через зміни в пов'язаної таблиці результат загальної заробітної плати був автоматично перерахований.

Спосіб 4: спеціальна вставка

Зв'язати табличні масиви в Excel можна також за допомогою спеціальної вставки.

1. Виділяємо значення, які потрібно буде «затягнути» в іншу таблицю. У нашому випадку це діапазон стовпця «Ставка» на Листі 2. Кількома по виділеному фрагменту правою кнопкою миші. У списку вибираємо пункт «Копіювати». Альтернативною комбінацією є поєднання клавіш Ctrl + C. Після цього переміщаємося на Лист 1.
2. Перемістившись в потрібну нам область книги, виділяємо комірки, в які потрібно буде підтягувати значення. У нашому випадку це стовпець «Ставка». Клацаємо по виділеному фрагменту правою кнопкою миші. У контекстному меню в блоці інструментів «Параметри вставки» клацаємо по піктограмі «Вставити зв'язок».

   Існує також альтернативний варіант. Він, до речі, є єдиним для старіших версій Excel. У контекстному меню наводимо курсор на пункт «Спеціальна вставка». У відкритому додатковому меню вибираємо позицію з однойменною назвою.

3. Після цього відкривається вікно спеціальної вставки. Тиснемо на кнопку «Вставити зв'язок» в нижньому лівому кутку комірки.
4. Який би варіант ви не вибрали, значення з одного табличного масиву будуть вставлені в інший. При зміні даних в исходнике вони також автоматично будуть змінюватися і під вставленому діапазоні.

урок: Спеціальна вставка в Ексель

Спосіб 5: зв'язок між таблицями в декількох книгах

Крім того, можна організувати зв'язок між табличними областями в різних книгах. При цьому використовується інструмент спеціальної вставки. Дії будуть абсолютно аналогічними тим, які ми розглядали в попередньому способі, за винятком того, що виробляти навігацію під час внесення формул доведеться не між областями однієї книги, а між файлами. Природно, що всі пов'язані книги при цьому повинні бути відкриті.

1. Виділяємо діапазон даних, який потрібно перенести в іншу книгу. Клацаємо по ньому правою кнопкою миші і вибираємо в меню позицію «Копіювати».
2. Потім переміщаємося до тієї книги, в яку ці дані потрібно буде вставити. Виділяємо потрібний діапазон. Натискаємо правою кнопкою миші. У контекстному меню в групі «Параметри вставки» вибираємо пункт «Вставити зв'язок».
3. Після цього значення будуть вставлені. При зміні даних у вихідній книзі табличний масив з робочою книги буде їх підтягувати автоматично. Причому зовсім не обов'язково, щоб для цього були відкриті обидві книги. Досить відкрити одну тільки робочу книгу, і вона автоматично підтягне дані з закритого пов'язаного документа, якщо в ньому раніше були проведені зміни.

Але потрібно відзначити, що в цьому випадку вставка буде проведена у вигляді незмінного масиву. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.

Разрыв связи между таблицами

Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.

Способ 1: разрыв связи между книгами

Разорвать связь между книгами во всех ячейках можно, выполнив фактически одну операцию. При этом данные в ячейках останутся, но они уже будут представлять собой статические не обновляемые значения, которые никак не зависят от других документов.

1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные» . Щелкаем по значку «Изменить связи» , который расположен на ленте в блоке инструментов «Подключения» . Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
2. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь» .
3. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи» .
4. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.

1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать» . Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C .
2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения» , которая размещена в группе инструментов «Параметры вставки» .
3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

Как видим, в Excel имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.

• ← Методи порівняння таблиць в Microsoft Excel
• Зведення числа в квадрат в Microsoft Excel →