Тема: Использование констант в составление формул с относительными и абсолютными адресами.

Порядок работы:

Задание 5.1.

1. На свободном листе электронной книги «Лаб41» создайте таблицу расчета дохода сотрудников организации. Константы введите в расчетные формулы в виде абсолютной адресации для начисления зарплаты, пример таблицы приведен в таблице 5.1.

 

			Таблица 5.1.
Таблица констант
Необлагаемый налогом доход
% подоходного налога
% отчисления в благотворительный фонд
Таб. №	ФИО	Оплата	Подоходный налог	% отчисления в благотворительный фонда	Всего удержано	К выдаче

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

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

3.1. Формулы для расчетов:

Подоходный налог = (Оклад - Необлагаемый налогом доход) * % подоходного налога;

Отчисления в благотворительный фонд = Оклад * % отчисления в благотворительный фонд.

Всего удержано = Подоходный налог + Отчисления в благотворительный фонд;

К выдаче = Оклад - Всего удержано.

4. Постройте объемную гистограмму по данным столбца «К выдаче», проведите форматирование диаграммы.

5. Переименуйте лист электронной книги, присвоив ему имя
«Доход сотрудников».

6. Выполните текущее сохранение файла (Файл/Сохранить Как) в Лаб5.

Лабораторная работа 6

Тема: ФИЛЬТРАЦИЯ ДАННЫХ И УСЛОВНОЕ ФОРМАТИРОВАНИЕ В MS EXCEL

Порядок работы. Изучение информационной технологии организации отбора и сортировки данных в таблицах MS Excel. В таблице «Доход сотрудников» выполнить сортировку и фильтрацию данных.

Задание 6.1.

1. Запустите редактор электронных таблиц Microsoft Excel. Откройте файл «Расчеты», созданный ранее (Файл/Открыть).

2. Скопируйте таблицы задания (лист «Доход сотрудников»).

3. Произведите сортировку по фамилиям сотрудников в алфавитном порядке по возрастанию (выделите блок ячеек с значениями, выберите в меню Данные команду Сортировка, сортировать по Ф.И.О.).

 

4. Постройте диаграмму по итогам расчета (данные столбца «К выдаче»). В качестве подписей оси «X» укажите фамилии сотрудников.

5. Произведите фильтрацию значений дохода, превышающих 1600 р.

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

6. Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные / Фильтр / Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «К выдаче»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации Условие. В открывшемся окне Пользовательский Автофильтр задайте условие — больше 1600.

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

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

9. Выполните сохранение файла (Файл/Сохранить Как) в Лаб6.

Дополнительное задание 6.1.

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.

 

		Средняя годовая температура воздуха
Город	Январь	Февраль	Март	Апрель	Май	Июнь
Москва	-12	-10
Екатеринбург	-20	-15
Владивосток	-10
Омск	-15	-10
Норильск	-25	-20

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

3. Используйте автоподбор ширины ячеек, предварительно выделив ячейки (Формат/Столбец/Автоподбор ширины).

4. Проведите условное форматирование ячеек значений температур (Формат/Условное форматирование):

Установите формат данных:

меньше 0 - синим цветом шрифта (полужирный);

равное 0 - зеленый фон, цвет шрифта белый;

больше 0 - красным цветом шрифта (полужирный).

5. Выполните сохранение файла (Файл/Сохранить Как) в Лаб61.

Дополнительное задание 6.2.

1. Скопировать таблицу расчета суммарной выручки ( Лист «Выручка») и определить фильтрацией, в какие дни выручка за день не превысила 3000 руб.

2. Скопируйте содержимое листа «Выручка» на новый лист (Правка/Переместить – Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создать копию. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишей [Ctrl].

3. Задайте фильтрацию, используя (Данные/Расширенный фильтр) в Microsoft Excel 2003, (в Microsoft Excel 2007, использовать Данные/Дополнительно). Необходимо предварительно подготовить Диапазон условий и Заголовок таблицы для результата выборки, как показано на примере рис. 6.2.

3.1. Для подготовки Диапазона условий, используется Заголовок столбца исходной таблицы, по которому осуществляется выборка по условию (Правка/Копировать) и указать условие выборки ( Диапазон условий);

Если необходимо поместить результат в другой диапазон таблицы, необходимо подготовить Заголовок таблицы для результата ( Результат Даты когда выручка превысила 3000 рублей). Исходная таблица Рис. 6.2.

Расчет суммарной выручки
Дата	Отделение 1	Отделение 2	Отделение 3	Всего за день
1 мая 2009 г.	246.22	1345.26	445.3	2036.78
2 мая 2009 г.	4578.36	436.97	1075.58	6090.91
3 мая 2009 г.	596.34	708.68	670.86	1975.88
4 мая 2009 г.	157.85	428.74	1709.63	2296.22
5 мая 2009 г.	354.11	1948.8	1128.41	3431.32
6 мая 2009 г.	157.85	428.74	1709.63	2296.22
7 мая 2009 г.	157.85	428.74	1709.63	2296.22
8 мая 2009 г.	354.11	1948.8	770.63	3073.54
Итого	7799.28	9679.26	11288.17	28766.71
	Диапозон условий
	Всего за день
	>3000
	Результат
Даты когда выручка превысила 3000 рублей
Расчет суммарной выручки
Дата	Отделение 1	Отделение 2	Отделение 3	Всего за день
2 мая 2009 г.	4578.36	436.97	1075.58	6090.91
5 мая 2009 г.	354.11	1948.8	1128.41	3431.32
8 мая 2009 г.	354.11	1948.8	770.63	3073.54

 

 

4. Определить, в какие дни выручка по первому подразделению не превысила 1000 руб.

5. Определить, в какие дни выручка по второму подразделению попала в диапазон от 1000 руб. до 2000 рублей.

6. Определить, в какие дни выручка по первому подразделениям больше 500 руб. по второму подразделению меньше 1000 рублей по третьему подразделению больше 1000 рублей.

Лабораторная работа 7