Формула Excel: COUNTIFS із змінним діапазоном -

Резюме

Щоб налаштувати COUNTIFS (або COUNTIF) із змінним діапазоном, ви можете використовувати функцію OFFSET. У наведеному прикладі формула в B11 має вигляд:

=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")

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

Пояснення

У наведеному прикладі формула в B11 має вигляд:

=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")

Працюючи зсередини, робота з налаштування змінного діапазону виконується функцією OFFSET тут:

OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range

OFFSET має п’ять аргументів і налаштований так:

  • посилання = B $ 5, починається з комірки B5, рядок заблоковано
  • рядків = 0, зміщення нульових рядків від початкової комірки
  • cols = 0, зміщення нульових стовпців початкової комірки
  • висота = РЯД () - РЯД (B $ 5) -1 = 5 рядків заввишки
  • ширина = 1 стовпець завширшки

Для обробки висоти діапазону в рядках ми використовуємо функцію ROW таким чином:

ROW()-ROW(B$5)-1 // work out height

Оскільки ROW () повертає номер рядка "поточної" комірки (тобто комірки, в якій живе формула), ми можемо спростити так:

=ROW()-ROW(B$5)-1 =11-5-1 =5

З наведеною вище конфігурацією OFFSET повертає діапазон B5: B9 безпосередньо в COUNTIFS:

=COUNTIFS(B5:B9,"") // returns 4

Зверніть увагу, що посилання на B $ 5 у наведеній вище формулі є змішаним посиланням, із відносним стовпцем та рядком, заблокованим. Це дозволяє формулу скопіювати в інший стовпець і все одно працювати. Наприклад, після копіювання в C12 формула:

=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")

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

З НЕПРЯМОЮ та АДРЕСОЮ

Інший підхід полягає у використанні формули на основі функцій INDIRECT та ADDRESS. У цьому випадку ми збираємо діапазон як текст, а потім використовуємо INDIRECT для обчислення тексту як посилання. Формула в B11 буде такою:

=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")

Функція ADDRESS використовується для побудови такого діапазону:

ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())

У першому випадку ADDRESS ми вводимо номер рядка як твердо закодоване значення 5 і надаємо номер стовпця з функцією COLUMN:

=ADDRESS(5,COLUMN()) // returns "$B$5"

У другому випадку ми надаємо "поточний" номер рядка мінус 2, а поточний стовпець із функцією COLUMN:

=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"

Після об'єднання цих двох значень разом ми маємо:

"$B$5:$B$9" // as text

Зверніть увагу, що це текстовий рядок. Щоб перетворити на дійсне посилання, нам потрібно використовувати INDIRECT:

=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range

Нарешті, формула в B11 стає:

=COUNTIFS($B$5:$B$9,"") // returns 4

Примітка: INDIRECT - це нестабільна функція, яка може спричинити проблеми з продуктивністю на великих або складних робочих аркушах.

Цікаві статті...