Как в кабинете

Excel суммировать по условию

Function BDSUMM 9 способов как используется функция БДСУММ в Excel

Продолжу свое рвение обрисовать обилие функций в Excel и последующей на рассмотрении у нас функция БДСУММ. Это очередной из представителей функций суммирования, но со своими специфичными критериями. Функция БДСУММ в Excel отыскивает и суммирует числа в вашей таблице по определенным вами аспектам, это ее основное свойство.

Положа руку на сердечко могу сказать, что много расчётов и вычислений можно создать и без нее используя различные функции массивов, функцию СУММЕСЛИМН либо СУММПРОИЗВ, но если же для вас необходимо создать сложную подборку используя «подстановочные» знаки, то тогда для вас буквально необходимо употреблять героя нашей статьи.

Для начала разглядим синтаксис, который употребляет функция БДСУММ в Excel:

=БДСУММ(спектр вашей базы данных; поле поиска; условие поиска), где

  • Спектр вашей базы данных — является неотклонимым аспектом и указывается спектр, из которого и будут по аспектам изыматься нужные для вас значения, непременное условие, что бы 1-ая строчка содержала в для себя заголовок вашей таблицы;
  • Поле поиска — это неотклонимый аспект, указывается для определения, по которому полю создавать поиск и суммирование чисел. Указывать можно как заглавие поля, другими словами текстовое значение, типа «Продукт», «Страна», с неотклонимым взятием аргумента в кавычки, так и числовое значение, типа, 1,2,3. для определения номера поля либо просто указать ссылку на необходимое поле, решать для вас;
  • Условие поиска — это неотклонимый аргумент, который содержит в для себя спектр с обозначенными в нём аспектами для суммирования значений. Подобная структура таблицы употребляется при разработке расширенного фильтра.

Function BDSUMM 2 9 способов как используется функция БДСУММ в Excel

При работе с функцией БДСУММ стоить отметить несколько критерий, на которые стоит уделять свое внимание при работе:

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

Итак, теоретическую часть, я считаю выполненной, приступим сейчас к практическому применению функции БДСУММ в собственной работе, для этого разглядим несколько примеров для выполнения, я примеры сделал по принципу расширенного фильтра, но заместо сбора значений по аспекту будет суммирование:

  • С одним числовым аспектом;
  • С одним текстовым аспектом;
  • Суммирование по двум аспектам по различным столбцам;
  • Суммирование по одному из 2-ух критерий в одном столбике;
  • Суммирование по одному из 2-ух критерий в 2-ух различных столбиках;
  • Суммирование по двум текстовым аспектам по двум столбикам;
  • Внедрение результата формулы для получения аспекта отбора и суммирования;
  • Суммирование по трём аспектам;
  • Суммирование по текстовому аспекту с учётом регистра.

Функция БДСУММ с одним числовым аспектом

Итак, для начала разглядим обычной пример с одним числовым аспектом, для этого выберем столбик «Сбор» и укажем что нам нужно деревья с урожайность «>=10». Что бы получить итог нам нужна формула такового вида (советую употреблять абсолютные ссылки):

=БДСУММ($B$6:$G$12;E6;E2:E3),

где, $B$6:$G$12 спектр в котором мы будем суммировать, E6 — столбик в котором мы будем суммировать и E2:E3 спектр в которые мы ввели аспекты для суммирования. В итоге формула отыскала 3 позиции на общую сумму 34.

Для получения аналогично результата вы сможете пользоваться также таковыми формулами:

=СУММЕСЛИ(E7:E12;»>=10″)

=СУММЕСЛИ(E7:E12;E3)

Function BDSUMM 3 9 способов как используется функция БДСУММ в Excel

Функция БДСУММ с одним текстовым аспектом

Сейчас разглядим, как ведет себя функция БДСУММ с текстовыми аспектами, в общем всё остается так и в прошлом примере кроме того как указывается текстовый аспект, а он указывается лишь в таком виде: =»=с.Серово» тогда и итог у вас получится, по другому формула не сумеет распознать ваш аспект. Сейчас подставляем этот аспект в формулу и получаем:

=БДСУММ($B$6:$G$12;E6;C2:C3), как лицезреем вышло лишь конфигурации спектра аспекта.

Для получения похожего результата для вас понадобится функция СУМЕСЛИ:

=СУММЕСЛИ(C7:C12;»с.Серово«;E7:E12)

Function BDSUMM 4 9 способов как используется функция БДСУММ в Excel

Суммирование по двум аспектам по различным столбцам

Пример усложняется применением 2-ух критериев, но ничего принципно новейшего мы использовать не будем, укажем текстовый аспект «с.Серово» и числовой аспект «>=10», оставив поле суммирование «Сбор», мы получим изменение формулы лишь по крайнему аргументу, как итог. Сейчас наша формула будет смотреться так:

=БДСУММ($B$6:$G$12;E6;C2:E3), опять видите конфигурации лишь адреса спектра аспекта.

Другой вариант можно получить при помощи функции СУММЕСЛИМН и так:

=СУММЕСЛИМН(E7:E12;C7:C12;C3;E7:E12;E3)

=СУММЕСЛИМН(E7:E12;C7:C12;»с.Серово»;E7:E12;»>=10″)

Function BDSUMM 5 9 способов как используется функция БДСУММ в Excel

Суммирование по одному из 2-ух критерий в одном столбике

Разглядим очередной вариант как применяется функция БДСУММ, но на данный момент будем употреблять не единичный аспект, а двойной, но для 1-го поля. Непременно два аспекта должны быть в различных строчках. Сущность формулы сводится к тому что, она проходит один и этот же спектр дважды считая любой из критериев раздельно. Для этого примера формула будет смотреться так:

=БДСУММ($B$6:$G$12;E6;C2:C4), тут опять изменяем спектр аспекта, но не в ширину, а в высоту.

Также заменителем, вы сможете употреблять сумму функции СУММЕСЛИ:

=СУММЕСЛИ(C7:C12;C3;E7:E12)+СУММЕСЛИ(C7:C12;C4;E7:E12).

Function BDSUMM 6 9 способов как используется функция БДСУММ в Excel

Суммирование по одному из 2-ух критерий в 2-ух различных столбиках

Пятым примером внедрения БДСУММ в Excel станет суммирование по одному из 2-ух критерий в 2-ух различных столбиках и непременно аргументы необходимо располагать все в новейших строчках, это дозволит их суммировать по очереди. Для этих целей нам пригодится формула:

=БДСУММ($B$6:$G$12;D6;C2:D4), принцип формирования формулы сохраняется, окромя спектра аспекта, который включает в себя три строчки: заголовок и два аспекта.

Function BDSUMM 7 9 способов как используется функция БДСУММ в Excel

Суммирование по двум текстовым аспектам по двум столбикам

В данном примере работы БДСУММ в Excel разглядим фактически полный аналог ранее рассматриваемого примера, когда были два аспекта в 2-ух столбика, но там были числовой и текстовый аспект, а тут разглядим суммирование по двум текстовым аспектам и по двум столбикам. Используем аспекты «=»=с.Иваново»» и «=»=Вишня«», которые мы и укажем в спектре критериев. Означает наша формула будет иметь последующий вид:

=БДСУММ($B$6:$G$12;D6;B2:C3).

Function BDSUMM 8 9 способов как используется функция БДСУММ в Excel

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

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

=G7>СРЗНАЧ($G$7:$G$12), не забываем о абсолютных ссылок для закрепления спектра, что бы при переборе формулой значений они не сползли вниз, а вот значение G7 обязано скользить по всему спектру для определения «ЛОЖЬ» это либо «ИСТИНА».

Function BDSUMM 9 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;G6;$G$2:$G$3)

А если для вас весьма любопытно другое решение вопросца, то тогда испробуйте вариант с функцией СУММЕСЛИ в таком виде:

=СУММЕСЛИ($G$7:$G$12;»>«&СРЗНАЧ($G$7:$G$12))

Function BDSUMM 10 9 способов как используется функция БДСУММ в Excel

Функция БДСУММ по трём аспектам

В этом примере посчитаем среднее по продажам с выращенных плодов в двоих сёлах: «с.Иваново» и «с.Комфортное». Основную идею отбора по аспектам я уже описывал, потому повторятся не буду, просто скажу, что это будет соединение ранее рассмотренных критериев. Для получения результата нам нужна функция БДСУММ в таком виде:

=БДСУММ($B$6:$G$12;G6;$C$2:$G$4)

Function BDSUMM 11 9 способов как используется функция БДСУММ в Excel

Суммирование по текстовому аспекту с учётом регистра

Как я упоминал ранее, функция БДСУММ может создавать поиск не только лишь с подстановочными знаками, да и с учётом регистра букв, сейчас конкретно таковой вариант. Для начала определим условие по отбору аспекта, если встречается заглавие «с.ИВАНОВО», большими, то мы производим суммирование, для определения этого аспекта нам нужна формула:

Как Начать Применять СЧЕТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel

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

В этом уроке, вы узнаете, как можно употреблять три очень нужных формулы в Excel: СУММЕСЛИ, СЧЕТЕСЛИ и СРЗНАЧЕСЛИ.

Тут у нас перечень транзакций в месяц с систематизацией по типам расходов.

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

С правой стороны, есть ячейка: Dining Out Expense в которой употребляются эти три формулы, что бы отследить мои издержки:

  • СЧЕТЕСЛИ — Употребляется для того чтоб подсчитать количество раз, когда в перечне возникает слово «Restaurant».
  • СУММЕСЛИ — Рассчитывает сумму всех издержек со словом «Restaurant».
  • СРЗНАЧЕСЛИ — Рассчитывает среднее значения для расходов со словом «Restaurant».

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

  • СУММЕСЛИ — суммирует значения, удовлетворяющие данным условиям, как к примеру, суммирует все издержки из одной группы.
  • СЧЕТЕСЛИ — Подсчитывает количество ячеек в спектре, удовлетворяющих данному условию, к примеру, сколько раз некое заглавие повторяется в перечне.
  • СРЗНАЧЕСЛИ — Рассчитывает условное среднее значение; вы сможете высчитать среднюю оценку лишь для экзаменов.

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

СЧЁТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel (Маленький ВидеоУрок)

Скринкаст — один из наилучших методов поглядеть и приобрести новейший навык, включая и этот — как начать употреблять эти три главные формулы в Microsoft Excel. Поглядите этот видеоурок ниже, что выяснить как я работаю в Excel. Загрузите безвозмездно Книжку с примерами, которую я использую в этом уроке.

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

Как Применять СУММЕСЛИ в Excel

Используйте для данной нам части урока, вкладку SUMIF (лист с таковым заглавием) в закачанном вами файле примеров.

Задумайтесь о СУММЕСЛИ, как о методе сложения значений, удовлетворяющих определенному правилу. Мы можем суммировать значения из перечня, которые принадлежат определенной группы, либо все значения которые больше либо меньше какого-то значения.

Ах так работает формула СУММЕСЛИ:

=СУММЕСЛИ(ячейки которые необходимо проверить на условие; само условие; какие ячейки ложить при ублажении условию)

Давайте вернемся вспять к моим затратам на ресторан в примере, что бы разобраться, как работает формула СУММЕСЛИ. Ниже, я представил перечень моих транзакций в месяц.

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

Я желаю знать две вещи:

  1. 1.Сколько всего средств я издержал в ресторанах в этом месяце.
  2. Все издержки в этом месяце, из хоть какой группы, которые превысили значение 50$.

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

Сумма Издержек на Ресторан

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

Вот формула, которую я использую в этом примере:

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

  • Глядит, что находится в ячейках с В2 по В17 в категориях издержек
  • Употребляет слово «Restaurant» в качестве аспекта для выбора того, что суммировать
  • Употребляет значения в ячейках С2-С17, что бы суммировать

Когда я жму ввод, Excel вычисляет сумму моих расходов на ресторан. Используя СУММЕСЛИ, просто созодать легкие статистические расчеты, которые посодействуют для вас выслеживать данные определенного типа.

Издержки Выше 50$

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

Давайте напишем ординарную формулу, что бы отыскать сумму всех издержек выше 50$:

В этом случае, формула чуток проще: потому что мы суммируем те же величины, что мы и проверяем на условие (С2-С17), мы просто должны указать эти ячейки. Потом мы должны добавить точку с запятой и позже «>50», что бы суммировать лишь те значения, которые больше 50$.

Сумма всех издержек, которые превосходят 50 баксов, при помощи обычной формулы в Excel/

В этом примере употребляется символ «больше», но в качестве доборной тренировки: попытайтесь суммировать все мелкие расходы, к примеру все расходы, которые меньше 20 баксов либо меньше.

Как Применять СЧЕТЕСЛИ в Excel

Используйте для данной нам части урока, вкладку COUNTIF (лист с таковым заглавием) в закачанном вами файле примеров.

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

Вот общий формат для формулы СЧЕТЕСЛИ:

= СЧЕТЕСЛИ(ячейки которые нужно подсчитывать, аспект по которым ячейку принимать в расчет)

Используя те же данные, давайте посчитаем случаи возникновения таковой инфы:

  • Сколько раз в течение месяца я брал одежку
  • Количество издержек, значение которых равно либо больше 100 баксов

Число Случаев Покупки Одежки

Мой 1-ый СЧЕТЕСЛИ будет глядеть на тип расходов и подсчитывать количество покупок с группой «Clothing» посреди моих транзакций.

Окончательная формула будет смотреться последующим образом:

Эта формула глядит в столбец с заглавием «Expense Type», подсчитывает количество раз, сколько ей повстречалось слово «clothing», и суммирует их. В итоге выходит 2.

Формула СЧЁТЕСЛИ подсчитывает количество расходов, с заглавием одежка «Clothing» и суммирует их.

Количество Издержек на Сумму 100$+

Сейчас давайте разглядим количество транзакций в моем перечне значение которых от 100 баксов и выше.

Вот формула, которую я буду употреблять:

Это обычная формула, состоящая из 2-ух частей: она просто показывает Excel перечень данных которые необходимо считать, и дает правило для подсчета. В этом случае, мы просматриваем ячейки С2-С17, на предмет значений, которые выше 100 баксов.

Подсчет числа транзакции на сумму 100$+ при помощи формулы СЧЕТЕСЛИ в Excel.

Как Применять СРЗНАЧЕСЛИ в Excel

Используйте для данной нам части урока, вкладку СРЗНАЧЕСЛИ (лист с таковым заглавием) в закачанном вами файле примеров.

И крайнее, давайте поглядим как употреблять формулу СРЗНАЧЕСЛИ. Я думаю, к истинному моменту, уже понятно, что СРЗНАЧЕСЛИ, можно употреблять для расчета среднего определенных величин, которые выбираются на основании критерий, которые мы зададим для Excel.

Формат формулы СРЗНАЧЕСЛИ последующий:

=СРЗНАЧЕСЛИ(ячейки которые необходимо проверить на условие; само условие; для каких ячеек рассчитывать среднее при ублажении условию)

Формат формулы СРЗНАЧЕСЛИ, таковой же как у формулы СУММЕСЛИ.

Давайте используем СРЗНАЧЕСЛИ формулу, для расчета 2-ух статистических величин для моих издержек:

  1. Средние издержки на рестораны..
  2. Среднее для всех издержек, которые меньше 25 баксов.

Издержки на Рестораны в Среднем

Чтоб подсчитать сколько в среднем я издержал на рестораны, я написал формулу СРЗНАЧЕСЛИ, что бы высчитать среднее значение на основании группы.

В данной нам формуле, есть три часть, любая из которых разделена точкой с запятой:

  • В2:В17 задает спектр ячеек для которых проверяется выполнение условия. Потому что категория издержек записана в этом столбце.
  • Слово «Restaurant» задает значение, которое необходимо находить.
  • И в конце концов С2-С17 — из которого берутся значения для расчета среднего.

И в конце концов, Excel усредняет все мои издержки на рестораны в путешествии. По формуле, которую я ему отдал.

Вы сможете так же испытать как работает эта формула заменив категорию «Restaurant» иной группой, к примеру «Clothing.»

Среднее для Издержек Меньше чем 25$

Если я смотрю за своими незначимыми покупками, и желаю знать сколько я издержал в среднем, я могу написать СРЗНАЧЕСЛИ для издержек, значение которых меньше некоторой величины.

Вот формула, которую я использую, для того, чтоб создать это:

Это обычная формула, которая инспектирует значения в столбце С, и рассчитывает среднее для значений которые меньше чем 25 баксов.

Обычная формула СРЗНАЧЕСЛИ для расчета среднего в случае издержек наименьших чем 25 баксов.

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

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

Главный момент, для всех формул «. ЕСЛИ», которые мы разглядели в этом уроке, то, что вы сможете использовать условия для ваших расчетов в Excel.

Чем больше знаешь, тем больше охото выяснить. Я предлагаю для вас еще три урока по Excel, с которыми вы сможете продолжить свое обучение (педагогический процесс, в результате которого учащиеся под руководством учителя овладевают знаниями, умениями и навыками):

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

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

Как в excel просуммировать ячейки по условию

На своём веб-сайте я много внимания уделял Excel функциям извлечения данных ( ИНДЕКС , ВПР , ПОИСКПОЗ , ГПР ). Они, непременно, весьма важны, но есть ещё один краеугольный класс формул, без которых просто никуда. Это, естественно же, формулы подсчёта и суммирования.

Эта статья будет посвящена формулам СУММЕСЛИМН (SUMIFS), СЧЁТЕСЛИМН (COUNTIFS) и СРЗНАЧЕСЛИМН (AVERAGEIFS). Если вы ранее употребляли формулы СУММЕСЛИ (SUMIF), СЧЁТЕСЛИ (COUNTIF) либо СРЗНАЧЕСЛИ (AVERAGEIF), то, ознакомившись со статьёй, сможете благополучно запамятовать о их существовании, потому что функционально *ЕСЛИМН формулы кроют *ЕСЛИ формулы, как бык овцу.

Мысль формулы

До этого, чем погружаться в детали функционирования формулы, полезно осознавать, что все-таки она делает по самой собственной сущности. Смотрим на набросок.

Мы располагаем таблицей, в которой нужно просуммировать значения из столбца Количество , но с учётом 2-ух критериев либо фильтров, если угодно. 1-ый аспект — столбец Магазин должен содержать значение Центр , 2-ой аспект — столбец Продукт должен содержать значение Комп . Оба аспекта действуют сразу либо, как молвят, по » И » (а не по » ИЛИ «). Другими словами нас интересует, сколько компов продал центральный магазин.

Для этого в ячейку G7 мы помещаем формулу СУММЕСЛИМН , в которую передаём 5 характеристик:

D3:D15 — спектр, содержащий числа, которые мы собираемся суммировать

B3:B15 — спектр, содержащий значения для сопоставления с аспектом 1

G3 — ячейка, содержащая, фактически, аспект 1 — значение, которое нас интересует — » Центр «

C3:C15 — спектр, содержащий значения для сопоставления с аспектом 2

G5 — ячейка, содержащая аспект 2 — » Комп «

Формула вернёт нам значение 14, так как 2 строчки таблицы удовлетворяют обоим нашим аспектам, и обе они содержат число 7. А сейчас давайте дадим наиболее формальное описание данной функции.

Синтаксис

Вот синтаксис формулы суммирования:

= СУММЕСЛИМН ( sum_range; criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

sum_range — 1-ый параметр — постоянно спектр суммирования.

criteria_range_1 — спектр для тестирования на соответствие аспекту 1

  • ссылки на ячейку, к примеру E3
  • выражения в виде текстовой строчки, к примеру » >10 «
  • значения в виде числа либо текста, к примеру 45 либо » Москва «

criteria_range_n и criteria_n — таковых критериев, которые описываются постоянно 2-мя параметрами, быть может до 127 штук. Принцип их организации остаётся постоянным.

Некие принципиальные замечания

Количество характеристик будет постоянно нечётным, потому что есть спектр суммирования, а аспекты идут парами. Это будет смотреться как 3, 5, 7, 9 и т.д. характеристик. Что будет соответствовать 1, 2, 3, 4 и т.д. критериев отбора строк для суммирования.

Спектры суммирования и спектры критериев должны постоянно иметь однообразный размер и схожую ориентацию (вертикальную либо горизонтальную)

Спектры суммирования и спектры критериев могут не быть векторами (другими словами размещаться наиболее чем в одной строке либо одном столбце), но снова же они должны соответствовать друг дружке. Другими словами если спектр суммирования имеет размер 10 на 3, то и все критериальные спектры должны быть строго 10 на 3. В целом, я не рекомендую схожую архитектуру ваших таблиц. Вы сэкономите для себя массу сил, если аспекты будут размещаться в отдельных столбцах. 1 столбец — 1 аспект.

СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН

Не откладывая в длинный ящик, сходу поглядим на синтаксис формул СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН . У СРЗНАЧЕСЛИМН отличие лишь в том, что она не суммирует числа, а вычисляет по ним среднюю величину.

= СРЗНАЧЕСЛИМН ( avg_range; criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

А СЧЁТЕСЛИМН считает строчки, а не числа, потому она не имеет аналога характеристик sum_range либо avg_range. Таковым образом её характеристики обрисовывают лишь аспекты и количество характеристик постоянно обязано быть чётным, в отличие от её коллег.

= СЧЁТЕСЛИМН ( criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

Файл примера

Скачать

Аспекты

Сейчас, когда мы получили некое представление по принципу работы СУММЕСЛИМН , мы можем погрузиться в наиболее сложные примеры, раскрывающие мощь и полезность данной нам формулы. В качестве полигона будем употреблять вот такую умную таблицу с именованием Sales :

СУММЕСЛИМН и умные таблицы

Поглядите на пример 1 нашего учебного файла, формула в L6 . Направьте внимание, как комфортно употреблять структурные ссылки умных таблиц в формулах совершенно и в СУММЕСЛИМН в частности.

L4 и L5 содержат значения критериев.

Аспект для периода дат

А ах так задать период дат (с. по . ) при суммировании:

Направьте внимание на весьма важную систему:

«> db_range»>$O$5

Это не что другое, как выражение в виде текстовой строчки. Причём это не статическое выражение, которое обычно приводят в справке по СУММЕСЛИМН , типа » >= 200000 «. Это выражение динамическое, другими словами — еще наиболее ценное и увлекательное. Всё что для вас необходимо — это вставить меж знаком операции » >= » и ссылкой на ячейку с параметром — символ операции сложения строк » & «.

Динамическая операция сопоставления

А почему бы не отдать на откуп юзеру право определять операцию для аспекта? В прошлом примере юзер мог выбирать порог N в аспекты » >=N «. А в этом примере юзер описывает и порог и саму операцию! А почему нет?

В L12 у нас находится выпадающий перечень, ссылающийся на 4 вероятные операции: >, >=, ? » — подменяет хоть какой знак, » * » — подменяет хоть какое количество знаков (в том числе и его отсутствие). К примеру аспект » *т* » сработает и на слово «Центр», и на слово «Восток». А, если б была таковая кандидатура, то сработал бы и на слово «опт» (это, как раз вариант, когда 2-ая звёздочка в «*т*» заменила ноль знаков справа от «т»).

Ответы на сложные вопросцы

При помщи *ЕСЛИМН формул можно отвечать на достаточно замысловатые вопросцы. К примеру, какая толика холодильников в Центре была продана по ценам, превосходящим средние цены холодильников по всем магазинам? А вот формула, которое это расчитывает:

Как видите мы употребляли 2 формулы СУММЕСЛИМН и 1 СРЗНАЧЕСЛИМН . СРЗНАЧЕСЛИМН возвратила нам средние цены по холодильникам. 1-ая СУММЕСЛИМН возвратила количество проданных телевизоров с ценами выше средних, а 2-ая формула возвратила полное количество холодильников, проданных в Центре. Обнаружив личное меж плодами 2-ух СУММЕСЛИМН , мы получили долю от единицы и просто выразили её в процентах.

Набор магазинов

А что созодать, если нам необходимы реализации 2-ух магазинов? В рамках одной формулы это решить в общем случае недозволено (способ через знаки подстановки не всепригоден, потому что магазины могут иметь на сто процентов неповторимые имена). Ответ предельно ясен — нужно употреблять 2 формулы и просто сложить их результаты. Хотя понятно, что таковой способ подойдёт далековато не во всех ситуациях.

Но есть и ограничения.

Надеюсь вы удостоверились, что *ЕСЛИМН довольно гибки, но есть один узенький момент, который нужно отлично осознавать. К примеру, я желаю знать, сколько раз я вел торговлю в Центре, предоставляя покупателям скидку наиболее либо равную 5%? Как я могу это выяснить? Лишь вычисляя по каждой строке отношение предоставленной скидки к базисной стоимости. Осознаете — по каждой строке нужно созодать вычисление и ассоциировать с 5%! Вот такое формулы *ЕСЛИМН сами, без вашей помощи создать не сумеют, потому что формула один раз вычисляет аспект, а позже ассоциирует его со всеми строчками критериального спектра, а нам нужно это созодать динамически. Но никто нам не мешает организовать доп столбец, который будет за ранее считать отношение скидки к базисной стоимости, а опосля этого можно уже остальную работу поручить формуле СЧЁТЕСЛИМН (см. пример 7).

Пустые ячейки

Если вы желаете, чтоб формула *ЕСЛИМН отреагировала на пустые ячейки, то следует употреблять аспекты «» (пустая строчка) либо » db_sheet»>Blank нашего учебного файла:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; » db_formula»>ЕСЛИ ), то таковая ячейка отреагирует лишь на аспект «», другими словами:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «» )

Не пустые ячейки

А вот, если аспектом будет то, что ячейка хоть что-то содержит, то можно употреблять такую форму:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «<>» )

Ну что ж, я надеюсь, что вы ощутили всю силу, сосредоточенную в формулах этого семейства. Фортуны!

Функция «суммеслимн» в Excel: примеры использования

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

Математические функции в Excel

В данной группы содержится порядка 80 функций. Тут можно отыскать операторы, способные просчитать значения электрической таблицы хоть какого формата. В Excel «суммесли» нередко встречается, а набор тригонометрических функций подступает для определенного круга юзеров. В чем все-таки выражается сущность арифметического оператора, у которого есть аналог в крайних версиях программного продукта «суммеслимн»? Его задачка суммировать значения, которые попадают под определенные аспекты.

В основном математические функции призваны заавтоматизировать работу юзера. Если появляются вопросцы по применению оператора, вызывают справку. Создать это можно в окне «Аргументы функции» нажатием на ссылку «Короткая справка» либо через F1.

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

Описание функции

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

В языке программирования VBA и британской версии редактора электрических таблиц принят синтаксис, написанный латинскими знаками. В российском аналоге – русскими.

Синтаксис функции смотрится последующим образом: =суммеслимн(диапазон_суммирования; [диапазон_условия1; условие1]; [диапазон_условия2; условие2]; …)

функция суммеслимн в excel примеры

Под спектром суммирования понимается массив, ячейки которого будут складываться, если они удовлетворяют следующим условиям. Иной блок аргументов в синтаксисе – диапазон_условия1; условие1. Они разрешают избрать в определенном массиве по первому фактору нужные ячейки, которые в предстоящем суммируются в границах начального интервала. Доп аспектами выступают следующие диапазон_условия10; условие10.

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

  • Если ассоциировать «суммесли» и «суммеслимн», то размещение аргументов в синтаксисе обозначенных операторов различается. У первой функции спектр суммирования находится на третьей позиции, у 2-ой – на первой.
  • Если некорректно ввести данные для «суммеслимн» в Excel, примеры выявляют ошибку. Дело в том, что размерность всех строк и столбцов диапазонов схожа для данного оператора. «Суммесли» допускает различное количество ячеек в интервале суммирования и условия.
  • В случае перебора первого аргумента обнаруживаются пустые либо текстовые ячейки, они игнорируются.
  • При прописывании аспекта употребляют символ «*». Он обозначает, что находить необходимо содержащийся фрагмент и не стопроцентное совпадение с условием.
  • Функция «суммеслимн» в Excel (примеры это подтверждают) допускают длину строчки до 255 знаков.
  • Ячейки, обозначенные в спектре суммирования, только тогда складываются, когда удовлетворяют всем поставленным условиям. По другому говоря, производится еще логическая функция «И».

Пример 1

Для закрепления материала юзеру необходимо самому решать подобные задачки по «суммеслимн» в Excel. Примеры использования представлены ниже.

Даны 5 столбцов, где указаны:

  • дата;
  • цвет;
  • штат;
  • количество;
  • стоимость.

функция суммеслимн в excel примеры

Производится поиск по последующим аспектам:

  • спектр суммирования: F5:F11;
  • интервал первого условия: поиск по цвету;
  • исходный аспект отбора: содержится слово red;
  • спектр второго условия: поиск по штату;
  • иной аспект: содержится аббревиатура TX.

Пример 2

Даны 4 столбца, в которых указаны:

  • А – категория продукта;
  • В – определенные продукты;
  • С – русский город;
  • D – размер продаж.

1-ое условие в примере 1 — отобрать все ячейки, содержащие слово «овощи». 2-ой аспект — отыскать клеточки, отвечающие за город «Москва». Во 2-м примере 1-ое условие содержит поиск по фруктам. 2-ой аспект относится к Казани.

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

суммеслимн в excel примеры использования

Во 2-м операторе цифрами помечены аргументы «суммеслимн»:

  • 1 – спектр суммирования (размер продаж);
  • 2 – интервал первого условия (поиск по группы);
  • 3 – аспект 1 (поиск слова «фрукты»);
  • 4 – спектр второго условия (нахождение по городку);
  • 5 – аспект 2 (поиск слова «Казань»).

Пример 3

Даны 2 столбца. В первом указаны имена служащих, во 2-м – размер продаж для всякого из их. Нужно сделать 3 функции «суммеслимн» в Excel. Примеры, как это создать, показаны на изображении.

excel суммесли

  1. Для первого оператора указывается спектр суммирования (для всех 3-х функций он схож): В2:В5; интервал первого и второго критерий совпадает с предшествующим, 1-ый аспект отбирает строчки, где размер продаж не наименее 100, 2-ой же – где не наиболее 500.
  2. Для второго оператора указывается предшествующий спектр суммирования. По первому столбцу идет отбор критерий: 1-ое гласит о поиске имен, где содержится буковка «а», 2-ое – о нахождении торговца, имя которого начинается на «И». Отысканные значения суммируются.
  3. Для третьего оператора также указывается предшествующий спектр суммирования. Для обоих критерий поиск идет в первом столбце. Исходный аспект отбора: имя торговца «<> Иван». 2-ое условие: размер продаж наиболее 200.

Пример 4

Даны 3 столбца, в которых указана дата, тип расходов, сумма на определенный вид растраты. Нужно создать подборку по двум условиям:

  1. Аспект не включает слово «Остальные».
  2. Условие содержит слово «Расходы*». Символ «*» гласит о том, что опосля введенной инфы идет продолжение в ячейке столбца В.

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

В качестве самостоятельного задания юзер может ввести условие по дате за определенный период: в месяц либо недельку. Чтоб это создать, нужно в кавычках указать период, например, «>01.01.2017». Вторым условием выступает: «<31.01.2017».

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

Интересно почитать:  Excel если ячейка содержит определенный текст то значение равно
Ссылка на основную публикацию
Adblock
detector