Работа с базой данных в электронной таблице Microsoft Excel: методические указания к лабораторным и самостоятельным работам

Министерство образования Российской Федерации ОРЕНБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ Кафедра систем автоматизации производства

А.М.Черноусова

МЕТОДИЧЕСКИЕ УКАЗАНИЯ к лабораторным и самостоятельным работам

Работа с базой данных в электронной таблице Microsoft Excel

Оренбург 2001

ББК 32.97 Ч49 УДК 681.3

Работа с базой данных в электронной таблице Microsoft Excel 1 Цель работы Целью работы является приобретение навыков использования электронной таблицы Microsoft Excel для работы с базой данных, сортировки информации и поиска по различным критериям.

2. Общие положения 2.1 Возможности Microsoft Excel по работе с базами данных Microsoft Excel является мощным программным средством для работы с таблицами данных, позволяющим упорядочивать, анализировать и графически представлять различные виды данных. Он появился в 1987 году и быстро завоевал популярность среди пользователей, так как способен работать с различными приложениями. Электронная таблица является компьютерным эквивалентом обычной таблицы, в клетках (ячейках) которой записаны данные различных типов: тексты, даты, формулы, числа. Запуск осуществляется по команде Пуск|Программы| Microsoft Excel аналогично другим программным средствам, входящим в состав Microsoft Office. После запуска на экран монитора будет выведено главное окно программы, которое представлено на рисунке 1. Структура экрана при работе с Excel во многом схожа со структурой экрана других приложений Windows. В главном окне Excel содержится множество инструментов, кнопок, меню и других элементов, назначение и возможности использования которых описаны ниже. Строка заголовка – верхняя строка окна Excel, которая содержит имя приложения Microsoft Excel и ограничена с обеих сторон стандартными кнопками. Строка меню находиться под строкой заголовка и содержит меню электронной таблицы. При активизации пункта меню открывается ниспадающее подменю вложенных в него команд. Для активизации команды следует сначала 2

открыть меню, в котором данная команда находиться, после чего активизировать данную команду, выполнив щелчок мышью. Панели инструментов обычно располагаются под строкой меню и содержат кнопки (пиктограммы), которые предназначены для быстрой активизации (выполнения) определённых команд меню и функций программы. Назначение каждой пиктограммы можно узнать, если установить на неё указатель мыши. На рисунке 1 представлена конфигурация панелей ( "Стандартная" и "Форматирование"), предлагаемые по умолчанию. Строка формул располагается под панелями инструментов и предназначена для обработки содержимого ячеек. Она разделена на три части. Правая часть служит для отображения содержимого текущей ячейки, которое можно редактировать с помощью кнопок, расположенных в центральной части строки. Левая часть указывает адрес активной ячейки или размер диапазона.

Рисунок 1 – Главное окно программы Excel Строка состояния находится в нижней части окна программы. В левой части строки отображаются различные текстовые сообщения. В Microsoft Excel файлы носят название рабочих книг. Рабочие книги состоят из нескольких рабочих листов, которые могут содержать электронную таблицу, диаграммы, тексты и прочее. Один из листов – активный (или рабочий, с белым ярлыком) отображается в окне рабочей книги. При открытии уже 3

существующей рабочей книги в строке состояние отображается процесс загрузки. Строка состояния информирует пользователя также о том, можно ли вводить данные в электронную таблицу или выбирать команду. В этом случае в ней отображается слово Готово. Окно рабочей книги содержит книгу, которая состоит по умолчанию из 16 листов (максимальное количество листов в книге 255). Книга, открываемая при загрузке программы, имеет временное имя "Книга 1". Линейки прокрутки используются для быстрого перемещения по тексту, позволяя листать рабочую книгу. Рабочая область электронной таблицы состоит из строк и столбцов, имеющих свои имена. Имена строк – это их номера. Нумерация строк начинается с 1 и заканчивается максимальным числом, установленным для данной программы. Имена столбцов – это буквы латинского алфавита сначала от A до Z, затем от АА до AZ, ВА до BZ и так далее. Ячейка – область, определяемая пересечением столбца и строки электронной таблицы. Microsoft Excel располагает средствами для обработки информации в базе данных, которая представляет собой упорядоченную информацию, объединенную в единое целое. Столбцы таблицы можно рассматривать как поля, а строки - как записи. Совокупность данных в виде таблицы полей и записей называется списком или базой данных. В Excel понятия список и база данных взаимозаменяемы, под которым понимают прямоугольную область ячеек, в которой строки электронной таблицы имеют фиксированную структуру заполнения. Существуют ограничения, накладываемые на структуру базы данных: - первый ряд базы данных должен содержать неповторяющиеся имена полей, которые могут состоять из нескольких слов, обязательно размещенных в одной ячейке; - остальные ряды базы данных содержат записи, которые не должны быть пустыми рядами; - информация по полям (столбцам) должна быть однородной, то есть только цифры или только текст. По отношению к базе данных выполняются различные операции обработки. Для определения списка перед выполнением операций обработки достаточно установить курсор в любую ячейку списка. В том случае, когда список сформирован неверно или нужно работать с частью области списка, требуется выделение области списка. Данные, представленные в виде такой таблицы, можно сортировать, осуществлять выборку по условию, анализировать, обрабатывать и представлять результаты в нужном виде. Применение электронной таблицы обеспечивает успешное решение относительно небольших, хорошо сформулированных задач. Но когда таблица содержит несколько сотен строк, а документ состоит из многих листов, то работать с ним становится довольно трудно. Если электронная таблица предназначена для других пользователей, то осложняется процесс контроля ввода новых и использования существующих данных. Например, когда в электронной таблице в одной ячейке должна храниться дата, а в другой – денежное значение, пользователь может легко ошибиться и ввести в них текстовые значения. При 4

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

2.2. Создание базы данных и работа с записями Базу данных в электронной таблице Excel рекомендуется размещать на отдельном рабочем листе. Чтобы сохранить несколько баз данных на одном рабочем листе, нужно указать диапазон ячеек для каждой базы данных, однако это усложнит работу с ней. Поскольку каждая рабочая книга в Excel может состоять из любого количества листов (вплоть до 255), можно легко управлять многочисленными базами данных, размещая каждую из них на отдельном листе. По мере увеличения базы данных новые строки будут добавляться в конец рабочего листа. Если же на листе будут размещены другие данные, не имеющие отношение к базе данных, то может возникнуть опасность их уничтожения. Поэтому не рекомендуется на листе, содержащем базу данных, размещать другие данные. Для создания базы данных необходимо поместить курсор в ячейку верхней части таблицы и ввести в одну строку названия полей. Строка с названиями полей должна предшествовать списку. Оставлять пустую строку между строкой с названиями полей и фактическими данными недопустимо. Название каждого поля должно быть уникальным. В верхней части списка может быть несколько строк с введенной в них информацией, однако только одна строка будет восприниматься как содержащая названия полей. Название поля не должно превышать 255 символов, для удобства лучше выбирать короткие названия. Дальнейшие шаги по созданию базы данных – это ввод в каждую ячейку в строке, которая находится непосредственно под строкой с названиями полей, фактических данных по каждому полю. Необходимо следить, чтобы данные, вводимые в один столбец, имели одинаковый формат. На рисунке 2 приведен пример базы данных "Параметры винчестеров". Записи базы данных добавляются и редактируются путем ввода данных непосредственно в ячейки, а удаляются из базы данных путем их выделения и выполнения команды Правка|Удалить. Однако более удобно находить и редактировать данные с помощью формы, которую представляет Excel для ввода и просмотра данных. Чтобы просмотреть данные на экране с помощью формы, необходимо поместить рамку выделения в любую ячейку базы данных и выполнить команду Данные|Форма. На экране появится форма с полями базы данных, показанная на рисунке 3. Чтобы добавить новую запись, необходимо щелкнуть в форме по кнопке Добавить. На экране появится пустая форма, в которой можно вводить данные в каждое поле. 5

Рисунок 2 – Пример базы данных Рисунок 3 – Форма базы данных Редактировать записи можно тоже с помощью формы. Вначале надо пощелкать на кнопках Назад и Далее, чтобы найти нужную запись. Кроме того, можно воспользоваться полосой прокрутки или клавишами управления курсором. При работе с большой базой данных для поиска можно использовать кнопку Критерии, которая будет описана ниже. После того, как запись будет найдена, необходимо щелкнуть на нужном поле и внести изменения. Чтобы удалить записи с помощью формы, надо найти ее, воспользовавшись кнопками Назад и Далее. После того как запись появится на экране, щелкнуть на кнопке Удалить.

6

2.3. Поиск данных по критерию Основная функция любой базы данных - поиск информации по определенным критериям. С увеличением количества записей поиск определенной информации затрудняется. Excel позволяет упростить этот процесс путем использования поиска данных в форме по критерию и фильтрации данных. Критерии поиска определяют данные, которые следует найти. Для отбора нужных данных используется условие, которое представляется в виде формулы. Указывая критерий в форме данных, можно использовать операторы сравнения. В таблице перечислены возможные операторы сравнения. Чтобы найти отдельные записи, используя критерий поиска, необходимо выполнить следующие действия: - выделить любую ячейку базы данных; - выполнить команду Данные|Форма, чтобы вывести на экран форму данных; - щелкнуть на кнопке Критерии, и форма примет вид, как на рисунке 4; в верхнем правом углу будет написано слово "Критерии", и кнопка Критерии заменится кнопкой Правка; - ввести критерии поиска в соответствующие поля (необходимо заполнить только те поля, по которым будет осуществляться поиск); - нажать клавишу Enter или щелкнуть на кнопке Правка, чтобы вернуться к исходной форме; - для перехода между отобранными записями использовать кнопки Назад и Далее; - завершив просмотр записей, щелкнуть на кнопке Закрыть. В критериях можно использовать символы шаблона * и ?. Вопросительный знак ? означает любой символ, а звездочка * - последовательность произвольных символов. Например, критерий "Sеagate *", введенный в поле "Модель НDD", позволит отобрать модели "Seagate ST251", "Seagate ST3145A", "Seagate ST1195N". Если ввести несколько условий в различные поля формы в режиме критерия, то поиск информации осуществится по этим критериям, связанным операция "логическое И". Критерии с логическим ИЛИ указывать в форме данных нельзя. Таблица – Операторы сравнения Оператор

Значение

7

< > = <> <= >=

Меньше Больше Равно Не равно Меньше или равно Больше или равно

Рисунок 4 – Форма данных в режиме критерия

2.4 Сортировка базы данных В электронной таблице можно осуществить сортировку базы данных, то есть упорядочивание данных в определенном порядке: по убыванию или по возрастанию. Символьные поля сортируются в алфавитном порядке и наоборот. Сортировка по полям, которые содержат дату или время, будет выполнена правильно только в том случае, если дата и время представлены в соответствующем для этих элементов формате. Для выполнения сортировки необходимо выделить область базы данных или поместить в нее курсор, а затем выполнить команду Сортировка в меню Данные (Данные|Сортировка). При этом появится диалоговое окно "Сортировка диапазона" (рисунок 5) . Нужно установить в разделе Идентифицировать поля по переключатель в зависимости от способа указания диапазона, который нужно отсортировать. Если не выделен диапазон строк, необходимо установить переключатель "подписям (первая строка диапазона)". В результате строка, в которую занесены названия полей, не будет участвовать в сортировке, то есть исключается из результата сортировки. Имя поля, по которому производится сортировка, называется ключом сортировки. Excel позволяет осуществить многоуровневую сортировку по значениям одного, двух или трех столбцов. Причем для результата сортировки важен порядок выбора столбцов ("старший" ключ сортировки, "промежуточный" ключ 8

сортировки, "младший" ключ сортировки). Для задания ключевых полей используются окна "Сортировать по", "Затем по", "В последнюю очередь по". Для каждого сортируемого поля необходимо указать порядок сортировки: "по возрастанию" или "по убыванию" и нажать кнопку ОК. Иногда необходимо отсортировать базу данных больше чем по трем полям. Поскольку в диалоговом окне "Сортировка диапазона" предусмотрена сортировка максимум по трем полям, то такая сортировка осуществляется поэтап-

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

2.5 Автофильтр Выделение (фильтрация) нужных записей осуществляется командой Фильтр в меню Данные. Фильтрация возможна как через автоматический фильтр Автофильтр, так и через Расширенный фильтр - ручной. При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных, или выделить ее. Затем нужно выполнить команды Данные| Фильтр| Автофильтр. На именах полей появятся кнопки с 9

изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. Критерии фильтрации находятся в списке в алфавитном порядке. В дополнении к ним имеются еще пять ключей: Все, Первые , Условие, Пустые, Непустые. Эти ключи позволяют найти в списке: десять записей, ведущих отбор по выбранному показателю; записи, удовлетворяющие заданному пользователем условию; записи, не содержащей никакой информации в данном поле; записи, ячейки выделенного поля которых не пусты. После выбора критерия фильтрации, все записи, не удовлетворяющие этому критерию, становятся невидимыми. По команде Автофильтр можно выполнить отбор записей, удовлетворяющих сложным критериям (например, записи только определенного диапазона или записи, удовлетворяющие критериям с логическим ИЛИ). Чтобы определить сложный критерий, необходимо выбрать пункт (Условие…) из раскрывающегося списка рядом с названием поля. После этого на экране появится диалоговое окно "Пользовательский автофильтр" (рисунок 6).

Рисунок 6 – Вид диалогового окна "Пользовательский автофильтр" Для одного поля, имя которого будет в диалоговом окне, могут быть заданы два условия одновременно, связанные логическими И или ИЛИ. Операторы сравнения, используемые в Автофильтре, такие же как при поиске данных в форме по критериям (таблица). Выбор оператора сравнения осуществляется из первого раскрывающегося списка. Величина, с которой сравнивается значение данного поля вводится в поле справа, здесь также имеется раскрывающийся список. Чтобы после фильтрации снова вывести на экран полный список, нужно щелкнуть на любой кнопке со стрелкой фильтра, и в списке критерия фильтрации выделить строки Все (этот пункт отключает фильтрацию). А также вывод полного списка осуществляется, если в меню Данные выбрать команду Фильтр |Отобразить все.

2.6. Расширенный фильтр Одновременная фильтрация по нескольким полям с сохранением условия отбора записей возможна при выполнении команды Данные|Фильтр|Расширенный фильтр. 10

При использовании Расширенного фильтра необходимо сначала определить (создать) три области: - интервал списка ( область базы данных); - интервал критериев (область, где задаются критерии фильтрации); - интервал извлечения (область, в которой будут появляться результаты фильтрации). Имена полей во всех интервалах должны точно совпадать. Для выполнения действий по фильтрации необходимо выбрать команду Фильтр в меню Данные, пункт Расширенный фильтр, в результате появится диалоговое окно "Расширенный фильтр" (рисунок 7). В диалоговом окне необходимо указать координаты интервалов. Если необходимо получить результаты фильтрации в интервале извлечения, нужно поставить переключатель [.] - скопировать результат в другое место.

Рисунок 7 – Вид диалогового окна "Расширенный фильтр" Рассмотрим в качестве примера задачу поиска моделей винчестеров, которые имеют емкость больше 200 Мб и время доступа не более 10 мс. На рисунке 8 приведен вид рабочего листа для решения этой задачи с использованием "Расширенного фильтра". Интервал базы данных А2:F11, интервал критериев А13:F14; интервал извлечения А16:F20.

11

Рисунок 8 – Вид рабочего листа при использовании "Расширенного фильтра"

3. Задание на выполнение работы 3.1 Заполнить таблицу, содержащую информацию о параметрах винчестеров в соответствии с рисунком 2. Сохраните ее на диске D: в папке с именем вашей группы. 3.2 Перейдите в режим формы базы данных, посмотрите действие кнопок "Далее", "Назад", "Удалить", "Добавить" (добавить удаленную запись). 3.3 Используя форму данных в режиме критерия, найти записи винчестеров, удовлетворяющих следующим условиям: имеют шину SCSI; скорость вращения больше 3600 об/мин; скорость передачи больше 2000 Кб/с и время доступа меньше или равно 10 мс. 3.4 Осуществить сортировку по полю "Скорость передачи" в порядке возрастания, отменить сортировку. 3.5 Осуществить сортировку по полю Модель HDD в порядке возрастания, затем по полю "Шина" в порядке убывания. 3.6 С использованием Автофильтра осуществите поиск винчестеров, имеющих модель HDD, которая начинается с буквы "S". 3.7. С использованием Автофильтра осуществите поиск винчестеров, имеющих шину MFM и АТ. 3.8 С использованием Расширенного фильтра осуществите поиск винчестеров, у которых: а) емкость больше или равна 200 Мб; б) модель HDD "Seagate" и скорость вращения меньше 5000 об/мин. 12

4 Содержание отчета 4.1 Название работы. 4.2 Цель работы. 4.3 Ответы на контрольные вопросы.

5 Контрольные вопросы 5.1 Для чего предназначено программное средство Microsoft Excel? 5.2 Что содержит главное окно Excel? 5.3 Из чего состоит рабочая область электронной таблицы? 5.4 Что такое база данных? 5.5 Что называют именем поля? 5.6 Что такое поле? 5.7 Что такое запись? 5.8 Какие ограничения накладываются на структуру данных, представленных в виде базы? 5.9 Какие операции обработки возможны по отношению к базе данных? 5.10 Как создается база данных в электронной таблице Excel? 5.11 Что можно осуществлять в форме базы данных? 5.12 Какие кнопки предназначены для перемещения по записям формы базы данных? 5.13 Что такое критерии поиска? 5.14 Как выполняется поиск данных в форме по критерию? 5.15 Какие операторы сравнения можно использовать при построении критерия? 5.16 Можно ли использовать несколько критериев в различных полях формы? Каким логическим оператором они соединяются? 5.17 Что такое сортировка? 5.18 Что такое ключ сортировки? 5.19 Как осуществляется сортировка данных? 5.20 Как проводится фильтрация данных? 5.21 Что позволяет команда Автофильтр? 5.22 Как отфильтровать данные по нескольким критериям? 5.23 Какой командой надо воспользоваться, чтобы вывести на экран весь список? 5.24 Что позволяет команда Расширенный фильтр?

13

Список использованных источников 1 Джонс Э., Саттон Д. Библия пользователя Оffice 97. – Киев: Диалектика, 1997. – 848 с. 2 Зайчикова С.А., Шамаева О.Ю. Программные средства обработки данных: Учебное пособие. – М.: МГТУ "Станкин", 1999. – 105с. 3 Microsoft Excel 97. Шаг за шагом: Практ.пособие. – М.: Издательство ЭКОМ, 1997. – 448 с.

14