Суммпроизв в Excel на примере множественной регрессии
При построении множественной регрессионной модели в Excel сталкиваешься с задачей: как умножить все коэффициенты регрессии на переменные:
Y=B + A1*X1 + A2*X2+ A3*X3+…+An*Xn
Если переменных много, то ручной перебор и фиксация превращается в пытку – зафиксировать коэффициенты регрессии A1, A2… и умножить коэффициенты на соответсвующие X1, X2…
Решение – формула Excel =Суммпроизв() или на английском =SUMPRODUCT
Перейдем к примеру.
Для прогноза объема продаж сделаем модель с 20 факторами, т.е. это - 20 коэффициентов = A1, A2… A19 и B
Таким образом, каждому моменту времени соответствует 19 переменных X1, X2… и 20 коэффициентов множественной регрессии.
Коэффициенты регрессии:
И X1, X2… X19 – для прогноза:
Скачайте Excel-файл с примером
Для расчета прогноза нам надо перемножить A1*X1+A2*X2+… A19*X19+B.
Для этого мы можем:
1. Минут 5 руками вводить формулу и фиксировать ссылки на коэффициенты регрессионной модели как на картинке ниже:
2.А можем воспользоваться формулой Excel =Суммпроизв() или на английском =SUMPRODUCT и перемножить все коэффициенты с иксами за несколько секунд.
Введем формулу Y=B+ A1*X1+A2*X2+… A19*X19
Получили формулу =$B$3+СУММПРОИЗВ($C$3:$U$3;C6:U6), где
- $B$3 – зафиксированная ссылка на коэффициент B
- СУММПРОИЗВ($C$3:$U$3 – зафиксированные ссылки на коэффициенты А1, А2… А19
- СУММПРОИЗВ($C$3:$U$3;C6:U6 – ссылки на X1, X2… X19
Протягиваем формулу:
Получаем прогноз:
Формула СУММПРОИЗВ помогает перемножить элементы из двух массивов и одновременно их сложить, т.е. из такой формулы:
=$C$2+$D$2*D5+$E$2*E5+$F$2*F5+$G$2*G5+$H$2*H5+$I$2*I5+$J$2*J5+$K$2*K5+$L$2*L5+$M$2*M5+$N$2*N5+$O$2*O5+$P$2*P5+$Q$2*Q5+$R$2*R5+$S$2*S5+$T$2*T5+$U$2*U5+$V$2*V5
Мы получили такую:
=$B$2+СУММПРОИЗВ($C$2:$U$2;C5:U5)
Это помогло нам сэкономить время и нервы J на вводе формулы и бесконечном фиксировании ссылок на ячейки.
Скачайте Excel-файл с примером
Если есть вопросы, пожалуйста, обращайтесь, с радостью поможем!
Точных вам прогнозов!
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite - автоматический расчет прогноза в Excel.
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Зарегистрируйтесь и скачайте решения
Статья полезная? Поделитесь с друзьями