Применение MS Excel для практических расчетов (информационно-аналитическая система магазина по продаже посуды)

             Информационно-аналитическая система  магазина посуды. Гиоргадзе М.Д.

ФЕДЕРАЛЬНОЕ АГЕНТСТВО  ПО ОБРАЗОВАНИЮ

РОССИЙСКАЯ  МЕЖДУНАРОДНАЯ АКАДЕМИЯ ТУРИЗМА

ВОЛЖСКО-КАМСКИЙ  ФИЛИАЛ

 

 

 

 

 

 

 

 

 

КУРСОВАЯ   РАБОТА

по дисциплине «Информатика»

на тему:

«Применение MS Excel для практических расчетов

(информационно-аналитическая  система магазина по продаже посуды)»

 

 

 

Выполнил:

Студентка  очного отделения  

Группа №23  Гиоргазде  М. Д.

 

Проверил:

д.т.н., проф. Розенцвайг А.К..

 

Оценка:____________

 

Дата:______________

 

 

 

 

 

 

г. Набережные Челны

2010 год

 

Содержание

 

Введение

Исходная информация содержится в двух таблицах:

1. «Цены» (№ п/п, код  товара, наименование фрукта, цена  за 1 шт) – 7 строк.

2. «Продажи» (№ п/п,  код товара, дата продажи, ФИО  продавца (не более 5-ти фамилий), наименование посуды (функция ВПР), количество проданной посуды (шт), общая стоимость посуды (функция ВПР))- 40 строк. Одним продавцом могут быть проданы разные видов фруктов.

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

Лист 1. Титульный лист

Открываем Лист 1 и даем ему название «Титульный лист».

Включаем панель Рисование с помощью команды  Вид/Панели инструментов/Рисование. С  помощью кнопки Добавить объект WordArt создаем заголовок работы («Информационно-аналитическая система магазина по продаже фруктов») и размещаем его в верхней части листа.

Включаем режим  привязки объектов к сетке с помощью  команды Действие/Привязать/Привязать  к сетке.

Для создания кнопок перехода к разным листам выбираем команду Вид/Панели инструментов/Формы и используем инструмент «кнопка». Создаются 13 кнопок с названиями: Выход, Таблица «Продажи», Таблица «Цены», Об авторе, Задание, Сортировка, Автофильтр, Расширенный фильтр, Итоги, Функции, Сводная таблица, Сводная диаграмма, Отчеты.

 

Добавляем фон  титульного листа (Формат/Лист/Подложка) и выбираем нужный рисунок. Для скрытия  сетки на листе используем команду  Сервис/Параметры/Вид и снимаем  флажок сетка.

Активизируем  лист «Продажи» и копируем его 6 раз  с помощью команды Правка/Переместить_Скопировать лист. Задаем параметры копирования: перед листом - (переместить в конец), устанавливаем галочку «Создавать копию» и нажимаем OK. Переименовываем созданные листы и даем им следующие названия: Сортировка, Автофильтр, Расширенный фильтр, Итоги, Функции, Отчеты.

Для добавления пустых строк перед таблицей выделяем 6 созданных листов, затем выделяем строки 1-9, выбираем команду Вставка/Строки, выделяем ячейку A1, переходим на лист «Продажи».

Добавляем в  рабочую книгу еще 2 новых листа (Вставка – Лист) и даем им названия:

Сводная таблица и Сводная диаграмма.

Выбираем команду  Вид/Панели инструментов/Visual Basic и щелкаем  на кнопке «редактор Visual Basic» или  вызываем редактор Visual Basic с помощью  сочетания клавиш Alt+F11.

Создаем первую пользовательскую форму: Insert/UserForm. На экране появится форма с набором инструментов:

В левой нижней части в окне свойств созданной  формы изменяем свойство Caption, вместо UserForm1 пишем «Задание».

Выбираем инструмент Label (надпись), добавляем надпись и  вносим все необходимые сведения.

Выбираем инструмент CommandButton (кнопка), задаем ей название «Закрыть», выделяем кнопку и в контекстном меню выбираем пункт «View code» вводим текст «UserForm1.Hide». В результате получим:

Private Sub CommandButton1_Click()

UserForm1.Hide

End Sub

Создаем вторую пользовательскую форму: Insert/UserForm. На экране появится форма с набором инструментов.

В левой нижней части в окне свойств созданной  формы изменяем свойство Caption, вместо UserForm2 пишем «Об авторе».

Выбираем инструмент Label (надпись), добавляем надпись и вносим все необходимые сведения.

Выбираем инструмент CommandButton (кнопка), задаем ей название «Закрыть», выделяем кнопку и в контекстном меню выбираем пункт «View code» вводим текст «UserForm1.Hide». В результате получим:

Private Sub CommandButton1_Click()

UserForm2.Hide

End Sub

 

Добавляем новый  модуль, в котором будут написаны все программы для работы с  титульным листом (Insert/Module) и называем его «№0_титул».

Добавляем первую процедуру с помощью команды Insert/Procedure и задаем следующие параметры:

Вводим текст: «UserForm1.Show». В результате получим:

Sub задание()

UserForm1.Show

End Sub

Добавляем вторую процедуру  с помощью команды Insert/Procedure и задаем следующие параметры:

Вводим текст: «UserForm2.Show». В результате получим:

Sub об_авторе()

UserForm2.Show

End Sub

 

Добавляем новую процедуру  и задаем ее название «лист_титульный_лист», вводим текст:

Sheets("Титульный лист").Select

Range("A1").Select

MsgBox ("Вы перешли на  титульный лист")

 

В результате получим:

Sub лист_титульный_лист()

Sheets("Титульный лист").Select

Range("A1").Select

MsgBox ("Вы перешли на  титульный лист")

End Sub

 

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

 

В результате получим:

 

Sub лист_продажи()

Sheets("Цены").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Цены")

End Sub

 

Sub лист_цены()

Sheets("Продажи").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Продажи")

End Sub

 

Sub лист_сортировка()

Sheets("сортировка").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Сортировка")

End Sub

 

Sub лист_автофильтр()

Sheets("автофильтр").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Автофильтр")

End Sub

 

Sub лист_расширенный_фильтр()

Sheets("расширенный  фильтр").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Расширенный фильтр")

End Sub

 

Sub лист_итоги()

Sheets("итоги").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Итоги")

End Sub

 

Sub лист_функции()

Sheets("функции").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Функции")

End Sub

 

Sub лист_сводная_таблица()

Sheets("сводная таблица").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Сводная таблица")

End Sub

 

Sub лист_сводная_диаграмма()

Sheets("сводная диаграмма").Select

MsgBox ("Вы перешли на лист Сводная диаграмма")

End Sub

 

Sub лист_отчеты()

Sheets("отчеты").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Отчеты")

End Sub

 

Добавляем процедуру  с названием: «выход», которая служит для завершения работы с системой. Код программы следующий:

Sub Выход()

Сообщение = "Вы действительно  хотите выйти из Excel?"

Заголовок = "До свидания!"

Кнопки = vbYesNo + vbQuestion

Результат = MsgBox(Сообщение, Кнопки, Заголовок)

If Результат = vbYes Then

  Application.Quit

  Else: MsgBox ("Выход не состоится")

End If

End Sub

 

Закрываем редактор Visul Basic и возвращаемся в EXCEL на Титульный лист. Для привязки к кнопкам соответствующих процедур выделяется нужная кнопка, в контекстном меню выбирается пункт «назначить макрос», из списка имеющихся подпрограмм выбирается нужная и нажимается OK. Эти действия повторяются для всех созданных на листе кнопок.

Листы 2-3. Исходные данные

Создать в MS Excel две таблицы на отдельных листах в соответствии с заданием.

Лист 2 переименовываем в «Цены», на нем расположена таблица, состоящая из 5 строк (данные не повторяются).

Таблица «Цены»

 

Лист 3 переименовываем в «Продажи» на нем расположена таблица, состоящая из 30 строк (данные должны повторяться).

Таблица «Продажи»

 

 

На листе  «Цены» ввести соответствующую информацию и отсортировать данные по столбцу «Наименование». Создаем списки на листе «Цены»: выделяем ячейки B2:B6, выполняем команду Вставка/Имя/Присвоить, вводим имя диапазона - «Код», выделяем ячейки C2:C6, выполняем команду Вставка/Имя/Присвоить, вводим имя диапазона - «Фрукты».

На листе  «Продажи» заполняем столбец  «№ п/п». Для задания параметров проверки вводимых значений используется команда Данные/Проверка. В ячейках L2:L4 создаем список фамилий продавцов фруктов (Антонова, Попова, Смирнова).

 

В столбце «Код товара» выделяем диапазон B2:B31 и задаем следующие параметры проверки значений:   Сообщение об ошибке: «Выбираются из списка».	В столбце «Дата  продажи» выделяем диапазон C2:C31 и задаем следующие параметры проверки значений:   Сообщение об ошибке: «Диапазон от 01.10.07 до 07.10.07»
В столбце «ФИО продавца» выделяем диапазон D2:D31 и задаем следующие параметры проверки значений:   Сообщение об ошибке: «Выбираются из списка».	В столбце «Количество» выделяем диапазон E2:E31 и задаем следующие параметры проверки значений: Сообщение об ошибке: «Диапазон от 10 до 50 кг»

 

Между таблицами  установить связь с помощью функции  ВПР. Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Буква «В» в имени функции ВПР означает «вертикальный».

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

Искомое_значение - это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

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

Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента «таблица»  должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР  может выдать неправильный результат. Если «интервальный_просмотр» имеет значение ЛОЖЬ, то «таблица» не обязана быть отсортированной.

Номер_столбца - это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее.

Интервальный_просмотр - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Заполним наименование и цены товаров, используя таблицу  Цены:

В ячейку E2 вводим формулу =ВПР(B2;Цены!$B$2:$D$6;2;ЛОЖЬ) и заполняем диапазон E2:E31.

В ячейку G2 вводим формулу =ВПР(B2;Цены!$B$2:$D$6;3;ЛОЖЬ)*F2 и заполняем диапазон G2:G31.

 

Для выполнения обработки исходных данных необходимо вставить в рабочую книгу еще 8 листов и скопировать таблицу с листа Продажи на листы 4, 5, 6, 7, 8, 11, поместив начало таблицы в ячейку А10. Первые строки оставить пустыми для размещения в них кнопок перехода на другие листы и прочую информацию!

Лист 4. «Сортировка»

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

Создаем на листе 6 кнопок, задаем им следующие названия: Титульный лист, Сортировка по фамилии  и наименованию по возрастанию, Сортировка по фамилии и наименованию по убыванию, Сортировка в особом порядке по возрастанию, Сортировка в особом порядке по убыванию, Отмена сортировки.