Функции проверки свойств и значений

Лабораторная работа №2

Работа с функциями

 

Понятие функции. Мастер функций

Функция в Excel представляет собой некоторую, заранее предопределенную формулу, выполняющую некоторое конкретное действие. Функция является как бы «запрограммированной» формулой.

 

Для упрощения создания сложных электронных таблиц пакет Excel содержит множество функций. Функции объединены в категории (группы) по назначению и характеру выполняемых операций:

* математические;

* статистические;

* даты и времени;

* логические;

* проверки свойств и значений;... и другие.

Любая функция имеет вид:

 

ИМЯ (СПИСОК АРГУМЕНТОВ)

 

ИМЯ- это фиксированный набор символов, выбираемый из списка функций;

СПИСОК АРГУМЕНТОВ (или только один аргумент)- это величины, над которыми функция выполняет операции. Аргументами функции могут быть адреса ячеек, константы, формулы, а также другие функции. В случае, когда аргументом является другая функция, мы имеем дело со вложенной функцией.

Например, функция СУММ (С7:C10;D7:D10) содержит список из двух аргументов, каждый из которых является диапазоном ячеек.

 

Аргументы функций

Информация, приведенная в скобках, называется аргументом функции.

Количество аргументов в разных функциях не одинаково. Функция может:

· не иметь ни одного аргумента;

· иметь один аргумент;

· иметь фиксированное число аргументов;

· иметь неограниченное число аргументов;

· иметь необязательные аргументы.

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

 

Аргументами функций могут быть:

ü константы;

ü ссылки на ячейки и диапазоны (можно также использовать имена диапазонов, строки или столбцы);

ü выражения (формулы);

ü функции;

ü массивы (массив – это ряд значений, разделенный точкой с запятой и заключенный в скобки).

 

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

 

 

Добавление функций

1. В строке ввода формул после знака равно можно ввести первую букву требуемой функции, рядом тут де появится список из имеющихся в Excel'е функций, начинающихся на указанную букву.

Рис. Добавление функции через строку ввода формул

 

2. На вкладке Формулы в группе команд Библиотека функций выбрать подходящую.

Рис. Добавление функции через вкладку Формулы

 

Вызвать матер функций (вкладка Формула, группа команд Библиотека функций, кнопка Вставить функцию.

 

Мастер функций

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

1. Вкладка Формулы, группа команд Библиотека функций, команда Вставить функцию .

2. На вкладке Формулы, в группе команд Библитека фукций в любом списке категорий пункт Другие функции.

3. Щелкнуть на кнопке fx слева от строки формул.

4. Нажать комбинацию клавиш <Shift+F3>.

 

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

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

 

Рис. Первый шаг мастера функций – «Выбор функции»

 

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

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

В категории Полный алфавитный перечень представлены функции всех категорий.

В диалоговом окне Мастер функций – Шаг 1 из 2 реализована возможность поиска функции. В поле Поиск функции необходимо ввести одно или несколько ключевых слов. Excel выдаст список функций, удовлетворяющий условиям поиска.

При выборе функции под окном Выберите функции появляется название функции (и аргументов), а также ее краткое описание.

 

Выбрав необходимую функцию, щелкните на кнопке ОК. Откроется диалоговое окно Аргументы функции.

Рис.Второй шаг мастера функций – «Аргументы функции»

 

 

В этом окне следует задать аргументы функции.

Для того, чтобы вставить в качестве аргумента другую функцию надо выполнить следующие действия:

1. поставить курсор в окно аргумента, в качестве которого будет введена другая функция;

2. слева от строки ввода формул раскрыть раскрывающийся список, в котором нужно выбрать нужную функцию:

Рис. Добавление вложенной функции

Если нужной функции в списке нет, то необходимо выбрать пункт Другие функции…

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

После заполнения необходимо нажать на кнопку ОК.

 

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

 

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

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

 

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

2.3.1. Описание некоторых логических функций.

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

ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)

Лог_выражение - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина - это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА.

Значение_если_ложь - это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ.

 

 

Рассмотрим некоторые функции из этой категории на примерах.

Пример 1: Разместить в ячейке C12 максимальное из двух значений, содержащихся в ячейках C10 и C11.

Решение:

Необходимо проанализировать условие C10>C11. Если это условие выполняется, т. е. имеет значение ИСТИНА, то в ячейку С12 нужно записать число из ячейки С10. Если же условие не выполняется, т.е. его значение есть ЛОЖЬ, в ячейку С12 нужно записать число из ячейки С11.

