РЕДАКТИРОВАТЬ: Спасибо за тестовый файл. Результаты показаны в таблице ниже. На снимке экрана показаны первые 25 строк, но фактическая таблица содержит все данные в вашем тестовом файле.
Вспомогательный столбец («∆ Активы», столбец E) представляет собой абсолютную величину разницы между активами оцениваемой компании и всеми остальными компаниями. Ячейка H6 содержит активы тестируемой компании и может быть легко изменена для указания на любую компанию в списке. В этом примере это указывает на B4. Формула в Е4:
=ABS($E4-$H$6)
И он заполняется, чтобы создать вспомогательный столбец. Формула в H4:
=MEDIAN(IF(RANK(E4:E55,E4:E55,1)<22,D4:D55))
Это формула массива, и ее нужно вводить с помощью сочетания клавиш CTRL+Shift+Enter, а не просто с помощью клавиши Enter.
В качестве проверки я вычислил среднее значение P/E вручную в G4, выбрав 21 P/E в соответствии с рангом столбца помощника в столбце F. Результаты те же, что и при использовании формулы, поэтому оба метода игнорируют ячейки, где P/E - "NA". НО, если эти ячейки сделать пустыми, вычисленная медиана изменится, потому что внутренний массив содержит ноль, где были NA.
Как это работает: функция RANK() возвращает массив рангов значений вспомогательного столбца в порядке возрастания. IF() возвращает массив из 21 коэффициентов P/E, соответствующих компаниям с активами, ближайшими к оцениваемой компании. (Примечание: я использовал 21, потому что я предполагаю, что оцениваемая компания находится в списке. Если нет, измените формулу соответственно.) Наконец, MEDIAN () вычисляет медианное значение массива из 21 значения активов.
Я был немного удивлен, обнаружив, что RANK() может обрабатывать два массива в качестве аргументов, но не вычисляемые массивы, то есть ABS($ I $ 4:$ I $ 508- $ I $ nn). Вот почему вспомогательный столбец был необходим. Еще более странный - RANK() может обрабатывать один вычисленный массив, но не два. Это заслуживает "Давай, человек ..." наверняка.
Надеюсь, что это помогает и удачи.
