Виртуальная лабораторная работа Базы данных в Microsoft Excel ГУ Куз. ГТУ, Кафедра «Автомобильные перевозки» Автор: Семенова Ольга Сергеевна 2013
Общие понятия Базы данных как способ хранения и обработки различной информации играют в настоящее время огромную роль. В базах данных хранят сведения о клиентах, заказах, справочники адресов и телефонов, различного рода информацию о туристических агентствах и предлагаемых услугах и т. д. Если под базой данных понимать любую упорядоченную информацию, объединенную в единое целое, то содержимое рабочего листа Microsof Excel можно рассматривать как базу данных, а сам Excel как средство для организации и хранения базы данных. В Microsoft Excel в качестве базы данных используется список (Рис1. ). Список - это группа строк таблицы, содержащая связанные данные. Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные, например, перечень фамилий, дату рождения и т. д. Для работы с базами данных Excel предлагает такие возможности, как: • удобные методы поиска информации; • анализ данных; • фильтрация данных; • сортировка данных; • формирование сводного отчета. Рисунок 1 Для того, чтобы таблица в Excel использовалась и обрабатывалась как база данных необходимо, чтобы она состояла из строк одинаковой структуры и имела строку заголовка. Если таблицу считают базой данных, то: • столбцы списков становятся полями базы данных; • заголовки столбцов становятся именами полей базы данных; • каждая строка списка преобразуется в запись данных.
Проверка данных при вводе Если с файлом работает сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. В Excel выполнение подобных условий проверяется при помощи средства, которое называется проверкой ввода. Для этого надо: • Выделить ячейки столбца, для которого устанавливается проверка ввода. • На ленте Данные в группе Работа с данными выбрать команду Проверка данных. • На вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например, "м" or "ж"). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения (Рис. 2). • • На вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек. На вкладке Сообщение об ошибке можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое появится при вводе в ячейку недопустимого значения. Рисунок 2
Сортировка полей данных Сортировка – изменение последовательности записей (строк) в таблице. Поле, по которому производится сортировка, называется ключом. Для сортировки данных необходимо: 1. Выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр (Рис. 3). 2. В диалоговом окне Сортировка выбрать поле, по которому будет происходить сортировка. 3. Задать тип сортировки: • по значению, • по цвету шрифта, • по цвету ячейки, • по значку ячейки. 4. Выбрать порядок сортировки: • прямой, • обратный, • настраиваемый. Рисунок 3
Промежуточные итоги в БД Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет: • упорядочить список посредством группировки записей с выводом промежуточных итогов, средних значений или другой вспомогательной информации; • выводить итоговую сумму; • отображать список в виде структуры, что позволяет разворачивать и сворачивать разделы с помощью щелчка мыши. Перед вызовом команды Итоги список обязательно надо отсортировать по полю, которое будет использоваться для группировки. Режим структуры, в котором оказывается список после выполнения команды Итоги (рис. 4), позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле (рис. 5). Кнопки, расположенные в верхнем левом углу, определяют количество выводимых уровней данных. Кнопки со значками "+" и "-" предназначены для свертыванияразвертывания отельных групп. Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все. Рисунок 5 Рисунок 4
Автофильтр Отфильтровать список - значит показать только те записи, которые удовлетворяют заданному критерию. Чтобы установить или убрать автофильтр надо на ленте Данные в группе Сортировка и фильтр выбрать команду Фильтр. После этого нажать кнопку со стрелкой возле названия какого-либо поля, чтобы раскрыть список его элементов и выбрать отображаемые значения или задать условие отбора (Рис. 6, 7). На экране появятся только те записи, которые отвечают заданному условию (Рис. 8). В случае необходимости можно продолжить фильтрацию, нажимая кнопки со стрелками на других полях. Рисунок 7 Рисунок 6 Рисунок 8
Построение диаграмм на базе промежуточных итогов • • Выделить данные, необходимые для построения диаграмм (используйте клавишу CTRL для выделения несвязанного диапазона). Выбрать команду Круговая диаграмма на ленте Вставка в группе Диаграммы. Рисунок 9
Задание 1 • • Ведомость реализации товаров Заполнить поля Цена, Количество самостоятельно. Рассчитать поле Сумма. Поле Скидка (в процентах) заполнить следующим образом (используя функцию ЕСЛИ): – для суммы менее 100 руб. - 0%; – для суммы от 100 руб. до 1000 руб. - 2%; – для суммы свыше 1000 руб. - 5%. В поле Итого подсчитывается общая сумма скидки (в рублях). Используя функцию Итоги…, рассчитать на какую сумму, и в каком количестве было продано товаров каждым поставщиком. Отфильтровать данные для отображения всех товаров, полученных с Баз № 2 и № 9. Установить фильтр для отображения всех продаж со скидками более 200 руб. Дата Наименован Поставщика реализации ие товаров 1/02/02 Носки База № 2 2/02/02 Платье Костюм Носки Платье Носки Костюм Платье Костюм * * * База № 5 3/02/02 * База № 7 4/02/02 База № 2 5/02/02 База № 5 6/02/02 База № 2 2/02/02 База № 7 11/02/02 База № 9 10/02/02 Итого База № 2 5/02/02 Скидка База № 9 4/02/02 Сумма База № 7 1/02/02 Количество База № 5 3/02/02 Цена База № 9 ИТОГО
Задание 2 Продажа мониторов. • Сформировать поле Цена (у. е. ), если 1$=28, 44 руб. • Поле Количество заполнить следующим образом (с помощью функции ЕСЛИ): – если цена меньше или равна 350, то 10 – если цена от 350 до 1000 то 7 – иначе 5 • Сформировать поле Стоимость. • Используя функцию Итоги…, вычислить средние цены мониторов каждого производителя в у. е. , и количество мониторов каждого типа. • Используя автофильтр, необходимо отфильтровать данные для отображения всех мониторов, произведенных фирмой Samsung. • Построить диаграмму количества мониторов каждого типа. Производитель Тип Модель Цена (руб. ) Samsung 17" Samsung 757 NF 1100 Samsung 17" Samtron 76 DF 350 LG 17" LG FK 710 PH 870 Philips 17" Philips 107 P 4 Q 1115 SONY 17" Samtron 19" SONY 19" Samsung Цена (у. е. ) Количество Стоимость(руб. ) 15" * * * . . . ВСЕГО *
Вопросы для конспекта • • Для чего необходимы базы данных? Какие возможности предоставляет программа Microsoft Excel для работы с базами данных? Что такое запись? Что такое поле? Как установить проверку вводимых в список значений? Что такое сортировка? Как отсортировать список по двум и более ключам? Что такое фильтр? В чем отличие сортировки списка от фильтрации списка?
Скачать презентацию Виртуальная лабораторная работа Базы данных в Microsoft Excel
Excel_Магистры_2.pptx