Перехресні посилання в Excel 2010
Коли інформація розкидана за декількома різними таблицями, може бути складним завданням об'єднати всі ці різні набори даних в один значний список або таблицю. Це де функція Vlookup входить у свої права.
ВВР
VlookUp шукає значення вертикально донизу для довідкової таблиці. VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) має 4 параметри, як показано нижче.
- lookup_value — це введення користувача. Це значення, яке функція використовує для пошуку.
- Table_array - Це область осередків, в якій розташована таблиця. Це включає не тільки стовпець, в якому виконується пошук, але і стовпці даних, для яких ви хочете отримати потрібні значення.
- Col_index_num - Це стовпець даних, який містить відповідь, яку ви хочете.
- Range_lookup - Це значення ІСТИНА або БРЕХНЯ. Коли встановлено значення TRUE, функція пошуку дає найближчий збіг до lookup_value, не пропускаючи lookup_value. Якщо встановлено значення FALSE, необхідно знайти точний збіг з lookup_value, інакше функція поверне #N/A. Зверніть увагу, що для цього необхідно, щоб стовпець, що містить lookup_value, був відформатований у порядку зростання.
lookup_value — це введення користувача. Це значення, яке функція використовує для пошуку.
Table_array - Це область осередків, в якій розташована таблиця. Це включає не тільки стовпець, в якому виконується пошук, але і стовпці даних, для яких ви хочете отримати потрібні значення.
Col_index_num - Це стовпець даних, який містить відповідь, яку ви хочете.
Range_lookup - Це значення ІСТИНА або БРЕХНЯ.Коли встановлено значення TRUE, функція пошуку дає найближчий збіг до lookup_value, не пропускаючи lookup_value. Якщо встановлено значення FALSE, необхідно знайти точний збіг з lookup_value, інакше функція поверне #N/A. Зверніть увагу, що для цього необхідно, щоб стовпець, що містить lookup_value, був відформатований у порядку зростання.
VLOOKUP Приклад
Погляньмо на дуже простий приклад перехресних посилань на дві таблиці. Кожна електронна таблиця містить інформацію про одну й ту саму групу людей. Перша таблиця має дати народження, а друга показує їх улюблений колір. Як створити список, що показує ім'я людини, її дату народження та її улюблений колір? ВЛОООКУП допоможе в цьому випадку. Насамперед, давайте подивимося дані в обох аркушах.
Це дані у першому аркуші
Це дані на другому аркуші
Тепер, щоб знайти відповідний улюблений колір для цієї людини з іншого аркуша, нам потрібно переглянути дані. Першим аргументом VLOOKUP є значення пошуку (у разі це ім'я людини). Другим аргументом є масив таблиць, який є таблицею другого листа від B2 до C11. Третій аргумент VLOOKUP це індекс стовпця num, відповідь на який ми шукаємо. У цьому випадку це 2, номер стовпця кольору дорівнює 2. Четвертий аргумент - True, що повертає частковий збіг, або false, що повертає точний збіг. Після застосування формули VLOOKUP він обчислить колір і результати відобразяться, як показано нижче.
Як ви можете бачити на знімку екрана, результати VLOOKUP шукали колір у другій таблиці аркушів. Він повернув #N/A, якщо збіг не знайдено. У цьому випадку дані Енді відсутні на другому аркуші, тому вони повернули #N/A.
Як зробити перехресні посилання на комірки у таблицях Microsoft Excel
У Microsoft Excel часто доводиться посилатися на комірки інших аркушах і навіть у різних файлах Excel. Спочатку це може здатися трохи складним і заплутаним, але як тільки ви зрозумієте, як це працює, це не так вже й складно.
У цій статті ми розглянемо, як посилатися на інший аркуш у тому самому файлі Excel і як посилатися на інший файл Excel. Ми також розповімо про те, як посилатися на діапазон осередків у функції, як спростити завдання за допомогою певних імен та як використовувати ВПР для динамічних посилань.
Як послатися на інший лист у тому ж файлі Excel
Базове посилання на комірку записується у вигляді літери стовпця, за якою слідує номер рядка.
Таким чином, посилання на комірку B3 відноситься до комірки на перетині стовпця B і рядка 3.
При посиланні на комірки на інших аркушах це посилання на комірку передує ім'я іншого листа. Наприклад, нижче наведено посилання на комірку B3 на аркуші під назвою «Січень».
Знак оклику (!) Відокремлює ім'я аркуша від адреси осередку.
Якщо ім'я аркуша містить пробіли, ви повинні укласти ім'я в одинарні лапки у засланні.
Щоб створити ці посилання, ви можете ввести їх прямо в комірку. Однак простіше і надійніше дозволити Excel написати довідкову інформацію.
Введіть знак рівності (=) у комірку, клацніть вкладку «Лист», а потім клацніть комірку, на яку необхідно створити перехресне посилання.
У міру того, як ви це робите, Excel записує для вас посилання на панель формул.
Натисніть клавішу Enter, щоб заповнити формулу.
Як послатися на інший файл Excel
Ви можете посилатися на осередки іншої книги, використовуючи той самий метод.Просто переконайтеся, що ви відкрили інший файл Excel, перш ніж починати вводити формулу.
Введіть знак рівності (=), перейдіть на інший файл і клацніть комірку в цьому файлі, на яку хочете послатися. Коли закінчите, натисніть клавішу Enter.
Заповнене перехресне посилання містить ім'я іншої книги, укладене у квадратні дужки, за яким йдуть ім'я аркуша та номер комірки.
=[Chicago.xlsx]January!B3
Якщо ім'я файлу або аркуша містить пробіли, вам необхідно укласти посилання на файл (включаючи квадратні дужки) в одинарні лапки.
='[New York.xlsx]January'!B3
У цьому прикладі ви можете побачити знаки долара ($) серед адрес комірки. Це абсолютне посилання на комірку (дізнайтеся більше про абсолютні посилання на комірки).
При посиланні на комірки та діапазони у різних файлах Excel за умовчанням посилання робляться абсолютними. У разі потреби ви можете змінити це на відносне посилання.
Якщо ви подивитеся на формулу, коли посилання на книгу закрито, вона міститиме повний шлях до цього файлу.
Хоча створення посилань на інші книги нескладно, вони більш схильні до проблем. Користувачі, які створюють або перейменовують папки та переміщують файли, можуть порушити ці посилання та викликати помилки.
По можливості, надійніше зберігати дані в одній книзі.
Як зробити перехресне посилання на діапазон осередків у функції
Посилання на одну комірку досить корисне. Але ви можете захотіти написати функцію (наприклад, SUM), яка посилається на діапазон осередків на іншому аркуші чи книзі.
Запустіть функцію як завжди, а потім клацніть аркуш і діапазон осередків так само, як ви робили в попередніх прикладах.
У наступному прикладі функція СУМ сумує значення з діапазону B2: B6 на аркуші з ім'ям Sales.
Як використовувати певні імена для простих перехресних посилань
В Excel ви можете присвоїти ім'я осередку або діапазону осередків. Це більш значуще, ніж адреса осередку або діапазону, коли ви на них дивитеся. Якщо ви використовуєте багато посилань у електронній таблиці, присвоєння їм імен може значно спростити перегляд того, що ви зробили.
Більше того, це ім'я є унікальним для всіх аркушів у цьому файлі Excel.
Наприклад, ми могли б назвати комірку «ChicagoTotal», і тоді перехресне посилання виглядатиме так:
Це більш значуща альтернатива такому стандартному засланню:
Створити певну назву легко. Почніть із вибору осередку або діапазону осередків, яким ви хочете присвоїти ім'я.
Клацніть поле імені у верхньому лівому куті, введіть ім'я, яке хочете призначити, і натисніть клавішу ENTER.
Під час створення певних імен ви не можете використовувати пробіли. Тому в цьому прикладі слова були об'єднані в ім'я та розділені великою літерою. Ви також можете розділяти слова такими символами, як дефіс (-) або підкреслення (_).
Excel також має диспетчер імен, який спрощує відстеження цих імен у майбутньому. Клацніть Формули> Менеджер імен. У вікні диспетчера імен можна побачити список усіх певних імен у книзі, де вони знаходяться і які значення вони зберігають в даний час.
Потім можна використовувати кнопки вгорі для редагування та видалення цих певних імен.
Як відформатувати дані у вигляді таблиці
Працюючи з великим списком пов'язаних даних використання функції Excel Format as Table може спростити спосіб посилання дані у ній.
Візьміть наступну просту таблицю.
Його можна відформатувати як таблиці.
Клацніть комірку у списку, перейдіть на вкладку «Головна», натисніть кнопку «Форматувати як таблицю», а потім виберіть стиль.
Переконайтеся, що діапазон комірок правильний і що у таблиці є заголовки.
Потім ви можете надати своїй таблиці осмислене ім'я на вкладці «Дизайн».
Потім, якщо нам потрібно підсумовувати продажі Чикаго, ми могли б посилатися на таблицю по її імені (з будь-якого аркуша), за яким слідує квадратна дужка ([) to see a list of table's columns.
Виберіть column за двома-натисніть його в листі і введіть closing square bracket. В результаті формуляра буде йти деякий час як це:
Ви можете побачити, як таблиці можуть спростити звернення до даних для функцій агрегування, таких як SUM та AVERAGE порівняно зі стандартними посиланнями на аркуші.
Ця таблиця є невеликою для демонстрації. Чим більше таблиця і що більше аркушів у книзі, тим більше переваг ви побачите.
Як використовувати функцію VLOOKUP для динамічних посилань
Всі посилання, використані в прикладах досі, були прив'язані до певного осередку або діапазону осередків. Це чудово і часто буває достатньо для ваших потреб.
Однак якщо осередок, на який ви посилаєтеся, може змінитися при вставці нових рядків або деяких
