Информационно-аналитическая система «Отдел экспорта»

Введение

Задание Основная часть Используемые макросы Лист «Регистрация поставок»

Фильтры Расширенный фильтр Сводная таблица Макросы Заключение Литература

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

Тема данной курсовой работы — это создание информационно-аналитической системы отдел экспорта.

Данная система создается для удобства пользователя, для автоматизации действий, для экономии времени и сил.

Эта работа содержит 8 листов: лист «Начало», лист «Регистрация поставок», «Прейскурант цен», «Фильтр», «Расширенный фильтр», «Итоги», «Сводная таблица», «Диаграмма». На каждом листе расположены специальные кнопки, с помощью которых можно осуществлять те или иные действия, что весьма облегчает задачу при работе с табличными данными.

Задание

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

«Регистрация поставок» (код товара, наименование товара; страна, импортирующая товар; объём поставляемой партии; стоимость партии (функция ВПР)) — 40 строк. Одна страна может импортировать разные товары.

«Прейскурант цен» (код товара, наименование товара, цена за единицу товара) — не более 7-ми строк.

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

Предложить свой вариант сбора информации и анализа данных.

Основная часть

Первоначально мы оказываемся на титульном листе:

Оформление листа происходит с помощью добавления объекта WordArt, автофигур, заливки и шрифтов.

Рисунок 1 «Лист „Начало“»

Титульный лист содержит 10 кнопок: 7 кнопок — переходов, с помощью которых можно перейти на лист «Регистрация поставок», «Прейскурант цен», «Фильтр», «Расширенный фильтр», «Сводная таблица», «Итоги», «Диаграмма» и кнопки «об авторе», «Регистрация», «Выход»

Используемые макросы:

Макрос для перехода на лист «Прейскурант Цен»

Sub Макрос1()

' Макрос1 Макрос

' Макрос записан 03.05.2008 (-)

Sheets («Прейскурант цен»).Select

ActiveWindow.SmallScroll Down:=-9

Range («A1:C1»).Select

End Sub

Макрос для перехода на лист «Начало»

Sub Макрос2()

' Макрос2 Макрос

' Макрос записан 03.05.2008 (-)

Sheets («Начало»).Select

Range («A1»).Select

End Sub

Макрос для перехода на лист «Расширенный фильтр»

Sub Макрос3()

' Макрос3 Макрос

' Макрос записан 03.05.2008 (-)

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

Range («A4»).Select

End Sub

Макрос для перехода на лист «Итоги»

Sub Макрос4()

' Макрос4 Макрос

' Макрос записан 03.05.2008 (-)

Sheets («Итоги»).Select

ActiveSheet.Shapes («WordArt 2»).Select

Range («A1»).Select

End Sub

Макрос для перехода на лист «Регистрация поставок»

Sub Макрос5()

' Макрос5 Макрос

' Макрос записан 03.05.2008 (-)

Sheets («Регистрация поставок»).Select

Range («A1:D1»).Select

End Sub

Макрос для перехода на лист «Фильтр»

Sub Макрос6()

' Макрос6 Макрос

' Макрос записан 03.05.2008 (-)

Sheets («Фильтр»).Select

Range («A1»).Select

End

Кнопка «Об авторе». При ее нажатии, на экране появляется окно.

Данное окно создавалось в UserForm1 с помощью панели инструментов: кнопки — «надпись».

Кнопка «Выход». Нужна для осуществления выхода из Excel. При ее нажатии появляется диалоговое окно:

Рисунок 2 «Кнопка выхода»

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

Рисунок 3 «Кнопка выхода»

Sub выход ()

'

' выход Макрос

' Макрос записан 01.05.08 ®

'

Dim txtСообщение As String, txtЗаголовок As String

Dim Кнопки As Integer, Результат As Integer

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

txtЗаголовок = «До свидания!»

Кнопки = vbYesNo + vbQuestion + vbfaultButton2

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

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

Application.Quit

Else

MsgBox «Выход не состоится», vbOKOnly, «Снова привет!»

End If

End Sub

Лист «Регистрация поставок»

На листе «Регистрация поставок» вставляем кнопку для сортировки Далее создаем UserForm 2

Рисунок 4 «Кнопка „Редактор“»

и прикрепляем программу к кнопке

Private Sub CommandButton4_Click ()

Range («B4»).Select

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

End Sub

Private Sub CommandButton2_Click ()

Range («C5»).Select

Selection.Sort Key1:=Range («C5»), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottomъ

End Sub

Private Sub CommandButton3_Click ()

Range («D5»).Select

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

End Sub

Private Sub CommandButton1_Click ()

UserForm2.Hide

End Sub

Private Sub UserForm_Click ()

UserForm2.Hide

End Sub

Таким же образом создаем кнопку для добавления данных

Рисунок 5 «Добавление данных»

Private Sub UserForm_Click ()

End Sub

Dim str As String

Dim k As Integer

Private Sub btnSave_Click ()

If cmbKod = «» Or TextNaimenovanie = «» Or cmbStrana = «» Or TextObem = «» Then

MsgBox («Введены не все данные»)

Exit Sub

End If

Sheets («Регистрация поставок»).Activate

For i = 3 To 5000

If Cells (i, 1) = «» Then

Cells (i, 1).Value = cmbKod. Text

Cells (i, 2).Value = TextNaimenovanie. Caption

Cells (i, 3).Value = cmbStrana. Text

Cells (i, 4).Value = TextObem. Text

k = i

Exit For

End If

Next i

Me.Hide

End Sub

Private Sub cmbKod_Change ()

TextNaimenovanie.Caption = «»

Sheets («Прейскурант цен»).Activate

Cells.Find (What:=cmbKod.Text, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _

.Activate

ActiveCell.Offset (rowOffset:=0, columnOffset:=1).Activate

TextNaimenovanie.Caption = ActiveCell. Text

cmbStrana.SetFocus

Sheets («Регистрация поставок»).Activate

End Sub

Private Sub TextObem_Change ()

If TextObem. Value < 0 Then

MsgBox «Числа не должны быть отрицательные!», vbOKOnly + vbInformation

TextKod.SetFocus

End If

If Not IsNumeric (TextObem.Text) And Len (TextObem) <> 0 Then

MsgBox «Вводить надо числовые данные!», vbOKOnly + vbInformation

TextObem.Value = «»

TextObem.SetFocus

End If

End Sub

Private Sub UserForm_Initialize ()

Sheets («Регистрация поставок»).Activate

TextNaimenovanie.Caption = «»

cmbKod.Clear

For i = 3 To 5000

If Worksheets («Прейскурант цен»).Cells (i, 1) <> «» Then

cmbKod.AddItem Worksheets («Прейскурант цен»).Cells (i, 1)

End If

Next i

cmbStrana.Clear

For i = 3 To 500

If Worksheets («Регистрация поставок»).Cells (i, 1) <> «» Then

cmbStrana.AddItem Worksheets («Регистрация поставок»).Cells (i, 3)

End If

Next i

cmbKod.SetFocus

End Sub

Фильтры

Сначала следует выделить всю таблицу. Затем выбрать команду Данные, Фильтр, Автофильтр.

Рисунок 6 «Фильтр»

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

Расширенный фильтр позволяет:

· сразу копировать отфильтрованные записи в другое место рабочего места рабочего листа;

· сохранять критерий отбора для дальнейшего использования;

· показывать в отфильтрованных записях не все столбцы, а только указанные;

По базе данных задаем условия отбора по Наименованию товара — cахар, рис; по объему партии ->2000, <1000

Рисунок 7 «Наименование товара»

Рисунок 8 «Промежуточные итоги»

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

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

Сводная таблица связана с источником данных, но эта связь не динамическая. Это означает, что сводная таблица автоматически не пересчитывается при изменении исходных данных. Для обновления таблицы нужно нажать кнопку Обновить данные на панели инструментов Запрос и сводная таблица, которая автоматически выводится при создании сводной таблицы. Сводная таблица создается с использованием мастера сводных таблиц.

Создание сводной таблицы. Последовательность действий следующая:

Выбрать в меню Вставка команду Сводная таблица.

Мастер сводных таблиц выведет окно диалога. Здесь необходимо указать тип данных которые будут использоваться для создания таблиц.

Задать диапазон, занимаемый исходной таблицей.

Далее необходимо указать, где расположить сводную таблицу и нажать кнопку Ок.

После нажатия кнопки Ок мастер сводных таблиц создаст таблицу.

Рисунок 9 «Сводная таблица»

Макросы

Макрос — это набор инструкций, указывающих последовательность действий, которые Microsoft Excel должен выполнить. Они, по сути, являются компьютерными программами, но они не выполняются независимо, а работают только внутри Excel. Их можно использовать для автоматизации трудоемких или часто повторяющихся задач. Макросы могут быть простыми или очень сложными. Они могут быть интерактивными, т. е. работающими в диалоге с пользователем.

Есть два способа создания макроса: можно автоматически записать последовательность действий пользователя или вручную ввести инструкции на особом листе Excel, называемом модулем. Для ввода инструкций в модуле используется язык программирования Visual Basic.

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

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

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

1. В меню Разработчик выбрать команду Макрос, а затем — Запись макроса.

2. Назначить имя макросу.

3. Ввести краткий комментарий к имя макросу в поле Описание.

4. При желании можно назначить макросу сочетание клавиш, установив флажок Сочетание клавиш и введя в поле английскую букву

5. Сохранить макрос в активной книге, выбрав из списка в поле Сохранить в значение Эта книга.

6. Нажать кнопку ОК. Если в меню Вид/Панели инструментов установлен флажок против пункта Остановка записи, то на экране появится панель инструментов с кнопкой Остановить запись.

7. Выполнить все необходимые действия и нажать кнопку Остановить запись.

Для проверки работы нового макроса необходимо очистить лист и запустить созданный макрос. Для запуска макроса нужно в меню Сервис выбрать команду Макрос, а затем — Макросы. В открывшемся диалоговом окне выбрать нужный макрос и нажать кнопку Выполнить.

Заключение

макрос фильтр сводный excel

Решением данной курсовой работы было создание рабочих листов с выше указанными названиями.

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

Также были построены итоговая таблица, которая содержит промежуточные и общие итоги и сводная таблица, которая объединяет и анализирует большие объемы данных, подводит итоги. На основе сводной таблицы была создана сводная диаграмма, которая изображает графический вариант сводной таблицы.

Была создана «Поисковая система» для быстрого поиска данных в таблице. Созданы кнопки переходов с титульного листа на все остальные листы таблицы и обратно.

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

1. Л. В. Маликова, А. Н. Пылькин. Практический курс по электронным таблицам MS EXCEL. — М.: Горячая линия — Телеком, 2004.

2. Гарнаев А. Ю. Использование MS EXCEL и VBA в экономике и финансах. — СПб: БХВ, 1999.

3. Ф. А. Гурьянова, А. А. Родигин, А. И. Сеселькин. Решение задач прикладной информатики в менеджменте туризма средствами MS EXCEL. М.: «Советский сорт», 2003.

4. Столяров А. М., Столярова Е. С. EXCEL 2002. M.: ДМК Пресс, 2002.

5. Каганов В. И., Компьютерные вычисления в средах EXCEL и MathCAD. — М.: Горячая линия — Телеком, 2003.