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

1. Назначение пунктов основного меню табличного процессора «Excel»?

2. Сколько листов, столбцов и строк в электронной таблице?

3. Как выделить блок ячеек, строк, столбцов?

4. Как скопировать, переместить, удалить или вставить ячейку, строку, столбец?

5. Как ввести, редактировать, копировать формулы?

6. Что такое абсолютные и относительные адреса ячеек? Как преобразовать относительные адреса ячеек в абсолютные?

7. Какова технология построения и редактирования диаграмм?

8. Возможности команды форматирования ячеек?

9. Назначение условного форматирования ячеек?

10. Что такое автоформат?

11. Как скопировать формат по образцу?

12. Возможности команды ПРАВКА/ЗАПОЛНИТЬ/ПРОГРЕССИЯ?

ЛАБОРАТОРНАЯ РАБОТА № 8

«Функции в excel»

Цель работы: знакомство и освоение различных функций, предлагаемых табличным процессором EXCEL и профессиональное применение этих возможностей при решении различных экономических задач, приобретение практических навыков по работе с Мастером функций, знакомство с основными статистическими функциями, c основными финансовыми функциями, с логическими функциями на примере практических экономических и управленческих задач.

КРАТКИЕ ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

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

1. Статистические

2. Финансовые

3. Математические и тригонометрические

4. Логические

5. Функции даты и времени

6. Функции баз данных и списков

7. Инженерные

8. Информационные

9. Функции ссылок и массивов

10. Текстовые

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

а) после знака = написать имя функции и список аргументов;

в) на панели инструментов "Стандартная" щелкнуть по кнопке f_x;

с) в основном меню "Вставка" выбрать параметр "Функция".

Диалоговое окно Мастерa функций, открывающееся при этом, дает возможность задать:

1. Класс (категорию) и имя функции - это первый шаг из двух, представленных Мастером функций. Затем нажимается кнопка "Далее" ("Next").

2. Аргументы (не более 30), которыми могут служить или какое-либо число, или текст, дата и время, или ссылка на ячейку, содержащую это число - это 2-ой шаг, представленный Мастером функций. Затем нажимается кнопка "Готово" ("Finish").

В случае, если неизвестен класс функций, то можно просмотреть все функции, выбрав в поле "Категория" Мастера функций "Полный алфавитный перечень".

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

Под полем "Категория" представлен способ задания выбранной функции и краткая информация о ней. Для получения более полной информации можно вызвать справку о данной функции, нажав клавишу F1 или кнопку "Справка", расположенную внизу окна Мастера функций. В справочном окне дана подробная информация о каждой функции.

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

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

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

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

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

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

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

Таблица 1

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

Примечание:

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

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

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

Таблица 2

		A		B	C	D	E	F	G
1					Ведомость начисления заработной платы
2				Рабочих дней =		20
3		N п/п		Ф.И.О.	Оклад (тенге)	Отработа но дней	Начислено (тенге)	Удержано (тенге)	К выдаче (тенге)
4		1.		Амантаев С.Е.	8200,00	21	8610,00	1033,20	7576,80
5		2.		Бартоев Г.А.	10000,00	20	10000,00	1200,00	8800,00
6		3.		Вильсон С.А.	9700,00	20	9700,00	1164,00	8536,00
7		4.		Ильясов А.Ж.	11500,00	21	12075,00	1449,00	10626,00
8		5.		Исмаилов К.К.	8500,00	18	7650,00	918,00	6732,00
9		6.		Иманов Р.К.	8200,00	22	9020,00	1082,40	7937,60
10		7.		Карцев Б.К.	9700,00	21	10185,00	1222,20	8962,80
11		8.		Локшин Г.А.	9700,00	22	10670,00	1280,40	9389,60
12		9.		Липов Б.Б.	10100,00	22	11110,00	1333,20	9776,80
13		10.		Цой А.Г.	10000,00	21	10500,00	1260,00	9240,00
14				Итого:			99520,00	11942,40	87577,60
15			СЧЕТ=			11
16			СЧЕТ3=			12
17					MAХ=		12075,00
18					МИН=		7650,00
19			РАНГуб. =						8
20			РАНГвозр. =						3
21					СРЗНАЧ=			1194,24
22					КВАДРОТКЛ=			212440,46
23					МЕДИАНА=			1211,10

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

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

Рассмотрим следующие статистические функции:

СЧЕТ (значение 1; значение 2;…) – определяет количество чисел в аргументе.

Для определения этой функции табличный курсор поставим в ячейку В15 и впишем пояснительный текст “СЧЕТ=”, а в ячейке D15 с помощью Мастера функций (способы вызова Мастера функций и работа с ним подробно описаны в теоретической части данного методического пособия) выберем категорию функций СТАТИСТИЧЕСКИЕ и функцию СЧЕТ, а во втором окне зададим аргументы D2:D13. В ячейке D15 получим значение функции СЧЕТ = 11.

СЧЕТЗ(значение 1; значение2; …) – определяет количество значений в аргументе.

В ячейке В16 впишем “СЧЕТ3=”, а в ячейке D16 получим значение функции СЧЕТ3 = 12 для тех же аргументов D2:D13.

МАКС(число1; число2;…) – определяет максимальное число среди аргументов.

Определим для аргумента (диапазона) Е4:Е13 и получим значение максимальной начисленной суммы в ячейке Е17;

МИН(число1; число2;…) – определяет минимальное число среди аргументов.

Определим для аргумента (диапазона) Е4:Е13 и получим значение минимальной начисленной суммы в ячейке Е18:

РАНГ(число; ссылка; порядок) – определяет порядковый номер числа среди цепочки чисел. В аргументе число – это конкретное число, порядковый номер которого должен быть определен; ссылка – это диапазон цепочки чисел; порядок – по умолчанию или 0 – убывающая последовательность, 1 – возрастающая последовательность.

Определим ранг для суммы к выдаче =7937,60, предварительно отсортировав данные таблицы по этой колонке по возрастанию. Тогда в аргументе функции запишем следующие данные: число=7937,60; ссылка – это диапазон G4:G13; порядок – 1 для возрастающей последовательности, 0 или по умолчанию – для убывающей последовательности. В ячейку В19 впишем РАНГуб.=, в ячейке G19 получим 8; в ячейку В20 впишем РАНГвозр.=, в ячейке G20 получим 3.

СРЗНАЧ(число1; число2;…) – определяет среднее арифметическое среди аргументов.

Определим среднее арифметическое число удержанных сумм, то есть аргумент будет равен F4:F13. В ячейке C21 запишем СРЗНАЧ=, в ячейке F21 получим 1194,24.

КВАДРОТКЛ(число1; число2;…) – определяет сумму квадратов отклонений {(Х-Х_ср)² где Х_ср – среднее арифметическое}.

Определим сумму квадратов отклонений для аргумента F4:F13, получив в ячейке F22 значение 212440,0.

МЕДИАНА(число1; число2;…) – определяет медиану для заданных аргументов. Медиана – это некоторое число М, при котором Х принимает значение как больше М, так и меньше М с вероятностью 1/2.

Определим медиану для аргумента F4:F13, получив в ячейке F23 значение медианы равное 1211,10.

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

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

Таблица 3

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

1. Рассмотрим логическую функцию ЕСЛИ(IF). Эта функция имеет сле-дующий формат записи:

= ЕСЛИ(условие; выражение В; выражение С)

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

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

=ЕСЛИ(С4> 0; В4*10%; “ - ”)

эта запись означает, что если имеется задолженность по кредиту за товары (т.е. задолженность >0), то удерживается с начислений 10%, если задол-женности нет, то ставится прочерк.

Эту функцию запишем в созданную Таблицу 3 в ячейку Е4 и затем ско-пируем в ячейки Е5:Е8.

Затем получим Таблицу 4 с числовыми значениями удержаний.

Таблица 4

	А	B	C		D			E
1	Ведомость удержаний по кредитам
2	Ф.И.О.	Начислено		Задолженность по видам кредитов			Удержано
3		(тенге)		Кредит за товары		Кредит на строи-тельство	(тенге)
4	Ашитов Г.Е.	12800,00		6400,00			1280,00
5	Бредун Э.Я.	10300,00					-
6	Валиев С.Р.	13100,00		5000,00		95000,00	1310,00
7	Ким Н.Н.	12500,00		26000,00			1250,00
8	Стамбеков Б.С.	9800,00				100000,00	-

2. Рассмотрим логическую функцию И(AND). Эта функция имеет следующий

формат записи: