Определенный текст
=СЧЁТЕСЛИ(rng;"*txt*»)
Чтобы подсчитать количество ячеек, содержащих определенный текст, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше) RNG — это диапазон ячеек, TXT — это текст, который должны содержать ячейки, а «*» — это подстановочный знак, который соответствует любому количеству символов.
В примере активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B12;"*a*»)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, содержащем «а», сопоставляя содержимое каждой ячейки с шаблоном «*а*», предоставленным в качестве критерия. Символ «*» (звездочка) является подстановочным знаком в Excel, что означает «соответствие любому количеству символов», поэтому эта модель будет учитывать любую ячейку, содержащую «а» в любой позиции. Количество ячеек, соответствующих этому шаблону, вычисляется как число.
Вы можете легко адаптировать эту формулу для использования содержимого другой ячейки в качестве критерия. Например, если A1 содержит текст, который соответствует тому, что вы хотите, используйте следующую формулу:
=СЧЁТЕСЛИ(rng;"*"&a1&»*»)
X или Y
=СУММПРОИЗВ(—((СЧЁТ(НАЙТИ("abc",B5:B12))+ЧИСЛО(НАЙТИ("def",B5:B12)))>0))
Вы можете использовать вспомогательный столбец решений или более сложные формулы для подсчета ячеек, содержащих любое значение.
При подсчете ячеек по критерию «или» следите за тем, чтобы число не удваивалось. Например, если вы подсчитываете ячейки, содержащие «abc» или «def», вы не можете просто сложить вместе две функции COUNTIF, поскольку вы можете удвоить количество ячеек, содержащих как «abc», так и «def».
Формула решения
Чтобы решить одну формулу, вы можете использовать комбинацию СУММПРОИЗВ с ЕСЧИСЛО + НАЙТИ. Формула в ячейке E4 будет такой:
=СУММПРОИЗВ(—((СЧЁТ(НАЙТИ("abc",B5:B12)) + СЧЁТ(НАЙТИ("def",B5:B12)))>0))
Эта формула основана на формуле, которая находит текст внутри ячейки:
IЧИСЛО(НАЙТИ("abc",B5:B12)
Учитывая диапазон ячеек, этот фрагмент вернет массив истинных или ложных значений, по одному значению для каждой ячейки в диапазоне. Поскольку мы используем это дважды (один раз для «abc» и один раз для «def»), мы получаем два массива.
Затем мы складываем эти матрицы вместе (+), сложение создаст новую единую матрицу чисел. Каждое число в этом массиве является результатом сложения вместе истинных и ложных значений исходных двух массивов. В показанном примере матрица выглядит так:
{2; 0; 2; 0; 1; 0; 2}
Нам нужно сложить эти числа вместе, но мы не хотим, чтобы счет удваивался. Поэтому нам нужно убедиться, что любое значение больше нуля. Для этого мы вернем любое значение больше 0 в True или False, а затем используем двойное отрицание (-) для преобразования массива в формат 1 и 0.
И, наконец, СУММПРОИЗВ суммирует полученные числа.
Столбец решения о помощи
Со вспомогательной колонкой для проверки каждой ячейки по отдельности проблема упрощается. Мы можем использовать СЧЁТЕСЛИ с двумя значениями (заданными как «бесконечный набор»). Формула:
=—(СУММ(СЧЁТЕСЛИ(B4;{"*abc*";"*def*"}))>0)
СЧЁТЕСЛИ возвращает массив, содержащий два элемента: счётчик для «abc» и счётчик для «def». Чтобы избежать двойного подсчета, мы добавляем элементы, а затем возвращаем результат true/false с «>0». Наконец, мы преобразуем значения True или False в 1 и 0 с двойным знаком минус (—).
Окончательный результат равен 1 или 0 для каждой ячейки. Чтобы получить сумму по всем ячейкам диапазона, необходимо просуммировать вспомогательный столбец.
Ошибки
=СУММПРОИЗВ(—EOS(rng))
Чтобы подсчитать количество ячеек, содержащих ошибки, вы можете использовать функцию EOR, заключенную в функцию СУММПРОИЗВ. В общей форме формулы (выше) rng — это диапазон ячеек, в которых вы хотите вычислить ошибку.
В примере активная ячейка содержит следующую формулу:
=СУММПРОИЗВ(—EOSH(B5:B9))
СУММПРОИЗВ берет одну или несколько матриц и вычисляет сумму произведений соответствующих чисел. Если есть только один массив, он просто суммирует элементы массива.
Функция EOS рассчитывается для каждой ячейки в rng. Результатом является массив значений true/false:
{ПОДЛИННЫЙ; ЛОЖЬ; ПОДЛИННЫЙ; ЛОЖЬ; ЛОЖЬ}
(-) — Оператор (называемый двойным простым) приводит значения true/false к 0 и 1. Результирующий массив выглядит так:
{1; 0; 1; 0; 0}
Затем СУММПРОИЗВ складывает элементы этого массива и возвращает итог, который в этом примере равен числу 2.
Примечание. EOSH подсчитывает все ошибки, кроме # N / A. Если вы хотите, чтобы # N / A также учитывались, используйте функцию ERROR вместо EOSH.
Вы также можете использовать функцию СУММ для подсчета ошибок. Структура формулы такая же, но ее нужно вводить как формулу массива (нажмите Ctrl+Shift+Enter, а не просто Enter). После ввода формулы она будет выглядеть так:
{=СУММ(—EOSH(B5:B9))}
Пять символов
=ЧИСЛОСЛИ(rng;»?????»)
Чтобы подсчитать количество ячеек, содержащих определенное количество символов текста, вы можете использовать функцию СЧЁТЕСЛИ. В общем виде формулы (выше) ГСЧ — это диапазон ячеек, а «?» соответствует одному символу.
В примере активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B10;»?????»)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, содержащем пять символов, сопоставляя содержимое каждой ячейки с шаблоном "???" который дается в качестве критерия для COUNTIF. "?" является подстановочным знаком в Excel, означающим «каждый отдельный символ», поэтому эта модель будет подсчитывать ячейки, содержащие пять символов. Подсчет ячеек, соответствующих этому шаблону, возвращает число, в данном примере число 3.
Положительные числа
=СЧЁТЕСЛИ(rng;">0″)
Чтобы подсчитать положительные числа в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИ. В общем виде формулы (выше) rng представляет собой диапазон ячеек, содержащих числа.
В примере активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B10; ">0»)
COUNTIF подсчитывает количество ячеек в диапазоне, соответствующих критериям. В этом случае критерии предоставляются в виде «> 0», что оценивается как «значения больше нуля». Функция вычисляет общее количество всех ячеек в диапазоне, удовлетворяющих этому критерию.
Вы можете легко адаптировать эту формулу для подсчета ячеек на основе других критериев. Например, чтобы подсчитать все ячейки со значением больше или равным 100, используйте следующую формулу:
=СЧЁТЕСЛИ(rng;">=100″)
Отрицательные числа
=СЧЁТЕСЛИ(rng;"
Чтобы подсчитать количество ячеек, содержащих отрицательные числа в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИ. В общем виде формулы (выше) rng представляет собой диапазон ячеек, содержащих числа.
В примере активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B10;"
COUNTIF подсчитывает количество ячеек в диапазоне, соответствующих критериям. В этом случае критерий предоставляется в виде «
Вы можете легко настроить эту формулу счетчика ячеек на основе других критериев. Например, чтобы подсчитать все ячейки со значением меньше -10, используйте следующую формулу:
=СЧЁТЕСЛИ(rng;"
Чтобы использовать значение в другой ячейке как часть критериев, используйте символ соединения амперсанда (&) следующим образом:
=СЧЁТЕСЛИ(rng;"
Если ячейка A1 содержит значение «-5», критерий будет «
Цифры
=COUNT(кольцо)
Чтобы подсчитать количество ячеек, содержащих числа, используйте функцию COUNT. Однако в общем виде формулы (выше) rng представляет собой массив ячеек.
В примере активная ячейка содержит следующую формулу:
=СЧЁТ(B5:B8)
Функция COUNT полностью автоматическая. Он подсчитывает количество ячеек в диапазоне, содержащем числа, и вычисляет результат.
Нечетные числа
=СУММПРОИЗВ(—(MOD(rng;2)=1))
Для подсчета ячеек, содержащих только нечетные числа, вы можете использовать формулу, основанную на функции СУММПРОИЗВ вместе с функцией ОСТАТ.
В примере формула в ячейке E4 выглядит так:
=СУММПРОИЗВ(—(ОСТАТ(B5:B10,2)=1))
Эта формула вычисляет 4, так как в диапазоне B5:B10 есть 4 нечетных числа (называемых в формуле «rng).
Функция СУММПРОИЗВ работает непосредственно с массивами.
Одна вещь, которую вы можете сделать с помощью СУММПРОИЗВ довольно легко, — это протестировать массив, используя один или несколько критериев, а затем вычислить результаты.
В этом случае мы проверяем нечетное число, используя функцию MOD:
MOD(кольцо;2)=1
MOD вычисляет остаток от деления. В этом случае делитель равен 2, поэтому MOD вычисляет остаток от 1 для любого нечетного числа и остаток от 0 для четного числа.
В функции СУММПРОИЗВ этот тест выполняется для каждой ячейки B5:B10, результатом является массив значений true/false:
{ЛОЖЬ; ПОДЛИННЫЙ; ПОДЛИННЫЙ; ПОДЛИННЫЙ; ЛОЖЬ; ПОДЛИННЫЙ}
После того, как мы присвоили числам значения true/false с помощью двойных отрицаний, мы получили:
{0; 1; 1; 1; 0; 1}
Затем СУММПРОИЗВ суммирует эти числа и вычисляет 4.
Текст
=СЧЁТЕСЛИ(кольцо;»*»)
Вы можете использовать функцию СЧЁТЕСЛИ и подстановочные знаки, чтобы подсчитать количество ячеек, содержащих текст (т е не числа, не ошибки и не пустые). В общей форме (выше) rng — это диапазон ячеек, а «*» — это подстановочный знак, который соответствует любому количеству символов.
В примере активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B9;»*»)
COUNTIF подсчитывает количество ячеек, соответствующих критериям. В этом случае критерий предоставляется в виде подстановочного знака «*», который соответствует любому количеству текстовых символов.
Несколько заметок:
- Булевы значения true и false не считаются текстом
- Числа не считаются "*", если они не введены в виде текста
- Пустая ячейка, начинающаяся с апострофа ('), будет учитываться.
Вы также можете использовать СУММПРОИЗВ для подсчета текстовых значений вместе с функцией ISTEXT следующим образом:
=СУММПРОИЗВ(—ETEXT(rng))
Двойной дефис приводит результат ETEXT из логического значения TRUE или FALSE к 1 и 0. Затем СУММПРОИЗВ складывает эти значения вместе, чтобы получить результат.
Версия с учетом регистра
Если вам нужна версия с учетом регистра, вы не можете использовать COUNTIF. Вместо этого вы можете проверить каждую ячейку в диапазоне, используя формулу, основанную на функции НАЙТИ и функции ЧИСЛОВОЕ.
НАЙТИ чувствителен к регистру, и вы должны дать ему массив ячеек, а затем использовать СУММПРОИЗВ для подсчета результатов. Формула выглядит следующим образом:
=СУММПРОИЗВ(—(ЧИСЛО(НАЙТИ(текст;rng))))
Где text — это текст, который вы ищете, а rng — это диапазон ячеек, который вы хотите подсчитать. Нет необходимости использовать подстановочные знаки, так как НАЙТИ вернет число, если текст будет найден где-либо в ячейке.
