Позвольте мне убедиться, что я понимаю проблему.
Первый лист рабочей книги (имя которого «Основной») содержит номера деталей в столбце A:
Последующие листы имеют номера деталей в качестве своих названий. Эти листы имеют «различные макеты», но все они имеют ячейку в столбце А, которая содержит слово «Сборка»:
И вы хотите заполнить консолидированный лист строками «сборки» из листов данных «детали».
В своем «сводном» листе, ячейка A1, введите
=VLOOKUP("assembly", INDIRECT(Main!$A1&"!$A$1:$Z$9"), COLUMN()-COLUMN($A$1)+1, FALSE)
Отрегулируйте $Z$9 чтобы соответствовать самой длинной строке «сборка» и наибольшему номеру строки, где появляется «сборка».
(Чтобы быть в безопасности, сделайте это наибольшее количество строк на любом листе.)
Затем выберите эту ячейку, перетащите ее на соответствующее количество столбцов, а затем перетащите ее вниз на соответствующее количество строк.
Объяснение:
Main!$A1 получает номер детали из ячейки A1 на первом (основном) листе.
A становится абсолютным с помощью $ , поэтому даже если вы перетащите его вправо, оно останется $A Но 1 является относительным (без $), поэтому, если вы перетащите его вниз на строку 2 на "Консолидированном" листе, он станет Main!$A2 .Main!$A1&"!$A$1:$Z$9" объединяет номер детали, который является именем таблицы данных деталей, со строкой !$A$1:$Z$9 (с поправкой на фактическую размерность), образуя строку, например, 17!$A$1:$Z$9 .
Это текстовое представление диапазона адресов для листа "part".
Если какой-либо из ваших номеров деталей содержит пробелы (или восклицательные знаки), вам нужно поместить название листа в кавычки: "'"&Main!$A1&"'!$A$1:$Z$9" .
Если любой из ваших номеров содержит одинарные кавычки (апострофы), у вас будут проблемы.INDIRECT(Main!$A1&"!$A$1:$Z$9") принимает текстовое представление диапазона адресов и превращает его в фактический диапазон адресов.COLUMN() - номер столбца текущей ячейки.
COLUMN($A$1) равна 1.
Таким образом, формула COLUMN()-COLUMN($A$1)+1 работает просто на COLUMN() ; т.е. текущий номер столбца.VLOOKUP("assembly", address range, column number, FALSE) значение FALSE) ищет диапазон адресов (паспорт) для ряда VLOOKUP относится к об поиску ertical) , который содержит assembly в первом столбце, и возвращает значение из указанного столбца ,
Я использовал COLUMN($A$1) чтобы вы могли вставить столбец слева и сделать все автоматически. Например, вы можете поставить =Main!$A1 в столбец 1 сводного листа:
Вы заметите, что пустые ячейки на листе данных деталей отображаются на консолидированном листе как нули.
Если это проблема, см. « Пустое отображение» при ссылке на пустую ячейку в Excel.
Если ваш основной лист содержит номера деталей, которые не соответствуют спецификациям, вы получите ошибки в своем сводном листе. Вы можете справиться с ними с помощью IFERROR() .
