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

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. 
   Варианты заданий
   

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

2 варианта) Среднее количество детей по отделам у мужчин и женщин.б) Суммарную заработную плату у мужчин и женщин.3 варианта) Максимальное и минимальное количество детей по отделам у мужчин и женщин.б) Максимальную заработную плату у мужчин и женщин.4 варианта) Среднее количество детей для мужчин и женщин на каждой улице.б) Минимальную заработную плату у мужчин и женщин.5 варианта) Общее количество детей для мужчин и женщин на каждой улице. б) Среднюю заработную плату по улицам у мужчин и женщин.6 варианта) Средний возраст у мужчин и женщин в отделах.б) Максимальную заработную плату по улицам у мужчин и женщин.7 варианта) Средний возраст мужчин и женщин по улицам.б) Минимальную заработную плату по улицам у мужчин и женщин.8 варианта) Найти самых молодых мужчину и женщину на каждой улице.б) Общую сумму заработной платы по улицам у мужчин и женщин.9 варианта) Найти самых молодых мужчину и женщину в каждом отделе.б) Общий фонд заработной платы по улицам в каждом отделе.10 варианта) Самого старого мужчину и женщину на каждой улице.б) Суммарную заработную плату у мужчин и женщин по отделам.11 вариант а) Самого старого мужчину и женщину в каждом отделе.б) Максимальную заработную плату у мужчин и женщин по отделам.12 вариант а) Средний возраст сотрудников по улицам в каждом отделе.б) Среднюю заработную плату по улицам у мужчин и женщин.13 вариант а) Количество работников по улицам в каждом отделе.б) Суммарную заработную плату по улицам в каждом отделе.14 вариант а) Количество мужчин и женщин в каждом отделе.б) Минимальную заработную плату у мужчин и женщин по отделам.15 вариант а) Количество мужчин и женщин на каждой улице.б) Общий фонд заработной платы по отделам.3.5. Функции для работы с базами данных3.5.1. Общие сведенияБиблиотека Excel содержит тринадцать встроенных функций, позволяющих получить информацию из БД или произвести над ней необходимые вычисления. Все они находятся в категории Работа с базой данных.Назначение функций очевидно из их названий. Например:БДСУММ(База_данных; Поле; Критерий поиска);БСЧЕТ(База_данных; Поле; Критерий поиска); ДМИН(База_данных; Поле; Критерий поиска