Часть 2. Решение задачи средствами MS Excel

1. Запустить табличный процессор MS Excel.

2. Создать книгу с именем «Поставки».

3. Лист 1 переименовать в лист с названием «Справочник поставщика»

4. На рабочем листе «Справочник поставщика» MS Excel создать таблицу справочных данных по поставщикам.

5. Заполнить таблицу исходными данными по поставщикам (рис. 13)

Рис. 13. Расположение таблицы «Справочник поставщиков» на рабочем листе «Справочник поставщиков» MS Excel

6. Лист 2 переименовать в лист с названием «Приходная накладная».

7. На рабочем листе «Приходная накладная» создать таблицу «Приходная накладная».

8. Заполнить таблицу исходными данными (см рис. 14).

Рис. 14. Расположение таблицы «Приходная накладная» на рабочем листе «Приходная накладная» MS Excel.

9. Лист 3 переименовать в «Справочник материалов».

10. На рабочем листе «Справочник материалов» создать таблицу справочника материалов.

11. Заполнить таблицу «Справочник материалов» исходными данными (рис. 15.)

Рис.15. Расположение таблицы «Справочник материалов» на рабочем листе «Справочник материалов» MS Excel.

12. Заполнить графу «Наименование поставщика» таблицы «Приходная накладная», находящейся на листе «Приходная накладная» следующим образом:

Занести в ячейку C3 формулу:

= ВПР(B3;'Справочник поставщиков'!$A$2:$B$7;2;0).

Размножить введенную в ячейку С3 формулу для остальных ячеек (с С4 по С13) данной графы.

13. Заполнить графу «Наименование материалов» таблицы «Приходная накладная», находящейся на листе «Приходная накладная» следующим образом:

Занести в ячейку Е3 формулу:

=ВПР(D3;'Справочник материалов'!$A$2:$C$7;2;0)

Размножить введенную в ячейку Е3 формулу для остальных ячеек (с Е4 по Е13) данной графы.

14. Создать ведомость «Фактическое выполнение поставок.

Для этого, выделить на листе «Приходная накладная» таблицу с исходными данными.

Выбрать команду «Сводная таблица» из меню «Данные»;

В окне «Мастер сводных таблиц» - шаг 1 из 3 выбрать опцию «В списке или базе даны Microsoft Excel», Вид создаваемого отчета – «Сводная таблица» и нажать кнопку «Далее».

В окне «Мастер сводных таблиц» - шаг 2 из 3 посмотреть выбранный диапазон таблицы, если его не надо изменять нажать кнопку «Далее». Если необходимо исправить, то исправить диапазон путем выделения всей таблицы с исходными данными и названиями столбцов и нажать кнопку «Далее».

В окне «Мастер сводных таблиц» - шаг 3 из 3 выберите «Поместить таблицу» на - «Новый лист» и нажать кнопку «Далее», затем «Готово».

В появившемся диалоговом окне необходимо перетащить поля таблицы в соответствующие зоны, обозначенные на рисунке.

Перенести в область «Страница» поле «Код материала» из списка полей (см рис. 16).

Рис. 16. Создание макета сводной таблицы

Перенести в область «Строка» поле «Наименование поставщика».

Перенести в область «Элементы данных» поле «Сумма поставки»

Переименовать новый лист в «Фактическое выполнение поставок»

(См. рис. 17).

См. рис. 17. Фрагмент листа «Фактическое выполнение поставок»

15. Создать ведомость «Сумма поставок по дням».

Установите курсор в поле таблицы «Приходная накладная».

Выделить на листе «Приходная накладная» таблицу с исходными данными.

Выбрать команду «Сводная таблица» из меню «Данные»;

В окне «Мастер сводных таблиц» - шаг 1 из 3 выбрать опцию «В списке или базе даны Microsoft Excel», Вид создаваемого отчета – «Сводная таблица» и нажать кнопку «Далее».

В окне «Мастер сводных таблиц» - шаг 2 из 3 посмотреть выбранный диапазон таблицы, если его не надо изменять нажать кнопку «Далее». Если необходимо исправить, то исправить диапазон путем выделения всей таблицы с исходными данными и названиями столбцов и нажать кнопку «Далее».

В окне «Мастер сводных таблиц» - шаг 3 из 3 выберите «Поместить таблицу» на - «Новый лист» и нажать кнопку «Далее», затем «Готово».

В появившемся диалоговом окне необходимо перетащить поля таблицы в соответствующие зоны, обозначенные на рисунке.

Перенести в область «Страница» поле «Код материала» из списка полей.

Перенести в область «Строка» надпись «Наименование поставщика»

Ещё раз перенести в область «Строка» надпись «Дата поставки», см. рис. 18.

Рис. 18.Создание макета сводной таблицы по поставщикам и датам поставки

Перенести в область «Элементы данных» поле «Сумма поставки».

Переименовать лист со сводной таблицей в «Поставки по дням».

Используя сводную таблицу «Фактическое выполнение поставок», можно получить данные по какому-либо материалу. Для этого на странице «Фактическое выполнение поставок» необходимо нажать кнопку правее поля «Код материала» и выбрать код интересующего материала.(см. рис. 19.)

Рис. 19. Фильтрация данных по полю «Код материала».

Создать сводную таблицу «Фактическое выполнение поставок» для каждого кода материала.

Аналогично создать сводные таблицы по поставщика и дата поставки используя сводную таблицу «Поставки по дням» на листе с таким же названием.

3.1.4. Часть 2. Решение задачи средствами MS Access

Постановка задачи в среде MS Access выполняется аналогич­но тому, как это осуществлялось в среде MS Excel: раскрывается ее организационно-экономическая сущность; описываются с помо­щью стандартных таблиц структуры входной, условно-постоян­ной и результирующей информации; указывается алгоритм реше­ния задачи.

Однако разработка «Инструкции к решению задачи» связа­на со спецификой среды MS Access. Специфика диктует деление «Инструкции» на две относительно автономные части: создание базы данных и выдача отчетов. Создание базы данных, в свою оче­редь, состоит из двух этапов: Описание структуры таблиц и Уста­новление связей между таблицами. Источниками информации для описания структуры таблиц служат таблицы из пунктов 2, 3, 4 постановки задачи, в которых дается характеристика каждому эле­менту базы данных. Например, в них указывается Имя поля, Тип данных, Длина поля (число десятичных знаков).

После описания структуры таблиц устанавливаются связи между ними, диктуемые логикой решения задачи. Эти связи поз­воляют впоследствии переносить информацию из одних таблиц в другие. Завершается создание базы данных ее наполнением из входных документов.

Далее происходит собственно решение задачи, т.е. выдают­ся требуемые отчеты. В качестве примера воспользуемся выпол­ненной постановкой задачи «Учет поставок» в среде MS Excel и рассмотрим содержание инструкции по выполнению этой задачи в среде MS Access. Номера рисунков указаны в пределах данной инструкции.

Создание базы данных

1. Вызовите Microsoft Access:

- нажмите Кнопку «Пуск»;

- выделите команду «Программы»;

- выберите Microsoft Access;

- нажмите клавишу Enter.

2.Сформируйте файл новой базы данных:

- раскройте вкладку «Новая база данных» в разделе «Создание» диалогового окна «Создание файла»: появляется диалоговое окно «Файл новой базы данных»;

-выберите имя текущей папки, например «Мои документы»
в поле «Папка» диалогового окна «Файл новой базы данных»;

-присвойте файлу имя «Сведения о поставке материалов»
в поле «Имя файла»;

нажмите кнопку «Создать»: появляется диалоговое окно с
именем файла «Сведения о поставке материалов» (рис. 20).

Рис. 20. Диалоговое окно с именем файла «Сведения о поставке материалов»

3. Создайте формы таблиц «Приходная накладная», «Спра­вочник поставщиков», «Справочник материалов».

3.1. Создайте форму таблицы «Приходная накладная»:

- нажмите кнопку объекта «Таблицы», если она не активизи­рована, в диалоговом окне с именем файла «Сведения о поставке материалов». Появляются команды «Создание таблицы в режиме конструктора», «Создание таблицы с помощью мастера», «Создание таблицы путем ввода данных»;

- выделите в объекте «Таблицы» команду «Создание таблицы в режиме конструктора»;

- нажмите клавишу Enter или «Открыть» в меню диалогового окна: в результате появляется макет для описания полей таблицы;

- осуществите описание полей таблицы в соответствии с таблицей, приведенной ниже и рекомендациями, указанными в лабораторном практикуме. (см.рис. 21,22)

Необходимо иметь в виду, что содержимое реквизита «Имя поля» вводится с клавиатуры; содержимое остальных реквизитов заполняется по указателю из предложенного списка. Ключевое полем можно создать вспомогательное поле «Код», в котором будут отражаться номера строк по порядку.

Имя поля	Тип данных	Размер поля	Формат поля	Число десятичных знаков	Значение по умолчанию	Обязательное поле	Пустые строки
Код поставщика	Числовой	Длинное целое	-	-	-	Да	Да
Код материала	Числовой	Длинное целое	-	-	-	Да	Да
Дата поставки	Дата/время	-	Краткий формат даты	-	-	Да	-
Сумма поставки	Денежный	-	Фиксированный			Да	-

рис. 21. Структура таблицы «Приходная накладная»

рис. 22. Проектирование таблицы «приходная накладная» в режиме конструктора

В результате появиться макет входного документа «Приходная накладная» (см. рис. 23.)

рис. 23. Макет таблицы «приходная накладная»

- Закройте макет входного документа в режиме конструктора и откройте его в режиме таблицы.

- Заполните документ исходными данными, взяв их из таблицы на рис. 9.

3.2.Создайте форму таблицы «Справочник поставщиков» аналогичным образом.

- Вернитесь в меню создания таблиц.

- Выделите в объекте «Таблицы» команду «Создание таблицы в режиме конструктора» диалогового окна с именем файла «Сведения о поставке материалов».

- Осуществите описание полей таблицы в соответствии с таблицей, приведенной на рис. 24.

Имя поля	Тип данных	Размер поля	Формат поля	Число десятичных знаков	Значение по умолчанию	Обязательное поле	Пустые строки
Код поставщика	Числовой	Длинное целое	-	-	-	Да	Да
Наименование поставщика	Текстовый		-	-	-	Да	Да
Адрес поставщика	Текстовый		-	-	-	Да	Да
Расчетный счет	Текстовый		-	-	-	Да	Да

Рис. 24. Структура таблицы «Справочник поставщиков»

Заполните таблицу исходными данными в соответствии с таблицей, приведенной на рис. 10.

3.3. Создайте форму таблицы «Справочник материалов».

Осуществите описание полей таблиц в соответствии с таблицей, приведенной на рис. 25.

Имя поля	Тип данных	Размер поля	Формат поля	Число десятичных знаков	Значение по умолчанию	Обязательное поле	Пустые строки
Код материала	Числовой	Длинное целое	-	-	-	Да	Да
Наименование материала	Текстовый		-	-	-	Да	Да
Единица измерения	Текстовый		-	-	-	Да	Да

Рис. 25. структура таблицы «Справочник материалов».

Заполните таблицу исходными данными в соответствии с таблицей, приведенной на рис.11.

3.Организуйте взаимосвязи между таблицами «Приходная накладная», «Справочник материалов», «Справочник поставщиков».

Создайте связь между таблицами через меню «Схема данных» - «Связи».

Добавьте все таблицы в поле «Схема данных».

4.1.Перетащите левой клавишей мыши поле «Код поставщика» из таблицы «Приходная накладная» в таблицу «Справочник поставщиков» и совместите его с полем «Код поставщика». При появлении диалогового окна «Изменение связей» установите флажок «Обеспечение целостности данных».

Проверьте тип отношения «Один-ко-многим» и нажмите кнопку «Создать». Появиться линия связи по полю «Код поставщика» между таблицами «Приходная накладная» и «Справочник поставщиков».

4.2. Перетащите левой кнопки мыши поле «Код материала» из таблицы «Приходная накладная» в таблицу «Справочник материалов» и совместите его с полем «Код материала». При появлении диалогового окна «Изменение связей» установите флажок «Обеспечение целостности данных».

Проверьте тип отношения «Один-ко-многим» и нажмите кнопку «Создать». Появиться линия связи по полю «Код материала» между таблицами «Приходная накладная» и «Справочник материалов». В результате организованы взаимосвязи между таблицами «Приходная накладная», «Справочник материалов», «Справочник поставщиков». (см рис. 26.)

Рис. 26. Организация взаимосвязи между таблицами «Приходная накладная», «Справочник материалов», «Справочник поставщиков».

4.Сконструируйте запрос «Приходная накладная» с учетом справочников:

В окне создания базы данных «Сведения о поставке материалов» нажмите кнопку объекта «Запросы».

Выделите в объекте «Запросы» команду «Создание запроса в режиме конструктора»

Выберите по очереди все три таблицы в диалоговом окне «Добавление таблицы», и нажав кнопку «Добавить» добавьте их в запрос.

При этом в верхней части окна конструктора запроса появятся окна с полями таблиц, участвующих в запросе вместе с связями между ними. При этом, помимо установленных связей между полями автоматически формируются связи между счетчиками справочников. Подобное формирование приводит к искажению запроса. Для удаления дополнительной линии связи щелкните на ней правой кнопкой мыши: появиться контекстное меню. В контекстном меню нажмите «Удалить».

Осуществите последовательно выбор в каждом столбце нижней части запроса следующие поля: «Дата поставки», «Код поставщика», «Наименование поставщика», «Код материала», «Наименование материала», «Сумма поставки». В результате получиться запрос, который назовите «Приходная накладная с учетом данных справочников» (см. рис. 27).

Рис. 27. Макет формирования запроса «Приходная накладная с учетом данных справочников»

Просмотрите полученный запрос в режиме таблицы. Выделите наименование запроса «Приходная накладная с учетом данных справочников». Нажмите «Открыть» в меню диалогового окна. Появиться таблица «Приходная накладная с учетом данных справочников» (см. рис. 28)

Рис. 28. Приходная накладная с учетом данных справочников

5.Создайте отчет на основе получено запроса.

Вернитесь в диалоговое окно создания базы данных «Сведения о поставке материалов».

Нажмите кнопку объекта «Отчеты». Нажмите кнопку «Создать».

Выберите команду «Создание отчета с помощью мастера»

Выберите в качестве источника данных в диалоговом окне «Новый отчет» запрос «Приходная накладная с учетом данных справочников».

Выделите последовательно в окне «Доступные поля»: «Наименование поставщика», «Сумма поставки» и каждое выбранное поле перенесите в окно «Выбранные поля» с помощью кнопки «>».

Нажмите кнопку «Далее» на последующем шаге окна «Мастера отчетов».

В появившемся диалоговом окне «Выберите порядок сортировки и вычисления, выполняемые для записей», внизу окна нажмите кнопку «Итоги..»

В появившемся диалоговом окне «Итоги..» поставьте флажки: 1) «Sum – Сумма поставки»; 2) «только итоги».

При нажатии кнопки ОК происходит возврат к диалоговому окну «Создание отчетов» для выбора порядка сортировки и вычисления, выполняемых для записей.

Нажмите кнопку «Далее». В появившемся диалоговом окне «Создание отчетов» для выбора вида макета для отчета, выберите ступенчатый вид макета и книжную ориентацию, поставив соответствующие флажки. Нажмите кнопку «Далее».

В следующем диалоговом окне выберите деловой стиль отчета и нажмите кнопку «Далее».

В завершении создания отчета присвойте имя формы отчета «Фактическое выполнение поставок ФАКТРЕЗ» и нажмите кнопку «Готово».

Литература:

1. Информационные системы в экономике: Учеб. Пособие / Под ред. Проф. А.Н. Романова, проф. Б.Е. Одинцова – М.: Вузовский учебник, 2008.-411с.

2. Информационные системы в экономике: учебник для студентов вузов, обучающихся по экономическим специальностям и специальностям экономики и управления/ Под ред. Г.А. Титоренко. – 2-е изд.. перераб., и доп. – М.: ЮНИТИ-ДАНА, 2006.

3. Информационные технологии управления: Учеб. Пособие для вузов/ Под ред. Проф. Г.А. Титоренко. – 2-е изд., доп. – М.: ЮНИТИ-ДАНА, 2003.

4. Одинцов Б.Е. Обратные вычисления в формировании экономических решений. М.: Финансы и статистика, 2004.

5. Романов А.Н., Одинцов Б.Е. Советующие информационные сис­темы в экономике. М.: ЮНИТИ, 2000.

6. Романов А.Н., Одинцов Б.Е. Информационные системы в экономике. М.: Вузовский учебник, 2006.

7. Робинсон С. Microsoft Access 2000; учебный курс. СПб: Питер, 2000

8. Колесников Л. Excel 2000. К.: группа BHV, 1999.

9. Литвин Пол, Гетц Кен, Гилберт Майкл. Access 2000, К.: Изд. группа BHV, 2001.