Стивен использует Excel для создания персонализированной базы данных фильмов. В одном столбце у него есть рейтинг, который он дал каждому фильму по шкале от одного до четырех, определяемый с помощью одной, двух, трех или четырех звездочек. Он хочет добавить информацию, показывающую, сколько у него фильмов с символом «*», сколько у него фильмов с символом «**» и т. Д. Стивен придумал, как подсчитать количество фильмов с символом «*», но когда он пытается подсчитать, сколько Фильмы ‘**’ находятся в столбце, рисунок неправильный.
Есть несколько способов решить эту проблему, но сначала прокомментируйте выбор звездочек для такой цели: В По большому счету, в Excel звездочка имеет множество целей. Чаще всего он используется в формулах как символ умножения и почти так же часто как подстановочный знак во многих аргументах формул. По этой причине не рекомендуется использовать звездочку для других целей, особенно для элементов, которые вы хотите подсчитать, например, для рейтингов фильмов. В этом случае может быть лучше просто использовать цифры от 1 до 4 для рейтингов, потому что с числами очень легко работать и они однозначны в их использовании.
Если вы должны использовать звездочки, то есть несколько способов составить формулу для подсчета. Функция СУММПРОИЗВ отлично справится с этой задачей. Предполагая, что звездочки находятся в столбце C, вы можете использовать следующее:
= SUMPRODUCT (- (C: C = «*»)) = SUMPRODUCT (- (C: C = «* * «)) = СУММПРОИЗВ (- (C: C =» *** «)) = СУММПРОИЗВ (- (C: C =» **** «))
Обратите внимание на использование двух знаков минус в каждой из этих формул. Это использование точнее всего называется «двойной унарный» (ботаник, не так ли?) И используется для преобразования результатов True/False в числовые эквиваленты (1/0). Это необходимо, потому что такая формула, как C: C = “*”, возвращает либо True, либо False, а SUMPRODUCT требует числовых значений. Без принудительного преобразования двойного унарного типа функция СУММПРОИЗВ каждый раз будет возвращать 0.
Вы также можете использовать СУММПРОИЗВ немного иначе, чтобы просто проверить длину всего, что находится в столбце 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)) = СУММ (ЕСЛИ (C: C = «***», 1,0)) = СУММ (ЕСЛИ (C: C = «****», 1,0))
Обратите внимание, что в примерах до сих пор используются функции суммирования, в частности СУММПРОИЗВ и СУММ. У этих функций нет упомянутой ранее проблемы неправильного понимания звездочки. Вы действительно сталкиваетесь с проблемой при использовании функций подсчета, хотя. Например, следующее не даст желаемых результатов:
= COUNTIF (C: C, «*»)
Звездочка действует как подстановочный знак, соответствующий что-нибудь в камере. Таким образом, вы получаете подсчет всех ячеек в столбце C, которые что-либо содержат. Вы можете указать, что вы хотите, чтобы звездочка рассматривалась как буквальный символ (а не как подстановочный знак), поставив перед ним тильду следующим образом:
= COUNTIF (C: C, «~ * «)
Когда дело доходит до двух звездочек, вы можете подумать, что это сработает:
= COUNTIF (C: C,» ~ ** «)
Не будет; Excel интерпретирует это как «одинарную буквальную звездочку, за которой следует что-нибудь». Другими словами, первая звездочка буквальная, а вторая – все еще подстановочный знак. Перед каждой звездочкой должна стоять тильда следующим образом:
= COUNTIF (C: C, «~ *») = COUNTIF (C: C, «~ * ~ * «) = СЧЁТЕСЛИ (C: C,» ~ * ~ * ~ * «) = СЧЁТЕСЛИ (C: C,» ~ * ~ * ~ * ~ * «)
Наконец, если вы хотите чтобы отказаться от использования всех формул, вы можете создать сводную таблицу, которая ссылается на столбец рейтинга для ваших фильмов. Если вы используете этот столбец в качестве строки в сводной таблице и измените метод агрегации так, чтобы он производил подсчет содержимого ячеек в столбце, вы можете получить красивую сводку о том, сколько оценок каждого типа вы присвоили своей фильмы.
Источник: