ема: Создание таблиц с использованием нескольких листов и работа с Мастером функций.

Отработано дней

Заработано

Квалифи-кация

Надбавка (в руб.)

Выдано на руки

Иванов И.И.

Петров В.В.

Скворцов В.П.

абораторная работа № 1

Тема: Создание и оформление таблиц

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

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

1. Введите заголовки колонок таблицы, начиная с ячейки А1. Шапка заголовков будет занимать две строки (см. рис.1.1). При необходимости используйте приемы объединения ячеек.

2. Оформите шапку заголовков таблицы по образцу, настроив ширину столбцов и высоту строк и используя приемы форматирования ячеек (перенос по словам, изменение ориентации текста). Выделите текст заголовков полужирным начертанием.

Дата	Наименование изделия	Произведено	Реализовано	Остаток (шт.)	Сумма выручки (руб.)
Цена (руб.)	Количество (шт.)	Цена (руб.)	Количество (шт.)
06.01.2005	Глушитель
06.01.2005	Колесо
06.01.2005	Корпус
10.01.2005	Диск
10.01.2005	Маховик
19.01.2005	Фланец
19.01.2005	Опора оси
22.01.2005	Болт
22.01.2005	Валик
	Итого:

Рис. 1.1.

3. Заполните столбец Дата, применяя для повторяющихся данных операцию копирования; установите формат даты по образцу на рис.1.1.

4. Заполните ячейки таблицы исходными данными (см. рис.1.1).

5. Вставьте левее столбца Дата новую колонку и озаглавьте её № п/п.

6. Используя операцию автоматического заполнения блока ячеек таблицы числами, пронумеруйте строки (от 1 до 9) в колонке № п/п.

7. Перед строкой с номером 8 в колонке № п/п, вставьте пустую строку и заполните её данными 19.01.2005 Труба 55, 60, 65, 60. Соответственно исправьте нумерацию строк.

8. Используя операцию «Заменить», исправьте дату 19.01.2005 на 18.01.2005.

9. Вставьте формулы для расчета Остатка и Суммы выручки.

10. Перед столбцом Остаток вставьте колонку % реализованного товара и выполните расчёт по формуле:

(% реализованного товара)= .

11. Установите процентный формат для чисел в колонке % реализованного товара.

12. Округлите числа в колонке % реализованного товара до целых.

13. Обрамите таблицу: внутри тонкой линией, а вокруг двойной.

14. Установите денежный формат для чисел колонок Цена и Сумма выручки.

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

16. Оформите строчку Итого,рассчитав итоговые суммы по количеству (шт.), остатку (шт.) и сумме выручки (руб.).

17. Сравните созданную Вами таблицу с приведённой на рис.1.2.

18. Выделите ячейки A1:J14 и скопируйте их на Лист2; выделите Лист1 и скопируйте его на Лист3. Объясните различия в результатах копирования.

19. Оформите таблицу на Листе2, используя стандартные варианты оформления таблиц, предлагаемых в автоформате.

20. Скопируйте созданную таблицу на другой лист и отобразите её в режиме формул. Назовите лист Формулы.

21. Скопируйте формат таблицы (без данных), созданной на Листе 1, на новый лист (используйте команду Правка Þ Специальная вставка). Назовите лист Формат.

22. Сохраните результаты работы.

Производственная деятельность предприятия

№ п/п	Дата	Наименование изделия	Произведено	Реализовано	% реализованного товара	Остаток (шт.)	Сумма выручки
Цена (руб.)	Количество (шт.)	Цена (руб.)	Количество (шт.)
	06.01.2005	Глушитель	240,00р.		250,00р.		94%	112500р.
	06.01.2005	Колесо	370,00р.		380,00р.		100%	57000р.
	06.01.2005	Корпус	95,00р.		100,00р.		25%	2000р.
	10.01.2005	Диск	65,00р.		70,00р.		92%	3850р.
	10.01.2005	Маховик	90,00р.		98,00р.		86%	29400р.
	19.01.2005	Фланец	22,00р.		25,00р.		100%	10000р.
	19.01.2005	Опора оси	185,00р.		200,00р.		88%	76000р.
	19.01.2005	Труба	55,00р.		65,00р.		100%	3900р.
	22.01.2005	Болт	17,00р.		18,00р.		95%	3240р.
	22.01.2005	Валик	28,00р.		30,00р.		93%	7800р.
		Итого:						3056 90р.

Рис. 1.2.

Задания для самостоятельной работы

ариант 1

1. Создайте таблицу Реализация материальных ценностей со склада со следующими заголовками столбцов: Дата реализации, Наименование товара, Цена за единицу (руб.), Склад №, Реализовано (шт.), Сумма реализации. Заполнить таблицу данными по своему усмотрению (8 строк).

2. Вычислите сумму реализации каждого товара, итоговую сумму и общее количество реализованного товара в строке Итого.

3. Отформатируйте ширину столбцов таблицы, оформите шапку таблицы в несколько строк полужирным начертанием, выровняйте числовые данные по центру.

4. Оформите название таблицы и расположите его по центру таблицы.

5. Обрамите таблицу: вокруг одним типом линий, а внутри - другим.

6. Данные столбцов Цена за единицу и Суммапредставьте в денежном формате.

7. Переместите столбец Склад № правее столбца Наименование товара. Пустой столбец удалите.

8. Разверните названия двух последних столбцов на 90⁰.

9. Переименуйте лист с таблицей, назвав его Склад.

ариант 2

1. Создайте таблицу Расчёт заработной платы в марте 2005г. со следующими названиями столбцов: шифр организации (223467, 223461), Фамилия работника, часовая тарифная ставка (руб.), фактически отработанное время (час.), заработная плата (руб.). Заполните таблицу данными по своему усмотрению (9 строк).

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

3. Отформатируйте ширину столбцов таблицы, оформите шапку таблицы в несколько строк, выровняйте числовые данные по центру.

4. Запишите название таблицы и расположите его по центру таблицы.

5. Обрамите таблицу: шапку двойной линией, а остальную таблицу - тонкой.

6. Данные полей Часовая тарифная ставкаи Заработная платапредставьте в денежном формате.

7. Левее столбца Шифр организации добавьте столбец № п/п и пронумеруйте строки таблицы.

8. Замените шифр организации 223467 на 223468,воспользовавшись командой.

9. Переименуйте лист с таблицей, назвав его Зарплата.

ариант 3

оздайте таблицу Расчёт прибыли организации со следующими названиями столбцов: Дата реализации, Наименование изделия, Количество изделий (шт.), Оптовая цена (руб.), Себестоимость (руб.), Прибыль. Заполните таблицу данными по своему усмотрению (8 строк).

2. Вычислите прибыль от реализации каждого вида изделий и общую прибыль организации в строке Итого.

3. Отформатируйте ширину столбцов, оформите шапку таблицы в две строки, выровняйте числовые данные по центру.

4. Обрамите таблицу: внутри линией одного типа, а вокруг – другой.

5. Запишите название таблицы и расположите его по центру таблицы.

6. Данные полей Оптовая цена, Себестоимость и Прибыль оформите в денежном формате.

7. Перед строкой с изделием Шайба вставьте пустую строку и заполните её своими данными.

8. Измените ориентацию названия двух последних столбцов.

9. Переименуйте лист с таблицей, назвав его Прибыль.

ариант 4

1. Создайте таблицу Учёт стоимости телефонных разговоров со следующими названиями столбцов: Дата разговора, Номер телефона, Город, Длительность разговора (мин.), Стоимость одной минуты разговора (руб.), Полная стоимость разговора. Заполните таблицу данными по своему усмотрению (10 строк).

2. Вычислите полную стоимость разговора по каждому абоненту и по всем абонентам в строке Итого.

4. Обрамите таблицу: внутри пунктирной линией, а вокруг - жирной.

5. Дайте название таблице и расположите его по центру таблицы.

6. Данные столбцов Стоимость одной минуты разговора и Полная стоимость разговора представьте в денежном формате.

7. Поменяйте местами столбцы Длительность и Стоимость.

8. Переименуйте лист с таблицей, назвав его Тел. Разговоры.

ариант 5

1. Создайте электронную таблицу Накопительная ведомость по приходу продукции на склад со следующими столбцами: Дата, Номер документа, Наименование товара, Количество (шт.), Цена (руб.), Стоимость (руб.). Заполните таблицу данными по своему усмотрению (8 строк).

2. Вычислите стоимость каждого товара на складе, общую стоимость и количество товара в строке Итого.

3. Отформатируйте ширину столбцов таблицы, оформите шапку таблицы в несколько строк, выровняйте числовые данные по центру, измените ориентацию названия двух последних столбцов на 90⁰.

4. Обрамите шапку таблицы двойной линией, а остальную таблицу - одинарной.

5. Данные столбцов Цена и Сумма представьте в денежном формате.

6. Запишите название таблицы и расположите его по центру таблицы.

7. Левее столбца Число месяца добавьте столбец № п/п и пронумеруйте строки таблицы.

8. Переименуйте лист с таблицей, назвав его Приход.

ариант 6

1. Создайте электронную таблицу Расчёт суммы к выдаче для сотрудников со следующими названиями столбцов: № п/п, Фамилия сотрудника, Табельный номер, Заработная плата, Налог, Сумма к выдаче. Заполните таблицу данными по своему усмотрению (10 строк).

2. Вычислите для каждого сотрудника налог – 13% от зарплаты и сумму к выдаче. Рассчитайте общую сумму к выдаче и суммарный налог в строке Итого.

4. Запишите название таблицы и расположите его по центру таблицы.

5. Обрамите таблицу внутри и вокруг линиями разного типа.

6. Данные полей Зарплата, Налог и Сумма к выдаче представьте в денежном формате и округлите до одного знака после запятой.

7. В середине таблицы вставьте пустую строку и заполните её данными о сотруднике.

8. Переименуйте лист с таблицей, назвав его Сумма к выдаче.

ариант 7

1. Создайте электронную таблицу Учёт товаров в магазине со следующими названиями столбцов: Отдел, Наименование товара, Приход (цена, количество), Расход (цена, количество), Остаток (количество, сумма). Заполните таблицу данными по своему усмотрению (9 строк).

2. Вычислите количество и сумму остатка по каждому товару и по всем товарам в строке Итого.

4. Запишите название таблицы и расположите его по центру таблицы.

5. Обрамите шапку таблицы двойной линией, а остальную таблицу –пунктирной линией.

6. Данные полей Цена и Сумма представьте в денежном формате.

7. Разверните название столбцов Цена и Количество на 90⁰.

8. Левее столбца Отдел вставьте столбец № п/п. С помощью автозаполнения пронумеруйте строки.

9. Переименуйте лист с таблицей, назвав его Магазин.

ариант 8

1. Создайте электронную таблицу Расчёт продолжительности отпуска сотрудников со следующими названиями столбцов: Фамилия сотрудника, Название отдела, Дата начала отпуска, Дата окончания отпуска, Продолжительность отпуска. Заполните таблицу данными по своему усмотрению (10 строк).

2. Вычислите продолжительность отпуска для каждого сотрудника и . суммарную продолжительность отпусков в строке Итого.

4. Запишите название таблицы и расположите его по центру таблицы.

5. Оформите таблицу, используя автоформат.

6. Переместите поле Отдел перед полем Фамилия.

7. В середине таблицы вставьте пустую строку и заполните её данными о сотруднике.

8. Переименуйте лист с таблицей, назвав его Сумма к выдаче.

ариант 9

1. Создайте электронную таблицу Расчёт прибыли со следующими названиями столбцов: Квартал, Валовая прибыль, Затраты на зарплату, Затраты на рекламу, Накладные расходы, Валовые издержки, Прибыль от продукции. Заполните таблицу данными по своему усмотрению (8 строк).

2. Вычислите валовые издержки (сумма всех затрат) и прибыль от продукции. Рассчитайте суммарную прибыль в строке Итого.

4. Запишите название таблицы и расположите по центру таблицы.

5. Обрамите таблицу внутри и вокруг линиями разного типа.

6. Содержимое полей с данными о прибыли представьте в денежном формате.

7. Переименуйте лист с таблицей, назвав его Прибыль.

ариант 10

1. Создайте электронную таблицу Расчёт премии для сотрудников со следующими названиями столбцов: № п/п, Фамилия сотрудника, Оплата в час, Количество отработанных часов, Зарплата, Премия. Заполните таблицу данными по своему усмотрению (10 строк).

2. Вычислите зарплату и премию каждого сотрудника – 40% от оклада. Рассчитайте общую сумму зарплаты и суммарную премию в строке Итого.

4. Запишите название таблицы и расположите его по центру таблицы.

5. Данные полей Зарплата, Премия представьте в денежном формате и округлите до целого.

6. Оформите таблицу, используя автоформат.

7. Измените ориентацию названия столбцов Премия и Зарплата.

8. Переименуйте лист с таблицей, назвав его Премия.

абораторная работа №2

ема: Создание таблиц с использованием нескольких листов и работа с Мастером функций.

Цель работы:Научиться создавать таблицы на нескольких листах, проводить сложные вычисления с использованием встроенных функций, абсолютной и относительной адресации. Освоить функции СУММ, СРЗНАЧ, ЕСЛИ, ВПР, СУММЕСЛИ, СЧЕТЕСЛИ, МАКС, МИН.

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

1. В новой рабочей книге организуйте учет реализации товаров со склада, состоящий из трех таблиц: прайс-лист, продажи и скидки. Начните работу с создания вспомогательных таблиц: на Листе1 и Листе2 расположите таблицы, приведенные на рис.2.1, 2.2, соответственно, и заполните их данными. Переименуйте листы, дав им названия: Прайс-лист и Скидки, в соответствии с хранящейся на них информацией.

2. На Листе 3 создайте таблицу реализации товаров со склада: введите название таблицы, текущую дату (используйте функцию СЕГОДНЯ())
и курс, сформируйте шапку заголовков (см. рис.2.3). Заполните данными первые три столбца таблицы: для заполнения первого столбца используйте автопродолжение; наименования товаров во втором столбце должны соответствовать списку прайс-листа. Переименуйте Лист3 в Продажи.

3. Заполните данными столбец Цена (в у.е.). Для этого введите в ячейку D5 вычисляемое выражение, позволяющее автоматически выбирать нужную цену из созданной ранее таблицы прайс-листа в зависимости от наименования товара, а затем продолжите формулу на все ячейки столбца. В данном случае для вычислений можно использовать встроенную функцию ВПР – «вертикального просмотра», которая будет сравнивать значение ячейки В5 со значениями первого столбца «диапазона просмотра» Прайс-лист!$A$3:$B$12 и в случае совпадения вернет соответствующее значение из второго столбца указанного диапазона:

(D5)= ВПР(В5; Прайс-лист!$A$3:$B$12; 2; 0).

Замечание: последний параметр функции имеет значение 0 (Ложь), что означает поиск точного совпадения искомого значения (В5) в первом столбце диапазона просмотра.

Рис. 2.3.

4. Заполните пятый столбец таблицы в соответствии с формулой:
Цена (в руб.)=Цена (в у.е.)*Курс.

Замечание: так как для всех товаров курс валют единый (Н3), при написании формулы используйте абсолютную адресацию для ссылки на соответствующую ячейку.

5. Введите формулу расчета стоимости партии для первого товара
(Стоим.партии =Кол-во*Цена в руб.) и продолжите ее на все ячейки шестого столбца.

6. Рассчитайте величину скидки для каждой партии реализованного товара. Скидка определяется в соответствии с таблицей на листе Скидки в зависимости от стоимости партии: от 0 руб. до 9 999 руб. - 0%

от 10 000руб. до 19 999руб. - 5%

свыше 20 000 руб. - 10%

Для автоматизации вычисления величины скидки можно воспользовать-ся функцией ЕСЛИ: (G5) = ЕСЛИ (F5<Скидки!$A$4; Скидки!$B$3; ЕСЛИ(F5<Скидки!$A$5; Скидки!$В$4; Скидки!$В$5)).

Замечание: таблица на листе Скидки сформирована таким образом, что для определения величины скидки также может быть использована функция ВПР. Напишите эту функцию самостоятельно, по аналогии с п.3., учитывая, что в данном случае значение последнего параметра равно 1 (Истина) - поиск ведется приближенно.

7. Введите формулу для расчета стоимости партии со скидкой для первого товара и продолжите ее на все ячейки столбца
(Стоим.партии со скидкой = Стоим.партии-Стоим.партии*Скидка)

8. Под таблицей вычислите общую сумму продаж (функция СУММ), среднюю стоимость партии (функция СРЗНАЧ), минимальное и максимальное количество товаров в партии (функции МИН и МАКС, соответственно).

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

10. Сохраните рабочую книгу под именем Учет товара.xls и покажите свою работу преподавателю.

Задания для самостоятельной работы

ариант 1

Средствами электронной таблицы Excel рассчитайте заработную плату работников в зависимости от их квалификации и числа отработанных дней.

1. На Листе1 создайте таблицу Табель рабочих дней (см.рис.2.4), заполнив ее для 10 сотрудников (рабочий день помечается символом «+»). Переименуйте Лист1 в Трудодни.

№	Ф.И.О.	март
1 мар	2 мар	. . .	31 мар
	Иванов И.И.	+	+	+
	Петров В.В.		+	+
…
…
	Скворцов В.П.	+		+

Рис. 2.4.

2. На Листе2 создайте таблицу Начисление надбавок, со следующими заголовками полей: Разряд, Надбавка (в %). Надбавки начисляются по следующему закону: Надбавка за квалификацию =

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

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.5. Заполните таблицу данными, следуя указаниям:

а) для заполнения первых двух столбцов скопировать данные из таблицы на листе Трудодни; столбец Квалификация заполните любыми цифрами от 1 до 6;

б) число отработанных дней (третий столбец) должно вычисляться автоматически с учетом данных таблицы на листе Трудодни (используйте функцию СЧЕТЕСЛИ);

в) заполоните столбец Заработано, рассчитав его по формуле:
Заработано = (Отработано дней)*(Оплата 1-го дня);

г) рассчитайте надбавку для каждого работника, используя данные таблицы Надбавки (для автоматизации вычислений воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

д) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением: Выдано на руки =Заработано +Надбавка.

Распределение дохода по труду

Оплата 1-го дня 200р.

Рис. 2.5.

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

5. Сохраните Вашу работу и покажите результат преподавателю.

Вариант 2

Средствами электронной таблицы Excel рассчитайте выплаты работникам, начисляемые в зависимости от стажа и за вычетом подоходного налога.

1. На Листе1 создайте таблицу, определяющую коэффициент надбавки (см. рис.2.6.) в зависимости от стажа:

от 0 лет до 5 лет - 1

от 5 лет до 10 лет - 1,5

от 10 лет и более - 2.

Переименуйте Лист1 в Надбавки.

2. На Листе2 создайте таблицу Подоходный налог, со следующими заголовками полей: Величина оклада, Ставка налога (в %). Налоговая ставка определяется в зависимости от величины оклада следующим образом:
подоходный налог = .

Заполните таблицу данными таким образом, чтобы их было удобно использовать в следующей таблице при расчете заработной планы сотрудников. Переименуйте Лист2 в Подоходный налог.

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.7. Заполните таблицу данными, следуя указаниям:

а) введите данные для 10 сотрудников, заполнив столбцы Ф.И.О. и Стаж; проставьте нумерацию (столбец № п/п), используя автозаполнение;

б) заполоните столбец Оклад, рассчитав его по формуле:
Оклад=(Базовый оклад)*(Коэффициент).

Замечание: коэффициент должен определяться в зависимости от стажа по таблице на листе Надбавки (используйте функцию ЕСЛИ ).

в) рассчитайте сумму подоходного налога для каждого сотрудника, используя данные таблицы Подоходный налог (для автоматизации вычислений воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

г) заполните столбец Получено на руки, вставив формулу в соответствии со следующим выражением:

Получено на руки =Оклад-Подоходный налог.

Расчет заработной платы

Базовый оклад 1500р.

№	Ф.И.О.	Стаж	Оклад	Подоходный налог	Получено на руки
	Иванов И.И.		1500р.	180р.	1320р.
	Сидоров П.И.		3000р.	750р.	2250р.
…
	Петров В.В.		2250р.	450р.	1800р.

Рис. 2.7.

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

5. Сохраните Вашу работу и покажите результат преподавателю.

ариант 3

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

1. На Листе1 создайте таблицу Список сотрудников, состоящую из двух столбцов: Ф.И.О, Квалификация. Заполните таблицу произвольными данными на 10 сотрудников (в поле Квалификация проставьте цифры от 1 до 6). Переименуйте Лист1 в Сотрудники.

2. На Листе2 создайте таблицу Начисление надбавок, со следующими заголовками полей: Квалификация, Надбавка (в %). Надбавки начисляются по следующему закону:

Надбавка за квалификацию =

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.8. Заполните таблицу данными, следуя указаниям:

а) из списка сотрудников выберете 5 любых фамилий и заполните первые три столбца таблицы (для заполнения первого столбца воспользуйтесь автозаполненинием);

б) вставьте формулу для автоматического заполнения столбца Квалификация по данным таблицы на листе Сотрудники (используйте функцию ВПР);

в) столбец Отработано дней заполните произвольными данными (числа от 0 до 30);

г) заполоните столбец Заработано, рассчитав его по формуле:
Заработано =(Отработано дней)*(Оплата за 1день);

д) рассчитайте величину надбавки для каждого сотрудника, используя данные таблицы Надбавки (для автоматизации вычислений воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

е) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением: Выдано на руки =Заработано+Надбавка.

Расчет заработной платы

Оплата за 1день 700р.

№	Ф.И.О.	Квалифи-кация	Отработано дней	Заработано	Надбавка (в руб.)	Выдано на руки
	Иванов И.И.
	Петров В.В.
…
	Мельник Ф.П.

Рис.2.8.

5. Сохраните рабочую книгу и покажите результат Вашей работы и преподавателю.

ариант 4

Средствами электронной таблицы Excel рассчитайте отпускные выплаты сотрудникам. Отпускные складываются из средней зарплаты по 3 предыдущим месяцам и премии, зависящей от стажа сотрудника.

1. На Листе1 создайте таблицу Список сотрудников с полями Ф.И.О. и Стаж. Заполните таблицу данными для 10 сотрудников (стаж от 5 до 30 лет). Переименуйте Лист1 в Сотрудники.

2. На Листе2 создайте таблицу Коэффициент начисления премий, со следующими заголовками столбцов: Стаж, Коэффициент. Коэффициент определяется в зависимости от стажа работы следующим соотношением:
Коэффицинт = .

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

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.9. Заполните таблицу данными, следуя указаниям:

а) выберите 5 сотрудников из таблицы на листе Сотрудники и внесите их фамилии в поле Ф.И.О.; для заполнения первого столбца используйте автозаполнение;

б) столбцы Апрель, Май, Июнь заполните произвольными числами от 1500 до 3000 (можно использовать функцию СЛЧИС(), генерирующую псевдослучайные числа от 0 до 1);

в) вычислите значения в столбце Среднее по трем месяцам, используя функцию СРЗНАЧ;

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

д) рассчитайте величину премии для каждого работника, используя данные таблицы Премиальные коэффициенты:

Премия = (Базовая премия) * (Коэффициент).

Замечание: для определения коэффициента воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций.

е) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением:

Выдано на руки = (Среднее по трем месяцам) +(Премия).

Расчет отпускных выплат

Базовая премия 1500р.

№	Ф.И.О.	Оклад	Среднее по 3мес.	Коэффициент	Премия	Выдано на руки
Апрель	Май	Июнь
	Иванов И.И.	2000р.	2200р.	1850р.
	Петров В.В.	3500р.	3000р.	3200р.
…
	Скворцов В.П.	2500р.	2200р.	2700р.

Рис.2.9.

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

5. Сохраните Вашу работу и покажите результат преподавателю.

ариант5

Создайте таблицу на 3-х листах для начисления стипендии студентам в зависимости от среднего балла и формы обучения.

1. На Листе1 создайте таблицу Список студентов с полями Ф.И.О. и Форма обучения. Заполните таблицу данными для 10 студентов, проставляя во втором столбце буквы «Б» (бюджет) или «Д» (договор). Переименуйте Лист1 в Студенты.

2. На Листе2 создайте таблицу Коэффициенты начисления стипендии со следующими столбцами: Средний балл, Коэффициент. Стипендиальный коэффициент определяется в зависимости от среднего балла (СБ) по следующему алгоритму: Коэффицинт = .

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

3. На Листе3 создайте таблицу Начисление стипендии, аналогичную таблице на рис.2.10. Заполните ее данными, следуя указаниям:

а) выберите 5 студентов из таблицы на листе Студенты и внесите их фамилии в поле Ф.И.О.; для заполнения первого столбца используйте автозаполнение;

б) столбцы Математика, Физика, Информатика, заполните произвольными числами от 0 до 5 (можно использовать функцию СЛЧИС, генерирующую псевдослучайные числа от 0 до 1);

в) вычислите Средний балл для каждого студента, используя функцию СРЗНАЧ;

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

д) заполните столбец Коэффициент, который должен определяться по таблице на листе Коэффициенты для каждого студента исходя из его среднего балла (воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

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

4. Вставьте после таблицы расчет следующих величин: общего стипендиального фонда, максимальный и минимальный средний балл, число студентов не получивших стипендию.

5. Сохраните Вашу работу и покажите результат преподавателю.

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

Базовая стипендия 640р.

№ п/п	Ф.И.О.	Оценки по предметам	Средний балл	Форма обучения	Коэффициент	Стипендия
Математика	Физика	Информатика
	Арсеньев Е.В.	3,5	4,2	3,8	3,8	Б	1,5	960р.
	Белых О.М.	5,0	4,8	3,9	4,6	Б		1280р.
…	…
	Паратов А.Ю.	3,2	3,0	3,5	3,2	Д		0р.

Рис. 2.10.

ариант 6

Средствами электронной таблицы Excel рассчитайте выплаты работникам нескольких филиалов в зависимости от их среднегодовой прибыли.

1. На Листе1 создайте таблицу Работа фирм-филиалов и заполните ее как показано на рис.2.11. Переименуйте Лист1 в Фирмы.

Прибыль по кварталам
Название фирмы	1 кв.	2 кв.	3 кв.	4 кв.
АЛИСА
ФЛОРА
ЗЕВС
АФРОДИТА
МАРС

Рис.2.11.

2. На Листе2 создайте таблицу Начисление премий, со следующими заголовками полей: Средняя прибыль, Премия (в %). Премия определяется следующим образом:

Премия =

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

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.12. Первые два столбца заполните вручную, введя названия фирм: АЛИСА, ФЛОРА и МАРС. Остальные ячейки таблицы должны заполняться автоматически, с помощью создаваемых Вами вычисляемых выражений:

а) вставьте формулу в ячейку С5 для определения прибыли фирмы АЛИСА за 1кв. (см. рис. 2.12), используя данные листа Фирмы;

б) продлите формулу ячейки С5 по строкам, а затем по столбцам для определения прибыли всех фирм по всем кварталам (при правильном использовании смешанных и абсолютных ссылок в исходной формуле, их не надо будет изменять при продлении, однако константу, соответствующую номеру столбца в просматриваемой таблице –третий параметр функции ВПР- придется менять вручную: для 2кв -3, для 3кв.-4, для 4кв.-5);

в) рассчитайте среднюю прибыль за год для каждого предприятия (используйте функцию СРЗНАЧ);

г) рассчитайте величину премии для каждой фирмы, используя данные таблицы Премия (для автоматизации вычислений воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

д) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением:

Выдано на руки = (Средний заработок работника) +(Премия).

Рис. 2.12.

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

5. Сохраните Вашу работу и покажите результат преподавателю.

ариант 7

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

1. На Листе1 создайте таблицу Табель рабочих дней (см.рис.2.13.), заполнив ее для 10 сотрудников (рабочий день помечается символом «Р», выходной «В», больничный «Б»). Переименуйте Лист1 в Табель.

№	Ф.И.О.	Январь
1 янв	2 янв	. . .	31 янв
	Иванов И.И.	В	Р	Р
	Петров В.В.	Б	Б	Р
…
…
	Скворцов В.П.	В	Р	Б

Рис.2.13.

2. На Листе2 создайте таблицу Ставки пенсионного фонда, со следующими заголовками полей: Оклад, Ставка налога (в %). Налоговая ставка определяется следующим образом:

Ставка налога = .

Заполните таблицу данными таким образом, чтобы их было удобно использовать в следующей таблице при расчете выплат в пенсионный фонд. Переименуйте Лист2 в Ставки.

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.14. Заполните таблицу данными, следуя указаниям:

а) для заполнения первых двух столбцов скопировать данные из таблицы на листе Табель;

б) число отработанных дней должно вычисляться автоматически с учетом данных таблицы на листе Табель (используйте функцию СЧЕТЕСЛИ);

в) заполоните столбец Оклад, рассчитав его по формуле:
Олад= (Отработано дней)*(Оплата 1-го дня);

г) рассчитайте отчисления в пенсионный фонд для каждого работника, используя данные таблицы Ставки (для автоматизации вычислений воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

д) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением: Выдано на руки =Оклад – Отчислено в пенс.фонд.

Отчисления в пенсионный фонд

Оплата 1-го дня 350р.

№	Ф.И.О.	Отработано дней	Оклад	Отчислено в пенсионный фонд	Выдано на руки
	Иванов И.И.
	Петров В.В.
…
	Скворцов В.П.

Рис.2.14.

5. Сохраните Вашу работу и покажите результат преподавателю.

ариант 8

Средствами электронной таблицы Excel создайте ведомость Продажи товаров с учетом предлагаемых магазином скидок и курса доллара.

1. На Листе1 создайте вспомогательную таблицу Изменение курса доллара, с полями: Дата и Курс. Заполните таблицу данными для марта месяца, проставляя даты с 1.03.06 по 25.03.06. Переименуйте Лист1 в Курс.

2. На Листе2 создайте вспомогательную таблицу Скидки, используя следующие поля: Стоимость товара, Скидка (в %). Скидка определяется в зависимости от Стоимости товара (СТ) по следующему алгоритму:

Скидка = .

Заполните таблицу данными таким образом, чтобы их было удобно использовать в следующей таблице при расчете суммы от реализации товаров. Переименуйте Лист2 в Скидки.

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.15.

Замечание: ячейка Н3 (курс) должна заполняться автоматически в зависимости от текущей даты (значения ячейки Н2) и данных таблицы на листе Курс. Для создания вычисляемого выражения воспользуйтесь функцией ВПР.

Заполните таблицу данными, следуя указаниям:

а) введите данные в первые 4 столбца таблицы (для заполнения 1-го столбца используйте автозаполнение);

б) пятый столбец (Цена в руб.) должен вычисляться автоматически в соответствии со следующей формулой: (Цена в руб.)=(Курс)*(Цена в у.е.);

в) заполоните столбец Стоимость товара, рассчитав его по формуле:
(Стоимость товара) = (Цена в руб.)*(Количество);

г) рассчитайте величину скидки, соответствующей каждой покупке, используя данные таблицы Скидки (для автоматизации вычислений воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций);

д) заполните столбец Сумма от реализации, вставив формулу в соответствии со следующим выражением:

(Сумма от реализации) = (Стоимость товара)- (Скидка).

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

5. Сохраните рабочую книгу и покажите результат Вашей работы преподавателю.

Ведомость продажи товаров

Дата 15.03.2006

Курс 26,7 р.

№	Наименование товара	Количество	Цена в у.е.	Цена в руб.	Стоимость товара	Скидка	Сумма от реализации
	Велосипед
	Сетка волейб.
…
	Костюм спорт.

Рис.2.15.

Вариант 9

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

1. На Листе1 создайте таблицу Сотрудники со столбцами Ф.И.О. и Кол-во детей. Заполните 10 строк таблицы произвольными данными. Переименуйте Лист1 в Сотрудники.

2. На Листе2 создайте таблицу Отчисления в профсоюзный фонд, со следующими заголовками полей: Кол-во детей, Проц.ставка. Процентная ставка определяется следующим образом:

Проц.ставка=

Заполните таблицу данными, как показано на рис.2.16. Переименуйте Лист2 в Профсоюзный фонд.

3. На Листе3 создайте таблицу Выплаты сотрудникам, аналогичную таблице на рис.2.17. Заполните ее данными, следуя указаниям:

а) выберите 5 сотрудников из таблицы на листе Сотрудники и внесите их фамилии в поле Ф.И.О.; столбец Оклад заполните произвольными данными;

б) столбец Кол-во детей должен заполняться автоматически, в соответствии с данным на листе Сотрудники (для автоматизации вычислений используйте функцию ВПР);

в) рассчитайте Пособие на детей для каждого сотрудника в соответствии с формулой: (Пособие на детей)=(Пособие на 1 ребенка)*(Кол-во детей);

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

(Проф. взносы) = (Оклад) * (Проц.ставка).

Замечание: для определения процентной ставки взноса, соответствующей каждому сотруднику, воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций.

д) заполните столбец Выдано на руки, вставив формулу в соответствии со следующим выражением:

(Выдано на руки) = (Оклад)+(Пособие на детей) -(Проф.взносы).

Выплаты сотрудникам

Пособие на 1 ребенка 500р.

Ф.И.О.	Оклад	Кол-во детей	Пособие на детей	Проф. взносы	Выдано на руки
Иванов И.И.	3200р.		500р.	64р.	3636р.
Сидоров П.И.	4000р.		0р.	120р.	3880р.
. . .
Петров В.В.	5700р.		1000р.	114р.	6586р.

Рис. 2.17.

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

5. Сохраните Вашу работу и покажите результат преподавателю.

ариант 10

Средствами электронной таблицы Excel создайте ведомость Продажи авиабилетов с учетом предлагаемых компанией скидок.

1. На Листе1 создайте вспомогательную таблицу Рейсы со столбцами № рейса, Дата вылета, Цена (в у.е.) и заполните ее данными для 5 рейсов: 01234, 01235, …,01238. Переименуйте Лист1 в Рейсы.

2. На Листе2 создайте таблицу Скидки на билеты (см. рис.2.18.). Для определения скидок выделяются билеты трех типов: взрослые (В), детские (Д), «горящие» (Г). Переименуйте Лист2 в Скидки.

3. На Листе3 создайте таблицу, аналогичную таблице на рис.2.19. Заполните ее данными, следуя указаниям:

а) в поле Ф.И.О. введите 5 различных фамилий и заполните соответствующие строки первого столбца с помощью автозаполнения;

б) заполните произвольными данными столбцы Тип билета (Д, В или Г) и № рейса (номера из таблицы Рейсы);

в) данные в столбцах Дата вылета и Цена (в у.е.) должны подставляться автоматически из таблицы Рейсы в соответствии с номером рейса (для автоматизации вычислений используйте функцию ВПР);

г) рассчитайте Цену в руб. в соответствии с формулой:

(Цена в руб.)=( Цена в у.е.)*(Курс);

д) вычислите величину Скидки, используя данные таблицы Скидки на билеты: (Скидка) = (Цена в руб.) * (Скидка в%).

Замечание: для определения величины скидки в % воспользуйтесь функцией ЕСЛИ или ВПР; объясните возможности и особенности использования обеих функций.

е) заполните столбец Цена со скидкой, вставив формулу в соответ-ствии со следующим выражением:

Цена со скидкой = (Цена в руб.) - (Скидка);

Ведомость продажи билетов

Курс 27,6 р.

№ п/п	Ф.И.О.	Тип билета	№ рейса	Дата	Цена (в у.е.)	Цена (в руб.)	Скидка	Цена со скидкой
	Иванов И.И.	В
	Сидоров П.И.	Д
…	. . .
	Петров В.В.	Г

Рис. 2.19.

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

5. Сохраните Вашу работу и покажите результат преподавателю.

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

Тема: Создание сложных связанных таблиц

Справочные сведения:

1. Использование элемента управления формы Флажок.

Меню ВидÞПанель инструментовÞФормы. Выберите Флажок ( ) и поместите на Лист. Правой кнопкой мыши щёлкните по Флажку и из раскрывшегося контекстного меню выберите «Формат объекта». Перейдите на вкладку «Элемент управления» и установите целевую ячейку в «Связь с ячейкой». Если Флажок установлен, то значение в целевой ячейке будет ИСТИНА, иначе - ЛОЖЬ.

2. Использование элемента управления формы Счётчик.

Меню Вид Þ Панель инструментов Þ Формы. Выберите Счётчик ( ) и поместите на Лист. Правой кнопкой мыши щёлкните по Счётчику и из раскрывшегося контекстного меню выберите «Формат объекта». Перейдите на вкладку «Элемент управления» и установите целевую ячейку в «Связь с ячейкой», а также Текущее, Минимальное и Максимальное значения и Шаг изменения. В целевой ячейке будет отображено Текущее значение.

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

1. Создайте новую рабочую книгу и сохраните ее под именем Ведомость.xls. На Листе 1 таблицу Списочный состав группы и заполните ее данными 10 строк таблицы, как показано на рис.3.1. Для заполнения столбца №п/п используйте прогрессию (ПравкаÞЗаполнитьÞПрогрессия). Первый лист книги Лист 1 переименуйте в Список группы.

2. Переименуйте Лист 2, назвав его Математика. На этом листе создайте таблицу Экзаменационная ведомость и оформите, как показано на рис.3.2. Заполните таблицу данными, следуя указаниям:

а) номер группы и список студентов вставьте как ссылку на лист Список группы, так чтобы при изменении данных на листе Список группы автоматически менялись данные на листе Математика;

б) поле Балл заполните произвольно числами от 0 до 5. Установите формат числа - один знак после запятой;

в) графа Оценка должна содержать формулу, которая в зависимости от балла выдаёт значение в соответствии с таблицей 3.1. (используйте функцию ЕСЛИ);

г) к столбцам Балл и Оценка примените условное форматирование (Условие и Формат см. в таблице 3.1).

д) ниже таблицы проведите автоматический подсчёт числа человек с оценками: «отлично», «хорошо», «удовлетворительно» и «неудовлетво-рительно» (используйте функцию СЧЕТЕСЛИ).

Таблица 3.1.

Балл	Оценка	Условное форматирование
от 4,5 и выше	отл.	-
от 3,5 до 4,4	хор.	-
от 2,5 до 3,4	удовл.	зелёный
меньше 2,5	неуд.	красный курсив

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

4. Создайте лист Ведомость начисления стипендии и разместите на нем одноимённую таблицу (см. рис.3.3). Все данные на этом листе должны заполняться автоматически через формулы и ссылки:

Рис. 3.3.

а) номер группы и список студентов вставьте как ссылки на соответствующие ячейки листа Список группы, для заполнения столбца № п/п используйте прогрессию (ПравкаÞЗаполнитьÞПрогрессия);

б) столбцы Предметы заполните ссылками на данные таблиц Экзаменационные ведомости одноименных листов;

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

г) в графе Соц.стип. отмечается, нуждается ли студент в социальной стипендии. Для реализации запроса используйте элемент управления формы Флажок : если галочка установлена, то студенту выплачивается соц.стип. Целевой ячейкой является ячейка из графы Соц.стип. ;

д) графа Стипендии по успеваемости заполняется без учёта социальной стипендии. Стипендия начисляется в процентах от базовой стипендии (БС) (см. рис.3.3). Если средний балл меньше 3,5, то стипендия равна нулю. Формат числа – денежный;

е) в графе Начисленная стипендия вычислите суммарную величину Стипендии по успеваемости и Социальной стипендии, если она есть;

ж) размеры базовой стипендии (БС) и социальной стипендии (Соц.стипендия) должны задаваться элементом управления формы Счётчик . Целевой ячейкой являются ячейки со значениями БС и Соц.стипендии, соответственно.

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

6. Сохраните рабочую книгу под именем Ведомость.xls и покажите свою работу преподавателю.

абораторная работа №4