Создание и обработка баз данных – MS Excel. КР Смыслова ЭБТ 808-3. Создание и обработка баз данных ms excel
 Скачать 1.85 Mb.
|
|
Ф ФЕДЕРАЛЬНОЕ АГЕНТСТВО ЖЕЛЕЗНОДОРОЖНОГО ТРАНСПОРТА Федеральное государственное бюджетное образовательное учреждение высшего образования «ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ Императора Александра I» Кафедра «ИНФОРМАТИКА И ИНФОРМАЦИОННАЯ БЕЗОПАСНОСТЬ» Дисциплина «ИНФОРМАТИКА» ОТЧЁТ ПО КОНТРОЛЬНОЙ РАБОТЕ № 5 НА ТЕМУ «Создание и обработка баз данных – MS Excel» ВАРИАНТ 3 К защите 11.01.19 Выполнила студентка А. В. Смыслова Факультет безотрывных форм обучения Группа: ЭБТ 808-3 Номер зачетной книжки: 07-808-3 Проверил Санкт-Петербург 2018 Оглавление
1 Введение Большие массивы данных об объектах называются базами данных. База данных -набор данных, содержательно связанных между собой общей темой или назначением. Обычно БД создается для одной конкретной прикладной задачи. И оформляется в таблицах в прикладных программах Excel или Access. Каждая таблица состоит из фиксированного числа столбцов и некоторого количества строк. Каждый столбец представляет конкретное данное. На языке баз данных столбцы таблицы называются полями, причем для каждого поля разработчик должен определить уникальное имя поля. Каждое поле может входить в несколько таблиц. Строки баз данных называют записями. Запись является стандартным блоком для хранения данных в таблице, выборке данных в запросе, форме, выводимой на экран. Рассмотрим в данной контрольной работе пример базы данных "Лекарственные средства" в прикладной программе MS Excel. 2 Постановка задачи Спроектировать базу данных в MS Excel, содержащую следующие данные: входные данные: База данных Лекарственные средства должна содержать следующие элементы: – наименование лекарственного средства, имеющегося в аптеке; – срок годности; – цену одной номенклатурной единицы; – количество единиц в наличии данного наименования; – необходимое количество единиц запаса; – сведения о предприятии-изготовителе лекарственного средства (название, адрес); – сведения о фирме-поставщике (название, номер телефона, фамилия и инициалы директора). выходные данные: 1. Заполнение и редактирование таблиц базы данных. 2. Формирование списка медикаментов, срок годности которых истек, с указанием названия медикамента и номера телефона поставщика. 3. Вывод сведений о медикаментах, которые поступили от заданного пользователем поставщика и предприятия-изготовителя, с подсчетом цены товара по номенклатурным единицам. 4. Вывод сведений о наименовании лекарственного средства, имеющего количество единиц в наличии меньше 10% от необходимого запаса. 5. Формирование отчета, включающего все лекарственные средства, сгруппированные по поставщикам. Отчет должен содержать следующие данные: количество номенклатурных единиц лекарственного средства и недостающих единиц запаса, название фирмы-поставщика и фамилию директора, общую стоимость товара по каждому поставщику. 3 Решение задачи 3.1 Создание базы данных по продажам лекарственных средств В MS EXСEL cоздается база данных по продажам медикаментов, позволяющая ее дополнять, редактировать, вносить изменения. В таблицу вносятся данные о наименовании медикаментов, имеющихся в аптеке, сроке годности медикаментов, цене одной номенклатурной единицы, количестве единиц в наличии данного наименования, необходимое количество единиц запаса, сведения о предприятии-изготовителе лекарственного средства (название и адрес), а также сведения о фирме-поставщике (название, номер телефона, фамилия и инициалы директора). В  результате получается таблица следующего вида (рис.1):Рис. 1. База данных 3.2 Формирование списка медикаментов, у которых истек срок годности, с указанием названия медикамента и номера телефона поставщика Создаем графу возможности ввода текущей даты и вводим в нее текущую дату, с которой будет производится сравнение срока годности медикаментов:
Создаем новую таблицу из требуемых граф (рис.2):
Рис. 2. Список медикаментов В таблице производится поиск медикамента по следующей формуле: =ЕСЛИ('База данных (1 зад и 3 зад)'!B5<='2 зад'!$C$5;'База данных (1 зад и 3 зад)'!A5;0), в которой производится сравнение текущей даты и даты срока годности медикаментов. Если срок годности закончился, то есть текущая дата больше даты срока годности, то данные по медикаменту попадают в таблицу. 3.3 Вывод сведений о медикаментах, которые поступили от заданного пользователем поставщика и предприятия-изготовителя, с подсчетом цены товара по номенклатурным единицам. Для расчета цены медикамента создается новая графа в таблице базы данных. В которой цена рассчитывается следующим образом: цена 1 единицы данного медикамента умножается на общее количество медикаментов данного вида на складе. Для того, чтобы получить сведения о медикаментах по заданному поставщику или изготовителю, требуется выделить всю таблицу, далее требуется нажать на панеле быстрого доступа вкладку ДАННЫЕ и далее выбрать кнопку ФИЛЬТР. Таким образом создается возможность фильтровать таблицу: появляется стрелка выбора поставщика, при нажатии которой можно выбрать требуемого поставщика или же изготовителя. Задание оформляется в следующей таблице (рис.3):  Рис. Рис. 3. Медикаменты заданного поставщика Рис.3. Сведения о медикаментах от заданного поставщика или изготовителя После выбора требуемого поставщика в таблице остаются данные только по нужному поставщику, остальные строки скрываются (рис.4):  Рис. 4. Данные по требуемому поставщику 3.4 Вывод сведений о наименовании лекарственного средства, имеющего количество единиц в наличии меньше 10% от необходимого запаса Создается новая таблица для вывода требуемых данных с наименованиями столбцом, как у первичной базы. Таблица заполняется по формуле: =ЕСЛИ(('База данных (1 зад и 3 зад)'!$D5/'База данных (1 зад и 3 зад)'!$E5)<0,1;'База данных (1 зад и 3 зад)'!A5;0), в которой количество медикаментов на складе делится на необходимый запас. Если полученная величина меньше, чем 0,1 (10 %%), то данные по медикаменту заносятся в таблицу, а данные по количеству медикаментов, которых хватает на складе, не заносятся в данную таблицу. Итоговая таблица выглядит следующим образом (рис.5):  Рис. 5. Сведения о заниженных количествах лекарств. 3.5 Формирование отчета, включающего все лекарственные средства, сгруппированные по поставщикам Отчет должен содержать следующие данные: количество номенклатурных единиц лекарственного средства и недостающих единиц запаса, название фирмы-поставщика и фамилию директора, общую стоимость товара по каждому поставщику. Формируется новая таблица на отдельном листе. Данные автоматически заносятся в таблицу по формуле: ='База данных (1 зад и 3 зад)'!A7 Далее требуется вручную выделить всю таблицу и отсортировать ее по алфавиту по Поставщику при помощи функции "Сортировка и фильтр" в закладке меню "Главная". Отсортированные по алфавиту данные показаны в таблице (рис.6):  Рис.6. Отчёт о лекарствах, выстроенных по алфавиту по поставщикам Далее в меню выбираем закладку: "Данные", "Промежуточные итоги": Открывается окно, в котором можно выбрать поставщика и данные, по которым следует подсчитать сумму. Выбираем те данные, которые требуются для формирования отчета и формируем отчет (рис.7):  Рис. 7. Функция Промежуточные итоги Отчет можно вывести на экран в сжатом виде и в подробном виде при помощи кнопок-знаков слева от окна. + и -. Подробный вид (рис.8):  Рис. 8. Подробный итоговый отчет Сжатый вид (рис.9):  Рис. 9. Итоги в сжатом виде 4 Заключение МS Excel является в настоящее время одной из самых популярных программ среди пользователей по созданию баз данных. Среди причин такой популярности следует отметить: - удобство ввода и редактирования данных таблиц, т.к. программа создает интерфейс по выбору пользователя; - поиск данных в таблицах по определенным критериям; - контроль ключевых полей; - создание форм отчетов, в которых можно менять содержание и стиль оформления; - возможность пополнять базу данных новыми таблицами и решать новые задачи, т.е. составлять новые отчеты и формы. 5 Список литературы 1 Электронный самоучитель Excel ( http://office-guru.ru), 2 Excel 2007. Пащенко И., изд. Эксмо. 3 Excel 2007. Эффективный самоучитель + справочник пользователя. Серогодский. 2008, изд. Наука, С-Пб. |