Excel сводная таблица итоги по строкам. Отображение ’’первой десятки ’’ данных. Отображение детальных данных

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

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

Эту функцию можно использовать так: =GETPIVOTDATA(«Sum of Amount»;$B$5), в русской версии Excel =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма по полю»;$B$5) или так: =GETPIVOTOATA(«Amount»;$B$5), в русской версии Excel ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма»;$В$5).

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

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

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

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


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

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

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

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

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

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


Выделение данных.

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

Способ выделения частей сводной таблицы Вы можете задать и с помощью команды Select (Выделить) выпадающего меню PivotTable (Сводная таблица) на панели инструментов. Здесь же можно выделить всю таблицу целиком, выбрав команду Entire Table (Таблица целиком) . Чтобы переключиться на обычный режим выделения (т.е. выделять не части, а ячейки таблицы), следует отжать кнопку в этом же меню. Чтобы после обновления и изменения структуры сводной таблицы форматы ее областей сохранялись, кнопка Enable Selection (Разрешить выделение) должна быть нажата.


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

Обновление исходных данных.

Если Вы изменили данные в исходных таблицах, необходимо обновить сводную таблицу, основанную на этих данных. Для этого необходимо кликнуть на кнопке панели инструментов PivotTable (Сводные таблицы) или выбрать команду Refresh Data (Обновить данные) из выпадающего меню PivotTable (Сводная таблица) . Так же обновляются данные и в том случае, если Вы добавили строки (или столбцы в случае консолидации диапазонов) внутри исходных диапазонов.

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

Общие и промежуточные итоги.

Выбрав команду Options (Параметры) из выпадающего меню PivotTable (Сводная таблица) , Вы можете указать нужна ли в сводной таблице общая сумма по строкам и / или столбцам. Если в сводной таблице есть поле страниц, причем некоторые значения этого поля скрыты, здесь же можно уточнить включать ли в итоговые суммы данные, соответствующие скрытым значениям полей страниц. Если флажок Subtotal hidden page items (включать скрытые значения) выключен, при просмотре сводной таблицы, соответствующей всем значениям поля страницы, скрытые значения не попадут в общие итоги.


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


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

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


Может так случиться, что Вам понадобятся итоги не по всем элементам поля, а по каким-то группам данных внутри поля (например, элементами поля являются названия месяцев, а итоги необходимо подвести по кварталам). В этом случае элементы поля можно сгруппировать, выделив их и кликнув на значке Group (Сгруппировать) на панели инструментов. У Вас появится еще одно поле, элементами которого будут заголовки групп. Причем, каждый несгруппированный элемент образует самостоятельную группу, наименование которой совпадает с именем элемента. В приведенном примере имеется одно поле столбца, содержащее четыре элемента: январь , февраль , март и апрель . Три элемента (январь , февраль , март ) были сгруппированы. Элемент апрель в группу не вошел. В результате появилось еще одно поле месяц2 , содержащее два элемента: Группа1 и апрель . Поле месяц по прежнему содержит названия месяцев: январь , февраль , март и апрель . Поле месяц2 является внешним полем, т.е. группирует внутреннее поле месяц . Вы можете задать промежуточные итоги для нового поля, и требуемые итоги будут вычислены для каждой группы данных. Чтобы разгруппировать элементы поля, достаточно выделить требуемую группу и кликнуть на значке Ungroup (Разгруппировать) на панели инструментов. Когда сгруппированных элементов не останется, дополнительное внешнее поле исчезнет.


Вычисляемые поля и элементы полей.


Обычно структура таблицы создается из заголовков полей исходного списка в случае одного диапазона данных или из объектов Строка , Столбец , Значение , Страница , если диапазонов несколько. Чтобы проанализировать данные, содержащиеся в исходных таблицах, имеющихся полей часто не хватает. Например, в сводной таблице есть два поля данных: количество и сумма , а Вам необходимо определить среднюю цену товара, которая определяется как частное этих двух полей. В этом случае необходимо создать свое собственное вычисляемое поле - поле сводной таблицы, использующее создаваемую пользователем формулу и позволяющее выполнить вычисления на основе значений других полей сводной таблицы. Для этого достаточно последовательно выбрать команды Formulas (Формулы) , Calculated Field (Вычисляемое поле) из выпадающего меню PivotTable (Сводная таблица) . Оперируя полями, которые можно выбрать из списка, Вы можете создать свою собственную формулу для вычисляемого поля. С помощью мастера сводных таблиц новое поле можно поместить в нужную область сводной таблицы.




Иногда для отчетов требуются не только дополнительные поля, но и элементы полей. Это происходит в тех случаях, когда необходимо поле, которое каким-то образом анализирует данные имеющихся элементов полей. Например, поле строки или поле столбца содержит элементы количество и сумма , а Вам необходимо определить среднюю цену товара, которая определяется как частное этих двух элементов. В таких случаях следует создавать не вычисляемое поле, а вычисляемый элемент - элемент поля сводной таблицы, использующий создаваемую пользователем формулу и позволяющий выполнить вычисления на основе значений других элементов полей сводной таблицы. Чтобы создать вычисляемый элемент, необходимо выделить поле или элемент поля, к которому будет добавлен новый объект и последовательно выбрать команды Formulas (Формулы) , Calculated Item (Вычисляемый объект) из выпадающего меню PivotTable (Сводная таблица) . После этого следует указать имя нового элемента и задать формулу, по которой он должен вычисляться. В формулах, по которым вычисляются дополнительные элементы, используются имена существующих элементов полей сводной таблицы. Если несколько полей имеют элементы с одинаковыми именами, необходимо перед именем элемента вставить имя поля. Новый элемент будет добавлен в поле, которое Вы выделили (или элемент которого выделили) перед тем как создать вычисляемый объект.

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

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

Рис.1. Диалоговое окно Надстройки.

      Составление итоговых отчетов

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

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

Таблица 1

Товар

Наименование

Цена

Кол - во

Сумма

Матричный

Матричный

Матричный

Струйный

Epson Stylus 1520

Струйный

Epson Stylus 3000

Струйный

Epson Stylus 1500

Струйный

Epson Stylus Photo 700

Лазерный

HP Laser Jet 4000

Лазерный

HP Laser Jet 5000

Лазерный

HP Laser Jet Color 8500

Листовой

Paragon Page 630

Листовой

Paragon Page Easy

Планшетный

Paragon 800IIEP

Планшетный

ScanExpress 6000 SP

Планшетный

Paragon 1200 SP

Планшетный

ScanExpress A3 P

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

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

    Выделите таблицу или хотя бы одну ячейку таблицы;

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

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

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


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

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

Функция

Назначение

Складывает все значения и выдает общий итог

Кол-во значений

Определяет количество элементов группы

Определяет среднее арифметическое значение группы

Максимум

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

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

Произведение

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

Кол-во чисел

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

Несмещенное отклонение

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

Смещенное отклонение

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

Несмещенная дисперсия

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

Смещенная дисперсия

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

При вычислении итогов таблица была структурирована - в этом Вы сможете убедиться, взглянув на экран. С помощью уровней структуры можно обеспечить лучший визуальный контроль данных. Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке для второго уровня структуры, вследствие чего данные третьего уровня (отдельные значения) будут скрыты (Рис.4, 5). Чтобы снова вывести на экран отдельные значения, необходимо выполнить щелчок на кнопке для третьего уровня

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


Рис.5. Вариант отображения итоговых данных.

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

Самостоятельно вычислите :

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

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

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


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

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

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

      Составление консолидированных отчетов

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

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

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

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

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

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


Рис.7. Диалоговое окно Консолидация

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

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

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

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


Рис.8. Консолидированный отчет

При изменении значений в исходных диапазонах ячеек консолидацию следует повторить. Если структура исходных таблиц (диапазонов ячеек) не меняется, то постоянного повторения этой процедуры можно из6ежать путем связывания консолидированных данных с исходными. В этом случае в консолидированном диапазоне ячеек будут содержаться ячейки с внешними ссылками на исходные данные. При изменении данных в исходном диапазоне ячеек автоматически будут изменяться и консолидированные данные. Для установки связей между консолидированными и исходными данными следует при задании консолидации установить опциюСоздавать связи с исходными данными в диалоговом окнеКонсолидация (Рис.7).

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

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

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

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

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


Рис.9. Данные для сводной таблицы.

        Создание сводной таблицы.

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


Рис. 10 Мастер сводных таблиц.

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

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

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


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

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


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

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


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


Рис.15 Сводная таблица.

        Настройка параметров сводной таблицы.

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


Рис.16. Окно параметров сводной таблицы.

В этом диалоговом окне в поле Имя задается название сводной таблицы. Установленные флажки Общая сумма по столбцам и Общая сумма по строкам о значают, что в сводной таблице будут напечатаны Общий итог и Итоги по полям . Установленный флажок Автоформат означает, что к сводной таблице будет применен используемый по умолчанию Автоформат . Установленный флажок Включать скрытые значения означает, что при итоговых подсчетах будут учитываться значения скрытых ячеек. Для объединения ячеек с подписями всех внешних строк и столбцов нужно установить флажок Объединять ячейки заголовков . Чтобы защитить форматы ячеек сводной таблицы от изменений при ее обновлении или изменении макета нужно установить флажок Сохранять форматирование . В полях Для ошибок отображать , Для пустых ячеек отображать введите значения, которые будут отображаться в ячейках в случае ошибки или отсутствия данных. Эти поля будут доступными только при наличии соответствующих флажков. Установите флажок Сохранять данные вместе с таблицей для копирования исходных данных, на основе которых была создана данная таблица, чтобы всегда иметь к ним доступ. Установите флажок Развертывание разрешено , чтобы иметь возможность отображать подробные данные двойным щелчком на области данных сводной таблицы. Установите флажок Обновить при открытии , чтобы сводная таблица обновлялась при открытии книги Excel, в которой она содержится. В случае использования внешних источников при создании сводной таблицы установите необходимые параметры в группе Внешние данные .

    Закройте окно щелчком на кнопке ОК .

        Фильтрация данных.

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

        Отображение детальных данных.

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


Рис.18. Окно для отображения детальных данных.

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


Рис.19. Детальные данные сводной таблицы.

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

        Сортировка данных.

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

    Отсортируйте данные по полю Общий итог . Для этого выделите данные для сортировки, выберите из меню Данные элемент Сортировка . На экране появится диалоговое окно Сортировка . Установите диапазон в группе Сортировать по , а в группе Сортировать установите переключатель значения . Щелкните кнопку ОК (Рис.20).


Рис.20 Окно сортировки данных.

    Чтобы при обновлении (изменении) таблицы поля данных автоматически сортировались по названиям, дважды щелкните на кнопке сортируемого поля и в появившемся окне щелкните на кнопке Далее . На экране появится окно (Рис.21). В группе Параметры сортировки установите переключатель по возрастанию или по убыванию, в списке с помощью поля выберите поле, элементы которого будут сортироваться. Закройте окно.



Рис.21. Окно дополнительных параметров.

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

        Отображение ’’первой десятки ’’ данных.

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

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

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

    В группе Параметры отображения установите переключатель автоматические (Рис.21). В поле отображать укажите количество отображаемых элементов, а в списке отображать выберите нужный вариант сортировки: наибольших или наименьших . В списке с помощью поля выберите поле данных, элементы которого будут отображаться. Щелкните на кнопке ОК . Закройте окно.

        Изменение функции вычисления общих итогов.

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

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

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


Рис.22. Применение функций вычисления общих итогов.

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

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

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

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

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


Рис.23. Выполнение дополнительных вычислений.



Рекомендуем почитать