Решение расчетной экономической задачи

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

СОДЕРЖАНИЕ

Введение

Постановка расчетной экономической задачи

Решение расчетной экономической задачи в среде MS Excel

Решение расчетной экономической задачи в среде MS Access

Результаты компьютерных экспериментов и их анализ

Заключение

Список использованной литературы



ВВЕДЕНИЕ

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

Цель данной работы -- приобретение практических навыков использования базовых возможностей MS Excel 2010 и MS Access 2010 для решения финансово_экономических задач.

Объектом изучения является конкретная экономическая задача. Нужно будет рассчитать для ООО «Холод» сумму, полученную от продаж за день каждой модели кондиционера, и итоговую сумму продаж. Результаты нужно будет представить в графическом виде и провести анализ результатов. Для решения этой экономической задачи будут использоваться возможности MS Excel и MS Access.



ПОСТАНОВКА РАСЧЕТНОЙ ЭКОНОМИЧЕСКОЙ ЗАДАЧИ

Постановка задачи

Условно-постоянной информацией (справочной) будут служить следующие реквизиты: артикул, модель и цена кондиционеров, номер продаж и количество проданных кондиционеров каждого отдельного вида за день. Будет создаваться сводная таблица с расчетами продаж за день, затем это будет проанализировано и представлено в графическом виде.

Цель решения задачи

Рассмотрим такую ситуацию. Фирма ООО «Холод» занимается продажей кондиционеров. В течение дня было продано некоторое количество кондиционеров определенных марок. Цель решения данной задачи состоит в необходимости рассчитать сумму, полученную от продаж за день по каждой модели кондиционера, и итоговую сумму продаж. Результаты нужно представить в графическом виде для проведения анализа.

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

Фирма ООО «Холод» занимается продажей кондиционеров. Номенклатура товаров указана на рис.1. В течение дня было продано некоторое количество кондиционеров определенных марок (рис.2).

Для решения задачи необходимо следующее:

1. Построить таблицы, указанные на рис.1 и рис.2.

2. Сформировать таблицу «Продажи за день».

3. Организовать межтабличные связи с использованием функции ВПР для автоматической подстановки данных из исходных таблиц с рис.1 и рис.2 в рис.3.

4. Рассчитать сумму, полученную от продаж за день по каждой модели кондиционера, и итоговую сумму продаж.

5. Результаты представить в графическом виде и провести анализ.

Артикул	Модель кондиционера	Цена, руб.
500	LG G07SK	16 000
601	Samsung AQ09TSB	20 000
702	Dantex RK-18SEG	22 000
803	General Climate GC-S36HR	36 000
904	Hitachi RAS-14AH1	24 000

Рис.1. Номенклатура товаров

Номер продажи	Артикул	Продано, шт.
1	500	5
2	601	4
3	702	2
4	803	8
5	904	1

Рис.2. Количество продаж

Артикул	Модель кондиционера	Цена, руб.	Продано, шт.	Сумма, руб.
500
601
702
803
904
Итого

Рис.3. Продажи за день

Входной оперативной информацией служит номенклатура товаров, в которой содержатся: артикул, модель кондиционера, цена (руб.). На основании документа «Номенклатура товаров» создается следующая экранная форма:

Номенклатура товаров

Артикул	Модель кондиционера	Цена, руб.
		Рi

где Рi - цена определенного вида кондиционера. Для упрощения описания входной информации ряд реквизитов, которые не используются для решения данной задачи (артикул, модель кондиционера), в документ не входят. Справочник номенклатуры товаров служит для расшифровки кодов кондиционеров. Документ «Количество продаж» содержит информацию для определения количества проданных кондиционеров.

Количество продаж

Номер продажи	Артикул	Продано, шт.
	i	Qi

где Qi - количество проданных кондиционеров i-го вида за день.

В результате следует получить ведомость «Продажи за день» со следующими реквизитами: артикул, модель кондиционера, цена (руб.), продано (шт.), сумма (руб.). Информация будет представлена в следующем документе:

Структура результирующего документа «Продажи за день»

Артикул	Модель кондиционера	Цена, руб.	Продано, шт.	Сумма, руб.
i	……..	Pi	Qi	Сi
Итого	С

где Сi - сумма за проданные кондиционеры определенного артикула, а С - общая сумма.

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

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

Компьютерная модель решения задачи

1. Информационная модель решения задачи (рис.4)

1. 2.

3. 4.

3.

Рис. 4. Информационная модель взаимосвязи исходных и результирующих данных

1 - документ (Номенклатура товаров), 2 - документ (Количество продаж), 3 - результирующий документ (Продажи за день), 4 - гистограмма.

2. Аналитическая модель решения задачи

Для получения документа «Продажи за день» необходимо рассчитать:

- сумму, вырученную за день за продажу каждой модели кондиционера;

- общую сумму, вырученную за продажу всех кондиционеров;

Расчеты выполняются по следующим формулам:

Ci= Рi* Qi , C =Уii C ,

где Ci - сумма, вырученная за продажу за день одного i-го вида кондиционеров; C - общая (по всем видам кондиционеров) вырученная сумма за день.



РЕШЕНИЕ РАСЧЕТНОЙ ЭКОНОМИЧЕСКОЙ ЗАДАЧИ В СРЕДЕ MS EXCEL

Технология решения задачи средствами MS Excel

1. Вызовем Excel:

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

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

- в меню Microsoft Office выберем MS Excel.

2. Переименуем «Лист 1» в «Номенклатура товаров»:

- установим курсор мыши на ярлык «Лист 1» (нижняя часть экрана) и нажмем правую кнопку мыши;

- выберем в контекстном меню команду «Переименовать» и нажмем левую кнопку мыши;

- наберем на клавиатуре «Номенклатура товаров»;

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

3. Введем заголовок таблицы «Номенклатура товаров»:

- сделаем ячейку A1 активной (установим курсор мыши на пересечение столбца A и строки 1 и нажмем левую кнопку мыши);

- наберем на клавиатуре «Номенклатура товаров»;

- нажмем кнопку с изображение «галочки» в строке формул.

4. Отформатируем заголовок:

- выделим ячейки A1чС1 (рис. 5)

Рис. 5. Пример выделения группы ячеек

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

5. Отформатируем ячейки A2чC2 под ввод длинных заголовков:

- выделим ячейки A2чC2;

- выполним команду «Выравнивание» в разделе «Формат ячеек» меню «Главная» на панели инструментов;

- выберем закладку «Выравнивание», в группе опций «Отображение» установим флажок опции «переносить по словам», нажмем «OK»;

6. Введем в ячейки A2чD2 информацию, представленную на рис.6.

Рис.6. Имена полей таблицы «Номенклатура товаров»

7. Организуем контроль данных, вводимых в колонку «Артикул»:

- выделите ячейки А3чА7, выполним «Проверку данных» в «Данные» (рис,7);

Рис.7. Задание контроля данных

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

- выберем «Целое число»;

- задайте в поле «Минимум»: 500;

- задайте в поле «Максимум»: 904 (рис. 8).

Рис.8. Задание диапазона ячейки

8. Отформатируем ячейки В3чВ7 для ввода текстовых символов:

- выделим ячейки В3чВ7;

- на панели инструментов в меню «Главная» выберем «Ячейки», где в пункте «Формат» выполним команду «Формат ячеек»;

- выберем закладку «Число»;

- выберем формат «Текстовый»;

- нажмем кнопку «OK».

9. Введем информацию, приведенную на рис.1. Вид полученной таблицы представлен на рис.9.



Рис.9. Вид полученной таблицы

10. Присвоим имя группе ячеек:

- выделим ячейки A3чС7;

- выберем команду «Присвоить имя» в разделе «Определенные имена» меню «Формулы» (рис. 10);

Рис. 10. Вид окна «Создание имени»

- нажмем кнопку «OK».

11. Переименуем «Лист 2» в «Количество продаж» (аналогично действиям пункта 2).

12. Создадим таблицу «Количество продаж» (аналогично действиям пунктов 3 - 5).

13. Заполним заголовки таблицы согласно рис.2.

14. Введем колонку «Артикул» список:

- сделаем ячейку В3 активной;

- в меню «Данные» выберем команду «Проверка данных», в поле «Тип данных» которой выберем «Список»;

- введем значение в поле «Источник», выделив диапазон A3чA7 в «Номенклатуре товаров»;

- нажмем кнопку «OK» (Рис.11).

Рис.11. Ввод списка в ячейку

В «Количестве продаж» теперь будем иметь возможность выбирать из предложенного списка артикул. Нажмем кнопку «галочка» рядом с ячейкой B3 и из предложенного списка выберем артикул - 500. Ячейка A3 - «Номер продажи» будет заполнена автоматически (рис.12).

