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

Стивен придумал, как подсчитать количество фильмов, но когда он пытается подсчитать, сколько фильмов с символом «*» находится в столбце, цифра неверна.

Есть несколько способов решить эту проблему, но сначала прокомментируем выбор звездочек для такой цели: в общей схеме вещей, которая представляет собой Excel, звездочка имеет множество целей. Чаще всего он используется в формулах как символ умножения и почти так же часто как подстановочный знак во многих аргументах формул. По этой причине не рекомендуется использовать звездочку для других целей, особенно для элементов, которые вы хотите подсчитать, например, для рейтингов фильмов. В этом случае может быть лучше просто использовать для рейтингов числа от 1 до 4, потому что с числами очень легко работать, и они однозначны в их использовании.

Если вам необходимо использовать звездочки, то есть несколько способов составить формулу для подсчета. Функция СУММПРОИЗВ отлично справится с этой задачей. Предполагая, что звездочки находятся в столбце C, вы можете использовать следующее:

=SUMPRODUCT(--(C:C="*"))

=SUMPRODUCT(--(C:C="**"))

=SUMPRODUCT(--(C:C=""))

=SUMPRODUCT(--(C:C="*"))

Обратите внимание на использование двух знаков минус в каждой из этих формул. Это употребление точнее всего назвать «двойным унарным» (ботаник, верно?)

и используется для преобразования результатов True / False в числовые эквиваленты (1/0).

Это необходимо, потому что такая формула, как C: C = «*», возвращает True или False, а SUMPRODUCT требует числовых значений. Без принудительного преобразования двойной унарной функции функция СУММПРОИЗВ каждый раз возвращала бы 0.

Вы также можете использовать SUMPRODUCT немного иначе, чтобы просто проверить длину того, что находится в столбце C. Этот подход хорошо работает, если C содержит только звездочки, но также будет работать, если вы используете что-то отличное от звездочек:

=SUMPRODUCT(--(LEN(C:C)=1))

=SUMPRODUCT(--(LEN(C:C)=2))

=SUMPRODUCT(--(LEN(C:C)=3))

=SUMPRODUCT(--(LEN(C:C)=4))

Вы также можете использовать прямую функцию СУММ, но следующие формулы необходимо вводить с помощью Ctrl + Shift + Enter. (Это формулы массива.)

=SUM(IF(C:C="*",1,0))

=SUM(IF(C:C="**",1,0))

=SUM(IF(C:C="",1,0))

=SUM(IF(C:C="*",1,0))

Обратите внимание, что в примерах до сих пор используются функции суммирования, в частности СУММПРОИЗВ и СУММ. У этих функций нет упомянутой ранее проблемы неправильного понимания звездочки. Однако вы сталкиваетесь с проблемой при использовании функций подсчета. Например, следующее не даст желаемых результатов:

=COUNTIF(C:C,"*")

Звездочка действует как подстановочный знак, соответствующий чему-либо в ячейке. Таким образом, вы получаете количество всех ячеек в столбце C, которые что-либо содержат. Вы можете указать, что звездочку следует рассматривать как буквальный символ (а не как подстановочный знак), поставив перед ней тильду следующим образом:

=COUNTIF(C:C,"~*")

Когда дело доходит до двух звездочек, вы можете подумать, что это сработает:

=COUNTIF(C:C,"~**")

Не будет; Excel интерпретирует это как «одинарную буквальную звездочку, за которой следует что-нибудь». Другими словами, первая звездочка буквальная, а вторая — все еще подстановочный знак. Перед каждой звездочкой должна стоять тильда, например:

=COUNTIF(C:C,"~*")

=COUNTIF(C:C,"~~")

=COUNTIF(C:C,"~~~*")

=COUNTIF(C:C,"~~~~")

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

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12849) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.