Google表格:具有范围的INDIRECT() [英] Google Sheets: INDIRECT() with a Range

问题描述

这个问题可以改写为在FILTER()中使用以编程方式生成的范围"，具体取决于人们回答该问题的方法.

This question could be rephrased to, "Using a programmatically generated range in FILTER()" depending on the approach one takes to answer it.

编辑- 似乎在尝试证明自己尝试过的过程中无意中包含了太多信息，因此我的问题不清楚.我在此修改中所做的更改可以弥补这一点.

EDIT- It seems that I inadvertently included too much information in my attempts to demonstrate what I've tried so that my question was unclear. The changes I made in this edit should remedy that.

我目前正在使用以下功能进行过滤:

I am currently filtering using the following function:

代码块1

=filter('Data Import'!1:10000,'Data Import'!D:D<12)

导入数据后，列D:D可以更改位置(例如，它可以在列F:F中)，但始终具有标题"student.grade".

After importing data, Column D:D can change positions (eg, it could be in column F:F), but always has the header "student.grade".

问题是:如何像代码块1中给出的那样在过滤器函数中引用具有固定标题的此可变位置列?换句话说，我可以替换'Data导入有效代码的'！D:D`可以使此功能正常运行，而不管标题为"student.grade?"的列的位置如何

The question is: How can I reference this variable-position column with a fixed header in a filter function as in the one given in code block 1? In other words, can I replace 'Data Import'!D:D` with valid code that will allow this function to work regardless of the location of the column with header "student.grade?"

我尝试过的事情:

我可以使用以下代码以字符串的形式正确找到列的地址(无论在数据导入之后是什么):

I can use the following code to correctly find the address of the column (whatever it happens to be after data import) as a string:

代码块2

=substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")&":"&substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")

当标头"student.grade"位于单元格D1中时，上面的代码块2中的函数返回"D:D"，而当"student.grade"位于单元格F1中时返回"F:F".我以为我可以将这个值简单地插入FILTER()函数中并以自己的方式使用，但是为了将我的字符串转换为可用地址，我尝试在代码块2中生成的字符串上使用INDIRECT()函数以上.

The function in code block 2 above returns "D:D" when the header "student.grade" is in cell D1, and "F:F" when "student.grade" is in cell F1. I thought I could simply plug this value into a FILTER() function and be on my merry way, but in order to convert my string to a usable address, I attempted to use an INDIRECT() function on the string produced in code block 2 above.

代码块3

=filter('Data Import'!1:3351,'Data Import'!indirect(substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")&":"&substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,""),TRUE)<12)

公式无法正确解析.

简化同一函数的间接部分以测试在给定范围时它是否将产生相同的错误:

Simplifying the indirect portion of the same function to test whether or not it will work when given a range produces the same error:

代码块4

=filter('Data Import'!1:3351,indirect('Data Import'!&"D:D")<12)

这使我相信INDIRECT()不能处理范围，或者如果可以，我不知道语法. 此Stack Overflow帖子似乎暗示这是可能的，但我可以不能弄清楚细节.

This leads me to believe INDIRECT() doesn't handle ranges, or if it does, I don't know the syntax. This Stack Overflow post seems to suggest this is possible, but I can't work out the details.

这个问题并不是想让别人帮助我解决编程难题.我可以使用各种脚本，辅助if语句的巨型列等等来实现.

This question is NOT an attempt to get others to help me solve my programming dilemma. I can do that with various scripts, giant columns of secondary if statements, and more.

问这个问题是为了了解如何将可变范围传递给过滤器函数(如果可能).

This question is asked for the sake of understanding how to pass a variable range into a filter function (if it's possible).

推荐答案

再一次，也许这就是您想要的:

once again, maybe this is what you want:

=FILTER('Data Import'!1:100000, 
 INDIRECT("'Data Import'!"&
 ADDRESS(1,       MATCH("student.grade", 'Data Import'!1:1, 0), 4)&":"&
 ADDRESS(1000000, MATCH("student.grade", 'Data Import'!1:1, 0), 4)) < 12)

这篇关于Google表格:具有范围的INDIRECT()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！