16431

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

Лекция

Информатика, кибернетика и программирование

ТЕМА №3: ВСТРОЕННЫЕ ФУНКЦИИ EXCEL. СТАТИСТИЧЕСКИЙ АНАЛИЗ Цель работы: научиться работать с Мастером функций проводить анализ данных . Содержание работы: 1. Использование Мастера функций. 2. Анализ статистических данных. 3. Инструменты пакета анализа. Методические рек...

Русский

2013-06-22

60.5 KB

38 чел.

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

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

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

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

Методические рекомендации.


MS Excel содержит 320 встроенных функций. Простейший способ получения полной информации о любой из них заключается в использовании меню
? .Для удобства функции в Excel разбиты по категориям (математические, финансовые, статистические и т.д.).

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

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) 

Формулы, содержащие функции, можно вводить непосредственно в ячейку, в строку формул или создавать с помощью Мастера функций. Для вызова Мастера функций необходимо выбрать команду Функция в меню Вставка или нажать кнопку Мастер функций (значок fx ). 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;...) – возращает значение ИСТИНА, если хотя бы один аргумент является - ИСТИНА 

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


Анализ статистических данных


MS EXCEL предоставляет широкие возможности для анализа статистических данных. Для решения простых задач можно использовать встроенные функции. Рассмотрим некоторые из них.

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. После нажатия кнопки ОК на экран будет выведена гистограмма, а рядом со столбцом карманов появится столбец частот, показывающий, сколько дней летом в Краснодаре имели температуру, попадавшую в каждый интервал.



Контрольные вопросы и задания


1. Для чего предназначен ПАКЕТ АНАЛИЗА и каков порядок доступа к его инструментам ?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. y= x + ex +tg(3?x?lgx2 -10=<x=<10 ?x=l

3. y=(x3 - cos(x2 )/e4x - 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

PAGE  1


 

А также другие работы, которые могут Вас заинтересовать

22563. Механізми, що лежать в основі формування довготривалої памяті 25 KB
  Механізми що лежать в основі формування довготривалої памяті Цей вид памяті не може базуватись лише на циркуляції імпульсів чи змінах електрофізичних характеристик окремих нейронів. Разом з тим велика кількість інформації при цьому зберігається в довготривалій памяті незмінню.Механізм памяті такоє пояснюють на основі складних морфологічних чи біохімічних змін синапсів. Молекулярна теорія памяті в основі якої лежить уявлення про те що виникаючий під дією зовнішнього подразника нервовий імпульс активує синтез РНК в нейроні.
22564. Визначення та класифікація емоцій 24 KB
  Визначення та класифікація емоцій Емоції рефлекторна адаптаційна психофізіологічна реакція яка повязана з проявом субєктивного ставлення до значущої ситуації і забезпечує організацію доцільної поведінки. Емоції поділяють на вищі та нижчі. Нижчі емоції найбільш елементарні повязані з органічними потребами тварин і людей поділяються на 2 види : 1 гомеостатичні проявляються в вигляді неспокою пошуковорухової активності спраги голоду і ін. Вищі емоції виникаютьлишу у людини в звязку з задоволенням соціальних потреб інтелектуальних...
22565. Функції емоцій 23 KB
  Сигнальна функція полягає в тому що емоції сигналізують про корисний чи негативний вплив даного організму чи успішність чи неуспішність виконання даної дії. Це призводить до моментальної мобілізації всіх систем організму для реакції відповіді характер якої залежить від того сигналом корисного чи негативного впливу на організм є даний подразник. Таким чином впливи що надходять з зовнішнього середовища і від самого організму призводять до виникнення емоційних переживань що дають загальну якісну характеристику фактору що впливають...
22566. Основні фізіологічні теорії емоцій 25 KB
  Основні фізіологічні теорії емоцій В першій класичній теорії відомій як теорія Джеймся Ланге робили висновок про характеристику стенічних та астенічних емоційних станів.Пізніше Кеннон та Бард показали що емоції гніву та стаху під впливом таламічних розрядів супроводжуються повишеним поступанням адреналіну в кров що призводить до розвитку симпатікотонії яка відіграє позитивну роль в підготовці організму до діяльності і навіть боротьби внаслідок чого ця теорія отримала незву таламічної теорії емоцій. Кортикальні емоційні процеси...
22567. Сон 42 KB
  Існує величезна кількість емпіричних даних і забобонів щодо значення сну і сновидінь але справжнє наукове вивчення сну почалося лише у другій половині ХІХ ст. Прибічники хімічної теорії сну спочатку пояснювали сон накопиченням в організмі гіпнотоксичних речовин молочна вугільна та карбонові кислоти холестерин а нині надають важливого значення особливим хімічним регуляторам сну таким як речовина сну фактор сну чи пептид дельтасну які являють низькомолекулярні поліпептиди 850 920 Да . Кортикальна теорія сну І. Нарешті...
22568. будливий та гальмівний постсиниптичні потенціали 23.5 KB
  Постсинаптичне гальмування ГПСП обумовлене виділенням пресинаптичним закінченням аксона гальмівного медіатора який знижує або гальмує збудливість мембран соми і дендритів нерв клітини з якою він контактує. Прикладами гальмівних нейронів є клітини Реншоу в спинному мозку клітини Пуркіньє мозочку зірчасті клітини кіркової речовини великого мозку . Збудження нейрона супроводжуеться змінами метаболізму зокрема синтезу РНК та іншими зрушеннями в процесі білкового синтезу посиленням теплопродукції поглинанням кисню які відображають...
22569. Постсинаптичне гальмування у ЦНС та його природа.Значення ггальмування у роботі 22.5 KB
  Значення ггальмування у роботі. Гальмування особливий нервовий процес який зумовлюється збудженням і зовнішньо проявляється пригніченням іншого збудження. Постсинаптичне гальмування ГПСП обумовлене виділенням пресинаптичним закінченням аксона гальмівного медіатора який знижує або гальмує збудливість мембран соми і дендритів нерв клітини з якою він контактує.
22570. ЦНС 22.5 KB
  Особливе місце в цій складній організації займае місце ЦНС що повязує в функціональну єдність всі клітини тканини і органи людського організму. Дякуючи великій кількості різних рецепторів ЦНС сприймає багаточисельні зміни що виникають в зовн средовищі і всередині організму і відіграє велику роль в регуляції всіх сторін життєдіяльності огранізму в зовн середовищі. Процеси що відбуваються в ЦНС лежать в основі психічної діяльності та поведінки людини. Діяльність ЦНС найчастіше наз координаційною або узгоджувальною.
22571. Спинний мозок 49.5 KB
  Він є сегментарним органом: у людини від спинного мозку відходять 31 пара спинномозкових корінців у жаби 10 які у кожному сегменті поділяються на дві частини: на передній вентральний і задній дорзальний корінці. Сіра речовина спинного мозку на поперечному перетині має вигляд метелика або літери Н . Є також дорзальні роги спинного мозку з'єднані з вентральними широкою перетинкою сірої речовинитак зване тіло сірої речовини . Крім вентральних і дорзальних рогів у грудному відділі спинного мозку є бокові роги сірої речовини рис.