在数组中查找行/列索引的 Excel 公式 [英] An excel formula to find a row/column index in array

问题描述

我有一个带有行和列索引的 excel 表数组.在数组内部，有一些 empyu 单元格和一些具有唯一值的单元格.

I have an excel table array, with row and columns indexes. Inside the array, there are some empyu cells and some cells with unique values.

现在我想得到一个简化的表格，当在第一列中我有来自表格数组和第二列的唯一值的列表时，以获得该值的确切单元格作为行和列之间的连接函数指数.首先这是表格示例:

Now i want to get a simplified table, when in first column i have the list of the unique values from the table array and in second column, to get the exact cells of this value as a function of concatenation between row and column index. First this is the table example:

    a   b   c   d
31              RESET_N
30  A[9]        A[11]   
29      A[6]        A[7]
28  A[3]        A[4]    

和想要的表:

A[9] a30
A[3] a28

等

谢谢

推荐答案

=B2&A2 应该就足够了，但是您必须先反向旋转"，并且可能在途中删除空白.在 Excel 2007 中:

=B2&A2 should be sufficient, but you would have to 'reverse pivot' first and maybe delete blanks en route. In Excel 2007:

在A1中添加标签.
Alt+D，P.

Add label in A1.
Alt+D, P.

深入研究 7(Grand Total 的交集).
对于 Value 过滤器仅用于 (Blanks) 并删除选定的行.取下过滤器并在D2中添加公式.如果需要，可以排序和删除多余的内容.

Drill down on 7 (the intersect of the Grand Totals).
For Value filter for (Blanks) only and delete selected rows. Take off filter and add formula in D2. Sort and delete surplus, if desired.

这篇关于在数组中查找行/列索引的 Excel 公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！