Анализ табличных данных средствами MS Excel

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

Анализ табличных данных средствами MS Excel

 

Цель работы: Научиться приемам работы, связанным с решением задач экономического анализа данных; самостоятельной постановке (формулировке) таких задач.

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

Используемые  средства Excel: Команды и мастерá меню Данные: Сортировка, Фильтр, Итоги, Сводная таблица.

 

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

 

Исходные данные для  решения поставленных задач находятся  на листе Все модели книги LR_3_Analiz_Dannykh_.xls.

 

 

 

1. Определение  экстремальных (минимальных и  максимальных) значений в списках при помощи сортировки и автофильтра

 

Подсказка: Для решения поставленных задач целесообразно совместно использовать автофильтр и сортировку.

 

Пример  содержания и оформления отчета

Например, необходимо решить такую задачу: найти, у кого из продавцов модель Sony Ericsson K850i продается по самой низкой цене.

 

Решение: Сначала при помощи автофильтра отобразить в таблице данные только этой модели (Sony Ericsson K850i), а затем отсортировать отфильтрованные данные по возрастанию по полю Цена, грн. (или Цена, $).

 

Ответ: Дешевле всего, по цене 565 $ (или 2881,50 грн.) телефон модели Sony Ericsson K850i можно купить в магазине «Topmobila» (поле Продавец).

 

 

 

 

Задания

Найти ответы на поставленные задачи в соответствии со своим вариантом в таблице 1.

Во всех задачах также указывать цену телефона. Задачу 3 каждого варианта решить двумя способами: 1) комбинируя фильтрацию и сортировку; 2) используя только многоуровневую сортировку.

Таблица 1.

№ Варианта	№ Задачи	Задание
1	1	у кого из продавцов Nokia N73 Music Edition продается по самой высокой цене
	2	самый дорогой  телефон (модель, продавец) производимый Францией
	3	продавца самого дорогого телефона модели Nokia N73 Music Edition, производимого Венгрией
2	1	у кого из продавцов  модель AppleiPhone8Gb продается по самой высокой цене
	2	самый дешевый  телефон (модель, продавец) производимый Германией
	3	продавца самого дорогого телефона модели AppleiPhone8Gb, производимого Францией
3	1	у кого из продавцов Nokia N95 продается по самой высокой цене
	2	самый дорогой  телефон (модель, продавец) производимый Финляндией
	3	продавца самого дешевого телефона модели Nokia N95, производимого Францией
4	1	у кого из продавцов Nokia-6300 продается по самой высокой цене
	2	самый дорогой  телефон (модель, продавец) производимый Венгрией
	3	продавца самого дорогого телефона модели Nokia-6300, производимого Францией
5	1	у кого из продавцов Sony Ericsson K850i продается по самой высокой цене
	2	самый дешевый  телефон (модель, продавец) производимый Францией
	3	продавца самого дорогого телефона модели Sony Ericsson K850i, производимого Францией
6	1	у кого из продавцов Nokia N73 Music Edition продается по самой низкой цене
	2	самый дешевый  телефон (модель, продавец) производимый Германией
	3	продавца самого дорогого телефона модели Nokia N73 Music Edition, производимого Германией
7	1	у кого из продавцов  модель AppleiPhone8Gb продается по самой низкой цене
	2	самый дешевый  телефон (модель, продавец), производимый Финляндией
	3	продавца самого дешевого телефона модели Nokia N95, производимого Германией
8	1	у кого из продавцов Nokia N95 продается по самой низкой цене
	2	самый дешевый  телефон (модель, продавец) производимый Венгрией
	3	срок гарантии и продавца самого дорогого телефона модели Nokia-6300, производимого Финляндией
9	1	у кого из продавцов Nokia-6300 продается по самой низкой цене
	2	минимальный срок гарантии на телефоны модели Sony Ericsson K850i, производства Финляндии
	3	продавца самого дорогого телефона модели Nokia-6300, производимого Германией
10	1	у кого из продавцов Sony Ericsson K850i продается по самой низкой цене
	2	страну-производителя  телефона модели Nokia-6300, для которого установлен наименьший срок гарантии
	3	продавца самого дорогого телефона модели Sony Ericsson K850i, производимого Германией

 

 

 

