Организация импорта данных из «1С»

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

Первый способ – вводить данные вручную. Но в случае, когда в компании используется какая-либо бухгалтерская программа (чаще всего это «1С: Бухгалтерия»), ручной ввод информации в реестр будет не лучшим выходом из положения.

Второй способ – придумать некий алгоритм ввода данных. Поскольку бухгалтерия ежедневно загружает данные из систем «клиент-банк» в «1С», нам необходимо каким-то образом организовать извлечение этих данных из «1С» и загрузку в реестр хозяйственных операций.

Конечно, удобнее всего было бы сразу выгружать нужные нам данные из «1С» в формате реестра хозяйственных операций. Но, раз мы решили обустраивать управленческий учет в Excel, такой возможности у нас, скорее всего, нет (иначе мы уже, без всяких сомнений, обустроили бы его в «1С»).

Поэтому воспользуемся одним из стандартных механизмов «1С», который наилучшим образом подойдет для этой цели. Таким механизмом является отчет «1С» под названием «Карточка счета».

Все рублевые безналичные транзакции отражаются в бухгалтерском учете на счете 51. Соответственно, алгоритм импорта данных из «1С» может выглядеть так.

1. Формирование карточки счета 51 за интересующий нас период времени в базе «1С».

2. Сохранение сформированной карточки счета на диске в формате Excel.

3. Открытие файла карточки в Excel и формирование на ее основе реестра хозяйственных операций с помощью макроса.

Карточка счета 51, выгруженная в Excel, выглядит примерно так, как показано на рис. 1 – возможны некоторые отличия, которые связаны с другой версией бухгалтерской программы. Но эти отличия совершенно несущественны для нашего изложения.

Рисунок 1. Карточка счета 51, выгруженная в Excel

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

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

• каждая строка соответствует одной транзакции (поступлению или платежу денежных средств);
• каждый столбец соответствует тому или иному реквизиту данной транзакции.

Основные отличия карточки счета 51 от реестра транзакций состоят в следующем:

• в некоторых реквизитах транзакции (например, Аналитика Дт и Аналитика Кт) содержится не один аналитический признак, а сразу несколько (разделенных символами перевода строки), что несколько усложняет конверсию карточки счета 51 в реестр хозяйственных операций;
• в карточке содержится ряд реквизитов, которые нам в реестре не нужны, в частности, дата и номер документа движения, счета дебета и кредита и т.д.

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

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

Sub RegMake()
Dim SheetIn, SheetOut As Worksheet
Dim S, S1, A1, A2, A3, B1, B2, B3, C1, C2, C3 As String
Dim K, BrakePos, BrakePos1, BrakePos2 As Integer

Set SheetIn = ActiveWorkbook.Worksheets("Карточка 51 счёта")
Set SheetOut = ActiveWorkbook.Worksheets("Реестр операций")

SheetOut.Range("A2:L10000").ClearContents

K = 9

Do While SheetIn.Cells(K, 1).Value <> ""

SheetOut.Cells(K + 1, 1).Value = K - 8
SheetOut.Cells(K + 1, 2).Value = CDate(SheetIn.Cells(K, 1))
SheetOut.Cells(K + 1, 13).Value = Month(CDate(SheetIn.Cells(K, 1)))
If SheetIn.Cells(K, 6).Value = "51" Then
SheetOut.Cells(K + 1, 3).Value = - (-Format(CSng(SheetIn.Cells(K,7).Value), "# ##0.00"))
SheetOut.Cells(K + 1, 10).Value = - (-Format(CSng(SheetIn.Cells(K,7).Value), "# ##0.00"))
Else
SheetOut.Cells(K + 1, 3).Value =-Format(CSng(SheetIn.Cells(K, 10).Value), "# ##0.00")
SheetOut.Cells(K + 1, 10).Value =-Format(CSng(SheetIn.Cells(K, 10).Value), "# ##0.00")

End If

SheetOut.Cells(K + 1, 4).Value = "Основной"
SheetOut.Cells(K + 1, 11).Value = "RUR"
SheetOut.Cells(K + 1, 9).Value = "Д" & SheetIn.Cells(K, 6).
Value & "К" & SheetIn.Cells(K, 9).Value

A1 = ""
A2 = ""
A3 = ""
B1 = ""
B2 = ""
B3 = ""
C1 = ""
C2 = ""
3 = ""

S = SheetIn.Cells(K, 2).Value
BrakePos = InStr(S, Chr(10))
If BrakePos <> 0 Then
A1 = Left(S, BrakePos - 1)
A2 = Right(S, Len(S) - BrakePos)
End If

S = SheetIn.Cells(K, 4).Value
BrakePos = InStr(S, Chr(10))

If BrakePos = 0 Then
B1 = S
Else
B1 = Left(S, BrakePos - 1)
S = Right(S, Len(S) - BrakePos)
BrakePos = InStr(S, Chr(10))
If BrakePos = 0 Then
B2 = S
Else
B2 = Left(S, BrakePos - 1)
B3 = Right(S, Len(S) - BrakePos)
End If
End If
S = SheetIn.Cells(K, 5).Value
BrakePos = InStr(S, Chr(10))
If BrakePos = 0 Then
C1 = S
Else
C1 = Left(S, BrakePos - 1)
S = Right(S, Len(S) - BrakePos)
BrakePos = InStr(S, Chr(10))
If BrakePos = 0 Then
C2 = S
Else
C2 = Left(S, BrakePos - 1)
C3 = Right(S, Len(S) - BrakePos)
End If
End If

SheetOut.Cells(K + 1, 8).Value = A2
If SheetIn.Cells(K, 6).Value = "51" Then
SheetOut.Cells(K + 1, 6).Value = B2
SheetOut.Cells(K + 1, 7).Value = C1
Else
SheetOut.Cells(K + 1, 6).Value = C2
SheetOut.Cells(K + 1, 7).Value = B1
End If

K = K + 1

Loop

SheetOut.Rows("2:9").Delete Shift:=xlUp
MsgBox ("Реестр банковских операций сформирован.")

End Sub

Вкратце алгоритм работы макроса можно описать так:

• сканирует все строки карточки счета, расположенной на листе «Карточка счета 51»;
• на основе данных из карточки формирует реестр хозяйственных операций, который размещает на листе «Реестр операций».

Формат результирующего реестра приведен на рис. 2.

Рисунок 2. Реестр хозяйственных операций, сформированный макросом

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

Кстати говоря, такой подход можно применить для импорта из «1С» не только транзакций поступлений и платежей денежных средств, но и любых других, которые должны присутствовать в нашем реестре хозяйственных операций. Например, выгружая карточки по затратным счетам (20, 25, 26, 44 и т.д.), можно без особых усилий добиться автоматического формирования в Excel операций начисления затрат, выгрузка карточки счета 01 позволит автоматизировать управленческий учет основных