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

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

Рис. 2.16. Содержание листа Номенклатура

Напрямую работа с листом, показанным на рис. 2.16, нежелательна. Конечно, менеджер при поступлении либо продаже товаров может напрямую вносить в него данные. Например, при приходе новой партии стенок «Уют» в количестве 5 штук по новой цена 25 770 рублей можно просто скорректировать и количество единиц на складе, и цену поступления. Однако это и не очень удобно, и является «плацдармом» для возможных ошибок со стороны пользователя. Удобнее создать лист управления (рис. 2.17) с элементами, обеспечивающими удобный интерфейс. А сам лист Номенклатура далее мы скроем (эта возможность легко реализуется с помощью сервиса приложения Microsoft Excel), так что он будет недоступен напрямую для пользователя. Разумеется, после этого можно ввести защиту на структуру книги (с указанием пароля). В результате пользователи могут работать с листом Номенклатура только с помощью интерфейса, реализованного на других листах.

Рис. 2.17. Лист для внесения товаров

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

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

Левая половина листа Поступление предназначена для новых поступлений товаров, которые уже имеются в информационной базе (на листе Номенклатура). Для этого элемент управления «Поле со списком» (расположен в районе ячейки A3) заполняется названиями товаров, присутствующими на листе Номенклатура. Это действие производится при открытии рабочей книги (текст необходимой для этого процедуры приведен в листинге 2.21). В программном коде предполагается, что в качестве значения свойства Name поля со списком выбрано Spk.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

' Листинг 2.21. Процедура, выполняемая при открытии книги
Private Sub Workbook_Open()
' Очистка списка
Worksheets("Поступление").Spk.Clear
' Подсчет количества товаров в информационной базе
N = 0
While Worksheets("Номенклатура").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Заполнение списка
For i = 1 To N
    Worksheets("Поступление").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value
Next
Worksheets("Поступление").Spk.ListIndex = -1
End Sub

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

1
2
3
4
5

' Листинг 2.22. Процедура обработки щелчка на поле со списком
Private Sub Spk_Click()
Range("C5").Value = Worksheets(3).Cells(Spk.ListIndex + 2, 2).Value
Range("C6").Value = ""
End Sub

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

• Name — Pass;
• PasswordChar — *.

Это обеспечит отображение «звездочек» при наборе пароля (набранное пользователем слово будет скрыто от посторонних глаз). Таким образом, мы подошли к написанию процедуры для внесения информации о новом поступлении (увеличении числа указанных товаров и возможном изменении цены) на третий лист. В листинге 2.23 приведена процедура, выполняемая при щелчке на кнопке Внести. В качестве пароля здесь рассматривается комбинация 357.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

' Листинг 2.23. Обработка щелчка на кнопке Внести
Private Sub CommandButton1_Click()
If Pass.Text = "327" Then
' Обновление цены поступления
Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 2).Value = Range("C5").Value
' Коррекция количества
Col = Range("C6").Value
Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value = _
Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value + Col
    MsgBox "Данные внесены"
    Pass.Text = ""
Else
    MsgBox "Ошибка пароля! Данные не внесены"
End If
End Sub

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

Перейдем теперь к рассмотрению правой части (в отличие от левой, она выделяется более темным оттенком) рис. 2.17. Этот фрагмент листа необходимо использовать для внесения нового товара (с указанием количества). В листинге 2.24 приведена процедура, которая выполняет данное функциональное действие в этом случае.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

' Листинг 2.24. Обработка щелчка на кнопке Внести новый товар
Private Sub CommandButton_Click()
' Подсчет числа внесенных товаров на листе Номенклатура
N = 0
While Worksheets("Nomen").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Проверка пароля внесенного в правое текстовое поле
If Pass2.Text = "35791" Then
    Worksheets("Номенклатура").Cells(N + 2, 1).Value = Range("G3").Value
    Worksheets("Номенклатура").Cells(N + 2, 2).Value = Range("G4").Value
    Worksheets("Номенклатура").Cells(N + 2, 4).Value = Range("G5").Value
    MsgBox "Данные внесены"
    Pass2.Text = ""
Else
    MsgBox "Ошибка пароля! Данные не внесены"
End If
End Sub

В правой части листа расположены два элемента управления — кнопка и текстовое окно для пароля. Можно было бы оставить одно поле пароля для левой и для правой части листа. Однако предложенный здесь вариант повышает функциональность разработки. В качестве значения свойства Name мы выбрали Pass2. Сформулируем порядок действий пользователя при работе с правой частью листа:

• в ячейку G3 вводится название нового товара;
• в ячейке G4 указывается цена;
• ячейка G5 отводится для количества единиц товара;
• пользователь вводит пароль, и щелчок на кнопке Внести новый товар фиксирует (если пароль правильный) введенную информацию на листе Номенклатура.

В начале процедуры CommandButton2_Click подсчитывается (для этого используется переметшая N) количество ячеек с информацией о товарах на листе Номенклатура. После этого информация о новом товаре записывается в очередную свободную строку листа.

Теперь перейдем к другому листу — Отгрузка (рис. 2.18), который используется при продаже товаров. На листе Отгрузка расположено три элемента управления: поле со списком (Name — Spk), поле для пароля (Name — Pass3) и кнопка (Name — CommandButton1).

Рис. 2.18. Организация листа Отгрузка

Для заполнения списка необходимо скорректировать ранее (листинг 2.21) рассмотренную процедуру Workbook_Open. Теперь она должна выглядеть так, как показано в листинге 2.25.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

' Листинг 2.25. Процедура, выполняемая при открытии книги (вариант 2)
Private Sub Workbook_Open()
Worksheets("Поступление").Spk.Clear
Worksheets("Отгрузка").Spk.Clear
' Подсчет уже имеющихся товаров
N = 0
While Worksheets("nomen").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Заполнение списков
For i = 1 To N
    Worksheets("Поступление").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value
    Worksheets("Отгрузка").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value
Next
Worksheets("Поступление").Spk.ListIndex = -1
Worksheets("Отгрузка").Spk.ListIndex = -1
End Sub

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

Рис. 2.19. Оформление продажи на листе Отгрузка

1
2
3
4
5

' Листинг 2.26. Процедура, выполняемая при щелчке на поле со списком
Private Sub Spk_Click()
Range("E6").Value = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value
Range("E7").Value = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 3).Value
End Sub

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

' Листинг 2.27. Процедура, выполняемая при щелчке на кнопке Отгрузить
Private Sub CommandButton1_Click()
If Pass3.Text = "775" Then
    ColPrais = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value
    Col = Range("E6").Value
If Col > ColPrais Then
    MsgBox "Такого количества на складе нет"
Exit Sub
End If
Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 3).Value = Range("E7").Value
ColPrais = ColPrais - Col
Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value = ColPrais
    MsgBox "В базу внесена информация"
Pass.Text = ""
Spk_Click
Else
    MsgBox "Ошибка пароля!"
End If
End Sub

После внесения изменений информация на экране будет обновлена — пользователь увидит уже скорректированное значение (за счет вызова процедуры Spk_Click, которую мы ранее уже разработали). Теперь для обеспечения ранее оговоренной функциональности необходимо скрыть лист Номенклатура. Пользователи в этом случае будут использовать разработанные листы Поступление и Отгрузка.