Подсчет значений с множественными аспектами (Часть 1. Условие И) в EXCEL

В качестве начальной таблицы возьмем таблицу с 2-мя столбцами: текстовым « Фрукты » и числовым « Количество на складе » (См. файл примера ).

Задача1

Разглядим задачку, когда аспекты используются к значениям из различных столбцов.

Найдем число партий продукта с определенным Фруктом И с Количеством на складе не наименее малого (Условие И — условие при котором строчка считается удовлетворяющей аспекту, когда оба ее поля сразу соответствуют критериям). К примеру, число партий персики ( ячейка D 2 ) с количеством ящиков на складе >=5 ( ячейка Е2 ) . Итог очевиден: 2. Для наглядности, строчки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Подсчет можно воплотить обилием формул, приведем несколько:

1. = СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»> Function_F»> =СУММПРОИЗВ(—(A2:A13=D2);—(B2:B13>=E2)) Это решение труднее, но дозволяет осознать работу функции СУММПРОИЗВ() , которая быть может полезна для подсчета с множественными аспектами в остальных вариантах.

Разберем подробнее применение функции СУММПРОИЗВ() :

Результатом вычисления A2_A13=D2 является массив <ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>Значение ИСТИНА соответствует персики . Итог можно узреть, выделив A2_A13=D2 , а потом нажав кнопку F9 ;
Двойное отрицание (—) конвертирует вышеуказанный массив в числовой:
Аналогично, 2-ой массив возвращает <0:1:1:1:0:1:1:0:0:1:1:1>, где 0 соответствует значениям =5, потому 2-ое значение в массиве =1 и т.д.
Дальше, функция попарно перемножает элементы массивов и суммирует их. Получаем – 2.

3. Остальным вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) . Тут, символ Умножения (*) эквивалентен Условию И.

4. Формула массива = СУММ((A2:A13=D2)*(B2:B13>=E2)) эквивалентна вышеупомянутой формуле =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) Единственное, опосля ее ввода необходимо заместо ENTER надавить CTRL + SHIFT + ENTER

5. Формула массива = СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет очередной вариант многокритериального подсчета значений.

6. Формула = БСЧЁТА(A1:B13;A1;D14:E15) просит подготовительного сотворения таблицы с критериями. Заглавия данной для нас таблицы должны в точности совпадать с заголовками начальной таблицы. Размещение критерий в одной строке соответствует Условию И.

Тут есть один трюк: в качестве второго аргумента функции БСЧЁТА() ( поле) необходимо ввести ссылку на заголовок столбца с текстовыми значениями, т.к. БСЧЁТА() подсчитывает текстовые значения . В случае использования БСЧЁТ() необходимо записать другую формулу =БСЧЁТ(A1:B13;B1;D14:E15) . Табличка с аспектами не поменяется.

Задача2

Разглядим задачку, когда аспекты используются к значениям из 1-го столбца.

Найдем число партий продукта с Количеством на складе не наименее малого и не наиболее наибольшего (Условие И — строчка таблицы соответствует аспекту, когда ее поле удовлетворяет обоим критериям сразу).

Решение стоится аналогично предшествующей задачки. К примеру, с внедрением функции СЧЁТЕСЛИМН() формула смотрится так (см. лист один столбец в файле примера ):

Другое решение

Подсчитать количество строк, удовлетворяющим 2-м критериям (Условие И) можно без внедрения формул при помощи обычного Автофильтра .

Установите автофильтр к столбцу Количество ящиков на складе , выделив заголовок столбца и нажав CTRL+SHIFT+L. Изберите числовой фильтр Меж .

Удостоверьтесь, что итог таковой же как в задаче2 — т.е. будет отобрано 7 строк (см. строчку состояния в нижней части окна).

Примечание : подсчет значений с множественными критерями также рассмотрен в статьях Подсчет значений с множественными аспектами (Часть 2. Условие ИЛИ) , Часть3 , Часть4 .

Функция СЧЁТЕСЛИ с внедрением 2-ух критериев поиска. Описание и примеры.

В данной статье разглядим варианты использования функции СЧЁТЕСЛИ с 2-мя (несколькими) аспектами поиска. Наиболее подробная информация с описанием функции СЧЁТЕСЛИ, ее способностями и примерами использования, находиться по ссылке: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры

Представим, что у нас есть обычная таблица, с текстовыми данными и числами.

Функция СЧЁТЕСЛИ с использованием нескольких критериев поиска. Описание и примеры.

СЧЁТЕСЛИ с 2-мя аспектами. Числовое значение.

Найдем количество ячеек в столбце Числа, числовые значения в которых, больше числа 50 и меньше числа 50. Поначалу, используя функцию СЧЁТЕСЛИ, мы находим, количество ячеек, значения в которых больше числа 50. Можно поначалу отыскать количество ячеек, с значением меньше числа 50. Это не имеет значение. Как это создать описано в статье: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры. Сейчас в строке формулы добавляем плюс «+», и снова используем функцию СЧЁТЕСЛИ. Спектр этот же самый, но в аспекты поиска задаем поиск значений меньше числа 50. Получаем общие количество ячеек, значение в которых больше и меньше числа 50.

Можно применять и остальные варианты: больше либо равно, меньше либо ровно, равно, не равно. Зависимо от задачки.

Так же, заместо суммирования поисковых результатов по нескольким критериям, можно провести вычитание этих результатов. Либо хоть какое другое действие.

СЧЁТЕСЛИ с 2-мя аспектами. Ссылка на ячейку.

В качестве аспекта поиска, можем применять значения, содержащиеся в ячейке, указывая ссылку на эту ячейку в поле Аспекты, диалогового окна Аргументы функции. Вернемся к нашей таблице и найдем количество ячеек в столбце Числа, числовые значения в которых, больше числа 50 и меньше числа 50. При всем этом используем в качестве критериев поиска ссылки на ячейки. Используем ячейку С7, как одну из ячеек, в которой находиться число 50.

Получили итог 12 ячеек.

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

СЧЁТЕСЛИ с 2-мя аспектами. Текстовые аспекты.

В качестве аспекта поиска, в данном варианте, можно применять текстовое значение (слово) либо часть слова. Как работает функции при внедрение таковых критериев поиска, можно выяснить по ссылке: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры. Объединение нескольких критериев поиска в данном варианте происходит по такому же принципу, как и в вариантах, обрисованных выше. Возьмём для примера столбец, в котором содержаться заглавие мебели. Найдем количество ячеек, в которых содержаться слово Стол и слово Шкаф. При всем этом, поиск будем производить используя буковкы находящиеся в этих словах. «Ст» и «ф» соответственно. К сиим буковкам нужно добавить знак «*». В случае с «Ст», опосля, потому что это 1-ые буковкы в слове. В случае с «ф», перед, потому что это конец слова. Кавычки покажутся автоматом.

Количество критериев не ограниченно 2-мя. Быть может больше, в зависимости от задачки.

Функция счет если в excel по нескольким критериям

В Excel функция СЧЁТЕСЛИ может посодействовать нам вычислить количество определенного значения в перечне. Но время от времени нам необходимо применять несколько критериев для подсчета, это будет труднее, сейчас я расскажу о неких элементах для подсчета с несколькими аспектами.

Countif с несколькими аспектами в одном столбце

Подсчет ячеек с несколькими аспектами на базе текстового значения

К примеру, у меня есть последующие данные, которые содержат некие продукты, и сейчас мне необходимо подсчитать количество KTE и KTO, которые заполнены в одном столбце, см. Скриншот:

Чтоб получить количество KTE и KTO, введите эту формулу:

Потом нажмите Enter ключ, чтоб получить количество этих 2-ух товаров. Смотрите снимок экрана:

Нотки:

1. В приведенной выше формуле: A2: A15 спектр данных, который вы желаете применять, КТЭ и KTO являются аспектами, которые вы желаете подсчитать.

2. Если есть наиболее 2-ух критериев, которые вы желаете подсчитать в одном столбце, просто используйте = СЧЁТЕСЛИ (диапазон1, критерий1) + СЧЁТЕСЛИ (диапазон2, критерии2) + СЧЁТЕСЛИ (диапазон3, критерии3) +…

Советы:
Еще одна малогабаритная формула также может посодействовать для вас решить эту делему: =SUMPRODUCT(COUNTIF($A$2:$A$15,<"KTE";"KTO">)) , and then press Enter key to get the result.
И вы сможете добавить аспекты так же, как =SUMPRODUCT(COUNTIF(range,< "criteria1";"criteria2";"criteria3";"criteria4"…>)).

Подсчет ячеек с несколькими аспектами меж 2-мя значениями

Если для вас необходимо подсчитать количество ячеек, значение которых находится меж 2-мя данными числами, как решить эту задачку в Excel?

В качестве примера возьмите приведенный ниже скриншот. Я желаю получить итог числа от 200 до 500. Используйте последующие формулы:

Введите эту формулу в пустую ячейку, в которой вы желаете отыскать итог:

Потом нажмите Enter ключ, чтоб получить подходящий итог, см. скриншот:

Внимание: В приведенной выше формуле:

B2: B15это спектр ячеек, который вы желаете применять, > 200 и > 500 аспекты, по которым вы желаете подсчитать ячейки;
Вся формула значит, что необходимо отыскать количество ячеек со значением больше 200, а потом отнять количество ячеек со значением больше 500.

Советы:
Вы также сможете применить функцию СЧЁТЕСЛИМН для решения данной для нас задачки, введите последующую формулу: =COUNTIFS($B$2:$B$15,»>200″,$B$2:$B$15,»<500″) , and then press Enter key to get the result.
И вы сможете добавить аспекты так же, как =COUNTIFS(range1,»criteria1″,range2,»criteria2″,range3,»criteria3″. ).

Подсчет ячеек с несколькими аспектами меж 2-мя датами

Для подсчета ячеек на базе спектра дат функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН могут оказать для вас услугу.

К примеру, я желаю подсчитать в столбце номера ячеек с датой меж 5 и 1, сделайте последующее:

Введите последующую формулу в пустую ячейку:

Потом нажмите Enter ключ, чтоб получить номер счета, см. скриншот:

Внимание: В приведенной выше формуле:

B2: B15 это спектр ячеек, который вы желаете применять;
> = 5 января 1 г. и <= 8 августа 1 г. аспекты даты, по которым вы желаете подсчитать ячейки;

Countif с несколькими аспектами в одном столбце с полезной функцией

Если у вас есть Kutools for Excel, С его Избрать определенные ячейки вы сможете стремительно избрать ячейки с определенным текстом либо ячейки меж 2-мя числами либо датами, а потом получить необходимое число.

Советы: Чтоб применить это Избрать определенные ячейки функция, во-1-х, вы должны скачать Kutools for Excel, а потом стремительно и просто примените эту функцию.

Опосля установки Kutools for Excel, пожалуйста, сделайте так:

1. Изберите перечень ячеек, в которых вы желаете подсчитать ячейки на базе критериев, а потом щелкните Kutools > Изберите > Избрать определенные ячейки, см. скриншот:

2. В Избрать определенные ячейки диалоговом окне, установите нужные операции и нажмите OK, были выбраны определенные ячейки, и количество ячеек показано в окне подсказки, как показано на снимках экрана ниже:

Внимание: Эта функция также может посодействовать для вас избрать и подсчитать ячейки меж 2-мя определенными числами либо датами, как показано на последующих скриншотах:

Countif с несколькими аспектами в нескольких столбцах

Если в нескольких столбцах обозначено несколько критериев, к примеру, как показано на последующем скриншоте, и я желаю получить количество KTE, порядок которых превосходит 300, а имя — Ruby.

Введите эту формулу в подходящую ячейку:

и потом нажмите Enter ключ, чтоб получить номер KTE, который для вас нужен.

Нотки:

1. A2: A15 и КТЭ это 1-ый спектр и аспект, который для вас нужен, B2: B15 и > 300 — это 2-ой спектр и аспект, который для вас нужен, а C2: C15 и рубин — это 3-ий спектр и аспект, на котором вы опираетесь.

2. Если для вас необходимы остальные аспекты, для вас просто необходимо добавить спектр и аспекты в формулу, к примеру: = СЧЁТЕСЛИМН (диапазон1, критерий1, диапазон2, критерии2, диапазон3, критерии3, диапазон4, критерии4,…)

Функция счет если в excel

Для подсчета ЧИСЛОвых значений, Дат и Текстовых значений, удовлетворяющих определенному аспекту, существует обычная и действенная функция СЧЁТЕСЛИ( ) , британская версия COUNTIF(). Подсчитаем значения в спектре в случае 1-го аспекта, также покажем как ее применять для подсчета неповторяющихся значений и вычисления ранга.

СЧЁТЕСЛИ(спектр;аспект)

Спектр — спектр, в котором необходимо подсчитать ячейки, содержащие числа, текст либо даты.

Аспект — аспект в форме числа, выражения, ссылки на ячейку либо текста, который описывает, какие ячейки нужно подсчитывать. К примеру, аспект быть может выражен последующим образом: 32, "32", ">32", "яблоки" либо B4.

Подсчет числовых значений с одним аспектом

Данные будем брать из спектра A15:A25 (см. файл примера ).

Аспект

Формула

Итог

Примечание

Подсчитывает количество ячеек, содержащих числа равных либо наиболее 10. Аспект указан в формуле

Подсчитывает количество ячеек, содержащих числа равных либо наименьших 10. Аспект указан через ссылку

Подсчитывает количество ячеек, содержащих числа равных либо наиболее 11. Аспект указан через ссылку и параметр

Примечание. О подсчете значений, удовлетворяющих нескольким критериям читайте в статье Подсчет значений со множественными аспектами. О подсчете чисел с наиболее чем 15 означающих цифр читайте статью Подсчет ТЕКСТовых значений с единственным аспектом в MS EXCEL.

Подсчет Текстовых значений с одним аспектом

Функция СЧЁТЕСЛИ() также годится для подсчета текстовых значений (см. Подсчет ТЕКСТовых значений с единственным аспектом в MS EXCEL).

Подсчет дат с одним аспектом

Потому что хоть какой дате в MS EXCEL соответствует определенное числовое значение, то настройка функции СЧЕТЕСЛИ() для дат не различается от рассмотренного выше примера (см. файл примера Лист Даты ).

Если нужно подсчитать количество дат, принадлежащих определенному месяцу, то необходимо сделать доп столбец для вычисления месяца, потом записать формулу = СЧЁТЕСЛИ(B20:B30;2)

Подсчет с несколькими критериями

Обычно, в качестве аргумента аспект у функции СЧЁТЕСЛИ() указывают лишь одно значение. К примеру, =СЧЁТЕСЛИ(H2:H11;I2) . Если в качестве аспекта указать ссылку на целый спектр ячеек с аспектами, то функция возвратит массив. В файле примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18) возвращает массив <3:2:5>.

Для ввода формулы выделите спектр ячеек такового же размера как и спектр содержащий аспекты. В Строке формул введите формулу и нажмите CTRL+SHIFT+ENTER, т.е. введите ее как формулу массива.

Это свойство функции СЧЁТЕСЛИ() употребляется в статье Отбор неповторимых значений.

Особые случаи использования функции

Возможность задать в качестве аспекта несколько значений открывает доп способности использования функции СЧЁТЕСЛИ() .

В файле примера на листе Особое применение показано как при помощи функции СЧЁТЕСЛИ() вычислить количество повторов всякого значения в перечне.

Выражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел <1:4:4:4:4:1:3:3:3>, который гласит о том, что значение 1 из перечня в спектре А6:А15 – единственное, также в спектре 4 значения 2, одно значение 3, три значения 4. Это дозволяет подсчитать количество неповторяющихся значений формулой =СУММПРОИЗВ(–(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)) .

Формула =СЧЁТЕСЛИ(A6:A14;" вычисляет ранг по убыванию для всякого числа из спектра А6:А15. В этом можно убедиться, выделив формулу в Строке формул и нажав кнопку F9. Значения совпадут с вычисленным рангом в столбце В (при помощи функции РАНГ() ). Этот подход использован в статьях Динамическая сортировка таблицы в MS EXCEL и Отбор неповторимых значений с сортировкой в MS EXCEL.

Функция СЧЁТ подсчитывает количество ячеек, содержащих числа, и количество чисел в перечне аргументов. Функция СЧЁТ употребляется для определения количества числовых ячеек в спектрах и массивах чисел. К примеру, для вычисления количества чисел в спектре A1:A20 можно ввести последующую формулу: =СЧЁТ(A1:A20). Если в данном примере 5 ячеек из спектра содержат числа, то результатом будет значение 5.

Синтаксис

Аргументы функции СЧЁТ указаны ниже.

Значение1 — неотклонимый аргумент. 1-ый элемент, ссылка на ячейку либо спектр, для которого требуется подсчитать количество чисел.

Значение2; . — необязательный аргумент. До 255 доп частей, ссылок на ячейки либо диапазонов, в которых требуется подсчитать количество чисел.

Примечание: Аргументы могут содержать данные разных типов либо ссылаться на их, но при подсчете учитываются лишь числа.

Замечания

Учитываются аргументы, являющиеся числами, датами либо текстовым представлением чисел (к примеру, число, заключенное в кавычки, такое как "1").

Логические значения и текстовые представления чисел, введенные конкретно в перечне аргументов, также учитываются.

Аргументы, являющиеся значениями ошибок либо текстом, который недозволено конвертировать в числа, пропускаются.

Если аргумент является массивом либо ссылкой, то учитываются лишь числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве либо ссылке пропускаются.

Если нужно подсчитать логические значения, элементы текста либо значения ошибок, используйте функцию СЧЁТЗ.

Если требуется подсчитать лишь те числа, которые соответствуют определенным критериям, используйте функцию СЧЁТЕСЛИ либо СЧЁТЕСЛИМН.

Пример

Скопируйте эталон данных из последующей таблицы и вставьте их в ячейку A1 новейшего листа Excel. Чтоб показать результаты формул, выделите их и нажмите кнопку F2, а потом — кнопку ВВОД. По мере необходимости измените ширину столбцов, чтоб созидать все данные.

Функция СЧЕТЕСЛИ заходит в группу статистических функций. Дозволяет отыскать число ячеек по определенному аспекту. Работает с числовыми и текстовыми значениями, датами.

Синтаксис и индивидуальности функции

Поначалу разглядим аргументы функции:

Спектр – группа значений для анализа и подсчета (неотклонимый).
Аспект – условие, по которому необходимо подсчитать ячейки (неотклонимый).

В спектре ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.

В качестве аспекта быть может ссылка, число, текстовая строчка, выражение. Функция СЧЕТЕСЛИ работает лишь с одним условием (по умолчанию). Но можно ее «вынудить» проанализировать 2 аспекта сразу.

Советы для правильной работы функции:

Если функция СЧЕТЕСЛИ ссылается на спектр в иной книжке, то нужно, чтоб эта книжка была открыта.
Аргумент «Аспект» необходимо заключать в кавычки (не считая ссылок).
Функция не учитывает регистр текстовых значений.
При формулировании условия подсчета можно применять подстановочные знаки. «?» – хоть какой знак. «*» – неважно какая последовательность знаков. Чтоб формула находила конкретно эти знаки, ставим перед ними символ тильды (

Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном спектре. Условие подсчета – один аспект.

У нас есть таковая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;">100"). Спектр – В1:В11. Аспект подсчета – «>100». Итог:

Если условие подсчета внести в отдельную ячейку, можно в качестве аспекта применять ссылку:

Посчитаем текстовые значения в одном спектре. Условие поиска – один аспект.

Формула: =СЧЁТЕСЛИ(A1:A11;"табуреты"). Либо:

Во 2-м случае в качестве аспекта употребляли ссылку на ячейку.

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;"таб*").

Для расчета количества значений, оканчивающихся на «и», в которых содержится хоть какое число символов: =СЧЁТЕСЛИ(A1:A11;"*и"). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;"<>"&"стулья"). Оператор «<>» значит «не равно». Символ амперсанда (&) соединяет воединыжды данный оператор и значение «стулья».

При применении ссылки формула будет смотреться так:

Нередко требуется делать функцию СЧЕТЕСЛИ в Excel по двум критериям. Таковым методом можно значительно расширить ее способности. Разглядим особые случаи внедрения СЧЕТЕСЛИ в Excel и примеры с 2-мя критериями.

Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;"столы")+СЧЁТЕСЛИ(A1:A11;"стулья"). Для указания нескольких критерий употребляется несколько выражений СЧЕТЕСЛИ. Они объединены меж собой оператором «+».
Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция отыскивает в ячейке А1. Текст «стулья» – на базе аспекта в ячейке А2.
Посчитаем число ячеек в спектре В1:В11 со значением огромным либо равным 100 и наименьшим либо равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;">=100")-СЧЁТЕСЛИ(B1:B11;">200").
Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это может быть, если спектры являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;">=100")-СЧЁТЕСЛИ(A1:B11;">200"). Отыскивает значения по двум критериям сходу в 2-ух столбцах. Если спектры несмежные, то применяется функция СЧЕТЕСЛИМН.
Когда в качестве аспекта указывается ссылка на спектр ячеек с критериями, функция возвращает массив. Для ввода формулы необходимо выделить такое количество ячеек, как в спектре с аспектами. Опосля введения аргументов надавить сразу сочетание кнопок Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЕТЕСЛИ с 2-мя критериями в Excel весьма нередко употребляется для автоматической и действенной работы с данными. Потому продвинутому юзеру настоятельно рекомендуется пристально изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Посчитаем количество реализованных продуктов по группам.

Поначалу отсортируем таблицу так, чтоб схожие значения оказались рядом.
1-ый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» – «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).