В) Сортировка с помощью кнопок панели инструментов
ЛАБОРАТОРНАЯ РАБОТА
Работа с базами данных в Excel
Задание:
1. создать базу данных,
2. упорядочить записи по трем полям,
3. сформулировать и выполнить простой запрос к БД,
4. сформулировать и выполнить 2 запроса с условиями поиска с помощью расширенного фильтра,
5. выполнить арифметическую обработку отфильтрованных в п.п. 3 и 4 записей с применением встроенных функций над БД.
Примечание. Во время защиты ЛР надо показать последовательность ваших действий при получении результата.
Методические рекомендации по выполнению задания
Общие сведения
С электронными таблицами Excel можно выполнять целый ряд действий, характерных для работы с базами данных (БД). При этом столбцы будем называть полями, а строки – записями.
Перед построением БД несколько полезных советов:
Ø расположите БД так, чтобы справа и слева был хотя бы один пустой столбец, а сверху (над заголовками) и снизу – пустая строка. Это позволит легко выделять всю непоименованную БД: нужно выделить любую ячейку и нажать комбинацию клавиш <Ctrl>+<Shift>+<*>;
Ø формулы, важные данные, диапазон критериев не располагайте слева или справа от БД. В режиме фильтрации диапазон ячеек слева или справа может оказаться скрытым, а вместе с ним будут скрыты и нужные данные;
Ø разумнее всего размещать на одном листе только одну БД;
Ø не рекомендуется размещать информацию любого типа под неограниченным диапазоном для извлечения – извлекаемые данные могут наложиться на нее и затереть;
|
|
|
Ø заранее предусмотрите достаточно места, то есть пустых срок, для расширения БД. При добавлении записей с использованием формы записи помещаются в конец БД, но (!) ячейки под БД, возможно, непустые, не передвигаются вниз, и если не окажется места для расширения, форма данных не позволит вставлять записи;
Ø если БД присвоено стандартное имя База_данных или некоторое другое, следует добавлять записи внутрь диапазона или использовать для этих целей форму данных. Потому что, если добавить строку ниже диапазона базы данных вручную, необходимо будет переопределять ее имя с указанием нового диапазона.
Основные работы, выполняемые с БД, следующие:
- сортировка,
- поиск записей по критерию,
- обработка записей, удовлетворяющих критерию.
Сортировка
Упорядочение записей по возрастанию или по убыванию принятого признака (или нескольких) в выбранных полях БД выполняется с помощью пункта меню Данные–Сортировка…
А) Сортировка элементов столбцов (перемещаются строки–записи) выполняется так:
4 вызвать на экран БД, курсор установить в любую ячейку,
|
|
|
4 Данные–Сортировка… на экране диалоговое окно Сортировка диапазона,
4 указать имена полей и направление сортировки,
4 ОК, на экране результат сортировки.
Б)Сортировка элементов строк (перемещаются в таблице столбцы) выполняется в следующем порядке:
4 вызвать на экран БД,
4 выделить сортируемую часть БД (все или часть строк),
4 Данные–Сортировка–Параметры…
4 Сортировать Столбцы (!) диапазона, ОК
4 Ввести номер строки электронной таблицы (а не порядковый номер записи в БД) и направление сортировки, ОК.
На экране результат сортировки.
В) Сортировка с помощью кнопок панели инструментов
С помощью кнопок можно выполнить сортировку по элементам только одного столбца, то есть по одному полю, но процедуру можно выполнять многократно. При такой последовательной сортировке нужно помнить, что на втором, третьем и т.д. шагах сортировки упорядочиваются уже отсортированные записи, поэтому назначать ключи сортировки нужно от младшего признака к старшему. Например, если ключи сортировки факультет- курс-группа, то сортировать нужно последовательно по полям группа-курс-факультет.
Возврат к первоначальному порядку записей бывает нужен, если сортировка была выполнена ошибочно, или если для печати нужна прежняя последовательность записей БД.
|
|
|
В таких случаях в БД нужно ввести еще один столбец – индекс. Индекс – это специальное поле, содержащее порядковый номер записи (как правило, в первом или последнем поле записи). Сортировка записей БД по этому полю приводит к первоначальной последовательности записей.
 |
Поиск
Поиск в БД производится только по полям, то есть по столбцам таблицы. Если же нужно производить поиск по строкам, то предварительно таблицу нужно транспонировать, в результате чего строки станут столбцами (а столбцы – строками). В такой транспонированной таблице можно выполнить заданный поиск записей.
Транспонирование базы данных в Excel-2003
4 Вызвать на экран БД,
4 Выделить всю БД (или нужную часть),
4 Правка–Копировать… БД копируется в Буфер Обмена,
4 Установить курсор в левый верхний угол места расположения новой, транспонированной БД,
4 Правка–Специальная вставка…
4 В диалоговом окне выбрать Транспонировать
4 ОК.
На листе на новом месте получим результат транспонирования.
Транспонирование базы данных в Excel-2007
4 Вызвать на экран БД,
|
|
|
4 Выделить на свободном месте диапазон ячеек для результата транспонирования,
4 Записать формулу с использованием функции ТРАНСП(массив) - ОК - в диалоговом окне функции задать диапазон транспонируемой таблицы/диапазона - нажать одновременно CTRL+SHIFT+ВВОД (вместо ОК).
Функция ТРАНСП должна быть введена в виде формулы массива (Формула массива. Формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ВВОД.) в интервал, который имеет столько же строк и столбцов, сколько столбцов и строк соответственно имеет исходный массив.
4 На листе на новом месте получим результат транспонирования
Поиск в БД часто называют фильтрацией записей, поэтому средство поиска в Excel получило название фильтров. При этом записи должны удовлетворять критерию поиска (фильтрации). Для удобства использования фильтров критерий поиска формируется в отдельной части таблицы, и в диалоговом окне фильтра указываются координаты ячеек с критерием.
В Excel предусмотрено два инструмента поиска:
1.автофильтр;
2.расширенный фильтр.
Поиск с автофильтром
4 вызвать на экран БД,
4 курсор установить в любую ячейку БД,
4 Данные–Фильтр или (Автофильтр в Excel-2003). На экране в каждом имени поля справа появилась стрелка,
4 установить курсор на стрелку того поля, по которому производится поиск, и щелкнуть левой кнопкой; на экране – перечень значений всех записей в этом поле, а также пункт "Все", позволяющий получить на экране все записи после неудачно выполненного поиска и повторить процедуру, и пункт "Текстовый фильтр", после щелчка на котором появляется диалоговое окно Пользовательский автофильтр (см. ниже),
4 ввести значение, по которому нужно выполнить поиск; на экране – все записи, удовлетворяющие введенному критерию с пометкой в верхней строке в виде стрелки; этот символ напоминает, что записи отображены не все, а только отфильтрованные, а при нажатии на стрелку можно получить список условий поиска и выбрать "Все" для отказа от фильтрации,
4 если требуется выполнить поиск по сложному логическому критерию, то в диалоговом окне Пользовательскийавтофильтр выбрать кнопку Условие…; на экране – диалоговое окно Пользовательский автофильтр,
4 ввести для одного или двух простых критериев поиска значения полей и условия фильтрации, для их объединения использовать логическую операцию И или ИЛИ; при этом могут использоваться символы шаблона "?" и "*".
Такой поиск можно последовательно выполнять по нескольким полям, при этом результаты каждого шага немедленно отображаются на экране.
Восстановление исходной БД
В нашем распоряжении имеются разные способы восстановления всей таблицы:
1.Щелчок на стрелке любого поля,
2.Выбрать в списке "Все";
или
1. Данные – Фильтр – Показать все; на экране – БД со стрелками;
или
1. Данные – Фильтр( – Автофильтр в Excel2003); на экране – исходная БД.
 |
Поиск с расширенным фильтром
Этот режим позволяет выполнять следующие действия:
¨ одновременный поиск по нескольким полям,
¨ поиск по вычисляемому критерию.
Одновременный поиск выполняется так:
4 вызвать на экран БД,
4 в отдельных ячейках сформировать критерий поиска; для примера назовем их D30:E32 и запишем в них критерий поиска по двум полям:
| Критерий | ||
| Номер строки | Столбец D | Столбец Е |
| 30 | Дата поставки | Цена |
| 31 | >=13.09.99 | |
| 32 | <500 | |
где "Дата поставки" и "Цена" это наименования полей из БД (это обязательное условие, так как поля БД Excel опознает по их именам),
4 определить для себя ячейки на листе рабочей книги для представления результата поиска, так как он будет скопирован в новое место БД; курсор поместить в любую ячейку БД,
4 Данные – Фильтр – Расширенный фильтр в Excel 2003 или Данные-в разделе Сортировка и фильтр кнопка Дополнительно… в Excel 2007; на экране – диалоговое окно Расширенный фильтр,
4 заполнить поля запроса – исходный диапазон, диапазон критериев (указать созданный критерий), диапазон результата, выбирать только уникальные записи или с повторением значений полей; ОК.
На экране – результат фильтрации БД.
При формировании критерия нужно учитывать следующее. Если каждое условие занимает отдельную строку, то считается, что условия связаны логическим ИЛИ (как в примере выше), если же они записаны в одну строку, то их соединяет операция И.
Когда мы выбираем записи с заданным диапазоном значений одного поля (например: 500<Цена<800), то в Критерии имя поля повторяется
Операция ИЛИ Операция И
| Тип | Единица измерения | Цена | Тип | Тип | ||
| Кондитерские изделия |
| <500 | Кондитерские изделия | Кондитерские изделия | ||
| Приправы |
| >600 | >500 | <800 |
Вычисляемый критерий вводится как обычный критерий при работе с расширенным фильтром, но он может включать формулы, в которых аргументами являются поля БД и константы. При этом формулы могут быть составлены пользователем, и могут включать также встроенные функции Excel. При использовании формул в Критерии каждая из них должна иметь собственное уникальное имя, не совпадающее ни с одним именем полей БД, а строкой ниже записывается формула (начиная со знака =), включающая поля БД как операнды. После ее вычисления для полей первой записи БД вместо нее с ячейку будет записан результат – ЛОЖЬ или ИСТИНА. Дальше работа проводится так же как для предыдущего случая.
 |
s Контрольные вопросы для самопроверки:
1. Какие требования предъявляются к таблице при использовании операций БД?
2. Какие инструменты предусмотрены для выполнения сортировки?
3. Как выполнить сортировку по нескольким полям (признакам)? Как отсортировать по 4-м и более полям?
4. Как сортировать (размещать в другом порядке) столбцы?
5. Можно использовать Текстовый фильтр для сортировки по числовым значениям поля?
6. Как вернуть к исходному виду таблицу после фильтрации?
7. Какие параметры нужно подготовить и задать Расширенному фильтру для получения результата на новом месте в таблице?
8. Как подготовить вычисляемый критерий?
9. Что значит «фильтровать только уникальные записи»?
10. Какие встроенные функции имеются в нашем распоряжении для работы с базами данных?
11. В чем особенность обработки записей БД, полученных после фильтрации?
Дата добавления: 2018-04-15; просмотров: 314; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!