Проектирование информационной системы в MS EXCEL

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Лабораторная работа

Проектирование информационной системы в MS EXCEL

Введение

Цель работы:

? изучить технологию проектирования информационных систем;

? получить практические навыки по проектированию информационной системы в EXCEL.

Результат обучения. После обучения студент должен:

? знать основные возможности табличного процессора EXCEL и использовать их для решения экономических задач;

? уметь выполнить проектирования информационной системы в EXCEL для практической задачи.

Продолжительность занятия - 4 часа.

1. Общие положения

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

Однако на современном этапе становиться практически невозможным создание многофункциональных информационных систем в области экономики без участия самих пользователей. Несомненно, только будущие пользователи информационной системы могут точно и полностью сформулировать требования, которые будут предъявляться к ИС, только конечные пользователи могут оценить удобство разработанного интерфейса, только они могут выявить на ранних этапах проектирования возникающие ошибки и недостатки системы.

Таким образом, хотя выпускники специальностей «Бухгалтерский учет, анализ и аудит», «Финансы и кредит» в будущем не будут непосредственно заниматься разработкой информационных систем, они должны получить определенный опыт в этом направлении, чтобы при необходимости смогли принять участие в разработке ИС и помочь программистам.

Предлагаемая практическая работа позволяет познакомиться с практической стороной разработки информационной системы на базе табличного процессора Microsoft EXCEL. При разработке программного обеспечения для информационной системы используется термин «автоматизированная система», который мы и будем использовать в работе.

2. Условие задачи

С помощью программы Microsoft EXCEL разработать автоматизированную систему, обеспечивающую формирование первичных документов для бухгалтерского учета по счету 10 «Материалы» и автоматически подводящую итоги за месяц.

Известно, что в организации на конец года имелись определенные остатки (см. рис. 71). Организация получила 10 января и 20 января материалы в соответствии с приходными накладными №1 и №2 (см. рис. 75 и рис. 76). 15 января и 25 января организация израсходовала материалы в соответствии с расходными накладными №1 и №2 (см. рис. 77 и рис. 78).

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

3. Создание справочника материалов

1. Запустите программу Microsoft EXCEL.

2. Переименуйте первый рабочий лист с именем «Лист1» в «Справочник».

3. Создайте справочник следующего вида (Рис. 60).

Рис. 60. Справочник материалов

4. Для создания заголовка таблицы необходимо выделить ячейки первой строки А1:D1 и нажмите кнопку «Объединить и поместить в центре» .

5. Для создания заголовков столбцов с текстом, расположенным в несколько строк, необходимо выделить все ячейки заголовков и выполнить команду Формат - Ячейки.

6. В открывшемся окне открыть закладку Выравнивание и включить опцию «переносить по словам». После чего нажмите кнопку ОК.

7. Для задания двух значений после запятой в столбце «Цена, руб.» необходимо выделить все ячейки данного столбца и выполнить команду Формат - Ячейки.

8. В открывшемся окне открыть закладку Число и выбрать числовой формат - «Числовой», число десятичных знаков - 2. После чего нажмите кнопку ОК.

9. Сохраните документ под именем «Материалы за январь».

4. Создание ведомости остатков по счету 10 «Материалы»

1. Перейдите на второй лист в программе Microsoft EXCEL.

2. Переименуйте второй лист в «Остатки».

3. Создайте ведомость по образцу (Рис. 61).

Рис. 61. Ведомость остатков

4. Установите курсор в ячейку В3.

5. Выполните команду Вставка - Функция.

6. В открывшемся окне выберите категорию «Полный алфавитный перечень». Найдите функцию «Просмотр», выделите ее и нажмите ОК. При этом откроется окно мастера функций (Рис. 62):

Рис. 62. Вызов функции ПРОСМОТР

7. Оставьте выделенным первый пункт и нажмите ОК.

8. В новом окне для поля «Искомое значение» щелкните мышкой на ячейке А3, при этом данная ячейка отобразится в этом поле (Рис. 63).

Рис. 63. Поля функции ПРОСМОТР

9. Далее мышкой щелкните в поле «Просматриваемый вектор», чтобы там замигал курсор.

10. Теперь мышкой выберите лист «Справочник» и выделите все коды материалов, в результате у вас отобразится в окне следующая информация:

Рис. 64. Заполнение поля «Вектор_просмотра»

11. Мышкой щелкните в поле «Вектор результатов», чтобы там замигал курсор.

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

Рис. 65. Заполнение поля «Вектор результатов»

13. Нажмите кнопку ОК. У вас автоматически в ячейке В3 появиться название материала.

14. Установите курсор на ячейку В3.

15. Внесите в формулу изменения, которые заключаются в установки абсолютных адресов для неизменяемых ячеек. Для указания абсолютного адреса ячейки используется знак доллар - $. Т.е. формула должна иметь вид:

=ПРОСМОТР(A3;Справочник!$A$3:$A$12;Справочник!$B$3:$B$12)

16. Скопируйте формулу во все нижеследующие ячейки. В результате столбец автоматически заполниться наименованиями товаров (Рис. 66).

Рис. 66. Заполнение столбца «Наименование материала»

проектирование информационная система excel

17. Проверьте работу созданной программы. Для этого в ячейке А3 измените код материала на любой другой, например, на 10005 и нажмите Enter. При этом название материала изменится автоматически (Рис. 67).



Рис. 67. Проверка работы функции ПРОСМОТР

18. Отмените сделанные изменения и верните прежний код.

19. Установите курсор в ячейке С3.

20. Выполните команду Вставка - Функция и выберите функцию «Просмотр». Аналогично заполните поля функции следующим образом:

Рис. 68. Заполнение полей функции ПРОСМОТР

21. Установите курсор на ячейку С3.

22. Внесите в формулу изменения, которые заключаются в установки абсолютных адресов для неизменяемых ячеек. Т.е. формула должна иметь вид:

=ПРОСМОТР(A3;Справочник!$A$3:$A$12;Справочник!$C$3:$C$12)

23. Скопируйте формулу во все нижеследующие ячейки. В результате столбец автоматически заполниться единицами измерений.

24. Остатки введите в соответствии со следующей таблицей (Рис. 69).

Рис. 69. Заполненная ведомость остатков материалов

25. Установите курсор в ячейку Е3. Выполните команду Вставка - Функция и выберите функцию «Просмотр». Аналогично заполните поля функции следующим образом:

Рис. 70. Заполнение полей функции для столбца «Цена, руб.»

26. Внесите в формулу изменения, которые заключаются в установки абсолютных адресов для неизменяемых ячеек. Т.е. формула должна иметь вид:

=ПРОСМОТР(A3;Справочник!$A$3:$A$12;Справочник!$D$3:$D$12)

27. Скопируйте формулу во все нижеследующие ячейки. В результате столбец автоматически заполниться значениями цен (Рис. 71).

Рис. 71. Заполнение данными столбца «Цена, руб.»

28. Далее найдите сумму остатков, умножив цену на количество по каждому виду материала.

29. Найдите итоговые значения по образцу (Рис. 72).

Рис. 72. Готовая ведомость остатков

5. Создание приходных накладных

1. Перейдите на третий лист в программе Microsoft EXCEL.

2. Переименуйте третий лист в «Приход 1».

3. Создайте документ следующего вида:

Рис. 73. Подготовка бланка приходной накладной

4. Для ввода значения даты выполните следующие действия. Введите в ячейку В12 значение 10.01.2006.

5. Установите курсор на ячейку В10.

6. Выполните команду Формат - Ячейки.

7. В открывшемся окне выберите закладку «Число».

8. В столбце числовых форматов выберите Дата, в столбце типов выберите «14 марта 2001 г.».

9. Нажмите кнопку ОК.

10. Используя функцию «Просмотр», заполните столбцы «Наименование материала», «Единица измерения», «Цена» значениями в соответствии с кодом материала (Рис. 74).



Рис. 74. Ввод данных с помощью функции ПРОСМОТР

11. Заполните столбец «Количество» по образцу и введите формулы для расчета значений «Сумма» и «Итого» (Рис. 75).

Рис. 75. Расчет значений в приходной накладной №1

12. Вставьте новый лист. Для этого выполните команду Вставка - Лист.

13. Переименуйте новый лист в «Приход 2». Скопируйте на этот лист таблицу из Приходной накладной №1. Для этого выполните следующие действия.

