4. Подведение промежуточных итогов

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

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

Порядок действий:

1. щёлкнуть внутри списка.

2. Выбрать из меню Данные команду Итоги.

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

4. Затем в списке Операция выбрать нужную операцию над данными, например Сумма.

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

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

Т.о. получим таблицу, в которой появятся итоги.

Замечание: 1) Группировать можно и по фамилиям, но предварительно нужно отсортировать данные по полю Фамилия (одинаковые фамилии должны стоять рядом)

2) Для удаления промежуточных итогов: Данные/Итоги/Промежуточные итоги и нажать кнопку Убрать все.

5. Сводные таблицы

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

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

Сводная таблица состоит из четырех областей: Страница, Строка, Столбец и Данные.

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

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

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

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

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

Сводные таблицы создаются с помощью Мастера сводных таблиц.

Порядок действий для построения сводной таблицы:

1. щёлкнуть внутри списка,

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

3. задать тип источника данных и тип отчёта, который необходим,

4. указать диапазон списка,

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

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

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

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

Замечания:

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

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

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

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

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

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

7) Для создания сводной диаграммы по существующей сводной таблице используем кнопку Мастер диаграмм на панели Сводные таблицы.

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