Як встановити зв'язок між таблицями в excel. Робоча книга Excel. Зв'язок між робочими листами. Спільне використання даних. Лабораторна робота з Microsoft Excel

Інформацію в електронній таблиці Microsoft Excel можна об'єднати з вилученими із креслення даними.

За допомогою створення зв'язку з електронною таблицею Excel можна включити інформацію з усього аркуша чи його частини, тобто. іменованого діапазону. У діалоговому вікні "Зв'язок із зовнішніми даними" можна виконати такі дії.

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

На сторінці "Виправлення даних" доступний діалогове вікно "Зв'язок із зовнішніми даними", в якому можна налаштувати зв'язок вилучених даних з усією електронною таблицею Excel або з діапазоном рядків і стовпців.

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

Після зміни пов'язаної електронної таблиці, наприклад, після додавання рядка або стовпця, можна оновити таблицю на кресленні відповідно до цієї зміни за допомогою команди ЗВ'ЯЗНИТИ. Аналогічно, при зміні таблиці в кресленні пов'язану електронну таблицю можна оновити за допомогою цієї команди.

Для видалення зв'язку з даними служить команда ЗВ'ЯЗОК. Зв'язатися з даними з панелі "Зовнішні посилання" неможливо видалити.

Про зв'язування та встановлення відповідності даних

Зв'язування даних полягає у виборі електронної таблиці Microsoft Excel з метою об'єднання усієї таблиці або її частини з витягнутими даними креслення. Щоб отримати додаткові відомості про зв'язування даних, див. Створення зв'язку таблиці із зовнішніми даними.

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

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

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

Відповідність даних можна перевірити шляхом натискання кнопки "Перевірити відповідність" у діалоговому вікні "Зв'язок із зовнішніми даними". При помилковій відповідності відображається попереджувальне повідомлення, що вказує на причину. У деяких випадках правильне створення зв'язку між даними креслення та даними електронної таблиці неможливе.

Перевірка зіставлених даних

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

Відповідності між значеннями даних креслення і зовнішніми даними не виявлено.

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

Стовпець даних у зовнішніх даних не містить унікальних значень.

• Стовпець зовнішніх даних містить один або кілька осередків з однаковими даними. У цій ситуації неможливо однозначно поставити відповідність між даними креслення та зовнішніми даними.

З цієї статті Ви дізнаєтеся, як швидко об'єднати дані з двох таблиць Excel, коли ключові стовпці не мають точних збігів. Наприклад, коли унікальний ідентифікатор першої таблиці являє собою перші п'ять символів ідентифікатора з другої таблиці. Всі рішення, що пропонуються в цій статті, протестовані мною в Excel 2013, 2010 і 2007.

Отже, є два аркуші Excel, які потрібно поєднати для подальшого аналізу даних. Припустимо, в одній таблиці містяться ціни (стовпець Price) та опис товарів (стовпець Beer), які Ви продаєте, а в другій відображені дані про наявність товарів на складі (стовпчик In stock). Якщо Ви або Ваші колеги складали обидві таблиці за каталогом, то в обох має бути принаймні один ключовий стовпець з унікальними ідентифікаторами товарів. Опис товару або ціна можуть змінюватись, але унікальний ідентифікатор завжди залишається незмінним.

Труднощі починаються, коли Ви отримуєте деякі таблиці від виробника чи інших відділів компанії. Справа може ще ускладнитися, якщо раптом вводиться новий формат унікальних ідентифікаторів або трохи зміняться складські номенклатурні позначення (SKU). І перед Вами стоїть завдання об'єднати в Excel нову та стару таблиці з даними. Так чи інакше виникає ситуація, коли в ключових стовпцях має місце тільки частковий збіг записів, наприклад, “ 12345 ” та “ 12345-новий_суфікс“. Вам зрозуміло, що це той самий SKU, але комп'ютер не такий догадливий! Це не точний збіг унеможливлює використання звичайних формул Excel для об'єднання даних з двох таблиць.

І що дуже погано - відповідності можуть бути зовсім нечіткими, і Деяка компанія” в одній таблиці може перетворитися на “ ЗАТ «Деяка Компанія»” в іншій таблиці, а “ Нова Компанія (колишня Деяка Компанія)” та “ Стара Компанія” теж виявляться записом про одну й ту саму фірму. Це Вам відомо, але як це пояснити Excel?

Примітка:Рішення, описані у цій статті, є універсальними. Ви можете адаптувати їх для подальшого використання з будь-якими стандартними формулами, такими як ВВР(VLOOKUP) , ПОШУКПОЗ(MATCH), ДПР(HLOOKUP) і таке інше.

Виберіть відповідний приклад, щоб одразу перейти до потрібного рішення:

Ключовий стовпець в одній із таблиць містить додаткові символи

Розглянемо дві таблиці. Стовпці першої таблиці містять номенклатурний номер (SKU), найменування пива (Beer) та його ціну (Price). У другій таблиці записано SKU та кількість пляшок на складі (In stock). Замість пива може бути будь-який товар, а кількість стовпців у реальному житті може бути набагато більшою.

У таблиці із додатковими символами створюємо допоміжний стовпець. Можна додати його в кінець таблиці, але найкраще вставити його наступним праворуч після ключового стовпця, щоб він був на увазі.

Ключовим у таблиці у нашому прикладі є стовпець Aз даними SKU і потрібно витягти з нього перші 5 символів. Додамо допоміжний стовпець і назвемо його SKU helper:

Готово! Тепер у нас є ключові стовпці з точним збігом значень – стовпець SKU helperв основній таблиці та стовпець SKUу таблиці, де виконуватиметься пошук.

Тепер за допомогою функції ВВР(VLOOKUP) ми отримаємо потрібний результат:

Інші формули

• Витягти перші Хсимволів праворуч: наприклад, 6 символів праворуч із запису “DSFH-164900”. Формула виглядатиме так:

  ПРАВСІМ(A2;6)
  =RIGHT(A2,6)

• Пропустити перші Хсимволів витягти наступні Yсимволів: наприклад, потрібно вийняти “0123” із запису “PREFIX_0123_SUFF”. Тут нам потрібно пропустити перші 8 символів та витягти наступні 4 символи. Формула виглядатиме так:

  ПСТР(A2; 8; 4)
  =MID(A2,8,4)

• Витягти всі символи до роздільника, довжина послідовності, що вийшла, може бути різною. Наприклад, потрібно витягти “123456” та “0123” із записів “123456-суфікс” та “0123-суфікс” відповідно. Формула виглядатиме так:

  ЛЕВСІМВ(A2;ЗНАЙТИ("-";A2)-1)
  =LEFT(A2,FIND("-",A2)-1)

Одним словом, можна використовувати такі функції Excel, як ЛЕВСІМВ(LEFT), ПРАВИМО(RIGHT), ПСТР(MID), ЗНАЙТИ(FIND), щоб витягувати будь-які частини складового індексу. Якщо з цим виникли труднощі – ми зробимо все можливе, щоб допомогти Вам.

Дані з ключового стовпця у першій таблиці розбиті на два або більше стовпця у другій таблиці

Припустимо, таблиця, в якій здійснюється пошук, містить стовпець з ідентифікаторами. У осередках цього стовпця містяться записи виду XXXX-YYYY, де XXXX– це кодове позначення групи товарів (мобільні телефони, телевізори, відеокамери, фотокамери), а YYYY- Це код товару всередині групи. Головна таблиця і двох стовпців: у одному містяться коди товарних груп (Group), у другому записані коди товарів (ID). Ми можемо просто відкинути коди груп товарів, оскільки і той ж код товару може повторюватися у різних групах.

У осередку C2запишемо таку формулу:

ЗЧЕПИТИ(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Тут A2- Це адреса осередку, що містить код групи; символ “ – ” – це роздільник; B2- Це адреса осередку, що містить код товару. Скопіюємо формулу до інших рядків.

Тепер об'єднати дані з наших двох таблиць не складе труднощів. Ми будемо зіставляти стовпець Full IDпершої таблиці зі стовпцем IDдругий таблиці. При виявленні збігу, запис зі стовпців Descriptionі PriceДруга таблиця буде додано до першої таблиці.

Дані у ключових стовпцях не збігаються

Ось приклад: Ви власник невеликого магазину, отримуєте товар від одного чи кількох постачальників. У кожного з них прийнято власну номенклатуру, яка відрізняється від Вашої. В результаті виникають ситуації, коли запис “Case-Ip4S-01” відповідає запису “SPK-A1403” у файлі Excel, отриманому від постачальника. Такі розбіжності виникають випадково і немає жодного загального правила, щоб автоматично перетворити “SPK-A1403” на “Case-Ip4S-01”.

Погана новина:Дані, що містяться в цих двох таблицях Excel, доведеться обробляти вручну, щоб надалі можна було об'єднати їх.

Хороша новина:Це доведеться зробити тільки один раз, і допоміжну таблицю, що вийшла, можна буде зберегти для подальшого використання. Далі Ви зможете об'єднувати ці таблиці автоматично і заощадити таким чином багато часу

1. Створюємо допоміжну таблицю для пошуку.

Створюємо новий лист Excel і називаємо його SKU converter. Копіюємо весь стовпець Our.SKUз листа Storeна новий лист, видаляємо дублікати та залишаємо в ньому тільки унікальні значення.

Поруч додаємо стовпець Supp.SKUі вручну шукаємо відповідності між значеннями стовпців Our.SKUі Supp.SKU(у цьому нам допоможуть описи зі стовпця Description). Це нудна роботя, нехай Вас тішить думка про те, що її доведеться виконати лише один раз:-).

В результаті ми маємо таку таблицю:

2. Оновлюємо головну таблицю за допомогою даних із таблиці для пошуку.

У головну таблицю (лист Store) вставляємо новий стовпець Supp.SKU.

Далі за допомогою функції ВВР(VLOOKUP) порівнюємо листи Storeі SKU converter Our.SKU, а для оновлених даних – стовпець Supp.SKU.

Стовпець Supp.SKUзаповнюється оригінальними кодами виробника.

Примітка:Якщо у стовпці Supp.SKUз'явилися порожні осередки, необхідно взяти всі коди SKU, що відповідають цим порожнім осередкам, додати їх до таблиці SKU converterта знайти відповідний код із таблиці постачальника. Після цього повторюємо крок 2.

3. Переносимо дані з таблиці пошуку до головної таблиці

У нашій головній таблиці є ключовий стовпець з точним збігом з елементами таблиці пошуку, тому тепер це завдання не викличе складнощів.

За допомогою функції ВВР(VLOOKUP) об'єднуємо дані листа Storeз даними листа Wholesale Supplier 1, використовуючи для пошуку відповідності стовпець Supp.SKU.

Ось приклад оновлених даних у стовпці Wholesale Price:

Все просто, чи не так? Задавайте свої питання у коментарях до статті, я постараюся відповісти якнайшвидше.

Пов'язана таблиця - це набір даних, якими можна управляти єдиним цілим.

Для створення пов'язаної таблиці призначено кнопку "Форматувати як таблицю"на панелі "Стилі"стрічки "Головна".

Виберіть потрібний стиль майбутньої таблиці та вкажіть діапазон комірок, на основі якого буде створена пов'язана таблиця.

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

Кожна пов'язана таблиця має унікальне ім'я. За замовчуванням – "Таблиця_номер". Змінити назву таблиці можна на панелі "Властивості".

На панелі "Стилі таблиць"можна, якщо потрібно, змінити або налаштувати стиль пов'язаної таблиці.

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

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

У пов'язаній таблиці можна підраховувати підсумкові значення стовпцях. Для цього треба встановити прапорець "Рядок підсумків"на панелі "Параметри стилів таблиць".

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

У пов'язану таблицю можна додавати/видаляти рядки та стовпці.

Це можна робити кількома способами.

1. Скористатися кнопкою "Змінити розмір таблиці"на панелі "Властивості".

2. Встановіть курсор у комірці зв'язаної таблиці, поряд з якою треба додати новий стовпець (рядок) та на панелі "Комірки"стрічки "Головна"скористайтесь кнопкою "Вставити".

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

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

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

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

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

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

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

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

Спосіб 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. Як бачимо, незважаючи на те, що порядок рядків у двох пов'язаних таблиць не збігається, проте всі значення підтягуються відповідно до прізвищ працівників. Цього вдалося досягти завдяки застосуванню поєднання операторів ІНДЕКС—ПОШУКПОЗ.

Якщо нам потрібно, щоб автоматично дані з однієї таблиці Excel переносилися до іншоїпотрібно зв'язати ці таблиці. Існує кілька способів, як зв'язати дані двох таблиць в Excel. Один спосіб - за допомогою формули, дивіться у статті "Як пов'язати таблиці в Excel".
Тут розглянемо, як зв'язати дві таблиці в різних книгах Excelза допомогою встановлення зв'язку між таблицями. Коли все налаштуємо, достатньо відкрити одну таблицю, щоб дані перенеслися в цю таблицю з іншої таблиці. Не треба відкривати всі книги, щоб зв'язок працював. Вона працюватиме з однією відкритою книгою.
Отже, ми маємо дві книги Excel – Книга1 і Книга2. У Книзі1 є така таблиця, що вже заповнена. Таблиця 1.Увага!
Якщо таблиці є об'єднані осередки, то зв'язок не працюватиме чи збиватися.
У Книзі2 робимо таку ж таблицю, але не заповнюємо її.

Ще функцію "Вставити зв'язок" можна викликати так. Також копіюємо, але в контекстному меню натискаємо на функцію «Спеціальна вставка». У діалоговому вікні, що з'явилося, натискаємо на кнопку «Вставити зв'язок».