Для анализа условий используют функцию ЕСЛИ, которая имеет синтаксис:

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

=ЕСЛИ(С10>С11;С10;С11)

Пример 2:

Сформировать формулу, определяющую значение ячейки С1 как:

· число из ячейки B1 в четвертой степени, если число в ячейке А1 меньше или равно нулю;

· число из ячейки А1, если в число ячейке А1 больше 0 но меньше 1;

· число из ячейки А1, деленное на 3, в других случаях.

 

Решение:

В качестве значений аргументов функции ЕСЛИ также могут быть функции как исполняемые, так и логические. Так, в данном примере при выполнении условия A1<=0 в ячейку С1 необходимо записать значение функции СТЕПЕНЬ(B1;4). При невыполнении этого условия нужно проверять условие A1<1 вложенной функцией ЕСЛИ.

Итак, в ячейку C1 необходимо ввести формулу:

=ЕСЛИ(A1<=0; СТЕПЕНЬ(B1;4); ЕСЛИ(A1<1; A1; A1/3))

Пример 3:

Если во всех ячейках А1, А2 и А3 записаны положительные числа, то в ячейку А4 записать слово «плюс», если положительное число записано хотя бы в одной ячейке, то в ячейку А4 записать слово «разные», в противном случае в ячейку А4 записать слово «минус».

Решение:

В данном примере необходимо проверять сложное условие, т.е. несколько простых логических условий одновременно. Это осуществляется с помощью логических функций И и ИЛИ.

 

Функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из её аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Синтаксис функции ИЛИ:

ИЛИ(логическое_значение1; логическое_значение2;...)

 

Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

 

 

Синтаксисфункции И:

И(логическое_значение1; логическое_значение2;...)

Таким образом, в ячейку А4 должна быть введена формула:

=ЕСЛИ(И(C12>0;D12>0;E12>0);"плюс";ЕСЛИ(ИЛИ(C12>0;D12>0;E12>0);"разные";"минус"))

 

Функции проверки свойств и значений

 

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

Например, функция ЕПУСТО возвращает логическое значение ИСТИНА, если проверяемое значение является ссылкой на пустую ячейку; в противном случае возвращается логическое значение ЛОЖЬ. Функция ЕТЕКСТ возвращает значение ИСТИНА, если проверяемое значение является ссылкой на текст.

 

Функции из категории «Дата и время»

 

Перейдите на новый лист рабочей книги.

Создайте таблицу:

 

	A	B	C	D
	Дата	День	Месяц	Год
	05.12.89
	04.10.76
	30.05.97
	11.02.47

 

В ячейки столбцов B, C, D записать формулы вычисления дня, месяца и года для дат из ячеек А2:А5. Для этого использовать функции ДЕНЬ, МЕСЯЦ и ГОД из категории «Дата и время».

Ниже создайте следующую таблицу:

 

	A	B	C	D	E
	Дата	День недели	Номер квартала	Номер декады	Номер полугодия
	11.12.89
	07.05.97
	04.10.76
	21.01.99

 

· В ячейки А15и А16 ввести текущую дату двумя способами: используя функции СЕГОДНЯ и ТДАТА из категории Дата и время.

 

 

Примечание: Функция ТДАТА требует расширения столбца.

· Записать в ячейки В11:E16 соответствующие формулы, используя функции ДЕНЬНЕД, ДЕНЬ, МЕСЯЦ из категории Дата и время.

 

 

Создайте на рабочем листе «Примеры Функций» ещё одну таблицу (по прежнему не заполняя последнюю графу- она будет использоваться для сверки результатов):

 

	A	B	C	D
	Логические функции
	Исходные данные	Результат
			да
	слово		да
		-3	-1

 

* В ячейку С13 ввести большее из значений, содержащихся в соседних слева ячейках.

* В ячейку С14 ввести слово «да», если значение ячейки A11 больше значения ячейки B14. В противном случае ввести слово «нет».

* Создать формулу, записывающую в ячейку С15 слово «да», если ячейка A15 пуста или содержит текст. В противном случае в ячейку С15 записать слово «нет».

* Сформируйте формулу, определяющую значение ячейки С16 равным:

-1, если содержимое ячейки B16 меньше 0;

0, если содержимое ячейки B16 равно 0;

формулу A16/B16, если содержимое ячейки

B16 больше 0.

* Сверьте полученные результаты.