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

 

Функции в Excel

 

Задание 1. Работа со статистическими функциями

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

	A	B	C	D	E	F	G
			Ведомость начисления заработной платы
		Рабочих дней =
	N п/п	Ф.И.О.	Оклад (тенге)	Отработа но дней	Начислено (тенге)	Удержано (тенге)	К выдаче (тенге)
	1.	Амантаев С.Е.	8200,00		=((C4/$D$2)*D4)	=E4*0,12	=E4-F4
	2.	Бартоев Г.А.	10000,00		=((C5/$D$2)*D5)	=E5*0,12	=E5-F5
	3.	Вильсон С.А.	9700,00		=((C6/$D$2)*D6)	=E6*0,12	=E6-F6
	4.	Ильясов А.Ж.	11500,00		=((C7/$D$2)*D7)	=E7*0,12	=E7-F7
	5.	Исмаилов К.К.	8500,00		=((C8/$D$2)*D8)	=E8*0,12	=E8-F8
	6.	Иманов Р.К.	8200,00		=((C9/$D$2)*D9)	=E9*0,12	=E9-F9
	7.	Карцев Б.К.	9700,00		=((C10/$D$2)*D10)	=E10*0,12	=E10-F10
	8.	Локшин Г.А.	9700,00		=((C11/$D$2)*D11)	=E11*0,12	=E11-F11
	9.	Липов Б.Б.	10100,00		=((C12/$D$2)*D12)	=E12*0,12	=E12-F12
	10.	Цой А.Г.	10000,00		=((C13/$D$2)*D13)	=E13*0,12	=E13-F13
		Итого:			=СУММ(E4:E13)	=СУММ(F4:F13)	=СУММ(G4:G13)

Примечание:

При копировании формулы из ячейки E4 (расчет начисленной суммы в зависимости от отработанных рабочих дней, оклада работника и количества рабочих дней в месяце) в ячейки Е4:Е13 необходимо, чтобы число рабочих дней в месяце было неизменным, для чего адрес ячейки D2 в формуле необходимо сделать абсолютным. При этом удобнее всего использовать функциональную клавишу F4, предварительно поместив в ячейку табличный курсор, тогда адрес этой ячейки будет иметь вид: $D$2.

Следует также обратить внимание, что необходим единый стандарт установки разделителя между целой и дробной частью числа "," и разделителя элементов списка ";". Эти установки производятся через главное меню Windows: Пуск - Настройка, Панель управления, Язык и стандарты, Числа…. Если же разделителем между целой и дробной частью числа выбрана ".", то разделителем элементов списка может быть "," или "; ".

При выполнении КОНТРОЛЬНОГО ПРИМЕРА 1 необходимо получить две таблицы: таблица 1 – с формулами, таблица 2 - с числовыми значениями (переход в режим формул и обратно: ФОРМУЛЫ-ЗАВИСИМОСТИ ФОРМУЛ-ПОКАЗАТЬ ФОРМУЛЫ).

Фрагмент Таблицы 2 в режиме отображения формул

		СЧЕТ=		=СЧЁТ (D2:D13)
		СЧЕТ3=		=СЧЁТЗ (D2:D13)
			MAХ=		=МАКС(E4:E13)
			МИН=		=МИН(E4:E13)
		РАНГуб. =					=РАНГ(7937,6;G4:G13;0)
		РАНГвозр. =					=РАНГ(7937,6;G4:G13;1)
			СРЗНАЧ=			=СРЗНАЧ (F4:F13)
			КВАДР ОТКЛ=			=КВАДРОТКЛ (F4:F13)
			МЕДИАНА=			=МЕДИАНА (F4:F13)

Задание 2. Работа с логическим функциями

Для знакомства с логическими функциями создадим следующую таблицу:

Таблица 3

 

	А	B	C	D	E
	Ведомость удержаний по кредитам
	Ф.И.О.	Начислено	Задолженность по видам кредитов	Удержано
		(тенге)	Кредит за товары	Кредит на строи-тельство	(тенге)
	Ашитов Г.Е.	12800,00	6400,00		=ЕСЛИ(C4>0;B4*10%;"-")
	Бредун Э.Я.	10300,00			=ЕСЛИ(C5>0;B5*10%;"-")
	Валиев С.Р.	13100,00	5000,00	95000,00	=ЕСЛИ(C6>0;B6*10%;"-")
	Ким Н.Н.	12500,00	26000,00		=ЕСЛИ(C7>0;B7*10%;"-")
	Стамбеков Б.С.	9800,00		100000,00	=ЕСЛИ(C8>0;B8*10%;"-")

 

Скопировав Таблицу 3 на новый лист ЕХСЕL, очистим ячейку Е4:Е8 и запишем в них (вначале в ячейку Е4, а затем скопировав в Е5:Е8) новую логическую функцию, которая позволит сделать выборку работников с задолженностью и по кредиту за товары, и по кредиту за строительство.

Таблица 6

 

	А	B	C	D	E
	Ведомость удержаний по кредитам
	Ф.И.О.	Начислено	Задолженность по видам кредитов	Удержано
		(тенге)	Кредит за товары	Кредит на строи-тельство	(тенге)
	Ашитов Г.Е.	12800,00	6400,00		=ЕСЛИ(И(C4>0;D4>0);B4*20%;"-")
	Бредун Э.Я.	10300,00			=ЕСЛИ(И(C5>0;D5>0);B5*20%;"-")
	Валиев С.Р.	13100,00	5000,00	95000,00	=ЕСЛИ(И(C6>0;D6>0);B6*20%;"-")
	Ким Н.Н.	12500,00	26000,00		=ЕСЛИ(И(C7>0;D7>0);B7*20%;"-")
	Стамбеков Б.С.	9800,00		100000,00	=ЕСЛИ(И(C8>0;D8>0);B8*20%;"-")

 

Скопировав Таблицу 3 на новый лист ЕХСЕL, очистим ячейки Е4:Е8 и запишем в них (вначале в ячейку Е4, а затем скопировав в Е5:Е8) новую логическую функцию, позволяющую сделать выборку работников, имеющих долг или по одному виду кредита или по двум видам кредита одновременно.

 

Таблица 9

	А	B	C	D	E
	Ведомость удержаний по кредитам
	Ф.И.О.	Начислено	Задолженность по видам кредитов	Удержано
		(тенге)	Кредит за товары	Кредит на строи-тельство	(тенге)
	Ашитов Г.Е.	12800,00	6400,00		=ЕСЛИ(ИЛИ(C4>0;D4>0);B4*10%;"-")
	Бредун Э.Я.	10300,00			=ЕСЛИ(ИЛИ(C5>0;D5>0);B5*10%;"-")
	Валиев С.Р.	13100,00	5000,00	95000,00	=ЕСЛИ(ИЛИ(C6>0;D6>0);B6*10%;"-")
	Ким Н.Н.	12500,00	26000,00		=ЕСЛИ(ИЛИ(C7>0;D7>0);B7*10%;"-")
	Стамбеков Б.С.	9800,00		100000,00	=ЕСЛИ(ИЛИ(C8>0;D8>0);B8*10%;"-")

Скопируем Таблицу 3 на новый лист ЕХСЕL, очистим ячейки Е4:Е8, запишем в ячейку Е4 вложенную логическую функцию, а затем скопируем ее ячейки Е5:Е8. Полученная таблица будет иметь вид:

Таблица 11

 

	А	B	C	D	E
	Ведомость удержаний по кредитам
	Ф.И.О.	Начислено	Задолженность по видам кредитов	Удержано
		(тенге)	Кредит за товары	Кредит на строи-тельство	(тенге)
	Ашитов Г.Е.	12800,00	6400,00		=ЕСЛИ(И(C4>0;D4>0);B4*20%;ЕСЛИ(И(C4=0;D4=0);"нет кредита";B4*10%))
	Бредун Э.Я.	10300,00			=ЕСЛИ(И(C5>0;D5>0);B5*20%;ЕСЛИ(И(C5=0;D5=0);"нет кредита";B5*10%))
	Валиев С.Р.	13100,00	5000,00	95000,00	=ЕСЛИ(И(C6>0;D6>0);B6*20%;ЕСЛИ(И(C6=0;D6=0);"нет кредита";B6*10%))
	Ким Н.Н.	12500,00	26000,00		=ЕСЛИ(И(C7>0;D7>0);B7*20%;ЕСЛИ(И(C7=0;D7=0);"нет кредита";B7*10%))
	Стамбеков Б.С.	9800,00		100000,00	=ЕСЛИ(И(C8>0;D8>0);B8*20%;ЕСЛИ(И(C8=0;D8=0);"нет кредита";B8*10%))