Как с помощью excel преобразовать карточку счета в реестр хозяйственных операций

Обновлено: 07.07.2024

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

Первый способ – вводить данные вручную. Но в случае, когда в компании используется какая-либо бухгалтерская программа (чаще всего это «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("Реестр операций")

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

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

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

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

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

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

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

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

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

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

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

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

Преобразование карточки счета (часть 2): программа EXTRACT

Программы ACCOUNT и EXTRACT - это как А и Б: сказав одно, нельзя не сказать другое. EXTRACT - это второй этап (наиболее творческий для пользователя), на котром собственно и происходит формирование плоской таблицы из карточки счета.

Для начала вспомним, как работает программа ACCOUNT. Схематично принцип ее действия можно представить так:

Таким образом, ACCOUNT преобразует отчет "Карточка счета" в плоскую таблицу. Обратите внимание на атрибут "Операции". Это сложный атрибут, в котором смешано несколько разных субконто. Причем состав и порядок субконто зависит от состава корреспондирующих счетов в проводке. Программа EXTRACT должна извлекать эти субконто из столбца "Операции" в отдельные, дополнительные столбцы. Для этого производится отбор определенного подмножества проводок с одинаковыми корреспондирующими счетами (просто ставится фильтр по атрибутам "Дебет" и "Кредит") и из этого подмножества с помощью функции EXTRACT производится извлечение отдельных субконто в дополнительные столбцы справа (например, "Статья затрат", "Подразделение" и т.д.):

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

Теперь посмотрим, как это выглядит на практике.

Для начала вспомним, как выглядит плоская таблица после реализации функции ACCOUNT:

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

1) создаем правее столбца "Кол-во" два дополнительных столбца с соответствующими заголовками,
2) ставим автофильтр по столбцам "Дебет" и "Кредит" (в данном случае сделан отбор по дебету счета 26 и кредиту счета 71),
3) ставим курсор на любую ячейку в столбце, в который будет производится извлечение из столбца "операции" (в данном случае выделена ячейка в столбце 9, "Статья затрат")
4) нажимаем на кнопку EXTRACT на панели инструментов 1C_ANALYTICS
5) в появившемся диалоговом окне ставим номер субконто, которое мы хотим извлечь (в данном случае это второе субконто)

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

Далее мы повторяем то же действие, пока не обработаем все записи плоской таблицы и не извлечем из них соответствующие атрибуты "Статья затрат" и "Подразделение" в крайние правые столбцы. По завершении процесса мы можем свернуть сводную таблицу и получить нужные нам результаты, например, такие (здесь часть статей затрат и подразделение укрупнены и показаны как прочие статьи/подразделения - иначе сводная таблица просто не поместилась бы на экран):

Таким образом, мы можем получить сведения о том, какие суммы по каким статьям затрат и на какие подразделения были списаны в данном периоде. Кроме того, мы можем получить дополнительно и некоторые полезные расшифровка (для этого я также использовал функцию EXTRACT, заполнив с ее помощью еще один атрибут "Расшифровки", правее столбца "Подразделение").

Код программы EXTRACT (VBA):

' Определяем объектные переменные: активная книга, лист, ячейка
Set w = ActiveWorkbook
Set s1 = ActiveSheet
Set c = ActiveCell

Артур Гаджиев, экономист ЗАО ПО «Азимут», к.э.н.

Хафиз Гафуров, экономист ЗАО ПО «Азимут»

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

Как загрузить данные

В шаблоне объединены четыре рабочих листа Excel. Три предназначены для выгрузки и обработки карточек счетов. Четвертый лист – сам отчет.

Первое, что потребуется сделать, – выгрузить из «1С» в формате xls карточкисчетов 50«Касса»,51«Расчетные счета» и52«Валютные счета», например, за период с 1 января текущего года и до текущей даты. Данные первой карточки надо скопировать в шаблон на лист «Касса», второй – на лист «Расчетный счет», третьей – «Валютный счет».

Далее для каждой проводки надо задать статью отчета и месяц. Достаточно нажать ALT+F8, в появившемся списке выбрать EnterItemsAndMonths и кликнуть по кнопке «Выполнить». Автоматически статьи присвоятся тем проводкам, которые однозначно интерпретируются (см. таблицу).

Часть строк в карточках счетов программа пропустит. Это вполне оправданно. Ведь не каждой проводке можно однозначно поставить в соответствие статью отчета о движении денежных средств. Например, проводке Дебет 60.1 «Расчеты с поставщиками» – Кредит 51 «Расчетные счета» могут соответствовать статьи «Сырье и материалы», «Техническое обслуживание и ремонт», «ГСМ». Для таких случаев наименование поступлений и выплат потребуется выбрать самостоятельно из раскрывающегося списка. Исходные данные для этого списка перечислены в диапазоне M9:M48 на первых трех листах.

КСТАТИ. Если в вашей компании этих статей больше или для них предусмотрены другие названия, то в файл надо внести изменения. Подробная инструкция, как это сделать.

Как сформировать отчет

Отчет о движении денежных средств размещается на отдельном листе шаблона – «Cash flow». Чтобы перенести в него данные из карточек, надо снова нажать ALT+F8, но на этот раз выбрать Cash_flow. Программа распределит исходные данные в отчете по статьям поступления и выбытия денег.

Если кликнуть по названию месяца, слева от него появятся три вспомогательных столбца – «Касса», «Расчетный счет» и «Валютный счет». В них напротив статей, по которым в этом периоде было движение денежных средств, проставлены надписи «Банк. Выписка». Щелкнув по ним, можно провалиться в проводки и посмотреть, в результате каких операций сформировалась сумма по той или иной статье.

Перечень операций, которые Excel распределит сам

Подготовлено в сотрудничестве с редакцией журнала «Финансовый директор»

Для создания финансовой модели в Excel, а также план-фактного анализа часто требуется сбор информации из внешних для Excel источников. Рассмотрим на примере составления отчета о движении денежных средств (ОДДС) по данным, выгруженным из 1С.

ОДДС – это отчёт, в котором информация о движении денежных средств структурирована в соответствии со справочником статей БДДС и финансовой структурой компании (ЦФО). В данном примере для упрощения финансовая структура не рассматривается. Для формирования отчёта нужно пройти следующие этапы:

экспорт данных о бухгалтерских проводках из 1С в Excel и их предварительная обработка;
присваивание каждой операции соответствующей статьи справочника ОДДС;
формирование сводной таблицы движения денежных средств с автоматическим подсчётом сумм платежей/поступлений по каждой статье.

На первом этапе необходимо сделать карточку счёта 51 «Расчётные счета» за нужный период и сохранить в формате xls (можете скачать файл примера kartochka-51, в нём уже сделан экспорт из 1С в Excel). Затем открыть этот файл, удалить в нём шапку и итоговые значения внизу:

Две правые колонки (сальдо) тоже можно удалить. Вместо шапки поставить свои заголовки.

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

Аналогичным образом заполняются остальные проводки.

В данном упрощённом примере используются следующие соответствия статей и проводок (если субсчёт не указан – к статье относятся проводки по всем субсчетам данного счёта):

Дебет проводки	Кредит проводки	Статья управленческого учёта
51	62	Поступления от покупателей
60	51	Платежи поставщикам
68.1	51	НДФЛ
68.2	51	НДС
68.4	51	Налог на прибыль
68.8	51	Налог на имущество
69	51	Отчисления в фонды
70	51	Зарплата
71	51	Подотчёт
76	51	Аренда
76	51	Членские взносы
76	51	Программы
76	51	Страховка
91	51	Расчётно-кассовое обслуживание

Проводка Д 76 К 51 может относиться к разным статьям управленческого учёта.

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

Теперь третий этап – формирование сводной таблицы. Нужно выбрать всю заполненную таблицу (горячая клавиша Ctrl-A), меню Вставка – Сводная таблица, в открывшемся окне проверить, что выбрано На новый лист:

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

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

Сначала нужно добавить в отчёт информацию о датах. Отметьте в окне Список полей сводной таблицы поле Дата и переместите соответствующее поле внизу из окошка Названия строк в окошко Названия столбцов. Получится большая сводная таблица, в которой каждому столбцу соответствует день c поступлениями или платежами:

Теперь можно группировать данные по месяцам. Щёлкните правой кнопкой мыши по любой дате, в контекстном меню выберите Группировать… В открывшемся окне выберите Месяцы.

Читайте также:

Как с помощью excel преобразовать карточку счета в реестр хозяйственных операций

Войти

Преобразование карточки счета (часть 2): программа EXTRACT