Excel 2016: Рассчитайте медиану группы сверстников, объединив две меры

Question

Я получил тестовый файл с 10 компаниями. Я хочу рассчитать медиану, комбинируя TA и ROE. Цель состоит в том, чтобы найти 5 компаний, наиболее близких к целевой компании с точки зрения TA, а затем 3 компании из тех 5, которые наиболее близки с точки зрения ROE! У меня уже есть формула для расчета медианы трех ближайших к фирме компаний, которые должны оцениваться в терминах TA и ROE, соответственно (см. Столбцы I и J).

например, для 3 (= N3) компаний, следующих за целью в терминах TA: {= MEDIAN (IF (IF (IF ($ B $ 4: $ B $ 15 = B4; ""; ABS (E4- $ E $ 4: $ E $ 15))) <= МАЛЫЙ (ЕСЛИ ($ B $ 4: $ B $ 15 = В4; ""; ABS (E4- $ E $ 4: $ E $ 15)); $ N 3); $ G $ 4: $ G $ 15; ""))}

Есть идеи, как решить эту проблему? Тестовый файл доступен здесь: https://we.tl/rNIrgWL9h8 Большое спасибо за помощь, ребята!

Answer 1

Если вы хотите сделать это с одной формулой, это станет чем-то вроде монстра ..... но вы можете использовать эту формулу массива в L4 скопированную

=MEDIAN(IF($B$4:$B$15<>B4,IF(ABS(E4-$E$4:$E$15)<=SMALL(IF($B$4:$B$15<>B4,ABS(E4-$E$4:$E$15)),5),IF(ABS(F4-$F$4:$F$15)<=SMALL(IF($B$4:$B$15<>B4,IF(ABS(E4-$E$4:$E$15)<=SMALL(IF($B$4:$B$15<>B4,ABS(E4-$E$4:$E$15)),5),ABS(F4-$F$4:$F$15))),3),$G$4:$G$15))))

Обратите внимание, что, как и в случае с вашими формулами, если вы вычисляете самые близкие 3 таким образом (сравнивая с 3-м наивысшим значением), то вполне возможно, что вы можете включить 4 или более, если есть связи на 3-м наименьшем числе.