Лабораторная работа №3 Создание базы данных

 

Цель работы: Создание базы данных средствами Excel. Сортировка данных, выборка по различным критериям, поиск записи. Автоматическое подведение итогов

Методика выполнения работы

1. Открыть новую рабочую книгу. Листу 1 присвоить имя «Продажа служащими ». Заполнить этот лист, как показано ниже на рисунке 5.50.

Рисунок 5.50 – Макет таблицы

2. Для того, чтобы отсортировать данные выполните команду Данные ÞСортировка. Откроется окно «Сортировка» (рис. 5.51). В поле «Сортировать по» установите имя столбца «Имя», щелкнуть Добавить уровень, в поле «Затем по» - имя столбца «Дата». Нажать ОК.

Рисунок 5.51

3. Промежуточные итоги. Выполнить команду ДанныеÞСтруктура ÞПромежуточные итоги. Откроется диалоговое окно (рис. 5.52). Установить в поле При каждом изменении в столбец «Имя», в поле Операция - сумма, в поле Добавить итоги по - столбец «Цена», нажать ОК. Повторить команду ДанныеÞСтруктура ÞПромежуточные итоги. В поле Операция выбрать «среднее», отменить флажок на опции «Заменить текущие итоги» и нажать ОК.

Рисунок 5.52 – Окно параметров промежуточных итогов

4. Сделать самостоятельно сортировку и подсчитать промежуточные итоги по Категории и Магазину.

5. Щелкнув левой клавишей мыши на имени столбца, выделить столбец «Магазин», захватить его мышкой и перетащить на столбец А.

6. Добавим столбец, содержащий количество проданных фильмов: щелкнуть правой клавишей мыши на имени столбца «Категория», выбрать команду Вставить. Ввести название нового столбца – «Кол-во». Заполнить этот столбец числами в пределах 20. В столбце Н ввести название «Продажа». Этот столбец заполнить формулами, выполняющими действие Кол-во*Цену. Примерный вид новой таблицы показан на рисунке 5.53.

Рисунок 5.53 – Данные для добавления

 

7. Ввод данных и просмотр с помощью формы. Достать на панель быстрого доступа кнопку Форма (Кнопка Office Þ Параметры Þ Настройка Þ Все команды). Отсортировать таблицу по категории «Магазин». Открыть с помощью соответствующей кнопки окно работы с формой (рис.5.54).

 

Рисунок 5.54 - Окно Форма

Щелкая на кнопках «Назад» и «Далее» можно просматривать записи из нашей таблицы. Щелчок на кнопке «Добавить» откроет, пустую форму для ввода. Введите в таблицу дополнительные записи (записи приводятся далее):

Перемещать курсор между полями мышкой или клавишей Tab. После ввода закрыть окно формы и просмотреть, добавились ли ваши записи в таблицу.

8. Выборка по критериям.Выполнить команду Данные Þ Фильтр. Обратите внимание, что справа от названий столбцов вашей таблицы появилась кнопка для раскрытия списка. Откройте список в столбце «Имя» и установите выборку по «Ольге», выбрав ее из списка. Выборка должна иметь следующий вид (рис. 5.55):

 

Рисунок 5.55 – Таблица с автофильтром

Выведите выборку на печать. Отменить выборку, можно командой ДанныеÞОчистить.

Сделайте подобно и распечатайте выборку по наименованию любого из фильмов.

Выборка по условию: из списка критериев столбца «Кол-во» выберите «Числовые фильтры» и введите условие >5. Выведите на печать. Из списка критериев столбца «Дата» выберите критерий «Фильтры по дате» и введите условие >=10.янв.06 и <=12.янв.06. Выведите на печать. Очистите выборку.

 

Самостоятельное задание

 

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

Лабораторная работа №4 Сортировка данных в списке. Фильтрация записей

Цель работы: выполнение сортировки данн1ых, ознакомление со способом фильтрации записей списка, автофильтрацией, работой с формой данных.

Задание 1.

Выполнить несколько раз сортировку данных таблицы 5.5 в соответствии со следующими признаками - в алфавитном порядке фамилий покупателей, по убыванию суммы сделки, по возрастанию даты сделки, по совокупности признаков (фамилия, дата, сумма).

Методика выполнения работы

1. Откройте новую рабочую книгу и сохраните ее под именем «Сортировка» в своей рабочей папке.

2. Создайте таблицу представленную на рисунке 5.56.

 

Рисунок 5.56 – Начальная таблица с данными

3. Задайте параметры форматирования для таблицы.

Шрифт Times New Roman, размер шрифта 12 пт., для заголовков начертание полужирное и выравнивание по центру, перенос по словам, заливка серым цветом; для основной части. Напоминаем, что команды форматирования доступны на ленте Главная Þ Ячейки.

4. Для выполнения сортировки по полю фамилия покупателя поставьте курсор в любое место этого столбца и выполните команду Данные Þ Сортировка (рис. 5.51).

В открывшемся диалоговом окне в поле Сортировать по выберите «Фамилия покупателя». По возрастанию.

5. Повторите все шаги пункта 4 и задайте сортировку по «Сумме сделки», по убыванию.

6. Выполните повторно сортировку по полю «Дата сделки», по возрастанию.

7. Скопируйте таблицу на новый лист и на нем выполните сортировку по совокупности признаков. Для этого вызовите команду Данные Þ Сортировка. Установите Сортировать по фамилии в порядке возрастания, Затем по дате в порядке возрастания, В последнюю очередь, по сумме в порядке убывания.

8. С помощью команды Переименовать присвойте имена этим двум листам.

Задание 2. Осуществите выборку информации из списка на основе команды Автофильтр.

Методика выполнения работы.

1. На листе 4 создайте таблицу и заполните ее сведениями из таблицы 5.5.

2. Переименуйте Лист4, присвоив ему имя «Автофильтр №1».

3. Чтобы применить Автофильтрацию, установите курсор в область списка и выполните команду Данные ÞФильтр. Рядом с названиями граф таблицы появятся стрелки направленные вниз, которые раскрывают список возможных значений. В графе «Пол» выберите «М» Скопируйте таблицу на лист 5 и переименуйте его в «Автофильтр №2».

4. На листе «Автофильтр №1» в графе «Пол» откройте список фильтрования и выберите «Все». Затем в графе «Дата рождения» выберите в списке фильтрования «Условие» и задайте условие (рис.5.57):

 

 

Таблица 5.5

Фамилия	Имя	Дата приема на работу	Дата рождения	Пол	Оклад	Возраст
Пашков	Игорь	16.05.74	15.03.49	М
Андреева	Анна	16.01.93	19.10.66	Ж
Ерохин	Владимир	23.10.81	24.04.51	М
Попов	Алексей	02.05.84	07.10.56	М
Тюньков	Владимир	03.11.88	19.07.41	М
Ноткин	Евгений	27.08.85	17.08.60	М
Кубрина	Марина	20.04.93	26.06.61	Ж
Гудков	Никита	18.03.98	05.04.58	М
Горбатов	Михаил	09.08.99	15.09.52	М
Быстров	Алексей	06.12.00	08.10.47	М
Крылова	Татьяна	28.12.93	22.03.68	Ж
Бершева	Ольга	14.12.01	22.12.74	Ж
Русанова	Надежда	24.05.87	22.01.54	Ж

 

Рисунок 5.57 – Задание условий фильтрации

 

1. Скопируйте отфильтрованную таблицу на лист 6 и переименуйте его в «Автофильтр №3. На листе «Автофильтр №1» отмените выборку.

2. В столбце «Фамилия» выберите в списке фильтрации «Условие» и задайте условие на отбор всех сотрудников, чья фамилия начинается на «Б» (рис.5.58).

 

Рисунок 5.58 – Пользовательский фильтр

 

3. Скопируйте отфильтрованный список на лист 7 переименуйте его в «Автофильтр №4».

4. На листе «Автофильтр №1» для графы «Фамилия» задайте «Все», а в графе «Оклад» задайте «Первые 10…» где в диалоговом окне введите «Показать 5 наибольших элементов списка».

5. Сохраните файл.

Задание 3. Выполните отбор записей, из списка используя команду Расширенный фильтр.

 

Методика выполнения работы.

1. Перейдите на лист 8 и переименуйте его в «Расширенный фильтр».

2. Скопируйте на этот лист таблицу из предыдущего задания (табл. 5.5), вставьте ее, начиная со строки 7. Первые 6 строк отводятся для задания условий.

3. Создадим диапазон условий. Предположим, нам требуется отобрать фамилии сотрудников, которые получают больше 5000 руб. Или чей возраст превышает 50 лет. Заполните условия, как показано на рисунке 5.59.

Рисунок 5.59 – Условия для расширенного фильтра

 

4. Выполните команду Данные Þ Дополнительно. Заполните диалоговое окно следующим образом (рис. 5.60):

Рисунок 5.60 – Окно параметров расширенного фильтра

 

Просмотрите результаты отбора. При записи условий в одной строке реализуется логическое И. При записи условий на разных строках они считаются связанными логическим ИЛИ. Мы рассмотрели первый вариант, теперь рассмотрим второй.

5. Предположим нам требуется вывести только тех сотрудников, фамилии которых начинаются с букв А, Г или Н. Заполните диапазон условий (рисунок 5.61).

Рисунок 5.61 – Условия для расширенного фильтра

 

6. Выполните команду ДанныеÞДополнительно и заполните диалоговое окно (рисунок 5.62).

Рисунок 5.62 – Окно параметров расширенного фильтра

 

Просмотрите результаты отбора записей.

1. Выведите список всех сотрудников, заработная плата которых больше среднего уровня. Перед созданием этого фильтра введите в ячейку H2 формулу =СРЗНАЧ(F8:F20) для вычисления среднего оклада.

2. Затем в ячейку А2 вводим вычисляемое условие =F8>$H$2, ссылающееся на ячейку H2 (рисунки 5.63 и 5.64).

Рисунок 5.63 – Условия для расширенного фильтра

Рисунок 5.64 – Параметры расширенного фильтра

 

Просмотрите результаты.

Самостоятельное задание

Выполнить самостоятельно следующие задания:

1. вывести всех мужчин, чей возраст не превышает 50 лет; а оклад менее 5000 руб.

2. вывести список сотрудников, в который включить всех женщин старше 50 лет и всех мужчин старше 60 лет;

3. вывести список сотрудников, которые проработали не более 10 лет.