Пояснения

· Наименование магазина берется из Задания 1.

· Наименования видов товаров берутся из Задания 1.

· Уровень продаж – денежные суммы, вырученные от продажи каждого вида товара за год (денежный формат).

· Всего – общая сумма продаж за год (по горизонтали) или за все годы по каждому виду товара (по вертикали) (денежный формат).

· Количество сотрудников – произвольное число людей, работающих в магазине.

Таблица 1

Примечания.

Таблица 1 содержит данные, полученные в результате выполнения 2-х предыдущих этапов статистического исследования. Т.е. в течение 11 лет проводилось наблюдение за процессом продаж в магазине, данные группировались по видам товара и получались сводные итоги по видам товара и по годам в целом.

Вопрос. К какому виду статистических показателей относятся исходные данные?

1. Табличное представление.

Рассчитать показатели и поместить в таблицы

Исходные данные взять из Таблицы 1.

1.1. Таблица 2. Показатели структуры совокупности и интенсивности.

1.1.1. Спроектируйте таблицу на том же рабочем листе, сразу справа от таблицы 1.

1.1.2. На рис. ниже показаны формулы MS Excel для расчета показателей структуры совокупности и интенсивности.

1.1.3. Формулы вводятся 1 раз (поэтому присутствует знак $), а затем используется маркер заполнения.

1.1.4. Показатели структуры совокупности выражаются в Процентном формате.

1.1.5. Показатели интенсивности представляются в Денежном формате.

Примечания.

· Показатели структуры указывают на долю продаж каждого вида товара в товарообороте за год.

· Показатели интенсивности определяют сумму продаж, приходящуюся на каждого сотрудника по видам товара и всего за год.

1.2. Таблица 3. Средние величины и показатели вариации.

1.2.1. Спроектируйте таблицу на том же рабочем листе под таблицей 1.

1.2.2. На рис. ниже показаны формулы MS Excel для расчета:

1.2.2.1. Средних величин (степенных и структурных средних) по видам товара и столбцу «Всего»

1.2.2.2. Минимального и максимального значения

1.2.2.3. Показателей вариации (размах, среднее линейное отклонение, дисперсию, среднее квадратическое отклонение, коэффициент вариации).

1.2.3. Исходными данными являются ячейки таблицы, содержащие информацию об уровне продаж по 1 виду товара (в примере – диапазон B4:B15)

1.2.4. Формулы вводятся 1 раз, а затем используется маркер заполнения.

1.3. Таблица 4. Показатели динамики

1.3.1. Спроектируйте таблицу на том же рабочем листе под таблицей 3.

Обратите Внимание! Динамические ряды начинаются с 1996г, т.к. уровень 1995г. сравнить не с чем.

1.3.2. На рис. ниже показаны формулы MS Excel для расчета показателей динамики с подвижной и неподвижной базой по видам товара, столбцу «Всего» и относительным показателям структуры совокупности и интенсивности.

1.3.3. Формулы вводятся 1 раз, а затем используется маркер заполнения.

1.4. Таблица 5. Тенденция и прогноз.

1.4.1. Спроектируйте таблицу 5 на том же рабочем листе под таблицей 4.

1.4.2. Рассчитайте показатели тенденции по видам товара и столбцу «Всего». Для этого:

1.4.2.1. Активизируйте ячейку таблицы 5 по 1-му виду товара в 1995 г. (в примере ячейка В123);

1.4.2.2. Вызовите команду Формулы – Вставить функцию – Категория «Статистические» – Тенденция;

1.4.2.3. В окне «Аргументы функции» укажите диапазон известных значений У. Это данные об уровне продаж по первому виду товара из таблицы 1 (в примере диапазон В4:В15). Нажмите ОК.

1.4.2.4. Функция ТЕНДЕНЦИЯ возвращает ряд значений, связанных линейным уравнением y=ax+b. В текущей ячейке отображается первое значение из массива. Для того чтобы развернуть полученный массив данных, необходимо:

