Лабораторная работа
Вычисления по формулам с использованием встроенных математических функций Excel
Цель работы:
§ изучить принципы построения и синтаксис математических формул в Excel;
§ изучить встроенные математические функции Excel, соответствующие основным элементарным функциям;
§ освоить различные технологии ввода формул, в частности, использование Мастера функций;
§ применить полученные знания к решению элементарных вычислительных математических задач;
§ развить навыки форматирования таблиц.
Основные понятия
Формулы
Формула задает правило для вычисления нового значения через исходные значения. Формула должна подчиняться определенным правилам записи, т.е. синтаксису. В Excel запись формулы всегда начинают со знака равенства. Часть формулы, следующая за знаком равенства, называется выражением.
Формулой в Excel называется последовательность, содержащая следующие элементы:
- знак равенства (=) – признак формулы в Excel;
- операнды (числа, текст, ссылки на ячейки, ссылки на массивы ячеек, встроенные функции);
- знаки операций (иногда их называют операторами);
- круглые скобки, причем число открывающих скобок должно быть равно числу закрывающих.
В первой работе уже приводились примеры вычисления по формулам, в которых выполнялись арифметические операции над числами. Числа в них задавались либо непосредственно в формуле, либо в виде ссылок на ячейки. Сейчас мы расширили это определение, включив в формулы функции в качестве операндов. Будем рассматривать только встроенные функции Excel.
Встроенные функции Excel
Встроенные функции Excel – это функции, вычисление которых выполняется по определенным алгоритмам, содержащимся в приложении Excel. Вызов встроенной функции происходит при вычислении по формуле, содержащей эту функцию. Запись функции в формуле Excel аналогична записи функций в математике. Она имеет вид 
, где f – имя функции, 
- аргументы. В общем случае аргументами функций могут быть данные любого вида, но для конкретной функции возможные аргументы определяются ее синтаксисом. Аргументы отделяются друг от друга точкой с запятой. Существуют встроенные функции, не содержащие аргументов, например, число p вычисляется с помощью функции ПИ(). Как видим, в этом случае после имени функции нужно ставить скобки, которые и являются признаком функции в записи. Встроенные функции Excel разбиты на категории. Каждая категория функций предназначена для определенных целей, например, имеются математические, логические, статистические функции и т.д. В данной лабораторной работе рассмотрим математические функции, причем только те, которые соответствуют элементарным функциям в математике. Они перечислены в табл. 2.1.
Таблица 2.1
| Математическая функция | Встроенная функция Excel | Тип аргументов | Пояснение |
| |x| | ABS(x) | Любое число | Абсолютная величина x (модуль x) |
| arccos x | ACOS(x) | “ | Значение функции выражено в радианах |
| arcsin x | ASIN{ x) | “ | Аналогично предыдущему |
| arctg x | ATAN(x) | “ | “ |
| cos x | COS(x) | “ | Косинус величины x, выраженной в радианах |
| EXP(x) | “ | Экспонента от x |
Продолжение таблицы 2.1
| ln x | LN(x) | “ | Натуральный логарифм x |
| logax | LOG(x;a) | “ | Логарифм x по основанию a |
| lg x | LOG10(x) | “ | Десятичный логарифм x |
| sin x | SIN(x) | “ | Синус величины x, выраженной в радианах |
| tg x | TAN(x) | “ | Тангенс величины x, выраженной в радианах |
| КОРЕНЬ(x) | “ | Квадратный корень |
| p | ПИ() | Без аргумента | Число p |
| xa | СТЕПЕНЬ(x;a) | Любые числа | x в степени a |
Замечание. При наборе формул с клавиатуры безразлично, набираются строчные или прописные буквы, но нужно соблюдать соответствие языка имени функции. Ссылки на ячейки записываются только латинскими буквами. При указании типа аргумента не рассматриваются ограничения на область определения функций, но, разумеется, их нужно соблюдать.
Операции
Операции (арифметические и некоторые другие действия) в формулах записываются с помощью специальных символов, называемых знаками операций. Полный список операций Excel приведен в табл. 2.2.
Таблица 2.2
| Знак операции | Операция | Пример записи |
| Арифметические операции | ||
| + | сложение | =А1+2 |
| - | вычитание | =4-С4 |
| * | умножение | =А3*С6 |
| / | деление | B3/5 |
| % | процент | =10% (равно 0,01) |
| ^ | возведение в степень | =2^3 (равно 8) |
Продолжение таблицы 2.2
| Операции сравнения | ||
| = | равно | A5=0 |
| < | меньше | A5<1 |
| > | больше | B3>100 |
| <= | меньше или равно | 3<=2*A10 |
| >= | больше или равно | A10>=0 |
| < > | не равно | A10< >5 |
| Операция связывания ячеек | ||
| : | Диапазон | =СУММ(А1:С10) |
| Текстовый оператор соединения | ||
| & | соединение текстов | =”Ответственный”&” Иванов И.П.” |
Операции выполняются над некоторыми данными (операндами). Операндом может быть число, ссылка на ячейку, ссылка на диапазон ячеек, функция, выражение, взятое в скобки. Рассмотрим формулы Excel:
1) =4-5,2+3,68 2) =2*5+12*6,2
Порядок действий в первой формуле следующий: вычитание, затем сложение. Во второй формуле сначала вычисляется 2*5, затем 12*6,2, после этого выполняется сложение ранее вычисленных чисел. Порядок выполнения операций определяется приоритетом операций. Для изменения порядка действий нужно использовать скобки.
Порядок вычисления значения по формуле Excel:
- вычисляются значения функций, входящих в формулу;
- вычисляются выражения в скобках;
- выполняются операции слева направо с учетом приоритета.
Приоритет арифметических операций в формулах Excel указан в табл. 2.3.
Таблица 2.3
| Знак операции | Операция | Свойства | Приоритет |
| - | Изменение знака | Унарная | |
| ^ | Возведение в степень | Бинарная | |
| *, / | Умножение, деление | Бинарная | |
| +, - | Сложение, вычитание | Бинарная |
Комбинировать арифметические операции с прочими не рекомендуется (за исключением связывания массива).
Примеры
1. Порядок вычислений по формуле: =3+5*COS(B4)-2*A2:
§ COS(B4)
§ 5*COS(B4)
§ 2*A2
§ 3+ 5*COS(B4)
§ 3+5*COS(B4)-2*A2
2. Порядок вычислений по формуле =(3+A2^3/2)*3/5:
§ A2^3
§ A2^3/2
§ 3+ A2^3/2
§ (3+ A2^3/2)*3
§ (3+ A2^3/2)*3/5
Замечания
1. При наборе сложной формулы легко сделать ошибку, поэтому надо хорошо знать синтаксис формул, чтобы в случае необходимости скорректировать формулу набором символов с клавиатуры.
2. Иногда Excel выводит подсказку пользователю, предлагая внести исправления в формулу. Их можно принять или отвергнуть после анализа предложения.
3. Если формула не может быть вычислена, в ячейке появляется сообщение об ошибке, которое начинается символом #.
Сообщения об ошибках.
#ДЕЛ/0! - деление на нуль
#ЧИСЛО! – недопустимый аргумент числовой функции
#ЗНАЧ! – недопустимое значение аргумента или операнда
#ИМЯ? – неверное имя ссылки или функции
#Н/ Д! – неопределенные данные
#ССЫЛКА! – ссылка на несуществующие ячейки
При обнаружении ошибки следует перейти в режим редактирования и исправить формулу. В случае затруднений надо провести синтаксический анализ формулы и ввести ее заново.
Порядок выполнения задания
Пример 1. Вычислить объем и площадь поверхности заданного конуса с основанием R и высотой h. Значения R и h заданы. Положить R=1 м, h=3 м. Отчет представить в виде распечатки рабочих листов, содержащих условие задачи, расчетные формулы, расчеты в Excel в режиме отображения данных и формул.
Решение:
1. Расчетные формулы. (Формулы рекомендуется предварительно записать в тетрадь):
· образующая 
· площадь основания конуса 
· площадь боковой поверхности 
· площадь поверхности конуса 
· объем конуса 
2. Создание рабочего листа с заданием и расчетными формулами. Порядок действий рекомендуется следующий:
· создать новый файл и сохранить его в личной папке под именем Конус.xls;
· в ячейку A1 ввести тему работы; в нижележащие ячейки (например, A3, A4) ввести номер примера и текст задачи;
· в отдельные ячейки A6:A7 и A10:A14 ввести обозначения размеров конуса с пояснениями; ввести данные: значения 1 и 3 для R и h соответственно в B6, B7;
· выполнить вставку расчетных формул с помощью приложения MS Equation 3.0.
