使用列号引用范围 [英] Reference a range using column number
问题描述
我正在寻找一些比较简单的东西，但是我一直在尝试一段时间。我需要从列中获取值。
I'm looking for something rather simple, but I've been trying to do it for quite some time now. I need to get values from a column.
让我说我有几列标题，我需要从列中提取具有特定标题的数据。我可以使用 MATCH 轻松获取它的号码来查看包含头部的行。现在我有号码 - 我需要从标题下面的这个列拉出10个单元格。
Lets say I have several columns with headers and I need to pull data from a column with a specific header. I can easily get it's number by using MATCH to look through the row with headers. Now that I have number - I need to pull say 10 cells from this column right below the header.
例如，如果头是在第一行，我需要的在 E 列，那么我需要拉
For example if headers are in the first row and the one I need is in the E column then I need to pull E2:E11. And I have 5 as the column number resulting from MATCH function. I would use OFFSET as it does pretty much what I need with values like this OFFSET(A1;1;MATCH(blablabla);10;1). And it works just fine, however I need to do this from a closed workbook and OFFSET only works with currently opened ones.
作为一个简单的解决方案，我不敢相信Excel提供了一个类似 COLUMN 的功能，但不是相反的解决方案。为了使用所得到的工作簿的人，我无法真正切换到 R1C1 视图，但如果这是我唯一的选择，我想这将是必须的。
There's got to be a simple solution, I can't believe Excel provides a function like COLUMN but not the reversed solution. For the sake of people using the resulting workbook I can't really switch to R1C1 view but if that would be my only option, I guess that will have to do.
请咨询我在这种情况下能做什么。
Please advice what I could do in this situation.
推荐答案
尝试使用 INDEX ，应该使用封闭的工作簿可以正常工作，语法将如下所示：
Try using INDEX, that should work OK with a closed workbook, syntax would be like this:
= INDEX（[Book1的.xls] Sheet 1中$ B $ 2：$ Z $ 11,0，MATCH（ X，[Book1.xls的] Sheet 1中$ B $ 1：$ Z $ 1,0））
将在book1 sheet1的B1：Z1中找到x，然后在下面给出10个单元格（第2到11行）
That will find "x" in B1:Z1 of book1 sheet1 and then give the 10 cells below (in rows 2 to 11)
这篇关于使用列号引用范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
