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

Как быстро суммировать данные каждого столбца в Excel?

Как сложить строки в Excel

Сложение столбцов в Microsoft Excel

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

Сложение столбцов

Функция суммирования столбцов в Excel соответствует тем же общим принципам, что и функция суммирования данных в программе. В процедуре есть некоторые особенности, но они не главное. Как и при любом другом суммировании в этом табличном процессоре, столбцы можно складывать с помощью простой арифметической формулы, встроенной функции Excel SUMM или Auto Sum.

Урок: Подсчет суммы в Excel

Способ 1: использование автосуммы

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

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

Таблица выручки по магазинам в Microsoft Excel

  1. Чтобы узнать общий оборот за 7 дней по каждому магазину отдельно, мы используем автосуммирование. Выделяем курсором с зажатой левой кнопкой мыши в столбце «Store 1» все элементы, содержащие числовые значения. Затем нажмите кнопку «Autosummary» на вкладке «Home», которая находится на ленте в группе настроек «Edit».

Подсчет автосуммы для Магазина 1 в Microsoft Excel

Автосумма для Магазина 1 подсчитана в Microsoft Excel

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

Маркер заполнения в Microsoft Excel

Автосумма подсчитана по каждому магазину в Microsoft Excel

Суммирование всех столбцов с помощью автосуммы в Microsoft Excel

Общая сумма по всем магазинам в Microsoft Excel

Способ 2: использование простой математической формулы

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

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

Формула, полученная в нашем случае, выглядит следующим образом:

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

Сложение ячеек в столбце в Microsoft Excel

Итог сложения ячеек в столбце в Microsoft Excel

Итог сложения ячеек во всех столбцах в отдельности в Microsoft Excel

В результате получается следующая формула

Но и эта формула зависит от каждого конкретного случая.

Сложение сумм столбцов в Microsoft Excel

Общая сумма столбцов в Microsoft Excel

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

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

Читайте так же:
Как в Excel вести итог в одной или отдельной ячейке?

Способ 3: применение функции СУММ

Устранить недостатки двух предыдущих методов можно с помощью функции Excel, известной как AML. Цель этого оператора — простое суммирование чисел. Эта функция относится к категории математических функций и имеет следующий простой синтаксис:

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

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

    Отмечаем элемент на листе, в котором будет отображаться величина дохода по первой колонке. Щелкаем по иконке «Вставить функцию», которая размещена слева от строки формул.

Переход в Мастер функций в Microsoft Excel

Переход в окно аргумнтов функции СУММ в Microsoft Excel

В поле «Номер 1» поместите координаты диапазона, содержащего ячейки столбца «Магазин 1». Это делается очень просто. Поместите курсор в поле аргументов. Затем, зажав левую кнопку мыши, выделите все ячейки столбца «Хранить 1», которые содержат числовые значения. Как только адрес был введен, он отобразился в виде координат массива. В нижней части окна должна появиться надпись «ОК».

Окно аргументов функции СУММ в Microsoft Excel

Результат вычисления фнкции СУММ в Microsoft Excel

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

Результаты рачета функции СУММ для всех магазинов в отдельности в Microsoft Excel

Окно аргументов функции СУММ в программе Microsoft Excel

Результат сложения чисел в массиве с помощью функции СУММ в Microsoft Excel

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

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

Перемещение в Мастер функций в Microsoft Excel

Категория Мастера функций 10 недавно использовавшихся в Microsoft Excel

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

После ввода адреса массива нажмите на кнопку «OK».

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

Результаты рачета функции СУММ по всем магазинам в Microsoft Excel

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

Однако бывают ситуации, когда вам нужно добавить не все столбцы таблицы, а только некоторые. Это еще сложнее, если они расположены не рядом друг с другом. Давайте посмотрим, как выполняется этот тип сложения с помощью оператора SUM в примере с той же таблицей. Предположим, что нам нужно сложить только значения в столбцах Big 1, Big 3 и Big 5. В этом случае результат должен быть вычислен без промежуточных сумм для столбцов.

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

Во время отображения введите в колонке «Store 1» адрес диапазона данных в поле «Number1». Как и раньше, установите курсор в поле и выделите соответствующий диапазон. Используя столбцы «Store 3» и «Store 5», вводим адреса массивов данных в «Number1» и «Number2». Введенные координаты выглядят следующим образом:

Наконец, нажимаем на кнопку «OK».

Окно аргументов функции СУММ при подсчете общей суммы в одтельных столбцах в программе Microsoft Excel

Результат вычисления фнкции СУММ при подсчете общей суммы в отдельных столбцах в Microsoft Excel

Урок: Использование Мастера функций в Microsoft Excel

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

Читайте так же:
Как в Excel разделить положительные и отрицательные числа на два столбца?

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

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

  1. 1 Как в Экселе посчитать сумму определенных ячеек
  2. 2 Формула суммы в Excel
  3. 3 Функция СУММ в Excel
    1. 3.1 Добавление горячих клавиш

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

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

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

    Сложение ячеек Excel выделением

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

    Формула суммы в Excel

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

    1. Откройте ту таблицу, в которой нужно произвести расчет.
    2. В свободной ячейке поставьте курсор и введите знак «=» без кавычек. Программа автоматически активирует функцию, предназначенную для суммирования чисел в ячейках таблицы.
    3. Щелкните левой кнопкой мыши по той ячейке, данные которой будут применены в расчете. Там, где поставлен знак «=», появится значение из цифр и букв, обозначающее местоположение числа в таблице. При этом буква – это столбец, а цифра – строка.

    Формула суммы в Excel

    Сложение ячеек Excel формулой

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

    Простое сложение в Excel

    Если ячейка или ее текст пустые, программа автоматически игнорирует эти данные. Можно ввести формулу вручную после знака «=» в верхней строке. Буквенно-цифровое значение в ячейке должно быть добавлено с помощью знака «+», независимо от того, используются ли буквы верхнего или нижнего регистра. Также нажмите клавишу Enter, чтобы получить результат.

    Функция СУММ в Excel

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

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

    Функция СУММ в Excel

    Функция СУММ в Excel

    Функция СУММ в Excel

    Сумма выбранной матрицы появляется в выделенной ячейке, а функция — в строке ввода. Если вы хотите добавить еще один диапазон данных, необходимо задать его в окне «Аргументы функции» в строке «Число 2», а затем нажать кнопку «OK». Новые данные будут добавлены к ранее полученному результату в выбранной ячейке.

    Функция СУММ в Excel

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

    Функция СУММ в Excel

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

    СУММ с помощью горячих клавиш

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

    1. Выберите пустую ячейку, в которой вы хотите сохранить сумму.
    2. Нажмите и удерживайте клавишу Alt, затем один раз нажмите клавишу =.
    3. Отпустите клавишу Alt . Функция SUM вставляется в выбранную ячейку.
    4. Нажмите Enter, чтобы Excel вычислил выбранный диапазон.

    Windows распознает эту комбинацию клавиш. Для MacOS одновременно нажмите Shift + Command + T, чтобы активировать функцию.

    В Excel существует три различных типа функций суммирования, которые используются в зависимости от цели. Помимо вычисления результата суммы выбранных ячеек или диапазона ячеек, существует также условная сумма: SUMMESLI и SUMMESLIMN.

    Автосумма в Excel

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

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

    Автосумма в Excel

    Автосумма в Excel

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

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

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

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

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

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

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

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

    Встроенные инструменты Excel позволяют создать форматированную таблицу из данных и извлечь результат. Вы можете сделать это с помощью :

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

    Форматировать как таблицу Excel

    Строка итогов в Excel

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

    Excel. Часть 3. Сводные таблицы

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

    Зачем нужны сводные таблицы?

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

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

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

    • Например, мы будем использовать данные Министерства образования РФ о количестве российских детей, усыновленных за рубежом в 2019 году. Мы используем эту статистику в нашем исследовании того, как ограничения, введенные российскими властями в отношении иностранцев, повлияли на российских сирот. Мы собрали эту обучающую базу данных из отчетов каждого региона. Они включают год, регион, из которого дети были усыновлены за границу, страну, в которую они отправились, количество усыновленных детей, количество усыновленных детей-инвалидов и количество усыновленных детей разных возрастных групп.
    • Это руководство подходит для работы в таблицах Google.

    Как создать сводную таблицу?

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

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

    Например, давайте посчитаем, сколько детей было усыновлено за границей и в какие страны. В поле «Строки» выбираем «Страна». В поле «Значения» выбираем «Общее количество усыновленных». В появившемся поле «Итого» с помощью параметра SUM будет просуммировано количество усыновлений.

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

    Сортировка в сводной таблице

    Используя сводные таблицы, работает сортировка. Чтобы наглядно увидеть, в каких странах было усыновлено больше всего детей, выбираем в поле «Страна» — «Сортировать по» — «SUM параметра Всего усыновлено» в поле «Страна». При перестановке таблицы мы увидим, что больше всего детей за рубежом усыновила Италия.

    Фильтры в сводной таблице

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

    Процентное отображение значений

    Данные в сводной таблице можно отобразить по-разному. Мы также можем отобразить количество усыновленных детей не в абсолютных цифрах, а в процентах. Это можно сделать, выбрав «Показать как» — «% от общего числа» в поле «Значения» параметра «Всего усыновлено». Например, в 2019 году 68% детей, усыновленных за рубежом, были из Италии.

    Добавление нескольких значений

    Существует способ добавления нескольких значений за один раз в сводную таблицу. В дополнение к общему количеству усыновленных в странах, покажите количество детей с ограниченными возможностями. Для этого добавьте еще одно «Значение» — «Усыновленные дети с ограниченными возможностями», а в окне «Сумма по» — параметр SUM. Можно добавить значение, зависящее от возраста. Возможно, вы захотите узнать, сколько детей в возрасте до одного года было усыновлено в этих странах. Есть еще одно «Значение» — «до 1 года», и мы добавляем еще один параметр в «Sum by». Надеемся, к тому времени мы увидим, что иностранные усыновители не принесли в свои дома малышей в 2019 году.

    Перемена строк

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

    Чтобы узнать, сколько детей покинуло определенный регион, снова можно обратиться к разделу «Фильтры». Параметр «Регион» добавляется в поле «Фильтр» нажатием кнопки «Добавить». Если вы выберете выпадающее меню под «Статус», вы увидите, что теперь все регионы выделены. Чтобы очистить поля, нажмите «Очистить» и введите только один регион, например «Москва». В появившейся сводной таблице мы увидим, что в 2019 году иностранцы усыновили только двух детей из Москвы.

    Суммирование значений разными способами

    В прошлом все значения суммировались с помощью функции SUM. Однако в выпадающем меню есть и другие опции, которые также можно использовать: среднее, минимум, максимум и так далее. Мы можем использовать функцию COUNTUNIQUE для подсчета уникальных значений в нашем наборе данных. Например, мы хотим узнать, сколько регионов в целом затронуто международным усыновлением. Для начала нам нужно очистить таблицу, а затем создать новую. В «Строках» добавим «Страну». В «Значениях» используется параметр COUNTUNIQUE — «Регион». Как видно из строки «Всего» сводной таблицы, в усыновлении участвовали 30 регионов.

    Эта же таблица показывает нам, из скольких регионов каждая страна усыновила детей (функция COUNTUNIQUE подсчитывает количество уникальных пар «страна»-«регион»). Если мы добавим к этому еще одно значение — «Всего усыновлено», то увидим, например, что все усыновленные Аргентиной дети — а их было 23 — были из одного региона.

    Вы можете добавить еще одну «Строку» под названием «Регион», чтобы определить, какой это регион. Такая таблица позволяет увидеть, какие страны усыновили детей из каких регионов. Аргентина усыновила всех 23 детей из Пермского края бывшего СССР. В этих данных, похоже, кроется какая-то интересная журналистская история.

    Использование столбцов

    Эту же таблицу можно отобразить в другом, более удобном для глаз виде, где строки — это регионы, а столбцы — страны. Это делается путем удаления ненужных значений: удалите «Страну» из «Строк» и «Регионы» из «Значений». Затем добавьте «Country» в поле «Columns».

    Сохранение результатов

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

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector