Лабораторная работа №3 Базы данных в Excel. Теоретическая часть.

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

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

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

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

Организация ввода и просмотра данных;

Сортировка, фильтрация и консолидация данных в таблицах;

Подведение итогов и сводная таблица.

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

В 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 или клавишами-стрелками и . Кроме того, вы можете использовать полосу прокрутки. Если вы вновь хотите просмотреть записи, удовлетворяющие введенным критериям, воспользуйтесь клавишами Далее и Назад.

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

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