Итоги в excel: Данные итогов в таблице Excel

Данные итогов в таблице Excel

Таблицы

Таблицы

Таблицы

Данные итогов в таблице Excel

• Создание и форматирование таблиц
  Статья
• Сортировка данных в таблице
  Статья
• Фильтрация данных в диапазоне или таблице
  Статья
• Данные итогов в таблице Excel
  Статья
• Использование срезов для фильтрации данных
  Статья

Далее: Диаграммы

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. ..Меньше

Вы можете быстро подвести итоги в таблице Excel, включив строку итогов и выбрав одну из функций в раскрывающемся списке для каждого столбца. По умолчанию в строке итогов применяется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая позволяет включать или пропускать скрытые строки таблицы. Но вы также можете использовать другие функции.

1. Щелкните любое место таблицы.

2. Выберите Работа с таблицами > Конструктор и установите флажок Строка итогов.

3. Строка итогов будет вставлена в нижней части таблицы.

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

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

   Excel создает следующую формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Midwest]). Это функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ для функции СУММ, которая является формулой со структурированными ссылками (такие формулы доступны только в таблицах Excel). См. статью Использование структурированных ссылок в таблицах Excel.

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

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

Вы можете быстро подвести итоги в таблице Excel, включив строку итогов и выбрав одну из функций в раскрывающемся списке для каждого столбца. По умолчанию в строке итогов применяется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая позволяет включать или пропускать скрытые строки таблицы. Но вы также можете использовать другие функции.

1. Щелкните любое место таблицы.

2. org/ListItem»>

   Выберите Таблица > Строка итогов.

3. Строка итогов будет вставлена в нижней части таблицы.

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

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

   Excel создает следующую формулу: =ПРОМЕЖУТОЧНЫЕ.

   ИТОГИ(109;[Midwest]). Это функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ для функции СУММ, которая является формулой со структурированными ссылками (такие формулы доступны только в таблицах Excel). См. статью Использование структурированных ссылок в таблицах Excel.

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

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

Вы можете быстро подвести итоги в таблице Excel, включив параметр

Переключить строку итогов.

1. org/ListItem»>

   Щелкните любое место таблицы.

2. Щелкните вкладку Конструктор таблиц > Параметры стилей > Строка итогов.

   Строка Итог будет вставлена в нижней части таблицы.

Настройка агрегатной функции для ячейки строки итогов

Примечание: Это одна из нескольких бета-функций, и в настоящее время она доступна только для части инсайдеров Office. Мы будем оптимизировать такие функции в течение следующих нескольких месяцев. Когда они будут готовы, мы сделаем их доступными для всех участников программы предварительной оценки Office и подписчиков Microsoft 365.

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

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

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

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

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

См. также

Общие сведения о таблицах Excel

Видео: создание таблицы Excel

Создание и удаление таблицы Excel

Форматирование таблицы Excel

Изменение размера таблицы путем добавления или удаления строк и столбцов

Фильтрация данных в диапазоне или таблице

Преобразование таблицы в диапазон

Использование структурированных ссылок в таблицах Excel

Поля промежуточных и общих итогов в отчете сводной таблицы

Поля промежуточных и общих итогов в сводной таблице

Проблемы совместимости таблиц Excel

Экспорт таблицы Excel в SharePoint

Функция ПРОМЕЖУТОЧНЫЕ.

ИТОГИ — Служба поддержки Майкрософт

Excel

Формулы и функции

Другие функции

Другие функции

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.

Описание

Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ. ИТОГИ.

Синтаксис

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;[ссылка2];…])

Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.

• Номер_функции     — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.

Function_num (включая скрытые строки)	Function_num (игнорирует скрытые строки)	Функция
1	101	СРЗНАЧ
2	102	СЧЁТ
3	103	СЧЁТЗ
4	104	МАКС
5	105	МИН
6	106	ПРОИЗВЕД
7	107	СТАНДОТКЛОН
8	108	СТАНДОТКЛОНП
9	109	СУММ
10	110	ДИСП
11	111	ДИСПР

• org/ListItem»>

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

• Ссылка2;…     Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.

Примечания

• Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;…» (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

• Для констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.

• Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».

• Функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

• Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.

Пример

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

Данные
120
10
150
23
Формула	Описание	Результат
=ПРОМЕЖУТОЧНЫЕ. ИТОГИ(9;A2:A5)	Значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 9 в качестве первого аргумента.	303
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A2:A5)	Среднее значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 1 в качестве первого аргумента.	75,75
Примечания
В качестве первого аргумента функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ необходимо использовать числовое значение (1–11, 101–111). Этот числовой аргумент используется для промежуточного итога значений (диапазонов ячеек, именованных диапазонов), указанных в качестве следующих аргументов.

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

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

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

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

10 лучших способов быстрого суммирования данных в Excel

2007 и даже Excel 2003. Кроме того, они работают с Excel для Windows , а также с Microsoft Excel, работающим на Mac.

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

1. Подготовка данных Для суммирования
2. Краткое описание автоматических функций
3. Быстрый анализ с сортировкой и фильтрацией
4. Суммировать данные с функцией SubTotal
5. Суммировать данные с помощью таблицы Excel
6. Использование слайсеров для суммирования по разным измерениям
7. Суммировать с помощью сводных таблиц Excel
8. Суммировать данные с помощью функций Excel
9. Расширенные функции Excel для суммирования данных
10. Суммировать с описательной статистикой из пакета инструментов анализа

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

Самые простые способы суммирования перечислены в начале.

И техника сводной таблицы — одна из моих любимых для быстрого и грязного суммирования данных в Microsoft Excel. Это всегда начинает давать мне многочисленные идеи о данных.

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

Давайте начнем с изучения различных методов обобщения данных.

1. Подготовьте данные к суммированию

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

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

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

Убедитесь, что заголовки столбцов правильные.

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

Удалите все дубликаты.

Повторяющиеся строки часто могут проникать из источников сбора данных. Таким образом, собираете ли вы данные из Интернета, SalesForce, SAP или загружаете их из текстовых или CSV-файлов, первое, что нужно сделать, это удалить дубликаты. Чтобы удалить дубликаты, щелкните в пределах диапазона данных и перейдите в меню «Данные».

Данные > Удалить дубликаты.

Вот и все, теперь ваши данные будут чище.

Избавьтесь от пустых строк:

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

Не оставляйте пустые ячейки пустыми, особенно если в них нет значения.

Лучше иметь 0, чем пустое значение в любой ячейке. Для текстового столбца, если значение неизвестно, лучше отображать NA (недоступно).

Очистка данных — это первый шаг в любом анализе данных. Теперь можно приступать к применению различных методов суммирования данных.

 

2. Быстрая сводка с помощью автоматических функций

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

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

Эти 5 номеров предоставят краткую сводку ваших данных. Вот как это сделать.

Вот как создать сводную секцию поверх ваших данных0005 Главная вкладка Microsoft Office, чтобы активировать функцию суммы Excel. Затем выберите диапазон данных столбца, который вы хотите суммировать. Вот пример:

Вычислить COUNT: Щелкните значок раскрывающегося списка на кнопке Автосумма на вкладке Главная в Microsoft Excel . Выберите Count из списка. Затем выберите диапазон данных столбца, который вы хотите подсчитать. Вы можете использовать функцию подсчета только для числовых столбцов, таких как «Зарплата», «Продажи», «Количество» и т. д., используя эту функцию. Так что не пробуйте это в текстовом столбце, таком как Country или Department.

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

Вычислить максимальные значения: Щелкните значок раскрывающегося списка на кнопке Autosum на вкладке «Главная» в Microsoft Excel. Выберите Максимум из списка. Затем выберите диапазон данных столбца, который вы хотите выбрать для получения максимального значения. С помощью этой функции вы можете выбрать только числовые столбцы, такие как количество, прибыль, рентабельность инвестиций и т. д. Вот пример:

Вычислить наименьшие значения: Щелкните значок раскрывающегося списка на кнопке Автосумма на вкладке Главная в Microsoft Excel. Выберите Минимум из списка. Затем выберите диапазон данных столбца, который вы хотите выбрать для получения наименьшего значения. С помощью этой функции вы можете выбрать только числовые столбцы, такие как количество, прибыль, рентабельность инвестиций и т. д.

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

 

3. Быстрый анализ с помощью сортировки и фильтрации

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

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

Главная страница > Сортировка и фильтрация > Сортировка от А до Я или Сортировка от Я до А.

Данные будут отсортированы выбранным вами способом ( по возрастанию или по убыванию ) для столбца, в котором вы находитесь.

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

После того, как вы отфильтровали какой-либо конкретный выбор, вы также можете выделить любой числовой столбец и просмотреть сводку, отображаемую на нижней панели Excel.

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

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

 

4. Суммируйте данные с помощью команды «Промежуточный итог»:

Если данные уже отсортированы, теперь вы готовы изучить функцию «Промежуточный итог» в Excel. Эта скрытая жемчужина томится в Excel с начала 1990-х, и очень немногие люди используют ее.

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

Функция «Промежуточный итог» доступна на вкладке «Данные» в Excel.

Выберите Данные > Промежуточный итог.

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

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

Использование промежуточных итогов для суммирования данных в Excel

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

Результаты выполнения промежуточных итогов в Excel

Вы можете очистить промежуточные итоги и снова выполнить промежуточные итоги в любое время. Чтобы очистить промежуточные итоги, выберите Данные > Промежуточный итог > Удалить все. Полностью удалены промежуточные итоги и строка общего итога.

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

В Microsoft Excel 2007 появилась новая функция под названием «Таблицы», которая прекрасно справляется с простым суммированием данных из таблицы.

Для начала оставайтесь в пределах диапазона данных на листе Excel. Затем нажмите

Главная > Форматировать как таблицу.

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

Теперь в конец меню Excel добавлена ​​новая вкладка. Это называется Дизайн стола .

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

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

Результаты итоговой строки в таблице Excel

Начните использовать функции таблицы, если вы еще этого не сделали.

 

6. Использование срезов для суммирования по различным измерениям

Начиная с Excel 2010, Microsoft добавила функциональные возможности срезов, которые поднимают таблицы на большую высоту.

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

Перейдите в раздел «Дизайн таблицы» > «Вставить слайсер» > выберите столбец по вашему выбору, по которому вы хотите отфильтровать.

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

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

 

7. Подведение итогов с помощью сводных таблиц Excel

Сводные таблицы используются в Excel уже более 30 лет. Это наиболее часто используемая функция Excel, поскольку она позволяет бизнес-аналитикам и менеджерам манипулировать и анализировать данные множеством лучших способов.

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

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

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

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

Чтобы визуализировать как сумму, так и количество точек данных, вы можете перетащить еще одну копию Sales в раздел Values ​​Area. Затем щелкните правой кнопкой мыши значение и выберите 9.0003

Суммировать значения по > счетчику.

Аналогично можно изменить тип расчета, и подобрать Среднее, Макс, Мин. Таким образом, вы можете получить всю важную статистику о ваших данных, обобщенную по каждому отделу.

Кроме того, чтобы увидеть процент продаж, выполненный каждым отделом,

Щелкните правой кнопкой мыши любое числовое значение в сводной таблице и выберите Показать значение как > % от общей суммы.

Это позволит мгновенно рассчитать % вклада каждого отдела. Общий итог покажет 100% продаж.

Хорошей идеей является сортировка значения % общей суммы от наибольшего к наименьшему значению, показывая самые высокие значения вверху.

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

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

Сводка также может суммировать данные из нескольких рабочих книг. Это обязательная функция Microsoft Excel. Вы можете пройти курс Advanced Excel Training в Сингапуре в Intellisoft, где я преподаю этот курс.

• БЕСПЛАТНЫЙ КУРС ПО СВОДНЫМ ТАБЛИЦАМ ДЛЯ АНАЛИЗА ДАННЫХ – Регистрация не требуется. Нажмите и начните смотреть видео бесплатно и улучшите свои навыки работы со сводными таблицами.

8. Суммирование данных с помощью функций Excel

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

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

Чтобы суммировать диапазон данных, используйте =СУММ(диапазон) в строке формул.

Чтобы подсчитать диапазон ячеек с числовыми данными, используйте =COUNT(диапазон) . Это генерирует числовой счет.

Чтобы подсчитать диапазон буквенно-цифровых ячеек данных, используйте =COUNTA(диапазон). COUNTA может использоваться для подсчета как числовых, так и нечисловых данных.

Чтобы найти среднее значение любых данных, используйте =СРЗНАЧ(диапазон).

 

9. Расширенные функции Excel для суммирования данных

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

Вы можете использовать следующие формулы Excel

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

=СУММЕСЛИ(диапазон данных по стране, выберите_страну, диапазон данных о месячных продажах)

Аналогичным образом, чтобы подсчитать количество товаров, проданных в стране, мы можем подсчитать с помощью функции СЧЁТЕСЛИ в Excel

= СЧЁТЕСЛИ(диапазон данных по стране, выберите_страну, диапазон данных о месячных продажах)

Средние продажи по стране можно проанализировать с помощью следующей формулы СРЗНАЧЕСЛИ.

=СРЗНАЧЕСЛИ(диапазон данных по стране, select_country, диапазон данных о месячных продажах)

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

 

10. Обобщение с описательной статистикой из пакета инструментов анализа

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

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

Файл > Параметры > Надстройки.

Затем выберите пакет инструментов анализа данных, если он неактивен. Возможно, вам придется нажать кнопку «Перейти» внизу. Выберите пакет инструментов и нажмите «ОК». Это добавит кнопку «Анализ данных» на вкладке «Данные» в Excel в конце. Проверьте это. Когда эта кнопка включена, она остается активной, и вы можете использовать ее впоследствии в любое время.

Чтобы использовать этот пакет инструментов анализа данных, перейдите к

Анализ данных > Описательная статистика.

Выберите весь числовой столбец, который вы хотите проанализировать в диапазоне ввода. Установите флажок «Ярлыки в первой строке», если ваши данные имеют заголовок.

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

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

Описательная статистика Результат пакета инструментов анализа

Несколько способов суммирования данных в Excel — заключение

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

Попробуйте их.

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

Ура,
Винай Пракаш

О Винай Пракаш: Винай — успешный спикер, автор, предприниматель и тренер по темам аналитики данных, управления проектами, передовых методов Excel, SQL, Python, визуализации данных с помощью Power BI и Создание информационной панели Excel и несколько других программных навыков. Он является одним из лучших тренеров по обучению аналитике данных, и его советы пользуются большим спросом. Свяжитесь с Vinai для вашего следующего тренинга по анализу данных.

• Присоединяйтесь к анализу данных Vinai с помощью Excel MasterClass и узнайте, как анализировать данные из первых рук.

Использование функций суммирования для суммирования данных в Excel

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

Для тех, кто только начинает работать с Excel, одной из первых групп функций, которые вам следует изучить, являются функции суммирования. К ним относятся СУММА, СРЕДНЕЕ, МАКС, МИН, РЕЖИМ, МЕДИАНА, СЧЕТЧИК, СТАНДОТКЛОН, БОЛЬШОЙ, МАЛЕНЬКИЙ и АГРЕГАТ. Эти функции лучше всего использовать с числовыми данными.

Содержание

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

Понимание формул и функций

Например, если вам нужно сложить 2 и 2 вместе, функция будет иметь вид СУММ , а аргументы – числа 2 и 2 . Обычно мы пишем это как 2 + 2, но в Excel вы бы написали это как =СУММ(2+2) . Здесь вы можете увидеть результаты этого простого сложения двух буквенных чисел.

Хотя в этой формуле нет ничего плохого, в ней нет необходимости. Вы можете просто ввести =2+2 в Excel, и это тоже сработает. В Excel при использовании такой функции, как SUM , имеет смысл использовать аргументы. С функцией SUM Excel ожидает по крайней мере два аргумента, которые будут ссылками на ячейки в электронной таблице.

Как сделать ссылку на ячейку в формуле Excel? Что ж, это довольно легко. Каждая строка имеет номер, а каждый столбец — букву. A1 — первая ячейка электронной таблицы слева вверху. B1 будет ячейкой справа от A1. A2 — ячейка непосредственно под A1. Достаточно легко, верно?

Прежде чем мы напишем нашу новую формулу, давайте добавим некоторые данные в столбцы A и B для работы. Продолжайте и введите случайные числа от A1 до A10 и от B1 до B10 для нашего набора данных. Теперь перейдите к D1 и введите =СУММ(A1,B1) . Вы должны увидеть, что результат представляет собой просто значение A1 + B1.

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

Во-вторых, либо вы можете ввести ссылку на ячейку вручную (A1), либо щелкнуть ячейку A1 после того, как вы ввели открывающую скобку. Excel также выделит ячейку тем же цветом, что и ссылка на ячейку, чтобы вы могли точно видеть соответствующие значения. Итак, мы суммировали одну строку вместе, но как мы можем суммировать все остальные строки без повторного ввода формулы или копирования и вставки? К счастью, Excel упрощает эту задачу.

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

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

Excel достаточно умен, чтобы знать, что формула должна измениться и отразить значения в других строках, а не просто показывать вам одни и те же A1 + B1 до конца. Вместо этого вы увидите A2+B2, A3+B3 и так далее.

Существует также другой способ использования SUM , который объясняет другую концепцию аргументов в Excel. Допустим, мы хотели просуммировать все значения от A1 до A12, тогда как мы это сделаем? Мы могли бы ввести что-то вроде =СУММ(A1, A2, A3 и т.д.) , но это занимает очень много времени. Лучше использовать диапазон Excel.

Чтобы суммировать A1 и A12, все, что нам нужно сделать, это ввести =СУММ(A1:A12) с двоеточием, разделяющим две ссылки на ячейки вместо запятой. Вы даже можете набрать что-то вроде =СУММ(A1:B12) , и он просуммирует все значения от A1 до A12 и от B1 до B12.

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

Функции суммирования

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

Здесь я ввел функцию COUNT в D2 и функцию COUNTA в E2, используя диапазон A2:A12  в качестве набора данных для обеих функций. Я также изменил значение в A9 на текстовую строку привет чтобы показать разницу. COUNT подсчитывает только ячейки, содержащие числа, тогда как COUNTA подсчитывает ячейки, содержащие текст и числа. Обе функции не считают пустые ячейки. Если вы хотите подсчитать пустые ячейки, используйте функцию СЧИТАТЬ ПУСТОТЫ .

Далее идут функции СРЗНАЧ , МЕДИАНА и РЕЖИМ . Среднее не требует пояснений, медиана — это среднее число в наборе чисел, а мода — это наиболее распространенное число или числа в наборе чисел. В более новых версиях Excel у вас есть MODE.SNGL  и MODE.MULT , потому что может быть более одного числа, которое является наиболее распространенным числом в наборе чисел. Я использовал B2:B12 для диапазона в примере ниже.

Двигаясь дальше, мы можем вычислить MIN, MAX и STDEV для набора чисел B2:B12. Функция СТАНДОТКЛОН рассчитает, насколько сильно значения разбросаны по сравнению со средним значением. В более новых версиях Excel у вас есть СТАНДОТКЛОН.P и СТАНДОТКЛОН.S , которые вычисляются на основе всей совокупности или на основе выборки соответственно.

Наконец, еще две полезные функции: БОЛЬШОЙ и МАЛЕНЬКИЙ . Они принимают два аргумента: диапазон ячеек и k-е наибольшее значение, которое вы хотите вернуть. Поэтому, если вам нужно второе по величине значение в наборе, вы должны использовать 2 в качестве второго аргумента, 3 в качестве третьего по величине числа и т.