Excel结构化参考表语法 [英] Excel structured reference table syntax

问题描述

我试图避免使用eEcel太多，但是当我喜欢使用结构化引用，因为它们看起来干净得多。如果我使用列col1和col2创建一个名为table1的表，那么我如何使用另一个表中的结构化引用引用col1中的第一行？我已经尝试了语法 = table1 [[＃row]，[col1]] ，只是得到一个错误。有没有像 = table1 [1,1]或= table1 [1，[col1]] 的语法？当然，这也不行，但是等同的呢？这是非常讨厌的，因为它似乎应该是简单的。

解决方案

Table1 [[＃ ] [Column1]] 工作，但公式必须与要引用的表格行相同。

要引用其他地方的第一行，请使用 COUNTIFS（criteria_range1，criteria1 [，criteria_rangen，criteria]）或稍微复杂的 SUMIFS（）如果您需要数字值而不是count，如studgeek所述：

  SUMIFS （sum_range1，criteria_range1，criteria1 [，criteria_rangen，criteria]）
  

你当然需要一个唯一的行选择行的标准。所以，例如：

  Table1 
 ID值名称
 1 2两个
 2 4四个
 3 8八
  

SUMIF（Table1 [Value] Table1 [ID]，2） ...返回值为4（如果ID = 2未找到则返回0）。如果你的值不是数字的，那么你不能使用这个方法。

然而，akuhn几乎打了真正的答案，但他没有走得太远

INDEX（Table1 [Name]，2）返回四
INDEX（表1，1，1）返回1

I try to avoid using eEcel too much but when I do I like using structured references as they seem a lot cleaner to write. If I create a table called "table1" with columns "col1" and "col2" how would I reference the first row in "col1" using a structured reference in another table? I have tried the syntax =table1[[#this row],[col1]], and just get an error. Is there a syntax like =table1[1,1] or =table1[1,[col1]]? Of course, this doesn't work either, but what's the equivalent? It's very annoying, as it seems like this should be simple.

解决方案

Table1[[#This Row][Column1]] does work, but the formula must be on the same row as the table row you wish to reference.

To reference the first row, elsewhere, use either COUNTIFS(criteria_range1, criteria1 [, criteria_rangen, criterian]) Or the slightly more complex SUMIFS() if you need numeric values instead of counts, as mentioned by studgeek:

SUMIFS(sum_range1, criteria_range1, criteria1 [, criteria_rangen, criterian])

You will of course need a unique row criteria by which to select the row. So, for example:

Table1
ID Value Name
1  2     Two
2  4     Four
3  8     Eight

SUMIF(Table1[Value], Table1[ID], 2) ... returns a value of 4 (or zero if ID=2 not found). If your value is not numeric, then you can't use this method, obviously.

However, akuhn almost hit the real answer, but he didn't go quite far enough in his explanation/example, IMO.

INDEX(Table1[Name], 2) returns "Four" INDEX(Table1, 1, 1) returns 1

这篇关于Excel结构化参考表语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！