Как в Экселе подключить набор данных из другой таблицы?

Question

[lukoie]

У меня есть таблица в Экселе с ФИО и определенными цифрами в колонках к каждой строке.
Есть еще одна таблица, там набор текста, где надо вставлять в определенном месте ФИО и цифры из первой таблицы. И в итоге сформировать на печать для каждой строки свою табличку.
Подскажите как именно такую задачу сделать максимально просто и быстро?

Comments

[Xander017]

Как успехи в поиске решения? Ответы не помогли?

[lukoie]

Xander017: нет, остается только слияние в ворде. обойтись только экселем не получилось.

[h8nor]

В Word'е не всегда проще, -
для Слияния следите за форматами в таблице Excel (в частности, чтобы во всей колонке был одинаковый формат).

Answer 1

[Xander017]

Квитанцию делаете в MS Word.
Таблица с данными в MS Excel.
Далее в Word через "Письма и рассылки" -> "Слияние" вставляете данные в квитанции и печатаете.

excelpractic.ru/massovaya-rassylka-sliyanie-s-pomo...

Comments

[lukoie]

Спасибо. Пока я именно так и делаю. Но странно что нельзя обойтись только Экселем, потому и спрашиваю - может я чего недопонимаю. Неужели силами самого Экселя не получится сделать такого массового слияния, как с Вордом?

[h8nor]

Это "слияние" не костыль?! Удачи с ним в многопользовательском доступе и работе с файлами по сети.
Можно всё, - было бы желание изучить.

[lukoie]

Дѣаволъ: да, можно и на руби написать. но зачем, если есть уже таблица в экселе, данные из которой надо подставить в квитанции. Спрашиваю у тех, кто может знать может ли то же самое делать сам Эксель. "Слияние" с данной задачей справляется вполне успешно, без лишнего усложнения. Если бы было УПРОЩЕНИЕ (например силами только Экселя) - вот это был бы ответ на мой вопрос.

[Xander017]

lukoie: ну так на один лист - бланк квитанции. На другом листе - данные по квартирам.
И с помощью ВПР с листа на лист вставляйте нужную информацию.

[lukoie]

Xander017: и как это сделать для 100 квартир, чтобы в итоге квитанции на квартплату распечатывать каждый месяц?

[Xander017]

lukoie: vba скрипт вам в помощь ( goo.gl/7vYTkO ). Но не понимаю, чем не устраивает слияние через Word? На выходе будете получать один файл с данными по всему дому. На печать это дело отправите тоже одной кнопкой.

[lukoie]

Xander017: да, слияние пока устраивает. Думал может это можно делать проще силами самого экселя.

[h8nor]

John Smith: не, с MS Access человек ещё больше запутается. Там порог вхождения выше, чем макросы под MS Excel.
lukoie: В ссылке "Подробно" моего ответа написано, как связать функцию ВПР со списком проверки данных, - выбираете значение из списка, а остальные поля заполнятся автоматически через формулы. Если нужна простыня с квитанциями готовыми к печати - то нужно читать VBA: синтаксис и функции VB и объектную структуру MS Exсel. На этом откланяюсь.

[lukoie]

Xander017: скрипт сегодня вечером буду пробовать, спасибо. Посмотрю насколько удобно окажется.

[lukoie]

John Smith: "Либо не мучатся и использовать MS Access"
Видимо так и придется.
Проблема только что в собес надо отдавать отчеты по субсидиям и льготам, а там дос и дбф формат, так что пока из экселя в эксель. Но наверное проще в Аксесс всё сделать, и в итоге и квитанции и отчеты из него формировать. Попробую сегодня, насколько это удобно получится.

[lukoie]

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

Answer 2

[h8nor]

Если во второй книге есть колонка, где значения совпадают со значениями из колонки в первой книге - функция ВПР должна помочь (таблица должна быть отсортирована по убыванию по нужным полям на случай, если будут найдены дубликаты искомых данных). Подробно.

Comments

[lukoie]

Спасибо, это тоже полезно - я так могу добавить сумму субсидии из отдельной таблицы к таблице с оплатами.

Но вопрос остается - мне нужно сделать из таблицы со сводными суммами квитанции для оплаты. Чтобы автоматически сформировались для каждой строки(квартиры) из таблицы.

[h8nor]

С заданием вам дорога на фриланс.

[lukoie]

Дѣаволъ: ничё подобного. это ж всего лишь эксель, ёлыпалы.

[lukoie]

John Smith: "квитанция" это ровно то же самое что и всегда - счет для оплаты услуг. Их разность ну совсем не имеет значения в данном случае. Главное - сформировать из набора данных набор квитанций.
Если есть 100 квартир, у каждой есть:
ФИО, м2, сумма, субсидия, итого;
то нужно для каждой вартиры автоматически сформировать свой документ на печать.
Ни ВПР ни аналог такого не сделает. А если каким-то образом и извратиться для прикручивания этой функции, то испонение будет большим костылем. Чего уж сразу не батником в досе?
Фриланс тут вообще не при чем, перестаньте.

[h8nor]

Ни ВПР ни аналог такого не сделает

Так вы ж не пробывали.

Чего уж сразу не батником в досе?

Можно и так. Я для пакетной обработки VBS-скрипты пишу через позднее связывание объектов. Разницы особой нет, если есть понимание решения задачи.

[lukoie]

Дѣаволъ: я на нем вчера вечером реализовал передачу данных по субсидиям - чтобы субсидианты и льготники были в отдельных таблицах. Чтобы их только там вертеть, а не в общей таблице, куда внесенные данные сами уже будут подтягиваться.
Но создание квитанций на оплату эти функции не смогут реализовать. По крайней мере чтобы это было удобно и просто, как в "слиянии". Вы представьте себе сделать квитанции для 100-200 квартир таким образом. Удобно ли это будет? Правильно ли выполнять это данным методом?

[Xander017]

lukoie: "Ни ВПР ни аналог такого не сделает." <-- Вот здесь позвольте усомниться))) Либо у вас неправильная структура данных в таблице Excel, либо вы не пробовали.
uploads.ru/O67Zr.gif

[lukoie]

Xander017: Вы себе представляете печатать каждый месяц квитанции для сотни-другой квартир вот таким вот образом?
Тогда уж чего не в Ворде для каждой квартиры вручную менять поля, как делают другие.
Пока я слиянием делаю в пару тыков. Вопрос данной темы исключительно в том, можно ли еще упростить, используя только возможности Экселя. А то что Вы предлагаете это далеко не упрощение задачи. Как на гифке я конечно и сам мог бы сделать, только потом слишком много телодвижений каждый отчетный период для квитанций и отчетов по всем этим собесам, статистикам, налоговым.

[Xander017]

lukoie: Вы не поверите, но я могу себе представить 10000 дополнительных соглашений заполненных от руки по шаблонам... На вкус и цвет как говорится)) Так что сотней-другой квартир и собесом не удивили.
А в гифке ответ на вашу же фразу - "Ни ВПР ни аналог такого не сделает." Не более и чуть-чуть сарказма.

Для ваших целей я бы использовал "Слияние" в Ворде. Уж телодвижений там точно минимум и проще некуда.
Но выбор за вами.

[lukoie]

John Smith: "Либо у вас данные по квартирам уже сформированы, тогда прямая дорога вам в мейл мердж вордовский, как уже написали; что более вероятно - данные не сведены в одну таблицу,"

данные БЫЛИ сведены в одну таблицу. я разнес по трем(льготы, субсидии, и оплаты для всех с учетом льготников и субсидиантов), и есть еще четвертая с данными каждого жильца.
Я ищу наиболее простое, лаконичное и дешевое решение для данной задачи. Да, пока мердж таковым является.

" которую, опять же, можно отправить в вордовский мейл-мердж."

Ну вот, приходится переключаться в Ворд. Я искал возможность обойтись внутренними инструментами Экселя.

Хотя обычно такие вещи делают либо в специализированных системах, либо в БД, да тупо в MS Access, и никаких мейл мерджей не понадобится, свел данные в отчет - и отдыхаешь.

Специализированные - либо досовские(на турбопаскале, думаю сами видели во всяких гос.учреждениях), либо платные, что для данной задачи не стоит того.
Конечно, можно развернуть sql-сервер, сделать бекенд с фронтэндом. Но опять же, избыточно, если для сотни-другой квартир.

"Да ну. Здесь задают вопросы типа...
Бесплатно просят советы, а не выполнить работу. Вот и разница."

Здесь был вопрос именно о совете, и никто выполнить работу не просил - работа и так вполне себе выполняется.

[lukoie]

Xander017: "А в гифке ответ на вашу же фразу - "Ни ВПР ни аналог такого не сделает." Не более и чуть-чуть сарказма. "

эта моя фраза была сразу после предложения, чего именно не сделает. Так вот гифка показывает костыль, но не решение того, что описывалось там на предложение выше.
Так ведь можно дойти и до утверждения что на мой вопрос может быть решением блокнот.екзе! И гифку в подтверждение.
: )

[lukoie]

Xander017: John Smith: Дѣаволъ: и кстати, упираемся в ограничение:
[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". В скриншотной таблице он автоматически суммирует, а функция ВПР такую строку не видит, потому результат не подставляет.

[lukoie]

Xander017 John Smith: Дѣаволъ: и кстати, упираемся в ограничение:
s018.radikal.ru/i514/1611/73/4f73eb9ba053.png
если в таблице с субсидиями автоматически считается сумма по месяцам, то итоговая сумма не воспринимается функцией ВПР
То есть вот такой запрос:
=IFERROR(VLOOKUP($J$2:$J$91,'03821016.xlsx'!$J$2:$AX$165,41,FALSE),0)
не отдаст результат, если сравнивать по запросу "4 Total", как на картинке. В скриншотной таблице он автоматически суммирует, а функция ВПР такую строку не видит, потому результат не подставляет.

[h8nor]

lukoie: У вас ошибка в фотохостинге

[lukoie]

Дѣаволъ: та нет там ошибки. адблок видимо ваш.
Вот в коммент прямо вставляю ссылку с фотохостинга:
s018.radikal.ru/i514/1611/73/4f73eb9ba053.png

[h8nor]

lukoie: adblock, да мой любимый - не отключается ))
Из описания и обрезка картинки я ничего не понял. Вам John Smith написал, что вы поленились сделать ТЗ, поэтому и у вас ничего не получается, и мы не понимаем какие данные откуда получить.
ТЗ нужно писать просто и понятно, как задачи по математики для начальной школы.

К тому же вы не разобрались с функцией VLOOKUP, у вас в 1-м аргументе указан массив, а должно быть значение или ссылка на ячейку. В нижней части дано описание к аргументу и ссылка на полное описание по работе с функцией.

[lukoie]

Дѣаволъ: глупости какие. в первом аргументе таки указывается МАССИВ, который нужно сравнивать со вторым массивом(первой колонкой). В первом аргументе колонка с номерами квартир. Во втором - несколько колонок, первая из которых тоже номера квартир. Итак, если номера квартир совпали - брать сумму к оплате из колонки номер такой то во втором массиве. Это ж элементарно.
Видимо таки Вы не разобрались с этой функцией, т.к. у меня на ней полностью все расчеты по квартплатам настроены. Т.е. именно эта таблица подключается для мерджа в ворд. И в этой таблице делается сверка по двум дополнительным таблицам - льготников и субсидиантов.

Проблема в том, что в таблице субсидиантов указана сверка с остачами, т.е. СОБЕС отдает таблицу в которой по одной квартире дано пару сумм субсидии. Т.е. если в лоб брать оттуда размер субсидии - мы получим только первую цифру.
Значит надо в таблице с субсидиями суммировать цифры по каждой из квартир.
Делается это вот таким одним телодвижением:

И в ответ получаем таблицу как на скрине выше.
Чтобы забрать нашу итоговую сумму, нам надо уже делать сверку не по номеру квартиры, а по ячейке "№ Total" которую сформировал предыдущей шаг.
Вот так и живем... Надо добиться чтобы в графу субсидии вставилась сумма, автоматически сформированная "субтоталом".