Трюк №42

Якщо вам необхідно постійно оновлювати та додавати дані або якщо ви працюєте з діаграмами та зведеними таблицями, вам знадобляться динамічні іменовані діапазони, які збільшуються та зменшуються залежно від ваших даних. Щоб зрозуміти, як працюють динамічні іменовані діапазони, спочатку потрібно познайомитися з функцією Excel ЗМІЩ (OFFSET), якщо ви ще не знайомі з нею. Функція ЗМІЩ (OFFSET) відноситься до посилальних функцій та функцій пошуку Excel.

Почнемо з найпростішого динамічного іменованого діапазону, який розширюватиметься вниз по одному стовпцю, але тільки доти, поки в цьому стовпці є записи. Наприклад, якщо у стовпці А є 10 послідовних рядків з даними, динамічний іменований діапазон охоплюватиме діапазон А1:А10. Щоб створити базовий динамічний іменований діапазон, зробіть таке.

Виберіть Вставка → Ім'я → Присвоїти (Insert → Name → Define) і введіть MyRange у полі Ім'я (Names in workbook). У полі Формула (Refers to) введіть таку формулу: =OFFSET($A$1;0;0;COUNTA($A$l:$A$100);l), в українській версії Excel =ЗМІЩ($А$1;0; 0; РАХУНОК ($ А $ 1: $ А $ 100); 1). Тепер натисніть кнопку Додати (Add), потім натисніть кнопку ОК.

На жаль, динамічні іменовані діапазони не можна перевірити у стандартному полі імені ліворуч від рядка формул. Незважаючи на це, можна натиснути в полі імені, ввести ім'я MyRange та натиснути клавішу Enter. Excel автоматично виділить діапазон. Звичайно, можна скористатися і діалоговим вікном Перехід (Go То), вибравши команду Правка → Перейти (Edit → Go To) (сполучення клавіш Ctrl/Apple+G). У полі Посилання введіть MyRange і клацніть на кнопці ОК.

У динамічному іменованомудіапазоні, який ви створили в попередньому прикладі, функція РАХУНОК (COUNTA) стоїть на місці аргументу Висота (Height) функції ЗМІЩ (OFFSET).

У наступному прикладі динамічний іменований діапазон ми застосуємо визначення таблиці даних, що має бути динамічної. Для цього в поле Формула (Refers to) введіть таку формулу: =OFFSET($A$1;0;0;COUNTA($A$1:$A$100);COUNTA($1:$1)), в українській версії Excel =ЗМІЩ( $ А $ 1; 0; 0; РАХУНОК ($ А $ 1: $ А $ 100); Тепер динамічний іменований діапазон буде розширюватися на стільки записів, скільки є в стовпці А, і на стільки рядків, скільки заголовків у рядку 1. Якщо ви впевнені, що кількість стовпців у таблиці даних не змінюватиметься, можете замінити другу функцію РАХУНОК (COUNTA) постійним числом, наприклад, 10

Єдина проблема під час використання динамічного іменованого діапазону для таблиці даних у тому, що передбачається, що стовпець А визначає максимальну довжину таблиці. Найчастіше це так, проте іноді найдовшим стовпцем може бути інший стовпець таблиці. Подолати цю проблему можна за допомогою функції Excel МАКС (МАХ), яка повертає найбільше в діапазоні осередків. Як приклад створіть таблицю, як у рис. 3.4.

діапазон

Мал. 3.4. Динамічна таблиця даних та діалогове вікно присвоєння імені

У рядку 1 зберігаються функції РАХУНОК (COUNTA), які посилаються вниз на відповідний стовпець і, таким чином, повертають кількість записів у кожному стовпці. Функція МАКС (МАХ) використовуватиметься як аргумент Висота (Height) функції ЗМІЩ (OFFSET). Це гарантує, що динамічний іменований діапазон для цієї таблиці завжди буде розширюватися вниз на стільки осередків, скільки їх міститься в найдовшому стовпчику таблиці. звичайнож, можна приховати рядок 1, тому що користувачеві зовсім не потрібно її бачити.

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

У наступному прикладі список чисел у стовпці А також містить порожні комірки. Це означає, що, якщо ви спробуєте скористатися функцією РАХУНОК (COUNT) або РАХУНОК (COUNTA), динамічний іменований діапазон закінчиться раніше, ніж остання комірка з даними. Погляньте, наприклад, на рис. 3.5.

введіть

Мал. 3.5. Діапазон чисел та діалогове вікно присвоєння імені

У цьому випадку, хоча останнє число діапазону насправді знаходиться в рядку 10, динамічний діапазон розширюється лише до шостого рядка. Причина цього лежить у функції РАХУНОК (COUNT), яка вважає значення в осередках від А1 до А100. Оскільки у списку лише шість числових значень, діапазон містить лише шість рядків.

Щоб подолати цю проблему, скористайтеся функцією Excel ПОШУКПОЗ (MATCH). Функція ПОШУКПОЗ (MATCH) повертає відносну позицію елемента масиву, що відповідає вказаному значенню у вказаному порядку. Наприклад, якщо ви застосуєте наступну функцію ПОШУКПОЗ (MATCH): =МАТСН(6;$А$1:$А$100;0), в українській версії Excel =ПОШУКПОЗ(6;$А$1:$А$100;0), до того ж ж набору чисел, як і рис. 3.5 вона поверне число 10, що представляє рядок 10 стовпця А. Вона повертає 10, так як ви наказали функції знайти число 6 в діапазоні А1:А100.

Очевидно, коли ви використовуєте функцію ПОШУКПОЗ (MATCH) у динамічному іменованому діапазоні, останнє число діапазону, ймовірно, заздалегідь невідоме. Таким чином, знадобиться поставити вфункції пошуку занадто великого числа, яке ніколи не з'явиться в діапазоні, і змінити її останній аргумент із 0 на 1.

У попередньому прикладі ви наказали функції ПОШУКПОЗ (MATCH) знайти в точності число 6, не більше і не менше. Замінивши 0 на 1, ви змусите функцію шукати найбільше значення, менше або рівне вказаному. Для цього скористайтеся формулою =МАТСН(1Е+306;$А$1:$А$100;1), в українській версії Excel =ПОШУКПОЗ(1Е+306;$А$1:$А$100;1).

Щоб створити динамічний іменований діапазон, який розширюватиметься до останнього рядка, що містить число (незалежно від наявності порожніх осередків до неї), введіть наступну формулу в полі Формула (Refers to) діалогового вікна Присвоєння імені (Define Name) (рис. 3.6): = OFFSET(Sheet2!$A$1;0;0;MATCH(lE+306;Sheet2!$A$l:$A$100;1);1), в українській версії Excel =CMEЩ(Sheet2!$A$1;0; 0;ПОШУКПОЗ(1E+306;Sheet2!$A$l:$A$100;1);1).

введіть

Мал. 3.6. Динамічний діапазон, що розширюється до останнього запису, що містить число

Наступний логічний тип динамічних іменованих діапазонів, що випливає з цього, - це діапазон, що розширюється до останнього текстового запису незалежно від порожніх осередків у списку або таблиці. Для цього змініть функцію ПОШУКПОЗ (MATCH) таким чином: МАТСН(«*»;$А$1:$А$100;-1), в українській версії Excel ПОШУКПОЗ(«*»;$А$1;$А$100;-1) . Така функція завжди повертає номер рядка, де міститься останній текстовий запис у діапазоні $А$1:$А$100.

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

Для цього спочаткувставте два порожні рядки вище за список. Виділіть рядки 1 і 2 і виберіть команду Вставка → Рядки (Insert → Row). У першому рядку (рядок 1) введіть наступну функцію: =МАХ(МАТСН»*»;$А$3:$А$100;-1);МАТСН(1Е+306;$А$3:$А$100;1)), в української версії Excel = МАКС(ПОШУКПОЗ»*»;$А$3:$А$100;-1);ПОШУКПОЗ(1Е+306;$А$3:$А$100;1)). У комірці під коміркою з формулою введіть число 1. Осередок ще нижче, під коміркою з числом 1, повинен містити текстовий заголовок списку. Число 1 було додано, щоб друга функція ПОШУКПОЗ (MATCH) не повернула помилку #N/A, якщо в діапазоні АЗ:А100 не виявиться чисел. Перша функція ПОШУКПОЗ (MATCH) завжди знайде текст у заголовку.

Дайте осередку А1 ім'я MaxRow. Виберіть команду Вставка → Ім'я → Присвоїти (Insert → Name → Define), надайте динамічному діапазону ім'я, наприклад, MyList і в полі Формула (Refers to:) введіть таку формулу: =OFFSET(Sheet2!$A$3;0;0;MaxRow ;1), в українській версії Excel = CMEЩ(Sheet2!$A$3;0;0;MaxRow;1).

У наступному списку наведено типи динамічних іменованих діапазонів, які можуть бути корисними. Для цих прикладів потрібно заповнити стовпець А і текстом, і числовими значеннями. Крім того, виберіть команду Вставка → Ім'я → Присвоїти (Insert → Name → Define) і введіть ім'я, яке складається з одного слова (наприклад, MyRange) у полі Ім'я (Names in workbook). Все, що буде змінюватися, - це формула в полі Формула (Refers to).

Розширити діапазон на стільки рядків, скільки існує числових записів : у поле Формула (Refers to) введіть наступне: =OFFSET($A$1;0;0;COUNT($A:$A);1), в українській версії Excel = ЗМІЩ ($ А $ 1; 0; 0; РАХУНОК ($ А; $ А); 1).

Розширити діапазон на стільки рядків, скільки існує числових та текстових записів: у поле Формула (Refers to) введіть наступне:=OFFSET($A$1;0;0;COUNTA($A:$A);1), в українській версії Excel =ЗМІЩ($А$1;0;0;РАХУНОК($А:$А);1).

Розширити до останнього числового запису : у полі Формула (Refers to) введіть наступне: =OFFSET($A$1;0;0;MATCH(1E+306;$A:$A)), в українській версії Excel = ЗМІЩ ($ А $ 1; 0; 0; ПОШУКПОЗ (1Е + 306; $ А: $ А)). Якщо ви очікуєте, що може зустрітися число більше 1Е+306 (1 з 306 нулями), введіть ще більше число.

Розширити до останнього текстового запису : у полі Формула (Refers to) введіть наступне: =OFFSET($A$1;0;0;МАТЧ(«*»;$А:$А;-1)), в українській версії Excel =ЗМІЩ($А$1;0;0;ПОШУКПОЗ(«*»;$А:$А;-1)).

Розширити вниз залежно від значення в іншому осередку :В осередок В1 введіть число 10, а потім у поле Формула (Refers to) введіть наступне: =OFFSET($A$1;0;0;$B$1; 1), в українській версії Excel = ЗМІЩ ($ А $ 1; 0; 0; $ В $ 1; 1). Тепер змініть число у клітинці В1, і діапазон зміниться відповідним чином.

Розширювати вниз по одному рядку щомісяця : У полі Формула (Refers to) введіть наступне: =OFFSET($A$1;0;0,MONTH(TODAY());1), в українській версії Excel = ЗМІЩ($А$1;0;0.МІСЯЦЬ(СЬОГОДНІ));1).

Розширювати вниз по одному рядку щотижня : У полі Формула (Refers to) введіть наступне: =OFFSET($A$1;0;0;WEEKNUM(TODAY());1), В українській версії Excel = ЗМІЩ($А$1;0;0;ДЕНЬНІД(СЬОГОДНІ());1)

Для останньої формули потрібно встановити Пакет аналізу (Analysis ToolPak). Це можна зробити командою Сервіс → Надбудови (Tools → Add-ins).