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

问题描述

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

现在我想得到一个简化的表，当第一列我有来自表数组和第二列的唯一值的列表，以将该值的确切单元格作为行和列索引之间的并置函数。
首先这是表示例：

  abcd 
 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中：

在A1中添加标签。

Alt + D，P.

向下钻取 7 （ Grand Total s）的相交。

价值仅为（空白）过滤，并删除所选行。
取消过滤器并在D2中添加公式。
如果需要，排序并删除剩余。

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]    

And desired table:

A[9] a30
A[3] a28

etc

thanks

解决方案

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

Add label in A1.
Alt+D, P.

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屋！