Методичка Exell

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

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

Диаграммы с областями и накоплением присутствуют среди стандартных типов диаграмм в Microsoft Excel. Имеются также анало-

гичные им гистограммы с накоплением.

На рис. 3.4 построены диаграммы с областями по данным о формировании доходов государственного бюджета Республики Беларусь (в текущих ценах, млрд. руб.) в течение 7 месяцев 2000 года.

200	100%
160	80%
120	60%
80	40%
40	20%
0	0%
янв фев мар апр май июн июл	янв фев мар апр май июн июл
Доходы от внешней торговли и	Доходы от внешней торговли и
внешнеэкономических операций	внешнеэкономических операций
Внутренние налоги на товары и услуги	Внутренние налоги на товары и услуги
Прямые налоги на доходы, прибыли, прирост	Прямые налоги на доходы, прибыли, прирост
капиталла и заработную плату	капиталла и заработную плату

Рис. 3.4. Обычная и нормированная диаграммы с областями

г) Лепестковая диаграмма называется также радиационной или просто "кактусом". Это график одного или нескольких рядов данных в полярных координатах. Имея значения какой-либо характеристики

41

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

На рис. 3.5 построена лепестковая диаграмма по данным о рентабельности отраслей экономики Республики Беларусь за 1992, 1997 и 1999 годы. Как видим, к 1997 году рентабельность одинаково снизилась во всех отраслях. В 1999 году ситуация улучшилась, при этом заметно изменилась общая структура рентабельности. Различия между отраслями сгладились. Возможно, часть прибыли из других отраслей перетекла в транспортную из-за увеличения тарифов перевозок.

Транспорт

25

20

Заготовительные 15 Промышленность организации

		10
		5
	Материально-	0
		Торговля
	техническое
		и общепит
	снабжение и сбыт

1992

1997

Строительство Связь

1999

Рис. 3.5. Лепестковая диаграмма рентабельности отраслей экономики

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

Например, пусть в ячейках A1:A25 расположен массив чисел:

7,35; 2,16; 0,76; 1,30; 2,12; 1,07; 4,21; 1,21; 4,86; 1,65; 4,78; 6,47; 0,96; 2,82; 2,98; 7,09; 5,75; 2,91; 7,80; 5,21; 6,85; 3,62; 6,13; 4,18; 7,79.

С помощью формул =МИН(A1:A25) и =МАКС(A1:A25) находим минимальное и максимальное числа: соответственно 0,76 и 7,80. Разбиваем промежуток от 0,5 до 8 на пять равных отрезков длины 1,5. В ячейки B1:B4 вводим числа, соответствующие границам отрезков:

42

2; 3,5; 5; 6,5. Граничные числа 0,5 и 8 вводить нет необходимости: при подсчете количества значений в первый диапазон попадут величины, меньшие 2, а в последний – большие либо равные 6,5.

В меню "Сервис" вызываем команду "Анализ данных". В списке инструментов выбираем "Гистограмма" и нажимаем "ОК". Появляется диалоговое окно для ввода параметров построения гистограммы. В поле "Входной интервал" указываем A1:A25, в поле "Интервал карманов" – B1:B4. В поле "Выходной интервал" вводим ссылку на ячейку C1, начиная с которой будут выводиться результаты. После нажатия на кнопку "ОК" в ячейках C1:D6 получаем следующую таблицу:

Таблица 3.3

Карман	Частота
2	6
3,5	5
5	5
6,5	4
Еще	5

В столбце "Карман" указана верхняя граница отрезка, в столбце "Частота" – количество попавших в него значений. При этом включаются значения на нижней границе отрезка и не включаются на верхней. Строим диаграмму по диапазону ячеек C2:В6. На первом диалоговом окне мастера диаграмм выбираем тип "Гистограмма" (там это слово имеет более широкий смысл – столбцовая диаграмма). Построенная гистограмма представлена на рис. 3.6.

7
6
5
4
3
2
1
0
2	3,5	5	6,5	Еще
	Рис. 3.6. Гистограмма

43

4. ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Задание 1 (к разделу 1)

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

а) Отобразить данные в таблице в экспоненциальном формате.

Варианты задания 1

