我有一个看起来像这样的excel表(25x25),

    C1  C2  C3
R1  5   6   7
R2  1   7   9
R3  2   3   0


我的目标是使其看起来像这样

C3  R3  0
C1  R2  1
C1  R3  2
C2  R3  3
C1  R1  5
C2  R1  6
C2  R2  7
C3  R1  7
C3  R2  9


它将生成一个新表,该表按第一个值排序。它还告诉相应的列和行名称。该表具有重复项,负数和小数。

我这样做是因为我想找到给定值的3个最接近的候选项(因此是C和R)。 VLOOKUP()需要一个排序表。

另一个问题(向前发展)是VLOOKUP()返回最接近的较小值,而不是实际的最小值。是否有更好的方法或解决方法?这样结果就是一个整洁的表,

Value to look up = 2.8
>> C2 R3 3
>> C1 R3 2
>> C1 R1 5


由于某些原因,我无法在该项目中使用VBA。仅在MS Excel中具有内置功能的解决方案吗?

最佳答案

如果只需要使用本机工作表函数,则可以完成此操作。即使没有数组公式。
arrays - 表排序和查找-LMLPHP
使用A1:D4中的原始数据,F3:H3中的公式为:

=INDEX(B$1:D$1, AGGREGATE(15, 6, COLUMN($A:$C)/(B$2:D$4=H3), COUNTIF(H$3:H3, H3)))
=INDEX(A$2:A$4, AGGREGATE(15, 6, ROW($1:$3)/(B$2:D$4=H3), COUNTIF(H$3:H3, H3)))
=SMALL(B$2:D$4,ROW(1:1))

根据需要填写。
K5:N5中的公式是
=INDEX(B$1:D$1, AGGREGATE(15, 6, COLUMN($A:$C)/(B$2:D$4=M5), COUNTIF(M$5:M5, M5)))
=INDEX(A$2:A$4, AGGREGATE(15, 6, ROW($1:$3)/(B$2:D$4=H3), COUNTIF(M$5:M5, M5)))
=IF(COUNTIF($B$2:$D$4, N5+$K$2)>=COUNTIF(N$5:N5, N5), N5+$K$2, $K$2-N5)
=AGGREGATE(15,6,ABS($B$2:$D$4-$K$2),ROW(1:1))

根据需要填写。
我在K5:N13矩阵中包含了足够的行,您可以看到如何处理这两个7值。

10-08 02:49