Применение 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 Мб (Скачать документ)

Задаем для  ячеек J5:J7 проверку вводимых значений (Данные/Проверка) с параметрами

4.1. Сортировка по столбцам «ФИО  продавца» и «Наименование»

Для проведения сортировки по столбцам «ФИО продавца»  и «Наименование» по возрастанию  необходимо выполнить следующие  действия:

  1. Установить курсор за переделами таблицы (J40)
  2. Выбрать меню Сервис/Макрос/Начать запись. Ввести имя макроса «сорт_фио_наим_возр».

Нажать кнопку ОК. После этого появится панель «Остановить запись».

  1. Выделить ячейку A10, выполнить команду Данные/Сортировка и задать сортировку по полю «ФИО продавца» и по полю «наименование» по возрастанию и нажать OK.

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

 

Текст полученного макроса:

Sub сорт_фио_наим_возр()

Range("A10").Select

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

    Range("E11"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _

    MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _

    DataOption2:=xlSortNormal

End Sub

Для кнопки «Сортировка  по фамилии и наименованию по возрастанию» назначаем созданный макрос «сорт_фио_наим_возр».

 

Для проведения сортировки по столбцам «ФИО продавца» и «Наименование» по убыванию необходимо выполнить следующие действия:

  1. Установить курсор за переделами таблицы (J40)
  2. Выбрать меню Сервис/Макрос/Начать запись. Ввести имя макроса «сорт_фио_наим_убыв».

Нажать кнопку ОК. После этого появится панель «Остановить запись».

  1. Выделить ячейку A10, выполнить команду Данные/Сортировка и задать сортировку по полю «ФИО продавца» и по полю «наименование» по убыванию и нажать OK.

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

 

Текст полученного макроса:

Sub сорт_фио_наим_убыв()

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("D11"), Order1:=xlDescending, Key2:= _

    Range("E11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, _

    MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _

    DataOption2:=xlSortNormal

End Sub

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

4.2. Сортировка в особом порядке  по полю «Наименование»

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

Для добавления нового списка используем команду Сервис/Параметры/Списки, в поле импорт списка выделяем ячейки J5:J7 и нажимаем кнопку «Импорт».

В макросе эти действия пользователя записываются в виде строки:

Application.AddCustomList ListArray:=Range("J5:J7")

Для удаления созданного списка используем команду Сервис/Параметры/Списки, в поле списки выделяем созданный ранее список и нажимаем кнопку «Удалить».

В макросе эти действия пользователя записываются в виде строки:

Application.DeleteCustomList ListNum:=5

 

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

  1. Устанавливаем курсор за переделами таблицы (J40)
  2. Выбираем меню Сервис/Макрос/Начать запись. Вводим имя макроса «сорт_особая_возр».

Нажимаем кнопку ОК. После этого появится панель «Остановить запись».

  1. Выделяем ячейку A10, выполняем команду Данные/Сортировка и задаем сортировку по полю «наименование» по возрастанию.

  1. Нажимаем кнопку «Параметры», в списке выбираем созданный ранее список

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

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

 

Текст полученного макроса  с добавлением 2х строк:

Sub сорт_особая_возр()

Application.DeleteCustomList ListNum:=5

Application.AddCustomList ListArray:=Range("J5:J7")

Range("A10").Select

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

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

    DataOption1:=xlSortNormal

End Sub

Для кнопки «Сортировка  в особом порядке по возрастанию» назначаем созданный макрос «сорт_особая_возр».

 

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

  1. Устанавливаем курсор за переделами таблицы (J40)
  2. Выбираем меню Сервис/Макрос/Начать запись. Вводим имя макроса «сорт_особая_убыв».

Нажимаем кнопку ОК. После этого появится панель «Остановить запись».

  1. Выделяем ячейку A10, выполняем команду Данные/Сортировка и задаем сортировку по полю «наименование» по убыванию.

  1. Нажимаем кнопку «Параметры», в списке выбираем созданный ранее список

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

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

 

Текст полученного макроса  с добавлением 2х строк:

Sub сорт_особая_убыв()

Application.DeleteCustomList ListNum:=5

Application.AddCustomList ListArray:=Range("J5:J7")

Range("A10").Select

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

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

    DataOption1:=xlSortNormal

End Sub

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

4.3. Отмена сортировки

Для отмены сортировки необходимо выполнить следующие  действия:

  1. Устанавливаем курсор за переделами таблицы (J40)
  2. Выбираем меню Сервис/Макрос/Начать запись. Вводим имя макроса «сорт_отмена».

Нажимаем кнопку ОК. После  этого появится панель «Остановить запись».

  1. Выделяем ячейку A10, выполняем команду Данные/Сортировка и задаем сортировку по полю «№ п/п» по возрастанию.

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

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

 

Текст полученного макроса:

Sub сорт_отмена()

Range("A10").Select

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

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

    DataOption1:=xlSortNormal

End Sub

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

 

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

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

 

Лист 5. «Автофильтр»

На этом листе  проводится отбор данных в таблице с помощью автофильтрации.

Создаем на листе 2 кнопки, задаем им названия в соответствии с рисунком.

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

  1. Установить курсор за пределы таблицы (A1) и выбрать меню Сервис/Макрос/Начать запись. Ввести имя макроса «Автофильтр». Нажать ОК.
  2. Выделить ячейку A10 и выбрать команду Данные/Фильтр/Автофильтр. После этого в строке заголовка таблицы появятся кнопки раскрывающихся списков
  3. Выбрать меню Сервис/Макрос/Остановить запись

 

Текст полученного макроса:

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

Range("A10").Select

Selection.AutoFilter

End Sub

Для кнопки «Включение/выключение автофильтра» назначаем созданный  макрос «автофильтр».

 

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

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

5.1. Использование автофильтра

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

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

    В результате получим  таблицу следующего вида

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

В результате получим таблицу следующего вида

 

  1. фильтрация с помощью пользовательского фильтра по двум условиям. Например, если нужно получить сведения о товарах, сумма продаж для которых больше 1000 руб. и меньше 2000 руб. - откройте список в столбце «сумма» и выберите пункт «условие» в появившемся окне введите следующие параметры:

 

 

  1. просмотр наибольших или наименьших элементов списка только для числовых данных. Для этого необходимо выбрать в соответствующем столбце пункт «первые 10» и ввести необходимые параметры.
  2. отображение всех данных с помощью пункта «все» для соответствующих столбцов с заданными ранее условиями отбора.

 

Для кнопки «Включение/выключение автофильтра» назначаем созданный  макрос «автофильтр».

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

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

 

Лист 6. «Расширенный фильтр»

Расширенный фильтр - сложный вид фильтрации по сравнению  с автофильтром. Для выполнения расширенной  фильтрации необходимо предварительно сформировать в свободном месте рабочего листа критерий фильтрации.

Для создания таблицы  критериев выделяем ячейки A10:G10 и копируем их в ячейки A1:G1. Добавляем еще одну ячейку «Количество» (G1). Строка 2 будет содержать критерии отбора данных. Создадим на листе следующие 6 кнопок: Титульный лист, Фильтр по одному наименованию, Фильтр по ФИО двух продавцов, фильтр по диапазону количество, Фильтр по дате и ФИО продавца, Отмена фильтра.

6.1. Фильтрация по наименованию

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

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

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

  1. Нажать ОК и выделить ячейку A1. В результате получится таблица:

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

 

В строке

ActiveCell.FormulaR1C1 = «тарелки»

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

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

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

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

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

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

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

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

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

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

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

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

Банковские информационные системы

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

Информационная система "Библиотека"

Похожие темы

Применение информационных технологий

Экономико-информационные системы

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