Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Средневзвешенноге по выборке из номеров строк исходной таблицы?
 
#1
20.03.2018 17:45:26
Добрый день!
Есть таблица пронумерованных графиков, есть выборки номеров этих графиков. Нужно по каждой строке выборки сделать расчет средне-взвешенного
Формула расчета среднего значения по выборке получается сложной даже в случае 5 столбцов в выборке. Если столбцов 50ят или больше - то формула не умещается в строку (((
Можно ли упростить это?  ("сложные" формулы выделил желтым)
 
 
 
#2
20.03.2018 17:49:59
VladimirVSh, а каким образом вы подставляете значение E3:I12? почему именно эти значения?
Изменено: a.i.mershik - 20.03.2018 17:51:10
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
20.03.2018 17:52:17
Для сведения - https://www.planetaexcel.ru/blog/novaya-statya-raschet-srednevzveshennogo-znacheniya-v-excel-formula...
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#4
20.03.2018 18:19:02
Таблица выборки - это результат отдельного алгоритма, который подбирает подходящий номер графика
Например:
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)
 
 
 
#5
20.03.2018 18:23:07
Я бы попробовал формулу массива через ДВССЫЛ и СУММПРОИЗВ
Код
=СУММПРОИЗВ(ДВССЫЛ(АДРЕС($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)

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

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

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

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

Не отговариваю от применения. Так, практика и личные предпочтения.
 
 
 
#9
21.03.2018 01:03:42
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.03.2018 13:07:01
По вопросам из тем форума, личку не читаю.
 
 
 
#10
21.03.2018 11:27:02
спасибо! утащил, буду изучать )))
 
 
 
#11
21.03.2018 13:08:14
Цитата
БМВ написал: транспонирование потребовалось
Наверное, я в задачу не вникал, только предложил вариант обхода.
 
 
 
Страницы: 1
Наверх