ТЕМА №3: ВСТРОЕННЫЕ ФУНКЦИИ EXCEL. СТАТИСТИЧЕСКИЙ АНАЛИЗ

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

Содержание работы:

1. Использование Мастера функций.
2. Анализ статистических данных.
3. Инструменты пакета анализа.

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

1)числовые константы, например, функция ПРОИЗВЕД(2;3) вычисляет произведение чисел 2 и 3, т.е. 2·3.

2)ссылки на ячейки и блоки ячеек (функция ПРОИЗВЕД (А1;С1:СЗ) вычисляет произведение содержимого ячеек А1,С1,С2 и С3, т.е. А1·С1·С2·СЗ

3) текстовые константы (заключенные в кавычки).

4) логические значения.

5) массивы.

6) имена ссылок, например, если ячейке А10 присвоить имя СУММА (последовательность команд Вставка, Имя, Определить.. .),а блоку ячеек В10:Е10 -имя ИТОГИ , то допустима следующая запись: =СУММ(СУММА; ИТОГИ).

7) смешанные аргументы, например, =СРЗНАЧ (Группа;АЗ;5*3)

Формулы, содержащие функции, можно вводить непосредственно в ячейку, в строку формул или создавать с помощью Мастера функций. Для вызова Мастера функций необходимо выбрать команду Функция в меню Вставка или нажать кнопку Мастер функций (значок f_x ). B открывшемся диалоговом окне выберите категорию и имя функции, затем в поля с соответствующими подсказками введите аргументы. После нажатия кнопки Закончить готовая функция появится в строке формул

Пример 1. Вычислить значение функции у = е^х · sin(x) для -1=< х <=1 c с шагом ?х=0,2 . Определить количество отрицательных у.

Заполним столбец А значениями аргумента функции. Чтобы не вводить их вручную , применим следующий прием. Введите в ячейку А1 начальное значения аргумента -1 . В меню Правка выберите команду Заполнить, затем Прогрессия и в открывшемся диалоговом окне укажите предельное значение

(1),шаг(0.2 ) и направление (По столбцам). После нажатия кнопки ОК в столбце А будут введены все значения аргумента . В ячейку В1 введите формулу : =exp(А1)* sin(AI). Размножьте эту формулу на остальные ячейки столбца В, ухватив левой мышью черный квадратик в правом нижнем углу рамки выделенной ячейки В1 и протащив рамку до конца изменения аргумента . В итоге будут вычислены соответствующие значения функции.

Для определения количества отрицательных у в ячейку С1 введите формулу =СЧЕТЕСЛИ (В 1: В 11; <0). В результате в ячейке С1 будет вычислено количество отрицательных значений в ячейках В1:В11 (т.е. у ). Принцип действия большинства логических функций Excel заключается в проверке некоторого условия и выполнения в зависимости от него тех или иных действий. Так, функция ЕСЛИ выполняет проверку условия, задаваемого первым аргументом логического выражения: ЕСЛИ (логичическое 1; значение_да; значение нет) и возвращает значение да, если условие выполнено (ИСТИНА) , и значение нет, в противном случае (ЛОЖЬ).

Например:

1)=ЕСЛИ(В6<10;5; 10). Если значение в ячейке В6<10, то функция вернет результат 5 , иначе -10.

2 )=ЕСЛИ(В4>80;"Сданы";"Не сданы"). Если значение В4>80, то в ячейке с приведенной формулой будет записано "Сданы ", иначе - "Не сданы".

3) =ЕСЛИ(СУММ(А1:А10)>0; СУММ(В1:В10);0). Если сумма значений в столбце А1:А10 >0 , то в ячейке, содержащей эту формулу вычислится сумма значений в столбце В1:В10, иначе – запишется 0.

Дополнительные логические функции:

=И (логическое выражение 1; логическое выражение 2;...) – возращает значение ИСТИНА, если все аргументы истинны, и ЛОЖЬ, если хотя бы один аргумент – ЛОЖЬ.

Например, для =ЕСЛИ(СУММ(А1:А10)>0; И; СУММ(В1:В10)>0; СУММ(А1:В10);0). Если суммы и в столбце А1:А10 и в столбце В1: В10 положительны , то вычислить сумму значений в ячейках А1:В10 , иначе - 0.

Аналогично используются:

=ИЛИ (логическое 1;логическое 2;...) – возращает значение ИСТИНА, если хотя бы один аргумент является - ИСТИНА

=НЕ (флаг) – меняет значение ИСТИНА на ЛОЖЬ и наоборот.

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

=СРЗНАЧ(числа).

Например: =СРЗНАЧ(5;7;9) , =СРЗНАЧ(А1 :А10;С1 :С10), =СРЗНАЧ(А1:Е20).

2. Нахождение максимального (минимального)значения: =МАКС(числа) =МИН(числа). Например: =МАКС(А4:С10);=МИН(А2;С4;7)

3.Вычисление медианы (числа являющегося серединой множества): =МОДА(числа).

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

5.Дисперсия: ДИСП(числа).

6 Стандартное отклонение: =СТАНДОТКЛОН(числа).

7. Ввод случайного числа: =СЛЧИС() .

ПАКЕТ АНАЛИЗА –дополнение EXCEL расширяющее аналитические возможности и позволяющее строить гистограммы, составлять таблицы ранг и персентиль, делать случайные или периодические выборки данных и находить их статистические характеристики, генерировать неравномерно распределенные случайные числа, проводить регрессионный анализ и многое другое. Чтобы воспользоваться инструментами анализа, выполните следующие действия:

1) В меню Сервис выберите команду Анализ данных....

2)Выберите из списка название нужного инструмента анализа и нажмите ОК.

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

Инструмент Описательная статистика формирует таблицу статистических данных, ускоряя и упрощая этот процесс по сравнению с использованием формул 1-6.

Инструмент Генерация случайных чисел дает возможность получать равномерное и неравномерное распределение.

Инструмент Гистограмма позволяет создавать гистограммы распределения данных.

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

Пример .

Дана таблица с данными о температуре воздуха в Краснодаре летом 2000 года. Интервал измерения от 18°С до 38°С (его можно определить с помощью функций MAKC() и МИН().

1. Разобьем этот интервал на подинтервалы - карманы шириной, например 2°С (ширина карманов не обязательно должна быть равной).

2. Воспользуемся командой Заполнить из меню Правка для быстрого заполнения столбца карманов (значения в столбце будут изменяться от 18 до 38°С с шагом 2°С).

3. Выполним команду Анализ данных из меню Сервис , открывшемся диалоговом окне зададим входной интервал (это ячейки с данными о температуре), интервал карманов выходной интервал (надо указать только верхнюю левую ячейку для выхода результатов) и установим флажок Выход графика.

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

2. В задании 2 своего варианта вычислите коэффициент вариации.

3. В чем заключаются особенности построения гистограммы распределения данных?

4. Напишите логическую формулу, которая выводит текстовое сообщение «Вычислена сумма» или "Вычислено произведение" в зависимости от того, что было вычислено на рабочем листе в п.З задания 1.

5. Используя информацию о том, что "как правило, 68% данных генеральной совокупности с нормальным распределением находятся в пределах одного стандартного отклонения от среднего значения, а 95% - в пределах двух отклонений",

создайте на рабочем листе строку, в которой для задания 1 автоматически будут рассчитываться указанные интервалы.

Для выполнения первого задания необходимо:

1. На рабочем листе построить таблицу значений функции согласно варианту задания и ее график (см. л.р №2 «Создание и редактирование диаграмм и графиков в Excel “)

2. Определить среднее , минимальное и максимальное значения функции вывести эти данные на графике, используя команду Добавить данные из меню Диаграмма.

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

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

Исходными данными для второго задания являются варианты заданий к лабораторной работе №I. Необходимо:

1. Провести статистический анализ с использованием функций 1-6 методических указаний к данной работе (раздел Анализ статистических данных).

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

Варианты заданий:

1. y=cos² (2?x) + x -1=<х=<1.5, ?х=0.2 ?

2. y= x + e^x +tg(3?x?lgx² -10=<x=<10 ?x=l

3. y=(x³ - cos(x² )/e^4x - tgx -5=<x=<5 , ?x=0.75

4. y=(lx+exp(x)l)^l/2+lnlx^sinxl -1.8=<х=<1.5 ?х =0.4

5. y=x^cosx/(lx+exp(x)(+tgx -5.2=<х=<1.5 ?х=0.7

6. y=lgx^2*exp^sm2x/lg3x 1=<х=<100 ?х=5

7. у=ехр^(х+2)+*1п^2(2х)/(х+10*ехр(х) 1=<х==<50 дельта х=2.5

8. y=lsin2x+tg3xl^l/2+exp^4x -2.5=<х=<1.5 дельта х=0.4

9. y= l-lsinxl+exp^(ln2x+lgx) 1=<х=<10 дельта х=0.1

10. y=(-l)^x*exp^sinx*cosx^2 1=<х=<15 дельта х=1