И(логическое_значение1; логическое_значение2;)

⇐ ПредыдущаяСтр 2 из 2

где Логическое_значение1, логическое_значение2, ... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

· для выполнения расчета необходимо нажать кнопку Ок.

5. С помощью кнопки Сохранить необходимо сохранить изменения в документе.

Статистические функции Excel

Задание 3. Известны следующие статистические данные о деятельности хозяйственных судов по осуществлению правосудия в области хозяйственных (экономических) отношений:

Месяц	Количество обращений субъектов хозяйствования в хозяйственные суды
Месяц	исковые заявления (заявления, материалы)	заявления о судебном приказе
Январь	21646	1852
Февраль	19794	1814
Март	16494	2745
Апрель	23395	3030
Май	25158	3746
Июнь	22413	3030
Июль	21842	2156
Август	14680	2569
Сентябрь	22877	1960
Октябрь	23395	1460
Ноябрь	23912	1690
Декабрь	24430	1520

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

Рекомендации к выполнению:

1. На третьем листе рабочей книге Лабораторная paбoтa4.xls необходимо создать проект таблицы следующего вида:

2. Для вычисления среднего в ячейку В15 необходимо вставить статистическую функцию СРЗНАЧ и указать в качестве ее аргумента интервал ячеек ВЗ:В14. Для этого можно проделать следующее:

· выделить ячейку В15 и выполнить команду Вставка - Функция;

· на первом шаге мастера функций необходимо из списка категорий выбрать Статистические, а в списке функций выбрать СРЗНАЧ и нажать кнопку Ок;

· на втором шаге мастера функций в строке для аргумента Число1 необходимо указать интервал ячеек ВЗ:В14, выделив его на рабочем листе. Для завершения работы мастера функций и расчета необходимо нажать кнопку Ок;

· полученную формулу надо скопировать в ячейку С15 одним из способов (через буфер обмена или с помощью операции заполнения смежных ячеек).

Примечание. Функция СРЗНАЧ возвращает среднее (арифметическое) своих аргументов. Синтаксис ее следующий:

СРЗНАЧ(число1; число2;...)

где число 1, число2, ... - это от 1 до 30 аргументов, для которых вычисляется среднее.

3. Для вычисления медианы в ячейку В16 нужно вставить статистическую функцию МЕДИАНА и указать в качестве ее аргумента интервал ячеек ВЗ:В14, а затем скопировать полученную формулу в ячейку С16.

Примечание. Функция МЕДИАНА возвращает медиану заданных чисел - число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеет значения меньшие, чем медиана. Синтаксис функции: МЕДИАНА(число1;число2;...)

где число1, число2, ... - это от 1 до 30 чисел, для которых определяется медиана.

4. Для вычисления моды в ячейку В17 нужно вставить статистическую функцию МОДА и указать в качестве ее аргумента интервал ячеек ВЗ:В14, а затем скопировать полученную формулу в ячейку С17.

Примечание. Функция МОДА возвращает наиболее часто встречающееся или повторяющееся значение в массиве или интервале данных. Синтаксис функции:

МОДА(число1;число2;...)

где число 1, число2, ... - это от 1 до 30 аргументов, для которых вычисляется мода.

5. Для вычисления максимального значения в ячейку В18 нужно вставить статистическую функцию МАКС и указать в качестве ее аргумента интервал ячеек ВЗ:В14, а затем скопировать полученную формулу в ячейку С18.

Примечание. Функция МАКС возвращает наибольшее значение из набора значений. Синтаксис функции:

МАКС(число1;число2;...)

где число 1, число2, ... - это от 1 до 30 чисел, среди которых ищется максимальное значение.

6. Для вычисления минимального значения в ячейку В19 нужно вставить статистическую функцию МИН и указать в качестве ее аргумента интервал ячеек ВЗ:В14, а затем скопировать полученную формулу в ячейку С19.

Примечание. Функция МИН возвращает наименьшее значение из набора значений. Синтаксис функции:

МИН(число1;число2;...)

где число 1, число2, ... - это от 1 до 30 чисел, среди которых ищется минимальное значение.

7. Для вычисления дисперсии в ячейку В20 нужно вставить статистическую функцию ДИСП и указать в качестве ее аргумента интервал ячеек ВЗ:В14, а затем скопировать полученную формулу в ячейку С20.

Примечание. Функция ДИСП оценивает дисперсию по выборке. Синтаксис функции:

ДИСП(число1;число2;...)

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

8. Для определения ранга поступлений исковых заявлений в суды в ячейку D3 нужно вставить статистическую функцию РАНГ и указать в качестве аргумента Число ячейку ВЗ, а в качестве аргумента Ссылка интервал ячеек ВЗ:В14 (также воспользоваться клавишей F4 для задания абсолютной ссылки на интервал ячеек). Затем необходимо скопировать полученную формулу в ячейки D4:D14. Аналогично нужно определить ранг поступлений заявлений о судебном приказе в ячейках ЕЗ:Е14.

