Формула суммирования в excel

Использование функции «Автосумма» для суммирования чисел. Формула суммы в экселе

Использование функции «Автосумма» для суммирования чисел

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Если нужно сложить столбце или строке чисел, позвольте Excel выполнить эти вычисления за вас. Выделите ячейку напротив тех номеров, которые нужно сложить, нажмите кнопку Автосумма на Главная tab, нажмите клавишу Ввод и все готово.

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Пример: чтобы сложить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая находится прямо под столбцом с числами. Затем нажмите кнопку Автосумма. В ячейке В7 появляется формула, и Excel выделяет ячейки, которые суммируются.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

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

Кнопка Автосумма находится в двух местах: на вкладке Главная > Автосумма и на вкладке Формулы > Автосумма.

Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Использование функции «Автосумма» для суммирования чисел

Если вам нужно сложить числа в столбце или строке, позвольте Excel Online выполнить все вычисления за вас. Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку «Автосумма» на вкладке Главная и нажмите клавишу ВВОД.

Когда вы нажимаете кнопку Автосумма, Excel Online автоматически вводит формулу (в которой используется функция СУММ) для сложения чисел.

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

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Вы также просмотреть формулу в строке формул.

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

Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.

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

Использование формул Excel для определения объемов платежей и сбережений

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

ПЛТ: возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и процентной ставки.

КПЕР: возвращает количество периодов выплаты для инвестиции на основе регулярных постоянных выплат и постоянной процентной ставки.

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

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

Расчет ежемесячных платежей для погашения задолженности по кредитной карте

Предположим, остаток к оплате составляет 5400 долларов США под 17% годовых. Пока задолженность не будет погашена полностью, вы не сможете рассчитываться картой за покупки.

С помощью функции ПЛТ(ставка;КПЕР;ПС)

получаем ежемесячный платеж в размере 266,99 долларов США, который позволит погасить задолженность за два года.

Аргумент «ставка» — это процентная ставка на период погашения кредита. Например, в данной формуле ставка 17% годовых делится на 12 — количество месяцев в году.

Аргумент КПЕР 2*12 — это общее количество периодов выплат по кредиту.

Аргумент ПС или приведенной стоимости составляет 5400 долларов США.

Расчет ежемесячных платежей по ипотеке

Представьте дом стоимостью 180 000 долларов США под 5% годовых на 30 лет.

С помощью функции ПЛТ(ставка;КПЕР;ПС)

получена сумма ежемесячного платежа (без учета страховки и налогов) в размере 966,28 долларов США.

Аргумент «ставка» составляет 5%, разделенных на 12 месяцев в году.

Аргумент КПЕР составляет 30*12 для ипотечного кредита сроком на 30 лет с 12 ежемесячными платежами, оплачиваемыми в течение года.

Аргумент ПС составляет 180 000 (нынешняя величина кредита).

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

Необходимо собрать деньги на отпуск стоимостью 8500 долларов США за три года. Процентная ставка сбережений составляет 1,5%.

С помощью функции ПЛТ(ставка;КПЕР;ПС;БС)

получаем, что чтобы собрать 8500 долларов США за три года, необходимо откладывать по 230,99 долларов США ежемесячно.

Аргумент «ставка» составляет 1,5%, разделенных на 12 месяцев — количество месяцев в году.

Аргумент КПЕР составляет 3*12 для двенадцати ежемесячных платежей за три года.

Аргумент ПС (приведенная стоимость) составляет 0, поскольку отсчет начинается с нуля.

Аргумент БС (будущая стоимость), которую необходимо достичь, составляет 8500 долларов США.

Теперь допустим, вы хотите собрать 8500 долларов США на отпуск за три года, и вам интересно, какую сумму необходимо положить на счет, чтобы ежемесячный взнос составлял 175,00 долларов США. Функция ПС рассчитает размер начального депозита, который позволит собрать желаемую сумму.

С помощью функции ПС(ставка;КПЕР;ПЛТ;БС)

мы узнаем, что необходим начальный депозит в размере 1969,62 долларов США, чтобы можно было откладывать по 175,00 долларов США в месяц и собрать 8500 долларов США за три года.

Аргумент «Ставка» составляет 1,5%/12.

Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).

Аргумент ПЛТ составляет -175 (необходимо откладывать по 175 долларов США в месяц).

Аргумент БС (будущая стоимость) составляет 8500.

Расчет срока погашения потребительского кредита

Представьте, что вы взяли потребительский кредит на сумму 2500 долларов США и согласились выплачивать по 150 долларов США ежемесячно под 3% годовых.

С помощью функции КПЕР(ставка;ПЛТ;ПС)

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

Аргумент «Ставка» составляет 3%/12 ежемесячных платежей за год.

Аргумент ПЛТ составляет -150.

Аргумент ПС (приведенная стоимость) составляет 2500.

Расчет суммы первого взноса

Допустим, вы хотите приобрести автомобиль стоимостью 19 000 долларов США под 2,9% годовых за три года. Вы хотите сохранить ежемесячные платежи на уровне 350 долларов США. Таким образом, вам необходимо рассчитать начальный платеж. В данной формуле результат функции ПС — это сумма кредита, которая вычитается из цены покупки для получения первого взноса.

С помощью функции ПС(ставка;КПЕР;ПЛТ)

выясняем, что первый взнос должен составлять 6946,48 долларов США.

Сначала в формуле указывается цена покупки в размере 19 000 долларов США. Результат функции ПС будет вычтен из цены покупки.

Аргумент «Ставка» составляет 2,9%, разделенных на 12.

Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).

Аргумент ПЛТ составляет -350 (необходимо будет выплачивать по 350 долларов США в месяц).

Оценка динамики увеличения сбережений

Начиная с 500 долларов США на счету, сколько можно собрать за 10 месяцев, если класть на депозит по 200 долларов США в месяц под 1,5% годовых?

С помощью функции БС(ставка;КПЕР;ПЛТ;ПС)

получаем, что за 10 месяцев выйдет сумма 2517,57 долларов США.

Аргумент «Ставка» составляет 1,5%/12.

Аргумент КПЕР составляет 10 (месяцев).

Аргумент ПЛТ составляет -200.

Аргумент ПС (приведенная стоимость) составляет -500.

5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

Здравствуйте!

Многие кто не пользуются Excel — даже не представляют, какие возможности дает эта программа! Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, складывать по условию и т.д. — в общем-то, по сути мини-язык программирования для решения «узких» задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал).

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

Возможно, что прочти подобную статью лет 15-17 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения «простых» (прим.: как я сейчас понимаю) задач).

Обучение основам Excel: ячейки и числа

Примечание : все скриншоты ниже представлены из программы Excel 2016 (как самой новой на сегодняшний день).

Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: «ну и где тут таблица?». Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!

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

• слева : в ячейке (A1) написано простое число «6». Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число «6».
• справа : в ячейке (C1) с виду тоже простое число «6», но если выбрать эту ячейку, то вы увидите формулу «=3+3» — это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например «=3+5+8» (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:

• первый: «=5+6» (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
• второй: «=A1+B1» — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!)

Сложение ячеек, в которых уже есть числа

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

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

1. в строке 2 написать формулу «=A2+B2» , в строке 3 — «=A3+B3» и т.д. (это долго и утомительно, этот вариант никогда не используют);
2. выбрать ячейку D1 (в которой уже есть формула), затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже). Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! ( Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)).

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу «=A2+B2» (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

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

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

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2.

Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид «=D2*$G$2». Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже).

Константа // в формуле ячейка не изменяется

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая «=A1+B1+C1» и т.п. Но в Excel есть более быстрые и удобные инструменты.

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

Что нужно сделать, чтобы посчитать сумму определенных ячеек:

1. сначала выделяем ячейки (см. скрин ниже);
2. далее открываем раздел «Формулы» ;
3. следующий шаг жмем кнопку «Автосумма» . Под выделенными вами ячейками появиться результат из сложения;
4. если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу «=СУММ(E2:E7)» .
5. таким образом, написав формулу «=СУММ(xx)» , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк.

Автосумма выделенных ячеек

Как посчитать сумму с каким-нибудь условием

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

Я в своей таблицы буду использовать всего 7 строк (для наглядности), реальная же таблица может быть намного больше. Предположим, нам нужно посчитать всю прибыль, которую сделал «Саша». Как будет выглядеть формула:

1. » =СУММЕСЛИМН( F2:F7 ; A2:A7 ;»Саша») » — ( прим .: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру «СУММ. «), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
2. F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
3. A2:A7 — это столбик, по которому будет проверяться наше условие;
4. «Саша» — это условие, те строки, в которых в столбце A будет «Саша» будут сложены (обратите внимание на показательный скриншот ниже).

Сумма с условием

Примечание : условий может быть несколько и проверять их можно по разным столбцам.

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию. Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой? Формула:

«=СЧЁТЕСЛИ( A2:A7 ; A2 )» — где:

• A2:A7 — диапазон, в котором будут проверяться и считаться строки;
• A2 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Количество строк с одним условием

Теперь представьте более расширенную задачу: нужно посчитать строки где встречается имя «Саша», и где в столбце И — будет стоять цифра «6». Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже).

Формула будет иметь вид:

=СЧЁТЕСЛИМН( A2:A7 ; A2 ; B2:B7 ;»6″) (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ; A2 — первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;»6″ — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

Счет строк с двумя и более условиями

Как посчитать процент от суммы

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

Самый простой способ, в котором просто невозможно запутаться — это использовать правило «квадрата», или пропорции. Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число). В принципе, используя это правило, запутаться в процентах практически невозможно .

Пример решения задач с процентами

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel), и не знаешь как быстрее это сделать.

Функция СУММ

В этом курсе:

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

=СУММ(A2:A10) Суммирует значения в ячейках A2:10.

=СУММ(A2:A10;C2:C10) Суммирует значения в ячейках A2:10, а также ячейки C2: C10.

Первое число для сложения. Это может быть число 4, ссылка на ячейку, например B6, или диапазон ячеек, например B2:B8.

Это второе число для сложения. Можно указать до 255 чисел.

Этот раздел содержит некоторые рекомендации по работе с функцией СУММ. Многие из этих рекомендаций можно применить и к другим функциям.

Метод =1+2 или =A+B. Вы можете ввести =1+2+3 или =A1+B1+C2 и получить абсолютно точные результаты, однако этот метод ненадежен по ряду причин.

Опечатки. Допустим, вы пытаетесь ввести много больших значений такого вида:

А теперь попробуйте проверить правильность записей. Гораздо проще поместить эти значения в отдельные ячейки и использовать их в формуле СУММ. Кроме того, значения в ячейках можно отформатировать, чтобы привести их к более наглядному виду, чем если бы они были в формуле.

Ошибки #ЗНАЧ!, если ячейки по ссылкам содержат текст вместо чисел

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

Если ячейки, на которые указывают ссылки, содержат нечисловые (текстовые) значения, формула может вернуть ошибку #ЗНАЧ!. Функция СУММ пропускает текстовые значения и выдает сумму только числовых значений.

Ошибка #ССЫЛКА! при удалении строк или столбцов

При удалении строки или столбца формулы не обновляются: из них не исключаются удаленные значения, поэтому возвращается ошибка #ССЫЛКА!. Функция СУММ, в свою очередь, обновляется автоматически.

Формулы не обновляют ссылки при вставке строк или столбцов

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

Функция СУММ — отдельные ячейки или диапазоны.

Используя формулу такого вида:

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

Такая формула будет обновляться при добавлении и удалении строк.

Мне нужно добавить, вычесть, умножить или поделить числа. Просмотрите серию учебных видео: Основные математические операции в Excel или Использование Microsoft Excel в качестве калькулятора.

Как уменьшить или увеличить число отображаемых десятичных знаков? Можно изменить числовой формат. Выберите соответствующую ячейку или соответствующий диапазон и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, затем щелкните вкладку Число и выберите нужный формат, указав при этом нужное количество десятичных знаков.

Как добавить или вычесть значения времени? Есть несколько способов добавить или вычесть значения времени. Например, чтобы получить разницу между 8:00 и 12:00 для вычисления заработной платы, можно воспользоваться формулой =(«12:00»-«8:00»)*24, т. е. отнять время начала от времени окончания. Обратите внимание, что Excel вычисляет значения времени как часть дня, поэтому чтобы получить суммарное количество часов, необходимо умножить результат на 24. В первом примере используется формула =((B2-A2)+(D2-C2))*24 для вычисления количества часов от начала до окончания работы с учетом обеденного перерыва (всего 8,5 часов).

Если вам нужно просто добавить часы и минуты, вы можете просто вычислить сумму, не умножая ее на 24. Во втором примере используется формула =СУММ(A6:C6), так как здесь нужно просто посчитать общее количество часов и минут, затраченных на задания (5:36, т. е. 5 часов 36 минут).

Как получить разницу между датами? Как и значения времени, значения дат можно добавить или вычесть. Вот распространенный пример вычисления количества дней между датами. Для этого используется простая формула =B2-A2. При работе со значениями дат и времени важно помнить, что дата или время начала вычитается из даты или времени окончания.

Другие способы работы с датами описаны в статье Вычисление разности двух дат.

Как вычислить сумму только видимых ячеек? Иногда когда вы вручную скрываете строки или используете автофильтр, чтобы отображались только определенные данные, может понадобиться вычислить сумму только видимых ячеек. Для этого можно воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Если вы используете строку итогов в таблице Excel, любая функция, выбранная из раскрывающегося списка «Итог», автоматически вводится как промежуточный итог. Дополнительные сведения см. в статье Данные итогов в таблице Excel.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

Функция СУММ – простое суммирование ячеек в Excel

Функция СУММ вычисляет сумму всех своих аргументов. Она является наиболее часто используемой функцией в Excel. К примеру, нам необходимо сложить значения в трех ячейках. Мы, конечно же, можем воспользоваться обычным оператором суммирования:

Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:

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

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

В следующем примере функция СУММ складывает весь столбец А, а это 1048576 значений:

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

Функция СУММ может принимать до 255 аргументов и суммировать сразу несколько несмежных диапазонов или ячеек:

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

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

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

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

СУММЕСЛИ – условная сумма в Excel

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

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

В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:

Меняем условие, меняется и результат:

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

Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?

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

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

Нажав Enter получим результат:

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

Суммирование – это одно из основных действий, которое пользователь выполняет в Microsoft Excel. Функции СУММ и СУММЕСЛИ созданы, чтобы облегчить эту задачу и дать пользователям максимально удобный инструмент. Надеюсь, что этот урок помог Вам освоить базовые функции суммирования в Excel, и теперь Вы сможете свободно применять эти знания на практике. Удачи Вам и успехов в изучении Excel!

Формулы суммирования по нескольким условиям в Excel

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

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

Ниже на рисунке находится часть списка стран с показателями ВВП за период начиная от 2000-х и до 2005-х годов. Допустим нам необходимо суммировать показатель ВВП Бразилии за 2001-2004 год. Для суммирования значений по двум или нескольким условиям удобно использовать функцию СУММЕСЛИМН. В данном случае нам нужно учитывать:

1. Первое условие – только страна Бразилия.
2. Второе условие – суммировать ВВП только за 2001 и 2004 год.

Формула вычисления суммы показателей ВВП для указанной страны за определенный период лет:

=»&F2;B2:B20;»

Первым аргументом функции СУММЕСЛИНМ является диапазон ячеек, содержащие суммированные значения. Следующие аргументы объединены в пары по схеме Диапазон_условия1 вместе c Услвоие1. Количество аргументов функции – это всегда непарное число учитывая их последовательность. Первая пара критериев (второй и третий аргумент) являются обязательными для заполнения. Без них данная функция ничем не отличалась бы от обычной функции СУММ. Дополнительные последующие пары критериев не обязательны для заполнения. А их максимальное количество пар аргументов может быть достигнуто до 127-ми. То есть максимальное количество всех аргументов в функции СУММЕСЛИМН 127*2+1= 255.