1) Объемы поставок топлива, тонн
		мар.99	июн.99	сен.99		дек.99	Всего
	Мазут	809,6	903,7	936,5		676,7	3326,5
	Уголь	747,7	37,2	696,2		94,2	1575,3
	Керосин	801,3	120,5	937,6		205,3	2064,8
	Итого	2358,7	1061,3	2570,3		976,2	6966,6
2)
		Объемы поставок топлива, тонн
		мар.99	июн.99	сен.99		дек.99	Всего
	Мазут	908,5	367,0	513,7		130,4	1919,6
	Уголь	471,7	705,3	141,8		194,7	1513,6
	Керосин	900,7	183,2	932,8		406,4	2423,2
	Итого	2280,9	1255,6	1588,3		731,6	5856,3
3) Объемы поставок топлива, тонн
		Март	Июнь	Сентябрь		Декабрь	Всего
	Мазут	136,9	228,9	839,2		445,4	1650,4
	Уголь	331,5	107,6	285,7		918,7	1643,5
	Керосин	207,7	954,4	431,5		509,4	2103,1
	Итого	676,1	1290,9	1556,5		1873,5	5397,1
4)		Объемы поставок топлива, тонн
		мар 99	июн 99	сен 99		дек 99	Всего
	Мазут	606,7	168,6	218,2		855,6	1849,1
	Уголь	370,6	629,4	428,7		325,9	1754,6
	Керосин	672,2	904,6	349,2		522,2	2448,3
	Итого	1649,5	1702,6	996,1		1703,7	6051,9
5) Объемы поставок топлива, тонн				сен.99		дек.99	Всего
		мар.99	июн.99
	Мазут	140,0	660,6	596,9		703,4	2100,9
	Уголь	629,4	617,3	184,4		196,9	1628,0
	Керосин	367,9	682,5	583,8		768,2	2402,4
	Итого	1137,4	1960,3	1365,1		1668,5	6131,3

44

6)Объемы поставок топлива, тонн

			03.1999				06.1999			09.1999			12.1999		Всего
	Мазут		618,9				482,0			756,7			673,6		2531,2
	Уголь		445,8				671,2			359,1			892,8		2368,9
	Керосин		368,7				677,0			3,4			537,2		1586,3
	Итого		1433,4				1830,1			1119,2			2103,6		6486,4
7)				мар.99			Объемы поставок топлива, тонн
							июн.99			сен.99			дек.99		Всего
	Мазут		931,8				681,7			343,5			567,6		2524,7
	Уголь		608,3				87,0			149,5			717,1		1561,8
	Керосин		323,1				715,7			52,5			580,8		1672,2
	Итого		1863,3				1484,4			545,5			1865,5		5758,7
8)			Объемы поставок топлива, тонн										дек 99		Всего
				мар 99			июн 99			сен 99
	Мазут		970,3				987,8			315,0			915,4		3188,6
	Уголь		526,1				681,1			173,1			772,1		2152,4
	Керосин		607,1				730,4			99,0			871,6		2308,1
	Итого		2103,5				2399,4			587,2			2559,1		7649,1
9)	Объемы поставок топлива, тонн
			мар. 1999				июн. 1999	сен. 1999					дек. 1999		Всего
	Мазут		785,9				864,7			825,8			183,6		2660,1
	Уголь		163,6				810,1			669,1			673,5		2316,3
	Керосин		92,4				961,4			465,2			263,6		1782,6
	Итого		1041,9				2636,2			1960,2			1120,7		6758,9
10) Объемы поставок топлива, тонн
				мар 99			июн 99			сен 99			дек 99		Всего
		Мазут		149,9			802,6			699,9			852,7		2505,1
		Уголь		82,9			981,5			337,7			360,5		1762,6
		Керосин		314,1			205,0			404,1			963,4		1886,6
		Итого		546,8			1989,1			1441,7			2176,6		6154,3

Задание 2 (к разделу 2)

а) (к разделу 2.1) Ввести в ячейку формулу нахождения значения функции f(x,a), где величины x и a вводятся в отдельных ячейках слева от нее. Оформить задание следующим образом:

Число a	Число х	Значение функции
		(формула для вычисления)

45

Варианты задания 2а

Таблица 4.1

№ варианта

Функция f (x ,a )

sin x,

если x >

π

1

2

π

если x ≤

cos x,

2

tg x +

