Лабораторная работа №5. «Функции для работы с базами данных»

Условие:

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

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

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

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

Результат задания а): Суммарную заработную плату у мужчин;

	Р
	пол
	м

И в ячейку Р12 водим формулу = БДСУММ(A2:K102;G2;P9:P10)

Результат задания б): Максимальное количество детей у мужчин с именами Олег и Сергей. Выполняем задание как пункт а).

M	N	O	P
	Имя		дети
	Сергей
	Имя
	Олег

P18= ДМАКС (A2:K102;H2;N15:N16)

P24= ДМАКС (A2:K102;H2;N23:N24)

Лабораторная работа № 6. «Консолидация данных»

Условие:

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

а) Количество работников с именами Елена, Ольга и Людмила.

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

Порядок выполнения:

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

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

В появившемся окне «Консолидация»:

- выбраем вид вычисления (в данном примере - функция «Количество»);

- сформируем ссылку на базу данных. Для этого находясь в поле «Ссылка» обводим мышью базу данных и затем щелкнув по кнопке «Добавить»;

- поставим галочки на переключатели «Подписи верхней строки» и «Значения левого столбца»;

- щелкаем «Ok».

Результат задания а): Количество работников с именами Елена, Ольга и Людмила.

	Имя	Таб.№
	Людмила
	Ольга
	Елена

Результат задания б):Суммарную и среднюю заработную плату работников, тех же работников.

Задание б) проделываем также как и пункт а).

Суммарная Средняя

Имя	Оклад
Людмила
Ольга
Елена

Имя	Оклад
Людмила
Ольга
Елена	8833,333

Контрольная работа

Для выполнения задания используется файл Brokers.xls

Первое задание: Отсортировать данные сначала по декадам, а внутри декад по дням недели, а внутри недель по объемам покупок.

1. Для выполнения многих заданий необходимо самостоятельно организовать новые столбцы. В частности практически обязательны столбцы «Стоимость сделки», «День», «Месяц», « День недели» и «Декада».

2. Столбец Стоимость сделки рассчитаем по формуле:

= Количество акций* Цена акции.

3. Столбец День рассчитаем, используя имеющуюся в Excel функции День

=ДЕНЬ(B4)

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

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

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

Insert> Module затем Insert> Procedure

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

Вернемся в Excel. Чтобы использовать созданную функцию установим курсор в ячейку J4 и ведем формулу

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

5. Для определения номера месяца по имеющейся дате в Excel имеется функция Месяц. И в толбце К4 водим формулу

=НазвМесяца(МЕСЯЦ(B4))

Также аналогично заполняем столбец Месяца и Декаду как Дни Недели.

=НазвМесяца(МЕСЯЦ(B4))

=Декада(H4)

Задание 1.Отсортировать данные сначала по декадам, а внутри декад по дням недели, а внутри недель по объемам покупок.

Создали столбец Объем покупок и вели формулу :

=ЕСЛИ(F4>0;F4;0)

Порядок выполнения: Сервис >Параметры >Список>В появившемся окне в поле >«Элементы списка» через запятую напечатаем нужные в списке «Добавить»> Оk.

Дальше начинаем сортировку Данные> Сортировка

Задание 2. Определить количество проданных акций для каждого брокера

Проданные акции (объем продаж). Для этого создаем столбец G4 ( с формулой =ЕСЛИ(F4<0;-F4;0)

Сортируем сначала брокеров.

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

Проданные акции	(Все)
Сумма по полю Проданные акции
Брокер	Итог
Везунчиков
Загребаев
Коробочкин
Кубышкин
Халявин
Общий итог

Составляем Диаграмму на основе полученных данных.

Задание 3. Определить количество проданных акций каждой компании по декадам.

Проделываем также как задание 2.

Сумма по полю Проданные акции	Декада
Акции	1 Декада	2 Декада	3 Декада	4 Декада	Общий итог
Автоваз
Газпром
Лукойл
НорильскНикель
Промтрактор
Росавиа
Рособувь
РусАл
Татнефть
Химпром
Общий итог

Задание 4. Определить количество акций Промтрактор, проданных брокером Халявиным, во второй декаде мая.

Для этого создаем таблицу в произвольной форме. В ячейке Q5 вводим функцию:

= БДСУММ(Данные;G3;Q2:T3)

Получаем результат.

	Q	R	S	T
	Акции	Брокер	Декада	Месяц
	Промтрактор	Халявин	2 Декада	Май