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

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

Задание 5.1.Использование констант в расчетных формулах

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задание 6.1. Условное форматирование и фильтрация данных

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

2. Cохранить файл с новым именем «Лаб6».

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

 

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

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

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

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

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

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

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

10. На очередном свободном листе электронной книги создайте таблицу по образцу на рис.6.1..

Средняя годовая температура воздуха

Рис.6.1.

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

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

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

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

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

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

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

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

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

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

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

17.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

 

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

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

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

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