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

Зміст

• Створення пов'язаних таблиць
  • Спосіб 1: пряме зв'язування таблиць формулою
  • Спосіб 2: Використання зв'язки операторів Індекс & 8212; ПОШУКПОЗ
  • Спосіб 3: виконання математичних операцій із пов'язаними даними
  • Спосіб 4: Спеціальна вставка
  • Спосіб 5: зв'язок між таблицями в декількох книгах
• Розрив зв'язку між таблицями
  • Спосіб 1: розрив зв'язку між книгами
  • Спосіб 2: вставка значень
• Питання та відповіді

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

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

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

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

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

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

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

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

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

1. На першому аркуші виділяємо першу клітинку стовпця»Ставка" . Ставимо в ній знак «=» . Далі натискаємо по ярличку «Лист 2» , який розміщується в лівій частині інтерфейсу Excel над рядком стану.

2. Відбувається переміщення в другу область документа. Клацаємо по першій клітинці в стовпці»Ставка" . Потім натискаємо на кнопку Enter на клавіатурі, щоб зробити введення даних в осередок, в якій раніше встановили знак " дорівнює» .

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

   =Лист2!B2


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

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


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

Спосіб 2: Використання зв'язки операторів Індекс & 8212; ПОШУКПОЗ

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

1. Виділяємо перший елемент стовпця»Ставка" . Переходимо в Майстер функцій , клікнувши по піктограмі " Вставити функцію» .

2. В Майстер функцій в групі " посилання та масиви» знаходимо і виділяємо найменування " Індекс» .

3. Даний оператор має дві форми: форму для роботи з масивами і посилальну. У нашому випадку потрібно перший варіант, тому в наступному віконці вибору форми, яке відкриється, вибираємо саме його і тиснемо на кнопку «OK» .

4. Виконано запуск віконця аргументів оператора Індекс . Задача зазначеної функції &8212; висновок значення, що знаходиться в обраному діапазоні в рядку з вказаним номером. Загальна формула оператора Індекс така:

   =Індекс (масив; номер_стовпчика; [номер_стовпця])

   " масив» &8212; аргумент, що містить адресу діапазону, з якого ми будемо витягувати інформацію за номером вказаного рядка.

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

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

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


5. Після того, як координати відобразилися у віконці оператора, ставимо курсор в поле " номер рядка» . Даний аргумент ми будемо виводити за допомогою оператора ПОИСКПОЗ . Тому натискаємо по трикутнику, який розташований зліва від рядка функцій. Відкривається перелік нещодавно використаних операторів. Якщо ви серед них знайдете найменування " ПОШУКПОЗ» , то можете кликати по ньому. У зворотному випадку клікайте по самому останньому пункту переліку – " інші функції...» .

6. Запускається стандартне вікно майстри функцій . Переходимо в ньому в ту ж саму групу " посилання та масиви» . На цей раз в переліку вибираємо пункт «ПОИСКПОЗ» . Виконуємо клацання по кнопці «OK» .

7. Проводиться активація віконця аргументів оператора ПОИСКПОЗ . Зазначена функція призначена для того, щоб виводити номер значення в певному масиві по його найменуванню. Саме завдяки цій можливості ми обчислимо номер рядка певного значення для функції Індекс . Синтаксис ПОИСКПОЗ представлений так:

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

   " шукане значення» &8212; аргумент, що містить назву або адресу комірки стороннього діапазону, в якій вона знаходиться. Саме позицію даного найменування в цільовому діапазоні і слід обчислити. У нашому випадку в ролі першого аргументу будуть виступати посилання на осередки на листі 1 , в яких розташовані імена співробітників.

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

   " Тип відповідності» &8212; аргумент, який є необов'язковим, але, на відміну від попереднього оператора, цей необов'язковий аргумент нам буде потрібен. Він вказує на те, як буде зіставляти оператор шукане значення з масивом. Цей аргумент може мати одне з трьох значень: -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. Як бачимо, незважаючи на те, що порядок рядків у двох пов'язаних таблиць не збігається, проте, всі значення підтягуються відповідно прізвищами працівників. Цього вдалося досягти завдяки застосуванню поєднання операторів Індекс &8212; ПОИСКПОЗ .

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

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

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

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

1. Виділяємо осередок, де буде виводитися підсумок розрахунку заробітної плати на листі 3 . Виробляємо клік по кнопці " Вставити функцію» .

2. Слід запуск вікна майстри функцій . Переходимо в групу »Математичні" і вибираємо там найменування «сума» . Далі тиснемо по кнопці «OK» .

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

   =sum(число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 є способи і інструменти, щоб зв'язати кілька таблиць між собою. При цьому, табличні дані можуть перебувати на інших аркушах і навіть в різних книгах. При необхідності цей зв'язок можна легко розірвати.