Эта статья является логическим завершением цикла, посвященного автоматизации загрузки данных из SMS в программу учета личных финансов GnuCash.
Судя по нескончаемому (на самом деле — нет) потоку комментариев к предыдущей статье, тема, действительно, интересна не только мне, поэтому было бы безответственно не довести описание до конца.

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

Логика работы формул в столбцах:

• A «SMS»
• B «ДатаSMS»
• C «Направление»
• F «Категория»
• G «Сумма»

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

• D «Счет1»
  В столбце задаем как постоянное значение название счета, с которого мы осуществляем покупки и на который приходят средства, например, от з.п. Мне его удобнее всего обозначать как карта какого-то определенного банка. Этот столбец нам нужен, чтобы тем счетам, которые мы определили в результате парсинга SMS, было с чем корреспондировать (т.е. мы должны указать не только то, на что мы потратили, но и откуда мы на это взяли средства, или не только что у нас появились деньги на карте, но и откуда они у нас — проценты с депозитов, з.п. или еще что-то)
• E «Счет2»
  На основе типов расходов/доходов мы получаем обычным ВПРом названия соответствующих им счетов:
  =ЕСЛИ(ЕНД(ВПР([@Категория];Счета;2;0));"";ВПР([@Категория];Счета;2;0))
• Переходим к новому. Как мы с вами уже обсудили, на каждую имеющуюся строку необходимо получить две строки — берем данные исходной строки, группируем их на две части и одну из этих частей помещаем под другую. Как это сделать?
  В Excel есть функция АДРЕС, которая (что не удивительно) возвращает адрес ячейки, номер строки и столбца из которой мы зададим в этой функции. Однако адрес ячейки — это не тоже самое, что и ее значение, поэтому нам понадобиться еще одна функция ДВССЫЛ, аргументом которой как раз будет адрес, а возвращать она нам будет необходимое значение из заданной ячейки. Соответственно, нам необходимо в первых двух новых строках подряд ссылаться на данные первой строки, в следующих третьей и четвертой строке сослаться на данные второй исходной строки и так далее. В этом нам поможет пара вспомогательных столбцов:
  • H «Строка» с формулой:
    =СТРОКА([@Строка])
    Возвращает порядковый номер текущей строки
  • I «Строка источника»:
    =ОКРУГЛВНИЗ([@Строка]/2+1;0)
    Эта нехитрая формула, ссылающаяся на значение предыдущего столбца, позволяет нам при протягивании продублировать номер строки, на который мы будем ссылаться, столько раз, какое число у нас указано в знаменателе дроби, в нашем случае это 2. Благодаря этой формуле в двух строках подряд мы сможем ссылаться на одну и ту же исходную строку.
• J «Столбец источника для счета»
  Помня о том, что в табличке для импорта в GnuCash корреспондирующие счета одной проводки находятся в одном столбце один под другим в двух разных строках, а в исходном состоянии наоборот — в одной строке, но в разных столбцах, нам необходима формула, которая позволит для итоговых четных строк брать информацию из одного столбца, а для нечетных из другого:
  =ЕСЛИ(ЕЧЁТН([@Строка]);4;5)
  Здесь 4 и 5 — порядковые номера столбцов с корреспондирующими счетами.
• K «Дата»
  С этого столбца начинаются данные, которые мы будем непосредственно использовать для импорта.
  =ДВССЫЛ(АДРЕС([@[Строка источника]];2;1))
  Как видно, здесь мы, зная номер строки источника, который у нас для новых строк дублируется, и задав константой номер столбца, в котором лежит дата получения SMS, получаем дату проводки как для строки дебета, так и для строки кредита
• L «Номер»
  =ЕСЛИ(ЕЧЁТН([@Строка]);[@[Строка источника]];"")
  Задаем номер проводки, который должен стоять в первой из двух строк этой проводки. Этот номер необходим для правильного порядка проводок созданных в одну дату
• M «Описание»
  Как и номер, описание указывается только для первой строки проводки:
  =ЕСЛИ(ЕЧЁТН([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];6;1));"")
  Формула практически полностью повторяет предыдущую, кроме того, что здесь нам не достаточно просто порядкового номера, а необходимо конкретное значение из заданного столбца, но в той же строке
• N «Название счёта»
  Для названия счета будем использовать уже рассчитанные номера строки и столбца источника:
  =ДВССЫЛ(АДРЕС([@[Строка источника]];[@[Столбец источника для счета]];1))
• O «Дебет»
  Вот здесь уже поинтереснее. Ни для дебета, ни для кредита мы уже не можем утверждать, например, что дебет всегда во второй строке проводки, а кредит всегда в первой, т.к. это справедливо только для проводок списания с нашей банковской карты. Однако, чтобы по карте можно было тратить, туда должно что-то и поступать, и как раз для проводок поступления ситуация должна быть обратной, поэтому здесь формула посложнее:
  =ЕСЛИ(И(ЕНЕЧЁТ([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Списание");ДВССЫЛ(АДРЕС([@[Строка источника]];7;1));ЕСЛИ(И(ЕЧЁТН([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Поступление");ДВССЫЛ(АДРЕС([@[Строка источника]];7;1));""))
  В Excel для большей читаемости я также написал эту формулу так, чтобы было легче понять, какая часть формулы за что отвечает:
  
  
  
  Рассмотрим эту формулу по блокам:
  
  • Первое условие:
    И(ЕНЕЧЁТ([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Списание")
  • Если первое условие верно, возвращаем сумму проводки:
    ДВССЫЛ(АДРЕС([@[Строка источника]];7;1))
  • Второе условие:
    И(ЕЧЁТН([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Поступление")
  • Если второе условие верно, возвращаем сумму проводки:
    ДВССЫЛ(АДРЕС([@[Строка источника]];7;1))
  • Если оба условия не выполняются — пустое значение.
  И первое, и второе условие возвращают нам сумму проводки, которая и в том, и другом случае определяется одинаково.
  Первое условие проверяет:
  • является ли номер текущей строки нечетным (в нашем случае это каждая вторая строка проводок, где чаще всего находится счет расхода),
  • а рассматриваемая исходная проводка — проводкой списания,
  если это так, то именно в такой строке в столбце дебета указывается сумма.
  Второе условие проверяет:
  • является ли текущая строка, наоборот, четной (здесь у нас всегда стоит счет банковской карты),
  • а рассматриваемая исходная проводка — проводкой поступления,
  если это так, тогда в такой строке в столбце дебета указывается сумма, т.е. это условие справедливо для строк со счетом карты (повторюсь у нас такие строки четные), когда на карту что-то приходит (з.п., дебетовые проценты и т.п.), а не уходит.
• P «Кредит»
  =ЕСЛИ(И(ЕЧЁТН([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Списание");ДВССЫЛ(АДРЕС([@[Строка источника]];7;1));ЕСЛИ(И(ЕНЕЧЁТ([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Поступление");ДВССЫЛ(АДРЕС([@[Строка источника]];7;1));""))
  
  
  
  Здесь формула практически полностью повторяет формулу для дебета, кроме того, что здесь нам надо поменять местами функции ЕЧЁТН и ЕНЕЧЁТ, а цепочка рассуждений выстраивается полностью по аналогии с описанием к предыдущему столбцу, таким образом, мы получили все возможные комбинации условий, которые дают возможность понять, в какой строке и в каком столбце Дебета или Кредита указывать сумму проводки.
• Подробно останавливаться на том, что за аброкадабра в столбце Q, не буду, т.к. уже говорил об этом в статье об импорте проводок, скажу лишь, что достаточно скопировать значения этого столбца в текстовый файл, и приступить, собственно, к импорту проводок.

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