Excel网格查询 [英] Excel Grid Lookup

问题描述

我在一个文件中有一个数组，例如：

名称年龄鞋尺寸
安德鲁19 12 
玛丽17 8 
 Sarah 15 10 
 Wesley 19 11 

我想根据给定的名称和数据类型精确定位一条数据。
例如，确定 Sarah 和年龄，我想让单元格读取 15 。

我已经读过这个双重查询的帮助指南，但有一件事仍然困扰着我。

这是公式：

$ b $ （A1：C5，MATCH（Sarah），OFFSET（A1：C5,0,0，ROWS（A1：C5），1），0） - b

= OFFSET 1，MATCH（Age，OFFSET（A1：C5,0,0,1，COLUMNS（A1：C5）），0）-1）

当数据在同一个文件中时，它起作用，但是当我尝试使用另一个文件中的给定公式时，它会得到一个 #VALUE！错误。

从其他文件读取的代码（我所做的只是添加文件路径）：

  = OFFSET（C：file\path\ '[Lister.xls] Shhet1' $ A $ 1：$ C $ 5 MATCH（ 萨拉，OFFSET（C：file\path\ '[Lister.xls] Shhet1' $ A $ 1：$ C $ 5,0,0，ROWS（C：file\path\ '[Lister.xls] Shhet1' $ A $ 1：$ C $ 5），1 ），0）-1，MATCH（ 年龄，OFFSET（C： file\path\ '[Lister.xls] Shhet1' $ A $ 1：$ C $ 5,0,0,1，柱（C：file\path\ '[Lister.xls] Shhet1' $ A！ $ 1：$ C $ 5）），0）-1）
  

阅读：

  = OFFSET（C：file \path\''[Lister.xls] Shhet1'！$ A $ 1 ：$ C $ 5，
 MATCH（Sarah，OFFSET（C：file\path\'[Lister.xls] Shhet1'！$ A $ 1：$ C $ 5，0，0，
 ROWS（C：file\path\''[Lister.xls] Shhet1'！$ A $ 1：$ C $ 5），1），0）-1，
 MATCH（Age，OFFSET文件\path \ [Lister.xls] Shhet1'！$ A $ 1：$ C $ 5，0，0，1，
 COLUMNS（C：file\path\''[Lister.xls] Shhet1有没有人知道为什么？为什么有人知道为什么？它不喜欢其他文件？ 
这是相同的信息。
 
 
 
如何解决这个问题？
 
 
 
提前感谢:) 
解决方案
只是为了扩展Scott的答案，封闭工作簿的外部路径被视为数组不引用，因此期望引用的函数如OFFSET，SUMIF或COUNTIF等参数返回错误，如果工作簿关闭。 
 
 
 
另一方面，INDEX允许数组作为参数，所以你可以尝试输入：
  = INDEX（A1：C5，MATCH（ 萨拉，INDEX（A1：C5,0,1），0），MATCH（ 年龄，INDEX（A1：C5,1,0） ，0））
  
这应该允许引用封闭的工作簿。 INDEX的另一个优点是它不是易失性的，所以只有当一个依赖单元格（A1：C5）中的单元格发生变化，而OFFSET是一个易失性的单元格时才重新计算。功能，并且将在工作簿中的任何地方更改效率较低的情况下重新计算。
 
I have an array in one file, for example:
    Names    Age   Shoe Size
    Andrew   19    12
    Mary     17     8
    Sarah    15    10
    Wesley   19    11
I want to pinpoint one piece of data based on a given name and data type.
For example, pinpoint Sarah and Age, I want the cell to read 15.

I have read this helpful guide on Double Lookups, but one thing is still bothering me.

Here is the formula:

=OFFSET(A1:C5,MATCH("Sarah",OFFSET(A1:C5,0,0,ROWS(A1:C5),1),0)-1,MATCH("Age",OFFSET(A1:C5,0,0,1,COLUMNS(A1:C5)),0)-1)

It works when the data is in the same file, however when I try and use the given formula from another file, it gets a #VALUE! error.

Code for reading from anther file (all I did was add the file path):
=OFFSET(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5,MATCH("Sarah",OFFSET(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5,0,0,ROWS(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5),1),0)-1,MATCH("Age",OFFSET(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5,0,0,1,COLUMNS(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5)),0)-1)
Here it is spaced so it is easier to read:
=OFFSET(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5,
MATCH("Sarah", OFFSET(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5, 0, 0,
ROWS(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5),1), 0)-1,
MATCH("Age", OFFSET(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5, 0, 0, 1, 
COLUMNS(C:file\path\'[Lister.xls]Shhet1'!$A$1:$C$5)), 0)-1)
Does anyone know why it doesn't like other files?
It's the same information.

How do I fix this?

Thanks in advance :)
解决方案
Just to expand on Scott's answer, external paths to closed workbooks are treated like arrays not references so functions that are expecting references in arguments like OFFSET, SUMIF, or COUNTIF return errors if the workbook is closed. 

INDEX on the other hand does allow arrays as arguments, so you could try entering instead:
=INDEX(A1:C5,MATCH("Sarah",INDEX(A1:C5,0,1),0),MATCH("Age",INDEX(A1:C5,1,0),0))
which should allow for references to closed workbooks as well. 

A further advantage of INDEX is that it is not volatile so will only recalculate when a cell in one of the dependent cells (A1:C5) changes whereas OFFSET is a volatile function and will recalculate whenever a change is made anywhere in the workbook which is less efficient.

                        
这篇关于Excel网格查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！