Microsoft Excel

Microsoft Excel

Лабораторная работа № 2

«работа с простейшими базами данных»

Одна из возможностей пакета Excel – работа с простейшими базами данных.

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

Первая строка таблицы должна содержать имена столбцов (по­лей).

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

Демонстрационный пример.

Пусть имеется список больных, со­держащий их некоторые характеристики

Не трудно представить себе список со значительно большим чис­лом больных и их характеристик. В этом случае работа со списком будет представлять определенные трудности. В частности, простой просмотр списка данных при помощи клавиш управления курсо­ром (стрелки, PgUp, PgDn) может занять значительное время. Для решения более сложных задач работы со списками используются команды меню Данные.

Введем список в рабочую таблицу. Для этого откроем новый лист и последовательно заполним все ячейки списка. Например, А1 -ФИО, Bl – Пол и т.д.

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

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

Пусть необходимо упорядочить список по фамилиям больных по алфавиту. Для этого табличный курсор устанавливаем в лю­бую ячейку списка (например, В2). Открываем раздел меню Дан­ные. Выбираем подраздел Сортировка. Открывается диалоговое окно Сортировка диапазона. Щелкаем по стрелке справа от рабо­чего поля Сортировать по и в появившемся списке выбираем ФИО. Переключатель по возрастанию - по убыванию ставим в по­ложение по возрастанию (щелчком указателя мыши черную точ­ку ставим в кружок рядом со словами по возрастанию). Нажима­ем кнопку ОК. Больные в списке должны расположиться в алфавитном порядке.

Упражнение 1.1.

Сортировать список так, чтобы в начале списка оказались больные стар­шего возраста.

Решение 1.1.

Табличный курсор устанавливаем в любую ячейку списка (например, В2). Открываем раздел меню Данные. Выбираем подраздел Сортировка. В диалоговом окне Сортировка диапазона щелкаем по стрелке справа от рабочего поля Сортировать по и в появившемся списке выбираем Воз­раст. Щелчком указателя мыши переключатель ставим в положение по убыванию. Нажимаем кнопку ОК.

В тех случаях, когда при сортировке оказывается несколько объек­тов (больных) с одинаковым значением характеристики (например, при сортировке по полу), используется сортировка по нескольким полям.

Пусть необходимо осуществить сортировку по полу (вначале женщины), причем женщины и мужчины должны быть расположены в алфавитном порядке.

Для этого табличный курсор устанавливаем в любую ячейку списка (например, В2). Открываем раздел меню Данные. Выбира­ем подраздел Сортировка. Открывается диалоговое окно Сорти­ровка диапазона. Щелкаем по стрелке справа от рабочего поля Сортировать поив появившемся списке выбираем Пол. Переключатель ставим в положение по возрастанию (щелчком указателя мыши). Щелкаем по стрелке справа от рабочего поля Затем по и в появив­шемся списке выбираем ФИО. Переключатель ставим в положение по возрастанию. Нажимаем кнопку ОК.

Упражнение 1.2.

Сортировать список так, чтобы в начале списка оказались больные леча­щего врача Соколовой, а затем Орлова, причем у обоих врачей вначале более легкие по весу.

Решение 1.2.

Табличный курсор устанавливаем в любую ячейку списка (например, В2). Открываем раздел меню Данные. Выбираем подраздел Сортировка. В диалоговом окне Сортировка диапазона щелкаем по стрелке справа от рабочего поля Сортировать по и в появившемся списке выбираем Врач. Щелчком указателя мыши переключатель ставим в положение по убыва­нию. В рабочее поле Затем по устанавливаем Вес. Переключатель ставим в положение по возрастанию. Нажимаем кнопку ОК.

Аналогичным образом выполняется сортировка по трем полям.

2. Поиск данных

Следующей важной операцией при работе с базами данных явля­ется поиск необходимой информации в базе. Поиск производят с помощью пункта Фильтр команды меню Данные. Фильтр - осу­ществляет выбор из списка указанного набора записей. Режим Автофильтрация позволяет проводить отбор записей, удовлетво­ряющих заданному критерию. Для фильтрации необходимо выпол­нить следующие действия:

— переместить курсор в любое место списка;

— в подменю команды Фильтр выбрать режим Автофильтр;

— щелкнуть мышью на одной из появившихся кнопок списков столбцов;

— в появившемся списке значений выбрать нужное значение.

Отмена результатов фильтрации осуществляется выбором зна­чения Все в списке столбца.

Пусть необходимо найти всех больных с фамилиями, начинаю­щимися на букву «с». Для осуществления поиска необходимо пе­реместить курсор в любое место списка (В2). Выбрать раздел меню Данные и подраздел Фильтр. В подменю команды Фильтр выбрать режим Автофильтр. Рядом с именами полей появляются стрелки (кнопки списков столбцов). Необходимо щелкнуть мы­шью на кнопке списка справа от аббревиатуры ФИО. В появив­шемся списке значений выбрать пункт (Условие...). Появляется диалоговое окно Пользовательский автофильтр. Формирование условий поиска данных (запроса) является важнейшим моментом процедуры. От правильности их задания зависит результат поис­ка. В данном случае компьютер должен найти все ФИО, которые имеют первую букву «С», а остальные буквы могут быть любые. Для этого в рабочем поле ФИО диалогового окна устанавливаем равно (щелчок по стрелке мышью и выбор в списке), в правое верхнее рабочее поле с клавиатуры вводим С* (* — означает пос­ледовательность любых сим-волов). Нажимаем кнопку ОК. На экране остаются данные только на больных Силина, Смирнову и Сидорова. Эти данные могут быть скопированы в другую таблицу, в файл или выведены на принтер.

Упражнения 2.1.

1) Найти всех больных моложе 35 лет.

Решение 2.2.

Отменить результаты предыдущего поиска щелчком мыши на кнопке списка справа от аббревиатуры ФИО и выбрать значение Все в списке столбца. Щелкнуть мышью на кнопке списка справа от поля Возраст. В появив­шемся списке значений выбрать пункт (Условие...). В появившемся диа­логовом окне Пользовательский автофильтр в рабочем поле Возраст устанавливаем меньше (щелчок по стрелке мышью и выбор в списке), в правое верхнее рабочее поле с клавиатуры вводим число 35. Нажимаем кнопку ОК. (Ответ — Петрова, Козлова).

Поиск по нескольким полям.

Упражнения 2.2.

2) Найти всех мужчин тяжелее 75 кг.

Решение 2.2.

Отменить результаты предыдущего поиска щелчком мыши на кнопке списка справа от имени поля Возраст и выбрать значение Все в списке столбца. Щелкнуть мышью на кнопке списка справа от поля Пол. В появившемся списке значений выбрать пункт м. Щелкнуть мышью на кнопке списка справа от поля Вес. В появившемся списке значений выбрать пункт (Условие...). В появившемся диалоговом окне Пользова­тельский автофильтр в рабочем поле Вес устанавливаем больше (щел­чок по стрелке мышью и выбор в списке), в правое верхнее ра­бочее поле с клавиатуры вводим число 75. Нажимаем кнопку ОК. (Ответ — Иванов).

Упражнения 2.3.

Задание диапазона поиска.

3) Найти всех больных в возрасте от 40 до 50 лет.

Решение 2.3.

Отменить результаты предыдущего поиска щелчком мыши на кнопке списка справа от имени поля Пол и выбрать значение Все в списке стол­бца. Аналогично повторить с полем Вес. Щелкнуть мышью на кнопке списка справа от поля Возраст. В появившемся списке значений вы­брать пункт (Условие...). В появившемся диалоговом окне Пользова­тельский автофильтр в рабочем поле Возраст устанавливаем меньше (щелчок по стрелке мышью и выбор в списке), в правое верхнее рабочее поле с клавиатуры вводим число 50. Переключатель и/или устанавлива­ем в положение и. В левом нижнем рабочем поле устанавливаем больше (щелчок но стрелке мышью и выбор в списке), в правое нижнее рабочее поле с клавиатуры вводим число 40. Нажимаем кнопку ОК. (Ответ -Смирнова, Власов).

Упражнения 2.4.

4) Найти всех больных с весом менее 70 кг и более 80 кг.

Решение 2.4.

Отменить результаты предыдущего поиска щелчком мыши па кнопке списка справа от имени поля Возраст и выбрать значение Все в списке столбца. Щелкнуть мытью на кнопке списка справа от поля Вес. В появившемся списке значений выбрать пункт (Условие...). В появившемся диалоговом окне Пользовательский автофильтр в рабочем поле Вес устанавливаем меньше (щелчок по стрелке мышью и выбор в списке), в правое верхнее рабочее поле с клавиатуры вводим число 70. Переключатель и/или уста­навливаем в положение или. В левом нижнем рабочем поле устанавлива­ем больше (щелчок по стрелке мышью и выбор в списке), в правое нижнее рабочее поле с клавиатуры вводим число 80. Нажимаем кнопку ОК. (От­вет - Петрова, Козлова, Иванов).

Для завершения работы в режиме поиска (фильтрации) необхо­димо выбрать раздел меню Данные и подраздел Фильтр. В подме­ню команды Фильтр щелкнуть указателем мыши по галочке (флаж­ку) рядом с пунктом Автофильтр.

3. Работа с окном формы

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

Для вызова окна формы данных необходимо переместить таб­личный курсор в любое место списка (В2), выбрать раздел меню Данные и подраздел Форма.... Появляется диалоговое окно Лист 1. Окно формы содержит имена полей списка и соответствующие данные на одного больного. С помощью полосы прокрутки можно по очереди просмотреть всех больных. Для ввода новой записи (больного) необходимо нажать кнопку Добавить и заполнить ра­бочие поля формы. Отметим, что переход из одного поля в другое осуществляется либо клавишей Tab, либо щелчком указателя мыши (не клавишей Enter). Для удаления записи из списка ее необходи­мо вывести в форму (полосой прокрутки) и нажать кнопку Уда­лить. После чего подтвердить удаление нажатием кнопки ОК в информационном окне Excel.

Упражнение 3.1.

Ввести запись нового больного в список: Нилов, м, 28,75,Орлов, а затем удалить ее.

Решение 3.1.

Переместить табличный курсор в любое место списка (В2), выбрать раз­дел меню Данные, и подраздел Форма,... В диалоговом окне Лист 1 на­жать кнопку Добавить и заполнить рабочие поля формы: в поле ФИО ввести Нилов, нажать клавишу Tab, в поле Пол ввести м, нажать клавишу Tab. Аналогично заполнить поля Возраст, Вес и Врач. Снова нажать кноп­ку Добавить.

Для удаления записи из списка полосой прокрутки устанавлива­ем запись Нилов ... в рабочие поля формы и нажимаем кнопку Уда­лить. После чего подтверждаем удаление нажатием кнопки ОК в информационном окне Excel.

Для осуществления поиска в режиме окна формы данных необ­ходимо нажать кнопку Критерии и ввести условия поиска в рабо­чие поля с соответствующими именами (условия: «равно», «боль­ше», «меньше» и т.д. здесь обозначаются соответствующими значками: «=», «>», «<», например, >40). Просмотр результатов осуществляется в окне формы нажатием кнопок Назад и Далее. Отметим, что в отличие от поиска в режиме Фильтр, здесь резуль­таты нельзя скопировать или распечатать.

Упражнение 3.2.

Найти всех больных с фамилиями, начинающимися на букву «С».

Решение 3.2.

Нажать кнопку Критерии и ввести в рабочее поле ФИО условие С*. Для просмотра результатов три раза нажать на кнопку Далее. Последователь­но появятся записи больных Силина, Смирновой и Сидорова. Дальней­шие нажатия на кнопку Далее к каким-либо изменениям не приведут. Нажатия на кнопку Назад приведут к просмотру найденных больных в обратном порядке.

4. Вычисление промежуточных итогов

Режим Итоги - позволяет вычислять промежуточные итоги по заданному столбцу. Для подведения итогов необходимо выполнить следующие действия:

— расположить указатель ячейки в любое место списка;

— выполнить сортировку списка по заданному столбцу (см. п. 1.);

— выбрать команду Итоги из меню Данные,

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

Пусть необходимо сосчитать количество больных у каждого лечащего врача. Для этого проведем сортировку больных. Пра­вильный выбор способа сортировки является решающим при вы­числении итогов. В результате сортировки больные должны быть разбиты на группы, в которых требуется произвести необходимые вычисления. В данном случае сортировку следует проводить по лечащему врачу. Табличный курсор устанавливаем в любую ячейку списка (например, В2). Открываем раздел меню Данные. Выбира­ем подраздел Сортировка. Открывается диалоговое окно Сорти­ровка диапазона. Щелкаем по стрелке справа от рабочего поля Сортировать по и в появившемся списке выбираем Врач. Пере­ключатель по возрастанию - по убыванию для определенности ста­вим в положение по возрастанию (щелчком указателя мыши чер­ную точку ставим в кружок рядом со словами по возрастанию). Нажимаем кнопку ОК. Вначале списка располагаются больные врача Орлова, затем больные Соколовой. Снова открываем раз­дел меню Данные. Выбираем подраздел Итоги.... Открывается диалоговое окно Промежуточные итоги. Щелкаем по стрелке справа от рабочего поля При каждом изменении в: и в появив­шемся списке выбираем Врач. Щелкаем по стрелке справа от ра­бочего поля Операция: и в появившемся списке выбираем Кол-во значений. В рабочем поле Добавить итоги по: устанавливаем га­лочку (флажок) только в позицию ФИО, остальные галочки уб­рать, воспользовавшись полосой прокрутки и мышью. Нажать кнопку ОК. В результате в столбце ФИО после всех больных врача Орлова появится их число - 3, после всех больных врача Соколовой - 4 и общее количество больных - 7. В столбце Врач в соответствующих строках появится фамилия лечащего врача и вид операции (Количество значений).

В случае больших списков визуальный поиск полученных про­межуточных итогов представляет определенные трудности. Для об­легчения этой задачи слева от рабочей таблицы Excel появляются 3 пронумерованные линейки. Для того, чтобы в таблице остались толь­ко итоги, необходимо щелкнуть мышью по кнопкам « — » на 2 (сред­ней) линейке. Восстановление списка больных осуществляется щел­чком мыши по кнопкам «+» на 2 линейке.

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

Упражнения 4.1.

1) Сосчитать количество мужчин и женщин.

Решение 4.1.

Проведем сортировку больных по полу. Табличный курсор устанавлива­ем в любую ячейку списка (например, В2). Открываем раздел меню Дан­ные. Выбираем подраздел Сортировка. В диалоговом окне Сортировка диапазона щелкаем по стрелке справа от рабочего поля Сортировать по и в появившемся списке выбираем Лол. Переключатель ставим в положе­ние по убыванию (щелчком указателя мыши). Нажимаем кнопку ОК. . Снова открываем раздел меню Данные. Выбираем подраздел Итоги.... В диалоговом окне Промежуточные итоги щелкаем по стрелке справа от рабочего поля При каждом изменении в: и в появившемся списке выбира­ем Пол. Щелкаем по стрелке справа от рабочего поля Операция: и в по­явившемся списке выбираем Кол-во значений. В рабочем поле Добавить итоги по: мышью устанавливаем галочку только в позицию ФИО. Нажи­маем кнопку ОК.

Упражнения 4.2.

Определить средний возраст больных врача Соколовой и боль­ных врача Орлова.

Решение 4.2.

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

Проводим сортировку больных по лечащему врачу — открываем раз­дел меню Данные, выбираем подраздел Сортировка, в диалоговом окне Сортировка диапазона в рабочем поле Сортировать по устанавливаем Врач. Переключатель ставим в положение по убыванию (щелчком ука­зателя мыши). Нажимаем кнопку ОК. . Снова открываем раздел меню Данные. Выбираем подраздел Итоги.... В диалоговом окне Промежу­точные итоги в рабочем поле При каждом изменении в: устанавлива­ем Врач. Щелкаем по стрелке справа от рабочего поля Операция: и в появившемся списке выбираем Среднее. В рабочем поле Добавить итоги по: устанавливаем галочку только в позицию Возраст. Нажимаем кноп­ку ОК.

В результате получим средний возраст больных Соколовой - 39,5 лет, Орлова - 48 лет, и всех больных - 43,14 лет.

Упражнения 4.3.

Найти средний вес больных мужчин врача Орлова, больных мужчин врача Соколовой, больных женщин врача Орлова и боль­ных женщин врача Соколовой.

Решение 4.3.

Восстанавливаем исходный список - раздел меню Данные, подраздел Итоги... и в диалоговом окне Промежуточные итоги нажимаем кнопку

Убрать все.

Проводим сортировку больных по полу и лечащему врачу — откры­ваем раздел меню Данные, выбираем подраздел Сортировка, в диалого­вом окне Сортировка диапазона в рабочем поле Сортировать по уста­навливаем Пол. Переключатель ставим в положение по убыванию (щелчком указателя мыши).'В рабочем поле Затем по устанавливаем Врач. Переключатель ставим в положение по возрастанию. Нажимаем кнопку ОК. . Снова открываем раздел меню Данные. Выбираем подраз­дел Итоги.... В диалоговом окне Промежуточные итоги в рабочем поле При каждом изменении в: устанавливаем Врач. В рабочем поле Опера­ция: устанавливаем Среднее. В рабочем поле Добавить итоги по: уста­навливаем галочку в позицию Вес. Нажимаем кнопку ОК.

В результате получим средний вес больных мужчин врача Орлова -78 кг, больных мужчин врача Соколовой - 73 кг, больных женщин врача Орлова - 64 кг и больных женщин врача Соколовой - 68,5 кг. Средний вес всех больных — 71,86 кг.