☰

СЧЁТЕСЛИМН, похоже, не учитывает пустые ячейки.

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
СЧЁТЕСЛИМН, похоже, не учитывает пустые ячейки.

Я пытаюсь построить оператор СЧЁТЕСЛИ в Excel 2010, используя в общей сложности 4 набора критериев. В идеале я хотел бы, чтобы он выглядел примерно так:

COUNTIFS(range1,value in a1,range2,value in b1,range3,value in c1,range4,value in d1)

Это работает нормально, если в ячейках A1-D1 есть фактические значения. Однако, если одна из этих ячеек пуста, я не получаю ожидаемого числа. (Я проверяю это с помощью автоматической фильтрации, используя критерии, которые я устанавливаю в A1-D1). Похоже, что происходит следующее: если ячейка C1 пуста, то функция СЧЁТЕСЛИМН возвращает количество всех строк, в которых ячейка C1 не пуста.

Есть ли способ задать значение по умолчанию, чтобы при отсутствии значений в ячейках A1-D1 функция СЧЁТЕСЛИМН включала все значения?

решение1

TheDCOUNTфункция может работать как альтернатива.

Вам необходимо использовать диапазон критериев, включающий собственные метки, но пустые значения критериев означают «включить все».

Примечание: На рисунках ниже пустые ячейки пусты, как будто вы щелкнули по ячейке и нажали Delete.

Настройка функции DCOUNT

Пример использования DCOUNT

решение2

Как вы знаете, использование фильтра Excel позволит вам выбрать показ всех элементов, включая пустые, показ только пустых элементов или показ любой другой комбинации элементов. Однако он скроет невыбранные элементы. Если скрытие невыбранных элементов приемлемо, то решение ниже может вам подойти.

Вы можете подсчитать строки, выбранные фильтром, с помощью функции SUBTOTAL следующим образом.

Если какие-либо ячейки в ваших данных пусты, настройте данные с помощью фиктивного столбца, как показано ниже. Фиктивный столбец содержит текст в каждой строке, поэтому у нас есть надежный способ подсчета любой строки.

Настройка значений фиктивных столбцов и функции SUBTOTAL

ИспользоватьПРОМЕЖУТОЧНЫЙ ИТОГфункция сfunction_num103 и выбрав ячейки фиктивных значений для диапазона.function_num103 учитывает только строки, выбранные фильтром. 1

Нажмите на ячейку в области данных и включите фильтр. Выберите весь фиктивный столбец (нажмите на заголовок столбца D), щелкните правой кнопкой мыши и выберите Скрыть.

Включение фильтра и скрытие фиктивного столбца

Теперь при изменении элементов в фильтре счетчик будет показывать количество строк, выбранных фильтром.

Фильтр используется, счетчик указывает количество строк, выбранных фильтром

  1. Я только что заметил примечания внизуИТОГО документацияскажем, он всегда игнорирует строки, не включенные в результат фильтра, поэтому вы можете использовать либоfunction_num3 или 103 для подсчета фиктивных значений.

решение3

CountIFS() не учитывает пробелы, но можно опустить пустые ячейки, используя что-то вроде =COUNTIFS(M2:M51, "<>Нокиа", М2:М51, "<>Samsung", M2:M51, "<>"), последнее условие пропускает все пустые ячейки из включения. Я подумал, что этим стоит поделиться.

решение4

Можно использовать COUNTIFS, если вы используете конкатенацию в своих условиях. (подтверждено в Excel 2010).

COUNTIFS(range1,a1,range2,b1,range3,c1,range4,d1)

становится

COUNTIFS(range1,a1 & "",range2,b1 & "",range3,c1 & "",range4,d1 & "")

Связанный контент