20533

Встроенные функции EXCEL. Статистический анализ

Лабораторная работа

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

Встроенные функции EXCEL. Простейший способ получения полной информации о любой из них заключается в переходе на вкладку Поиск из меню после чего необходимо напечатать имя нужной функции и нажать кнопку Показать. Для удобства функции в EXCEL разбиты по категориям матаматические финансовые статистические и т. Зная к какой категории относится функция справку о ней можно получить следующим образом: Щелкните на закладке Содержание в верхней части окна а затем последовательно пункты Создание формул и проверка книг Функции листа.

Русский

2013-07-31

101 KB

23 чел.

ЛАБОРАТОРНАЯ РАБОТА № 3.

Встроенные функции EXCEL. Статистический анализ.

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

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

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

  1.  Щелкните на закладке Содержание в верхней части окна, а затем последовательно пункты Создание формул и проверка книг,  Функции листа.

  1.  

Щелкните название нужной категории.

  1.  

Щелкните имя необходимой функции и ознакомьтесь с ее описанием.

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

1)  Числовые константы.

=ПРОИЗВЕД(2,3)                  2*3

2)  Ссылки на ячейки и блоки ячеек.

=ПРОИЗВЕД(А1;С1:С3)      А1*С1*С2*С3

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

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

5)  Массивы.

6)  Имена ссылок. Например, если ячейке А10 присвоить имя Сумма (последовательность команд Вставка, Имя, Определить ...), а блоку ячеек В10:Е10 – имя Итоги, то допустима следующая запись:

=СУММ(Сумма;Итоги)

7)  Смешанные аргументы.

=СРЗНАЧ(Группа;А3;5*3)  

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

необходимо выбрать команду Функция в меню Вставка или нажать кнопку Мастер функций.

В открывшемся диалоговом окне выберите категорию и имя функции, а затем нажмите кнопку OK.

В полях с соответствующими подсказками впечатайте аргументы*. После нажатия кнопки OK готовая функция появится в строке формул.

В приложении 2 представлены некоторые математические и тригонометрические функции EXСEL.

Пример 1.  Вычислить значения функции

y= ex sin(x)  для  -1 <= x <= 1   Dx = 0.1.

Определите количество отрицательных у.

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

=EXP(A1)*Sin(A1).

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

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

=СЧЕТЕСЛИ (В1:В11;<0)

В результате в ячейке C1 будет вычислено количество отрицательных значений в ячейках B1:В11 (т.е. у).

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

Так, функция ЕСЛИ выполняет проверку условия, задаваемого первым аргументом логич_выр:

=ЕСЛИ(логич_выр;знач_да;знач_нет)

и возвращает знач_да, если условие выполнено (ИСТИНА), и знач_нет, противном случае (ЛОЖ).

Например:

  1.  =ЕСЛИ(А6<10;5;10).

Если значение в ячейке А6<10, то функция вернет результат 5, а иначе – 10.

  1.  =ЕСЛИ(B4>80;”Сданы”; ”Не сданы”).

Если значение в ячейке B4>80, то в ячейке с приведенной формулой будет записано ”Сданы”, иначе - ”Не сданы”.

  1.  =ЕСЛИ (СУММ(А1:А10)>0;СУММ(В1:B10);0).

Если сумма значений в столбце A1:А10 больше 0, то вычислится сумма значений в столбце B1:В10, в противном случае результат – 0.

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

=И(логич_выр1;логич_выр2)

=ИЛИ(логич_выр1;логич_выр2)

=НЕ(логич_выр)

позволяют создавать сложные условия, например:

=ЕСЛИ(И(СУММ(А1:А10)>0;СУММ(В1:B10)>0);

             СУММ(A1:B10);0).

Если суммы и в столбце А1:А10 и в столбце В1:В10 положительны, то вычислить суму значений в ячейках А1:В10, иначе – 0.

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

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

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

Например:        =СРЗНАЧ(5;7;9);

                         =СРЗНАЧ(А1:А10;С1:С10)4

                         =СРЗНАЧ(А1:Е20).

2. Нахождение максимального (минимального) значения:

=МАКС(числа)

=МИН(числа).

Например:        =МАКС(А4:С10);

                         =Мин(А2;С4;7).

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

=МЕДИАНА(числа).

4. Вычисление моды (наиболее часто встречающегося значения в множестве):

 =МОДА(числа).

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

5. Дисперсия:

=ДИСП(числа).  

6. Стандартное  отклонение:

=СТАНДОТКЛОН(числа).  

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

  1.  В меню Сервис выберите команду Анализ данных.
  2.  Выберите из списка название нужного инструмента анализа и нажмите кнопку ОК.
  3.  В большинстве случаев в открывшемся диалоговом окне нужно просто указать интервал исходных данных, интервал для вывода результатов и задать некоторые параметры.

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

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

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

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

  1.  Разобьем этот интервал на подинтервалы -  карманы шириной, например, 2С (ширина карманов не обязательно должна быть равной).
  2.  Воспользуемся командой Заполнить из меню Правка для быстрого заполнения столбца карманов (значения в столбце будут изменятся от 18 до 38С с шагом 2 градуса).
  3.  Выполним команду Анализ данных из меню Сервис. В открывшемся диалоговом окне зададим входной интервал (это ячейки с данными о температуре), интервал карманов, выходной интервал (надо указать только верхнюю, левую ячейку для вывода результатов) и установим флажок Вывод графика.
  4.  После нажатия кнопки ОК на экран будет выведена гистограмма, а рядом со столбцом карманов появится столбец частот, показывающий, сколько дней летом в Краснодаре имели температуру, попадающую в каждый интервал.

 

Порядок выполнения работы.

     Каждый вариант состоит из двух заданий. Для выполнения первого задания необходимо:

  1.  На рабочем листе №4 построить таблицу значений функции согласно варианта задания и ее график.
  2.  Определите среднее, минимальное и максимальное значение функции и вывести эти данные на графике.
  3.  Используя логическую формулу, вычислить сумму значений функций, если среднее, минимальное и максимальное значения имеют одинаковые знаки и произведение в противном случае.
  4.  Произвольной ячейке присвоить имя и сгенерировать в ней случайное число. В таблице значений функции добавить еще один столбец, полученный умножением у на случайное число. Добавить на графике функции второй график, соответствующий полученному столбцу данных.

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

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

        

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

1

Y = excos2 2x+/x/

-1 <= x <=1.5, Dx = 0.2

2

Y = /x+ex/+tg3x*lg x2

-10 <= x <= 10, Dx = 1

3

Y = (x3-cos x2)/(e4x)-tg x

-5 <= x <= 5, Dx = 0.75

4

Y = /x+ex/1/2 +ln/xsin x/

-1.8 <= x <= 1.5, Dx = 0.4

5

Y = xcos x/(/x+ex/+tg x)

-5.2 <= x <= 1.5, Dx = 0.7

6

Y = lg x2 esin 2x /lg3x

1 <= x <= 100, Dx = 5

7

Y = ex+2 ln2 2x/(x+10ex)

1 <= x <= 50, Dx = 2.5

8

Y = /sin 2x+tg 3x/1/2+e4x

-2.5 <= x <= 1.5, Dx = 0.4

9

Y = 1-/sin x/+eln 2x+lg x

1 <= x <= 10, Dx = 0.1

10

Y = (-1)x esin x cos x2

1 <= x <= 15, Dx = 1

Контрольные  вопросы.

  1.  Для чего предназначены Пакет анализа и каков порядок доступа к его инструментам?
  2.  В задании 2 своего варианта вычислите коэффициент вариации.
  3.  В чем заключаются особенности построения гистограммы распределения данных?
  4.  Напишите логическую формулу, которая выводит текстовое сообщение ”Вычислена сумма” или ”Вычислено произведение” в зависимости от того, что было вычислено на рабочем листе в п. 3 задания 1.
  5.  Используя информацию о том, что “как правило, 68% данных генеральнлй совокупности с нормальным распределением находятся в пределах одного стандартного отклонения от среднего значения, а 98% - в пределах двух отклонений”, создайте на рабочем листе строку, в которой для задания 1 автоматически будут рассчитываться указанные интервалы.
  6.  *Напишите программу на языке Бейсик для вычисления среднего, минимального, максимального значений, дисперсии, стандартного отклонения и коэфициента вариации для исходных данных своего варианта в задании №2.

* Как уже отмечалось для ввода аргументов можно использовать мышь