В выше приведенном примере суммируются значения столбца C только тех ячеек, которые соответствуют значениям в столбцах A и B при выполнении определенных условий отбора. Условием для отбора ячеек в столбце A является совпадение значений с ячейкой F1, где указывается название страны для отбора. Год проверяется по двум условиям определяющих начальный год и конечный период времени. Начальный год записан в ячейке F2, а конечный – F3. Ссылки на эти ячейки через символ амперсант (&) объединены с оператором сравнения «больше или равно» (>=) и «меньше или равно» ( =F2)*(B2:B20

Как видно на рисунке эта формула СУММПРОИЗВ в первом своем аргументе содержит пары диапазонов ячеек и логических выражений с условиями, подобно как в предыдущей функции. Внутри каждой пары скобок (за исключением последней) находятся диапазоны ячеек и операторы сравнения со значениями. Последняя пара скобок просто содержит адрес общего диапазона заполненных всех ячеек в столбце D.

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

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

Принцип работы функции СУММПРОИЗВ основан на табличных массивах. Это такие таблицы, которые создаются в памяти и представляют сбой ничто иное как списки значений. В функции СУММПРОИЗВ каждый элемент таблицы сравнивается с соответственным ему значением. Например, в выше описанном примере сначала проверяется значение каждой ячейки в диапазоне A2:A20 на соответствие значению, записанному в F1. То есть функция ищет адреса ячеек, которые содержать значение «Бразилия». Результатом выполненных сравнений является очередная таблица, которая содержит список значений ИСТИНА и ЛОЖЬ, в соответствии с каждой ячейкой исходной таблицы. Эти результирующие логические значения в памяти называются элементами. Большая часть из них это естественно ложные элементы (в данном примере), но 6 элементов соответствуют ячейкам содержащих строку «Бразилия» и получают результативное значение ИСТИНА.

Четыре пары скобок соответствуют четырем таблицам, которые умножаются между собой. Фактически каждый элемент каждой таблицы перемножается с текущим элементом каждой другой таблицы. В Excel логическое значение ИСТИНА = 1, а ЛОЖЬ = 0. В результате если хотя бы один из 4-х умножаемых текущих элементов возвращает значение 0, результирующий текущий элемент также возвращает значение 0 – то есть ЛОЖЬ. Ведь любое число, умноженное на ноль = 0. Но, если вместо этого каждый текущий умножаемый элемент имеет значение ИСТИНА, тогда элемент в результирующей таблице будет иметь значение текущей ячейки в диапазоне D2:D20. Так как будут перемножены между собой три единицы и текущее значение с ячейки столбца D.

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

(название страны это не Бразилия)*(год больше чем 2001)*(год меньше чем 2004)*(текущее значение ВВП)

Первый ноль – это результат проверки первого табличного массива, который вернул логическое значение ЛОЖЬ – не равно Бразилия! Так как одно из умножаемых числе является нулем в результате простых арифметических операций также получаем 0.

В случае с 15-ой строкой листа складывается совсем другая ситуация. Все результаты проверки значений возвращают ИСТИНА, а значит =1. На этот раз при проверки первой ячейки выявлено, что она содержит текст «Бразилия». При проверке года на «больше или равно» >=2001 снова получаем ИСТИНА. Эта же ячейка с годом теперь проверяется по третьему условию «меньше или равно 2005» и в результате ИСТИНА. Таким образом выполняется следующая операция:

Для наглядности примера приведем таблицу:

Это итоговый результат вычислений для 15-ой строки листа 14-ой строки таблицы в памяти. По такому же принципу происходят операции и для остальных строк таблицы. По каждой строке выводится свой итоговый результат (0 или показатель ВВП) и суммируется функцией СУММПРОИЗВ. Данная функция весьма сложная и работает медленнее чем СУММЕСЛИМН, но ее все еще можно использовать в новых версиях Excel так как опытные пользователи привыкли к ней. Так же это дает возможность открывать старые версии файлов без ошибок в формулах. А при некоторых редких случаях она может быть даже более удобной других функций, поэтому стоит знать ее принципы работы.