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

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

1 вариант

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

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

---

2 вариант

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

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

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

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

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

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

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

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

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

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

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

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

а) Найти самых молодых мужчину и женщину на каждой улице.

б) Общую сумму заработной платы по улицам у мужчин и женщин.

9 вариант

а) Найти самых молодых мужчину и женщину в каждом отделе.

б) Общий фонд заработной платы по улицам в каждом отделе.
10 вариант

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

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

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

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

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

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

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

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

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

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

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

б) Общий фонд заработной платы по отделам.
3.5. Функции для работы с базами данных

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

Библиотека Excel содержит тринадцать встроенных функций, позволяющих получить информацию из БД или произвести над ней необходимые вычисления. Все они находятся в категории Работа с базой данных.

Назначение функций очевидно из их названий. Например:
БДСУММ(База_данных; Поле; Критерий поиска);

БСЧЕТ(База_данных; Поле; Критерий поиска);

ДМИН(База_данных; Поле; Критерий поиска

---

);

ДМАКС(База_данных; Поле; Критерий поиска);

ДСРЗНАЧ(База_данных; Поле; Критерий поиска).
Все функции имеют один и тот же формат:

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

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

– третий параметр представляет собой ссылку на критерии поиска.

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

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

Порядок присвоения имен:

1. 
   С помощью мыши выделить все ячейки, содержащие базу данных.
   
2. 
   В строке формул в ячейку адреса текущей ячейки ввести имя базы данных (рис. 3.2):
   

Рис. 3.2. Порядок присвоения имени БД
Пример 1.

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

Для решения в произвольном месте рабочего листа записывается условие отбора записей для расчетов:

	M	N	O
9
10		Отдел
11		Снабжения
12
13		12181,81

А в ячейку N13 ввести формулу:
=ДСРЗНАЧ(Данные;G5;N10:N11),
где G5 – адрес заголовка «Оклад»;

N10:N11 – адрес критерия фильтрации.
Пример 2.

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

При решении задач, связанных возрастом, рекомендуется создать поле «Возраст». Для этого в ячейку L5 ввести название поля, т.е. – «Возраст», а в ячейку L6 ввести формулу: =2009-H6, которая затем копируется на весь столбец L.

---

Непосредственно для решения в свободном месте листа вводится условие фильтрации:

	M	N	O	P
15
16		Пол	Возраст
17		м	>=60
18		ж	>=55
19
20		18

А в ячейку N20 ввести формулу:
=БСЧЁТ(Данные;;N16:O18)
Примечание. Для функции БСЧЕТ в качестве заголовка поля можно указывать любое поле или даже просто не вводить его.

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

Дана база данных «Кадры». С помощью функций работы с базами данных рассчитать:

1 вариант

а) Общее количество мужчин в плановом и производственном отделах.

б) Количество работников планового отдела, проживающих на улице Хевешская и по проспекту Мира.
2 вариант

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

б) Средний возраст мужчин с именами Алексей и Андрей.
3 вариант

а) Средний возраст женщин с именами Ольга и Мария.

б) Количество детей у мужчин в плановом и производственных отделах.
4 вариант

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

б) Среднее количество детей в организации, приходящееся на одного работника.
5 вариант

а) Суммарную заработную плату у мужчин.

б) Максимальное количество детей у мужчин с именами Олег и Сергей.
6 вариант

а) Среднее количество детей у женщин, проживающих на ул. Водопроводная.

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

а) Общее количество детей у мужчин, проживающих на ул. Горького.

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

---

8 вариант

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

б) Средний возраст у мужчин в производственном отделе.
9 вариант

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

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

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

б) Самого молодого мужчину на ул. Лебедева.
11 вариант

а) Общую сумму заработной платы в плановом отделе.

б) Самую старшую женщину в отделе сбыта.
12 вариант

а) Общий фонд заработной платы для работников с одним ребенком.

б) Самого старого мужчину на ул. Володарского.
13 вариант

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

б) Количество мужчин, у которых нет детей.
14 вариант

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

б) Средний возраст женщин на ул. Яковлева.
15 вариант

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

б) Количество работников с двумя детьми.
3.6. Консолидация данных
3.6.1. Общие сведения

Средство «Консолидация» представляет собой еще одну возможность для выполнения итоговых вычислений. С его помощью можно обобщить данные, расположенные на разных листах, или в разных местах одного листа. Единственное требование к консолидируемым данным – они должны иметь одинаковую структуру.

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

Дана база данных «Кадры». Определить средний оклад в производственном и плановом отделах.

Решение задачи состоит из следующих этапов.

а) Столбец, по которому выполняется консолидация, переставляется на первое место в исходной таблице. В нашем примере это столбец «Отдел».

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

		N	O	P
1		Отдел	Оклад
2		Производственный
3		Плановый
4

---