Как с помощью excel преобразовать карточку счета в реестр хозяйственных операций
Часто налоговая инспекция или аудиторская проверка требует от организации выгрузки в Excel данных по всем счетам по БУ и НУ за несколько лет. Если объем данных большой, то вручную сделать это трудоемко.
Предлагаемая обработка позволяет сделать автоматическую выгрузку типового отчета Карточка Счета в Excel:
- по любым счетам в разрезе БУ или НУ (счета надо раскомментировать в коде)
- по 55 счетам в разрезе валюты и первого субконто (Банковский счет)
- в коде надо задать период, за который надо формировать отчет в разрезе дней или в разрезе месяцев.
С помощью данной обработки были сформированы файлы Excel объемом более 100 МБ.
Условие: на Сервере приложений 1С должен быть установлен MS Excel.
По аналогии можно выгружать любые другие типовые отчеты (ОСВ, Анализ Субконто) в любом нужном разрезе.
ДополнительнаяОбработка_ВыгрузкаКарточкиСчетаВExcel_v1.epf проверена на БП 3.0.46.16
ДополнительнаяОбработка_ВыгрузкаКарточкиСчетаВExcel_v2.epf проверена на БП 3.0.70.25
Специальные предложения
Часто налоговая инспекция или аудиторская проверка требует от организации выгрузки в Excel данных по всем счетам по БУ и НУ за несколько лет
. из моего опыта, они еще требуют оборотки - гораздо сильнее(как "сводную-по всем счетам", так и "по каждому счету")
Обработка выгружает Карточки счета по всем счетам в выбранную папку.
Делать выбор отдельного счета не имеет смысла, т.к. сохранить эксель по отдельному счету можно прямо из типового отчета.
я посмотрю причину ошибка на новой версии БП
(11)
я пока очередной стартмани нарою, и эта устареет ;)
делитесь свежей версией уж ибо была куплена напрасно. а вот далее за версии с выборкой счетов и может даже однажды ОСВ, справедливо будет заплатить снова ;)
а ваще как то интересно,если у меня по одному счету за один месяц эксель получается под 50Мб и времени на это уходит не менее 30ти минут, боюсь по всем счетам будет нереально
все же бодрее было бы выбирать счет(счета) и не ждать до утра. как вариант например утром можно обнаружить ошибку, и продолжать динамить ждущих отчеты ;)
(16)
не знаю, про не публично.
но мне кажется, Вы можете сказать инфострату, что вот была ошибка, она исправлена и мне нужна новая версии и они должны давать возможность ее скачивать без доп. страмани.
Просмотры 14775
Загрузки 29
Рейтинг 4
Создание 23.08.16 17:33
Обновление 18.07.19 18:09
№ Публикации 544560
Кому Бухгалтер
Конфигурация 1С:Бухгалтерия 3.0
Операционная система Windows
Доступ к файлу Абонемент ($m)
Код открыт Да
См. также
Универсальный бухгалтерский отчет Промо
Стандартные отчеты на СКД из 1С:Бухгалтерия предприятия 8 КОРП, адаптированные для: 1С:Бухгалтерия предприятия 8, 1С:Бухгалтерия предприятия 8 для Украины, 1С:Бухгалтерия для Беларуси, 1С:Управление производственным предприятием, 1С:Управление производственным предприятием для Украины, 1С:Управление торговым предприятием для Украины.
1 стартмани
24.08.2009 27817 2462 bonv 173
Открыть Анализ субконто, ОСВ по счету из движений документа
Думаю, многие пользователи ловят себя на мысли, что при просмотре движений документа было бы неплохо посмотреть обороты по этому договору, по этой номенклатуре, да хотя бы просто "провалиться", чтобы посмотреть вложения и т.п. Однако в типовой конфигурации это сделать невозможно.
1 стартмани
Прайс-лист, прайс-заказ с картинками, с выгрузкой в Excel с подстановкой формул для вычисления итогов по сумме заказа для УТ 11.5
Доработка типовых отчетов "ПрайсЛист" (УТ 11.5) и "Прайс-лист (2.5)". Особенности: вывод колонок "Заказ, Сумма", выгрузка в Excel с подстановкой формул, отправка excel файла Прайс-Заказа на e-mail.
3 стартмани
26.11.2021 2270 24 shtinalex 35
Бухгалтерский баланс на СКД
Подключаемый внешний отчет для БП 3.0 - Бухгалтерский баланс на СКД.
10 стартмани
11.10.2021 2589 14 TimofeySin 6
Отчет ОСВ++ для 1С:Бухгалтерии 3.0 - расшифровка оборотов, добавление любого количества субконто без изменения конфигурации и не только Промо
Отчет ОСВ++, наряду с возможностями типовых отчетов ОСВ, ОСВ по счету, Карточка счета, Анализ субконто и Карточка субконто, позволяет: Консолидировать данные ОСВ нескольких организаций; Добавлять произвольное количество субконто - наряду со стандартными, в качестве субконто могут быть задействованы любые реквизиты и дополнительные сведения документов движения; Получать расшифровки оборотов по корреспондирующим счетам и субконто; Получать данные оборотов с расшифровкой по документам движения; Формировать сальдо по оборотным субконто; Выводить данные по счетам и аналитикам, обороты по которым в выбранном периоде равны нулю (типовой отчет "ОСВ по счету" в подобной ситуации данных не выводит). Внесение изменений в конфигурацию не требуется.
3 стартмани
22.03.2017 54133 380 TSSV 12
Оборотно-сальдовая ведомость из документа Требование-накладная
Обработка табличной части, позволяющая вызвать стандартный отчет ОСВ из Требования-накладной с отбором по номенклатуре из табличной части.
1 стартмани
27.11.2020 4758 6 CyberMax 0
Анализ вычета НДС по предоплате и восстановления НДС с выданного аванса
Помогает бухгалтеру проверить вычет НДС по предоплате и восстановленному НДС с выданного аванса. Отчет формируется по счетам 60.02, 62.02, 76.АВ, 76.ВА
1 стартмани
09.09.2020 4418 39 artemua 1
Оборотно-сальдовая ведомость по счету с группировкой по первому субконто
Аналог типового отчета Оборотно-сальдовая ведомость по счету из БП 2.0, но группировка делается вначале по первому субконто, а потом по счетам. Таким образом, например, если формировать по счету 62, то для Контрагента1 будут вначале все данные по всем счетам, затем по Контрагенту2.
1 стартмани
04.04.2020 9801 3 aldor188 3
Прайс-лист с картинками в EXCEL из 1С: УТ10, УПП, КА (с формой заказа) Промо
Обработка для формирования прайса в EXCEL с формой заказа. Выгрузка картинок, характеристик, нескольких цен, остатков. Расчет заказа прямо в прайсе.
9 стартмани
26.11.2015 49170 61 spy-83 21
Универсальный конструктор отчетов по бухгалтерским оборотам и остаткам
Универсальный конструктор отчетов на основе оборотов и остатков регистров бухгалтерии. Имеется возможность настройки бланка отчета. Гибкое оформление позволяет в том числе готовить финансовую отчетность на иностранных языках для иностранного руководства. Может работать в любой типовой конфигурации, поддерживающей бухгалтерские проводки, и не требует ее изменения или доработки. Поддерживает любое количество настроенных вариантов отчетов. Все варианты хранятся внутри базы и не потеряются при копировании базы в другое место (например, на ноутбук). Поддерживает работу с любым регистром бухгалтерии. В одном отчете можно использовать источники из разных регистров. В качестве показателей могут выступать любые ресурсы регистров бухгалтерии (не только "Сумма").
Facebook Если у вас не работает этот способ авторизации, сконвертируйте свой аккаунт по ссылке ВКонтакте Google RAMBLER&Co ID
Авторизуясь в LiveJournal с помощью стороннего сервиса вы принимаете условия Пользовательского соглашения LiveJournal
Преобразование карточки счета (часть 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
' Проверка обязательного состава (системных) столбцов плоской таблицы.
z1 = s1.Cells(1, c.Column).Value
z0 = s1.Cells(1, c.Column).Interior.ColorIndex
ctrl = True
For i = 1 To 10
Select Case i
Case 1: txt = "Год"
Case 2: txt = "Месяц"
Case 3: txt = "День"
Case 4: txt = "Документ"
Case 5: txt = "Операции"
Case 6: txt = "Сторона"
Case 7: txt = "Дебет"
Case 8: txt = "Кредит"
Case 9: txt = "Сумма"
Case 10: txt = "Кол-во"
End Select
If z1 = txt Then
ctrl = False
Exit For
End If
Next
If ctrl = False Then ' Нельзя извлекать значения в системные столбцы.
msg = "Столбец '" & txt & "' является системным - извлекать в него значение не рекомендуется!"
MsgBox msg, vbExclamation, ttl
Exit Sub
Else
If z1 = Empty Then s1.Cells(1, c.Column).Value = "(заглавие?)"
If Not z0 = 15 Then s1.Cells(1, c.Column).Interior.Color In dex = 15
' Поиск столбца "Операции" (обычно это 5-й столбец).
fcl = s1.Cells(1, 256).End(xlToLeft).Column
ctrl = False
For l = 1 To fcl
z1 = s1.Cells(1, l).Value
If z1 = "Операции" Then
ctrl = True
j = l
Exit For
End If
Next
If ctrl = False Then
msg = "На текущей странице не обнаружен системный столбец 'Операции'!"
MsgBox msf, vbExclamation, ttl
Exit Sub
Else
' Диалог с пользователем.
txt = "Введите номер строки, которую Вы хотите извлечь из столбца 'Операции'"
resp = InputBox(txt, ttl, 1)
On Error Resume Next
resp = CDbl (resp)
If Not IsNumeric(resp) Then
txt = "Вводимое значение должно быть числовым!"
MsgBox (txt)
Cancel = True
Exit Sub
End If
' Извлечение.
With s1
frw = .Cells(65536, 1).End(xlUp).Row
Set rng = Range(.Cells(2, 1), .Cells(frw, 1)).SpecialCells(xlCellTypeVisible)
For Each cls In rng
k = cls.Row
z1 = .Cells(k, j).Value
z2 = Split(z1, Chr(10)) ' Ключевая операция во всем алгоритме.
If resp - 1 .Cells(k, c.Column).Value = nmm
Next
End With
End If
End If
Facebook Если у вас не работает этот способ авторизации, сконвертируйте свой аккаунт по ссылке ВКонтакте Google RAMBLER&Co ID
Авторизуясь в LiveJournal с помощью стороннего сервиса вы принимаете условия Пользовательского соглашения LiveJournal
Преобразование карточки счета (часть 1): программа ACCOUNT
Карточка счета - очень важный бухгалтерский отчет, который может дать менеджеру очень много полезной информации. Главное преимущество карточки счета - это полнота информации. Например, карточка счета 20 или 26 содержит абсолютно все проводки, которые прошли через этот счет за данный период. Карточка счета по какому-нибудь контрагенту также содержит в себе абсолютно все проводки, где так или иначе фигурирует данный контрагент. Это дает уверенность, что мы получили из бухгалтерии все необходимые нам факты, которые дают исчерпывающий ответ на поставленный нами вопрос. Именно этим обстоятельством, на мой взгляд, объясняется востребованность программы ACCOUNT.
Однако за эти преимущества приходится платить. Обработать карточку счета в действительности гораздо сложнее, чем тот же журнал проводок. Причиной тому - различный состав и последовательность субконто в графе "Операции", которые меняются в зависимости от состава корреспондирующих счетов в данной проводке. Поэтому обработка карточки счета представляет собой комбинацию двух функций: ACCOUNT+EXTRACT. Первая функция преобразует данные карточки счета в формат плоской таблицы, а вторая производит последовательное извлечение субконто из стол,ца "Операции" в отдельно стоящие столбцы. Применение функции EXTRACT является наиболее творческим этапом обработки карточки счета и требует от пользователя хотя бы минимальных знаний в бухгалтерии и навыки владения автофильтром.
В этой статьей пойдет речь о функции ACCOUNT. О функции EXTRACT будет рассказано в следующей статье.
ФУНКЦИЯ ACCOUNT
ШАГ 1. ФОРМИРОВАНИЕ ОТЧЕТА "КАРТОЧКА СЧЕТА" В ФОРМАТЕ *.XLS
В программе 1С необходимо сформировать отчет "Карточка счета". Сохраните этот отчет на жестком диске, используя функцию "Сохранить как. ". Назовите сохраняемый файл, например, "Карточка.xls". Откройте файл в программе Excel:
ШАГ 2. ЗАПУСК ФУНКЦИИ "ACCOUNT"
Нажмите на панели инструментов кнопку "ACCOUNT":
Результатом выполнения функции станет создание новой рабочей книги, в которой автоматически будет создана новая плоская таблица следующего вида:
Таблица содержит в себе следующие данные:
1) Год, месяц и день - дата документа, разбитая на три составляющие;
2) Документ - название и номер;
3) Операции - содержание проводок, состоящее из наименования субконто корреспондирующих счетов;
4) Сторона: Д - если главный (26-й) счет корреспондирует по дебету, К - если главный (26-й) счет корреспондирует по кредиту;
5) Дебет, Кредит - номера корреспондирующих счетов;
6) Сумма, Кол-во - сумма проводки в стоиомостном и натуральном выражении (важно: если Сторона=Д, то сумма и кол-во > 0, если Сторона=К, то сумма и кол-во < 0).
Полученный результат является не конечным, а всего лишь промежуточным. Далее наступает время для использования функции EXTRACT.
Текст программы ACCOUNT (VBA):
' Объектные ссылки на активную книгу и активную страницу
Set w = ActiveWorkbook
Set s1 = ActiveSheet
Dim oSheet As Object
'проверка активной страницы Excel - действительно ли это карточка счета
z1 = s1.Cells(2, 1).Value
z2 = Mid(z1, 1, 14)
If Not z2 = "Карточка счета" Then
'ttl = "Easy BI tools"
txt = "Данная функция применяется только для работы с отчетом 1С 'Карточка счета'"
MsgBox txt, vbExclamation, ttl
Exit Sub
End If
'если проверка пройдена, то для начала создаем
'новую рабочую книгу с 1 рабочим листом Главная
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Set wb = ActiveWorkbook
Set s2 = ActiveSheet
s2.Name = "Главная"
Application.SheetsInNewWorkbook = 3
'Оформляем Главную страницу
With s2.Cells.Font
.Name = "Tahoma"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
s2.Cells.WrapText = False
With s2
' формируем шапку на Главной странице
For l = 1 To 10
Select Case l
Case 1: txt = "Год"
Case 2: txt = "Месяц"
Case 3: txt = "День"
Case 4: txt = "Документ"
Case 5: txt = "Операции"
Case 6: txt = "Сторона"
Case 7: txt = "Дебет"
Case 8: txt = "Кредит"
Case 9: txt = "Сумма"
Case 10: txt = "Кол-во"
End Select
.Cells(1, l).Value = txt
.Cells(1, l).Interior.ColorIndex = 15
Next
'закрепляем первую строку
.Cells(2, 1).Select
ActiveWindow.FreezePanes = True
'---------------------------------
' Подсчитываем количество строк в будущем массиве tml.
frw = s1.Cells(65536, 1).End(xlUp).Row 'количество строк на исходной странице (frw - от final row)
n = 0 ' здесь n - это размерность будущей плоской таблицы.
For k = 7 To frw ' перебор строк через одну на исходной странице
z1 = s1.Cells(k, 1).Value
' На всякий случай ставим условие выхода из цикла, чтобы строка карточки счета с записью
' "Обороты за период" не включалась в нашу плоскую таблицу.
If z1 = "Обороты за период" Then Exit For
If IsDate(z1) Then n = n + 1
Next
'---------------------------------
' Формируем массив tml размером n*10, где n - количество строк в будущей плоской таблице,
' а 10 - количство столбцов, или атрибутов в будущей плоской таблице.
ReDim tml(1 To n, 1 To 10)
'---------------------------------
' Заполняем массив tml данными.
frw = s1.Cells(65536, 1).End(xlUp).Row 'количество строк на исходной странице (frw - от final row)
i = 0 ' инициализация индекса массива tml (сейчас в массиве заполнено данными 0 строк)
For k = 7 To frw ' перебор строк через одну на исходной странице
z1 = s1.Cells(k, 1).Value
If z1 = "Обороты за период" Or k > frw Then Exit For
If IsDate(z1) Then
dt = s1.Cells(k, 1).Value ' Дата
dcc = s1.Cells(k, 2).Value 'Документ
opr = s1.Cells(k, 3).Value ' Операция
dbt = s1.Cells(k, 4).Value ' Дебет
crt = s1.Cells(k, 6).Value ' Кредит
' Блок Сторона/Сумма
z1 = s1.Cells(k, 5).Value
z2 = s1.Cells(k, 7).Value
If z2 = Empty Or z2 = " " Then
sdd = "Д"
smm = z1
qnt = s1.Cells(k + 1, 5).Value
Else
sdd = "К"
smm = -z2
qnt = s1.Cells(k + 1, 7).Value
End If
If qnt = " " Then qnt = Empty
' Вносим полученные на страницу "Главная"
i = i + 1
tml(i, 1) = Year(dt) ' Год
tml(i, 2) = Month(dt) ' Месяц
tml(i, 3) = Day(dt) ' День
tml(i, 4) = dcc ' Документ
tml(i, 5) = opr ' Операция
tml(i, 6) = sdd ' Сторона
tml(i, 7) = "'" & dbt ' Дебет
tml(i, 8) = "'" & crt ' Кредит
tml(i, 9) = smm ' Сумма
If Not qnt = Empty Then tml(i, 10) = qnt ' Кол-во
End If
Next
.Activate
.Range("A2").Resize(n, 10).Value = tml
В нашей учетной системе основным источником информации для управленческих отчетов является реестр хозяйственных операций. При этом для нормального функционирования системы необходимо, чтобы в этот реестр каким-то образом попадали исходные данные о транзакциях.
Первый способ – вводить данные вручную. Но в случае, когда в компании используется какая-либо бухгалтерская программа (чаще всего это «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 позволит автоматизировать управленческий учет основных
Общие условия выбора системы дренажа: Система дренажа выбирается в зависимости от характера защищаемого.
Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов (88‰).
Поперечные профили набережных и береговой полосы: На городских территориях берегоукрепление проектируют с учетом технических и экономических требований, но особое значение придают эстетическим.
© cyberpedia.su 2017-2020 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!
Для создания финансовой модели в Excel, а также план-фактного анализа часто требуется сбор информации из внешних для Excel источников. Рассмотрим на примере составления отчета о движении денежных средств (ОДДС) по данным, выгруженным из 1С.
ОДДС – это отчёт, в котором информация о движении денежных средств структурирована в соответствии со справочником статей БДДС и финансовой структурой компании (ЦФО). В данном примере для упрощения финансовая структура не рассматривается. Для формирования отчёта нужно пройти следующие этапы:
- экспорт данных о бухгалтерских проводках из 1С в Excel и их предварительная обработка;
- присваивание каждой операции соответствующей статьи справочника ОДДС;
- формирование сводной таблицы движения денежных средств с автоматическим подсчётом сумм платежей/поступлений по каждой статье.
На первом этапе необходимо сделать карточку счёта 51 «Расчётные счета» за нужный период и сохранить в формате xls (можете скачать файл примера kartochka-51, в нём уже сделан экспорт из 1С в Excel). Затем открыть этот файл, удалить в нём шапку и итоговые значения внизу:
Две правые колонки (сальдо) тоже можно удалить. Вместо шапки поставить свои заголовки.
На втором этапе каждой проводке присваивается значения статьи управленческого учёта из справочника. Когда записей много, эта работа должна быть максимально автоматизирована с помощью макросов, но в этом примере всё будет сделано вручную.
Добавьте столбец Статья. Включите фильтр (Меню Данные – область Сортировка и фильтр – кнопка Фильтр). Теперь, выбирая значения фильтров по столбцам Дебет и Кредит, можно заполнить столбец Статья. Например, поступления от заказчиков отражаются проводками Д 51 К 62.1 и Д 51 К 62.2. Чтобы отфильтровать соответствующие проводки, в столбце Дебет нужно выбрать в меню фильтра 51, а в фильтре столбца Кредит задать Текстовые фильтры…— Начинается с… — ввести 62, ОК. Теперь всем отфильтрованным проводкам нужно присвоить значение статьи. Во вторую сверху ячейку столбца Статья можно написать название статьи поступлений/затрат (Поступления от заказчиков) и «протянуть» мышью до конца таблицы.
Аналогичным образом заполняются остальные проводки.
В данном упрощённом примере используются следующие соответствия статей и проводок (если субсчёт не указан – к статье относятся проводки по всем субсчетам данного счёта):
Дебет проводки | Кредит проводки | Статья управленческого учёта |
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 может относиться к разным статьям управленческого учёта.
После разнесения проводок нужно проконтролировать, что обработаны все проводки: проверить отсутствие в фильтре столбца Статья «Пустые» в самом конце списка, если есть пустые ячейки – дозаполнить.
Добавьте столбец Сумма, в котором будeт формула для ячейки I2 — «=E2+G2».
Теперь третий этап – формирование сводной таблицы. Нужно выбрать всю заполненную таблицу (горячая клавиша Ctrl-A), меню Вставка – Сводная таблица, в открывшемся окне проверить, что выбрано На новый лист:
На новом листе справа откроется окно Список полей сводной таблицы. Отметьте галочками поля Статья, Сумма. Соответствующие поля попадут в области внизу этого окна. Выделите столбец В и задайте финансовый формат ячеек. Отчёт о движении денежных средств в Excel готов!
Пользуясь сводными таблицами, можно достаточно гибко структурировать данные. Рассмотрим, как можно сделать на основе этих же данных помесячный отчёт по поступлениям и платежам.
Сначала нужно добавить в отчёт информацию о датах. Отметьте в окне Список полей сводной таблицы поле Дата и переместите соответствующее поле внизу из окошка Названия строк в окошко Названия столбцов. Получится большая сводная таблица, в которой каждому столбцу соответствует день c поступлениями или платежами:
Теперь можно группировать данные по месяцам. Щёлкните правой кнопкой мыши по любой дате, в контекстном меню выберите Группировать… В открывшемся окне выберите Месяцы.
Читайте также: