Корреляционный анализ и составление прогнозов
Перенесите в Excel нижеследующую таблицу в свою рабочую книгу на отдельный лист. Присвойте этому листу имя «корреляция».
Средняя зарплата | Количество преступлений | Количество ДТП |
Проведите корреляционный анализ данных и объясните его результаты.
Для этого используйте инструмент “ Корреляция ” из приложения “ Анализ данных ”.
Для доступа к инструментам нажмите кнопку “ Анализ данных” в группе “ Анализ ” на вкладке “ Данные ”.
Если кнопка “ Анализ данных ” недоступна, необходимо загрузить надстройку «Пакет анализа » следующим образом:
-нажмите кнопку “ Microsoft Office ”, а затем щелкните “ Параметры Excel ”;
-перейдите на вкладку “ Надстройки ”, а затем в поле “ Управление ” выберите “ Надстройки Excel ”;
-нажмите кнопку “ Перейти ”;
-в поле “ Доступные надстройки ” установите флажок “ Пакет анализа ”, а затем нажмите кнопку ОК.
На отдельном листе постройте диаграммы, а именно гладкие графики зависимостей количества преступлений и количества ДТП от уровня зарплаты.
Удалите кривую зависимости количества преступлений.
Составьте прогноз количества преступлений для величин зарплаты 550 и 600. Используйте для этого функцию “ТЕНДЕНЦИЯ”.
Совет! Для ввода формулы массива при работе с функцией “ТЕНДЕНЦИЯ” нажмите комбинацию клавиш <Ctrl+Shift+Enter>.
Оба прогноза отразите на одном графике.
Перенесите в свою рабочую книгу (или создайте самостоятельно) приведенную ниже таблицу.
Недели | Кол-во заказов |
Составьте прогноз поступления заказов на 11-ю и 12-ю недели. Используйте вначале функцию “ТЕНДЕНЦИЯ”, а затем функцию “РОСТ”.
Совет! Для ввода формулы массива при работе с функциями нажмите комбинацию клавиш <Ctrl+Shift+Enter>.
Постройте на отдельном листе три графика: Количество заказов, Тенденция, Рост. Присвойте этому листу имя "Прогнозы".
Построение гистограмм встречаемости
На основе приведенных ниже данных постройте гистограмму, отображающую количество двоек, троек, четверок и пятерок, полученных студентами на экзаменах. Используйте для этого инструмент “ГИСТОГРАММА” из приложения “ Анализ данных ”.
Группа1 |
Оценки |
Для приведенного ряда оценок рассчитайте значения следующих статистических параметров: СЧЕТ, МЕДИАНА, МОДА. Используйте для этого одноименные статистические функции.
Контроль качества продукции
Вы заказали 200 кофейных чашек с росписью, содержащей логотип вашей компании. Эта партия товара будет отличаться от других, поэтому данные 200 чашек составляют конечную генеральную совокупность. Согласно контракту допускается 5-процентный коэффициент брака в партии.
Вы планируете произвести выборку размером 20 чашек и забраковать всю партию, если более чем в 5% выборки обнаружите недопустимые дефекты. Какова вероятность того, что качество всей партии будет соответствовать требуемым критериям и содержать не более 5% брака?
Воспользуйтесь функцией Excel “ГИПЕРГЕОМЕТ”. Эта функция возвращает вероятность заданного количества успехов в выборке, если заданы размер выборки, количество успехов в генеральной совокупности и размер генеральной совокупности.
Применение доверительных интервалов
Предположим, ваша компания рассматривает вопрос о приобретении участка для нового магазина. Очень важным критерием при принятии решения является место положения магазина, т.е. насколько оно многолюдно. Чтобы выяснить это, вы каждый день в течение двух недель подсчитываете, сколько пешеходов проходит мимо магазина. Результаты этих наблюдений составляют выборку из генеральной совокупности всех возможных дней, в течение которых магазин будет оставаться вашей собственностью. Результаты наблюдений показаны ниже:
Кол-во пешеходов |
С помощью инструмента анализа данных “ОПИСАТЕЛЬНАЯ СТАТИСТИКА” рассчитайте значение доверительного интервала при уровне надежности (другое название этого фактора точность оценки) 95%.
Финансовые вычисления и функции
Амортизация
Создайте новый лист в вашей рабочей книге с названием "Амортизация". На этом листе оформите результаты вычисления амортизационных отчислений.
Строительная фирма приобрела станок за 58000 руб. Срок службы этого станка 8 лет. Остаточная стоимость станка через 8 лет равна 4000 руб. Составьте таблицу амортизационных отчислений и стоимости станка по годам, если амортизация равномерная.
Для решения данной задачи воспользуйтесь финансовой функцией "АПЛ".
Расчеты представьте в следующем виде:
Данные: | ||
Начальная стоимость | 58 000,00р. | |
Остаточная стоимость | 4 000,00р. | |
Срок службы | ||
Равномерная амортизация | ||
Год службы | Амортизационные отчисления | Стоимость на конец года |
0,00р. | 58 000,00р. | |
6 750,00р. | 51 250,00р. | |
6 750,00р. | 44 500,00р. | |
6 750,00р. | 37 750,00р. | |
6 750,00р. | 31 000,00р. | |
6 750,00р. | 24 250,00р. | |
6 750,00р. | 17 500,00р. | |
6 750,00р. | 10 750,00р. | |
6 750,00р. | 4 000,00р. |
Для тех же исходных данных рассчитайте ускоренную амортизацию по правилу суммы лет. Воспользуйтесь финансовой функцией "АСЧ".
Вы должны получить следующие результаты:
Амортизационные отчисления по правилу суммы лет | ||
Год службы | Амортизационные отчисления | Стоимость на конец года |
0,00р. | 58 000,00р. | |
12 000,00р. | 46 000,00р. | |
10 500,00р. | 35 500,00р. | |
9 000,00р. | 26 500,00р. | |
7 500,00р. | 19 000,00р. | |
6 000,00р. | 13 000,00р. | |
4 500,00р. | 8 500,00р. | |
3 000,00р. | 5 500,00р. | |
1 500,00р. | 4 000,00р. |