МЕТОДИЧКА Excel_2.02_2007

Лабораторная работа №3

При копировании формул с абсолютной адресацией ссылки сохраняются (ссылка всегда указывает на одну и ту же ячейку). Ссылка на ячейку

при абсолютной адресации содержит номер строки и букву столбца, перед

которым стоит знак доллара $, например: $B$5; $D$12.

АГ

НИ

ка

Рис.20. Копирование полных абсолю ных ссылок

т

е

Иногда бывает необходимо, чтобы при копировании не менялась только

о

строка или только столбец. В этих случаях спользуется смешанная адре-

и

сация, которая содержит как абсолютные, так и относительные ссылки. При

л

этом символ $ ставится либо только перед номером строки, либо только пе-

б

ред наименованием столбца. Напр мер: B$5, D$12 — не меняется номер

строки; $B5, $D12

и

— не меняется наименование столбца.

ая

б

н

н

о

тр

е

к

л

Э

Рис.21. Копирование смешанных абсолютных ссылок

33

Относительная и абсолютная адресация

Можно вводить символ $ непосредственно, а можно (в режимеНИввода/редактирования) с помощью клавиши F4 для текущей ссылки. Последовательное нажатие этой клавиши влечет поочередный ввод знакаАГ $ перед элементами адреса. Так, для адреса А1 получим цепочку преобразований:

A1→$A$1→ A$1→$A1→ A1. Т.е. для получения желаемого вида ссылки

те ка

1.Переименуйте Лист1 под именем Зарпла а и создайте таблицу, как показано на рисунке 22а. о

либ

ая	б	и

Рис. 22а. Т блица распределения заработной платы

2.	В ячейке В8 рассчитайте сумму коэффициентов: =СУММ(В4:В7).
3.				о	н
	Произведите расчетн заработной платы по формуле:
		тр
	Зарплата = Ф нд з.п / Сумма коэфф. * Коэфф. работника
	Таким об азом, зарплата Петра (ячейка С4) вычисляется по формуле:
	=В$1/B$8*B4
4.	В яч йкек		С8 рассчитайте сумму всех установленных зарплат, кото-
Э	раяев	точности должна быть равна фонду заработной платы
	(1000)л	.
34

	Лабораторная работа №3
5. На рисунке 22б отображена итоговая таблица.			НИ
	ка	АГ
т
Рис. 22б. Таблица распределения зарабоеной платы

1.Перейдите на Лист2, переименуйтелегои в Собственность и соз- дайте таблицу, как показано на рисункеб 23а.

2.В ячейках В3 и D3 самостоятельнои рассчитайте общие суммы ста- рых и новых акций. б о

					н	н	ая
				о
				тр
			к	Рис. 23а. Таблица перераспределения собственности
	3. В ячей ах С4 и Е4 также самостоятельно рассчитайте относитель-
		е
		ную значимость каждого акционера для старого и для нового паке-
		л
	Э	тов акций по формуле:
		= Сумма акций акционера / Общая сумма акций
							35

Относительная и абсолютная адресация

4.С помощью соответствующих кнопок на панели инструментовНИ Форматирование к ячейкам В3-В7 и D3-D9 примените формат Денежный, а к ячейкам С3-С7 и Е3-Е9 примените форматАГ Про-

центный.

5.Правильность расчетов подтверждает получение 100% в ячейках С3 и Е3. В итоге вы должны получить таблицу, ккак пок зано на ри- сунке 23б.

те

би б л и

1.Перейдите на Лист3,аяпереименуйте его в Ведомость и создайте таблицу, как показано а рисунке 24а.

он о

	л	е	к	тр
Э				Рис. 24а. Таблица расчета заработной платы
36

Лабораторная работа №3

2. Произведите расчет заработной платы с учетом того, что:

§если оклад <= 2000 рублей, то оклад увеличивается в 2 раза;

§если 2000 < оклад <= 4000 рублей, то оклад увеличивается в

1,5 раза;

АГ

НИ

§ если оклад > 4000 рублей, то оклад не увеличивается.

В ячейке F4

ка

с помощью логической функции это будет записано сле-

дующим образом:

е

=ЕСЛИ(С4<=2000;C4*2;ЕСЛИ(С4<=4000;C4*1,5;C4))

3.Скопируйте формулу в остальные ячейки.

4.В ячейке Е4 самостоятельно произведите расчетт

надбавки с уче-

том того, что:

л

и

о

§если пол = “ж“ и месяц = “март“, то надбавка равна 300;

б

§если пол = “м“ и месяц = “февраль“, то надбавка равна 300;

и

§в остальных случаях надбавка равна 0.

б

5.В ячейке H4 также самостоятельно произведите расчет суммы к

ая

выдаче с учетом того, что:

§если стаж <= 3 лет, то к зарплате добавляется компенсация и

надбавка;

н

н

§если 3 < стаж <= 10 лет, то к зарплате добавляется 30% от зар-

о

платы и надбавка;

§если стаж > 10 лет, то к зарплате добавляется 50% от зарплаты и

надбавка.

е

6. Скопируйтетр

формулу в остальные ячейки и сравните полученные

л

со значениями, приведенными на рисунке 24б.

Э

знач нияк

37

Относительная и абсолютная адресация

АГ

НИ

ка

Рис. 24б. Таблица расчета заработной платы

Упражнение 4

и

о

т

е

Перейдите на Лист4 и по номеру вашего к мпьютера выберите но-

мер варианта, приведенного ниже.

л

Вариант 1

и

1) Создайте таблицу «Зарплата бр гадыб », как показано на рисунке.

Минимальная зарплата

3000

№

Фамилия

Начислено

Вычислительные значения

п/п

б

Подоходный налог

На руки

1.

Белов

15000

2.

Сергеева

18000

3.

Павлова

н

ая

22000

4.

Тарасова

7000

5.

Чернов

н

20000

2) В соответствующих ячейках введите формулы:

тр

Подоходный налог:

§3% от начисленияо , если сумма начисления < 15000 рублей;

§15% от начисления, если сумма начисления >= 15000 рублей,

но < 20000 рублей;

е

§ 20% от начисления, если сумма начисления >= 20000 рублей.

На рукик

= Начислено – Налог + Минимальная зарплата.

Э

л

38

Лабораторная работа №3

Вариант 2

НИ

1) Создайте таблицу «Прибыль», как показано на рисунке.

№

Название товара

Выручка

Вычислительные значения

п/п

Налог с продаж

Прибыль

1.

Меха

500000

2.

Кожа

200000

3.

Головные уборы

150000

4.

Детская одежда

280000

ка

АГ

5.

Детская обувь

100000

Аренда помещения

10000

т

е

2) В соответствующих ячейках введите формулы:

Налог с продаж:

§8% от Выручки, если выручка < 200000 рублей;

и

§5% от Выручки, если выручка >= 200000 рублей, но < 300000

рублей;

л

о

§ 3% от Выручки, если выручка >= 300000 рублей.

Прибыль = Выручка – Налог – Аренда помещения.

Вариант 3

б

и

б

1) Создайте таблицу «Ведомость зарплаты», как показано на рисунке.

№

Фамилия

ая

Вычеты

Подоходный

На руки

Количество Начислено

п/п

детей

на детей

налог

1.

Белов

3

7000

2.

Сергеева

0

15000

3.

Павлова

2

25000

4.

Тарасова

н

2

6500

5.

Чернов

о

н

1

18000

Общий вычет

400

тр

2) В соответствующих ячейках введите формулы:

Вычет на детей:

§500 на каждого ребенка, если начисления < 10000 рублей;

§300 на каждого ребенка, если начисления >= 10000 рублей,

е

л

но < 20000 рублей;

§0 рублейк

, если начисления >= 20000 рублей.

Э

Подоходный налог = 13% от «Начислено – Вычеты на детей»

На руки = Начислено – Налог – Общий вычет.

39

Относительная и абсолютная адресация

Вариант 4

1) Создайте таблицу «Товары на складе», как показано на рисунке.

Наценка за регион

500

№

Название

Количество

Оптовая

Расчетные величины

п/п

товара

цена

НИ

Наценка

Розничная

Сумма

цена

1.

Костюмы

120

3200

2.

Брюки

90

850

ка

АГ

3.

Рубашки

65

120

4.

Юбки

110

1100

5.

Платья

45

3000

е

т

2) В соответствующих ячейках введите формулы:

о

Наценка (рассчитывается от оптовой цены):

§10% , если количество > 100;

§15%, если количество <= 100, но > 50;

§ 20%, если количество <= 50.

л

б

+ Наценка за регион

Розничная цена = Оптовая цена + Наценкаи

Сумма = Оптовая

цена * Количество.

Вариант 5

1) Создайте таблицу «Электроэнергияи

», как показано на рисунке.

б

Наценка

100

№

Фамилия

Затраты электроэнергии (кВт)

Расчетные величины

п/п

Цена за 1 кВт

Сумма

1.

Белов

н

ая

80

2.

Сергеева

120

3.

Павлова

н

290

4.

Тарасова

65

5.

Чернов

580

соответствующихо

2) В

ячейках введите формулы:

Цена за 1 кВт:

к

§2,05 рубля при потреблении электроэнергии до 100 кВт;

§3 рублятрпри потреблении электроэнергии >= 100 кВт, но < 500

л

кВт;

§ (3 рубля + 10% от Наценки) при потреблении электроэнергии

Э

е>= 500 кВт.

Сумма = Затраты электроэнергии * Цена за 1 кВт.

40

Лабораторная работа №3

Вариант 6

1) Создайте таблицу «Стоимость лечения», как показано на рисунке.

№

Фамилия

Длительность лечения

Оплата за день

Стоимость

п/п

(дни)

лечения

НИ

1.

Белов

8

2.

Сергеева

25

3.

Павлова

10

4.

Тарасова

12

ка

АГ

5.

Чернов

20

Курсовка

600

2) В соответствующих ячейках введите формулы:

Оплата за день:

т

§300 рублей, если длительность лечения <е10 дней;

§400 рублей + 10% от Курсовки, если

длительность лечения >=

10 дней, но < 20 дней;

и

одлительность лечения >=

§ 500 рублей + 20% от Курсовки, если

20 дней.

Стоимость лечения = Длительность

ечения * Оплата за день +

Курсовка

л

Вариант 7

б

и

б

1) Создайте таблицу «Оплата за коммунальные услуги», как показано

на рисунке.

Сумма за текущ. ремонт дома

150

№

Фамилия

Количество

Количество

Сумма оплаты

С учетом льгот

п/п

жильцов

льгот

ая

1.

Белов

н

4

1

2.

Сергеева

1

2

2)В соответствующихтр о ячейках введите формулы: Суммакоплаты = 200 рублей с одного жильца + Сумма за текущ.§еснижается на 19%, если льготын > 1;

		л
	Э	§снижается на 10%, если льготы = 1;
		§ не снижается, если льгот нет.
		41

Относительная и абсолютная адресация
Вариант 8							НИ
1) Создайте таблицу «Срочный вклад», как показано на рисунке.
	№	Фамилия	Размер	Срок договора	Расчетные величины
	п/п		вклада	(мес)	Проценты	Полученная
						сумма
	1.	Белов	30000	2		АГ
	2.	Сергеева	10000	6
	3.	Павлова	50000	3	ка
	4.	Тарасова	65000	1
	5.	Чернов	18000	10
	Минимальный вклад		1000

т

2) В соответствующих ячейках введите формулы:

Проценты (рассчитывается от размера вкладае):

§1%, если срок договора < 3 месяцев;

и

§3%, если срок договора >= 3 месяцев, но < 6 месяцев;

б

л

§ 5%, если срок договора >= 6 месяцев.о

от Мини-

Вариант 9

б

и

1) Создайте таблицу «Стоимость работ», как показано на рисунке.

№

Состав работы

ая

Стоимость работы

НДС

Итоговая сумма

п/п

1.

Изучение документов

13,81

2.

Составление справки

46,85

3.

н

2,18

Изготовление копий

4.

н

7,40

Подшивка докуме тов

5.

Сдача в архив

5,35

о

2

Количество собстве иков

тр

2) В соответствующих ячейках введите формулы:

НДС = 18% от Стоимости работ

Итоговая сумма:

§Стоимость работ + НДС, если количество собственников = 1;

е

§Стоимость работ * Количество собственников + НДС, если ко-

личк

ство собственников =2;

л

Э

§ Стоимость работ * Количество собственников +14,50 + НДС,

если количество собственников >2.

42