Функция; Промежуточные итоги; в Microsoft Excel

Функция «Промежуточные итоги» в Microsoft Excel

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

Внедрение функции «Промежуточные итоги» в Excel

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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли посодействовать Для вас в решении препядствия.

Кроме данной статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

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

Изюминка функции состоит в том, что она создана для использования вместе с иными средствами EXCEL: Автофильтром и Промежными итогами . См. Файл примера .

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

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

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

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

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

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

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

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

Пусть имеется начальная таблица.

Применим Автофильтр и отберем лишь строчки с продуктом Товар1 . Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает сумму продуктов Товар1 , как следует будем употреблять код функции 9 либо 109.

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

Если б мы записали формулу = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) либо = ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 103;B11:B20), то мы бы подсчитали число отобранных фильтром значений (5).

Таковым образом, эта функция «ощущает» укрыта ли строчка автофильтром либо нет. Это свойство употребляется в статье Автоматическая перенумерация строк при применении фильтра .

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

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

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

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

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

Скроем строчки с Товар2 , нажав на подобающую клавишу «минус» в структуре.

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

Остальные функции

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

Воспользуемся той же начальной таблицей.

Применим Автофильтр и отберем лишь строчки с продуктом Товар1 . Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает среднее геометрическое цен продуктов Товар1 (пример не весьма актуальный, но он указывает принцип). Будем употреблять код функции 3 — подсчет значений.

Для подсчета будем употреблять формулу массива (см. файл примера , лист2)

При помощи выражения СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1 в качестве второго аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подается не один спектр, а несколько ( равного числу строк ). Если строчка укрыта, то заместо цены выводится значение Пустой текст «» , которое игнорируется функцией СРГЕОМ() . Таковым образом, подсчитывается среднее геометрическое цен продукта Товар1 .

Функция в Excel: промежуточные итоги

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

Требования к таблицам для использования промежных итогов

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

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

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

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

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

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

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

Написание формулы промежных итогов вручную

Еще есть один метод посчитать промежуточные итоги – при помощи специальной функции.

1. Для начала отмечаем ячейку, где должен быть выведен результат подсчета. Дальше жмем на значок «Вставить функцию» (fx) рядом со строчкой формул с левой стороны от нее.
2. Раскроется Мастер функций. Избираем категорию “Полный алфавитный список”, находим из предложенного списка функцию “ПРОМЕЖУТОЧНЫЕ.ИТОГИ”, ставим на нее курсор и жмем OK.
3. Сейчас необходимо задать опции функции. В поле «Номер_функции» указываем цифру, которой соответствует подходящему варианту обработки инфы. Всего опций одиннадцать:
   • цифра 1 – расчет среднего арифметического значения
   • цифра 2 – подсчет количества ячеек
   • цифра 3 – подсчет количества заполненных ячеек
   • цифра 4 – определение наибольшего значения в избранном массиве данных
   • цифра 5 – определение малого значения в избранном массиве данных
   • цифра 6 – перемножение данных в ячейках
   • цифра 7 – выявление обычного отличия по выборке
   • цифра 8 – выявление обычного отличия по генеральной совокупы
   • цифра 9 – расчет суммы (ставим в нашем варианте согласно задачке)
   • цифра 10 – нахождение дисперсии по выборке
   • цифра 11 – нахождение дисперсии по генеральной совокупы
4. В поле «Ссылка 1» указываем координаты спектра, для которого требуется просчитать итоги. Всего можно указать до 255 диапазонов. Опосля введения координат первой ссылки, покажется строчка для прибавления последующей. Прописывать координаты вручную не совершенно комфортно, к тому же, велика возможность ошибиться. Потому просто ставим курсор в поле для ввода инфы и потом левой клавишей мыши отмечаем подходящую область данных. Аналогичным образом можно добавить последующие ссылки, если будет нужно. По окончании подтверждаем опции нажатием клавиши OK.
5. В итоге в ячейке с формулой будет выведен итог подсчета промежных итогов.

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

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер обработки данных;координаты ячеек)

Дальше нажимаем кнопку Enter и получаем хотимый итог в данной ячейке.

Заключение

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