Как посчитать сумму в строке в Excel. 3 способа расчета суммы чисел в строке Эксель

Как в Excel сцепить (склеить) несколько ячеек и текст в одну ячейку. Функция СЦЕПИТЬ, & (амперсанд) и несколько трюков объединения множества ячеек.

Что Означает Конкатенация?

Предположим, что у вас есть имя клиента в колонке С, и фамилия в колонке D. Вы хотите, чтобы в колонке E, у вас было записано полное имя, состоящее из этих двух частей, но это слишком затратно, заново все записывать в новой колонке.

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

How to Concatenate in Excel ExampleHow to Concatenate in Excel ExampleHow to Concatenate in Excel ExampleПример, как выглядит конкатенация в Excel, для данных о именах сотрудников.

К счастью, Excel может сделать это для нас автоматически. Эта техника называется конкатенация, и в общем под этим подразумевается объединение ячеек.

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

Объединение данных с помощью символа “амперсанд” (&)

  1. Выделите ячейку, в которую вы хотите вставить объединенные данные.

  2. Введите = (знак равенства) и выберите первую ячейку, которую нужно объединить.

  3. Введите символ & и пробел, заключенный в кавычки.

  4. Выберите следующую ячейку, которую нужно объединить, и нажмите клавишу ВВОД. Пример формулы: =A2&” “&B2.

Суммирование значений в строке

Воспроизвести процесс суммирования значений в строке можно следующими методами:

  • арифметическая формула;
  • автосумма;
  • различные функции.

Каждый из этих способов подразделяется на дополнительные способы. Разберемся с ними более подробно.

Способ 1: арифметическая формула

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

1

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

  1. Производим выделение той ячейки, где в дальнейшем будет отражаться результат. Вводим в ячейку символ «=». Жмем ЛКМ по самой первой ячейке в этой строчке, содержащей числовые показатели. Замечаем, что после нажатия координаты ячейки отобразились в ячейке для подсчета результата. Вводим символ «+» и кликаем на следующую ячейку в строке. Продолжаем чередовать символ «+» с координатами ячеек строки первой торговой точки. В результате мы получаем формулу: =B3+C3+D3+E3+F3+G3+H3.

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel2

  1. После проведения всех манипуляций нажимаем на «Enter».
  2. Готово! Результат вывелся в ячейке, в которую мы вводили формулу для подсчёта суммы.

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel3

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

Способ 2: автосумма

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

  1. Используя зажатую ЛКМ, производим выделение всех ячеек первой строки, которые имеют числовые данные. Передвигаемся в раздел «Главная», располагающийся в верхней части интерфейса табличного процессора. Находим блок команд «Редактирование» и кликаем на элемент под названием «Редактирование».kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel4

Рекомендация! Альтернативный вариант – переходим в раздел «Формулы» и нажимаем на кнопку «Автосумма», находящуюся в блоке «Библиотека функций». Третий вариант – использование комбинации клавиш «Alt» + «=» после выделения ячейки.

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel5

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

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel6

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

Способ 3: функция СУММ

Использование интегрированной функции табличного процессора под названием СУММ не имеет недостатков, как у рассмотренных ранее способов. СУММ – математическая функция. Задача оператора – суммирование числовых значений. Общий вид оператора: =СУММ(число1;число2;…).

Важно! Аргументами этой функции могут являться как числовые значения, так и координаты ячеек. Максимальное число аргументов – 255.

Пошаговое руководство выглядит следующим образом:

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

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel7

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

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel8

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

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel9

  1. Готово! В выбранной изначально ячейке отобразился результат суммирования.

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel10

Функция СУММ(SUM) не работает

Иногда случается так, что оператор СУММ не работает. Основные причины неисправности:

  • неправильный формат чисел (текстовый) при импорте данных;
  • наличие скрытых символов и пробелов в ячейках с числовыми значениями.

Стоит отметить! Числовые значения всегда выравниваются по правой стороне, а текстовая информация – по левой.

Как найти сумму наибольших (наименьших) значений

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

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel11

Оператор НАИБОЛЬШИЙ позволяет возвратить максимальный показатель из выбранных данных. 2-й аргумент указывает, какой именно показатель необходимо вернуть. В нашем конкретном примере формула выглядит так: =СУММ(НАИБОЛЬШИЙ(B2:D13;{1;2;3})).

Таким же образом работает и поиск наименьшего значения, только вместо оператора НАИБОЛЬШИЙ используется функция НАИМЕНЬШИЙ. Формула выглядит следующим образом: =СУММ(НАИМЕНЬШИЙ(B2:D13;{1;2;3})).

Растягивание формулы/функции суммирования на другие строки

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

  1. Производим подсчет суммы одним из вышеописанных способов. Перемещаем указатель мышки на нижнюю правую рамку ячейки с выведенным результатом. Курсор примет вид небольшого плюсика темного цвета. Зажимаем ЛКМ и растягиваем формулу до самого низа таблички.

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel12

  1. Готово! Мы вывели итоговые результаты по всем названиям. Мы добились такого результата благодаря тому, что при копировании формулы адреса смещаются. Смещение координат происходит из-за того, что адреса являются относительными.

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel13

  1. Для 3-й строчки формула имеет вид: =B3+C3+D3+E3+F3+G3+H3.

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel14

Как вычислить сумму каждой N-ой строки.

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

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel15

Задача: произвести расчёт еженедельной прибыли за каждую неделю.  Оператор СУММ позволяет суммировать данные не только в диапазоне, но и в массиве. Здесь необходимо использование вспомогательного оператора СМЕЩ. В операторе СМЕЩ указывается некоторое количество аргументов:

  1. Первая точка. Ячейка С2 вводится в виде абсолютной ссылки.
  2. Количество шагов вниз.
  3. Количество шагов вправо.
  4. Число шагов вниз.
  5. Количество столбиков в массиве. Попадание в последнюю точку массива показателей.

В конечном итоге мы получаем следующую формулу для первой недели: =СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1)). В результате оператор сумм произведет суммирование всех пяти числовых значений.

3-D сумма, или работаем с несколькими листами рабочей книги Excel

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

У нас есть четыре идентичных таблички. Обычный способ подсчета прибыли выглядит так: =СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8). Здесь в роли аргументов выступают диапазоны ячеек.

Формула 3D-суммы выглядит следующим образом: =СУММ(неделя1:неделя4!B2:B8). Здесь говорится о том, что производится суммирование в диапазонах В2:В8, которые располагаются на рабочих листах: неделя (от 1 до 4). Происходит пошаговое увеличение номера рабочего листа на единицу.

Как сцепить несколько ячеек.

Простейшее выражение для конкатенации содержимого A1 и B1 выглядит следующим образом:

=СЦЕПИТЬ(A1; B1)

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

Чтобы разделить их пробелом, введите “ “ во втором аргументе, как в строке 4.

=СЦЕПИТЬ(A4, ” “, B4)

Чтобы разделить объединенные значения с другими разделителями, такими как запятая, пробел или косая черта, смотрите здесь.

Объединение и суммирование данных с помощью опции консолидации

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

Другой метод — использовать сводную таблицу и суммировать данные (далее в этом руководстве).

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

76192c3b71ac85a61ab05c636156391a.pngХотя это уникальные записи, так как стоимость продаж различается, для целей отчетности вы можете удалить несколько экземпляров одной и той же страны и показать стоимость продаж как одну консолидированную сумму.

Ниже приведены шаги для этого:

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

f20cb5acad31888d3be390fb5d8b8e2d.pngВ конечном результате вы получите уникальный список стран вместе со стоимостью продаж из исходного набора данных.

Я решил получить СУММУ значений из каждой записи. Вы также можете выбрать другие параметры, такие как «Счетчик» или «Среднее» или «Макс. / Мин.».

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

Как Делать Конкатенацию в Excel (Смотри и Учись)

Посмотрите этот короткий видеоурок ниже, чтобы получить представление о том, как использовать конкатенацию для объединения текстовых строк и данных. И обязательно закачайте бесплатное приложение к уроку в виде рабочих документов Excel. А затем прочтите текстовую часть урока, чтобы узнать еще больше о конкатенации в Microsoft Excel.

7658cb2a9671b592f8f81967cf62cef5bacbbb90.jpg

Как вызвать функции СЦЕПИТЬ, СЦЕП, ОБЪЕДЕНИТЬ.

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

Соединение данных в MS Excel. Описание и примеры.

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

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

kak-poschitat-summu-v-stroke-v-excel-3-sposoba-rascheta-summy-chisel-v-stroke-excel16

Пошаговое руководство выглядит следующим образом:

  1. Для начала формируется табличка.
  2. Производится выбор ячейки, в которой будет выведен результат суммирования.
  3. Перемещаемся в строчку для ввода формул.
  4. Вводим оператор: =СУММАЕСЛИМН.
  5. Пошагово вводим диапазон сложения, диапазон условия1, условие1 и так далее.
  6. После проведения всех манипуляций жмем «Enter». Готово! Подсчет произведен.

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Сумма с разных листов

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

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

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

Объединение чисел и дат в различных форматах

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

ТЕКСТ(значение; формат) имеет два аргумента:

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

Например:

=СЦЕПИТЬ(“Сегодня “; ТЕКСТ(СЕГОДНЯ(); “dd-mm-yy”))

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

=A2 & ” ” & ТЕКСТ(B2; “$# ##0,0”) – отобразить число с двумя десятичными знаками и знак $.

=A2 & ” ” & ТЕКСТ(B2; “0,#”) – не отображает лишние нули и знак валюты.

=A2&” “&ТЕКСТ(B2;”#_??/??”)  – отобразить десятичную часть в виде дроби.

=”Сегодня  ” & ТЕКСТ(СЕГОДНЯ();”dd.mm.yyyy”) & ”  ” & A2 & ” ” & ТЕКСТ(B2;”$# ##0,0″) – показать текст, дату и число.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...