Ввод и просмотр данных в таблице

В Excel базы данных размещаются в таблицах. Каждая таблица состоит из строк и столбцов, которые в базах данных называются записями и полями, соответственно.

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

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

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

В качестве примера на рисунке 1 показана форма для базы данных “Адресно-телефонная книга”, которая предназначена для работы с информацией о сотрудниках фирмы.

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

Помимо полей форма содержит ряд кнопок и индикатор номера записи.

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

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

Удаленные записи не могут быть восстановлены.

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

После того как вы покинете измененную запись, вы не сможете восстановить ее содержимое.

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

Кнопка Критерии позволяет вам ограничить количество записей отображаемых в форме.

Кнопка Закрыть закрывает форму.

 

 

Рисунок 1 - Окно диалога “Форма”

 

Индикатор номера записи показывает номер отображаемой записи и общее количество записей в базе данных. Если при работе с базой данных вы переместитесь ниже последней записи, индикатор будет отображать сообщение “Новая запись”.

Для перемещения в форме используются стандартные средства. Наиболее просто выбор полей и кнопок формы осуществляется с помощью мыши. Если вы предпочитаете использовать клавиатуру, для перемещения на следующее поле или кнопку формы нажмите клавишу Tab. Для перемещения в обратном направлении воспользуйтесь Shift+Tab.

Каждому имени поля Excel автоматически назначает оперативную клавишу, позволяющую быстро перейти в требуемое поле. Символ в имени поля, соответствующий оперативной клавише, отображается подчеркнутым. Например, для перемещения в поле “Улица” нажмите Alt+л, а в поле “Дата рождения” — Alt+д.

Excel не чувствителен к регистру букв, вводимых при использовании оперативной клавиши. Переход в поле “Улица” осуществляется независимо от того, нажмете вы Alt+л или Alt+Л.

В качестве примера создания и редактирования базы данных обратимся к “Адресно-телефонной книге”.

В этом примере вам предстоит определить поля базы данных и ввести данные в таблицу.

Прежде всего определим название базы данных. Для этого введите в ячейку A1 текст: Адресно-телефонная книга.

Во второй строке введите названия полей базы данных: № п/п, Фамилия, Имя, Отчество, Дата рождения, Возраст, Начало работы, Стаж работы, Индекс, Улица, Дом, Квартира, Телефон. Значения в полях “Возраст” и “Стаж работы” будут вычисляться на основании информации, содержащейся в полях “Дата рождения” и “Начало работы”, соответственно.

Во второй строке на рисунке 2 вы можете видеть названия полей базы данных. Если при работе с базой данных вам потребуется добавить новое поле, вы сможете сделать это в дальнейшем. Для этого нужно ввести еще один столбец в таблицу

Чтобы столбец без заголовка появился в форме, перед выполнением команды Данные | Форма выделите все ячейки в строке заголовков. В поле имени этого столбца в окне диалога “Форма” появится поле без названия.

Определите выражения для вычисляемых полей. Для этого введите в ячейки F3 и H3 следующие формулы для вычисления возраста и стажа работы:

=СЕГОДНЯ()-E3+1

=ДОЛЯГОДА(G3;СЕГОДНЯ())

Теперь вы можете ввести информацию в базу данных. Выберите Данные | Форма, в результате чего на экране появится окно диалога “Форма” (рисунок 7.3). Отметьте, что горизонтальное расположение полей более удобно при вводе данных. Доступ к вычисляемым полям закрыт.

 

 

Рисунок 2 - Поля базы данных “Адресно-телефонная книга”

 

Необходимость в распространении формул на ячейки столбцов F и H отсутствует. Excel автоматически распространит формулы по мере ввода данных.

Для ввода следующей записи нажмите кнопку Добавить. Все поля в форме очистятся и индикатор номера записи будет содержать сообщение: “Новая запись”. Теперь вы можете ввести данные в следующую запись.

При вводе данных вы можете не заботиться об упорядочении записей по алфавиту или по какому-либо иному признаку. Средства Excel позволяют осуществить сортировку данных по любому признаку. Сортировка данных будет подробно рассмотрена в следующем разделе.

После сортировки у вас может возникнуть необходимость возвратиться к исходному порядку записей в базе данных. В этом случае поступают следующим образом. В Адресно-телефонной книге содержится 30 записей. После ввода последней записи нажмите кнопку Закрыть или клавишу Esc, в результате чего вы возвратитесь к табличному представлению данных. В столбец с заголовком “№ п/п” введите номера, начиная с 1. Теперь все строки таблицы пронумерованы. Какую бы сортировку базы данных вы ни проводили, вам всегда удастся вернуться к исходному порядку, отсортировав записи по столбцу с их порядковыми номерами.

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

Предположим, что часть ваших сотрудников проживают на проспекте Октябрьской Революции (ПОР). После ввода данных вы обнаружили, что в некоторых записях после буквы “П” стоит “.” (точка), а в других — нет. Для просмотра требуемых записей и исправлений выполните следующие действия:

Нажмите кнопку Критерии, в результате чего вы перейдете в режим формирования критерия поиска. При этом поля формы очистятся, а название кнопки Критерии изменится на Форма.

С помощью клавиши Tab перейдите в поле “Улица”.

Введите букву п или П. Регистр значения не имеет.

Нажмите клавишу Enter или кнопку Форма для возврата в режим просмотра и редактирования данных.

Теперь в форме отображаются только те записи, у которых названия улиц начинается с буквы п или П. С помощью клавиш Далее и Назад найдите в поле “Улица” значение “ПОР”.

Переместитесь в поле “Улица” при помощи мыши или клавиши Tab.

Введите точку после буквы “П” и нажмите Enter.

С помощью клавиш Далее и Назад найдите все ошибочные записи и исправьте их.

В рассмотренном примере мы воспользовались выбором записей, удовлетворяющих заданному критерию. При этом критерий был достаточно прост: найти записи, у который названия улиц начинаются с буквы “П”. Теперь выберем записи об определенной возрастной категории работников. В этом случае вам придется воспользоваться более сложным критерием.

Предположим, что нам нужно просмотреть данные о сотрудниках в возрасте от 30 до 40 лет и фамилии начинаются на букву “С”. Для этого выполните следующие действия:

Для перехода в режим ввода критерия выборки нажмите кнопку Критерии.

Перейдите в поле “Фамилия”, нажав Alt+а. В этом случае вы используете оперативную клавишу. (Буква “а” в названии поля “Фамилия” подчеркнута.)

Введите букву с или С. Это условие выберет записи о сотрудниках, у которых фамилия начинается на букву “С”. Результат выбора не зависит от того, какую букву (прописную или строчную) вы ввели при задании критерия.

С помощью клавиши Tab перейдите в поле “Дата рождения”. Введите условие >01.01.55. Это условие выберет записи о сотрудниках, чей возраст не превышает 40 лет.

Перейдите в поле “Возраст”. Введите условие >01.01.30, которое выберет записи о сотрудниках старше 30 лет.

Для перехода в режим просмотра данных нажмите клавишу Enter.

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

Для просмотра других записей воспользуйтесь клавишей Enter или клавишами-стрелками и. Кроме того, вы можете использовать полосу прокрутки. Если вы вновь хотите просмотреть записи, удовлетворяющие введенным критериям, воспользуйтесь клавишами Далее и Назад.

Если записи, удовлетворяющие критериям поиска, упорно не появляются, а вы точно знаете, что они присутствуют в базе данных, проверьте в русском ли регистре вы ввели букву “С”.

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

 

Сортировка базы данных

 

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

Команда Данные | Сортировка устанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов.

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

Числа

Текст, включая текст с числами (почтовые индексы, номера автомашин)

Логические значения

Значения ошибок

Пустые ячейки

Сортировка по убыванию происходит в обратном порядке. Исключением являются пустые ячейки, которые всегда располагаются в конце списка.

Тип данных влияет на результат сортировки. Для получения правильного результата все ячейки в столбце должны содержат один и тот же тип данных. Введите данные как числа или как текст, но избегайте ситуации, когда в ячейках одного столбца содержатся числа и текст.

При выборе Данные | Сортировка открывается окно диалога “Сортировка диапазона”, которое позволяет вам указать поля для сортировки и определить критерий сортировки (рисунок 3). С помощью раскрывающегося списка Сортировать по вы можете выбрать столбец для сортировки. Порядок сортировки устанавливается переключателями по возрастанию или по убыванию.

При сортировке по возрастанию текстовые данные упорядочиваются в алфавитном порядке от А к Я. Числовые данные упорядочиваются по возрастанию значений от минимального к максимальному. Даты упорядочиваются от наиболее ранней даты к наиболее поздней. При выборе переключателя по убыванию порядок сортировки изменяется на противоположный.

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

Переключатель Строка меток столбцов указывает, имеется ли в сортируемом списке строка заголовков, которую нужно исключить из сортировки. При наличии в списке меток столбцов выберите переключатель Есть, в противном случае — Нет.

Окно диалога содержит кнопку Параметры, в результате нажатия которой открывается окно диалога “Параметры сортировки” (рисунок 4). С помощью этого окна вы можете:

определить пользовательский порядок сортировки для столбца, указанного в раскрывающемся списке Сортировать по;

сделать сортировку чувствительной к использованию прописных и строчных букв;

изменить направление сортировки (вместо сортировки сверху вниз установить сортировку слева направо).

В “Адресно-телефонной книге” сотрудники фирмы записаны в алфавитном порядке. В этом примере вам предстоит расположить их в порядке убывания стажа работы.

Рисунок 3 - Окно диалога “Сортировка диапазона”

 

Рисунок 4 - Окно диалога “Параметры сортировки”

 

 

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

Установите маркер на ячейку “Адресно-телефонной книги” и выберите Данные | Сортировка. Появится окно диалога “Сортировка диапазона”.

В раскрывающемся списке Сортировать по выберите поле “Стаж работы”.

Установите переключатель в положение по убыванию.

Окно диалога “Сортировка диапазона” с установленными параметрами сортировки представлено на рисунке 3.

Нажмите кнопку OK.

На рисунке 5 показана “Адресно-телефонная книга” после сортировки. Записи в ней расположены в порядке убывания стажа работы сотрудников. Обратите внимание на поле “№ п/п”. Восстановить исходный порядок сортировки данных можно при помощи сортировки текущей базы по полю “№ п/п” с переключателем по возрастанию.

 

 

Рисунок 5 - База данных после сортировки

 

В данном конкретном случае исходный порядок записей можно восстановить сортировкой в порядке возрастания по полю “Фамилия”. Нумерация по порядку в “Адресно-телефонной книге” была введена после ввода данных в алфавитном порядке.

 

Фильтрация данных в списке

 

В Excel списком называется снабженная метками последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа.

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

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

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

Создайте метки столбцов перед использованием команд фильтрации.

Список в Excel можно рассматривать как базу данных.

Команда Данные | Фильтр | Автофильтр помещает кнопки раскрывающихся списков (кнопки со стрелкой) непосредственно в строку с метками столбцов (рисунок 6).

При помощи этих кнопок вы можете выбрать строки таблицы, которые необходимо вывести на экран. Нажмите на кнопку с изображением стрелки в поле “Индекс”. Появится раскрывающийся список этого поля (рисунок 6).

 

Рисунок 6 - Раскрывающийся список поля “Индекс”

 

В раскрывающемся списке поля выводятся все значения встречающиеся в столбце. В результате выбора значения (например “335038”) на экран выводятся все строки, содержащие значение “335038” в столбце “Индекс”.

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

Вы можете применять пользовательские критерии сравнения для фильтрации данных в списке. Эта операция осуществляется выбором команды Условие.

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

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

При фильтрации данных с использованием команды Автофильтр вы можете определить в окне диалога “Пользовательский автофильтр” один или два критерия сравнения для одного и того же столбца. Excel сравнит элементы списка с введенными в окне диалога значениями и выведет лишь те строки, которые удовлетворяют заданному критерию. Можно также воспользоваться операторами И/ИЛИ для объединения или сравнения критериев для одного и того же столбца.

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

Кроме того, для расширения возможностей поиска вхождений текста вы можете использовать символы шаблона “?” и “*”.

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

Для определения действия двух критериев установите переключатель “И” или “ИЛИ”

Если установлен переключатель “И”, то на экран будут выводиться только те строки, для которых выполняется каждый из двух критериев. Если установлен переключатель “ИЛИ”, то на экран будут выводиться все строки, для которых выполняется хотя бы один из двух критериев.

 

Рисунок 7 - Окно определения критерия

 

Отметим одну деталь. Если вам нужно сбросить один из фильтров, то войдите в поле, в котором он установлен, и нажмите на кнопку раскрытия списка. В этом списке нажмите мышью на строку Все. Параметры фильтра в этом поле будут сброшены.