Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Средневзвешенноге по выборке из номеров строк исходной таблицы?
 
Добрый день!
Есть таблица пронумерованных графиков, есть выборки номеров этих графиков. Нужно по каждой строке выборки сделать расчет средне-взвешенного
Формула расчета среднего значения по выборке получается сложной даже в случае 5 столбцов в выборке. Если столбцов 50ят или больше - то формула не умещается в строку (((
Можно ли упростить это?  ("сложные" формулы выделил желтым)
 
VladimirVSh, а каким образом вы подставляете значение E3:I12? почему именно эти значения?
Изменено: a.i.mershik - 20 Мар 2018 17:51:10
 
Для сведения - https://www.planetaexcel.ru/blog/novaya-statya-raschet-srednevzveshennogo-znacheniya-v-excel-formula...
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Таблица выборки - это результат отдельного алгоритма, который подбирает подходящий номер графика
Например:
E3:I3= {10;34;14;34;5} - это номера графиков для формирования "график1"
значение "график1(День[1])" определяется как средне-взвешенное из выборки номеров графиков E3:I3 с весами E2:I2 = {1, 2, 3, 2 ,1}
т.е.:
график1(День[1]) = (графики(10)[День(1)] *1+ графики(34)[День(1)]*2 + графики(14)[День(1)]*3 + графики(34)[День(1)] *2 + графики(5)[День(1)]*1 ) / (1+2+3+4+1))

и так для каждой точки День(i)
 
Я бы попробовал формулу массива через ДВССЫЛ и СУММПРОИЗВ
Код
=СУММПРОИЗВ(ДВССЫЛ(АДРЕС($E$3:$I$12+1;СТОЛБЕЦ(F28);4;1;$C$1));$E$2:$I$2)/СУММ($E$2:$I$2)

Или СМЕЩ
Код
=СУММПРОИЗВ(СМЕЩ(Тбл!$A1;$E$3:$I$12+1;СТОЛБЕЦ(E28));$E$2:$I$2)/СУММ($E$2:$I$2)

(для ячейки E17)

К сожалению, они показывают ошибку - не могу сейчас вплотную заняться - может кто из коллег на форуме меня поправит...
 
А я бы даже не пробовал ДВССЫЛ(АДРЕС :)
Летучее, тормознутое...  Получаем текстовый адрес, потом его преобразовываем в ссылку. Вместо этой неудобной парочки что-то типа:
=ИНДЕКС(Тбл!$E$1:$DD$37;E3;СТОЛБЕЦ(F1))
Для получения массива значений в функции ИНДЕКС:
=ИНДЕКС(Тбл!$E$1:$DD$37;Ч(ИНДЕКС($E$3:$I$12+1;));СТОЛБЕЦ(F1))
СМЕЩ - тоже летуча.

ДВССЫЛ И СМЕЩ тоже когда-то были любымимы функциями. Вставлял их куда можно и куда не очень нужно. А Вот АДРЕС вообще нелюбим. Одна из самых невостребованных функций.
 
Цитата
vikttur написал: А Вот АДРЕС  вообще нелюбим
Ну когда выбора нет и нужен ДВССЫЛ. то помогает получив имя листа не заботится о апострофах, если имя тоже получать автоматом через Cell("Address"; ..)

Цитата
vikttur написал: Для получения массива значений в функции ИНДЕКС:
никогда не получалось :-) вот что не так? =SUMPRODUCT(INDEX(Тбл!$1:$37;$E3:$I3;COLUMN(F1))*$E$2:$I$2)/SUM($E$2:$I$2)
 
ИНДЕКС не умеет напрямую работать с масивом. Но вместо $E3:$I3 ей можно подарить Ч(ИНДЕКС($E3:$I3;)) - такое лакомство она любит.

Цитата
Ну когда выбора нет
За 10 лет  никогда не попадал в такую ситуацию с АДРЕС.
ДВССЫЛ, СМЕЩ
- эти да, без них бывает  сложно.

Не отговариваю от применения. Так, практика и личные предпочтения.
 
Addres - обойтись можно, но бывает короче именно из-за формирования имени листа с пробелами. Хотя апостроф можно удалить и поставит, дабы не дублировать и делать это всегда.

UPD. конструкция INDEX( ...;N(INDEX(...;))) работает только если строки во втором INDEX. транспонирование потребовалось.
Код
=SUMPRODUCT(INDEX(Тбл!$2:$37;N(INDEX(TRANSPOSE($E3:$DB3);));COLUMN(F1))*TRANSPOSE($E$2:$DB$2))/SUM($E$2:$DB$2)
Изменено: БМВ - 21 Мар 2018 13:07:01
 
спасибо! утащил, буду изучать )))
 
Цитата
БМВ написал: транспонирование потребовалось
Наверное, я в задачу не вникал, только предложил вариант обхода.
Страницы: 1
Читают тему (гостей: 1)
Наверх