| ЛАБОРАТОРНАЯ РАБОТА Базы данных в MS EXCEL | |
| Автор: drug | Категория: Технические науки / Автоматизация | Просмотров: | Комментирии: 0 | 10-01-2013 10:32 |
ЛАБОРАТОРНАЯ РАБОТА №9. Базы данных в MS EXCEL
Цель работы: Изучение возможностей пакета MS Excel при работе с базами данных. Приобретение навыков создания и обработки БД.
Довольно часто возникает необходимость хранить и обрабатывать данные представленные в виде таблиц.
Информация, хранящаяся в таблицах, организована в виде строк и столбцов. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.
Т.о., можно сказать, что База данных (БД)- это совокупность различных записей, обладающих определенными свойствами.
В первой строке любой базы данных обязательно должны быть указаны имена полей. Максимальный размер базы данных в MS Excel определяется возможностями версии Excel (число строк и число столбцов в листе).
БД может быть сформирована на одном листе. Один лист может содержать несколько БД, но активной и доступной для выполнения различных операций в данный момент времени может быть только одна из них.
Рассмотрим процесс построения и обработки базы данных на следующем примере.
ПРИМЕР 9.1.
Наименование товара Дата Поставщик Город Закупочная цена (грн.) Отпускная цена Транспортные расходы Количество Прибыль
Колбаса 02.10.03 Колбико Макеевка 15.80 18.00 20 грн. 100
Творог 05.10.03 Ромол Харьков 2.20 2.50 45 грн.. 500
Сформируем БД на первом листе MS Excel, после чего лист переименуем (рис. 9.1).
Сформируем поле Прибыль. Для этого в ячейку I2 введем формулу:
I2=(F2-E2)*H2-G2
С помощью маркера заполнения скопируем эту формулу в ячейки I2:I18.
В результате лист примет вид изображенный на рис. 9.2.
Рис. 9.1. База данных "Поставщики товара"
Сортировка баз данных
Для сортировки базы выделим ячейки A1:I18 и выполним команду Данные Сортировка.
Добавление итогов в базу данных
Посчитать суммарную прибыль при продаже всех продуктов можно при помощи функции СУММ. Если же воспользоваться пунктом меню Итоги, то появляется возможность рассчитать сумму прибыли по каждому товару или по каждому поставщику. Итак, для суммирования прибыли по каждому из товаров сделаем следующее: отсортируем БД по наименованию товаров (рис. 9.3) и выполним команду Данные Итоги (рис. 9.4). Рабочий лист примет вид изображенный на рис. 9.5.
Рис. 9.3. Диалоговое окно Сортировка диапазона Рис. 9.4. Диалоговое окно Промежуточные итоги
Рис. 9.5. Итог по прибыли по всем видам товара
Фильтрация базы данных
Процесс поиска и отбора информации в базе данных MS Excel называется фильтрацией. В MS Excel есть два вида фильтра: Автофильтр и Расширенный фильтр.
Автофильтр
Для включения автофильтра необходимо:
1. Щелкнуть в любом месте базы данных, в нашем случае диапазон A1:I18.
2. Выполнить команду Данные Фильтр Автофильтр. Щелкнуть по кнопке списка справа от нужного поля (например, поле Наименование товара). Окно БД примет вид, изображенный на рис. 9.6. В качестве условия отбора можно выбрать либо любое значение из списка, либо пункт Условие.
Рис. 9.6. БД после применения команды Автофильтр
Выберем в качестве условия значение Колбаса. В результате в БД останется информация, касающаяся только поставок колбасы (рис. 9.7).
Рис. 9.7. Фрагмент отфильтрованной БД
Результат будет тем же, если из списка предложенных фильтров выбрать Условие…, и в открывшемся диалоговом окне (рис. 9.8), ввести в качестве условия "равно Колбаса".
Рис. 9.8. Диалоговое окно Пользовательский Автофильтр
Кроме того, диалоговое окно Пользовательский автофильтр позволяет устанавливать различные условия фильтрации, выбрав их из предлагаемого списка (рис. 9.9).
Рис. 9.9. Фрагмент диалогового окна Пользовательский Автофильтр
Расширенный фильтр
Для выделения из БД более сложных условий можно воспользоваться командой Данные Фильтр Расширенный фильтр. Рассмотрим работу с расширенным фильтром на примере выделения из БД записей донецких производителей колбасы. Для этого определим область для хранения условий отбора. Каждое условие записывается в две ячейки: в верхнюю - имя поля, в нижнюю - знак отношения (>,=,<=,) и значение. В нашем случае в ячейку K1 введем Наименование товара, в ячейку K2 - Колбаса, в ячейку L1 - Город, в ячейку L2 - Донецк (рис. 9.10).
Рис. 9.10. Область для хранения условий отбора
Теперь выполним команду Данные Фильтр Расширенный фильтр (рис.9.11).
Рис. 9.11. Диалоговое окно расширенный фильтр
В данном случае два условия соединены логическим действием "И". Для объединения с помощью "ИЛИ" необходимо между именем поля и условием пропустить строчку (рис. 9.12).
Рис. 9.12. Соединение условий при помощи логического "ИЛИ"
При копировании отфильтрованных данных в другое место необходимо, чтобы копируемый диапазон начинался со строки, в которой указываются имена полей БД.
Сводная таблица
Сводные таблицы - одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.
Создадим из нашей БД сводную таблицу для расчета прибыли по каждому товару. Выполним команду Данные Сводная таблица. В первом диалоговом окне Мастер сводных таблиц (рис. 9.13) необходимо установить переключатель в положение, показывающее, откуда берутся данные для сводной таблицы:
• в списке или базе данных MS Excel - если данные берутся с одного рабочего листа;
• во внешнем источнике данных - если данные берутся из внешней базы данных;
• в нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;
• в другой сводной таблице - если сводная таблица создается на основании данных другой сводной таблицы.
В этом же диалоговом окне указывается вид создаваемого отчета - сводная таблица или сводная диаграмма.
Рис. 9.13. Первое диалоговое окно Мастер сводных таблиц
В рассматриваемом примере переключатель устанавливается в положение В списке или базе данных MS Excel.
В поле Диапазон второго диалогового окна Мастер сводных таблиц (рис. 9.14) необходимо указать диапазон, на основании которого строится сводная таблица. В нашем случае: 'Поставщики товара'!$A$:$I$18.
В третьем диалоговом окне Мастер сводных таблиц (рис. 9.15) необходимо установить переключатель в положение, указывающее, где будет размещена сводная таблица - на новом листе или на уже существующем.
Рис. 9.14. Второе диалоговое окно Мастер сводных таблиц
Рис. 9.15. Третье диалоговое окно Мастер сводных таблиц
Структуру сводной таблицы можно создать, воспользовавшись кнопкой Макет…, третьего диалогового окна Мастер сводных таблиц.
Поля БД, на основании которой строится сводная таблица, представлены в окне создания макета в виде кнопок с названием этих полей (рис. 9.16). Перетаскивая их в соответствующие области, пользователь задает необходимую структуру сводной таблицы.
В окне имеются четыре области:
• Строка - для использования данных поля, расположенного в этой области, в качестве заголовка строки;
• Столбец - для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;
• Данные - для суммирования значений поля, расположенного в этой области, в ячейках сводной таблицы;
• Страница - для обеспечения возможности вывода данных сводной таблицы, относящихся только к полю, расположенному в этой области.
Рис. 9.16. Диалоговое окно для создания макета сводной таблицы
В рассматриваемом примере создается сводная таблица, состоящая из строк с наименованием товара и столбцов с названием города из которого этот товар доставлен. В область Данные перемещена кнопка Прибыль. Двойной щелчок по кнопке в области данных, открывает диалоговое окно Вычисление поля сводной таблицы (рис. 9.17), позволяющее выбрать правило по которому подводятся итоги в сводной таблице.
Допустимыми операциями подведения итогов являются: сумма, количество значений, среднее арифметическое, максимальное и минимальное значение, произведение, количество чисел, несмешанное и смешанное отклонение, несмешанная и смешанная дисперсия. В нашем примере в качестве допустимой операции выбрана сумма.
Рис. 9.17. Диалоговое окно Вычисление поля сводной таблицы
Кнопка Параметры… третьего диалогового окна Мастер сводных таблиц позволяет задать некоторые параметры, определяющие вид сводной таблицы (рис. 9.18). Например, в поле Имя можно задать название таблицы. По умолчанию сводные таблицы называются Сводная таблица 1, Сводная таблица 2 и т.д. Устанавливая флажок Общие итоги по столбцам или флажок Общие итоги по строкам можно подвести итоги по столбцам или строкам в сводной таблице. Установка флажка Автоформат позволяет пользоваться средствами автоформата MS Excel. Установив флажок Сохранить данные вместе с таблицей можно создать дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении.
Рис. 9.18. Диалоговое окно Параметры сводной таблицы
После щелчка по кнопке Готово в третьем диалоговом окне Мастер сводных таблиц на рабочем листе будет создана сводная таблица, представленная на рис. 9.19.
Рис. 9. 19. Сводная таблица подсчета прибыли по каждому товару
1.
ВАРИАНТЫ ЗАДАНИЙ
1. Создайте табличный документ и сохраните его в личной папке.
2. Заполните таблицу данными и формулами в соответствии с условием задания (не менее 10 строк).
3. Используя функцию Итоги…, заполнить поля отмеченные звездочкой.
4. Установите фильтры в соответствии с вариантом задания.
5. Получите сводные таблицы для исходной и отфильтрованной таблиц по нескольким показателям с помощью мастера сводных таблиц. Строки и столбцы для сводных таблиц выберите самостоятельно.
6. Переставьте местами строки и столбцы сводных таблиц.
7. Создайте диаграммы по сводным таблицам
Вариант № 1. Ведомость по продаже сигарет
Марка Месяц Фактическое количество Цена Сумма План
Dunhill Февраль 100 20,5 р.
... ... ... ... ... ...
ИТОГО * *
1. Сформировать столбец суммы.
2. Столбец План заполнить в зависимости от марки сигарет следующими значениями:
• для Pall-Mal, Bond, L&M - 250;
• для Dunhill - 100;
• для Marlboro - 150.
3. Используя функцию Итоги…, рассчитать на какую сумму, и в каком количестве было продано сигарет каждой марки.
4. Используя расширенный фильтр, отфильтровать информацию о сигаретах, поставленных в феврале по цене ниже 20.85 р. за пачку.
5. Установить фильтр для отображения всех поставок сигарет Bond.
Вариант № 2. Ведомость о реализации товаров
Дата реализации Наименование товаров Поставщика Цена Количество Сумма Скидка Итого
1/02/11 Носки База №2
2/02/11 Платье База №5
3/02/11 Костюм База №7
1/02/11 Костюм База №9
4/02/11 Носки База №2
5/02/11 Костюм База №9
10/02/11 Костюм База №7
11/02/11 Носки База №2
2/02/11 Платье База №5
6/02/11 Носки База №2
5/02/1 Костюм База №7
4/02/11 Платье База №5
3/02/11 Костюм База №9
ИТОГО * * * *
1. Сформировать поле Сумма.
2. Поле Скидка заполнить следующим образом:
• для суммы менее 100 р. - 0%;
• для суммы от 100 р. до 1000 р. - 2%;
• для суммы свыше 1000 р. - 5%.
3. В поле Итого подсчитывается общая сумма скидки в рублях.
4. Используя функцию Итоги…, рассчитать на какую сумму, и в каком количестве было продано товаров каждым поставщиком.
5. Используя расширенный фильтр, отфильтровать данные для отображения всех товаров, полученных с Баз №2 и № 9.
6. Установить фильтр для отображения всех продаж со скидками более 200 р.
Вариант № 3. Доставка товара
Название мороженого Месяц Поставщик Закупочная цена Отпускная цена Количество Стоимость доставки Общая прибыль
Пломбир Июль Геркулес 10.69 10.85 3000
Пломбир Июль Винтер
Морозко Июнь Геркулес
Эскимо Август Мушкетер
Каштан Сентябрь Винтер
Пломбир Сентябрь
... ... ... ... ... ... ... ...
Всего * * * * *
1. Поле Стоимость доставки заполняется следующим образом:
• Пломбир - 900 р.
• Морозко - 1500 р.
• Крем-брюле - 500 р.
2. Сформировать поле Общая прибыль.
3. Используя функцию Итоги…, рассчитать прибыль, стоимость доставки и количество мороженого проданного каждым производителем.
4. Используя автофильтр, отфильтровать все закупки пломбира летом.
5. Используя расширенный фильтр, отобразить все закупки объемом более 2000 р.
Вариант № 4. Поставки товара
Дата поставки Наименования товара Поставщик товара Цена Количество Сумма Дилерская скидка
1/04/10 Портфель ЧП "Днепр"
1/04/10 Ремень мужской ООО "Кожгалантерея"
1/04/10 Косметический набор Lancom ЧП "Иванофф"
1/04/10 Сумка дорожная ЧП "Днепр"
5/04/10 Косметический набор Lancom ЧП "Иванофф"
5/04/10 Сумка дорожная ЧП "Днепр"
5/04/10 Ремень мужской ООО "Кожгалантерея"
10/04/10 Косметический набор Lancom ЧП "Иванофф"
10/04/10 Сумка дорожная ООО "Кожгалантерея"
11/04/10 Сумка дорожная ЧП "Днепр"
12/04/10 Косметический набор Lancom ЧП "Иванофф"
15/04/10 Ремень мужской ООО "Кожгалантерея"
Всего * *
1. Сформировать поле Сумма.
2. Поле "Дилерская скидка" заполняется следующим образом:
• ЧП "Иванофф" - 5%.
• ООО "Кожгалантерея" - 10%.
• ЧП "Днепр" - 6%.
3. Используя функцию Итоги…, рассчитать на какую сумму и в каком количестве было продано товаров каждого вида.
4. Используя расширенный фильтр, отфильтровать данные для отображения всех поставок от ЧП "Днепр" после 5/04/10.
5. Используя автофильтр, отобразить все поставки косметики объемом более 30 единиц после 5/02/10.
Вариант № 5. ООО "Мир ПК". Ведомость выполнения плана товарооборота по подразделениям предприятия
Наименование подразделения Наименование товарной группы Поставщик Сумма заказа Сумма фактической реализации Прибыль Форма оплаты
Отдел сбыта Компьютеры ООО "Электронная техника"
Магазин №1 Оргтехника ООО "Империя компьютеров"
Магазин №4 Фирма "Мультимедиа"
...
Сумма * * *
1. Заполнить поле Прибыль.
2. Поле Форма оплаты заполняется следующим образом:
• ООО "Электронная техника" - наличные;
• ООО "Империя компьютеров" - безналичные;
• Фирма "Мультимедиа" - кредит.
3. Используя функцию Итоги…, рассчитать прибыль и сумму заказов каждого подразделения фирмы.
4. Используя автофильтр, отфильтровать данные для отображения всех поставок из фирмы "Мультимедиа" с прибылью более 20000 р.
5. Используя расширенный фильтр, сформировать новую базу данных для отображения всей оргтехники, поставленной из ООО "Электронная техника"
Вариант № 6. Ведомость закупки чая
Тип чая Форма упаковки Производитель Месяц Цена, р Количество Сумма
Зеленый Пачка Riston Январь 80,8 50
Черный Пакетики Ahmad
Красный Dilmah
Желтый Edwin
...
Всего * *
1. Сформировать поле Сумма.
2. В графу Форма упаковки заносят следующие значения: пачка, пакетики, банка.
3. Используя функцию Итоги…, подсчитать сумму закупки каждого типа чая, сумму закупки от каждого производителя и количество упаковок в пачках, пакетиках и банках.
4. Используя автофильтр, необходимо отобразить данные о продаже зеленого чая в мае.
5. Используя расширенный фильтр сформировать новую базу данных, куда занести информацию о продаже либо красного чая, либо чая в пакетиках.
Вариант № 7. ООО "ПРОДОВОЛЬСТВИЕ"
Группа товаров Наименование товаров Количество Закупочная цена Отпускная цена Сумма Прибыль
Хлебобулочные Хлеб
Молочные Молоко
Мясные Паштет
Молочные Творог
Мясные Паштет
Мясные Мясо
Хлебобулочные Хлеб
Мясные Мясо
Мясные Паштет
Хлебобулочные Батон
Молочные Ряженка
Всего * * *
1. Сформировать поле Сумма.
2. Поле Прибыль заносится значение ДА, если отпускная цена превышает закупочную цену на 10%, иначе НЕТ.
3. Используя функцию Итоги…, подсчитать на какую сумму было закуплено товара по каждой группе и количество продуктов, принесших прибыль.
4. Используя автофильтр, вывести информацию о мясных продуктах ценой менее 70 р.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о поставках хлеба или молочных продуктов.
Вариант № 8. Ведомость реализации товаров
Наименование товара Дата реализации Поставщик Цена Количество Сумма
Сыр 1/04/10
Масло 1/04/10
Сметана 1/04/10
Молоко 1/04/10
Сметана 4/04/10
Молоко 5/04/10
Сыр 5/04/10
Масло 6/04/10
...
Всего * *
1. В графу Поставщик заносятся в зависимости от наименования товара следующие значения:
• Сыр, масло - Молокозавод №1;
• Сметана, молоко - Молокозавод №2;
• Йогурт - ЧП "Свежесть".
2. Сформировать поле Сумма.
3. Используя функцию Итоги…, рассчитать на какую сумму и в каком количестве было продано товаров каждого вида и каждым поставщиком.
4. Используя автофильтр, необходимо отфильтровать данные для отображения всех продаж молока на сумму более 1000 р.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию всех продаж йогурта и сыра на сумму менее 700 р.
Вариант № 9. Мониторы
Производитель Тип Модель Цена (р.) Цена (у.е.) Количество Стоимость(грн)
Samsung 17" Samsung 757NF 11000
Samsung 17" Samtron 76DF 3500
LG 17" LG FK 710PH 8700
Philips 17" Philips 107P4Q 11150
SONY 17"
Samtron 19"
SONY 19"
Samsung 15"
...
ВСЕГО * * * *
1. Сформировать поле Цена (у.е.), если 1$=29,35 р.
2. Поле Количество заполнить следующим образом:
• если цена меньше или равна 3500, то 10
• если цена от 3500 до 10000 то 7
• иначе 5
3. Сформировать поле Стоимость.
4. Используя функцию Итоги…, вычислить средние цены мониторов каждого производителя в у.е., и количество мониторов каждого типа.
5. Используя автофильтр, необходимо отфильтровать данные для отображения всех мониторов, произведенных фирмой Samsung.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех 17" мониторах.
Вариант № 10. Информация о ряде стран мира
Полушарие Земли Часть света Страна Площадь, тыс. кв. км. Население, тыс. чел. Плотность населения, чел./кв. км.
Восточное Африка Гвинея 246 5290
Восточное Европа Дания 44.5 5111
Западное Юж. Америка Уругвай 176 2947
Восточное Африка Сенегал 196 6600
Западное Юж. Америка Бразилия 8512 135560
Западное Юж. Америка Перу 12285 19700
Западное Юж. Америка Чили 757 12470
Восточное Европа Швеция 450 8359
Восточное Азия Вьетнам 331.7 60863
Восточное Африка Либерия 111 22200
Восточное Азия Монголия 1566.5 1866
Восточное Азия Япония 372 120030
...
1. Сформировать поле Полушарие, в зависимости от части света.
2. Сформировать поле Плотность населения.
3. Определить общую площадь и общее количество населения каждого полушария и каждой части света.
4. Используя функцию Итоги…, определить средние значения площади стран для каждого полушария и части света.
5. Используя автофильтр, необходимо отфильтровать данные для стран, плотность населения которых, меньше среднего значения.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о странах западного полушария, у которых площадь больше среднего значения.
Вариант № 11. Сотрудники
Ф. И. О Подразделение Должность Дата поступления на работу Ставка Оклад Надбавка Начислено
Иванов П. Ф. каф. физики Доцент 13.08.1991 0,5
Петров А.Р. каф. ВМ Доцент 1
Сидоров Р.Л. каф. ТМ Доцент 0,25
Козлов А.Д. каф. физики Ассистент 1
...
ИТОГО *
1. Сформировать поле Оклад, учитывая, что ставка доцента - 8460 р, старшего преподавателя - 6470, ассистента - 4840.
2. Поле Надбавка заполняется в зависимости от стажа работы на предприятии:
• При стаже более 3 лет - 10% от оклада;
• При стаже более 10 лет - 20% от оклада;
• При стаже более 20 лет - 30% от оклада.
3. Сформировать поле Начислено.
4. Используя функцию Итоги…, определить средний оклад по каждому подразделению.
5. Используя автофильтр, необходимо отфильтровать данные для отображения информации о всех доцентах.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех ассистентах, работающих на кафедре математики.
Вариант № 12. БД "БАНК"
Фамилия клиента Тип вклада Размер вклада (р) Вклад (у.е.) Отделение банка Примечание
Сидоров Текущий 231000 Северное
Иванов Депозит 345000 Центральное
Петров Депозит 345720 Западное
Сидоров Депозит 45678930 Западное
Козлов Текущий 1254883 Центральное
Васин Текущий 54589663 Северное
Сидоров Депозит 25486 Западное
Козлов Депозит 6897674 Северное
Васин Депозит 65783700 Центральное
Петров Текущий 537000 Центральное
...
ИТОГО * *
1. Заполнить поле Вклад (у.е.), если 1$=29,44 р.
2. Заполнить поле Примечание :
• Сидоров - Временно выбыл
• Козлов - Сменил адрес
• Иванов - Перевел в другое отделение
3. Используя функцию Итоги…, определить сумму вкладов каждого клиента и сколько в среднем денег хранится в каждом отделении банка.
4. Используя автофильтр, отобразить всех клиентов хранящих деньги на депозите.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех клиентах, хранящих деньги в Северном отделении банка.
Вариант № 13. Принтеры
Производитель Тип Модель Цена (р) Цена (у.е.) Количество Стоимость(грн)
LEXMARK струйный Z25 2180
EPSON струйный C62 4240
CANON струйный S-200 3160
HP струйный 3420C 2990
EPSON матричный LX-300 8600
CANON лазерный LBP-810 9520
HP лазерный 1000W 9570
HP лазерный 1200 17730
Samsung лазерный ML-1210 9030
LEXMARK струйный Z45 3640
...
ВСЕГО * *
1. Сформировать поле Цена (у.е.)
• если цена в р. меньше 5000, то 1$=29,56 р.
• если цена в р. от 5000 до 10000, то 1$=29,44 р.
• иначе 1$=29,37 р.
2. Сформировать поле Стоимость.
3. Используя функцию Итоги…, вычислить средние цены принтеров каждого производителя в р., и количество принтеров каждого типа.
4. Используя автофильтр, отфильтровать данные для отображения всех принтеров, произведенных фирмой HP.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех лазерных принтерах.
Вариант № 14. ООО "АВТО"
Фирма Марка Цена (у.е.) Цена (р.) Мощность двигателя, л.с. Скидка Тип кузова
Mitsubishi Pajero Sport 2,5 TD GLX $29 990 100 внедорожник
Mitsubishi Pajero 3,5 GDI GLS AT $49 590 202 внедорожник
Skoda Fabia Combi 1,4 Classic $10 500 68 комби
Mitsubishi Galant 2,5-V6 Elegance $26 900 161 седан
Mitsubishi Galant 2,0 Comfort $22 900 133 седан
Mitsubishi Pajero Sport 3,0 V6 GLX $36 590 177 внедорожник
Peugeot 307 $12 930 75 хэтчбек
Skoda Fabia Sedan 1,4 Classic $10 200 68 седан
Skoda Octavia 1,8 Elegance $18 800 150 седан
Skoda Octavia 1,6 Classic $12 100 75 седан
Peugeot 206 $8 775 60 седан
Skoda Octavia 1,6 Ambiente $12 450 101 седан
Skoda Fabia 1,4 Basic $8 600 60 хэтчбек
Skoda Fabia 1,4 Comfort $9 990 68 седан
...
ИТОГО * * *
1. Сформировать поле Цена (у.е.), 1$=29,37 р
2. Сформировать поле Скидка, следующим образом:
• мощность двигателя меньше 100 л.с. - 2%
• мощность двигателя от 100 до 150 л.с. - 1,5%
• иначе - 1%
3. Используя функцию Итоги…, вычислить среднюю цену на автомобили каждой марки в у.е., и количество автомобилей каждого типа.
4. Используя автофильтр, отфильтровать данные для отображения всех автомобилей марки Skoda.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех внедорожниках марки Mitsubishi .
Вариант № 15. Сведения о ряде геометрических тел
Номер тела Вид тела Вид материала Плотность материала, г/см3 Объем тела, см3 Масса тела, г
1 Куб 7,8 123
2 Шар 2,6 50
3 Куб 1,5 41
4 Куб 1,2 200
5 Шар 2,6 8
6 Шар 1,5 134
7 Шар 7,8 30
8 Куб 1,2 51
9 Куб 7,1 100
10 Куб 8,9 43
11 Шар 1,3 258
...
Итого * *
1. Сформировать поле Масса тела.
2. Поле Вид материала заполнить следующим образом:
• плотность более 1,5 - металл,
• иначе пластмасса.
3. Используя функцию Итоги…, вычислить общую массу и общий объем всех шаров и всех кубов, среднее значение массы и объема для всех тел из металла и для всех тел из пластмассы.
4. Используя автофильтр, отфильтровать данные для отображения информации о всех шарах.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех однотипных изделиях (металлические шары, пластмассовые кубы и т.д.).
Вариант № 16. Сведения о ряде геометрических фигур
Номер фигуры Вид фигуры Тип фигуры Сторона 1/ Катет 1 Сторона 2/ Катет 2 Площадь фигуры
1 Треугольник 12 12
2 Треугольник 3 3
3 Прямоугольник 10 10
4 Прямоугольник 3 5
5 Треугольник 10 5
6 Треугольник 3 7
7 Прямоугольник 5 6
8 Треугольник 4 4
9 Треугольник 5,5 2,4
...
Итого
1. Сформировать поле Тип фигуры:
• Сторона 1 = Сторона 2 - квадрат
• Сторона 1 ≠ Сторона 2 - неквадрат
• Катет 1 = Катет 2 - равносторонний
• Катет 1 ≠ Катет 2 - прямоугольный
2. Сформировать поле Площадь фигуры, в зависимости от ее типа.
3. Используя функцию Итоги…, вычислить среднюю площадь всех треугольников и всех прямоугольников.
4. Используя автофильтр, отфильтровать данные для отображения информации обо всех прямоугольниках.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию обо всех квадратах и прямоугольных треугольниках.
Вариант № 17. Акционеры фирмы "КУПИ-ПРОДАЙ"
№ п/п Фамилия Выпуск акций Вид акции Кол-во Номинальная стоимость акции Общая стоимость акций
1 Сидиромов 1 2 5000
2 Мониторов 2 10 500
3 Сидиромов 1 12 500
4 Мышкин 2 30 5000
5 Сидиромов 2 67 500
6 Мониторов 1 43 500
7 Мышкин 1 57 5000
8 Мониторов 2 2 5000
9 Сидиромов 1 5 500
10 Мониторов 2 54 500
11 Дискеткин 2 32 5000
...
Итого * *
1. Сформировать поле Общая стоимость.
2. Сформировать поле Вид акции, следующим образом:
• номинальная стоимость 500 р. - обыкновенная
• номинальная стоимость 5000 р. - привилегированная
3. Используя функцию Итоги…, вычислить на какую сумму приобретены акции каждым из акционеров и количество акций в каждом выпуске.
4. Используя автофильтр, отфильтровать данные для отображения информации о акциях первого выпуска.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о акциях каждого вида.
Вариант № 18. Сведения о прохождении автомобилями участков пути
№ п/п Фирма Вид автомобиля Средняя скорость на участке, км/ч Время прохождения участка, ч Длина участка, км
1 Nissan Легковой 0,7 131
2 Fiat Грузовой 2,3 163
3 Nissan 4,3 665
4 Nissan 0,9 90
5 Nissan 1,6 240
6 Fiat 2,8 254
7 Fiat 3,6 234
8 Fiat 4,1 654
9 Fiat 4,6 346
10 Nissan 3,5 786
11 Nissan 4,5 144
12 Fiat 1,9 346
...
Итого * *
1. Сформировать поле Вид автомобиля:
• для нечетных номеров - легковой
• для четных - грузовой.
2. Сформировать поле Средняя скорость на участке, км/ч.
3. Используя функцию Итоги…, вычислить среднюю скорость по каждому виду автомобилей и по каждой фирме.
4. Используя автофильтр, отфильтровать данные для отображения информации о легковых автомобилях.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о грузовых автомобилях фирмы Fiat.
Вариант № 19. Сотрудники
Ф. И. О Подразделение Должность Дата поступления на работу Ставка Оклад Надбавка Начислено
Иванов П. Ф. каф. физики Доцент 13.08.1991 0,5
Петров А.Р. каф. ВМ Доцент 1
Сидоров Р.Л. каф. ТМ Доцент 0,25
Козлов А.Д. каф. физики Ассистент 1
...
ИТОГО *
1. Сформировать поле Оклад, учитывая, что ставка доцента - 8460 р, старшего преподавателя - 6470, ассистента - 4840.
2. Поле Надбавка заполняется в зависимости от стажа работы на предприятии:
• При стаже более 3 лет - 10% от оклада;
• При стаже более 10 лет - 20% от оклада;
• При стаже более 20 лет - 30% от оклада.
3. Сформировать поле Начислено.
4. Используя функцию Итоги…, определить средний оклад по каждому подразделению.
5. Используя автофильтр, необходимо отфильтровать данные для отображения информации о всех доцентах.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех ассистентах, работающих на кафедре математики.
Вариант № 20. БД "БАНК"
Фамилия клиента Тип вклада Размер вклада (р) Вклад (у.е.) Отделение банка Примечание
Сидоров Текущий 231000 Северное
Иванов Депозит 345000 Центральное
Петров Депозит 345720 Западное
Сидоров Депозит 45678930 Западное
Козлов Текущий 1254883 Центральное
Васин Текущий 54589663 Северное
Сидоров Депозит 25486 Западное
Козлов Депозит 6897674 Северное
Васин Депозит 65783700 Центральное
Петров Текущий 537000 Центральное
...
ИТОГО * *
1. Заполнить поле Вклад (у.е.), если 1$=29,44 р.
2. Заполнить поле Примечание :
• Сидоров - Временно выбыл
• Козлов - Сменил адрес
• Иванов - Перевел в другое отделение
3. Используя функцию Итоги…, определить сумму вкладов каждого клиента и сколько в среднем денег хранится в каждом отделении банка.
4. Используя автофильтр, отобразить всех клиентов хранящих деньги на депозите.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех клиентах, хранящих деньги в Северном отделении банка.
Вариант № 21. ООО "ПРОДОВОЛЬСТВИЕ"
Группа товаров Наименование товаров Количество Закупочная цена Отпускная цена Сумма Прибыль
Хлебобулочные Хлеб
Молочные Молоко
Мясные Паштет
Молочные Творог
Мясные Паштет
Мясные Мясо
Хлебобулочные Хлеб
Мясные Мясо
Мясные Паштет
Хлебобулочные Батон
Молочные Ряженка
Всего * * *
1. Сформировать поле Сумма.
2. Поле Прибыль заносится значение ДА, если отпускная цена превышает закупочную цену на 10%, иначе НЕТ.
3. Используя функцию Итоги…, подсчитать на какую сумму было закуплено товара по каждой группе и количество продуктов, принесших прибыль.
4. Используя автофильтр, вывести информацию о мясных продуктах ценой менее 70 р.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о поставках хлеба или молочных продуктов.
Вариант № 22. Ведомость реализации товаров
Наименование товара Дата реализации Поставщик Цена Количество Сумма
Сыр 1/04/10
Масло 1/04/10
Сметана 1/04/10
Молоко 1/04/10
Сметана 4/04/10
Молоко 5/04/10
Сыр 5/04/10
Масло 6/04/10
...
Всего * *
1. В графу Поставщик заносятся в зависимости от наименования товара следующие значения:
• Сыр, масло - Молокозавод №1;
• Сметана, молоко - Молокозавод №2;
• Йогурт - ЧП "Свежесть".
2. Сформировать поле Сумма.
3. Используя функцию Итоги…, рассчитать на какую сумму и в каком количестве было продано товаров каждого вида и каждым поставщиком.
4. Используя автофильтр, необходимо отфильтровать данные для отображения всех продаж молока на сумму более 1000 р.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию всех продаж йогурта и сыра на сумму менее 700 р.
Вариант № 23. Мониторы
Производитель Тип Модель Цена (р.) Цена (у.е.) Количество Стоимость(грн)
Samsung 17" Samsung 757NF 11000
Samsung 17" Samtron 76DF 3500
LG 17" LG FK 710PH 8700
Philips 17" Philips 107P4Q 11150
SONY 17"
Samtron 19"
SONY 19"
Samsung 15"
...
ВСЕГО * * * *
1. Сформировать поле Цена (у.е.), если 1$=29,35 р.
2. Поле Количество заполнить следующим образом:
• если цена меньше или равна 3500, то 10
• если цена от 3500 до 10000 то 7
• иначе 5
3. Сформировать поле Стоимость.
4. Используя функцию Итоги…, вычислить средние цены мониторов каждого производителя в у.е., и количество мониторов каждого типа.
5. Используя автофильтр, необходимо отфильтровать данные для отображения всех мониторов, произведенных фирмой Samsung.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех 17" мониторах.
Вариант № 24. Информация о ряде стран мира
Полушарие Земли Часть света Страна Площадь, тыс. кв. км. Население, тыс. чел. Плотность населения, чел./кв. км.
Восточное Африка Гвинея 246 5290
Восточное Европа Дания 44.5 5111
Западное Юж. Америка Уругвай 176 2947
Восточное Африка Сенегал 196 6600
Западное Юж. Америка Бразилия 8512 135560
Западное Юж. Америка Перу 12285 19700
Западное Юж. Америка Чили 757 12470
Восточное Европа Швеция 450 8359
Восточное Азия Вьетнам 331.7 60863
Восточное Африка Либерия 111 22200
Восточное Азия Монголия 1566.5 1866
Восточное Азия Япония 372 120030
...
1. Сформировать поле Полушарие, в зависимости от части света.
2. Сформировать поле Плотность населения.
3. Определить общую площадь и общее количество населения каждого полушария и каждой части света.
4. Используя функцию Итоги…, определить средние значения площади стран для каждого полушария и части света.
5. Используя автофильтр, необходимо отфильтровать данные для стран, плотность населения которых, меньше среднего значения.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о странах западного полушария, у которых площадь больше среднего значения.
Цель работы: Изучение возможностей пакета MS Excel при работе с базами данных. Приобретение навыков создания и обработки БД.
Довольно часто возникает необходимость хранить и обрабатывать данные представленные в виде таблиц.
Информация, хранящаяся в таблицах, организована в виде строк и столбцов. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.
Т.о., можно сказать, что База данных (БД)- это совокупность различных записей, обладающих определенными свойствами.
В первой строке любой базы данных обязательно должны быть указаны имена полей. Максимальный размер базы данных в MS Excel определяется возможностями версии Excel (число строк и число столбцов в листе).
БД может быть сформирована на одном листе. Один лист может содержать несколько БД, но активной и доступной для выполнения различных операций в данный момент времени может быть только одна из них.
Рассмотрим процесс построения и обработки базы данных на следующем примере.
ПРИМЕР 9.1.
Наименование товара Дата Поставщик Город Закупочная цена (грн.) Отпускная цена Транспортные расходы Количество Прибыль
Колбаса 02.10.03 Колбико Макеевка 15.80 18.00 20 грн. 100
Творог 05.10.03 Ромол Харьков 2.20 2.50 45 грн.. 500
Сформируем БД на первом листе MS Excel, после чего лист переименуем (рис. 9.1).
Сформируем поле Прибыль. Для этого в ячейку I2 введем формулу:
I2=(F2-E2)*H2-G2
С помощью маркера заполнения скопируем эту формулу в ячейки I2:I18.
В результате лист примет вид изображенный на рис. 9.2.
Рис. 9.1. База данных "Поставщики товара"
Сортировка баз данных
Для сортировки базы выделим ячейки A1:I18 и выполним команду Данные Сортировка.
Добавление итогов в базу данных
Посчитать суммарную прибыль при продаже всех продуктов можно при помощи функции СУММ. Если же воспользоваться пунктом меню Итоги, то появляется возможность рассчитать сумму прибыли по каждому товару или по каждому поставщику. Итак, для суммирования прибыли по каждому из товаров сделаем следующее: отсортируем БД по наименованию товаров (рис. 9.3) и выполним команду Данные Итоги (рис. 9.4). Рабочий лист примет вид изображенный на рис. 9.5.
Рис. 9.3. Диалоговое окно Сортировка диапазона Рис. 9.4. Диалоговое окно Промежуточные итоги
Рис. 9.5. Итог по прибыли по всем видам товара
Фильтрация базы данных
Процесс поиска и отбора информации в базе данных MS Excel называется фильтрацией. В MS Excel есть два вида фильтра: Автофильтр и Расширенный фильтр.
Автофильтр
Для включения автофильтра необходимо:
1. Щелкнуть в любом месте базы данных, в нашем случае диапазон A1:I18.
2. Выполнить команду Данные Фильтр Автофильтр. Щелкнуть по кнопке списка справа от нужного поля (например, поле Наименование товара). Окно БД примет вид, изображенный на рис. 9.6. В качестве условия отбора можно выбрать либо любое значение из списка, либо пункт Условие.
Рис. 9.6. БД после применения команды Автофильтр
Выберем в качестве условия значение Колбаса. В результате в БД останется информация, касающаяся только поставок колбасы (рис. 9.7).
Рис. 9.7. Фрагмент отфильтрованной БД
Результат будет тем же, если из списка предложенных фильтров выбрать Условие…, и в открывшемся диалоговом окне (рис. 9.8), ввести в качестве условия "равно Колбаса".
Рис. 9.8. Диалоговое окно Пользовательский Автофильтр
Кроме того, диалоговое окно Пользовательский автофильтр позволяет устанавливать различные условия фильтрации, выбрав их из предлагаемого списка (рис. 9.9).
Рис. 9.9. Фрагмент диалогового окна Пользовательский Автофильтр
Расширенный фильтр
Для выделения из БД более сложных условий можно воспользоваться командой Данные Фильтр Расширенный фильтр. Рассмотрим работу с расширенным фильтром на примере выделения из БД записей донецких производителей колбасы. Для этого определим область для хранения условий отбора. Каждое условие записывается в две ячейки: в верхнюю - имя поля, в нижнюю - знак отношения (>,=,<=,) и значение. В нашем случае в ячейку K1 введем Наименование товара, в ячейку K2 - Колбаса, в ячейку L1 - Город, в ячейку L2 - Донецк (рис. 9.10).
Рис. 9.10. Область для хранения условий отбора
Теперь выполним команду Данные Фильтр Расширенный фильтр (рис.9.11).
Рис. 9.11. Диалоговое окно расширенный фильтр
В данном случае два условия соединены логическим действием "И". Для объединения с помощью "ИЛИ" необходимо между именем поля и условием пропустить строчку (рис. 9.12).
Рис. 9.12. Соединение условий при помощи логического "ИЛИ"
При копировании отфильтрованных данных в другое место необходимо, чтобы копируемый диапазон начинался со строки, в которой указываются имена полей БД.
Сводная таблица
Сводные таблицы - одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.
Создадим из нашей БД сводную таблицу для расчета прибыли по каждому товару. Выполним команду Данные Сводная таблица. В первом диалоговом окне Мастер сводных таблиц (рис. 9.13) необходимо установить переключатель в положение, показывающее, откуда берутся данные для сводной таблицы:
• в списке или базе данных MS Excel - если данные берутся с одного рабочего листа;
• во внешнем источнике данных - если данные берутся из внешней базы данных;
• в нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;
• в другой сводной таблице - если сводная таблица создается на основании данных другой сводной таблицы.
В этом же диалоговом окне указывается вид создаваемого отчета - сводная таблица или сводная диаграмма.
Рис. 9.13. Первое диалоговое окно Мастер сводных таблиц
В рассматриваемом примере переключатель устанавливается в положение В списке или базе данных MS Excel.
В поле Диапазон второго диалогового окна Мастер сводных таблиц (рис. 9.14) необходимо указать диапазон, на основании которого строится сводная таблица. В нашем случае: 'Поставщики товара'!$A$:$I$18.
В третьем диалоговом окне Мастер сводных таблиц (рис. 9.15) необходимо установить переключатель в положение, указывающее, где будет размещена сводная таблица - на новом листе или на уже существующем.
Рис. 9.14. Второе диалоговое окно Мастер сводных таблиц
Рис. 9.15. Третье диалоговое окно Мастер сводных таблиц
Структуру сводной таблицы можно создать, воспользовавшись кнопкой Макет…, третьего диалогового окна Мастер сводных таблиц.
Поля БД, на основании которой строится сводная таблица, представлены в окне создания макета в виде кнопок с названием этих полей (рис. 9.16). Перетаскивая их в соответствующие области, пользователь задает необходимую структуру сводной таблицы.
В окне имеются четыре области:
• Строка - для использования данных поля, расположенного в этой области, в качестве заголовка строки;
• Столбец - для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;
• Данные - для суммирования значений поля, расположенного в этой области, в ячейках сводной таблицы;
• Страница - для обеспечения возможности вывода данных сводной таблицы, относящихся только к полю, расположенному в этой области.
Рис. 9.16. Диалоговое окно для создания макета сводной таблицы
В рассматриваемом примере создается сводная таблица, состоящая из строк с наименованием товара и столбцов с названием города из которого этот товар доставлен. В область Данные перемещена кнопка Прибыль. Двойной щелчок по кнопке в области данных, открывает диалоговое окно Вычисление поля сводной таблицы (рис. 9.17), позволяющее выбрать правило по которому подводятся итоги в сводной таблице.
Допустимыми операциями подведения итогов являются: сумма, количество значений, среднее арифметическое, максимальное и минимальное значение, произведение, количество чисел, несмешанное и смешанное отклонение, несмешанная и смешанная дисперсия. В нашем примере в качестве допустимой операции выбрана сумма.
Рис. 9.17. Диалоговое окно Вычисление поля сводной таблицы
Кнопка Параметры… третьего диалогового окна Мастер сводных таблиц позволяет задать некоторые параметры, определяющие вид сводной таблицы (рис. 9.18). Например, в поле Имя можно задать название таблицы. По умолчанию сводные таблицы называются Сводная таблица 1, Сводная таблица 2 и т.д. Устанавливая флажок Общие итоги по столбцам или флажок Общие итоги по строкам можно подвести итоги по столбцам или строкам в сводной таблице. Установка флажка Автоформат позволяет пользоваться средствами автоформата MS Excel. Установив флажок Сохранить данные вместе с таблицей можно создать дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении.
Рис. 9.18. Диалоговое окно Параметры сводной таблицы
После щелчка по кнопке Готово в третьем диалоговом окне Мастер сводных таблиц на рабочем листе будет создана сводная таблица, представленная на рис. 9.19.
Рис. 9. 19. Сводная таблица подсчета прибыли по каждому товару
1.
ВАРИАНТЫ ЗАДАНИЙ
1. Создайте табличный документ и сохраните его в личной папке.
2. Заполните таблицу данными и формулами в соответствии с условием задания (не менее 10 строк).
3. Используя функцию Итоги…, заполнить поля отмеченные звездочкой.
4. Установите фильтры в соответствии с вариантом задания.
5. Получите сводные таблицы для исходной и отфильтрованной таблиц по нескольким показателям с помощью мастера сводных таблиц. Строки и столбцы для сводных таблиц выберите самостоятельно.
6. Переставьте местами строки и столбцы сводных таблиц.
7. Создайте диаграммы по сводным таблицам
Вариант № 1. Ведомость по продаже сигарет
Марка Месяц Фактическое количество Цена Сумма План
Dunhill Февраль 100 20,5 р.
... ... ... ... ... ...
ИТОГО * *
1. Сформировать столбец суммы.
2. Столбец План заполнить в зависимости от марки сигарет следующими значениями:
• для Pall-Mal, Bond, L&M - 250;
• для Dunhill - 100;
• для Marlboro - 150.
3. Используя функцию Итоги…, рассчитать на какую сумму, и в каком количестве было продано сигарет каждой марки.
4. Используя расширенный фильтр, отфильтровать информацию о сигаретах, поставленных в феврале по цене ниже 20.85 р. за пачку.
5. Установить фильтр для отображения всех поставок сигарет Bond.
Вариант № 2. Ведомость о реализации товаров
Дата реализации Наименование товаров Поставщика Цена Количество Сумма Скидка Итого
1/02/11 Носки База №2
2/02/11 Платье База №5
3/02/11 Костюм База №7
1/02/11 Костюм База №9
4/02/11 Носки База №2
5/02/11 Костюм База №9
10/02/11 Костюм База №7
11/02/11 Носки База №2
2/02/11 Платье База №5
6/02/11 Носки База №2
5/02/1 Костюм База №7
4/02/11 Платье База №5
3/02/11 Костюм База №9
ИТОГО * * * *
1. Сформировать поле Сумма.
2. Поле Скидка заполнить следующим образом:
• для суммы менее 100 р. - 0%;
• для суммы от 100 р. до 1000 р. - 2%;
• для суммы свыше 1000 р. - 5%.
3. В поле Итого подсчитывается общая сумма скидки в рублях.
4. Используя функцию Итоги…, рассчитать на какую сумму, и в каком количестве было продано товаров каждым поставщиком.
5. Используя расширенный фильтр, отфильтровать данные для отображения всех товаров, полученных с Баз №2 и № 9.
6. Установить фильтр для отображения всех продаж со скидками более 200 р.
Вариант № 3. Доставка товара
Название мороженого Месяц Поставщик Закупочная цена Отпускная цена Количество Стоимость доставки Общая прибыль
Пломбир Июль Геркулес 10.69 10.85 3000
Пломбир Июль Винтер
Морозко Июнь Геркулес
Эскимо Август Мушкетер
Каштан Сентябрь Винтер
Пломбир Сентябрь
... ... ... ... ... ... ... ...
Всего * * * * *
1. Поле Стоимость доставки заполняется следующим образом:
• Пломбир - 900 р.
• Морозко - 1500 р.
• Крем-брюле - 500 р.
2. Сформировать поле Общая прибыль.
3. Используя функцию Итоги…, рассчитать прибыль, стоимость доставки и количество мороженого проданного каждым производителем.
4. Используя автофильтр, отфильтровать все закупки пломбира летом.
5. Используя расширенный фильтр, отобразить все закупки объемом более 2000 р.
Вариант № 4. Поставки товара
Дата поставки Наименования товара Поставщик товара Цена Количество Сумма Дилерская скидка
1/04/10 Портфель ЧП "Днепр"
1/04/10 Ремень мужской ООО "Кожгалантерея"
1/04/10 Косметический набор Lancom ЧП "Иванофф"
1/04/10 Сумка дорожная ЧП "Днепр"
5/04/10 Косметический набор Lancom ЧП "Иванофф"
5/04/10 Сумка дорожная ЧП "Днепр"
5/04/10 Ремень мужской ООО "Кожгалантерея"
10/04/10 Косметический набор Lancom ЧП "Иванофф"
10/04/10 Сумка дорожная ООО "Кожгалантерея"
11/04/10 Сумка дорожная ЧП "Днепр"
12/04/10 Косметический набор Lancom ЧП "Иванофф"
15/04/10 Ремень мужской ООО "Кожгалантерея"
Всего * *
1. Сформировать поле Сумма.
2. Поле "Дилерская скидка" заполняется следующим образом:
• ЧП "Иванофф" - 5%.
• ООО "Кожгалантерея" - 10%.
• ЧП "Днепр" - 6%.
3. Используя функцию Итоги…, рассчитать на какую сумму и в каком количестве было продано товаров каждого вида.
4. Используя расширенный фильтр, отфильтровать данные для отображения всех поставок от ЧП "Днепр" после 5/04/10.
5. Используя автофильтр, отобразить все поставки косметики объемом более 30 единиц после 5/02/10.
Вариант № 5. ООО "Мир ПК". Ведомость выполнения плана товарооборота по подразделениям предприятия
Наименование подразделения Наименование товарной группы Поставщик Сумма заказа Сумма фактической реализации Прибыль Форма оплаты
Отдел сбыта Компьютеры ООО "Электронная техника"
Магазин №1 Оргтехника ООО "Империя компьютеров"
Магазин №4 Фирма "Мультимедиа"
...
Сумма * * *
1. Заполнить поле Прибыль.
2. Поле Форма оплаты заполняется следующим образом:
• ООО "Электронная техника" - наличные;
• ООО "Империя компьютеров" - безналичные;
• Фирма "Мультимедиа" - кредит.
3. Используя функцию Итоги…, рассчитать прибыль и сумму заказов каждого подразделения фирмы.
4. Используя автофильтр, отфильтровать данные для отображения всех поставок из фирмы "Мультимедиа" с прибылью более 20000 р.
5. Используя расширенный фильтр, сформировать новую базу данных для отображения всей оргтехники, поставленной из ООО "Электронная техника"
Вариант № 6. Ведомость закупки чая
Тип чая Форма упаковки Производитель Месяц Цена, р Количество Сумма
Зеленый Пачка Riston Январь 80,8 50
Черный Пакетики Ahmad
Красный Dilmah
Желтый Edwin
...
Всего * *
1. Сформировать поле Сумма.
2. В графу Форма упаковки заносят следующие значения: пачка, пакетики, банка.
3. Используя функцию Итоги…, подсчитать сумму закупки каждого типа чая, сумму закупки от каждого производителя и количество упаковок в пачках, пакетиках и банках.
4. Используя автофильтр, необходимо отобразить данные о продаже зеленого чая в мае.
5. Используя расширенный фильтр сформировать новую базу данных, куда занести информацию о продаже либо красного чая, либо чая в пакетиках.
Вариант № 7. ООО "ПРОДОВОЛЬСТВИЕ"
Группа товаров Наименование товаров Количество Закупочная цена Отпускная цена Сумма Прибыль
Хлебобулочные Хлеб
Молочные Молоко
Мясные Паштет
Молочные Творог
Мясные Паштет
Мясные Мясо
Хлебобулочные Хлеб
Мясные Мясо
Мясные Паштет
Хлебобулочные Батон
Молочные Ряженка
Всего * * *
1. Сформировать поле Сумма.
2. Поле Прибыль заносится значение ДА, если отпускная цена превышает закупочную цену на 10%, иначе НЕТ.
3. Используя функцию Итоги…, подсчитать на какую сумму было закуплено товара по каждой группе и количество продуктов, принесших прибыль.
4. Используя автофильтр, вывести информацию о мясных продуктах ценой менее 70 р.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о поставках хлеба или молочных продуктов.
Вариант № 8. Ведомость реализации товаров
Наименование товара Дата реализации Поставщик Цена Количество Сумма
Сыр 1/04/10
Масло 1/04/10
Сметана 1/04/10
Молоко 1/04/10
Сметана 4/04/10
Молоко 5/04/10
Сыр 5/04/10
Масло 6/04/10
...
Всего * *
1. В графу Поставщик заносятся в зависимости от наименования товара следующие значения:
• Сыр, масло - Молокозавод №1;
• Сметана, молоко - Молокозавод №2;
• Йогурт - ЧП "Свежесть".
2. Сформировать поле Сумма.
3. Используя функцию Итоги…, рассчитать на какую сумму и в каком количестве было продано товаров каждого вида и каждым поставщиком.
4. Используя автофильтр, необходимо отфильтровать данные для отображения всех продаж молока на сумму более 1000 р.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию всех продаж йогурта и сыра на сумму менее 700 р.
Вариант № 9. Мониторы
Производитель Тип Модель Цена (р.) Цена (у.е.) Количество Стоимость(грн)
Samsung 17" Samsung 757NF 11000
Samsung 17" Samtron 76DF 3500
LG 17" LG FK 710PH 8700
Philips 17" Philips 107P4Q 11150
SONY 17"
Samtron 19"
SONY 19"
Samsung 15"
...
ВСЕГО * * * *
1. Сформировать поле Цена (у.е.), если 1$=29,35 р.
2. Поле Количество заполнить следующим образом:
• если цена меньше или равна 3500, то 10
• если цена от 3500 до 10000 то 7
• иначе 5
3. Сформировать поле Стоимость.
4. Используя функцию Итоги…, вычислить средние цены мониторов каждого производителя в у.е., и количество мониторов каждого типа.
5. Используя автофильтр, необходимо отфильтровать данные для отображения всех мониторов, произведенных фирмой Samsung.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех 17" мониторах.
Вариант № 10. Информация о ряде стран мира
Полушарие Земли Часть света Страна Площадь, тыс. кв. км. Население, тыс. чел. Плотность населения, чел./кв. км.
Восточное Африка Гвинея 246 5290
Восточное Европа Дания 44.5 5111
Западное Юж. Америка Уругвай 176 2947
Восточное Африка Сенегал 196 6600
Западное Юж. Америка Бразилия 8512 135560
Западное Юж. Америка Перу 12285 19700
Западное Юж. Америка Чили 757 12470
Восточное Европа Швеция 450 8359
Восточное Азия Вьетнам 331.7 60863
Восточное Африка Либерия 111 22200
Восточное Азия Монголия 1566.5 1866
Восточное Азия Япония 372 120030
...
1. Сформировать поле Полушарие, в зависимости от части света.
2. Сформировать поле Плотность населения.
3. Определить общую площадь и общее количество населения каждого полушария и каждой части света.
4. Используя функцию Итоги…, определить средние значения площади стран для каждого полушария и части света.
5. Используя автофильтр, необходимо отфильтровать данные для стран, плотность населения которых, меньше среднего значения.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о странах западного полушария, у которых площадь больше среднего значения.
Вариант № 11. Сотрудники
Ф. И. О Подразделение Должность Дата поступления на работу Ставка Оклад Надбавка Начислено
Иванов П. Ф. каф. физики Доцент 13.08.1991 0,5
Петров А.Р. каф. ВМ Доцент 1
Сидоров Р.Л. каф. ТМ Доцент 0,25
Козлов А.Д. каф. физики Ассистент 1
...
ИТОГО *
1. Сформировать поле Оклад, учитывая, что ставка доцента - 8460 р, старшего преподавателя - 6470, ассистента - 4840.
2. Поле Надбавка заполняется в зависимости от стажа работы на предприятии:
• При стаже более 3 лет - 10% от оклада;
• При стаже более 10 лет - 20% от оклада;
• При стаже более 20 лет - 30% от оклада.
3. Сформировать поле Начислено.
4. Используя функцию Итоги…, определить средний оклад по каждому подразделению.
5. Используя автофильтр, необходимо отфильтровать данные для отображения информации о всех доцентах.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех ассистентах, работающих на кафедре математики.
Вариант № 12. БД "БАНК"
Фамилия клиента Тип вклада Размер вклада (р) Вклад (у.е.) Отделение банка Примечание
Сидоров Текущий 231000 Северное
Иванов Депозит 345000 Центральное
Петров Депозит 345720 Западное
Сидоров Депозит 45678930 Западное
Козлов Текущий 1254883 Центральное
Васин Текущий 54589663 Северное
Сидоров Депозит 25486 Западное
Козлов Депозит 6897674 Северное
Васин Депозит 65783700 Центральное
Петров Текущий 537000 Центральное
...
ИТОГО * *
1. Заполнить поле Вклад (у.е.), если 1$=29,44 р.
2. Заполнить поле Примечание :
• Сидоров - Временно выбыл
• Козлов - Сменил адрес
• Иванов - Перевел в другое отделение
3. Используя функцию Итоги…, определить сумму вкладов каждого клиента и сколько в среднем денег хранится в каждом отделении банка.
4. Используя автофильтр, отобразить всех клиентов хранящих деньги на депозите.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех клиентах, хранящих деньги в Северном отделении банка.
Вариант № 13. Принтеры
Производитель Тип Модель Цена (р) Цена (у.е.) Количество Стоимость(грн)
LEXMARK струйный Z25 2180
EPSON струйный C62 4240
CANON струйный S-200 3160
HP струйный 3420C 2990
EPSON матричный LX-300 8600
CANON лазерный LBP-810 9520
HP лазерный 1000W 9570
HP лазерный 1200 17730
Samsung лазерный ML-1210 9030
LEXMARK струйный Z45 3640
...
ВСЕГО * *
1. Сформировать поле Цена (у.е.)
• если цена в р. меньше 5000, то 1$=29,56 р.
• если цена в р. от 5000 до 10000, то 1$=29,44 р.
• иначе 1$=29,37 р.
2. Сформировать поле Стоимость.
3. Используя функцию Итоги…, вычислить средние цены принтеров каждого производителя в р., и количество принтеров каждого типа.
4. Используя автофильтр, отфильтровать данные для отображения всех принтеров, произведенных фирмой HP.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех лазерных принтерах.
Вариант № 14. ООО "АВТО"
Фирма Марка Цена (у.е.) Цена (р.) Мощность двигателя, л.с. Скидка Тип кузова
Mitsubishi Pajero Sport 2,5 TD GLX $29 990 100 внедорожник
Mitsubishi Pajero 3,5 GDI GLS AT $49 590 202 внедорожник
Skoda Fabia Combi 1,4 Classic $10 500 68 комби
Mitsubishi Galant 2,5-V6 Elegance $26 900 161 седан
Mitsubishi Galant 2,0 Comfort $22 900 133 седан
Mitsubishi Pajero Sport 3,0 V6 GLX $36 590 177 внедорожник
Peugeot 307 $12 930 75 хэтчбек
Skoda Fabia Sedan 1,4 Classic $10 200 68 седан
Skoda Octavia 1,8 Elegance $18 800 150 седан
Skoda Octavia 1,6 Classic $12 100 75 седан
Peugeot 206 $8 775 60 седан
Skoda Octavia 1,6 Ambiente $12 450 101 седан
Skoda Fabia 1,4 Basic $8 600 60 хэтчбек
Skoda Fabia 1,4 Comfort $9 990 68 седан
...
ИТОГО * * *
1. Сформировать поле Цена (у.е.), 1$=29,37 р
2. Сформировать поле Скидка, следующим образом:
• мощность двигателя меньше 100 л.с. - 2%
• мощность двигателя от 100 до 150 л.с. - 1,5%
• иначе - 1%
3. Используя функцию Итоги…, вычислить среднюю цену на автомобили каждой марки в у.е., и количество автомобилей каждого типа.
4. Используя автофильтр, отфильтровать данные для отображения всех автомобилей марки Skoda.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех внедорожниках марки Mitsubishi .
Вариант № 15. Сведения о ряде геометрических тел
Номер тела Вид тела Вид материала Плотность материала, г/см3 Объем тела, см3 Масса тела, г
1 Куб 7,8 123
2 Шар 2,6 50
3 Куб 1,5 41
4 Куб 1,2 200
5 Шар 2,6 8
6 Шар 1,5 134
7 Шар 7,8 30
8 Куб 1,2 51
9 Куб 7,1 100
10 Куб 8,9 43
11 Шар 1,3 258
...
Итого * *
1. Сформировать поле Масса тела.
2. Поле Вид материала заполнить следующим образом:
• плотность более 1,5 - металл,
• иначе пластмасса.
3. Используя функцию Итоги…, вычислить общую массу и общий объем всех шаров и всех кубов, среднее значение массы и объема для всех тел из металла и для всех тел из пластмассы.
4. Используя автофильтр, отфильтровать данные для отображения информации о всех шарах.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех однотипных изделиях (металлические шары, пластмассовые кубы и т.д.).
Вариант № 16. Сведения о ряде геометрических фигур
Номер фигуры Вид фигуры Тип фигуры Сторона 1/ Катет 1 Сторона 2/ Катет 2 Площадь фигуры
1 Треугольник 12 12
2 Треугольник 3 3
3 Прямоугольник 10 10
4 Прямоугольник 3 5
5 Треугольник 10 5
6 Треугольник 3 7
7 Прямоугольник 5 6
8 Треугольник 4 4
9 Треугольник 5,5 2,4
...
Итого
1. Сформировать поле Тип фигуры:
• Сторона 1 = Сторона 2 - квадрат
• Сторона 1 ≠ Сторона 2 - неквадрат
• Катет 1 = Катет 2 - равносторонний
• Катет 1 ≠ Катет 2 - прямоугольный
2. Сформировать поле Площадь фигуры, в зависимости от ее типа.
3. Используя функцию Итоги…, вычислить среднюю площадь всех треугольников и всех прямоугольников.
4. Используя автофильтр, отфильтровать данные для отображения информации обо всех прямоугольниках.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию обо всех квадратах и прямоугольных треугольниках.
Вариант № 17. Акционеры фирмы "КУПИ-ПРОДАЙ"
№ п/п Фамилия Выпуск акций Вид акции Кол-во Номинальная стоимость акции Общая стоимость акций
1 Сидиромов 1 2 5000
2 Мониторов 2 10 500
3 Сидиромов 1 12 500
4 Мышкин 2 30 5000
5 Сидиромов 2 67 500
6 Мониторов 1 43 500
7 Мышкин 1 57 5000
8 Мониторов 2 2 5000
9 Сидиромов 1 5 500
10 Мониторов 2 54 500
11 Дискеткин 2 32 5000
...
Итого * *
1. Сформировать поле Общая стоимость.
2. Сформировать поле Вид акции, следующим образом:
• номинальная стоимость 500 р. - обыкновенная
• номинальная стоимость 5000 р. - привилегированная
3. Используя функцию Итоги…, вычислить на какую сумму приобретены акции каждым из акционеров и количество акций в каждом выпуске.
4. Используя автофильтр, отфильтровать данные для отображения информации о акциях первого выпуска.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о акциях каждого вида.
Вариант № 18. Сведения о прохождении автомобилями участков пути
№ п/п Фирма Вид автомобиля Средняя скорость на участке, км/ч Время прохождения участка, ч Длина участка, км
1 Nissan Легковой 0,7 131
2 Fiat Грузовой 2,3 163
3 Nissan 4,3 665
4 Nissan 0,9 90
5 Nissan 1,6 240
6 Fiat 2,8 254
7 Fiat 3,6 234
8 Fiat 4,1 654
9 Fiat 4,6 346
10 Nissan 3,5 786
11 Nissan 4,5 144
12 Fiat 1,9 346
...
Итого * *
1. Сформировать поле Вид автомобиля:
• для нечетных номеров - легковой
• для четных - грузовой.
2. Сформировать поле Средняя скорость на участке, км/ч.
3. Используя функцию Итоги…, вычислить среднюю скорость по каждому виду автомобилей и по каждой фирме.
4. Используя автофильтр, отфильтровать данные для отображения информации о легковых автомобилях.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о грузовых автомобилях фирмы Fiat.
Вариант № 19. Сотрудники
Ф. И. О Подразделение Должность Дата поступления на работу Ставка Оклад Надбавка Начислено
Иванов П. Ф. каф. физики Доцент 13.08.1991 0,5
Петров А.Р. каф. ВМ Доцент 1
Сидоров Р.Л. каф. ТМ Доцент 0,25
Козлов А.Д. каф. физики Ассистент 1
...
ИТОГО *
1. Сформировать поле Оклад, учитывая, что ставка доцента - 8460 р, старшего преподавателя - 6470, ассистента - 4840.
2. Поле Надбавка заполняется в зависимости от стажа работы на предприятии:
• При стаже более 3 лет - 10% от оклада;
• При стаже более 10 лет - 20% от оклада;
• При стаже более 20 лет - 30% от оклада.
3. Сформировать поле Начислено.
4. Используя функцию Итоги…, определить средний оклад по каждому подразделению.
5. Используя автофильтр, необходимо отфильтровать данные для отображения информации о всех доцентах.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех ассистентах, работающих на кафедре математики.
Вариант № 20. БД "БАНК"
Фамилия клиента Тип вклада Размер вклада (р) Вклад (у.е.) Отделение банка Примечание
Сидоров Текущий 231000 Северное
Иванов Депозит 345000 Центральное
Петров Депозит 345720 Западное
Сидоров Депозит 45678930 Западное
Козлов Текущий 1254883 Центральное
Васин Текущий 54589663 Северное
Сидоров Депозит 25486 Западное
Козлов Депозит 6897674 Северное
Васин Депозит 65783700 Центральное
Петров Текущий 537000 Центральное
...
ИТОГО * *
1. Заполнить поле Вклад (у.е.), если 1$=29,44 р.
2. Заполнить поле Примечание :
• Сидоров - Временно выбыл
• Козлов - Сменил адрес
• Иванов - Перевел в другое отделение
3. Используя функцию Итоги…, определить сумму вкладов каждого клиента и сколько в среднем денег хранится в каждом отделении банка.
4. Используя автофильтр, отобразить всех клиентов хранящих деньги на депозите.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех клиентах, хранящих деньги в Северном отделении банка.
Вариант № 21. ООО "ПРОДОВОЛЬСТВИЕ"
Группа товаров Наименование товаров Количество Закупочная цена Отпускная цена Сумма Прибыль
Хлебобулочные Хлеб
Молочные Молоко
Мясные Паштет
Молочные Творог
Мясные Паштет
Мясные Мясо
Хлебобулочные Хлеб
Мясные Мясо
Мясные Паштет
Хлебобулочные Батон
Молочные Ряженка
Всего * * *
1. Сформировать поле Сумма.
2. Поле Прибыль заносится значение ДА, если отпускная цена превышает закупочную цену на 10%, иначе НЕТ.
3. Используя функцию Итоги…, подсчитать на какую сумму было закуплено товара по каждой группе и количество продуктов, принесших прибыль.
4. Используя автофильтр, вывести информацию о мясных продуктах ценой менее 70 р.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о поставках хлеба или молочных продуктов.
Вариант № 22. Ведомость реализации товаров
Наименование товара Дата реализации Поставщик Цена Количество Сумма
Сыр 1/04/10
Масло 1/04/10
Сметана 1/04/10
Молоко 1/04/10
Сметана 4/04/10
Молоко 5/04/10
Сыр 5/04/10
Масло 6/04/10
...
Всего * *
1. В графу Поставщик заносятся в зависимости от наименования товара следующие значения:
• Сыр, масло - Молокозавод №1;
• Сметана, молоко - Молокозавод №2;
• Йогурт - ЧП "Свежесть".
2. Сформировать поле Сумма.
3. Используя функцию Итоги…, рассчитать на какую сумму и в каком количестве было продано товаров каждого вида и каждым поставщиком.
4. Используя автофильтр, необходимо отфильтровать данные для отображения всех продаж молока на сумму более 1000 р.
5. Используя расширенный фильтр, сформировать базу данных, куда занести информацию всех продаж йогурта и сыра на сумму менее 700 р.
Вариант № 23. Мониторы
Производитель Тип Модель Цена (р.) Цена (у.е.) Количество Стоимость(грн)
Samsung 17" Samsung 757NF 11000
Samsung 17" Samtron 76DF 3500
LG 17" LG FK 710PH 8700
Philips 17" Philips 107P4Q 11150
SONY 17"
Samtron 19"
SONY 19"
Samsung 15"
...
ВСЕГО * * * *
1. Сформировать поле Цена (у.е.), если 1$=29,35 р.
2. Поле Количество заполнить следующим образом:
• если цена меньше или равна 3500, то 10
• если цена от 3500 до 10000 то 7
• иначе 5
3. Сформировать поле Стоимость.
4. Используя функцию Итоги…, вычислить средние цены мониторов каждого производителя в у.е., и количество мониторов каждого типа.
5. Используя автофильтр, необходимо отфильтровать данные для отображения всех мониторов, произведенных фирмой Samsung.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о всех 17" мониторах.
Вариант № 24. Информация о ряде стран мира
Полушарие Земли Часть света Страна Площадь, тыс. кв. км. Население, тыс. чел. Плотность населения, чел./кв. км.
Восточное Африка Гвинея 246 5290
Восточное Европа Дания 44.5 5111
Западное Юж. Америка Уругвай 176 2947
Восточное Африка Сенегал 196 6600
Западное Юж. Америка Бразилия 8512 135560
Западное Юж. Америка Перу 12285 19700
Западное Юж. Америка Чили 757 12470
Восточное Европа Швеция 450 8359
Восточное Азия Вьетнам 331.7 60863
Восточное Африка Либерия 111 22200
Восточное Азия Монголия 1566.5 1866
Восточное Азия Япония 372 120030
...
1. Сформировать поле Полушарие, в зависимости от части света.
2. Сформировать поле Плотность населения.
3. Определить общую площадь и общее количество населения каждого полушария и каждой части света.
4. Используя функцию Итоги…, определить средние значения площади стран для каждого полушария и части света.
5. Используя автофильтр, необходимо отфильтровать данные для стран, плотность населения которых, меньше среднего значения.
6. Используя расширенный фильтр, сформировать базу данных, куда занести информацию о странах западного полушария, у которых площадь больше среднего значения.
Не Пропустите:
- Тест по электронной таблице
- Создание новой БД в Access
- Список экзаменационных вопросов и практических заданий по дисциплине «Базы данных»
- Лабораторная работа № 2. 1. СОЗДАНИЕ ТАБЛИЦЫ С РАСЧЕТНЫМИ ФОРМУЛАМИ
- Список экзаменационных вопросов и практических заданий по дисциплине «Разработка и эксплуатация удаленных баз данных» для специальности 230105 «Программное обеспечение вычислительной техники и автоматизированных систем»