Заполнение бланков данными из таблицы
Постановка задачи
Имеем базу данных (список, таблицу - называйте как хотите) с информацией по платежам на листе Данные:
Задача: быстро распечатывать приходно-кассовый ордер (платежку, счет-фактуру...) для любой нужной записи выбранной из этого списка. Поехали!
Шаг 1. Создаем бланк
На другом листе книги (назовем этот лист Бланк) создаем пустой бланк. Можно самостоятельно, можно воспользоваться готовыми бланками, взятыми, например, с сайтов журнала "Главный Бухгалтер" или сайта Microsoft. У меня получилось примерно так:
В пустые ячейки (Счет, Сумма, Принято от и т.д.) будут попадать данные из таблицы платежей с другого листа - чуть позже мы этим займемся.
Шаг 2. Подготовка таблицы платежей
Прежде чем брать данные из таблицы для нашего бланка, таблицу необходимо слегка модернизировать. А именно - вставить пустой столбец слева от таблицы. Мы будем использовать для ввода метки (пусть это будет английская буква "икс") напротив той строки, данные из которой мы хотим добавить в бланк:
Шаг 3. Связываем таблицу и бланк
Для связи используем функцию ВПР (VLOOKUP) - подробнее про нее можно почитать здесь. В нашем случае для того, чтобы вставить в ячейку F9 на бланке номер помеченного "x" платежа с листа Данные надо ввести в ячейку F9 такую формулу:
=ВПР("x";Данные!A2:G16;2;0)
=VLOOKUP("x";Данные!B2:G16;2;0)
Т.е. в переводе на "русский понятный" функция должна найти в диапазоне A2:G16 на листе Данные строку, начинающуюся с символа "х" и выдать нам содержимое второго столбца этой строки, т.е. номер платежа.
Аналогичным образом заполняются все остальные ячейки на бланке - в формуле меняется только номер столбца.
Для вывода суммы прописью я воспользовался функцией Propis из надстройки PLEX.
В итоге должно получиться следующее:
Шаг 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
Этот макрос не дает пользователю ввести больше одного "х" в первый столбец.
Ну, вот, собственно и все! Пользуйтесь!
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для подстановки значений
- Улучшенный вариант функции ВПР (VLOOKUP)
- Сумма прописью (функция Propis) из надстройки PLEX
=ЕСЛИ(ЯЧЕЙКА("строка";)=СТРОКА(A2);"Х";""
затем растянуть на всю колонку, предназначеную для пометок,
затем добавить макрос к листу:
В результате пометка Х ставится в первой колонке напротив любой активной ячейки на листе.
Плюс такого способа: пользователю не нужно вводить с клавиатуры пометку Х, а достаточно щелкнуть мышкой, допустим, на необходимом юрлице и после этого перейти на второй лист с бланком.
Кстати идея и макрос взяты отсюда , пункт 2.
При открытии примера светиться запрос на обновление связей.
Для вывода суммы прописью я воспользовался из .
Если сумма прописью вам не нужна - просто удалите ее.
Подскажите как дописать формулу, чтобы в бланке оставалась незаполненная ячейка, совершенно пустая, если в БД нет соответствующих данных.
=ЕСЛИ(ДЛСТР(ВПР("x";Данные!A2:G16;7;0))>0;ВПР("x";Данные!A2:G16;7;0);"")Я на пустой лист вставил макрос, предложенный в примере.
Сначала, при вводе значений в каждую следующую ячейку столбца А (начиная с А2), значение из предыдущей ячейки удаляется. Но, начиная с 10 строки удаление ранее введенных значений прекращается. Очищаю столбец А от всех значений. Снова начинаю ввод значений в ячейки столбца А (с А2). Уже на 4 строке удаление ранее введенных значений прекращается.
Кроме того, если вводить не одинаковые значения, то работа макроса прекращается сразу после ввода значения, отличающегося от предыдущего
С чем это связано?
Или где такое описано, что-бы его почитать.........
спасибо.
Есть 12 листов и один шаблон. Как в шаблоне динамически менять ссылку на таблицы (листы), в зависимости от месяца. Я так понимаю, что в шаблоне, за границами печати необходимо сделать выпадающий список, в котором будет выбираться месяц. Пока ничего не получается и выглядит это так
=ВПР("акт";январь!$A$14:$Q$67;3;0)
а нужно
=ВПР("акт";февраль!$A$14:$Q$67;3;0)
=ВПР("акт";март!$A$14:$Q$67;3;0)
спс, сильно не пинать
=ВПР("акт";ДВССЫЛ(R1&"!$A$14:$Q$67");3;0)
где R1 - адрес ячейки, где будет лежать имя нужного листа (лучше сделать там )
А если шаблон разместить в другой книге?
Сделал оглавление листов рабочей книги с помощью PLEX, а ячейки со списком листов скопировал в шаблон, создал выпадающий список из списка листов. внес изменения в формулу. Не работает((. Кликая на список листов в шаблоне(которые созданы для выпадающего списка) , они не открываются, как если бы просто вставил гипперсылку для открытия книги)
Пока все было в одной книге, формула выглядела так: =ВПР("акт";ДВССЫЛ(BP1&"!$A$14:$Q$67");3;0)
А в идеале, что бы это выглядело так: =ВПР("акт";ДВССЫЛ(отчет, январь&"!$A$14:$Q$67");3;0)
есть еще вариант, использую для печати конвертов:
=СМЕЩ(Адреса!A2:E13;ПОИСКПОЗ(I24;Адреса!A2:A13;0)-1;1;1;1)
второй лист "адреса" с данными для заполнения. "х" не использую"
в ячейках бланка, который надо заполнить меняю только № столбца. проблема нулей решилась просто: в пустых ячейках данных просто ставлю пробел
PS в ячейке I24 проверка данных "список" (ссылаюсь на таблицу листа "адреса"), т.е. выбор возможен только из заполненных строк.
С вордом очен хорошо реализована надстройка с сайта Excel-vba
Сам пользуюсь (но платная)
Возможно ли эту задачу решить наоборот? Разные отделы заполняют форму (требование на закупку) с указанием отдела, кода, описания, суммы итд. Возможно ли составить базу данных из разных файлов и чтобы она автоматически (периодически) обновлялась?
Или собирать данные макросом, но это уже сложнее.
На данный момент я использую функцию рассылки через привязку таблицы excel к бланку в word`е, что не совсем удобно!
есть таблица с 15тыс строк, и бланк, куда с помощью ВПР подставляется таблица - печатается ВРУЧНУЮ
как сделать, что бы строка последовательно подставлялась, печаталась, далее подставлялась новая - и так по кругу
Очень надо, помогите
Но здесь нужно связать бланк в Word с таблицей Excel через пошаговый мастер слияния.
Подскажите как выйти из следующей ситуации и существует ли выход?
Есть форма, в которую подгружается все что нужно мне, есть планшет с пером и установленным офисом 2013, но хочу добавить следующую вещь.
Сотрудник ставит подпись в ячейке экселя в соответствующем поле, и при открытии формы необходимо чтоб соответствующем поле появлялась его подпись напротив соответствующего значения, которая берется из того листа и перечня где он ставил свою подпись пером.
Сори если непонятно объяснил, но нужно понять, возможно ли подобное реализовать.
Как сделать так, чтобы при выборе строки иксом переходя на лист с бланком видеть уже подтянувшуюся информацию, без всяких сохранений.
Я пробовал заменить "x" на "1" - не работает, пишет н/д.
С заполнением единичного бланка все понятно. А как сформировать простыню из бланков, заполненных данными из всего множества строк таблицы? В каждый бланк последовательно заносится информация из одной строки таблицы. Может кто-нибудь реализовывал такое? Заранее благодарю за отклик
Но есть обратная задача - вносить данные в печатную форму и по нажатию какой нибудь кнопки - все улетает в таблицу
А потом когда таблица вырастет уже в базу - по необходимости можно будет воспользоваться описанным рецептом печати формы на выбранную строчку. Люди, Посоветуйте плз. варианты решения обратной задачи?
Помогите с распечатывание. Сделала на подобие такой таблицы, только у меня заполняется Путевой лист, все хорошо заполняеться, но проблема в распечатовании. Там нужно, чтоб распечатовался на одном листе с двух сторон, и не полностью, а половина. Так вот если сразу ставлю двухстороннюю печать на обратной стороне либо перевернуто, либо с другой стороны листа, как это можно исправить? Надеюсь понятно написала. Заранее спасибо!
я скопировал макрос . сделал как вы сказали.
результат
удаляет только на первых двух строках
и впринципе можно обойтись без макроса.
а можно было чтоб наоборот. то есть вводишь данные в форму ( например , расходный ордер ) а на следующем листе сформировалась отчет . сделйте пожалуйста урок на эту тему
Информация по путевым листам для автомобилей.
Ваш пример хорош для выбора данных из одной таблицы, то есть с одного листа.
А можно так, чтобы считывал с одного из 12-ти листов, где мы проставили знак "х"?
А как сделать, что бы заполнялся не один бланк а несколько?
Т.е. ставить не одну галку а сразу например 10, потом создается лист по образцу бланка, в этом листе 10 страниц бланков с разными данными. Добавить 11 галку лист дополняется 11 страницей. и т.д.
Подскажите пожалуйста. неудобно на печать выводить по одному