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

Обновлено: 04.07.2024

Для сложения или вычитания в Excel достаточно создать простую формулу. Не забывайте, что все формулы в Excel начинаются со знака равенства (=), а для их создания можно использовать строку формул.

Сложение нескольких чисел в одной ячейке

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

После знака равенства введите несколько чисел, разделенных знаком "плюс" (+).

Например: 50+10+5+3.

Нажмите клавишу RETURN.

Если использовать числа из примера, получается результат 68.

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

Сложение чисел с помощью ссылок на ячейки

Ссылка на ячейку представляет собой букву столбца и номер строки, например А1 или F345. При использовании в формуле ссылки на ячейку вместо значения ячейки можно изменить значение, не меняя формулу.

Введите число, например 5, в ячейку C1. Затем введите другое число, например 3, в ячейку D1.

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

После знака равенства введите C1+D1.

Нажмите клавишу RETURN.

Если использовать числа из примера, получается результат 8.

Если изменить значение в ячейке C1 или D1 и нажать клавишу RETURN, значение ячейки E1 изменится, даже если формула осталась неизменной.

Быстрое суммирование чисел в строке или столбце

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

На строка состояния, посмотрите на значение рядом с sum. Общее количество — 86.

Вычитание нескольких чисел в одной ячейке

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

После знака равенства введите несколько чисел, разделенных знаком "минус" (–).

Например: 50-10-5-3.

Нажмите клавишу RETURN.

Если использовать числа из примера, получается результат 32.

Вычитание чисел с помощью ссылок на ячейки

Введите числа в ячейки C1 и D1.

Например, введите 5 и 3.

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

После знака равенства введите C1-D1.

Нажмите клавишу RETURN.

Если использовать числа из примера, получается результат 2.

Сложение нескольких чисел в одной ячейке

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

После знака равенства введите несколько чисел, разделенных знаком "плюс" (+).

Например: 50+10+5+3.

Нажмите клавишу RETURN.

Если использовать числа из примера, получается результат 68.

Примечание: Если вместо ожидаемого результата вы видите дату, выберите ячейку, а затем на вкладке "Главная" в области "Число" выберите во всплывающее меню пункт "Общие".

Сложение чисел с помощью ссылок на ячейки

Введите число, например 5, в ячейку C1. Затем введите другое число, например 3, в ячейку D1.

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

После знака равенства введите C1+D1.

Нажмите клавишу RETURN.

Если использовать числа из примера, получается результат 8.

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

Быстрое суммирование чисел в строке или столбце

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

На строка состояния посмотрите на значение рядом с sum=. Общее количество — 86.

Если строка состояния не отображается, в меню Вид выберите пункт Строка состояния.

Вычитание нескольких чисел в одной ячейке

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

После знака равенства введите несколько чисел, разделенных знаком "минус" (–).

Например: 50-10-5-3.

Нажмите клавишу RETURN.

Если использовать числа из примера, получается результат 32.

Вычитание чисел с помощью ссылок на ячейки

Введите числа в ячейки C1 и D1.

Например, введите 5 и 3.

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

После знака равенства введите C1-D1.

Нажмите клавишу RETURN.

Если вы использовали числа из примера, результат будет -2.

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

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

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

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

Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию

Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF) :

Первый аргумент этой функции - диапазон проверяемых ячеек, второй - критерий отбора (слово "Выручка"), третий - диапазон ячеек с числами для суммирования.

Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF) , которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.

Способ 2. Формула массива для суммирования каждой 2-й, 3-й . N-й строки

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

Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:

Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.

Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:

А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:

И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:

Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.

Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.

Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:

Способ 3. Функция БДСУММ и таблица с условием

Формулы массива из предыдущего способа - штука красивая, но имеют слабое место - быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой - функцией БДСУММ (DSUM) . Перед использованием эта функция требует небольшой доработки, а именно - создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) - не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM) :

Способ 4. Суммирование каждой 2-й, 3-й. N-й строки

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

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

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

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

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

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

Жмем ОК и вводим ее аргументы:

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

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

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

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

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

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

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

=(A2="Копейка")*(B2="Григорьев")

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

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

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

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

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

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

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

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

Число находится в ячейке A2

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

=ОСТАТ( ЦЕЛОЕ(A2) ; 10) + ОСТАТ( ЦЕЛОЕ( A2/10) ; 10 ) + ОСТАТ( ЦЕЛОЕ( A2/100 ) ; 10) + ОСТАТ(ЦЕЛОЕ( A2/1000 ) ; 10 )+ОСТАТ( ЦЕЛОЕ( A2/10000 ) ; 10)

Скопируйте эту формулу и растяните на весь диапазон

=ЕСЛИ( ДЛСТР( A2&"" )>0 ; ЗНАЧЕН( ПСТР( A2&"" ; 1 ; 1) ) ; 0) + ЕСЛИ( ДЛСТР(A2&"")>1 ; ЗНАЧЕН(ПСТР(A2&"" ; 2 ; 1 )) ; 0) + ЕСЛИ( ДЛСТР( A2&"" )>2 ; ЗНАЧЕН(ПСТР(A2&"" ; 3 ; 1)) ; 0) + ЕСЛИ( ДЛСТР( A2&"" )>3 ; ЗНАЧЕН(ПСТР(A2&"" ; 4 ; 1)) ; 0) + ЕСЛИ(ДЛСТР( A2&"" )>4 ; ЗНАЧЕН(ПСТР(A2&"" ; 5 ; 1 )) ; 0)

