Резюме
Щоб налаштувати 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 - це нестабільна функція, яка може спричинити проблеми з продуктивністю на великих або складних робочих аркушах.