Задача 4 (общая). Создать собственный параметр сортировки, например, по стране-производителю в таком порядке: Франция, Германия, Финляндия, Венгрия. Для этого сначала создать список с указанной последовательностью значений, в окне Параметры на вкладке Списки: Сервис® Параметры® Списки. (рис. 1) Элементы списка можно добавить вручную с клавиатуры (в поле Элементы списка) или импортировать из предварительно созданного диапазона на каком-либо листе книги, нажав кнопку <Импорт>.

После этого (ввода  или импорта элементов списка) нажать кнопку <Добавить> для добавления нового списка к уже имеющимся.

Рис. 1. Создание пользовательского списка

Отсортировать список по своему параметру. Для этого:

• вернуться в таблицу с исходными данными;
• выполнить команду Данные®Сортировка…;
• в списке Сортировать по выбрать Производитель и нажать кнопку <Параметры> (рис.2);
• в списке Параметры сортировки выбрать собственный список и нажать кнопку <ОК> (рис. 2);
• вернувшись в окно Сортировка диапазона нажать кнопку <ОК>.

 

Рис. 2. Сортировка по параметру пользователя

 

 

2. Отбор данных  при помощи фильтров

Задачи 1 и 2 решить двумя способами: при помощи автофильтра  и расширенного фильтра (данные расширенным  фильтром фильтровать на месте). Задачу 3 решить при помощи расширенного фильтра.

Найти ответы на поставленные задачи в соответствии со своим вариантом в таблице 2.

Таблица 2.

№ Варианта	№ Задачи	Задание
1	1	Название продавца содержит сочетание символов «com»
	2	Название модели содержит «5», а цена не более 3500 грн.
	3	Гарантия на телефон не менее полугода а также  все телефоны производства Франции
2	1	Название продавца заканчивается на «ua»
	2	Название модели содержит «Nokia», а цена лежит в пределах от 1000 до 2000 грн.
	3	Гарантия на телефон менее полугода и все такие, которые произведены в Венгрии
3	1	Название продавца заканчивается на «market»
	2	Название модели не содержит «Nokia», а цена лежит в пределах от 2000 до 4000 грн
	3	Любые финские  телефоны и все такие, у которых цена не менее 900 $
4	1	Название продавца не заканчивается на «ua»
	2	Цена модели лежит в пределах от 3000 до 5000 грн, а производитель начинается на «Ф»
	3	Все телефоны, предлагаемые продавцом koala.com.ua и все телефоны Nokia
5	1	Название продавца содержит «mobil»
	2	Цена модели больше 4000 грн, а производитель начинается на «Ф»
	3	Все телефоны, в  названии продавца которых содержится «market» и все телефоны с гарантией 6 или 7 месяцев
6	1	Название продавца начинается с «mobil»
	2	Цена модели не превышает 2000 грн, а название продавца содержит  «ua»
	3	Все телефоны, цена которых в грн. не менее 4500, и все  телефоны, цена которых в $ не более 250
7	1	Название продавца не заканчивается на «a» (англ.)
	2	Цена модели лежит в пределах от 600 до 1000 $, а название продавца содержит  «mobil»
	3	Название продавца содержит «tele» и все телефоны с гарантией 3 месяца
8	1	Название продавца содержит «net»
	2	Цена модели лежит в пределах от 500 до 700 $, а  страна производитель Финляндия или Франция
	3	Все телефоны с  гарантией не более полугода и  все телефоны, цена которых в $ не более 250
9	1	Название продавца начинается с «sk»
	2	Цена модели не ниже 600$, а страна производитель  Германия или Венгрия
	3	Все телефоны, цена которых в $ не менее 1000, и все телефоны, цена которых в грн. от 1300 до 1400
10	1	Название продавца содержит «market»
	2	Гарантия на телефон не более полугода, а цена модели от 1000 $
	3	Название продавца заканчивается на «net» и все телефоны с гарантией не более 1 месяца

 

Задача 4 (общая). * При помощи расширенного фильтра найти все телефоны с ценой в полтора раза выше средней с использованием функции СРЗНАЧ. 

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

 

При выполнении заданий  не забывайте выполнять предварительную  сортировку по тому полю, по которому будут подводиться итоги! Флажок <Заменить Итоги> должен быть установлен.

 

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

1. Среднюю цену каждой модели телефона;
2. Количество моделей, предлагаемых каждым продавцом;
3. Минимальную цену (в грн и $), для каждой страны-производителя;
4. Разброс цен на каждую модель телефона с точностью два десятичных знака (стандартное отклонение – мера того, насколько широко разбросаны точки данных относительно их среднего, рассчитывается по формуле: ).

 

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

• свернуть исходные данные, оставив на листе только итоги и выделить их;
• выполнить команду Правка®Перейти…®<Выделить…>®только видимые ячейки (рис. 3)
• после этого выполнить команду Правка®Копировать. В результате в буфер обмена будут скопированы только итоговые значения.

Рис. 3. Как скопировать промежуточные итоги

 

 

5. * Решите задачу 4 «вручную», используя приведенную выше формулу и встроенные функции Excel КОРЕНЬ, СУММКВРАЗН (сумма квадратов разностей), СЧЕТ. Для удобства скопируйте таблицу на новый лист книги и добавьте по одной пустой строке после каждой группы моделей (как показано на рис. 4).

 

 

 

Название Модели	Цена, грн.	Цена, $
AppleiPhone8Gb	6122,60	1210
………	………	………
AppleiPhone8Gb	3970,20	780
Средний разброс цен на AppleiPhone8G
Nokia N73 Music Edition	2360,28	462,80
………	………	………
Nokia N73 Music Edition	2143,76	422,00
Средний разброс цен на NokiaN73MusicEdition
Nokia 6300	2040,00	400,00
и т.д.

Рис.  4.

 

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

 

Перед выполнением  этого задания убрать все итоги!

Все полученные сводные таблицы скопировать  в отчет.

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

Для вызова мастера  сводных таблиц выполнить команду  меню Данные ® Сводная таблица. Источником данных является исходная таблица. С помощью кнопки Макет на третьем шаге мастера укажите, как будут располагаться данные в сводной таблице: в область строк сводной таблицы перетащите поле Название Модели, в столбцы – Производитель, а в область данных – Цена, грн. Сводную таблицу разместите на отдельном листе «Сводная Таблица».

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

- Редактирование сводной таблицы перетаскиванием  полей – установите курсор мыши на поле Производитель и, удерживая левую кнопку мыши, перетащите его в область строк левее поля Название Модели. В результате у вас должна получиться сводная таблица, как показано на рис. 5. Выделите ячейки с промежуточными итогами (по каждой стране) и установите для них шрифт Impact 12 пт. полужирный (Для выделения сразу всех строк с промежуточными итогами по странам-производителям проверьте, что в контекстном меню сводной таблицы команда Выделить®Разрешить выделение включена.

 

Рис. 5. Изменение сводной таблицы путем прямого перетаскивания полей

Скройте данные поля Название Модели. Для этого щелкните в сводной таблице по заголовку поля Название Модели и нажмите кнопку <Скрыть детали> на панели инструментов Сводные таблицы (Вид®Панели инструментов®Cводные таблицы, рис. 6). Восстановите отображение деталей (<Отобразить детали> ).

- Редактирование сводной таблицы с использованием списка полей сводной таблицы. Для отображения списка полей сначала нажмите на панели кнопку <Отобразить список полей> (рис. 6)

Рис. 6. Панель инструментов Сводные таблицы

Рис. 7. Окно Список полей сводной таблицы

С помощью этого  списка добавьте поля в сводную таблицу. Например, необходимо выяснить, как  распределяются средние цены на модели телефонов в зависимости от срока  гарантии: в область столбцов добавьте поле Гарантия (выделить поле в списке и щелкнуть кнопку <Поместить в>, выбрав предварительно значение «Область столбцов» в поле со списком справа рис. 7).

Посмотрите, как  теперь выглядит макет сводной таблицы  – в выпадающем списке Сводная таблица (рис. 6) на панели инструментов выберите команду Мастер сводных таблиц, а затем Макет…; удалите из таблицы поле Производитель – просто перетащите его мышью за пределы макета сводной таблицы.

5. ***Комплексная  задача

 

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

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