Математические и статистические.

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

Числовые значения, используемые для вычисления значений функции, называют аргументами, возвращаемые вычисления значения – результатами. Запись функции начинается с ее названия; аргументы функции записывают в круглых скобках, и отделяются друг от друга символом ";". Помните, что пробел так же является символом, поэтому в ряде случаев наличие непредусмотренного пробела рассматривается как ошибка. В качестве аргументов могут быть использованы числа, текст, логические значения, массивы, значения ссылок или ошибок. Аргументы могут быть константами или формулами. Эти формулы, в свою очередь, могут содержать функции. Если функция является аргументом другой функции, то ее называют вложенной. Допускается использовать до семи уровней вложенности функций. Значения вложенных функций должны соответствовать допустимым значениям аргумента основной функции. Некоторые функции не имеют аргумента. Например, чтобы вернуть значение числа, используется формула, содержащая функцию ПИ () без аргументов:

=ПИ()

Ряд функций имеет необязательные аргументы, которые могут отсутствовать. Вызов Мастера функций производятся командой Выставка \ Функции или кнопкой Мастер функции панели инструментов Стандартная. Рекомендуется всегда набирать формулу строчными буквами. Благодаря тому, что Excel всегда преобразует все корректные имена функций и имена ячеек в прописные буквы, можно узнать в каком месте ошибка.

Задание № 18.

Составить таблицу значений функции у = 2,5*sin(x) c 8 знаками после запятой на интервале 0,2 … 1,5 с шагом 0,1; выполнить округление до 4 и 2 знаков после запятой, вычислить в обоих случаях значение абсолютной и относительной погрешности округления, найти для обоих случаев наибольшее значение погрешности округления. На рисунке показаны результаты вычислений.

Абсолютная погрешность числа y определяется как модуль разности этого числа y и его приближенного значения: Относительная погрешность определяется как модуль отношения абсолютной погрешности числа y к самому числу: .

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

Первая фаза – создание заголовка, поясняющих надписей, исходных данных. Вторая фаза – заполнение первой строки таблицы расчетными формулами. В ячейках В6, В7, и В8 записаны значения левой и правой границ интервала и шага табулирования соответственно. В строке № 1 таблицы последовательно записаны: Функция sin (x) может быть введена с помощью Мастера функций. Эта функция относится к категории Математические о на имеет один аргумент.

Рассмотрим подробно порядок работы с Мастером функций.

1. Нажать левую кнопку с символами . Откроется диалоговое окно.

2. В левом окне выбрать категорию функций – Математические.

3. В правом окне выбрать функцию sin (x).

4. Нажать на экране кнопку Далее. Вам предлагается ввести значение аргумента функции. Следует указать адрес ячейки, в которой находится значение аргумента. Его можно ввести вручную с клавиатуры, удобно так же указывать на ячейку щелчком мыши. Если диалоговое окно перекрывает нужную область таблицы, то его можно перетащить в другое место экрана. Для этого надо установить указатель мыши на заголовок, нажать левую кнопку и, не отпуская ее, переместить окно.

5. Теперь можно нажать кнопку Готово. Диалоговое окно закроется.

6. В строке формул индицируется набранное вами с помощью Мастера функций математическое выражение. Чтобы ввести его в ячейку, нажмите клавишу Enter. В ячейке появится вычисленное значение функции. Ввод функции ОКРУГЛ(арг1;арг2) производится аналогично. Но эта функция имеет два аргумента: первый – округляемое значение (адрес ячейки, где хранится соответствующее число), второй – количество десятичных разрядов после запятой в округленном числе. Данная функция так же относится к категории Математические.

Остальные формулы этой строки не требуют комментариев. В следующей строке записаны:

ﺀ Формула для вычисления номера текущей строки;

ﺀ Формула для вычисления текущего значения х.

На этом ввод данных и расчетных соотношений в таблицу закончен. Выделите ячейку D7, нажмите правую кнопку мыши и выберите команду Копировать.

Выделите в столбце D вниз, начиная со строки 8, нужное число ячеек. Выполните команду Вставить контекстного меню; в ячейках будут записаны номера строк таблицы. На рисунке 16 установлен режим индикации формул. Повторите аналогичную процедуру для текущих значений х. Здесь установлен режим индикации числовых значений (команда Сервис \ Параметры \ Вид, флажок Формулы снят). Теперь выполним копирование остальной части строки №1 в массив ячеек F7:L19. Выделим массив ячеек F6:L6 для копирования и скопируем его содержимое в буфер (можно использовать основное меню – команда Правка \ Копировать, кнопку Копировать в буфер панели инструментов Стандартная или контекстного меню). Затем следует выделить весь массив ячеек для копирования или его левый столбец, установит курсор в левый верхний угол массива, выполнить команду Правка \ Вставить основного меню, или щелкнуть мышью по кнопке Вставить из буфера панели инструментов Стандартная, или использовать команду Вставить контекстного меню. Для нахождения максимальных значений относительных погрешностей δ1, δ2 воспользуемся функцией МАКС категории Статистические. Синтаксис функции так же разъясняется в Мастере функций.

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

Редактирование формул и выражений функций выполняется обычным способом в строке формул после выделения ячейки либо непосредственно в ячейке после нажатия функциональной клавиши F2, либо двойного щелчка мыши по ячейке.

Задания для самостоятельной работы.

1. Составить таблицу для расчета площади и объема шара, если радиус изменяется от__ до__, с шагом__.

2. Задача на оптимизацию. Дан квадратный лист картона со стороной 57. Из него склеили коробочку делая квадратные вырезы l с углов листа. Найти значения величины l, при котором объем склеенной коробочки будет максимальным. Шаг изменения величины l равен 1. Записать в тетрадь пример формулы расчета объема и поиска максимального значения.

3. Составить таблицу персональных данных сотрудников предприятия. Вводятся дата рождения и дата начала работы. Определяется возраст и стаж работы. Использовать функцию СЕГОДНЯ() и ДНЕЙ360(…).

4. Составить таблицу из 15 значений функции при различных значениях аргумента x, изменяющегося c шагом h=0,1, начиная со значения . Таблица с исходными данными должна включать значение шага и начальное значение аргумента . Расчетная таблица должна иметь строку с заголовками: номер строки i, значение аргумента x, значение функции y в соответствующих столбцах таблицы. Значения аргумента должны содержать 2 цифры после запятой, а значения функции – 4 цифры.

 

Логические функции.

Логические функции входят в состав многих формул. Эти функции необходимы для вычисления результатов анализа тех или иных условий. В Excel используются следующие логические функции: ИСТИНА(), ЛОЖЬ(), НЕ (арг), И (арг1, арг2…), ИЛИ(ИЛИ арг1, арг2), ЕСЛИ (арг_лог; арг1; арг2). Логические функции и правила работы с ними изучаются алгеброй логики. Простейшие действия с логическими функциями, необходимые для программирования элементарных операций, легко осваиваются на примерах.

Задание № 19.

Составить таблицу значений функции у(х) в диапазоне {-0,2; 3} с шагом 0,2

Вычислите сумму отрицательных значений у(х); вывести на экран столбцы для отображения выражения, по которому вычисляется значение у(х), и комментарии и относительно достижения правой границы диапазона изменения х. Результаты вычислений записать с 6 разрядами после десятичной запятой. На рисунке приведены фрагменты таблицы начальных этапов выполнения задания.

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

Строка № 10 в таблице – пустая, нулевое значение числа в ячейках. Необходимые формулы после выполнения начального этапа скопированы или распространены в заданные ячейки.

Задания для самостоятельной работы.

1. Составить бланк для вычисления корней квадратного уравнения. Записать решение задачи в тетрадь.

 

2. Составить таблицу значений функции для изменяющегося с шагом h= 0,05 аргумента, начиная с . При достижении расчет y завершить. Форматировать аргумент числовым форматом с 2 десятичными разрядами, а функцию числовым форматом с 7 десятичными разрядами. Дополнить таблицу округленным значением функции (округление производить до 3-х десятичных разрядов). Вычислить относительную и абсолютную погрешность округления.

Дополнить таблицу столбцом анализа необходимости завершения расчетов, если для всех значений аргумента произведен расчет. Столбец анализа на завершение расчетов содержит проверку (в этом столбце выводится текст СТОП или ПРОДОЛЖИТЬ).

3. В предыдущей таблице предусмотреть столбец анализа на значение относительной погрешности: если , то выводится текст ”малая погрешность”, если , то текст “высокая погрешность, в противном случае нужно выводить саму относительную погрешность . Здесь граничные значения относительных погрешностей заданы: , .

4. Составить таблицу значений функции, называемой равномерным распределением:

Значения x изменяются, начиная от –0,5 с шагом h=0,1, но не более чем до величины . Параметры a =1 и b =2,5 постоянные.

Использовать логическую функцию ИЛИ.

Ввести в расчетную таблицу столбец анализа на завершение вычислений (в этом столбце выводится текст СТОП или ПРОДОЛЖИТЬ).

Ввести в расчетную таблицу столбец анализа номера формулы, используемой для вычисления y (в этом столбце выводится текст ФОРМУЛА1 или ФОРМУЛА2).

5. В предыдущем задании вместо функции ИЛИ использовать дополнительную функцию ЕСЛИ.

6*. Изменить предыдущую таблицу расчета y, исключив из нее одну строку со значением x=S, которое записывается в таблице исходных данных. Проверить расчетную таблицу при значениях S= 0,2; S= 0,7; S= 0,9, S =1,5.