1. В таблице Начисление зарплаты за январь 2007г. (файл Начисление зарплаты.xlsx, Лист2)измените формулу для расчета подоходного налога с физических лиц по следующей шкале налогообложения:
если налогооблагаемая (н/о) сумма -
· до 12 000 руб – налог 12%;
· от 12 000 руб до 24 000 руб – 144 руб + 20% от н/о суммы;
· от 24 000 руб до 36 000 руб – 384 руб + 25% от н/о суммы;
· от 36 000 руб до 48 000 руб – 684 руб + 30%от н/о суммы;
· свыше 48 000 руб – 1044руб + 35%от н/о суммы;
проанализируйте полученные результаты.
2. Используя логические функции, определите, кому из студентов начислить стипендию, и в каком размере. Разработайте таблицу, содержащую результаты сдачи трех экзаменов группой студентов (10 человек). Используйте следующую зависимость:
· если сессия сдана на все «5» - повышенная стипендия;
· на «4» и «5», а также на все «4» - обычная стипендия;
· на «3» и ниже – стипендия не начисляется.
Практическая работа №5
РАБОТА С БАЗОЙ ДАННЫХ
База данных – это упорядоченный набор данных, содержащий строку заголовков (имена полей) и строки данных, которые могут быть числовыми и текстовыми. В электронных таблицах базы данных часто называют списками. Максимальный размер списка не может превышать размер одного рабочего листа (не более 256 колонок и не более 65535 строк). В базах данных колонки принято называть полями, а строки – записями. Для того чтобы работать с таблицей как с базой данных, она должна отвечать некоторым требованиям:
§ база данных обязательно должна содержать заголовки столбцов – имена полей, размещенные в одной строке (верхняя строка списка);
§ в каждом поле информация строго ограничивается по типу. Не допускается смешивать в одном столбце данные различных типов (даты, числа, текст);
§ в списке не допускается наличие пустых строк, колонок и ячеек;
§ список должен быть отделен от остальной информации рабочего листа пустыми строками и пустыми колонками. Рекомендуется размещать базы данных на отдельных рабочих листах.
Excelобладает мощными средствами работы с базами данных: пополнение базы данных с помощью формы, сортировка данных, поиск и выборка данных с помощью фильтров, расчет промежуточных итогов, создание итоговой сводной таблицы на основе данных списка.
1. Загрузите программу Excel 2007.
2. В новой книге разработайте приведенную ниже таблицу.
3. Поле Дата закупки заполните значениями дат по рабочим дням месяца, воспользуйтесь командой Главная/Редактирование/ 
/Прогрессия.
4. Значения в колонке Стоимость рассчитайте по формуле Цена * Количество.
5. Сохраните таблицу под именем Software.xlsx.
Примечание: В Excel существует возможность автоматически включать новую информацию в область базы данных. Для этого диапазону ячеек списка можно присвоить имя База_данных с помощью команды Формулы/Определенные имена/. Если щелкнуть на кнопке списка справа от адреса текущей ячейки в строке ввода, в списке Имя появляется имя диапазона База_данных.
6. Задайте для исходного списка имя База_данных.
7. Создайте форму для добавления данных в список:
Внимание! Для того чтобы начать пользоваться Формой данных, необходимо добавить кнопку Форма на панель быстрого доступа, так как в Excel 2007 кнопки Форма нет на ленте (см. практическую работу №1).
· выделите ячейку в списке и щелкните по кнопке Форма на панели быстрого доступа, заголовок окна формы должен совпадать с именем рабочего листа;
· щелкните на кнопке<Добавить>, в правом верхнем углу появится надпись Новая запись;
· заполните поля формы новой информацией из приведенной ниже таблицы. Для перехода к следующей новой записи повторно щелкните на кнопке<Добавить>;
Примечание: Форму ввода можно использовать не только для ввода данных, она позволяет просматривать существующие записи, редактировать их, удалять и выборочно отображать данные по определенному условию.
8. Закройте форму кнопкой<Закрыть>.
9. Убедитесь в том, что вновь введенные записи также учитываются в диапазоне База_данных. Для этого откройте список в поле Имя слева от строки ввода, щелкните на имени База_данных, выделенная область содержит старые и новые данные.
СОРТИРОВКА СПИСКА
10. Расположите записи списка в порядке убывания стоимости заказа. Для этого установите курсор в любую ячейку поля Стоимость и воспользуйтесь командой Данные/Сортировка и фильтр/ 
.
11. По аналогии отсортируйте БД по убыванию значений количества единиц товара, используя кнопку 
Сортировка по убыванию.
12. Выполните сортировку данных в списке по двум полям – в алфавитном порядке значений поля Тип программы, а для одинаковых типов программ расположите данные по возрастанию Даты закупки. Выполните следующие действия:
· установите курсор в любую ячейку диапазона База_данных (в любую ячейку таблицы);
· выполните команду Данные/Сортировка и фильтр/ .;
· в диалоге Сортировка диапазона в поле Сортировать по выберите из списка Тип программы, по возрастанию, в поле Затем по – Дата закупки, по убыванию, остальные параметры сортировки оставьте по умолчанию, <ОК>.
· убедитесь в том, что данные в списке отсортированы верно: для одинаковых значений Типа программы записи располагаются по убыванию значений Даты закупки.
13. По аналогии выполните сортировку списка по двум полям – по убыванию Количества, а внутри в алфавитном порядке Наименований.
ПОИСК И ВЫБОРКА ЗАПИСЕЙ В РЕЖИМЕ «ФИЛЬТР»
Основное отличие фильтра от упорядочивания – это то, что во время фильтрации записи, не удовлетворяющие условиям отбора, временно скрываются (но не удаляются), в то время, как при сортировке показываются все записи списка, меняется лишь их порядок.
Фильтры бывают двух типов: Обычный фильтр (его еще называют автофильтр) и Расширенный фильтр.
14. Выберите для просмотра из списка информацию об обучающих программных продуктах, используя фильтр:
· установите курсор в любую ячейку диапазона База_данных;
· выполните команду Данные/Сортировка и фильтр/ 
, в ячейках с именами полей справа появятся кнопки списков со стрелками;
· раскройте список поля Тип программы, выберите значение обучение, в результате на экране останутся записи об обучающих программах;
Внимание! Если фильтр включен, стрелки на кнопках фильтра окрашиваются в синий цвет. При необходимости можно выполнять фильтрацию данных по нескольким полям базы данных.
· отмените фильтрацию повторно выполнив команду Данные/Сортировка и фильтр/ , или выберите в списке фильтра поля Тип программы значение Выделить все (в этом случае режим фильтр остается активным и можно повторить фильтрацию с другим условием).
15. Используя фильтр выберите для просмотра информацию из базы данных о компьютерных играх с ценой 250. Для этого установите фильтр в двух полях Тип программы – игры и Цена - 250.
16. Отмените режим фильтр.
Внимание! Если стандартных условий фильтра не достаточно можно создать собственный Пользовательский Автофильтр. Для его создания используется диалоговое окно Пользовательский Автофильтр, которое открывается командой Условие … из раскрывающегося списка кнопки фильтра в поле, по которому осуществляется поиск.
17. Выберите из базы данных информацию о компьютерных программах с ценой от 200р. до 1000р. Выполните следующие действия:
· выделите все значения в поле Цена;
· активизируйте режим фильтр;
· в списке фильтра поля Цена выберите значение Текстовые фильтры, а затем выберите условие больше или равно, рядом в пустой строке введите значение цены 200;
· укажите оператор логического объединения И, так как значение цены должно удовлетворять двум условиям одновременно;
· во втором блоке условий выберите из списка условие меньше или равно, рядом ведите значение 1000, <ОК>.
18. В результате в списке останутся только записи, отвечающие условиям цены:
19. Отмените режим фильтр.
20. Скопируйте базу данных на второй лист текущей книги.
21. Самостоятельно, используя фильтр, выберите из базы данных в просмотр:
· информацию об антивирусных программах, приобретенных фирмой до 15 февраля 2006г. (на первом листе);
· записи о программных продуктах для абитуриентов (на втором листе).
22. Сдайте работу преподавателю.
23. Отмените режим фильтр. Сохраните таблицу.
ПОИСК И ВЫБОРКА ДАННЫХ В РЕЖИМЕ
«РАСШИРЕННЫЙ ФИЛЬТР»
Для решения более сложных задач поиска информации в базе данных используется режим расширенной фильтрации. В отличие от фильтра Расширенный фильтр позволяет определить более сложные критерии поиска, устанавливать вычисляемые условия отбора. Расширенный фильтр более гибкий чем фильтр, он позволяет копировать результат поиска и выборки записей в другое место таблицы или на новый лист рабочей книги. Однако его использование требует некоторых подготовительных действий. Чтобы использовать Расширенный фильтр нужно сформировать три отдельных блока таблицы: Исходный диапазон, Диапазон условий (критериев), Диапазон вывода. Блоки не должны пересекаться.
За Исходный диапазон принимается диапазон Базы данных, включая имена полей.
Диапазон критериев – область рабочего листа, в которую вносятся условия отбора. Диапазон критериев должен содержать не менее двух строк, первая из которых содержит имена полей исходной базы данных. Для правильного выполнения поиска в Диапазоне критериев не должно быть пустых строк. Рекомендуется Диапазон критериев формировать над списком или под ним. Условия отбора вносятся в пустые ячейки Диапазона критериев. Если условия отбора расположены в одной строке, они объединяются логическим оператором И. Условия, расположенные на разных строках, соединяются логическим оператором ИЛИ.
Диапазон вывода – область рабочего листа, в которую копируются результаты поиска данных. Диапазон вывода может находиться на другом листе рабочей книги. Первой строкой Диапазона вывода также является строка с именами полей из Исходного диапазона. Для выполнения поиска информации по условиям координаты всех трех блоков указываются в диалоге Расширенный фильтр.
24. Используя Расширенный фильтр, выберите из списка записи, соответствующие обучающим программам. Для этого выполните следующие действия:
· для формирования диапазона условий скопируйте из базы данных имя поля Тип программы в ячейку А18, в ячейку А19 введите условие поиска – обучение;
· для формирования блока вывода скопируйте строку с именами полей из исходной базы данных в строку A22:F22;
· установите курсор в любую ячейку области База_данных;
· выполните команду меню Данные/Сортировка и фильтр/ 
;
· в диалоговом окне Расширенный фильтр установите режим Скопировать результат в другое место;
· укажите координаты трех диапазонов: Исходный диапазон – $A$1:$F$14 (заполняется автоматически, если курсор находится в области базы данных), Диапазон условий – $A$18:$A$19, Поместить результат в диапазон - $A$22:$F$30. Для записи координат диапазонов используются абсолютные ссылки на ячейки.
Примечание. Заполнять поля диалога Расширенный фильтр можно не с клавиатуры, а путем выделения соответствующих блоков с помощью мыши. Для этого используются кнопки 
.
· Закройте диалог кнопкой <ОК>. В результате будут отобраны записи, соответствующие типу программ – обучение.
25. На втором листе текущей рабочей книги отберите информацию о компьютерных программах, закупленных до 10 февраля 2006г. с общей стоимостью заказа до 20000 руб:
· сформируйте Диапазон условий, включив в него два поля Дата закупки и Стоимость. Во второй строке Диапазона условий задайте условия поиска: в поле Дата закупки ведите выражение <10.02.06, в поле Стоимость – выражение <20000;
· сформируйте в свободной области рабочего листа Диапазон вывода;
· в диалоге Расширенный фильтр укажите координаты трех диапазонов, выполните поиск;
· проанализируйте полученные результаты.
