Изготовление стола с меньшими столами из других листов

Question

[Я использую Excel Pro 2013]

У меня есть лист с названием «11», где у меня есть таблица в ячейках P41:S43

1     2      3     4
5     6      7     8
9     10     11    12
13    14     15    16

На листе «12» я хочу воспроизвести ту же таблицу (например, если я изменю таблицу в «11», она будет обновлена в «12»). Один из способов сделать это вручную, используя такие формулы, как:

='11'!P41   ='11'!Q41   ='11'!R41
='11'!P42   ='11'!Q42   ='11'!R42
='11'!P43   ='11'!Q43   ='11'!R43

Есть много других листов (от 1:11), каждый из которых имеет разные таблицы в одних и тех же ячейках (P41:S41). Я хочу иметь лист под названием «Все таблицы», где у меня есть большой стол со всеми отдельными таблицами вместе. Для таблиц 1:4 в листе «Все таблицы» это будет выглядеть так

1   ='1'!P41    ='1'!Q41    ='1'!R41    ='1'!S41
1   ='1'!P42    ='1'!Q42    ='1'!R42    ='1'!S42
1   ='1'!P43    ='1'!Q43    ='1'!R43    ='1'!S43
1   ='1'!P44    ='1'!Q44    ='1'!R44    ='1'!S44
2   ='2'!R38    ='2'!S38    ='2'!T38    ='2'!U38
2   ='2'!R39    ='2'!S39    ='2'!T39    ='2'!U39
2   ='2'!R40    ='2'!S40    ='2'!T40    ='2'!U40
2   ='2'!R41    ='2'!S41    ='2'!T41    ='2'!U41
3   ='3'!Q40    ='3'!R40    ='3'!S40    ='3'!T40
3   ='3'!Q41    ='3'!R41    ='3'!S41    ='3'!T41
3   ='3'!Q42    ='3'!R42    ='3'!S42    ='3'!T42
3   ='3'!Q43    ='3'!R43    ='3'!S43    ='3'!T43
4   ='4'!P38    ='4'!Q38    ='4'!R38    ='4'!S38
4   ='4'!P39    ='4'!Q39    ='4'!R39    ='4'!S39
4   ='4'!P40    ='4'!Q40    ='4'!R40    ='4'!S40
4   ='4'!P41    ='4'!Q41    ='4'!R41    ='4'!S41

Что на практике я достиг, написав всегда первую строку каждой группы, а затем перетащив формулы вниз на три строки, например,

    ='1'!P41    ='1'!Q41    ='1'!R41    ='1'!S41

Когда я закончил с четырьмя первыми строками, мне пришлось бы изменить первую строку с ссылки на лист «1» на лист «2», а затем перетащить вниз на три строки (для достижения строк 5-8).

Есть ли способ, где я могу достичь того же результата (все таблицы из листов в одной супер таблицы) без такой большой ручной работы? Моя идея была бы что-то вроде этого:

=INDIRECT(CONCATENATE("'";A2;"'!P41"))
              =INDIRECT(CONCATENATE("'";A2;"'!Q41"))
                                =INDIRECT(CONCATENATE("'";A2;"'!R41")
                                                     =INDIRECT(CONCATENATE("'";A2;"'!S41")

(представьте эти формулы в одном ряду)

с A2 = 1 (при перетаскивании формулы ее значение будет 1, 2, 3 или 4 в зависимости от положения таблицы. Представляет название листа, взятого из первого ряда)

Проблема заключается в том, что при использовании этой формулы перетаскивание вниз не изменит ссылку с ячеек P41:S41. Есть ли какой-нибудь способ, которым я могу получить формулу, тянущую вниз, чтобы получить строку P42:S42 от P41:S41 и так далее?

Не обращайте внимания на предложение INDIRECT + CONCATENATE, если есть более простой способ сделать это.

Answer 1

Если у вас есть идентичные таблицы в разных листах с одним и тем же диапазоном данных, приведенная ниже формула может помочь объединить таблицы.

= IFERROR(INDEX(Comb1, ROWS(A $ 2:A2), COLUMN(A2)), IFERROR(INDEX(Comb2, ROWS(A $ 2:A2)-ROWS(Comb1), COLUMN(A2)), "-"))

Перед использованием формулы в новом листе скопируйте строку заголовка, затем поместите указатель ячейки в ячейку A2, напишите формулу и выполните ее, затем перетащите ее вправо, а затем вниз, пока она не понадобится.

Примечание: Comb1 и Comb2 - это две таблицы.

Надеюсь, это поможет вам. Я отправил решение после того, как был проверен мной.