Тема. Табличный процессор MS Excel. Анализ данных с помощью сводных таблиц.

Цель работы: научиться проводить анализ данных с помощью сводных таблиц в табличном процессоре MS Excel.

 

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

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

Задание 1. Подготовьте начальные данные для построения сводной таблицы, или загрузите готовый файл общие_сводные.xls. Создайте сводную таблицу объема продаж по товарам и регионам (используя мастер сводных таблиц).

В таблице приведены данные об объемах продажи телевизоров разных производителей по регионам за три месяца 2018 года.

 

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

 

Методические указания.

1. Выберите пункт меню Данные – Сводная таблица…. Будет запущен мастер.

2. На первом шаге запрашивается начало данных для сводной таблицы - выберите переключатель в списке или базе данных Microsoft Excel и нажмите кнопку Далее>.

3. На второму шаге выделите диапазон ячеек A2:D29. Нажмите Далее>.

Внимание!!! При выделении диапазона, захватываем заголовок таблицы, а нижнюю строку с итогами не берем.

4. На третьем шаге, непосредственно создается сводная таблица - на макет, показанный на рис. 1, перетягиваем заголовки таблицы в нужны области. ОК.

Рисунок 1. Макет для создания сводных таблиц

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

 

 

Рис.2. Диалоговое окно Вычисление поля сводной таблицы.

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

 

 

4. На третьем шаге выбираем местоположение таблицы.

 

 

 

 Сводная таблица может иметь вид, как на рис. 3. По всей видимости, в ней столбец Объем продажи подсчитан и обобщен по столбцам Регион и Товар по всем месяцам. Поля Период, Регионы и Товары есть списки, которые позволяют выбирать и просматривать срезы данных по некоторым критериям. В поле Сумма по полю Объем продажи можно задавать другие операции.

 

Рисунок 3. - Сводная таблица по товарам и регионам.

 

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

Задание 2. Проанализируйте срезы данных по месяцам, раскрыв поле со списком Период. Рассмотрите основные кнопки: панели инструментов Сводная таблица.

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

- Формат отчета позволяет добавить сводной таблице "презентабельный" для печати вид;

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

- Отобразить детали позволяет детализировать сводную таблицу по некоторому параметру. Например, выделите поле Товар, нажмите кнопку, дальше в отдельном окне Показать детали выберите Период и нажмите ОК - товары будут детализированы по всем периодам;

- Отключить детали отменяет детализацию;

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

- Параметры поля позволяет установить обобщенную операцию по выбранному полю. Например, выделите на сводной таблице поле Сумма по полю Объем продаж и щелкните по кнопке. В окне, которое появилось, вместо операции Сумма задайте операцию Минимум (или другую). Нажмите ОК, проанализируйте результат, а потом отмените результат кнопкой.

 

Задание 3. Для полученной сводной таблицы постройте объемную диаграмму.

Методические указания.

1. Щелкните по сводной таблице для ее выделения. На панели инструментов Сводная таблица нажмите кнопку   Мастер диаграмм. Будет построена гистограмма с накоплением.

2. Измените тип гистограммы: еще раз нажмите кнопку Мастер диаграмм (можно щелкнуть правой кнопкой мыши по самой гистограмме и из списка, который появился, выбрать Тип диаграммы, но тогда гистограмма будет построена на отдельном письме). В окне, которое появилось, выберите вид - объемная гистограмма и нажмите Далее.

3. В следующем окне Мастер диаграмм нажмите Далее.

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

Рисунок 4 - Гистограмма анализа продажи товаров по сводной таблице.

 

Задача 3. Выполните фильтрацию данных в таблице с помощью автофильтра.

Методические указания.

1. Выделит любую ячейку начальной таблицы и выполните команды: Данные – фильтр – автофильтр.

2. Из открывающегося списка строки «Период» выберите «март».

 

 

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

 Для восстановления всех строк начальной таблицы необходимо выполнить команды: Данные – Фильтр – Отобразить все.

 

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

 

 

Лабораторная работа №6

Тема: Табличный процессор MS Excel. Использование стандартных функций.

Цель работы: научиться проводить расчеты в табличном процессоре MS Excel с помощью стандартных функций.

 

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

Функция – это готовая специализированная формула, которая составляется из имени функции и аргумента или нескольких аргументов.

 

Синтаксис (правило написания) функции:

Имя_Функции (Аргумент)

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

Введение функции в ячейку:

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

2. Вставка ð Функция или <f_х> или <Shift + F3>

3. В поле Категория выбрать нужную категорию.

4. В поле Функция -функцию. ОК

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

5. В открывшемся диалоговом окне, ввести аргументы.

6. После выбора аргументов в нижней части диалогового окна будет виден результат. Если он правильный, то <ОК>.

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

 

Рассмотрим функцию: