Структурирование с подведением итогов
КомандаДанные\ Промежуточные итоги создает структуру таблицы и одновременно вставляет строки промежуточных и общих итогов для выбранных столбцов в соответствии с заданной функцией. Функции для подведения автоматических итогов приведены в табл. 1.
Примечания:
1 Для получения итогов по группам следует заранее упорядочить строки списка с помощью командыДанные\ Сортировка.
2 Подведение итогов выполняется при изменении значений в столбце, который образует группы.
КомандаДанные\ Промежуточные итоги может выполняться для одного и того же списка записей многократно, ранее созданные промежуточные итоги могут заменяться новыми или оставаться неизменными посредством установки или снятия флажка параметра Заменить текущие итоги(рис. 10.5). Таким образом, имеется возможность подведения итогов различных уровней вложенности.
Таблица 1
| Операция | Значение в строке итогов по группе |
| Сумма | Сумма значений |
| Количество значений | Число непустых значений |
| Среднее | Среднее значение в группе |
| Максимум | Наибольшее числовое значение в группе |
| Минимум | Наименьшее числовое значение в группе |
| Произведение | Произведение всех значений в группе |
| Количество чисел | Количество записей или строк, которые содержат числовые данные в группе |
| Смещенное отклонение | Смещенная оценка среднего квадратического отклонения генеральной совокупности по выборке данных |
| Несмещенное отклонение | Несмещенная оценка среднего квадратического отклонения генеральной совокупности по выборке данных |
| Смещенная дисперсия | Смещенная оценка дисперсии генеральной совокупности по выборке данных |
| Несмещенная дисперсия | Несмещенная оценка дисперсии генеральной совокупности по выборке данных |
Столбец, при изменении значений в котором образуются группы, выбирается из списка в поле параметра При каждом изменении в:.
Перечень столбцов, по которым подводятся итоги, указывается в поле Добавить итоги по( рис. 10.5).
Итоги выводятся либо под данными, либо над данными установкой флажка параметра Итоги под данными. Принудительно группы могут размещаться на отдельных печатных страницах (параметр Конец страницы между группами). Кнопка Убрать все удаляет все итоги из текущего списка.
При нажатии кнопки ОК автоматически создаются промежуточные итоги. Слева таблицы на служебном поле появляются символы структуры:
§ линии уровней структуры;
§ кнопка "+" показа групп;
§ кнопка "-" скрытия групп;
§ кнопки уровней структуры 1,2,3 и т.д.
Рисунок 10.5 Окно Промежуточные итоги для установки
параметров автоматического подведения итогов
На рис. 10.6 представлена структура с итогами для таблицы рис. 10.3. В ней:
1-й уровень — средняя оценка по всему списку (КТ-11 Среднее, КТ-32 Среднее, КТ-61 Среднееи Общее среднее);
2-й уровень — средняя оценка по коду преподавателя (1 Среднее и 2 Среднее).
Команда Данные\ Промежуточные итоги\ клавиша Убрать всёубирает структурирование и все предыдущие итоги.
4 Контрольные вопросы
1 Что такое структурированная таблица?
2 Виды структурирования таблиц в MSExcel 2007.
3 Как выполняется ручное структурирование?
4 Как выполняется автоструктурирование?
5 Как выполняется структурирование с подведением итогов?
6 Для чего применяется структурирование таблиц?
7 Как выглядит структура таблицы?
8 Как отменить структуру таблицы?
Рисунок 10.6Пример структуры с промежуточными итогами
Задание
1 Подготовить для структурирования таблицу своего варианта из лабораторной работы№9 (excel-9) "Списки в MSExcel 2007. Сортировка и фильтрация данных".
Для этого добавить:
▪ справа - ещё три столбца: в первые два ввести числовые данные,
▪ в третий – формулу для их обработки по строкам.
Например, в первом и втором - Зарплата и Налог, в третьем – Сумма к выдаче (как разность зарплаты и налога), или Цена и Количество = Общая стоимость (произведение цены на количество), или Доза и Количество приёмов = Общая доза (произведение дозы и количества), или Стоимость бензина и Пробег = Затраты на топливо (произведение стоимости и пробега) и т.п.
▪ снизу – ёщё одну строку, ввести в её ячейки формулы для обработки данных в столбцах (например, сумма, среднее, произведение, максимум и т.п.)
2 Выполнить в подготовленной таблице:
▪ Ручное структурирование
▪ Автоструктурирование с введением нового иерархического уровня по строкам.
▪ Структурирование с подведением итогов.
В результате выполнения работы должно получиться три таблицы – аналогично рис.10.1, 10.4 и 10.6.
Пояснения к выполнению:
1 Для ручного структурирования:
▪ отсортировать таблицу по любому какому-нибудь столбцу, например, по фамилии, должности, маршруту;
▪ создать структурную группу для строк 1-3. Для этого нужно выделить строки 1-3 и выполнить команду Данные\ Группировать (для строк). Аналогично создать структуру для двух других групп (строки 4-6 и 7-10).
▪ создать структурные группы для столбцов. Для этого выделить 2-3 столбца (начиная от ячейки с именем и вниз - до последней строки) и выполнить команду Данные\ Группировать (для столбцов). Аналогично повторить для оставшихся столбцов.
Сохранить результат на своём носителе.
2 Для автоструктурирования таблицы и введения нового уровня ручным способом:
2.1 Создание автоструктуры
▪ установить курсор в любую ячейку области данных, выполнить команду Данные\ Групировать\ Создать структуру;
2.2 Введение нового иерархического уровня ручным способом – по строкам:
▪ выделить строки 1-3 (с данными) и выполнить команду Данные\Группировать;
▪ выделить остальные строки таблицы и выполнить команду Данные\ Группировать;
В результате внутри первого уровня структурирования (1) появится второй уровень (2), созданный вручную.
3 Структурирование с подведением итогов (на примере таблицы на рис. 10.3, 10.6).
▪ отсортировать список записей таблицы рис. 10.3 командой Данные\ Сортировка, в окне Сортировка диапазона указать те столбцы, которые нужны для вычисления итогов. Для таблицы на рис. 10.6 сначала задавалась сортировка для № группы, затем для Код преп, чтобы получить итоги – средний балл по каждой группе, всей группе и по каждому преподавателю.
▪ подобрать вид итогов, которые можно сделать для таблицы своего варианта и отсортировать нужные для этого столбцы.
▪ создать 1-й уровень итогов – средний балл по группе. Для таблицы рис. 10.3 устанавливался курсор в любую ячейку списка и выполнялась команду Данные\ Промежуточные итоги. В диалоговом окне Промежуточные итоги указывать:
При каждом изменении в- № группы
Операция:Среднее
Добавить итоги по:Оценка
Заменять текущие итоги:нет
Конец страницы между группами:нет
Итоги по данными:да.
После нажатия ОК рассчитается средний балл по каждой группе и общее среднее.
▪ создать 2-й уровень итогов – средний балл по каждому преподавателю для каждой группы: установить курсор в любую ячейку списка и выполнить команду Данные\ Промежуточные итоги.В окне Промежуточные итоги указать:
При каждом изменении в– Код преп
Операция:Среднее
Добавить итоги по:Оценка
Заменять текущие итоги:нет
Конец страницы между группами:нет
Итоги по данными:да.
После нажатия ОК рассчитается средний балл по каждому преподавателю в каждой группе.
Для получения промежуточных итогов можно скомпоновать любые поля исходного списка, например, на 1-м уровне – по маршруту (званию, коду предмета…), на 2-м уровне – по № зачётной книжки (зарплате, должности…, на 3-м уровне - по дате, стоимости, телефону…).
Содержание отчёта
1 Название работы
2 Цель работы
3 Содержание работы (порядок выполнения)
4 Структуры задания (на флэшке) – три таблицы.
5 Письменные ответы на контрольные вопросы
6 Выводы по работе.
Практическое занятие №11
Сводные таблицы
Цель работы: научиться создавать и применять сводные таблицы
при работе с данными
Содержание работы:
1 Мастер сводных таблиц
2 Построение макета сводной таблицы
3 Работа со сводной таблицей
Мастер сводных таблиц
Сводная таблица (или свод) –этотаблица итогов различных видов, составленная по данным из нескольких таблиц. Сводная таблица составляется в виде отчёта, в котором данные сгруппированы по блокам, для каждого блока и всего свода по какому-либо полю вычисляется заданная функция – среднее, сумма и др., причём таких полей может быть несколько. В качестве таблиц-источников могут выступать списки, другие сводные таблицы, базы данных внешние и внутренние, отдельные блоки данных электронной таблицы Excel и др. Сводная таблица обеспечивает различные способы агрегирования информации.Простейший свод составляется по одной таблице.
Пример 1Дан список "Экзаменационная ведомость" (рис.11.1).
Рисунок 11.1 Список "Экзаменационная ведомость"
Требуется построить сводную таблицу, в которой строками являются № группы, Фамилия И.О. и № зачётной книжки, а столбцами – Коды преподавателей. В ячейках на их пересечении нужно расположить оценки, которые каждый студент получил у принимавшего экзамен преподавателя и вычислить средний балл для каждого преподавателя.
Сводная таблица строится в виде отчёта с помощью Мастера сводных таблиц, который вызывается командами вкладки Вставка\Сводная таблица\.Сводная таблица.
В окне Создание сводной таблицы(рис. 11.2)нужно ввести следующую информацию:
1Указание вида источника сводной таблицы :
§ использование списка (базы данных Excel);
§ использование внешнего источника данных;
§ использование нескольких диапазонов консолидации;
§ использование данных из другой сводной таблицы.
Рисунок 11.2 Составление сводной таблицы
В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Наиболее распространён вариант использования внутренних списков.
2Указание диапазона ячеек, содержащего исходные данные. Список (база данных Ехсеl) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде :
[имя_книги] имя_листа! диапазон ячеек
(параметр в квадратных скобках необязателен)
Если предварительно курсор установлен в список, для которого строится сводная таблица, то интервал ячеек будет указан автоматически. Данные из других документов (внешние базы данных, другие таблицы и их диапазоны) указываются через кнопку Выбрать подключение…, появляется окно, в котором выбирается диск, каталог и файл рабочей книги, вводится имя рабочего листа и диапазон либо имя блока ячеек.
3 Выбор места расположения и параметров сводной таблицы.
Можно выбрать место расположения отчёта сводной таблицы, установив переключатель На новый лист илиНасуществующий лист, для которого необходимо задать диапазон размещения.