Математические, финансовые и другие функции

Для удобства работы пользователя при построении формул функции в Ехсеl разбиты по категориям: функции управления базами данных и списками, функции даты и времени, финансовые, статистические, текстовые, математические, логические (рис. 13).

 

Рис. 13. Панель Библиотека функций на вкладке Формулы

 

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

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

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

В Ехсеl широко представлены математические функции, в частности помимо действий с числами можно выполнять операции округления.

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

 

Правила синтаксиса при записи функций

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

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

После этого вводится имя функции и сразу за ним - список аргументов в круглых скобках. Аргументы отделяются друг от друга точкой с запятой «;». Скобки позволяют Ехсеl определить, где начинается и где заканчивается список аргументов (рис. 14).

 

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

 

Рис. 14. Запись функций

 

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

Например, в формуле, отображенной на рис. 15, осуществляется суммирование ячеек В2, ВЗ, В4, В5 и Е6.

 

Рис.15. Суммирование ячеек

 

Рассмотрим работу функции ОКРУГЛ(арг1;арг2), которая округляет число до заданного количества знаков после запятой и имеет два аргумента:

• арг1 - адрес ячейки с числом (или само число), которое нужно округлить;
• арг2 - количество цифр после запятой у числа после округления.

Чтобы округлить число 2,71828, находящееся в ячейке А1, с точностью до одного, двух или трех знаков после запятой и записать результаты вычислений соответственно в ячейки В1, С1 и D1, необходимо действовать следующим образом:

1. Ввести число 2,71828 в ячейку А1.

2. Ввести в ячейки В1, С1 и D1 такие формулы (рис. 16): =ОКРУГЛ(А1;1)

=ОКРУГЛ(А1;2)

=ОКРУГЛ(А1;3)

 

Рис. 16. Результат использования функции округления

 

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

=СУММ(ОКРУГЛ(А1;2);ОКРУГЛ(А2;2))

Здесь функция ОКРУГЛ является вложенной. Ехсеl позволяет использовать в формулах не более семи уровней вложенности функций.

В Ехсеl существуют функции, которые не имеют аргументов. Примерами таких функций являются ПИ (возвращает значение числа p, округленное до 15 знаков) или СЕГОДНЯ (возвращает текущую дату). При использовании подобных функций следует в строке формул сразу после названия функции ставить круглые скобки. Другими словами, чтобы получить в ячейках значение числа p или текущую дату, нужно ввести формулы такого вида:

=ПИ()

=СЕГОДНЯ()

ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ

Вариант 1

1. На первом листе открытой книги набрать следующую таблицу

Фамилия И.	Алгебра	Геометрия	Общ. пок-ль
	Кол-во оценок	Баллы	Средний балл	Кол-во оценок	Баллы	Средний балл
Иванов М.						4,5
Петров Д.
Сидоров В.						3,571428571

2. Набрать заголовок таблицы Экспресс оценка учащихся по точным дисциплинам.

3. Результат столбцов Средний балл получить при помощи формулы.

4. В столбце Средний балл осуществить округление числа с точностью до двух знаков после запятой.

5. Посчитать столбец Общий показатель, получая сумму двух столбцов Средний балл.

6. Переименовать лист1 в лист Математика.

7. На втором листе создать аналогичную таблицу с блоком гуманитарных дисциплин (н-р, Литература, История).

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

Вариант 2

1. На первом листе открытой книги набрать таблицу, содержащую данные о ДТП с участием детей за январь месяц 2008/2009 гг.

Наименование ОВД	Всего	Погибло	Ранено
ДТП
ГУВД по г.Тамбову
Жердевский РОВД
Кирсановский ГРОВД
Котовский ГОВД
Мичуринское ГУВД
Мичуринский РОВД
Моршанский ГОВД
Моршанский РОВД
Рассказовский ГРОВД
Бондарский РОВД

 

2. На втором листе создать аналогичную таблицу за февраль месяц 2008/2009 гг.

Наименование ОВД	Всего	Погибло	Ранено
ДТП
ГУВД по г.Тамбову
Жердевский РОВД
Кирсановский ГРОВД
Котовский ГОВД
Мичуринское ГУВД
Мичуринский РОВД
Моршанский ГОВД
Моршанский РОВД
Рассказовский ГРОВД
Бондарский РОВД

 

3. Переименоватьпервый лист книги Excel лист1 в 01, а лист2 –в 02.

4. На третьем листе создать таблицу с такими же параметрами, но данные получить, суммируя результаты за два месяца одного года.

5. Обозначить лист3 как лист с названием +2.

Вариант 3

1. На первом листе открытой книги набрать таблицу с заголовком Платежное извещение

Адрес: ул. Пролетарская, 11, кв. 067
Лицевой счет
Период	янв.08
Всего к оплате
Добр. страх жилья:		23,35
Всего со страховкой:
Вид платежа (ед. изм.)	Тариф	Объем	Начислено по тарифу
Сод. и рем. Жил. (м2)	4,33	46,7
Отопление (м2)	23,68	46,7
Газ (плиты) (чел)
Водоснабжение (чел.)	84,27
Водоотведение (чел.)	58,16
Гор. Водоснабж. (чел.)	150,73
Вывоз ТБО (чел.)	20,13
Домофон
Всего к оплате:
Приборы	На день	Предыдущее	Кол-во (кВт.)
учета	выписки
Свет/1/

2. В основную таблицу в столбец Вид платежа добавить строку Электроэнергия (кВт), со значением тарифа равным 2,05р.

3. Посчитать количество киловатт в строке Свет по прибору учета (разница между показаниями прибора).

4. Записать посчитанное количество киловатт в строку Электроэнергия (кВт), осуществив связь между этими ячейками;

5. Значения столбца Начислено по тарифу получить с помощью формулы (необходимо перемножить значения в столбцах Тариф и Объем).

6. Посчитать строку Всего к оплате.

7. Оформить итоговые строки над таблицами Всего к оплате и Всего со страховкой осуществив связь с основной таблицей через формулу.

Вариант 4

1. На первом листе открытой книги Excel набрать таблицу по продажам телевизоров

Модель	Цена	Продано, шт.	Доход, руб.
Panasonic TX-R32LM70	25 848,00р.		51 696,00р.
Panasonic TX-R32LX70	33 084,00р.		99 252,00р.
Panasonic TX-R32LX700	44 604,00р.		44 604,00р.
Итого:			195 552,00р.

2. Столбец Доход, руб посчитать при помощи формулы (“умножение Цены на Продано, шт ”).

3. Переименовать лист1 в Panasonic.

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

курс евро

36,20р.

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

6. На втором листе книги Excel аналогичным образом получить следующую таблицу продаж телевизоров:

Модель	Цена	Продано, шт.	Доход
Philips 20PF4121	10 980,00р.		43 920,00р.
Philips 20PF5120	16 812,00р.		84 060,00р.
Philips 20PF5123	11 376,00р.		11 376,00р.
Итого:			139 356,00р.

Вариант 5

1. Набрать заголовок таблицы Ведомость заработной платы.

2. Оформить следующую таблицу, введя фамилии и величины оклада в рублях.

3. Налог посчитать 13% от Оклада; сумма к выдаче составляет разность между Окладом и Налогом.

4. Ввести количество детей в столбец число детей.

№	Фамилия,			Сумма к	число
	имя отчество	Оклад	Налог	выдаче	детей

5. В отдельной от таблице ячейке оформить значение Начисление на детей.

 

Начисление на детей

153р

6. На лист2 оформить следующую таблицу, осуществив связь с таблицей, расположенной на первом листе через столбец Фамилия.

№	Фамилия, имя отчество	Сумма

7. Значения в столбце Сумма получить при помощи формулы (умножая количество детей на значение Начисление на детей), используя абсолютную ссылку на ячейку, в которой расположено значение Начисление на детей.

 

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

1. Как называется документ в Excel и из чего он состоит?
2. Как добавить новый лист в книгу? Как переименовать лист?
3. Что такое ячейка?
4. Из чего состоит адрес ячейки?
5. Что такое активная ячейка?
6. Что такое абсолютная и относительная адресация?
7. С какого символа начинают ввод формулы в ячейку?
8. Что такое Мастер функций, как он работает?
9. Как скопировать или переместить ячейку?
10. Как отредактировать содержимое ячейки?
11. Что такое автозаполнение и как оно выполняется?
12. Как удалить (вставить) строку (столбец)?
13. Что такое автосуммирование?

 

Список используемой литературы

 

1. Глушаков, С.В. Microsoft Office 2007. Лучший самоучитель / С.В. Глушаков, А.С. Сурядный. – изд. 3-е доп. и переработ. – М.: АСТ: АСТ МОСКВА: Владимир: ВКТ, 2008. -446.[2] c. (Учебный курс).
2. Глушаков, С.В. Microsoft Excel 2007. Лучший самоучитель / С.В. Глушаков, А.С. Сурядный. – изд. 2-е доп. и переработ. – М.: АСТ: АСТ МОСКВА:, 2008. -416 c. - (Учебный курс).
3. С. Симонович, В. Мураховский. Популярный самоучитель работы на компьютере – М.: “Техбук”, 2006. – 576 с.