Рис.12. Ввод списка в ячейку

- «протянем» формулу до ячейки В7, поочередно введем артикулы.

После заполнения колонки «Продано, шт», таблица получила вид, представленный на рис.13.



Рис. 13. Вид таблицы «Количество продаж»

15. Аналогично п.10 присвоим имя группе ячеек A3чC7:

16.Создадим на листе 3 таблицу «Продажи за день»:

- введем заголовок, отформатируем ячейки для оглавления колонок с переносом по словам, внесем названия колонок;

- организуем выбор из списка в А3чА7, протянем формулу, введем значения.

При вводе моделей кондиционеров, отформатируем ячейки под текст, выбрав Перенос по словам. Встанем курсором в ячейку В3 и воспользуемся функцией ВПР:

- воспользуемся командой «Вставить функцию» меню «Формулы»;

- в поле «Категория» выберем «Ссылки и массивы»;

- в поле «Выберите функцию» нажмем «ВПР» (рис. 14);

Рис. 14. Вид первого окна мастера функций

- нажмите кнопку «OK»;

- введем наименование поставщика в поле «Искомое_значение», щелкнув по ячейке А3;

- введем информацию в поле «Таблица»;

- воспользуемся командой «Использовать в формуле» меню «Формулы», выбрав «Вставить имена»;

- выделим «Имя» «Номенклатура товаров» ;

- введем информацию - цифру 2 в поле «Номер_столбца»;

- введем информацию - цифру 0 в поле «Интервальный_просмотр» (рис. 15);

Рис. 15. Вид второго окна мастера функций

- нажмем кнопку «OK».

Установим курсор на маркер в правом нижнем углу ячейки В3, щелкнем левой клавишей мыши и протянем его до ячейки В7.

Аналогично вводим информацию в колонку «Цена», установив цифру 3 в поле «Номер_столбца», предварительно отформатировав ячейки под значение «Денежный».

В колонке «Продано» так же используем функцию ВПР.

Отформатируем Е3чЕ7 под значение «Денежный». В колонке «Сумма» в ячейку Е3 введем формулу: « =D3*C3», нажмем Enter.

- «протянем» формулу до ячейки Е7.

- объедим ячейки А8чD8, внесем слово «Итого», выберем смещение к левому краю разделе «Выравнивание» в закладке «Главная».

Посчитаем общую сумму проданных кондиционеров:

- встанем на ячейку Е8, нажмем левую клавишу мыши и, не отпуская, протянем до ячейки Е3;

- нажмем значок «Сумма» в разделе «Редактирование» в закладке «Главная». Таблица имеет вид, изображенный на рис.16.

Рис. 16. Результат заполнения таблицы «Продажи за день»

Технология построения гистограммы средствами MS Excel

Представим наглядно результаты расчетов, создав гистограмму по данным результирующей таблицы:

- сделаем активным лист «Продажи за день»;

- выделим диапазон Е3чЕ7;

- выберем команду «Гистограмма» в разделе «Диаграммы» меню «Вставка»;

- в разделе «Объемная гистограмма» выберем «Объемная гистограмма с группировкой»;

- переименуйте гистограмму в «Продажи за день»;

- зададим осям значения: Наименование кондиционера и Сумма (рис.17).



Рис.17. Гистограмма «Продажи за день»

РЕШЕНИЕ РАСЧЕТНОЙ ЭКОНОМИЧЕСКОЙ ЗАДАЧИ В СРЕДЕ MS ACCESS

экономический задача excel access

1. Вызовем Microsoft Access 2010:

- нажмем кнопку Пуск;

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

- во вкладке Microsoft Office выберем Microsoft Access 2010;

- нажмем Enter.

Появится диалоговое окно Доступные шаблоны.

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

- в поле Имя файла присвоим файлу имя ПРОДАЖИ КОНДИЦИОНЕРОВ;

- нажмем кнопку Создать.

3. Создадим формы таблиц Номенклатура товаров , Количество продаж и Продажи за день .

3.1. Создадим форму таблицы Номенклатура товаров :

- на ленте активизируем вкладку Главная, нажмем кнопку Режим, активизируем Конструктор;

- в появившемся окне зададим имя Номенклатура товаров, нажмем ОК;

- появится макет для описания полей и типа данных таблицы (рис.18);

Рис.18. Панель инструментов конструктора таблиц

3.1.1. Осуществим описание полей и типа данных таблицы, в соответствии с рис.19 .

Имя поля	Тип данных	Размер поля	Формат поля	Число десятичных знаков	Обязательное поле	Индексиро-ванное поле
Артикул	Текстовый	3	-	-	Да	Да (Совпадения не допускаются)
Модель конди-ционера	Текстовый	50	-	-	Да	Да (Совпадения не допускаются)
Цена, руб	Денежный	-	Фиксированный	2	Да	Да (Совпадения не допускаются)

Рис.19. Задание полей таблицы «Номенклатура товаров»

Таблица имеет вид (рис.20):



Рис.20. Описание структуры таблицы

- нажмем в Макете кнопку Закрыть, в диалоговом окне на вопрос «Сохранить изменения макета или структуры таблицы Номенклатура товаров?» нажмем Да.

В объекте Таблицы появится название таблицы.

3.2.Создадим таблицу «Количество продаж»

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

- активизируем закладку Главная, нажмем на стрелочке кнопки Режим, активизируем пиктограмму Конструктор;

- в появившемся окне зададим имя «Количество продаж», нажмем ОК;

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

Имя поля	Тип данных	Размер поля	Формат поля	Число десятичных знаков	Обязательное поле	Индексиро-ванное поле
Номер продажи	Числовой	целое	-	-	Да	Да (Совпадения не допускаются)
Артикул	Текстовый	3	-	-	Да	Да (Совпадения не допускаются)
Продано, шт	Числовой	Целое	-	-	Да	Да (Допускаются совпадения)

Рис.21. Задание полей таблицы «Количество продаж»

Полученную таблицу закроем, сохранив изменения.

В объекте Таблицы появилось название Количество продаж.

3.3.Создадим таблицу «Продажи кондиционеров за день»:

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

- активизируем закладку Главная, нажмем на стрелочке кнопки Режим, активизируем пиктограмму Конструктор;

- в появившемся диалоговом окне зададим имя «Продажи кондиционеров за день», нажмем ОК;

Осуществим описание полей и типа данных таблицы сгласно рис.22.

Имя поля	Тип данных	Размер поля	Формат поля	Число десятичных знаков	Обязательное поле	Индексиро-ванное поле
Артикул	Текстовый	3	-	-	Да	Да (Совпадения не допускаются)
Модель конди-ционера	Текстовый	50	-	-	Да	Да (Совпадения не допускаются)
Цена, руб	Денежный	-	Фиксированный	2	Да	Да (Совпадения не допускаются)
Продано, шт	Числовой	Целое	-	-	Да	Да (Допускаются совпадения)
Сумма, руб	Денежный	-	Фиксированный	2	Да	Да (Допускаются совпадения)

Рис.22. Задание полей таблицы «Продажи кондиционеров за день»

Введем входные данные в в таблицы «Номенклатура товаров», «Количество продаж», «Продажи кондиционеров за день» (рис.23).



Рис.23. Вод данных в таблицу

4. Организуем взаимосвязи между таблицами Номенклатура товаров и Количество продаж.

- активизируем в ленте вкладку Работа с базами данных;

- в группе Отношения найдем команду Схема данных, нажмем Отобразить таблицу;

- выделим по очереди наименования таблиц Номенклатура товаров, Количество продаж и Продажи кондиционеров за день (после каждого выделения нажмем кнопку Добавить), затем закроем диалоговое окно;

- расположим таблицы удобно для формирования связей;

- перетащим левой клавишей мыши поле Артикул из Количества продаж в Продажи кондиционеров за день;

- в появившемся окне поставим галочку на Обеспечении целостности данных, нажмем кнопку Создать;

- перетащим левой клавишей мыши поле Цена из Номенклатуры товаров в Продажи кондиционеров за день;

- в появившемся окне поставим галочку Обеспечение целостности данных, нажмем кнопку Создать;

Процесс и результат организации взаимосвязей между таблицами изображены на рис.24 и рис.25.

Рис.24. Создание схемы данных Рис.25. Результат создания схемы

- закроем, сохранив изменения.

5. Сконструируем запрос «Продажи кондиционеров»:

- в ленте активизируем закладку Создание и в группе Запросы нажмем кнопку конструктор запросов;

- в появившемся диалоговом окне выберем по очереди каждое наименование таблицы, нажимая кнопку Добавить, затем закроем диалоговое окно;

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

Поля, которые должны быть выведены на экран в последствии все должны быть отмечены «галочками» (рис.26)



Рис.26. Макет сформированного запроса

6.Постороим сводную диаграмму:

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

- из открывшегося списка полей диаграммы перетянем названия полей на оси Сумма и Модель кондиционера;

Диаграмма имеет вид:

Рис.27. Наглядный вид продаж кондиционеров за день

7.Формирование отчета.

Создадим отчет «Продажи кондиционеров за день»:

- на ленте активизируем закладку Создание, выполним команду Мастер отчетов, нажав соответствующую кнопку;

- появится диалоговое окно Создание отчетов;

- в окне Таблицы и запросы выберем запрос «Продажи за день»;

- выделим в окне поля «Артикул» и «Цена»;

- нажмем кнопку Далее, отразится вид представленных данных в отчете, еще раз нажмем Далее;

- в появившемся окне Создание отчетов нажмем кнопку Итоги;

- поставим флажки: Sum и Только итоги, нажмем ОК, Далее (рис.28);

- выберем ступенчатый вид макета и книжную ориентацию, нажмем Далее;

Рис.28. Диалоговое окно Создания отчетов

- присвоим имя форме «Продажи кондиционеров за день», флажок должен указывать действие Просмотреть отчет;

- нажмем кнопку Готово, появится окно с отражением отчета (рис.29).



Рис.29. Отчет «Продажи кондиционеров за день»

- нажимаем кнопку Закрыть , в объекте Отчеты появляется отчет «Продажи кондиционеров за день».

РЕЗУЛЬТАТЫ КОМПЬЮТЕРНЫХ ЭКСПЕРЕМЕНТОВ И ИХ АНАЛИЗ

Результаты компьютерного эксперимента

Для тестирования правильности решения задачи рассчитаем результаты:

1. 16 000 * 5 = 80 000 (руб.) получено за продажу кондиционеров LG G07SK;

2. 20 000 * 4 = 80 000 (руб.) получено за продажу кондиционеров Samsung AQ09TSB;

3. 22 000 * 2 = 44 000 (руб.) получено за продажу кондиционеров Dantex RK-18SEG;

4. 36 000 * 8 = 288 000 (руб.) получено за продажу кондиционеров General Climate GC-S36HR;

5. 24 000 * 1 = 24 000 (руб.) получено за продажу кондиционеров Hitachi RAS-14AH1;

6. 80 000 + 80 000 + 44 000 + 288 000 + 24 000 = 516 000 (руб.) получено за продажу всех кондиционеров.

Результаты расчетов совпадают с результатами таблиц.

Анализ данных таблицы

Представление в графическом виде позволяет наглядно представить, который из видов кондиционеров имеет больший удельный вес среди продаж. В данной задаче кондиционер General Climate GC-S36HR дал наибольшую сумму продаж. К тому же возможности MS Excel 2010 позволяют с помощью фильтрации изменять данные, на основе которых построена диаграмма, непосредственно в области ее построения. Например, нажатием клавиши галочка в области построения диаграммы «Продажи за день» можно осуществить выбор вида материала, по которому требуется наглядное представление информации.



ЗАКЛЮЧЕНИЕ

В результате проделанной работы получен навык создания баз данных в среде MS Excel и MS Access.

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

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

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



СПИСОК ЛИТЕРАТУРЫ

1. Компьютерная обучающая программа по дисциплине «Информатика» / А.Н. Романов, В.В Дудихин, Г.Б.Коняшина, Г.А. Жуков, О.Е. Кричевская, Г.Е. Красникова, Д.А.Савин. - М.: ВЗФЭИ, 2010. - URL: http://repository.vzfei.ru. Доступ по логину и паролю

2. Информатика. Методические указания по выполнению курсовой работы для самостоятельной работы студентов первого курса, направления 080100.62 «Экономика» и 080200.62 «Менеджмент», квалификация (степень) бакалавр. - М.: ВЗФЭИ, 2011.

3. Информатика: Учеб. Пособие / Под ред. Б.Е. Одинцова, А.Н. Романова. - 2-е изд., перераб. И доп. - М.: Вузовский учебник: ИНФРА -М, 2012. - 410с.

4. Создание базы данных "Поликлиника" в MS Office Access 2007, опубликовано 01.07.2012,[Электронный ресурс] http://www.youtube.com/watch?v=6SRq_tDyaUo .

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