Можливість посилання на клітинки та діапазони - це те, що змушує працювати будь -який інструмент електронної таблиці. І Excel - найкращий і найпотужніший з усіх існуючих.
У цьому підручнику я розгляну все, що вам потрібно знати про те, як посилатися на клітинки та діапазони в Excel. Крім основних посилань на тому ж аркуші, основна частина цього підручника буде про як посилатися на інший аркуш або книгу в Excel.
Хоча немає особливої різниці в тому, як це працює, коли ви посилаєтесь на інший аркуш у цьому ж файлі або посилаєтесь на повністю окремий файл Excel, формат цього посилання дещо змінюється.
Крім того, є деякі важливі речі, про які потрібно пам’ятати, посилаючись на інший аркуш або інші зовнішні файли.
Але хвилюйтесь … нічого надто божевільного!
Поки ви закінчите з цим підручником, ви дізнаєтесь усе, що потрібно знати про посилання на клітинки та діапазони в Excel (бути в тій самій книзі чи іншій книзі).
Давайте розпочнемо!
Посилання на клітинку в тому ж аркуші
Це найпростіший рівень посилань, коли ви посилаєтесь на клітинку на тому ж аркуші.
Наприклад, якщо я перебуваю у комірці В1 і хочу звернутися до комірки А1, формат буде таким:
= A1
Коли ви це зробите, значення у комірці, де ви використовуєте це посилання, буде таким самим, як у клітинці A1. І якщо ви внесете будь -які зміни в клітинку А1, вони відображатимуться в тій комірці, де ви використовували це посилання.
Посилання на клітинку в іншому аркуші
Якщо вам потрібно посилатися на інший аркуш у тій самій книзі, вам потрібно використати такий формат:
Sheet_name! Cell_address
По -перше, у вас є ім'я аркуша, за яким стоїть знак оклику, за яким слідує посилання на клітинку.
Отже, якщо вам потрібно звернутися до комірки А1 у Аркуші 1, вам потрібно використати таку посилання:
= Аркуш1! A1
І якщо ви хочете посилатися на діапазон клітинок на іншому аркуші, вам потрібно використовувати такий формат:
Назва аркуша! Перша клітинка: Остання клітинка
Отже, якщо ви хочете звернутись до діапазону A1: C10 на іншому аркуші тієї самої книги, вам потрібно скористатися посиланням нижче:
= Аркуш1! A1: C10
Зауважте, що я показав вам лише посилання на клітинку або діапазон. Насправді ви б використовували їх у формулах. Але формат зазначених вище посилань залишиться незмінним
У багатьох випадках на робочому аркуші, на який ви посилаєтесь, у назві буде кілька слів. Наприклад, це могло бути Дані проекту або Дані продажів.
Якщо у вас є пробіли або символи без алфавіту (наприклад, @,!, #, -тощо), вам потрібно використовувати ім’я в одинарних лапках.
Наприклад, якщо ви хочете посилатись на клітинку A1 на аркуші з назвою Дані продажів, ви скористаєтесь посиланням нижче:
= 'Дані про продажі'! A1
І якщо ім’я аркуша-Дані продажів, то для посилання на клітинку А1 на цьому аркуші потрібно скористатися посиланням нижче:
= 'Дані про продажі'! A1
Коли ви звертаєтесь до аркуша в тій самій книзі, а потім пізніше змінюєте назву робочого аркуша, вам не потрібно турбуватися про те, що посилання зруйнується. Excel автоматично оновить ці посилання для вас.
Хоча чудово знати формат цих посилань, на практиці щоразу вводити їх вручну не так добре. Це займе багато часу і дуже схильне до помилок.
Дозвольте мені показати вам кращий спосіб створення посилань на клітинки в Excel.
Автоматичне створення посилання на інший аркуш у тій же книзі
Набагато кращий спосіб створити посилання на клітинку на іншому аркуші - це просто вказати Excel на клітинку/діапазон, до якого потрібно створити посилання, і дозволити Excel створити її самому.
Це гарантує, що вам не доведеться турбуватися про відсутність оклику або лапок або про будь -які інші проблеми з форматом. Excel автоматично створить для вас правильне посилання.
Нижче наведено кроки для автоматичного створення посилання на інший аркуш:
- Виберіть клітинку в поточній книзі, де вам потрібен посилання
- Введіть формулу, поки вам не знадобиться посилання (або знак рівності, якщо ви просто хочете посилання)
- Виберіть аркуш, до якого потрібно звернутися
- Виберіть клітинку/діапазон, до якого потрібно звернутися
- Натисніть Enter, щоб отримати результат формули (або продовжте роботу над формулою)
Наведені вище кроки автоматично створили б посилання на клітинку/діапазон на іншому аркуші. Ви також зможете побачити ці посилання у рядку формул. Як тільки ви закінчите, ви можете просто натиснути клавішу Enter, і це дасть вам результат.
Наприклад, якщо у вас є деякі дані у комірці A1: A10 на аркуші з назвою Дані продажів, і ви хочете отримати суму цих значень у поточному аркуші, виконайте наступні кроки:
- Введіть у поточному аркуші (де вам потрібен результат) таку формулу: = Сума (
- Виберіть аркуш "Дані про продажі".
- Виберіть діапазон, який потрібно додати (A1: A10). Як тільки ви це зробите, Excel автоматично створить посилання на цей діапазон (це можна побачити в рядку формул)
- Натисніть клавішу Enter.
Коли ви створюєте довгу формулу, вам може знадобитися звернутися до комірки або діапазону на іншому аркуші, а потім виникнути потреба повернутися до вихідного аркуша та звернутися до якоїсь комірки/діапазону там.
Коли ви це зробите, ви помітите, що Excel автоматично вставляє посилання на аркуш до аркуша, де у вас є формула. Хоча це нормально і не шкодить, це не потрібно. У такому випадку ви можете залишити посилання або видалити його вручну.
Ще одне, що вам потрібно знати під час створення посилань, вибравши аркуш, а потім клітинку/діапазон, це те, що Excel завжди створюватиме файл відносне посилання (тобто посилання зі знаком n0 $). Це означає, що якщо я скопіюю та вставлю формулу (одну з посиланням на інший аркуш) у якусь іншу клітинку, вона буде автоматично коригувати посилання.
Ось приклад, який пояснює відносні посилання.
Припустимо, я використовую таку формулу в комірці А1 поточного аркуша (для посилання на комірку А1 у назві аркуша SalesData)
= SalesData! A1
Тепер, якщо я скопіюю цю формулу та вставлю її у клітинку A2, формула зміниться на:
= SalesData! A1
Це відбувається тому, що формула відносна, і коли я копіюю та вставляю її, посилання автоматично коригуються.
Якщо я хочу, щоб це посилання завжди посилалося на клітинку A1 на аркуші SalesData, мені доведеться використати формулу нижче:
= SalesData! $ A $ 1
Знак долара перед рядком і номером стовпця блокує ці посилання, щоб вони не змінювалися.
Ось детальний підручник, де ви можете дізнатися більше про абсолютні, змішані та відносні посилання.
Тепер, коли ми розглянули, як посилатися на інший аркуш у тій самій книзі, давайте подивимось, як ми можемо посилатися на іншу книгу.
Як отримати посилання на іншу книгу в Excel
Коли ви посилаєтесь на клітинку або діапазон в іншу книгу Excel, формат цього посилання буде залежати від того, відкрита чи закрита ця книга.
І, звичайно, назва робочої книги та аркушів також відіграють певну роль у визначенні формату (залежно від того, чи є у назві пробілами чи не алфавітні символи чи ні).
Тож давайте розглянемо різні формати зовнішніх посилань на іншу книгу в різних сценаріях.
Зовнішнє посилання на відкриту книгу
Коли йдеться про посилання на зовнішню відкриту книгу, вам потрібно вказати назву книги, назву аркуша та адресу комірки/діапазону.
Нижче наведено формат, який потрібно використовувати під час посилання на зовнішню відкриту книгу
= '[Ім'я файлу] Ім'я аркуша! Адреса клітинки
Припустимо, у вас є робоча книга "ExampleFile.xlsx", і ви хочете звернутися до комірки A1 у Таблиці1 цієї книги.
Нижче наведено посилання на це:
= [ExampleFile.xlsx] SalesData! A1
Якщо у назві зовнішньої книги чи назви аркуша (або обох) є пробіли, вам потрібно додати назву файлу (у квадратних дужках) та назву аркуша в одинарних лапках.
Нижче наведені приклади, коли імена потрібно мати в одинарних лапках:
= '[Приклад файлу.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Дані продажів'! A1 = '[Приклад файлу.xlsx] Дані продажів'! A1
Як створити посилання на іншу книгу (автоматично)
Знову ж таки, хоча добре знати формат, краще не вводити його вручну.
Натомість просто наведіть Excel у правильному напрямку, і він створить для вас ці посилання. Це набагато швидше з меншою ймовірністю помилок.
Наприклад, якщо у вас є деякі дані у клітинці A1: A10 у книзі під назвою "Файл прикладу" на аркуші з назвою "Дані про продажі", і ви хочете отримати суму цих значень у поточному аркуші, наступні кроки будуть наступними :
- Введіть у поточному аркуші (де вам потрібен результат) таку формулу: = Сума (
- Перейдіть до книги «Файл прикладу»
- Виберіть аркуш "Дані про продажі".
- Виберіть діапазон, який потрібно додати (A1: A10). Як тільки ви це зробите, Excel автоматично створить посилання на цей діапазон (це можна побачити в рядку формул)
- Натисніть клавішу Enter.
Це негайно створить формулу з правильними посиланнями.
Одне, що ви помітите, створюючи посилання на зовнішню книгу, це те, що вона завжди створюватиме абсолютні посилання. Це означає, що перед номерами рядків і стовпців є знак $. Це означає, що якщо ви скопіюєте та вставте цю формулу в інші клітинки, вона буде посилатися на той самий діапазон через абсолютну посилання.
Якщо ви хочете, щоб це змінилося, вам потрібно змінити посилання вручну.
Зовнішнє посилання на закриту книгу
Коли відкрита зовнішня книга і ви звертаєтесь до цієї книги, вам просто потрібно вказати ім’я файлу, назву аркуша та адресу комірки/діапазону.
Але коли це закрито, Excel не має уявлення, де шукати осередки/діапазон, на які ви посилалися.
Ось чому, коли ви створюєте посилання на закриту книгу, вам також потрібно вказати шлях до файлу.
Нижче наведено посилання, яке посилається на клітинку A1 на аркуші Sheet1 у книзі прикладів файлів. Оскільки цей файл не відкритий, він також посилається на місце зберігання файлу.
= 'C: \ Users \ sumit \ Desktop \ [Приклад файлу.xlsx] Аркуш1'! $ A $ 1
Вищезгадане посилання має такі частини:
- Шлях до файлу - розташування у вашій системі або мережі, де знаходиться зовнішній файл
- Ім'я файлу - назва зовнішньої книги. Це також включатиме розширення файлу.
- Назва аркуша - назва аркуша, у якому ви посилаєтесь на клітинки/діапазони
- Адреса комірки/діапазону - точна адреса комірки/діапазону, на яку ви посилаєтесь
Коли ви створюєте зовнішнє посилання на відкриту книгу, а потім закриваєте книгу, ви помітите, що посилання автоматично змінюється. Після закриття зовнішньої книги Excel також автоматично вставляє посилання на шлях до файлу.
Вплив зміни розташування файлу на посилання
Коли ви створюєте посилання на клітинку/діапазон у зовнішньому файлі Excel, а потім закриваєте його, посилання тепер також використовує шлях до файлу.
Але тоді, якщо ви зміните розташування файлу, у вашій книзі (у якій ви створюєте посилання) нічого не зміниться. Але оскільки ви змінили місцезнаходження, посилання тепер не працює.
Тож якщо ви закриєте та відкриєте цю книгу, вона повідомить вам, що посилання не працює, і вам потрібно або оновити посилання, або повністю його розірвати. Він покаже вам підказку, як показано нижче:
Коли ви натискаєте «Оновити», він покаже вам інший запит, у якому ви зможете вибрати параметри редагування посилань (що покаже вам діалогове вікно нижче)
Якщо вам потрібно, щоб ці файли залишалися зв’язаними, ви можете вказати нове місце розташування файлу, натиснувши «Оновити значення». Excel відкриває для вас діалогове вікно, де ви можете вказати нове місце розташування файлу, перейшовши туди та вибравши його.
Посилання на визначене ім'я (у тій самій або зовнішній книзі)
Коли вам доводиться посилатися на клітинки та діапазони, кращим способом є створення визначених імен для діапазонів.
Це корисно, оскільки полегшує посилання на ці діапазони, використовуючи ім’я, а не довгу та складну довідкову адресу.
Наприклад, простіше використовувати = SalesData замість = [Приклад файлу.xlsx] Sheet1 ′! $ A $ 1: $ A $ 10
І якщо ви використовували це визначене ім’я у кількох формулах і вам потрібно змінити посилання, вам потрібно це зробити лише один раз.
Ось кроки для створення іменованого діапазону для діапазону клітинок:
- Виберіть усі клітинки, які потрібно включити до іменованого діапазону
- Перейдіть на вкладку Формули
- Натисніть на опцію Визначити назву (вона знаходиться в групі Визначені імена)
- У діалоговому вікні «Нова назва» дайте цьому діапазону ім’я (у цьому прикладі я використовую ім’я SalesData). Пам’ятайте, що у назві не може бути пробілів
- Збережіть область дії як книгу (якщо у вас немає вагомих причин зробити її аркушем на рівні аркуша)
- Переконайтеся, що посилання на діапазон правильні.
- Натисніть OK.
Тепер ваш іменований діапазон створено, і ви можете використовувати його замість посилань на клітинки з адресами комірок.
Наприклад, якщо я хочу отримати суму всіх цих комірок у діапазоні SalesData, ви можете скористатися формулою нижче:
= SUM (SalesData)
А що, якщо ви хочете використовувати цей іменований діапазон - це інші аркуші чи навіть інші книги?
Ти можеш!
Вам просто потрібно дотримуватися того самого формату, який ми обговорювали у вищенаведеному розділі.
Не потрібно повертатися до початку цієї статті. Дозвольте мені навести всі приклади тут, щоб ви зрозуміли це.
Робочий зошит проти робочого аркуша Рівень іменованих діапазонів: Під час створення рівня книги з іменем діапазон може бути лише один діапазон з такою назвою. Тому, якщо ви посилаєтесь на нього із зовнішніх робочих книг або аркушів у тій самій книзі, вам не потрібно вказувати назву аркуша (оскільки він доступний для використання у всій книзі). Якщо ви використовуєте іменований діапазон рівня аркуша, ви зможете використовувати його лише на робочому аркуші, і, посилаючись на нього, вам також доведеться вказати назву аркуша.Посилання на визначене ім'я в тому ж робочому аркуші або книзі
Якщо ви створили визначене ім’я для рівня книги, ви можете використовувати його в будь -якому місці книги, просто використовуючи саме визначене ім’я.
Наприклад, якщо я хочу отримати суму всіх комірок у створеному нами іменованому діапазоні (SaledData), я можу використати формулу нижче:
= SUM (SaledData)
Якщо ви створили рівень робочого аркуша з іменем діапазон, ви можете використовувати цю формулу, лише якщо іменований діапазон створено на тому ж аркуші, де ви використовуєте формулу.
Якщо ви хочете використовувати його на іншому аркуші (скажімо, Sheet2), вам потрібно використати таку формулу:
= SUM (Аркуш 1! $ A $ 1: $ A $ 10)
І якщо у назві аркуша є пробіли або буквено -цифрові символи, вам доведеться помістити назву аркуша в одинарні лапки.
= SUM ('Аркуш 1'! $ A $ 1: $ A $ 10)
Посилання на визначене ім’я в іншій книзі (відкритій чи закритій)
Якщо ви хочете посилатися на іменований діапазон в іншій книзі, вам доведеться вказати ім’я книги, а потім назву діапазону.
Наприклад, якщо у вас є книга Excel з назвою ExampleFile.xlsx та іменований діапазон із назвою SalesData, то ви можете використати формулу нижче, щоб отримати суму цього діапазону з іншої книги:
= SUM (ExampleFile.xlsx! SalesData)
Якщо у назві файлу є пробіли, їх потрібно використовувати в одинарних лапках.
= SUM ('Приклад файлу.xlsx'! SalesData)
Якщо у вас є іменовані діапазони на рівні аркуша, вам потрібно вказати ім’я робочої книги, а також аркуша, коли посилаєтесь на нього із зовнішньої книги.
Нижче наведено приклад посилання на діапазон імен на рівні аркуша:
= SUM ('[Приклад файлу.xlsx] Аркуш1'! SalesData)
Як я вже згадував вище, завжди найкраще створювати діапазони імен рівня робочої книги, якщо у вас немає вагомих причин для створення першого рівня аркуша.
Якщо ви посилаєтесь на іменований діапазон у закритій книзі, вам також доведеться вказати шлях до файлу. Нижче наведено приклад цього:
= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)
Коли ви створюєте посилання на іменований діапазон у відкритій книзі, а потім закриваєте книгу, Excel автоматично змінює посилання та додає шлях до файлу.
Як створити посилання на іменований діапазон
Якщо ви створюєте та працюєте з великою кількістю іменованих діапазонів, неможливо запам’ятати ім’я кожного з них.
Excel допомагає вам, показуючи список усіх іменованих діапазонів, які ви створили, і дозволяє вставляти їх у формули одним клацанням миші.
Припустимо, ви створили іменований діапазон SalesData, який хочете використати у формулі, щоб підсумувати всі значення в іменованому діапазоні.
Нижче наведено кроки для цього.
- Виберіть клітинку, в яку потрібно ввести формулу.
- Введіть формулу до точки, де потрібно вставити іменований діапазон
- Натисніть клавішу F3 на клавіатурі. Відкриється діалогове вікно Вставити назву зі списком усіх створених вами імен
- Двічі клацніть ім'я, яке потрібно вставити.
Наведені вище кроки додадуть ім’я до формули, і ви зможете продовжити роботу над формулою.
Примітка: Ця техніка працює лише для діапазонів імен, які є в одній книзі. Якщо ви хочете отримати імена у зовнішній книзі, вам доведеться зайти туди, перевірити ім’я за допомогою діалогового вікна Вставити ім’я, а потім повернутися і використати його у своїй формулі (у правильному форматі з використанням назви файлу).Це все, що вам потрібно знати про те, як посилатися на інші аркуші чи книги та як створити зовнішнє посилання в Excel.
Сподіваюся, цей підручник був вам корисним.
