Тема: Базы данных в табличных процессорах

Цель: закрепить навыки работы с табличным редактором, научиться работать с базами данных в табличных процессорах

Оборудование: ПК;

ПО: ОС Windows, Microsoft Excel.

Ход работы:

Если курсор находится на каком-то элементе списка, то MS Excel воспринимает этот список как базу данных (БД). Первая строка списка воспринимается как заголовки полей, а строки – как конкретные записи. Одновременно автоматически определяются максимальные размеры БД по вертикали и горизонтали даже в том случае, если в списке имеются пропуски.

Для работы с БД используется пункт «Данные» главного меню. Рассмотрим операции, связанные с отбором данных и итоговыми вычислениями.

На сетевом диске находится учебная база данных «Кадры.xls».

Скопируйте этот файл в свою рабочую папку и откройте его.

КАЖДОЕ ИЗ ПОСЛЕДУЮЩИХ ЗАДАНИЙ НЕОБХОДИМО

ВЫПОЛНЯТЬ НА ОТДЕЛЬНОМ ЛИСТЕ!!!

Сортировка

Общие сведения

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

Но в практике делопроизводства не редки случаи, когда необходима сортировка в порядке, отличном от алфавитного.

Организацию собственного порядка сортировки рассмотрим на следующем примере.

Пусть нам необходимо отсортировать список работников по месту работы в следующем порядке:

– сначала должны идти работники планового отдела, затем отдела сбыта, далее отдела снабжения и, наконец, работники производственного отдела.

Выполнение поставленной задачи состоит из следующих этапов:

1. Создается собственный список сортировки:

Данные>Сортировка > Сортировать по Отдел> Порядок (в данном случае: Плановый, Сбыта, Снабжения, Производственный > Щелкнуть кнопку «Добавить» > Ok.

Обратите внимание: элементы списка печатаются именно в том виде, в котором они присутствуют в списке – с большой буквы и соответствующем падеже.

Варианты заданий

Имеется база данных «Кадры». Отсортировать ее в следующем порядке

1. По отделам: сначала - плановый, затем - сбыта, далее - производственный и, наконец, снабжения.

2. По количеству детей: сначала работники с одним ребенком, затем с двумя детьми и, наконец, работники у которых нет детей.

3. По месту проживания: сначала работники, проживающие по ул. Лебедева, затем по ул. Хевешская, далее по ул. Мира и т. Д.

4. По именам: сначала Владимиры, затем Алексеи и далее все остальные.

5. По фамилиям: сначала Ивановы, затем Петровы и далее все остальные.

6. По количеству детей: сначала работники с двумя детьми, затем без детей и, наконец, работники у которых один ребенок.

7. По отделам: сначала - сбыта, затем - плановый, далее - снабжения и, наконец, производственный.

8. По отделам: сначала - снабжения, затем - сбыта, далее - производственный и, наконец, плановый.

9. По количеству детей: сначала работники с одним ребенком, затем с двумя и, далее, работники у которых детей нет.

10. По фамилиям: сначала Алексеевы, затем Петровы, далее Ивановы и, наконец, все остальные.

11. По месту проживания: сначала работники, проживающие по ул. Мира, затем по ул. Хевешская, далее ул. Лебедева и потом все остальные.

12. По именам: сначала Ольга, Вера, Елена и далее все остальные.

13. По именам: Иван, Олег, Петр и далее все остальные.

Фильтрация данных

Общие сведения

Для фильтрации (отсеивания) данных имеется два средства – «Автофильтр» и «Расширенный фильтр». Оба они вызываются посредством:

Данные > Фильтр.

Использование средства «Автофильтр» не вызывает трудностей. С помощью появившихся флажков можно по каждому полю установить критерий отбора на конкретное значение или произвести отбор по условию.

Средство «Расширенный фильтр» более мощное и позволяет производить отбор записей по комплексным условиям.

Пример 1.

Имеется база данных «Кадры». Необходимо получить сведения о работниках планового и производственного отделов, имеющих зарплату меньше 10000 рублей.

Более формально условие фильтрации записей в поставленной задаче можно записать следующим образом:

Отдел = «Плановый» И Оклад <10000

ИЛИ (1)

Отдел = «Производственный» И Оклад < 10000.

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

Условия записываются по следующим правилам:

– в качестве первой строки выписываются точные названия полей, для которых задаются условия;

– во второй (и последующих) строках записываются условия отбора;

– если условия отбора записаны в разных строках, то они объединяются по правилу «ИЛИ»;

– если условия записаны в одной строке, то они объединяются по правилу «И».

Пусть условия (1) размещены следующим образом:

A	B	C	D
	Отдел	Оклад
	Плановый	<10000
	Производственный	<10000

Для выполнения фильтрации выполняются операции:

Курсор устанавливается в любое место списка данных > Данные > Дополнительно> В появившемся окне поле «Исходный диапазон» будет уже заполнено адресом базы данных > В поле «Диапазон условий» указать $A$1:$C$3 > Ok.

Для того чтобы убрать результаты фильтрации:

Данные > Очистить

Пример 2.

Имеется база данных «Кадры». Необходимо получить сведения о работниках планового отдела, имеющих зарплату более 10000 и менее 15000 рублей.

Формально условие фильтрации записывается следующим образом:

Отдел = «Плановый» И Оклад >10000 И Оклад <15000(2)

Оформить и разместить условие (2) в Excel можно следующим образом:

F	G	H
Отдел	Оклад	Оклад
Плановый	>10000	<15000

При таком размещении во время фильтрации в поле «Диапазон условий» следует указать $F$1:$H$2.

Пример 3.

Имеется база данных «Кадры». Необходимо получить сведения о фамилиях всех работников организации.

Критерий фильтрации будет иметь вид названия поля и пустой ячейки под ним:

	F
	Фамилия


	Фамилия

Кроме того, в отдельной ячейке для вывода результаты фильтрации необходимо указать название поля – в таблице это ячейка F9.

При таком размещении данных:

Данные > Дополнительно> В поле «Диапазон условий» указать $F$6:$F$7 > Установить флажок «Только уникальные записи» > Установить переключатель «Скопировать результат в другое место» > В поле «Поместить результат в диапазон» указать $F$9 > Ok.

Варианты заданий

Имеется база данных «Кадры».

С помощью средства «Расширенный фильтр» получить:

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

2. Список сотрудников отдела сбыта, проживающих по ул. Хевешская и Мира.

3. Список сотрудников с фамилиями «Иванов» и «Петров» и окладом в пределах от 7500 до 15000 руб.

4. Список всех пенсионеров с окладом менее 10000 рублей (учесть, что женщины являются пенсионерами с 55 лет, а мужчины с 60).

5. Список сотрудников всех отделов с окладом от 10000 до 17000 рублей.

6. Список всех имен сотрудников.

7. Получить список улиц, на которых проживают сотрудники организации.

8. Получить список фамилий сотрудников производственного отдела.

9. Определить улицы, на которых проживают работники отдела сбыта