Автоматическое заполнение ячеек в excel из другого файла
Имеются расценки различных фирм (листы Расценки 1, Расценки 2, Расценки 3) на строительные работы. Фирмы специализируются на определенном типе работ, например, первая - малярно-штукатурные, вторая - отделочные, третья - общестроительные. Структура расценок в целом схожая, но могут быть отличия в очередности столбцов и их названии.
На основании этих расценок необходимо создать смету, которая включает некоторые работы из расценок строительных фирм. В результате должна получиться единая смета, покрывающая индивидуальный объём работ и включающий работы из разных типов. Список работ в смете должен содержаться в столбце Наименование работ (лист Смета). Для заполнения ячеек в Название работ используются ссылки на выбранные работы соответствующих типов. Например, B2 = ='Расценки 1'!B3.
Задача
При заполнении ячейки в Наименовании работ, автоматически заполнить другие столбцы (Тип ресурса, Ед.изм., Ст-ть за ед.) на основе данных, указанных в соответствующих строках на листах Расценки 1, 2, 3.
При необходимости упростить задачу, можно менять очередность столбцов на вкладках Расценки, а также привести их название к единообразному виду.
Скопировать все расценки на один лист, приведя их к единой структуре, на практике не всегда удобно/возможно.
Как я пытался найти решение
1. Из ссылки ячейки в столбце B (Смета) попробовал выбирать имя листа, но который ссылается конкретная ячейка, пользовался для этого пришлось добавить макрос с переводом содержимого формулы ячейки в текстовую строку.
2. Далее полученный результат обрабатывал функцией ПОИСК, чтобы получить название листа в виде !Расценки 2!.
3. Затем в функции ИНДЕКС и ПОИСКПОЗ передавал название искомой работы из столба B (Смета) и ссылку на нужный лист, по которому нужно провести поиск значений для соответствующих столбцов (Тип ресурса, Ед.изм., Ст-ть за ед.)
Всё заработало, но получилась монструозная формула, которой невозможно управлять. Вот пример из реальной книги.
Еще одним недостатком моего решения является необходимость использования макроса для перевода содержимого формулы ячейки в текстовую строку. Аналогичная функция есть в Excel 2013, но к сожалению необходима поддержка Excel 2010.
Вопрос
Есть ли варианты более элегантного и управляемого решения данной задачи? То, которое я сделал не подходит совсем.
На одном из листов рабочей книги Excel, находиться база информации регистрационных данных служебных автомобилей. На втором листе ведется регистр делегации, где вводятся личные данные сотрудников и автомобилей. Один из автомобилей многократно используют сотрудники и каждый раз вводит данные в реестр – это требует лишних временных затрат для оператора. Лучше автоматизировать этот процесс. Для этого нужно создать такую формулу, которая будет автоматически подтягивать информацию об служебном автомобиле из базы данных.
Автозаполнение ячеек данными в Excel
Для наглядности примера схематически отобразим базу регистрационных данных:
Как описано выше регистр находится на отдельном листе Excel и выглядит следующим образом:
Здесь мы реализуем автозаполнение таблицы Excel. Поэтому обратите внимание, что названия заголовков столбцов в обеих таблицах одинаковые, только перетасованы в разном порядке!
Теперь рассмотрим, что нужно сделать чтобы после ввода регистрационного номера в регистр как значение для ячейки столбца A, остальные столбцы автоматически заполнились соответствующими значениями.
Как сделать автозаполнение ячеек в Excel:
- На листе «Регистр» введите в ячейку A2 любой регистрационный номер из столбца E на листе «База данных».
- Теперь в ячейку B2 на листе «Регистр» введите формулу автозаполнения ячеек в Excel:
- Скопируйте эту формулу во все остальные ячейки второй строки для столбцов C, D, E на листе «Регистр».
В результате таблица автоматически заполнилась соответствующими значениями ячеек.
Принцип действия формулы для автозаполнения ячеек
Главную роль в данной формуле играет функция ИНДЕКС. Ее первый аргумент определяет исходную таблицу, находящуюся в базе данных автомобилей. Второй аргумент – это номер строки, который вычисляется с помощью функции ПОИСПОЗ. Данная функция выполняет поиск в диапазоне E2:E9 (в данном случаи по вертикали) с целью определить позицию (в данном случаи номер строки) в таблице на листе «База данных» для ячейки, которая содержит тоже значение, что введено на листе «Регистр» в A2.
Третий аргумент для функции ИНДЕКС – номер столбца. Он так же вычисляется формулой ПОИСКПОЗ с уже другими ее аргументами. Теперь функция ПОИСКПОЗ должна возвращать номер столбца таблицы с листа «База данных», который содержит название заголовка, соответствующего исходному заголовку столбца листа «Регистр». Он указывается ссылкой в первом аргументе функции ПОИСКПОЗ – B$1. Поэтому на этот раз выполняется поиск значения только по первой строке A$1:E$1 (на этот раз по горизонтали) базы регистрационных данных автомобилей. Определяется номер позиции исходного значения (на этот раз номер столбца исходной таблицы) и возвращается в качестве номера столбца для третьего аргумента функции ИНДЕКС.
Благодаря этому формула будет работать даже если порядок столбцов будет перетасован в таблице регистра и базы данных. Естественно формула не будет работать если не будут совпадать названия столбцов в обеих таблицах, по понятным причинам.
Всем добрый день, вопрос в следующем.
Есть две книги, пускай они называются "А" и "Б", в каждой книге три листа, допустим "день", "неделя" и "месяц".
Книги идентичные по структуре(в плане таблиц, формул и прочего), отличаются только цифровые данные ячеек.
Есть еще одна книга(назовем ее "общая"), в которой есть список книг(в нашем случае это А и Б) и список периодов(в нашем случае день, неделя, месяц). В этой же книге созданы два выпадающих списка, один с названием книг, второй с периодами).
Вопрос первый, нужно чтобы при выборе из выпадающего списка определенной книги и определенного периода, ячейки(пускай А1-А10) в книге "общая", ссылалась на конкретный лист и диапазон ячеек указаной в выпадающем списке книге.
И второй, нужно сделать так, чтобы при создании дополнительной книги "N"(идентичной А и Б),добавлении ее в выпадающий список и выборе в нем этой книги, ячейки А1-А10 в книге "общая" начинали бы ссылаться на нее. И т.д. с n-ым количеством книг.
Всем добрый день, вопрос в следующем.
Есть две книги, пускай они называются "А" и "Б", в каждой книге три листа, допустим "день", "неделя" и "месяц".
Книги идентичные по структуре(в плане таблиц, формул и прочего), отличаются только цифровые данные ячеек.
Есть еще одна книга(назовем ее "общая"), в которой есть список книг(в нашем случае это А и Б) и список периодов(в нашем случае день, неделя, месяц). В этой же книге созданы два выпадающих списка, один с названием книг, второй с периодами).
Вопрос первый, нужно чтобы при выборе из выпадающего списка определенной книги и определенного периода, ячейки(пускай А1-А10) в книге "общая", ссылалась на конкретный лист и диапазон ячеек указаной в выпадающем списке книге.
И второй, нужно сделать так, чтобы при создании дополнительной книги "N"(идентичной А и Б),добавлении ее в выпадающий список и выборе в нем этой книги, ячейки А1-А10 в книге "общая" начинали бы ссылаться на нее. И т.д. с n-ым количеством книг. SV_fep
Есть две книги, пускай они называются "А" и "Б", в каждой книге три листа, допустим "день", "неделя" и "месяц".
Книги идентичные по структуре(в плане таблиц, формул и прочего), отличаются только цифровые данные ячеек.
Есть еще одна книга(назовем ее "общая"), в которой есть список книг(в нашем случае это А и Б) и список периодов(в нашем случае день, неделя, месяц). В этой же книге созданы два выпадающих списка, один с названием книг, второй с периодами).
Вопрос первый, нужно чтобы при выборе из выпадающего списка определенной книги и определенного периода, ячейки(пускай А1-А10) в книге "общая", ссылалась на конкретный лист и диапазон ячеек указаной в выпадающем списке книге.
И второй, нужно сделать так, чтобы при создании дополнительной книги "N"(идентичной А и Б),добавлении ее в выпадающий список и выборе в нем этой книги, ячейки А1-А10 в книге "общая" начинали бы ссылаться на нее. И т.д. с n-ым количеством книг. Автор - SV_fep
Дата добавления - 23.05.2013 в 18:58
Достаточно использовать функцию =ДВССЫЛ() для формирования ссылки на ячейку. Недостаток - книги, на которые ссылается "Общая" - должны быть тоже открыты.
Достаточно использовать функцию =ДВССЫЛ() для формирования ссылки на ячейку. Недостаток - книги, на которые ссылается "Общая" - должны быть тоже открыты. AndreTM
AndreTM, а можно текстовый пример?чтобы, допустим, ячейка А1 при значении ячейки А2=111 и значении ячейки А3=Лист2, ссылалась на документ "111", на второй лист и брала значение из ячейки А1. При значении А2=112, на документ "112" и т.д.
AndreTM, а можно текстовый пример?чтобы, допустим, ячейка А1 при значении ячейки А2=111 и значении ячейки А3=Лист2, ссылалась на документ "111", на второй лист и брала значение из ячейки А1. При значении А2=112, на документ "112" и т.д. SV_fep
= Мир MS Excel/Статьи об Excel
Приёмы работы с книгами, листами, диапазонами, ячейками [6] |
Приёмы работы с формулами [13] |
Настройки Excel [3] |
Инструменты Excel [4] |
Интеграция Excel с другими приложениями [4] |
Форматирование [1] |
Выпадающие списки [2] |
Примечания [1] |
Сводные таблицы [1] |
Гиперссылки [1] |
Excel и интернет [1] |
Excel для Windows и Excel для Mac OS [2] |
- в таблице не должно быть объединенных ячеек. Вернее сказать так: ЕСЛИ в таблице есть объединённые ячейки, то надо быть готовым к тому, что при экспорте объединение будет отменено, и соответственно образуются лишние пустые строки и/или столбцы, что может нарушить структуру таблицы. В общем, объединённые ячейки - это зло :)
- все столбцы должны иметь уникальные названия, которые будут использоваться при слиянии. Если в таблице отсутствует первая строка с названиями столбцов, то её заменит первая строка данных, а значит, она в рассылке участвовать не будет.
На этом этапе в текстовом редакторе Word формируется документ, в который в дальнейшем будут внедряться данные электронной таблицы. Текст этого документа представляет собой общую для всех рассылок часть.
Предположим всем клиентам, у которых срок действия клубной карты истекает в следующем месяце, планируется разослать письма с уведомлением.
Текст письма будет одинаковым за исключением обращения, номера клубной карты и даты окончания её действия. Эти данные будут импортироваться из таблицы Excel (выделено синим)
Для более удобной дальнейшей работы при слиянии рекомендуется установить параметр Затенение полей в положение Всегда, чтобы отличать вставленные поля слияния от обычного текста. Если этот параметр включен, поля отображаются на сером фоне. На печать этот фон, естественно, не выводится.
Проще всего осуществить слияние данных, следуя указаниям Мастера слияния. В версиях после Word2003 Мастер слияния запускается с помощью кнопки Начать слияние на вкладке Рассылки
В версиях до Word2007 следует выполнить команду меню Сервис -- Письма и рассылки -- Слияние. Кроме того, для более удобной работы версиях до Word2007 можно вывести панель инструментов Слияние
Ещё раз обращаю внимание, что в бланке письма содержится только общий для всех писем текст, поэтому обращение выглядит как Уважаем!, а номер карты и дата пропущены.
Выбираем поле Имя, нажимаем Вставить, то же самое для поля Отчество. Закрываем окно Вставка полей слияния и добавляем пробелы между вставленными полями. Если параметр Затенение полей установлен в положение Всегда, то вставленные поля будут отчетливо видны на сером фоне. Устанавливаем курсор после №, снова нажимаем ссылку Другие элементы. , выбираем № клубной карты - Вставить. Аналогично вставляем поле Дата окончания действия карты
Кроме указанных выше полей требуется вставить окончание обращения ый(ая), которое зависит от значения поля Пол. Для этого воспользуемся специальным полем, позволяющим вставлять одно из двух значений в зависимости от данных. Поставим курсор сразу после слова "Уважаем", нажмём кнопку Правила на вкладке Рассылки и выберем вариант IF. THEN. ELSE. В версиях до Word2007 аналогичная кнопка называется Добавить поле Word и находится на панели инструментов Слияние
В раскрывшемся диалоговом окне зададим параметры
После нажатия ОК, получим результат
Имеем базу данных (список, таблицу - называйте как хотите) с информацией по платежам на листе Данные:
Задача: быстро распечатывать приходно-кассовый ордер (платежку, счет-фактуру. ) для любой нужной записи выбранной из этого списка. Поехали!
Шаг 1. Создаем бланк
На другом листе книги (назовем этот лист Бланк) создаем пустой бланк. Можно самостоятельно, можно воспользоваться готовыми бланками, взятыми, например, с сайтов журнала "Главный Бухгалтер" или сайта Microsoft. У меня получилось примерно так:
В пустые ячейки (Счет, Сумма, Принято от и т.д.) будут попадать данные из таблицы платежей с другого листа - чуть позже мы этим займемся.
Шаг 2. Подготовка таблицы платежей
Прежде чем брать данные из таблицы для нашего бланка, таблицу необходимо слегка модернизировать. А именно - вставить пустой столбец слева от таблицы. Мы будем использовать для ввода метки (пусть это будет английская буква "икс") напротив той строки, данные из которой мы хотим добавить в бланк:
Шаг 3. Связываем таблицу и бланк
Для связи используем функцию ВПР (VLOOKUP) - подробнее про нее можно почитать здесь. В нашем случае для того, чтобы вставить в ячейку F9 на бланке номер помеченного "x" платежа с листа Данные надо ввести в ячейку F9 такую формулу:
Т.е. в переводе на "русский понятный" функция должна найти в диапазоне A2:G16 на листе Данные строку, начинающуюся с символа "х" и выдать нам содержимое второго столбца этой строки, т.е. номер платежа.
Аналогичным образом заполняются все остальные ячейки на бланке - в формуле меняется только номер столбца.
В итоге должно получиться следующее:
Шаг 4. Чтобы не было двух "х".
Если пользователь введет "х" напротив нескольких строк, то функция ВПР будет брать только первое найденное значение. Чтобы не было такой многозначности, щелкните правой кнопкой мыши по ярлычку листа Данные и выберите Исходный текст (Source Code) . В появившееся окно редактора Visual Basic скопируйте следующий код:
Этот макрос не дает пользователю ввести больше одного "х" в первый столбец.
Читайте также: