Основные сведения об MS EXCEL

15.09.07

15.09.07

15.09.07

15.09.2007.

Район	Поставка	Дата поставки	Количество	Опт.цена	Розн.цена	Доход	Отчисления	Налог	Остаток
Западный	Мясо	01.сен	23 тонн	12 т.руб	14,4 т.руб	331,2 т.руб	159, т.руб	33,1 т.руб	139,1 т.руб
Южный	Молоко	02.сен	30 тыс.литр.	3 т.руб	3,6 т.руб	108, т.руб	51,8 т.руб	10,8 т.руб	45,4 т.руб
Восточный	Капуста	03.сен	32 тонн	62 т.руб	74,4 т.руб	2 380,8 т.руб	1 142,8 т.руб	238,1 т.руб	999,9 т.руб
Северный	Картофель	04.сен	95 тонн	95 т.руб	114, т.руб	10 830, т.руб	5 198,4 т.руб	1 083, т.руб	4 548,6 т.руб
Западный	Морковь	05.сен	46 тонн	76 т.руб	91,2 т.руб	4 195,2 т.руб	2 013,7 т.руб	419,5 т.руб	1 762, т.руб
Южный	Мясо	03.сен	65 тонн	32 т.руб	38,4 т.руб	2 496, т.руб	1 198,1 т.руб	249,6 т.руб	1 048,3 т.руб
Восточный	Молоко	07.сен	79 тыс.литр.	64 т.руб	76,8 т.руб	6 067,2 т.руб	2 912,3 т.руб	606,7 т.руб	2 548,2 т.руб
Северный	Капуста	05.сен	23 тонн	62 т.руб	74,4 т.руб	1 711,2 т.руб	821,4 т.руб	171,1 т.руб	718,7 т.руб
Западный	Картофель	06.сен	13 тонн	45 т.руб	54, т.руб	702, т.руб	337, т.руб	70,2 т.руб	294,8 т.руб
Южный	Морковь	04.сен	46 тонн	62 т.руб	74,4 т.руб	3 422,4 т.руб	1 642,8 т.руб	342,2 т.руб	1 437,4 т.руб
Восточный	Мясо	01.сен	95 тонн	31 т.руб	37,2 т.руб	3 534, т.руб	1 696,3 т.руб	353,4 т.руб	1 484,3 т.руб
Северный	Молоко	02.сен	76 тыс.литр.	32 т.руб	38,4 т.руб	2 918,4 т.руб	1 400,8 т.руб	291,8 т.руб	1 225,7 т.руб
Западный	Капуста	01.сен	32 тонн	64 т.руб	76,8 т.руб	2 457,6 т.руб	1 179,6 т.руб	245,8 т.руб	1 032,2 т.руб
Южный	Картофель	04.сен	54 тонн	32 т.руб	38,4 т.руб	2 073,6 т.руб	995,3 т.руб	207,4 т.руб	870,9 т.руб
Восточный	Морковь	02.сен	65 тонн	62 т.руб	74,4 т.руб	4 836, т.руб	2 321,3 т.руб	483,6 т.руб	2 031,1 т.руб

ИТОГО		Доход
		Остаток

Рис.3. Поставки по районам

Ø «Доход» — общая сумма, полученная за весь продукт, проданный по розничной цене;

Ø «Отчисления» — сумма, определяемая с помощью Процента отчислений от дохода;

Ø «Налог» — часть дохода, вычисляемая Процентом налога от дохода;

Ø «Остаток» — сумма, равная разности дохода и вычетов налога и отчислений.

6. Используя «Формат по образцу» () указать денежные единицы.

7. Посчитать общий Доход и Остаток.

Задание 2

1. Используя данные рис.4 составить таблицу «Отчет по фирме».

15.09.07

	Товар 1	Товар 2
Месяц	Цена	Продано	Итого	Цена	Продано	Итого
Январь	$15	10 шт.	$150	$20	100 шт.	$2 000
Февраль	$20	16 шт.	$320	$20	90 шт.	$1 800
Март	$25	22 шт.	$550	$20	80 шт.	$1 600
Квартал 1		48 шт.	$1 020		270 шт.	$5 400
Апрель	$30	28 шт.	$840	$20	70 шт.	$1 400
Май	$35	34 шт.	$1 190	$20	60 шт.	$1 200
Июнь	$40	40 шт.	$1 600	$20	50 шт.	$1 000
Квартал 2		102 шт.	$3 630		180 шт.	$3 600
Июль	$45	46 шт.	$2 070	$30	40 шт.	$1 200
Август	$50	52 шт.	$2 600	$30	50 шт.	$1 500
Сентябрь	$55	58 шт.	$3 190	$30	60 шт.	$1 800
Квартал 3		156 шт.	$7 860		150 шт.	$4 500
Октябрь	$60	64 шт.	$3 840	$30	70 шт.	$2 100
Ноябрь	$65	70 шт.	$4 550	$30	80 шт.	$2 400
Декабрь	$70	76 шт.	$5 320	$30	90 шт.	$2 700
Квартал 4		210 шт.	$13 710		240 шт.	$7 200
Итого за год		516 шт.	$26 220		840 шт.	$20 700

Рис.4 Отчет по фирме Север за 2005 год

2. Таблица заполняется с использованием функции MS EXCEL Автозаполнение с последующей вставкой строк.

(Подробнеео функции Автозаполнение сказано в Приложении к данной лабораторной работе — раздел Основные сведения об MS EXCEL пункт 3. Ввод и редактирование данных.)

3. Итоги по кварталам заполняются суммированием соответствующих ячеек по месяцам.

Рис. 5.

4. Создать 2 круговых диаграммы – по каждому товару (см.рис.5).

Задание 3

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

2. Автозаполнением заполнить столбец года.

3. Продлить таблицу до прошлого календарного года.

4. Величина капитала на первый рабочий год равна начальному капиталу.

5. Размер капитала на начало остальных периодов равен величине остатка на предыдущий отчетный год.

6. Столбцы «Доход» и «Расход» заполняются с использованием функции Прогрессия, находящейся в Правке\Заполнить. Шаг прогрессии взять равным («+» или «-») последним двум цифрам № студенческого.

7. Столбец «Остаток» формируется из капитала в начале года, добавляется доход за год и вычитается расход.

8. Заполнить нижние строки при помощи «Мастера функций».

9. Отформатировать столбцы по образцу.

10. Построить Гистограмму, отражающую рост капитала по годам.

Начальный капитал

$10 000

	капитал	доход	расход	остаток
1992 год	$10 000	$500	$300	$10 200
1993 год	$10 200	$600	$280	$10 520
1994 год	$10 520	$700	$260	$10 960
1995 год	$10 960	$800	$240	$11 520
1996 год	$11 520	$900	$220	$12 200
1997 год	$12 200	$1 000	$200	$13 000
1998 год	$13 000	$1 100	$180	$13 920
1999 год	$13 920	$1 200	$160	$14 960
2000 год	$14 960	$1 300	$140	$16 120
2001 год	$16 120	$1 400	$120	$17 400
2002 год	$17 400	$1 500	$100	$18 800
2003 год	$18 800	$1 600	$80	$20 320
Максимум	$18 800	$1 600	$300	$20 320
Среднее	$13 300	$1 050	$190	$14 160
Минимум	$10 000	$500	$80	$10 200

Рис.6 Общая таблица доходов по годам

Задание 4

Данное задание состоит из 4 упражнений на использование Логических функций (см.рис.8).

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

В Упражнении №1 (см.рис.8-а) столбцы «Доход» и «Расход» заполняются одновременно, используя функцию генерация случайных чисел. Интервал распределения — последние две цифры № студенческого10; число переменных в этом случае равно двум (см.рис.7).

Рис.7.

Столбец «Рабочие дни» заполняется при помощи автозаполнения.

