Excel - Как подсчитать вхождение символов в строках в диапазонах переменной длины

Question

Фон: я анализирую частоту, с которой темы появляются в ряде электронных писем. Каждой теме соответствует соответствующий символ (az), и каждому электронному письму присваивалось несколько таких символов каждый раз, когда возникал какой-либо аспект темы:

В приведенном выше примере MAIL2 затрагивал тему «c» 2 раза и «d» 2 раза.

Вопрос: Как мне суммировать эти вхождения для каждого электронного письма отдельно, если число строк и символов, соответствующих каждому электронному письму, непредсказуемо (от 1 до ~ 12)?

Я думаю о форматировании результатов, как это:

Я могу дать каждой строке соответствующий адрес электронной почты. Я думаю, что тогда я бы нашел в каждом подсчете числа серых ячеек одного символа (например, 'a' для C2) в какой-то строке, объединенной из всех ячеек в строках, отмеченных как MAIL1? Но я буду благодарен за любое решение, которое позволяет получать отдельные результаты для каждого электронного письма.

Answer 1

Обратите внимание, что формулы в этом ответе должны быть введены в ячейку C2 , а затем скопированы / заполнены по мере необходимости.

---

EDIT: Оказывается, есть способ решить эту проблему без использования вспомогательных клеток:

=
SUMPRODUCT(
  ($B2=$A$2:INDEX($A:$A,MATCH("*",$B:$B,-1)))*
  (LEN($B$2:INDEX($B:$B,MATCH("*",$B:$B,-1)))-LEN(SUBSTITUTE($B$2:INDEX($B:$B,MATCH("*",$B:$B,-1)),C$1,"")))
)

Объяснение: Термин $A$2:INDEX($A:$A,MATCH("*",$B:$B,-1)) и соответствующая версия столбца B используются для динамической настройки диапазонов источника. Формула эквивалентна следующей статической версии, применимой к электронной таблице примера:

=
SUMPRODUCT(
  ($B2=$A$2:$A$11)*
  (LEN($B$2:$B$11)-LEN(SUBSTITUTE($B$2:$B$11,C$1,"")))
)

---

Простейшим решением будет следующая формула:

=COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*")

К сожалению, это не работает в особом случае, когда в ячейке содержится более одной буквы. Например, «f» в ячейке B11 будет учитываться только один раз.


Мы можем решить эту проблему некрасивым и ограниченным способом, суммируя количество повторений. Например, следующее будет работать максимум для трех вхождений любого персонажа:

=COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*")
+COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*"&C$1&"*")
+COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*"&C$1&"*"&C$1&"*")

В качестве альтернативы, мы могли бы использовать вспомогательные клетки. Следующая формула копируется / заполняется в каждую ячейку в диапазоне C2:H11 делает именно это:

=
IF(
  ""<>$A2,
  LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")),
  SUMIF($A:$A,$B2,C:C)
)

Краткое объяснение:

""<>$A2 выбирает, является ли ячейка вспомогательной (true) или серой, результирующей ячейкой (false).

LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")) вычисляет количество вхождений соответствующей буквы в соответствующей ячейке.

SUMIF($A:$A,$B2,C:C) суммирует значения для соответствующей буквы.