MS Excel: как использовать COUNTIF() динамически

Question

У меня есть таблица для ведения журнала неисправных предметов. Каждый элемент заносится в отдельный ряд, и в нем есть «Используется?столбец для каждого элемента. Вот как выглядит строка примера:

  Item   | Usable?
 ---------------
1 Item1  | Yes
2 Item2  | No
3 Item3  | Unknown
4 Item4  | No

«Можно использовать?«В столбце есть раскрывающееся меню для каждой ячейки, чтобы установить ее состояние" Да "," Нет "или" Неизвестно ", и я использую функцию COUNTIF() для подсчета общего количества каждого состояния. Я хочу иметь счет в последней строке, сразу после последнего зарегистрированного элемента, то есть:

  Item   | Usable?
 ---------------
1 Item1  | Yes
2 Item2  | No
3 Item3  | Unknown
4 Item4  | No
 ---------------
5 Total Yes: | 1
6 Total No:  | 2
7 Total Unknown: | 1

Проблема в том, что COUNTIF() принимает предопределенный набор ячеек, и когда я добавляю новую строку, COUNTIF() не учитывает новую ячейку в счетчике, пока я не изменю функцию.

Есть ли способ заставить COUNTIF() включать новые данные, не меняя функцию?

Answer 1

Вы можете использовать:=COUNTIF($B$2:INDIRECT("$B"&ROW()-1),INDIRECT("$A"&ROW()))
Напишите статус в последней строке (ту, которую вы хотите посчитать, и вы можете изменить ее без изменения формулы)
во втором столбце рядом со статусом та же строка напишите приведенную выше формулу.
Столбец B - это статус (Да, Нет, Неизвестно)
Столбец А - это предметы

Если вы хотите написать:

Total Yes       2
Total No        2
Total Unknown   1  

Прямо под вами можно использовать:
=COUNTIF($B$2:INDIRECT("$B"&ROW()-1),"Yes") рядом с "Да" =COUNTIF($B$2:INDIRECT("$B"&ROW()-2),"No") рядом с нет
=COUNTIF($B$2:INDIRECT("$B"&ROW()-3),"Unknown") рядом с Unknown

Независимо от того, какую строку вы вставите, она будет засчитана, даже если вы выберите Всего Да и вставите строку

Answer 2

Вариант 1. Ссылка на весь столбец

Как недостаток, вы должны будете поместить свои итоги в другой столбец или лист.

=COUNTIF(B:B,"Yes")

Вариант 2: пустая строка над итогами

Вставка строки в пределах диапазона расширит ее. Включив пустую строку в формулу, Excel автоматически добавит новый диапазон в ваши итоги.

Просто убедитесь, что вставили в пустую строку, а не в итоговую строку.

    A       B
 ---------------
1 Item1  | Yes
2 Item2  | No
3 Item3  | Yes
4
5 TotYes | =COUNTIF(B1:B4,"Yes")

Щелкните правой кнопкой мыши заголовок строки 4 и нажмите "Вставить". Вы заметите, что формула COUNTIF станет B1:B5 .