Упражнение №1.

Рабочие дни	Доход	Расход	Прибыль	Убыток
01.04.2006	12 руб..	22 руб.		10 руб.
02.04.2006	23 руб.	11 руб.	12 руб.
03.04.2006	32 руб.	55 руб.		23 руб.
04.04.2006	97 руб.	66 руб.	31 руб.
05.04.2006	65 руб.	88 руб.		23 руб.
08.04.2006	32 руб.	33 руб.		1 руб.
09.04.2006	65 руб.	44 руб.	21 руб.
10.04.2006	54 руб.	55 руб.		1 руб.
11.04.2006	87 руб.	22 руб.	65 руб.
12.04.2006	65 руб.	77 руб.		12 руб.

Рис.8-а

Упражнение №2.

Цена изделия	Скидка 10%	Стоим. покупки
45 руб.	да	41 руб.
65 руб.	нет	65 руб.
32 руб.	нет	32 руб.
54 руб.	да	49 руб.
87 руб.	нет	87 руб.
32 руб.	нет	32 руб.
42 руб.	да	38 руб.

Рис.8-б

В Упражнениях №2-№4 (см.рис.8-б-г) столбец «Цена изделия» заполняется аналогично столбцу «Количество» из Задания 1.

Упражнение №3.

Цена изделия	Кол-во изделий	Процент скидки	Сумма скидки	Стоим. покупки
45 руб.	1 шт.	0%	0 руб.	45 руб.
65 руб.	3 шт.	0%	0 руб.	195 руб.
32 руб.	5 шт.	3%	5 руб.	155 руб.
54 руб.	11 шт.	5%	30 руб.	564 руб.
32 руб.	2 шт.	0%	0 руб.	64 руб.
42 руб.	4 шт.	3%	5 руб.	163 руб.

Рис.8-в

В Упражнении №3 процент скидки зависит от кол-ва, покупаемых изделий: до 3 шт. — 0%, от 4 до 5 — 3%, свыше 5 шт. — 5% и вычисляется с помощью функции ЕСЛИ().

Упражнение №4.

Цена изделия	Кол-во изделий	Процент скидки	Сумма скидки	Стоим. покупки
45 руб.	1 шт.	0%	0 руб.	45 руб.
65 руб.	3 шт.	5%	9,75 руб.	185,25 руб.
32 руб.	5 шт.	5%	8 руб.	152 руб.
54 руб.	11 шт.	5%	29,7 руб.	564,3 руб.
87 руб.	8 шт.	5%	34,8 руб.	661,2 руб.
32 руб.	2 шт.	0%	0 руб.	64 руб.
42 руб.	4 шт.	5%	8,4 руб.	159,6 руб.

Рис.8-г

В Упражнении №4 (см.рис.8-г) процент скидки зависит от стоимости покупаемых изделий: до 100 руб. — 0%, свыше — 5%.

Задание 5

1. Создать «Ведомость заработной платы преподавателей» (см.рис.9). Заполнить исходные данные.

2. Не забудьте вставить перед таблицей текущую дату.

№	Ф.И.О.	Должность	Дата приема	Стаж	Оплата за час	Нагрузка
	Иванов И.И.	преподаватель	01.07.1999	6 лет	70 руб.	60 ак.час.
	Петров А.Г.	ст.преподаватель	04.11.1995	5 лет	75 руб.	50 ак.час.
	Сидоров К.П.	ассистент	11.06.2001	7 лет	60 руб.	65 ак.час.
	Федоров А.Л.	профессор	25.03.1985	16 лет	110 руб.	40 ак.час.
	Васильев Е.О.	преподаватель	04.07.1990	11 лет	70 руб.	60 ак.час.
	Гигорева А.О.	ст.преподаватель	04.04.2000	15 лет	75 руб.	50 ак.час.
	Зорина П.Л.	ассистент	23.03.1992	9 лет	60 руб.	65 ак.час.
	Жуков Н.Д.	профессор	16.05.1991	10 лет	110 руб.	40 ак.час.
	Сурков Р.Д.	преподаватель	05.12.1990	10 лет	70 руб.	60 ак.час.
	Барсуков Е.Л.	ст.преподаватель	08.12.1989	11 лет	75 руб.	50 ак.час.
	Тюрин Н.Л.	ассистент	08.06.2003	4 лет	60 руб.	65 ак.час.

Продолжение таблицы

Зарплата	Процент надбавки	Сумма надбавки	Итого	Сумма налога	На руки
900 руб.	15%	135 руб.	1 035 руб.	135 руб.	900 руб.
1 000 руб.	15%	150 руб.	1 150 руб.	150 руб.	1 001 руб.
650 руб.	15%	98 руб.	748 руб.	97 руб.	650 руб.
2 400 руб.	25%	600 руб.	3 000 руб.	390 руб.	2 610 руб.
900 руб.	25%	225 руб.	1 125 руб.	146 руб.	979 руб.
1 000 руб.	25%	250 руб.	1 250 руб.	163 руб.	1 088 руб.
650 руб.	15%	98 руб.	748 руб.	97 руб.	650 руб.
2 400 руб.	25%	600 руб.	3 000 руб.	390 руб.	2 610 руб.
900 руб.	25%	225 руб.	1 125 руб.	146 руб.	979 руб.
1 000 руб.	25%	250 руб.	1 250 руб.	163 руб.	1 088 руб.
650 руб.	25%	163 руб.	813 руб.	106 руб.	707 руб.

Рис.9 Ведомость расчета заработной платы

3. «Дата Приема» — генерация случайных чисел (разумные пределы).

4. Стаж вычисляется, используя разницу между датой приема и текущей датой (данный результат MS EXCEL вычисляет в сутках). Полное число лет вычисляется функцией ЦЕЛОЕ().

5. Зарплата зависит от оплаты за час и количества отработанных часов.

6. Процент надбавки зависит от стажа:

до 3 лет — 0%; до 5 лет — 10 %; от 5 до 10 лет — 15 %; свыше 10 лет — 25 %.

7. Сумма надбавки = Зарплата * Процент надбавки.

8. Сумма налога зависит от итога: до 4300 руб. — 13%; свыше — 30%.

9. Таблицу необходимо отсортировать по алфавиту преподавателей (и/или по году приема на работу и по нагрузке). Обратить внимание на нумерацию строк в таблице — порядок должен остаться прежним.

10. Для представления преподавателю данного задания разделить окно таблицы (Окно\Разделить) на две части, выделив три первых столбца.

Задание 6

1. Таблица «Сметная ведомость» (см.рис.10) формируется из данных предыдущего задания.

2. Столбец «Ф.И.О.» копируется из Задания 5.

Дата

15.09.07

Стоимость БТ

700 руб.

№	Ф.И.О.	Стаж	Тип полиса	Доплата за стоматологию	Доплата за стационар	Сумма
	Иванов И.И.	6 лет	БТС	1 000 руб.	0 руб.	2 700 руб.
	Петров А.Г.	5 лет	БТС	0 руб.	0 руб.	1 700 руб.
	Сидоров К.П.	7 лет	БТС	1 000 руб.	0 руб.	2 700 руб.
	Федоров А.Л.	16 лет	БТСС	1 000 руб.	2 500 руб.	5 200 руб.
	Васильев Е.О.	11 лет	БТСС	1 000 руб.	2 500 руб.	5 200 руб.
	Гигорева А.О.	15 лет	БТСС	1 000 руб.	2 500 руб.	5 200 руб.
	Зорина П.Л.	9 лет	БТС	1 000 руб.	0 руб.	2 700 руб.
	Жуков Н.Д.	10 лет	БТСС	1 000 руб.	2 500 руб.	5 200 руб.
	Сурков Р.Д.	10 лет	БТСС	1 000 руб.	2 500 руб.	5 200 руб.
	Барсуков Е.Л.	11 лет	БТСС	1 000 руб.	2 500 руб.	5 200 руб.
	Тюрин Н.Л.	14 лет	БТСС	1 000 руб.	2 500 руб.	5 200 руб.

Рис.10 Сметная ведомость по медицинскому страхованию для сотрудников

3. Столбец «Стаж» определяется ссылкой на соответствующий столбец в предыдущем задании.

4. Столбец «Тип страхового полиса», выдаваемого сотруднику, зависит от стажа работы: стаж до 5 лет — тип полиса БТ, до 10 лет — БТС, 10 и выше — БТСС (использовать функцию ЕСЛИ()).

5. Для типов полисов БТС и БТСС доплата за услуги стоматолога 1000 руб.

6. Для типа БТСС дополнительно доплата за лечение в стационаре 2,5 тыс.руб.

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

Задание 7

Используя данные таблицы «Поставки» из Задания 1, составить сводные таблицы по образцам на рис.11. для выполнения задания использовать «Данные\Мастер сводных таблиц».

Сумма по полю Розн.цена	Поставка
Дата поставки	Капуста	Картофель	Молоко	Морковь	Мясо	Общий итог
01.09.2003	76,8 т.руб	0 т.руб	0 т.руб	0 т.руб	51,6 т.руб	128,4 т.руб
02.09.2003	0 т.руб	0 т.руб	42, т.руб	74,4 т.руб	0 т.руб	116,4 т.руб
03.09.2003	74,4 т.руб	0 т.руб	0 т.руб	0 т.руб	38,4 т.руб	112,8 т.руб
04.09.2003	0 т.руб	152,4 т.руб	0 т.руб	74,4 т.руб	0 т.руб	226,8 т.руб
05.09.2003	74,4 т.руб	0 т.руб	0 т.руб	91,2 т.руб	0 т.руб	165,6 т.руб
06.09.2003	0 т.руб	54, т.руб	0 т.руб	0 т.руб	0 т.руб	54, т.руб
07.09.2003	0 т.руб	0 т.руб	76,8 т.руб	0 т.руб	0 т.руб	76,8 т.руб
Общий итог	225,6 т.руб	206,4 т.руб	118,8 т.руб	240, т.руб	90, т.руб	880,8 т.руб

Рис.11-а Изменение розничных цен по отдельным категориям продуктов

Сумма по полю Доход	Поста вка
Район	Капуста	Картофель	Молоко	Морковь	Мясо	Общий итог
Восточный	2 380,8 т.руб	0 т.руб	6 067,2 т.руб	4 836, т.руб	3 534, т.руб	16 818, т.руб
Западный	2 457,6 т.руб	702, т.руб	0 т.руб	4 195,2 т.руб	331,2 т.руб	7 686, т.руб
Северный	1 711,2 т.руб	10 830, т.руб	2 918,4 т.руб	0 т.руб	0 т.руб	15 459,6 т.руб
Южный	0 т.руб	2 073,6 т.руб	108, т.руб	3 422,4 т.руб	2 496, т.руб	8 100, т.руб
Общий итог	6 549,6 т.руб	13 605,6 т.руб	9 093,6 т.руб	12 453,6 т.руб	6 361,2 т.руб	48 063,6 т.руб

Рис.11-б Доходы районов по отдельным видам поставок

Сумма по полю Количество	Поставка
Дата поставки	Капуста	Картофель	Молоко	Морковь	Мясо	Общий итог
01.09.2003	32 тонн	0 тонн	0 тыс.литр.	0 тонн	118 тонн	150 тонн
02.09.2003	0 тонн	0 тонн	106 тыс.литр.	65 тонн	0 тонн	171 тонн
03.09.2003	32 тонн	0 тонн	0 тыс.литр.	0 тонн	65 тонн	97 тонн
04.09.2003	0 тонн	149 тонн	0 тыс.литр.	46 тонн	0 тонн	195 тонн
05.09.2003	23 тонн	0 тонн	0 тыс.литр.	46 тонн	0 тонн	69 тонн
06.09.2003	0 тонн	13 тонн	0 тыс.литр.	0 тонн	0 тонн	13 тонн
07.09.2003	0 тонн	0 тонн	79 тыс.литр.	0 тонн	0 тонн	79 тонн
Общий итог	87 тонн	162 тонн	185 тыс.литр.	157 тонн	183 тонн	774 тонн

Рис.11-в Изменение количества поставок по видам продукции

Сумма по полю Налог	Район
Дата	Восточный	Западный	Северный	Южный	Общий итог
01.09.2003	353,4 т.руб	278,9 т.руб	0 т.руб	0 т.руб	632,3 т.руб
02.09.2003	483,6 т.руб	0 т.руб	291,8 т.руб	10,8 т.руб	786,2 т.руб
03.09.2003	238,1 т.руб	0 т.руб	0 т.руб	249,6 т.руб	487,7 т.руб
04.09.2003	0 т.руб	0 т.руб	1 083, т.руб	549,6 т.руб	1 632,6 т.руб
05.09.2003	0 т.руб	419,5 т.руб	171,1 т.руб	0 т.руб	590,6 т.руб
06.09.2003	0 т.руб	70,2 т.руб	0 т.руб	0 т.руб	70,2 т.руб
07.09.2003	606,7 т.руб	0 т.руб	0 т.руб	0 т.руб	606,7 т.руб
Общий итог	1 681,8 т.руб	768,6 т.руб	1 546, т.руб	810, т.руб	4 806,4 т.руб

Рис.11-г Налоговые отчисления по районам

Приложение к лабораторной работе «Работа с электронными таблицами MS EXCEL»

1. Структура экрана

Окно приложения MS Excel обладает всеми элементами окон Windows: строка заголовка окна, кнопки управления размерами окна, системное меню. В главном меню Файл, Правка и т.д. функционально скомпонованы команды, необходимые для работы с электронными таблицами. Пункт меню Вид содержит список команд, определяющих внешний вид экрана. Команда Панели инструментов... позволяет вывести на экран/убрать с экрана панели инструментов: стандартную, форматирования, рисования и т.д. Вывод на экран Строки формул и Строки состояния также управляется из меню Вид. В режиме Полный экран на экране остается только строка главного меню. Команда Масштаб позволяет увеличить (напр., 150%) или уменьшить (напр., 75%) изображение на экране.

Рабочая область окна MS Excel представляет собой таблицу, состоящую из строк и столбцов, на пересечение которых находятся ячейки. Строки заголовков содержат наименования столбцов (А, В, С...) и строк (1, 2, 3...). Количество строк и столбцов в электронной таблицу ограничено только ресурсами компьютера. Для просмотра таблицы используются вертикальная и горизонтальная полосы прокрутки. Стиль адресных ссылок, наличие или отсутствие полос прокрутки, сетка и т.д. задаются командой Параметры из меню Сервис.

2. Рабочая книга

Документ, создаваемый в MS Excel — файл рабочей книги. Рабочая книга состоит из заданного по умолчанию (Сервис\Параметры\Основные) количества листов, ярлыки которых присутствуют на экране. Переход по листам осуществляется с помощью мыши. Состав рабочей книги может изменяться: листы добавляются (Вставка\Рабочий лист), удаляются (Правка\Удалить лист), переименовываются (Формат\Лист). Вы можете скопировать, переместить их в пределах данной рабочей книги или в другую книгу — Правка\Переместить или Скопировать. Команда Формат\Лист\Скрыть или Показать позволяет не выводить рабочий лист на экран или, наоборот показать его. Список этих команд приведен также в контекстном меню, которое вызывается щелчком правой кнопки мыши на ярлыке листа (см. Рис.1).

Рис.1.

3. Ввод и редактирование данных

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

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

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

а) б)

Рис.2.

Дата добавления: 2014-10-15