Как посчитать количество ячеек не равных заданному значению
Для подсчета количества ячеек, содержащих значения не равных определенному значению, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, а Х представляет собой значение, которое вы не хотите рассчитывать. Все остальные значения будут учитываться.
В примере, активная ячейка содержит следующую формулу:
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые отвечают критериям.
В примере, мы используем «» (логический оператор «не равно») для подсчета ячеек в диапазоне D5:D11, которые не равны «Готово». СЧЁТЕСЛИ возвращает число в качестве результата.
СЧЁТЕСЛИ не чувствительна к регистру. В этом примере слово «готово» может появиться в любой комбинации прописных / строчных букв.
Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:
Если значение в ячейке A1 равно «100», критерии будут « 100» после конъюнкции, и СЧЁТЕСЛИ будет считать ячейки не равные 100.
Как подсчитать различные и уникальные значения в Excel таблицах
Например, она может подсказать, когда выгодно продавать и покупать акции, если вы настроите нужный уровень цен. Для этого в формулу через точку с запятой добавляем условие — цена акции выросла до определенного значения — и нужную реакцию программы — подсказку «продавать».
Также можно выделить значения, которые находятся в определенном интервале в условиях форматирования между , содержат нужный текст текст содержит , или задать сразу несколько условий. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Например, можно подсветить месяцы, когды вы тратили больше, чем зарабатывали, или задать цветовое кодирование для каждой категории расхода. В этом случае ячейка может быть зеленой, пока вы вписываетесь в бюджет, желтой, если на грани, и красной, когда вы вышли за лимит.
Добавление вспомогательного столбца в набор данных
Это простой способ подсчета разных значений в сводной таблице, поскольку вам нужно только добавить вспомогательный столбец к исходным данным. После добавления вспомогательного столбца вы легко ответите на вопросы задачи.
Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).
Позвольте мне сначала показать вам, как добавить вспомогательный столбец и посчитать разные значения.
Предположим, у меня есть набор данных, как показано ниже:
Добавьте следующую формулу в столбец F и примените ее ко всем ячейкам, в которых есть данные в соседних столбцах.
Приведенная выше формула использует функцию СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данном регионе
Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Это означает, что они продолжают расширяться, когда вы идете вниз по столбцу
Например, в ячейке F2 диапазон критериев составляет $C$2:C2 и $B$2:B2, а в ячейке F3 эти диапазоны расширяются до $C$3:C3 и $B$3:B3.
Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.
Поскольку мы хотим получить только разные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только разные имена, а не повторы.
Ниже показано, как будет выглядеть таблица, когда вы добавите вспомогательный столбец.
Теперь, когда мы изменили исходные данные, мы можем использовать их для создания сводной таблицы. Подключив вспомогательный столбец, получим количество различных сотрудников в каждом регионе.
Ниже приведены шаги, как сделать это:
- Выберите любую ячейку в таблице.
- Нажмите вкладку «Вставка».
Нажмите на кнопку Сводная таблица.
В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.
Вышеуказанные шаги вставят новый лист со сводной таблицей.
Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».
Вы получите вот такую сводную таблицу:
Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».
Как подсчитать уникальные значения в Excel, используя СУММПРОИЗВ и СЧЁТЕСЛИ
Если вы хотите подсчитать уникальные значения в Excel, лучший способ сделать это — использовать формулу. Для этого можно использовать комбинацию функций СУММ и СЧЁТЕСЛИ.
Чтобы быстро объяснить, что делают эти функции, СУММПРОИЗВ умножит, а затем вернет сумму диапазона данных. COUNTIF подсчитает количество ячеек в диапазоне, соответствующих условию. Вы можете использовать обе функции вместе, чтобы вернуть количество уникальных значений для каждой ячейки в массиве.
Например, если у вас есть список чисел от одного до восьми, но число восемь повторяется дважды, возвращаемый массив будет содержать значение 1, а не восьмерку, где будет возвращено значение 2. Это связано с тем, что значение 8 повторяется дважды.
Чтобы подсчитать уникальные значения в Excel, используя СУММПРОИЗВ и СЧЁТЕСЛИ:
- Откройте электронную таблицу Excel.
- Выберите пустую ячейку рядом с вашим набором данных, убедившись, что столбец, в котором он находится, пуст.
- В строке формул введите следующую формулу: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(диапазон,диапазон)) при замене обоих значений диапазона диапазоном ячеек, содержащим ваши данные. (например, =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A8;A2:A8))).
- Нажмите Enter — формула вернет общее количество уникальных значений в диапазоне ячеек.
Метод 4: оператор СЧЕТЕСЛИ
Наконец, мы подошли к функции, которая способна сделать подсчет по определенным условиям. Оператор СЧЕТЕСЛИ создан именно для этой задачи. Все ячейки без данных и те, которые не отвечают заданным пользователем параметрам, данная функция игнорирует.
Синтаксис СЧЕТЕСЛИ типичен для всех операторов, работающих с условиями:
=СЧЕТЕСЛИ(диапазон;критерий)
Диапазон – область ячеек, внутри которой необходимо найти количество совпадений по заданному условию.
Критерий – конкретное условие, совпадение по которому ищет функция. Условие указывается в кавычках, может быть задано как в виде точного совпадения с введенным числом или текстом, или же как математическое сравнение, заданное знаками «не равно» («<>»), «больше» («>») и «меньше» («<�»). Также предусмотрена возможность добавить условия «больше или равно» / «меньше или равно» («=>/=<�»).
Разберем наглядно применение функции СЧЕТЕСЛИ:
- Давайте, к примеру, определим, сколько раз в столбце с видами спорта встречается слово «бег». Переходим в ячейку, куда нужно вывести итоговый результат.
- Одним из двух описанных выше способов входим в Мастер функций. В списке статистических функций выбираем СЧЕТЕСЛИ и кликаем ОК.
- Окно аргументов несколько отличается от тех, что мы видели при работе с СЧЕТЗ и СЧЕТ. Заполняем аргументы и кликаем OK.
- В поле «Диапазон» указываем область таблицы, которая будет участвовать в подсчете.
- В поле «Критерий» указываем условие. Нам нужно определить частоту встречаемости ячеек, содержащих значение “бег”, следовательно пишем это слово в кавычках. Кликаем ОК.
- Функция СЧЕТЕСЛИ посчитает и отобразит в выбранной ячейке количество совпадений с заданным словом. В нашем случае их 16.
Для лучшего понимания работы с функцией СЧЕТЕСЛИ попробуем изменить условие:
- Давайте теперь определим сколько раз в этом же столбце встречаются любые другие значения, кроме слова «бег».
- Выбираем ячейку, заходим в Мастер функций, находим оператор СЧЕТЕСЛИ, жмем ОК.
- В поле «Диапазон» вводим координаты того же столбца, что и в примере выше. В поле «Критерий» добавляем знак не равно («<>») перед словом «бег».
- После нажатия кнопки OK мы получаем число, которое сообщает нам, сколько в выбранном диапазоне (столбце) ячеек, не содержащих слово «бег». На этот раз количество равно 17.
Напоследок, можно разобрать работу с числовыми условиями, содержащими знаки «больше» («>») или «меньше» («<�»). Давайте, например, выясним сколько раз в столбце “Продано” встречается значение больше 350.
- Выполняем уже привычные шаги по вставке функции СЧЕТЕСЛИ в нужную результирующую ячейку.
- В поле диапазон указываем нужный интервал ячеек столбца. Задаем условие “>350” в поле “Критерий” и жмем OK.
- В заранее выбранной ячейке получим итог – 10 ячеек содержат значения больше числа 350.
Как посчитать количество ячеек по нескольким условиям в Excel?
Пример 3. В таблице приведены данные о количестве отработанных часов сотрудником на протяжении некоторого периода. Определить, сколько раз сотрудник работал сверх нормы (более 8 часов) в период с 03.08.2018 по 14.08.2018.
Вид таблицы данных:
Для вычислений используем следующую формулу:
В качестве первых двух условий проверки указаны даты, которые автоматически преобразовываются в код времени Excel (числовое значение), а затем выполняется операция проверки. Последний (третий) критерий – количество рабочих часов больше 8.
В результате расчетов получим следующее значение:
Количество пустых ячеек как условие.
В некоторых случаях вам может потребоваться не просто пересчитать пустые ячейки в диапазоне, но предпринять некоторые действия в зависимости от того, есть ли таковые, либо нет. Самый популярный случай — подсчитать итоги, только если все данные в таблице заполнены.
Хотя в Excel нет встроенной функции ЕСЛИСЧИТАТЬПУСТОТЫ, вы можете легко создать свою собственную формулу, используя вместе функции ЕСЛИ и СЧИТАТЬПУСТОТЫ. Вот как:
- Создаем условие, что количество пробелов равно нулю, и помещаем это выражение в логический тест ЕСЛИ:СЧИТАТЬПУСТОТЫ(B2:D2)=0
- Если логический результат оценивается как ИСТИНА, выведите «Нет пустых».
- Если же — ЛОЖЬ, возвращаем «Пустые».
Полная формула принимает такой вид:
В результате формула определяет все строки, в которых отсутствует одно или несколько значений:
Или вы можете создать другой расчет в зависимости от количества незаполненных позиций. Например, если в диапазоне нет пустот (т.е. если СЧИТАТЬПУСТОТЫ возвращает 0), сложите цифры продаж, в противном случае покажите предупреждение:
То есть, сумма за квартал будет рассчитана только тогда, когда будут заполнены все данные по месяцам.