16431

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

Лекция

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

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

Русский

2013-06-22

60.5 KB

35 чел.

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


 

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

4155. Эргономика как наука. Основные содержания курса эргономики 197 KB
  Введение Эргономика изучает особенности и возможности функционирования человека в системах, человек, вещь, среда. Эргономика - наука о системах. Она включает в себя такие понятия, как антропометрия, биомеханика, гигиена труда, физиология труда, техн...
4156. Бухгалтерское дело 582 KB
  В тексте курса последовательно раскрыты история бухгалтерского дела, порядок организации и ведения учета в зарубежных странах, концепция реформирования бухгалтерского учета в РФ, бухгалтерский учет на различных стадиях существования организации и др...
4157. История создания фильма Если завтра война 137.5 KB
  История создания фильма Если завтра война Фильм Если завтра война - это один из первых советских предвоенных художественных фильмов о готовности СССР к отражению нападения вероломных и коварных агрессоров. Фильм подготовлен коллективом кинорежис...
4158. Инвестиционная стратегия предприятия в современных условиях 206 KB
  Введение Одной из основных отличительных черт рыночной экономики является способ распределения ресурсов на основе рыночного механизма. Рыночный механизм в свою очередь функционирует на основе модели равновесия спроса и предложения на необходимые рес...
4159. Русское народное творчество и средства народной педагогики 98 KB
  Еще в середине прошлого столетия И.В. Киреевский (1806 - 1856) писал, Уничтожить особенность умственной жизни народной так же невозможно, как невозможно уничтожить его историю. Заменить литературными понятиями коренные убеждения нар...
4160. Эволюция денежной системы денежная система Украины 147.5 KB
  Введение В литературе существует множество различных определений денег, значительно отличающихся друг от друга. Под деньгами часто понимается все то, что обычно принимается в обмен на товары и услуги продукт соглашения между людьми товар особого...
4161. Эволюция взглядов на семью в экономической теории 150 KB
  Во многих книгах в домашнем окружении главных героев фигурирует персонаж, который на Руси назывался ключницей (она держала при себе ключи от всех помещений и шкафов). В Европе её называли экономкой. Она руководила всем хозяйством дома, распо...
4162. Эволюция украинских денег 156.5 KB
  История украинских денег Деньги VI — XIX вв. Самыми первыми в истории Украины деньгами, существующими на ее территории, следует считать монеты древнегреческих полисов и Боспорского царства, которые датируются VI столетием до Рождества Христова ...
4163. Перспективы использования системы автоматизации в управлении предприятием 101 KB
  Роль автоматизации в управлении предприятием По мере роста рынка в определенной сфере деятельности растут и требования потребителей продуктов этой сферы, и, как следствие, в целях повышения конкурентоспособности предприятия вынуждены улуч...