В эксель промежуточные итоги

Применение функции и обработка ячеек вручную

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

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

8

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

9

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

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

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

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

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

Расчёты при помощи функции

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

Для этого достаточно совершить несколько простых действий.

1. Перейдите на любую клетку. Нажмите на иконку «Fx». В окне «Вставка функции» выберите категорию «Полный алфавитный перечень». Найдите там нужный пункт. Кликните на кнопку «OK».

1. После этого появится окно, в котором вас попросят указать аргументы функции.

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

Обратите внимание на то, что здесь используется формула для подсчета обычной суммы. Функция СУММЕСЛИ в этом списке отсутствует

1. В качестве примера поставим цифру 9.

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

1. Затем выделите какие-нибудь значения. В нашем случае посчитаем сумму продаж первого товара. В конце кликаем на «OK».

1. Результат будет следующим.

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

Настройка вычислений в сводных таблицах

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

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

Другие функции расчета вместо банальной суммы

Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings) , то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

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

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

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

…а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля (Field settings) , чтобы в итоге получить желаемое:

Долевые проценты

Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as) :

В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row) , Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total) , чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:

Динамика продаж

Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference) , а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):

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

А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом — то получим то же самое, но не в рублях, а в процентах:

В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще — щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By) :

. и Дополнительные вычисления (Show Data as) :

Также в версии Excel 2010 к этому набору добавились несколько новых функций:

% от суммы по родительской строке (столбцу) — позволяет посчитать долю относительно промежуточного итога по строке или столбцу:

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

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

Сортировка от минимального к максимальному и наоборот — немного странное название для функции ранжирования (РАНГ), вычисляющей порядковый номер (позицию) элемента в общем списке значений. Например, с ее помощью удобно ранжировать менеджеров по их суммарной выручке, определяя кто на каком месте в общем зачете:

Синтаксис функции

В программе Excel опция отображается в виде ПРОМЕЖУТОЧНЫЕ.ИТОГИ (№; ссылка 1; ссылка 2; ссылка3;…;ссылкаN), где номер – обозначение функции, ссылка – столбец, по которому подводится итог.

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

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

Синтаксис опции итогов указан в таблице:

Синтаксис	Действие
1-СРЗНАЧ	Определение среднего арифметического двух и более значений.
2-СЧЁТ	Расчет количества чисел, которые представлены в списке аргументов.
3-СЧЁТЗ	Подсчёт всех непустых аргументов.
4-МАКС	Показ максимального значения из определяемого набора чисел.
5-МИН	Показ минимального значения из определяемого набора чисел.
6-ПРОИЗВЕД	Перемножение указанных значений и возврат результата.
7-СТАНД ОТКЛОН	Анализ стандартного отклонения каждой выборки.
8-СТАД ОТКЛОН П	Анализ отклонения по общей совокупности данных.
9-СУММ	Возвращает сумму выбранных чисел.
10-ДИСП	Анализ выборочной дисперсии.
11-ДИСПР	Анализ общей дисперсии.

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

Работа со строками в Excel. Текстовые функции Excel

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

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

В этой статье я коротко опишу необходимые функции для работы со строками категории «Текстовые» и некоторые рассмотрим на примерах.

Функции категории «Текстовые»

Итак, рассмотрим основные и полезные функции категории «Текстовые», с остальными можно ознакомиться самостоятельно.

• БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
• ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
• ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
• ЗНАЧЕН (Текст) – преобразует текст в число;
• ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
• ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
• НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
• ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
• ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
• СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;

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

Пример 1Дан набор строк:

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

    Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :

=ПСТР(A2;НАЙТИ(«№»;A2)+1;6)

Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.

Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:

• =ПРАВСИМВ(A2;8)
• но извлеченная дата у нас будет строкой, чтоб преобразовать ее в дату необходимо после извлечения, текст перевести в число:
• =ЗНАЧЕН(ПРАВСИМВ(A2;8))
• а затем, задать формат отображения в ячейке, как это сделать было описано в статье «Формат данных в Excel».

Ну и последнее, для удобства дальнейшей фильтрации строк, введем столбец месяц, который мы получим из даты. Только для создания месяца нам необходимо откинуть день и заменить его на «01». Формула для D2:

=ЗНАЧЕН(СЦЕПИТЬ(«01»;ПРАВСИМВ(A2;6))) или =ЗНАЧЕН(«01″&ПРАВСИМВ(A2;6))

Задайте формат ячеке «ММММ ГГГГ». Результат:

1. Пример 2   В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».
2. Формула будет следующая:
3. =ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)

Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК,  последней будет ПОДСТАВИТЬ.

Прикрепленный файл: text_func_excel.zip

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

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

Синтаксис функции

​ Автоматическая перенумерация строк​​ СУММ() также имеет​

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

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

​ подвести итог. В​	​Выделенные строки или столбцы​	​ выход есть.​
​ при применении фильтра.​	​ код 109, т.е.​	​ SUBTOTAL(), используется для​
​Скрыть строки​	​Номер_функции​	​ фильтром элементам страницы​
​Задание отображения или скрытия​	​ генеральной совокупности, где​	​Выберите вариант​
​Число значений данных. Подведения​	​ и столбцов, отображать​	​ (такие формулы доступны​
​ воспользуйтесь пунктом​	​ иметь возможность регулировать​	​ этом примере мы​
​ будут сгруппированы. В​	​Воспользуемся той же исходной​	​Пусть имеется та же​
​ можно записать формулу​	​ вычисления промежуточного итога​	​(меню​
​(с включением скрытых​	​, чтобы включить или​	​ общих итогов по​
​ выборка является подмножеством​	​Показывать все промежуточные итоги​	​ итогов работает так​
​ или скрывать строки​	​ только в таблицах​	​Удалить структуру​

​ количество отображаемой информации,​ подводим промежуточный итог​ нашем примере это​ таблицей.​ исходная таблица. Скроем​ =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10).​ (сумма, среднее, количество​Формат​ значений) Номер_функции​ исключить отфильтрованные элементы​ умолчанию​

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

​ для каждого размера​ столбцы A, B​Применим Автофильтр и отберем​ строки с товаром​ В чем различие​ значений и т.д.)​, подменю​

​(с исключением скрытых​​ страницы.​   ​Смещенная дисперсия​ группы​ СЧЕТЗ . Число​ итогов всего отчета,​ Использование структурированных ссылок​ команды​ 1, 2, 3​ футболок, поэтому информация​

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр

​ и C.​

​ только строки с​ Товар2  через меню​ — читайте ниже.​ в диапазоне, в​Скрыть/Показать​ значений) Функция​Примечание:​Щелкните отчет сводной таблицы.​

​Смещенная оценка дисперсии генеральной​.​ — функция по​ а также вычислять​ в таблицах Excel.​​Разгруппировать​​ в левой части​ на листе Excel​Чтобы разгруппировать данные в​ товаром Товар1. Пусть​

​ Главная/ Ячейки/ Формат/​ Обычно используют коды​ котором имеются скрытые​) в группе​1​ Источник данных OLAP должен​

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

​Примечание:​ в заголовке группы​Среднее​ элементов при помощи​ функции, щелкнув​

​Вы можете быстро подвести​ между всеми тремя​Размер​ а затем щелкните​

​ товаров Товар1 (пример​ меню.​Ссылка1​​ том, что она​​Лист​2​Помечать итоги *​Сводная таблица​ С источниками данных OLAP​

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

​.​Среднее чисел.​ фильтра или без​Другие функции​ итоги в таблице​ уровнями структуры.​от меньшего к​ команду​ не очень жизненный,​В этом случае имеется​; Ссылка2; — от​ предназначена для использования​. Эти константы используются​102​, чтобы отображать или​нажмите кнопку​ использовать нестандартные функции​Отобразить промежуточные итоги для​

​Max​ него.​

​или создав их​ Excel, включив​Хоть в этом примере​ большему.​Разгруппировать​ но он показывает​ разница между использованием​ 1 до 29​ совместно с другими​ для получения промежуточных​СЧЁТ​

​ скрывать звездочку рядом​Параметры​ невозможно.​

​ заголовка внутренней строки​

​Максимальное число.​Поля строк и столбцов​ самостоятельно.​строку итогов​ представлено всего три​Откройте вкладку​.​ принцип). Будем использовать​ кода функции СУММ(): 9​ ссылок на диапазон,​ средствами EXCEL: Автофильтром​ итогов для скрытых​3​ с итогами. Звездочка​

excel2.ru>

Применение функции промежуточных итогов

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

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

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

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

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

У нас в таблице имеется столбец под названием “Продано, в руб.” Его и укажем для параметра «Добавить итоги по».
также следует обратить внимание на пункт “Заменить текущие итоги”. Если напротив него нет установленной галочки, нужно ее поставить

В противном случае возникнут проблемы при внесении каких-либо изменений и повторном пересчете итогов.
перейдем к надписи “Конец страницы между группами” и разберемся, стоит ли ставить напротив нее галочку. Если этот параметр будет отмечен галочкой, это повлияет на внешний вид документа при отправке на принтер. Все блоки таблицы с подведенными промежуточными итогами распечатаются на отдельных листах каждый.
и, наконец, параметр “Итоги под данными” определяет расположение результата относительно строк. Если убрать отметку напротив этого пункта, то результат будет выводиться над строками. Приемлемы оба варианта, но всё-таки привычнее и визуально понятнее расположение итогов под данными.
закончив с настройками, подтверждаем действие нажатием на OK.

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

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

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

Заключение

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

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

Использование функции «Промежуточные итоги» в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Номер_функции — это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.

Нр. ф-и	Функция	Описание функции
1	СРЗНАЧ	Возвращает среднее (арифметическое) своих аргументов.
2	СЧЁТ	Подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек.
3	СЧЁТЗ	Подсчитывает количество непустых значений в списке аргументов. Функция СЧЁТЗ используется для подсчета количества ячеек с данными в интервале или массиве.
4	МАКС	Возвращает наибольшее значение из набора значений.
5	МИН	Возвращает наименьшее значение в списке аргументов.
6	ПРОИЗВЕД	Перемножает числа, заданные в качестве аргументов и возвращает их произведение.
7	СТАНД ОТКЛОН	Оценивает стандартное отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.
8	СТАНД ОТКЛОНП	Вычисляет стандартное отклонение по генеральной совокупности. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.
9	СУММ	Суммирует все числа в интервале ячеек.
10	ДИСП	Оценивает дисперсию по выборке
11	ДИСПР	Вычисляет дисперсию для генеральной совокупности

Ссылка1; Ссылка2; — от 1 до 29 интервалов или ссылок, для которых подводятся итоги.

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

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

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

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

Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

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

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

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

Такой оперативный анализ очень полезен техническим исполнителя. Представьте, Вы спокойно сидите перед своим персональным компьютером, на своем рабочем месте и мирно занимаетесь своими личными делами. И вдруг, звонок начальника: “а скажи-ка мне Тяпкин-Ляпкин, скока у нас того-то и того-то”? Пока начальник выговаривает, что ему надо, Вы быстренько запускаете файл с базой данных и по фильтру отбираете то, что интересует руководство. Ответ готов, начальник доволен, товарищи по работе в шоке, а Вы опять спокойно занимаетесь своими делами.

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

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

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

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

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

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

В нашем образце рабочего листа щелкните цифру 2, чтобы отобразить первую группировку по регионам :

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

Или же используйте кнопки «Показать детали» или «Скрыть детали» в меню «Данные» в группе «Структура».

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

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

Самый очевидный способ, который приходит на ум – получить желаемые промежуточные итоги, а затем копировать эти строки в другое место – не сработает! 

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

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

1. Отобразите только те строки промежуточных итогов, которые вы хотите скопировать, используя цифры в структуре или символы «плюс» и «минус».
2. Выберите любую ячейку с промежуточным итогом и нажмите , чтобы выделить все ячейки.
3. Выделив промежуточные итоги, перейдите на вкладку «Главная»> «Редактирование» и нажмите «Найти и выделить» > «Выделить группу ячеек…»
4. В появившемся диалоговом окне выберите «Только видимые ячейки» и нажмите «ОК».
5. На текущем листе нажмите  для копирования выбранных ячеек с промежуточными итогами.
6. Откройте другой лист или книгу и нажмите , чтобы вставить промежуточные итоги.

Готово! В результате у вас есть только сводка данных, скопированная на другой рабочий лист. Обратите внимание, что этот метод копирует только значения, а не формулы:

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

ВЫЧИСЛЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ В EXCEL

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

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

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

• Таблица оформлена в виде простого списка или базы данных.
• Первая строка – названия столбцов.
• В столбцах содержатся однотипные значения.
• В таблице нет пустых строк или столбцов.

Приступаем…

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

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

1. 1. Заполняем диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выбираем условие для отбора данных (в примере – «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.

1. Закрываем диалоговое окно, нажав кнопку ОК. Исходная таблица приобретает следующий вид:

Если свернуть строки в подгруппах (нажать на «минусы» слева от номеров строк), то получим таблицу только из промежуточных итогов:

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

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

Чтобы промежуточные данные отображались НАД группой, снимите условие «Итоги под данными».

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

Снова вызываем меню «Промежуточные итоги». Снимаем галочку «Заменить текущие». В поле «Операция» выбираем «Среднее».