14. Перейдите на лист «Приход 1».

15. Выделите имеющуюся таблицу.

16. Выполните команду Правка - Копировать.

17. Перейдите на лист «Приход 2».

18. Выполните команду Правка - Вставить.

19. Исправьте заголовок, коды и количество согласно образцу (наименование, единица измерения, цена и сумма будут введены автоматически):

Рис. 76. Приходная накладная №2

6. Создание расходных накладных

1. Вставьте новый лист. Для этого выполните команду Вставка - Лист.

2. Переименуйте новый лист в «Расход 1».

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

Рис. 77. Расходная накладная №1

4. Вставьте новый лист. Для этого выполните команду Вставка - Лист.

5. Переименуйте новый лист в «Расход 2».

6. Скопируйте таблицу из Расходной накладной №1 и измените ее согласно образцу:

Рис. 78. Расходная накладная №2

7. Создание журнала оборотов

1. Вставьте новый лист. Для этого выполните команду Вставка - Лист.

2. Переименуйте новый лист в «Журнал оборотов».

3. Используя функцию «Просмотр» для столбца «Наименование материала», создайте документ по образцу (Рис. 79).

Рис. 79. Бланк журнала оборотов

4. Заполним эту таблицу. Значения «Входящее сальдо…» это данные из таблицы «Остатки». Для их ввода проделайте следующее.

5. Установите курсор в ячейке С3. Выполните команду Вставка - Функция и выберите функцию «Просмотр». Заполните поля функции следующим образом, ссылаясь на таблицу «Остатки» (Рис. 80).

Рис. 80. Аргументы функции ПРОСМОТР для столбца «Входящее сальдо…»

6. Внесите в формулу изменения, которые заключаются в установки абсолютных адресов для неизменяемых ячеек. Т.е. формула должна иметь вид:

=ПРОСМОТР(A3;Остатки!$A$3:$A$12;Остатки!$F$3:$F$12)

7. Скопируйте формулу во все нижеследующие ячейки. В результате столбец автоматически заполниться остатками по каждому материалу.

8. Значения «Обороты по дебету» это данные из таблиц «Приход 1» и «Приход 2». Для их ввода проделайте следующее.

9. Установите курсор в ячейку D3.

10. Выполните команду Вставка - Функция.

11. В открывшемся окне выберите категорию «Полный алфавитный перечень». Найдите функцию «СУММЕСЛИ», выделите ее и нажмите ОК. При этом откроется окно для ввода параметров функции.

12. Заполните поля функции согласно образцу (Рис. 81).

Рис. 81. Аргументы функции СУММЕСЛИ для столбца «Обороты по дебету»

13. К полученной формуле необходимо добавить данные из приходной накладной №2. Для этого к полученной формуле добавьте знак «плюс» и вызовите функцию СУММЕСЛИ.

14. Заполните поля функции следующим образом (Рис. 82):

Рис. 82. Аргументы второй функции СУММЕСЛИ для столбца «Обороты по дебету»

15. В результате общая функция будет иметь следующий вид:

=СУММЕСЛИ('Приход 1'!A3:A9;A3;'Приход 1'!F3:F9) +СУММЕСЛИ('Приход 2'!A3:A9;A3;'Приход 2'!F3:F9)

16. Внесите в формулу изменения, которые заключаются в установки абсолютных адресов для неизменяемых ячеек. Т.е. формула должна иметь вид:

=СУММЕСЛИ('Приход 1'!$A$3:$A$9;A3;'Приход 1'!$F$3:$F$9) +СУММЕСЛИ('Приход 2'!$A$3:$A$9;A3;'Приход 2'!$F$3:$F$9)

17. Скопируйте формулу во все нижеследующие ячейки. В результате столбец автоматически заполниться данными по каждому материалу.

18. Установите курсор в ячейку E3.

19. Вызовите функцию «СУММЕСЛИ».

20. Заполните поля функции согласно образцу (Рис. 83):

Рис. 83. Аргументы функции СУММЕСЛИ для столбца «Обороты по кредиту»

21. К полученной формуле необходимо добавить данные из расходной накладной №2. Для этого к полученной формуле добавьте знак «плюс» и вызовите функцию СУММЕСЛИ.

22. Заполните поля функции следующим образом:



Рис. 84. Аргументы второй функции СУММЕСЛИ для столбца «Обороты по кредиту»

23. В результате общая функция будет иметь следующий вид:

=СУММЕСЛИ('Расход 1'!A3:A9;A3;'Расход 1'!F3:F9) +СУММЕСЛИ('Расход 2'!A3:A9;A3;'Расход 2'!F3:F9)

24. Внесите в формулу изменения, которые заключаются в установки абсолютных адресов для неизменяемых ячеек. Т.е. формула должна иметь вид:

=СУММЕСЛИ('Расход 1'!$A$3:$A$9;A3;'Расход 1'!$F$3:$F$9) +СУММЕСЛИ('Расход 2'!$A$3:$A$9;A3;'Расход 2'!$F$3:$F$9)

25. Скопируйте формулу во все нижеследующие ячейки. В результате столбец автоматически заполниться данными по каждому материалу.

26. Значения «Исходящее сальдо» определяется как:

Входящее сальдо + Обороты по дебету - Обороты по кредиту

Для расчета исходящего сальдо установите курсор в ячейку F3 и введите формулу =C3+D3-E3

27. Скопируйте полученную формулу в остальные ячейки. В результате у вас должна получиться следующая таблица (Рис. 85).



Рис. 85. Журнал оборотов

28. На этом автоматизированная система для ведения учета по счету 10 «Материалы» создана.

29. Сохраните еще раз свои документы и закройте программу.

8. Работа с автоматизированной системой

1. Создайте копию файла «Материалы за январь». Для этого на рабочем столе откройте папку Мои документы.

2. Выделите файл «Материалы за январь».

3. Выполните команду Правка - Копировать, а затем команду Правка - Вставить.

4. Переименуйте файл в «Материалы за февраль».

5. Откройте файл в «Материалы за февраль».

6. Исправьте ведомость остатков согласно образцу, приведенному на Рис. 86 (учтите, что новые остатки - это данные столбца «Исходящее сальдо…» журнала оборотов):



Рис. 86. Исправленная ведомость остатков

7. Исправьте первую приходную накладную согласно образцу, представленному на Рис. 87 (обратите внимание, что при изменении кода автоматически изменяются значения столбцов):

Рис. 87. Исправленная приходная накладная №1

8. Исправьте вторую приходную накладную согласно образцу (Рис. 88).



Рис. 88. Исправленная приходная накладная №2

9. Исправьте первую расходную накладную (Рис. 89):

Рис. 89. Исправленная расходная накладная №1

10. Исправьте вторую расходную накладную (Рис. 90).

Рис. 90. Исправленная расходная накладная №2

11. Откройте журнал оборотов. Все значения в нем будут автоматически заполнены (Рис. 91):

Рис. 91. Исправленный журнал оборотов

12. Таким образом, на заполнение первичных документов и получение журнала оборота по счету мы затратили значительно меньше времени.

13. Покажите результат работы преподавателю.

9. Итог работы

Выполнив предложенную лабораторную работу, а также выполнив работу по постановке задачи на проектирование ИС, вы практически создали небольшую автоматизированную систему на базе табличного процессора Microsoft EXCEL, которая позволяет автоматизировать учет материалов и выполняет следующие функции:

? формирование приходных накладных;

? формирование расходных накладных;

? формирование ведомости остатков;

? формирование журнала оборотов.

Конечно, данная система является прообразом настоящих информационных систем, но она позволяет познакомиться с процессом создания информационных систем.

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

10. Самостоятельная работа

Самостоятельно разработайте автоматизированную систему для любого другого счета бухгалтерского учета. В качестве счетов рекомендуется использовать следующие счета бухгалтерского учета:

01 - Основные средства;

40 - Выпуск продукции;

41 - Товары;

43 - Готовая продукция;

44 - Расходы на продажу;

45 - Товары отгруженные;

50 - Касса;

51 - Расчетный счет;

60 - Расчеты с поставщиками;

62 - Расчеты с покупателями;

90 - Продажи;

91 - Прочие доходы и расходы.

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

Размещено на Allbest.ru

...