Добавление табличных значений между неопределенным списком рабочих таблиц, которые хранят связи ключей данных

Question

Представим, что у вас есть итоговая таблица и 5 других таблиц, которые состоят из данных для итоговой таблицы. Допустим также, что на каждом рабочем листе определена одинаковая структура таблицы. Например

ID | # played

1  | 4

2  | 1

3  | 11

...

Как составить формулу для итоговой таблицы, чтобы я знал сумму, # played для идентификатора 1, между всеми другими таблицами без необходимости изменять формулу при каждом добавлении новой таблицы?

Answer 1

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

В настоящее время я не знаю, как более динамически ссылаться на имена листов, используя только базовые формулы Excel.

Попробуй это:

Подготовьте свои листы с информацией, например, назовите первый лист "01" и введите свои данные в столбцы A и B, например:

**Sheet 01**

A1 = 1   B1 = 4
A2 = 2   B2 = 1
A3 = 3   B3 = 11

Продолжайте использовать этот формат на других ваших листах, например:

**Sheet 02**

A1 = 1   B1 = 5
A2 = 2   B2 = 6
A3 = 3   B3 = 7

а также

**Sheet 03**

A1 = 1   B1 = 8
A2 = 2   B2 = 9
A3 = 3   B3 = 10

Затем создайте лист, на котором вы хотите отобразить всю эту информацию. Ради аргумента давайте назовем этот лист "Сводка".

Затем в сводном листе введите следующее:

**Summary**

A1 =     B1 = 01                                            C1 = 02   D1 = 03
A2 = 1   B2 = =VLOOKUP($A2,INDIRECT(B$1&"!A:B"),2,FALSE)    C1 =      D1 =   
A3 = 2   B3 =                                               C1 =      D1 =   
A4 = 3   B4 =                                               C1 =      D1 =   

Затем просто скопируйте формулы в B2 в остальные ячейки, в которых вы хотите отобразить свои значения.

Обратите внимание, что в этом примере вам нужно будет отформатировать ячейки в B1:D1 как текст, иначе Excel автоматически удалит первые 0 и формула будет разбита. В любом случае, вы будете использовать текстовые имена для своих листов.

Недостатки этого решения:

1. Как уже упоминалось, вы должны вводить имена листов вручную в сводном листе, и как только имя листа изменится, вы должны будете изменить его в сводном листе.
2. Это решение не является динамическим, принимая во внимание пункт (1.) и тот факт, что вам нужно копировать формулу в новые ячейки каждый раз, когда вы хотите расширить диапазон суммируемых значений.

В качестве альтернативы вы можете также рассмотреть вопрос о замене формулы в B2 в сводной таблице выше на

=IFERROR(VLOOKUP($A2,INDIRECT(B$1&"!A:B"),2,FALSE),"")

Затем с помощью этой формулы вы можете просто заполнить сводный лист на столько, сколько пожелаете, что сэкономит вам дополнительную работу при следующем добавлении дополнительных листов в рабочую книгу.

Если имена ваших листов следуют шаблону или если вы заранее знаете имена будущих листов, вы даже можете предварительно заполнить строки 1 этими именами, и последняя формула все равно будет работать, потому что она ничего не отобразит в случае, если лист не существует, и он будет отображать соответствующее значение, как только лист с предварительно известным именем листа будет добавлен в вашу рабочую книгу.