16431

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

Лекция

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

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

Русский

2013-06-22

60.5 KB

34 чел.

ТЕМА №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


 

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

16242. ПРОЕКТИРОВАНИЕ И АНАЛИЗ ЭЛЕКТРИЧЕСКИХ СХЕМ 462.5 KB
  ПРОЕКТИРОВАНИЕ И АНАЛИЗ ЭЛЕКТРИЧЕСКИХ СХЕМ Методические указания для выполнения лабораторных работ по дисциплинам Автоматизация проектирования Основы автоматизированного проектирования Лабораторная работа № 1 Разработка графических моделей ...
16243. ВЕЛ про поліпшення питного водопостачання та охорони вод в Україні 122 KB
  ВЕЛ про поліпшення питного водопостачання та охорони вод в Україні. Вода – найцінніший природний ресурс. Вода – основа життя вона відіграє виняткову роль у процесах обміну речовин без яких життя не можливе. Загальні запаси води на земній кулі становлять близько 1390 м...
16245. Интерфейс Adobe Photoshop. Работа с документом 948.87 KB
  Лабораторная работа № 1 Интерфейс Adobe Photoshop. Работа с документом Открытие документов в Photoshop Запустите графический редактор Photoshop Пуск → Программы → Adobe Photoshop CS2. В меню File Файл выберите команду Open Открыть. В появившемся диалоговом окне Open Открыть
16246. Изучение выпрямителей и стабилизаторов напряжения 55.5 KB
  Лабораторная работа № 11 Изучение выпрямителей и стабилизаторов напряжения 11.1. Цель работы Изучение различных схем выпрямителей и линейных стабилизаторов напряжения. 11.2. Порядок выполнения работы 11.2.1. Для исследования двухполупериодного выпрямите
16247. Созданоие приложения визуализирующего работу cash-памяти в 3-х архитектурах 200.5 KB
  Содержание: Краткая информация о процессорах семейства х-86. Кэш-память Архитектура кэш-памяти Кэш-память с прямым отображением Полностью ассоциативная архитектура Наборно-ассоциативн...
16248. Эмуляция работы программы FDisk 471 KB
  Курсовой проект по по информатике Тема: Эмуляция работы программы FDisk Краткие теоретические сведения. Конструкция HDD Рис. 1 Диск представляет собой круглую металлическую пластину с очень ровной поверхностью покрытую тонким ферро...
16249. Конфигурация функции IGMP Snooping 724.66 KB
  Лабораторная работа №1 Конфигурация функции IGMP Snooping 1 Цель работы 1.1Научиться конфигурировать протокол управления групповой multicast рассылкой на коммутаторах Dlink. 2 Литература 2.1 Смирнова Е.В. Пролетарский А.В. Баскаков И.В. Федотов Р.А. Построение комму
16250. Создание профиля многоадресной рассылки 724.41 KB
  Лабораторная работа №2 Создание профиля многоадресной рассылки 1 Цель работы 1.1 Научиться управлять multicast трафиком. 2 Литература 2.1 Смирнова Е.В. Пролетарский А.В. Баскаков И.В. Федотов Р.А. Построение коммутируемых компьютерных сетей: учебное пособие. –...