ИНФОРМАТИКА Индивидуальные контрольные задания в среде Excel 2000

Министерство образования и науки рф

Санкт-Петербургский государственный электротехнический

университет ”ЛЭТИ”

ИНФОРМАТИКА

Методические указания

к выполнению индивидуальных контрольных заданий в среде табличного процессора Excel 2000

для студентов всех специальностей

факультета экономики и менеджмента

Санкт-Петербург

Издательство СПбГЭТУ “ЛЭТИ”

2005

Утверждены редакционно-издательским советом СПбГЭТУ

“ЛЭТИ”

СОСТАВИТЕЛИ:

кандидат технических наук, доцент И..Б. Никифоров

ассистент С.А.Кожевников

Рецензенты:

кандидат технических наук, доцент А.В. Татаринов

кандидат технических наук, доцент К.П.Голоскоков (ИНЖЭКОН)

Подготовлено

на кафедре менеджмента и систем качества. Печатается в авторской редакции

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

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

Пример выполнения задания (файл METEXL.XLS)

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

Задержка от 6 до 10 дней: 50 руб. за 1 день;

Задержка от 11 до 20 дней: 100 руб. за 1 день;

Задержка свыше 20 дней: 300 руб. за 1 день.

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

На рис.1 представлена электронная таблица для расчета, в которой отображены вычисленные значения для 4 контейнеров. Исходные данные вводятся в столбцы A, B, D. Значения в клетках столбцов C, E, F вычисляются по формулам. Формулы вычислений, введенные в электронную таблицу Excel, приведены на рис.2- 4.

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

1. Отметить курсором область таблицы с данными и заголовками, которые должны быть отражены на диаграмме (область А33..В37)

2. Вызвать “мастера построения диаграмм” - пиктограмма верхнего меню

3. Отметить курсором место построения диаграммы - С33

4. Задать параметры диаграммы путем диалога с “мастером”

А	B	C	D	E	F
		Расчет суммы штрафа				22
		на				23
		19/09/1997				24
№ Контракта	Дата Поступления	Плановая дата возврата без штрафа	Фактическая дата возврата	Количество дней задержки	Сумма штрафа	25 26
1	3/09/97	08/09/97		16	1000	27
22	11/09/97	16/09/97		8	150	28
32	24/08/97	29/08/97		26	3300	29
21	11/09/97	16/09/97	17/09/97	6	50	30
			ИТОГО		4500

Рис. 1

C

Расчет суммы штрафа

На

=СЕГОДНЯ()

Плановая дата

Возврата без штрафа

=ЕСЛИ(ЕПУСТО(B27);" ";B27+5)

=ЕСЛИ(ЕПУСТО(B28);" ";B28+5)

=ЕСЛИ(ЕПУСТО(B29);" ";B29+5)

=ЕСЛИ(ЕПУСТО(B30);" ";B30+5)

Рис. 2

E

Количество

дней задержки

=ЕСЛИ(И(ЕПУСТО(D27);НЕ(ЕПУСТО(B27)));СЕГОДНЯ()-B27;ЕСЛИ(И(ЕПУСТО(D27);ЕПУСТО(B27));" ";D27-B27))

=ЕСЛИ(И(ЕПУСТО(D28);НЕ(ЕПУСТО(B28)));СЕГОДНЯ()-B28;ЕСЛИ(И(ЕПУСТО(D28);ЕПУСТО(B28));" ";D28-B28))

=ЕСЛИ(И(ЕПУСТО(D29);НЕ(ЕПУСТО(B29)));СЕГОДНЯ()-B29;ЕСЛИ(И(ЕПУСТО(D29);ЕПУСТО(B29));" ";D29-B29))

=ЕСЛИ(И(ЕПУСТО(D30);НЕ(ЕПУСТО(B30)));СЕГОДНЯ()-B30;ЕСЛИ(И(ЕПУСТО(D30);ЕПУСТО(B30));" ";D30-B30))

Рис. 3

F
	22
	23
	24
Сумма	25
Штрафа	26
=ЕСЛИ(ЕПУСТО(E27);" ";ЕСЛИ(E27<6;" ";ЕСЛИ(И(E27>5;E27<11);(E27-5)*50;ЕСЛИ(И(E27>10;E27<21);(E27-11)*100+500;(E27-20)*300+1500))))	27
=ЕСЛИ(ЕПУСТО(E28);" ";ЕСЛИ(E28<6;" ";ЕСЛИ(И(E28>5;E28<11);(E28-5)*50;ЕСЛИ(И(E28>10;E28<21);(E28-11)*100+500;(E28-20)*300+1500))))	28
=ЕСЛИ(ЕПУСТО(E29);" ";ЕСЛИ(E29<6;" ";ЕСЛИ(И(E29>5;E29<11);(E29-5)*50;ЕСЛИ(И(E29>10;E29<21);(E29-11)*100+500;(E29-20)*300+1500))))	29
=ЕСЛИ(ЕПУСТО(E30);" ";ЕСЛИ(E30<6;" ";ЕСЛИ(И(E30>5;E30<11);(E30-5)*50;ЕСЛИ(И(E30>10;E30<21);(E30-11)*100+500;(E30-20)*300+1500))))	30
=СУММ(F27:F30)

Рис. 4

а) подтвердить диапазон,

б) выбрать тип диаграммы,

в) задать метки и названия осей, текст названия и легенды, сетку, цвета и фон.

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

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

Вариант 1

Создать таблицу следующего вида:

Ведомость учета отработанного времени

Фамилия, И.О.	Отработанное время, час.

Ввести 15 фамилий рабочих с данными по отработанному времени. С помощью двух справочных таблиц должна автоматически заполняться ве­домость начисления зарплаты с итоговыми данными. Привести круговую диаграм­му распределения сумм зарплаты по цехам, автоматически корректируемую при изменении данных в исходной таблице. Определить разряд с максималь­ной суммарной зарплатой.

Справочник распределения рабочих по цехам и разрядам

Фамилия, И. О.	Разряд	Цех

Справочник тарифов

Разряд	Тариф, руб./час.

Ведомость начисления зарплаты

Фамилия, И.О.	Начислено, руб.

Вариант 2

Создать таблицу следующего вида:

Ведомость учета отработанного времени

Фамилия, И.О.	Отработанное время, дней

Ввести 15 фамилий служащих с данными по отработанному времени. С помощью справочных таблиц должна автоматически заполняться ведомость начисления зарплаты с итоговыми данными. Зарплата должна начисляться пропорционально количеству дней, отработанных в текущем месяце. Привести круговую диаграмму распределения сумм зарплаты по подразделениям, автоматически корректируемую при изменении данных в исходной таблице. Определить должность с максимальной суммарной зарплатой.

Справочник распределения служащих

подолжностям и подразделениям

Фамилия, И. О.	Должность	Подразделение

Справочник должностных окладов

Должность	Оклад, руб./мес.

Ведомость начисления зарплаты

Фамилия, И.О.	Начислено, руб.

Вариант 3

Создать таблицу следующего вида:

Ведомость учета отработанного времени

Фамилия, И.О.	Отработанное время, час.

Ввести 15 фамилий рабочих с данными по отработанному времени. С помощью справочных таблиц должна автоматически заполняться ведомость начисления зарплаты с итоговыми данными. Привести круговую диаграмму распределения сумм зарплаты по разрядам, автоматически корректируемую при изменении данных в исходной таблице. Определить номер цеха с максимальной суммой зарплаты.

Справочник распределения рабочих по цехам и разрядам

Фамилия, И. О.	Разряд	Цех

Справочник тарифов

Разряд	Тариф, руб./час.

Ведомость начисления зарплаты

Фамилия, И.О.	Начислено, руб.

Вариант 4

Создать таблицу следующего вида:

Ведомость учета отработанного времени

Фамилия, И.О.	Отработанное время, дней

Ввести 15 фамилий служащих с данными по отработанному времени. С помощью справочных таблиц должна автоматически заполняться ведомость начисления зарплаты с итоговыми данными. Зарплата должна начисляться пропорционально количеству дней, отработанных в текущем месяце. Привести круговую диаграмму распределения сумм зарплаты по должностям, автоматически корректируемую при изменении данных в исходной таблице. Определить подразделение с максимальной суммарной зарплатой.

Справочник распределения служащих

по должностям и подразделениям

Фамилия, И. О.	Должность	Подразделение

Справочник должностных окладов

Должность	Оклад, руб./мес.

Ведомость начисления зарплаты

Фамилия, И.О.	Начислено, руб.

Вариант 5

Создать таблицу следующего вида:

Ведомость учета отработанного времени

Фамилия, И.О.	Отработанное время, час.

Ввести 15 фамилий рабочих с данными по отработанному времени. С помощью справочных таблиц должна автоматически заполняться ведомость начисления зарплаты с итоговыми данными. Привести круговую диаграмму распределения сумм зарплаты по разрядам, автоматически корректируемую при изменении данных в исходной таблице. Фамилии в ведомости начисления зарплаты должны быть упорядочены по алфавиту.

Справочник распределения рабочих по цехам и разрядам

Фамилия, И. О.	Разряд	Цех