Как подвести итоги взвешенных нормализаций столбцов?

Question

Скажем, у меня есть лист со следующей (упрощенной) структурой с абсолютными значениями:

Чего я хочу добиться, так это получить таблицу с нормализованными значениями (т.е. максимум эквивалентен 1. В моем примере в value_1 4 является максимальным и, следовательно, 1 и 2 будут равны 0.5). Наконец, я хочу взвесить value_1 и value_2 и получить нормализованную сумму (максимальное число = 1)

Конечно, на моем реальном листе много строк и еще пара столбцов, поэтому создание новых столбцов на одном листе - это не то, что мне нужно.

Вот упрощенное решение (с примером веса (0,6-0,4):

Теперь вопрос:

Как я могу получить это решение с большим листом с сотнями строк и десятками столбцов?

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

• Сделайте values столбца и используйте Show values as... > % of column results (см. Следующий снимок экрана, извините, немецкая версия Excel)
• Я не мог получить взвешенную сумму с этой версией, хотя

Answer 1

Вариант 1 - использование формулы в дополнительном листе

• Дублируйте свой лист и сделайте вычисления во втором. (дублирование хорошо, так как у вас те же заголовки и диапазон данных, что и в оригинальном).
• Если вы поместите веса в первый ряд, а заголовки во второй, тогда ваша формула в A3 будет выглядеть так: =old_sheet!A3/max(old_sheet!A3:A<last row>)/A1
• теперь вам нужно только суммировать строки, чтобы получить нормализованное взвешенное значение

Вариант 2 - изменение исходных данных

• вставить 3 новые строки в верхней части данных
• введите в новых строках: максимальное значение столбца, вес, максимум * вес
• выберите третью строку и нажмите CRTL+C, выберите все свои значения и выберите вставить специальное - разделить.
• суммировать строки