Обобщение
За да конфигурирате 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, компенсира нула колони начална клетка
- височина = ROW () - ROW (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())
В първия случай на АДРЕС предоставяме номер на реда като твърдо кодирана стойност 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 е променлива функция и може да причини проблеми с производителността в големи или сложни работни листове.
