Порядок выполнения лабораторной работы.

1. По методическим указаниям познакомьтесь с вопросами создания и обработке баз данных в Excel.

2. Загрузите Excel.

3. Создайте структуру БД по заданию преподавателя.

4. Введите данные в БД но не менее 20 записей.

5. Выведите подготовленную БД на принтер

6. Внесите изменения в БД обычным образом в рабочем листе:

¨ удалите 3 записи в разных местах;

¨ добавьте 5 любых записей;

¨ внесите изменения в любые 3 записи.

7. Внесите изменения в БД с помощью формы данных:

¨ удалите 2 любые записи в различных местах;

¨ добавьте 5 любых записей;

¨ внесите изменения в любые 2 записи.

8. Выведите измененную БД на принтер.

9. Настройте форму данных на поиск записей по одному заданному критерию.

10. Настройте форму данных на поиск записей по двум заданным критериям.

11. Выполните сортировку всей БД в алфавитном порядке для выбранного поля по возрастанию и по убыванию.

12. Верните БД в исходное состояние.

13. Выполните сортировку всей БД для двух выбранных полей по возрастанию и по убыванию.

14. Верните БД в исходное положение.

15. Выполните пункты 11 и 13 для выделенной части БД.

16. Создайте БД, у которой наименования полей размещены не в строку, а в столбец.

17. Выполните сортировку по выбранному полю по столбцам.

18. Оформите отчёт и сдайте преподавателю на проверку.

Лабораторная работа № 5.

Организация работы с фильтрами в Excel 97.

Цель работы:

1. Ознакомиться с понятием "фильтрация".

2. Изучить команду Автофильтр и приобрести навыки работы с ней для поиска и отбора информации в БД.

3. Изучить команду Расширенный фильтр и приобрести навыки работы с ней для поиска и отбора информации в БД.

Понятие о фильтрации.

В Excel процесс поискаи отбора информации называется “ фильтрацией ”.

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

Фильтры, интегрированные в Excel, упрощают процесс ввода и удаления записей из списка, а также процесс поиска информации.

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

В Excel существуют две команды для решения задач фильтрации:

n Автофильтр;

n Расширенный фильтр.

Функция автофильтра.

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

Последовательность вызова функции автофильтра:

1. Выберите меню Данные

2. Выберите команду Фильтр

3. Выберите команду Автофильтр

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

Вид полей списка после активизации функции автофильтр показан на рис. 1.

Рис.1.

После установки автофильтра в качестве критерия можно использовать содержимое любой ячейки, кроме заголовков столбцов. Для этого необходимо щёлкнуть на кнопке со стрелкой в строке заголовка и выбрать нужный элемент из списка.

Например, элемент Днепропетровск из списка столбца Город для отображения записей, относящихся к адресам клиентов фирм, имеющихся в выбранном городе. При этом в строке состояния (внизу) появится сообщение о том, сколько записей, отвечающих заданному критерию, найдено в списке (например, найдено записей: 4 из 15)

По умолчанию в списке представлены все записи, так как в качестве критерия установ­лен элемент (Все).

 

Порядок восстановления всех записей:

1. Выберите меню Данные

2. Выберите команду Фильтр

3. Выберите команду Отобразить все.

 

Отбор записей можно продолжить, задавая критерии в других столбцах. Каждый новый критерий связывается с предыдущим условным оператором И.

С помощью элемента (Первые 10...) пользователь может создать новый список, состоящий из 10 самых больших или самых малых чисел предыдущего списка (значение 10 установлено по умолчанию). После выбора элемента (Первые 10...) открывается диалоговое окно Наложение условия по списку с тремя полями ввода.

Правое поле предназначено для указания количества элементов списка. Путем выбора элементов в списке (Первые 10...) пользователь указывает Excel, что необходимо составить новый список из максимальных (или минимальных) значений предыдущего списка. Значения элементов списка и % от количества элементов в правом поле диалогового окна Наложение условия по списку позволяют установить числовое или % ограничение на количество выводимых на экран элементов списка.

При использовании функции автофильтра можно задавать пользовательские критерии для фильтрации данных. Откройте поле списка столбца, данные которого вы хотите отфильтровать, и выберите элемент (Условие...).

Диалоговое окно Пользовательский автофильтр предоставляет возможность задать комплексные критерии, объединенные с помощью условных операторов И и ИЛИ.

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

 

Рассмотрим пример настройки пользовательского автофильтра.

 

Предположим, что нам нужны данные о клиентах фирмы, у которых номера телефонов находятся в пределах: >3210-368 но <7761-256.

На рис.2 изобразим диалоговое окно Пользовательский автофильтр с настройкой его на заданные критерии.

 

Рис.2.

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

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

Для выхода из режима Автофильтр выполните следующие действия:

1. Выберите меню Данные

2. Выберите команду Фильтр

3. Выберите команду Автофильтр

Расширенный фильтр.

При использовании расширенного фильтра критерии фильтрации задаются на рабочем листе. В этом случае в диалоговом окне при активизации функции расширен­ного фильтра указывается диапазон ячеек, который содержит используемые в качестве критериев данные. Достоинство этого способа состоит в том, что пользователь всегда имеет чёткое представление о применяемых критериях и может их быстро изменять.

Основой расширенного фильтра является множество критериев, для задания которых следует вставить несколько пустых строк в начало таблицы.

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

Теперь в ячейку под соответствующим заголовком столбца можно вставить критерий. Критерии, введенные в одной строке, объединяются условным оператором И, а критерии в разных строках - оператором ИЛИ. В случае необходимости вместе с критерием в ячейку вводят и оператор сравнения.

Внимание: При задании критериев знак равенства (=) не используется, поскольку в этом случае программа интерпретирует критерий как формулу и выдаёт значение ошибки.

Для активизации функций расширенного фильтра выберите:

1.Меню Данные.

2.Команду Фильтр.

3.Команду Расширенный фильтр.

После этого на экране (рис.4) появится диалоговое окно Расширенный фильтр.

 

Рис.4.

В диалоговом окне следует указать диапазон критериев и позицию списка, созданного с помощью фильтра.

Сначала укажите новую позицию выбранных записей. Если активизирована опция скопировать результат в другое место, в поле Поместить результат в диапазон следует указать адрес ячейки, начиная с которой будут располагаться соответствующие критериям записи. Если указатель ячейки находится в списке, поле Исходный диапазон содержит адрес первой и последней ячейки списка. Фильтруемый список всегда можно определить заново, установив курсор ввода в поле Исходный диапазон и выделив ячейки на рабочем листе с помощью мыши. Чтобы задать диапазон ячеек, содержащих критерии, установите курсор ввода в поле Диапазон условий и отметьте нужные ячейки, включая заголовки столбцов. В результате установки опции Только уникальные записи из полученного списка будут исключены записи с общим элементами.

Порядок выполнения работы.

1. Ознакомиться с понятием " Фильтрация ".

2. Загрузите Excel.

3. Загрузите далее созданную БД в Excel или создайте новую.

4. Войдите в режим работы с командой Автофильтр.

5. С помощью Автофильтра выполните поиск и отбор информации из БД:

- по одному любому критерию;

- с заданием комплексного критерия (применение условного оператора И);

- с заданием комплексного критерия (применение условного оператора ИЛИ);

- по двум любым критериям.

6. Подготовьте БД к применению команды Расширенный фильтр.

7. Активизируйте функцию Расширенный фильтр.

8. С помощью файлового окна Расширенный фильтр выполните настрой для поиска и отбора информации из БД.

8.1. С фильтрацией списка на месте:

- по одному любому критерию;

- с применением условного оператора И;

- с применением условного оператора ИЛИ.

8.2. С копированием результата в другое место:

- по одному любому критерию;

- по двум любым критериям.

9. Результаты поиска и отборов информации выведите на печать или срисуйте с экрана.

10. Закройте БД.

11. Закройте приложение Excel.

12. Оформите отчет и сдайте преподавателю на проверку.

Содержание отчёта.

1. Номер, наименование и цель работы.

2. Понятие о фильтрации.

3. Опишите функцию Автофильтр.

4. Опишите функцию Расширенный фильтр.

5. Результаты работы представьте в виде распечаток или зарисованных с экрана с подробным описанием настроек и указанием критериев.

6. Краткие выводы.

------------------------------------------------------------------------------------------------------

Контрольные вопросы к лабораторным работам

Работа №1

1. Что такое Excel и для чего он нужен?

2. Способы перемещения курсора по электронной таблице.

3. Типы данных.

4. Порядок ввода данных.

5. Как создать электронную таблицу?

6. Как сохранить электронную таблицу?

7. Как вывести электронную таблицу на печать?

Работа №2

1. Как открыть новую книгу?

2. Как открыть ранее созданный файл книги?

3. Как закрыть файл книги?

4. Как отменять выполненные действия?

5. Как выделять ячейки?

6. Как копировать данные?

7. Как перемещать данные?

8. Как удалять данные?

9. Как изменять масштаб изображения рабочего листа?

10. Как закреплять заголовки столбцов и строк?

 

Работа №3

1. Дайте классификацию типов диаграмм.

2. Назначение основных диаграмм.

3. Дайте основные определения применяемые в диаграммах.

4. Порядок создания диаграммы с помощью МАСТЕРА ДИАГРАММ.

5. Быстрый способ построения диаграммы.

6. Изменение диаграммы с помощью панели инструментов ДИАГРАММЫ.

7. Порядок сохранения диаграммы.

 

Работа №4

1. Дайте понятия о базе данных (БД).

2. Порядк создания БД и возможности её редактирования.

3. Создание структуры БД и ввод в неё данных.

4. Добавление, правка и удаление записей

5. Поиск записей с помощью формы данных.

6. Сортировка БД по строкам и столбцам.

 

Работа №5

1. Дайте понятия о фильтрации.

2. Расскажите о функции Автофильтра.

3. Расскажите о функции Расширенный фильтр.

4. Последовательность включения режима Автофильтр.

5. Последовательность включения режима Расширенный фильтр.

6. Расскажите о возможных настройках диалогового окна Расширенный фильтр.

 

Литература

1. М.Додж и др. Эффективная работа с Microsoft Excel 7.0, Питер, 1996.

2. П.Блатнер и др. Использование Microsoft Excel. Пер. с англ. 2000

3. Джон Уокенбах Подробное руководство по созданию формул в Excel 2002, Диалектика,2003

4. Д.М. Златопольский 1700 заданий по Microsoft Excel, БХВ-Петербург, 2003