计算总资产最接近的 20 家同行的中位数

Question 1

编辑：感谢测试文件。结果显示在下表中。屏幕截图显示前 25 行，但实际表格包含测试文件中的所有数据。

辅助列（“∆ 资产”，E 列）是待评估公司资产与所有其他公司资产差额的绝对值。单元格 H6 包含测试公司资产，可以轻松更改为指向列表中的任何公司。在此示例中，它指向 B4。E4 中的公式为：

=ABS($E4-$H$6)

并向下填充以创建辅助列。H4 中的公式为：

=MEDIAN(IF(RANK(E4:E55,E4:E55,1)<22,D4:D55))

这是一个数组公式，必须用CTRL++输入，而不是仅仅。ShiftEnterEnter

为了进行检查，我根据 F 列中辅助列的排名，在 G4 中手动选择 21 个市盈率，计算了中位数市盈率。结果与使用公式相同，因此两种方法都会忽略市盈率为“NA”的单元格。但是，如果将这些单元格留空，则计算出的中位数会发生变化，因为内部数组在 NA 所在的位置包含零。

工作原理：RANK() 函数按升序返回辅助列值的排名数组。IF() 返回 21 个市盈率数组，这些市盈率对应于资产最接近被估值公司的公司。（注意：我使用 21 是因为我假设被估值的公司在列表中。如果不是，请相应地更改公式。）最后，MEDIAN() 计算 21 个资产值数组的中值。

我有点惊讶地发现 RANK() 可以将两个数组作为参数处理，但不能计算数组，即 ABS($I$4:$I$508-$I$nn)。这就是为什么需要辅助列的原因。更奇怪的是 - RANK() 可以处理一计算数组，但不是两个。这绝对值得一句“拜托了...”

希望这会有所帮助并祝你好运。

Answer

编辑：感谢测试文件。结果显示在下表中。屏幕截图显示前 25 行，但实际表格包含测试文件中的所有数据。

辅助列（“∆ 资产”，E 列）是待评估公司资产与所有其他公司资产差额的绝对值。单元格 H6 包含测试公司资产，可以轻松更改为指向列表中的任何公司。在此示例中，它指向 B4。E4 中的公式为：

=ABS($E4-$H$6)

并向下填充以创建辅助列。H4 中的公式为：

=MEDIAN(IF(RANK(E4:E55,E4:E55,1)<22,D4:D55))

这是一个数组公式，必须用CTRL++输入，而不是仅仅。ShiftEnterEnter

为了进行检查，我根据 F 列中辅助列的排名，在 G4 中手动选择 21 个市盈率，计算了中位数市盈率。结果与使用公式相同，因此两种方法都会忽略市盈率为“NA”的单元格。但是，如果将这些单元格留空，则计算出的中位数会发生变化，因为内部数组在 NA 所在的位置包含零。

工作原理：RANK() 函数按升序返回辅助列值的排名数组。IF() 返回 21 个市盈率数组，这些市盈率对应于资产最接近被估值公司的公司。（注意：我使用 21 是因为我假设被估值的公司在列表中。如果不是，请相应地更改公式。）最后，MEDIAN() 计算 21 个资产值数组的中值。

我有点惊讶地发现 RANK() 可以将两个数组作为参数处理，但不能计算数组，即 ABS($I$4:$I$508-$I$nn)。这就是为什么需要辅助列的原因。更奇怪的是 - RANK() 可以处理一计算数组，但不是两个。这绝对值得一句“拜托了...”

希望这会有所帮助并祝你好运。

Question 2

还有一个问题。现在我想结合 TA 和 ROE。目标是找出 TA 最接近目标公司的 5 家公司，然后从这 5 家公司中找出 ROE 最接近的 3 家公司！

有什么想法可以解决该问题吗？

测试文件可从此处获取：https://we.tl/rNIrgWL9h8

非常感谢你们的帮助！

@Nayrb

Answer