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

Примечание. Шаблон может быть размещен в произвольном месте листа или на другом листе. Главное требование к нему – это отсутствие конфликта с уже имеющимися данными.

в) Подготовленный шаблон выделяется (включая заголовки) и затем выполняются команды: Данные > Консолидация.

г) В появившемся окне «Консолидация» (рис. 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 функцию

---

ДЕНЬ.
7. Для прямого расчета значений столбца «День недели» (столбец I) в Excel нет соответствующей функции. Имеется только функция:

ДЕНЬНЕД(Дата в виде чч/мм/год; тип)

Но она вычисляет только порядковый номер дня недели.

Нам же желательно получить привычные названия, т.е. – Понедельник, Вторник…

Одним из вариантов решения данной задачи является создание собственных функций.

Создадим функцию, определяющую название дня недели, по его номеру.

а) Выполним команды:

Вид > Панели инструментов > Visual Basic

б) На появившейся панели Visual Basic выберем кнопку «Редактор Visual Basic»

В редакторе выполним команды:

Insert > Module и затем Insert > Procedure

в) Появится окно параметров создаваемой процедуры.

В этом окне:

– в качестве имени процедуры (Name) напечатать НазвДняНедели (без пробела!);

– переключатель типа (Type) установить в положение Function;

– щелкнуть Ok.

г) Должна появиться заготовка функции следующего вида:
Public Function НазвДняНедели()
End Function

д) Исправьте ее следующим образом (скопируйте!):
Public Function НазвДняНедели(k As Integer) As String

Select Case k

Case 1

S = "Понедельник"

Case 2

S = "Вторник"

Case 3

S = "Среда"

Case 4

S = "Четверг"

Case 5

S = "Пятница"

Case 6

S = "Суббота"

Case 7

S = "Воскресенье"

End Select

НазвДняНедели = S

End Function
е) Вернитесь в Excel.

ж) Чтобы использовать созданную функцию установите курсор в ячейку I4 и введите в нее формулу:

=НазвДняНедели(ДЕНЬНЕД(B4;2))

Здесь в качестве аргумента нашей созданной функции используется стандартная функция – ДЕНЬНЕД.

В I4 должно появиться слово «Четверг»

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

и) В I3 запишите название столбца – «День недели»
8. Для определения номера месяца по имеющейся дате в Excel имеется функция:

МЕСЯЦ(Дата в виде чч/мм/год).
Она также вычисляет только числовой номер месяца.
9. Самостоятельно создайте функцию, выводящую название месяца по его номеру (примерное название функции – НазвМесяца).

Этапы создания функции аналогичны этапам а) – и) пункта 7.

Аргументом данной функции должна быть стандартная функция МЕСЯЦ.

С помощью созданной функции заполните столбец J.
10. Самостоятельно создайте функцию, выводящую номер декады (примерное название функции –

---

Декада).

Этапы создания функции аналогичны этапам а) – и) пункта 7.

Аргументом данной функции должны быть значения столбца H.

С помощью созданной функции заполните столбец K.
11. Для решения заданий можно использовать любые средства Excel как для работы с базами данных (сортировка, фильтрация, итоги, функции и т.д.) так и функции общего назначения (ПРОСМОТР, ВПР, МИН, МАХ и т.д.)
12. Внимательно изучите задание своего варианта. Может оказаться, что вам потребуются дополнительные столбцы типа - общая сумма продаж (покупок), количество проданных (купленных) акций и т.д. Например, если необходимо определить количество сделок-продаж, то создается аналогичный столбец с формулой =ЕСЛИ(F4<0;1;0).
13. При выполнении заданий настоятельно рекомендуется использовать имена диапазонов ячеек.
14. Во всех вариантах по результатам расчетов п.2 и 3 построить диаграммы. Вид диаграмм определяется самостоятельно исходя из целесообразности и наглядности представления данных.

---

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

Вариант 1

1. 
   Отсортировать данные сначала по декадам, а внутри декад по месяцам, а внутри месяцев по фамилиям брокеров.
   
2. 
   Определить общее количество сделок для каждого брокера.
   
3. 
   Определить общее количество сделок для каждого брокера с акциями каждой компании.
   
4. 
   Определить общее количество сделок брокера Везунчикова с акциями Автоваза в первой декаде января.
   

Вариант 2

1. 
   Отсортировать данные сначала по декадам, а внутри декад по месяцам, а внутри месяцев по объемам продаж
   
2. 
   Определить количество сделок-продаж с акциями каждой компании.
   
3. 
   Определить количество сделок-продаж для каждого брокера по декадам.
   
4. 
   Определить количество сделок-продаж брокера Загребаева с акциями Газпрома по понедельникам в феврале
   

Вариант 3

1. 
   Отсортировать данные сначала по декадам, а внутри декад по месяцам, а внутри месяцев по объемам покупок.
   
2. 
   Определить количество сделок-покупок по дням недели.
   
3. 
   Определить количество сделок-покупок для каждого брокера по дням недели.
   
4. 
   Определить количество сделок-покупок брокера Коробочкина с акциями Лукойла по средам в марте.
   

Вариант 4

1. 
   Отсортировать данные сначала по декадам, а внутри декад по дням недели, а внутри недель по объемам продаж.
   
2. 
   Определить общее количество акций по месяцам.
   
3. 
   Определить количество акций для каждого брокера по месяцам.
   
4. 
   Определить количество акций Норильскникель, с которыми оперировал брокер Кубышкин, в первой декаде апреля.
   

Вариант 5

1. 
   Отсортировать данные сначала по декадам, а внутри декад по дням недели, а внутри недель по объемам покупок.
   
2. 
   Определить количество проданных акций для каждого брокера.
   
3. 
   Определить количество проданных акций каждой компании по декадам.
   
4. 
   Определить количество акций Промтрактор, проданных брокером Халявиным, во второй декаде мая.
   

Вариант 6

1. 
   Отсортировать данные сначала по декадам, а внутри декад по объемам продаж, а внутри продаж по объемам покупок.
   
2. 
   Определить количество купленных акций каждой компании.
   
3. 
   Определить количество купленных акций каждой компании по дням недели.
   
4. 
   Определить количество акций Росавиа, купленных брокером Везунчиковым, в третьей декаде июня.
   

---