Подсчет ТЕКСТовых значений с учетом РЕгиСТра в EXCEL

Буквы могут находиться в ВЕРХНЕМ и нижнем регистре (ПРОПИСНЫЕ и строчные). Текстовые строки, соответственно, могут состоять целиком из строчных или ПРОПИСНЫХ букв, а также состоять из букв находящихся в разном РЕгиСТре (например, ЗагоЛовок). Научимся подсчитывать значения с учетом их регистра.

Подсчет текстовых значений с учетом регистра разобьем на несколько частных случаев:

• подсчет значений из букв в ВЕРХНЕМ регистре ;
• подсчет значений из букв в нижнем регистре ;
• подсчет значений с первой Заглавной буквой;
• подсчет значений из букв в РаЗныХ регистрах (включая значения с первой заглавной буквой);
• подсчет значений по критерию.

Функции, учитывающие регистр: НАЙТИ() , СОВПАД() , СТРОЧН() , ПРОПИСН() , ПРОПНАЧ() , ПОДСТАВИТЬ() . В принципе, еще может помочь функция КОДСИМВ() , возвращающая код символа, т.к. код ПРОПИСНЫХ символов всегда меньше кода строчных ( КОДСИМВ("Б")=193 , а КОДСИМВ("б")=225 ) и функция СИМВОЛ() .

Пусть в исходном списке (диапазон А2:А16 ) содержатся слова, состоящие из букв в различных регистрах. Исходный список не должен содержать чисел, пустых ячеек и ошибок. (См. файл примера ).

Подсчет значений из букв в ВЕРХНЕМ регистре

Для подсчета значений из букв в ВЕРХНЕМ регистре используем формулу =СУММПРОИЗВ(--(СОВПАД(A2:A16;ПРОПИСН(A2:A16))))

Функция СОВПАД() возвращает результат ИСТИНА, если 2 значения совпадают, причем с учетом регистра. В нашем случае каждое значение, начиная с А2 до А16 , сравнивается со своей прописной версией.

Выделив в строке формул СОВПАД(A2:A16;ПРОПИСН(A2:A16)) и нажав клавишу F9 получим массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}

Как и следовало ожидать, только 3 значения (ЯБЛОКИ) находятся в ВЕРХНЕМ регистре. Применяя операцию двойного отрицания переводим этот массив в форму {0:0:0:0:0:0:1:1:1:0:0:0:0:0:0}. Далее функция СУММПРОИЗВ() возвратит сумму элементов массива, т.е. 3.

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(ПРОПИСН(A2:A16);A2:A16);1;0)) После окончания редактирования формулы в ячеке, нужно вместо ENTER нажать CTRL+SHIFT+ENTER . В принципе, можно было обойтись и без формулы массива, но тогда необходимо было бы создать вспомогательный столбец рядом с исходным, записав в каждую ячейку формулу =ЕСЛИ(СОВПАД(ПРОПИСН(A2);A2);1;0) , и просуммировать результат.

Подсчет значений из букв в нижнем регистре

Для подсчета значений из букв в нижнем регистре используем формулу

=СУММПРОИЗВ(--(СОВПАД($A$2:$A$16;СТРОЧН($A$2:$A$16))))

Как видно, она практически аналогична формуле предыдущего случая: только функция ПРОПИСН() заменена на СТРОЧН() .

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(СТРОЧН($A$2:$A$16);$A$2:$A$16);1;0))

Подсчет значений с первой Заглавной буквой

Для подсчета значений с первой Заглавной буквой используем формулу

=СУММПРОИЗВ(--(СОВПАД($A$2:$A$16;ПРОПНАЧ($A$2:$A$16))))

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(СТРОЧН($A$2:$A$16);$A$2:$A$16);1;0))

Подсчет значений, состоящих из букв в РаЗныХ регистрах (включая значения с первой заглавной буквой)

В этом случае подсчет становится немногим более сложным.

=СУММПРОИЗВ(--(НЕ(СОВПАД($A$2:$A$16;ПРОПИСН($A$2:$A$16))));--(НЕ(СОВПАД($A$2:$A$16;СТРОЧН($A$2:$A$16)))))

Выделив два аргумента функции СУММПРОИЗВ() и нажав клавишу F9 получим два массива: {1:1:1:1:1:1:0:0:0:1:1:1:1:1:1} – все значения, кроме ПРОПИСНЫХ {0:0:1:1:1:1:1:1:1:1:1:1:1:1:1} – все значения, кроме строчных

Попарное перемножение элементов массива с последующим сложением (функция СУММПРОИЗВ() ) приводит к нужному результату – 10.

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(ПРОПИСН($A$2:$A$16);$A$2:$A$16);0; ЕСЛИ(СОВПАД(СТРОЧН($A$2:$A$16);$A$2:$A$16);0;1)))

Подсчет значений по критерию

Иногда требуется подсчитать значения соответствующие определенному критерию, например ябЛОки (причем будут подсчитаны все ячейки содержащие слово ябЛОки в любой части значения, т.е. будут подсчитаны значения типа «свежие ябЛОки »).

Для этого используем замечательную функцию СУММПРОИЗВ() : =СУММПРОИЗВ(--НЕ(ЕОШИБКА(НАЙТИ("ябЛОки";A2:A16))))

Функция НАЙТИ() ищет построчно с учетом регистра вхождение критерия ябЛОки в ячейкиот А2 до А16 . Не найдя значение равное критерию, функция возвращает ошибку, иначе - начальную позицию вхождения (в нашем случае это всегда 1).

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(ЕОШИБКА(НАЙТИ("ябЛОки";A2:A16));0;1))

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