Формула Excel: Динамічний іменований діапазон із OFFSET -

Загальна формула

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Резюме

Одним із способів створити динамічний іменований діапазон із формулою є використання функції OFFSET разом із функцією COUNTA. Динамічні діапазони також відомі як розширювані діапазони - вони автоматично розширюються та стискаються для розміщення нових або видалених даних.

Примітка: OFFSET - це летюча функція, що означає, що вона перераховується при кожній зміні робочого аркуша. З сучасною машиною та меншим набором даних це не повинно викликати проблем, але ви можете спостерігати зниження продуктивності великих наборів даних. У такому випадку розгляньте можливість побудови динамічного іменованого діапазону з функцією INDEX.

У наведеному прикладі формула, яка використовується для динамічного діапазону:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Пояснення

Ця формула використовує функцію OFFSET для генерації діапазону, який розширюється та скорочується шляхом регулювання висоти та ширини на основі підрахунку непорожніх комірок.

Перший аргумент у OFFSET представляє першу комірку даних (початок), яка в даному випадку є коміркою B5. Наступні два аргументи є зміщенням для рядків і стовпців і подаються як нуль.

Останні два аргументи представляють висоту та ширину. Висота та ширина генеруються на льоту за допомогою функції COUNTA, що робить отриману контрольну динаміку динамічною.

Для висоти ми використовуємо функцію COUNTA для підрахунку непустих значень у діапазоні B5: B100. Це не передбачає відсутність порожніх значень у даних та значень понад B100. COUNTA повертає 6.

Для ширини ми використовуємо функцію COUNTA для підрахунку непустих значень у діапазоні B5: Z5. Це передбачає відсутність комірок заголовків і заголовків, крім Z5. COUNTA повертає 6.

На даний момент формула виглядає так:

=OFFSET(B5,0,0,6,6)

З цією інформацією OFFSET повертає посилання на B5: G10, що відповідає діапазону висоти 6 рядків на 6 стовпців поперек.

Примітка: Діапазони, що використовуються для висоти та ширини, слід регулювати відповідно до макета робочого аркуша.

Варіація з повними посиланнями на стовпці / рядки

Ви також можете використовувати повні посилання на стовпці та рядки для висоти та ширини так:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Зверніть увагу, що висота коригується за допомогою -2, щоб врахувати значення заголовка та заголовка в клітинках B4 та B2. Перевагою такого підходу є простота діапазонів всередині COUNTA. Недолік полягає у великих розмірах повних стовпців та рядків - потрібно подбати про те, щоб запобігти помилковим значенням поза діапазоном, оскільки вони можуть легко скинути рахунок.