Excel суммесли функция - Учим Эксель

Примеры функции СУММЕСЛИ и СУММЕСЛИМН в Excel

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

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

Примеры функции СУММЕСЛИ и СУММЕСЛИМН в Excel

СУММЕСЛИ

Аргументы

Разглядим аргументы для СУММЕСЛИ.

=СУММЕСЛИ(спектр; аспект; [диапазон суммирования])

Спектр – тут нужно указать, тот столбец, данные в каком будут сравниваться с данным Аспектом. В примере, это D2:D26 .

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

Спектр суммирования – это столбец, значения в каком необходимо просуммировать, если они относятся к избранному Аспекту. К примеру, если для избранного Спектра D2:D26 указан Аспект «мальчишка» , означает в столбце «Средний бал» : С2:С26 , будут суммироваться все значения, которые относятся к мальчишкам.

Пример

Выделяем ячейку G4 и ставим «=» . Пишем в ней СУММЕСЛИ и открываем скобку «(» . В качестве Спектра, из которого будут выбираться значения, выделяем D2:D26 . В качестве Аспекта, необходимо избрать одно из значений в ранее обозначенном Спектре. У нас это «мальчишка» , потому выделяем ячейку D3 . Спектр суммирования – это столбец, в каком указан «Средний бал» . Выделяем С2:С26 . Меж аргументами ставьте «;» , в конце, закройте скобку «)» . Нажмите «Enter» .

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

Сейчас давайте растянем формулу по столбцу. Естественно, можно также прописать ее и для девченок, но если у Вас много различных критериев – это займет много времени. Закрепим спектры для столбцов С и D , чтоб при копировании формулы они не ползли вниз.

Для этого выделяем G4 и в «Строке формул» опосля D2 , D26 , C2 , C26 жмем кнопку «F4» . В адресах на ячейки, покажутся знаки «$» . Таковым образом, мы делаем ссылки абсолютными. Чтоб растянуть формулу по столбцу, потяните за правый нижний угол ячейки, курсор изменит при всем этом вид на темный плюсик.

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

Далее смотрим на «Строчку формул» . В качестве аргументов для Спектра и Спектра суммирования берутся правильные значения: D2:D26 , C2:C26 . В качестве Аспекта укажите адресок подходящей ячейки. Так как, необходимо посчитать средний балл для всех девченок, в столбце D избираем адресок хоть какой ячейки, в какой обозначено «девченка» : D4 .

СУММЕСЛИМН

Аргументы

Сейчас разглядим аргументы для функции СУММЕСЛИМН в Excel.

=СУММЕСЛИМН(спектр суммирования; спектр условия1; условие1; спектр условия2; условие2)

Спектр суммирования – это столбец, значения из которого будут выбираться, если они соответствуют одному и второму данному условию. В примере, это С2:С26 .

Спектр условия1 – это 1-ый спектр для сопоставления. Необходимо избрать пол ученика, потому напишем D2:D26 .

Условие1 – необходимо указать то значение, которое будет выбираться из Спектра условий1. Нас заинтересовывают мальчишки, потому укажем D3 .

Спектр условия2 – 2-ой спектр сопоставления. Тут будем выбирать класс, в каком обучается ребенок. Соответственно укажем В2:В26 .

Условие2 – конкретное значение, которое нас интересует из Спектра условий2. Поначалу необходимо будет выделить ячейку, в какой указан «8 класс» , потом «9 класс» .

Пример

Решать будем вторую задачку. Выделяем ячейку Н8 , ставим «=» , пишем СУММЕСЛИМН и открываем скобку «(» . В качестве Спектра суммирования указываем столбец со «Средним балом» : С2:С26 . Спектр условия1 – это пол ученика: D2:D26 , Условие1 – это мальчишки: ячейка D3 . Спектр условия2 – это класс: В2:В26 , Условие2 – 8 класс: ячейка В3 . Закройте скобку, нажмите «Enter» .

В «Строке формул» сходу сделайте абсолютные ссылки на столбцы: Класс, Средний бал, Мал/Дев.

Растягиваем формулу на Н8:Н11 . Сейчас укажем правильные ячейки в качестве Условия1 и Условия2.

В Н9 считается средний балл для мальчишек, которые обучаются в 9 классе. Потому, Условие1 для первого спектра «мальчишка» : D3 , Условие2 для второго спектра «9» : В6 .

Интересно почитать:  Функция умножения в excel

В Н10 считаем средний балл девченок, которые обучаются в 8 классе. Условие1 – «девченка» , D4 , Условие2 – «8» , В3 .

В Н11 считаем средний балл девченок, которые обучаются в 9 классе. Условие1 – «девченка» , D4 , Условие2 – «9» , В2 .

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

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

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

Вот на таковых легких примерах мы разобрались, как работает функция СУММЕСЛИ и функция СУММЕСЛИМН в Эксель.

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

Работа в почти всех проф областях на нынешний денек не обходится без использования программки «Эксель». Эта программка, как хоть какое орудие труда, становится полезной лишь в опытных руках. Для удачной деятельности в сфере экономики, финансирования и почти всех остальных нужны базисные познания по Excel. На обширных просторах веба представители профессий, относящихся к вышеуказанным областям, сформировали перечень функций и способностей, остро нужных для решения проф задач. В этот перечень, кроме умения работы со сводными таблицами, вошли функции:

  • СУММЕСЛИ.
  • ЕСЛИ.
  • СУММПРОИЗВ.
  • ВПР.
  • СУММЕСЛИМН.

Как следует, функция СУММЕСЛИ в «Эксель» является одной из самых нужных.

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

Функция СУММЕСЛИ: диалоговое окно аргументов функции

Если при анализе таблицы данных в «Эксель» задачка стоит в необходимости суммирования характеристик, которые соответствуют определенным условиям, означает, настало время внедрения функции СУММЕСЛИ. Курсор мыши ставится в ячейку, куда необходимо вывести итог. Дальше или функция «Эксель» СУММЕСЛИ выбирается из ленты (Формула – математические – СУММЕСЛИ), или вводится через жаркую кнопку =, которая значит готовность ввести в ячейку формулу либо функцию. Если опосля знака = начать вводить 1-ые буковкы наименования функции, «Эксель» сам выдаст несколько пригодных, остается лишь избрать подходящую. Бывалые юзеры прописывают формулу СУММЕСЛИ в «Эксель» прямо в строке формул, вписывая аргументы через точку с запятой. Новенькому же лучше пользоваться значком fx, который находится слева от строчки формул. Возникает окно «Аргументы функции». Грамотное наполнение всех окон – залог удачной работы с функцией СУММЕСЛИ в «Эксель».

Обычно три поля:

  1. Спектр – совокупа ячеек, которые программка и будет ассоциировать на соответствие данному условию. Курсор ставится в поле аргумента, и мышью выбираются нужные ячейки.
  2. Аспект – условие, при котором будет делается суммирование. Тут могут быть числа, выражения, текст, даты. Обычно значение аспекта вводится в кавычках.
  3. Спектр суммирования – ячейки со значениями, которые войдут в итоговую сумму, если соответственная ячейка в спектре соответствует аспекту. Курсор ставится в поле аргумента, и мышью выбираются нужные ячейки.

Могут заполняться 2 поля – спектр и аспект, такое происходит, если 1-ый совпадает с спектром суммирования. В диалоговом окне аргументы функции, в самой нижней строке показывается получившееся значение. Если с спектрами все просто: необходимо просто обозначить область анализируемых ячеек, то для наполнения поля «Аспект» необходимо знать некие тонкости и индивидуальности программки.

Аспекты для функции СУММЕСЛИ в «Эксель»

Аспектами для функции могут служить:

  1. Логические выражения.
  2. Текст.
  3. Четкие даты.
  4. Временной интервал.

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

Основная таблица

Логическое значение в качестве аспекта

Самый нередкий вариант использования функции СУММЕСЛИ в «Эксель» – это внедрение логического значения (больше, меньше, равно) в качестве условия. Для вышеуказанной таблицы сумма заказов, равных 2000 рублей, опишется так.

Интересно почитать:  Index функция в excel

логическое выражение в критерии

Как видно из примера, спектр и спектр суммирования совпадают, заполняется лишь 1-ая строчка. Условие прописывается в кавычках (это справедливо постоянно для этого поля!). Сходу видно вычисленное значение: 4000. Возвращаемся к исходной таблице: вправду, находятся два заказа в сумме 2000 рублей. Совместно они составляют 4000 рублей. Не используя диалоговое окно, можно записать в строке формул: =СУММЕСЛИ(E2:E15;»=2000″), итог будет этим же.

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

  1. «>2000» – функция посчитает сумму всех заказов больше 2000 рублей.
  2. «<2000» – возвратится сумма заказов меньше 2000 рублей
  3. «>=2000» – возвратится сумма заказов которые больше и равны 2000 рублей.
  4. «<=2000» – сумма заказов меньше и равных 2000 рублям.

Текст в качестве аспекта

Пример СУММЕСЛИ в «Эксель» текстовым аспектом: подсчитать, на какую сумму продал заказов менеджер Иванов И. И. Так смотрится окно аргументов в этом случае.

Текстовый критерий

Функция возвратила значение 13000 рублей. На эту сумму продал продуктов менеджер Иванов И. И.

Время от времени возникает необходимость в условии задать некорректное совпадение по тексту. К примеру, необходимо отыскать сумму, вырученную от заказов всех миксеров (в таблице их два вида). Для неточного совпадения необходимо снутри кавычек проставить знаки *.

В поле аспект будет запись «*миксер*».

Задать условие с текстом, которое будет иметь значение «не равно», можно композицией символов <>. К примеру, для подсчета суммы заказов не от реализации миксеров, окно аргументов функции будет иметь последующий вид.

Текстовый критерий на фото

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

Дата и спектр дат в качестве аспекта

Если в функции СУММЕСЛИ в «Эксель» в качестве аспекта для условия обязана употребляться определенная дата, то к оформлению поля используются те же аспекты, что и в случае с числами и с текстом. Этим же знакам =,>,<,<=,>=,<>, соответствуют те же значения, лишь применимо это к датам. К примеру, в начальной таблице нужно суммировать заказы за 1-ый квартал 2018 года. В качестве спектра выбирается дата реализации, аспект, основываясь на задании, смотрится как «»>=31.03.2018″, в итоге получится последующее.

Критерий-дата

Означает, 19600 рублей – размер продаж по нареченной номенклатуре за 1-ый квартал.

Ссылка в поле аспекта

Нередко итоговый итог от вычислений по СУММЕСЛИ в «Эксель» необходимо создать оживленным, зависящим от конфигураций в ячейках, входящих в аргументы функции. Если с первым и третьим аргументом все ясно – они являются ссылками вначале, то в поле «Аспект» для того, чтоб ввести ссылку, необходимо пользоваться доп эмблемой – значком амперсанд (&). Знаки для логического выражения нужно, как и до этого, взять в кавычки, а перед ссылкой поставить &.

В начальной таблице нужно отыскать сумму по всем заказам, которые больше, чем заказ в ячейке E10. Это смотрится так.

Ссылка на ячейку

Аналогично ссылки можно вставить в хоть какое выражение в поле «Аспект» в аргументах функции СУММЕСЛИ в «Эксель».

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

Функция СУММЕСЛИ и ее варианты в Excel

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

Сложение в Excel

Определение суммы частей в программке может быть при помощи простых арифметических операций, которые обозначаются знаком «+». Для прибавления маленького количества слагаемых этот способ подступает идеальнее всего.

You will be interested: How dangerous is the new coronavirus?

К примеру, для подсчета итоговой цены туристской заявки, которая включает цены на гостиницу, трансферы и экскурсии, можно употреблять операцию сложения при помощи формулы. Формула вносится в ячейку опосля знака «равно» («=»). Для выбора ячеек, включенных в сумму, довольно кликнуть на подходящую, и ее размещение будет обозначено в формуле.

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

Нахождение суммы

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

Для таковых ситуаций подступает функция Excel СУММ(). Она складывает числа в обозначенном спектре ячеек без необходимости прописывания расположения всякого поля: СУММ(число1;[число2];…), где в качестве аргумента «число» быть может указан спектр чисел в ячейках. Функция воспринимает не наиболее 255 аргументов. В примере заявок туристского агентства она будет использована к столбцу I:

Условное суммирование

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

СУММЕСЛИ(спектр; условие; [диапазон_суммирования]).

В интервале полей, обозначенном аргументом «спектр» проверяется данное требование для прибавления числа к сумме. Этот атрибут и последующий за ним – «условие» — являются неотклонимыми для использования в формуле. Потому в поле «Аспект» мы указываем свое требование.

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

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

Подсчитаем доходы лишь от тех туристов, которые заказывали у нас личные трансферы. В качестве полей для проверки требования указываем столбец «Трансферы», сам аспект – равенство поля значению «Личные».

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

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

Очередной пример – внедрение команды в совокупы с формулой ВПР: ВПР(разыскиваемый показатель; интервал поиска; номер колонки в интервале; четкое либо примерное равенство — 0 (четкое) либо 1(примерное)).

На практике для использования ВПР в Excel выделяется подтаблица (параметр 2), 1-ый столбец которой содержит разыскиваемое значение (параметр 1). Команда описывает строчку, в какой оно содержится, и указывает в итоге содержимое поля в отысканной строке и обозначенном столбце (параметр 3).

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

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

Она возвратит число 2 – код личных трансферов. Сейчас подставляем эту функцию в окно характеристик СУММЕСЛИ в Excel и получаем то же значение, что и в первом случае.

Сумма с несколькими критериями

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

По аналогии с функцией СУММЕСЛИ в окне характеристик данной формулы указываем спектр ячеек для сложения и условия. Синтаксис команды:

СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …).

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

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

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

Внедрение функции СУММЕСЛИ в Excel упрощает выполнение почти всех задач. Ее синтаксис прост и доступен для внедрения даже неопытному юзеру программки.

Ссылка на основную публикацию
Adblock
detector