Заполнение бланков данными из таблицы

Постановка задачи

Имеем базу данных (список, таблицу - называйте как хотите) с информацией по платежам на листе Данные:

order_database1.gif

Задача: быстро распечатывать приходно-кассовый ордер (платежку, счет-фактуру...) для любой нужной записи выбранной из этого списка. Поехали!

Шаг 1. Создаем бланк

На другом листе книги (назовем этот лист Бланк) создаем пустой бланк. Можно самостоятельно, можно воспользоваться готовыми бланками, взятыми, например, с сайтов журнала "Главный Бухгалтер" или сайта Microsoft. У меня получилось примерно так:

order_database2.png

В пустые ячейки (Счет, Сумма, Принято от и т.д.) будут попадать данные из таблицы платежей с другого листа - чуть позже мы этим займемся.

Шаг 2. Подготовка таблицы платежей

Прежде чем брать данные из таблицы для нашего бланка, таблицу необходимо слегка модернизировать. А именно - вставить пустой столбец слева от таблицы. Мы будем использовать для ввода метки (пусть это будет английская буква "икс") напротив той строки, данные из которой мы хотим добавить в бланк:

order_database5.gif

Шаг 3. Связываем таблицу и бланк

Для связи используем функцию ВПР (VLOOKUP) - подробнее про нее можно почитать здесь. В нашем случае для того, чтобы вставить в ячейку F9 на бланке номер помеченного "x" платежа с листа Данные надо ввести в ячейку F9 такую формулу:

=ВПР("x";Данные!A2:G16;2;0)

=VLOOKUP("x";Данные!B2:G16;2;0)

Т.е. в переводе на "русский понятный" функция должна найти в диапазоне A2:G16 на листе Данные строку, начинающуюся с символа "х" и выдать нам содержимое второго столбца этой строки, т.е. номер платежа.

Аналогичным образом заполняются все остальные ячейки на бланке - в формуле меняется только номер столбца.

Для вывода суммы прописью я воспользовался функцией Propis из надстройки PLEX.

В итоге должно получиться следующее:

order_database4.png

Шаг 4. Чтобы не было двух "х"...

Если пользователь введет "х" напротив нескольких строк, то функция ВПР будет брать только первое найденное значение. Чтобы не было такой многозначности, щелкните правой кнопкой мыши по ярлычку листа Данные и выберите Исходный текст (Source Code). В появившееся окно редактора Visual Basic скопируйте следующий код:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim str As String

If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
    str = Target.Value
    Application.EnableEvents = False
    r = Cells(Rows.Count, 2).End(xlUp).Row
     Range("A2:A" & r).ClearContents
    Target.Value = str
End If
  Application.EnableEvents = True
End Sub

Этот макрос не дает пользователю ввести больше одного "х" в первый столбец.

Ну, вот, собственно и все! Пользуйтесь!

Ссылки по теме

 


14.01.2013 14:18:50
А можно ввести в ячейку A2 исходной таблицы формулу:
=ЕСЛИ(ЯЧЕЙКА("строка";)=СТРОКА(A2);"Х";"";)
затем растянуть на всю колонку, предназначеную для пометок,
затем добавить макрос к листу:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.Calculate End Sub

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

Кстати идея и макрос взяты отсюда http://planetaexcel.ru/techniques/3/58/ , пункт 2.
30.01.2013 13:39:57
Николай, в Вашем примере установлена связь с надстройкой PLEX (C:\Dropbox\Текущие проекты\PLEX\PLEX_MY).
При открытии примера светиться запрос на обновление связей.;)
30.01.2013 14:12:45
Так и должно быть. Я же на Шаге 3 писал:
Для вывода суммы прописью я воспользовался функцией Propis из надстройки PLEX.

Если сумма прописью вам не нужна - просто удалите ее.
22.02.2013 15:37:18
Я столкнулась с такой ситуацией. В базе данных, в силу некоторых обстоятельств, заполнены не все ячейки, а бланк сформировать необходимо. Если воспользоваться ВПР("x";Данные!A2:G16;7;0), например, то рискую вместо пустой ячейки, то есть незаполненной, получить ячейку со значением ноль.
Подскажите как дописать формулу, чтобы в бланке оставалась незаполненная ячейка, совершенно пустая, если в БД нет соответствующих данных.
23.02.2013 10:18:32
Татьяна, нужно будет предварительно проверить - не находит ли ВПР пустую строку (с нулевой длиной), т.е. так:
=ЕСЛИ(ДЛСТР(ВПР("x";Данные!A2:G16;7;0))>0;ВПР("x";Данные!A2:G16;7;0);"")
03.03.2013 13:35:36
Николай, не знаю почему, но у меня макрос ведет себя довольно странно (Excel 2010)
Я на пустой лист вставил макрос, предложенный в примере.
Сначала, при вводе значений в каждую следующую ячейку столбца А (начиная с А2), значение из предыдущей ячейки удаляется. Но, начиная с 10 строки удаление ранее введенных значений прекращается. Очищаю столбец А от всех значений. Снова начинаю ввод значений в ячейки столбца А (с А2). Уже на 4 строке удаление ранее введенных значений прекращается.
Кроме того, если вводить не одинаковые значения, то работа макроса прекращается сразу после ввода значения, отличающегося от предыдущего :(
С чем это связано?
03.03.2013 17:05:32
Не видя вашего файла - это гадание на кофейной гуще. Пришлите файлик мне на почту или выложите в форум с описанием проблемы.
23.03.2013 21:25:03
Позволил себе объединить и немного допилить два Ваших приема Вот что получилось. Откомментируйте на досуге
19.06.2013 10:59:53
А как быть, если в документе необходимо ввести данные нескольких строк?
15.02.2014 15:05:45
Можно разместить их в столбцах.
08.09.2013 14:22:52
Подскажите. А можно ли создать бланк !!! При введении данных в него., - и нажатию волшебной ;-) кнопочки,  они запрыгивают в сводную таблицу.... тобысь ускорить ввод  в базу данных......
Или где такое описано, что-бы его почитать.........
спасибо.
03.12.2013 19:25:32
Вместо галочки меняю номер в бланке, ВПР() ищет по номеру запись в списке. Для удобство воткнул кнопки +/-, которые меняют номер в бланке. Без макроса.
18.01.2014 22:53:37
Подскажите как сделать так чтобы автоматически указывался всегда следующий номер приходника. Наверное нужно чтобы был список  из всех существующих приходников с их номерами и программа указывала в приходнике  номер последнего в списке +1. Какую формулу или функцию применить?
15.02.2014 15:04:40
А если просто брать максимальный из существующих номеров и добавлять к нему единицу, т.е. что-то типа =МАКС(B:B)+1 для данного примера?
15.02.2014 21:15:11
Спасибо большое. Попробую
13.02.2014 20:00:24
22.02.2014 09:21:50
Здравствуйте, спасибо Вам за Ваши примеры и пояснения. Есть один вопросик, как можно сделать следующее:
Есть 12 листов и один шаблон. Как в шаблоне динамически менять ссылку на таблицы (листы), в зависимости от месяца. Я так понимаю, что в шаблоне, за границами печати необходимо сделать выпадающий список, в котором будет выбираться месяц. Пока ничего не получается и выглядит это так
=ВПР("акт";январь!$A$14:$Q$67;3;0)
а нужно
=ВПР("акт";февраль!$A$14:$Q$67;3;0)
=ВПР("акт";март!$A$14:$Q$67;3;0)
спс, сильно не пинать :(
22.02.2014 09:40:46
Сергей, вам нужна функция ДВССЫЛ, которая умеет превращать текст в ссылку и символ & для склеивания текста. В вашем случае это будет примерно так:
=ВПР("акт";ДВССЫЛ(R1&"!$A$14:$Q$67");3;0)
где R1 - адрес ячейки, где будет лежать имя нужного листа (лучше сделать там выпадающий список)
22.02.2014 10:54:59
Спасибо, Николай, все получилось, именно то, что нужно! ;)
А если шаблон разместить в другой книге?
Сделал оглавление листов рабочей книги с помощью PLEX, а ячейки со списком листов скопировал в шаблон, создал выпадающий список из списка листов. внес изменения в формулу.  Не работает((. Кликая на список листов в шаблоне(которые созданы для выпадающего списка) , они не открываются, как если бы просто вставил гипперсылку для открытия книги)
Пока все было в одной книге, формула выглядела так: =ВПР("акт";ДВССЫЛ(BP1&"!$A$14:$Q$67");3;0)
А в идеале, что бы это выглядело так: =ВПР("акт";ДВССЫЛ(отчет, январь&"!$A$14:$Q$67");3;0)
11.04.2014 09:26:16
Спасибо
есть еще вариант, использую для печати конвертов:
=СМЕЩ(Адреса!A2:E13;ПОИСКПОЗ(I24;Адреса!A2:A13;0)-1;1;1;1)
второй лист "адреса" с данными для заполнения. "х" не использую"
в ячейках бланка, который надо заполнить меняю только № столбца. проблема нулей решилась просто: в пустых ячейках данных просто ставлю пробел
PS в ячейке I24 проверка данных "список" (ссылаюсь на таблицу листа "адреса"), т.е. выбор возможен только из заполненных строк.  
29.09.2014 10:39:35
Николай, а если бланк в Ворде и необходимо вставить сразу несколько строк из таблицы, т.е. договор на одно ФИО, а список товаров в этом договоре из нескольких строк для этого фио. Может при слиянии какие-нибудь настройки можно сделать? Может быть, Вы где-нибудь про это писали?
23.11.2014 09:00:23
Ну, про Word не скажу, а в Excel это можно сделать точно с помощью многоразового ВПР.
19.01.2017 07:23:24
Анна
С вордом очен хорошо реализована надстройка  с сайта Excel-vba

Сам пользуюсь (но платная)
19.01.2017 22:02:40
Сергей, спасибо! 1600 не так уж много, если очень надо:)
Николай, спасибо за прекрасные решения!
Возможно ли эту задачу решить наоборот? Разные отделы заполняют форму (требование на закупку) с указанием отдела, кода, описания, суммы итд. Возможно ли составить базу данных из разных файлов и чтобы она автоматически (периодически) обновлялась?
23.11.2014 08:56:01
Можно попробовать это реализовать через веб-опрос, как описано в этой статье.
Или собирать данные макросом, но это уже сложнее.
11.12.2014 20:34:39
Здравствуйте , в базе имется приходный и расходный ордер на одном  листе как сделать так чтобы автоматически указывался всегда следующий номер пихоодника ирасходника
29.01.2015 13:23:19
Добрый день! Подскажите, а можно ли реализовать печать определенного диапозона строк, а не одной? (порой нужно распечатать  более 1000 заполненных бланков)
На данный момент я использую функцию рассылки через привязку таблицы excel к бланку в word`е, что не совсем удобно!
08.07.2015 23:52:38
искал как выразить туже мысль...
есть таблица с 15тыс строк, и бланк, куда с помощью ВПР подставляется таблица - печатается ВРУЧНУЮ
как сделать, что бы строка последовательно подставлялась, печаталась, далее подставлялась новая - и так по кругу
Очень надо, помогите
09.07.2015 08:55:59
Через функцию рассылки в Word можно напечатать хоть 20 тыс строк подряд.
Но здесь нужно связать бланк в Word с таблицей Excel через пошаговый мастер слияния.
16.09.2015 13:08:19
А подскажите, можно ли создав один шаблон бланка распечать данные не с одного листа, а с нескольких (данные расположены идентично). И данные не цыфры а текст? Спасибо заранеее.:))
21.10.2015 01:31:07
А возможно ли сделать наоборот? Есть бланк, который заполняют вручную, можно ли составить базу данных из той информации, которую вносят в бланк, с постоянным обновлением. И бланк и таблица в одном файле.
26.11.2015 09:06:35
А как быть если нужно распечатать все бланки?
19.08.2016 19:54:03
А подскажите, пожалуйста, как пользоваться ВПР при динамической таблице данных. Как прописать диапазон ячеек?
04.01.2017 09:14:34
Если под "динамической таблицей" вы имеете ввиду умную, то просто указать ее имя в качестве второго аргумента ВПР.
15.12.2016 23:55:52
Все работает прекрасно вот только одно но, когда строка доходит до 912 ссылки дают ошибку, как увеличить диапазон строк?
04.01.2017 09:17:27
Проверяйте ссылки в формулах - где-то у вас косяк. Не видя вашего файла, не смогу сказать где именно, к сожалению.
15.02.2017 00:23:41
Хорошо как я могу отправить вам файл?
29.12.2016 16:43:25
Уважаемые профессионалы!
Подскажите как выйти из следующей ситуации и существует ли выход?
Есть форма, в которую подгружается все что нужно мне, есть планшет с пером и установленным офисом 2013, но хочу добавить следующую вещь.
Сотрудник ставит подпись в ячейке экселя в соответствующем поле, и при открытии формы необходимо чтоб соответствующем поле появлялась его подпись напротив соответствующего значения, которая берется из того листа и перечня где он ставил свою подпись пером.
Сори если непонятно объяснил, но нужно понять, возможно ли подобное реализовать.
04.01.2017 09:20:35
Похожая штука есть тут - выпадающий список с картинкой, т.е. привязка изображения к тексту с помощью ВПР.
10.02.2017 05:16:53
Здравствуйте, Николай! Огромное Вам человеческое спасибо за Ваш труд! А самое замечательное, что Вы охотно делитесь с людьми своими знаниями. Я не дружу с математикой с детства, порой не хватает базовых знаний, но с помощью Вашего сайта, я немного освоила Эксель. Он такой молодец:), очень облегчает жизнь и избавляет от многих непродуктивных действий. Подскажите, пож-та, если нет готового шаблона бланка, как создать бланк самому? Спасибо!
24.05.2017 13:15:00
Помниться мне когда я работал в банке нам ребята сделали таблицу после набора которой мы нажимали сочетание Ctrl+Shift+M после чего все данные из строки шли в бланк и отправлялись сразу на печать. Научите такому пожалуйста
10.07.2017 09:44:27
Скорее всего, это был макрос, который назначили на сочетание клавиш. Если интересно, как такое делают - добро пожаловать на курс по программированию на VBA :)
30.06.2017 11:34:08
Добрый день. Сделал все как написано, но для того чтобы данные подтягивались в бланк нужно каждый раз сохранять таблицу с данными. После этого возвращаться на лист бланка и каждую ячейку с формулой адресовать на только что сохраненную книгу.
Как сделать так, чтобы при выборе строки иксом переходя на лист с бланком видеть уже подтянувшуюся информацию, без всяких сохранений.
10.07.2017 09:45:29
А в один файл все положить?
09.07.2017 19:01:00
Как отправить на печать выделенные "х" бланки??
10.07.2017 09:42:32
Ну, "х" то у нас один, а не несколько. Так что просто нажать Файл-Печать на листе с бланком. Для массовой печати нужен макрос или связка Excel-Word в слиянии.
10.07.2017 16:45:52
Бланков в 90% случаев много, а не один. Очень надо макрос, который бы печатал несколько отмеченных "х", а не один...
18.09.2017 12:15:23
Вместо букв применять цифры нельзя?
Я пробовал заменить "x" на "1" - не работает, пишет н/д.
12.12.2017 13:31:46
а такое же действие только в открытую форму в окне браузера возможно?