Выполнение курсовой работы

В рабочей книге создается еще 7 листов:

· Сортировка

· Автофильтр

· Расширенный фильтр

· Итоги

· Функции

· Сводная таблица

· Диаграмма

На каждый из листов (кроме двух последних) скопирована таблица «Регистрация заказов». На каждом из листов выполняется определенное задание.

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

На этом листе проводится сортировка данных в таблице по одному или нескольким столбцам. Сортировка данных таблицы «регистрация заказов» осуществляется с помощью макроса и UserForm.

Сортировка по наименованию и стоимости через макрос:

ActiveWorkbook.Worksheets("Сортировка").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Сортировка").Sort.SortFields.Add Key:=Range(_

"C6:C45"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

ActiveWorkbook.Worksheets("Сортировка").Sort.SortFields.Add Key:=Range(_

"F6:F45"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("Сортировка").Sort

.SetRange Range("A5:F45")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

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

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

Процедура выглядит следующим образом:

Range("A5").Select

Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 

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

Макрос на выполнение автофильтра:

Range("C11").Select

Selection.AutoFilter

ActiveSheet.Range("$A$5:$F$45").AutoFilter Field:=1, Criteria1:=Array(_

"ИП ""Вилон""", "ОАО ""Старый город""", "ООО ""Камилла""", "ООО ""Эссен"""), _

Operator:=xlFilterValues

ActiveSheet.Range("$A$5:$F$45").AutoFilter Field:=6, Criteria1:=">1500", _

Operator:=xlAnd

 

На отмену автофильтра:

Range("C17").Select

Selection.AutoFilter

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

Расширенный фильтр более сложный вид фильтрации по сравнению с автофильтром. Для выполнения расширенной фильтрации необходимо предварительно сформировать в свободном месте рабочего листа критерий фильтрации. Таблица критериев состоит из заголовка столбца и самого критерия. Записано два макроса – один для расширенного фильтра, второй для отмены фильтра. В первом макросе использовано диалоговое окно InputBox для ввода критерия.

Текст макроса:

Range("A2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите фирму-заказчика")

Range("C19").Select

Range("A11:F51").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

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

ActiveWindow.SmallScroll Down:=-3

 

При нажатии на кнопку «Фильтрация» появляется окно:

В которое необходимо ввести название любой фирмы из таблицы.

 

 

4.4. Лист 4. «Итоги»

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

Текст макроса:

Range("A5").Select

ActiveWorkbook.Worksheets("Итоги").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Итоги").Sort.SortFields.Add Key:=Range("A5"), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Итоги").Sort

.SetRange Range("A6:F45")

.Header = xlNo

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

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

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

4.5. Лист 5. «Функции»

В курсовой работе использованы пять видов функций рабочего листа из категории «Работа с базой данных»:

· БДСУММ - нахождение общей суммы, на которую был продан указанный товар;

· ДСРЗНАЧ – нахождения среднего значения суммы, на которую был продан указанный товар;

· ДМАКС – возвращает наибольшее число в столбце стоимости;

· ДМИН – возвращает наименьшее число в столбце стоимости;

· БСЧЕТ – подсчитывает число заказов указанного товара.

Все эти функции имеют одинаковую синтаксическую форму записи, и на каждую из них записан макрос. Наименование товара вводится через диалоговое окно InputBox:

4.6. Лист 6. «Сводная таблица»

Наиболее удобным встроенным инструментом для анализа данных в Excel является сводная таблица. Это вспомогательная таблица, построенная с помощью мастера сводных таблиц и анализирующая данные исходной таблицы.

4.7. Лист 7. «Диаграмма»

Диаграмма — графическое представление числовых данных, позволяющее быстро оценить соотношение нескольких величин. Преимущество диаграмм перед другими типами наглядной статистической информации заключается в том, что они позволяют быстро произвести логический вывод из большого количества полученных данных. Результаты расчётов, выполненных с помощью систем статистических вычислений, заносятся в таблицы. Они являются основой для последующего анализа или для подготовки статистического отчёта.

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

Созданная диаграмма, связана с полями сводной таблицы.