Формулу упростить и получится так:

=ЕСЛИ( ДЛСТР( A2 )>0 ; ЗНАЧЕН( ПСТР( A2 ; 1 ; 1) ) ; 0)+ЕСЛИ( ДЛСТР(A2)>1 ; ЗНАЧЕН(ПСТР(A2 ; 2 ; 1 )) ; 0) + ЕСЛИ( ДЛСТР( A2 )>2 ; ЗНАЧЕН(ПСТР(A2 ; 3 ; 1)) ; 0) + ЕСЛИ( ДЛСТР( A2 )>3 ; ЗНАЧЕН(ПСТР(A2 ; 4 ; 1)) ; 0) + ЕСЛИ(ДЛСТР( A2 )>4 ; ЗНАЧЕН(ПСТР(A2 ; 5 ; 1 )) ; 0)

Если скопировать эту формулу в Эксель и не забыть делить строчку комбинацией клавиш ALT+ENTER, получится примерно так:

Если в колонке А записать дробное число то будет ошибка

Проверил, формулы работают. Здесь Вы применяете текстовые и логические функции, а нужно только математические. Спасибо, в любом случае они будут представлять интерес для пользователей БВ. — 3 года назад А в чем смысл писать А2&"" ? Результат же все равно равен А2, или я чего-то не понимаю? — 3 года назад A2&"" преобразовывает содержимое ячейки к строке для обработки функцией ДЛСТР, а выражение ДЛСТР(A2;1;1) является некорректной и непонятной записью, потому что в A2 записано число, а число не имеет длины строки , но работать будет правильно, потому что функция ДЛСТР сама преобразовывает число в строку. — 3 года назад А, неявное преобразование типов! Понятно. — 3 года назад

Интересная задача. Для пятизначных и менее чисел ее решение может выглядеть например так, если в Ячейке A1 число, сумму цифр которого надо просуммировать, то ,например, в ячейке В1 запишем формулу(если число в другой ячейке, то во вводимой формуле вместо А1, запишем ее адрес) :

=ОТБР (A1/10000)+ОТБР (ОСТАТ(A1;10000)/100 0)+ОТБР (ОСТАТ(A1;1000)/100) +ОТБР (ОСТАТ(A1;100)/10)+О СТАТ(A1;10). Поясню, что здесь в формуле делает каждый элемент: ОТБР(A1/10000)-выдел яет десятки тысяч, если их нет выдаст просто ноль, ОТБР(ОСТАТ(A1;10000) /1000) выделяет тысячи, ОТБР(ОСТАТ(A1;1000)/ 100) - выделяет сотни в числе, ОТБР(ОСТАТ(A1;100)/1 0 выделяет десятки в числе, и , наконец, ОСТАТ(A1;10) выделяет единицы. Далее мы их просто суммируем. Нетрудно будет переделать формулу под шести, семи и т. д.-значные числа.

Только формула при копировании в Эксель прямо со страницы дает ошибку, а если набирать руками, то очень легко ошибиться, особенно если сделать формулу на десятизначное число - лишний ноль (или недостаток нуля) заметить трудно, а формула будет давать неверный результат. — 3 года назад При копировании формулы из ехсел в текст ответа почему-то выдавало гиперссылку вместо части формулы, поэтому проблему устранила, добавив пробелы в формуле перед скобками. Поэтому можно скопировать формулу со страницы и убрать все пробелы. Все получится. — 3 года назад Можно вместо 10000 написать 10^4, тогда не ошибешься — 3 года назад

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

=ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР( A2&"" ; 1 ; 1 )) ; 0 ) + ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР( A2&"" ; 2 ; 1)) ; 0) + ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР( A2&"" ; 3 ; 1)) ; 0) + ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР(A2&"" ; 4;1)) ; 0) + ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР(A2&"" ; 5 ; 1)) ; 0)

Эту формулу можно записать и так:

=ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР( A2 ; 1 ; 1 )) ; 0 ) + ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР( A2 ; 2 ; 1)) ; 0) + ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР( A2 ; 3 ; 1)) ; 0) + ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР(A2 ; 4;1)) ; 0) + ЕСЛИОШИБКА( ЗНАЧЕН( ПСТР(A2 ; 5 ; 1)) ; 0)

Я не владею формулами Microsoft Excel, но меня заинтересовала данная задача исключительно математически. В конце концов математические функции везде одинаковые, разнятся только формы записи.

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

В расчете используется модуль, сумма, целая часть числа (floor) и округление (round). Надеюсь, что подобными функциями Excel располагает.

Рассмотрим первую формулу. Вычисление суммы чисел целой части числа (Sc).

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

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

Вторая формула оказалась сложнее и капризней. Сумма цифр дробной части числа (Sd).

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

Третья формула общая сумма цифр (Sob)

Теперь о том почему у меня именно 9 разрядов целой части и 6 дробной.

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

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

Я понимаю, что не ответил на поставленный вопрос, но мне было интересно для себя разобраться с данной задачей.

Читайте также: