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

МЕТОДИЧЕСКАЯ РАЗРАБОТКА

для студентов Iкурса фармацевтического факультета

к практическому занятиюпо теме:

«Организация сводных таблиц»

1. Научно-методическое обоснование темы:

Освоить технологию работы со сводными таблицами.

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

2. Краткая теория:

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

Агрегирование – проведение групповых операций над данными, таких как суммирование, нахождение среднего и т.п. по определенным признакам.

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

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

Например, имеется таблица с исходной информацией:

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

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

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

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

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

Предположим, имеется следующая таблица:

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

1. Выделить таблицу исходных данных вместе с шапкой.

2. Данные ® Сводная таблица ® … Далее ® Далее ® Готово…

На отдельном листе будет создан шаблон для построения сводных таблиц (см. рис. ниже).

3. Перетащить кнопки с наименованиями граф в соответствующий области шаблона:

- кнопку «Покупат…» необходимо перетащить (нажать, и удерживая кнопку мыши переместить курсор мыши в область строк шаблона таблицы).

- кнопку «Сумма з…» необходимо переместить в область данных.

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

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

Предположим, что аптека «Фармация» реализует медикаменты в розницу. Ежедневно по проданным товарам продавцы предоставляют данные о количестве проданных медикаментов в формате:

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

Исходные данные представлены в следующей таблице.

РЕАЛИЗАЦИЯ МЕДИКАМЕНТОВ

Графа «Сумма» должна быть рассчитана по формуле: Сумма = Кол-во * Цена.

В связи с тем, что итоги (объем продаж) должны рассчитываться за месяц и за год необходимо ввести две дополнительные графы «Месяц» и «Год», рассчитываемые по формулам:

Графа "Месяц" = МЕСЯЦ(Графа "Дата")

Графа "Год" = МЕСЯЦ(Графа "Год")

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

Теперь необходимо выделить таблицу и активировать опцию Данные ® Сводная таблица … ® Далее ® Далее ® Готово. Получаем на отдельном листе шаблон создания сводной таблицы.

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

1. Переместить "Наимено…" в секцию "Перетащите сюда поля строк" как показано на рисунке.

2. Переместить "Ед. изм." в область строк таблицы.

3. Переместить последовательно "Кол-во" и "Сумма" в область
данных таблицы.

4. Переместить образовавшуюся графу «Данные» в область столбцов таблицы как это показано на рисунке:

В результате получим таблицу:

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

1. Перейти на любую ячейку графы "Наименование товара".

2. Вызвать контекстное меню нажатием правой кнопки мыши и выбрать опцию параметры поля как это показано на рисунке:

3. В появившемся диалоге необходимо выбрать переключатель «Нет» в информационной группе «Итоги».

® Нажать Ok

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

4. Графы сводной таблицы «Сумма по полю Кол-во» и «Сумма по полю Сумма» необходимо переименовать в «Кол-во» и «Сумма», соответственно (перед текстом необходимо набрать пробел).

Далее необходимо переместить кнопки «Мес», «Год», «Группа» в
область полей страниц. Получим:

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

Например, необходимо определить выручку за август месяц 2001 года по группе «Группа №3». Для этого в верхней части таблицы из списка выбираются необходимые параметры. Результат представлен на рисунке.

Из рисунка видно, что значения в таблице автоматически изменяются при изменении полей «Месяц», «Год», «Группа».

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

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

Присвоим области исходных данных имя «ИсходныеДанные»:

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

2. Вставка ® Имя …® Присвоить … ® Ввести название диапазона: «ИсходныеДанные». …® Ok.

Рис.Выделение диапазона ячеек – исходных данных для сводной таблицы

Рис.Присвоение имени диапазону ячеек.

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

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

2. Вызвать контекстное меню и активировать опцию «Мастер…».

3. В открывшемся диалоге необходимо переместиться на шаг 2 (один раз нажать кнопку «Назад») до места выбора диапазона.

4. Ввести имя диапазона ячеек с исходными данными как показано на рисунке:

5. Нажать «Готово».

Теперь сводная таблица будет брать исходные данные из диапазона ИсходныеДанные. При увеличении числа строк в таблице исходных данных необходимо будет увеличить размерность определенного именованного диапазона ИсходныеДанные: Вставка ® Имя ® Присвоить… ® Выбрать диапазон ИсходныеДанные в списке ® После чего увеличить размер диапазона как показано на рисунке изменив номер последней строки диапазона на больший. Например, «15» заменить на «20».

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

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

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

Результат предстает в виде автоматически сформированной новой сводной таблицы как это показано на рисунке:

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

Рис. Полученная в результате установки критериев отбора
сводная таблица.

3.Цель деятельности студентов на занятии:

Студент должен знать:

1. Понятие агрегирования, Сводная таблица

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

3. Знание областей полей сводной таблицы.

4. Как создать сводную таблицу.

5. Для чего необходимо поле сводной таблица «Поля строк»

6. Для чего необходимо поле сводной таблица «Поля столбцов»

7. Как обновить данные в Сводной таблице.

Студент должен уметь:

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

2.Эффективно использовать инструмент Сводная таблица, получения сводных данных по отдельным операциям за какой-либо период.

4. Содержание обучения:

Теоретическая часть:

1.Работа со сводными таблицами.

2. Использование сводных таблиц в итоговых отчетах.

Практическая часть:

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

2.Получить сводную информацию :

Вариант 1

В представленной ниже таблице приведена информация о реализации медикаментов.

РЕАЛИЗАЦИЯ МЕДИКАМЕНТОВ

Требуется:

- Рассчитать недостающие данные таблицы – ячейки, отмеченные знаком вопроса.

- Отсортировать исходные данные по дате.

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

Вариант 2

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

ПРОИЗВОДСТВО МЕДПРЕПАРАТОВ

Требуется:

- рассчитать значения ячеек, отмеченных знаком вопроса;

- рассчитать итоговую строку;

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

5. Перечень вопросов для проверки исходного уровня знаний:

1.Расскажите, как происходит ввод и редактирование данных в список.

2.Как можно созданный список дополнить и отредактировать с помощью электронной формы. (Данные ® Форма …).

3.Как присвоение списку определенного имени.

4.Сортировка (упорядочивание) записей в списке.

5.Фильтрация (отбор) записей в списках. Дайте определение фильтрации данных.

6.Как после фильтрации можно ввести дополнительные ограничения.

7.Подведение итогов в отфильтрованном списке.

8.Опишите синтаксис функции Промежуточные итоги.

9.Расскажите об использовании Автосуммы.

6.Перечень вопросов для проверки конечного уровня знаний:

1. Что такое сводная таблица? Область применимости сводных таблиц.

2. Опишите порядок создания сводной таблицы.

3. Каким образом можно изменить формат полей сводной таблицы? Как возможно изменить наименование графы сводной таблицы?

4. Что необходимо сделать, чтобы обеспечить достоверность данных сводной таблицы?

5. Как при создании сводной таблицы необходимо учесть возможность увеличения объема исходных данных?

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

7. Каким образом производится выбор критериев отбора сводной таблицы?

8. Опишите порядок выполнения работы. Как должна быть оформлена работа? Как необходимо представлять результаты проделанной работы?

7.Хронокарта учебного занятия:

1.Организационный момент – 5 мин.

2.Текущий контроль знаний – 30 мин.

3.Разбор темы – 20 мин.

4.Практическая работа – 30 мин.

5.Подведение итогов занятия – 10 мин.

8.Самостоятельная работа студентов.

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

9.Перечень учебной литературы к занятию:

1. Арунянц Г.Г., Столбовский Д.Н., Калинкин А.Ю.

«ИНФОРМАТИКА практический курс для студентов медицинских вузов».Владикавказ, 2005, Раздел 2. Занятие 1, стр. 184-189. Вариант 3, стр. 203.

2. Есауленко И.Э., Семенов С.Н. Основы практической информатики в медицине; Воронеж, 2005.

Не нашли, что искали? Воспользуйтесь поиском по сайту: