Заполнение документов excel данными из excel
Как известно, табличный редактор Excel используется для работы с табличными данными, а текстовый редактор Word для работы с текстами. И одно и другое приложение широко используются пользователями для подготовки различного рода документов, таких как договоры, сметы, акты выполненных работ, квитанции, путевые листы, доверенности и так далее. Кроме того, зачастую различные реестры и базы данных с информацией по клиентам, продавцам, покупателям, поставщикам и подрядным организациям создаются в приложении Excel, а сами данные из этих реестров используются для составления документов и заполнения шаблонов в текстовом редакторе Word. Далее рассматриваются варианты автоматизации при заполнении документов данными из рабочих книг Excel.
Заполнение бланков Excel данными активного рабочего листа
Большое количество документов создается пользователями в табличном редакторе Excel, это всевозможные приказы, ведомости, графики, журналы, ордера, акты, чеки, счета и счет-фактуры. Все это изобилие документов требует своего заполнения. Какие-то бланки заполняются специальными программами по учету финансово-хозяйственной деятельности, такими как 1С или SAP, какие-то просто распечатываются для дальнейшего заполнения их вручную, а какие-то требуют своего заполняются на компьютере с последующей печатью. Достаточно часто возникает необходимость сформировать сразу несколько документов, в которых используются одни и те же данные, например счет, счет-фактура и акт выполненных работ.
Процесс заполнения бланков документов в Excel можно автоматизировать. Одним из способов автоматизации является использование надстройки для заполнения подготовленных бланков документов.
Надстройка для Excel позволяет выбрать как один, так и несколько бланков документов и быстро заполнить их данными активного рабочего листа, предварительно подготовленными для этого.
Заполнение бланков Excel исходными данными по списку
В случаях, когда в качестве исходных данных используется готовый список значений, можно использовать надстройку, которая заполняет выбранные бланки и шаблоны по каждой строке списка в отдельности. Эта надстройка отличается от описанной выше программы тем, что предыдущая надстройка заполняет бланки документов одним набором исходных значений, а эта программа осуществляет заполнение документов сразу по всему списку данных. Если нужно запустить процесс заполнения не по каждой строке, а по каким-то отдельным, то используются стандартные фильтры Excel.
Надстройка также позволяет выбирать сразу несколько шаблонов для заполнения.
Имеем базу данных (список, таблицу - называйте как хотите) с информацией по платежам на листе Данные:
Задача: быстро распечатывать приходно-кассовый ордер (платежку, счет-фактуру. ) для любой нужной записи выбранной из этого списка. Поехали!
Шаг 1. Создаем бланк
На другом листе книги (назовем этот лист Бланк) создаем пустой бланк. Можно самостоятельно, можно воспользоваться готовыми бланками, взятыми, например, с сайтов журнала "Главный Бухгалтер" или сайта Microsoft. У меня получилось примерно так:
В пустые ячейки (Счет, Сумма, Принято от и т.д.) будут попадать данные из таблицы платежей с другого листа - чуть позже мы этим займемся.
Шаг 2. Подготовка таблицы платежей
Прежде чем брать данные из таблицы для нашего бланка, таблицу необходимо слегка модернизировать. А именно - вставить пустой столбец слева от таблицы. Мы будем использовать для ввода метки (пусть это будет английская буква "икс") напротив той строки, данные из которой мы хотим добавить в бланк:
Шаг 3. Связываем таблицу и бланк
Для связи используем функцию ВПР (VLOOKUP) - подробнее про нее можно почитать здесь. В нашем случае для того, чтобы вставить в ячейку F9 на бланке номер помеченного "x" платежа с листа Данные надо ввести в ячейку F9 такую формулу:
Т.е. в переводе на "русский понятный" функция должна найти в диапазоне A2:G16 на листе Данные строку, начинающуюся с символа "х" и выдать нам содержимое второго столбца этой строки, т.е. номер платежа.
Аналогичным образом заполняются все остальные ячейки на бланке - в формуле меняется только номер столбца.
В итоге должно получиться следующее:
Шаг 4. Чтобы не было двух "х".
Если пользователь введет "х" напротив нескольких строк, то функция ВПР будет брать только первое найденное значение. Чтобы не было такой многозначности, щелкните правой кнопкой мыши по ярлычку листа Данные и выберите Исходный текст (Source Code) . В появившееся окно редактора Visual Basic скопируйте следующий код:
Этот макрос не дает пользователю ввести больше одного "х" в первый столбец.
В этой статье описано, как пользоваться программой заполнения документов.
Рекомендую также посмотреть видеоинструкцию, где рассказывается о назначении программы, её настройке, и показан процесс её работы.
Как пользоваться надстройкой (порядок работы):
1) скачайте файл надстройки, и сохраните его в любой папке на вашем компьютере (кнопка СКАЧАТЬ справа сверху на странице программы FillDocuments)
2) разблокируйте и запустите файл надстройки, при этом в Excel появится панель инструментов (подробнее о том, где искать эту панель)
Порядок разблокировки и запуска отображается во всплывающем окне при скачивании
3) на панели инструментов нажмите кнопку «Настройки», и укажите путь к папке, содержащей шаблоны документов
(или переместите шаблоны документов в папку, указанную в настройках)
По умолчанию, программа ищет шаблоны в папке с названием « Шаблоны », а создаваемые файлы помещает в папку « Результат »
Обе эти папки располагаются в том же каталоге, что и файл надстройки, и создаются автоматически при первом открытии формы «О программе» (или нажатии кнопки «Настройки» на панели инструментов)
Например, скачайте 4 прикреплённых к статье тестовых шаблона, и извлеките их из архива в папку «Шаблоны»:
4) откройте в Excel таблицу, содержащую данные для заполнения шаблонов
(порядок открытия таблицы с данными, и файла надстройки, не важен, - можно сначала запустить надстройку, а потом открыть файл с данными, а можно при открытом файле с данными запустить надстройку)
В качестве примера, можете использовать прикреплённый к статье файл Excel с списком сотрудников
5) выделите строки в таблице Excel, для которых надо сформировать документы
(выделять строки целиком необязательно - достаточно выделить хотя бы по одной ячейке в каждой строке. Для выделения несмежных строк - к примеру, строк с номерами 3,7,10, - удерживайте клавишу CTRL при выделении ячеек)
6) Нажмите на панели инструментов кнопку «Сформировать документы»
Если в папке «Шаблоны» присутствуют корректные шаблоны (например, файлы с расширением DOC, DOT, XLS, TXT),
то в папке «Результат» через несколько секунд появятся созданные файлы, заполненные данными из выделенных строк
Пример результата (в папке «Документы»):
Как настроить шаблоны документов и таблицу Excel, чтобы заполнение выполнялось корректно:
Всё очень просто: возьмите шаблон - файл Excel (или Word) - и в те места шаблона, куда надо подставлять данные из исходной таблицы, поместите название столбца в фигурных скобках.
Например, если в образце создаваемого документа Word есть текст: Директор ООО «Рога и Копыта» Иванов Иван Скотиныч ,
и вы хотите, чтобы вместо ООО «Рога и Копыта» подставлялось название организации из столбца «Фирма»,
а вместо текста «Иванов Иван Скотиныч» в документе оказывалось ФИО сотрудника из одноимённого столбца,
замените в шаблоне этот текст на Директор
Обратите внимание: в шаблон подставляется только текст из исходной таблицы Excel.
Форматирование текста используется то, какое было в шаблоне на месте вставляемых данных.
Т.е., если в шаблон вы вставите следующее: Директор ,
то название фирмы после подстановки значения из таблицы Excel так и останется красным,
а ФИО сотрудника будет выделено жирным шрифтом.
Смотрите также инструкции по доступным подстановочным кодам, и по шаблонам документов.
В данной статье научимся создавать простую форму для автоматического заполнения договора на оказание услуг. Так как тема IT нам ближе, рассмотрим на примере договора на разработку ПО.
Видео инструкция
Подготовка
Нам потребуется обычный файл Excel. Вы можете взять за основу имеющийся или начать с чистого листа.
Составляем список данных, которыми нам требуется заполнять договор: номер, дата, предмет договора, сумма, срок, данные заказчика и так далее. Для каждого элемента нам потребуется создать в Excel свою ячейку для заполнения. У меня получилась примерно такая форма:
Оформление может быть любым, на ваш вкус и цвет. При формировании формы я использовал стили, расположенные на вкладке Главная. Это быстро и удобно, а также позволяет в один клик изменить цветовую гамму на вкладке меню "Разметка страницы" - "Тема"
Автоматизация формы
Часть данных (номер договора, сумма, срок), необходимо заполнять всегда тут ничего не сделаешь. Но как мы видим есть часть данных, которые хорошо бы заполнить автоматически. Это - сумма прописью, срок прописью, данные заказчика в родительном падеже и так далее. Давайте их автоматизируем.
Дата договора
В Excel существует множество функций по работе с датами. Если дата договора всегда текущая, то можно использовать функцию =СЕГОДНЯ(). При необходимости можно настроить, чтобы дата рассчитывалась как следующий рабочий день =РАБДЕНЬ(СЕГОДНЯ();1).
Для вставки даты вы можете использовать календарь из программы:
Сумма договора
Тут можно автоматизировать заполнение значения суммы договора прописью, для этого в программе есть специальная кнопка Деньги прописью, которая поможет сформировать формулу для автоматического расчета. Программа умеет склонять по всем падежам и поддерживает формирование суммы в нескольких форматах.
Срок прописью
Срок прописью аналогично, хочется вводить только цифры, чтобы в документ вставлялось прописью полностью. Кстати, это касается не только срока, это может быть количество чего угодно: услуги, штуки, килограммы, яблоки, помидоры и так далее. В программе Doc.filler все это можно автоматизировать. Откройте функции и выберите Вещи прописью.
В диалоговом окне сформируйте нужную вам формулу.
Заполнение заказчика
Данные заказчика идеально подгружать из внутренней системы заказчика 1С или другой CRM. Если такой возможности нет, то для начала можно упростить жизнь путем создания справочника правовых форм (ООО, ОАО и т.д.) Тогда нам потребуется просто выбрать из списка нужную форму и указать наименование. Остальное мы сделаем на формулах.
Итак создадим отдельный лист и разместим там таблицу со списком правовых форм.
Таблицу можно отформатировать как табличный диапазон, так с ней будет проще работать далее.
Теперь на форме создадим динамичный выпадающий список с данными правовых форм.
Осталось создать формулы для заполнения заказчика сокращенно в моем случае это формула = Правовая_форма &" "& Заказчик_наименование . Замените на соответствующие ячейки.
Формула для полного наименования заказчика следующая =ВПР( Правовая_форма ; ПравовыеФормы ;2;0)&" "& Заказчик_наименование . ПравовыеФормы - название таблицы со справочником форм.
Результат выглядит следующим образом:
Склонение ФИО подписанта
Сейчас посмотрим как автоматически склонять ФИО подписанта. Для этого в программе есть соответствующая функция, которую можно вставить из меню:
Программа умеет склонять 90% русских ФИО по всем падежам.
Склонение должности и других фраз
Аналогичным образом склоняются должности и другие короткие фразы договора, например Устав. Вставьте функцию Склонение фраз через ленту меню:
Итоговая форма
В результате мы создали форму и автоматизировали часть информации, чтобы не приходилось вводить ее по нескольку раз.
Следующим нашим шагом будет создание шаблона договора и последующее его автоматическое заполнение данными из формы.
Каждому хоть раз в жизни приходилось заполнять договоры, шаблоны, бланки и прочие документы. Конечно, если это разовая операция, то можно потратить время и кропотливо заполнить нужную информацию. Но когда эта операция ежедневная, то хочется автоматизировать эту рутинную работу. Ведь каждый день заполняются одни и те же поля, постоянно допускаются ошибки, часть информации указывается по несколько раз в одном документе. Наш новый продукт нацелен как раз на решение этих задач.
Возможности программы
Программа представляет собой расширение функционала в Excel. После установки на ленте меню появится отдельная вкладка для автоматизации заполнения документов. Почему Excel? Все просто - это самый привычный формат для пользователя по ведению реестра и заполнению различных форм. Не нужно обучаться новому функционалу и осваивать сложные программы, все необходимое уже есть. Вы продолжаете как обычно заполнять форму Excel но плюс к этому получаете возможность автоматически генерировать документы. Вот так выглядит лента меню в программе:
Пример создания форм заполнения
Создавайте простые формы для заполнения прямо в Excel. Просто укажите программе какие ячейки (поля) вы хотите вставлять в отчеты. Вы также можете создавать сложные формы для заполнения документов с различными условиями. Например, договоры, где различные формулировки должны вставляться при определенных условиях. Подробнее про создание форм в Excel.
Вставка функций
Часто при заполнении мы указываем числа прописью или склоняем фамилии или должности по падежам. Теперь вы сможете вставить функцию в Excel и она автоматически сделает это за вас. Вам потребуется один раз указать ФИО, а программа сама просклоняет ее и подготовит к вставке в документ. Аналогично с должностями или другими фразами. Программа склоняет фразы по всем падежам русского языка.
Необходимо вставить сумму, количество или дни прописью - тоже не проблема в программе имеются функции для записи прописью любых данных.
Заполнение шаблона
После того, как форма для заполнения документа готова необходимо создать шаблон документа. В шаблоне нужно расставить так называемые метки, в которые программа будет вставлять данные. Например, , , и так далее. Для простоты мы подготовили примеры готовых документов. Вы можете создавать свои метки в любых количествах. В этой статье можно посмотреть процесс создания шаблона документа.
После того как шаблон готов, данные в него вставляются из формы Excel всего одной кнопкой.
Заполнение нескольких документов
Как правило, единовременно необходимо заполнить сразу несколько документов. Например договор и счет на аванс, акт выполненных работ и счет-фактуру и так далее. В программе предусмотрена возможность заполнения сразу нескольких документов.
Создание и ведение реестра документов
Помимо заполнения документов в программе можно вести реестр создаваемых документов. Данные будут автоматически переноситься в реестр и вы сможете к ним вернуться спустя время. Вы сможете повторно создавать документы или исправлять в них данные нажав пару кнопок.
Реестр документов можно настраивать по своему усмотрению добавляя в него формулы или дополнительные данные, а можно и наоборот хранить в нем только выжимку информации.
В дальнейшем у нас в панах добавить функционал документооборота с возможностью создавать напоминания и визуальным отслеживанием статусов.
Читайте также: