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中最有用的函數之一，掌握它可以大大提高數據處理效率。