Подсчет ТЕКСТовых значений с учетом РЕгиСТра в MS 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))

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)