Excel промежуточные итоги с условием

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вычисление промежуточных итогов в Excel

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

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

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

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

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

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

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

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

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

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

Формула «Промежуточные итоги» в Excel: примеры

Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .

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

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

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

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

Формула для среднего значения промежуточного итога диапазона (для прихожей «Ретро»): .

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

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

В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».

Таким образом, для отображения промежуточных итогов в списках Excel применяется три способа: команда группы «Структура», встроенная функция и сводная таблица.

[expert_bq id=»1570″]Кроме того, для получения из сводной таблицы совокупности чисел в зависимости от того, насколько они большие, можно воспользоваться функциями НАИБОЛЬШИЙ LARGE и НАИМЕНЬШИЙ SMALL. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Одно из наиболее раздражающих свойств сводных таблиц состоит в том, что общий итог, где суммируются данные, всегда находится внизу таблицы, то есть приходится пролистывать всю таблицу, просто чтобы увидеть цифры. Давайте переместим общий итог наверх, где его проще найти.

Итоги в сводной таблице excel

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

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

labarat / лаборат7 / Лабораторная работа Excel7

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

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

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

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

Для случая, когда сводную страницу создают из одного документа (листа Microsoft Excel), то надо щелкнуть по любой ячейке с данными внутри таблицы, а затем щелкнуть по кнопке Сводная таблица на вкладке Вставка.

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

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

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

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

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

Столбец — поля в этой области формируют заголовки столбцов сводной таблицы; если таких полей несколько, то они в таблице размещаются последовательно, обеспечивая группирование данных сводной таблице в порядке размещения полей;

Пример заполненной сводной таблицы может иметь следующий вид:

На вкладке Вставка щелкните по кнопке Сводная таблица.

Перетащите в область строк поле Отделение банка, в область столбцов — поле Вклад, а в область данных поле Сумма вкладов.

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

Поменяйте порядок следования отделений банка. Расположите отделения в следующем порядке:

Измените поле, по которому происходит суммирование данных. Для этого надо:

поставить указатель мыши на элемент Сумма по полю Сумма вклада

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

перетащить в область данных поле Проценты.

Можно изменить операции, выполняемые над полем в области данных. Для этого надо:

Щелкнуть по кнопке Параметры поля на ленте в разделе Активное поле

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

Измените операцию в поле данных на Максимум.

Детальные данные сводной таблицы и выполняемые над ними действия

Детальные данные являются подкатегорией сводной таблицы. Эти элементы сводной таблицы являются уникальными элементами таблицы или списка. Например, поле «Отделение банка» может содержать названия вкладов: «Юбилейный», «Обычный» и т.д.

Отображение или скрытие детальных данных сводной таблицы

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

Далее необходимо нажать на кнопку Активное поле на вкладке Параметры и выбрать «Развернуть все поле» Для отображения деталей или «Свернуть все поле» для скрытия деталей. При появлении диалогового окна, выберите поле, для которого необходимо показать детали.

Группировка и разгруппировка данных в сводной таблице

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

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

Группировка чисел в сводной таблице

поместите в область строк или столбцов поле, по которому надо выполнять группировку

выделите число в поле, в котором следует сгруппировать элементы

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

Выполните группировку по полю Сумма вклада от 0 до 1000000 с шагом 100000 и подсчитайте количество вкладов в каждой группе. Для этого в область строк надо перетащить поле Сумма вклада, выполнить для него группировку, а в область данных можно перетащить Отделения банков.

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

Для создание вычисляемого поля в сводной таблице выделите ячейку в сводной таблице.

После этого выберите команду «Формулы» щелкнув по кнопке Сервис, а затем выберите команду «Вычисляемое поле».

В появившемся окне введите имя вычисляемого поля в поле «Имя», а так же формулу в поле «Формула».

Чтобы использовать в формуле данные поля, выберите поле в списке «Поля», а затем нажмите кнопку «Добавить поле». Потом нажмите кнопку «Добавить», а затем — кнопку «OK».

щелкните по кнопке Сервис и выберите команду Формулы.

В строке формул после знака равно укажите формулу Сумма вклада + Процент. тИмена полей не набираются, а вставляются из списка.

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

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

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

Постройте сводную диаграмму по вычисляемому полю Итого.

Откройте таблицу Сотрудники редакции.xlsx.

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

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

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

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

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

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

Откройте таблицу БазаАвтосалон.xls. Выполните следующие задания.

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

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

Создать сводную таблицу, в которой выполнена группировка по цене в интервале от 0 до 40000 с шагом 1000. Подсчитать количество автомобилей в каждой группе.

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

[expert_bq id=»1570″]Найдем суммы, затраченные на покупку отдельно для всех принтеров и отдельно для всех сканеров с помощью функции автоматического вычисления итогов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Но как поступить, если вы хотите сохранить результаты, но сами данные вам не нужны, то есть вы хотите освободить стол? Такая ситуация часто возникает, если руководству нужны только итоги. Алгоритм действий:

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

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

Итоги в сводной таблице excel

Чтобы дополнить Excel средствами для обработки данных (надстройки Диспетчер отчетов и Поиск решения ), в диалоговом окне Надстройки меню Сервис отметьте соответствующий переключатель и нажмите OK (Рис.1).

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

Цель задания: научиться составлять промежуточные итоги.

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

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

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

В меню Данные выберите командуИтоги . Откроется диалоговое окно Промежуточные итоги (Рис. 2.).

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

Рис.3. Таблица – пример применения функции автоматического вычисления итогов.

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

Определяет наибольшее значение в группе и наибольшее значение во всем столбце

Определяет произведение всех значений в группе и произведение значений всего столбца

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

Определяет значение стандартного отклонения для совокупности, если данные образуют выборку

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

Определяет значение дисперсии, если данные образуют выборку

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

Рис.4. Вариант отображения итоговых данных (третий уровень скрыт).

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

Среднее значение цены принтеров и сканеров;

Количество разновидностей наименований принтеров и сканеров;

Суммы, потраченные на покупку каждого типа товара (Рис. 6)

Рис.6. Суммы, потраченные на покупку каждого типа товара.

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

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

Цель задания: научиться составлять консолидированные отчеты.

В качестве примера мы будем использовать предыдущий пример. Разнесите только данные о принтерах и данные о сканерах на два рабочих листа, переименуйте “Лист1 ” в “Принтеры”, а “Лист2 ” в “Сканеры ”.

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

Выберите “Лист3 ” и присвойте ему новое имя “Отчет ”. Создайте “шапку” новой таблицы (Рис.8);

Выделите ячейку A 7 (“Товар ”), и в меню Данные выберите командуКонсолидация . В открывшемся одноименном диалоговом окне укажите консолидируемые диапазоны ячеек и вид операции (функцию) (Рис.7.):

убедитесь, что в поле Функция находится функция Сумма ,

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

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

Нажмите ОК , в полученном консолидированном отчете столбцы “Тип ” и “Наименование ” можно скрыть (Рис.8)

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

Построение сводных таблиц и сводных диаграмм

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

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

Выберите команду Сводная таблица меню Данные или щелкните на кнопке Сводные таблицы панели инструментов, чтобы запустить Мастер сводных таблиц и диаграмм. На экране появится диалоговое окно Мастера сводных таблиц (Рис.10).

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

В группе Вид создаваемого отчета: установите сводная таблица. Щелкните на кнопке Далее – появится окно:

В поле Диапазон : (Рис.11) укажите диапазон, содержащий исходные данные. Это можно сделать двумя способами: ввести адрес или имя диапазона ячеек с клавиатуры или выбрать диапазон ячеек в рабочем листе с помощью мыши.

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

Нажмите на кнопку Макет…. Появится диалоговое окно построения сводной таблицы (Рис.13).

При разметке макета перетащите с помощью мыши кнопки полей в области:

Перетащите кнопку Наименование в область Строка , кнопку Производитель в область Страница , кнопку Квартал в область Столбец , кнопки Оборот и Количество в область Данные (Рис.14).

Нажмите кнопку ОК , а затем Готово . Вы получите сводную таблицу (Рис.15.)

Щелкните правой кнопкой мыши на любой ячейке уже созданной сводной таблицы и в появившемся меню выберите пункт Параметры таблицы. На экране появится окно Параметры сводной таблицы (Рис.16).

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

Щелкните дважды на элементе поля, для которого нужно отобразить детальные данные. На экране появится окно Показать детали (Рис.18) .

Выберите в предложенном списке поле, содержащее детали, которые нужно показать, и щелкните на кнопке ОК . В сводную таблицу будут вставлены соответствующие строки или столбцы.

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

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

В созданной сводной таблице можно отразить заданное количество элементов только с максимальными или минимальными значениями.

Дважды щелкните на поле данных, элементы которого будут выборочно отображены.

В появившемся окне Вычисление поля сводной таблицы щелкните на кнопке Далее . На экране появится окно Дополнительные параметры поля сводной таблицы .

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

Щелкните на кнопке Параметры поля на панели инструментов Сводные таблицы . На экране появится окно Вычисление поля сводной таблицы.

Выберите подходящую функцию в списке Операция (Рис.22) и щелкните на кнопке ОК .

Задание дополнительных вычислений при подведении итогов.

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

Выделите в сводной таблице поле, содержащее автоматически подведенный итог.

Щелкните на кнопке Параметры поля на панели инструментов Сводные таблицы . На экране появится окно Вычисление поля сводной таблицы . Щелкните на кнопке Дополнительно , чтобы расширить окно.

Выберите в списке Дополнительные вычисления нужную функцию. В случае необходимости в списке поле выберите имя поля, а в списке элемент – элемент, используемый в качестве базового значения при выполнении дополнительных вычислений. Щелкните на кнопке ОК (Рис.23.)

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

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

В первой функции используется поле Sum of Amount, а во второй — поле Amount. Если в области Данные (Data) сводной таблицы есть поле Amount, необходимо присвоить этому полю имя Amount. Если, однако, поле Amount используется в области Данные (Data) несколько раз, необходимо указать имя, которое вы присвоили ему, или же имя, принятое по умолчанию (рис. 4.5).

К сожалению, если вы используете мастер функций (Function Wizard) или сначала введете =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() (=GETPIVOTDATA()) и уже после этого щелкнете ячейку, в которой находится общий итог, Excel создаст неразбериху, попытавшись поместить в эту ячейку еще одну функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).

Вероятно, самый простой и менее запутанный способ получить общий итог — воспользоваться функцией =МАХ(PivGTCol), в русской версии Excel =MAKC(PivGTCol), где столбец, в котором содержится общий итог, имеет имя PivGTCol.

Кроме того, для получения из сводной таблицы совокупности чисел в зависимости от того, насколько они большие, можно воспользоваться функциями НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL). Например, следующая формула выделяет второе по величине число из сводной таблицы: =LARGE(PivGTCol;2), в русской версии Excel =НАИБОЛЬШИЙ(PivGTCol;2).

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

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

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

Команда Options (Параметры) позволяет определить имя сводной таблицы, параметры отображения и форматирования, а также настроить параметры внешних данных, если таблица строится на внешнем источнике данных.

Поля, элементы полей и итоги можно удалять из сводной таблицы. Для этого выделите удаляемую часть таблицы (кнопка Enable Selection (Разрешить выделение) должна быть нажата) и выберите команду Delete (Удалить) из контекстного меню.

Если строки или столбцы были добавлены к исходным диапазонам, необходимо вызвать мастера сводных таблиц, кликнув на кнопке PivotTable Wizard (Мастер сводных таблиц) на панели инструментов. Вернувшись ко второму шагу мастера, Вы можете переопределить исходные диапазоны.

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

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

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

[expert_bq id=»1570″]В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен таблица, которую можно менять, крутить, демонстрировать в разных проекциях. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Поля, элементы полей и итоги можно удалять из сводной таблицы. Для этого выделите удаляемую часть таблицы (кнопка Enable Selection (Разрешить выделение) должна быть нажата) и выберите команду Delete (Удалить) из контекстного меню.

Как сделать сводную таблицу в Excel — способы создания

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

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