Kabachenko&Drig

Указания к выполнению.

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

·В ячейку A19 таблицы введите формулу, вводящую дату, год которой совпадает с годом даты ячейки C1: =ДАТА(ГОД(C1);12;22). В этой формуле первый параметр опи- сывает год, второй - месяц, третий - число.

·Аналогичные формулы запишите в ячейки A7 - A18.

·В ячейку C2 занесите функцию ВПР поиска знака зоди- ака по дате рождения. Параметры формулы: Искомое_значе- ние: дата рождения (ячейка C1); Таблица: таблица поиска (A7

:D19); Номер_столбца: столбец со знаками зодиака (2).

·Аналогичные формулы занесите в ячейки C3 и C4. Обратите внимание, что в этом случае функция ВПР ищет

значения не по полному совпадению ключевых параметров, а по признаку «наибольшего меньшего». Очевидно, что и в этом

случае значения в первом столбце должны быть расположены по возрастанию.

Самостоятельная работа.

Определите по дате рождения соответствующий год по вос- точному календарю. Для определения года примените функ- цию ВПР к данным следуюшей таблицы:

Остаток	Год	Остаток	Год
0		6	тигра
1	петуха	7	кролика
2	собаки	8	дракона
3	свиньи	9	змеи
4	крысы	10	лошади
5	быка	11	козы

Указание: этот год вычисляется по остатку деления года рождения на 12. Остаток вычисляется функцией ОСТАТ, име- ющей два параметра: Число (год рождения, т.е. ГОД(C1) и

делитель (12).

7 1

Практическая работа 9 Диаграммы

Цель работы: Получить понятие о построении простейших диаграмм.

Образец.

	1	A	B	C	D	E	F
		Предварительные итоги выборов
	2
	3						Всего
	4	Избирательный	Путин	Жирино Явлин		Зюганов
							проголосо-
		участок		вский	ский
							вавших
	5	Участок № 1	58	45	36	34
	6	Участок № 2	24	12	25	12
	7	Участок № 3	97	17	25	64
	8	Участок № 4	26	26	26	98
	9	Участок № 5	93	15	14	25
	10	Участок № 6	28	26	67	46
	11	Участок № 7	27	35	26	25
	12	Участок № 8	28	25	74	56
	13	Участок № 9	39	48	48	38

14Сумма

15%

Указания к выполнению.

·Для заполнения столбца с названиями участков запиши- те Участок 1 в ячейку A5 и распространите содержимое на нижние ячейки.

·Для вычисления сумм голосов в ячейках F5 – F13 и B14

–F14 используйте кнопку Автосумма на стандартной панели инструментов.

·Перед вычислением процентного распределения прого- лосовавших за того или иного кандидата, предварительно ус- тановите нужный формат представления чисел в ячейках B15

–E15 (Формат/Ячейки/Число/Процентный/Число десятич-

ных знаков: 2). После этого в ячейку B15 введите расчетную формулу (с использованием абсолютной ссылки) и распрост- раните ее на следующие ячейки справа.

7 2

		заголовок		область
		диаграммы
				построения
Ось Y	Распределение голосов на выборах
	500
	400
название	300
оси
	200
	100
	0
область	Путин	Жириновский	Явлинский	Зюганов
диаграммы
			Кандидат
	Ось X		название	ряд
				данных
			оси
Тип диаграммы: гистограмма; вид: обычная

Распределение голосов

	Зюганов	Путин
	49%
		51%

Тип диаграммы: круговая

7 3

Для построения гистограммы предварительно необходимо выделить область ячеек B14 – E14, в которых размещаются данные гистограммы. После этого на стандартной панели ин- струментов нажмите кнопку Мастер диаграмм и пройдите по- шаговый процесс построения диаграммы:

·На первом шаге выберите тип и вид диаграммы (Тип -

гистограмма, Вид - обычная).

·На втором шаге выберите диапазон ячеек, значения ко- торых являются подписями под осью X. Для этого во вкладке Ряд найдите поле ввода Подписи оси X и нажмите кнопку сво- рачивания диалогового окна в конце поля ввода. После этого выделите диапазон ячеек B4 - E4 таблицы и нажмите клавишу

Enter.

·На третьем шаге Мастера диаграмм запишите назва- ние диаграммы и подписей под осями; уберите линии сетки и легенду, открыв соответствующие вкладки.

·По завершении третьего шага нажмите кнопку Готово диалогового окна.

Завершите процесс построения диаграммы, выполнив сле- дующие действия:

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

·Измените размеры диаграммы, сделав ее ширину рав- ной ширине талицы, а высоту - примерно 2/3 ширины.

·Измените формат следующих объектов диаграммы, предварительно произведя на них двойной щелчок:

1. Область диаграммы: рамка – обычная.

2. Область построения: рамка – невидимая; заливка – про- зрачная.

3. Ось Х: выравнивание – ориентация – 90 градусов.

4.Ось Y: Шкала – цена основных делений =100.

5.Ряд данных: Вид/Способ заливки/Узор.

· Подберите размер шрифта текста в объектах диаграм- мы пропорционально размерам диаграммы.

7 4

Перед построением круговой диаграммы предварительно выделите ячейки B14 и E14, в которых размещаются исходные данные. Для этого выполните следующую последовательность действий:

·щелкните мышью ячейку B14;

·нажмите и удерживайте клавишу Ctrl;

·щелкните мышью ячейку E14.

При работе с Мастером диаграмм на втором шаге в каче- стве подписей категорий выберите ячейки B4 и E4.

На третьем шаге введите заголовок диаграммы, выберите вариант подписи значений (вкладка Подписи данных) и убе- рите легенду.

После появления диаграммы на экране выполните следую- щие действия:

·перенесите диаграмму так, чтобы она располагалась справа от таблицы;

·измените высоту и ширину диаграммы, сделав их рав- ными высоте таблицы;

·измените размеры круга, чтобы он занимал более поло- вины области диаграммы. Для этого мысленно опишите около круга квадрат и щелкните в одном из его углов. После этого измените размер выделенного объекта;

·подберите пропорциональный размер шрифта в подписях данных.

Самостоятельная работа.

Для таблицы на с.72 постройте следующие диаграммы:

1.Гистограмму распределения голосов, поданных за Путина на разных участках. Предварительно к таблице добавьте но- вый столбец (% голосов за Путина) и рассчитайте значения в нем. Источником данных к гистограмме должны быть данные этого столбца.

2.Объемную круговую диаграмму распределения голосов, поданных за всех кандидатов. Источником данных к диаграм- ме выберите диапазон B15 - E15. При построении диаграммы исключите из нее легенду.

7 5

Наберите таблицу, приведенную ниже, и рассчитайте значе- ния ее последнего столбца (по формуле: потребление в 1998 г./ потребление в 1991 г.). В этом столбце используйте процент- ный формат представления чисел (0 десятичных знаков).

ПОКУПАТЕЛЬНАЯ СПОСОБНОСТЬ СРЕДНЕДУШЕВЫХ ДЕНЕЖНЫХ

ДОХОДОВ НАСЕЛЕНИЯ (в месяц, килограммов)

№	Продукт	1991	1995	1996	1997	1998
1	Говядина	76	35	40	39	31
2	Молоко, литров	971	221	187	155	135
3	Яйца, штук	2081	958	966	962	851
4	Рыба мороженая	188	59	60	66	54
5	Сахар-песок	236	97	142	137	89
6	Масло растительное	127	44	59	66	35
7	Маргарин	162	32	45	50	32
8	Картофель	278	263	312	337	210
9	Капуста	357	148	169	230	201
10	Цитрусовые		60	48	80	60
11	Хлеб ржано-пшеничный	795	196	179	195	188
12	Рис	291	105	100	117	72
13	Масло животное	63	20	25	25	18

1998 г.,'% к 1991г.

41

Постройте следующие диаграммы:

1.Сравнение покупательной способности за разные годы (см. образец на с. 77).

При построении диаграммы предварительно выделите об-

ласть ячеек с названиями продуктов и числовыми данными за различные годы. Выберите в качестве вида диаграммы гис- тограмму с накоплением. На втором шаге работы Мастера диаграмм отметьте, что ряды данных располагаются в стро- ках и укажите диапазон ячеек для подписей по оси X.

2.Гистограмму на основе последнего столбца таблицы (за исключением цитрусовых).

3.Круговую диаграмму распределения покупательной спо- собности в 1998 году (предпоследний столбец).

7 6

Покупательная способность

Покупательная способность среднедушевых доходов населения

6000														Масло животное
5000														Рис
														Хлеб ржано-пшеничный
4000														Цитрусовые
3000														Капуста
														Картофель
2000														Маргарин
														Масло растительное
1000														Сахар-песок
0														Рыба мороженая
														Яйца, штук
1991				1995		1996		1997		1998
														Молоко, литров
						Годы
														Говядина

Практическая работа 10 Работа с листами. Консолидация данных

Цель работы: Изучить основные приемы работы с листами Excel. Получить понятие о консолидации данных.

Задача.

Исходя из следующей таблицы начислений сотрудников

№ п/п	Фамилия ИО			Начислено, руб.
		Январь		Март	Апрель		Май	Июнь
1	Иванов И.Д.	2000	2787		3512		3814	4213
2	Сергеев Р.Р.	1657	1847	1987	2132		2431	2621
3	Карелин С.А.	4624	3892	3012	3957		3874	4153
4	Бородин П.П.	767	925	1200	956		1032	1152
5	Заварзин В.К.	2876	3780	4210	4812		5142	4988
6	Лазарев Н.П.						1238	2984

составить за каждый месяц таблицу начислений и удержанно- го подоходного налога, а также соответствующие сводные таб- лицы за 1-й квартал, 2-й квартал и 1-е полугодие. Учесть, что ставка налога равна 13% и рассчитанный налог округляется до целых рублей.

7 7

Указания к выполнению.

	Наберите следующую таблицу:
		A		B		C		D
1				Выплаты за январь 2001 г.
2
3		№ п/п		Фамилия ИО		Начислено		Подоход-
								(13%)
4
		1		Иванов И.Д.		2 000,00р.
5		2		Сергеев Р.Р.		1 657,00р.
6		3		Карелин С.А.		4 624,00р.
7		4		Бородин П.П.		767,00р.
8	5			Заварзин В.К.		2 876,00р.
9				Итого:

·При заполнении ячеек C4 - C9 используйте денежный фор- мат (2 десятичных знака). При расчете подоходного налога (=Начислено * 13%) используйте функцию ОКРУГЛ для ок- ругления до целых рублей. С помощью кнопки Автосумма рас- считайте итоговые значения в ячейках C9 и D9.

·Задайте название рабочего листа таблицы Январь. Для этого в контекстном меню рабочего листа (вызывается щелч- ком правой кнопки мыши по наименованию листа) выберите

Переименовать.

·Скопируйте рабочий лист Январь так, чтобы он оказал- ся вторым листом рабочей книги. Для этого:

1. из контекстного меню листа Январь выберите Переме-

стить/Скопировать; 2. отметьте флажок Создавать копию;

3. выберите лист, перед которым будет создана копия.

·Задайте название Февраль для скопированного листа.

·В листе Февраль измените следующие данные:

1. в заголовке запишите название месяца;

7 8

2. в столбце C запишите значения начислений за февраль (данные возьмите с с. 77);

Обратите внимание, что остальные данные изменились ав- томатически.

· Организуйте рабочий лист Март копированием листа Февраль. Заполните таблицу данными начислений работни- ков за март.

Для того, чтобы рассчитать суммарные значения начисле- ний сотрудников за первые три месяца года (1-й квартал) мож- но воспользоваться специальной возможностью MS Excel, на-

званной консолидация данных.

·Скопируйте лист Март и переименуйте его в 1-йквартал.

·В листе 1-й квартал удалите содержимое ячеек C4 - D9. Для этого выделите этот диапазон и выберите Правка/Очис- тить/Содержимое или нажмите Del на клавиатуре.

·Проведите процесс консолидации данных, выполнив пос- ледовательно следующие шаги:

1. В листе 1-й квартал выделите ячейку C4 (начало консо- лидированных данных).

2. Выполните Данные/Консолидация. В диалоговом окне выберите функцию консолидации - Сумма, а затем

3. Выберите поле ввода Ссылка. В листе Январь выдели- те диапазон C4 - D9. Нажмите кнопку Добавить.

4. Проделайте действия предыдущего шага для листов

Февраль и Март.

5. Отметьте флажок Создавать связи с исходными дан-

ными и нажмите конопку OK для завершения процесса консо- лидации.

Описанная методика носит название консолидации по расположению. Она может быть использована только если дан-

ные исходных областей находятся в одном и том же месте и размещены в одном и том же порядке.

· Скопируйте данные листа Март в лист Апрель и заме- ните данные начислений работников.

7 9

·Скопируйте данные листа Апрель в лист Май и замени- те данные начислений работников.

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

1. Выделите любую ячейку в строке 8.

2. Выполните Вставка/Строки.

3. Скопируйте в буфер обмена диапазон ячеек A9 - D9

(Правка/Копировать).

4. Выделите ячейку A8 и вставьте туда из буфера обмена скопированные данные, сохранив действующие параметры фор-

матирования (Правка/Специальная вставка/Формулы). 5. В строке 9 запишите данные Лазарева Н.П.

·Скопируйте данные листа Май в лист Июнь и замените данные начислений работников.

·Создайте новый лист 2-й квартал на основе листа Июнь.

·	В листе 2-йквартал удалите содержимое ячеек B4 - D10.
·	Проведите процесс консолидации данных, выполнив пос-

ледовательно следующие шаги:

1.В листе 2-й квартал выделите ячейку B4 (начало консо- лидированных данных).

2.Выполните Данные/Консолидация. В диалоговом окне выберите функцию консолидации - Сумма, а затем

3.Выберите поле ввода Ссылка. В листе Апрель выдели- те диапазон B4 - D9. Нажмите кнопку Добавить.

4.Проделайте действия предыдущего шага для листов Май

иИюнь. В этих листах выделяйте ячейки B4 - D10 (выделяе- ется ячеек больше из-за появления нового сотрудника).

5.Отметьте флажок Использовать в качестве имен/Зна- чения левого столбца.

6.Отметьте флажок Создавать связи с исходными дан-

ными и нажмите конопку OK для завершения процесса консо- лидации.

Описанная методика носит название консолидации по ка-

8 0