Сводная ведомость результатов экзаменационной сессии

Группа № ______

№	Фамилия	Экзамены	Средний балл	Кол-во сданных экзаменов	Стипендия
…
Всего оценок:
	«отлично»
	«хорошо»
	«удовл.»
	«неудовл.»
	«неявился»
Средний балл
	Итого стипендиальный фонд группы

Размер минимальной стипендии 1000 руб.

Стипендия назначается при условии сдачи всех экзаменов на оценки «4» и «5». В зависимости от среднего балла размер стипендии устанавливается в соответствии с таблицей:

Средний балл	Размер стипендии
От	До (включительно)
	4,5	минимальная стипендия
4,5		увеличение минимальной стипендии на 50 %

2. На рабочем листе «Ведомость» подготовить ведомость для выплаты стипендии, в которую включаются только студенты, которым начислена стипендия.

Разработанная таблица должна допускать изменения всех исходных значений. При выполнении вычислений все значения должны округляться до двух знаков после запятой. Тип ячеек столбца «Сумма» – денежный.

ВЕДОМОСТЬ НА ВЫПЛАТУ СТИПЕНДИИ

Группа № ___________

№	Фамилия И.О.	Сумма	Роспись
…
Всего

Задание 4.

Разработать книгу для расчета заработной платы сотрудников фирмы (число сотрудников не менее 3):

Расчетный лист сотрудника может иметь следующий вид:

	<Ф.И.О.>, например Иванов И.И.	Сумма	Значение
	Стаж
	Разряд
	Базовый оклад
	Увеличение должностного оклада за работу в отрасли
	Увеличение должностного оклада за вредность
	Должностной оклад
	Надбавка к должностному окладу за разряд
	Надбавка к окладу за выслугу лет
	Районный коэффициент
	Всего начислено
	Подоходный налог
	Выдано аванса
	К выдаче

В таблице указываются индивидуальные значения для каждого сотрудника:

– стаж – указывается любой;

– разряд может принимать значения от 1 до 5;

– базовый оклад – указывается любой;

– увеличение базового оклада за работу в отрасли одинаковое для всех и устанавливается равным 25 % из [4];

– увеличение базового оклада за вредность – в 10 % из [4];

– должностной оклад вычисляется как сумма значений [4], [5] и [6].

Надбавки к должностному окладу за разряд устанавливаются в % из [7] в соответствии с нормативными данными.

Разряд	% надбавки

Надбавки к окладу за выслугу лет устанавливаются в % из [7] в соответствии с нормативными данными.

Стаж работы, лет	Надбавка, %
От	До
	и более

Районный коэффициент устанавливается в 15 % к сумме позиций [7],
[8], [9].

Всего начислено равно: сумме позиций [7], [8], [9] и [10].

Подоходный налог устанавливается в 13 % из [11].

Значение «К выдаче» равно: [11] – [12] – [13].

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

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

Результаты выполнения работы

В папке «Лабораторная работа № 9» должны присутствовать файлы:

Ø Задание 1.xclx

Ø Задание 2. хclx

Ø Задание 3. хclx

Ø Задание 4. хclx

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

РАСЧЕТЫ В ЭЛЕКТРОННЫХ КНИГАХ

Цель работы – отработка навыков проведения единой системы расчетов в электронной книге.

Документы, созданные в табличном процессоре Excel, называют рабочими книгами.

Рабочие книги в Microsoft Excel 2007 записываются как файлы с расширением .XLSX (в отличие от Microsoft Excel 2003, в котором книги имеют расширение .XLS).

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

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

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

= ВЗ*Лист2!СЗ

= Янв!Б12+ФеврD12+Март!В12

= СУММ(Янв:Март!D12).

С помощью первой формулы вычисляется произведение чи­сел, содержащихся в ячейке ВЗ текущего рабочего листа и ячейке С3 листа «Лист2». Между именем рабочего листа и адресом ячей­ки ставится восклицательный знак.

Во второй и третьей формулах вычисляется сумма чисел, нахо­дящихся в ячейках D12 на рабочих листах «Янв», «Февр», «Март».

Например, формула =СУММ(Лист1:Лист3!$A$1) суммирует содержимое ячеек А1 в трех подряд идущих листах одной рабочей книги.

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

В рабочем листе «Итого» подсчитаем суммарные значения за квартал.

Решение. Переименуем рабочие листы «Лист1», «Лист2», «Лист3» и «Лист4» соответственно в «Янв», «Февр», «Март», «Итого» с помощью контекстно-зависимого меню.

Выделим рабочие листы «Янв», «Февр», «Март», «Итого» с по­мощью мыши и клавиши «Shift». При этом ярлычки выделенных рабочих листов станут белыми. Это свидетельствует о том, что мы включили групповой режим работы. Все, что мы будем набирать на рабочем листе «Янв», автоматически будет набираться на всех выделенных рабочих листах. В целях экономии времени имеет смысл в групповом режиме создать таблицу «Ведомость» с общи­ми для всех выделенных рабочих листов элементами. Это прежде всего заголовок, сведения о минимальной зарплате, список работников, шапка таблицы, формулы подсчета итоговых сумм по столбцам, а для первых трех таблиц и формулы для расчета отчис­лений в пенсионный фонд и текущего облагаемого дохода.

В колонку «Пенсионный фонд» (ячейка D4) занесем формулу = С4*0,01 и размножим ее в ячейки D5:D6 в таблице, представленной на рис. 10.1.

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов		=С4*0,01	=С4-D4-$D$2	=E4
		Петров		=С5*0,01	=С5-D5-$D$2	=E5
		Сидоров		=С5*0,01	=С6-D6-$D$2	=E6
			=СУММ (С4:С6)	=СУММ (D4:D6)	=СУММ (E4:E6)	=СУММ (F4:F6)

Рис. 10.1.Рабочий лист с формулами за январь

В ячейку Е4 («Текущий облагаемый доход») внесем формулу =C4-D4-$D$2 и размножим ее в ячейки Е5:Е6.

В ячейках C7:F7 вычислим суммы. В ячейках A3:F6 установим сетку.

Отменим выделение группы. Для этого достаточно щелкнуть мышью по невыделенному листу или с помощью контекстно-за­висимого меню «Разгруппировать листы». Обратите внимание, что таблица одновременно создавалась на всех выделенных рабо­чих листах.

Запишем формулу для облагаемого дохода с начала года. В ян­варе она равна текущему облагаемому доходу, т.е. в клетке F4 за­пишем формулу =Е4, в клетке F5 – формулу =Е5 и т. д.

В результате получим данные об облагаемом доходе за январь (рис. 10.2).

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов			1412,5	1412,5
		Петров			1709,5	1709,5
		Сидоров			1907,5	1907,5
					5029,5	5029,5

Рис. 10.2. Рабочий лист со значениями за январь

В феврале облагаемый доход равен текущему облагаемому до­ходу плюс облагаемый доход за январь. Формула в феврале в клет­ке F4 будет иметь вид =Е4+Янв!Е4. Скопируем данную формулу в диапазон F5:F6 (рис. 10.3). В результате получим данные об облагаемом доходе за февраль (рис. 10.4).

В марте аналогичная формула в клетке F4 будет иметь вид =Е4+СУММ(Янв:Март!Е4) (рис. 10.5). Результаты вычислений за март представлены на рис. 10.6.

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов		=С4*0,01	=С4-D4-$D$2	=E4+Янв!Е4
		Петров		=С5*0,01	=С5-D5-$D$2	=E5+Янв!Е5
		Сидоров		=С5*0,01	=С6-D6-$D$2	=E6+Янв!Е6
			=СУММ (С4:С6)	=СУММ (D4:D6)	=СУММ (E4:E6)	=СУММ (F4:F6)

Рис. 10.3.Рабочий лист с формулами за февраль

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов			917,5
		Петров			1115,5
		Сидоров			1313,5
					3346,5

