Использование списка MICROSOFT EXCEL в качестве базы данных. Группировка цен по интервалам

Страницы работы

16 страниц (Word-файл)

Фрагмент текста работы

Министерство транспорта Российской Федерации

Федеральное агентство морского и речного транспорта

ФГОУ ВПО «Новосибирская Государственная Академия водного транспорта»

Енисейский филиал ФГОУ ВПО

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

По дисциплине «Информатика»

Тема: Использование списка  MICROSOFT EXCEL в качестве  базы данных

Вариант 6

Выполнил:  ст-т  гр.ОП

Проверил:

Красноярск

2008 г.

ВАРИАНТ 6

Создайте список, содержащий следующие столбцы:

Месяц

Клиент

Продукт

Кол-во, шт.

Цена, руб.

Сумма, руб.

Стоимость доставки, руб.

Столбцы "Месяц" и "Клиент" должны содержать не более трех значений. Например, для "Месяца" – январь, февраль, март. Столбец "Продукт" должен содержать 4 значения. Например, хлеб, булка, молоко, кефир. Каждое наименование продукта может иметь различную цену (например, молоко – за 12 руб. и за 24 руб.). Столбец "Сумма" вычисляется по формуле как произведение "Кол-во" на "Цену".

Список должен содержать 20 строк, не считая метки столбцов.

На этом же рабочем листе отсортируйте список простой сортировкой сначала по "Месяцу", затем по "Продукту", затем по "Цене", применив к "Месяцу" пользовательский порядок сортировки.

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

На следующем рабочем листе выполните расширенный фильтр, наложив условия на столбцы "Месяц" и "Кол-во, при этом результат скопируйте на другое место. На столбец "Кол-во" должно быть наложено два условия.

На следующем рабочем листе подведите итоги по "Месяц" – выведите сумму по столбцу "Стоимость доставки".

На следующем рабочем листе подведите итоги по "Продукт" – выведите среднюю цену.

По исходному списку постройте на рабочем листе рядом со списком сводную таблицу с макетом, приведенном на рисунке:

Поле "Продукт" переместите в строки, поле "Цена" добавьте в столбец.

Сгруппируйте Продукты в две группы и переименуйте их.

Сгруппируйте цены по интервалам.

Из поля "Клиент" скройте одного из клиентов.

Скройте детализирующие данные по одной из групп Продуктов.

В область данных добавьте поле "Стоимость доставки".

Для данных "Сумма по полю Сумма" и "Сумма по полю Стоимость доставки" представьте числа в денежном формате.

На основе исходного списка с использованием функций базы данных ДМАКС(), и БИЗВЛЕЧЬ() рассчитайте и сформируйте следующую таблицу:

январь

Сумма, руб.

Клиент

Продукт

Максимальная сумма


1. Сортируем список простой сортировкой сначала по "Месяцу" по убыванию, затем по "Продукту" и затем по "Цене" по возрастанию.

Месяц

Продавец

Продукт

Продано, шт

Цена

Сумма

январь

Петров

булка

28

7,00р.

196,00р.

январь

Иванов

булка

7

7,00р.

49,00р.

январь

Иванов

кефир

40

14,80р.

592,00р.

январь

Иванов

кефир

5

14,80р.

74,00р.

январь

Сидоров

молоко

54

21,00р.

1 134,00р.

январь

Иванов

молоко

45

21,00р.

945,00р.

январь

Иванов

хлеб

10

15,00р.

150,00р.

февраль

Петров

булка

28

7,00р.

196,00р.

февраль

Петров

булка

3

7,00р.

21,00р.

февраль

Иванов

кефир

76

14,80р.

1 124,80р.

февраль

Петров

кефир

67

14,80р.

991,60р.

февраль

Петров

молоко

9

21,00р.

189,00р.

февраль

Иванов

молоко

8

21,00р.

168,00р.

февраль

Петров

хлеб

5

15,00р.

75,00р.

март

Петров

булка

6

7,00р.

42,00р.

март

Сидоров

булка

4

7,00р.

28,00р.

март

Сидоров

кефир

44

14,80р.

651,20р.

март

Сидоров

молоко

30

21,00р.

630,00р.

март

Сидоров

молоко

4

21,00р.

84,00р.

март

Иванов

хлеб

34

15,00р.

510,00р.


2. Отфильтруем список автофильтром так, чтобы отображались данные только для одного продукта при ограничении на цену.

 

Месяц

Продавец

Продукт

Продано, шт

Цена

Сумма

январь

Иванов

хлеб

10

15,00р.

150,00р.

февраль

Петров

хлеб

5

15,00р.

75,00р.

март

Иванов

хлеб

34

15,00р.

510,00р.

3. Выполним расширенный фильтр, наложив условия на столбцы "Месяц" и "Кол-во. На столбец "Кол-во" должно быть наложено два условия.

Месяц

Продавец

Продукт

Продано, шт

Цена

Сумма

январь

Петров

булка

28

7,00р.

196,00р.

январь

Иванов

кефир

40

14,80р.

592,00р.

январь

Иванов

молоко

45

21,00р.

945,00р.


4. Проведем сортировку по столбцу «Месяц» (Данные/Сортировка),  выведем итоги по сумме (Данные/Итоги) «Сумма»

Месяц

Продавец

Продукт

Продано, шт

Цена

Сумма

март

Петров

булка

6

7,00р.

42,00р.

март

Сидоров

булка

4

7,00р.

28,00р.

март

Сидоров

кефир

44

14,80р.

651,20р.

март

Сидоров

молоко

30

21,00р.

630,00р.

март

Сидоров

молоко

4

21,00р.

84,00р.

март

Иванов

хлеб

34

15,00р.

510,00р.

март Итог

1 945,20р.

февраль

Петров

булка

28

7,00р.

196,00р.

февраль

Петров

булка

3

7,00р.

21,00р.

февраль

Иванов

кефир

76

14,80р.

1 124,80р.

февраль

Петров

кефир

67

14,80р.

991,60р.

февраль

Петров

молоко

9

21,00р.

189,00р.

февраль

Иванов

молоко

8

21,00р.

168,00р.

февраль

Петров

хлеб

5

15,00р.

75,00р.

февраль Итог

2 765,40р.

январь

Петров

булка

28

7,00р.

196,00р.

январь

Иванов

булка

7

7,00р.

49,00р.

январь

Иванов

кефир

40

14,80р.

592,00р.

январь

Иванов

кефир

5

14,80р.

74,00р.

январь

Сидоров

молоко

54

21,00р.

1 134,00р.

январь

Иванов

молоко

45

21,00р.

945,00р.

январь

Иванов

хлеб

10

15,00р.

150,00р.

январь Итог

3 140,00р.

Общий итог

7 850,60р.


5. Проведем сортировку по столбцу «Продукт»  (Данные/Сортировка),  выведем итоги по «средней цене»  (Данные/Итоги)

Месяц

Продавец

Продукт

Продано, шт

Цена

Сумма

март

Иванов

хлеб

34

15,00р.

510,00р.

февраль

Петров

хлеб

5

15,00р.

75,00р.

январь

Иванов

хлеб

10

15,00р.

150,00р.

хлеб Среднее

15,00р.

март

Сидоров

молоко

30

21,00р.

630,00р.

март

Сидоров

молоко

4

21,00р.

84,00р.

февраль

Петров

молоко

9

21,00р.

189,00р.

февраль

Иванов

молоко

8

21,00р.

168,00р.

январь

Сидоров

молоко

54

21,00р.

1 134,00р.

январь

Иванов

молоко

45

21,00р.

945,00р.

молоко Среднее

21,00р.

март

Сидоров

кефир

44

14,80р.

651,20р.

февраль

Иванов

кефир

76

14,80р.

1 124,80р.

февраль

Петров

кефир

67

14,80р.

991,60р.

январь

Иванов

кефир

40

14,80р.

592,00р.

январь

Иванов

кефир

5

14,80р.

74,00р.

кефир Среднее

14,80р.

март

Петров

булка

6

7,00р.

42,00р.

март

Сидоров

булка

4

7,00р.

28,00р.

февраль

Петров

булка

28

7,00р.

196,00р.

февраль

Петров

булка

3

7,00р.

21,00р.

январь

Петров

булка

28

7,00р.

196,00р.

январь

Иванов

булка

7

7,00р.

49,00р.

булка Среднее

7,00р.

Общее среднее

14,35р.


6. По исходному списку строим сводную таблицу в соответствии с макетом, приведенном на рисунке:

Месяц

(Все)

Сумма по полю Сумма

Продукт

Продавец

булка

кефир

молоко

хлеб

Общий итог

Иванов

49

1790,8

1113

660

3612,8

Петров

455

991,6

189

75

1710,6

Сидоров

28

651,2

1848

2527,2

Общий итог

532

3433,6

3150

735

7850,6

Месяц

(Все)


7. Изменяем «Макет» сводной таблицы в соответствии с рисунком:

Месяц

(Все)

Сумма по полю Сумма

Цена

Продавец

Продукт

7,00р.

14,80р.

15,00р.

21,00р.

Общий итог

Иванов

булка

49

49

кефир

1790,8

1790,8

молоко

1113

1113

хлеб

660

660

Иванов Итог

49

1790,8

660

1113

3612,8

Петров

булка

455

455

кефир

991,6

991,6

молоко

189

189

хлеб

75

75

Петров Итог

455

991,6

75

189

1710,6

Сидоров

булка

28

28

кефир

651,2

651,2

молоко

1848

1848

Сидоров Итог

28

651,2

1848

2527,2

Общий итог

532

3433,6

735

3150

7850,6


8. Группируем «Продукты» в две группы и переименовываем их.

Выделим позиции, объединяемые в одну группу. Нажмем правой кнопкой мыши, выберем  Группа и Структура/Группировать. Переименовываем группы набором с клавиатуры

Сумма по полю Сумма

Цена

Продукт2

Продукт

Продавец

7,00р.

14,80р.

15,00р.

21,00р.

Общий итог

хлебобулочные изделия

булка

Иванов

49

49

Петров

455

455

Сидоров

28

28

булка Итог

532

532

хлеб

Иванов

660

660

Петров

75

75

хлеб Итог

735

735

молочные продукты

кефир

Иванов

1790,8

1790,8

Петров

991,6

991,6

Сидоров

651,2

651,2

кефир Итог

3433,6

3433,6

молоко

Иванов

1113

1113

Петров

189

189

Сидоров

1848

1848

молоко Итог

3150

3150

Общий итог

532

3433,6

735

3150

7850,6


9. Группируем цены по интервалам.

В сводной таблице проведем группировку по полю Цена (Группа и Структура/Группировать), Укажем интервалы

Сумма по полю Сумма

Цена

Продукт2

Продукт

Продавец

7-8

14-15

15-16

20-21

Общий итог

хлебобулочные изделия

булка

Иванов

49

49

Петров

455

455

Сидоров

28

28

булка Итог

532

532

хлеб

Иванов

660

660

Петров

75

75

хлеб Итог

735

735

молочные продукты

кефир

Иванов

1790,8

1790,8

Петров

991,6

991,6

Сидоров

651,2

651,2

кефир Итог

3433,6

3433,6

молоко

Иванов

1113

1113

Петров

189

189

Сидоров

1848

1848

молоко Итог

3150

3150

Общий итог

532

3433,6

735

3150

7850,6


10. Из поля "Продавец" скроем одного человека

В  сводной таблице уберем галочку с одного из продавцов

Сумма по полю Сумма

Цена

Продукт2

Продукт

Продавец

7-8

14-15

15-16

20-21

Общий итог

хлебобулочные изделия

булка

Петров

455

455

Сидоров

28

28

булка Итог

483

483

хлеб

Петров

75

75

хлеб Итог

75

75

молочные продукты

кефир

Петров

991,6

991,6

Сидоров

651,2

651,2

кефир Итог

1642,8

1642,8

молоко

Петров

189

189

Сидоров

1848

1848

молоко Итог

2037

2037

Общий итог

483

1642,8

75

2037

4237,8

11. Скроем детализирующие данные по группе «Молочные продукты».

В имеющейся сводной таблице с разбивкой по  группам товаров в ячейке с названием группы «Молочные продукты» щелкнем правой кнопкой мыши Группа и структура/Скрыть детали

Сумма по полю Сумма

Цена

Продукт2

Продукт

Продавец

7-8

14-15

15-16

20-21

Общий итог

хлебобулочные изделия

булка

Петров

455

455

Сидоров

28

28

булка Итог

483

483

хлеб

Петров

75

75

хлеб Итог

75

75

молочные продукты

1642,8

2037

3679,8

Общий итог

483

1642,8

75

2037

4237,8


12. В область данных добавьте поле "Цена" и измените операцию "Сумма по полю Сумма".


Цена

Продукт2

Продукт

Продавец

Данные

7-8

14-15

15-16

20-21

Общий итог

хлебобулочные изделия

Булка

Петров

Сумма по полю Сумма

455

455

Количество по полю Цена

4

4

Сидоров

Сумма по полю Сумма

28

28

Количество по полю Цена

1

1

булка Сумма по полю Сумма

483

483

булка Количество по полю Цена

5

5

хлеб

Петров

Сумма по полю Сумма

75

75

Количество по полю Цена

1

1

хлеб Сумма по полю Сумма

75

75

хлеб Количество по полю Цена

1

1

молочные продукты

Сумма по полю Сумма

1642,8

2037

3679,8

Количество по полю Цена

2

4

6

Итог Сумма по полю Сумма

483

1642,8

75

2037

4237,8

Итог Количество по полю Цена

5

2

1

4

12


13. В макете сводной таблицы последовательно щелкнем по кнопкам «Сумма

Похожие материалы

Информация о работе