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

ЧД – число детей до 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	…	…	…	…