5. Лабораторная работа № 5 Тема: табличный процессор ms excel

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

Задание: По указанной литературе, методическим указаниям к данной работе и Приложению изучить порядок работы с программой (приложением) MS Excel. Ответить на контрольные вопросы.

Литература: [1]-стр. 282-338, [2]-стр. 252-291.

Перечень заданий по Excel

1. Основные приемы работы, ввод данных и формул, оформление таблиц.

2. Создание электронной таблицы и заполнение формулами, форматирование ячеек. Редактирование таблиц.

3. Работа с автозаполнением и составление формул, абсолютные и относительные ссылки.

4. Диаграммы. Типы диаграмм.

5. Процедура создания диаграммы с помощью мастера диаграмм.

6. Настройка (оформление) диаграммы.

7. Составление условий в электронных таблицах. Анализ «что-если».

8. Подбор параметра.

9. Сортировка данных, фильтры, работа с листами рабочей книги.

Задания для выполнения работы

Задание 1. Расчет налога на добавленную стоимость

Вычислить сумму налога на добавленную стоимость (НДС), «чистую» (без НДС) и общую сумму, если:

• цена единицы товара, включая НДС, — 12 500 руб.;

• количество проданного товара — 27 шт.;

• ставка налога на добавленную стоимость — 20 %.

Заполнить таблицу — столбцы А и В, начиная с ячейки А1. В столбце А — названия параметров, в столбце В — значения параметров:

	А	В
1	Цена	12 500 р.	12 500 р.
2	Количество	27	27
3	Общая стоимость	=В1*В2	337 500 р.
4	Ставка НДС	0,2	20%
5	Сумма НДС	=ВЗ*20/120	56 250р.
6	Стоимость без НДС	=ВЗ-В5	281 250 р.

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

Задание 2. На предприятии работники имеют следующие оклады: начальник отдела — 1000 руб., инженер 1 кат. — 860 руб., инженер — 687 руб., техник — 315 руб., лаборант — 224 руб. Предприятие имеет два филиала: в Средней Полосе и в условиях Крайнего Севера. Все работники получают надбавку 10% от оклада за вредный характер работы, 25% — от оклада ежемесячной премии. Со всех работников удерживают 20% подоходный налог, 3% — профсоюзный взнос и 1% — в пенсионный фонд. Работники филиала, расположенного в Средней Полосе, получают 15% районного коэффициента, работники филиала, расположенного в районе Крайнего Севера, имеют 70% районного коэффициента и 50% северной надбавки от начислений.

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

Требуется:

а) при помощи электронной таблицы рассчитать суммы к получению каждой категории работников;

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

Задание 3. Обработка данных метеостанции

1. Подготовьте лист с именем «Осадки»

	А	В	С	D
1	Количество осадков (в мм)
2
3		2003 г.	2004 г.	2005 г.
4	январь	37,2	34,5	8
5	февраль	1,4	51,3	1,2
6	март	16,5	20,5	3,8
7	апрель	19,5	26,9	11,9
8	май	11,7	45,5	66,3
9	июнь	129,1	71,5	60
10	июль	57,1	152,9	50,6
11	август	43,8	96,6	145,2
12	сентябрь	85,7	74,8	79,9
13	октябрь	86	14,5	74,9
14	ноябрь	12,5	21	56,6
15	декабрь	21,2	22,3	9,4

2. Получите итоговые данные

	А	В	С	D	Е
16	Суммарно за каждый год
17	Суммарно за три года
18	максимум
19	минимум
20	среднее за месяц

Введите формулы:

в В17 = СУММ (В4:В15)

в В18 = МАКС (В4:В15)

в В19 = МИН (В4:В15)

в В20 = СРЗНАЧ (В4:В15)

Далее эти формулы скопируйте в C17:D21.

Задание 4.

1. Составить электронные таблицы для вывода графиков функций y=A*sin(x*3,14)+C+B, y₁=A₁*sin(x*3,14)+C₁+B₁ и их суммарного значения у₂ на интервале [-2;2] с шагом 0,2. Параметры: А=-2,0, В=1,57, С=0,00, А₁=5,0, В₁=0,628, С₁= -2,00. В одной системе координат (на одной диаграмме) построить синусоиды у, у₁ и у₂.

2. Составить электронную таблицу для вывода графика квадратичной функции y=Ax²+Вх+С, х изменяется на интервале [-5;5] с шагом 0,2.

3. Составить электронную таблицу для вывода графика функции y=A*sin(Вx+C), х изменяется на интервале [n1;n2] с шагом h=(n2-n1)/30.

4. Составить электронную таблицу для вывода графика квадратичной функции

5. . Шаг h=0,1. Построить график функции z.

6. Построить график функции y=f(x) при заданных значениях аргумента x=-2(0.1)2. .

7. Построить график функции g=f(x) при заданных значениях аргумента x= -2(0.1)2.

Задание 5.

Расчет заработной платы за 1 квартал				за январь
Ф.И.О.	оклад	Премия 20%	Итого начислено	Подох. Налог 13%	Итого к выплате
Баранова	15000
Васильев	8000
Петрова	11000
Петухова	9800
Савин	12500
Итого:

Задание 6.

Таблица подсчета котировок курса доллара
Дата	Курс покупки	Курс продажи	Доход
01.12.2003	31,2	31,4
02.12.2003	31,25	31,45
03.12.2003	31,45	31,5
04.03.2003	31,45	31,56
05.12.2003	31,2	31,3
средний курс

Задание 7.

			цена из.		57р.
Показатель		Квартал 1	Квартал 2		Квартал 3		Квартал 4
Кол-во выпущенных изделий, шт.		1750	2150		2415		1800
Себестоимость одного изделия, р.		49,5р.	47,3		48,6		48,2
Выпуск продукции, руб.
Себестоимость выпускаемой продукции, руб.
Прибыль от реализации продукции, руб.
Рентабельность продукции, руб.
	Расчет с абсолютной адресацией данных
	1. Выпуск продукции=Кол-во изд. Х Цена изд. (=С4*$E$4)
	2. Себестоимость выпускаемой прод. = Кол-во выпущ.изд. Х Себестоим. изд.									(=С3*С4)
	3. Прибыль = Выпуск продукции - Себестоимость (=С6-С7)
	4. Рентабельность = Прибыль/себестоимость (=С8/С7)
	Задание 8.
	Расчет квартальной и годовой прибыли
		1 квартал		2 квартал		3 квартал		4 квартал		За год
	Кол-во проданных изделий	125		412		647		583		1767
	Доход
	Себестоимость
	Расходы	8000000		6000000		7300000		5800000		27100000
	Прибыль
	Таблица констант:
	Розничная цена	50000
	Процент себестоимости	40%
	Доход=Розничная цена х Кол-во проданных изделий
	Себестоимость=Розничная цена х процент себестоимости
	Прибыль=Доход - Себестоимость - расходы

Задание 9.

Премия за экономию ГСМ
Ф.И.О.	План	Факт	Ставка	Премия 25% от ставки, если план>факт
Сергеев	800	752	2000
Петров	800	852	2000
Сидоров	900	946	2000
Кремнев	400	345	1000
Андреев	250	251	1000
Всего
Премия=Ставка х 0,25% при условии, что
План>Факта
Вставка/функция; Логические/Если
В3>C4=истина
если истина Д3*0,25=500
если ложь 0=0

Задание 10.

Наименование расходов и приходов	сумма
Кол-во экземпляров	20000
Доход
Себестоимость реализованной продукции
Валовая прибыль
% накладных расходов	30
Затраты на зарплату	5000
Затраты на рекламу	1000
Накладные расходы
Валовые издержки
Прибыль от продажи
Цена продукции	6
Себестоимость продкукции	2

Задание 11.

Расчет удельного веса документально проверенных организаций
№ п\п	Вид организаций	Общее число плательщиков на 01.01.2003	Число документально проверенных организаций на 2002 г.	Удельный вес (в %)
1	Организации
	Всего
	В том числе
	- государственные	426	36
	- муниципальные	3686	1253
	- индивидуально-частных	10245	812
	- с иностранными инвестициями	73	5
	- других организаций	1245	246
2	Банки	23	6
3	Страховые организации	17	3

Задание 12.

	ноябрь	декабрь	январь	февраль	март
Напитки	5	10	10	4	8
Молоко	15	18	13	20	14
Фрукты	3	12	25	15	23
Промтовары	30	24	35	33	29
Хлеб	40	34	45	47	34
Рыба	12	35	23	39	46
Кондит. Изд.	17	33	44	22	44
Мясо/птица	55	34	60	43	70

Задание 13.

	Июнь	Июль	Август
Угорь	100	54	33
Форель	59	67	60
Окунь	190	170	200

Задание 14.

	январь	февраль	март	апрель	май	июнь	июль
Гастроном 1	12	15	9	16	11	17	4
Гастроном 46	22	15	14	25	19	13	30
Магазин "Диета"	33	44	20	29	39	41	34

Задание 15.

Доход фирмы
Год	Доход	Линия тренда
1990	454	453,9637
1991	131	130,9232
1992	213	212,8817
1993	462	461,8432
1994	584	583,8125

Задание 16.

	январь	февраль	март	апрель	май	июнь
Предложения	25	28	34	12	56	67
Налоги	10	13	32	47	55	87
Аренда	9	56	78	43	56	90
Зарплата	34	55	77	90	66	88
Имущество	46	68	68	63	87	95

Тестовые задания: Электронные таблицы

1. Какой результат даст формула в ячейке С1?

1. 1

2. ИСТИНА

3. ЛОЖЬ

4. 0

2. В электронной таблице MS Excel знак “$” перед номером строки в обозначении ячейки указывает на …

1. денежный формат числа

2. абсолютную адресацию строки

3. начало записи формулы

4. начало выделения блока ячеек

3. В ячейке таблицы записано число 1.1Е+11. Эта запись соответствует числу:

1. 0,00000000011

2. 1,10000000001

3. 1,00000000011

4. 110000000000

4. Выделен диапазон ячеек А13:D31. Диапазон содержит:

1. 2 ячейки.

2. 72 ячеек.

3. 54 ячейки.

4. 76 ячеек

5. После копирования ячейки А4 в ячейку В4 значение в ячейке В4 будет равно

1. 48

2. 47

3. 55

4. 36

6. После изменения данных в каких-либо ячейках происходит пересчет:

1. только формул со ссылками на эти ячейки на текущем листе.

2. всех формул, имеющих ссылки на эти ячейки на любой стадии цепочки ссылок

3. только формул, имеющих ссылки на эти ячейки в выделенном блоке.

4. только формул, имеющих непосредственную ссылку эти ячейки.

7. Если ячейка содержит «#ЗНАЧ!», то:

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

2. ячейка содержит числовое значение

3. ячейка содержит любое значение

4. ячейка содержит значение даты или времени

8. При объединении ячеек A1,B1,C1, результирующая ячейка будет иметь значение

1. 7

2. 13

3. 23

4. 43

9. Когда несколько первых символов, вводимых в ячейку, совпадают с символами записи, ранее введенной в этом столбце, то недостающая часть набора будет произведена автоматически. В Microsoft Excel автоматический ввод производится:

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

2. только для тех записей, которые содержат текст

3. для любых типов записей

4. только для записей состоящих из дат

10. Табличный процессор – это…

1. программный продукт для ввода данных и создания электронных форм

2. набор команд для редактирования содержимого таблиц

3. специализированная программа, позволяющая создавать электронные таблицы и автоматизировать вычисления в них

4. процессор (микросхема), снабжённый встроенными командами для работы с массивами данных

11. «Легендой» диаграммы MS Excel является …

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

2. порядок построения диаграммы (список действий)

3. руководство для построения диаграмм

4. таблица для построения диаграммы

12. Диапазон ячеек в MS Excel задается …

1. указанием адресов двух диагональных ячеек блока, разделенных символом «:» или «.»

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

3. указанием адресов первой и последней ячейки строки диапазона

4. нажатием на кнопку, соответствующую блоку ячеек и указанием размеров блока

13. Представлен фрагмент электронной таблицы в режиме отображения формул.

Значение в ячейке В3 будет равно

1. 2

2. 1,333333

3. 1,6

4. 1,5

14. Представлен фрагмент электронной таблицы в режиме отображения формул.

Значение в ячейке В3 будет равно

1. 1,25

2. 1,5

3. 1

4. 1,4

15. Представлен фрагмент электронной таблицы в режиме отображения формул.

Значение в ячейке В3 будет равно

1. 1,25

2. 1,5

3. 1

4. 1,75

16. Представлен фрагмент электронной таблицы в режиме отображения формул.

Значение в ячейке В3 будет равно

1. 4

2. 3

3. 5

4. 2