Материалы сайта
Это интересно
Пособие для программ Word и Exel
ЛАБОРАТОРНАЯ РАБОТА № 3. Встроенные функции EXCEL. Статистический анализ. Методические рекомендации. MS EXCEL содержит 320 встроенных функций. Простейший способ получения полной информации о любой из них заключается в переходе на вкладку Поиск из меню ?, после чего необходимо напечатать имя нужной функции и нажать кнопку Показать. Для удобства функции в EXCEL разбиты по категориям (матаматические, финансовые, статистические и т. д.) . Зная, к какой категории относится функция, справку о ней можно получить следующим образом: 1. Щелкните на закладке Содержание в верхней части окна, а затем последовательно пункты Создание формул и проверка книг, Функции листа. 2. Щелкните название нужной категории. 3. Щелкните имя необходимой функции и ознакомьтесь с ее описанием. Обращение к каждой функции состоит из двух частей:имени функции и аргументов в круглых скобках. Аргументы функции могут быть следующих типов: 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 заключается в проверке некоторого условия и выполнения в зависимости от него тех или иных действий. Так, функция ЕСЛИ выполняет проверку условия, задаваемого первым аргументом логич_выр: =ЕСЛИ(логич_выр;знач_да;знач_нет) и возвращает знач_да, если условие выполнено (ИСТИНА), и знач_нет, противном случае (ЛОЖ). Например: I. =ЕСЛИ(А6<10;5;10). Если значение в ячейке А6<10, то функция вернет результат 5, а иначе – 10. II. =ЕСЛИ(B4>80;”Сданы”; ”Не сданы”). Если значение в ячейке B4>80, то в ячейке с приведенной формулой будет записано ”Сданы”, иначе - ”Не сданы”. III. =ЕСЛИ (СУММ(А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. После нажатия кнопки ОК на экран будет выведена гистограмма, а рядом со столбцом карманов появится столбец частот, показывающий, сколько дней летом в Краснодаре имели температуру, попадающую в каждый интервал. Порядок выполнения работы. Каждый вариант состоит из двух заданий. Для выполнения первого задания необходимо: I. На рабочем листе №4 построить таблицу значений функции согласно варианта задания и ее график. II. Определите среднее, минимальное и максимальное значение функции и вывести эти данные на графике. III. Используя логическую формулу, вычислить сумму значений функций, если среднее, минимальное и максимальное значения имеют одинаковые знаки и произведение в противном случае. IV. Произвольной ячейке присвоить имя и сгенерировать в ней случайное число. В таблице значений функции добавить еще один столбец, полученный умножением у на случайное число. Добавить на графике функции второй график, соответствующий полученному столбцу данных. Исходными данными для второго задания являются варианты заданий к лабораторной работе №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 | Контрольные вопросы. I. Для чего предназначены Пакет анализа и каков порядок доступа к его инструментам? II. В задании 2 своего варианта вычислите коэффициент вариации. III. В чем заключаются особенности построения гистограммы распределения данных? IV. Напишите логическую формулу, которая выводит текстовое сообщение ”Вычислена сумма” или ”Вычислено произведение” в зависимости от того, что было вычислено на рабочем листе в п. 3 задания 1. V. Используя информацию о том, что “как правило, 68% данных генеральнлй совокупности с нормальным распределением находятся в пределах одного стандартного отклонения от среднего значения, а 98% - в пределах двух отклонений”, создайте на рабочем листе строку, в которой для задания 1 автоматически будут рассчитываться указанные интервалы. VI. *Напишите программу на языке Бейсик для вычисления среднего, минимального, максимального значений, дисперсии, стандартного отклонения и коэфициента вариации для исходных данных своего варианта в задании №2. * Как уже отмечалось для ввода аргументов можно использовать мышь * Задание для повторения пройденного материала в разделе «Алгоритмизация и программирование».