Задания для индивидуальной работы №1

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;

· сформируйте в свободной области рабочего листа Диапазон вывода;

· в диалоге Расширенный фильтр укажите координаты трех диапазонов, выполните поиск;

· проанализируйте полученные результаты.