В этой статье мы разберем, как обрабатывать выписку в формате .txt. Вот как она выглядит (начало выписки):
Если нужно, можно скачать мой вариант выписки:
Если выписка не скачивается, а открывается в браузере, нажмите правую клавишу мыши и выберите пункт «Сохранить ссылку как…».
Далее нужно скачать файл для обработки данных:
После того, как вы скачаете этот файл, его лучше сразу сохранить в формате .xlsb. Этот формат больше подходит для обработки больших массивов информации в Excel. Для этого открываете файл в Excel, нажимаете «Сохранить как» и в поле «Тип файла» выбираете «Двоичная книга Excel (*.xlsb)». Нажимаете «Сохранить».
Далее нужно открыть выписку в файле .txt и выделить все данные. Это можно сделать либо через меню «Правка – Выделить все», либо нажать Ctrl+A. Данные выделятся синим цветом, нажимаем Ctrl+C, и они копируются в буфер обмена.
После этого нужно открыть файл Excel «Обработка выписки», зайти на лист «Копия выписки», поставить курсор на ячейку A1 и нажать Ctrl+V. Вставятся данные, которые были в выписке. Смотрим, сколько заполненных строк получилось в списке. Для этого сначала нужно убедиться, что вы именно в конце списка. Я это делаю так: ставлю курсор на соседний столбец B, потом нажимаю Ctrl+↓ (стрелка вниз) и оказываюсь в конце листа Excel. Потом стрелкой влево перемещаю курсор на столбец A, нажимаю Ctrl+↑ (стрелка вверх) и оказываюсь в конце списка с данными. Обычно в конце списка с данными в последней строке написано «КонецФайла».
Поясню, для чего я это делаю. В выписке в формате .txt между документами иногда бывают разрывы, и в Excel они попадут в виде пустых строк. И если в дальнейшем вы будете выделять массив информации при помощи Ctrl+Shift+↓, то рискуете выделить не все нужные для обработки данные.
Итак, вы в конце списка. Мой опыт такой: если в списке более 100 тысяч строк, то я обычно делю выписку на части. Если изначально выписка сформирована за год, то можно сделать ее за полгода, за квартал и т.д. и обрабатывать каждый период по отдельности. Это нужно, чтобы во время работы на тормозил компьютер. Хотя если у вас мощный процессор и большой объем оперативной памяти, то, возможно, компьютер не будет зависать и при работе с большим количеством строк. Лично у меня компьютеры всегда были средней мощности.
Далее этот список с данными нужно выделить. Причем выделить нужно не весь столбец, а именно область. В нашем случае это диапазон A1:A741. Я обычно выделяю так: ставлю курсор в ячейку A1, потом нажимаю Ctrl+Shift+↓. Если вижу, что из-за пустой строки я не в конце списка (а я уже был в конце списка и помню, сколько там заполненных строк), то, удерживая Ctrl+Shift, продолжаю нажимать на ↓ до тех пор, пока не дойду до конца. Выделенный диапазон копируем в буфер обмена Ctrl+C, переходим на лист «Обработка выписки», ставим курсор на ячейку A4, нажимаем правую клавишу мыши, кликаем левой клавишей на пункт «Специальная вставка», отмечаем пункт «Значения» и нажимаем «OK».
Должно получиться вот так:
Почему я одни и те же данные копирую два раза: сначала в лист «Копия выписки», потом в лист «Обработка выписки»? На самом деле, в данном случае можно было бы обойтись без этого шага. Но в других случаях при копировании данных в буфер обмена, помимо букв и цифр, могут попадать лишние данные: например, невидимые знаки (таковые иногда встречаются) или же форматы, которые будут портить внешний вид и перегружать файл информацией. Поэтому у меня за много лет сформировалась привычка переносить данные именно так.
Далее нужно выделить диапазон B2:AV2. В этом диапазоне находятся формулы. О том, как они работают, я напишу позже. Сейчас же нужно скопировать эти формулы, чтобы они были вставлены в диапазон B4:AV744. Для этого копируем выделенные ячейки B2:AV2 с помощью Ctrl+C, далее выделяем диапазон B4:B744 и нажимаем Enter. Должно получиться вот так:
Следующим шагом нужно выделить диапазон F4:AV744 (внимание: выделяем начиная с колонки F), нажать Ctrl+C, перейти в лист «Данные для базы», поставить курсор в ячейку A5, нажать правую клавишу мыши и через пункт «Специальная вставка» вставить данные (внимание!!!) как значения.
Далее кликаем на фильтр в столбце A, выделяем галочкой пустые ячейки и нажимаем «OK».
Номера строк слева стали синими:
Нужно выделить весь диапазон с этими строками, в нашем случае это A5:A745, нажать правую клавишу мыши, кликнуть на пункт «Удалить строку». Появится окошко с надписью «Удалить всю строку листа?». Далее нажимаем «OK», кликаем на фильтр в столбце A, ставим галочку «Выделить все», нажимаем «OK». Должно получиться следующее:
Переходим в правую часть таблицы. Там есть область, выделенная голубым цветом. Над ней, в самой первой строке, в диапазоне AR1:BE1, есть формулы. Нужно выделить диапазон AR1:BE1, нажать Ctrl+C, затем выделить диапазон AR5:AR27 и нажать Enter. В выделенную область скопируются формулы, это будет иметь такой вид:
В ячейке BE5 нужно удалить тире нажатием Delete.
Далее возвращаемся к файлу «Выписка.txt». Нужно мышкой выделить номер расчетного счета, по которому сформирована выписка, вот так:
Прошу обратить внимание, что при выделении чего-либо мышкой нужно всегда контролировать, чтобы в выделенную область попадали только нужные знаки. Бывает так, что случайно захватываются пробелы справа или слева. В рассматриваемом случае можно случайно захватить знак «=». При обработке данных в программе Excel это всегда критично.
Нажимаем Ctrl+C, переходим к файлу «Обработка выписки.xlsb», на листе «Данные для базы» идем в ячейку BE5, помещаем курсор в строку формул и нажимаем Ctrl+V, после чего Enter.
Этот счет нужно скопировать до конца списка, в нашем случае это диапазон BE5:BE27. Должно получиться вот так:
Диапазон AR5:BE27 – это данные, с которыми мы будем дальше работать.
Следующий шаг – контроль остатков. В выписке находим начальный и конечный остатки, вот они:
Остаток на начало вносим в ячейку BC2 (она зеленого цвета), можно руками, только вместо точки (десятичный разделитель) нужно поставить запятую. А в ячейке BC3 (коричневого цвета) пишем формулу: =BC2+СУММ(BC5:BC27). Получившаяся сумма должна совпадать с суммой конечного остатка в выписке.
В следующей статье мы будем переносить информацию в проект базы данных.