Ви застосовували функцію ВПР, щоб перемістити дані стовпця з однієї таблиці до іншої? Оскільки в Excel тепер є інтегрована модель даних, функція ВПР застаріла. Ви можете створити зв'язок між двома таблицями на основі даних, що збігаються в них. Потім можна створити аркуші Power View або зведені таблиці та інші звіти з полями кожної таблиці, навіть якщо вони отримані з різних джерел. Наприклад, якщо у вас є дані про продаж клієнтам, вам може знадобитися імпортувати і зв'язати дані логіки операцій з часом, щоб проаналізувати тенденції продажів за роками та місяцями.
Усі таблиці у книзі вказуються у списках полів зведеної таблиці та Power View.
При імпорті зв'язаних таблиць з реляційної бази даних Excel часто може створювати зв'язки в моделі даних, що формується у фоновому режимі. В інших випадках необхідно створювати зв'язки вручну.
- Переконайтеся, що книга містить принаймні дві таблиці і в кожній з них є стовпець, який можна зіставити зі стовпцем з іншої таблиці.
- Виконайте одну з таких дій: відформатуйте дані у вигляді таблиці або Імпорт зовнішніх даних у вигляді таблиці на новому аркуші.
- Надайте кожній з таблиць зрозуміле ім'я: На вкладці Робота з таблицями клацніть Конструктор >Ім'я таблиці та введіть ім'я.
- Переконайтеся, що стовпець в одній таблиці має унікальні значення без дублікатів. Excel може створювати зв'язки лише у тому випадку, якщо один стовпець містить унікальні значення.Наприклад, щоб зв'язати продаж клієнта з логікою операцій з часом, обидві таблиці повинні включати дату в однаковому форматі (наприклад, 01.01.2012) і принаймні в одній таблиці (логіка операцій з часом) повинні бути перераховані всі дати лише один раз у стовпці .
- Клацніть Дані>Відносини.
Якщо команда Відносини недоступна, отже книга містить лише одну таблицю.
- У вікні Управління зв'язками натисніть кнопку Створити.
- У вікні Створення зв'язку клацніть стрілку поруч із полем Таблиця і виберіть таблицю зі списку, що розкривається. У зв'язку "один до багатьох" ця таблиця має бути частиною з кількома елементами. У прикладі з клієнтами та логікою операцій з часом необхідно спочатку вибрати таблицю продажів клієнтів, тому що щодня, швидше за все, відбувається безліч продажів.
- Для елемента Стовпець (чужий) Виберіть стовпець, який містить дані, що стосуються елемента Пов'язаний стовпець (первинний ключ). Наприклад, за наявності стовпця дати в обох таблицях необхідно вибрати цей стовпець саме зараз.
- У полі Пов'язана таблиця виберіть таблицю, що містить хоча б один стовпець даних, пов'язаних з таблицею, вибраною в полі Таблиця.
- У полі Пов'язаний стовпець (первинний ключ) виберіть стовпець, який містить унікальні значення, які відповідають значенням у стовпці, вибраному в полі Стовпець.
- Натисніть кнопку ОК.
Додаткові відомості про зв'язки між таблицями в Excel
Примітки про зв'язки
- Ви дізнаєтеся, чи існують зв'язки при перетягуванні полів з різних таблиць до списку полів зведеної таблиці. Якщо вам не буде запропоновано створити зв'язок, то Excel вже має відомості, необхідні для зв'язку даних.
- Створення зв'язків аналогічне використанню VLOOKUP: потрібні стовпці, що містять збігаються дані, щоб Excel зміг перехресно посилатися на рядки в одній таблиці з рядками іншої таблиці. У прикладі аналітики часу таблиця Customer має містити значення дати, які також існують у таблиці аналітики часу.
- У моделі даних зв'язку таблиць можуть бути типу "один до одного" (у кожного пасажира є один посадковий талон) або "один до багатьох" (у кожному рейсі багато пасажирів), але не "багато хто до багатьох". Зв'язки "багато до багатьох" призводять до помилок циклічної залежності, таким як "Виявлена циклічна залежність". Ця помилка може статися, якщо ви створюєте пряме підключення між двома таблицями зі зв'язком "багато до багатьох" або непрямі підключення (ланцюжок зв'язків таблиць, в якій кожна таблиця пов'язана з наступним ставленням "один до багатьох", але між першою та останньою утворюється відношення " багато хто до багатьох "). Щоб отримати додаткові відомості, див. Зв'язок між таблицями в моделі даних.
- Типи даних у двох стовпцях мають бути сумісні. Докладніші відомості див. у статті Типи даних у моделях даних.
- Інші способи створення зв'язків можуть виявитися зрозумілішими, особливо якщо невідомо, які стовпці використовувати. Щоб отримати додаткові відомості, див. Створення зв'язку в поданні діаграми Power Pivot.
приклад. Зв'язування даних логіки операцій з часом з даними по рейсах авіакомпанії
Ви можете дізнатися про зв'язки обох таблиць та логіку операцій з часом за допомогою вільних даних на Microsoft Azure Marketplace. Деякі з цих наборів даних дуже великі, і для їх завантаження за розумний час потрібне швидке підключення до Інтернету.
- Запустіть Power Pivot у Microsoft Excel і відкрийте вікно Power Pivot.
- Натисніть Отримання зовнішніх даних >Зі служби даних >З Microsoft Azure Marketplace. У майстрі імпорту таблиць відкриється домашня сторінка Microsoft Azure Marketplace.
- У розділі Price (Ціна) натисніть Free (Безкоштовно).
- У розділі Category (Категорія) натисніть Science & Statistics (Наука та статистика).
- Знайдіть DateStream та натисніть кнопку Subscribe (Підписатися).
- Введіть облікові дані корпорації Майкрософт і натисніть Sign in (Вхід). Відкриється вікно попереднього перегляду даних.
- Прокрутіть вниз і натисніть Select Query (Запит на вибірку).
- Натисніть кнопку Далі.
- Щоб імпортувати дані, виберіть BasicCalendarUS та натисніть Готово. При швидкому підключенні до Інтернету імпорт триватиме близько хвилини. Після виконання ви побачите звіт про стан переміщення 73414 рядків. Натисніть Закрити.
- Щоб імпортувати другий набір даних, натисніть Отримання зовнішніх даних >Зі служби даних >З Microsoft Azure Marketplace.
- У розділі Type (Тип) натисніть Data Дані).
- У розділі Price (Ціна) натисніть Free (Безкоштовно).
- Знайдіть US Air Carrier Flight Delays та натисніть Select (Вибрати).
- Прокрутіть вниз і натисніть Select Query (Запит на вибірку).
- Натисніть кнопку Далі.
- Натисніть Готово для імпорту даних. При швидкому підключенні до Інтернету імпорт триватиме близько 15 хвилин. Після виконання ви побачите звіт про стан переміщення 2427284 рядків. Натисніть Закрити. Тепер у вас є дві таблиці у моделі даних. Щоб зв'язати їх, потрібні сумісні стовпці кожної таблиці.
- Переконайтеся, що значення у стовпці DateKey у таблиці BasicCalendarUS вказано у форматі 01.01.2012 00:00:00. У таблиці On_Time_Performance також є стовпець дати та часу FlightDate, значення якого вказано у тому ж форматі: 01.01.2012 00:00:00. Два стовпці містять збігаються дані однакового типу і принаймні один із стовпців (DateKey) містить лише унікальні значення. У наступних діях ви використовуватимете ці стовпці, щоб зв'язати таблиці.
- У вікні Power Pivot натисніть Зведена таблиця, щоб створити зведену таблицю на новому чи існуючому аркуші.
- У списку полів розгорніть таблицю On_Time_Performance та натисніть ArrDelayMinutes, щоб додати їх до області значень. У зведеній таблиці ви побачите загальний час затриманих рейсів за хвилини.
- Розгорніть таблицю BasicCalendarUS та натисніть MonthInCalendar, щоб додати його до області рядків.
- Зверніть увагу, що тепер у зведеній таблиці перераховані місяці, але кількість хвилин однакова кожного місяця. Потрібні однакові значення, що вказують на зв'язок.
- У списку полів у розділі "Можуть знадобитися зв'язки між таблицями" натисніть Створити.
- У полі "Зв'язана таблиця" виберіть On_Time_Performance, а в полі "Зв'язаний стовпець (первинний ключ)" – FlightDate.
- У полі "Таблиця" виберітьBasicCalendarUS, а в полі "Стовпець (чужий)" – DateKey. Натисніть ОК для створення зв'язку.
- Зауважте, що час затримки в даний час відрізняється для кожного місяця.
- У таблиці BasicCalendarUS перетягніть YearKey у область рядків над пунктом MonthInCalendar.
Тепер ви можете розділити затримки прибуття за роками та місяцями та іншими значеннями в календарі.
Поради: За замовчуванням місяці перераховані за абеткою.За допомогою надбудови Power Pivot можна змінити порядок сортування так, щоб вони відображалися в хронологічному порядку.
- Таблиця BasicCalendarUS має бути відкрита у вікні Power Pivot.
- У головній таблиці натисніть Сортування по стовпцю.
- У полі "Сортувати" виберіть MonthInCalendar.
- У полі "По" виберіть MonthOfYear.
Зведена таблиця тепер сортує кожну комбінацію "місяць та рік" (жовтень 2011, листопад 2011) за номером місяця на рік (10, 11). Змінити порядок сортування нескладно, тому що канал DateStream надає всі необхідні стовпці для цього сценарію. Якщо ви використовуєте іншу таблицю логіки операцій із часом, ваші дії будуть іншими.
"Можуть знадобитися зв'язки між таблицями"
У міру додавання полів у зведену таблицю ви отримаєте сповіщення про необхідність зв'язку між таблицями, щоб розібратися з полями, вибраними у зведеній таблиці.
Хоча Excel може підказати вам, коли необхідний зв'язок, він не може підказати, які таблиці та стовпці використовувати, а також чи можливий зв'язок між таблицями. Щоб отримати відповіді на свої запитання, спробуйте зробити таке.
Крок 1. Визначте, які таблиці вказати у зв'язку
Якщо ваша модель містить лише кілька таблиць, зрозуміло, які з них потрібно використовувати. Але для великих моделей вам знадобиться допомога. Один із способів полягає в тому, щоб використовувати представлення діаграми у надбудові Power Pivot. Подання діаграми забезпечує візуалізацію всіх таблиць моделі даних. За допомогою нього можна швидко визначити, які таблиці відокремлені від решти моделі.
Примітка: Можна створювати неоднозначні зв'язки, які є неприпустимими під час використання у зведеній таблиці або звіті Power View.Нехай всі ваші таблиці пов'язані якимось чином з іншими таблицями в моделі, але при спробі об'єднання полів з різних таблиць ви отримаєте повідомлення "Можуть знадобитися зв'язки між таблицями". Найбільш імовірною причиною є те, що ви зіткнулися зі зв'язком "багато хто до багатьох". Якщо ви будете слідувати ланцюжку зв'язків між таблицями, які підключаються до необхідних вам таблиць, то ви, ймовірно, виявите наявність двох або більше зв'язків "один до багатьох" між таблицями. Не існує простого обхідного шляху, який би працював у будь-якій ситуації, але ви можете спробувати створити обчислювані стовпці, щоб консолідувати стовпці, які ви хочете використовувати в одній таблиці.
Крок 2. Знайдіть стовпці, які можуть бути використані для створення шляху від однієї таблиці до іншої
Після того, як ви визначили, яка таблиця не пов'язана з рештою моделі, перегляньте стовпці в ній, щоб визначити, чи містить інший стовпець в іншому місці моделі відповідні значення.
Припустимо, у вас є модель, яка містить продаж продукції по території, і ви згодом імпортуєте демографічні дані, щоб дізнатися, чи є кореляція між продажами та демографічними тенденціями на кожній території. Оскільки демографічні дані надходять із різних джерел, їх таблиці спочатку ізольовані від решти моделі. Для інтеграції демографічних даних з рештою своєї моделі вам потрібно буде знайти стовпець в одній з демографічних таблиць, який відповідає тому, який ви вже використовуєте.Наприклад, якщо демографічні дані організовані по регіонах і ваші дані про продаж визначають область продажу, ви могли б зв'язати два набори даних, знайшовши спільні стовпці, такі як держава, поштовий індекс або регіон, щоб забезпечити підстановку.
Крім значень, що збігаються є кілька додаткових вимог для створення зв'язків.
- Значення даних у стовпці підстановки мають бути унікальними. Іншими словами, стовпець не може містити дублікати. У моделі даних нулі та порожні рядки еквівалентні порожньому полю, яке є самостійним значенням даних. Це означає, що не може бути кілька нулів у стовпці підстановок.
- Типи даних стовпця підстановок та вихідного стовпця мають бути сумісні. Докладніше про типи даних див. у статті Типи даних у моделях даних.
Докладніше про зв'язки таблиць див. у статті Зв'язки між таблицями в моделі даних.
Excel Online є одним із найпопулярніших та зручних табличних процесорів, необхідних для авторизації розрахунків, розробки діаграм та таблиць, проведення функцій різної складності. Працюючи з програмою Excel Online і створення великих обсягів інформації та документів, часто виникає потреба пов'язувати таблиці між собою. Наприклад, це зручно, коли є формула, яка буде включати дані з декількох аркушів або навіть книг. Розглянемо докладніше у цій статті, як можна пов'язати таблиці між файлами.
Як створити посилання між кількома аркушами Excel Online
Спочатку необхідно створити формули, в яких міститься інформація з різних аркушів. Книга може складатися з декількох аркушів, кожен з яких має вкладку.Якщо ваш документ складається з двох і більше аркушів, необхідно написати формулу для використання інформації з цих аркушів.
Щоб створити формулу між аркушами в одному документі, виконайте такі дії:
- Створіть формулу. Клацніть подвійним клацанням у комірці, поставте знак «=» і приступайте до створення, до якого потрібно вставити посилання. Щоб увімкнути якийсь осередок, просто клацніть у потрібний осередок після введення потрібного знака. Не закривайте формулу до перемикання на інший аркуш.
- Перейдіть на другий аркуш документа, доки не завершено формулу. Встановіть курсор у ту комірку, яку необхідно додати, і програма запише частину формули, яка звернена до комірки на другому аркуші.
- Закрийте формулу. Клацніть кнопку "Enter" для її завершення, і програма перенесе вас на те місце, звідки створювалася формула і висвітить підсумок. Доповніть формулу за потреби, комбінуючи посилання.
Як зв'язати між собою таблиці в Екселі: інструкція
Іноді виникає ситуація, коли необхідно отримати дані з іншого документа. Щоб зв'язати кілька, виконайте такі дії:
- Відкрийте дві або кілька робочих книг і розмістіть їх поряд. При цьому можна скористатися опцією "Windows Snap", яка дозволяє зменшити кожне вікно на підлогу екрана.
- Починайте створення формули. Поставте знак «=» та введіть необхідні дані. Не закривайте формулу та переконайтеся, що вона залишається відкритою.
- Перейдіть до другого робочого документа. Поки формула відкрита, клацніть на вкладці другої книги і натисніть на потрібну комірку, щоб зв'язати дані двох книг. Програма миттєво додасть посилання другого документа.
- Клацніть кнопку "Enter", і система розрахує підсумковий результат, а книги залишаться пов'язаними.
Примітка: важливою особливістю при роботі з кількома книгами є розташування однієї книги поряд з іншою для зручності зв'язування двох документів.
Якщо під час роботи з Excel Online та створення великих обсягів інформації виникає потреба пов'язувати таблиці між собою, скористайтеся рекомендаціями, описаними в цій статті. Зробити це досить просто, якщо слідувати точним алгоритмам роботи.
Блог про програму Microsoft Excel: прийоми, хитрощі, секрети, трюки
Зв'язок між таблицями Excel – це формула, яка повертає дані із осередку іншої робочої книги. Коли ви відкриваєте книгу, що містить зв'язки, Excel зчитує останню інформацію з книги-джерела (оновлення зв'язків)
Міжтабличні зв'язки в Excel використовуються для отримання даних з інших аркушів робочої книги, так і з інших робочих книг Excel. Наприклад, у вас є таблиця з розрахунку підсумкової суми продажів. У розрахунку використовуються ціни на продукт та обсяг продажів. У такому разі має сенс створити окрему таблицю з даними за цінами, які підтягуватимуться за допомогою зв'язків першої таблиці.
Коли ви створюєте зв'язок між таблицями, Excel створює формулу, яка включає в себе ім'я вихідної книги, укладену в дужки [], ім'я аркуша зі знаком оклику на кінці і посилання на комірку.
Створення зв'язків між робітничими книгами
- Відкриваємо обидві робочі книги в Excel
- У вихідній книзі вибираємо комірку, яку необхідно зв'язати, та копіюємо її (поєднання клавіш Ctrl+С)
- Переходимо до кінцевої книги, клацаємо правою кнопкою миші по комірці, куди ми хочемо помістити зв'язок. З меню вибираємо Спеціальна вставка
- У діалоговому вікні, що з'явилося. Спеціальна вставка обираємо Вставити зв'язок.
Є ще один, простіший варіант створення зв'язку між таблицями. У комірку, куди ми хочемо вставити зв'язок, ставимо знак рівності (як і для звичайної формули), переходимо у вихідну книгу, вибираємо комірку, яку хочемо зв'язати, клацаємо Enter.
Ви можете використовувати інструменти для копіювання та автозаповнення для формул зв'язку так само, як і для звичайних формул.
Перш ніж створювати зв'язки між таблицями
Перш ніж ви почнете розповсюджувати знання на свої грандіозні ідеї, прочитайте кілька порад щодо роботи зі зв'язками в Excel:
Робіть зв'язки, що легко відстежуються. Немає автоматичного пошуку всіх осередків, що містять посилання. Тому, використовуйте певний формат, щоб швидко ідентифікувати зв'язки з іншими таблицями, інакше документ, що містить посилання, може розростатися настільки, що його буде важко підтримувати.
Автоматичні обчислення. Вихідна книга повинна працювати в режимі автоматичного обчислення (за замовчуванням). Щоб переключити параметр обчислення, перейдіть по вкладці Формули у групу Обчислення. Виберіть Параметри обчислень -> Автоматично.
Уникайте циклічних посилань. Циклічні зв'язки – коли дві робочі книги містять посилання один на одного – можуть бути причиною повільного відкриття та роботи файлу.
Оновлення зв'язків
Для ручного оновлення зв'язку між таблицями перейдіть по вкладці Дані у групу Підключення. Клацніть по кнопці Змінити зв'язки.
У діалоговому вікні, що з'явилося. Зміна зв'язків, виберіть зв'язок, що вас цікавить, і клацніть по кнопці Оновити.
Розірвати зв'язки у книгах Excel
Розрив зв'язку з джерелом призведе до заміни існуючих формул зв'язку значення, які вони повертають.Наприклад, зв'язок =[Джерело.xlsx]Ціни!$B$4 буде замінено на 16. Розрив зв'язку не можна скасувати, тому перш ніж зробити операцію, рекомендую зберегти книгу.
Перейдіть по вкладці Дані у групу Підключення. Клацніть по кнопці Змінити зв'язки. У діалоговому вікні, що з'явилося. Зміна зв'язків, виберіть зв'язок, що вас цікавить, і клацніть по кнопці Розірвати зв'язок.
Вам також можуть бути цікаві наступні статті
- Як порівняти два стовпці в Excel – методи порівняння даних Excel
- Формули таблиць Excel
- Функція СЖПРОБІЛИ в Excel з прикладами використання
- Чотири способи використання ВПР із кількома умовами
- Що якщо відобразити приховані рядки в Excel не працює
- Сьомий урок навчального курсу – Основи Excel – Управління кількома робочими листами
- П'ятий урок курсу з основ Excel — Друк у програмі
- Шостий урок онлайн курсу з основ Excel — Управління робочим листом
- Четвертий урок курсу з основ Excel — Зміна осередків
- Третій урок курсу з основ Excel — Форматування робочих листів
8 коментарів
Дякую! дуже корисний матеріал! Будь ласка, виправте друкарську помилку:
«У вихідній книзі вибираємо комірку, яку необхідно зв'язати, та копіюємо її (поєднання клавіш Ctrl+V)»
Думаю, має бути «Ctrl+С»
Доброго дня!
Дякую! Цікава інформація Підкажіть, зв'язок між файлами не порушиться, якщо змінити назву файлу Джерело?
Доброго дня.
Є: як мультиплікувати зв'язку, тобто. копіювати готові зв'язки у файлі, застосовувати їх до інших осередків та змінюючи властивості скопійованих зв'язків «підкачувати» інформацію з іншого файлу?
наприклад:
Є файли "Січень", "лютий", "березень" з показниками, форма файлів (комірки, стовпці, рядки) ідентичні, змінюються лише дані.
Завдання зробити файл «зведення», в якому рядково
січень
лютий
березень
звести дані кожного файлу.
легко виходить зробити зв'язки по рядку січень, і далі потрібно знову ВРУЧНУ робити зв'язки для лютого та березня
Хотілося б зв'язку січня «скопіювати» у рядки лютого та березня та налаштувати зв'язок у кожному рядку на файл відповідного місяця. Заздалегідь дякую.
p.s. сам потикав, не виходить. всі скопійовані зв'язки він бачить як один і той самий зв'язок, хоч сто разів її в цьому файлі встав (у вкладці Дані\змінити зв'язки, залишається один набір зв'язків), а хотілося б що після Ctrl+V було 2, 3, 4 і .т.д. наборів зв'язків, які вже можна було налаштовувати. Якщо не складно підкажіть, як це можна зробити?
Доброго дня.
Створюю пов'язані таблиці для створення бланків замовлення, щоб не вводити 2 рази ті самі значення, а також для того, щоб була створена база даних клієнтів.
Разом: Потрібен документ із даними клієнтів, Вихідний бланк замовлень повинен автоматично копіювати дані клієнта, зрозуміло ці дані змінюються. Створив таблицю бланка замовлень, створив таблицю Бази даних клієнтів із основною інформацією заповнення бланка. Створив зв'язок, все вписується автоматично. Як тепер вийти із ситуації, щоб наступного разу, при внесенні відомостей про нового клієнта, зберегти попереднього клієнта та в бланк вписати нові дані? Я подумав, що зможу простим додаванням нового рядка поверх створеного клієнта обнулити бланк замовлення і внести нові дані, проте при такому ході дані в бланку залишаються незмінними, при тому, що вихідні координати змінилися. ідеї у бланку мають бути порожні значення. Чи можна якось вирішити це завдання? Прив'язати значення рядка у вихідній книзі?