Практическое занятие 3
Тема. Электронная таблица Excel. Работа с элементарными базами данных.
Цель - приобрести навыки создания элементарных баз данных в Excel, провести сортировку, поиск данных и подсчет промежуточных итогов.
Ход выполнения работы
Запустить программу Excel. Создать новую книгу C:\User\ЛД???\базаданных.xls, открыть новый лист электронной таблицы. Заполнить ее соответственно базе данных таблицы 3.1: А – ФИО, В – Пол, С – Возраст, D – Вес, E – Врач.
Таблица 3.1
| ФИО | Пол | Возраст | Вес | Врач |
| Иванов А.С. | м | Орлов | ||
| Петрова О.А. | ж | Орлов | ||
| Сидоров И.И. | м | Орлов | ||
| Козлова П.С. | ж | Соколова | ||
| Власов К.И. | м | Соколова | ||
| Смирнова О.М. | ж | Соколова | ||
| Силин М.С. | м | Соколова |
Скопировать базу данных на Лист2 и Лист3.
Сортировка данных
Сортировка данных позволяет упорядочить список по каким-либо характеристикам.
Переименовать Лист1 на Сортировка.
Провести сортировку базы данных согласно заданиям, предложенным ниже.
Задание 1 Упорядочить базу данных по алфавиту фамилий.
Разместить курсор в какую-нибудь ячейку (например, В2). Открыть меню Данные, выбрать подраздел Сортировка. В диалоговом окне Сортировка диапазона нажать на стрелочку справа от рабочего поля Сортировать по и из списка выбрать ФИО. Переключатель по возрастанию - по убыванию установить в положение по возрастанию. Нажать кнопку Ок.
Задание 2 Осуществить сортировку базы данных таким образом, чтобы вначале были больные, старшие по возрасту.
Разместить курсор в какую-нибудь ячейку (например, В2). Открыть меню Данные, выбрать подраздел Сортировка. В диалоговом окне Сортировка диапазона нажать на стрелочку справа от рабочего поля Сортировать по и из списка выбрать Возраст. Переключатель по возрастанию - по убыванию установить в положение по убыванию. Нажать кнопку Ок.
Задание 3 Провести сортировку базы данных по полу (вначале женщины), причем разместить их по алфавиту.
Разместить курсор в какую-нибудь ячейку (например, В2). Открыть меню Данные, выбрать подраздел Сортировка. В диалоговом окне Сортировка диапазона нажать на стрелочку справа от поля Сортировать по и из списка выбрать Пол. Переключатель по возрастанию - по убыванию установить в положение по возрастанию. Нажать на стрелочку справа от рабочего поля Затем по и выбрать из списка ФИО. Переключатель установить в положение по возрастанию. Нажать кнопку Ок.
Задание 4 Провести сортировку списка таким образом, чтобы вначале списка были больные врача Соколовой, а потом - Орлова, и у обоих врачей сначала были больные более легкие по весу.
Разместить курсор в любую ячейку (например, В2). Открыть меню Данные, выбрать подраздел Сортировка. В диалоговом окне Сортировка диапазона нажать на стрелочку справа от рабочего поля Сортировать по и из списка выбрать Врач. Переключатель по возрастанию - по убыванию установить в положение по убыванию. Нажать на стрелочку справа от рабочего поля Затем по и выбрать из списка Вес. Переключатель установить в положение по возрастанию. Нажать на кнопку Ок.
Задание 5 Провести сортировку списка по полу (вначале мужчины), причем в каждой группе пациенты должны быть размещены, начиная с младшего по возрасту.
Задание 6 Провести сортировку списка таким образом, чтобы вначале были размещены больные врача Соколовой, а потом - Орлова, причем в каждой группе были вначале мужчины, размещенные по алфавиту, а потом женщины.
Поиск данных
Перейти на Лист2 и переименовать его на Фильтр.
Провести поиск данных базы данных согласно заданиям, предложенным ниже.
Задание 1 Найти всех больных с фамилиями, которые начинаются на букву «С».
Разместить курсор в любую ячейку (например, В2). Открыть меню Данные, выбрать подраздел Фильтр ® Автофильтр. Рядом с именами полей появятся стрелочки. Нажать мышкой на стрелочке списка ФИО. В появившемся списке выбрать пункт Условие…. Появится диалоговое окно Пользовательский автофильтр. В рабочем поле ФИО установить равно, в правом верхнем рабочем поле с клавиатуры ввести С*. Нажать кнопку Ок.
На экране останутся только данные больных: Силина, Смирновой, Сидорова.
Задание 2 Найти всех больных младше 35 лет.
Отменить результаты предыдущего поиска: нажать на стрелку возле ФИО и выбрать Все. Нажать мышкой на стрелочке списка Возраст. В появившемся списке выбрать пункт Условие…. В диалоговом окне Пользовательский автофильтр в рабочем поле Возраст установить меньше, в правому верхнем рабочем поле с клавиатуры ввести 35. Нажать кнопку Ок.
На экране останутся только данные больных: Петровой, Козловой.
Задание 3 Найти всех мужчин тяжелее 75 кг.
Отменить результаты предыдущего поиска: нажать на стрелочку возле Возраст и выбрать Все. Нажать мышкой на стрелочке списка Пол. В появившемся списке выбрать м. Нажать мышкой на стрелочке списка Вес. В появившемся списке выбрать значение Условие…. В диалоговом окне Пользовательский автофильтр в рабочем поле Вес установить больше, в правом верхнем рабочем поле с клавиатуры ввести 75. Нажать кнопку Ок.
На экране останутся только данные больного Иванова.
Задание 4 Найти всех больных возрастом от 40 до 50 лет.
Отменить результаты предыдущего поиска: нажать на стрелочку возле Пол и выбрать Все. Аналогично сделать для поля Вес. Нажать мышкой на стрелочке списка Возраст. В появившемся списке выбрать пункт Условие…. В диалоговом окне Пользовательский автофильтр в рабочем поле Возраст у становить меньше, в правом верхнем рабочем поле с клавиатуры ввести 50. Переключатель и/или установить в положение и. В левом нижнем поле установить больше, в правому нижнем рабочем поле с клавиатуры ввести число 40. Нажать кнопку Ок.
На экране останутся только данные больных: Смирновой, Власова.
Задание 5 Найти всех больных с весом меньше 70 кг и больше 80 кг.
Отменить результаты предыдущего поиска: нажать на стрелочке списка Возраст и выбрать Все. Нажать мышкой на стрелочке списка справа от Вес. В появившемся списке выбрать пункт Условие. В диалоговом окне Пользовательский автофильтр в рабочем поле Вес установить меньше, в правом верхнем рабочем поле с клавиатуры ввести 70. Переключатель и/или установить в положение или. В левом нижнем поле установить больше, в правом нижнем рабочем поле с клавиатуры ввести число 80. Нажать кнопку Ок.
На экране останутся только данные больных: Петровой, Козловой, Иванова.
Задание 6 Найти всех больных женщин младше 40 лет.
На экране останутся только данные больных: Козловой, Петровой.
Задание 7 Найти всех больных врача Орлова с весом более 80 кг.
На экране останутся только данные больного Иванова.
Подсчет промежуточных итогов
Режим Итоги позволяет подсчитать промежуточные итоги в заданном столбце.
Перейти на Лист3 и переименовать его на Итоги.
Провести подсчет промежуточных итогов согласно заданиям, предложенным ниже.
Задание 1 Посчитать количество больных каждого врача.
Провести сортировку базы данных больных по врачу. Табличный курсор установить в любую ячейку списка (например, В2). Открыть пункт меню Данные, выбрать подраздел Сортировка. В диалоговом окне Сортировка диапазона нажать на стрелочку справа от рабочего поля Сортироватьпо и в появившемся списке выбрать Врач. Переключатель установить в положение по возрастанию. Нажать кнопку Ок.
Открыть пункт меню Данные, выбрать Итоги. В диалоговом окне Промежуточные итоги нажать на стрелочку справа от рабочего поля При каждом изменении в: и в появившемся списке выбрать Врач. Нажать на стрелочку справа от рабочего поля Операция: и из списка выбрать Кол-во значений. В рабочем поле Добавить итоги по: установить флажок в позицию ФИО, другие убрать. Нажать Ок.
Как результат, получим количество больных врача Соколовой – 4, Орлова – 3 и всех больных – 7.
Задание 2 Посчитать количество мужчин и женщин.
Восстановить начальный список – открыть раздел меню Данные, выбрать подраздел Итоги и в окне Промежуточные итоги нажать кнопку Убрать все.
Провести сортировку больных по полу. Табличный курсор установить в любую ячейку (например, В2). Открыть пункт меню Данные, выбрать подраздел Сортировка. В диалоговом окне Сортировка диапазона нажать на стрелочку справа от рабочего поля Сортироватьпо и из появившегося списка выбрать Пол. Переключатель установить в положение по убыванию. Нажать кнопку Ок.
Открыть пункт меню Данные, выбрать Итоги. В диалоговом окне Промежуточные итоги нажать на стрелочку справа от рабочего поля При каждом изменении в: и в появившемся списке выбрать Пол. Нажать на стрелочку справа от рабочего поля Операция: и из списка выбрать Кол-во значений. В рабочем поле Добавить итоги по: установить флажок в позицию ФИО, другие убрать. Нажать Ок.
Как результат, получим количество больных мужчин – 4, женщин – 3 и всех больных – 7.
Задание 3 Определить средний возраст больных врача Соколовой и больных врача Орлова.
Восстановить начальный список – открыть раздел меню Данные, выбрать подраздел Итоги и в окне Промежуточные итоги нажать кнопку Убрать все.
Провести сортировку больных по врачу. Открыть пункт меню Данные, выбрать подраздел Сортировка. В диалоговом окне Сортировка диапазона в рабочем поле Сортироватьпо установить Врач. Переключатель установить в положение по убыванию. Нажать кнопку Ок.
Открыть пункт меню Данные, выбрать Итоги. В диалоговом окне Промежуточные итоги нажать на стрелочку справа от рабочего поля При каждом изменении в: и в появившемся списке выбрать Врач. Нажать на стрелочку справа от рабочего поля Операция: и из списка выбрать Среднее. В рабочем поле Добавить итоги по: установить флажок в позицию Возраст, другие убрать. Нажать Ок.
Как результат, получим средний возраст больных врача Соколовой – 39,5 года, врача Орлова – 48 лет и всех больных – 43,14 года.
Задание 4 Найти средний вес больных мужчин врача Орлова, больных мужчин врача Соколовой, больных женщин врача Орлова и больных женщин врача Соколовой.
Восстановить начальный список – открыть раздел меню Данные, выбрать подраздел Итоги и в окне Промежуточные итоги нажать кнопку Убрать все.
Провести сортировку больных по врачу. Открыть пункт меню Данные, выбрать подраздел Сортировка. В диалоговом окне Сортировка диапазона в рабочем поле Сортироватьпо установить Пол. Переключатель установить в положение по убыванию. В рабочем поле Затем по установить Врач. Переключатель установить в положение по возрастанию. Нажать кнопку Ок.
Открыть пункт меню Данные, выбрать Итоги. В диалоговом окне Промежуточные итоги нажать на стрелочку справа от рабочего поля При каждом изменении в: и в появившемся списке выбрать Врач. Нажать на стрелочку справа от рабочего поля Операция: и из списка выбрать Среднее. В рабочем поле Добавить итоги по: установить флажок в позицию Вес, другие убрать. Нажать Ок.
Как результат, получим средний вес больных мужчин врача Орлова – 78 кг, больных мужчин врача Соколовой – 73 кг, больных женщин врача Орлова – 64 кг и больных женщин врача Соколовой – 68,5 кг. Средний вес больных – 71,86 кг.
Задание 5 Определить средний возраст больных каждого пола.
Как результат, получим средний возраст больных мужчин – 49,75 года, больных женщин – 34,33 года. Средний возраст всех больных – 43,14 года.
Задание 6 Определить средний вес больных каждого врача.
Как результат, получим средний вес больных врача Соколовой – 70,75 кг, больных врача Орлова – 73,33 кг. Средний вес всех больных – 71,86 кг.
Оформить отчет в тетради (постановка задачи, входные данные, основные этапы выполнения, полученные результаты).
Закрыть программу Excel.
Контрольные вопросы
1 Робота с элементарными базами данных.
2 Сортировка данных. Этапы сортировки. Сортировка по нескольким признакам.
3 Поиск данных. Этапы поиска данных.
4 Расчет промежуточных итогов.