Урок №11. Тема: Редактирование баз данных в Excel
| Вид материала | Урок |
- Программа повышения квалификации «Использование Microsoft Excel для автоматизации бухгалтерского, 14.19kb.
- Урок №10. Тема: Работа с базами данных в Excel, 119.05kb.
- Microsoft Office Excel. Интерфейс программы. Заполнение и редактирование ячейки. Форматирование, 1488.42kb.
- Проектирование базы данных, 642.58kb.
- Вопросы для контрольной работы по дисциплине «итпд», 12.37kb.
- Программа Дата Форма занятий Тема выступления Место и время выступления 15., 22.47kb.
- Окно программы ms excel 2 Основные понятия ms excel. 2 Адреса ячеек 3 Типы данных, 742.75kb.
- Учебное пособие Допущено Министерством образования Российской Федерации в качестве, 2582.59kb.
- Программа учебной дисциплины " публикация баз данных в интернете, 81.35kb.
- Урок «Решение задач оптимизации с помощью табличного процессора Excel», 59.97kb.
УРОК №11.
Тема: Редактирование баз данных в Excel.
Цели:
- Обучающая: научить учащихся редактировать базы данных в MS Excel.
- Развивающая: продолжить обучение учащихся логически мыслить, принимать правильное решение.
- Воспитательная: способствовать воспитанию самостоятельности, активности учащихся.
Ход урока
- Организационный момент.
Проверка присутствующих и готовности, учащихся к занятию.
- Актуализация.
- Назовите элементы списка.
- Какой список называют полнофункциональным?
- Что такое диапазон базы данных?
- Какими способами можно ввести данные в базу данных?
- Как произвести сортировку полей по возрастанию (убыванию)?
- Что представляет собой сортировка в особом порядке?
- Можно ли отсортировать список по шести полям?
- Новая тема.
Поиск, фильтрация и редактирование в БД.
Одним из самых частых действий над списками в MS Excel являются поиск и фильтрация. Поиск производится на основе задаваемых пользователем критериев – требований, налагаемых на информацию. Результатом фильтрации является временное скрытие записей, не удовлетворяющих критериям. Поиск и фильтрацию данных можно осуществить с помощью формы данных, автофильтра и расширенного фильтра.
Критерии поиска.
Поиск точного соответствия. Критерии на основе сравнения позволяют находить точные соответствия или отбирать данные из соответствующего интервала. При этом математические вычисления и логические операции не выполняются. Критерии на основе сравнения можно использовать в Excel при любом способе поиска или фильтрации.
Для ввода простейшего критерия достаточно ввести в соответствующем поле искомое значение. В критериях на основе сравнения можно использовать различные операции сравнения: =, <, >, <=, >=, <>.
Поиск близкого соответствия с использованием образца. Для задания таких критериев используются символы шаблона * и ?: «*» заменяет любое количество любых символов, «?» -- один любой символ. Например, нужно выбрать все фамилии, начинающиеся ни «Ив» (Иванов, Ивлев, Иванович и т.п.). Для решения этой задачи можно использовать шаблон «Ив*».
Поиск соответствия с использованием множественного критерия с операциями И и ИЛИ. Для поиска записей, удовлетворяющих нескольким условиям, можно задавать множественные критерии. Для наложения нескольких условий поиска в Excel используются логические операции и, или.
Использование формы данных.
Форма данных в Excel – великолепное средство для поиска и редактирования отдельных записей.
Чтобы осуществить поиск записей, нужно воспользоваться командой Данные/Форма. В появившемся диалоговом окне нажать кнопку Критерии и в нужных полях задать критерии поиска. Для перехода к записям удовлетворяющих критерию нажать Далее или Назад. Найденные записи можно легко изменять в полях формы.
В форме данных можно использовать критерии поиска точного соответствия, соответствия с использованием образца. Если в критерии задать условия для нескольких полей, то будет осуществлён поиск соответствия с использованием множественного критерия с операцией И. Множественный критерий с операцией ИЛИ, а также критерии, требующие вычислений, с помощью формы данных реализовать нельзя.
Кнопки формы данных Вернуть и Удалить можно использовать для отмены внесенных в запись изменений и удаление записи соответственно.
Автофильтр.
Автофильтр предоставляет простой доступ к мощным средствам рабочего листа. Он выводит информацию на рабочем листе, при этом записи, не удовлетворяющие критерию, скрыты.
Чтобы включить Автофильтр, нужно воспользоваться командой Данные/Фильтр/Автофильтр. Excel выведет кнопки со стрелками (кнопки автофильтра) рядом с каждым заголовком столбца.
При работе с автофильтром имеется три метода фильтрации данных.
Выбор значения поля для поиска точного соответствия: нажать кнопку автофильтра и в появившемся списке выбрать требуемый вариант. Например, необходимо получить информацию о всех сотрудниках бухгалтерии. Для этого следует воспользоваться кнопкой автофильтра возле заголовка соответствующего поля и списке выбрать вариант «Бухгалтерия». На экране останутся только о сотрудниках бухгалтерии, а остальные записи будут скрыты.
Пользовательский Автофильтр: в списке, который появляется при нажатии на кнопку автофильтра, выбрать вариант. На экране появится диалоговое окно, позволяющее задавать условия отбора данных.
Чтобы снять фильтрацию с поля, нужно нажать на кнопку автофильтра и выбрать вариант Все. Можно одновременно накладывать условия на несколько полей. В этом случае условия отбора связаны операцией И, т.е. отображаться будут только те записи, для которых условия выполняются одновременно во всех полях.
Чтобы выключить Автофильтр, нужно снова воспользоваться командой Данные/Фильтр/Автофильтр.
Расширенная фильтрация с помощью диапазона критериев.
Команда Расширенный фильтр, в отличие от команды Автофильтр, имеет некоторые дополнительные возможности. Можно задавать условия, соединённые логическим оператором ИЛИ, для нескольких столбцов. Допускается задавать вычисляемые условия (например, можно вывести на экран список только тех сотрудников, у которых оклад хотя бы на 25% выше среднего). Кроме того, команда Расширенный фильтр может использоваться для извлечения строк из списка и вставки копий этих строк в другую часть текущего листа. Поскольку при фильтрации скрываются целые строки, диапазон условий лучше поместить выше или ниже списка.
Диапазон условий должен содержать, по крайней мере, две строки. Введите один или несколько заголовков столбцов в верхней строке, а условия отбора – во вторую и последующие строки. За исключением вычисляемых условий, заголовки в диапазоне условий должны точно совпадать с заголовками столбцов в списке. Для обеспечения точности эти заголовки лучше всего создавать, выделяя заголовки столбцов в списке и копируя их в верхнюю строку диапазона условий с помощью команд Копировать и Вставить меню Правка.
Имейте в виду, что в диапазон условий достаточно включить заголовки только тех столбцов, которые используются в условиях отбора.
Чтобы воспользоваться расширенным фильтром, нужно выбрать команду меню Данные/Фильтр/Расширенный фильтр. На экране появится диалоговое окно.
Флажок Только уникальные записи позволяет исключить повторяющиеся.
В том случае, когда список фильтруется на месте, не удовлетворяющие критерию строки скрываются. Чтобы вновь их отобразить, необходимо воспользоваться командой Данные/Фильтр/ Отобразить все.
Лабораторная работа №11.
Тема: Редактирование баз данных.
Цель: научиться редактировать базы данных, рассмотреть различные виды поиска данных в базе данных.
- Запустить Excel.
- Откройте файл « База данных.xls».
- П
роизведите фильтрацию значений дохода, превышающих 4800 руб. Для этого воспользуйтесь командой Данные/ Фильтр/ Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце Оклад), и вы увидите список всех повторяющихся значений этого поля. Выберите команду для фильтрации Условие. В открывшемся поле Пользовательский Автофильтр задайте условие – больше 4800.
- Произведем поиск с помощью расширенной фильтрации. Для этого под таблицей введите столбец Имя и введите туда имя Елена. Выберите пункт меню Данные/Фильтр/Автофильтр. В исходном диапазоне выделите всю таблицу, а в диапазоне условий укажите только что введенный столбец.
- Нажмите кнопку ОК, и на экране появится следующее:
Фамилия
Имя
Отчество
Отдел
Оклад
Роева
Елена
Михайловна
Бухгалтерия
6550
Шубина
Елена
Михайловна
Бухгалтерия
5200
- Для того, чтобы вернуть весь список выберите пункт меню Данные/Фильтр/Отобразить все.
- Сохраните данный файл.
- Результаты работы покажите учителю.
ЗАДАНИЕ.
- Выполнить автофильтрацию по следующим данным:
| № | Запрос |
| 1 | Получить информацию о сотрудниках двух конкретных отделов, родившихся в период (Дата1; Дата2) и принятых на работу не позднее даты Дата3 |
| 2 | Получить информацию о мужчинах, имя которых начинается на букву Буква, отчество – «Иванович», с окладом ниже значения Оклад |
| 3 | Получить информацию о женщинах, фамилии которых заканчиваются на «их» или «ко», в возрасте от 35 до 40 лет, работающих либо в отделе Отдел1, либо в Отделе2 |
| 4 | Определить, если в отделах Отдел1 и Отдел2 мужчины, размеры окладов которых относится к пяти наибольшим на всем предприятии |
| 5 | Отобразить информацию о студентах групп Группа1 и Группа2 по предмету Предмет с оценками Хорошо и Отлично |
| 6 | Найти информацию о студентах, сдавших экзамены по предметам Предмет1 и Предмет2 на оценку Отлично либо раньше даты Дата1, либо позже даты Дата2 |
| 7 | Найти двух студентов отличников с двух факультетов Факультет1 и Факультет2, родившихся в период (Дата1; Дата2) |
| 8 | Найти информацию о студентах групп Группа1 и Группа2, сдавших экзамен по предмету Предмет либо на неудовлетворительно, либо на отлично |
| 9 | Определить, читают ли лекции по предмету Предмет на факультетах Факультет1 и Факультет2 профессора |
| 10 | Определить, в каких группах читает лекции и ведет лабораторные работы преподаватель Преподаватель |
| 11 | Найти информацию о доцентах и ассистентах с фамилией Фамилия, которые проводят занятия по предмету Предмет на факультетах Факультет1 и Факультет2 |
| 12 | Найти дисциплины, изучаемые на факультетах Факультет1 и Факультет2 с максимальным количеством часов, отводимых на практические занятия |
| 13 | Отобразить информацию о сделках, проведенных менеджером Менеджер, с суммой, превышающей среднюю сумму сделки |
| 14 | Найти информацию о деятельности менеджера Менеджер1 по товару Товар1 и Менеджера2 по товару Товар2 в период (Дата1; Дата2) |
| 15 | Найти поставки от Клиентов Клиент1 и Клиент2 на суммы, равные средней сумме поставки ±N руб. |
| 16 | Отобразить информацию о сделках за период с Дата1 по Дата2, проведенных менеджерами Менеджер1, Менеджер2 и Менеджер3 по товарам Товар1, Товар2 и Товар3 на сумму, превышающую Сумма. |
| 17 | Найти поставки от поставщиков Поставщик1, Поставщик2 и Поставщик3 в период от Дата1 до Дата2 на суммы, превышающие среднюю сумму поставки в 1,2 раза |
| 18 | Найти поставки способами перевозки Способ перевозки1 и Способ перевозки2 от поставщиков Поставщик1, Поставщик2 и Поставщик3 со стоимостью перевозимого товара от Сумма1 до Сумма2 рублей |
| 19 | Определить какими способами перевозки поставлялся товар от поставщиков Поставщик1 и Поставщик2 в период с Дата1 по Дата2 |
| 20 | Определить, какие поставщики использовали способы перевозки Способ перевозки1 и Способ перевозки2 с общими транспортными расходами меньше Сумма |