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

Автор работы: Пользователь скрыл имя, 23 Января 2013 в 15:32, курсовая работа

Краткое описание

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

Содержание

Введение 3
Лист 1. Титульный лист 4
Листы 2-3. Исходные данные 9
Лист 4. «Сортировка» 12
4.1. Сортировка по столбцам «ФИО продавца» и «Наименование» 13
4.2. Сортировка в особом порядке по полю «Наименование» 15
4.3. Отмена сортировки 19
Лист 5. «Автофильтр» 20
5.1. Использование автофильтра 21
Лист 6. «Расширенный фильтр» 23
6.1. Фильтрация по наименованию 23
6.2. Фильтрация по ФИО двух продавцов 25
6.3. Фильтрация по диапазону количество 26
6.4. Фильтрация по дате и фамилии 27
6.5. Отмена фильтрации 29
Лист 7. «Итоги» 32
7.1. Итоги по ФИО продавца 32
7.2 . Итоги по наименованию фруктов 33
7.3. Отмена итогов 35
Лист 8. «Функции» 37
8.1. Функция ДМАКС 37
8.2 Функция ДМИН 38
8.3. Функция ДСРЗНАЧ 39
8.4. Функция БДСУММ 40
8.5. Функция БСЧЕТ 41
8.6. Отмена функций 42
Лист 9. «Сводная таблица» 44
Лист 10. «Сводная диаграмма» 46
Лист 11. «Отчеты» 47

Скачать полностью (1.74 Мб) Сколько стоит заказать работу?

Прикрепленные файлы: 1 файл

курсовая информ.doc

— 5.97 Мб (Скачать документ)

6.2. Фильтрация по ФИО двух  продавцов

Для фильтрации по ФИО двух продавцов выполняются  следующие действия:

  1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_фио:

  1. Установить курсор в ячейку D2 и ввести с клавиатуры одну фамилию, в ячейку D3 ввести с клавиатуры другую фамилию из тех, которые есть в таблице, например Антонова и Попова.
  2. Установить курсор в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон – ячейки A10:G40 (вся исходная таблица) и диапазон условий – ячейки А1:H3

  1. Нажать ОК и выделить ячейку A1.
  2. Выбрать команду Сервис/Макрос/Остановить запись
  3. Для внесения изменений в текст макроса нужно выбрать команду Сервис/Макрос/Макросы, в появившемся окне выделить макрос фильтр_фио и нажать кнопку «Войти». В результате перехода мы окажемся в среде редактора Visual Basic в тексте нужного макроса.

 

В строке

ActiveCell.FormulaR1C1 = «антонова»

Необходимо  заменить слово «антонова», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите ФИО первого продавца")

В строке

ActiveCell.FormulaR1C1 = «попова»

Необходимо  заменить слово «попова», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите  ФИО второго продавца")

При нажатии  на кнопку «фильтр_фио» последовательно  появятся 2 диалоговых окна

 

6.3. Фильтрация по диапазону количество

Для фильтрации по диапазону количество выполняются  следующие действия:

  1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_колво:

  1. Установить курсор в ячейку F2 и ввести с клавиатуры одно условие для количества, в ячейку G2 ввести с клавиатуры другое условие для количества, например >=20 и <=40.
  2. Установить курсор в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон – ячейки A10:G40 (вся исходная таблица) и диапазон условий – ячейки А1:H2

  1. Нажать ОК и выделить ячейку A1.
  2. Выбрать команду Сервис/Макрос/Остановить запись
  3. Для внесения изменений в текст макроса нужно выбрать команду Сервис/Макрос/Макросы, в появившемся окне выделить макрос фильтр_колво и нажать кнопку «Войти». В результате перехода мы окажемся в среде редактора Visual Basic в тексте нужного макроса.

 

В строке

ActiveCell.FormulaR1C1 = «>=20»

Необходимо  заменить слово «>=20», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите  знак >= и наименьшее значение  количества")

В строке

ActiveCell.FormulaR1C1 = «<=40»

Необходимо  заменить слово «<=40», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите  знак <= и наибольшее значение  количества")

При нажатии  на кнопку «фильтр_колво» последовательно  появятся 2 диалоговых окна

 

6.4. Фильтрация по дате и фамилии

Для фильтрации по диапазону количество выполняются следующие действия:

  1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_дата_фио:

  1. Установить курсор в ячейку С2 и ввести с клавиатуры дату, в ячейку D2 ввести с клавиатуры ФИО продавца, например 01.10.2007 и антонова.
  2. Установить курсор в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон – ячейки A10:G40 (вся исходная таблица) и диапазон условий – ячейки А1:H2

  1. Нажать ОК и выделить ячейку A1. В результате получится таблица:
  2. Выбрать команду Сервис/Макрос/Остановить запись
  3. Для внесения изменений в текст макроса нужно выбрать команду Сервис/Макрос/Макросы, в появившемся окне выделить макрос фильтр_дата_фио и нажать кнопку «Войти». В результате перехода мы окажемся в среде редактора Visual Basic в тексте нужного макроса.

 

В строке

ActiveCell.FormulaR1C1 = «01.10.2007»

Необходимо заменить слово  «01.10.2007», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите дату продажи")

В строке

ActiveCell.FormulaR1C1 = «антонова»

Необходимо заменить слово  «антонова», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите  ФИО продавца")

При нажатии на кнопку «фильтр_дата_фио» последовательно появятся 2 диалоговых окна

 

6.5. Отмена фильтрации

Для отмены фильтрации выполняются следующие действия:

  1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_отмена:

  1. Выделить ячейки A2:H3 и нажать клавишу Delete.
  2. Установить курсор в ячейку A1, затем в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон – ячейки A10:G40 (вся исходная таблица) и диапазон условий – ячейки А1:H2

  1. Нажать ОК. В результате все скрытые данные отобразятся в таблице
  2. Выбрать команду Сервис/Макрос/Остановить запись

 

Для кнопки «Титульный лист»  назначаем ранее созданный макрос «лист_титульный_лист».

Для всех остальных кнопок назначаем соответствующие макросы.

Модулю, в котором записаны все программы для работы с  листом «расширенный фильтр» задаем имя «№3_расширенный_фильтр».

Во все макросы, кроме  макроса «фильтр_отмена», для отмены действия предыдущей фильтрации данных добавляем после названия строку

Call фильтр_отмена

 

Полный текст полученного  модуля:

Sub фильтр_наименование()

Call фильтр_отмена

Range("E2").Select

ActiveCell.FormulaR1C1 = InputBox("введите  наименование фрукта")

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

    Range("A1:H2"), Unique:=False

Range("A1").Select

End Sub

 

Sub фильтр_фио()

Call фильтр_отмена

Range("D2").Select

ActiveCell.FormulaR1C1 = InputBox("введите ФИО первого продавца")

Range("D3").Select

ActiveCell.FormulaR1C1 = InputBox("введите ФИО второго продавца")

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

    Range("A1:H3"), Unique:=False

Range("A1").Select

End Sub

 

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

Call фильтр_отмена

Range("F2").Select

ActiveCell.FormulaR1C1 = InputBox("введите  знак >= и наименьшее значение  количества")

Range("G2").Select

ActiveCell.FormulaR1C1 = InputBox("введите  знак <= и наибольшее значение  количества")

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

    Range("A1:H2"), Unique:=False

Range("A1").Select

End Sub

 

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

Call фильтр_отмена

Range("C2").Select

ActiveCell.FormulaR1C1 = InputBox("введите дату продажи")

Range("D2").Select

ActiveCell.FormulaR1C1 = InputBox("введите ФИО продавца")

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

    Range("A1:H2"), Unique:=False

Range("A1").Select

End Sub

 

Sub фильтр_отмена()

Range("A2:H3").Select

Selection.ClearContents

Range("A1").Select

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

    Range("A1:H2"), Unique:=False

End Sub

 

Лист 7. «Итоги»

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

Создадим на листе 4 кнопки: Титульный лист, Итоги  по ФИО продавца, Итоги по наименованию фруктов, Отмена итогов.

7.1. Итоги по ФИО продавца

Для подведения итогов по ФИО продавца выполняются следующие действия:

  1. Установить курсор за пределами таблицы в ячейке A1, выбрать команду Сервис/Макрос/Начать запись. Задать макросу имя Итоги_фио, нажать ОК
  2. Установить курсор в ячейку А10 и выполнить команду Данные/Сортировка для группировки данных в столбце ФИО продавца

  1. Вызвать команду Данные/Итоги и задать следующие параметры:

  1. Нажать ОК и выбрать команду Сервис/Макрос/Остановить запись

В результате получится таблица:

7.2 . Итоги по наименованию фруктов

Для подведения итогов по наименованию фруктов выполняются следующие действия:

  1. Установить курсор за пределами таблицы в ячейке A1, выбрать команду Сервис/Макрос/Начать запись. Задать макросу имя Итоги_наим, нажать ОК
  2. Установить курсор в ячейку А10 и выполнить команду Данные/Сортировка для группировки данных в столбце Наименование

  1. Вызвать команду Данные/Итоги и задать следующие параметры:

  1. Нажать ОК и выбрать команду Сервис/Макрос/Остановить запись

В результате получится таблица:

7.3. Отмена итогов

Для отмены подведения итогов выполняются следующие действия:

  1. Установить курсор за пределами таблицы в ячейке A1, выбрать команду Сервис/Макрос/Начать запись. Задать макросу имя Итоги_отмена, нажать ОК
  2. Выделить ячейку А10, вызвать команду Данные/Итоги и нажать кнопку Убрать все
  3. Выполнить команду Данные/Сортировка для сортировки данных по столбцу №п/п

  1. Нажать ОК и выбрать команду Сервис/Макрос/Остановить запись

 

Для кнопки «Титульный лист» назначаем ранее созданный  макрос «лист_титульный_лист».

Для всех остальных кнопок назначаем соответствующие макросы.

Модулю, в котором записаны все программы для работы с  листом «итоги» задаем имя «№4_итоги».

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

Call итоги_отмена

 

Полный текст  полученного модуля:

Sub итоги_фио()

Call итоги_отмена

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("D11"), Order1:=xlAscending, Header:= _

    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

    DataOption1:=xlSortNormal

Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(6, 7), _

    Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub

 

Sub итоги_наим()

Call итоги_отмена

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("E11"), Order1:=xlAscending, Header:= _

    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

    DataOption1:=xlSortNormal

Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 7), _

    Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub

 

Sub итоги_отмена()

Range("A10").Select

Selection.RemoveSubtotal

Range("A10:G40").Sort Key1:=Range("A11"), Order1:=xlAscending, Header:= _

    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

    DataOption1:=xlSortNormal

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

Связанные документы

Информационные аналитические системы

Информационные системы

Информационные системы

Информационная система

Информационная система организации

Информационные системы маркетинга

Информационно-аналитическая служба

Логистическая информационная система

Понятие информационной системы

Информационная система и управление

Похожие темы

Информационная система магазина

Информационно-аналитическая система

Система безналичных расчетов