☰

Excel. 从多列返回值

tag-icon • tag-icon microsoft-excel worksheet-function
Excel. 从多列返回值

下面的电子表格显示了 3 种产品在不同地点的数据。我想按位置列出前 10 个“最低”数字。SMALL 函数可以返回这些值，但我不知道如何返回这些值以及位置和产品详细信息。

原始表 原来的

+--------------+-----------+-----------+-----------+
| Location     | Product 1 | Product 2 | Product 3 |
+--------------+-----------+-----------+-----------+
| Bristol      |    -10.2% |           |           |
| Bangkok      |    -30.5% |           |     -5.7% |
| Brighton     |     -4.7% |    -16.2% |           |
| Birmingham   |    -43.3% |    -13.8% |     -5.1% |
| Bath         |    -27.3% |     -6.9% |           |
| Bradford     |    -17.3% |    -25.9% |     -3.8% |
| Chelsea      |    -18.5% |    -13.2% |    -15.6% |
| Clevedon     |     -9.2% |     -6.3% |           |
| Durban       |    -42.5% |     -7.4% |           |
| Epsom        |    -12.9% |           |           |
| Exeter       |           |           |           |
| Glasgow      |           |           |           |
| High Wycombe |    -17.3% |           |    -47.2% |
| Leeds        |           |    -15.1% |           |

                             and so on
                                 ︙

预期成绩：

在此处输入图片描述

答案1

在此处输入图片描述

怎么运行的：

  • 要获得前 10 个值，请在单元格中输入此公式F181并填写。

=IF(ROW(A1)>10,"",SMALL(B$181:D$199,ROWS(F$2:F2)))

  • 单元格中的公式G181来获取相关位置，并将其填充下来。

    =IFERROR(INDEX($A$181:$A$199,MATCH(F181,B$181:B$199,0)),IFERROR(INDEX($A$181:$A$199,MATCH(F181,C$181:C$199,0)),IFERROR(INDEX($A$181:$A$199,MATCH(F181,D$181:D$199,0)),"")))

  • 单元格中的最终数组（CSE）公式H181，以Ctrl+Shift+Enter并向下填充。

    {=INDEX(B$180:D$180,MAX(IF(B$181:D$199=F181,COLUMN(B$181:D$199)))-COLUMN(B181)+1)}

根据需要调整公式中的单元格引用。

答案2

更新答案：

根据@fixer1234 的评论...我同意这个想法...只需要对重复的值添加一些特殊考虑。

目标：列出10个城市。

想法：从每个城市获取最低的产品 X 值，使用该值对城市进行排序，列出百分比，然后是城市，然后是产品编号。对于重复的城镇，列表中的顶部名称将首先显示（您可以在产品 2 埃克塞特中输入 -49.90% 来测试这一点）。公式使用间接 + 偏移 + 索引 + 匹配 excel 函数，不使用数组公式。

细节 ：

假设“十大关注点”文本位于 F1，而“-10.2%”结果位于 B2（数据在 A2:D25 中）。

在 F2 中输入：

=IF(A2="","",IFERROR(SMALL(B2:D2,1),1))

并拖动到 F25，然后进入：

H2 ->  =H1+1
I2 ->  =SMALL(F:F,H2)
J2 ->  =INDEX(INDIRECT("A"&IF(I2=I1,MATCH(J1,A$2:A$25,0)+2,2),TRUE):A$25,MATCH(I2,INDIRECT("F"&IF(I2=I1,MATCH(J1,A$2:A$25,0)+2,2),TRUE):F$25,0))
K2 ->  =INDEX(B$1:D$1,MATCH(I2,OFFSET(B$1:D$1,MATCH(J2,A$2:A$25,0),0),0))

然后向下拖动 10 个单元格。完成。

希望它能解决。（：

上一个答案（用于存档目的）：

假设“十大关注”文本位于 F1，而“-49.90%”结果位于 H2。我还假设 Top10 结果是唯一的。我建议在 F2 中输入：

=IFNA(INDEX($A$2:$A$25,MATCH(H2,$D$2:$D$25,0)),"")&IFNA(INDEX($A$2:$A$25,MATCH(H2,$C$2:$C$25,0)),"")&IFNA(INDEX($A$2:$A$25,MATCH(H2,$B$2:$B$25,0)),"")

然后在 G2 中：

=INDEX($B$1:$D$1,MATCH(H2,OFFSET($B$1:$D$1,MATCH(F2,$A$2:$A$25,0),0),0))

并将两者向下拖动。

想法：使用索引/匹配获取位置名称，如果为“#N/A”，则显示空值。然后在 G2 中使用双索引匹配（带偏移）“加载”产品 1/2/3 文本。

p/s：如果真的有平局……（我试过了……）就会失败……所以为了即兴发挥……我只需将 0.001% 添加到其中一个“平局”结果（在 B、C、D 列中），显示的值就不会受到影响。如果您确实有办法以不同的方式处理平局，请分享，我们将不胜感激。（：

希望能帮助到你。

相关内容