Тема: табличный процессор Microsoft Excel.

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

Тема: табличный процессор Microsoft Excel.

Цель работы: ознакомление с табличным процессором Microsoft Excel; выполнение расчетов с использованием формул и постоянных данных; отработка навыков проведения единой системы расчетов в электронной книге; изучение возможностей применения в электронных таблицах логических данных и выражений и построения разного типа диаграмм; получение навыков построения баз данных небольшого объема, упорядочения и фильтрации данных в них.

 

Задание 1.

 

Рассчитать заработную плату для сотрудников предприятия:

Фамилия И.О.	Количество налоговых льгот	Категория	Начислено по видам оплат	Начислено всего	Облагаемая налогом сумма	Удержано и учтено	Сумма к выдаче
начислено	районный коэффициент	премия	аванс	подоходный налог	пенсионный фонд	всего
Никифоров И.Л.			5 700					2 500
Петрова М.Р.			8 400					4 200
Самойлова А.О.			3 500					1 700
Певцов Н.А.			4 860					1 700
Кузбасов Е.Р.			3 020					1 200
Романова Н.Г.			3 930					1 350
Симонов С.П.			3 700					1 200
Зубова Е.А.			8 500					3 500
ВСЕГО

При проведении расчета принять следующие правила и исходные данные:

- минимальная заработная плата – 140 руб.;

- районный коэффициент – 15% от [3];

- премия начисляется в размере:

o 15% от [3] для сотрудников категории 2 и 4,

o 10% от [3] для остальных сотрудников;

- начислено всего [6]=[3]+[4]+[5];

- облагаемая налогом сумма получается после вычитания из “Начислено всего” произведения “Количество налоговых льгот” на величину минимальной заработной платы: [7]=[6]-[1]´[Минимальная заработная плата]. Облагаемая налогом сумма принимается равной нулю, если в результате расчетов она будет отрицательной;

- отчисление в пенсионный фонд – 1% от [6];

- подоходный налог – 13% от [7];

- удержано всего – [11]=[8]+[9]+[10];

- сумма к выдаче – [12]=[6]–[11];

Для колонок [6], [11] и [12] вычислить среднее, минимальные и максимальные значения.

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

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

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

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

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

Задание 2.

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

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

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

Таблица 1

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

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

 

Таблица 2

Расход материалов по каждому виду продукции

Виды продукции	Вид материала
Материал 1	Материал 2	Материал 3	Материал 4	Материал 5
Продукция 1			10,7	5,6	2,8
Продукция 2			18,4	7,9	4,7
Продукция 3	98,5	47,7	5,9	3,4	0,98

 

Таблица 3

Потребности предприятия в материалах

Цеха	Вид материала
Материал 1	Материал 2	Материал 3	Материал 4	Материал 5
Цех №1
Цех №2
Цех №3
Цех №4
Всего

III. ЛОГИЧЕСКИЕ ФОРМУЛЫ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ.

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

Рис. 11. Окно вставки логических функций.

Аргументами логических функций являются логические значения ИСТИНА и ЛОЖЬ. Логические значения, в свою очередь могут быть получены как результат определения логических выражений. Например, для логического выражения 10>5 результатом будет логическое выражение ИСТИНА, а для логического выражении А1<А2 (где в ячейке А1 хранится число 10, а в ячейке А2 – число 5) – значение ЛОЖЬ.

Логическая функция И обеспечивает проверку одновременного выполнения связанных этой функцией условий. При выполнении всех условий функция принимает значение ИСТИНА, а если хотя бы одно из условий не выполняется, функция принимает значение ЛОЖЬ.

Логическая функция ИЛИ обеспечивает проверку выполнения хотя бы одного условия, из всех условий связанных этой функцией. В этой ситуации функция принимает значение ИСТИНА, а если ни одно условие не выполняется, функция принимает значение ЛОЖЬ.

Логическая функция НЕ преобразует уже имеющееся логическое значение и принимает значение ИСТИНА, если аргумент имеет значение ЛОЖЬ и наоборот.

Логическая функция ЕСЛИ служит проверке истинности логического условия и описывает выполняемые действия в случае выполнения или невыполнения условия. Функция обеспечивает реализацию конструкции ЕСЛИ …, ТО …, ИНАЧЕ …. Функция ЕСЛИ позволяет организовывать вложенные проверки нескольких условий согласно конструкции (ЕСЛИ …, ТО …, ИНАЧЕ (ЕСЛИ …, ТО …, ИНАЧЕ (ЕСЛИ …, ТО …, ИНАЧЕ …))).

Логическая функция ЕСЛИОШИБКА возвращает значение ЕСЛИ_ОШИБКА, если выражение ошибочно. В противном случае функция возвращает само выражение.

 

Задание 3.

1. На рабочем листе «Результаты экзаменационной сессии» электронной книги «Стипендия» подготовить ведомость назначения студентов на стипендию по результатам экзаменационной сессии (число студентов не менее 12). Вычислить число полученных группой оценок и средний бал по каждой дисциплине.

