Як зв’язати ваші дані з різних книг в excel
У міру того як ви використовуєте і створюєте більше робочих документів Excel, у вас може виникнути необхідність зв'язати їх між собою. Може бути ви захочете написати формулу, яка використовує дані з різних Листів. Або може навіть ви напишете формулу, яка буде використовувати дані з різних Книг (стандартну назву робочого документа Excel - workbook (анг.))
Я зрозумів, що якщо я хочу, щоб мої файли були зрозумілими і акуратними, краще розділяти великі Листи з даними від формул, які їх обробляють. Я часто використовую одну Книгу або Лист, який обробляє всі що мені потрібно.
У цьому уроці, ви дізнаєтеся як додавати посилання на дані в Excel. Перш за все, ми навчимося пов'язувати дані з різних Листів в межах однієї Книги. Потім, ми перейдемо до зв'язування декількох Книг Excel, щоб імпортувати і синхронізувати дані між файлами.
Як Можна Швидко Організувати Посилання Між Даними в Книгах Excel (Дивись і Вчися)
Я покажу вам два приклади, як можна пов'язати дані з ваших електронних таблиць. Ви дізнаєтеся як витягувати дані з іншої таблиці в Excel і як організувати зв'язок між двома Книгами. Ми також розглянемо основні приклади написання формул, що використовують дані з різних Листів в межах одного робочого документа.
Давайте подивимося ілюстроване керівництво по зв'язуванню Листів і Книг в Excel.
Основи: Як Додати Посилання між Листами в Excel
Давайте почнемо з того, що навчимося створювати формули, які використовують дані з інших Листів. Ви можливо вже знаєте, що робочий документ (Книга) Excel, може містити кілька робочих сторінок (Листів). Кожен лист має свою вкладку, і ви можете перемикатися між ними клацаючи по вкладках в нижній частині документа Excel.
Складні документи можуть містити велику кількість листів. І в якийсь час, вам доведеться написати формулу яка буде використовувати дані з різних листів.
Урок за 60 секунд: Як Вставляти, Видаляти і Приховувати Листи в Excel
Може ви будете використовувати один аркуш у документі для всіх ваших формул, які обробляють дані, а дані будете розподіляти по окремим листам.
У моєму документі три вкладки з Листами (Sheet). Я збираюся написати формулу, яка буде працювати з даними з кожного листа.
Давайте розберемося як написати багатосторінкову формулу, яка працює з даними з різних Листів в межах одного робочого документа.
1. Створіть в Excel нову Формулу
Багато формули в Excel починаються зі знака рівності (=). Двічі клацніть або почніть друкувати в осередку, і ви почнете створювати формулу, в яку ви хочете вставити посилання. Наприклад, я збираюся написати формулу, яка буде підсумовувати значення з різних осередків.
Як Працювати з Математичними Формулами в Excel (Основи)
Я відкриваю запис знаком =. і потім клацаю по першій клітинці на поточному аркуші, щоб створити першу частину формули. Потім, я надрукую знак +. щоб додати значення з другого осередку в цій формулі.
Ще не завершуйте введення формули і не тисніть Enter! Вам потрібно залишити формулу відкритої перед тим як ви перейдіть на інший Лист.
2. Перейдіть між Листами в Excel
Поки ваша формула не закінчена, клацніть на вкладці іншого листа в нижній частині програми. Дуже важливо, щоб ви не закривали формулу, перед тим як ви клацніть по іншому осередку, яку ви хочете вставити в формулу.
Перейдіть на інший Лист Excel
Після того, як ви перейдіть на інший аркуш, клацніть по комірці яку ви хочете додати в формулу. Як ви можете бачити на зображенні нижче, Excel автоматично запише частину формули, яка звертатиметься до комірки на іншому Листі.
Зауважте на зображенні нижче, що для посилання на осередок на іншому Листі, Excel додав напис "Sheet2! B3", яка посилається на клітинку В3 на аркуші з назвою Sheet2. Ви можете написати це вручну, але коли в клацаєте по осередку, Excel робить цей напис автоматично за вас.
3. Закрийте Excel формулу
Тепер, ви можете натиснути enter. щоб завершити створення мульти-сторінкової формули. Коли ви це робите, Excel повернеться туди, де ви почали створювати формулу і покаже вам результат.
Ви можете продовжувати набирати формулу, включаючи в неї осередки з інших листів і інші осередки на цьому ж аркуші. Продовжуйте комбінувати посилання з усього робочого документа, на всі дані, які вам потрібні.
Підвищуємо Рівень Складнощі: Як Створити Зв'язок Між Кількома Книгами Excel
Давайте дізнаємося, як витягти дані з іншого робочого документа. За допомогою цієї навички, ви зможете писати формул, які зв'язують дані з різних робочих документів Excel.
Для цього розділу уроку, вам знадобиться два робочих документа, які ви можете завантажити у вигляді безкоштовного додатку до цього уроку. Відкрийте їх обидва в Excel, і слідуйте подальшим вказівкам.
1. Відкрийте Дві Робочі Книги
Давайте почнемо створювати формулу, яка буде використовувати дані з двох різних документів.
Найпростіший спосіб зробити це, це відкрити два документа Excel, одночасно і розташувати їх поруч один з одним. Я використовую для цього функцію Windows Snap, щоб кожне вікно займало пів екрану. Вам потрібно мати дві книги перед собою, щоб писати формулу, яка буде взаємодіяти з двома Книгами.
На малюнку нижче, я відкрив два робочих документа один поруч з іншим, для яких я буду створювати формули. У моєму прикладі, я веду бізнес, і купую різні продукти, і продаю їх в різні країни. Отже, я використовую різні робочі документи, щоб відстежувати покупки / продажу і дані за вартістю.
2. Почніть Створювати Формулу в Excel
Ціна за яку я купую товари, може змінитися, а так само може змінитися співвідношення курсів до валюти в якій я отримую виплати. Тому мені потрібно мати список в якому відслідковуються курси валют і множити їх на вартість моїх закупівель. Це відповідна можливість, щоб використовувати інформацію з двох файлів і записати формулу, яка буде використовувати дані обох файлів.
Давайте розглянемо число барелів нафти яку я купую щомісяця і ціну за барель. Спочатку в осередку з колонки Cost (осередок C3), я починаю створювати формулу, клацнувши в ній і натиснувши знак рівності (=), а потім клацаю по осередку В3. щоб взяти значення для кількості. Тепер, я тисну знак *. щоб уможножіть кількість на курс.
Отже, ваша формула, поки повинна виглядати так:
Чи не закривайте поки вашу формулу. Переконайтеся, що вона залишилася відкритою перед тим як перейти до наступного кроку; нам потрібно вказати Excel дані за ціною, на яку буде множитися кількість.
3. Перейдіть на Інший Робочий Документ Excel
Прийшов час переключитися на інший робочий документ, ось чому так важливо мати дані з двох документів між собою, коли ви працюєте з двома Книгами.
Поки ваша формула відкрита, клацніть по вкладці інший книги. Потім, клацніть по комірці в вашій другій Книзі, щоб зв'язати дані з двох файлів Excel.
Excel автоматично додасть посилання на інший робочий документа, як частина формули:
Після того як ви натиснете Enter, Excel автоматично розрахує остаточну вартість помноживши кількість в першій книзі на ціну з другої книги.
Потренуйте свої навички роботи в Excel, множачи кількість товару або обсяг на відповідну йому ціну з документа "Prices".
Головне, розташувати книги одну поряд з іншою, і створювати формулу перемикаючись між ними.
При цьому, нічого не заважає вам використовувати більше, ніж два робочих документа. Ви можете відкрити багато робочих документів, на які ви хочете посилатися, і створювати формули, що зв'язують дані між безліччю листів.
Як Оновлювати Дані При Роботі з Кількома Книгами
Коли ви використовуєте формули, які посилаються на різні книги Excel, вам потрібно подбати про те, як будуть оновлюватися дані.
Отже, що ж станеться, якщо дані в Книзі, на яку ви посилаєтеся, поміняються? Чи будуть виконані автоматичні зміни у вашій Книзі, або вам потрібно оновлювати ваші файли, щоб витягти останні зміни та імпортувати їх?
Відповідь така, "залежить", і в основному залежить від того, чи відкриті два документа одночасно.
Приклад 1: Відкрито Обидві Книги Excel
Давайте перевіримо приклад використовуючи ті ж книги, що ми використовували раніше. Обидві книги відкриті. Давайте подивимося, що станеться, якщо ми змінимо ціну нафти за барель з 45 $ на 75 $:
На сріншоте нижче, ви можете бачити, що коли ми змінили ціну на нафту, результати в іншому документі автоматично помінялися.
Важливо пам'ятати: якщо обидва робочих документа відкриті одночасно, зміни будуть внесені автоматично в реальному часі. Коли ви змінюєте змінну, то інформація в іншому документі буде автоматично змінена або перерахована, на підставі нових даних.
Приклад 2: Якщо Один Робочий Документ Закрито
А що якщо ви відкрили тільки один робочий документ? Наприклад, щоранку ми оновлюємо ціни на наші товари і валюти, а ввечері ми розглядаємо вплив змін на наші покупки і продажу.
Наступного разу, коли ви відкриєте документ, який посилається на інші таблиці, ви повинні побачити повідомлення подібне до того, що наведено нижче. Ви можете клацнути по кнопці Відновити (Update), щоб витягти полседніе дані з документа, на який посилається формула.
Ви так само можете бачити меню, де можна натиснути на копку Включити вміст (Enable Content), щоб автоматично оновлювати дані між файлами Excel.
Підводимо підсумки і продовжуємо вчитися роботі в Excell
Написання формул які беруть дані з різних Листів і Книг, важливий навик, при роботі в Excel. Тим більше, що це зовсім не складно, якщо ви знаєте як це робити.
Подивіться ці додаткові уроки, щоб дізнатися більше про те як працювати з програмою Excel і як обробляти в ній дані. Ці уроки, добре підійдуть для того, щоб продовжити навчання.
Як Захистити Осередки, Листи, і Робочі Документи в Excel
Як Створена в Microsoft Excel Cвою Першу Зведену таблицю
Як Працювати з Датою і Часом у формулі Excel