Арчі Гудвін Програма рознесення банківської виписки на базі Excel - Готові рішення - MS Office та VBA
Авторизація
Рубрики блогу
- Public / Загальні теми
- Новина + Думка
- Роздуми
- Уроки чи поради
- Це цікаво
- Гумор
- Креатив
- Рецензія
- Особистість
- Притчі, історії
- Новини сайту
Рекомендуємо
Хмара тегів
Вустами великих
Програма рознесення банківської виписки на базі Excel.
У чому суть програми?
Метою цього проекту спочатку було рознесення позицій банківської виписки, вивантаженої з бухгалтерської програми АСФУ зі спеціальних фінансових позицій. Програма поділена на два файли – «Надходження» та «Витрати», відповідно для ведення доходних та видаткових позицій. Повністю реалізована в екселе (.xlsm) за допомогою стандартних формул, фільтрів і, можливо, не зовсім стандартних макросів.
Переваги «банківської виписки»
Від будь-якої іншої саморобної таблички, яка могла б виконувати хоч якоюсь мірою зазначену роль, цей проект відрізняється тим що:
- Кожен окремий день банківської виписки розноситься в окремому аркуші, що унеможливлює нагромадження та хаотичність.
- Результати виконаної роботи з допомогою спеціального макросу зберігаються у «базі даних» – окремому аркуші экселя, у разі зміни – благополучно перезберігаються.
- Аркуш «Головна книга» дозволяє підтягувати, фільтрувати та сортувати результат рознесеної виписки, як за окремий день, так і за певний період, всього в пару кліків.
- Можливістю рознесення і фільтрування виписки за різними банківськими рахунками паралельно – наприклад, було рух і з валютному, і розрахунковим рахунках, логічно і розносити це одну дату одному й тому самому аркуші.
- Можливістю досить просто налаштовувати список фінансових позицій, а також список існуючих банківських рахунків, що знаходяться на аркушах «Бібліотека рахунків» та «Бібліотека ФП».
- Введена в книгу надбудова Date Picker – календар, що випадає, для зручного вибору дат.
Крім цього, додано тісну інтеграцію до програм АСФУ і SAP. З АСФУ в екселевском файлі експортується виписка, далі цю виписку імпортуємо до нашого проекту, клікнувши лише раз на кнопку «Імпорт», після чого макрос визначить який розрахунковий рахунок і який саме діапазон осередків потрібно скопіювати. Список фінансових позицій підігнаний до тих, що використовуються в програмі SAP, і, вивантаживши екселевський файл з модуля «Реєстр заявок», досить просто автоматизувати рознесення виписки, клацнувши по кнопці «Напів-автомат», тобто, макрос самостійно проставляє відповідності виписки з двох сторонніх. програм за сумами.
Чим може бути корисним рознесення банківської виписки в такому форматі?
2)Для тих, хто переслідує схоже завдання – створення середовища для ведення та рознесення банківської виписки на підприємстві, так само як і для тих, хто потрапить на статтю у пошуках програмного вирішення якогось конкретного процесу, що використовується в цій програмі для реалізації якогось зовсім іншого проекту, далі буде розписано вихідний код та принцип дії макросів.
Специфікація "програми" або яка структура проекту.
Як було написано вище, проект «банківська виписка» складається з двох файлів, в яких окремо ведеться іфіксується рух надходжень та витрат. Принцип дії обох файлів однаковий, лише файл для ведення витрат складніше через наявність інтеграції з програмою SAP через макрос «Напівавтомат», тому розглядатимемо структуру на прикладі саме цього файлу.
Як місце дляопційних налаштуваньвиступають листи«Бібліотека рахунків»та«Бібліотека ФП», звідси підтягуються дані про можливі варіанти фінансових позицій або розрахункових рахунків і тут же їх можна відредагувати, якщо є така потреба.
Далі йдуть аркуші, найменуваннями яких єдати, це «робочі конячки» саме тут і відбувається все найцікавіше – у кожен такий аркуш імпортується виписка з АСФУ (або клієнт-банку) і тут же для кожного запису виставляються відповідності до фінансових позицій, результати такої роботи потім зберігаються в базі даних, тобто в аркушіDataBase.
Нарешті, листГоловна книга- це той лист, який дозволяє подивитися результати роботи «банківської виписки» для подальшого аналізу або навіть прогнозу, якщо хочете, сюди відповідно до фільтрів дат і типів рахунків підтягуються дані з листа«DataBase».
Про те, як реалізовані списки, що випадають, і використані формули для підтягування даних за типом ВПР і СУМЕСЛІ, комбінації логічних умов через формули ЯКЩО, обрізання і склеювання тексту в осередках та інші рішення через стандартні формули та фільтри в Екселі, розписувати не буду, тому що кожен із таких питань вимагає або окремої повноцінної статті, або взагалі нічого.
Макрос «ОЧИСТИТИ» - визначення діапазонів непотрібних даних та їхочищення
Листи безпосередньо з виписками, копіюються (дублюються) з попередньої дати, після чого оновлюється вручну назва аркуша та комірка з датою до тієї дати, за яку планується розносити виписку. Плюсом такого дублювання є збереження структури таблиць, стилів та необхідних кнопок для запуску макросів, а мінус у тому, що залишаються дані виписки з того дня, який копіювали, тому ці дані явно неактуальні. Очистити їх можна вручну, виділяючи діапазони незахищених від змін осередків і натискаючи кнопку Delete, але особисто я вважав за краще передоручити цю «складну справу» макросу.
На підприємстві виписка вивантажується із програми АСФУ в екселівські файли у вигляді табличок із шаблонною структурою (приклад такої виписки також додається до статті). Копіювати комірки із сумами та описом кожної суми можна вручну, але навіщо, коли і це можна доручити макросу? Макрос сам визначить корисні осередки, скопіює їх значення та перенесе у необхідні стовпці на аркуш у наш проект «банківської виписки» для подальшої обробки.
Макрос Напівавтомат – інтеграція із заявками на платіж, вивантаженими з SAP
Так як на підприємстві реалізовано таку схему, при якій практично всі платежі набираються в програмі SAP через модуль «Реєстр заявок на платіж», а потім вивантажуються в клієнт-банк для здійснення оплати, після факту оплати з клієнт-банку вивантажується виписка, яку власне і стоїть завдання рознести за фінансовими позиціями, що використовуються в SAP, то безглуздо розносити виписку вручну, якщо цей процес можна автоматизувати.
Автоматизація передбачає вивантаження в екселівському файлі даних по платежах за день, що цікавить, і згодом підтягування цих даних у витратну частину банківської виписки і за допомогою формул ВПРі простого копіювання проводиться зіставлення витратних позицій і фактично рознесення виписки - автоматично проставляються фінансові позиції в проекті «банківська виписка» за вивантаженими платежами, що перетинаються, в САП.
Макрос називається напівавтоматом тільки з тієї причини, що він дозволяє рознести платежі, які проходили через САП, а інші, на кшталт централізованих перекидів все одно необхідно дорозносити руками.
Макрос Del_FromBase – сценарій, що дозволяє перетворити лист екселю на базу даних.
Макрос порівнює поточну дату з усіма записами в базі даних, якщо знаходить записи з таким параметром, то видаляє їх і тільки після цього внизу під усіма іншими записами зберігає дані з листа з рознесеною випискою, таким чином, забезпечуючи безпечне збереження даних, якщо вони зберігаються вперше, і нескінченна кількість разів перезбереження, у разі змін даних на аркуші з випискою.