Формула в Excel Счет Если Несколько Условий • Относительные функции

Формулы Excel «СУММЕСЛИ» и «СЧЕТЕСЛИ» при расчете сезонности

Если мы хотим быстро, не задумываясь, без ошибок рассчитать сезонность для длинных временных рядов, то рекомендую использовать 2 формулы Excel — «СУММЕСЛИ» и «СЧЕТЕСЛИ».

Рассмотрим использование формул «СУММЕСЛИ» и «СЧЕТЕСЛИ»
на примере расчета прогноза с помощью модели «Скользящей средней к 3-м месяцам с аддитивной сезонностью».

Рассчитаем:

  1. Скользящую среднюю к 3-м месяцам;
  2. Разность между значениями ряда и средними значениями к 3-м месяцам (пункт 1);
  3. Усредним разность ряда и средней для каждого месяца получим сезонность в абсолютной величание — для этого воспользуемся функциями СУММЕСЛИ и СЧЕТЕСЛИ;
  4. Продлим значения ряда с помощью скользящей средней к 3-м месяцам и скорректируем её аддитивной сезонностью.

1. Скользящую среднюю к 3-м месяцам для каждого момента времени во временном ряду начиная с 4-го периода:

суммесли excel

2. Разность между значениями ряда и средними значениями к 3-м месяцам для каждого момента времени t (пункт 1):

суммесли Excel примеры

3. Усредним разность ряда и средней (пункт 2) для 12 месяцев — получим сезонность в абсолютной величание — аддитивную сезонность.

Для этого вначале выделим номера месяцев с помощью функции Excel =месяц(дата).

функция excel месяц

Чтобы воспользоваться функцией Excel =месяц(дата), проверяем, являются ли наши даты «январь 2010 г.» датами, если нет, функция =месяц() выдаст ошибку.

Получаем ряд с пронумерованными месяцами от 1 до 12 для каждого момента времени t:

счетесли

Далее с помощью функции Excel =СУММЕСЛИ суммируем для каждого месяца от 1 до 12 отклонения, которые мы считали в пункте 2.

Для этого в формулу СУММЕСЛИ передаем следующие значения:

  • диапазон — $G$8:$AY$8 — диапазон с номера месяцев за весь период;
  • критерий — D9 — номер месяца для которого суммируем отклонения и рассчитываем сезоность;
  • диапазон для суммирования — $G$7:$AY$7 — диапазон с отклонениями рассчитанными в пункте 2).

функция Excel суммесли

Фиксируем ссылки на диапазон с номерами месяцев и диапазон с отклонениями (как зафиксировать ссылки описано в статье «Как зафиксировать ссылку в Excel?»), и протягиваем формулу для 12 месяцев от 1 до 12.

формула суммесли

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

  • диапазон — $G$8:$AY$8 — фиксируем ссылку — номера месяцев во временном ряду;
  • критерий — D9 — номера месяцев для сезонности, кол-во которых нам надо рассчитать во временном ряду для того чтобы рассчитать среднее отклонение.

счетесли

Протягиваем формулу Excel =СЧЁТЕСЛИ от 1 до 12 и получаем ряд с количеством месяцев для каждого месяца сезонности:

счетесли excel пример

Далее сумму отклонений делим на количество месяцев в расчете сезонности, получаем абсолютную (аддитивная) сезонность для каждого месяца в году:

счетесли в екселе

Данный расчет мы проделали в 3 шага

  1. Просуммировали отклонения для каждого месяца сезонности с помощью СУММЕСЛИ,
  2. Рассчитали количество периодов для каждого месяца сезонности с помощью СЧЁТЕСЛИ,
  3. И разделили пункт 1 на пункт 2, т.е. сумму отклонений на количество периодов, т.е. нашли среднее отклонение для каждого месяца.

Но эти 3 шага можно совместить в один и сразу получить 12 значений аддитивной сезонности.
Для этого СУММЕСЛИ разделим на СЧЁТЕСЛИ, как на картинке:

счетесли и суммесли в excel

