Гипермаркет знаний>>Информатика>>Информатика 11 класс>> Информатика: Основные понятия баз данных

Конспект урока к предмету «Інформатика 11 клас» по теме «Базы данных в Excel»

Урок 6. Базы данных в Excel

6.1. Создание Базы Данных
6.2. Сортировка данных
6.3. Формы данных
6.4. Установление диапазона критериев фильтрации
6.5. Автофильтр
6.6. Расширенный фильтр

6.1. Создание Базы данных

База данных (БД) – таблица, состоящая из однотипных записей (строк). Информация в БД имеет постоянную структуру. Программа Excel позволяет содавать простые реляционные (табличные) БД. Столбцы таблицы являются полями записи в БД. Под имена полей выделяется первая строка таблицы. Например, если БД считать сведения об учениках класса (школы), то полями записи могут быть N, Фамилия,т Имя, Отчество, Адрес, Телефон, Дата рождения и т.д. Для работы с базой данных необходимо сначала создать соответствующую таблицу. Если выделить ячейку в таблице и выбрать одну из команд обработки баз данных в меню Данные, Excel автоматически определяет и обрабатывает всю таблицу. Данные, расположенные в столбцах и строках рабочего листа, обрабатываются как набор полей, которые образуют записи.


 
6.2. Сортировка данных  

Сортировка позволяет переупорядочить строки в таблице по любому полю. Например, чтобы отсортировать данные по Фамилии. Для сортировки данных следует:

1. выделить одну ячейку таблицы и вызвать команду Сортировка меню Данные;
2. в поле списка Сортировать по выбрать поле, по которому будут отсортированы данные, и тип сортировки: по возрастанию – цифры сортируются по возрастанию, текст – в алфавитном порядке. По убыванию – сортировка в обратном порядке;
3. в поле Затем по указать поле, по которому будут отсортированы данные, имеющие одинаковые значения в первом ключевом поле;
4. в в поле В последнюю очередь по указаь поле, по которому будут отсортированы данные, имеющие одинаковые значения в первых двух ключевых полях.


 
Для сортировки данных также используются кнопки Сортировать по возрастанию и . Сортировать по убыванию. Перед их использованием следует выделить столбец, по которому необходимо сортировать записи.

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

6.3. Формы данных

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


 
Имена столбцов становятся именами полей в форме данных. Форма данных автоматически раскрывается так, чтобы вывести на экран сразу все поля в данной таблице, до 32 полей за один раз. С помощью полосы прокрутки можно прокручивать записи базы данных. Позиция выведенной записи указывается в верхнем правом углу. Передвигаться по полям формы можно с помощью мыши и клавиш Tab (вниз), Shift+Tab (вверх). В правой части окна расположены следующие кнопки.

• Добавить – очищает поля для ввода новой записи базы данных. Если снова щелкнуть кнопку Добавить , то введенные данные будут добавлены, как новая запись, в конец базы данных.

• Удалить – удаляет выведенную запись, другие записи базы данных сдвигаются. Удаленные записи не могут быть восстановлены.

• Вернуть – восстанавливает отредактированные поля в выведенной записи, удаляя сделанные изменения. Чтобы восстановить запись, необходимо сделать это перед нажатием клавиши Enter или перед переходом к другой записи.

• Назад – выводит предыдущую запись в списке. Если был определен критерий с помощью кнопки Критерии , то кнопка Назад выведет предыдущую запись из тех, которые удовлетворяют заданному критерию.

• Далее – выводит следующую запись базы данных.

• Критерии – очищает поля перед вводом критериев сравнения с операторами сравнения для поиска необходимого подмножества записей.

• Правка – служит для выхода из режима ввода критериев. Доступна только после нажатия кнопки Критерии.

• Очистить – удаляет существующий критерий из окна диалога. Доступна только после нажатия кнопки Критерии.

• Закрыть – закрывает форму данных.

Для добавления записи к базе данных необходимо:

1. выделить ячейку в таблице, к которой следует добавить запись;
2. в меню Данные выбрать команду Форма;
3. щелкнуть кнопку Добавить;
4. заполнить поля новой записи;
5. для перемещения к следующему полю нажать клавишу Тab;
6. после ввода данных нажать клавишу Enter для добавления записи;
7. после добавления всех необходимых записей, щелкнуть кнопку Закрыть.

Новые записи будут добавлены в конец базы данных.

6.4. Установление диапазона критериев фильтрации

Фильтрация - выделение из БД данных, отвечающих некоторому критерию. Критерии бывают двух типов.

1. Критерии вычисления – это критерии, которые являются результатом вычисления формулы. Например, диапазон критериев =F7=СУММ($В$2:$В$22) выводит на экран строки, имеющие в столбце F значения большие, чем сумма величин в ячейках В2:В22. Формула должна представлять из себя логическое выражение (условие). При фильтрации будут доступные только те строки, значения которых будут придавать формуле значения ИСТИНА .

2. Критерии сравнения – это набор условий для поиска, используемый для извлечения данных при запросах по примеру. Критерий сравнения может быть последовательностью символов (константой) или выражением (например, Цена > 700 ).

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

1. выделить ячейку в таблице;
2. в меню Данные выбрать команду Форма;
3. щелкнуть кнопку Критерии;
4. в полях редактирования ввести критерии для поиска данных;
5. для вывода на экран первой записи, отвечающей критерию, щелкнуть кнопку Далее;
6. для вывода на экран предыдущей записи, отвечающей критерию, щелкнуть кнопку Назад;
7. для поиска записей в списке по другим критериям щелкнуть кнопку Критерии и ввести новые критерии;
8. по окончанию щелкнуть кнопку Закрыть.

Чтобы снова получить доступ ко всем записям таблицы, необходимо щелкнуть кнопку Критерии , а затем – кнопку Правка.

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

6.5. Автофильтр

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов. С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты. Например, если в скрытом списке поля Дата рождения , то будут выведены только записи, у которых в соответствующем поле содержится значение 07-апр-88.


 
Если в поле списка выбрать пункт Условие … , то появится окно Пользовательский автофильтр.


 
В верхнем правом списке следует выбрать один из операторов (равно, больше, меньше и др.), в поле справа – выбрать одно из значений. В нижнем правом списке можно выбрать другой оператор, и в поле справа – значение. Когда включен переключатель И, то будут выводиться только записи, удовлетворяющие обеим условиям. При включенном переключателе ИЛИ будут выводиться записи, удовлетворяющие одному из условий.

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


 
Чтобы вывести все данные таблицы, необходимо вызвать команду Отобразить все или отменить команду Автофильтр.

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

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

• фильтровать список на месте – переключатель, скрывающий строки, которые не удовлетворяют указанному критерию;

• скопировать результат в другое место – копирует отфильтрованные данные на другой рабочий лист или на другое место на этом же рабочем листе;

• Исходный диапазон – поле, определяющее диапазон, который содержит список, подлежащий фильтрации;

• Диапазон условий – поле, определяющее диапазон ячеек на рабочем листе, который содержит необходимые условия;

• Поместить результат в диапазон – поле, определяющее диапазон ячеек, в который копируются строки, удовлетворяющие определенным условиям; это поле активно только в том случае, если выбран переключатель скопировать результат в другое место ;

• Только уникальные записи – переключатель, который выводит только строки, удовлетворяющие критерию и не содержащие неповторяющихся элементов. Если диапазон критериев не определен, то в этом случае все строки списка, содержащие дубликаты, будут скрыты.

Для установки сложных критериев необходимо:

1. вставить несколько строк в верхней части рабочего листа;
2. в одном из вставленных пустых строк ввести имена столбцов, по которым следует отфильтровать таблицу;
3. при использовании критериев сравнения, имена критериев должны быть идентичны именам столбцов, которые проверяются;
4. в строках, расположенных под строкой с именами проверяемых столбцов, ввести критерии, которым должны соответствовать ячейки проверяемых столбцов;
5. выбрать в меню Данные пункт Фильтр, затем – Расширенный фильтр, и в диалоговом окне ввести условия фильтрации.


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

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

Чтобы снова вывести все записи следует в меню Данные выбрать пункт Фильтр, затем пункт Отобразить все.

Вопросы для самоконтроля

1. Что представляет из себя БД?
2. Как создать БД в Excel?
3. Для чего нужны Формы данных?
4. Как работать с Формой данных?
5. Как настраиваются критерии поиска?
6. Как выполняется команда Автофильтр?
7. Что такое Пользовательский фильтр?
8. Что такое Расширенный фильтр?
9. Как устанавливаются сложные критерии?
10. Как отменить результаты фильтрации?

Отослано Чебаном Л.И., учителем информатики Международного лицея "Гранд"