【Excel】通过Index函数向下拖动单元格并【重复引用/循环引用】数据源

CASE1: 列数据源，向下拖动，每个单元重复N次

案例：从A6单元格开始，一步一步实现最简单的公式，使用INDEX函数引用Q3:Q7范围内的数据，每3行引用下一个单元格

步骤1：基本的INDEX函数

最基本的格式是：

=INDEX(Q3:Q7,1)

这会引用Q3:Q7范围中的第1个元素，即Q3。

步骤2：添加行号计算

我们需要根据当前行与起始行(A6)的差值来计算要引用的行：

=INDEX(Q3:Q7,1+INT((ROW()-ROW(A6))/3))

步骤3：添加绝对引用以便拖动

为了确保拖动时引用正确的范围，添加绝对引用符号$：

=INDEX($Q$3:$Q$7,1+INT((ROW()-ROW($A$6))/3))

CASE2:列数据源，向下拖动，每个单元重复1次，周而复始

让我们一步步实现这个公式，从A6单元格开始，每行引用Q3:Q7范围内的下一个数据，并且在超出范围时循环重复。

步骤1：基本的INDEX函数

首先，我们使用INDEX函数引用Q3:Q7范围的第一个单元格：

=INDEX(Q3:Q7,1)

这会返回Q3的值。

步骤2：根据行号确定引用位置

我们需要计算当前行相对于起始行(A6)的偏移量：

=INDEX(Q3:Q7,ROW()-ROW(A6)+1)

这样，在A6单元格会引用Q3，A7单元格会引用Q4，依此类推。

步骤3：添加MOD函数实现循环引用

为了在超出Q3:Q7范围时循环，我们使用MOD函数：

=INDEX(Q3:Q7,MOD(ROW()-ROW(A6),5)+1)

这里的5是Q3:Q7的元素个数，MOD函数确保结果始终在1-5之间循环。

步骤4：添加绝对引用以便拖动

为了确保拖动时引用正确，我们添加绝对引用符号$：

=INDEX($Q$3:$Q$7,MOD(ROW()-ROW($A$6),5)+1)

最终公式效果

当你从A6单元格开始，向下拖动时：

• A6: 引用Q3（第1个元素）
• A7: 引用Q4（第2个元素）
• A8: 引用Q5（第3个元素）
• A9: 引用Q6（第4个元素）
• A10: 引用Q7（第5个元素）
• A11: 循环回到Q3（第1个元素）
• A12: 引用Q4（第2个元素）
• 以此类推…

这个公式确保了无论你向下拖动多少行，引用都会在Q3:Q7范围内循环，不会出现错误。

这个公式的工作方式：

• 当在A6单元格时：ROW()-ROW($A$6) = 6-6 = 0，INT(0/3) = 0，所以引用的是Q3
• 当拖到A7单元格时：ROW()-ROW($A$6) = 7-6 = 1，INT(1/3) = 0，所以引用的仍是Q3
• 当拖到A8单元格时：ROW()-ROW($A$6) = 8-6 = 2，INT(2/3) = 0，所以引用的仍是Q3
• 当拖到A9单元格时：ROW()-ROW($A$6) = 9-6 = 3，INT(3/3) = 1，所以引用的是Q4

依此类推，每3行引用下一个数据源。这是最简单直接的实现方式。

CASE3: 列数据源，向下拖动，每个单元重复N次，周而复始

让我实现一个公式，使数据源Q3:Q7中的每个单元格值重复N次，并且循环往复。

步骤1: 基本的INDEX函数结构

首先建立基本结构：

=INDEX($Q$3:$Q$7,position)

我们需要确定position的计算方法。

步骤2: 计算相对行号

假设从A6开始，每个单元格重复N次：

=INDEX($Q$3:$Q$7,INT((ROW()-ROW($A$6))/N)+1)

这个公式计算当前行与起始行的差值，除以N取整，然后加1。

步骤3: 添加MOD函数实现循环

为了确保循环引用Q3:Q7中的数据：

=INDEX($Q$3:$Q$7,MOD(INT((ROW()-ROW($A$6))/N),5)+1)

这里的5是Q3:Q7的元素个数。

完整公式

假设要每个单元格重复3次：

=INDEX($Q$3:$Q$7,MOD(INT((ROW()-ROW($A$6))/3),5)+1)

效果演示

如果N=3（每个单元格重复3次），从A6开始：

• A6, A7, A8: 引用Q3（第1个元素）
• A9, A10, A11: 引用Q4（第2个元素）
• A12, A13, A14: 引用Q5（第3个元素）
• A15, A16, A17: 引用Q6（第4个元素）
• A18, A19, A20: 引用Q7（第5个元素）
• A21, A22, A23: 循环回到Q3（第1个元素）
• 以此类推…

如何调整重复次数

只需修改公式中的除数即可改变重复次数：

• 重复2次: /2
• 重复4次: /4
• 重复10次: /10

例如，如果要每个单元格重复4次：

=INDEX($Q$3:$Q$7,MOD(INT((ROW()-ROW($A$6))/4),5)+1)

这个公式可以灵活调整重复次数和数据源范围，实现各种复杂的引用模式。

附录： Excel INDEX函数介绍

INDEX函数是Excel中非常强大的查找函数，它能够从数据区域中返回指定位置的值。

基本语法

INDEX函数有两种形式：

数组形式

INDEX(array, row_num, [column_num])

引用形式

INDEX(reference, row_num, [column_num], [area_num])

参数解释

• array/reference: 要查找的单元格区域或范围
• row_num: 要返回的行号
• column_num: [可选] 要返回的列号。如果省略，默认为1
• area_num: [可选] 在引用形式中，如果引用包含多个区域，指定使用哪个区域

使用示例

基本用法

=INDEX(A1:D10, 3, 2)

返回A1:D10区域中第3行第2列的值

单列数据查询

=INDEX(A1:A10, 5)

返回A1:A10中第5个单元格的值（即A5）

单行数据查询

=INDEX(A1:E1, 1, 3)

返回A1:E1中第3个单元格的值（即C1）

高级应用

与其他函数结合

常与MATCH函数配合使用，实现灵活的查找功能：

=INDEX(A1:D10, MATCH("Smith", A1:A10, 0), 3)

查找A列中包含"Smith"的行，并返回该行第3列的值

动态引用

可以结合ROW、COLUMN等函数实现动态引用：

=INDEX(A1:Z100, ROW()-1, COLUMN())

INDEX vs VLOOKUP

• INDEX更灵活，可以查找行、列或指定的交叉点
• INDEX可以从左向右或从右向左查找，而VLOOKUP只能从左向右
• 与MATCH结合使用时，INDEX通常比VLOOKUP效率更高
• INDEX可以返回整行或整列数据，而VLOOKUP只能返回单个值

INDEX函数是Excel中最有用的函数之一，掌握它可以大大提高数据处理效率。