* Задание для повторения пройденного материала в разделе «Алгоритмизация и программирование».


 

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

52851. Розвиток емоційного інтелекту школярів на уроках української літератури 52.5 KB
  Емоційний інтелект опинився у центрі уваги вчених в останні десять років. Проте як не парадоксально це звучить останнім часом було відкрито що в розвитку гармонійної особистості людини емоційна компетентність емоційний інтелект відіграє важливішу роль ніж академічна. Емоційний центр мозку безпосередньо пов’язаний із системою довготривалої пам’яті.
52852. eMule. Полное описание 231.5 KB
  Своей целью я поставил написать наиболее полное руководство по программе eMule при этом не вдаваясь в технические подробности с одной стороны и с другой стороны объясняя не только назначение отдельных кнопочек но и рассказывая про принципы работы как самого eMule так и функционирования сети в частности. Почему именно eMule а не чтото другое Здесь есть целый ряд причин. Самое главное: eMule это наиболее функциональный P2Pклиент к тому же его использует подавляющее большинство пользователей. Вот статистика собранная моим Мулом за 18...
52853. Збереження електроенергії у побуті 49 KB
  Мета: Поширити уявлення дітей про способи добування електроенергії. В домі дід мороз живе І продукти зберігає А щоб свіжими були Зіпсуватись не могли Їх у холоді тримає Холодильник Коментарі вчителя щодо збереження електроенергії. Своєчасно розморожувати щоб менше споживав електроенергії.
52854. Енергозбереження - працюємо разом 115.5 KB
  Мета: Навчати оцінювати запаси енергетичних ресурсів формувати переконаність у можливості раціонального використання природних ресурсів; навчати оцінювати побутові втрати енергії; підвести до висновку про можливість раціонального використання енергії; переконати учнів у необхідності пошуків засобів збереження енергії. Наші споживання в електроенергії змінюються в часі : доба тиждень рік. Доба Наші споживання в енергії змінюються на протязі доби частково від того в яких умовах ми живемо і частково від нашої роботи мал.1 З малюнок 1...
52855. Енергозбереження – почнемо з себе 83.5 KB
  Розширення індивідуального екологічного простору розвиток емпатії до природних об'єктів; Форма заняття: заняття студії Заняття розраховане на учнів 910 класу Обладнання: таблиці з зображенням будови атмосфери комп’ютер підключений до мережі Інтернет свічка лампа розжарювання лампа енергозберігаюча лампа 20 21 Вт; лампа розжарювання 100 Вт; настільна лампа зі стандартним цоколем; кімнатний термометр; годинник кольорові стікери з зображенням пір року. Мета етапу – сфокусувати увагу учнів на проблемі й викликати інтерес до...
52856. Енергосистема України. Споживання електроенергії. Енергозбереження 158 KB
  Споживання електроенергії. Мета: показати утворення енергосистеми України поєднання великих електростанцій і ліній електропередач; формувати вміння і навички встановлення відповідності у секторних діаграмах на основі аналізу статистичної інформації розширювати уміння усвідомлення власної значущості та можливості впливати на загальну енергозалежність країни; сформувати вміння аналізувати зміни частки різних видів електростанцій у загальному обсязі виробництва електроенергії в Україні. Символічним є те...
52857. Вулиця, на якiй я живу 30 KB
  My name is Semen. I live with my family in a big town. I live in Rivna street. My street is long. There are many green trees and flowers in our street. You can see many big houses and new shops in our street. My school is in our street too. In Rivna street you can always see many trams,buses and cars. In our street there are many men,women and children. My street is clean and nice. I like it very much
52858. About myself. My Biographical time-line 48 KB
  Мета уроку:систематизація пройденого лексико–граматичного матеріалу; формування комунікативних навичок усного мовлення; формування навичок аудіювання через прослуховування тексту з метою виділення ключової інформації; - biography to be born ballet primary school secondary school university to cheer to clap
52859. Освіта в Англії 192 KB
  Great Britain does not have a written constitution, so there are no constitutional provisions for education. The system of education is determined by the National Education Acts. It has many different faces, but one goal. Its aim is to realize the potential of all, for the good of the individual and the country. It embraces two educational purposes: first it gives a general education to all children, and second, it selects the most able and gives them more advanced education.