1.4.2.4.1. Выделить мышью (выделить ячейки без использования маркера заполнения!!!) диапазон ячеек таблицы 5 по 1-му виду товара за 1995-2006 г.г. (в примере ячейки В123:В134);

1.4.2.4.2. Нажать функциональную клавишу F2 (верхняя строка клавиатуры);

1.4.2.4.3. Нажать сочетание клавиш Ctrl+Shift+Enter.

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

1.4.3. Получите прогноз на 2007 – 2010 годы. Для этого:

1.4.3.1. Активизируйте ячейку таблицы 5 по 1-му виду товара в 2007 г. (в примере ячейка В135);

Т.к. тенденция – это линейное сглаживание, то каждая точка полученного ряда отличается от предыдущей на одно и то же число. (например 4; 7; 10; 13; 16..). Прогноз – это сумма уровня предыдущего года и шага (т.е. разницы между уровнями двух любых лет).

1.4.3.2. Введите формулу: = Уровень 2006 + (Уровень 2005 - Уровень 2004) (В примере = В134+(В133–В132).

1.4.3.3. Далее воспользуйтесь автозаполнением.

2. Графическое представление. Построить диаграммы и графики:

2.1. Показатели уровней продаж по годам и видам товара (исходные данные взять из Таблицы 1) – З-мерная столбчатая диаграмма:

2.1.1. Выполнить Вставка – Диаграммы – Гистограмма – Объемная гистограмма (Сравнение значений по категориям и по рядам на трех осях);

2.1.2. Перейти в меню Работа с Диаграммами – Конструктор – Расположение – Переместить Диаграмму. В открывшемся окне выбрать переключатель «На отдельном листе», имя листа – Уровень продаж.

2.1.3. Выполнить Работа с Диаграммами – Конструктор – Данные – Выбрать данные. В качестве источника данных выбрать диапазон ячеек, содержащий наименование видов товара и уровень продаж по годам. (В примере – диапазон В3:Е15)

2.1.4. В этом же окне выбрать кнопку «Изменить подписи горизонтальной оси (категорий)». Указать диапазон лет с 1995 по 2006 (В примере – ячейки А4:А15)

2.1.5. Оформить Название диаграммы и подписи горизонтальной оси (Работа с диаграммами – Макет – Подписи – Название диаграммы, Названия осей).

2.1.6. Диаграмма на листе «Уровень продаж» должна иметь примерно такой вид:

2.2. Процентный вклад каждого вида товара по годам (исходные данные взять из Таблицы 1) – Объемная нормированная гистограмма:

2.2.1. Выполнить Вставка – Диаграммы – Гистограмма – Объемная нормированная гистограмма с накоплением;

2.2.2. Перейти в меню Работа с Диаграммами – Конструктор – Расположение – Переместить Диаграмму. В открывшемся окне выбрать переключатель «На отдельном листе», имя листа – Структура по годам.

2.2.3. Выполнить Работа с Диаграммами – Конструктор – Данные – Выбрать данные. В качестве источника данных выбрать диапазон ячеек, содержащий наименование видов товара и уровень продаж по годам. (В примере – диапазон В3:Е15)

2.2.4. В этом же окне выбрать кнопку «Изменить подписи горизонтальной оси (категорий)». Указать диапазон лет с 1995 по 2006 (В примере – ячейки А4:А15)

2.2.5. Оформить Название диаграммы и подписи горизонтальной оси (Работа с диаграммами – Макет – Подписи – Название диаграммы, Названия осей).

2.2.6. Диаграмма на листе «Структура по годам» должна иметь примерно такой вид:

2.3. Показатели структуры совокупности по видам товара за все годы (исходные данные взять из Таблицы 1) – круговая (секторная) диаграмма

2.3.1. Выполнить Вставка – Диаграммы – Круговая – Объемная круговая;

2.3.2. Перейти в меню Работа с Диаграммами – Конструктор – Расположение – Переместить Диаграмму. В открывшемся окне выбрать переключатель «На отдельном листе», имя листа – Структура.

2.3.3. Выполнить Работа с Диаграммами – Конструктор – Данные – Выбрать данные. В качестве источника данных выбрать диапазон ячеек, содержащий наименование видов товара и всего по каждому виду товара. (В примере – диапазон В3:Е3; В16:Е16)

2.3.4. Оформить Название диаграммы, Подписи данных – Доли (Работа с диаграммами – Макет – Подписи – Название диаграммы; Подписи данных).

2.3.5. Диаграмма на листе «Структура» должна иметь примерно такой вид:

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

2.4.1. Выполнить Вставка – Диаграммы – Линейчатая – Линейчатая с группировкой;

2.4.2. Перейти в меню Работа с Диаграммами – Конструктор – Расположение – Переместить Диаграмму. В открывшемся окне выбрать переключатель «На отдельном листе», имя листа – Интенсивность.

2.4.3. Выполнить Работа с Диаграммами – Конструктор – Данные – Выбрать данные. В качестве источника данных выбрать диапазон ячеек, содержащий наименование видов товара и интенсивность продаж по годам. (В примере – диапазон L3:O15)

2.4.4. В этом же окне нажать кнопку «Строка/Столбец».

2.4.5. «Элементы легенды (ряды)» – Изменить. Каждому ряду присвоить год (см. рисунок).

2.4.6. Оформить Название диаграммы (Работа с диаграммами – Макет – Подписи – Название диаграммы).

2.4.7. Диаграмма на листе «Интенсивность» должна иметь примерно такой вид:

2.5. Показатели динамики по всем видам товара(исходные данные взять из Таблицы 1).

2.5.1. Выполнить Вставка – Диаграммы – График – График с маркерами;

2.5.2. Перейти в меню Работа с Диаграммами – Конструктор – Расположение – Переместить Диаграмму. В открывшемся окне выбрать переключатель «На отдельном листе», имя листа – Динамика.

2.5.3. Выполнить Работа с Диаграммами – Конструктор – Данные – Выбрать данные. В качестве источника данных выбрать диапазон ячеек, содержащий наименование видов товара и уровень продаж по годам. (В примере – диапазон В3:Е15)

2.5.4. В этом же окне выбрать кнопку «Изменить подписи горизонтальной оси (категорий)». Указать диапазон лет с 1995 по 2006 (В примере – ячейки А4:А15)

2.5.5. Оформить Название диаграммы (Работа с диаграммами – Макет – Подписи – Название диаграммы).

2.5.6. Диаграмма на листе «Динамика» должна иметь примерно такой вид:

2.5.7. По любым двум рядам построить линии тренда (линейные). Для этого:

2.5.7.1. Выделить ряд (щелкнуть мышью на одном из рядов);

2.5.7.2. В локальном меню (правая кнопка мыши) выбрать команду «Добавить линию тренда»;

2.5.7.3. В открывшемся окне выбрать «Линейную» линию тренда, настроить тип и цвет линии.

Вопросы.

· Сделайте вывод о тенденции процессов динамики в каждом случае.

· Сравните полученные линии.

· Подходит ли линейное сглаживание для данного динамического ряда?

2.5.8. По одному из оставшихся рядов построить линии скользящей средней (n=3). Для этого: аналогично п. 2.5.7. выбрать команду «Добавить линию тренда», но в окне параметров линии выбрать переключатель «Линейная фильтрация» и установить «3 точки».

2.5.9. По одной из линейных линий тренда построить прогноз на 2007-2010 годы. Для этого:

2.5.9.1. Выделить линию тренда (щелкнуть мышью на одной из линий);

2.5.9.2. В локальном меню (правая кнопка мыши) выбрать команду «Формат линии тренда»;

2.5.9.3. В открывшемся окне установить «Прогноз вперед на 4 периода».

2.5.10. Окончательно диаграмма на листе «Динамика» должна иметь примерно такой вид: