Вы можете суммировать столбец одним щелчком мыши, функцией автосуммирования, функцией СУММ, функцией фильтрации и путем преобразования набора данных в таблицу.
Сложение столбцов или строк чисел - это то, что большинству из нас требуется делать довольно часто. Например, если вы храните важные данные, такие как записи о продажах или прайс-листы, в ячейках одного столбца, вам может потребоваться быстро узнать общую сумму этого столбца. Итак, необходимо знать, как суммировать столбец в Excel.
Существует несколько способов суммирования или суммирования столбца / строки в Excel, включая использование функции автосуммирования, функции СУММ, функции фильтрации, функции СУММЕСЛИ и преобразования набора данных в таблицу одним щелчком мыши. В этой статье мы увидим различные методы добавления столбца или строки в Excel.
СУММИРОВАТЬ столбец одним щелчком мыши с помощью строки состояния
Самый простой и быстрый способ рассчитать общее значение столбца - щелкнуть букву столбца с числами и проверить строку «Статус» внизу. В Excel есть строка состояния в нижней части окна Excel, в которой отображается различная информация о листе Excel, включая среднее, количество и суммарное значение выбранных ячеек.
Предположим, у вас есть таблица данных, как показано ниже, и вы хотите найти сумму цен в столбце B.
Все, что вам нужно сделать, это выбрать весь столбец с числами, которые вы хотите суммировать (столбец B), щелкнув букву B в верхней части столбца и взглянув на строку состояния Excel (рядом с элементом управления масштабированием).
Там вы увидите общее количество выбранных ячеек, а также среднее и подсчетное значения.
Вы также можете выбрать диапазон данных от B2 до B11 вместо всего столбца и увидеть строку состояния, чтобы узнать общую сумму. Вы также можете найти общее количество чисел в строке, выбрав строку значений вместо столбца.
Преимущество использования этого метода заключается в том, что он автоматически игнорирует ячейки с текстовыми значениями и суммирует только числа. Как вы можете видеть выше, когда мы выбрали весь столбец B, включая ячейку B1 с текстовым заголовком (Цена), он суммировал только числа в этом столбце.
СУММИРОВАТЬ столбец с функцией автосуммирования
Еще один самый быстрый способ суммировать столбец в Excel - использовать функцию автосуммирования. Автосумма - это функция Microsoft Excel, которая позволяет быстро складывать диапазон ячеек (столбец или строка), содержащий числа / целые / десятичные дроби, с помощью функции СУММ.
На обеих вкладках «Главная» и «Формула» ленты Excel есть командная кнопка «Автосумма», которая при нажатии вставляет «функцию СУММ» в выбранную ячейку.
Предположим, у вас есть таблица данных, как показано ниже, и вы хотите просуммировать числа в столбце B. Выберите пустую ячейку прямо под столбцом или в правом конце строки данных (для суммирования строки), которую необходимо суммировать.
Затем выберите вкладку «Формула» и нажмите кнопку «Автосумма» в группе «Библиотека функций».
Или перейдите на вкладку «Главная» и нажмите кнопку «Автосумма» в группе редактирования.
В любом случае, как только вы нажмете кнопку, Excel автоматически вставит «= СУММ ()» в выбранную ячейку и выделит диапазон с вашими числами (маршируют муравьи по диапазону). Убедитесь, что выбран правильный диапазон, и если это не тот диапазон, вы можете изменить его, выбрав другой диапазон. И параметры функции будут автоматически настроены в соответствии с этим.
Затем просто нажмите Enter на клавиатуре, чтобы увидеть сумму всего столбца в выбранной ячейке.
Вы также можете вызвать функцию автосуммирования с помощью сочетания клавиш.
Для этого выберите ячейку, которая находится чуть ниже последней ячейки в столбце, для которого вы хотите получить результат, и используйте приведенный ниже ярлык:
Alt + = (Нажмите и удерживайте клавишу Alt, затем нажмите клавишу со знаком равенства =И он автоматически вставит функцию СУММ и выберет для нее диапазон. Затем нажмите Enter, чтобы подвести итоги столбца.
Автосумма позволяет быстро суммировать столбец или строку одним щелчком мыши или нажатием сочетания клавиш.
Однако существует определенное ограничение для функции автосуммирования, она не обнаружит и не выберет правильный диапазон в случае, если в диапазоне есть пустые ячейки или любая ячейка, имеющая текстовое значение.
Как вы можете видеть в приведенном выше примере, ячейка B6 пуста. И когда мы ввели функцию автосуммирования в ячейку B12, она выберет только 5 ячеек выше. Это связано с тем, что функция воспринимает ячейку B7 как конец данных и возвращает всего 5 ячеек.
Чтобы исправить это, вам нужно изменить диапазон, щелкнув и перетащив мышью, или ввести правильные ссылки на ячейки вручную, чтобы выделить весь столбец, и нажать Enter. И вы получите правильный результат.
Чтобы избежать этого, вы также можете ввести функцию СУММ вручную, чтобы вычислить сумму.
СУММ для столбца путем ручного ввода функции СУММ
Хотя команда «Автосумма» является быстрой и простой в использовании, иногда вам может потребоваться ввести функцию СУММ вручную, чтобы вычислить сумму столбца или строки в Excel. Особенно, если вы хотите добавить только некоторые из ячеек в столбце или если столбец содержит какие-либо пустые ячейки или ячейки с текстовым значением.
Кроме того, если вы хотите отобразить значение суммы в любой из ячеек на листе, кроме ячейки прямо под столбцом или ячейки после строки чисел, вы можете использовать функцию СУММ. С помощью функции СУММ вы можете вычислить сумму или общее количество ячеек в любом месте рабочего листа.
Синтаксис функции СУММ:
= СУММ (число1; [число2]; ...).номер 1(обязательно) - первое добавляемое числовое значение.номер 2(необязательно) - второе добавляемое числовое значение.
Хотя номер 1 является обязательным аргументом, вы можете суммировать до 255 дополнительных аргументов. Аргументами могут быть числа, которые вы хотите сложить, или ссылки на ячейки с числами.
Еще одно преимущество использования функции СУММ вручную заключается в том, что вы складываете числа в несмежных ячейках столбца или строки, а также в нескольких столбцах или строках. Вот как вы используете функцию СУММ вручную:
Сначала выберите ячейку, в которой вы хотите увидеть общую сумму столбца или строки в любом месте листа. Затем начните формулу, набрав = СУММ ( в камере.
Затем выберите диапазон ячеек с числами, которые вы хотите суммировать, или введите ссылки на ячейки для диапазона, который вы хотите суммировать в формуле.
Вы можете либо щелкнуть и перетащить с помощью мыши, либо удерживать клавишу Shift, а затем использовать клавиши со стрелками для выбора диапазона ячеек. Если вы хотите ввести ссылку на ячейку вручную, введите ссылку на первую ячейку диапазона, затем двоеточие и ссылку на последнюю ячейку диапазона.
После того, как вы ввели аргументы, закройте скобку и нажмите клавишу Enter, чтобы получить результат.
Как видите, даже если в столбце есть пустая ячейка и текстовое значение, функция дает вам сумму всех выбранных ячеек.
Суммирование непрерывных ячеек в столбце
Вместо того, чтобы суммировать диапазон непрерывных ячеек, вы также можете суммировать прерывистые ячейки в столбце. Чтобы выделить несмежные ячейки, удерживайте клавишу Ctrl и щелкните ячейки, которые вы хотите добавить, или введите ссылки на ячейки вручную и разделите их запятыми (,) в формуле.
Это отобразит сумму только выбранных ячеек в столбце.
Суммирование нескольких столбцов
Если вам нужна сумма нескольких столбцов, выберите несколько столбцов с помощью мыши или введите ссылку на первую ячейку в диапазоне, затем двоеточие и последнюю ссылку на ячейку в диапазоне для аргументов функции.
После ввода аргументов закройте скобку и нажмите клавишу Enter, чтобы увидеть результат.
Суммирование несмежных столбцов
Вы также можете суммировать несмежные столбцы, используя функцию СУММ. Вот как:
Выберите любую ячейку на листе, в которой вы хотите отобразить сумму несмежных столбцов. Затем запустите формулу, набрав функцию = СУММ ( в этой камере. Затем выберите первый диапазон столбцов с помощью мыши или введите ссылку на диапазон вручную.
Затем добавьте запятую и выберите следующий диапазон или введите ссылку на второй диапазон. Таким образом вы можете добавить столько диапазонов, сколько захотите, и разделить каждый из них запятой (,).
После аргументов закройте скобку и нажмите Enter, чтобы получить результат.
Суммирование столбца с использованием именованного диапазона
Если у вас большой рабочий лист данных и вы хотите быстро вычислить общее количество чисел в столбце, вы можете использовать именованные диапазоны в функции СУММ, чтобы найти итог. При создании именованных диапазонов вы можете использовать эти имена вместо ссылок на ячейки, что упрощает обращение к наборам данных в Excel. В функции легко использовать именованный диапазон вместо того, чтобы прокручивать сотни строк для выбора диапазона.
Еще одна хорошая вещь в использовании именованного диапазона заключается в том, что вы можете обратиться к набору данных (диапазону) на другом листе в аргументе SUM и получить значение суммы на текущем листе.
Чтобы использовать именованный диапазон в формуле, сначала необходимо его создать. Вот как вы создаете и используете именованный диапазон в функции СУММ.
Сначала выберите диапазон ячеек (без заголовков), для которого вы хотите создать именованный диапазон. Затем перейдите на вкладку «Формулы» и нажмите кнопку «Определить имя» в группе «Определенные имена».
В диалоговом окне «Новое имя» укажите имя, которое вы хотите дать выбранному диапазону в поле «Имя:». В поле «Область:» вы можете изменить область действия именованного диапазона для всей книги или отдельного рабочего листа. Область определяет, будет ли именованный диапазон доступен для всей книги или только для определенного листа. Затем нажмите кнопку «ОК».
Вы также можете изменить ссылку на диапазон в поле «Относится к».
Кроме того, вы также можете назвать диапазон, используя поле «Имя». Для этого выберите диапазон, перейдите в поле «Имя» слева от строки формул (чуть выше буквы A) и введите имя, которое вы хотите присвоить выбранному диапазону дат. Затем нажмите Enter.
Но когда вы создаете именованный диапазон с помощью поля «Имя», он автоматически устанавливает область действия именованного диапазона для всей книги.
Теперь вы можете использовать созданный вами именованный диапазон, чтобы быстро найти значение суммы.
Для этого выберите любую пустую ячейку в любом месте книги, где вы хотите отобразить результат суммы. Введите формулу СУММ с указанным диапазоном в качестве аргументов и нажмите Enter:
= СУММ (Цены)
В приведенном выше примере формула на листе 4 ссылается на столбец с именем «Цены» на листе 2, чтобы получить сумму столбца.
Суммируйте только видимые ячейки в столбце, используя функцию ПРОМЕЖУТОЧНЫЙ ИТОГ
Если у вас есть отфильтрованные или скрытые ячейки в наборе данных или столбце, использование функции СУММ для суммирования столбца не идеально. Поскольку функция СУММ включает в свой расчет отфильтрованные или скрытые ячейки.
В приведенном ниже примере показано, что происходит, когда вы суммируете столбец со скрытыми или отфильтрованными строками:
В приведенной выше таблице мы отфильтровали столбец B по ценам, которые меньше 100. В результате у нас есть несколько отфильтрованных строк. Вы можете заметить, что в таблице есть отфильтрованные / скрытые строки по отсутствующим номерам строк.
Теперь, когда вы суммируете видимые ячейки в столбце B с помощью функции СУММ, вы должны получить «207» в качестве значения суммы, но вместо этого оно показывает «964». Это потому, что функция СУММ также учитывает отфильтрованные ячейки при вычислении суммы.
Вот почему вы не можете использовать функцию СУММ, когда задействованы отфильтрованные или скрытые ячейки.
Если вы не хотите, чтобы отфильтрованные / скрытые ячейки включались в расчет при суммировании столбца, и вы хотите суммировать только видимые ячейки, вам необходимо использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ.
ПРОМЕЖУТОЧНЫЙ ИТОГ Функция
ПРОМЕЖУТОЧНЫЙ ИТОГ - это мощная встроенная функция в Excel, которая позволяет выполнять различные вычисления (СУММ, СРЕДНЕЕ, СЧЁТ, МИН, РАЗБИРАТЕЛЬНОСТЬ и другие) для диапазона данных и возвращает общий или агрегированный результат столбца. Эта функция суммирует данные только в видимых ячейках, игнорируя отфильтрованные или скрытые строки. ПРОМЕЖУТОЧНЫЙ ИТОГ - это универсальная функция, которая может выполнять 11 различных функций в видимых ячейках столбца.
Синтаксис функции ПРОМЕЖУТОЧНЫЙ ИТОГ:
= ПРОМЕЖУТОЧНЫЙ ИТОГ (номер_функции; ссылка1; [ссылка2]; ...)Аргументы:
function_num(обязательный)– Это номер функции, который указывает, какую функцию использовать для расчета итоговой суммы. Этот аргумент может принимать любое значение от 1 до 11 или от 101 до 111. Здесь нам нужно суммировать видимые ячейки, игнорируя отфильтрованные ячейки. Для этого нам нужно использовать «9».ref1(обязательный)– Первый именованный диапазон или ссылка, по которой вы хотите вычислить промежуточные итоги.ref2(по желанию) - Второй именованный диапазон или ссылка, по которой вы хотите вычислить промежуточные итоги. После первой ссылки вы можете добавить до 254 дополнительных ссылок.
Суммирование столбца с использованием функции ПРОМЕЖУТОЧНЫЙ ИТОГ
Если вы хотите суммировать видимые ячейки и исключить отфильтрованные или скрытые ячейки, выполните следующие действия, чтобы использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ для суммирования столбца:
Во-первых, вам нужно отфильтровать вашу таблицу. Для этого щелкните любую ячейку в наборе данных. Затем перейдите на вкладку «Данные» и щелкните значок «Фильтр» (значок воронки).
Стрелки появятся рядом с заголовками столбцов. Щелкните стрелку рядом с заголовком столбца, с помощью которого вы хотите отфильтровать таблицу. Затем выберите вариант фильтра, который вы хотите применить к своим данным. В приведенном ниже примере мы хотим отфильтровать столбец B числами меньше 100.
В диалоговом окне «Пользовательский автофильтр» вводим «100» и нажимаем «ОК».
Числа в столбце фильтруются по значениям меньше 100.
Теперь выберите ячейку, в которой вы хотите отобразить значение суммы, и начните вводить функцию ПРОМЕЖУТОЧНЫЙ ИТОГ. Как только вы откроете функцию ПРОМЕЖУТОЧНЫЙ ИТОГ и наберете скобку, вы увидите список функций, которые вы можете использовать в формуле. Щелкните «9 - СУММ» в списке или введите «9» вручную в качестве первого аргумента.
Затем выберите диапазон ячеек, которые вы хотите суммировать, или введите ссылку на диапазон вручную и закройте скобку. Затем нажмите Enter.
Теперь вы получите сумму (промежуточный итог) только видимых ячеек - «207».
Кроме того, вы также можете выбрать диапазон (B2: B11) с числами, которые вы хотите сложить, и нажать «Автосумма» на вкладке «Главная» или «Формулы».
Он автоматически добавит функцию ПРОМЕЖУТОЧНЫЙ ИТОГ в конец таблицы и суммирует результат.
Преобразуйте ваши данные в таблицу Excel, чтобы получить сумму столбца
Еще один простой способ суммирования столбца - преобразование данных электронной таблицы в таблицу Excel. Преобразуя данные в таблицу, вы можете не только суммировать столбец, но и выполнять множество других функций или операций со списком.
Если ваши данные еще не представлены в формате таблицы, вам необходимо преобразовать их в таблицу Excel. Вот как преобразовать данные в таблицу Excel:
Сначала выберите любую ячейку в наборе данных, которую вы хотите преобразовать в таблицу Excel. Затем перейдите на вкладку «Вставка» и щелкните значок «Таблица».
Или вы можете нажать сочетание клавиш Ctrl + T, чтобы преобразовать диапазон ячеек в таблицу Excel.
В диалоговом окне «Создание таблицы» подтвердите диапазон и нажмите «ОК». Если в вашей таблице есть заголовки, оставьте установленным флажок «Моя таблица имеет заголовки».
Это преобразует ваш набор данных в таблицу Excel.
Когда таблица будет готова, выберите любую ячейку в таблице. Затем перейдите на вкладку «Дизайн», которая появляется только при выборе ячейки в таблице, и установите флажок «Строка итогов» в группе «Параметры стиля таблицы».
После того, как вы отметите опцию «Строка итогов», в конце таблицы сразу же появится новая строка со значениями в конце каждого столбца (как показано ниже).
И когда вы щелкаете ячейку в этой новой строке, вы увидите раскрывающийся список рядом с этой ячейкой, из которого вы можете применить функцию для получения суммы. Выберите ячейку в последней строке (новой строке) столбца, который вы хотите суммировать, щелкните раскрывающееся меню рядом с ней и убедитесь, что в списке выбрана функция «СУММ».
Вы также можете изменить функцию на Среднее, Счетчик, Мин, Макс и другие, чтобы увидеть соответствующие значения в новой строке.
Суммируйте столбец на основе критерия
Все предыдущие методы показали вам, как вычислить сумму для всего столбца. Но что, если вы хотите суммировать только определенные ячейки, которые соответствуют критериям, а не все ячейки. Затем вы должны использовать функцию СУММЕСЛИ вместо функции СУММ.
Функция СУММЕСЛИ ищет определенное условие в диапазоне ячеек (столбец), а затем суммирует значения, которые соответствуют данному условию (или значения, соответствующие ячейкам, удовлетворяющим условию). Вы можете суммировать значения на основе числового условия, текстового условия, условия даты, подстановочных знаков, а также на основе пустых и непустых ячеек.
Синтаксис функции СУММЕСЛИ:
= СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])Аргументы / Параметры:
диапазон– Диапазон ячеек, в котором мы ищем ячейки, соответствующие критериям.критерии- Критерии, определяющие, какие ячейки необходимо суммировать. Критерием может быть число, текстовая строка, дата, ссылка на ячейку, выражение, логический оператор, подстановочный знак, а также другие функции.диапазон_суммы(по желанию) - Это диапазон данных со значениями для суммирования, если соответствующая запись диапазона соответствует условию. Если этот аргумент не указан, вместо этого суммируется «диапазон».
Предположим, у вас есть приведенный ниже набор данных, который содержит данные о продажах каждого представителя из разных регионов, и вы хотите суммировать только суммы продаж из «Южного» региона.
Вы можете легко сделать это с помощью следующей формулы:
= СУММЕСЛИ (B2: B19; «Юг»; C2: C19)Выберите ячейку, в которой вы хотите отобразить результат, и введите эту формулу. Приведенная выше формула СУММЕСЛИ ищет значение «Юг» в столбце B2: B19 и складывает соответствующую сумму продаж в столбце C2: C19. Затем отображает результат в ячейке E7.
Вы также можете ссылаться на ячейку, содержащую текстовое условие, вместо того, чтобы напрямую использовать текст в аргументе критерия:
= СУММЕСЛИ (B2: B19; E6; C2: C19)
Вот и все.