Як можна звернутися до осередку розташованого на іншому аркуші поточної книги в excel

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

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

Посилання на комірки з інших робочих листів
Щоб використовувати посилання на комірку з іншого робочого аркуша, який належить тій самій робочій книзі, дотримуйтесь наступного формату:

Іншими словами, перед адресою комірки потрібно вставити ім'я робочого листа зі знаком оклику. Наведемо приклад формули, в якій використовується посилання на комірку з робочого листа Лист2: = А1 * Лист2!А1.

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

Якщо ім'я робочого аркуша містить один або кілька пробілів, то Ви повинні укласти це ім'я в одинарні лапки.

Посилання на осередки з інших робочих книг
Щоб послатися на комірку з іншої робочої книги, дотримуйтесь наступного формату:

= [Ім'я_Робочої_книги] Ім'я_Ліста!
В даному випадку адресі осередку передують ім'я робочої книги, укладене в квадратні дужки, ім'я робочого листа та знак оклику. Наведемо приклад формули, в якій використовується посилання на комірку з робочого листа Лист1, що знаходиться в робочій книзі Бюджет: = [Бюджет.х1s]Лист1!А1.

Якщо в імені робочої книги міститься один або кілька пробілів, то посилання це ім'я і ім'я аркуша слід укласти в одинарні лапки. Наведемо приклад формули, в якій є посилання на комірку, розташовану в аркуші Лист1 робочої книги Бюджет на 2005: = А1 * '[Бюджет на 2005] Лист1'! А1.

Якщо у формулі використовуються посилання на комірки з іншої робочої книги, відкривати цю робочу книгу не обов'язково. Якщо вона закрита, треба додати до імені робочої книги повний шлях. Наведемо приклад: = А1 * 'C:\MSOffice\Excel\[Бюджет на 2005]Лист1'!А1.

Найпростіший спосіб створення формул, в яких є посилання на комірки, розташовані за межами поточного робочого листа, — скористатися методом вказівки. Щоб скористатися методом вказівки, робоча книга, на яку Ви посилаєтеся у своїй формулі, має бути відкритою.

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

Ви можете посилатися на вміст комірок в іншій книзі, створивши формулу зовнішнього посилання. Зовнішнє посилання (також зване посиланням) – це посилання на комірку або діапазон на книзі Excel або посилання на певне ім'я в іншій книзі.

Відкрийте книгу, яка міститиме зовнішнє посилання (призначену книгу) та книгу з даними, з якими потрібно зв'язатися (з першої книги).

Виберіть осередок або осередки, для яких потрібно створити зовнішнє посилання.

Введіть = (знак рівності).

Якщо ви хочете використовувати функцію, наприклад СУМ, введіть її ім'я та відкриваю дужки. Наприклад, = СУМ (.

Перейдіть в книгу-джерело і клацніть книгу, що містить комірки, які ви хочете зв'язати.

Виберіть комірку або комірки, на які ви хочете зв'язатися, і натисніть Enter.

Примітка: Якщо ви виберете кілька осередків, наприклад =[SourceWorkbook.xlsx]Аркуш1!$A$1:$A$10 і у вас є поточна версія Microsoft 365,то можна просто натиснути введення, щоб підтвердити формулу як формулу динамічного масиву. В іншому випадку формула повинна бути введена як формула застарілої масиву, натиснувши CTRL+SHIFT+ВВЕДЕННЯ. Додаткові відомості про формули масиву див. у статті Використання формул масиву: рекомендації та приклади.

Excel повертається до 9-ї книги і відображаються значення з неї.

Зверніть увагу Excel повертає посилання з абсолютними посиланнями, тому якщо ви хочете скопіювати формулу в інші осередки, вам потрібно видалити знак долара ($):

Якщо закрити книгу-джерело, Excel автоматично у формулу буде автоматично включений шлях до файлу:

Створення зовнішнього посилання на певне ім'я в іншій книзі

Виберіть осередок або осередки, для яких потрібно створити зовнішнє посилання.

Введіть = (знак рівності).

Перейдіть в книгу-джерело і клацніть книгу, що містить комірки, які ви хочете зв'язати.

Натисніть F3, виберіть ім'я, на яке буде посилатися посилання, та натисніть Enter.

Примітка: Якщо названа комірка посилається на кілька осередків і у вас є поточна версія Microsoft 365, можна просто натиснути введення, щоб підтвердити формулу як формулу динамічного масиву.В іншому випадку формула повинна бути введена як формула застарілої масиву, натиснувши CTRL+SHIFT+ВВЕДЕННЯ. Додаткові відомості про формули масиву див. у статті Використання формул масиву: рекомендації та приклади.

Excel ви повернетеся до книги призначення та відобразить значення з іменованого діапазону в книзі-джерелі.

Визначення імені, що містить зовнішнє посилання на комірки в іншій книзі

Відкрийте 9-у книгу та книгу-джерело.

У книзі призначення перейдіть до Формули > певні імена > визначити ім'я.

У діалоговому вікні Нове ім'я в полі Ім'я введіть ім'я діапазону.

У полі Посилання видаліть вміст, а потім зберігайте курсор у полі.

Якщо ви хочете використовувати ім'я функції, введіть ім'я функції та наведіть курсор у місце, де ви хочете використовувати зовнішнє посилання. Наприклад, введіть = СУМ () і розташувати курсор між скобами.

Перейдіть в книгу-джерело і клацніть книгу, що містить комірки, які ви хочете зв'язати.

Виберіть комірку або діапазон комірок, які потрібно зв'язати, і натисніть кнопку ОК.

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

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

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

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

Формули із зовнішніми посиланнями на інші книги відображаються двома способами залежно від того, чи є книга-джерело (яка містить дані у формулу) відкритою або закритою.

Коли джерело відкрито, зовнішнє посилання включає ім'я книги у квадратних дужках([ ]), за яким йдуть ім'я таблиці, пункт оклику(!) і комірки, від яких залежить формула. Наприклад, наступна формула додає клітинки C10:C25 з книги з ім'ям Budget.xls.

Якщо джерело не відкрите, зовнішнє посилання включає весь шлях.

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

У формулах, які посилаються на певне ім'я в іншій книзі, використовується ім'я книги, за яким слідують пункт оклику (!) та ім'я. Наприклад, наступна формула додає осередки з книги "Продажі" Budget.xlsx.

Виберіть осередок або осередки, для яких потрібно створити зовнішнє посилання.

Введіть = (знак рівності).

Якщо ви хочете використовувати функцію, наприклад СУМ, введіть її ім'я та відкриваю дужки. Наприклад, = СУМ (.

Перемикання на таблицю із осередками, на які ви хочете зв'язатися.

Виберіть комірку або комірки, на які ви хочете зв'язатися, і натисніть Enter.

Примітка: Якщо вибрати кілька комірок (=Аркуш1! A1:A10) і поточну версію Microsoft 365 ,після чого можна просто натиснути введення, щоб підтвердити формулу як формулу динамічного масиву.В іншому випадку формула повинна бути введена як формула застарілої масиву, натиснувши CTRL+SHIFT+ВВЕДЕННЯ. Додаткові відомості про формули масиву див. у статті Використання формул масиву: рекомендації та приклади.

Excel повернутися на вихідний і відобразити значення вихідного.

Створення зовнішнього посилання на комірки у різних книгах

Відкрийте книгу, яка міститиме зовнішнє посилання (9-у книгу, також звану книгою формул) і книгу з даними, з яких має бути зв'язок (книга-джерело даних, також звана книгою даних).

У книзі-джерелі виберіть осередок або осередки, які потрібно зв'язати.

Натисніть CTRL+C або перейдіть на головна> буфер обміну > Копіювати.

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

Виберіть комірку, в якій ви хочете розмістити зв'язані дані, а потім перейдіть на головна> буфер обміну> в> в буфер обміну.

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

Щоб використовувати посилання у формулі, введіть = перед посиланням, виберіть опцію, введіть (і введіть ) після посилання.

Створення зв'язку з книгою

Виберіть осередок або осередки, для яких потрібно створити зовнішнє посилання.

Введіть = (знак рівності).

Якщо ви хочете використовувати функцію, наприклад СУМ, введіть її ім'я та відкриваю дужки. Наприклад, = СУМ (.

Перемикання на таблицю із осередками, на які ви хочете зв'язатися.

Виберіть комірку або комірки, на які ви хочете зв'язатися, і натисніть Enter.

Excel повернутися на вихідний і відобразити значення вихідного.

Додаткові відомості

Ви завжди можете поставити запитання спеціалісту Excel Tech Community або попросити допомоги у спільноті Answers community.

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

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

Посилання на лист у формулі Excel

Доходи за січень, лютий та березень введіть на трьох окремих аркушах. Потім на четвертому аркуші в осередку B2 підсумуйте їх.

Постає питання: як зробити посилання на інший лист в Excel? Для реалізації цього завдання робимо таке:

Заповніть Лист1, Лист2 і Лист3 оскільки показано вище малюнку.
Перейдіть на Лист4, комірка B2.
Поставте знак «=» і перейдіть на Лист1, щоб там клацнути лівою клавішою мишки по комірці B2.
Поставте знак "+" і повторіть ті ж дії попереднього пункту, але тільки на Лист2, а потім і Лист3.
Коли формула матиме такий вигляд: = Аркуш1! B2 + Аркуш 2! B2 + Аркуш 3! B2, натисніть Enter. Результат повинен вийти такий самий, як на малюнку.

Як зробити посилання на лист в Excel?

Посилання на лист трохи відрізняється від традиційного посилання. Вона складається з 3-х елементів:

Ім'я аркуша.
Знак вигуку (служить як роздільник і допомагає візуально визначити, якого листа належить адресу осередку).
Адреса на комірку в цьому ж аркуші.

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

Посилання на лист в іншій книзі Excel

Посилання на аркуш в іншій книзі має 5 елементів.Виглядає вона так: ='C:\Docs\[Звіт.xlsx]Аркуш1'!B2.

Опис елементів посилання на іншу книгу Excel:

Шлях до файлу книги (після знака = відкривається апостроф).
Ім'я файлу книги (ім'я файлу взято у квадратні дужки).
Ім'я аркуша цієї книги (після імені закривається апостроф).
Вигук знак.
Посилання на осередок або діапазон осередків.

Це посилання слід читати так:

книга розташована на диску C:\ у папці Docs;
ім'я файлу книги "Звіт" з розширенням ".xlsx";
на «Аркуш1» в комірці B2 знаходиться значення, на яке посилається формула або функція.

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

Без функцій і формул Excel був однією великою таблицею призначеної для ручного заповнення даними. Завдяки функціям та формулам він є потужним обчислювальним інструментом. А отримані результати динамічно представляє в бажаному вигляді (якщо потрібно навіть у графічному).

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

Отримання осередків з декількох аркушів одну за одною за допомогою формули

Наприклад, вам потрібно зв'язати комірку B15 з робочого листа «Перша чверть» з поточним листом, наступна формула допоможе вам легко досягти цього. Будь ласка, зробіть таке.

1. На поточному аркуші виберіть порожню комірку, в яку ви хочете отримати значення комірки B15 листа «Перша чверть», потім введіть формулу.

= 'The First Quarter'!B15

1. У формулі «Перша чверть» – це ім'я аркуша, що містить комірку, яку необхідно вказати на інший лист, B15 – це комірка, на яку вказує посилання. Будь ласка, змініть їх відповідно до ваших потреб. 2. Якщо вміст імені аркуша розділений пробілами, помістіть ім'я аркуша в одинарні лапки у формулі. Якщо в імені листа немає прогалин, просто введіть ім'я листа без одинарних лапок.

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

Швидко отримувати осередки з кількох аркушів одночасно за допомогою Kutools for Excel

Якщо ви хочете швидко послатися на одну і ту ж комірку (каже B15) з декількох аркушів на поточний, Динамічно звертатися до робочих аркушів корисності Kutools for Excel заощадить ваш час.

Перед застосуванням Kutools for Excel, будь ласка, спочатку скачайте та встановіть.

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

2. Натисніть Kutools > Ще > Динамічно звертатися до робочих аркушів.

3. в Заповнити робочі листи Посилання діалогове вікно необхідно налаштувати так:

3.1 Вибрати Заповнювати по вертикалі осередок за осередком Заповнити замовлення список, що розкривається; 3.2 Клацніть значок Формула блокування у Формула заповнення розділ (тут відображається адреса комірки, вибрана на кроці 1); 3.3 Перевірте імена аркушів, з яких потрібно посилатися на комірки до списку робочих аркушів коробки; 3.4. Виберіть комірку на робочому аркуші призначення для виведення осередків. 3.5 Натисніть кнопку Діапазон заповнення кнопка.Дивіться скріншот:

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

3. Закрий Заповнити робочі листи Посилання діалогове вікно.

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

Як можна звернутися до осередку розташованого на іншому аркуші поточної книги в excel - Druzhba

Зазвичай ми можемо застосувати формулу = Аркуш1! A1 для відображення вмісту осередку в осередку A1 аркуша Sheet1 на інших аркушах. Але в цій статті представлені набагато простіші та зручніші способи пакетного дзеркалювання або зв'язування осередків/діапазонів на листах в Excel.

Дзеркальне відображення/зв'язування осередків на аркушах за допомогою функції From Microsoft Query

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

1. Натисніть Дані > З інших джерел > З Microsoft Query. Дивіться скріншот:

2. У діалоговому вікні Вибір джерела даних виберіть Файли Excel * у лівому полі та клацніть OK .

3. У діалоговому вікні «Вибір книги» виконайте такі дії:
(1) Виберіть диск, що містить книгу, дані з якої ви дзеркаліруєте в Диски список, що розкривається;
(2) Двічі клацніть, щоб відкрити папку, що містить книгу, з якої ви дзеркаліруєте в Довідники коробка;
(3) Клацніть, щоб виділити книгу в Ім'я бази даних пунктом.
(4) Клацніть піктограму OK .

4. З'явиться діалогове вікно із попередженням, як показано на скріншоті нижче. Клацніть піктограму OK кнопку, щоб продовжити.

5. Відкриється Майстер запитів. Клацніть піктограму Можливості кнопку, щоб відкрити кнопку Параметри таблиці, а потім перевірте Системні таблиці та натисніть OK кнопки. Дивіться скріншот:

6. Тепер ви повернетесь до майстра запитів, і листи будуть перераховані у лівому полі. Будь ласка, розгорніть робочий аркуш, з якого ви відображатимете дані в лівому полі, потім клацніть стовпець, який ви дзеркалюватимете, а потім натисніть кнопку, щоб додати його праворуч Стовпці у вашому запиті коробки. Дивіться скріншот:

Увага: ви можете відобразити/додати кілька стовпців із зазначеного робочого листа в Стовпці у вашому запиті поле та продовжуйте наступні кроки.

7. Натисніть Наступна кілька разів, доки не з'явиться діалогове вікно Імпорт даних. У діалоговому вікні «Імпорт даних» встановіть прапорець Таблиця , потім вкажіть цільовий діапазон, у який ви розмістите дзеркальні дані, та клацніть OK кнопки. Дивіться скріншот:

І тепер вказані стовпці із зазначеної книги дзеркалуються та зв'язуються з поточною книгою.

Примітка: Коли ви змінюєте значення комірок, додаєте / видаляєте рядки у вихідній книзі, дзеркальні дані в цільовому діапазоні автоматично зміняться після натискання Дані > Оновити все.

Легко поєднуйте кілька аркушів/книг в один аркуш/книгу

Об'єднання десятків аркушів із різних книг на один може виявитися стомливим заняттям. Але з Kutools for Excel's Об'єднати (робочі листи та робочі зошити) утиліту, ви можете зробити це всього за кілька кліків!

Kutools for Excel – Зміцніть Excel більш ніж 300 необхідними інструментами. Насолоджуйтесь постійно безкоштовними функціями ІІ! Get It Now

Відобразіть один і той же осередок на кількох аркушах за допомогою Kutools for Excel

Цей метод представить Kutools for Excel's Динамічно звертатися до робочих листів Утиліта для пакетного дзеркалювання однієї і тієї ж комірки на декількох аркушах на новий аркуш Excel.

1. Створити новий робочий лист, клацнувши значок Нове кнопка або на панелі вкладок аркуша.

2. Виберіть комірку на новому аркуші, вміст комірки якого ви скопіюєте з іншого аркуша, і натисніть Кутулс > Ще (у Формула група)> Динамічно посилатися на робочий лист. Дивіться скріншот:

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

І потім ви побачите, що ті ж осередки з усіх зазначених аркушів відображаються дзеркально на новому аркуші. Дивіться скріншот вище.

Дзеркальне відображення кількох діапазонів на кількох аркушах за допомогою Kutools for Excel

Цей метод представить Kutools for Excel's Майстер друку множинних виділень утиліта для відображення декількох діапазонів із багатьох аркушів у вигляді зображення на новому аркуші.

1. Натисніть Підприємство > Друк > Майстер друку множинних виділень. Дивіться скріншот:

2. У майстрі друку, що відкрився, множинних виділень натисніть кнопку Додати кнопка для повторного додавання діапазонів, а потім клацніть Завершити кнопки. Дивіться скріншот:

І тоді ви побачите, що всі вказані діапазони з кількох аркушів відображаються як зображення на новому аркуші.

Демонстрація: дзеркальне відображення / зв'язування осередків або діапазонів на аркушах Excel

Kutools for Excel: Більше 300 зручних інструментів у вас під рукою! Насолоджуйтесь постійно безкоштовними функціями ІІ! Завантажити