Оценки могут принимать значения «5», «4», «3», «2» и пустая ячейка – «не явился».

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

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

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

 

Задание 5.

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

Таблица 4

Результаты выборов

Задание 6.

Имеется информация о продажах товаров фирмами:

Дата заказа	Фирма	Товар	Ед. изм.	Кол.	Цена	Сумма	Налог	Дата поставки
24.07.08	Мечта	Клей обойный	шт.					06.09.08
05.12.08	Соловей	Плинтус	п.м.
30.11.08	Приз	Гвозди	кг					23.12.08
12.03.09	Приз	Клей обойный	шт.
16.09.08	Приз	Обои	п.м.					24.10.08
28.02.09	Соловей	Линолеум	п.м.
31.01.08	Мечта	Гвозди	кг					15.02.09
29.10.08	Мечта	Потолок подв.	м^2					08.11.08
12.07.08	Приз	Обои	п.м.					04.03.09
22.02.09	Соловей	Плинтус	п.м.
31.08.08	Соловей	Обои	п.м.					06.09.08
17.09.08	Мечта	Клей обойный	шт.					25.09.08
19.02.08	Приз	Краска	кг
06.08.08	Мечта	Обои	п.м.					23.09.08
29.10.08	Соловей	Плинтус	п.м.					07.11.08
02.09.08	Мечта	Обои	п.м.					18.09.08
16.12.08	Приз	Краска	кг					28.12.08
05.03.09	Приз	Клей обойный	шт.
13.01.09	Соловей	Гвозди	кг					17.02.09
05.10.08	Соловей	Ручка дверная	шт.					12.10.08
06.03.09	Мечта	Линолеум	п.м.					24.03.09
16.08.08	Приз	Обои	п.м.
22.06.08	Мечта	Клей обойный	шт.					14.08.08
04.12.08	Мечта	Плинтус	п.м.
14.02.08	Соловей	Плинтус	п.м.
11.12.08	Приз	Ручка дверная	шт.					14.01.09
04.02.09	Соловей	Потолок подв.	м^2
06.03.08	Мечта	Краска	кг					04.04.08
17.11.08	Приз	Краска	кг					23.11.08
04.03.09	Соловей	Клей обойный	шт.
15.03.08	Соловей	Обои	п.м.					18.03.08
17.10.08	Мечта	Обои	п.м.					29.10.08
12.02.09	Приз	Гвозди	кг					18.02.09
29.01.09	Мечта	Линолеум	п.м.
23.11.08	Приз	Потолок подв.	м^2					30.11.08
29.10.08	Приз	Клей обойный	шт.					23.11.08
26.03.09	Мечта	Обои	п.м.

 

Значение данных в столбце «Сумма» рассчитывается как произведение [Количество]*[Цена].

Значение в столбце «Налог» определяется как 20 процентов от [Сумма]

 

1. Создать электронную книгу «Анализ работы фирм», на первом листе разместить исходную информацию. Дать листу имя «Исходная информация». Оформить данные в виде списка.

2. Сделать копии списка на листы Сортировка, Итоги, Выборка 1, Выборка 2.

3. На листе Сортировка выполнить сортировку данных по товарам, фирмам, количеству.

4. На листе Итоги для фирм подвести итоги по количеству, сумме и налогу.

5. На листе Выборка 1 отобразить данные только по выполненным заказам, выполнить сортировку данных по фирмам, дате поставки.

6. На листе Выборка 2 отобразить данные о гвоздях, заказанных во II квартале 2009 г. Выполнить сортировку списка по фирмам.

7. Сохранить файл под именем «Сортировка и фильтрация».

 

VI. СВОДНЫЕ ТАБЛИЦЫ

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

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

Для создания сводных таблиц используется Мастер сводных таблиц, вызываемый командой

Вставка®Сводная таблица®…

Мастер сводных таблиц осуществляет построение сводной таблицы в несколько этапов:

1. Указание вида источника сводной таблицы.

2. Указание диапазона ячеек, содержащего исходные данные.

3. Построение макета сводной таблицы.

4. Выбор места расположения и параметров сводной таблицы.

Внимание! Для выполнения работы используются исходные данные предыдущего задания (№6).

Задание 7.

1. В книге «Анализ работы фирм» добавить листы СТ1, СТ2, СТ3, СТ4 и построить на них сводные таблицы:

 

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

Тема: табличный процессор Microsoft Excel.

Цель работы: ознакомление с табличным процессором Microsoft Excel; выполнение расчетов с использованием формул и постоянных данных; отработка навыков проведения единой системы расчетов в электронной книге; изучение возможностей применения в электронных таблицах логических данных и выражений и построения разного типа диаграмм; получение навыков построения баз данных небольшого объема, упорядочения и фильтрации данных в них.