Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

ЧД – число детей до 18 лет, находящихся на содержании сотрудника;

МРОТ – минимальный размер оплаты труда (указан в ячейке D4).

7. В ячейках H7:H13 рассчитайте величину подоходного налога для каждого сотрудника. Расчет ведется по формуле:
НДФЛ =(ИН - СНОН) * СПН /100, (1.6)

где

СПН – cтавка подоходного налога (указана в ячейке Е4).

8. В ячейках I7:I13 рассчитайте общую сумму удержаний (ВУ) каждого сотрудника:
ВУ=П+НДФЛ (1.7)
9. В ячейках J7:J13 рассчитайте сумму к выдаче (СВ) для каждого сотрудника. Расчет ведется по формуле:
СВ=ИН – ВУ (1.8)
10. В ячейках D15, D16, D17 с помощью соответствующих функций найдите соответственно Максимальную зарплату, Минимальную зарплату и Среднюю зарплату в коллективе.
11. Сохраните созданную вами таблицу в файле Расчет зарплаты.xls в вашей рабочей папке.
1.2. Математические функции

1.2.1. Общие сведения

В Excel имеется более 400 встроенных функций, которые удобно применять во всевозможных расчетах. Каждая встроенная функция Excel работает по принципу «черного ящика»: вы задаете входные данные или аргументы, а функция по заданному в Excel алгоритму вычисляет некоторый результат или выходные данные. В формулах функция выглядит следующим образом:

НазваниеФункции(Аргументы),

где НазваниеФункции – уникальное имя функции, а Аргументы – список аргуметов.

Например:

• 
  COS(число);
  
• 
  СУММ(число1; число2;…);
  
• 
  LOG(число, основание);
  
• 
  КОРЕНЬ(число) и др.
  

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

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

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

---

Рис. 2 Окно диалога Мастер функций
Все встроенные функции условно разделены на несколько категорий:

• 
  Математические;
  
• 
  Статистические;
  
• 
  Финансовые;
  
• 
  Логические;
  
• 
  Текстовые;
  
• 
  Дата и время и др.
  

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


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

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

Используя встроенные математические функции MS Excel найти значение выражения:

.

1. Оформите таблицу:

2. В ячейку B2 запишите формулу для вычисления функции, где в качестве аргумента x укажите ячейку B1:
=(LOG(1+B1^2;2)-TAN((ABS(1-B1))^(1/5)))/(COS(1-B1)+EXP(2-2*B1))*(5-B1)
3. Для проверки правильности записи формулы подставьте в ячейку B1 проверочные значения. В данном примере это значения 1 и 5. Должны получиться следующие значения F(x):

---

1.2.3. Варианты заданий

Найти значения выражений согласно табл. 1.1.

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

№	F(x)	Проверочные значения
		X1	X2
1		2	4
2		1	3
3		1	3
4		2	5
5		1	4
6		1	5
7		1	4
8		1	2
9		3	5
10		4	6
11		1	3
12		1	5
13		1	5
14		2	5
15		1	5

---

1.3. Вычисления с условиями
1.3.1. Общие сведения

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

Это, прежде всего, функции СУММ, МАКС, МИН, СРЗНАЧ. Они известны со школьного курса и не требуют комментариев.

Однако в экономических расчетах довольно часто приходится учитывать различные условия. Поэтому там дополнительно используются следующие функции.
Функция ЕСЛИ

Позволяет производить вычисления по условию.

Имеет следующий формат:
ЕСЛИ(Условие; Значение_Если_Да; Значение_Если_Нет)
Например.

Пусть имеются сведения о расходах и доходах разных организаций следующего типа:

	B	C	D	E
4		Расходы	Доходы	Баланс
5	ТД "Акатуй"	1234	5432
6	ТД "Пятерочка"	2345	1234
7	ТД "Яхтинг"	4321	5432
8	…

В колонку «Баланс» необходимо вывести следующую информацию:

Если доходы превышают расходы, то в указанной колонке вывести “+”, иначе вывести “–“.

Для этого в ячейку Е5 вводится формула: = ЕСЛИ(D5>C5;”+”;”–‘), которая затем копируется на весь столбец Е.
Функции И, ИЛИ

С их помощью можно составлять очень сложные условия для функции ЕСЛИ.

Например, для данных:

	C	D	E	F
9	Фамилия	Пол	Возраст	Статус
10	Петрова	ж	48
11	Кузнецов	м	65
12	Степанова	ж	34
13	Сидорова	ж	56
14	….	….	….	….

---

требуется заполнить колонку «Статус» со значениями «пенсионер» или «не пенсионер».

Формально статус пенсионера определяется по условию:
Если ((Возраст>60) и (Пол = «м»)) или ((Возраст>55) и (Пол = «ж»)).
В синтаксисе Excel это записывается следующим образом (в ячейку F10):
=ЕСЛИ(ИЛИ(И(E10>60;D10="м");И(E10>55;D10="ж"));"пенсионер"; "–")
Затем данная формула копируется на весь столбец F.
Функция СЧЕТЕСЛИ

Подсчитывает количество данных, удовлетворяющих некоторому условию.

Имеет следующий формат:

СЧЕТЕСЛИ(Диапазон просмотра; Критерий)

Например.

Имеются следующие данные:

	C	D	E	F
9	Фамилия	Пол	Возраст	Статус
10	Петрова	Ж	48	–
11	Кузнецов	М	65	пенсионер
12	Степанова	Ж	34	–
13	Сидорова	Ж	56	пенсионер

Необходимо подсчитать количество пенсионеров.

Для этого, например, в ячейку D15 вводим формулу:
=СЧЁТЕСЛИ(F10:F13;"пенсионер")
Функция СУММЕСЛИ

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

Общий формат:

СУММЕСЛИ(Диапазон_проверки _критерия;

Критерий; Диапазон_суммирования)

Например.

Для данных приведенных в таблице вычислить доходы торгового дома «Акатуй».

	С	D	E	F
3	Организация	Дата	Расходы	Доходы
4	ТД Акатуй	12.12.2008	1234	5432
5	ТД Пятерочка	12.12.2008	2345	1234
6	ТД Яхтинг	12.12.2008	4321	5432
7	ТД Акатуй	13.12.2008	3456	1234
8	…	…	…	…

---