Excel рассчитывает диапазон сумм вместе с другими вычислениями

Question

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

         column 1 | column 2 | column 3 | SUM

date1    | 1      | 5        | 3        | column1_reference * multiplier + column2_reference * multiplier...
date2    | 2      | 3        | 6        | 
date3    | 1      | 1        | 5        |

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

колонка 1: 40

колонка 2: 243

колонка 3: 321

В общем, мне нужно рассчитать общее количество каждого дня. Получить номер из списка ссылок и умножить его на число в данных легко (OFFSET с именованным множителем диапазона *), но это работает только с одной ячейкой. Я не знаю, как применить это к диапазону.

Поэтому, если использовать ранее установленные примеры, столбец SUM будет выглядеть так:

дата1 СУММА = (40 * 1) + (243 * 5) + (321 * 3)

дата2 СУММА = (40 * 2) + (243 * 3) + (321 * 6)

date3 SUM = (40 * 1) + (243 * 1) + (321 * 5)

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

Answer 1

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

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

Этот подход будет автоматически корректироваться при изменении имен столбцов или их порядка.

В приведенном ниже примере листа B2 является первой ячейкой строки помощника; оно содержит формулу

=VLOOKUP(B1,$A$7:$B$10,2,0)

который копируется по столбцам данных.

Первая ячейка в столбце суммы - E3 ; его формула

=SUMPRODUCT($B$2:$D$2,B3:D3)

который копируется в нижней части данных.

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

Answer 2

Если в первой строке есть числа в B2:D2 и вы хотите получить сумму в E2 попробуйте скопировать эту формулу

=SUM(MMULT(B2:D2,B$9:B$11))

Предполагая, что 40, 243, 321 появляется в B9:B11

см. скриншот ниже:

Редактировать:

Если B1:D1 и A9:A11 могут быть не в том же порядке, то решение Чаффа является хорошим. Если вы хотите , чтобы избежать дополнительный ряд , то вам необходимо включить часть ВПР в SUMPRODUCT - вы не можете использовать сам ВПР , потому что он не будет возвращать массив, LOOKUP возможно потому , что он делает возвращает массив , но проблематичной , так как А9:A11 нужно отсортировать по возрастанию (и даже в этом случае могут возникнуть проблемы, если некоторые заголовки столбцов не совпадают).

Обходной путь, который заключается в использовании SUMIF в качестве «квази-поиска», то есть с этой формулой

=SUMPRODUCT(B2:D2,SUMIF(A$9:A$11,B$1:D$1,B$9:B$11))

Это все равно будет работать, если вторая таблица находится на другом листе или определена OFFSET.

[Примечание: спорный момент сейчас, но в моей оригинальной формуле функция SUM не требуется - MMULT будет работать сам по себе]