Резюме
Чтобы настроить СЧЁТЕСЛИ (или СЧЁТЕСЛИ) с диапазоном переменных, вы можете использовать функцию СМЕЩЕНИЕ. В показанном примере формула в 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(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET имеет пять аргументов и настроен следующим образом:
- ссылка = B $ 5, начало с ячейки B5, строка заблокирована
- rows = 0, смещение нулевых строк от начальной ячейки
- cols = 0, смещение нулевых столбцов начальной ячейки
- height = ROW () - ROW (B $ 5) -1 = 5 рядов в высоту
- ширина = 1 столбец в ширину
Чтобы определить высоту диапазона в строках, мы используем функцию СТРОКА следующим образом:
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),"")
Примечание. СМЕЩЕНИЕ - это непостоянная функция, которая может вызвать проблемы с производительностью на больших или сложных листах.
С НЕПРЯМЫМ и АДРЕСОМ
Другой подход - использовать формулу, основанную на функциях КОСВЕННЫЙ и АДРЕС. В этом случае мы собираем диапазон как текст, а затем используем INDIRECT для оценки текста как ссылки. Формула в B11 будет такой:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Функция АДРЕС используется для создания такого диапазона:
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
Примечание. КОСВЕННЫЙ - это непостоянная функция, которая может вызвать проблемы с производительностью на больших или сложных листах.
