зміст
Чи є у вас таблиці з даними в Excel, розмір яких можна змінювати, тобто кількість рядків (стовпців) може збільшуватися або зменшуватися в процесі роботи? Якщо розміри таблиці «плавають», то доведеться постійно контролювати цей момент і виправляти його:
- посилання у формулах звітів, які посилаються на нашу таблицю
- початкові діапазони зведених таблиць, які будуються за нашою таблицею
- початкові діапазони діаграм, побудованих за нашою табл
- діапазони для спадних меню, які використовують нашу таблицю як джерело даних
Все це в сукупності не дасть вам нудьгувати😉
Набагато зручніше і правильніше буде створити динамічний «гумовий» діапазон, який буде автоматично підлаштовуватися за розміром під реальну кількість рядків і стовпців даних. Для реалізації цього є кілька способів.
Спосіб 1. Розумний стіл
Виділіть діапазон комірок і виберіть із вкладки Домашня сторінка – форматувати як таблицю (домашня сторінка – форматувати як таблицю):
Якщо вам не потрібен смугастий дизайн, який додається до таблиці як побічний ефект, ви можете вимкнути його на вкладці, що з’явиться Конструктор (дизайн). Кожна таблиця, створена таким чином, отримує назву, яку можна замінити на більш зручну в тому ж місці вкладки Конструктор (дизайн) в польових умовах назва таблиці (Назва таблиці).
Тепер ми можемо використовувати динамічні посилання на нашу «розумну таблицю»:
- Таблиця 1 – посилання на всю таблицю, за винятком рядка заголовка (A2:D5)
- Таблиця1[#Усі] – посилання на всю таблицю (A1:D5)
- Таблиця1[Пітер] – посилання на стовпець діапазону без першого заголовка клітинки (C2:C5)
- Таблиця 1[#Заголовки] – посилання на «шапку» з назвами колонок (A1:D1)
Такі посилання чудово працюють у формулах, наприклад:
= SUM (Таблиця1[Москва]) – розрахунок суми для графи «Москва»
or
=VPR(F5;Таблиця 1;3;0) – шукати в таблиці місяць з комірки F5 і видавати для нього пітерську суму (що таке VLOOKUP?)
Такі посилання можна успішно використовувати при створенні зведених таблиць шляхом вибору на вкладці Вставити – зведену таблицю (Вставити – зведену таблицю) і введення назви смарт-таблиці як джерела даних:
Якщо виділити фрагмент такої таблиці (наприклад, перші два стовпці) і створити діаграму будь-якого типу, то при додаванні нових рядків вони автоматично додаватимуться до діаграми.
Під час створення розкривних списків не можна використовувати прямі посилання на елементи розумної таблиці, але ви можете легко обійти це обмеження за допомогою тактичної хитрощі – скористайтеся функцією НЕПРАВИЛЬНО (НЕПРЯМИЙ), який перетворює текст на посилання:
Ті. посилання на смарт-таблицю у вигляді текстового рядка (в лапках!) перетворюється на повноцінне посилання, і випадаючий список її нормально сприймає.
Спосіб 2: Динамічний іменований діапазон
Якщо перетворювати ваші дані в розумну таблицю з якихось причин небажано, то ви можете скористатися трохи складнішим, але набагато більш тонким і універсальним методом – створити в Excel динамічний іменований діапазон, який посилається на нашу таблицю. Тоді, як і у випадку з розумною таблицею, ви можете вільно використовувати назву створеного діапазону в будь-яких формулах, звітах, діаграмах тощо. Почнемо з простого прикладу:
Завдання: створіть динамічний іменований діапазон, який посилатиметься на список міст і автоматично розтягуватиметься та зменшуватиметься під час додавання чи видалення нових міст.
Нам знадобляться дві вбудовані функції Excel, доступні в будь-якій версії − POICPOZ (МАТЧ) щоб визначити останню клітинку діапазону, і ІНДЕКС (ІНДЕКС) щоб створити динамічне посилання.
Пошук останньої клітинки за допомогою MATCH
MATCH(пошукове_значення, діапазон, тип_відповідності) – функція, яка шукає задане значення в діапазоні (рядок або стовпець) і повертає порядковий номер комірки, де воно знайдено. Наприклад, формула MATCH(“March”;A1:A5;0) у результаті поверне число 4, оскільки слово “March” розташовано в четвертій клітинці стовпця A1:A5. Останній аргумент функції Match_Type = 0 означає, що ми шукаємо точну відповідність. Якщо цей аргумент не вказано, то функція перейде в режим пошуку найближчого найменшого значення – це саме те, що можна успішно використовувати для пошуку останньої зайнятої комірки в нашому масиві.
Суть трюку проста. MATCH шукає комірки в діапазоні зверху вниз і, теоретично, має зупинятися, коли знаходить найближче найменше значення до заданого. Якщо ви вкажете значення, яке явно більше будь-якого наявного в таблиці, як потрібне значення, тоді MATCH досягне самого кінця таблиці, нічого не знайде та видасть порядковий номер останньої заповненої комірки. А нам це потрібно!
Якщо в нашому масиві є тільки числа, то ми можемо вказати число як потрібне значення, яке явно більше за будь-яке з таблиці:
Для гарантії можна використовувати число 9E + 307 (9 помножити на 10 у ступені 307, тобто 9 з 307 нулями) – максимальне число, з яким в принципі може працювати Excel.
Якщо в нашому стовпці є текстові значення, то в якості еквівалента максимально можливого числа можна вставити конструкцію REPEAT(“i”, 255) – текстовий рядок, що складається з 255 букв “i” – останньої букви алфавіт. Оскільки Excel фактично порівнює коди символів під час пошуку, технічно будь-який текст у нашій таблиці буде «меншим», ніж такий довгий рядок «yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
Створіть посилання за допомогою INDEX
Тепер, коли ми знаємо позицію останнього непорожнього елемента в таблиці, залишилося сформувати посилання на весь наш діапазон. Для цього використовуємо функцію:
INDEX(діапазон; номер_рядка; номер_стовпця)
Він видає вміст комірки з діапазону за номером рядка та стовпця, тобто, наприклад, функція =INDEX(A1:D5;3;4) у нашій таблиці з містами та місяцями з попереднього методу видасть 1240 – вміст з 3-го рядка і 4-го стовпця, тобто комірки D3. Якщо стовпець тільки один, то його номер можна не вказувати, тобто формула INDEX(A2:A6;3) дасть на останньому скріншоті «Самара».
І є один не зовсім очевидний нюанс: якщо ІНДЕКС не просто вводиться в комірку після знака =, як зазвичай, а використовується як кінцева частина посилання на діапазон після двокрапки, то він вже не видає вміст комірки, але її адреса! Таким чином, формула на зразок $A$2:INDEX($A$2:$A$100;3) дасть посилання на діапазон A2:A4 на виході.
І саме тут з’являється функція MATCH, яку ми вставляємо всередину INDEX для динамічного визначення кінця списку:
=$A$2:ІНДЕКС($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Створіть іменований діапазон
Залишилося упакувати це все в єдине ціле. Відкрийте вкладку формула (Формули) І натисніть кнопку Менеджер імен (Менеджер імен). У вікні, що відкриється, натисніть кнопку Створювати (новий), введіть у поле назву діапазону та формулу Діапазон (Довідка):
Залишилося натиснути на OK а готовий діапазон можна використовувати в будь-яких формулах, випадаючих списках або діаграмах.
- Використання функції VLOOKUP для зв’язування таблиць і значень пошуку
- Як створити спадний список, що автоматично заповнюється
- Як створити зведену таблицю для аналізу великої кількості даних