Вопрос или проблема
Я испытываю трудности с функцией СУММПРОИЗВ. У меня есть две таблицы, одна из которых представляет собой физических лиц, а другая — проекции на определённый период времени.
Для каждой строки в таблице 1 мне нужно посчитать произведение суммы столбцов “фактор”, “вес”, “снижение” и “скидка” (где состояние = состояние, пол = пол и возраст между возрастом и максимальным возрастом) и из таблицы 2 столбцы “ставка” и “количество” (где возраст в таблице 2 = возраст в таблице 1 и время в таблице 2 <= максимальный возраст + 1 в таблице 1).
По сути, я хочу
=СУММПРОИЗВ(($C$2:$C$2801>=C2)*($C$2:$C$2801<=H2)*($A$2:$A$2801=A2)*($B$2:$B$2801=B2)*($D$2:$D$2801)*($E$2:$E$2801)*($F$2:$F$2801)*($G$2:$G$2801),(Лист2!$B$2:$B$5051=$C2)*(Лист2!$A$2:$A$5051<=$H2+1)*(Лист2!$C$2:$C$5051)*(Лист2!$D$2:$D$5051))
Но это не сработает, так как массивы разной длины, даже если после обработки условий “если” массивы фактически одинаковой длины. Мне удалось найти способ, чтобы достичь решения, но поскольку множители (значения в других столбцах) контролируются другими выборами, когда нужно выполнить расчет, это в основном бесполезно (даже с всего ~50 тыс. строк).
Вот два способа, которыми я смог это сделать (по сути, это одно и то же):
=СУММПРОИЗВ(ФИЛЬТР(ФИЛЬТР($A$2:$H$2801,($C$2:$C$2801>=C2)*($C$2:$C$2801<=H2)*($A$2:$A$2801=A2)*($B$2:$B$2801=B2),""),($A$1:$H$1="фактор"),""),ФИЛЬТР(ФИЛЬТР($A$2:$H$2801,($C$2:$C$2801>=C2)*($C$2:$C$2801<=H2)*($A$2:$A$2801=A2)*($B$2:$B$2801=B2),""),($A$1:$H$1="вес"),""),ФИЛЬТР(ФИЛЬТР($A$2:$H$2801,($C$2:$C$2801>=C2)*($C$2:$C$2801<=H2)*($A$2:$A$2801=A2)*($B$2:$B$2801=B2),""),($A$1:$H$1="снижение"),""),ФИЛЬТР(ФИЛЬТР($A$2:$H$2801,($C$2:$C$2801>=C2)*($C$2:$C$2801<=H2)*($A$2:$A$2801=A2)*($B$2:$B$2801=B2),""),($A$1:$H$1="скидка"),""),ФИЛЬТР(ФИЛЬТР(Лист2!$A$2:$D$5051,((Лист2!$B$2:$B$5051=$C2)*(Лист2!$A$2:$A$5051<=$H2+1)),""),(Лист2!$A$1:$D$1="ставка"),""),ФИЛЬТР(ФИЛЬТР(Лист2!$A$2:$D$5051,((Лист2!$B$2:$B$5051=$C2)*(Лист2!$A$2:$A$5051<=$H2+1)),""),(Лист2!$A$1:$D$1="количество"),""))
Или с помощью LET(
=LET(Array1,ФИЛЬТР($A$2:$H$2801,(($C$2:$C$2801>=$C2)*($C$2:$C$2801<=$H2)*($B$2:$B$2801=$B2)*($A$2:$A$2801=$A2)),""),Array1A,ФИЛЬТР(Array1,($A$1:$H$1="фактор"),""),Array1B,ФИЛЬТР(Array1,($A$1:$H$1="вес"),""),Array1C,ФИЛЬТР(Array1,($A$1:$H$1="снижение"),""),Array1D,ФИЛЬТР(Array1,($A$1:$H$1="скидка"),""),Array2,ФИЛЬТР(Лист2!$A$2:$D$5051,((Лист2!$B$2:$B$5051=$C2)*(Лист2!$A$2:$A$5051<=$H2+1)),""),Array2A,ФИЛЬТР(Array2,(Лист2!$A$1:$D$1="ставка"),""),Array2B,ФИЛЬТР(Array2,(Лист2!$A$1:$D$1="количество"),""),СУММПРОИЗВ(Array1A,Array1B,Array1C,Array1D,Array2A,Array2B))
Исходная книга полагалась на огромное количество функций ССЫЛКА и НЕКТорг, что делало её такой медленной, что было невозможно использовать, поэтому меня попросили найти лучший способ. Они хотят это без использования VBA, хотя я начинаю думать, что этого не избежать. Единственная другая идея, которую я имею, — это множество дополнительных столбцов.
Данные в текстовом формате:
Таблица 1:
| состояние | пол | возраст | фактор | вес | снижение | скидка | максимальный возраст |
|---|---|---|---|---|---|---|---|
| 1 | M | 0 | 0.7957 | 0.4980 | 0.9170 | 0.4596 | 49 |
Таблица 2:
| время | возраст | ставка | количество |
|---|---|---|---|
| 1 | 0 | 0.9186 | 592.6036 |
В основном для заданного возраста/состояния/пола создается значение суммы произведения для следующих 50 лет возраста и каждого значения времени, соответствующего начальному возрасту (и соответствующему следующему возрасту). Таким образом, первое наблюдение дает эти 50 строк, которые затем образуют столбец произведений. Сумма этого произведения (сумма произведений) = 564.7810.
Таблица 3:
| фактор | вес | снижение | скидка | ставка | количество | произведение |
|---|---|---|---|---|---|---|
| 0.7957 | 0.4980 | 0.9170 | 0.4596 | 0.9186 | 592.6036 | 90.9004 |
К сожалению, я только что осознал, что это не работает, как только я перейду за пределы 50 лет (вероятно, из-за размеров массивов), но это всё равно выводит всё правильно до этого момента. В общем, я в замешательстве.
Ответ или решение
Чтобы решить проблему с использованием функции SUMPRODUCT для работы с массивами из разных листов в Excel, нужно учитывать несколько важных аспектов, особенно когда длины массивов различаются. Давайте разберем это подробнее.
Проблема
У вас есть две таблицы: первая таблица (Table1) содержит данные о физических лицах и включает такие поля, как "state", "sex", "age", "factor", "weight", "reduction", "discount" и "maxage". Вторая таблица (Table2) содержит проекции по времени и включает поля, такие как "time", "age", "rate" и "count". Задача заключается в том, чтобы для каждой строки в первой таблице вычислить сумму произведений значений столбцов из обеих таблиц на основании определенных условий, связанных со значениями возраста, пола и штата.
Условия
- Возраст из таблицы 1 должен быть в диапазоне от "age" до "maxage".
- Пол и штат должны совпадать.
- Возраст во второй таблице должен соответствовать значению из первой таблицы, а время должно быть меньше или равно "maxage + 1".
Решение
-
Использование FILTER: Одним из подходов для получения значений, которые соответствуют условиям, является использование функции
FILTER, которая позволяет фильтровать массивы по заданным критериям, тем самым избегая ошибки различия в длине массивов. -
Оптимизация с использованием LET: Функция
LETпозволяет создавать переменные для обеспечения более ясного, читаемого и производительного кода.
Вот как может выглядеть решение вашей задачи:
=LET(
// Данные из первой таблицы (Таблица 1)
tb1_data, A2:H2801,
filter_tb1, FILTER(tb1_data,
(INDEX(tb1_data,,3) >= C2) * (INDEX(tb1_data,,3) <= H2) *
(INDEX(tb1_data,,1) = A2) * (INDEX(tb1_data,,2) = B2)),
// Данные из второй таблицы (Таблица 2)
tb2_data, Sheet2!A2:D5051,
filter_tb2, FILTER(tb2_data,
(INDEX(tb2_data,,2) = C2) *
(INDEX(tb2_data,,1) <= H2 + 1)),
// Получение значений factor, weight, reduction, discount
factor, INDEX(filter_tb1, , 4),
weight, INDEX(filter_tb1, , 5),
reduction, INDEX(filter_tb1, , 6),
discount, INDEX(filter_tb1, , 7),
// Получение значений rate и count
rate, INDEX(filter_tb2, , 3),
count, INDEX(filter_tb2, , 4),
// Вычисление результата
SUMPRODUCT(factor * weight * reduction * discount * rate * count)
)Объяснение
- Фильтрация данных: В функции
LET, содержимое обеих таблиц фильтруется на основании заданных условий. - Выбор столбцов: После фильтрации мы используем функции
INDEX, чтобы выбрать нужные столбцы (factor, weight и т. д.). - Вычисление результата: Мы применяем
SUMPRODUCTдля всех отфильтрованных значений, чтобы получить итог.
Заключение
Использование FILTER в сочетании с LET оснащает вашу формулу необходимыми гибкостью и мощью, предотвращая проблемы с различием в длине массивов. Надеюсь, это поможет вам эффективно решить вашу задачу с проекциями и суммами в Excel без использования VBA.