Использование функций

Для выполнения упражнений перейти на второй лист текущей рабочей книги и переименовать лист, присвоив ему имя "Функции".

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

 

 

кнопка

Функция, как правило, содержит переменную часть – аргументы.

Аргументы могут быть разного типа у разных функций, их может быть много или не быть вовсе.

 

Аргументы – это исходные

данные, которые используются функцией для получения результата.

Использование Мастера функций существенно облегчает ввод функций.

§ Щелчок на кнопке (Вставка функции), что на стандартной панели, вызывает диалоговое окно Мастера функций, в котором выводится список категорий и список функций, сгруппированных по категориям (шаг 1 из 2 Мастера функций).

§ Выбрать функцию и прочитать её описание в этом окне, затем при помощи кнопки ОК перейти в следующее окно.

§ В окне аргументов выполнить пошаговую подстановку аргументов, необходимых для данной функции: задать вручную в диалоговом окне или отметить указателем мыши в ЭТ.

§ Обратить внимание на результат в подсказке «Значение». Далее - кнопка ОК.

В этой брошюре из-за недостатка места невозможно дать описа­ния всех функций. Их следует искать в Справке Excel, в разделе "Создание и проверка формул в книгах/ Справка по функциям".

Кнопка (Справка) в диалоге Мастера функций дает подробное описание выделенной функции, её аргументов. Здесь же приводятся примеры применения функции.

Список соответствия между русскими и английскими названиями функций имеется в файле FUNCS.XLS, который находится в папке C:\Program Files\ Microsoft Office\0ffice. Из этого файла также есть выход к справке по функциям и примеры.

Использование панели форму л для ввода и изменения формул.

Отобразить панель формул - нажать кнопку Изменить формулу (=) слева от строки ввода.

Рис.3. Использование панели формул.

С помощью панели формул можно легко вставить функцию в формулу – открыть список функций и выбрать нужную. Если функция отсутствует в списке, для вывода дополнительного списка функций следует выбрать строку «Другие функции».

После вставки функции в панели формул отображается имя функции и ее аргументы, описание функции и аргументов, а также возвращаемое функцией и формулой значение.

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

Упражнение 3.2.1. В ячейку А1 ввести формулу: tg x.

Здесь и далее в качестве аргумента x вводится число из ячейки В1 листа "Формулы" (см. упр. 3.1.1 главы 2).

§ установить указатель ячейки на А1;

§ вызвать Мастер функций (кнопка );

§ выбрать категорию "Математические", в окне функций выбрать TAN;

§ в окне аргументов задать x (или В1),

§ ОК. Результат = 1,157821.

G Примечание. Обозначение функций в математике и соответствующие им имена в Excel различаются, например: матем. Excel матем. Excel

tg x - TAN(x) |x| - ABS(x)

arcsin x - ASIN(x) ln x - LN(x)

arccos x - ACOS(X) lg x - Log10(x)

arctg x - ATAN(x) e^x - EXP(x)

Упражнение 3.2.2. В ячейку А2 ввести формулу: tg² x.

При этом следует учесть, что функция в формуле обладает наивысшим приоритетом.

Поэтому правильным будет решение: = TAN(x)^2,

ошибочно:= TAN^2 (x).

Задача 3.2.1. Имеются данные о месячном количестве осадков (в мм) по наблюдениям метеостанции за три года. Подсчитать суммарное, максимальное, минимальное и среднемесячное количество осадков за каждый год и за три года. Подсчитать стандартное отклонение от среднего по годам.

G Рекомендации.

1)задать имена блокам ячеек с данными по годам;

(вызвать список имен - F3).

2) использовать копирование формул.

Упражнение 3.2.3. Вычислить: .

Эта функция представляет со­бой комбинацию

двух функций: y = и z = tg x +1.

Соответ­ствующие функции Excel: KOPEHb(z) и TAN(x)+1.

Порядок действий:

§ Выделить ячейку А3;

§ вызвать Мастер функций;

§ выбрать категорию "Математические", в окне функций выбрать КОРЕНЬ;

§ находясь в поле ввода аргумента функции КОРЕНЬ, в панели функций выбрать TAN и в поле ввода аргумента этой функции ввести имя х.

G Внимание! Не следует щёлкать кнопку "ОК", как обычно — это досрочно завершит ввод фор­мулы. Вместо этого установить в поле вво­да курсор на функции КОРЕНЬ (т.е. на внешней функции). Тот­час второе окно Мастера функций для TAN заменится на вто­рое окно для функции КОРЕНЬ.

§ В поле ввода аргумента отображается TAN(x). Добавляем к этой функции +1 и щелкаем "ОК".

Окончательная формула в строке ввода:

= КОРЕНЬ (TAN(x)+1). Результат = 1.468952.

G Примечание. Функции можно вводить в строке ввода вручную, причем имена функций следует вводить строчными буквами, например: =корень(tan(x)+1). Если введены имена функций правильно, то по завершении ввода они автоматически будут преобразованы в прописные буквы.

Задача 3.2.2. В Excel имеется функция ПИ(), она не имеет ар­гументов (хотя скобки обязательны) и возвращает число =3.14159....

Каким образом вычислить в Excel число е = 2.71828... — основание натуральных логарифмов, если функ­ция, аналогичная ПИ(), для числа е отсутствует?

Задача 3.2.3. Что больше: е или ^е?

3.3.Функции округления

В бухгалтерских расчетах большую роль играют функции, позволяющие округлять результаты вы­числений. В Excel их несколько, все из категории "Математические".

1) Функция ЦЕЛОЕ (число) округляет число до ближайшего меньшего целого. Применяется для вычисления частного от деления нацело.

2) Функция ОСТАТ (число, делитель) вычисляет остаток от деления нацело.

Между функциями ЦЕЛОЕ и ОСТАТ имеет место соотношение:

n = d*ЦЕЛОЕ (n/d) + ОСТАТ (n,d)

3) ОКРУГЛ (число, число_разрядов) - округление числа до указанного числа разрядов.

§ Если число_разрядов больше 0, то число округляется до указанного количества десятичных знаков после запятой;

§ Если число_разрядов равно 0, то число округляется до ближайшего целого;

§ Если число_разрядов меньше 0, то число округляется до указанного количества десятичных знаков до запятой;

Упражнение 3.3.1. В ячейке А1 записано число 143,3184.

§ Разместить в В1 формулу =ОКРУГЛ(А1, 2).

Результат - число 143,32.

 

§ Раз­местить в С1 формулу =ОКРУГЛ (А1, 0).

Результат: число 143.

 

§ Ввести в D1 формулу =ОКРУГЛ (А1, -1).

Результат - число 140.

4) Несколько иные задачи решают функции:

ОКРУГЛВНИЗ (число, число_разрядов),

ОКРУГЛВВЕРХ (число, число_разрядов).

В соответствии с их названиями они работают как функция ОКРУГЛ, но округляют всегда в большую или меньшую сторо­ну. Рассмотрите примеры самостоятельно.

G Примечание.

1) Округление числа можно выполнить установкой соответствующего формата числа - команда Формат/Ячейки/Число, задать количество десятичных разрядов.

2) Кнопки ("Увеличить разрядность", "Уменьшить разрядность") на панели форматирования также устанавливают разрядность числа.

3) Функции округления часто применяются в сложных вычислениях, где имеет место применение вложенных функций.

Упражнение 3.3.2. В ячейке А1 записано целое число, лежащее в промежутке от 0 до 999. В ячейку В1 ввести формулу, которая вычисляет сумму цифр числа.

(Пример: для числа 143 сумма цифр = 1+4+3 = 8)

Ответ: ЦЕЛОЕ(A5/100)+ЦЕЛОЕ(ОСТАТ(A5/10;10))+ОСТАТ(A5;10)

(сотни) (десятки) (единицы)

Упражнение 3.3.3. Имеется выручка от реализации продукции в сумме 21 675 рублей. Рассчитать налог на пользова­телей автодорог 2,5%, в том числе федеральный 0,5% и москов­ский 2%.

Решение:

А В С D

	Выручка от реализации		21 675,00р.
	Налог на пользовате-лей автодорог	2,5%	541,88р.	=ОКРУГЛ(C1*B2;2)
	федеральный	0,5%	108,38р.	=ОКРУГЛ(C1*B3;2)
	московский	2,0%	433,50р.	=ОКРУГЛ(C1*B4;2)

Задача 3.3.1. Идет к -я секунда суток (записана в В3). Сколько полных часов h от 0 до 23 (в В4), полных минут m от 0 до 59 (в В5) и секунд s от 0 до 59 (в В6) прошло к этому моменту. (Пример: k = 13257 = 3*3600+40*60+57,

т.е. h = 3, m = 40, s = 57.)

Задача 3.3.2. В ячейке А2 записано положительное число. Поместить в В2 формулу, которая возвращает первую цифру из дробной части числа.

(Пример: для числа 32.597 формула вернет 5.)

Ответ: = ЦЕЛОЕ((A2-ЦЕЛОЕ(A2))*10).

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

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

Для работы с упражнениями перейти на новый рабочий лист. Дать ему имя "Логика".

Упражнение 3.4.1.Логические выражения.

a) Ввести в ячейку А1 формулу =7>5. Она вернет значение ИСТИНА. Скопировать содержимое А1 в А2 и исправить в А2 формулу: = 3>5. Эта формула вернет значение ЛОЖЬ.

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

 

 

b) Ввести в ячейку А4 число 2, а в ячейку В4 формулу = А4>3. Формула возвращает значение ЛОЖЬ.

Ввести в А4 число 6. Формула возвращает значение ИСТИНА.

В В4 записан предикат.

 

пример:

b2 > 4ac

Сравнение двух арифметических выражений, содержащих переменные, даёт предикат.

 

Высказывание и предикат имеют общее название —

логиче­ское выражение.

Логические операции - операции, которые объединяют сложные логические выражения.

Логические операции реализованы в Excel как функции.

Название	Обозначение	Функция Excel
Отрицание		НЕ
Конъюнкция	(&)	И
Дизъюнкция		ИЛИ

Рис. 4. Перечень логических операций и соответствующих им функций Excel.

У логических функций аргументы могут принимать только два значения: ИСТИНА и ЛОЖЬ. Поэтому логические функции можно задать таблицей, где перечислены все возможные значе­ния аргументов и соответствующие им значения функций. Та­кие таблицы называются таблицами истинности.

Таблица для функции НЕ:

х НЕ(х)

ЛОЖЬ ИСТИНА

ИСТИНА ЛОЖЬ

Таблица для функций И и ИЛИ имеет вид:

X	Y	И(x,y)	ИЛИ(x,y)
ЛОЖЬ	ЛОЖЬ	ЛОЖЬ	ЛОЖЬ
ЛОЖЬ	ИСТИНА	ЛОЖЬ	ИСТИНА
ИСТИНА	ЛОЖЬ	ЛОЖЬ	ИСТИНА
ИСТИНА	ИСТИНА	ИСТИНА	ИСТИНА

Функция И – логическое умножение - объединяет два или более логических выражения. Выдаёт результат ИСТИНА, если все условия (логические выражения) выполняются совместно (одновременно), и ЛОЖЬ, если хотя бы одно не выполняется.

Общий вид функции:

И( лог_выражение 1;лог_выражение 2;… и т.д .)

Упражнение3.4.2. Принадлежит ли значение ячейки А1 диапазону чисел [10,100]?

Решение: И (А1 >= 10; А1 <=100)

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

Общий вид функции:

ИЛИ ( лог_выражение 1;лог_выражение 2;… и т.д .)

Упражнение 3.4.3. Находятся ли значения ячейки А1 вне диапазона чисел [10,100]?

Решение: ИЛИ (А1 < 10; А1 >100)

 

 

 

Рис.5. Иллюстрация к упражнениям 3.4.2 и 3.4.3.

Если учесть то, что лучи [-∞,10] и [100, ∞] дополняют отрезок [10,100] до полной числовой оси, то можно воспользоваться функциями НЕ и И для определения ИЛИ:

ИЛИ (А1 < 10; А1 >100) ≡ НЕ (И (А1 >= 10; А1 <=100))

и наоборот.

На практике логические выражения используются в сочетании с функцией ЕСЛИ, являясь первым аргументом её.

используется для проверки содержимого ячейки и возврата логического значения ИСТИНА или ЛОЖЬ.

Если возвращаемое значение ИСТИНА, выполняется оператор1, иначе - оператор2:

ЕСЛИ ( логическое выражение;оператор 1;оператор 2)