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

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

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

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.
14.01.2019 21:18:22
Что-то excel ругается на цикличность. Можно поподробнее об этой формуле в данном примере.
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
8)8)8)
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
а такое же действие только в открытую форму в окне браузера возможно?
22.12.2017 23:46:43
Добрый день, Николай. Спасибо за Ваши советы. Пытался сделать заполнение бланка данными из строки, отобранной фильтром, хотелось обойтись без метки. Но так и смог сообразить как на неё сослаться, ведь номер строки каждый раз разный. Это в принципе возможно? Пытался использовать ДВССЫЛ и СМЕЩ.
04.03.2018 13:34:16
Всем Добрый день!
С заполнением единичного бланка все понятно. А как сформировать простыню из бланков, заполненных данными из всего множества строк таблицы? В каждый бланк последовательно заносится информация из одной строки таблицы. Может кто-нибудь реализовывал такое? Заранее благодарю за отклик
07.09.2018 21:15:52
Я делал чуть иначе.прописывал макрос так чтобы печаталась та строчка которая выделена. плюсы моей идеи в том что не надо добавлять нового столбика и крестиков. тупо выделяем любую ячейки нужной строчки и печатаем)
02.01.2019 20:32:23
Классная тема. Народ продвинутый. Теперь понятно как из таблицы выдергивать данные в форму.
Но есть обратная задача - вносить данные в печатную форму и по нажатию какой нибудь кнопки - все улетает в таблицу
А потом когда таблица вырастет уже в базу - по необходимости можно будет воспользоваться описанным рецептом печати формы на выбранную строчку. Люди, Посоветуйте плз. варианты решения обратной задачи?
01.03.2019 08:38:35
Добрый день!
Помогите с распечатывание. Сделала на подобие такой таблицы, только у меня заполняется Путевой лист, все хорошо заполняеться, но проблема в распечатовании. Там нужно, чтоб распечатовался на одном листе с двух сторон, и не полностью, а половина. Так вот если сразу ставлю двухстороннюю печать на обратной стороне либо перевернуто, либо с другой стороны листа, как это можно исправить? Надеюсь понятно написала. Заранее спасибо!
16.01.2020 14:53:36
Добрый день, подскажите, пожалуйста, как называется функция выбора X нужной строки и как ее настроить ?
07.04.2020 16:56:09
Здравствуйте, а что делать если наоборот в бланке множество значений. ну например транспортная накладная в которой есть десять значений, как сделать так что бы по номеру документа заполнялись все данные в бланке?
07.08.2020 16:02:00
Например, Я, пока не знаю), но есть мастер слияния с WORD - и, возможно, в нем есть простой ответ.. Сам жду раскрытия темы Автором. (параллельно осваивается Access)
16.07.2020 12:53:26
Здравствуйте.  после ввода функции  =ВПР("x";Данные!A2:G16;2;0) и нажатия на клавишу энтер в ячейке с формулой выводится не номер документа, а  #Н/Д
17.07.2020 15:59:58
Спасибо Вам Николай Павлов. Я автоматизировал формирования счет фактуры (счет на оплату). Теперь у меня есть журнал регистрации. И не нужно записать последний номер счета на оплату все по журналу и по порядку будет.  
29.07.2020 15:49:26
Этот макрос не дает пользователю ввести больше одного "х" в первый столбец.

я  скопировал макрос . сделал как вы сказали.
результат
удаляет только на первых двух строках
 
11.08.2020 12:36:26
Здравствуйте. Макрос работает замечательно. вот уже месяц как я пользуюсь этим макросом. Столкнулся с такой проблемой, макрос работает только в "умной таблице". Подскажите пожалуйста, как сделать, чтобы макрос работал и в обычной таблице, созданной в ручную.
26.08.2020 09:11:22
Чтобы не было двух "X" можно воспользоваться "Проверка данных" на вкладке "Данные", тип данных "Другой" и "Формула":
СЧЁТЕСЛИ($B$2:$B$17;B2)<=1

и впринципе можно обойтись без макроса.
13.11.2020 14:04:40
Здравствуйте !
а можно было чтоб наоборот. то есть вводишь данные в форму ( например , расходный ордер ) а на следующем листе сформировалась отчет . сделйте пожалуйста урок на эту тему  
05.03.2021 00:33:38
Здравствуйте, Николай. Пытаюсь применить этот приём, но он при добавлении формулы ВПР в нужную ячейку бланка просит указать файл, в котором надо обновить значения. Какой бы лист файла с бланком я не указал, появляется ошибка "#ИМЯ?" в ячейке куда я ввожу формулу ВПР. Не пойму что с этим делать. Подскажите, пожалуйста.
У меня 12 листов, на каждый месяц. одинаковые таблицы, с определенным количеством строк.
Информация по путевым листам для автомобилей.
Ваш пример хорош для выбора данных из одной таблицы, то есть с одного листа.
А можно так, чтобы считывал с одного из 12-ти листов, где мы проставили знак "х"?
15.05.2023 09:55:58
Здравствуйте!. Прекрасно прием и макрос работают..
А как сделать, что бы заполнялся не один бланк а несколько?
Т.е. ставить не одну галку а сразу например 10, потом создается лист по образцу бланка, в этом листе 10 страниц бланков с разными данными. Добавить 11 галку лист дополняется 11 страницей. и т.д.
Подскажите пожалуйста. неудобно на печать выводить по одному
Наверх