Вводим формулу, протягиваем на 12 месяцев, получаем сезонность:

формула счетесли

Еще раз её разберем =СУММЕСЛИ($G$8:$AY$8;D9;$G$7:$AY$7)/СЧЁТЕСЛИ($G$8:$AY$8;D9)

СУММЕСЛИ($G$8:$AY$8 (зафиксированный диапазон с номерами месяцев);D9 (номер месяца, для которого мы рассчитываем сезонность); $G$7:$AY$7 (зафиксированный диапазон со значениями разности между рядом и средней — пункт 2))

СЧЁТЕСЛИ($G$8:$AY$8 (зафиксированный диапазон с номерами месяцев);D9 (номер месяца, для которого мы рассчитываем сезонность))

Для этого продлим значения ряда с помощью скользящей средней к 3-м месяцам и скорректируем её аддитивной сезонностью.

Продлим скользящую среднею к 3-м месяцам в будущее на 12 месяцев:

формула счетесли в excel

Для этого к скользящей средней прибавим аддитивную сезонность. Сезонность для каждого месяца подтянем с помощью функции Excel ГПР.

Прогноз = средние продажи за последние 3 месяца + сезонность:

=СРЗНАЧ(AW4:AY4 (средние продажи за 3 последних месяца))+ГПР(AZ3 (искомый номер месяца$D$9:$O$13 (зафиксированная ссылка на таблицу с сезонностью);5 (номер строки с сезонностью);0)

формула в excel суммесли

Очень часто сталкиваюсь с ошибками посетителей наших онлайн курсов по прогнозированию, когда усредняют отклонения воспользовавшись стандартной формулой СРЗНАЧ и одну из ссылок ставят на не соответствующий месяц, т.е. считаем средние отклонения января, а одна из ссылок оказывается на декабрь или февраль, в результате чего коэффициент сезонности получается не верным.

Для того чтобы этой ошибки сознательно избежать и заставить думать машину используйте формулы Excel =СУММЕСЛИ и СЧЁТЕСЛИ. Комбинация данных формул поможет вам рассчитать среднее значение по заданному критерию.

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

Forecast4AC PRO сэкономит вам кучу времени позволит делать более точные прогнозы.

Если есть вопросы или комментарии, пожалуйста, пишите в форме ниже!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

Тестируйте возможности платных решений:

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться см. Если же вы хотите что-то уточнить, я с радостью помогу!
Эта функция позволяет определить сумму значений, которые соответствуют нескольким условиям. Например, перед нами была поставлена задача определить суммарную заработную плату всех менеджеров, работающих в южном филиале компании.
функция если

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

  • диапазон — $G$8:$AY$8 — диапазон с номера месяцев за весь период;
  • критерий — D9 — номер месяца для которого суммируем отклонения и рассчитываем сезоность;
  • диапазон для суммирования — $G$7:$AY$7 — диапазон с отклонениями рассчитанными в пункте 2).

Иногда, функция БСЧЁТ() действительно удобна, особенно при использовании сложных критериев с подстановочными знаками или критериев на основе формул. Подробно эти задачи решаются в статьях Подсчет значений с множественными критериями ( Часть 2 , Часть 3 , Часть 4 ).

Примеры использования условий в формулах Excel

формула если примеры нескольких условий

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

Условная функция ЕСЛИ()

В этой функции проверяется истинность одного или нескольких логических выражений и выбирается дальнейшее действие. В логических выражениях используют знаки операций отношения между сравниваемыми величинами.

операции отношения в логических выражениях

Операции отношения в логических выражениях

Простое условие

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

операции отношения в логических выражениях

Блок-схема «Простое условие». Определение отрицательных и неотрицательных чисел

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

Если результатом должны быть данные, полученные в результате вычислений, то смотрим следующий пример. Выполним увеличение неотрицательного числа на 10, а отрицательное оставим без изменений.

формула если простое условие в эксель

Блок-схема «Простое условие». Расчет данных

На схеме видно, что при выполнении условия число увеличивается на десять, и в формуле Excel записывается расчетное выражение А1+10 (выделено зеленым цветом). В противном случае число не меняется, и здесь расчетное выражение состоит только из обозначения самого числа А1 (выделено красным цветом).

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

Задание:
Процентная ставка прогрессивного налога зависит от дохода. Если доход предприятия больше определенной суммы, то ставка налога выше. Используя функцию ЕСЛИ, рассчитайте сумму налога.

прогрессивный налог в эксель

В расчетной формуле адреса ячеек записаны в виде A$5, B$6, B$5. Знак доллара делает фиксированной часть адреса, перед которой он установлен, при копировании формулы. Здесь установлен запрет на изменение номера строки в адресе ячейки.

Составное условие

Составное условие состоит из простых, связанных логическими операциями И() и ИЛИ().

И() — логическая операция, требующая одновременного выполнения всех условий, связанных ею.
ИЛИ() — логическая операция, требующая выполнения любого из перечисленных условий, связанных ею.

Логическая операция И()

Например: Рассмотрим электронную таблицу «Ведомость сдачи вступительных экзаменов». Для зачисления абитуриента в ВУЗ, ему необходимо преодолеть проходной балл, и по математике отметка должна быть выше 70 баллов.
Посмотрите внимательно на рисунок ниже.

Составное условие в Excel

В этом примере функция ЕСЛИ() использует составное условие, связанное логической операцией И(). Обратите внимание: абитуриент Петров не зачислен, хотя сумма его баллов равна проходному.

примел логической функции И()

Для проведения расчетов необходимо вписать следующие формулы:

и скопировать по соответствующим столбцам до 16 строки включительно.

Логическая операция ИЛИ()

Посмотрим пример с логической операцией ИЛИ() в той же таблице. Только немного изменим условие задачи. Для зачисления абитуриента достаточно иметь по математике более 60 баллов или общую сумму баллов не меньше проходного. Рисунок ниже.

Пример Если несколько условий в excel

Абитуриент Сидоров зачислен, хотя не набрал проходной балл. Вот формула =ЕСЛИ(ИЛИ(B7>60;E7>D2;);»зачислен»;»не зачислен»). Здесь использована операция ИЛИ(), поэтому достаточно выполнение хотя бы одного условия. Что и произошло, первое условие B7>60 истинно. Оно привело к выводу сообщения о зачислении абитуриента.

пример логической функции ИЛИ()

Для проведения расчетов необходимо вписать следующие формулы:

и скопировать по соответствующим столбцам до 15 строки включительно.

Вложенные условия

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

вложенные условия формула если

В данном примере вторая функция ЕСЛИ() является вложенной и записывается на месте действия, которое вызывается при не выполнении условия первой функции. В Excel последних версий допускается делать до 64 вложений.

пример вложенной функции ЕСЛИ()

Для проведения расчетов необходимо вписать следующие формулы:

и скопировать по соответствующим столбцам до 19 строки включительно.

В этой статье я постарался максимально подробно и доходчиво объяснить применение условной функции в Excel. Конечно же, в Excel есть и другие функции с условием, но о них поговорим в следующей статье. Друзья, если вам была интересна и полезна данная информация, не забывайте делиться ею в социальных сетях. А также буду рад прочитать ваши комментарии.

Формула в Excel Счет Если Несколько Условий • Относительные функции

Дорогой читатель! Вы посмотрели статью до конца. Получили вы ответ на свой вопрос? Напишите в комментариях пару слов. Если ответа не нашли, укажите что искали или откройте содержание блога.

ОЧЕНЬ ВАЖНО! Оцени лайком или дизлайком статью!

счетеслимн excel примеры

Счетеслимн excel примеры Рассмотрим функции: СЧЁТЕСЛИ(диапазон;критерий) и СЧЁТЕСЛИМН(диапазон_условия; условие; . ). Функция счетесли подсчитывает количество непустых

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
ДМИН DMIN Находит минимальное значение среди выделенных записей базы данных ДМИН A6 E12 ;5; A1 F3 ДМИН A6 E12 ; Прибыль ; A1 F3 DMIN A6 E12 ,5, A1 F3 вернет сумму 75 000р. Если же вы хотите что-то уточнить, я с радостью помогу!
Логический оператор ЕСЛИ в Excel применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.

Excel Счет Если Несколько Условий Или. Задача 2 условие и | 📂Все о программе Word

БСЧЁТ (DCOUNT) — Подсчитывает количество числовых ячеек в базе данных:
=БСЧЁТ( A6:E12 ;5; A1:F3 )
=БСЧЁТ( A6:E12 ;»Прибыль»; A1:F3 )
=DCOUNT( A6:E12 ,5, A1:F3 ) вернет число 2 , т.к. только две строки в таблице отвечают критериям

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

Самый большой COUNTIF Функция — это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию. Он поддерживает логические операторы (, =,> и <), а также символы подстановки (? И *) для частичного сопоставления.

Синтаксис и аргументы

Самый большой COUNTIF функция возвращает количество ячеек, удовлетворяющих указанным критериям.

Основное использование

Предположим, вы хотите подсчитать количество ячеек, равное 2, вы можете использовать формулу, как показано ниже:

Нажмите Enter Клавиша возвращает количество ячеек, равное 2.

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

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

Предположим, что существует диапазон (B2: D5) чисел, вы хотите подсчитать количество ячеек, превышающих 65, используя следующую формулу:

Если вы хотите подсчитать ячейки меньше числа, например, подсчитать ячейки меньше 50, просто используйте формулу, подобную этой =COUNTIF(B2:D5,»

Расширенное использование

Скачать образец файла

Относительные функции:

Excel COUNTA функцияСамый большой COUNTA функция возвращает количество ячеек, содержащих число, текст, значение ошибки или пустой текст «», за исключением реальных пустых ячеек.

Excel COUNTBLANK функцияСамый большой COUNTBLANK функция возвращает количество пустых ячеек. Функция СЧИТАТЬПУСТОТЫ — это встроенная статистическая функция, которую можно использовать как функцию рабочего листа (WS) в Excel.

Excel COUNT функцияСамый большой COUNT Функция используется для подсчета количества ячеек, содержащих числа, или подсчета чисел в списке аргументов. Возьмите пример, COUNT(1,2,3,4,11,2,1,0) возвращает 8.

Excel COUNTIFS функцияСамый большой COUNTIFS Функция возвращает количество ячеек, соответствующих одному или нескольким критериям. Функция СЧЁТЕСЛИМН является одной из встроенных статистических функций, и ее можно использовать как функцию рабочего листа (WS) в Excel.

Относительные статьи:

Считайте ячейки равнымиЕсли вы хотите подсчитать ячейки, точно соответствующие заданному критерию, вы можете выбрать COUNTIF функция или SUMPRODUCT (в сочетании с EXACT function) функция для точного подсчета ячеек без учета регистра или с учетом регистра.

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

Считайте ячейки равными x или yЕсли вы хотите подсчитывать ячейки, если они соответствуют хотя бы одному из заданных условий (логика ИЛИ) в Excel, т.е. подсчитывать ячейки, равные либо x, либо y, вы можете использовать несколько COUNTIF формулы или объедините COUNTIF формула и SUM формула для выполнения этой работы.

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

Зачастую пользователям требуется провести поиск по нескольким названиям или условиям сразу. В этом случае вы можете объединить несколько формул в одну с помощью знака «+». Посчитаем, сколько позиций столов в количестве более 140 встречается в таблице:

Формула в Excel Счет Если Несколько Условий • Относительные функции

Формула в Excel Счет Если Несколько Условий • Относительные функции

Формула в Excel Счет Если Несколько Условий • Относительные функции

  • если ваша формула отсылается на ячейки из другой базы данных, то в момент вычисления она должна быть открыта в Excel;
  • при поиске текста не учитывается регистр символов;
  • чтобы программа искала сами знаки >,

Как посчитать, содержит ли ячейка текст или часть текста в Excel?

Подсчитайте, если ячейка содержит текст или часть текста, с помощью функции СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ может помочь подсчитать ячейки, содержащие часть текста в диапазоне ячеек в Excel. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку (например, E5), скопируйте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер заполнения вниз, чтобы получить все результаты.

  • В формуле B5: B10 — это диапазон ячеек, который нужно подсчитать. D5 — это ссылка на ячейку, содержащую то, что вы хотите найти. Вы можете изменить ссылочную ячейку и критерии в формуле по своему усмотрению.
  • Если вы хотите напрямую вводить текст в формуле для подсчета, примените следующую формулу:=COUNTIF(B5:B10,»*Apple*»)
  • В этой формуле регистр не учитывается.

Самый большой Выбрать определенные ячейки полезности Kutools for Excel может помочь вам быстро подсчитать количество ячеек в диапазоне, если они содержат определенный текст или часть текста. После получения результата во всплывающем диалоговом окне все совпавшие ячейки будут выбраны автоматически. .Загрузите Kutools for Excel прямо сейчас! (30-дневная бесплатная трасса)

Счетные ячейки содержат текст с функцией СЧЁТЕСЛИ

Как показано на скриншоте ниже, если вы хотите подсчитать количество ячеек в определенном диапазоне, которые содержат только текст, метод в этом разделе может вам помочь.

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

Подсчитайте, если ячейка содержит текст или часть текста с помощью Kutools for Excel

Чаевые: Помимо приведенной выше формулы, здесь представлена ​​замечательная функция, позволяющая легко решить эту проблему. С Выбрать определенные ячейки полезности Kutools for Excel, вы можете быстро подсчитать, содержит ли ячейка текст или часть текста, щелкнув мышью. С помощью этой функции вы даже можете подсчитать с помощью OR или AND, если вам нужно. Пожалуйста, сделайте следующее.

Перед использованием Kutools for Excel, вам нужно потратить несколько минут, чтобы в первую очередь.

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

2. Нажмите Kutools > Выберите > Выбрать определенные ячейки.

3. в Выбрать определенные ячейки диалоговое окно, вам необходимо:

  • Выберите Ячейка вариант в Тип выбора раздел;
  • В Конкретный тип раздел, выберите Комплект в раскрывающемся списке введите Apple в текстовом поле;
  • Нажмите OK кнопку.
  • Затем появляется окно подсказки, в котором указано, сколько ячеек соответствует условию. Щелкните значок OK кнопка и все соответствующие ячейки выбираются одновременно.

Наконечник. Если вы хотите получить бесплатную (60-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

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

Подсчитайте, начинаются ли ячейки или заканчиваются определенным текстом в Excel Предположим, у вас есть диапазон данных, и вы хотите подсчитать количество ячеек, которые начинаются с «kte» или заканчиваются «kte» на листе. Эта статья знакомит вас с некоторыми хитростями вместо ручного подсчета.Нажмите, чтобы узнать больше …

Подсчитайте конкретное значение на нескольких листах Как можно подсчитать конкретные значения на нескольких листах? Например, получить количество вхождений определенного значения «Excel» из нескольких листов. В этом руководстве представлены методы, позволяющие это сделать.Нажмите, чтобы узнать больше …

Мастер функций

Для неопытного пользователя легче всего производить подсчет ячеек, содержащих числа, используя формулу СЧЁТ при помощи Мастера функций.

Кликаем по пустой ячейке на листе, в которую будет выводиться результат расчета. Жмем на кнопку «Вставить функцию».

Есть и другой вариант запуска Мастера функций. Для этого после выделения ячейки нужно перейти во вкладку «Формулы». На ленте в блоке инструментов «Библиотека функций» жмем на кнопку «Вставить функцию».

Существует ещё один вариант, наверное, самый простой, но вместе с тем требующий хорошей памяти. Выделяем ячейку на листе и жмем комбинацию клавиш на клавиатуре Shift+F3.

Во всех трёх случаях запустится окно Мастера функций. Для перехода к окну аргументов в категории «Статистические» или «Полный алфавитный перечень» ищем элемент «СЧЁТ». Выделяем его и кликаем по кнопке «OK».

Также окно аргументов можно запустить другим способом. Выделяем ячейку для вывода результата и переходим во вкладку «Формулы». На ленте в группе настроек «Библиотека функций» жмем по кнопке «Другие функции». Из появившегося списка наводим курсор на позицию «Статистические». В открывшемся меню выбираем пункт «СЧЁТ».

Запускается окно аргументов. Единственным аргументом данной формулы может быть значение, представленное в виде ссылки или просто записанное в соответствующее поле. Правда, начиная с версии Excel 2007, таких значений может быть до 255 включительно. В более ранних версиях их было всего 30.

Данные занести в поля можно, набрав с клавиатуры конкретные значения или координаты ячеек. Но при наборе координат намного легче просто установить курсор в поле и выделить соответствующую ячейку или диапазон на листе. Если диапазонов несколько, то адрес второго из них можно занести в поле «Значение2» и т.д. После того, как значения занесены, жмем на кнопку «OK».

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

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина “Копейка”.

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в “Копейку”, например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

  • Диапазон – это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае – это диапазон с фамилиями менеджеров продаж.
  • Критерий – это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак – один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву “П”, а заканчивается на “В” – критерий П*В. Строчные и прописные буквы не различаются.
  • Диапазон_суммирования – это те ячейки, значения которых мы хотим сложить, т.е. нашем случае – стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3–Условие3), и четвертую, и т.д. – при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться – см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в “Копейку” и от Григорьева, то в ячейке этого столбца будет значение 1, иначе – 0. Формула, которую надо ввести в этот столбец очень простая:

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter – тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев – ячейки, содержащие условия отбора – и указать затем этот диапазон функции как аргумент:

Функция СУММЕСЛИМН в Excel

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

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

  1. Диапазон суммирования. Ячейки, которые будут суммироваться.
  2. Диапазон условия 1 – диапазон, который будет проверяться на предмет соответствия определенному критерию.
  3. Условие 1 – непосредственно условие.
  4. Диапазон условия 2 – второй диапазон, который будет проверяться на предмет соответствия критерию.
  5. Условие 2 – второе условие.

Дальше логика аналогична. В результате, нами была определена заработная плата всех менеджеров Южного филиала.

Как посчитать количество ячеек по нескольким условиям в Excel?

Пример 3. В таблице приведены данные о количестве отработанных часов сотрудником на протяжении некоторого периода. Определить, сколько раз сотрудник работал сверх нормы (более 8 часов) в период с 03.08.2018 по 14.08.2018.

В качестве первых двух условий проверки указаны даты, которые автоматически преобразовываются в код времени Excel (числовое значение), а затем выполняется операция проверки. Последний (третий) критерий – количество рабочих часов больше 8.

Формула в Excel Счет Если Несколько Условий • Относительные функции

Поиск по части слова или букве

Инструмент позволяет подсчитать количество клеток, которые начинаются или заканчиваются на определенную букву. Также поиск можно осуществить по части слова. Рассмотрим следующий пример:

  1. Найдем количество позиций, которые начинаются на букву «Т». Для этого вновь выбираем отдельную ячейку и начинаем писать «СЧЕТЕСЛИ».
  1. Выбираем диапазон с названиями продукции.
  1. Далее вписываем букву «Т» и ставим знак «*» (звездочка). Если поставить его после вписанного знака, то буква будет считаться начальной. Если поставить звездочку перед буквой, то оператор проведет поиск по последним символам слов.
  1. Жмем Enter и смотрим на результат.

Функция СЧЁТЕСЛИМН в Excel

​ от проданной продукции​ 2-м критериям (Условие​) =5, которое удовлетворяет​

Синтаксис

​=БСЧЁТА(A1:B13;A1;D14:F15) или БСЧЁТ(A1:B13;B1;D14:F15), которые требуют наличия​ критериями.​

​формула массива =СУММ((B2:B13=D2))​​ ячейку или текста,​ и Климов превысили​В условии можно использовать​Функция​

​ еженедельно и даже​​Прописываем аргументы.​ зарплаты.​Критерий – определяет, какие​ на пустую ячейку​ клавиш Ctrl+Shift+Enter:​ составляет техника фирмы​ И) можно без​ критерию >=5, поэтому​ возьмем таблицу с​ отдельной таблички с​

​Рассмотрим задачу, когда 1​​формула массива =СЧЁТ(ЕСЛИ((B2:B13>=D2)*(B2:B13​ которые определяют, какие​ квоту для периодов​ подстановочные знаки: вопросительный​СЧЁТЕСЛИМН​

​ чаще. Пользуйтесь поиском​​диапазон суммирования – ячейки​Аналогично можно подсчитать зарплаты​ ячейки из диапазона​ или пустая строка​Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив​​ Samsung.​​ применения формул с​ второе значение в​ двумя столбцами: текстовым​

Замечания

​ критериями.​ текстовый критерий с​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют​ ячейки требуется учитывать.​ Q1, Q2 и​ знак (?) и​применяет критерии к​ по форуму.​ с зарплатой;​ всех менеджеров, продавцов-кассиров​ будут выбраны (записывается​ «», результат вычисления​ дробных чисел 1/количество_вхождений,​Вид исходной таблицы данных:​ помощью стандартного Автофильтра.​

​ массиве =1 и​ «Фрукты» и числовым​О подсчете с множественными​ подстановочным знаком применяются​​ наличия отдельной таблички​​ Например, условие может​

​ Q3 (только в​ звездочку (*). Вопросительный​ ячейкам в нескольких​r_kot​диапазон условия 1 –​ и охранников. Когда​ в кавычках).​ для любого диапазона​ например, для пользователя​Для расчета используем формулу:​Установите автофильтр к столбцу​ т.д.​​ «Количество на складе»​​ критериями можно почитать​

Пример 1

​ к значениям текстового​ с критериями.​ быть выражено следующим​ Q2).​ знак соответствует любому одиночному​ диапазонах и вычисляет​: Поправил формулу. Вместо​ ячейки с указанием​ табличка небольшая, кажется,​Диапазон суммирования – фактические​ ячеек будет числовое​ с ником sam​=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17​

Формула если в excel примеры несколько условий
Запускается окно аргументов. Единственным аргументом данной формулы может быть значение, представленное в виде ссылки или просто записанное в соответствующее поле. Правда, начиная с версии Excel 2007, таких значений может быть до 255 включительно. В более ранних версиях их было всего 30.
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Используйте countif с несколькими критериями в Excel В Excel функция СЧЁТЕСЛИ может помочь нам вычислить количество определенного значения в списке. Если же вы хотите что-то уточнить, я с радостью помогу!
Использование функции БСЧЁТ() доступно только «продвинутым» пользователям, поэтому при встрече с ней у неподготовленных пользователей часто возникают негативные эмоции. Однако, есть хорошие новости — без функции БСЧЁТ() можно вообще обойтись, заменив ее функциями СУММПРОИЗВ() , СЧЁТЕСЛИМН() или формулами массива .

Excel значение ячейки несколько условий • Вэб-шпаргалка для интернет предпринимателей!

  • В формуле B5: B10 — это диапазон ячеек, который нужно подсчитать. D5 — это ссылка на ячейку, содержащую то, что вы хотите найти. Вы можете изменить ссылочную ячейку и критерии в формуле по своему усмотрению.
  • Если вы хотите напрямую вводить текст в формуле для подсчета, примените следующую формулу:=COUNTIF(B5:B10,»*Apple*»)
  • В этой формуле регистр не учитывается.

Рассмотрим использование формул «СУММЕСЛИ» и «СЧЕТЕСЛИ»
на примере расчета прогноза с помощью модели «Скользящей средней к 3-м месяцам с аддитивной сезонностью».

Оставить отзыв

Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.