Эксель заполнение таблицы из справочника
При заполнении ячеек данными иногда необходимо ограничить возможность ввода определенным списком значений. Например, при заполнении ведомости ввод фамилий сотрудников с клавиатуры можно заменить выбором из определенного заранее списка (табеля).
Одним из вариантов заполнения ячеек является выбор значений из заранее определенного списка в MS EXCEL. Предположим, что в таблицу ведомости необходимо вводить фамилии сотрудников. Чтобы не ошибиться с написанием фамилий можно предварительно создать список всех сотрудников организации, а заполнение ведомости свести к выбору фамилии из этого списка.
Инструмент Проверка данных (Данные/ Работа с данными/ Проверка данных ) с условием проверки Список , как раз предназначен для решения нашей задачи: с помощью него можно создать Выпадающий (раскрывающийся) список (см. Файл примера ).
СОВЕТ : Если в будущем потребуется пополнять список сотрудников, то придется вручную модифицировать границы именованного диапазона. Как обойти это неудобство читайте в статье Динамический диапазон .
Теперь создадим Выпадающий список для ввода фамилий в ведомость:
- выделите ячейки ведомости, куда будут вводиться фамилии сотрудников, например А2:А5 ;
- вызовите инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных );
- установите условие проверки Список ;
- в поле Источник введите =Сотрудники ;
Теперь при выделении любой ячейки из диапазона А2:А5 , справа от ячейки будет появляться кнопка со стрелкой, нажав на которую можно выбрать необходимую фамилию.
Недостатком этого решения является то, что у пользователя есть потенциальная возможность ввести в ведомость повторяющиеся фамилии. Для того, чтобы контролировать появление повторяющихся фамилий, можно использовать идеи из статьи Ввод данных из списка значений. Часть 2. Выпадающий список с контролем дублирования .
Имеем базу данных (список, таблицу - называйте как хотите) с информацией по платежам на листе Данные:
Задача: быстро распечатывать приходно-кассовый ордер (платежку, счет-фактуру. ) для любой нужной записи выбранной из этого списка. Поехали!
Шаг 1. Создаем бланк
На другом листе книги (назовем этот лист Бланк) создаем пустой бланк. Можно самостоятельно, можно воспользоваться готовыми бланками, взятыми, например, с сайтов журнала "Главный Бухгалтер" или сайта Microsoft. У меня получилось примерно так:
В пустые ячейки (Счет, Сумма, Принято от и т.д.) будут попадать данные из таблицы платежей с другого листа - чуть позже мы этим займемся.
Шаг 2. Подготовка таблицы платежей
Прежде чем брать данные из таблицы для нашего бланка, таблицу необходимо слегка модернизировать. А именно - вставить пустой столбец слева от таблицы. Мы будем использовать для ввода метки (пусть это будет английская буква "икс") напротив той строки, данные из которой мы хотим добавить в бланк:
Шаг 3. Связываем таблицу и бланк
Для связи используем функцию ВПР (VLOOKUP) - подробнее про нее можно почитать здесь. В нашем случае для того, чтобы вставить в ячейку F9 на бланке номер помеченного "x" платежа с листа Данные надо ввести в ячейку F9 такую формулу:
Т.е. в переводе на "русский понятный" функция должна найти в диапазоне A2:G16 на листе Данные строку, начинающуюся с символа "х" и выдать нам содержимое второго столбца этой строки, т.е. номер платежа.
Аналогичным образом заполняются все остальные ячейки на бланке - в формуле меняется только номер столбца.
В итоге должно получиться следующее:
Шаг 4. Чтобы не было двух "х".
Если пользователь введет "х" напротив нескольких строк, то функция ВПР будет брать только первое найденное значение. Чтобы не было такой многозначности, щелкните правой кнопкой мыши по ярлычку листа Данные и выберите Исходный текст (Source Code) . В появившееся окно редактора Visual Basic скопируйте следующий код:
Этот макрос не дает пользователю ввести больше одного "х" в первый столбец.
Квитанцию делаете в MS Word.
Таблица с данными в MS Excel.
Далее в Word через "Письма и рассылки" -> "Слияние" вставляете данные в квитанции и печатаете.
Можно всё, - было бы желание изучить.
И с помощью ВПР с листа на лист вставляйте нужную информацию.
Либо не мучатся и использовать мейл мердж.
Либо не мучатся и использовать MS Access, т.к. это типичная задача вида "набор данных - отчет", а Excel - это _табличный_ процессор, который хоть и можно использовать для задач, решаемых базами данных, но не без издержек. lukoie: vba скрипт вам в помощь ( goo.gl/7vYTkO ). Но не понимаю, чем не устраивает слияние через Word? На выходе будете получать один файл с данными по всему дому. На печать это дело отправите тоже одной кнопкой.
lukoie: В ссылке "Подробно" моего ответа написано, как связать функцию ВПР со списком проверки данных, - выбираете значение из списка, а остальные поля заполнятся автоматически через формулы. Если нужна простыня с квитанциями готовыми к печати - то нужно читать VBA: синтаксис и функции VB и объектную структуру MS Exсel. На этом откланяюсь.
Видимо так и придется.
Проблема только что в собес надо отдавать отчеты по субсидиям и льготам, а там дос и дбф формат, так что пока из экселя в эксель. Но наверное проще в Аксесс всё сделать, и в итоге и квитанции и отчеты из него формировать. Попробую сегодня, насколько это удобно получится.
lukoie:
"проще в Аксесс всё сделать"
если знать Access. Если есть время, гугл, и базовое понимание принципов устройства баз данных - не так трудно. Если нет - сложнее.
"в итоге и квитанции и отчеты из него формировать"
В данном контексте квитанция и будет отчетом (или одним из отчетов). Отчет суть документ, формируемый на основании определенных полей записей. Т.е. если у вас есть в итоге таблица с данными для квитанций вида "номер квартиры - сумма - что там еще есть", то на основании ее и отчета формируются заполненные квитанции, по количеству нужных записей. Их можно печатать, при должном умении - экспортировать в пдф, эксель, word.
Надо найти ту золотую серединку, чтобы эта работа выполнялась удобно и быстро. Потому что мне кажется что сейчас чего-то не хватает, или что-то лишнее. В любом случае, формирование документов сейчас выглядит шаманством.
Спасибо, это тоже полезно - я так могу добавить сумму субсидии из отдельной таблицы к таблице с оплатами.
Но вопрос остается - мне нужно сделать из таблицы со сводными суммами квитанции для оплаты. Чтобы автоматически сформировались для каждой строки(квартиры) из таблицы.
lukoie: это ж всего лишь эксель
Во-первых, ваше ТЗ крайне короткое. Что такое "квитанция"? Они бывают очень разные. И если у вас есть все данные - чего не хватает?
Во-вторых, с помощью ВПР или аналогов (index-match) можно не только добавить "сумму субсидии", а получить любые данные из другой таблицы.
В-третьих, таки на фриланс, т.к. выполнение задания, которое вы не можете делать сами, и по которому нужно делать ТЗ - именно для фриланса.
Если есть 100 квартир, у каждой есть:
ФИО, м2, сумма, субсидия, итого;
то нужно для каждой вартиры автоматически сформировать свой документ на печать.
Ни ВПР ни аналог такого не сделает. А если каким-то образом и извратиться для прикручивания этой функции, то испонение будет большим костылем. Чего уж сразу не батником в досе?
Фриланс тут вообще не при чем, перестаньте.
Но создание квитанций на оплату эти функции не смогут реализовать. По крайней мере чтобы это было удобно и просто, как в "слиянии". Вы представьте себе сделать квитанции для 100-200 квартир таким образом. Удобно ли это будет? Правильно ли выполнять это данным методом?
Хотя обычно такие вещи делают либо в специализированных системах, либо в БД, да тупо в MS Access, и никаких мейл мерджей не понадобится, свел данные в отчет - и отдыхаешь.
"Фриланс тут вообще не при чем, перестаньте."
Да ну. Здесь задают вопросы типа:
- какой функцией воспользоваться, чтобы получить А из таблицы Б по идентификатору С
- почему не работает %вот_такая_описанная_в_вопросе_реализация%
- как лучше сделать - так или эдак?
А у вас явно рабочая задача, которую можно решить, имея ТЗ. Которое вы, кстати, поленились сделать - скриншот или перечень таблиц, столбцов, с примером данных, скрин квитанции ну никак не помешали бы. Ну понятно, тогда бы это окончательно стало бы похоже на задание, которое вы сами сделать не хотите\не можете, а просите сделать других, бесплатно.
Бесплатно просят советы, а не выполнить работу. Вот и разница.
John Smith: соответственно, Xander017 дал вам отличный ответ, как сделать квитанции на основе одной сведенной таблицы.
Тогда уж чего не в Ворде для каждой квартиры вручную менять поля, как делают другие.
Пока я слиянием делаю в пару тыков. Вопрос данной темы исключительно в том, можно ли еще упростить, используя только возможности Экселя. А то что Вы предлагаете это далеко не упрощение задачи. Как на гифке я конечно и сам мог бы сделать, только потом слишком много телодвижений каждый отчетный период для квитанций и отчетов по всем этим собесам, статистикам, налоговым.
lukoie: Вы не поверите, но я могу себе представить 10000 дополнительных соглашений заполненных от руки по шаблонам. На вкус и цвет как говорится)) Так что сотней-другой квартир и собесом не удивили.
А в гифке ответ на вашу же фразу - "Ни ВПР ни аналог такого не сделает." Не более и чуть-чуть сарказма.
Для ваших целей я бы использовал "Слияние" в Ворде. Уж телодвижений там точно минимум и проще некуда.
Но выбор за вами.
John Smith: "Либо у вас данные по квартирам уже сформированы, тогда прямая дорога вам в мейл мердж вордовский, как уже написали; что более вероятно - данные не сведены в одну таблицу,"
данные БЫЛИ сведены в одну таблицу. я разнес по трем(льготы, субсидии, и оплаты для всех с учетом льготников и субсидиантов), и есть еще четвертая с данными каждого жильца.
Я ищу наиболее простое, лаконичное и дешевое решение для данной задачи. Да, пока мердж таковым является.
Ну вот, приходится переключаться в Ворд. Я искал возможность обойтись внутренними инструментами Экселя.
Хотя обычно такие вещи делают либо в специализированных системах, либо в БД, да тупо в MS Access, и никаких мейл мерджей не понадобится, свел данные в отчет - и отдыхаешь.
Специализированные - либо досовские(на турбопаскале, думаю сами видели во всяких гос.учреждениях), либо платные, что для данной задачи не стоит того.
Конечно, можно развернуть sql-сервер, сделать бекенд с фронтэндом. Но опять же, избыточно, если для сотни-другой квартир.
"Да ну. Здесь задают вопросы типа.
Бесплатно просят советы, а не выполнить работу. Вот и разница."
Здесь был вопрос именно о совете, и никто выполнить работу не просил - работа и так вполне себе выполняется.
Xander017: "А в гифке ответ на вашу же фразу - "Ни ВПР ни аналог такого не сделает." Не более и чуть-чуть сарказма. "
эта моя фраза была сразу после предложения, чего именно не сделает. Так вот гифка показывает костыль, но не решение того, что описывалось там на предложение выше.
Так ведь можно дойти и до утверждения что на мой вопрос может быть решением блокнот.екзе! И гифку в подтверждение.
: )
[url=http://s018.radikal.ru/i514/1611/73/4f73eb9ba053.p.
если таблица с субсидиями считает суммы, то итоговая сумма не воспринимается функцией ВПР
То есть вот такой запрос:
=IFERROR(VLOOKUP($J$2:$J$91,'03821016.xlsx'!$J$2:$AX$165,41,FALSE),0)
не отдаст результат, если сравнивать по запросу "4 Total". В скриншотной таблице он автоматически суммирует, а функция ВПР такую строку не видит, потому результат не подставляет.
s018.radikal.ru/i514/1611/73/4f73eb9ba053.jpg
если в таблице с субсидиями автоматически считается сумма по месяцам, то итоговая сумма не воспринимается функцией ВПР
То есть вот такой запрос:
=IFERROR(VLOOKUP($J$2:$J$91,'03821016.xlsx'!$J$2:$AX$165,41,FALSE),0)
не отдаст результат, если сравнивать по запросу "4 Total", как на картинке. В скриншотной таблице он автоматически суммирует, а функция ВПР такую строку не видит, потому результат не подставляет.
lukoie: adblock, да мой любимый - не отключается ))
Из описания и обрезка картинки я ничего не понял. Вам John Smith написал, что вы поленились сделать ТЗ, поэтому и у вас ничего не получается, и мы не понимаем какие данные откуда получить.
ТЗ нужно писать просто и понятно, как задачи по математики для начальной школы.
К тому же вы не разобрались с функцией VLOOKUP, у вас в 1-м аргументе указан массив, а должно быть значение или ссылка на ячейку. В нижней части дано описание к аргументу и ссылка на полное описание по работе с функцией.
Дѣаволъ: глупости какие. в первом аргументе таки указывается МАССИВ, который нужно сравнивать со вторым массивом(первой колонкой). В первом аргументе колонка с номерами квартир. Во втором - несколько колонок, первая из которых тоже номера квартир. Итак, если номера квартир совпали - брать сумму к оплате из колонки номер такой то во втором массиве. Это ж элементарно.
Видимо таки Вы не разобрались с этой функцией, т.к. у меня на ней полностью все расчеты по квартплатам настроены. Т.е. именно эта таблица подключается для мерджа в ворд. И в этой таблице делается сверка по двум дополнительным таблицам - льготников и субсидиантов.
Проблема в том, что в таблице субсидиантов указана сверка с остачами, т.е. СОБЕС отдает таблицу в которой по одной квартире дано пару сумм субсидии. Т.е. если в лоб брать оттуда размер субсидии - мы получим только первую цифру.
Значит надо в таблице с субсидиями суммировать цифры по каждой из квартир.
Делается это вот таким одним телодвижением:
И в ответ получаем таблицу как на скрине выше.
Чтобы забрать нашу итоговую сумму, нам надо уже делать сверку не по номеру квартиры, а по ячейке "№ Total" которую сформировал предыдущей шаг.
Вот так и живем. Надо добиться чтобы в графу субсидии вставилась сумма, автоматически сформированная "субтоталом".
Довольно часто требуется рассчитать итоговый результат для различных комбинаций вводных данных. Таким образом пользователь сможет оценить все возможные варианты действий, отобрать те, результат взаимодействия которых его удовлетворяет, и, наконец, выбрать самый оптимальный вариант. В Excel для выполнения данной задачи существует специальный инструмент – «Таблица данных» («Таблица подстановки»). Давайте узнаем, как им пользоваться для выполнения указанных выше сценариев.
Использование таблицы данных
Таблицу подстановки можно использовать во многих случаях. Например, типичный вариант, когда нужно рассчитать сумму ежемесячного платежа по кредиту при различных вариациях периода кредитования и суммы займа, либо периода кредитования и процентной ставки. Также этот инструмент можно использовать при анализе моделей инвестиционных проектов.
Но также следует знать, что чрезмерное применение данного инструмента может привести к торможению системы, так как пересчет данных производится постоянно. Поэтому рекомендуется в небольших табличных массивах для решения аналогичных задач не использовать этот инструмент, а применять копирование формул с помощью маркера заполнения.
Оправданным применение «Таблицы данных» является только в больших табличных диапазонах, когда копирование формул может отнять большое количество времени, а во время самой процедуры увеличивается вероятность допущения ошибок. Но и в этом случае рекомендуется в диапазоне таблицы подстановки отключить автоматический пересчет формул, во избежание излишней нагрузки на систему.
Главное отличие между различными вариантами применения таблицы данных состоит в количестве переменных, принимающих участие в вычислении: одна переменная или две.
Способ 1: применение инструмента с одной переменной
Сразу давайте рассмотрим вариант, когда таблица данных используется с одним переменным значением. Возьмем наиболее типичный пример с кредитованием.
Итак, в настоящее время нам предлагаются следующие условия кредитования:
- Срок кредитования – 3 года (36 месяцев);
- Сумма займа – 900000 рублей;
- Процентная ставка – 12,5% годовых.
Выплаты происходят в конце платежного периода (месяца) по аннуитетной схеме, то есть, равными долями. При этом, вначале всего срока кредитования значительную часть выплат составляют процентные платежи, но по мере сокращения тела процентные платежи уменьшаются, а увеличивается размер погашения самого тела. Общая же выплата, как уже было сказано выше, остается без изменений.
Нужно рассчитать, какова будет сумма ежемесячного платежа, включающего в себя погашение тела кредита и выплат по процентам. Для этого в Экселе имеется оператор ПЛТ.
ПЛТ относится к группе финансовых функций и его задачей является вычисление ежемесячного кредитного платежа аннуитетного типа на основании суммы тела кредита, срока кредитования и процентной ставки. Синтаксис этой функции представлен в таком виде
-
Итак, приступаем к расчету. Выделяем ячейку на листе, куда будет выводиться расчетное значение. Клацаем по кнопке «Вставить функцию».
Ставим курсор в поле «Ставка», после чего кликаем по ячейке на листе со значением годовой процентной ставки. Как видим, в поле тут же отображаются её координаты. Но, как мы помним, нам нужна месячная ставка, а поэтому производим деление полученного результата на 12 (/12).
В поле «Кпер» таким же образом вносим координаты ячеек срока кредита. В этом случае делить ничего не надо.
В поле «Пс» нужно указать координаты ячейки, содержащей величину тела кредита. Выполняем это. Также ставим перед отобразившемся координатами знак «-». Дело в том, что функция ПЛТ по умолчанию выдает итоговый результат именно с отрицательным знаком, справедливо считая ежемесячный кредитный платеж убытком. Но нам для наглядности применения таблицы данных нужно, чтобы данное число было положительным. Поэтому мы и ставим знак «минус» перед одним из аргументов функции. Как известно, умножение «минус» на «минус» в итоге дает «плюс».
Кроме того, можно заметить, что величина ежемесячного платежа при 12.5% годовых, полученная в результате применения таблицы подстановок, соответствует величине при том же размере процентов, которую мы получили путем применения функции ПЛТ. Это лишний раз доказывает правильность расчета.
Проанализировав данный табличный массив, следует сказать, что, как видим, только при ставке 9,5% годовых получается приемлемый для нас уровень ежемесячного платежа (менее 29000 рублей).
Способ 2: использование инструмента с двумя переменными
Конечно, отыскать в настоящее время банки, которые выдают кредит под 9,5% годовых, очень сложно, если вообще реально. Поэтому посмотрим, какие варианты существуют вложиться в приемлемый уровень ежемесячного платежа при различных комбинациях других переменных: величины тела займа и срока кредитования. При этом процентную ставку оставим неизменной (12,5%). В решении данной задачи нам поможет инструмент «Таблица данных» с использованием двух переменных.
Проанализировав табличный массив, можно сделать некоторые выводы. Как видим, при существующем сроке кредитования (36 месяцев), чтобы вложиться в выше обозначенную сумму ежемесячного платежа, нам нужно взять заём не превышающий 860000,00 рублей, то есть, на 40000 меньше первоначально запланированного.
Если же мы все-таки намерены брать кредит размером 900000 рублей, то срок кредитования должен составлять 4 года (48 месяцев). Только в таком случае размер ежемесячного платежа не превысит установленную границу в 29000 рублей.
Таким образом, воспользовавшись данным табличным массивом и проанализировав «за» и «против» каждого варианта, заёмщик может принять конкретное решение об условиях кредитования, выбрав наиболее отвечающий его пожеланиям вариант из всех возможных.
Конечно, таблицу подстановок можно использовать не только для расчета кредитных вариантов, но и для решения множества других задач.
В общем, нужно отметить, что таблица подстановок является очень полезным и сравнительно простым инструментом для определения результата при различных комбинациях переменных. Применив одновременно с ним условное форматирование, кроме того, можно визуализировать полученную информацию.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: