Применение Excel для анализа информации из базы данных

3 ПРИМЕРЫ ВОЗМОЖНОСТЕЙ ЯЗЫКА VBA

3.1 Задание к курсовой работе

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

Необходимо создать приложение на основе Excel, которое бы:

• Производило вставку в лист Excel данные по всем строкам и всем вышеуказанным столбцам этой таблицы.

• Генерировало  бы в Excel дополнительные столбцы следующего содержания:

Заказ товара, штук — разница между столбцами Минимальный запас и На складе. В этот столбец помещалась бы информация о количестве товара в штуках, которое нужно срочно заказать. Эту информацию нужно генерировать только для тех записей, для которых значение в столбце Минимальный запас больше, чем в столбце На складе, и у которых значение столбца Поставки прекращены установлено в Ложь.

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

• Вставляло бы одной строкой ниже полученных записей из базы данных две итоговые строки:

общая стоимость товаров  на складе — итоговая стоимость  всех товаров, которые находятся  на складе (как сумма произведений столбцов На складе и Цена для каждой строки);

общая стоимость товаров  к заказу — итог по столбцу Стоимость  заказа.

3.2 Описание алгоритма

Для выполнения поставленной задачи я использовала учебную базу данных Борей.mdb, созданную специально для изучения Microsoft Office, которая расположена в каталоге C:\Program Files\Microsoft Office\OFFICE11\SAMPLES. Для моей курсовой работы необходима только таблица Товары, в которой находятся следующие важные для нас столбцы:

Код товара — идентификатор  товара;

Марка — наименование продукта;

Цена — стоимость продукта за единицу;

На складе — количество единиц этого товара на складе;

Минимальный запас — минимально допустимое количество единиц данного  товара на складе. Если реальное количество единиц этого товара меньше, чем  этот уровень, товар нужно срочно заказывать;

Поставки прекращены —  флаг прекращения работы с товаром. Если в этом столбце стоит единица, то это значит, что принято решение  закупки этого товара больше не производить.

Все остальные столбцы  для целей этой работы можно игнорировать.

Общий вид исходной базы данных представлено на рис 3.1.

Рисунок 3.1 – Первые строки исходной базы данных

В реальной задаче правильнее (и намного производительнее) было бы перенести расчет таких столбцов на сервер баз данных, использовав SQL-запрос с вычисляемыми столбцами. Однако для целей моей курсовой работы необходимо реализовать их вставку средствами Excel.

Далее переходим к основной работе, т.е. создание самого приложения. Для этого создаю новый файл Excel, отображаю вкладку Разработчик. На вкладке Разработчик в группе Элементы управления необходимо нажать кнопку Вставить, а затем в разделе Элементы ActiveX выбрать элемент Кнопка. И помещаю кнопку на лист Excel, для наших целей будем считать, что созданная кнопка занимает две верхние строки первого листа (рисунок 3.2).

Рисунок 3.2 – Вставка кнопки

Чтобы задать свойства Кнопки команды, на вкладке Разработчик в группе Элементы управления нажимаем кнопку Свойства. Также можно щелкнуть по Кнопке правой кнопкой мыши и выбрать пункт Свойства. Прежде чем нажать кнопку Свойства, нужно убедиться в том, что объект, свойства которого необходимо изучить или изменить, выбран. Настраиваю для свойства Caption значение Получить данные. Воспользуюсь свойством Font, чтобы настроить подходящий шрифт для нашей кнопки.

Щелкнув правой кнопкой мыши по созданной мной кнопке и в контекстном меню выбрав Исходный текст, откроется редактор Visual Basic с курсором ввода на месте события Click для нашей кнопки, где и будет писаться код программы приложения (рисунок 3.3).

Рисунок 3.3 – Окно редактора VBA

Необходимо еще подключить нужную библиотеку. Для этого в окне редактора кода в меню Tools выбераю References и устанавливаю флажок напротив строки Microsoft ActiveX Data Objects 2.1 Library.

3.3 Листинг программы

Private Sub CommandButton1_Click()

'Вначале — чистим всю  книгу от старых данных 

Cells.Select

Selection.Clear

' Создаем и настраиваем объект Connection

Dim cn As New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Борей.mdb"

cn . Open

'Создаем и настраиваем  объект Recordset

Dim rs As New ADODB.Recordset

rs.Open"SELECT[КодТовара],[Марка],[Цена],[НаСкладе],[МинимальныйЗапас]," & _ "[ПоставкиПрекращены] FROM Товары", cn

'На основе Recordset создаем объект QueryTable и вставляем его, начиная с 4-й строки

Dim QT1 As QueryTable

Set QT1 = QueryTables.Add(rs, Range("A4"))

QT1. Refresh

'Определяем количество  записей в QueryTable

Dim nRowCount As Integer

Dim oRange As Range

Set oRange = QT1.ResultRange

nRowCount = oRange.Rows.Count

'Формируем столбец "Заказать товара, штук"

Range("G4").Value = "Заказать товара, штук"

Range("G4").Font.Bold = True

Range("G4").Columns.AutoFit

'Формируем столбец "Стоимость  заказа"

Range (" H 4"). Value = "Стоимость заказа"

Range("H4").Font.Bold = True

Range("H4").Columns.AutoFit

'Создаем диапазон, который  включит в себя столбец G "вдоль" QueryTable

Set oRange = Range("G5", "G" & nRowCount + 3)

'Готовим переменные, которые  нам потребуются в цикле 

Dim oCell As Range

Dim sRowNumber As String

Dim cMoney As Currency

Dim cItogMoney As Currency

Dim cItogSklad As Currency

'Проходим циклом по  всем ячейкам созданного диапазона 

For Each oCell In oRange.Cells

'Получаем абсолютный номер  строки в виде строковой переменной 

sRowNumber = Replace(oCell.Address(True), "$G$", "")

'Проверяем определенные нами условия

If Range("E" & sRowNumber).Value > Range("D" & sRowNumber) And _

Range("F" & sRowNumber).Value = False Then

'Получаем значение для  столбца G (заказ в штуках)

oCell.Value = (CInt(Range("E" & sRowNumber).Value) — CInt(Range("D" & sRowNumber).Value))

'Получаем значение для  столбца H (стоимость заказа)

cMoney = (CInt(Range("E" & sRowNumber).Value) — CInt(Range("D" & sRowNumber).Value)) * CCur(Range("C" & sRowNumber).Value)

'Записываем его в столбец H

Range("H" & sRowNumber).Value = cMoney

'Сразу плюсуем к итогу  в рублях 

cItogMoney = cItogMoney + cMoney

End If

'И в том же цикле  сразу суммируем стоимость товаров  на складе 

cItogSklad = cItogSklad + (Range("C" & sRowNumber).Value * Range("D" & sRowNumber).Value)

Next

'Формируем две строки  с итогами 

Range("B" & nRowCount + 6). Value = "Общая стоимость товаров на складе:"

Range("B" & nRowCount + 6).Font.Bold = True

Range("B" & nRowCount + 7).Value = "Общая стоимость товаров к заказу:"

Range("B" & nRowCount + 7).Font.Bold = True

Range("D" & nRowCount + 6).Value = cItogSklad

Range("D" & nRowCount + 6).Font.Bold = True

Range("D" & nRowCount + 7).Value = cItogMoney

Range("D" & nRowCount + 7).Font.Bold = True

'Для красоты выделяем  итоговое значение… 

Range("D" & nRowCount + 7). Select

'…и производим скроллирование

Range("D" & nRowCount + 7).Show

End Sub

 

 

ЗАКЛЮЧЕНИЕ

Не смотря на множество  недостатков язык Visual Basic for Applications широко применяется для решения профессиональных задач в области автоматизации работы с офисным пакетом Microsoft Office.

К недостаткам VBA можно отнести  невозможность создания более-менее  автономного кода, а также проблемы с совместимостью. Исходный код, написанный в более новом программном пакете может не запуститься или выдавать ошибки при запуске в более старом. Также к недостаткам часто относят и слишком высокую открытость кода для случайного изменения. Тем не менее, многие программные пакеты, поддерживающие VBA (в частности, MS Office) позволяют зашифровать исходный код и установить пароль на его просмотр.

Но все же язык прост  и его может освоить любой пользователь не имеющего никакого опыта в программировании на VBA. Что позволит решить множество проблем и упростит всю работу в приложении Excel.

 

 

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

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

 

 

СПИСОК  ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

1. Гарнаев, А. С. Использование MS Excel и VBA в экономике и финансах / А. С. Гарнаев – Санкт-Петербург, BHV, 1999. – 336 с.
2. Программирование на VBA : учебно-методическое пособие / под ред. В. Л. Титов. – Могилев,2008. – 124 с.
3. Академия Специальных Курсов по Информационным Технологиям [Электронный ресурс] / Академия Специальных Курсов по Информационным Технологиям. – Санкт-Петербург, 2004. – Режим доступа : http://www.askit.ru/custom/vba_office/vba_office_plan.htm. – Дата доступа : 15.05.2011.