Работа с табличным процессором MS Excel.

Освоение приемов работы с электронными таблицами

Практическое занятие 11.

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

Цель работы:

1.                 Освоение средства подведения итогов в  электронных таблицах

2.                 Изучения средств структурирования данных в  электронных таблицах

1.                  Подготовка к работе

Откройте файл с ведомостью результатов тестирования, составленной на практическом занятии 10.

Добавьте новый лист в рабочую книгу, назовите его «Итоги1» и скопируйте на новый рабочий лист данные  листа «ВПР».  Отсортируйте данные по убыванию столбца «Оценка». Скопируйте лист «Итоги1»  на еще два новых листа «Структура1» и «Структура2».

Подсчитайте количество оценок «5», «4», «3», «2» и поместите  результат в строке ниже строки со средними значениями. (Используйте функцию СЧЕТЕСЛИ).

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

Удобным и быстрым способом обобщения и анализа данных в электронных таблицах является автоматическое подведение итогов.

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

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

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

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

·        сумму

·        количество значений

·        количество чисел

·        среднее

·        максимум

·        минимум

·        произведение

·        статистические  характеристики: смещенное и несмещенное отклонение, смещенная и несмещенная дисперсия.

Для автоматического подведения итогов необходимо:

- отсортировать список по столбцу, по которому будут подводиться итоги;

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

- выполнить команду Данные – Итоги;

- в диалоге Промежуточные итоги  (см. рис.11.1)

- выбрать из списка тот столбец, по которому будут подводиться итоги (это должен быть тот столбец, по которому производилась сортировка);

- из списка Операции выбрать одну из перечисленных функций;

- в списке Добавить итоги по: отметить столбцы,  по которым необходимо подвести итоги.

Рисунок 11‑1 Диалог «Промежуточные  итоги»

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

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

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

3.                  Задание

Средствами  команды Данные – Итоги подчитайте количество оценок «5», «4», «3», «2». Подсчитайте средние значения суммы баллов в диапазоне  оценок «5», «4», «3», «2».

Указание. Примените команду дважды, выбирая разные операции из списка операций; флажок «Заменить текущие итоги» необходимо сбросить.

4.            Структурирование рабочих листов Excel

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

4.1. Задание

4.1.1. Исследуйте возможности работы со структурой на листе  «Итоги1»:
- скройте все  исходные данные, отобразив только промежуточные и общие итоги;
- отобразите только общие итоги;

- отобразите группу испытуемых, получивших оценку «удовлетворительно».
4.1.2. Отобразите промежуточные итоги и постройте гистограмму оценок (на отдельном листе).  Обратите внимание на изменение диаграммы при  раскрытии одной из групп детальных данных.

4.2. Автоматическое и ручное создание структуры

Автоматически структура таблицы создается при выполнении таких операций из меню Данные, как Итоги, Консолидация, Сводная таблица.
Сгруппировать строки или столбцы таблицы можно и самостоятельно, командами:
- Данные – Группа и структура – Группировать;

- Данные – Группа и структура – Создание структуры.

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

Вторая автоматически выделяет имеющиеся в таблице структуры. Такие структуры  выделяются, если в таблице использовались формулы для вычисления промежуточных итогов или использовались функции СУММ, МАКС, МИН, СРЗНАЧ и т.п.

Задание.  На листах «Структура1» и «Структура2» создайте структуру автоматически (Данные – Группа и структура – Создание структуры)  и вручную (Данные – Группа и структура – Группировать).

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

5        Дополнение. Интерфейс программы Excel – 2007

5.1              Для сортировки в Excel – 2007 используются пиктограммы из группы «Сортировка и фильтр» на вкладке «Данные».

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