Tw-city.info

IT Новости
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как в excel сделать сумму строк

Подсчет суммы в строке таблицы в Microsoft Excel

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

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

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

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

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

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

  1. Выделяем ячейку, в которую будет выводиться готовый результат подсчета итога. Ставим туда знак «=». Кликаем левой кнопкой мыши по первой ячейке в данной строке, которая содержит числовые значения. Как видим, её адрес тут же отобразится в элементе для вывода суммы. Ставим знак «+». Затем кликаем по следующей ячейке в строке. Таким способом чередуем знак «+» с адресами ячеек строчки, которая относится к первому магазину.

В итоге в конкретно нашем случае получается следующая формула:

Естественно, при использовании других таблиц вид её будет отличаться.

  • Для выведения общей суммы выручки по первой торговой точке жмем на кнопку Enter на клавиатуре. Результат выводится в ту ячейку, в которой была расположена формула.
  • Как видим, данный способ довольно простой и интуитивно понятный, но у него имеется один существенный недостаток. На его осуществление нужно потратить много времени, если сравнивать с теми вариантами, которые мы рассмотрим ниже. А если в таблице очень много столбцов, то временные затраты увеличатся ещё больше.

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

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

      Выделяем все ячейки с числовыми значениями первой строчки. Выделение проводим, зажав левую кнопку мыши. Перейдя во вкладку «Главная», жмем на значок «Автосумма», который расположен на ленте в блоке инструментов «Редактирование».

    Другим вариантом вызова автосуммы является переход во вкладку «Формулы». Там в блоке инструментов «Библиотека функций» на ленте следует щелкнуть по кнопке «Автосумма».

    Если же вы вообще не хотите перемещаться по вкладкам, то после выделения строчки можно просто набрать сочетание горячих клавиш Alt+=.

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

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

    Преодолеть недостатки двух вышеописанных способов может вариант с применением встроенной функции Excel под названием СУММ.

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

    Как видим, аргументами этого оператора являются числа или адреса ячеек, в которых они располагаются. Их количество может быть до 255.

    Посмотрим, как можно просуммировать элементы в строке при помощи данного оператора на примере нашей таблицы.

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

    Запускается инструмент, который носит наименование Мастер функций. Переходим в нем в категорию «Математические» и из открывшегося списка операторов выбираем название «СУММ». Затем кликаем по кнопке «OK» внизу окошка Мастера функций.

    Производится активация окна аргументов оператора СУММ. В этом окне может располагаться до 255 полей, но для решения нашей задачи понадобится всего одно поле – «Число1». В него нужно ввести координаты той строки, значения в которой следует сложить. Для этого ставим курсор в указанное поле, а затем, произведя зажим левой кнопкой мыши, выделяем курсором весь числовой диапазон нужной нам строчки. Как видим, адрес данного диапазона тут же будет отображен в поле окна аргументов. Затем щелкаем по кнопке «OK».

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

    Способ 4: массовое суммирование значений в строках

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

    Читать еще:  Как найти доверительный интервал в excel

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

  • Как видим, все ячейки были заполнены данными. Это и есть сумма значений отдельно по строкам. Такой результат удалось получить потому, что по умолчанию все ссылки в Excel относительны, а не абсолютны, и при копировании изменяют свои координаты.
  • Как видим, в Экселе существует три основных способа подсчитать сумму значений в строчках: арифметическая формула, автосумма и функция СУММ. Каждый из данных вариантов имеет свои достоинства и недостатки. Наиболее интуитивно простой способ – это применение формулы, самый быстрый вариант – автосумма, а самый универсальный – использование оператора СУММ. Кроме того, с помощью маркера заполнения можно проводить массовое суммирование значений по строкам, выполненное одним из трех способов, которые были перечислены выше.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Как посчитать сумму в строке в Excel

    В данной статье мы рассмотрим, как посчитать сумму в строке в Excel с помощью функции СУММ, а также, как посчитать сумму в строке с неопределенным количеством столбцов.

    Как посчитать сумму в строке с помощью функции СУММ

    Самым распространенным способом, как посчитать сумму в строке в Excel, является способ с использованием функции СУММ . Например, чтобы посчитать сумму ячеек в строке в диапазоне B2-D2, используйте следующую формулу:

    Как посчитать сумму в строке в Excel – Посчитать сумму в строке в Excel

    Вставить функцию СУММ можно с помощью кнопки « Автосумма », которая находится на вкладке « ГЛАВНАЯ » в группе « Редактирование ». Для этого достаточно выбрать ячейку, где нужно автоматически посчитать сумму в строке и нажать на « Автосумму » и на клавишу « Enter ».

    Как посчитать сумму нескольких строк в Excel

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

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

    Как посчитать сумму в строке в Excel – Копирование формулы вниз для суммирования значений для каждой строки

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

    =СУММ(B2:D6) – посчитает сумму в строках 2-6.

    =СУММ(B2:D3; B5:D6) – посчитает сумму в строках 2, 3, 5 и 6.

    Как посчитать сумму строки

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

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

    Как посчитать сумму в строке в Excel – Посчитать сумму всей строки

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

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

    Как посчитать сумму в Экселе

    Существует несколько способов как посчитать сумму в Экселе:

    • выделить область ячеек;
    • использовать кнопку “Автосумма”;
    • создать формулу простого сложения;
    • с помощью функции СУММ.

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

    Как в Экселе посчитать сумму в столбце

    Посчитать сумму с помощью выделения области ячеек

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

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

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

    Как посчитать сумму в столбце с помощью формулы простого сложения

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

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

    =B2+B3+B4

    В формуле выше указывайте номера ячеек которые вы хотите суммировать:

    Как посчитать сумму в столбце с помощью кнопки “Автосумма”

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

    Для подсчета чисел в столбце с помощью автосуммы сделайте следующее:

    • кликните на первую пустую ячейку в колонке под значениями, которые вы хотите суммировать:

    • на панели инструментов выберите значок “Автосумма”:

    • после нажатия кнопки система автоматически выберет диапазон для суммирования. Если система выбрала диапазон не правильно – вы можете его скорректировать просто изменив формулу:

    • как только вы убедитесь что диапазон значений для суммы выбран правильно, просто нажмите клавишу Enter и система посчитает сумму в столбце:
    Читать еще:  Разбить ячейку на 2 в excel

    Как посчитать сумму в столбце с помощью функции СУММ в Excel

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

    • выделите левой клавишей мыши ячейку и введите функцию “СУММ“, задав необходимый диапазон ячеек:

    • нажмите кнопку “Enter” и функция посчитает сумму в указанном диапазоне.

    Как посчитать сумму в столбце в Excel с помощью таблицы

    Для подсчета суммы в столбце с данными вы можете отформатировать данные как таблицу. Для этого:

    • выделите диапазон ячеек с данными и преобразуйте их в таблицу с помощью клавиши на панели инструментов “Форматировать как таблицу”:

    • после того как ваши данные представлены в формате таблицы, на вкладке “Конструктор” на панели инструментов выберите пункт “Строка итогов” для того, чтобы добавить сумму колонок под таблицей:

    Как посчитать сумму в нескольких столбцах в Экселе одновременно

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

    • выделите область ячеек, которую вы хотите суммировать + захватите одну пустую колонку и строку рядом с таблицей для суммирования:

    • нажмите на панели инструментов кнопку “Автосумма”:

    • после этого действия, система автоматически посчитает сумму по выделенным колонкам и строкам:

    Суммирование каждой 2-й, 3-й. N-й ячейки

    Постановка задачи

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

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

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

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

    Способ 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) :

    Читать еще:  Excel 2020 в разных окнах

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

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

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

    Функция СУММ

    В этом курсе:

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

    =СУММ(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.

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