Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

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

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

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

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

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

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

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

Более формально условие фильтрации записей в поставленной задаче можно записать следующим образом:
Отдел = «Плановый» И Оклад <10000

ИЛИ (1)

Отдел = «Производственный» И Оклад < 10000.
Чтобы использовать это условие для фильтрации его необходимо ввести в Excel. Вводить можно на тот же самый или на другой рабочий лист. Если условие вводится на тот же лист, то его обычно записывают над данными.

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

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

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

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

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

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

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

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

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

---

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

Данные > Фильтр > Отобразить все
Пример 2.

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

Формально условие фильтрации записывается следующим образом:
Отдел = «Плановый» И Оклад >10000 И Оклад <15000(2)
Оформить и разместить условие (2) в Excel можно следующим образом:

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

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

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

	F
6	Фамилия
7
8
9	Фамилия

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

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

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

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

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

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

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

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

---

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

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

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

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

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

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

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

10. Получить список всех пенсионеров, проживающих на ул. Водопроводная. Учесть, что женщины являются пенсионерами с 55 лет, а мужчины с 60.

11.Список женщин планового и производственного отделов с двумя детьми.

12. Список мужских имен.

2. 
   Средство «Итоги»
   

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

Очень часто для списков необходимо произвести итоговые вычисления. Их можно выполнить с помощью стандартных функций типа СУММА, СРЕДЗНАЧ и т.д. Но в случае списков намного удобнее использовать средства «Итоги» и «Сводные таблицы».
Пример.

Имеется база данных «Кадры». Рассчитать фонд заработной платы по отделам.

Внимание!! Очень важно!!

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

В рассматриваемом примере, таким полем будет являться поле «Отдел».

Непосредственно для расчетов:

Данные > Итоги > В появившемся окне в поле «При каждом изменении в» указать поле группировки (в данном случае «Отдел») > В поле «Операция» выбрать вид расчета (в данном случае – Сумма) > В поле «Добавить итоги по» поставить галочку на вычисляемое поле (в данном случае – Оклад) и снять галочки с остальных полей > Ok.

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

В рассматриваемом случае можно оставить только четыре записи:

Отдел	Оклад
Плановый Итог	414500
Производственный Итог	239500
Сбыта Итог	321000
Снабжения Итог	268000

---

Для того чтобы вернуть список в исходное состояние:

Данные > Итоги > Убрать все

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

Дана база данных кадры. С помощью средства «Итоги» рассчитать:

1 вариант

а) Количество детей по отделам.

б) Количество сотрудников в отделах
2 вариант

а) Среднее количество детей в каждом отделе.

б) Максимальную заработную плату по отделам.
3 вариант

а) Количество детей по улицам.

б) Минимальную заработную плату по отделам.
4 вариант

а) Среднее количество детей на каждой улице.

б) Фонд заработной платы по отделам.
5 вариант

а) Количество детей для мужчин и женщин.

б) Среднюю заработную плату по улицам.
6 вариант

а) Среднее количество детей для мужчин и женщин.

б) Суммарную заработную плату по улицам.
7 вариант

а) Средний возраст сотрудников в отделах.

б) Максимальную заработную плату по улицам.
8 вариант

а) Возраст самого старого сотрудника каждого отдела.

б) Минимальную заработную плату по улицам
9 вариант

а) Возраст самого молодого сотрудника каждого отдела.

б) Среднюю заработную плату мужчин и женщин.
10 вариант

а) Максимальный возраст мужчин и женщин.

б) Максимальную заработную плату мужчин и женщин.
11 вариант

а) Минимальный возраст мужчин и женщин.

б) Минимальную заработную плату мужчин и женщин.
12 вариант

а) Средний возраст мужчин и женщин.

б) Количество работников по улицам.
13 вариант

а) Возраст самого старого сотрудника на каждой улице.

---

б) Количество детей по отделам.
14 вариант

а) Возраст самого молодого сотрудника на каждой улице.

б) Суммарную заработную плату по улицам.
15 вариант

а) Средний возраст сотрудников по улицам.

б) Фонд заработной платы по отделам.

2. 
   Сводные таблицы
   

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

Данное средство также позволяет производить итоговые вычисления, но по более сложным критериям группировки. Кроме того, здесь не обязательна предварительная сортировка.
Пример.

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

Для решения задачи выполняются операции:

Данные > Сводная таблица > Будет вызван мастер сводных таблиц > В мастере щелкнуть: «Далее» > «Далее» > «Готово» > Появится пустая заготовка сводной таблицы (см. рис. 3.1) > В эту заготовку из списка полей перетащить нужные данные > С помощью кнопки «Параметры поля» выбрать вид выполняемой операции (в данном случае – Среднее) > Выделить получившуюся таблицу и (с помощью команд «Формат» > «Ячейки») установить числовой формат с двумя знаками после запятой.


Рис. 3.1. Порядок формирования сводной таблицы

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

Среднее по полю Оклад	Пол
Отдел	ж	м	Общий итог
Плановый	14406,25	11500,00	12953,13
Производственный	10772,73	13444,44	11975,00
Сбыта	13692,31	11000,00	12346,15
Снабжения	11000,00	13600,00	12181,82
Общий итог	12673,08	12166,67	12430,00

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

---