Суммесли в excel как сделать

Суммирование ячеек в Excel по условию

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

Суммирование ячеек в Excel по условию

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

Функция «СУММ»

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

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

Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию
Выбрать «fx» и в списке найти действие «СУММ», это можно сделать в категории «Полный алфавитный перечень» или в «Математических».

Нажимаем по значку функции «fx»

В списке «Категория» выбираем «Полный алфавитный перечень»

Находим и щелкаем по функции «СУММ», нажимаем «ОК»
Кликните на «ОК», появится окно «Аргументы функции». В нем можно сложить значения ячеек или вбить дополнительные цифры.

Ставим курсор мышки в поле «Число1», щелкаем по ячейке со значением

Ставим курсор мышки поочередно в поля «Число2» и «Число3», щелкаем по ячейкам со значением, нажимаем «ОК»

Важно! Знайте, что программа проигнорирует логическое или текстовое значения.

В выделенной ранее ячейке появится результат функции «СУММ»

При суммировании чисел с одним условием применяйте функцию «СУММЕСЛИ»

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

Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию
Нажмите на кнопку «fx» («Вставить функцию»), которая расположена перед строкой формул.

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

В списке «Категория» выбираем «Математичсекие», находим и щелкаем по функции «СУММЕСЛИ», затем «ОК»
Проявится всплывающее меню «Аргументы функции».

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

Большим плюсом нажмем на первую ячейку и, не отпуская, доведем до последней ячейки в диапазоне
В строку «Критерий» впишем «Васечкин» (можно писать как с кавычками, так и нет, программа их выставит автоматически).

В поле «Критерий» вписываем «Васечкин»

Примечание! Можно вводить также математическое выражение.

Отмечаем диапазон суммирования из столбца «Сумма».

В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», выделив первую ячейку и, не отпуская левую кнопку мышки, протягиваем до последней ячейки
Нажмите «ОК». Результат действия появится в выбранной ранее ячейке.

Проверяем данные, нажимаем «ОК»

В ячейке появится результат, заданной функции

На заметку! Эту функцию также можно ввести вручную, используя базовую запись: «=СУММЕСЛИ(x), где х – диапазон, критерий и диапазон суммирования, которые перечисляются через «;». Например, «=СУММЕСЛИ(А1:А2;«Условие»;В1:В2)».

Вводим вручную данные, в выбранную ячейку, нажимаем «Enter»

Однако, если нужно отобрать информацию по нескольким разным критериям?

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

Функция «СУММЕСЛИМН»

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

Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию
Нажмите на «fx» или «Вставить функцию».

Нажимаем по значку функции «fx»
Появится окно, где из перечня выберите функцию «СУММЕСЛИМН».

Выбираем функцию «СУММЕСЛИМН»
Кликните на «ОК» или два раза нажмите на «СУММЕСЛИМН».

Щелкаем по функции дважды или нажимаем «ОК»
Проявится меню «Аргументы функции».

В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», протянув левую кнопку мышки от первой до последней ячейки

Важно! Обратите внимание – в отличие от «СУММЕСЛИ», в данном окне сначала задается диапазон суммирования, а потом уже условия. Также можно ввести до 127 условий.

Заполните диапазоны условий и сами условия.

В поле «Диапазон условия1» вводим ячейки из столбца «Товар», выделив их левой кнопкой мышки

В поле «Условие1» вводим «яблоки» или другое значение, результат которого мы хотим получить
Нажмите «ОК».

Читать еще:  Как в excel сделать по горизонтали буквы?

Щелкаем по кнопке «ОК»

Примечание! Более подробную инструкцию вы можете найти в этой статье чуть выше.

Предположим, нам нужно узнать, на какую сумму Васечкин продал яблок. У нас есть всего 2 условия – продавец должен быть Васечкин, а товар – яблоки. В нашем случае, аргументы функции будут выглядеть следующим образом.

Вводим данные в соответствующие поля, задаем необходимые условия в кавычках, щелкаем по кнопке «ОК»

Результат, высчитанный функцией «СУММЕСЛИМН»

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

С наглядной инструкцией вы также можете ознакомиться в видео.

Видео — Суммирование по условию в Excel, функция «СУММЕСЛИМН»

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

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

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

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

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

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

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

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

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

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

Критериями для функции могут служить:

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

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

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

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

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

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

  1. «>2000» – функция посчитает сумму всех заказов больше 2000 рублей.
  2. » =2000″ – вернется сумма заказов которые больше и равны 2000 рублей.
  3. » . Например, для подсчета суммы заказов не от продажи миксеров, окно аргументов функции будет иметь следующий вид.

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

Дата и диапазон дат в качестве критерия

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

Читать еще:  Как сделать принтер по умолчанию в excel?

Значит, 19600 рублей – объем продаж по названной номенклатуре за первый квартал.

Ссылка в поле критерия

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

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

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

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

Суммирование в Excel через одну, две, три ячейки

Всем известен способ подсчёта данных в Excel путём элементарного сложения ячеек =C2+C4+C6, либо с помощью функции =СУММ(C2;C4;C6), но если таблица большая, то такой способ выведет из себя даже самого терпеливого. Поэтому предлагаю рассмотреть способ суммирования по условию: =СУММЕСЛИ(B2:B15;»Доход»;C2:C15)

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

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

В качестве второго варианта можно использовать функцию СУММПРОИЗВ, которая вводится как обычная формула (без Ctrl+Shift+Enter), но работает также:

Для подсчёта суммы, находящейся в столбцах, вместо СТРОКА используем СТОЛБЕЦ, затем протягиваем формулу вниз:

Примечание: для красивого отображения цифр устанавливаем формат ячеек «Числовой» – Число десятичных знаков «0» – галочка на «Разделитель групп разрядов». Для процентов «Процентный» – Число десятичных знаков «0».

А для англоязычных excel кто переводить формулы будет, еклмн? Как мне конвертировать теперь?

Я позже в комментарии выложу английскую версию формул, специально для вас

Я понимаю, что это вопрос привычки, но с английских формул на «=ВВПНПМЦЧТЬ» не перейду никогда.

Вопрос не по теме. Есть ли в Excel возможность суммировать значения только в помеченных ячейках/строках?

А2+В2 и так далее

Поддерживаю предыдущего комментатора.

Ну как там? Мне тоже надо на английском

Как сделать случайные значения чтоб они не изменялись при любом другом действии?

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

Впредь я буду английский вариант тоже указывать

Нахуя англоязычные формулы? Если есть нормальная русская версия

Я могу понять, когда пользуются английским Adobe Lightroom или Sony Vegas. Но зачем пользоваться продуктами для американского рынка компании Microsoft (встречаю скриншоты с кнопкой Start регулярно) — для меня загадка.

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

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

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

Он один а нас таких много. Я конвертировать не могу пока он формулы для тебя переводит.

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

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

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

Многие русскоязычные пользователи используют английскую версию именно из-за этого. Более того английские формулы интуитивно понятны (vlookup vs впр например)

ВПР я примерно имею представление за что отвечает, знаю что заменяется конструкцией ИНДЕКС и ПОИСКПОЗ, а vlookup впервые увидал пол-минуты назад. Мб и интуитивно понятно, но не в российских реалиях. Я бы вряд ли правильно назвал российский аналог этой функции. Просто потому что в России пользуется абсолютное большинство русским офисом.

Я имел ввиду другое:

Первый раз видишь формулу ВПР и даже примерно не представляешь что она делает.

VLOOKUP — уже понятно, что это как минимум поиск.

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

Поэтому «большинство» совсем не показатель.

Интересно, но нихера не понятно)))

Спасибо! Я думала, я одна тупень)) рада, что нас двое)))

Если надо сумировать, например, каждую третью клетку первого столбца, то можно в втором столбце написать в первых трех клетках 0, 0, 1 и протянуть вниз. Получится стобец вида 0, 0, 1, 0, 0, 1, 0, 0, 1. В третий столбец пишем формулу произвдения первого на второй и третий сумируем (в сумму будут входить только нужные значния, так как ненужные будут 0). ИМХО так проще, чем рзбиратся как работают те функции.

Читать еще:  Как сделать электронный журнал для школы в excel?

P. s. С екслем давно не игрался и это написал по памяти. Может не работать если я что-то забыл.

Так примитивнее. Но по моему опыту, так гораздо удобнее. Особенно когда баг ищешь.

Для чего в формулах два минуса ?Что это дает?

Чтобы преобразовать логические значения ИСТИНА и ЛОЖЬ, получившиеся в результате проверки формулой ОСТАТ(СТРОКА(C2:C17);4)=1, в единицы и нули, используется двойной минус – он равносилен двукратному умножению на -1, т.е. не меняет значения, но конвертирует ИСТИНУ в 1, а ЛОЖЬ в 0.

А почему бы тогда не использовать просто суммирование по условию?

А умножение ИСТИНА/ЛОЖЬ на число напрямую дает ошибку?

Подскажите, пожалуйста, как можно формулой найти количество уникальных значений в столбце?

А также, что нужно, чтобы все уникальные значения вынести в отельный столбец?

Если данную операцию нужно сделать один раз, то можно воспользоваться advanced filter. Перенести только уникальные значения в отдельный столбик и там уже посчитать. Еще, возможно, подойдет remove duplicates.

Вы про сравнение списков?

Можно, у меня есть формулы решающие именно эти задачи. Нужны функции Если, суммесли, макс. Можно в Гугл вбить «вывести уникальные значения таблицы Эксель» буквально первая ссылка предлагает решение

А есть в екселе возможность посчитать ячейки определенного цвета без макросов и сторонних надстроек?

Есть такая возможность, я выложу это отдельным постом

Спасибо за посты об Экселе.

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

Есть столбец с наименованиями деталей. Рядом с ним столбец с их стоимостью соответственно.

В третьем столбце количество этих деталей соответственно.

Иногда список деталей расширяется, столбцы удлинняются.

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

По сути сейчас в ячейке с общей стоимостью стоит формула, вбитая вручную, где суммируются произведения 2 и 3 столбца.

Может как-то можно не обновлять не каждый раз и сделать проще?

Сам задал вопрос, и полез в Гугл. Оказывается, есть просто такая функция, как сумма произведений:))) Не знал, теперь знаю

Человечище. Я искал этот способ полгода назад, нигде нормального описания нет. А тут всё понятно. Благодарю безмерно!

Подскажите пожалуйста. Есть задача: есть много столбцов с разными значениям, как сделать чтобы при пересечении в нужных столбцах выводил например ОК. Например, как на картинке. Пересекается любые значения из зеленого, серого и фиолетового столбца — значит выводит ОК, А если пересекается значения из зеленого, коричневого и фиолетового, то выводит ошибку. То есть нужно сделать чтобы при совпадении трех значений из трех столбцов выводил нужное мне значение

К сожалению я не знаю как такое можно реализовать в Excel

На месте ТСа я бы подумал, можно ли данную таблицу сделать на фанкциях AGGREGATE и SUBTOTAL

Я вот как о них узнал (и о возможности игнора этих самых AGGREGATE и SUBTOTAL )- перешел на них с функции SUM насовсем. Рекомендую ТСу заиметь такую же привычку.

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

нет ничего хуже для екцеля, чем суммпризв и суммеслиМН. 16гб памяти, винт твердотельный, а файл булет подвисать уже при нескольких таких сотнях формул, даже если юзать динамические рейеджи. проще прогу написать, которая просто +а1+а3+а6 сделает или суммесли с ключрм, чем это говно юзать.

Говорят, любой язык программирования проще эксэля

Это не совсем так, возможности языков гораздо шире, но. с тех пор, как я освоил связку PHP+MySQL, я забыл про ексель, как страшный сон. То есть, я ВООБЩЕ перестал им пользоваться в своей работе, НАВСЕГДА! Хотя раньше слыл среди коллег в нём едва ли не экспертом.

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

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

Много лишних действий. Особенно по засовыванию данных в MySQL.

Все таки PHP+MySQL и Ексель инструменты для совершенно разных действий. Как и 1С и ексель, например.

Работа с БД в екселе имеется, VBA тоже есть. И интерфейсы отрисовывать не нужно.

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