Примечание. Функция РАНГ .возвращает ранг числа в списке чисел. Ранг числа - это его величина относительно других значений в списке; если список отсортировать, то ранг числа будет его позицией. Синтаксис функции:

РАНГ(число;ссылка;порядок)

где число - это число, для которого определяется ранг;

ссылка - это массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются;

порядок - это число, определяющее способ упорядочения. Если порядок равен 0 (нулю) или опущен, то Microsoft Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания.

9. С помощью кнопки Сохранить необходимо сохранить изменения в документе и завершить работу с Microsoft Excel.

ЛАБОРАТОРНАЯ РАБОТА №8. ТАБЛИЧНЫЙ

ПРОЦЕССОР MICROSOFT EXCEL. ГРАФИЧЕСКИЙ

АНАЛИЗ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ

ДИАГРАММ

Цель лабораторной работы: освоить построение и редактирование диаграмм различных типов на основе табличных данных, изменение и редактирование диаграмм.

8.1. Построение гистограмм и круговых диаграмм

Задание 1. На основе таблицы, разработанной в лабораторной работе №7 (задание i), построить:

гистограмму, показывающую количество единиц товара, возмещаемого в результате нанесенного ущерба, по видам ущерба;

круговую диаграмму на новом листе рабочей книги, показывающую в долях соотношение сумм ущерба по видам ущерба.

Рекомендации к выполнению:

1. Для создания диаграмм необходимо создать таблицу, содержащую
анализируемые данные (выполнить задание 1 лабораторной работы №7).
Если данное задание было выполнено, то необходимо запустить Microsoft
Excel и на первый лист новой рабочей книги скопировать созданную и
рассчитанную таблицу, выполнив следующее:

с помощью команды Файл - Открыть (или кнопки Открыть на панели инструментов Стандартная) открыть файл Лабораторная pa6oma7.xls, хранящийся в своей папке на диске D:;

выделить таблицу (интервал ячеек A1.-F11) и выполнить команду Правка - Копировать (или нажать кнопку Копировать на панели инструментов Стандартная);

• с помощью кнопки Создать на панели инструментов Стан-
ш дартная необходимо создать новую пустую рабочую книгу;

• на первом листе рабочей книги выделить ячейку А1 и выпол
нить команду Правка - Вставить (или нажать кнопку Вста
вить на панели инструментов Стандартная). При этом начиная
с ячейки А1 будет вставлена таблица с исходными данными,
расчетами и элементами форматирования,

2. Для построения гистограммы необходимо выполнить следующее.

выделить интервал ячеек D5:D10. который будет отображен на диаграмме;

выполнить команду Вставка - Диаграмма (или нажать кнопку Мастер диаграмм на панели инструментов Стандартная):

на первом шаге мастера диаграмм выбрать Тип: Гистограмма и Вид: на свое усмотрение (для большей наглядности можно вы-

брать Объемный вид обычной диаграммы). Для перехода на следующий шаг мастера необходимо нажать кнопку Далее>;

на втором шаге мастера диаграмм на вкладке Ряд установить щелчком мыши курсор поле Подписи оси X: и выделить интервал ячеек В5:В10. Для перехода на следующий шаг мастера необходимо нажать кнопку Далее>;

на третьем шаге мастера диаграмм на вкладке заголовки можно ввести название диаграммы (например, Возмещаемый ущерб в товарных единицах), заголовок оси X (например, Виды ущерба) и заголовок оси Z (например, Количество товара). На вкладке Легенда можно отключить параметр Добавить легенду, так как в данном случае легенда не несет в себе смысла. На вкладке Подписи данных можно включить параметр значение для отображения значений таблицы на диаграмме. Для перехода на следующий шаг мастера необходимо нажать кнопку Далее>;

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

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

выделить интервал ячеек Е5:Е10, который будет отображен на диаграмме, и вызвать мастер диаграмм;

на первом шаге мастера диаграмм выбрать Тип: Круговая и Вид: на свое усмотрение (для большей наглядности можно выбрать Объемный вариант разрезанной круговой диаграммы);

на втором шаге мастера диаграмм на вкладке Ряд указать в качестве подписей категорий интервал ячеек В5:В10;

на третьем шаге мастера на вкладке Заголовки ввести на звание диаграммы, а на вкладке Подписи данных включить параметр доля;

на четвертом шаге мастера диаграмм необходимо активизировать переключатель отдельном для указания местоположения диаграммы и нажать кнопку Готово.

4. С помощью команды Файл - Сохранить как... документ необхо
димо сохранить в своей папке на диске D: под именем Лабораторная ра
ботав.

8.2. Построение графиков функций

Задание 2. Построить график функции v — cos(tzx) • д;' при jc в [-16:16].

Рекомендации к выполнению:

1. Для построения графика необходимо вначале на втором листе рабочей книги рассчитать значения функции на заданном интервале с опредс-

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

• ввести в ячейку А1 число -7,6;

i • сделать ячейку А1 текущей и выполнить команду Правка — Заполнить - Прогрессия;

• в окне Прогрессии указать расположение по столбцам, Шаг: 0,2,
Тип: арифметическая, Предельное значение: 1,6 и нажать Ок.

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

сделать текущей ячейку В1 и вызвать мастер функций;

на первом шаге мастера выбрать категорию Математические и функцию COS;

на втором шаге мастера для вставки вложенной функции необходимо раскрыть список функций возле строки формул следующим образом:

из списка необходимо выбрать пункт Другие функции..., а затем в мастере функций снова выбрать категорию Математические и функцию ПИ и нажать Ок для вставки функции;

для возврата из вложенной функции необходимо в строке формул выполнить щелчок левой кнопкой мыши по функции COS и добавить недостающие аргументы, чтобы запись в строке Число выглядела следующим образом:

ПИ()*А1

• после этого можно нажать Ок в окне мастера функций и отре
дактировать формулу, чтобы она приняла следующий вид:

-COS(nn()*Al)*(Al Л3)

• для построения ряда значений функции необходимо скопиро
вать созданную формулу в ячейки В2:В17.

3. Для построения графика функции необходимо выполнить следующее:

выделить интервал ячеек В1: В17, содержащий значения функции;

вызвать мастер диаграмм;

на первом шаге мастера выбрать Тип: График и Вид: на свое усмотрение;

на втором шаге мастера диаграмм на вкладке Ряд установить щелчком мыши курсор поле Подписи оси X: и выделить интервал ячеек А1: А17, содержащий значения аргумента;

на третьем шаге мастера необходимо озаглавить оси (X и Y) и отключить легенду;

на четвертом шаге мастера диаграмм необходимо активизировать переключатель имеющемся и нажать кнопку Готово.

4. С помощью кнопки Сохранить необходимо сохранить изменения в документе.

[л/х, х > О
Задание 3. Построить график функции I при

У ~ 1 2 + х

, х<09

[ е~х '

xe[-l;l].

Рекомендации к выполнению:

На третьем листе рабочей книги аналогично, как и в предыдущем задании, постройте ряд значений х в интервале ячеек А1-.А21 (арифметическая прогрессия от -1 до 1 с шагом ОД).

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

сделать текущей ячейку В1 и вызвать мастер функций;

на первом шаге мастера выбрать категорию Логические и функцию ЕСЛИ;

• на втором шаге мастера, пользуясь вложенными функциями,
необходимо задать аргументы функции следующим образом:

для расчета следует нажать Ок;

для построения ряда значений функции необходимо скопировать созданную формулу в ячейки В2:В21.

Построение графика функции осуществляется аналогично как в предыдущем задании по интервалу ячеек В1:В21? в качестве подписей оси X следует использовать интервал А1:А21.

С помощью кнопки Сохранить необходимо сохранить изменения в документе.

8.3. Построение поверхностей Задание4. Построить поверхность z = sin X + еу при х,у е [-1;l].

Рекомендации к выполнению:

I 1. Вставьте еще один лист в рабочую книгу с помощью команды Вставка - Лист.

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

в ячейку А2 необходимо ввести значение -7, а в ячейку A3 -значение -0,8. Выделив интервал ячеек А2:АЗ и затем нажав левую кнопку мыши на маркере заполнения и протянув до ячейки А127 можно получить ряд значений переменной у с шагом 0,2;

аналогично в ячейку В1 необходимо ввести значение -7, а в ячейку С1 - значение -0,8. Выделив интервал ячеек В1:С1 и за-

* тем нажав левую кнопку мыши на маркере заполнения и протя-

нув до ячейки L1, можно получить ряд значений переменной х с шагом 0,2. #■ 3. Для построения значений функции z необходимо в ячейке В2, пользуясь мастером функций и заданием абсолютных ссылок, создать следующую формулу:

=(SIN($A2))A2+EXP(B$1)

Формулу необходимо скопировать в ячейки В2:Ы2.

Для построения поверхности необходимо выполнить следующее:

выделить интервал ячеек A1.-L12, содержащий значения аргументов и функции:

вызвать мастер диаграмм:

на первом шаге мастера выбрать Тип: Поверхность;

на втором шаге мастера диаграмм на вкладке Диапазон данных активизировать переключатель Ряды в столбцах;

на третьем шаге мастера необходимо озаглавить оси (X, Y и Z) и отключить легенду:

6. С помощью кнопки Сохранить необходимо сохранить изменения в
документе и завершить работу с Microsoft Excel.

ЛАБОРАТОРНАЯ РАБОТА №9. ТАБЛИЧНЫЙ

ПРОЦЕССОР MICROSOFT EXCEL. СОЗДАНИЕ

И ОБРАБОТКА СПИСКОВ

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

Задание. Имеются следующие данные о количестве заключенных договоров охраны за три месяца по районам г. Минска:

Месяц	Район	Количество договоров
Январь	Заводской	2582
Январь	Ленинский	2154
Январь	Московский	3080
Январь	Октябрьский	1883
Январь	Партизанский	1346
Январь	Первомайский	2570
Январь	Советский	2680
Январь	Фрунзенский	2888
Январь	Центральный	2034
Февраль	Заводской	2349
Февраль	Ленинский	2347
Февраль	Московский	2345
Февраль	Октябрьский	2344
Февраль	Партизанский	2342
Февраль	Первомайский	2340
Февраль	Совете кий	2339
Февраль	Фрунзенский	2337
Февраль	Центральный	2335
Март	Заводской	2333
Март	Ленинский	2332
Март	Московский	2330
Март	Октябрьский	2328
Март	Партизанский	2327
Март	Первомайский	2325
Март	Советский	2323
Март	Фрунзенский	2321
Март	Центральный	2320

Требуется:

проанализировать информацию по районам и месяцам;

определить месяцы, в которых количество заключенных договоров было меньше 2000;

отсортировать и сгруппировать данные по районам;

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

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

Рекомендации к выполнению:

Для создания документа необходимо запустить Microsoft Excel и на первом листе новой рабочей книги создать проект таблицы (начиная с ячейки А1) и оформить его на свое усмотрение.

Просмотр и редактирование списка возможен в режиме формы, для чего необходимо выполнить команду Данные - Форма и проделать следующее:

просмотреть список с помощью кнопок Назад и Далее;

задать критерий отбора данных списка - нажав кнопку Критерии и указав для поля Количество договоров: критерий <1000, а затем просмотрев отобранные данные;

для завершения работы с формой необходимо нажать кнопку Закрыть.

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

выполнить команду Данные - Фильтр - Автофильтр;

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

для вьюода всех записей списка необходимо выбрать пункт (Все),

аналогично можно отфильтровать записи по районам, а также с помощью пункта (Условие...) для поля Количество договоров указать следующее условие:

• отобразить все записи списка можно с помощью команды Дан
ные - Фильтр - Отобразить все.

4. Для проведения дальнейшего анализа списка его можно скопиро
вать на новый лист со связью, выполнив следующие действия:

выделить интервал ячеек Al:C28 и скопировать его в буфер обмена;

перейти на Лист2, выделить ячейку А1 и выполнить команду Правка - Специальная вставка и нажать кнопку Вставить связь;

снова скопировать список на первом листе в буфер обмена и на втором листе, активизировав ячейку А1, выполнить команду Правка -Специальная вставка и включить переключатель Форматы.

Для сортировки списка по районам необходимо воспользоваться командой Данные - Сортировка, где указать поле для сортировки (Район) и критерий сортировки по возрастанию.

Для группировки списка по районам и подсчета итогов необходимо воспользоваться командой Данные - Итоги, задав следующие параметры расчета итогов:

Аналогично, повторно выполнив команду Данные - Итоги, можно рассчитать средние значения, выбрав операцию Среднее.

Поэкспериментировав со структурой списка, необходимо привести его к следующему виду:

9. Для определения районов и месяцев, в которых количество заклю
ченных договоров было больше среднего по городу, необходимо восполь
зоваться вышеприведенными рекомендациями и скопировать со связью
полученные данные на ЛистЗ.

10. Документ необходимо сохранить в своей папке на диске D: под
именем Лабораторная работая и завершить работу с Microsoft Excel.

ЛАБОРАТОРНАЯ РАБОТА №10. ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL. РЕШЕНИЕ ПРОСТЫХ ЗАДАЧ ТЕОРИИ ВЕРОЯТНОСТЕЙ

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

Задание 1. Список экзаменационных вопросов содержит 52 вопро> са. Студент знает 37 вопросов. Какова вероятность того, что вытянув билет, содержащий 3 вопроса, студент ответит верно на все из них?

Рекомендации к выполнению: 1 способ:

1. Создайте в MS Excel таблицу следующего вида:

Примечание. Функция ЧИСЛКОМБ возвращает количество комбинаций для заданного числа объектов. Функция ЧИСЛКОМБ используется для определения числа всех возможных сочетаний объектов в группы. Синтаксис функции следующий:

ЧИСЛКОМБ(число; число_выбранных)

где Число - это число объектов;

Числовыбранных - это число объектов в каждой комбинации.

Общее число элементарных событий равно числу сочетаний С"1. где

п - общее количество вопросов (n=52), a m - количество вопросов билета (т=3). Число благоприятствующих исходов равно выборке 3 вопросов ю

количества тех. которые студент знает, т.е. С^7 (в MS Excel - ЧИСЛ-

КОМБ(37;3)).

Число благоприятствующих-исходов

> Искомая вероятность = ~— '-

Общее число элементарных событии

Сохраните файл в своей папке под именем Лабораторная работа 10,

Переименуйте лист в Задача! (1 способ).

Скопируйте диапазон ячеек А1:ВЗ на Лист2.

2 способ: 1. Создайте таблицу следующего вида:

Число элементарных событий уменьшилось на 1, потому, что на один вопрос уже ответили, а число благоприятных исходов уменьшилось на 1, потому, что на него студент ответил верно.

2. Сравните полученные результаты. Переименуйте лист в Задание! (2-й способ).

Задание 2. В архиве следственного отдела зафиксировано 70 уголовных дел, из которых раскрытыми являются 13. Какова вероятность того, что среди взятых из архива 20 дел ровно 3 окажутся раскрытыми?

Рекомендации к выполнению:

1. Для реализации решения задачи создайте в MS Excel таблицу следующего вида:

Примечание. Общее число возможных элементарных исходов равно числу способов, которыми можно извлечь 20 дел из 70, т.е.

Су® = ЧИСЛКОМБ(70;20). Число благоприятствующих исходов равно числу способов взять 3 раскрытых дела из 13 (С13), при этом из остальных (70-13) дел (20-3) должны быть нераскрытыми (С7"0ЧЗ). Искомая ве-

^13 ^'70-13

роятность равна —-—^р— .

2. Переименуйте лист в Задаиие2. Сохраните файл в своей папке.

Задание 3. Ценные бумаги выпускают два предприятия, причем первое предприятие покрывает 80% рынка ценных бумаг, а второе - 20%. Известно также, что 25% ценных бумаг, выпускаемых первым предприятием - фальшивые, для второго предприятия - 70% выпускаемых ценных бумаг фальшивые. Какова вероятность того, что наудачу выбранная ценная бумага будет фальшивая? С какой вероятностью она будет принадлежать:

а) первому;

б) второму предприятию.

Рекомендации к выполнению:

1. Для реализации решения задачи создайте в MS Excel таблицу следующего вида:

Примечание. В ячейках В5 и В6 содержатся формулы условной вероятности Рн (А)7 Р?и(А).

Ячейка С5 содержит формулу полной вероятности

Р(А) = Р(Н1ГРН1(А) + Р{Н1)*РН!(А).

В В8 и В9 используется формула Байеса

Р{НХ)*РН{А)

2. Переименуйте лист в ЗаданиеЗ.

Задание 4. В ходе проверки аудитор случайным образом отбирает 10 счетов. Найти вероятность того, что он обнаружит 1 счет с ошибкой, если в среднем 3% счетов содержат ошибки.

Рекомендации к выполнению:

1. Для реализации решения задачи создайте в MS Excel таблицу следующего вида:

Примечание. Функция ПУАССОН возвращает распределение Пуассона. Обычное применение распределения Пуассона состоит в предсказании количества событий, происходящих за определенное время, например, количество машин, появляющихся на площади за 1 минуту. Синтаксис функции следующий:

ПУАССОН(х;среднее;1штегральная)

где X - это количество событий:

Среднее - это ожидаемое численное значение;

Интегральная - это логическое значение, определяющее форму возвращаемого распределения вероятностей. Если аргумент интегральная имеет значение ИСТИНА, то функция ПУАССОН возвращает интегральное распределение Пуассона, то есть вероятность того, что число случайных событий будет от 0 до х включительно; если этот аргумент имеет значение ЛОЖЬ, то возвращается функция плотности распределения Пуассона, то есть вероятность того, что событий будет в точности х.

В данном случае требуется найти вероятность пг=1 успеха из п=10 испытаний Бернулли с вероятностью успеха р=0,03. По формуле Бернулли

S~y 111 «A? 111 jt- /-| \ П П!

эта вероятность = ( n p (1 - p) - однако, если число испытаний велико, вероятность успеха мала, а произведение п*р<20. то используется

приближенная формула Пуассона. При этом используется статистическая функция, реализующая формулу Пуассона, где первый параметр - это сколько счетов с ошибкой мы хотим обнаружить, а среднее ожидаемое значение - это среднее количество счетов с ошибкой, т.е. 10*0,03. 2. Переименуйте лист в Задание4.

Задание 5. Кандидата в высший орган власти поддерживает 65% населения. Число избирателей равно 1 500 000. С какой вероятностью число проголосовавших «за» на выборах находится в пределах от 974500 до 976500.

Рекомендации к выполнению:

1. Для реализации решения задачи создайте в MS Excel таблицу следующего вида:

Примечание. Функция НОРМСТРАСП возвращает стандартное нормальное интегральное распределение. Это распределение имеет среднее, равное нулю, и стандартное отклонение, равное единице. Эта функция используется вместо таблицы для стандартной нормальной кривой Синтаксис функции следующий:

НОРМСТРАСП(г),

где Z - это значение, для которого строится распределение.

Вероятность того, что число m появления события А (проголосуют «за») в схеме Бернулли находится в заданном промежутке а <т <Ь, при большом числе испытаний п приблизительно равна

(i \ ( \

Л b-np Jfl- tip Ф\ —J=- \-Ф\ ~=J~ (п=150000(). а=974500. Ь=976500, р=0.65,

I 4"рч ) I <прч )

ч ~\-р) Функция Лапласа в Excel и реализована в статистической функции НОРМСТРАСП.

2. Переименуйте лист в Задание5.

на»™ fo% ™™ "' fЗГ™™™ УГ"агашх ™»<*"лей удается

1 п Рекомендации к выполнению:

Дующе^™™ РВЖШЯ 3адЗЧИ С0ЗДаЙТе В MS Excel ™йшЧУ еле-

Примечание. Функция БИНОМРАСП возвращает отдельное значение биномиального распределения. Функция БИНОМРАСП используется в задачах с фиксированным числом тестов или испытаний, когда результатом любого испытания может быть только успех или неудача, испытания независимы, и вероятность успеха постоянна на протяжении всего эксперимента. Синтаксис функции следующий:

БИНОМРАСП (чисг10_успехов; числоиспытшнш; вероятность_успеха;

интегральная)

где Число успехов - это количество успешных испытаний;
> Число испытаний - это число независимых испытаний;

Вероятность успеха - это вероятность успеха каждого испытания;

Интегральная - это логическое значение, определяющее форму функции. Если аргумент интегральная имеет значение ИСТИНА, то функция БИНОМРАСП возвращает интегральную функцию распределения, то есть вероятность того, что число успешных испытаний не менее значения аргумента число_успехов; если этот аргумент имеет значение ЛОЖБ. то возвращается

функция распределения, то есть вероятность того, что число успешных испытаний в точности равно значению аргумента число_успехов.

Закон распределения этой дискретной случайной величины может быть задан схемой испытаний Бернулли, а именно биномиальным распределением. 11исло испытаний в данной задаче = 5, а вероятность успеха - это число, равное проценту нахождения угнанных автомобилей, т. е. - 0,6. Математическое ожидание находится по формуле МХ=п*р, где п - Число иащгдний, a DX=n*p*q, где q^l-p. Среднее квадратическое отклонение - с = 2. Переименуйте лист в Задание б

Задание 7. Текущая цена ценной бумаги - нормально распределенная случайная величина со средним 100 у.е. и дисперсией 9. Найти значения функции распределения на интервале от 91 до 109 и построить график плотности вероятности. Найти вероятность того, что цена актива будет находиться в пределах от 95 до 105 у.е.

Рекомендации к выполнению:

1. Для реализации решения задачи создайте в MS Excel таблицу следующего вида:

Примечание. Функция НОРМРАСП возвращает нормальную функцию распределения для указанного среднего и стандартного отклонения. Эта функция имеет очень широкий круг приложений в статистике, включая проверку гипотез. Синтаксис функции следующий:

НОРМРАСП(х;среднее;с i андар! ное_откл;интегра.1ьная)

где X - это значение, для которого строится распределение; Среднее - это среднее арифметическое распределения; Стандартноеоткл это стандартное отклонение распределения: Интегральная - это логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА, то функция

НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ, то возвращается функция плотности распределения.

Искомая вероятность =F(105)-F(95)

Диапазон ячеек А5:А23 должен быть заполнен командой Правка -Заполнить - Прогрессия, заполнение по столбцам, предельное значение -709.Формула, находящаяся в ячейке В5, должна быть распространена на диапазон В6:В23.

В ячейку С4 введите текст: Плотность вероятности, а в ячейку С5 - = НОРМРАСП (А5; $В$1; SBS3; 0). Распространите формулу на диапазон С6:С23. Постройте график плотности вероятности (диапазон С5:С23), выбрав тип диаграммы - Гладкие графики на вкладке Нестандартные. В качестве подписей оси X используйте диапазон А5:А23.

Переименуйте лист в Задание 7.

Сохраните изменения в документе.

ЛАБОРАТОРНАЯ РАБОТА №11. ТАБЛИЧНЫЙ

ПРОЦЕССОР MICROSOFT EXCEL.

ОБРАБОТКА СТАТИСТИЧЕСКИХ ДАННЫХ

Цель лабораторной работы: рассмотреть возможности статистического анализа данных в Excel и научиться обрабатывать статистические данные в табличном процессоре.

11.1. Регрессионный анализ

Задание 1. Данные о росте населения и преступности в городе приведены в таблице:

Количество населения	500000	525000	612000	689000	720000
в городе
Количество совершен-	4810	5230	8930	9200	12600
ных гфеступлений в городе

Полагая, что данные связаны зависимостью у^ах+Ъ (линейная регрессия), определить примерное количество преступлений, если население достигнет 1000000 человек. Найти параметры а и b и построить прямую регрессии с отображением заданной табличной функции.

Рекомендации к выполнению:

Для создания документа необходимо запустить Microsoft Excel и на первом листе новой рабочей книги, начиная с ячейки А1, создайте приведенную в задании таблицу. При этом данные о количестве населения должны содержаться в ячейках B1:F1 (значения х)? данные о количестве совершенных преступлений должны содержаться в ячейках B2:F2 (значения у).

В ячейку G1 введите значение 1000000.

В ячейку G2 вставьте статистическую функцию ТЕНДЕНЦИЯ. Аргументы функции будут следующими:

Изв_знач_у - В2: F2 Изв знач х - B1:F1 Нов знач х - G1 Примечание. Функция РОСТ возвращает значения в соответствии с линейным трендом. Она аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные значения^ и известныезначениях и возвращает значения у. в соответствии с этой прямой для заданного массива но-ныезначения \. Синтаксис функции следующий:

ТЕНДЕНЦИЯ (швестнысзначенияу; известныезначениях; но-
* i в ые_з н ачениях; конст)

где Извсстныс_значения_у - это множество значений у. которые уже известны для соотношения у = птх + Ь;

Известныезначениях - это необязательное множество значений х, которые уже известны для соотношения у = тх н- Ь;

Новыезначениях - это новые значения х, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения у;

Конст - это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

В ячейку А4 введите текст а , а в ячейку А5 введите текст Ь= .

В ячейке В4 создайте следующую формулу, пользуясь мастером функций и вложенными функциями:

=ИВДЕКС(ЛИНЕЙН(В2:Р2;В1:Р1);1) Примечание. Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Уравнение для прямой линии имеет следующий вид: у = mx + b или у = mlxl + т2х2 + ... + b (в случае нескольких интервалов значений х), где зависимое значение у является функцией независимого значения х. Значения m - это коэффициенты, соответствующие каждой независимой переменной х, a b - это постоянная. Функция ЛИНЕЙН возвращает массив {mn;mn-l;...;ml;b}. Синтаксис функции следующий:

ЛИНЕЙН (швестные__значения_у; известныезначениях; конст;

статистика)

где Известные значенияу - это множество значений у, которые уже известны для соотношения у = тх + Ь:

Известные значения х - это необязательное множество значений х, которые уже известны для соотношения у = тх + Ь:

Конст - это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0;

Статистика - это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

6. Аналогично, в ячейке В5 создайте формулу

=ИНДЕКС (ЛИНЕЙН (B2:F2; B1:F1) ВЗ; 2)

7. В ячейку A3 введите текст у регр. . а в ячейку ВЗ вставьте формулу

=$В$4*В1 + SBS5 и скопируйте ее в ячейки C3:F3.

8. Чтобы построить прямую регрессии и отобразить заданную таблич
ную функцию на графике, выполните следующие действия:

выделите интервал ячеек Al: F3 и нажмите кнопку Мастер диаграмм;

на первом шаге выберите тип диаграммы - Точечная, а Вид диаграммы оставьте выбранный по умолчанию (первый). Нажмите кнопку Готово;

чтобы регрессионная функция отображалась в виде прямой, а не в виде точек (маркеров), щелкните мышью по одном}' из маркеров, отображающих значение у регр. и выполните команду Формат -Выделенный ряд...;

в окне Формат ряда данных на вкладке Вид в группе Линия включите переключатель обычная, а в группе Маркер включите переключатель отсутствует]

нажмите кнопку Ок и щелкните мышью по любой ячейке рабочего листа.

9. Сохраните документ в своей папке под именем Лабораторная работа П.

Задание 2. Имеются данные о прибыли адвокатской конторы (млн р.) за 7 лет:

Год	1	2	3	4	5	6	7
Прибыль	6,3.	9,5	13,9	16,1	20,2	-24,1	26.8.

Определить прибыль в следующем (8) году, пользуясь формулой у^Ьах (экспоненциальная регрессия) и найти ее параметры Построить кривую регрессии с отображением заданной табличной функции

Рекомендации к выполнению:

Перейдите на второй лист рабочей книги и начинам с ячейки AI создайте приведенную в задании таблицу. 11ри -пом данные о годач должны содержаться в ячейках В1:Н1 (значения \). данные о причн.нш доижны содержаться в ячейках В2:Н2 (значения у).

В ячейку II введите 8, а в ячейку 12 вставьте стачистичсскую функцию РОСТ с аргументами:

Изв_знач_у - В2:Н2

Изв знач х - В1 :Н1

Нов _знач_х-И 3 Для нахождения параметров кривой регрессии в ячейку А4 введите текст а . а в ячейку А5 введите текст /)=,ав ячейки В4 и В5 аналогично как в предыдущей задаче вставьте статистическую функцию ЛГРФПРИБЛ (также используйте функцию ИНДЕКС).

4. В ячейку A3 введите тексту регр. , а в ячейку ВЗ вставьте формулу'

=$В$5*$В$4ЛВ1 и скопируйте ее в ячейки C3:F3

Аналогично, как и в предыдущей задаче ^постройте график кривой регрессии и отобразите значения табличной функции на графике.

Сохраните документ.

11.2. Описательная статистика и гистограмма

Задание 3. Известно, что в городе ежедневно в среднем совершается 10 экономических правонарушений. Требуется выполнить анализ данной информации за месяц, используя описательную статистику, выполнить группировку данных с выводом графика.

Перед выполнением работы убедитесь, что в MS Excel в пункте меню Сервис присутствует команда А на!из данных. Если она отсутствует, необходимо выполнить команду Сервис - Надстройки и включить параметр Пакет анализа.

Рекомендации к выполнению:

Перейдите на третий лист рабочей книги и создайте шапку будущей таблицы: в ячейку А1 введите День, в ячейку В1 - Количество правонарушении. В ячейки А2:А31 введите числа от 1 до 30 (дни месяца).

Количество совершаемых правонарушений подчиняется распределению Пуассона. Выполните команду Сервис - Анализ данных, выберите инструмент Генерация случайных чисел. В окне генерации случайных чисел установите следующие параметры: Распределение - Пуассона, Число переменных - 1. Число случайных чисел - 30, Лямбда - 10, Выходной интервал - $В$2 и нажмите Ок. Просмотрите созданный ряд чисел.

Выполните команду Сервис - Анализ данных и выберите инструмент анализа Описательная статистика. В окне описательной статистики задайте параметры: Входной интервал - $В$1:$В$31, активизируйте параметр Метки в первой строке. Выходной интервал - $D$1, активизируйте параметр Итоговая статистика и нажмите кнопку Ок. Проанализируйте полученные значения среднего количества правонарушений, моды, медианы, дисперсии, минимального, максимального и суммарного количества правонарушений за месяц.

Для группировки данных введите границы интервала - например, в ячейки D19:D22 введите числа 4. 8, 12 и 16. Выполните команд) Сервис -Анализ данных и выберите инструмент Гистограмма. В окне гистограммы задайте параметры: Входной интервал - $В$2.$В$31, Интервал карманов -$D$19:$D$22. Выходной интервал - $Е$18, активизируйте параметры Интегральный процент и Вывод графика и нажмите Ок. Проанализируйте полученные значения частоты и интегрального процента.

Примечание. Частота показывает количество попаданий числа правонарушений в заданный интервал, интегральный процент выражает число накопленных частот.

5. Сохраните документ.

11.3. Корреляция

Задание 4. В восьми районах собраны сведения о числе правонарушений за год, численности населения (тыс.чел.) и размере ежемесячного среднедушевого дохода (у.е.):

Число правонарушений	300	133	100	200	120	270	120	260
Население	250	75	82	150	100	120	42	50
Доход	18	20	33	18	31	18	31	20

Требуется определить, имеется ли связь между этими величинами.

Рекомендации к выполнению:

Вставьте четвертый лист в рабочую книгу и создайте вышеприведенную таблицу, начиная с ячейки А1.

Выполните команду Сервис - Анализ данных, выберите инструмент Корреляция. В окне корреляции установите следующие параметры: Входной интервал - $А$1:$1$3, Группирование - по строкам, активизируйте параметр Метки в первом столбце, Выходной интервал - $А$5 и нажмите Ок.

Примечание. В полученной таблице расположенные по диагонали числа представляют собой коэффициенты корреляции г^у. Вели этот коэффициент попадает в интервал 0,1 - 0,5, то связь между показателями еллинг 0 s о / связь заметная, 0,7 - 0,99 - связь очень сильная. Знак "-" в коэффициент означает наличие обратной связи между исследуемыми величинами.

3. Проанализировав полученные результаты, можно сделан» ныноды

с увеличением населения в районе количество правонарушении заметно увеличивается (гХу^ 0,70986257):

с увеличением среднедушевого дохода населения в районе количество правонарушений существенно уменьшается (глт -0,76191)7Н> >).

