Поиск и деление одного диапазона на другой

Question

У меня есть два набора данных в двух таблицах: в этом формате:

Таблица 1

A 1 2 4 5 6 7 8 3 2 1 4
D 5 6 7 8 3 2 1 4 5 6 7
E 1 4 5 6 7 5 6 7 2 1 4

Таблица 2

A 1 4 5 6 7 7 6 7 2 1 4
B 5 6 7 8 3 2 1 4 5 6 7
C 1 4 5 6 7 5 6 7 2 1 4

Теперь я хочу получить соотношение сумм значений A в таблице 1 к суммам значений в A в таблице 2 (и для каждой другой метки столбца, которая соответствует). Мне нужно найти имя, вытащить 11 значений, суммировать их и разделить на сумму соответствующих значений из второй таблицы, чтобы получить требуемое соотношение одного к другому.

Предполагая, что я не могу добавить дополнительный столбец в конце с суммой этого ряда значений и vlookup, что (это самый простой способ решить это, я думаю), есть ли другой способ достичь того, что я хочу?

Answer 1

Вот строительные блоки, которые вы можете использовать, чтобы получить то, что вы хотите:

• Подготовьте список дубликатов меток (или кодов) в первых столбцах обеих таблиц (A, B, C, D, E в вашем примере).

• Используйте MATCH для определения строки, в которой каждая из меток в не дублированном списке находится в каждой таблице. Здесь предполагается, что каждая метка встречается только один раз или не встречается в каждой таблице. Например, значения MATCH для A в каждой таблице будут 1 и 1 соответственно.

• Используйте OFFSET чтобы вернуть строку значений для каждой метки в каждой таблице. Результат MATCH используется здесь, чтобы определить соответствующее смещение строки. OFFSET - это функция массива.

• Используйте SUM чтобы сложить строку значений для каждой метки, возвращаемой OFFSET.

• Разделите SUM для строки из Таблицы 1 на SUM для строки из Таблицы 2 (для каждой из меток в не дублированном списке).

• Используйте IFERROR чтобы отфильтровать результаты ошибок в делениях, которые произойдут из-за меток, которые встречаются в одной таблице, но не в другой. В этом примере, поскольку A является единственным значением в обеих таблицах, формулы для B, C, D и E будут возвращать значения ошибок.

Эти функции могут быть объединены в одну формулу для каждой строки в не дублированном списке меток.