Excel: створити посилання на іншу електронну таблицю

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

У цій статті ви дізнаєтесь, як можна зв’язати електронні таблиці Excel кількома клацаннями миші та виконувати обчислення в кількох електронних таблицях.

Як створити посилання на іншу електронну таблицю?

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

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

Щоб створити посилання на іншу електронну таблицю, спочатку відкрийте обидві електронні таблиці в Microsoft Excel. Потім у цільовій таблиці клацніть цільову клітинку, де потрібно відобразити дані з вихідного файлу. У нашому практичному прикладі це цільова комірка B1.Формула для посилання завжди починається зі знака рівності. Введіть "="у попередньо виділену клітинку.

На наступному кроці перейдіть до таблиці з даними про співробітників і виділіть число 55 у клітинці C9. Саме стільки співробітників працює у філії в Пассау.

Натисніть Enter, щоб завершити ярлик. Кількість 55 співробітників тепер відображається в таблиці 2, оскільки ви фактично пов’язали клітинки обох робочих аркушів одна з одною.

Якщо ви хочете створити посилання вручну за допомогою клавіатури, використовуйте такий синтаксис:

=назва аркуша!комірка

Для назви аркуша введіть назву аркуша, на комірку якого ви хочете посилатися. У нашому прикладі аркуш має назву «Аркуш1». Мітка комірки вказує на клітинку(и) у Аркуші1, на яку потрібно посилатися.

Як виконати обчислення в кількох електронних таблицях Excel?

Часто буває так, що обчислення доводиться проводити в кількох електронних таблицях. У нашому прикладі книги є кілька електронних таблиць, які мають однакову структуру. У таблиці описані витрати електроенергії на різних підприємствах компанії за один рік з розбивкою по місяцях. Робочий зошит містить дані за 2016-2021 роки. Підсумкові та максимальні значення потрібно розрахувати в підсумковому аркуші.

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

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

У наступному прикладі потрібно підсумувати витрати на електроенергію за останні кілька років. На ілюстрації показано структуру окремих аркушів:

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

На наступному малюнку показано розташування робочих аркушів у прикладі книги:

Після завершення підготовки ви можете почати вводити формули.

Як обчислити суму кількох аркушів у Excel?

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

Формула, необхідна для підсумовування, має таку структуру:

=SUM('ПершийАркуш:ОстаннійАркуш'!Діапазон)

" За допомогою FirstSheet ви передаєте назву першого аркуша, який потрібно включити в обчислення. Аргумент lastsheet визначає назву останнього аркуша для обчислення. Також враховуються всі листи, що лежать між цими двома листами.Площа визначає площу, яку потрібно підсумувати."

Використовуйте таку формулу, щоб обчислити суму значень у клітинках H16 робочих аркушів 2016–2021:

=SUM('2016:2021'!H16)

На наступному малюнку показано використання цієї формули у прикладі таблиці:

Адаптувавши цю просту формулу, можна також запитувати інші клітинки. Наприклад, якщо ви хочете дізнатися, скільки електроенергії було спожито на заводі у Франкфурті у квітні з 2016 по 2021 рік, замініть у формулі посилання на клітинку H16 на клітинку F7.

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

Як розрахувати максимальне значення кількох аркушів у Excel?

У нашому прикладі таблиці, у якій порівнюються витрати на електроенергію різних станцій у 2016–2021 роках, потрібно визначити абсолютне максимальне значення всіх окремих значень за всі роки. Індивідуальні значення знаходяться в таблицях з 2016 по 2021 у клітинках B4:G15.

Використовуйте таку формулу, щоб визначити максимальне значення всіх окремих значень у п’яти електронних таблицях відповідних років:

=МАКС('2016:2021'!B4:G15)

На наступному малюнку показано використання цієї формули у прикладі таблиці:

Як обчислити середнє значення для різних електронних таблиць?

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

=СЕРЕДНЄ ('початковий аркуш:кінцевий аркуш'!діапазон клітинок)

За допомогою формули ви передаєте загалом три аргументи:

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

Для нашої зразкової таблиці формула та результат виглядають так:

=СЕРЕДНЄ('2016:2021'!B4:G15)

Як можна зв'язати значення з інших документів?

Якщо ви часто працюєте з Excel, іноді вам потрібно зв’язати посилання на клітинки з електронної таблиці, які не містяться в одному документі.Це працює без будь-яких проблем, додаючи назву документа до посилання на комірку, описаного вище. Це позначено квадратними дужками (" [" або "]" ).

Приклад:=[Документ1]Походження!G9

Використовуйте цей варіант, якщо відкрита друга папка з назвою «Документ1». З іншого боку, якщо документ не відкрито, використовуйте шлях, де він збережений.

Приклад:=C:\Files\FolderName\[Document1]Origin!G9

Як пов’язати посилання на клітинки за допомогою миші?

Посилання на клітинки на інші документи не обов’язково вводити вручну, оскільки ви можете легко зв’язати клітинки лише кількома клацаннями миші. (ВСТАВТЕ ЗОБРАЖЕННЯ)

Як виправити помилку посилання на клітинку?

Під час зв’язування електронних таблиць і використання посилань Microsoft Excel може відобразити помилку в посиланні на клітинку. Якщо це так, формула містить помилку або сталася помилка під час виділення області мишею

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

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

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