MU_LR_po_Excel_2007_EP1__BU1

21

Таблица 1

№

F(x)

a

b

Хн

Хк

1		x								ln					а x																								5.1	2	0	2.5
											b x 1
2		x sin( а xb) e x																																					3	2	5.2	11.2
3		ln					а b x																																2	3	0.1	5.1
			sin x x
4		cos(a x2 ) e b x																																					0.1	3.4	-1.2	0.2
5						ea x							1																										0.2	2	0	6
		cos x b x
6			a						sin(x2																	2.6 x)													356	23	2.5	3.5
		b x
7		sin a x cos 2 x																																					5	0.2	1.7	7.7
													b x
8	tg(a x b) ex b																																						5	12	0	1.5
9						x						a x																											5	0.2	-5.1	5.1
		x cos(b x)
10		ln						x a																															3.4	22	-4.5	1.9
			tgx b
11	cos(x																			a									)										1	2	10.1	20.3
														b sin x
12				x2 ln(a x)																																			2	3	13.2	102
							x arctgxb
13	sin(a x																		xb							cosx											lnx		0.1	3	1	10
14		arctg(a b x)																																					0.6	0.4	0	1
			sin(x cosx)
15				( x a )																																			1.2	1	1	5
			e arctg															( x b )
16		arctg(ea x b )																																					2	0.23	0	3

22

2. Ссылки на другие листы и связывание рабочих книг

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

При задании ссылки на другой рабочий лист перед адресом ячейки указывается имя листа, которое отделяется от адреса ячейки восклицательным знаком. Например: Лист2!А1.

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

[Книга2]Лист1!$A$1.

Задание 2

Загрузите с диска книгу с именем Общая книга. Для выполнения задания используйте таблицу, находящуюся на Листе 1.

В ячейку К2 занесите текст “ % выполнения плана”.

В ячейку К3 - формулу =J3/F3

Скопируйте формулу в интервал ячеек К4:К21

Отформатируйте данные, находящиеся в интервале ячеек К3:К21

Выделите интервал ячеек К3:К21

Задайте для данных процентный формат, нажав на соответствующую кнопку на панели инструментов Форматирования

Задайте один знак после запятой.

В ячейку А25 занесите текст % Реализации продукции, в А26 текст - В фирменных

магазинах, в B26 текст - Оптовым покупателям

 Занесите в А27 значение - 60% , в B27 - 10%

Занесите в L2 текст - Реализовано в фирменных магазинах, в М2 текст - Реализовано

оптовым покупателям

В L3 задайте формулу =J3*$A$27

Скопируйте формулу в ячейки L4:L20. При копировании формулы адрес J3 будет выступать как относительный, а адрес А27 как абсолютный.

В интервале ячеек М3:М23 сформируйте значения реализации продукции каждого наименования оптовым покупателям. Причем при формировании этих значений должно соблюдаться условие: оптовым покупателям будет реализована, согласно установленного

процента, лишь сверхплановая	продукция. Чтобы определить, когда осуществлялась
реализация оптовым покупателям,	воспользуйтесь функцией ЕСЛИ. Эта функция относится к

категории логических функций. Она имеет 3 аргумента:

1–ый аргумент - логическое условие;

2-ой аргумент выражение 1, соответствующее истинности этого условия;

3-ий аргумент - выражение 2, соответствующее невыполнению логического

условия.

Для расчета реализации продукции оптовым покупателям проделайте следующие действия:

-В ячейке М3, воспользовавшись Мастером функций, задайте формулу:

=ЕСЛИ(K3>100%;(J3-F3)*$B$27;0)

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

23

-Скопируйте формулу в интервал ячеек М4:М20. В формуле в качестве относительных адресов выступают адреса К3, J3, F3, а в качестве абсолютного - адрес В27.

В ячейках L21, M21 подсчитайте итоговые значения по рассчитанным колонкам

В ячейку А22 занесите текст - Максимальный % выполнения плана.

В ячейку K22 занесите формулу для подсчета максимального процента выполнения плана. Для этого воспользуйтесь Мастером функций. Из категорий функций выберите Статистические, а в этой категории найдите функцию МАКС, задайте интервал ячеек, на котором будет осуществляться подсчет максимального значения:

=МАКС(K3:K20)

В ячейку А23 - Средний % выполнения плана.

Аналогично расчету максимального процента в ячейке К23 подсчитайте средний процент выполнения плана, с той лишь разницей, что в категории Статистические нужно выбрать функцию СРЗНАЧ.

Врезультате расчетов получится информация, представленная в таблице 1.

Задание 3

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

Условием начисления премии является перевыполнение плана по выпуску продукции за квартал. Причем рабочим премия начисляется в размере 20% от суммы окладов за квартал, остальным работающим - 10%. Отчисления в пенсионный фонд за квартал - 2% от квартальной заработной платы.

Для выполнения задания проделайте следующие действия:

Перейдите на Лист 2 Общей книги.

В ячейке G2 задайте текст - Квартальный оклад

В ячейке G3 формулу: =3*D3

Формулу скопируйте в интервал ячеек G4:G16

24

Ячейке Н2 задайте текст – Премия

Задайте текст в ячейке А19 - Процент начисления премии, в ячейке А20 – Рабочим, в ячейке В20 – Всем служащим

 Введите значения: в ячейку А21 – 20%, в ячейку В21 - 10%

В ячейку С19 введите текст - % отчисления в пенсионный фонд, объедините ячейку С19 и

С20

В ячейку С21 задайте значение 2%

Ячейке Н3 задайте с помощью Мастера функция формулу для расчета премии:

=ЕСЛИ(Лист1!$K$21>=100%;ЕСЛИ(C3="Рабочий";G3*$A$21;G3*$B$21);0)

Вэтой формуле:

Первый аргумент: Лист1!$K$21>=100% - проверка выполнения квартального плана.

Второй аргумент – если план перевыполнен, то начисляется премия. Начисление премии производится по формуле: ЕСЛИ(C3="Рабочий";G3*$A$21;G3*$B$21)

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

Третий аргумент - 0, так как, если план не выполнен, то премия не начисляется. Диалоговое окно Мастера функций при задании аргументов вложенной в формулу функции

Если будет иметь следующий вид:

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

В ячейке I2 задайте текст - Квартальная зарплата

В ячейку I3 введите формулу: =G3+H3, которую скопируйте в интервал ячеек I4:I16

В ячейке J2 задайте текст - Отчисления в пенсионный фонд

В ячейку J3 введите формулу: =I3*$C$21, которую скопируйте в интервал ячеек J4:J16

Подсчитайте итоговые значения по квартальным окладам, премии, квартальной зарплате, отчислениям в пенсионный фонд.

Отформатируйте полученную таблицу. В результате расчетов получим информацию, представленную в таблице 2.

Новую версию книги сохраните в своей папке на диске. Она потребуется для дальнейшей работы.

25

Вопросы для самоподготовки

1.Какие типы ссылок используются в табличном процессоре?

2.Как изменить тип ссылки?

3.Как задать ссылку на другой лист?

4.Каким образом указать, что ссылка является абсолютной?

5.Как произвести копирование формулы?

6.Когда при расчетах используется функция Если?

7.Сколько аргументов имеет функция Если? Какая информация задается в каждом аргументе?

26

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

Тема: Создание диаграмм в табличном процессоре Excel

Цель работы: Овладеть навыками создания диаграмм в табличном процессоре

1. Графическое представление данных с помощью диаграмм

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

В Microsoft Excel существует 2 вида диаграмм: внедренная диаграмма и лист диаграммы. Внедренная диаграмма – это объект-диаграмма, которая помещается в лист Excel и

сохраняется вместе с этим листом при сохранении всей книги Excel.

Лист диаграммы — это отдельный лист в книге, имеющий собственное имя. Листы диаграмм следует использовать в случаях, когда требуется просмотреть или изменить большие или сложные диаграммы отдельно от данных, или когда требуется сохранить пространство экрана для работы с листом. При создании листа диаграммы Microsoft Excel вставляет его в книгу слева от листа, содержащего данные, на которых основана диаграмма.

2.Элементы диаграммы

Ряды данных - это та информация (строки или столбцы), на основе которой будет строиться диаграмма. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных.

Легенда - служит для идентификации рядов данных. Применение легенды целесообразно, если в диаграмме несколько рядов данных.

Категории: если для рядов данных используются значения столбцов, то значения строк - это категории и наоборот.

Подписи значений - подпись с дополнительными сведениями о маркере данных, представляющем одну точку данных или значение ячейки листа.

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

2.Типы диаграмм Excel

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

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

Гистограмма - показывает изменение в течение некоторого периода времени (Рис.1) или отражает соотношение величин (рис.2). В гистограммах категории обычно формируются по горизонтальной оси, а значения — по вертикальной

27

Диаграмма продаж

40

30

20

10

0

Iкв

IIкв

IIIкв

Ivкв

Диаграмма продаж

	60
	40
	20				Италия
	0
					Испания
	Iкв	IIкв	IIIкв	квIv
					Ирландия

Рис.1. Рис.2.

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

			Диаграмма продаж								Диаграмма продаж
Америка											Израиль
																									1997
Европа											Ирландия
																									1996
											Испания
Африка																									1995
											Италия
0			50		100		150		200
											0		50								100		150
					(Вмлн.руб.)
																(Вмлн.руб.)

Рис.3. Рис.4.

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

	Диаграмма продаж
100
80
60			Европа
40			Азия
20
0
Iкв	IIкв	IIIкв	IV кв
		Рис.5.

Круговая и кольцевая диаграммы показывают соотношение частей в целом (Рис.6, Рис.7)

		28
Диаграмма продажи напитков		Диаграмма продаж
Газ.вода	Соки
17%	17%
			23 Т.р.	1 год
	Чай	33 Т.р.	51 Tр.	2 год
	28%	60 T.р.
Кофе
38%

Рис.6. Рис.7.

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

Круговая и кольцевая диаграммы являются диаметрально противоположным типом для гистограмм.

Точечная диаграмма подходит для отображения соотношения между двумя наборами данных, один из которых откладывается по оси Х, а второй по оси У (Рис.8.). Эта диаграмма отображает неравномерность интервалов и группировки данных и часто используется для представления данных научного характера.

Таблица 1

	Время	Темп.	Расчетная темп.
	2:30	23,0	5,5
	4:00	22,5	18,2
	6:30	21,0	10,6
	9:00	20,2	4,9
	11:30	21,4	5,8
	14:00	18,2	10
	17:30	17,2	4
	22:00	16,2	20,5

Рис.8

Диаграмма с областями подчеркивает изменение общего количества в течение какого-то периода времени, показывая сумму введенных значений. Она также отображает вклад отдельных значений в общую сумму (Рис.9.).

		29
		Диаграмма продаж
200	Mлн.р.
150	Mлн.р.		Ангола
100	Mлн.р.		Австрия
			Австралия
50	Mлн.р.
			Аргентина
0	Mлн.р.
	1995	1996	1997

Рис.9

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

Рис. 10.

Поверхностная диаграмма позволяют, используя три оси X,Y,Z, показать зависимость трех наборов данных. Поверхностные диаграммы позволяют “прочувствовать” трехмерные данные и позволяют наблюдать их объемный вид, что не всегда доступно при использовании других типов диаграмм (Рис.11.).

Диаграмма прочности

Секунды0:14

0:10

0:06

0:02

200

400

600

300

250

200 Предел

150

100 прочности

50

0

Температур а С

Рис.11

30

Пузырьковая диаграмма – напоминает точечную диаграмму. Но в ней размер точки данных описывает значение этой точки. Для построения этой диаграммы необходимо создать три столбца данных: первый из них содержит значение по оси Х, второй - по оси У, а третий определяет размер каждого пузырька (Рис.12.).

количество

посетителей

Анализ продаж за неделю

60

40

20

Ряд1

0

0 5 10

дни недели

Рис.12.

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

-Максимальная – минимальная – закрытие

-Начальная - максимальная –минимальная - закрытие

-Объем - максимальная –минимальная - закрытие

-Объем – начальная - максимальная - минимальная - закрытие

Для любого из типов данные должны быть расположены в порядке, описанном в названии.

Например, при создании диаграммы Максимальная – минимальная - закрытие необходимо создать следующие столбцы данных: дата, максимальная цена, минимальная цена и цена на момент закрытия.

Диаграммы в виде цилиндра, конуса, пирамиды: в таком формате могут быть представлены

гистограммы, линейчатые диаграммы. В этом типе столбики гистограмм или полосы линейчатой диаграммы заменяются на цилиндры, конусы, пирамиды.

 Нестандартные типы диаграмм: Excel предлагает большое количество нестандартных диаграмм, обладающих теми или иными особенностями многих основных типов. Если хочется добавить в диаграмму что-нибудь непривычное – то рекомендуется выбрать наиболее подходящую диаграмму именно из нестандартных типов.

3. Построение диаграммы

Для создания диаграммы необходимо:

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

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

Выбрать вкладку Вставка.

В группе команд Диаграммы выбрать вид диаграммы:

Из списка выбранного вида выбрать подвид диаграммы, который необходимо использовать.