Алгоритм выполнения задания. 1) Открыть документ с базой данных, который находится в файле Y:\01
1) Открыть документ с базой данных, который находится в файле Y:\01. Информатика (общий курс)\04. Ивасюк Ю.Д\Задачи Excel\tr14.xls. База данных соответствует таблице 1.
2) Провести выборку записей базы данных по заданным признакам (критериям) с помощью операций автофильтра:
- выделить базу данных путем выделения ячейки, соответствующей имени первого поля (# п/п), - выбрать операцию фильтрации (пункт меню действий Данные, операция Фильтр/Автофильтр) и - ввести сформированную команду - область табличного поля документа, занимаемая базой данных, - вводится программой при вводе команды) для последовательной выборки записей по каждой категории признаков (критериев):
а) для выборки по фамилиям: - ввести заданные признаки - критерии; - открыть диалоговое окно автофильтра в поле Фамилия (операция Настройка), - набрать критерии в соответствующих строках ввода (= П* ИЛИ = Л*), при этом отметить, что операции автофильтра позволяют использовать только 2 признака выборки для каждого столбца, и выборка по фамилии, начинающейся на букву З, выполняется отдельно; - ввести команду (клавиша ОК);
б) для выборки по размерам ссуды: - провести действия, аналогичные 1/, но открыть диалоговое окно автофильтра в поле Размер ссуды... и набрать критерии >= 98 И <= 200;
в) для выборки по числу взносов: - провести действия, аналогичные 1/, но открыть диалоговое окно автофильтра в поле Число взносов... и набрать критерии <= 100. Если требуется поместить полученный результат выборки в отдельное место, то после выполнения пунктов а) -в) необходимо провести копирование этого результата в это место. Повторить пункты а) - в) и копирование полученного результата для добавления выборки по фамилии, начинающейся с буквы З; - отменить операцию выборки с помощью автофильтра (Данные, Фильтр/Автофильтр).
3) Провести выборку записей базы данных по заданным критериям с помощью операций усиленного фильтра. Для этого:
- создать таблицу критериев в отдельном от таблицы с исходными данными месте (отдельно от таблицы 1):
Примеры таблицы критериев
1/ Таблица критериев без использования имен ячеек таблицы с исходными данными (создается в указанных ниже ячейках)
C D E F
64 Размер Размер Число
65 ссуды, ссуды, взно-
66 сов за
67 тыс. тыс. весь
68 Фамилия руб руб срок
69 П* >=98 <=200 <=100
70 Л* >=98 <=200 <=100
71 З* >=98 <=200 <=100
Необходимые заголовки могут быть скопированы в эти ячейки.
2/ Таблица критериев с использованием имен ячеек таблицы с исходными данными
H I J
68 Фамилия
69 П* ЛОЖЬ ИСТИНА
70 Л* ЛОЖЬ ИСТИНА
71 З* ЛОЖЬ ИСТИНА
В ячейках H69:J71 представлен результат действия формул, помещенных в них:
Ячейки Формула
I69:I71 =И(E11<=200;E11>=98)
J69:J71 =H11<=100
Отметим, что имена полей в строке (C68:F68), за которой непосредственно следуют значения критериев, не содержащие имен ячеек (C69:F71), должны быть строго идентичны таким же полям исходной базы данных (C10, E10, H10), так как только по ним можно определить, в каких столбцах исходной базы данных следует искать значения, удовлетворяющие критериям. Поэтому такие имена рекомендуется вводить посредством копирования.
Отметить, что при использовании формул адресация ячеек должна быть относительной (без знаков $), а имена полей (в строке выше первой строки со значениями критериев) отсутствуют, так как имена столбцов исходной базы данных, где следует искать значения, удовлетворяющие критериям, содержатся в формулах (E11, H11).
- Определить место, куда будет помещен результат выборки (фильтровать список на месте или копировать на другое место).
Если выбрано другое место, то его необходимо предварительно подготовить его, проведя туда копирование имен тех полей записи, которые подлежат выборке.
Примеры подготовки места для копирования результатов выборки
1/ Результаты выборки могут содержать все поля исходной базы данных
B C D E F G H I J K
74 # Размер Срок, Число Число Годо- Норма Размер
75 ссуды, ежего- взно- вая про- переоди
76 дных сов за норма цента ческого
77 тыс. число взно- весь про- за один взноса,
78 п/п Фамилия И.О. руб лет сов срок цента взнос руб
2/ Результаты выборки могут содержать некоторые поля исходной базы данных в любой последовательности
B C D E F G H I J K
90 # Размер Срок, Число Годо- Размер
91 ссуды, взно- вая переоди
92 сов за норма ческого
93 тыс. число весь про- взноса,
94 п/п Фамилия И.О. руб лет срок цента руб
Отметим, что место для копирования результатов отбора может выбираться только на том же листе, где находится база данных.
- Выделить базу данных путем выделения части таблицы, начиная со строки, содержащей имена полей и непосредственно под которой начинаются записи базы данных со значениями полей (B10:K60). - Выбрать операцию фильтрации (пункт меню действий Данные, операция Фильтр/Усиленный фильтр) и установить ее параметры: - интервал списка , - интервал критериев (C68:F71 или H68:J71). Отметим, что этот интервал должен обязательно содержать 1/ не менее двух строк, 2/ первую строку - с именами полей, строго идентичными именам полей выделенной базы данных (C68:F68 или H68:J68), если критерии не содержат имен ячеек, и - пустую, если критерии содержат имена ячеек. - Выбрать место, куда будет помещена выборка, раздел Действие диалогового окна Усиленный Фильтр: фильтровать список на месте или копировать на другое место). В случае копирования на другое место указать соответствующий интервал ячеек (B78:K86 или C94:J102). Отметим, что этот интервал должен обязательно содержать 1/не менее двух строк, 2/ первую строку с именами полей, строго идентичными с именами полей выделенной базы данных (B78:K78 или C94:J94). Нажмите ОК.
Задание 2.Повторить выполнение Задания 1 и сохранить результаты на дискете в файлеExcel-7.xls.
Задание 3.В ячейках D69:D71 и E69:E71 (размер ссуды) введите разные диапазоны отбора данных для соответствующих фамилий и просмотрите получившиеся результаты. Сохраните результаты на дискете в каталоге Excel в файлеExcel-8.xls.
Задание 4.С помощью Автофильтра отберите данные по следующим критериям: а) фамилии начинаются на Г или Рои б) размер ссуды больше 250 и меньше 100. Сохраните результаты на дискете в каталоге Excel в файлеExcel-9.xls.
Очевидно, что использование Усиленного фильтрапри поиске данных дает большие возможности, по сравнению с Автофильтром, при задании критериев отбора данных.
Лабораторно-практическое занятие N6. Обработка базы данных: использование программы Excel для сортировки записей базы данных
Задание:научиться использовать программу Excel для работы с базами данных, представленными в табличной форме и осуществлять сортировку данных в таблицах по задаваемым условиям.
Результат:пользовательсможет с помощью программы Excel сортировать в таблицах данные по вводимым условиям сортировки.
Решение:рассмотрим возможности программы Excel на примере решения задачи.
1. Провести сортировку записей базы данных (таблица файла Y:\01. Информатика (общий курс)\04. Ивасюк Ю.Д\Задачи Excel \tr14.xls) так, чтобы фамилии и имена отчества устанавливались в алфавитном порядке.