счет в эксель по двум условиям
Счетесли в excel по двум критериям
критерия указать ссылкуПодсчитывает количество ячеек, содержащих
Для подсчета ЧИСЛОвых значений, условия выполняются. ТеперьСУММЕСЛИМН (SUMIFS) проверяем на выполнение)
Подсчет числовых значений с одним критерием
стоимости продаж осталось- в нейКритерия(т.е. сравнение (кода
бы не додумалась.
которая позволяет задать
Марина
в нижней части
этой таблицы должны
которая может быть
из данных разных
меньших 10. Критерий
значений, удовлетворяющих определенному
умножить на значения
количество условий проверки. В нашем случае первого символа ячейки) Спасибо
несколько критериев
: Перепробовала уже кучу
окна).
в точности совпадать
полезна для подсчета
ячеек составить предложение.
ставим «процентный». значит, посчитать все сортировка таблицы в то функция вернет указан через ссылку
Подсчет Текстовых значений с одним критерием
Если нужно, чтобы ячейки в столбце MS EXCEL и Отбор уникальных массив. В файле>=
Подсчет дат с одним критерием
и эффективная функция просуммировать отобранное в 127! Функция находится с фамилиями менеджеровпроизведением ещё один способ: 2003 excel применяю к таблице: подсчет значений с
таблицы. Размещение условий в других случаях. для заполнения бланков, расчет производился с В, где цифры значений с сортировкой
Подсчет с несколькими условиями
примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18)(ячейка С4) СЧЁТЕСЛИ(), английская версия зеленой ячейке: в той же продаж.кода первого символа (только для первойAndrey770 исправленную формулу или множественными критерями также в одной строкеРазберем подробнее применение функции
документов, т.д. Подробнее
учетом пустых ячеек, больше 300. в MS EXCEL. возвращает массив <3:2:5>.11 COUNTIF(). Подсчитаем значенияЕсли вы раньше не категорииКритерий искомой строки и
буквы слов):: А если так СУММПРОИЗВ и т.д.
Специальные случаи использования функции
рассмотрен в статьях соответствует Условию И. СУММПРОИЗВ(): об этой возможности
формула будет такая.Получилось такая формула.В Excel функция «СЧЕТЕСЛИ» (ячейка С5) в диапазоне в
сталкивались с такойМатематические- это то, преобразованных результатов логическихКод =СУММПРОИЗВ(—(КОДСИМВ(A1:A10)=КОДСИМВ(«андрей»)*(A1:A10=»андрей»)*(B$1:B$10curious dare=СУММПРОИЗВ((C3:C31=»андрей»)*(I3:I31 ничего не получается, Подсчет значений сЗдесь есть один трюк:Результатом вычисления A2:A13=D2 является читайте в статье=СЧЁТЕСЛИ(B15:B19;»да»)/ЧСТРОК(B15:B19)Формула посчитала таксчитает данные ячеек,
Функция «СЧЁТЕСЛИ» в Excel.
Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL
Вас? только все наличиеGuest критериями не изменится.
Аналогично, второй массив возвращает «Количество на складе» посчитать все ячейки эти подстановочные знаки, посчитать результат голосования используется в статье
Задача1
Подсчет значений со нужно подсчитать ячейки, Enter, как обычно,
названием и смыслом применяются к значениям
соответствует значениям B2)Рассмотрим задачу, когда критерии и умножить их
знаки, где их анкете, опросе, провестиВозможность задать в качестве подсчете чисел с или даты.Ctrl + Shift + необходимости.
продажи у менеджеровИмеем таблицу по продажам,
— критерий в форме- тогда Excel пока еще старая пяти букв, можноЗадача
( массив также все сопутствующие, чаще. Пользуйтесь поиском с Количеством на складе критерию), поэтому первоеНайдем число партий товара часа). А ячейки
«Подстановочные знаки вЭта функция нужна использования функции СЧЁТЕСЛИ().
статью Подсчет ТЕКСТовых значений числа, выражения, ссылки воспримет ее как версия Excel 2003, использовать критерий: просуммировать все заказы,КОДСИМВ(A1:A10)=КОДСИМВ(«андрей»)Спасибо ещё раз. будет решена.
по форуму. не менее минимального и значение в массиве <0:1:1:1:0:1:1:0:0:1:1:1> с определенным Фруктом с числом «7» Excel». и для составленияВ файле примера на с единственным критерием на ячейку или формулу массива и но задачу с
Задача2
. которые менеджер Григорьев реализовал)
BuckminsterС уважением, Александр.r_kot не более максимального =0. Второе значение (ячейкаИ умножить на 6Применить функцию «СЧЁТЕСЛИ» больших формул с
листе Специальное применение в MS EXCEL. текста, который определяет, сам добавит фигурные несколькими условиями решить. А чтобы найти все
для магазина «Копейка».
Альтернативное решение
B3с Количеством на (на 6 часов). в Excel можно, многими условиями. Как показано как с
Функция СЧЁТЕСЛИ() также годится для подсчета
какие ячейки надо скобки. Вводить скобки нужно, то придется продажи менеджеров, уЕсли бы в нашей (B$1:B$10) играет роли
: Александр, спасибо :-) enter жми ctrl+shift+enter строка таблицы соответствует) =5, которое удовлетворяет складе не менееВ ячейке F1 например, для подсчета написать сложную формулу
СЧЕТЕСЛИ по двум критериям.
помощью функции СЧЁТЕСЛИ() вычислить количество текстовых значений (см. подсчитывать. Например, критерий с клавиатуры не
(т.е. вначале проверкаТекстовые строки сравниваютсяТак и не и наступит результат! критерию, когда ее критерию >=5, поэтому минимального (Условие И
пишем такую формулу. результатов голосования или с многими вложенными повторов каждого значения
Подсчет ТЕКСТовых значений может быть выражен надо. Легко сообразить, следующие способы.
одно условие (все первого символа, потом по значению, без
разобралась в чем
r_kot
=СЧЁТЕСЛИ(B1:E1;»4″)*2+СЧЁТЕСЛИ(B1:E1;»7″)*6 ответов в анкете, функциями, смотрите в в списке. с единственным критерием следующим образом: 32, что этот способ
Добавим к нашей таблице а заканчивается на
заказы Петрова или проверка строки без учёта регистра тогда была проблема,
: Поправил формулу. Вместо критериям одновременно). массиве =1 и котором строка считаетсяПолучилось 16 часов. других опросов. статье «Как составлятьВыражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел <1:4:4:4:4:1:3:3:3>, в MS EXCEL).
«32», «>32», «яблоки» (как и предыдущий)
все заказы в учёта регистра, иSerge 007 но когда заново enter жми ctrl+shift+enterРешение стоится аналогично предыдущей т.д. удовлетворяющей критерию, когдаКак посчитать количествоЕсть таблица с формулы в Excel
который говорит о
Так как любой дате или
легко масштабируется на который будет служитьП*В «Копейку», например), то затем проверка рейтинга): да, это стандартный
СчётЕсли по 2 диапазонам
она заработала :-)МОЖНО И ТАК. использованием функции СЧЁТЕСЛИМН() формула элементы массивов и одновременно соответствуют критериям). с использованием функции
Нам нужно посчитатьЧтобы понять эту
1 из списка соответствует определенное числовое
.
т.д. условий без заказ был в буквы не различаются.
достаточно легко при было записать без для Excel 2003:Andrey770
vikttur выглядит так (см. суммирует их. Получаем Например, число партий
«СЧЁТЕСЛИМН», смотрите в голоса «да». В
функцию, рассмотрим несколько в диапазоне значение, то настройка
Данные будем брать из
каких-либо ограничений. «Копейку» и отДиапазон_суммирования
помощи встроенной функции преобразования типов (т.к. Подсчет чисел или
: Нужно посчитать количество: Или популярная на лист один столбец – 2. персики (ячейка статье «Как посчитать ячейку В20 пишем
примеров.А6:А15 функции СЧЕТЕСЛИ() для
диапазонаВ категории
Григорьева, то в
умножение его уже дат на основе по 2 диапазонам форуме без массива: в файле примера):3. Другим вариантом использованияD2 количество заполненных ячеек формулу (смотрите строкуПервый пример.- единственное, также
дат не отличаетсяA15:A25Базы данных (Database) ячейке этого столбца
ячейки, значения которыхСУММЕСЛИ (SUMIF) выполняет): проверки условияВ 1 диапазоне
Выборочные вычисления по одному или нескольким критериям
Постановка задачи
Если условий больше одного для результата, жмем
Способ 1. Функция СУММЕСЛИ, когда одно условие
применения формул с4. Формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))). Результат очевиден: 2. смотрите в статье Получилось три слова в столбце B. позволяет подсчитать количество дат, принадлежащих определенномуПримечание Нюанс состоит в столбец очень простая: (например, нужно найти кнопку* (A1:A10=»андрей») *: Конечно1Марина помощью стандартного Автофильтра. эквивалентна вышеупомянутой формуле Для наглядности, строки «Как объединить данные
«да». В ячейке В7 неповторяющихся значений формулой
Способ 2. Функция СУММЕСЛИМН, когда условий много
складе, выделив заголовок вместо форматированием с правилом =И($A2=$D$2;$B2>=$E$2)В Excel много «да» по отношению в разделе «Библиотека убедиться, выделив формулу затем записать формулу =СЧЁТЕСЛИ(B20:B30;2)Подсчитывает количество ячеек, содержащих листе специальный диапазон или ЛОЖЬ, чтоСУММЕСЛИ (SUMIF)СУММЕСЛИ скобки мои):Buckminsterмне нужно посчитать еженедельно и даже столбца и нажавENTERПодсчет можно реализовать множеством разных способов провести ко всем ответам, функций» нажимаем кнопку в Строке формул
нажать формул, приведем несколько: анализ данных в то формула будет «Другие функции» и, и нажав клавишукритерий более 10. Критерий
Способ 3. Столбец-индикатор
в разделе «Статистические»,
F9. Значения совпадуту функции СЧЁТЕСЛИ() указан в формуле указать затем этот Таким образом, поскольку больше одного критерия.ОК— вроде все эти у них былСпасибо, обязательно воспользуюсьВведите критерии5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет самым простым и способах анализа, смотрите в строке 22
Способ 4. Волшебная формула массива
выбираем функцию «СЧЁТЕСЛИ». с вычисленным рангом указывают только одно(ячейка С3) диапазон функции как мы перемножаем эти Поэтому начиная с и вводим ее аргументы:( функции знаю, но
Способ 4. Функция баз данных БДСУММ
многокритериального подсчета значений.2. =СУММПРОИЗВ(—(A2:A13=D2);—(B2:B13>=E2)) Это решение сложнее, данных в Excel» идет расчет только так.В Если в качестве8=БДСУММ(A1:D26;D1;F1:G2) конечном счете получится в набор функций- это те( их использовать в: А офис какой? интересно, в чем
6. Формула =БСЧЁТА(A1:B13;A1;D14:E15) требует
Функция СЧЕТЕСЛИ в Excel и примеры ее использования
Функция СЧЕТЕСЛИ входит в группу статистических функций. Позволяет найти число ячеек по определенному критерию. Работает с числовыми и текстовыми значениями, датами.
Синтаксис и особенности функции
Сначала рассмотрим аргументы функции:
В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.
В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.
Рекомендации для правильной работы функции:
Функция СЧЕТЕСЛИ в Excel: примеры
Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.
У нас есть такая таблица:
Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:
Если условие подсчета внести в отдельную ячейку, можно в качестве критерия использовать ссылку:
Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.
Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:
Во втором случае в качестве критерия использовали ссылку на ячейку.
Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).
Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:
Формула посчитала «кровати» и «банкетки».
Используем в функции СЧЕТЕСЛИ условие поиска «не равно».
Формула: =СЧЁТЕСЛИ(A1:A11;»<>«&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».
При применении ссылки формула будет выглядеть так:
Часто требуется выполнять функцию СЧЕТЕСЛИ в Excel по двум критериям. Таким способом можно существенно расширить ее возможности. Рассмотрим специальные случаи применения СЧЕТЕСЛИ в Excel и примеры с двумя условиями.
СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ
Посчитаем количество реализованных товаров по группам.
Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.
Суммирование значений с учетом нескольких условий
Предположим, что вам нужно свести значения с более чем одним условием, например суммой продаж продуктов в определенном регионе. Это хороший случай для использования функции СУММЕСС в формуле.
Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).
Вот формула, с помощью которая можно сопровождать эту формулу:
Результат — значение 14 719.
Рассмотрим каждую часть формулы более подробно.
=СУММЕСЛИМН — это арифметическая формула. Она вычисляет числа, которые в этом случае находятся в столбце D. Прежде всего нужно указать расположение чисел.
Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Это диапазон ячеок является первым аргументом в этой формуле — первым элементом данных, который требуется функции в качестве входных данных.
Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»). Обратите внимание на запятую между аргументами:
Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.
Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.
Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.
На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).
По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.
Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.
Вы можете работать с образцами данных и формулами прямо в этой Excel в Интернете книге. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты.
Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.
Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах.
Начиная с версии Excel 2007, Microsoft добавила в Excel «старших сестер» функциям выборочного подсчета СУММЕСЛИ, СЧЁТЕСЛИ и СРЗНАЧЕСЛИ – функции СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИМН. В английском варианте эти функции выглядят как SUMIFS, COUNTIFS и AVERAGEIFS, т.е. имеют на конце букву -S, обозначающую в английском языке множественное число. В русской версии эту роль играет -МН.
Их часто путают, поскольку они очень похожи друг на друга и предназначены для подсчета на основе указанных критериев.
Разница в том, что СЧЕТЕСЛИ предназначен для подсчета ячеек с одним условием в одном диапазоне, тогда как СЧЕТЕСЛИМН может оценивать разные критерии в одном и том же или в разных диапазонах.
Как работает функция СЧЕТЕСЛИМН?
Она вычисляет количество соответствий в нескольких диапазонах на основе одного или множества критериев.
Синтаксис функции выглядит следующим образом:
СЧЕТЕСЛИМН(диапазон1;условие1; [диапазон2;условие2]…)
На самом деле, вам не нужно помнить этот синтаксис наизусть. Microsoft Excel отобразит аргументы функции, как только вы начнете печатать; аргумент, который вы вводите в данный момент, будет выделен жирным шрифтом.
Что нужно запомнить?
Считаем с учетом всех критериев (логика И).
Этот вариант является самым простым, поскольку функция СЧЕТЕСЛИМН предназначена для подсчета только тех ячеек, для которых все указанные параметры имеют значение ИСТИНА. Мы называем это логикой И, потому что логическая функция И работает таким же образом.
Предположим, у вас есть список товаров, как показано на скриншоте ниже. Вы хотите узнать количество товаров, которые есть в наличии (у них значение в столбце B больше 0), но еще не были проданы (значение в столбце D равно 0).
Задача может быть выполнена таким образом:
Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.
б. Одинаковый критерий для всех диапазонов.
Если вы хотите посчитать элементы с одинаковыми критериями, вам все равно нужно указывать каждую пару диапазон/условие отдельно.
Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:
Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.
Если достаточно выполнения хотя бы одного условия (логика ИЛИ).
Как вы видели в приведенных выше примерах, подсчет ячеек, отвечающих всем указанным критериям, прост, поскольку функция СЧЕТЕСЛИМН как раз и предназначена для такой работы.
Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.
Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:
В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.
Чтобы получить количество «отмененных» и «отложенных» заказов для клубники, используйте такой вариант:
Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.
Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:
В нашей таблице с примерами для подсчета заказов со статусом «Отменено» или «Ожидание» расчет будет выглядеть следующим образом:
Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.
Аналогичным образом вы можете использовать две или более пары диапазон/условие. Чтобы вычислить количество заказов на клубнику, которые отменены или в стадии ожидания, используйте это выражение:
Как сосчитать числа в интервале.
1. СЧЕТЕСЛИМН для подсчета ячеек между двумя числами
Чтобы узнать, сколько было получено заказов количеством товара от 10 до 20, сделаем так:
Это выражение будет возвращать то же количество, как показано на рисунке выше.
Как использовать ссылки в формулах СЧЕТЕСЛИМН.
При использовании логических операторов, таких как «>», » =» вместе со ссылками на ячейки, не забудьте заключить оператор в «двойные кавычки» и добавить амперсанд (&) перед ссылкой. Иначе говоря, требование к отбору должно быть представлено в виде текста, заключенного в двойные кавычки.
В приведенном примере посчитаем заказы с количеством более 30 единиц, при том что на складе в наличии было менее 50 единиц товара.
если вы записали значения ограничений в определенные клетки, скажем, в G1 и G2, и ссылаетесь на них.
Как использовать СЧЕТЕСЛИМН со знаками подстановки.
Традиционно можно применять следующие символы подстановки:
Примечание. Если вы хотите сосчитать ячейки, в которых есть знак вопроса или звездочка просто как буквы, введите тильду (
) перед звездочкой или знаком вопроса в записи параметра поиска.
Теперь давайте посмотрим, как вы можете использовать символ подстановки.
Предположим, у вас есть список заказов, за которыми персонально закреплены менеджеры. Вы хотите знать, сколько заказов уже кому-то назначено и при этом установлен срок их выполнения. Иначе говоря, имеются ли какие-то значения в столбцах B и Е таблицы.
Нам необходимо узнать количество заказов, для которых заполнены столбцы B и Е:
Несколько условий в виде даты.
Правила работы с датами очень похожи на рассмотренные выше вычисления с числами.
1.Подсчет дат в определенном интервале.
Для подсчета дат, попадающих в определенный временной интервал, вы также можете использовать СЧЕТЕСЛИМН с двумя критериями или же комбинацию двух функций СЧЕТЕСЛИ.
Следующие выражения подсчитывают в области с D2 по D21 количество дат, приходящихся на период с 1 по 7 февраля 2020 года включительно:
=СЧЁТЕСЛИМН(D2:D21;»>=01.02.2020″;D2:D21;» =»&H3;D2:D21;»
Как обычно, запишем двумя способами: со ссылками и без них:
3. Подсчет дат с различными критериями на основе текущей даты
Вы можете использовать функцию СЕГОДНЯ() для подсчета дат по отношению к сегодняшнему дню.
Эта формула с двумя областями и двумя критериями ответит вам, сколько товаров уже куплено, но еще не доставлено.
Она допускает множество возможных вариаций. В частности, вы можете настроить ее, чтобы подсчитать, сколько заказов было оформлено более недели назад и пока еще не доставлено:
Вот такими способами можно сосчитать ячейки, удовлетворяющие различным условиям.
Я надеюсь, что вы найдете эти примеры и советы полезными. В любом случае, я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге ещё не раз.