Решение практических задач с использованием

Формул и функций в MS Excel

 

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

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

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

в ячейку D10 введите формулу = (C10-$C$3)*$C$4

Отчисления в благотворительный фонд = Оклад x% отчисления в благотворительный фонд, в ячейку E10 введите формулу = C10*$C$5

Всего удержано = Подоходный налог - Отчисления в благотворительный фонд, ячейку F10 введите формулу = D10+E10

К выдаче = Оклад – Всего удержано, ячейку G10 введите формулу = C10-F10.

 

Задание 1. Создать таблицу расчета дохода «ТУР» сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис.1.

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

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

Выполните сохранение файла(Файл/Сохранить).

Задание 2. Создать таблицу расчета стоимости туров. Константу вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис.2.

Задание 3. Создать таблицу расчета оборот и итогов «ЭлитОтелей». Константы вводить в расчетные формулы в виде абсолютной адресации.

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

Работа с электронной таблицей как с базой данных.

Сортировка и фильтрация данных.

 

 

Задание1. Заполните таблицу, содержащую информацию о планетах Солнечной системы согласно рис. 1. и сохраните ее под именем work6.xls.

Планеты солнечной системы

Планета	Период	Расстояние	Диаметр	Масса	Спутники
Солнце	0	0	13929	2000000	0
Меркурий	0,241	58	4,9	0,32	0
Венера	0,615	108	12,1	4,86	0
Земля	1	150	12,8	6	1
Марс	1,881	288	6,8	0,61	2
Юпитер	11,86	778	142,6	1906,98	16
Сатурн	29,46	1426	120,2	570,9	17
Уран	84,01	2869	49	87,24	14
Нептун	164,8	4496	50,2	103,38	2
Плутон	247,7	5900	2,8	0,1	1

рис. 1.

Единицы измерения, используемые в таблице:

- период обращения по орбите - в земных годах;

-среднее расстояние от Солнца – в млн. км;

- экваториальный диаметр – в тыс. км

- масса – в

Основные понятия баз данных

Область таблицы А2:F12 можно рассматривать как базу данных. Столбцы A,B,C,D,E,F этой таблицы называются полями, а строки 3 по 12-записями.

Область A2:F2 содержит имена полей.

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

Фильтрация данных.

Команда Данные, фильтр позволяют выделять(фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр – Автофильтр, так и через ручной Расширенный.                                                    

Автофильтр.

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

 рис. 2.

Задание 2. С использованием Автофильтра осуществить поиск планет, начинающихся на букву С или Ю с массой менее 600*

2.1. Выполните команды Данные, Фильтр, Автофильтр.

На полях появились кнопки.

2.2 .Нажмите на кнопку в поле Планета. Выберите пункт Условие.

2.3. В диалоговом окне задайте критерии согласно рисунку 6.2. и нажмите на кнопку ОК.

Проверьте! В базе данных остались планеты Солнце, Юпитер, Сатурн.

2.4 Нажмите на кнопку на поле Масса. Выберите пункт Условие.

2.5 В диалоговом окне задайте критерий: Масса<600.

Проверьте! Остался только Сатурн.

2.6 Выполните команды меню Данные, Фильтр, Отобразить все.

Задание 3. С использованием Автофильтра самостоятельно:

- осуществите поиск планет, имеющих экваториальный диаметр менее 50 тыс. км. и массу менее 50* (ответ: Меркурий, Венера, Земля, Марс, Плутон);

-осуществите поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3* *  не более 2 спутников (ответ: Венера, Земля, Нептун).

Сортировка данных.

Команды Данные, Сортировка позволяют упорядочивать(сортировать) базу данных.

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

рис.3.

Кроме того, необходимо указать метод сортировки: по возрастанию или по убыванию и нажать ОК.

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

Задание 4. Отсортируйте данные в порядке убывания количества спутников.

Задание 5. Отсортируйте данные в таблице в алфавитном порядке названий планет.

Задание 6. Отсортируйте данные в порядке увеличения их расстояния от Солнца.

Задание 7. Подведите итоги.

Проверьте:

Знаете ли вы что такое:

База данных, имя поля, поле, запись, требования к базе, критерии поиска.

Умеете ли вы:

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

Производить сортировку информации.

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

Подбор параметра. Организация обратного отсчета.