Задание 1
Файл Облигации.XLS содержит значения следующих переменных:
Дата – текущая дата.
10 - летние облигации – значения учетной ставки 10-летних облигаций Министерства финансов США (процентное изменение по отношению к величине учетной ставки на момент закрытия предыдущих торгов).
DJIA - значения индекса Доу Джонса на момент закрытия предыдущих торгов.
1. Постройте график (один) временных рядов для ежедневных значений индекса, его ежедневных процентных изменений.
2. Вычислите основные статистические характеристики переменных.
3. Определите ежедневные изменения индекса DJIA, выраженные в процентах. Насколько соответствуют этим данным правила областей (правило трёх сигм (3;))?
Ответы:
1. Постройте график (один) временных рядов для ежедневных значений индекса, его ежедневных процентных изменений.
Итак, для построения графика временных рядов используем надстройку Stat Pro. Из перечня слуг выбираем вкладку Charts и далее нажимаем Time series plots. В качестве переменной оси Х выбираем время (годы, месяцы, дни). Так как наша задача заключается в прогнозировании, нам нужно определить, наблюдается ли какая-нибудь устойчивая зависимость от времени заданной величины (в данном случае индекс Доу Джонса) и наблюдается ли компонента.
Теперь построим упомянутую выше зависимость. Воспользовавшись надстройкой, наблюдаем следующий линейный график:
Насколько заметно, мы имеем ярко выраженный возрастающий тренд.
2. Вычислите основные статистические характеристики переменных.
Теперь, для вычисления статистических характеристик переменных можем воспользоваться стандартными инструментами Excel такими как: Мода, Медиана (для этого достаточно: Вставка – Функция – Статистические – Медиана), МАКС, МИН, СЧЕТЕСЛИ и др. Далее можно построить график точечных частот (Вставка – Диаграмма – Стандартные – Точечная). Но для максимизации качества и количества полезных дел за определенный отрезок времени, предлагаю опять же воспользоваться надстройкой Stat Pro, которая за короткий промежуток автоматически подсчитает и выведет необходимую статистику.
Итак, переходим к данным в файле Облигации (Excel). Для расчета выбираем Stat Pro, вкладка Summary Stats и далее One-Variable Summary Stats. Выделяем заголовки (без выделения надстройка работать не будет), выбираем массу данных и получаем следующие показатели:
Summary measures for selected variables
10-летние облигации DJIA
1 Count 60,000 59,000
2 Sum 230,210 483657,210
3 Mean 3,837 8197,580
4 Median 3,890 8257,610
5 Standard deviation 0,139 311,364
6 Minimum 3,540 7524,060
7 Maximum 4,090 8726,730
8 Range 0,550 1202,670
9 First quartile 3,708 7912,230
10 Third quartile 3,940 8462,930
На приведенной выше таблице наблюдаем: счет, среднее значение, медиану, стандартное отклонение, минимум, максимум, первый и третий квартили, диапазон.
Аналогичную процедуру можно проделать с использованием анализа данных (выбираем инструмент «описательная статистика»). На выходе получаем следующие данные:
3,88 *
Среднее 3,836102 Среднее 8197,58
Стандартная ошибка 0,018198 Стандартная ошибка 40,53614
Медиана 3,89 Медиана 8257,61
Мода 3,9 Мода #Н/Д
Стандартное отклонение 0,139778 Стандартное отклонение 311,364
Дисперсия выборки 0,019538 Дисперсия выборки 96947,53
Эксцесс -0,90294 Эксцесс -0,69893
Асимметричность -0,52894 Асимметричность -0,4464
Интервал 0,55 Интервал 1202,67
Минимум 3,54 Минимум 7524,06
Максимум 4,09 Максимум 8726,73
Сумма 226,33 Сумма 483657,2
Счет 59 Счет 59
3. Определите ежедневные изменения индекса DJIA, выраженные в процентах. Насколько соответствуют этим данным правила областей (правило трёх сигм (3;))?
Для того, чтобы определить изменения индекса DJIA, воспользуемся следующей формулой. В файле «Облигации» выбираем пустую ячейку справа от данных, и вводим формулу: =(C4-C3)/C3. Получаем значение 0,013032 и применяем к ячейке с этим значением процентный формат. Получаем 1%.
Далее, чтобы не повторять предыдущие шаги, выделяем ячейку с полученным значением, нажимаем на ее правый нижний угол и перетаскиваем его вниз до ячейки. Получаем следующие значения: 1%,-2%,1%, -1%,0% и т.д.
Далее проверяем, правильно ли все подсчитано. Для этого выбираем нижнее значение DJIA и применяем первоначальную формулу. Если значения сходятся – значит вычисления прошли верно.
Также можно вычислить общее изменение. Для этого опять ж выделяем свободную ячейку и пользуемся следующей формулой: =(C4-$C$3)/$C$3. Далее выбираем ячейку с получившимся значением и применяем процентный формат. Затем выделяем эту же ячейку, нажимаем на ее правый нижний угол и перетащите вниз до ячейки С61.
Проверяем, все ли прошло хорошо.
Когда мы протягиваем (копируем) формулу вниз, абсолютная ссылка остается неизменной, а относительная изменяется.
Теперь три сигма. Правило трех сигм гласит, что практически все значения нормально распределённой случайной величины лежат в определенном интервале (не будем сейчас его приводить) и приблизительно с 0,9973 вероятностью значение нормально распределённой случайной величины лежит в указанном интервале (при условии, что величина Х истинная, а не полученная в результате обработки выборки).
Если же истинная величина Х неизвестна, то следует пользоваться S - таким образом, правило трёх сигм преобразуется в правило трёх s.
Но сейчас не об этом. Чтобы определить, насколько соответствуют полученным данным правила областей, обратимся к инструменту «вставить функцию» и при помощи инструмент ОБЛАСТИ мы можем соотнести полученные данные.
Задание 2.
Для анализа финансовых расчетов с филиалами торговой компании за последние 4 месяца (файл Торговая компания.XLS) собрана информация об операциях поставки товаров, а именно, Филиал№, месяц, категория товара, сумма поставки, сумма поступившей оплаты. Необходимо исследовать данные с помощью инструментария Excel, а также:
А) создайте сводную таблицу для вычисления количества операций по каждому филиалу и по каждому месяцу (по всем категориям)
Б) создайте сводную таблицу для вычисления общих сумм поставок по каждому филиалу за каждый месяц. Используя полученные данные, постройте соответствующие временные ряды для каждого филиала.
В) постройте гистограмму для поступивших оплат для трех категорий поставки.
Ответы:
А) создайте сводную таблицу для вычисления количества операций по каждому филиалу и по каждому месяцу (по всем категориям)
Итак, для создания сводной таблицы выбираем в Excel инструмент «сводные таблицы» и при помощи «параметры» и «конструктор» начинаем работу: выбираем дизайн таблицы и пр.
Далее в «выберите поля для добавления в отчет» переносим «Филиалы» в «Названия строк», «Месяц» в «Названия столбцов», а «Количество товара» в «Значения». Получаем следующую сводную таблицу:
Количество по полю
Категория товара Названия столбцов
Названия строк Март Апрель Май Июнь Общий итог
Филиал_1 5 4 2 4 15
Филиал_2 9 4 6 9 28
Филиал_3 4 4 4 2 14
Филиал_4 7 3 5 7 22
Филиал_5 3 5 3 3 14
Филиал_6 2 5 3 2 12
Филиал_7 5 4 2 11
Общий итог 35 29 25 27 116
Таким образом, нам удалось вычислить количества операций по каждому филиалу и по каждому месяцу (по всем категориям).
Б) создайте сводную таблицу для вычисления общих сумм поставок по каждому филиалу за каждый месяц. Используя полученные данные, постройте соответствующие временные ряды для каждого филиала.
Теперь переходим к созданию сводной таблицы для вычисления общих сумм поставок по каждому филиалу за каждый месяц. Действуем аналогично первому случаю за исключением того, что теперь в «Значения» мы перетаскиваем не «Количество товара», а «Сумма (т.р)» - сумма поставки. Опять же, при желании, мы можем в «Название строк» перенести к «Филиалы» еще и «Категория товаров». В полученной таблице рядом с «Филиал» будет стоять «+» - то есть кроме значений по филиалам, мы можем отдельно посмотреть значения по категориям товаров. На выходе получаем следующую сводную таблицу:
Сумма по полю Сумма (т.р.) Названия столбцов
Названия строк Март Апрель Май Июнь Общий итог
Филиал_1 1550 764 232 1403 3949
Филиал_2 1474 1686 1291 3426 7877
Филиал_3 2449 1750 1037 941 6177
Филиал_4 2080 1048 1027 3189 7344
Филиал_5 253 1574 1255 693 3775
Филиал_6 1276 595 649 232 2752
Филиал_7 1651 2088 423 4162
Общий итог 10733 9505 5914 9884 36036
Таким образом, Excel наглядно изобразил общие суммы поставок по каждому филиалу за каждый месяц.
Теперь перейдем к построению соответствующих временных рядов для каждого филиала. Для этого воспользуемся надстройкой Stat Pro, «Charts» и далее «Time Series Plots». Получаем следующую таблицу временных рядов для каждого филиала:
Для построения таблицы были намеренно избраны сумма поставки и сумма поступившей оплаты дабы нагляднее рассмотреть и соотнести ряды для каждого филиала.
В) постройте гистограмму для поступивших оплат для трех категорий поставки.
Перейдем к построению гистограммы. Опять же можем воспользоваться надстройкой Stat Pro, «Charts» и далее не «Time Series Plots», вкладка «Histograms». Выбираем «Оплачено (т.р)», далее в numbers of categories ставим 3, и в Minimum value и category length подставляем интересующие нас значения. В данном случае я подставил числа: 1000 и 5000. На выходе получили следующую гистограмму и таблицу частот:
Frequency table for Оплачено (т.р.)
Upper limit Category Frequency
1000 <=1000 36
6000 1000- 6000 57
>6000 23
Таким образом, мы построили гистограмму для поступивших оплат для трех категорий поставки.
Задание 3
Владелец ресторана европейской кухни заинтересовался особенностями заказов, принимаемых на выходные. Он стал записывать количество заказов на различные виды блюд. Предположим, что владельца ресторана интересует также, заказывают ли посетители десерт. Он решил записывать значения еще двух переменных: пол посетителя и заказывал ли он говядину. Результаты этих исследований приведены ниже.
Заказ десерта Мужской Женский Всего
Да 96 224 320
Нет 40 240 280
Всего 136 464 600
Заказ говядины
Да 71 116 187
Нет 65 348 413
Всего 136 464 600
Подготовьте модель (Excel: относительные, абсолютные ссылки) и вычислите:
1. Какова вероятность того, что первый же клиент закажет десерт?
2. Какова вероятность того, что первый клиент не закажет говядину?
3. Какова вероятность того, что первый клиент закажет десерт или говядину?
4. Какова вероятность того, что первый клиент окажется женщиной и не закажет десерт?
5. Какова вероятность того, что первый клиент закажет десерт и говядину?
6. Какова вероятность того, что первый клиент окажется женщиной и не закажет десерт?
7. Предположим, что первый клиент, у которого официант принял заказ, оказался женщиной. Какова вероятность того, что она не закажет десерт?
8. Предположим, первый же клиент заказал говядину. Какова вероятность, что он закажет и десерт?
9. Являются ли пол клиента и заказ десерта статистически независимыми?
10. Являются ли заказ десерта и заказ говядины статистически независимыми?
Ответы:
Итак, модель подготовлена следующим образом:
Заказ десерта Мужской Женский Всего
Да 96 224 320
Нет 40 240 280
Всего 136 464 600
Заказ говядины * * *
Да 71 116 187
Нет 65 348 413
Всего 137 464 600
Теперь перейдем к вычислениям:
1. Чтобы определить вероятность того, что первый клиент закажет десерт, воспользуемся следующим вычислением: 320/600. Получаем 0,5333. Полученное значение переводим в проценты. Вероятность заказа десерта составит 53%
2. Для определения вероятности того, что первый клиент не закажет говядину, производим вычисление: 413/600. Получаем 0,688. Переводим в проценты и получаем 69% - вероятность того, что клиент не закажет говядину.
3. Для определения вероятности того, что первый клиент закажет десерт или говядину, производим вычисления.
Определяем вероятность заказа десерта: 320/600=53%
Теперь определяем вероятность заказа говядины: 187/600=31%
Вероятность того, что клиент закажет десерт, значительно превышает вероятность заказа говядины. И так как суммой событий А и B называется событие А + B, которое наступает тогда и только тогда, когда наступает хотя бы одно из событий: А или B, сложим полученные результаты: 53% + 31% = 84%. Таково вычисление необходимой вероятности.
4. Вероятность того, что первый клиент окажется женщиной и не закажет десерт, составляет: вероятность того, что клиент окажется женщиной: 464/600=0,773 (77%), того, что не закажет десерт: 240/464=0,517 (52%).
Теперь, для того, чтобы вычислить общую вероятность, воспользуемся правилом умножения вероятностей (Р (А и В) = Р(А|В) * Р(В)) и умножаем первое значение на второе и получаем процентную вероятность: 40%.
5. Вероятность того, что первый клиент закажет десерт и говядину составляет: по приведенному выше примеру вычисляем сначала вероятность клиентом заказа десерта, затем вероятность заказа говядины и затем умножаем полученные значения. 17% - такова будет вероятность заказа клиентом и десерта, и говядины.
6. Вероятность того, что первый клиент окажется женщиной и не закажет десерт составляет: выясняем вероятность того, что первый клиент окажется женщиной (464/600) и вероятность того, что она не закажет десерт (240/464). Умножаем полученные значении и получаем желаемую вероятность: 40%
7. Итак, в этом случае мы имеем дело с зависимыми события. И будем производить вычисления по формуле P(A+B) = P(A) + P(B) — P(AB). Для этого произведем вычисления вероятности.
Первый клиент, у которого официант принял заказ, оказался женщиной.
Вероятность того, что это именно женщина – 77%. А вероятность, что она не закажет десерт, составляет (240/464) 52%. Теперь складываем полученные данные и вычитаем их из их же умножения (ниже приводится расчет). Получаем следующее значение – 89%.
8. Итак, мы имеем разный вид заказа, и если допустить, что эти события независимы, но совместны, то вероятность суммы событий равна: P(A) + P(B) — P(AB).
Произведем вычисления: Вероятность того, что клиент закажет говядину – 31%. Того, что закажет десерт – 53%. Их сумма составит 85%, а произведение 17%. Производим вычитание и получаемым ответ на интересующий вопрос – 68%
9. Событие А называется независимым от события B, если вероятность события А не зависит от того, произошло событие B или нет. Соответственно, пол клиента и заказ десерта являются статистически зависимыми.
10. Соответственно, в случае с заказом десерта и заказом говядины события являются статистически независимыми, так как вероятность их событий не зависят друг от друга.
Задача с первой по третью не нуждаются в иллюстрации.
Задание 4
В рабочей книге PIZZA.XLS содержатся данные о 36 порциях пиццы: стоимость в долларах, количество калорий и количество жира в граммах для трех категорий продуктов: сырной пиццы из пиццерии (тип 1), сырной пиццы из супермаркета (тип 2) и острой пиццы из супермаркета (тип 3).
Используйте инструмент Сводные таблицы и функции вычисления статистических характеристик.
Вычислите распределение частот и процентное распределение для стоимости, калорий и жирности.
Постройте кривую распределения (полигон накопленных процентов) для стоимости, калорий и жирности.
Изучите аналитически и графически взаимосвязь переменных.
Какие выводы можно сделать о стоимости, количестве калорий и жирности каждой из разновидностей пиццы?
Ответы:
1. Итак, начинаем вычисления с использования Сводных таблиц, функций вычисления статистических данных, а также надстройки Stat Pro. И для начала строим Сводные таблицы и исследуем распределения для стоимости, калорий и жирности. Для больших наборов данных следует создавать сводные таблицы, распределяя данные по группам (или категориям). Такой способ представления данных называется распределением частот. Распределение частот представляет собой сводную таблицу, в которой данные распределены по группам или категориям. Если данные сгруппированы в виде распределения частот, процесс их анализа и интерпретации становится более управляемым и осмысленным. При распределении частот следует внимательно выбирать интервал группирования, или размах групп, а также вычислять границы каждой группы, не допуская их перекрытия.
А. Для более наглядного рассмотрения предлагаю для начала рассмотреть распределение частот и процентное распределение для стоимости. Для этого используем инструмент «сводные таблицы». Выбираем массив данных, В «название строк» переносим «вид» и «название». В «значение» переносим» «цена». Получаем следующую таблицу для стоимости:
Сумма по полю Цена
Вид Название Итог
Chain 10,16
Cheese 16,97
Pepperoni DiGiorno Rising Crust Pizza Pepperoni 0,88
Freschetta Bakes & Rises Pepperoni Pizz 0,96
Healthy Choice Solos Pepperoni French Bread Pizza 1,62
Jeno's Crisp'n Tasty Pizza Pepperoni 0,74
Red Baron Classic Pepperoni Pizza 0,89
Red Baron Deep Dish Singles 2 Pepperoni Pizzas 1,28
Stouffer's French Bread Pizza 2 pepperoni 1,26
Tombstone Original Pepperoni Pizza 0,88
Tombstone Stuffed Crust Pepperoni Pizza 0,9
Tony's Original Crust Pepperoni Pizza 0,87
Totino's The Original Crisp Crust Party Pizza Pepperoni 0,64
Weight Watchers Smart Ones Bistro Selections Pepperoni Pizza 1,51
Pepperoni Итог 12,43
Общий итог 39,56
Для наглядного примера мной была раскрыта вкладка «Pepperoni». Точно также значения для названия отдельной пиццы можно посмотреть во вкладках «Chain» и «Cheese». Это удобно – можно наблюдать как общее значение категории\вида, так и отдельно взятой единицы продукции.
Теперь посмотрим на процентное распределение стоимости по трем категориям товара. Для этого изменяем значение, находим дополнительные вычисления и нажимаем на % от суммы по столбцу.
Вид Название Итог
Chain 25,68%
Cheese Amy's Organic Crust & Tomatoes Cheese Pizza 4,85%
Baboli Original Pizza Crust made with Bobali Sauce and mozzarella cheese 2,25%
Celeste Pizza for One Cheese Pizza 2,96%
DiGiorno Rising Crust Pizza Four Cheese 2,38%
Freschetta Bakes & Rises 4-Cheese Pizza 2,48%
Freschetta Bakes & Rises Sauce Stuffed Crust 4-Cheese Pizza 3,11%
Jack's Original Cheese Pizza 2,33%
Kroger Self Rising Crust Four Cheese Pizza 2,02%
McCain Ellio's Chesse Pizza 1,37%
Michelina's Zap 'ems That'za Pizza! Cheese 3,24%
Red Baron Classic 4 Cheese Pizza 2,30%
Reggio's Chicago Style Cheese Pizza 2,58%
Safeway Select Verdi Quattro Formaggio Self Rising Crust Pizza 2,12%
Tombstone Stuffed Crust Cheese Pizza 2,43%
TombstoneOriginal Extra Cheese Pizza 2,38%
Tony's Super Rise Crust Four-Cheese Pizza 2,43%
Totino's The Original Crisp Crust Party Pizza Cheese 1,69%
CheeseИтог 42,90%
Pepperoni 31,42%
ОбщийИтог 100,00%
Получили следующую таблицу процентного распределения. В дальнейшем (в сводных таблицах) мной будут (для удобства) показаны только процентные распределения.
Б. Теперь строим аналогичную таблицу для распределения калорий (вместо «ценна» в «значение» подставляем «калории» и переводим в процентное распределение).
Вид Название Итог
Chain Domino's Hand Tossed Pepperoni Pizza 2,83%
Donimo's Deep Dish Pepperoni Pizza 3,09%
Little Caesars Round Pepperoni Pizza 2,53%
Papa John's Original Crust Pepperoni Pizza (28 oz) 3,00%
Papa John's Original Crust Pepperoni Pizza (37 oz) 2,53%
Pizza Hut Pan Pizza Pepperoni 2,98%
Pizza Hut Thin & Crispy Pepperoni Pizza 2,76%
Chain Итог 19,73%
Cheese 45,53%
Pepperoni 34,73%
Общий итог 100,00%
В. Аналогично поступаем для построения сводной таблицы касаемо жиров.
Вид Название Итог
Chain 20,82%
Cheese 41,10%
Pepperoni 38,08%
Общий итог 100,00%
Здесь приведены общие значения по трем категориям продукта. Также можем посмотреть каждое наименование пиццы в отдельности.
В. Также можно построить одну таблицу для процентного распределения для стоимости, калорий и жирности. Выглядеть она будет так:
2. Теперь перейдем к построению гистограмм, которая внешне похожа на график нормального распределения и при увеличении количества измерений приближается к графику нормального распределения (распределения Гаусса).
Итак, переходим во вкладку «Анализ данных» и выбираем «Гистограмма». Выбираем входной интервал – в данном случае выделяем колонку «Цена». Здесь же предлагается выбрать интервал карманов. Мы можем оставить интервал пустым – программа вычислит за нас. Далее ставим галочку «интервальный процент» и «вывод графика». Жмем Ок.
Получили гистограмму для стоимости. На вертикальной оси изображена абсолютная чистота. Ее можно изменить на относительную. Для этого под появившейся таблицей со столбцами «Карман» и «Частота» под столбцом «Частота» вводим формулу «=СУММ» и складываем абсолютные частоты. Далее добавляем рядом столбик «Относительная частота». Во всех ячейках нового столбца вводим формулу, которая будет рассчитывать относительную частоту: 100 множим на абсолютную частоту и делим на сумму, которую вычислили изначально («=СУММ» и складываем абсолютные частоты). Приводить наглядно не станем.
Аналогичные диаграммы приводим для калорий и жиров соответственно. К широте использования инструментария ставим галочку «Парето» для наблюдения отсортированной гистограммы:
Видим, что интегральный процент калорий и жиров возрастает – тренд возрастающий, вид гиперболы.
3. Теперь рассмотрим зависимость калорий от жира в целом во всех пиццах. Для этого используем надстройку Stat Pro, Charts, Scatterplots, вводим массив данных и получаем следующую зависимость:
Correlation = 0,936 – корреляция практически равна единице, что говорит о полной зависимости Жиров и калорий.
4. Теперь рассчитываем обобщающие характеристики переменных. Строим распределения по стоимости, калориям и жирности. Воспользуемся надстройкой Stat Pro\Summary stats\One-variable Summary stats. Далее выбираем все три значения и получаем следующие данные:
Summary measures for selected variables
Цена Калории Жир
Count 36,000 36,000 36,000
Mean 1,099 351,806 15,611
Median 0,960 354,000 14,500
Standard deviation 0,329 35,733 5,156
Minimum 0,540 280,000 4,000
Maximum 1,920 412,000 26,000
Range 1,380 132,000 22,000
First quartile 0,888 327,250 12,750
Third quartile 1,280 378,500 19,250
Из таблицы наблюдаем средние значения, стандартные отклонения, первый и третий квартили и тд.
Итог:
Итак, мы изучили аналитически и графически взаимосвязь переменных. Можно сказать, что количество калорий варьируется с учетом вида и процента жирности. Стоимость также варьируется в зависимости от вида. Также видно из графика, что калории и жиры имеют полную зависимость друг от друга, чего нельзя сказать о стоимости скажем относительно калорий. В этом случае корреляция будет низкой. С исследуемых данных были приведены автоматические инструменты надстроек, а также такие инструменты как (мода, медиана и проч.)