Ввод данных в список

Лекция 6: Создание, ввод и обработка баз данных в MS Excel.

Цель: Рассмотреть этапы создания, способы ввода и методы обработки баз данных в MS Excel.

План.

Определение базы данных в Excel.

Ввод данных в список.

Работа с записями.

Поиск записей.

Сортировка данных в списках (самостоятельная работа).

Создание пользовательского списка (самостоятельная работа).

Фильтрация.

Отбор данных с помощью операторов (самостоятельная работа).

Использование при фильтрации данных символов подстановки (самостоятельная работа).

Выполнение более сложного отбора.

Ход лекции.

 

Определение базы данных в Excel.

Одной из типичных задач, выполняемых с помощью электронных таблиц, является ведение списков — списков номеров телефонов, клиентов, торговых операций, материальных ценностей и т. д. Базы данных в Excel часто называют списком.

Конечно, списки Excel несравнимы по объему и возможностям с профессиональными системами управления базами данных. Но если объем информации невелик и стабилен (не имеет тенденции к бурному возрастанию), то наличие в Excel специальных функций и команд для управления такой базой упрощает работу. Microsoft Excel имеет самый богатый набор средств для работы со списками по сравнению с любой другой программой электронных таблиц, что позволяет легко создавать, вести и анализировать такого рода информацию.

Список — это ряд строк, в которых содержатся определенный набор данных (рис. 1).

Каждому такому множеству строк предшествует строка меток-идентификаторов. Преимущество списка заключается в возможности гибко им манипулировать: искать данные, удовлетворяющие определенным условиям, отбрасывая при этом остальные данные, ненужные в данный момент. Так, в списке персонала можно найти сотрудника по фамилии Василенко или всех, кто зарабатывает больше 5 тыс. в год. Можно также отсортировать список множеством способов — например, расположив его элементы в алфавитном порядке.

 

 

Рисунок 1 – Список данных в MS Excel.

 

Максимальный размер списка, создаваемого в Excel, ограничен размером одного рабочего листа. Он может содержать не более 256 полей и не более 65535 записей (одна строк предназначена для имен полей). Список такого размера требует огромного количества памяти и, иногда, его даже невозможно отобразить.

Существует несколько правил для создания списка:

· Рабочий лист должен иметь свое уникальное имя.

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

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

· Информация в списках должна иметь постоянную структуру.

· Нельзя включать в список пустые строки и столбцы.

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

· Нельзя размещать данные слева или справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка.

· Не следует вводить лишние пробелы или какие-либо знаки перед данными в ячейке.

· Первую строку с именами полей желательно закрепить, выполнив команду Окно – Закрепить область.

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

Ввод данных в список.

Для непосредственного ввода информации в список можно поступать стандартно: выделить ячейку, ввести данные и нажать клавишу Enter.

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

Рисунок 2 – Окно Формы.

 

Для второго способа ввода важно присвоить имя рабочему листу (в примере Курс1Аттестация1), на котором будет размещаться список, и имена полям списка. Имя рабочего листа определяет заголовок диалогового окна (а не имя книги), которое вызывается командой Форма.

В левой части диалогового окна перечислены имена каждого столбца в списке, наиме­нование которого определяется заголовком столбца. Если в список уже введено несколько строк, то справа от заголовков столбцов стоят данные для первой строки списка. Помимо текстовых по­лей, содержащих исходные значения, в форме могут присутствовать вычисляе­мые поля, с помощью которых можно выполнять разнообразные вычис­ления. Примером таких полей служат поля № и Средний бал (см. рис. 2). Значения этих полей Excel заполняет автоматиче­ски, используя введенные в списке формулы, на основании информации, которая уже содержится в базе данных. Однако нельзя ни ввести, ни из­менить значения в вычисляемых полях, т.к. в режиме редактирования они в Форме не отображаются. Кроме того, можно самостоятельно, определить некоторые из полей таблицы в качестве не редактируемых, т. е. неотображаемых.

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

Для добавления новой записи выполнить следующие действия:

1. Выполнить команду Данные - Форма.

2. Нажать кнопку Добавить.

3. В появившемся диалоговом окне заполнить текстовые поля.

4.Нажать кнопку Добавить или Enter для добавления новой записи списка.

5. Повторять шаги 3 и 4, пока не будут введены все данные.

6. По окончании ввода нажать клавишу Закрыть.

Для перемещения между полями и между записями с помощью Формы можно использовать такие клавиши:

Клавиша / элемент окна	Действие
Tab Shift+Tab Enter Esc Ctrl+PgUp Ctrl+PgDn PgUp или кнопка Предыдущая PgDn или кнопка Следующая Полоса прокрутки	Перемещение к следующему полю Перемещение к предыдущему полю Конец ввода очередной записи Выход из диалогового окна Форма Перемещение к первой записи Перемещение за последнюю запись Перемещение к предыдущей записи Перемещение к следующей записи Перемещение от записи к записи

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

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

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

Если при работе используется Microsoft Access, то можно создать формы в Access для ввода данных в список Excel. По сравнению со стандартными формами Excel формы Access предоставляют некоторые дополнительные возможности. Например, при работе с формами Access можно задавать условия, используемые для проверки вводимых данных.

 

Работа с записями.

Записи в список добавляются и редактируются путем ввода данных непосредственно в ячейки, а удаляются - путем их выделения и выполнения команды Правка - Удалить. Однако эти же действия можно сделать и с помощью Формы.

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

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

Для удаления записи ее предварительно необходимо найти, затем нажать кнопку Удалить.  Excel перед удалением записи еще раз запра­шивает разрешение.

Для отмены изменений используется кнопка Восстановить.

Для завершения работы с Формой нажать кнопу Завершить или Esc.

 

Поиск записей.

Нужные записи можно найти, просматривая список с помощью кнопок Назад, Далее. Однако можно их определить и с помощью кнопки Критерий. После нажатия ее открывается пустая Форма, и в полях необходимо ввести условия, которым должны удовлетворять разыскиваемые записи.

Описывая условия, можно использовать операторы сравнения: = (равно), <> (не равно), < (меньше), <= (меньше или равно), > (больше), >= (больше или равно).

В критериях можно использовать символы шаблонов * и?:? - обозначает любой, но только один символ; * - обозначает любое количество символов.

Например, критерий О???, введенный в поле Имя, позволяет опре­делить имена, состоящие из точно четырех букв и начинающиеся на букву О. Критерий О* определяет слова, начинающиеся на О (такие как Олексій, Олександр, в том числе и Олег).

При поиске определенных записей Excel не различает строчные и прописные буквы.

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

1. Выполнить команду Данные - Форма.

2. Нажать кнопку Критерии.

3. Ввести условие поиска в соответствующие поля. Необходимо заполнить те поля, по которым будет осуществляться поиск.

4. Нажать Enter для того, чтобы вернуться к исходной Форме.

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

Завершив просмотр записей, нажать кнопку Закрыть.

Например, необходимо найти все записи студентов с именем начинающимся на О. Критерий поиска приведен на рис. 3.

Рисунок 3 – Окно задания критериев просмотра.

 

Если же надо организовать поиск на основе нескольких критериев, то в этом случае надо использовать логическую операцию И. Операция логическое И в Форме реализована по умолчанию: просто нужно несколько полей заполнить своими условиями. По смыслу эти условия должны выполняться одновременно, т.е. они должны быть связаны логическим И.

К сожалению, критерий с логическим ИЛИ в Форме указывать невозможно. Однако по таким критериям можно организовывать поиск с помощью команды Автофильтр.

Например, пусть необходимо найти всех студентов, у которых оценки по Математике и Физике больше 6 баллов. Критерий поиска приведен на рис. 4.

Рисунок 4 – Окно задания нескольких критериев.

 

Чтобы снова получить возможность просмотра всех записей, необходимо нажать на кнопке Критерии и затем — на кнопке Очистить, в результате чего все критерии будут отменены. После этого нажать на кнопке Вернуть. Теперь можно, используя кнопки Далее и Назад, просматривать все записи в списке.