Недавно, в комментариях к заметке Excel. Суммирование по ячейкам, выделенным цветом меня спросили, как подсчитать число ячеек, содержащих определенный символ / букву. У меня получилось решить задачу без использования кода VBA на основе формулы массива. Если вы не использовали ранее такие формулы, рекомендую начать с заметки Excel. Введение в формулы массива.
Шаг 1. Определим, содержится ли искомый символ в ячейке (рис. 1).
Рис. 1. Содержится ли искомый символ в выбранной ячейке
НАЙТИ($C$1;A1) – ищет символ, хранящийся в $C$1, в строке А1; если находит, возвращает позицию этого символа в строке; если не находит, то возвращает ошибку #ЗНАЧ!
Шаг 2. Избавляемся от ошибочных значений (рис. 2).
Рис. 2. Замена ошибочных значений нулями
ЕСЛИОШИБКА(НАЙТИ($C$1;A1);0) – возвращает 0, если значение функции НАЙТИ($C$1;A1) выдает ошибку, в противном случае возвращает само значение функции НАЙТИ($C$1;A1)
Шаг. 3. Заменяем номер позиции на единицу
Рис. 3. Приведение всех положительных значений к единице
=ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0) – преобразование, позволяющее для любого значения позиции (не ошибки) получить 1; номер позиции, возвращаемый функцией НАЙТИ($C$1;A1), делится на 1000 и округляется до ближайшего целого (если быть точным, то ячейка Excel максимально может содержать 32 767 символов, так что «для надежности» можно заменить 1000 на 32 767 ; в этом случае, вы избежите ошибки при любом содержимом ячеек).
В качестве альтернативы можно использовать функцию ЕСЛИ:
=ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1)>0;1);0) – если значение функции НАЙТИ($C$1;A1) больше нуля, то функция ЕСЛИ заменяем это значение на единицу. Заметьте, что третий аргумент в функции ЕСЛИ отсутствует, так как функция НАЙТИ($C$1;A1) не может вернуть значение меньше или равно 0 (функция НАЙТИ($C$1;A1) возвращает только положительные значения или ошибку #ЗНАЧ!).
Шаг. 4. Суммируем все единички. Напомню, что единица соответствует ячейке, содержащей искомый символ, а ноль – ячейке, не содержащей искомый символ (рис. 4).
Рис. 4. Суммирование ячеек, содержащих искомый символ
В ячейке С2 я использовал формулу массива:
{=СУММ(ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1:A14)/1000;1);0))}
Функция последовательно вычисляет значения =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0), =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A2)/1000;1);0) … и так вплоть до =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A14)/1000;1);0)
Результат вычисления (0 или 1) заносится в память, формируя виртуальный массив {1,1,1,0,1,1,0,1,1,0,0,0,1}. Функция СУММ просто суммирует все единицы.
Альтернативная функция: =СУММ(ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1:A14)>0;1);0))
P.S. Не вводите фигурные скобки в строку формул, а наберите всю формулу без фигурных скобок и нажмите одновременно Ctrl+Shift+Enter