我有一个看起来像这样的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中具有内置功能的解决方案吗?
最佳答案
如果只需要使用本机工作表函数,则可以完成此操作。即使没有数组公式。
使用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值。