Движение денежных средств: работа с выписками других форматов

Если выписка выгружена в Excel, то получение данных для базы – процедура не сложная.

Начнем с карточки 51 счета. Как правило, выгруженные  таблицы имеют одинаковую структуру:

Дата расположена в ячейке B9. Ячейка B9 – это результат объединения пяти ячеек B9:B13. Объединение ячеек произошло в ходе выгрузки данных из 1 С в Excel, но это никак не отразится на работе с данными.

Следующая дата в ячейке B14, которая образована на месте пяти ячеек B14:B18. Ну и так далее. Каждый платеж имеет один и тот же шаблон.

Можно скопировать мой образец карточки:

11_02_Карточка 51 счета

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

 

Что приведет к такому результату:

 

Но если скопировать ссылки вниз, то получится не очень удобная для визуального восприятия картина:

Поэтому ссылки, с помощью которых мы получаем данные для вставки в базу, нуждаются в доработке.

Получаем дату в ячеке L9, при обработке даты нужно ввести такое условие:

=ЕСЛИ(B9=0;"";B9)

Если в ячейке B9 будет хоть какое-нибудь значение, то оно отобразится в нужной нам ячейке. А иначе будет видна пустота.

Кроме того, иногда при выгрузке данных из 1 С в Excel даты переносятся как текст, а не как число. Чтобы это проверить, я обычно выделяю две ячейки с датами, и если внизу вижу количество и сумму, значит, даты записаны числами, а если только количество, - значит, даты записаны как текст:

Аналогичные проверки нужно будет делать, когда из карточки будем получать суммы платежей.

В моей карточке даты – как числа, но если в какой-нибудь другой карточке окажется, что дата в виде текста, то нужно будет применить формулу ДАТАЗНАЧ:

=ЕСЛИ(B9=0;"";ДАТАЗНАЧ(B9))

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

 

После чего скопировать формулу до самых нижних значений карточки:

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

В базе данных, которую мы формировали в предыдущих статьях, платежный документ описан тремя признаками: название документа, дата документа, номер документа (столбцы 1, 2, 3 базы данных). Значит, нужно получить данные именно в таком виде: в виде трех ячеек на каждый платежный документ. В карточке 51 счета вся эта информация присутствует, но нужно выделить ее из записи.

Есть запись: «Платежное поручение исходящее 00011 от 11.01.2018 0:00:00». Она состоит из четырех частей: «Платежное поручение исходящее», «00011», «11.01.2018», «0:00:00». На месте первой части может быть и иная запись, например, «платежный ордер на списание денежных средств» или какой-либо другой документ.

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

Чтобы быстро найти все виды документов, я воспользуюсь фильтром. Для этого я ставлю курсор в ячейку A1 (первая строка при этом должна быть пустая, а если она не пустая, то нужно сделать вставку новой строки), после чего выделяю весь диапазон с данными (у меня это диапазон A1:J15056) и устанавливаю фильтр. После этого, открыв окно фильтра, я с помощью прокрутки визуально просматриваю весь перечень платежных документов и получаю результат: в моей карточке есть платежное поручение входящее, платежное поручение исходящее и платежный ордер на списание денежных средств:

Делается это достаточно быстро, даже если карточка содержит много заполненных строк с данными.

Я возвращаюсь к изначальной карточке, поскольку дальнейшие действия можно демонстрировать и на малом количестве транзакций. В карточке 51 счета я записываю названия платежных документов так:

после чего в ячейку M9 записываю такую формулу:

=ЕСЛИ($L9="";"";ЕСЛИ(ЕОШ(ПОИСК(M$7;$C9;1));"";ПСТР($C9;ПОИСК(M$7;$C9;1);ДЛСТР(M$7))))

Разберем по частям эту формулу.

Функция

=ПОИСК(M$7;$C9;1)

берет текст, который находится в ячейке M7 и выдает порядковый номер позиции знака, с которого начинается этот текст. В данном случае это будет #ЗНАЧ!, поскольку в ячейке C9 нет текста «Платежное поручение входящее».

Функция

=ДЛСТР(M$7)

измеряет, сколько знаков в тексте в ячейке M7.

Функция

=ПСТР($C9;ПОИСК(M$7;$C9;1);ДЛСТР(M$7))

вставляет из текста, расположенного в ячейке C9, столько знаков, сколько определено функцией ДЛСТР(M$7), и начиная с позиции, определенной функцией ПОИСК(M$7;$C9;1).

Если ПОИСК(M$7;$C9;1) выдает #ЗНАЧ!, то формула должна выдать пустую ячейку, и это описывается так:

=ЕСЛИ(ЕОШ(ПОИСК(M$7;$C9;1));"";ПСТР($C9;ПОИСК(M$7;$C9;1);ДЛСТР(M$7)))

И, наконец, если в ячейке L9 не стоит дата, то в результате должна быть пустая ячейка. В итоге получается приведенная выше формула, которая копируется на диапазон M9:O18 и выдает такой результат:

В столбец P пишем заголовок «Документ», после чего можно вывести либо такое название документа, которое мы получили (для этого делаем простую сцепку =M9&N9&O9), либо избавиться от лишних слов. Лишние слова – это «входящее», «исходящее», «списание» (это понятно из того, с плюсом платеж или с минусом), «денежные средства» (ни о чем другом, кроме денег, в базе данных речь не идет).

Я предпочитаю избавиться от лишних слов и запишу это так:

=ЕСЛИ(M9<>"";"Платежное поручение";ЕСЛИ(N9<>"";"Платежное поручение";ЕСЛИ(O9<>"";"Платежный ордер";0)))

Если в ячейке M9 не пусто, то формула выдает результат «Платежное поручение», в противном случае если в ячейке N9 не пусто, то формула выдает результат «Платежное поручение», в противном случае если в ячейке O9 не пусто, то формула выдает результат «Платежный ордер», в противном случае формула выдает 0:

Работая с выписками, я периодически делаю проверки, чтобы убедиться в правильной работе формул. В качестве примера покажу, как я проверяю, все ли нужные документы найдены. В данном случае я хочу проверить два момента: все ли документы получили свое название; нет ли двойных названий.

Для этого я напишу такие формулы:

Суть в том, что если в соответствующих ячейках есть названия документов, то формула ставит 1. Но в каждой строке должно быть только одно название документа, то есть сумма столбцов Q, R, S должна быть равна единице. Если сумма больше 1, то формулы нашли не один документ, а больше. Если сумма равна нулю, то документа вообще не нашли. Поэтому я записываю для столбца T такое условие, что в случае, если сумма столбцов Q, R, S равна 1, формула выдает ноль. В итоге если в ячейке T7 сумма всех результатов равна нулю, то все правильно. Кроме этого, введены условия для того, чтобы пустые строки не мешали делать проверку.

После окончания проверки столбцы Q, R, S, T можно удалить и продолжить работу с данными из карточки.

Далее в записи «Платежное поручение исходящее 00011 от 11.01.2018 0:00:00» мне нужно выделить «00011».

Первая цифра номера документа стоит на тридцать первой позиции в тексте «Платежное поручение исходящее 00011 от 11.01.2018 0:00:00». Мне нужно, чтобы формула автоматически определяла, начиная с какой позиции нужно вставлять текст. Это можно сделать так:

=ДЛСТР(M9&N9&O9)+1+1

Длина строка «Платежное поручение исходящее» равна 29, плюс еще один пробел – это 30 знаков. Номер платежного поручения начинается с тридцать первого знака, для чего прибавляем еще одну единичку.

Далее нужно понять, сколько знаков мне нужно вставить, начиная с позиции 31:

=ПОИСК(" от ";C9;1)-1-(ДЛСТР(M9&N9&O9)+1)

Текст « от » начинается с 36-го знака, значит, до него 35 знаков. От 35 знаков я отнимаю число знаков в тексте «Платежное поручение исходящее» и плюс один пробел. В итоге получаю такую формулу:

=ПСТР(C9;ДЛСТР(M9&N9&O9)+1+1;ПОИСК(" от ";C9;1)-1-(ДЛСТР(M9&N9&O9)+1))

И дополню ее условием, что если вместо даты в столбце L стоит пустота, то результат формулы равен нулю:

=ЕСЛИ(L9="";0;ПСТР(C9;ДЛСТР(M9&N9&O9)+1+1;ПОИСК(" от ";C9;1)-1-(ДЛСТР(M9&N9&O9)+1)))

Отмечу, что в моем случае между всеми частями исходного текста в ячейке C9 стоит по одному пробелу. Но бывает так, что пробел не единичный. В этом случае нужно либо сначала использовать функцию =СЖПРОБЕЛЫ(C9), либо при вводе в формулу корректирующих единичек учитывать, что пробел не одинарный.

По этому же принципу я пишу формулу для выделения даты. В этом случае использование функции ДАТАЗНАЧ обязательно, поскольку, когда дату выделяешь из текста, результат получается всегда в виде текста:

=ЕСЛИ(L9="";"";ДАТАЗНАЧ(ПСТР(C9;ПОИСК(" от ";C9;1)+4;10)))

Формула вставляет 10 знаков (11.01.2018 – это 10 знаков) начиная с позиции, которая определена как позиция начала текста " от " и плюс 4 знака самого текста " от ".

Не забываем присвоить ячейке формат даты, поскольку сразу после ввода формулы дата появится в виде числа 43 111.

Промежуточный результат обработки карточки:

 

Далее идет назначение платежа. В нем есть перенос текста в строке формул:

Обратите внимание: текст расположен в двух строках, но не потому, что в одной строке не хватило места, а потому, что был перенос в новую строку. Если вам когда-нибудь будет нужно сделать подобный перенос, то делается это с помощью Alt+Enter. Ну а пока нам нужно от этого переноса избавиться.

Можно применить к тексту функцию ПЕЧСИМВ, что в большинстве случаев решает проблему.

А можно сделать иначе. Сначала в ячейке S9 написать формулу

=ЕСЛИ(L9="";"";ПОДСТАВИТЬ(D9;"";" "))

Затем поставить курсор в ячейку D9; затем поместить мигающий курсор в строке формул сразу после последней точки в первой строке; затем нажать Shift; затем нажимать стрелку «вправо» до тех пор, пока курсор не остановится перед первым знаком во второй строке, как показано на рисунке:

После чего нажать Ctrl+C. Нужный кусок текста скопируется в буфер обмена. Потом нужно перейти в ячейку S9; поставить в строке формул мигающий курсор между первых кавычек в функции «ПОДСТАВИТЬ» и нажать Ctrl+V; после этого нажать Enter. В формулу вставится тот кусок текста, который ранее был скопирован в буфер обмена. Формула будет выглядеть так:

В результате ненужный кусок текста исчезнет, и с назначением платежа можно будет работать.

Контрагента, договор и банк выводим простыми ссылками, не забывая про условие, что если вместо даты стоит пустота, то значение должно быть нулевым:

=ЕСЛИ(L9="";0;D10)

И, наконец, для денежной суммы напишем так:

=ЕСЛИ(L9="";0;ЕСЛИ(F9=0;-H9;F9))

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

Далее копируем формулы вниз и получаем следующее:

Из карточки получено все, что можно. Далее вставляем в файл новый лист, называем его «Данные для базы» и вставляем те же заголовки столбцов, которые получились в листе с карточкой 51 счета. После чего выделяем в листе с карточкой (лист «Абрикос») диапазон L9:W18 и вставляем его как значения в лист «Данные для базы». На форматы ячеек можно не обращать внимания. Выделяем заголовки столбцов (диапазон A1:L1) и устанавливаем фильтр. Выделяем пустые строки:

 

нажимаем «OK», удаляем пустые строки и получаем следующее:

 

Далее открываем файл «Обработка выписки», который мы скачивали в одной из предыдущих статей. Из этого файла нужно скопировать лист «Вставка в базу»:

после чего из строки 3 формулы удалить и добавить справа несколько столбцов: «Валюта», «ЮЛ», «Тип источника», «Банк», а также столбец «Договор».

Простыми ссылками переносим данные из листа «Данные для базы» в лист «Вставка в базу». В столбце «Счет – источник» ставим наш номер счета, пусть это будет 40702810404257845789. Назовем наше юридическое лицо «Зебра», поставим название в соответствующую колонку. Валюта RUR, тип источника – Банк, название банка поставим ссылкой из карточки.

(Замечание! Прошу обратить внимание, что в карточке указаны банки «"3457", Приволжский» и «"0781", Уральский». Оба банка – «наши», в том смысле, что юридическое лицо «Зебра» имеет расчетные счета и в том, и в другом банке. И указанные в карточке 51 счета транзакции прошли по разным расчетным счетам. Однако в базе я на обе транзакции поставил один номер расчетного счета. Это условность, которая может быть только в рамках примера. В действительности если банки разные, то и расчетные счета тоже разные.)

Скопируем получившуюся строку вниз, чтобы встали все значения из карточки.

Можете скачать и посмотреть, что получилось:

11_22_Карточка 51 счета

Далее нужно открыть файл «База» (его образец есть в статье «Движение денежных средств: начинаем формировать базу данных»), добавить справа пять столбцов, пронумеровать их, озаглавить («Валюта», «ЮЛ», «Тип источника», «Банк», «Договор»), задать форматы ячеек (эта процедура описана в той же самой статье), установить фильтр и как значения перенести данные из файла «Карточка 51 счета». Пустые ячейки заполнить нулями или тире, в зависимости от формата. Должно получиться так:

11_23_База

Мы разобрали, как получить данные для базы из карточки 51 счета.

Если приходится работать с выпиской, экспортированной из банк-клиента в формате Excel, тот это мало чем отличается от работы с карточкой 51 счета. Используются аналогичные ссылки, но с доработкой данных по обстоятельствам.

Но перед тем, как выгрузить из банк-клиента выписку, попробуйте в настройках самого банк-клиента сделать так, чтобы получить максимальный перечень признаков платежей и их значений – так будет легче работать в дальнейшем. Теоретически настройки должны позволять получить те же данные, которые попадают в выписку в формате .txt.

Если придется работать с выпиской в формате .rtf или .docx, то в файле будет таблица, которая копируется в Excel. Но если ее скопировать напрямую, через Ctrl+V, то номера банковских счетов отобразятся неправильно.

Разберем, как это происходит. Скачайте образец выписки:

11_24_Образец выписки в формате rtf

Откройте файл, нажмите Ctrl+A, затем Ctrl+C, затем откройте новый лист Excel и нажмите Ctrl+V. Получится так:

Как видно, БИК и номер банковского счета скопировались неправильно.

Поэтому нужно после открытия нового листа Excel сначала выделить предполагаемую область вставки, можно с запасом:

Задать во всей этой области формат ячеек «Текстовый» и только после этого зайти в документ .rtf, нажать Ctrl+A, затем Ctrl+C. После этого нужно перейти в файл Excel с ячейками в текстовом формате, поставить курсор в ячейку A1, нажать правую клавишу мыши, выбрать «Специальная вставка», после чего появится окно, в котором нужно выбрать «Текст» и нажать OK:

 

В этом случае и БИК, и номер счета, и все остальные данные встанут как надо:

 

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *