№3. Основные статистические функции для работы в среде MS Excel
Цель работы: Использовать Мастер функций для ввода и редактирования формул. Вычисления в Excel. Использование мастера функций.

Задание 1 Использование имен ячеек в формулах

Вычислить значение для различных значений х и у, задаваемых пользователями

1. 
   Переименовать лист 1 в Расчет по формуле.
   
2. 
   Создать таблицу для расчета по формуле, выполняя действия:
   

a) в ячейки A1, A2, A3 занести обозначения переменных X, Y, Z;

b) в ячейку B3 занести формулу для вычисления Z. Значения X, Y хранятся в ячейках с адресами B1 и В2. Ввод адресов X и Y выполнить с использованием мыши.

После завершения ввода формулы в ячейке В3 появилось сообщение об ошибке #ДЕЛ/0! (деление на ноль). Почему?

c) задать значения: X=10.5, Y=-3.7. Чему равно значение Z?;

d) задать новые значения: X=5.1∙ 10¹⁷ (в экспоненциальной форме), Y=2, посмотреть результат

3. 
   Произвести те же вычисления, используя в формулах имена ячеек:
   

a) в ячейки А5, А6, А7 занести обозначения переменных X, Y, Z;

b) присвоить имена X, Y, Z ячейкам В5, В6, В7, используя команду меню Вставка – Имя – Создать;

c) занести в ячейку В7 формулу для вычисления Z. Ввод адресов X и Y выполнить с использованием мыши;

d) задать значения: X=10.5 и Y=2;

Ctrl+.

e) отобразить на листе формулы, для чего нажать

Сравнить формулы в ячейках В3 и В7.

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

Замечание: Для данной таблицы можно было использовать в формуле имена Х и Y, не присваивая эти имена ячейкам. По умолчанию ячейки со значениями Х и Y получают имена из ячеек в столбце слева;

Ctrl+.

---

f) возвратиться в режим отображения значений, для чего нажать

Задание 2 Многократное вычисление по формуле

Вычислить значение для различных значений Х и Y , заданных таблицей

Х	-4	-2	0	2	4	6
Y	1	4	7	10	13	16

2.1 Переименовать лист 2 в Таблица значений функций

2.2 В ячейки А1, А2, А3 занести обозначения переменных X, Y, Z.

2.3. В ячейки В1:G1 и B2:G2 занести соответственно значения Х и Y, используя автозаполнение.

2.4. В ячейку В3 занести формулу для вычисления значений Z по значению Х в ячейке В1 и значению Y в ячейке В2.

2.5. Скопировать формулу из ячейки В3 в ячейки С3:G3, используя автозаполнение. Замечание: Чтобы для данной задачи формула содержала имена Х и Y, необходимо присвоить имя Х диапазону B2:G2. При наборе формулы вводить X и Y непосредственно или выделением соответствующих диапазонов.

2.6 В результате получится таблица:



Задание 3 Вычисления в плоской таблице
Переименовать лист 3 в Таблица функции 2-х переменных. Вычислить значение для всех возможных пар значений Х и Y, изменяющихся следующим образом: X изменяется от –4 до 6 с шагом 2, Y изменяется от 1 до 16 с шагом 3,т.е. создать таблицу.



Задание выполнить двумя способами:

3.1. В формуле использовать смешанную адресацию.

3.2. Скопировать таблицу ниже. Присвоить диапазонам имена X_ и Y_ соответственно. В формуле использовать имена диапазонов.
Задание 4 Создание таблицы тригонометрических функций с использованием мастера функций

4.1. Переименовать лист 4 в Таблица и график.

4.2. Задать шапку таблицы тригонометрических функций.

4.3. Заполнить первый столбец таблицы значениями от 0 до 180 с шагом 15, используя автозаполнение.

4.4 Вычислить первое значение второго столбца, применяя функцию

---

РАДИАНЫ к соответствующему значению из первого столбца, используя Мастер функций. Заполнить остальные ячейки второго столбца автозаполнением



4.5. Аналогично вычислить в третьем столбце значения функции SIN. Аргументами этой функции будут значения второго столбца.

4.6. В четвертом столбце вычислить значения COS.

4.7. В пятом столбце вычислить гиперболический синус, используя

соотношение . Аргументами функции экспоненты являются значения второго столбца.

4.8. В шестом столбце вычислить , используя Мастер функций.

4.9. Построить графики функций sin(x) и cos(x) для угла, заданного в градусах:

a) скрыть второй столбец, для чего выделить столбец, выполнив щелчок правой кнопкой мыши на заголовке столбца В, и в контекстном меню выбрать команду Скрыть,

b) выделить диапазон ячеек А1:D14,

c) на панели инструментов Стандартная нажать кнопку Мастера диаграмм ,

d) на первом шаге Мастера диаграмм выбрать тип диаграммы – , вид – . Нажать кнопку для перехода ко второму шагу и еще раз для перехода к третьему шагу,

e) на третьем шаге Мастера диаграмм ввести название диаграммы – Графики функций sin(x) и cos(x), Ось Х (категорий) – Х, градусы, Ось Y (значений) – Y. Нажать кнопку Далее.

f) на четвертом шаге Мастера нажать кнопку и получить график

Графики функций sin(x) и cos(x)



Задание 5 Расчет колебаний

Отобразить графически затухающие колебания за время t от 0 до 50. Зависимость амплитуды х от времени t

---

описывается в виде x = A₀ sin(t+ ₀ ), где А₀=50, δ=0.1, φ₀=0. Значение  рассчитывается формуле . Значение ₀ принять равным 0.7. Задание выполнить на листе 5, которому присвоить имя Расчет колебаний. Результирующая таблица и график приведены на рисунке – График затухающих колебаний:




Задание 6 Вычисление площади и углов треугольника
Даны три стороны треугольника а, b, с. Требуется вычислить:

1. Площадь фигуры по формуле Герона , где р – полупериметр.

2. Углы треугольника, используя теорему косинусов cos α , где α – угол, лежащий против стороны а.

6.1. Переименовать лист 6 в Площадь и углы треугольника.

6.2. В ячейку А1 ввести заголовок: Стороны треугольника.

6.3. В ячейки А2, А3, А4 ввести обозначения сторон а, b, c; в ячейки А6 и А7 – обозначение полупериметра Р и площади S.

6.4. Присвоить ячейкам В2, В3, В4, B6 имена a, b, c, p соответственно, используя команду меню Вставка - Имя – Создать.

Замечание: Ячейке B4 было присвоено имя c _, а не ожидаемое имя с. Это связано с тем, что имена R/r и C/c в Excel зарезервированы (C – column – столбец, R – row - строка), поэтому Excel ввел в имя символ подчеркивания.

6.5. В ячейку В6 ввести формулу для вычисления полупериметра.

6.6. В ячейку В7 ввести формулу для вычисления площади. Эту формулу можно ввести или с помощью мастера функций или непосредственно как следующее выражение: =КОРЕНЬ(p*(p-a)*(p-b)*(p-c_))

Замечание: Обратите внимание на ввод имени ячейки, где хранится значение с.

6.7. Задать значения сторон: a=3, b=4, c=5. Площадь S равна 6.

6.8. Задать новое значение a=10. В ячейке с результатом появилось сообщение об ошибке #Число!. Чтобы выявить причину ошибки, сделать ячейку активной, открыть список действий

---

и выбрать команду Показать этапы вычисления. При вычислении площади под корнем получается отрицательное число: дело в том, что треугольника со сторонами 10, 4, 5 не существует.

6.9. Заменить значение а на прежнее (а=3).

6.10. В ячейки А10, А11, А12 ввести заголовки Угол a, Угол b, Угол g. Изменить латинские буквы (a, b, g) на греческие (,, ):

a) щелкнуть дважды по ячейке А10 для перехода в режим редактирования,

b) выделить букву a,

c) меню Формат – Ячейки, вкладка Шрифт,

d) выбрать шрифт Symbol,

e) аналогично изменить шрифт у остальных названий.

6.11. Для расчета углов в градусах ввести в ячейки В10, В11, В12 формулы: =ГРАДУСЫ(ACOS((b^2+c_^2- a^2)/(2*b*c_)))

=ГРАДУСЫ(ACOS((a^2+c_^2- b^2)/(2*a*c_)))

=ГРАДУСЫ(ACOS((b^2+a^2- c_^2)/(2*b*a)))



6.12. Для контроля правильности расчетов вычислить сумму вычисленных углов.

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

1. Назначение Мастера функций.

2. Способы вызова Мастера функций.

3. Порядок ввода функции с использованием Мастера.

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

5. Ввод вложенных функций и сложных формул.

6. Редактирование функций с использованием Мастера.

---

Смотрите также файлы

• Решение типового варианта Задание I.doc

• Задачи к разделу 1 Финансовая математика.docx

• 1 Алматы аза мемлекеттік гуманитарлыпедагогтік колледжі.docx

• Сказка о спящей красавице. Этот поэтический образ возник из наблюдений за шиповником. Шиповник это дикая роза. Сказка о спящей красавице у некоторых народов так и называется Дикая розочка.docx

• Определение потребности и спроса в лекарственных средствах.docx