с увеличением среднедушевого дохода населения в районе незначительно уменьшается количество населения (/дт -0,492743901).

4. Сохраните документ.

11.4. Скользящее среднее и экспоненциальное сглаживание

Задание 5. Имеются данные о совершенных дорожно-транспортных происшествиях за 9 месяцев года:

Месяц		1	2		4	5	6	7	8	9
Зарегистрир	овано ДТП	133	80	132	70	64	113	75	112	121

Выполнить прогноз с помощью методов скользящего среднего и экспоненциального сглаживания.

Рекомендации к выполнению:

Вставьте пятый лист в рабочую книгу и создайте вышеприведенную таблицу, начиная с ячейки А1.

Выполните команду Сервис - Анализ данных, выберите инструмент Скользящее среднее. В окне скользящего среднего установите следующие параметры: Входной интервал - $A$2:$J$2, активизируйте параметр Метки в первой строке, Выходной интервал - $В$4, активизируйте параметр Вывод графика и нажмите Ок.

Примечание. Этот метод позволяет сгладить временные ряды, он основан на переходе от начальных значений ряда к их средним значениям на интервале времени.

Проанализируйте полученные результаты. Обратите внимание на сравнительно низкое число ДТП в 4 и 5 месяце и всплеск в 6 месяце.

Выполните команду "Сервис - Анализ данных, выберите инструмент Экспоненциальное сглаживание. В окне экспоненциального сглаживания установите следующие параметры: Входной интервал -$A$2:$J$2, активизируйте параметр Метки, Выходной интервал - $В$2(), активизируйте параметр Вывод графика и нажмите Ок.

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

Проанализируйте полученные результаты и сравните с графиком скользящего среднего.

Сохраните документ.

Дата добавления: 2018-04-05; просмотров: 204; Мы поможем в написании вашей работы!

Поделиться с друзьями:

⇐ Предыдущая 12

Мы поможем в написании ваших работ!