a

2

x −

π

2

3

e x

−cos ax

sin(ax)

4

arcsin

ax

x

x

5

arctg

+arcctg

a

a

6

elg(a+x ) - ln(a-x )

7

x −

a − x

−2x

8

(1+ x)

a

1+x

9

(xa ln x)2

a + x, если x >sin x

10

если x =sin x

2a,

a − x, если x < sin x

Указания к выполнению задания. При выполнении вариантов 1

и 10 используйте функцию ЕСЛИ(Условие,Выражение1,Вражение2).

Она возвращает значение Выражение1, если Условие выполняется (т.е. принимает значение "ИСТИНА". В противном случае функция ЕСЛИ выдает значение Выражение2. Число π вызывается в Excel с помощью функции Пи(). Выражение ex записывается в виде Exp(x).

б) (к разделу 2.2) Создать таблицу значений функции f(x,a) (см. таблицу 4.1), где переменная x принимает значения из указанного промежутка с заданным шагом, a – константа, находящаяся в отдельной ячейке.

46

Варианты задания 2б

									Таблица 4.2
	Вариант	Промежуток						Шаг	Значение
									константы
	1		[0;4]					0,1	10-6
	2		[-1;4,6]					0,2	2
	3		[0;10]					0,5	lg 3
	4		−	π	;	π		0,1π	3
				2		2
	5		[-1;1]					0,1	0,8
	6		[-2;3]					0,2	1
	7		[-8;8]					1	4
	8		[-3;4]					0,5	sin 3
	9		[0;2,5]					0,1	2
	10	[-0,7;0,7]						0,1	0,35

Указания к выполнению задания. С помощью автозаполнения арифметической прогрессией создайте ряд значений x. Около него создайте ряд значений f(x,a). Для этого в первую ячейку нового ряда введите формулу вычисления функции f(x,a). Эта формула должна зависеть от первого значения ряда x и от отдельной ячейки, содержащей константу a (ссылка на константу должна быть абсолютной). Затем "размножьте" ячейку с формулой таким образом, чтобы около ряда значений x появился ряд значений f(x,a).

в) (к разделу 2.3) Подсчитайте сумму значений функции f(x,a) для переменной x, принимающей значения из указанного промежутка с заданным шагом, и заданного значения константы a (см. таблицу 4.2). При этом не пользуйтесь созданной ранее таблицей значений функции.

Указания к выполнению задания. Введите в ячейку формулу над массивом { = СУММ( F(Mx,A) ) } . Здесь F(Mx,A) – выражение заданной функции f(x,a). Аргумент x заменяется на Mx – ссылка на диапазон ячеек, содержащих таблицу значений переменной x. Параметр A – ссылка на ячейку, содержащую значение константы a.

Задание 3 (к разделу 2.4)

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

известны объем необходимых инвестиций и прибыль от реализации

47

проекта. Имеется также возможность вложить деньги в государст-

венные краткосрочные облигации (ГКО): цена одной ГКО – 50, при-

быль – 8, максимальное количество ГКО – 4. Имеющиеся в наличии средства ограничены суммой, равной половине стоимости всех проектов.

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

Варианты задания 3

Таблица 4.3

	№ проекта	1	2	3	4	5	6	7	8	9
	Вариант
	1	457	507	409	554	403	579	593	455	598
		50,5	50,7	47,3	61,5	43,7	59,5	63,3	52,9	71,1
	2	529	581	520	567	598	521	416	566	440
		57,9	59,9	55,2	59,7	70,3	58,0	47,8	64,1	44,0
	3	578	473	525	587	401	569	422	408	515
		68,1	48,1	56,6	67,4	41,0	59,4	44,4	43,1	54,0
	4	461	503	513	524	498	535	595	582	558
		52,7	52,6	57,9	57,1	59,3	59,5	62,7	60,0	62,7
	5	543	484	533	418	490	580	495	572	559
		54,6	52,8	63,3	44,0	51,7	66,2	58,6	66,0	59,5
	6	469	596	574	513	496	464	535	572	450
		55,0	70,5	61,7	52,0	54,7	55,4	59,4	64,6	53,0
	7	441	511	440	401	478	464	552	502	409
		45,5	61,3	46,5	41,0	56,6	49,5	62,5	59,1	46,8
	8	474	422	586	487	424	448	516	447	478
		48,8	43,7	59,4	56,3	42,5	45,0	57,2	49,4	53,3
	9	472	475	447	447	585	464	532	504	451
		55,8	50,7	45,1	50,4	58,7	47,1	61,9	55,5	49,3
	10	436	473	487	588	447	463	593	408	455
		45,4	55,9	56,6	68,4	46,1	48,3	69,6	42,6	48,5

Каждая ячейка таблицы 4.3 содержит объем затрат и прибыль проекта, номер которого соответствует номеру столбца. Названия проектов даны ниже.

1)Строительство дороги

2)Строительство дома

3)Достройка магазина

4)Партия продуктов

5)Партия телевизоров

6)Производство макарон

48

7)Производство компьютеров

8)Производство компакт-дисков

9)Производство кирпичей

Указания к выполнению задания. Пакет инвестиций будем представлять в виде вектора x=(x1,…,x10): для i=1,…,9 элемент xi равен единице, если i-й проект включен в пакет, и равен нулю в противном случае; элемент x10 – количество приобретаемых ГКО. Пусть ai –

объем затрат на i-й проект, ci – прибыль от i-го проекта (a10 и c10 – соответственно стоимость и доход одной ГКО). Тогда затраты на ин-

10

вестиции равны ∑ai xi , прибыль от реализации пакета инвестицион-

i =1

10

ных проектов равна ∑ci xi .

i =1

Таким образом, задача сводится к нахождению максимального

10

значения величины ∑ci xi при следующих ограничениях на вектор x:

i=1

xi ≥0 для любого i=1,…,10;

xi ≤1 для любого i=1,…,9;

x10≤4;

xi – целое для любого i=1,…,10;

	10	1	9
	∑ai xi ≤		∑ai (ограничение объема вкладываемых средств).
	i =1	2 i=1

Для постановки и решения задачи с помощью надстройки "Поиск решения" введите на рабочий лист данные в соответствии с рис. 4.1.

В ячейках A3:A12 – названия вариантов вложения средств, в ячейках B3:B12 – затраты, а в ячейках C3:C12 – прибыль каждого из вариантов вложения. В ячейке B13 рассчитывается половина стоимости всех проектов. В ячейки D3:D12 предназначены для элементов вектора x, которые будет отыскивать надстройка "Поиск решения". При любом векторе x в ячейке D13 рассчитываются затраты на осуществление соответствующих инвестиций, а в ячейке D14 – получаемая прибыль. Обратите внимание, что каждая из этих двух ячеек содержит формулу над массивами.

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

49

	A	B	C	D
1	Расчет пакета инвестиционных проектов (пример)
2	Название проекта	Затраты	Прибыль	Решение
3	Строительство дороги	634	70,4	1
4	Строительство дома	585	63,5
5	Достройка магазина	417	43,7
6	Партия продуктов	441	50,1	1
7	Партия телевизоров	631	71,1	1
8	Производство макарон	611	61,1
9	Производство ПК	541	62,1
10	Производство CD	511	53,1
11	Производство кирпи-	535	58,5
	чей
12	ГКО	50	8,6	3
13	Затрат, не больше	=СУММ(B3:	Затраты:	{=СУММ(B3:B12*D3:
		B11)/2		D12)}
14	Макс. количество ГКО	4	Прибыль:	{=СУММ(C3:C12*D3:
				D12)}

Рис. 4.1. Исходные данные для решения задачи об инвестициях

Задание 4 (к разделу 3.2)

Производственная функция с постоянной эластичностью замещения имеет вид

−γ

f (K, L) = a (δ K −ρ +(1−δ)L−ρ ) ρ ,

где K – капитал, L – трудовые ресурсы, a – коэффициент шкалы, δ – коэффициент распределения, ρ – коэффициент замещения, γ – степень однородности. Переменные K и L неотрицательны, коэффициенты a, δ, ρ и γ положительны, причем δ < 1.

Пусть L = 15, а коэффициенты принимают следующие началь-

ные значения: a = 20, δ = 1/3, ρ = 4, γ = 3.

Построить график функции y=f(K,L) от переменной K, прини-

мающей значения 1,2,…,20, причем три заданные константы вынести в отдельные ячейки.

50