lab1
.pdf
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Шаг 2 (рисунок 12).
На данном шаге необходимо выбрать поля, по которым будет строиться сводная таблица, и перетащить выбранные поля в соответствующие области.
Поле Дата надо перетащить в область «Название столбцов», в которой содержатся данные для формирования заголовков столбцов.
Поля Продавец и Товар должны быть расположены в области «Названия строк», а поле «Выручка» перетаскивается в окно «Значения».
Рисунок 12 - Шаг 2
Изменение типа вычисляемого итога в сводной таблице
По умолчанию в сводной таблице всегда вычисляется сумма по полям, внесенным в область Данные. Тип итога можно изменить непосредственно в сводной таблице.
Задание. Создать копию листа со сводной таблицей и изменить в сводной таблице тип вычисляемого итога с суммы на среднее арифметическое.
Для того, чтобы изменить тип итога необходимо:
-поставить курсор на ячейку с итоговыми данными, -правой кнопкой мыши вызвать контекстное меню и задать команду
Параметры полей значений, -в окне Операция выбрать новый тип итога (рисунок 13).
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Рисунок 13. Изменение типа итога
Скопируйте новую сводную таблицу на другой лист и снова измените в исходной сводной таблице тип итога на сумму.
Примечание. Команду Параметры полей значений также можно задать непосредственно в области «Значения».
Дополнительная группировка итогов в сводной таблице
В созданной таблице можно еще раз сгруппировать данные и вычислить итоговые суммы для вновь сгруппированных данных.
Задание. Сгруппировать в сводной таблице имеющиеся даты по месяцам и вывести итоговые данные за месяц. Новую сводную таблицу скопировать на другой лист.
Для группировки надо перевести курсор на любую дату, вызвать контекстное меню и задать команду [Группировать]. Затем указать шаг группировки – месяц.
Скопируйте сгруппированную таблицу на новый лист. При вставке копии на другой лист надо использовать команду «Специальная вставка» (по значению).
Задание. Откройте исходный лист с группировкой дат и отмените группировку.
Для того, чтобы разгруппировать итоги надо поставить курсор на значение группового поля (название месяца – « янв») и задать команду [Разгруппировать] в контекстном меню.
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Создание сводных таблиц с выбором отдельных значений из списка
Задание. На основе исходной базы данных создать на новом листе сводную таблицу, в которой дату можно было бы выбрать из списка. В таблице в заголовках строк выводятся фамилии продавцов, а в заголовках столбцов – названия товаров. В клетках сводной таблицы – суммарная выручка продавца по товару.
Порядок работы точно такой же, как и при создании предыдущей сводной таблицы.
На шаге 2 (рисунок 14) необходимо перетащить поле Дата в область «Фильтр отчета».
Данная область используется при создании списка для выбора данных
Поле Продавец располагается в области «Название строк»,
Поле Товар надо перетащить в область «Название столбцов».
Поле Выручка должно быть расположено в области «Значения».
Рисунок 14-Выбор полей сводной таблицы
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Самостоятельные работы к лабораторной работе №4
Вариант 1
Создать базу данных по данным следующей таблицы.
Таблица 1
КВАРТИРНЫЙ РЫНОК
Адрес |
Кол |
Этаж |
Кол-во |
Об- |
Жи- |
Кух- |
Тип |
Агенство |
Стои- |
|
|
-во |
|
комнат |
щая |
лая |
ня |
дома |
|
мость, |
|
|
эта- |
|
|
площ. |
площ |
|
|
|
|
у.е. |
|
жей |
|
|
|
|
|
|
|
|
|
ул.Тимме |
5 |
5 |
1 |
30 |
17 |
6 |
пан. |
АТС |
50000 |
|
ул.Садовая |
9 |
2 |
1 |
32.2 |
12 |
9 |
93 |
сер. |
Арх.недвижи |
38000 |
|
|
|
|
|
|
|
|
|
мость. |
|
ул.Дачная |
9 |
2 |
1 |
30 |
18 |
6 |
ул.пан |
Арх.недвижи |
26000 |
|
|
|
|
|
|
|
|
|
|
мость. |
|
ул.Дрейера |
2 |
2 |
2 |
40 |
25 |
8 |
дер. |
Инфосервис |
15000 |
|
ул.Воронина |
9 |
2 |
2 |
42 |
28 |
7 |
ул.пан |
Инфосервис |
30000 |
|
|
|
|
|
|
|
|
. |
|
|
|
ул.Садовая |
9 |
1 |
2 |
50 |
30 |
8.5 |
93 |
сер. |
Инфосервис |
68000 |
ул.Галушина |
10 |
9 |
3 |
67 |
39 |
9 |
93 |
сер. |
АТС |
76000 |
ул.Попова |
9 |
9 |
3 |
62 |
42 |
8 |
ул.пан |
СаиН |
80000 |
|
|
|
|
|
|
|
|
. |
|
|
|
ул.Садовая |
10 |
4 |
4 |
82 |
58 |
9 |
93 |
сер. |
Инфосервис |
95000 |
ул.Победы |
9 |
8 |
4 |
77 |
57 |
7 |
ул.пан |
Инфосервис |
61000 |
|
|
|
|
|
|
|
|
. |
|
|
|
ул.Логинова |
9 |
7 |
5 |
118 |
80 |
10 |
93 |
сер. |
Инфосервис |
100000 |
2.Отсортировать базу по количеству комнат в квартирах в порядке убывания.
3.Сделать запрос к базе на выборку квартир с одинаковым количеством комнат.
4.Отсортировать базу в алфавитном порядке по АГЕНТСТВАМ
5.Сделать запрос к базе на выборку квартир, продаваемых одним и
итем же агентством. Выбранные строки отсортировать по кол-ву комнат в квартирах.
6.Создать сводную таблицу с информацией о средней стоимости квартир различных типов с различным количеством комнат.
7. Рассчитать количество квартир, выставленное на продажу каждым агентством недвижимости.
Каждое задание выполнить на отдельном листе.
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Вариант 2
1. Создать базу данных «КВАРТИРНЫЙ РЫНОК» по данным следующей таблицы.
Таблица 2
КВАРТИРНЫЙ РЫНОК
Адрес квартиры |
Кол |
Эта |
Кол- |
Об- |
Жи- |
Площ |
Тип |
Агенство |
Стои- |
|
-во |
ж |
во |
щая |
лая |
адь |
дом |
недвижимост |
мость , |
|
эта- |
|
ком- |
пло- |
пло- |
кухн |
а |
и |
у.е. |
|
жей |
|
нат |
щадь |
щадь |
и |
|
|
|
пр.Сов.Космонавтов, |
5 |
1 |
2 |
47,3 |
33,1 |
6 |
пан. |
Северный |
100000 |
154 |
|
|
|
|
|
|
|
Меркурий |
|
ул.Воскресенская, 7 |
12 |
11 |
1 |
33,4 |
17,8 |
6,8 |
инд. |
Архангельска |
78000 |
|
|
|
|
|
|
|
кирп |
я |
|
|
|
|
|
|
|
|
|
недвижимость |
|
ул. Тимме,21 |
9 |
8 |
1 |
31 |
17 |
6 |
улья |
Архангельска |
90000 |
|
|
|
|
|
|
|
н. |
я |
|
|
|
|
|
|
|
|
|
недвижимость |
|
ул.Галушина,11 |
9 |
1 |
1 |
30 |
17 |
6 |
пан. |
Архангельска |
63000 |
|
|
|
|
|
|
|
|
я |
|
|
|
|
|
|
|
|
|
недвижимость |
|
пр.Обводный |
5 |
5 |
2 |
43 |
31 |
6 |
пан. |
Архангельска |
98000 |
канал,16 |
|
|
|
|
|
|
|
я |
|
|
|
|
|
|
|
|
|
недвижимость |
|
ул.Воскресенская,85 |
5 |
3 |
3 |
55 |
41 |
6 |
кирп |
Архангельска |
125000 |
|
|
|
|
|
|
|
|
я |
|
|
|
|
|
|
|
|
|
недвижимость |
|
ул.Воскресенская,112 |
9 |
5 |
1 |
36 |
18 |
8 |
кирп |
ЗАО "Альфа- |
93000 |
|
|
|
|
|
|
|
. |
Центр" |
|
пр.Ломоносова,260 |
2 |
5 |
2 |
43 |
30,5 |
6.5 |
пан. |
ЗАО "Альфа- |
88000 |
|
|
|
|
|
|
|
|
Центр" |
|
2.Отсортировать базу по количеству комнат в квартирах в порядке возрастания.
3.Сделать запрос к базе на выборку квартир одного типа.
4.Отсортировать базу в порядке убывания по общей площади.
5.Сделать запрос к базе на выборку двухкомнатных квартир, находящихся не выше пятого этажа.
6.Создать сводную таблицу с информацией о квартирах, проданных каждым агентством. Агентство должно выбираться из списка.
7.Вычислить среднюю общую площадь среди квартир с одинаковым количеством комнат.
Каждое задание выполнить на отдельном листе.
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Вариант 3
1. Создать базу данных "СУПЕРМАРКЕТ" со следующими полями:
Номер товара Наименование товара
Группа товара (Кондитерские изделия, Молочные продукты и т.д.) Страна-изготовитель товара Единица измерения Цена Количество
2.Отсортировать эту базу так, чтобы наименования товаров расположились в алфавитном порядке.
Осуществить поиск товара по наименованию.
4.Выделить из базы все товары, относящиеся к какой-то одной группе. Внутри этой группы сделать сортировку по цене в порядке возрастания
5.Выделить из базы все товары, изготовленные в какой-то одной стране.
Вычислите стоимость каждого товара, умножив цену на количество. Создать сводную таблицу, показывающую суммарную стоимость по каждой группе товара.
7.Определите максимальную цену среди товаров, относящихся к одной группе.
8.Создайте сводную таблицу, по которой можно узнать о цене товаров, изготовленных в определенной стране. Страна-изготовитель должна выбираться из списка.
Каждое задание необходимо выполнить на отдельном листе.
Исходная база данных должна состоять не менее чем из 15 строк.
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Вариант 4
1.Создайте базу данных по данным следующей таблицы.
Таблица 3 ИНФОРМАЦИЯФОНДАИМУЩЕСТВААРХАНГЕЛЬСКОЙОБЛАСТИ
Наименование |
Отрасль |
Кол-во в |
Кол-во во |
Номинал, |
|
промышлен- |
первом |
втором |
руб. |
|
ности |
пакете |
пакете |
|
|
|
|
|
|
Севтрансстрой |
Транспорт |
415 |
267 |
15000 |
|
|
|
|
|
Ленский ЛПХ |
Лесозаготовки |
1613 |
|
5000 |
|
|
|
|
|
Котласский з-д керамических изд. |
Строительство |
|
2106 |
3000 |
|
|
|
|
|
Союз |
Строительство |
170 |
631 |
7000 |
|
|
|
|
|
Мезенский лесозавод |
Лесозаготовки |
929 |
752 |
2500 |
|
|
|
|
|
Котласский химзавод |
Хим. пром. |
311 |
|
12000 |
|
|
|
|
|
Коношский комбикормовый з-д |
Сельское х-во |
368 |
|
10000 |
|
|
|
|
|
Архстальконструкция" |
Строительство |
178 |
222 |
7000 |
|
|
|
|
|
МККП "Архсельстроя" |
Сельское х-во |
101 |
58 |
15000 |
|
|
|
|
|
Вилегодский ЛПХ |
Лесозаготовки |
315 |
|
6000 |
|
|
|
|
|
Новодвинское АТП |
Транспорт |
293 |
68 |
3500 |
|
|
|
|
|
Такси-Сервис |
Транспорт |
85 |
37 |
10000 |
|
|
|
|
|
Вторресурсы(Северодвинск) |
Хим. пром. |
304 |
|
2500 |
|
|
|
|
|
Спецэлектромонтаж |
Строительство |
125 |
|
5000 |
|
|
|
|
|
Котласский уч-ок механизации |
Сельское х-во |
65 |
80 |
4000 |
|
|
|
|
|
Онежский з-д ЖБИ |
Строительство |
741 |
|
500 |
|
|
|
|
|
Архтисиз |
Транспорт |
118 |
|
2500 |
|
|
|
|
|
Трест дорожного хоз-ва |
Строительство |
11 |
|
4500 |
|
|
|
|
|
2. |
Отсортируйте базу так, чтобы организации расположились в |
||||||
алфавитном порядке. |
|
|
|
|
|||
3. |
Определите общую стоимость выставленных на продажу акций для |
||||||
каждой организации как сумму стоимости первого и |
второго пакета, |
||||||
умноженную на номинал акции. |
|
|
|
|
|||
|
Отсортируйте базу по общей стоимости пакетов в порядке убывания. |
||||||
Создайте |
на |
отдельном листе |
сводную |
таблицу, |
показывающую |
||
суммарную стоимость акций по |
каждой организации с распределением по |
||||||
отраслям промышленноси. |
|
|
|
|
|||
4. |
Распределите |
предприятия |
по |
группам |
в зависимости от общей |
||
стоимости выставленных на продажу акций. Скопируйте каждую группу на другой лист. Первая группа - до Стоимости1 (любое число).
Вторая группа – от Стоимости1 до Стоимости2. Третья группа - больше Стоимости2 (любое число).
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Вариант 5
1.Создать базу данных по данным приведенной ниже таблицы.
Таблица 4
. ИТОГИ СПЕЦИАЛИЗИРОВАННОГО АУКЦИОНА
N |
Организация |
Отрасль |
Продавало |
Объявленный |
|
|
промышленности |
сь акций |
номинал акций |
|
|
|
|
|
1 |
Коношский л/з |
Лесная |
3590 |
1000 |
|
|
|
|
|
2 |
Двинской КЛПХ |
Лесная |
7068 |
1000 |
|
|
|
|
|
3 |
Двинские лесопромышленники |
Лесная |
11437 |
1000 |
|
|
|
|
|
4 |
АрхангельскСтальконструкция |
Строительство |
1061 |
1000 |
|
|
|
|
|
5 |
Савинский завод абсоцем. изделий |
Строительство |
2015 |
1000 |
|
|
|
|
|
6 |
Арх. обл. РСТ дор. хозяйства |
Строительство |
312 |
1000 |
|
и благоустройства |
|
|
|
|
|
|
|
|
7 |
Архавтотранс |
Транспорт |
4198 |
200 |
|
|
|
|
|
8 |
Котласский химзавод |
Химическая |
1424 |
1000 |
|
|
|
|
|
9 |
Новодвинский ЗЖБИ |
Строительство |
291 |
500 |
|
|
|
|
|
10 |
УМ № 3 |
Строительство |
1207 |
31,25 |
|
|
|
|
|
11 |
ММП "Комплекс" |
Транспорт |
1365 |
50 |
|
|
|
|
|
2.Отсортировать базу по количеству выставленных на продажу акций в порядке возрастания.
3.Отсортировать базу так, чтобы организации расположились в алфавитном порядке.
4. Определить стоимость выставленных на продажу акций, как произведение количества акций на номинал. Создать сводную таблицу с информацией об общей стоимости проданных акций для каждой организации с распределением по отраслям промышленности.
5. Распределите предприятия по группам (отфильтруйте)
в зависимости от количества выставленных на продажу акций. Первая группа – меньше 500.
Вторая группа - от 500 до 3000. Третья группа - больше 3000.
Скопируйте группы на другой лист.
6.Рассмотрите вопросы поиска данных:
-по наименованию
-по номиналу акции.
Каждое задание необходимо выполнить на отдельном листе.
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Вариант 6
1.Сформировать базу данных "КВАРТИРНЫЙ РЫНОК" со следующими полями:
-Адрес,
-Кол-во этажей в доме,
-Этаж,
-Кол-во комнат в квартире,
-Общая площадь,
-Жилая площадь,
-Площадь кухни,
-Тип дома (панельный, кирпичный, деревянный, 93 серии и др.)
-Агентство недвижимости,
-Стоимость квартиры,
-Дата продажи
2.Вычислить:
-Среднюю жилую площадь квартир с одинаковым кол-вом комнат,
-Выручку каждого агентства недвижимости на каждый день продажи,
-Общее количество квартир, выставленных на продажу каждым
агентством недвижимости (с разбивкой квартир по кол-ву комнат),
-Общее кол-во квартир с одинаковым количеством комнат,
-Минимальную стоимость среди квартир с одинаковым количеством комнат.
-Среднюю стоимость среди квартир одного типа дома с одинаковым количеством комнат.
Результат выполнения каждого задания скопировать на отдельный лист.
3.Выполнить выборку по трехкомнатным квартирам и скопировать ее на отдельный лист.
4.Создать на отдельном листе сводную таблицу по средней стоимости квартир различных типов с одинаковым количеством.
5.Создать на отдельном листе сводную таблицу о продаже квартир по датам.
База данных должна состоять не менее чем из 15 строк.
АГТУ, Чурбанова О.В. Работа с базами данных в MS Excel 2007, 2010
Вариант 7
1.Сформировать базу данных "МАГАЗИН" со следующими полями:
-Дата продажи
-Группа товара (Мясо, Рыба, Овощи, Кондитерские изделия и т.д.)
-Наименование
-Страна-изготовитель
-Единица измерения
-Цена
-Кол-во
-Выручка (Выручка=Цена*Кол-во)
2.Определить:
-выручку на каждый день продажи по каждой группе товара,
-выручку на каждый день продажи с разбивкой по странамизготовителям
-выручку по товарам, произведенным в одной стране,
-максимальную цену в каждой группе товара,
-среднюю выручку по каждой группе товара.
Результат выполнения каждого задания скопировать на отдельный лист.
3.Создать на отдельном листе сводную таблицу, показывающую выручку от продажи товаров по дням.
4.Создать на отдельном листе сводную таблицу, показывающий выручку по наименованиям товаров, произведенных в одной стране. Страна должна выбираться из списка.
База данных должна состоять не менее, чем из 15 строк.