Автоматизация в бизнес-задачах

3.Автоматизация в бизнес-задачах

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

Учет поступлений и продаж

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

Рис.3.1.

Итак, напрямую работа с листом на рис.3.1 нежелательна. Конечно при поступлении, либо продаже можно напрямую вносить в данный лист данные. Так, скажем, при приходе новой партии стенок Уют в количестве 5 штук по новой цена 27 770 рублей можно напрямую скорректировать и количество единиц и цену поступления в необходимой строке (предполагается, что в качестве цены поступления конкретных товаров устанавливается цена последней поставки). Однако это и не очень удобно и является “плацдармом” для возможных ошибок со стороны пользователя. Более удобно создать лист управления (рис.3.2).

Рис.3.2.

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

Перейдем к рассмотрению деталей. Так, левая половина листа организована для новых поступлений товаров, которые уже имеются в информационной базе. Верхний список заполняется названиями товаров уже имеющимися в информационной базе. Это производится при открытии рабочей книги (текст процедуры приведен на листинге 3.1). В тексте процедуры предполагается, что в качестве значения свойства Name выбрано Spk.

Листинг 3.1. Процедура, выполняемая при открытии книги

Private Sub Workbook_Open()

' Очистка списка

Worksheets(1).Spk. Clear

' Подсчет количества товаров в информационной базе

N = 0

While Worksheets(3).Cells(N + 2, 1).Value <> ""

N = N + 1

Wend

' Заполнение списка

For i = 1 To N

Worksheets(1).Spk. AddItem Worksheets(3).Cells(i + 1, 1).Value

Next

Worksheets(1).Spk. ListIndex = -1

End Sub

После выбора названия товара в поле со списком в ячейку С5 заносится цена с третьего листа книги. Для этого процедуру, выполняемую при щелчке по списку оформить так, как показано на листинге 3.2. В ней также ничего принципиально нового (по сравнению с ранее рассмотренными примерами) нет.

Листинг 3.2. Процедура обработки

Private Sub Spk_Click()

Range("c5").Value = _

Worksheets(3).Cells(Spk. ListIndex + 2, 2).Value

End Sub

Теперь от пользователя требуется внести количество единиц нового поступления указанного товара и нажать кнопку Внести. Однако для повышения защищенности от случайных действий мы разместили еще поля для пароля (ниже кнопки Внести).

Так, мы обеспечим внесение информации о новом поступлении (увеличении числа указанных товаров) на третий лист только при вводе конкретного пароля в текстовое окно для пароля (Name – Pass). Для этого процедуру, выполняемую при щелчке по кнопки Внести необходимо оформить так, как показано на листинге 3.3.

Листинг 3.3. Обработка щелчка по кнопке Внести

Private Sub CommandButton1_Click()

If Pass. Text = "357" Then

Worksheets(3).Cells(Spk. ListIndex + 2, 2).Value = Range("c5").Value

Col = Range("C6").Value

Worksheets(3).Cells(Spk. ListIndex + 2, 4).Value = _

Worksheets(3).Cells(Spk. ListIndex + 2, 4).Value + Col

MsgBox "Данные внесены"

Pass. Text = ""

Else

MsgBox "Ошибка пароля! Данные не внесены"

End If

End Sub

Здесь при введении пароля 357 на третий лист будет добавлено количество товара в указанную строку. Далее на экран выводится информационное сообщение, что данные внесены и очищается поля пароля.

Теперь перейдем к рассмотрению правой части на рис.3.2. Этот фрагмент необходимо использовать при внесении нового товара (с указанием количества).

Листинг 3.4. Обработка щелчка по кнопке Внести новый товар

Private Sub CommandButton2_Click()

N = 0

While Worksheets(3).Cells(N + 2, 1).Value <> ""

N = N + 1

Wend

If Pass2.Text = "35791" Then

Worksheets(3).Cells(N + 2, 1).Value = Range("c3").Value

Worksheets(3).Cells(N + 2, 2).Value = Range("c4").Value

Worksheets(3).Cells(N + 2, 4).Value = Range("c5").Value

MsgBox "Данные внесены"

Pass. Text = ""

Else

MsgBox "Ошибка пароля! Данные не внесены"

End If

End Sub

Мы использовали в процедуре другое поле для пароля (Name - Pass2). Теперь перейдем к другому листу – Отгрузка (рис.3.3).

Рис.3.3.

На листе Отгрузка расположено два элемента управления – Поле со списком и Кнопка. Для заполнения списка необходимо процедуру Workbook_Open (листинг 3.5).

Листинг 3.5. Процедура, выполняемая при открытии книги

Private Sub Workbook_Open()

' Очистка списка

Worksheets(1).Spk. Clear

Worksheets(2).Spk. Clear

' Подсчет количества записей в прайсе на втором листе

N = 0

While Worksheets(3).Cells(N + 2, 1).Value <> ""

N = N + 1

Wend

' Заполнение списка

For i = 1 To N

Worksheets(1).Spk. AddItem Worksheets(3).Cells(i + 1, 1).Value

Worksheets(2).Spk. AddItem Worksheets(3).Cells(i + 1, 1).Value

Next

Worksheets(1).Spk. ListIndex = -1

Worksheets(2).Spk. ListIndex = -1

End Sub

На очереди следующая процедура (листинг 3.6),которая выполняется при щелчке по списку. В результате на лист Отгрузка будет внесено количество товара и цена отгрузки.

Листинг 3.6. Процедура, выполняемая при щелчке по полю со списком

Private Sub Spk_Click()

Range("e6").Value = _

Worksheets(3).Cells(Spk. ListIndex + 2, 4).Value

Range("e7").Value = _

Worksheets(3).Cells(Spk. ListIndex + 2, 3).Value

End Sub

Листинг 3.7. Процедура, выполняемая при щелчке по кнопке

Private Sub CommandButton1_Click()

If Pass. Text = "357" Then

ColPrais = Worksheets(3).Cells(Spk. ListIndex + 2, 4).Value

Col = Range("C6").Value

If Col > ColPais Then

MsgBox "Òàêîãî êîëè÷åñòâà òîâàðà íà ñêëàäå íåò"

Exit Sub

End If

Worksheets(3).Cells(Spk. ListIndex + 2, 2).Value = Range("c5").Value

ColPrais = ColPrais - Col

Worksheets(3).Cells(Spk. ListIndex + 2, 4).Value = ColPrais

MsgBox "Äàííûå âíåñåíû"

Pass. Text = ""

Else

MsgBox "Îøèáêà ïàðîëÿ! Äàííûå íå âíåñåíû"

End If

End Sub