是否有 Excel 等效方法来模仿 pandas.DataFrame.iloc？

Question

给定一个包含 30 行数据的 Excel 电子表格，如下所示：|------|------------||  id  |    text    ||------|------------||   1  |    apple   ||   2  |    apple   ||   3  |   banana   ||   4  |    apple   ||   5  |   orange   ||  ... |    ...     ||  30  |   orange   ||------|------------|我想知道是否有一种方法可以进行基于整数位置的索引。例如，如果我想返回从第 2 行开始的前 3 行，我将使用df.iloc[2:5]：expected output:|------|------------||   2  |    apple   ||   3  |   banana   ||   4  |    apple   ||------|------------|我最终想要在 Excel 中指定 (1) 起始位置和 (2) 从起始位置返回的行数（例如，返回从 id=21 开始的 10 行）。我尝试过使用 INDEX 创建数组，但无法弄清楚。

Answer 1

为此，您需要两个函数：

MATCH 将在范围内查找值并返回其位置。 INDIRECT 将允许您指定表示范围的字符串，并将返回该范围中包含的值。

匹配功能

间接函数

做了这样的测试：

我在K9中的公式是：

=INDIRECT("A"&MATCH(L4;A:A;0)&":B"&(MATCH(L4;A:A;0)-1+L5))

它是这样工作的：

1. MATCH(L4;A:A;0)会在A列中找到ID位置。因为我说我是在整列中搜索，所以返回的值是ID所在的行号。所以如果我想找到 ID 2，它将返回3。

2. 我们将上一步中的数字与字符串结合起来，得到一个像 一样的字符串A3:B5。5 是从MATCH(L4;A:A;0)-1+L5)我想要查找的 ID 的行号中获得的，将要获取的行数相加，然后减去 1。

3. INDIRECT("A3:B5")是我想要的并且已返回。

注意：根据您的 Excel 版本，间接是否会自动扩展到相邻单元格。

注 2：如果未找到 id，MATCH 将引发错误，并且公式将不起作用。

首先使用 COUNTIF 检查 ID 是否存在于 A 列中，如果为真，则执行 MATCH 和 INDIRECT。像这样的东西：

=IF(COUNTIF(A:A;L4)>0;INDIRECT("A"&MATCH(L4;A:A;0)&":B"&(MATCH(L4;A:A;0)-1+L5));"ID NOT FOUND")

如果返回的行数为 0 或更少，则相同。

Answer 2

根据您的 Excel 版本，有多种公式。

如果您有动态数组（Excel 2016+，如果我没记错的话），您可以在单个单元格中输入以下内容。如果您的 Excel 没有动态数组，则需要进行修改。

=INDEX(fruitTbl,Start,0):INDEX(fruitTbl,Count+Start-1,0)

我将数据范围转换为 Excel 表，但如果您愿意，也可以使用常规寻址。对于表，第一行数据将是，1因为表仅返回数据主体范围。

如果您没有动态数组，请尝试

在某个单元格中输入：

=IFERROR(INDEX(INDEX(fruitTbl,Start,0):INDEX(fruitTbl,Count+Start-1,0),ROWS($1:1),COLUMNS($A:A)),"")

• 然后向右拖动一列

• 选择两个单元格并向下拖动至少直到您开始看到空白单元格