Рис. 10.4. Рабочий лист со значениями за февраль

	А	В	С	Е	F
		Минимальный оклад
	№	Ф.И.О.	Начислено	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов		=С4-D4-$D$2	=E4+СУММ (Янв:Февр!Е4)
		Петров		=С5-D5-$D$2	=E5+ СУММ (Янв:Февр!Е5)
		Сидоров		=С6-D6-$D$2	=E6+ СУММ (Янв:Февр!Е6)
			=СУММ(С4:С6)	=СУММ(E4:E6)	=СУММ(F4:F6)

Рис. 10.5.Рабочий лист с формулами за март (столбец D спрятан)

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов			1511,5	3841,5
		Петров			1709,5	4534,5
		Сидоров			2105,5	5326,5
					5326,5	13702,5

Рис. 10.6. Рабочий лист со значениями за март

В рабочем листе «Итого» запишем формулы для подсчета ито­говых сумм (рис. 10.7):

– по колонке «Начислено» – =СУММ(Янв:Март!С4);

– по колонке «Пенсионный фонд» – =СУММ(Янв:Март!D4);

– по колонке «Облагаемый доход с начала года» – =СУММ
(Янв: Март!Е4).

	А	С	D	Е
			Ведомость
			72,5
	№	Начислено	Пенсионный фонд	Облагаемый доход с начала года
		=СУММ (Янв:Март!С4)	=СУММ(Янв:Март!D4)	=СУММ(Янв:Март!E4)
		=СУММ (Янв:Март!С5)	=СУММ(Янв:Март!D5)	=СУММ(Янв:Март!E5)
		=СУММ (Янв:Март!С6)	=СУММ(Янв:Март!D6)	=СУММ(Янв:Март!E6)
		=СУММ(C13:С21)	=СУММ(D13:D21)	=СУММ(E13:E21)

Рис. 10.7.Рабочий лист «Итого» с формулами (столбец В спрятан)

Размножим данные формулы. Результаты вычислений в рабочем листе «Итого» представлены на рис. 10.8.

Связывание рабочих листов можно осуществлять тремя спосо­бами:

– с помощью формул, прямо осуществляя их написание;

– с помощью копирования и специальной вставки с использова­нием кнопки «Вставить ссылку»;

– с помощью консолидации рабочих листов.

	А	В	С	D	Е
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Облагаемый доход с начала года
		Иванов			3841,5
		Петров			4534,5
		Сидоров			5326,5
					13702,5

Рис. 10.8.Рабочий лист «Итого» со значениями

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

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

На вкладке Данные в группе Работа с даннымивыберите команду Консолидация(рис. 10.9, 10.10).

Рис. 10.9. Окно «Консолидация»

В первом поле данного окна выбирается функция, например «Сумма», во втором – ссылки на области в рабочих листах, кото­рые с помощью кнопки «Добавить» переводятся в поле «Список диапазонов:». Необходимые области для консолидации в рабочих листах можно выделить с помощью мыши, их диапазон автомати­чески будет заноситься в поле «Ссылка:». Это значительно уско­ряет рабочий процесс.

Рис. 10.10.Диалоговое окно «Консолидация»

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

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

Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.

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

Задание 1.

Переименуйте первые три листа книги соответственно «Январь», «Февраль», «Март». Внесите на них данные, как показано на рис. 10.1, 10.3, 10.5.
На новом листе «Квартал» консолидируйте данные с трех рабочих листов.

Задание 2.

Четыре цеха предприятия выпускают три вида продукции. Известны объемы производства продукции в месяц по цехам (табл. 10.1) и расходы сырья на единицу продукции (табл. 10.2). Разработать электронную таблицу для расчета потребности цехов и всего предприятия в сырье на месяц (табл. 10.3).

Каждую таблицу разместить на отдельном листе электронной книги, названном соответственно названию таблицы.

Формулы для расчета данных в табл. 10.3 должны вклю­чать в себя частично абсолютные ссылки. При защите ла­бораторной работы следует объяснить, почему выбрана та или иная частично абсолютная ссылка.

Таблица 10.1

План выпуска продукции по цехам

Цех	Виды продукции
Продукция 1	Продукция 2	Продукция 3
Цех № 1
Цех № 2
Цех № 3
Цех № 4

Таблица 10.2