Формула подсчета количества ячеек по нескольким условиям в Excel
Функция СЧЁТЕСЛИ имеет родственную функцию в Excel – СУММЕСЛИ. Естественно разработчики Excel не позволили бы себе разработав в функцию СУММЕСЛИМН служащей для суммирования значений с несколькими условиями, при этом не создать отдельной функции СЧЁТЕСЛИМН для подсчета количества ячеек по нескольким условиям.
Формула СЧЁТЕСЛИМН для подсчета ячеек с несколькими условиями в Excel
Ниже на рисунке представлена таблица медалистов зимних олимпийских игр в 1972-ом году по горнолыжному спорту. Допустим, в данном примере необходимо узнать, сколько серебряных медалистов имеют в фамилии букву «ö». Буква, которую нужно найти в списке фамилий записана отдельно в ячейке G1, а тип медали находится в ячейке G2. Формула следующая:
Функция СЧЁТЕСЛИМН требует заполнять аргументы по парам Диапазон_1;Условие_1, подобно как в синтаксисе функции СУММЕСЛИМН (за исключением того, что у нее на 1 аргумент больше – Диапазон_суммирования).
Первый аргумент в функции СЧЁТЕСЛИМН – это Диапазон_1. Он определяет диапазон ячеек B2:B19, в котором содержится список фамилий призеров. Второй аргумент – Критерий_1 содержит сборную строку из комбинации многозначных символов и ссылки на ячейку между ними "*"&G1&"*". Сборка строки как видно реализована соединительным символом амперсант – &. Многозначные символы звездочки по бокам ссылки указывают на то, что совпадение значений может быть не точным. Допустимы любые символы в любом количестве слева и справа от искомого фрагмента строки, а именно буквы ö. А также допустимы пустые строки. Такая комбинация в критерии условия с использованием звездочек перед необходимым символом «ö» и после него позволяет учитывать все значение которые содержатся в проверяемой строке. Это значит, что не нужно волноваться если данная буква находится не вначале или конце фамилии, а в любом месте.
Третий аргумент Диапазон_2 (первый во второй паре аргументов) ведет считывание значений ячеек в диапазоне D2:D19 содержащих строку «Серебро» (как указано в ячейке G2). Таким способом подсчитывается количество только тех ячеек, которые выполняют условия в первой и второй паре аргументов функции СЧЁТЕСЛИМН. То есть только строки содержащие фамилии серебряных медалистов с буквой «ö». В данном примере это фамилии мужчин и женщин: Gustav Thöni, Annemarie Moser-Pröll и снова Annemarie Moser-Pröll. Всего 3 медалиста соответствуют условиям в критериях выборки значений из таблицы.
Другая формула для подсчета количества значений по нескольким условиям
Функция СЧЁТЕСЛИМН – относительно новая. Впервые она появилась в Excel 2010-ой версии. Если вы используете старую версию Excel или открыли файл старой версии, то там вместо нее может быть использована функция СУММПРОИЗВ. Раньше именно она применялась для подсчета количества ячеек по нескольким условиям или в других решениях для многих подобного рода задач. Поэтому эта старая функция будет поддерживается и в новых версиях Excel. Например, решить выше описанную задачу можно альтернативной формулой:
В итоге получаем тот же результат, хот такая формула менее читабельна.
Значение определено условием, находящимся с левой стороны звездочки будет заменен на табличный массив логических значений ИСТИНА или ЛОЖЬ. Подобным же образом будет определено значение с правой стороны звездочки. Логические значения в Excel часто используются в математических операциях и определяются так: ИСТИНА=1, а ЛОЖЬ=0. Если определенное значение в левой и правой таблице = 1, тогда формула умножает эти две единицы между собой 1*1=1 и передает результат (число 1) в корневую функцию для суммирования. Но если хоть одна таблица вернет ложное значение = 0, тогда для суммирования будет передано число 0. Функция СУММПРОИЗВ складывает все полученные числа и выдает их общую сумму, которая и является результатом вычисления целой формулы. В итоге получаем количество строк, которые соответствуют нескольким условиям.
Во втором табличном массиве справа от звездочки достаточно легко прочитать его условие: (D2:D19=G2). Каждое значение в диапазоне ячеек D2:D19 должно быть равно значению в ячейке G2. Если это так в таблицу записывается ИСТИНА, в противном случае – ЛОЖЬ.
Однако первый табличный массив расположенный слева от звездочки, весьма сложный по синтаксису. Как известно результатом проверки выполнения условий должен быть набор логических значений из ИСТИНА и/или ЛОЖЬ. Для этой цели необходимо правильно составить логическое выражение возвращающее такие значения. Сначала функция НАЙТИ ищет в текущей ячейке букву «ö». Адрес диапазона ячеек где выполняется поиск, а также сама буква указываются в ее аргументах. В результате своего вычисления функция НАЙТИ возвращает номер позиции в текущей строке где была найдена необходимая буква. Например, формула =НАЙТИ("ö";"Thöni") возвращает число 3, так как буква ö является третьим символом в фамилии Thöni. Если же буква не будет найдена, тогда формула ввернет #ОШИБКУ! Но нам не нужны ошибочные значения, нас интересуют только логические ИСТИНА или ЛОЖЬ.
Обработать ошибочные значения позволяет специально предназначенная функция ЕОШИБКА. Она как раз и возвращает в результате своих вычисления только логические значения ИСТИНА или ЛОЖЬ, принимая ошибочные. Мы уже максимально близко приблизились к своей цели, однако проблема заключается в том, что значение ИСТИНА будет возвращено если функция НАЙТИ не найдет букву «ö» в текущей фамилии и вернет ошибку для функции ЕОШИБКА. А нам как раз нужно наоборот получить итоговое значение ЛОЖЬ в этом случаи если буква не будет найдена. Перевернуть в Excel логические значения наоборот позволяет функция =НЕ(). Эта логическая функция в своих аргументах принимает значения ИСТИНА или ЛОЖЬ и возвращает обратно противоположные им.
С этом разобрались. Что же происходит тогда, когда функция НАЙТИ находит букву «ö» и возвращает число, например, 10? Тогда ЕОШИБКА возвращает значение ЛОЖЬ ведь число 10 не является ошибочным значением, а только лишь числом. После чего функция НЕ преобразует ложное значение в истинное. Так работает данная формула.
Как вводить непечатные символы ALT-кодом
Чтобы ввести непечатную букву с клавиатуры как в данном случи нам нужен символ – «ö», можно воспользоваться специальным альт-кодом. Для этого сначала переведите на английскую раскладку клавиатуры, затем зажмите клавишу ALT на основной клавиатуре и наберите код 0246 на цифровой клавиатуре сбоку (клавиша NumLock должна быть включена). После чего отпустите клавишу ALT и будет введен символ соответствующий коду. Сразу стоит отметить, что для ввода альт-кода нет смысла пользоваться цифрами вверху основной клавиатуры – это не сработает. Каждый альт-код соответствует коду в таблице символов ASCII стандарта. Там буква «ö» имеет код с номером 246. В данной таблице содержатся все печатные и непечатные с клавиатуры символы ввода. Поэтому любой символ можно ввести альт-кодом.
Если ваша клавиатура не обустроена дополнительной цифровой клавиатурой (как часто бывает на ноутбуках), тогда просто скопируйте этот символ ö. Вы не сможете воспользоваться специальной функцией =СИМВОЛ() для ввода символов по коду таблицы ASCII в Excel, так как у вас русская версия. В английской версии сработает формула: =CHAR(246). Все дело в том, что таблица ASCII также отличается на русскую (коды от 32 и до 255) и английскую версию (коды 9-253). Поэтому в данной ситуации проще скопировать этот символ – ö, который встречается только в английской версии ASCII с кодом 246 и забыть о проблеме.
