Рассмотрим, как работают формулы при переносе данных из выписки в базу. Начинаем с файла «Обработка выписки», лист «Обработка выписки», столбец C. Этот файл был получен в результат работы с выпиской, но, если это необходимо, его можно скачать:
и сохранить в формате .xlsb.
В столбце C находится такая формула:
=ЕСЛИ(ЕОШ(ЛЕВСИМВ(A4;НАЙТИ("=";A4;1)-1));0;ЛЕВСИМВ(A4;НАЙТИ("=";A4;1)-1))
В столбце A находятся записи, которые попали туда из банковской выписки. Они имеют такой вид: ВерсияФормата=1.00, Кодировка=Windows и т.д. Нам нужно отделить ту часть, которая до знака «=», от той части, которая после знака «=». Часть до «=» я буду называть признаком. Каждая платежная транзакция имеет некоторое количество признаков: контрагент, дата, сумма, назначение и т.д. Вот эти признаки и нужно сейчас выделить.
Возьмем для примера любую ячейку, пусть это будет A21, в которой написано «КонечныйОстаток=318201.01».
Если в ячейке C21 написать =НАЙТИ("=";A21;1), то формула выдаст значение 16. Это значит, что знак «=» находится на 16-м месте, если считать от первого знака слева. Формула ЛЕВСИМВ смотрит на ячейку A21 и выдает те знаки, которые ограничены числом 16: если мы запишем =ЛЕВСИМВ(A21;16), то получим значение «КонечныйОстаток=». Но, во-первых, число 16 не постоянно, оно в каждой строке свое, в зависимости от того, сколько знаков имеет признак и на каком месте находится знак «=». А во-вторых, нам нужно получить запись без «=» в конце, поэтому нам нужно знаков 16 минус 1. Поэтому запишем так:
=ЛЕВСИМВ(A21;НАЙТИ("=";A21;1)-1)
что выдает нам нужный результат:
КонечныйОстаток
Однако не все ячейки в столбце A содержат знак «=». Например, A4, A14 не содержат его. (Эти ячейки вообще нам не нужны, и задача в том, чтобы формулы с ними не работали.) Поэтому формула в таком виде, не найдя «=», будет выдавать такой результат: «#ЗНАЧ!». Если к результату в ячейке A14 применить формулу ЕОШ, вот так:
=ЕОШ(ЛЕВСИМВ(A14;НАЙТИ("=";A14;1)-1))
то в ячейке появится результат ИСТИНА, что означает, что формула обнаружила ошибочное значение и сообщила об этом. А дальше нам нужно записать, что если формула нашла ошибку, то нужно поставить ноль, а если не нашла ошибку, то она должна показать результат в виде признака. И в итоге получается та самая формула, которая и стоит в ячейке.
Теперь о простом способе делать формулы для расчетов. Сделаем рассмотренную нами формулу, двигаясь от простого к сложному. Скопируем в чистый лист Excel несколько записей из столбца A. И будем поэтапно решать стоящие перед нами задачи. В одной ячейке найдем позицию знака «=», а в следующей ячейке отделим символы слева до знака «=». Скопируем формулы до конца списка. Получится так (показаны формулы, а не результаты вычислений):
Затем ставим курсор в строку формул и выделяем формулу, но без знака «=»:
Нажимаем Ctrl+C, переходим в соседнюю ячейку справа, курсором в строке формул выделяем B1:
И нажимаем Ctrl+V. Формула «НАЙТИ("=";A1;1)» из предыдущей ячейки вставилась в итоговую формулу вместо «B1»:
И нажимаем Enter, после чего копируем формулу до конца списка. Столбец B после этого можно удалить. В результате получаем такой результат:
Правее пишем формулу, что если значение ошибочное, то результат вычислений нулевой, а если ошибки нет, то ставится сам этот результат:
После чего вместо B1 в этой формуле нужно вставить формулу из столбца B (в двух местах):
Далее нажать Enter, скопировать формулу до конца списка и удалить столбец B. Мы получили нужный результат:
Именно так работает формула в столбце C файла «Обработка выписки», но с той разницей, что в ячейках нулевые значения видно как тире (это настройка формата ячеек).
Важно! Пока вы обрабатываете информацию с помощью формул, вы не можете ставить текстовый формат ячейки: если формат ячейки «Текстовый», то все, что вы пишете, Excel воспринимает как текст, и формулы не работают. Но не нужно беспокоиться насчет номеров банковских счетов: в той ячейке, в которой работала формула, они отображаются корректно, даже если формат «Общий».
Возвращаемся к файлу «Обработка выписки», лист «Обработка выписки». В столбце B осуществляется нумерация платежных документов. Если в ячейке справа (столбец C) формула видит запись «СекцияДокумент», то это означает, что началось новое платежное поручение. И формула присваивает ему номер на один больше, чем у предыдущего платежного поручения. Если же записи «СекцияДокумент» нет, то по текущему документу информация еще не закончилась, и номер остается тот же. В итоге все платежные поручения пронумерованы от 1 и выше.
Далее в столбце D значения столбцов B и C (номер платежного документа и признак) сцепляются, что делает уникальным каждый признак. Например, каждый документ содержит данные о сумме. Но сумма первого платежа описана как «1Сумма», сумма второго платежа – «2Сумма» и т.д.
В столбце E выводится значение для каждого признака, а именно: чему равна сумма, какое название контрагента и т.д. Работает это так: изначально в столбце A каждая запись имеет вид «Признак=Значение». Сейчас нам нужно выделить значение. Это значит, что нужно справа отсчитать количество знаков, равное количеству знаков исходного текста минус количество знаков признака минус «=» (то есть минус еще один знак).
=ДЛСТР(A11) – показывает, сколько знаков в исходном тексте.
=ДЛСТР(C11) – показывает, сколько знаков в признаке, который мы выделили ранее.
=ДЛСТР(A11)-ДЛСТР(C11)-1 – показывает количество знаков значения (после «=»).
Далее с помощью ПРАВСИМВ мы берем полученное количество знаков справа:
=ПРАВСИМВ(A11;ДЛСТР(A11)-ДЛСТР(C11)-1)
и получаем искомое значение.
Ну и вводим условие, что если формула выдает ошибку (то есть признак в столбце C был равен нулю), то и здесь тоже ставится ноль. В итоге:
=ЕСЛИ(C11=0;0;ПРАВСИМВ(A11;ДЛСТР(A11)-ДЛСТР(C11)-1))
Вся остальная область с формулами F4:AV744 содержит одну и ту же формулу, которая выводит нужные нам значения в нужные столбцы. Разберем ее на примере ячейки H23.
За показ нужных данных отвечает формула
=ВПР($B23&H$3;$D:$E;2;ЛОЖЬ)
В строке 3, в диапазоне F3:AV3, содержатся те же самые признаки, которые получены в столбце C. Но там содержатся, во-первых, не все записи, а только те, которые относятся к платежному поручению. Во-вторых, это уникальные записи признаков, то есть они не повторяются. И в-третьих, в нашем примере в выписке некоторых признаков может не быть (например, «Код», «Плательщик»), но в выписках по другим банковским счетам они встречаются. Другими словами, в диапазоне F3:AV3 находится полный перечень признаков, которые могут быть вообще.
Сцепка $B23&H$3 означает, что мы соединили номер, присвоенный нами каждому платежному поручению в столбце B, и признак из строки 3. Поскольку нам нужно, чтобы в каждой ячейке была сцепка всегда строки 3 и всегда столбца B, то это закреплено значком «$». В результате в любой ячейке сцепляются признак из строки 3, который находится в том же столбце, в котором сейчас курсор, и номер платежного поручения из столбца B, который находится в той строке, в которой сейчас курсор.
Далее формула смотрит на диапазон $D:$E, которой представляет из себя два столбца D и E. В первом столбце (D) формула находит точное соответствие результату сцепки $B23&H$3. Результат этой сцепки – «1Дата». Точное соответствие находится в ячейке D25. И дальше формула смотрит на второй столбец диапазона $D:$E (потому что после $D:$E стоит точка с запятой, после чего идет цифра 2 – значит, второй столбец диапазона) и выдает значение этого столбца. Значение столбца выдается из той строки, в которой формула нашла «1Дата», то есть в нашем случае это стока 25. Значение ячейки E25 равно 01.07.2011, его и выдала формула.
Далее нужно сделать так, чтобы в ячейки не вставали записи «#Н/Д». (Такая запись появится, если функция ВПР чего-то не найдет. Например: а столбце O есть признак «Плательщик», а в выписке его нет, и функция ВПР будет выдавать «#Н/Д».) Это делается с помощью функции ЕНД. Если в результате работы формулы получается значение «#Н/Д», то в ячейке должна появиться пустота (пустота записывается как «""» - два раза кавычки). Если же в результате получается нормальное значение, то работает сама формула ВПР. Выглядит это так:
=ЕСЛИ(ЕНД(ВПР($B23&J$3;$D:$E;2;ЛОЖЬ));"";ВПР($B23&J$3;$D:$E;2;ЛОЖЬ))
Почему пустота, а не нолики? На данном этапе обработки мне комфортно видеть пустоты, а когда придет время избавляться от пустых ячеек, то нужные мне знаки и цифры я поставлю в самой базе.
Следующая задача: мне нужно, чтобы все искомые значения вытянулись в строки, по одному платежному поручению в каждой строке (одно платежное поручение – одна строка базы), поэтому я ввожу формулу, что если в столбце C есть запись «СекцияДокумент» (а эта запись означает, что началось новое платежное поручение), то формула выдает значение, а если такой записи нет – пустоту. Итоговая формула выглядит так:
=ЕСЛИ($C23="СекцияДокумент";ЕСЛИ(ЕНД(ВПР($B23&H$3;$D:$E;2;ЛОЖЬ));"";ВПР($B23&H$3;$D:$E;2;ЛОЖЬ));"")
Результат: все значения попали в те колонки, в которых находятся признаки этих значений, и данные по каждому платежному поручению находятся в одной строке.
Переходим в лист «Данные для базы».
Формулы начинаются в области, окрашенной голубым цветом. В ячейку AR5 должна попасть дата платежного поручения. Поскольку в исходном варианте дата записана в виде текста (в этом листе это ячейка C5), а мне все даты нужны в виде чисел, то преобразование делается с помощью формулы ДАТАЗНАЧ. Но не каждый платежный документ в исходной выписке содержит дату документа, поэтому, в случае, если появится ошибка в виде «#ЗНАЧ!», результат обработки должен быть ноль. Получается формула:
=ЕСЛИ(ЕОШ(ДАТАЗНАЧ(C5));0;ДАТАЗНАЧ(C5))
В столбце AS ситуация сложнее. Там находится дата транзакции. Платежи бывают входящими (когда нам платили) или исходящими (когда платили мы). В зависимости от этого, нужная дата находится либо в столбце I «ДатаСписано», либо в столбце T «ДатаПоступило». Чтобы понять, из какого из этих столбцов брать дату, нужно смотреть, кем мы были в данной транзакции: плательщиком или получателем. А для этого мы смотрим, в каком столбце находится наш расчетный счет: H или S. Поэтому делаем следующее: сравниваем данные столбца H с данными столбца BE (а в столбце BE записан наш счет), и если они равны, то дата транзакции находится в столбце I. Выглядит это так:
=ЕСЛИ(H5=BE5;ДАТАЗНАЧ(I5))
Однако если формула не найдет в столбце H наш расчетный счет, то нужная дата не встанет. Поэтому начинает работать следующее условие: если счет получателя из столбца S равен нашему расчетному счету из столбца BE, то формула ставит дату поступления денег из столбца T.
=ЕСЛИ(S5=BE5;ДАТАЗНАЧ(T5))
Ну и если не выполнится ни одно из этих условий, то формула поставит ноль – в этом случае нужно будет разбираться, почему так произошло, хотя я на практике такого не встречал. Итоговая формула выглядит так:
=ЕСЛИ(H5=BE5;ДАТАЗНАЧ(I5);ЕСЛИ(S5=BE5;ДАТАЗНАЧ(T5);0))
По этому же принципу формулы ставят значения и в остальные столбцы. Исключение составляет столбец BC с суммами, поскольку суммы, пришедшие из выписки, нуждаются в преобразованиях.
Рассмотрим, как получить нужное значение на примере ячейки BC5. Изначально сумма находится в ячейке D5, она имеет в качестве разделителя точку. Точка меняется на запятую с помощью функции =ПОДСТАВИТЬ(D5;".";","). Получившееся значение имеет формат текста, поэтому нужно преобразить его в числе с помощью функции ЗНАЧЕН:
=ЗНАЧЕН(ПОДСТАВИТЬ(D5;".";","))
Далее с помощью функции ABS я получаю модуль числа. Дело в том, что мне попадались выписки, в которых суммы были со знаком минус. Поэтому для дальнейшей обработки минусы убираем:
=ABS(ЗНАЧЕН(ПОДСТАВИТЬ(D5;".";",")))
После чего работает все тот же принцип: если счет плательщика равен нашему счету, то это выбытие денежных средств. Сумма появится с минусом. Если счет получателя равен нашему, то это поступление, его мы ставим с плюсом:
=ЕСЛИ(H5=BE5;-ABS(ЗНАЧЕН(ПОДСТАВИТЬ(D5;".";",")));ЕСЛИ(S5=BE5;ABS(ЗНАЧЕН(ПОДСТАВИТЬ(D5;".";",")));0))
Если ни одно из условий не выполняется, то появляется ноль, но на практике мне такое не встречалось.
В лист «Вставка в базу» копируются данные из листа «Данные для базы», но только те данные, которые нужны для вставки в базу, без лишних столбцов. При этом данные расположены в удобном для меня порядке.
Если вернуться в лист «Данные для базы», то можно в строке 4 увидеть неокрашенные ячейки. Это данные, которые ни в каком виде в базу не попадают: ни напрямую, ни после обработки.
Данные из столбцов:
КвитанцияДата; КвитанцияВремя; КвитанцияСодержание; ВидПлатежа; ВидОплаты; Очередность; Код; СрокПлатежа
я в своей практике никогда не использовал, поэтому в базу их не переношу. Что касается столбцов «ПлательщикРасчСчет» и «ПолучательРасчСчет», то это номер корсчета банка плательщика и получателя соответственно в случае непрямых расчетов. Это межбанковские вопросы, напрямую они не касаются работы наших юридических лиц.
Впрочем, не исключено, что данные из перечисленных выше столбцов могут для кого-то иметь значение. В этом случае можно перенастроить файл «Обработка выписки», руководствуясь принципами, которые содержатся в предыдущих статьях.
О форматах. В листе «Обработка выписки» я для всех ячеек использую формат «Общий», кроме столбцов B, C, D, E, в которых использую формат
# ##0;[Красный](# ##0);-
хотя ничего не поменяется, если и в них использовать формат «Общий».
В листе «Данные для базы» в столбцах от A до AQ используется общий формат. В эти столбцы данные попадают как значения, и с ними ничего плохого не происходит: номера счетов, БИК, ИНН, - все копируется, как нужно.
Однако, при таких переносах данных, с большим вниманием нужно относиться вот к чему. В лист «Данные для базы» информация попала с помощью «Специальная вставка» и далее вставки значений. Это дало корректное отображение данных в ячейках. Но если поставить курсор на ячейку, например, H5 в листе «Данные для базы», потом мышкой кликнуть на строку формул, в результате чего курсор появится в строке формул (он будет мигать):
а затем нажать Enter, то номер счета сразу станет таким:
Но это особенность работы с Excel: нужно помнить, что непроизвольное попадание в ячейку может нарушить работу таблиц. В качестве альтернативы можно предложить создание макросов, и при обработке выписки не нужно будет работать с промежуточными таблицами, - результат сразу попадет в базу. Но это для продвинутых пользователей.
Надеюсь, разбор работы формул был и несложным, и понятным.