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

);

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

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

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

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

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

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

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

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

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

Примечание. Шаблон может быть размещен в произвольном месте листа или на другом листе. Главное требование к нему – это отсутствие конфликта с уже имеющимися данными.в) Подготовленный шаблон выделяется (включая заголовки) и затем выполняются команды: Данные > Консолидация.г) В появившемся окне «Консолидация» (рис. 3.3) необходимо:Рис. 3.3. Окно Консолидация– выбрать вид вычисления (в данном примере - функция «Среднее»);– сформировать ссылку на базу данных. Для этого находясь в поле «Ссылка» обвести мышью базу данных и затем щелкнуть по кнопке «Добавить»;– поставить галочки на переключатели «Подписи верхней строки» и «Значения левого столбца»;– щелкнуть «Ok». Должны появиться следующие результаты:

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

3.6.2. Варианты заданийИмеется база данных «Кадры». С помощью средства консолидация определить:Вариант 1а) Количество работников, проживающих на ул. Хевешская, Мира и Горького.б) Суммарную и среднюю заработную плату работников, проживающих на тех же улицах.Вариант 2а) Количество детей у работников планового и производственного отделов.б) Суммарную и среднюю заработную плату у тех же работников.Вариант 3а) Суммарную и среднюю заработную плату у работников, имеющих детей.б) Количество работников, имеющих детей.Вариант 4а) Количество работников с именами Иван, Петр и Алексей.б) Суммарную и среднюю заработную плату тех же работников.Вариант 5а) Количество работников с именами Елена, Ольга и Людмила.б) Суммарную и среднюю заработную плату работников тех же работников.

Вариант 6а) Количество мужчин, работающих в организации.б) Суммарную и среднюю заработную плату мужчинВариант 7а) Количество женщин, работающих в организации.б) Суммарную и среднюю заработную плату у женщинВариант 8а) Количество работников, проживающих на ул. Хевешская, Мира и Горького.б) Суммарную и среднюю заработную плату работников, проживающих на тех же улицах.Вариант 9а) Количество детей у работников планового и производственного отделов.б) Суммарную и среднюю заработную плату у тех же работников.Вариант 10а) Суммарную и среднюю заработную плату у работников, имеющих детей.б) Количество работников, имеющих детей.Вариант 11а) Количество работников с именами Иван, Петр и Алексей.б) Суммарную и среднюю заработную плату тех же работников.Вариант 12а) Количество работников с именами Елена, Ольга и Людмила.б) Суммарную и среднюю заработную плату работников тех же работников.Вариант 13а) Количество мужчин, работающих в организации.б) Суммарную и среднюю заработную плату мужчинВариант 14а) Количество женщин, работающих в организации.б) Суммарную и среднюю заработную плату у женщин3.7. Контрольная работа
по теме «Базы данных в Excel»3.7.1. Указания1. Для выполнения заданий используется файл Brokers.xls, находящийся на сетевом диске.2. Скопируйте указанный файл в свою рабочую папку и вся дальнейшая работа должна производиться только с этой копией.3. В заданиях используются следующие понятия:Сделка – факт совершения любой операции (купли или продажи);Продажа - означает количество акций со знаком «минус»;Покупка - означает количество акций со знаком «плюс»;Стоимость сделки – вычисляется по формуле:Стоимость сделки = Количество_акций * Цена_акцииСумма продаж – суммарная стоимость сделок со знаком «минус»;Сумма покупок – суммарная стоимость сделок со знаком «плюс».4. Для выполнения многих заданий необходимо самостоятельно организовать новые столбцы. В частности практически обязательны столбцы «Стоимость сделки», «День», «Месяц», «День недели» и «Декада».5. Столбец «Стоимость сделки» (столбец G) рассчитать по формуле, приведенной в п. 3.6. Столбец «День» рассчитать (столбец H), используя имеющуюся в Excel функцию