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

Функция "Промежуточные итоги()" используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые сроки.

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами Excel (например, автофильтрами).

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

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

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

Для этого можно воспользоваться функцией Excel =Промежуточные.итоги(). Функция =Промежуточные.итоги() делает расчет по отфильтрованным значениям.

В формуле можно задать расчет:

• Средней – номер 1 или 101 – для выборки
• Счет значений – номер 3 или 103
• Сумма - 9 или 109
• И т.д.

• Если номер функции трехзначный, например, 109, то функция работает как промежуточные итоги, т.е. рассчитывает значения по отфильтрованным параметрам.
• Если номер однозначный или двузначный 2 или 11, то формула промежуточные.итоги() работает, как стандартная формула по выбранной функции.

Перейдем к нашим мероприятиям:

У нас есть выгрузка из базы по следующим столбцам:

• Ситуация
• Действия
• Кол-во клиентов
• Объём_Продаж_До
• Объём_Продаж_План
• Объём_Продаж_Факт
• Затраты_План
• Затраты_Факт

Для оценки ситуации в целом введем стандартную формулу суммирования:

Для расчета промежуточных итогов по выбранным параметрам введем =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;RC:RC), где

• 109 – функция суммы для расчета промежуточных итогов;
• RC:RC – ссылка на диапазон суммирования.

Теперь, отфильтровав столбец «Ситуация» - «Ситуация 4» и «Действие» - Действие 2 и 3, мы получим перерасчет промежуточных итогов по каждому из показателей и можем их сравнить с показателями «Итого»:

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

Формула Excel =промежуточные.итоги() – отличный инструмент для расчета показателей по отфильтрованным параметрам.

Если есть вопросы - обращайтесь!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа :

• Novo Forecast Lite - автоматический расчет прогноза в Excel .
• 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

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

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

• Таблица должна иметь формат обычной области ячеек;
• Шапка таблицы должна состоять из одной строки и размещаться на первой строке листа;
• В таблице не должно быть строк с незаполненными данными.

Создание промежуточных итогов в Excel

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

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



2. Откроется окно, в котором нужно настроить выведение промежуточных итогов. В нашем примере нам надо просмотреть сумму общей выручки по всем товарам за каждый день. Значение даты расположено в одноименной колонке. Поэтому в поле «При каждом изменении в» выбираем столбец «Дата» .
3. В поле «Операция» выбираем значение «Сумма» , так как нам требуется подбить именно сумму за день. Кроме суммы доступны многие другие операции, среди которых можно выделить: количество, максимум, минимум, произведение.
4. Так как значения выручки выводятся в столбец «Сумма выручки, руб.» , то в поле «Добавить итоги по» , выбираем именно его из списка столбцов таблицы.
5. Кроме того, надо установить галочку, если ее нет, около параметра «Заменить текущие итоги» . Это позволит при пересчете таблицы, если вы проделываете с ней процедуру подсчетов промежуточных итогов не в первый раз, не дублировать многократно запись одних и тех же итогов.
6. Если поставить галочку в пункте «Конец страницы между группами» , при печати каждый блок таблицы с промежуточными итогами будет распечатываться на отдельной странице.
7. При добавлении галочки напротив значения «Итоги под данными» промежуточные итоги будут устанавливаться под блоком строк, сумма которых в них подбивается. Если же снять галочку, тогда они будут показываться над строками. Для большинства удобнее размещение под строками, но сам выбор сугубо индивидуален.
8. По завершении жмем на «OK» .



9. В результате промежуточные итоги появились в нашей таблице. Кроме того, все группы строк, объединенные одним промежуточным итогом, можно свернуть, просто кликнув по знаку «-« слева от таблицы напротив конкретной группы.



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



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

Формула «ПРОМЕЖУТОЧНЫЕ.ИТОГИ»

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

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



2. Откроется «Мастер функций» , где среди списка функций ищем пункт «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» . Выделяем его и кликаем «OK» .



3. В новом окне понадобится ввести аргументы функции. В строке «Номер функции» впишите номер одного из одиннадцати вариантов обработки данных, а именно:
   • 1 — среднее арифметическое значение;
   • 2 — количество ячеек;
   • 3 — количество заполненных ячеек;
   • 4 — максимальное значение в выбранном массиве данных;
   • 5 — минимальное значение;
   • 6 — произведение данных в ячейках;
   • 7 — стандартное отклонение по выборке;
   • 8 — стандартное отклонение по генеральной совокупности;
   • 9 — сумма;
   • 10 — дисперсия по выборке;
   • 11 — дисперсия по генеральной совокупности.



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



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



6. Опять появится окно аргументов функции. Если понадобилось добавить еще один или несколько массивов данных, воспользуйтесь тем же алгоритмом, который был описан выше. В обратном случае просто нажмите «OK» .



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



8. Синтаксис самой функции выглядит следующим образом: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;адреса_массивов_ячеек) . В нашей ситуации формула будет выглядеть так: «ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C6)» . Эту функцию, используя этот синтаксис, можно вводить в ячейки и вручную, без вызова «Мастера функций» . Только важно не забывать перед формулой в ячейке ставить знак «=» .

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

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

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

Условия использования функции

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

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

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

Создание промежуточного итога. Использование специального инструмента

Как создать промежуточный итог в "Эксель"? Первый способ будет продемонстрирован при использовании стандартного одноименного инструмента в программе. Итак, приступим.

Шаг 1: Открытие инструмента

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

Шаг 2: Настройка отображения

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

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

• минимум;
• количество;
• максимум;
• произведение.

Остается определить лишь то, куда будет выводиться результат. Для этого в поле "Добавить итог по" вам необходимо установить галочку рядом с названием столбца, в котором будет отображаться результат. Так как мы вычисляем сумму, то выбираем "Сумма выручки, руб.".

Также в этом окне есть еще несколько переменных: "Заменить текущие итоги", "Конец страницы между группами" и "Итоги под данными". Из важных можно отметить лишь первый пункт, а остальные устанавливайте по желанию.

Шаг 3: Финал

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

Создание промежуточного итога. Использование специальной формулы

Помимо выше представленного способа, как можно еще рассчитать в "Эксель" промежуточные итоги? Формула - вот что нам поможет.

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

Шаг 1: Открытие мастера функций

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

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

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

Шаг 2: Ввод аргументов функции

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

В нашем случае необходимо вычислить сумму, поэтому нужно вписать число "9".

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

Снова появится знакомое нам окно, в котором можно указать вторую ссылку (всего их четыре). Если делать этого вам не надо, то нажимайте на кнопку "ОК" и результаты выведутся в таблице.

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

Группировка строк и столбцов в Excel

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

Как скрыть и показать группы

Подведение итогов в Excel

Команда Промежуточный итог позволяет автоматически создавать группы и использовать базовые функции, такие как СУММ, СЧЁТ и СРЗНАЧ, чтобы упростить подведение итогов. Например, команда Промежуточный итог способна вычислить стоимость канцтоваров по группам в большом заказе. Команда создаст иерархию групп, также называемую структурой, чтобы упорядочить информацию на листе.

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

Создание промежуточного итога

В следующем примере мы воспользуемся командой Промежуточный итог , чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

Просмотр групп по уровням

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

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

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

Удаление промежуточных итогов в Excel

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

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

Доброго времени суток друзья!

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

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

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

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

А теперь давайте на созданном примере посмотрим поэтапно, как это осуществить:

Работа со сгруппированными данными

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

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

Убрать промежуточные итоги

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

1. Открыть вкладку на панели управления «Данные» и в блоке «Структура» нажимаете на пиктограмму «Промежуточный итог» .
2. В появившемся диалоговом окне «Промежуточные итоги» нажимаем кнопку «Убрать всё» .
3. После нажатия все ваши данные разгруппируются, а промежуточные итоги будут удалены.

Если же вам всё-таки промежуточные итогинужно оставить, а вот группирование не нужно, вы можете в панели задач на вкладке на вкладке «Данные» в кнопки «Разгруппировать» выбрать пункт «Удалить структуру» , структура группировки будет удалена, оставив вам на прощание промежуточные итоги.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

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