一、題目要求
題目：
請(qǐng)編制公式將A1:H3單元格區(qū)域轉(zhuǎn)換為J2:K13區(qū)域
要求：
J列K列用一個(gè)公式橫向和縱向下拉填充生成
二、解題思路
這個(gè)題看起來似乎很難，是一個(gè)難以完成的任務(wù)。
實(shí)際上并太難。我們一步步分析：
1、分析要引用的數(shù)據(jù)的規(guī)律
如果不用公式，而是直接用單元格鏈接，J列、K列的公式應(yīng)該是這樣的：
我們將上圖中所引用的單元格地址，用行號(hào)列號(hào)來表示，
J列要引用的項(xiàng)目單元格：
A1即為第1行第1列，
C1即為第1行第3列
E1即為第1行第5列
......
K列要引用的金額單元格：
B1即為第1行第2列
D1即為第1行第4列
F1即為第1行第6列
......
將所引用單元格的行號(hào)列號(hào)依次羅列，即為N列和O列、N列和P列所示。
我們分析一下N列O列P列數(shù)據(jù)有無規(guī)律，可以看出，規(guī)律很明顯：
行號(hào)：每重復(fù)四個(gè)然后遞增1；
列號(hào)分別是在1、3、5、7循環(huán)重復(fù)（或在2、4、6、8循環(huán)重復(fù)）
如果我們能構(gòu)造出這樣的序列，然后用INDEX函數(shù)來引用就行了。
INDEX函數(shù)就是專門干這活的：取第幾行第幾列交叉點(diǎn)的值。
比如要取A1:H3單元格區(qū)域的C2單元格的"F"，就是取A1:H3區(qū)域的第2行第3列，公式為：
=INDEX(A1:H3,2,3)
要取F3單元格的11，就是取A1:H3區(qū)域的第3行第6列，其公式為：
=INDEX(A1:H3,3,6)
關(guān)鍵是用什么公式、如何構(gòu)造出N列、O列、P列三列中的序列？
2、構(gòu)造序列
這個(gè)就要用到高中的數(shù)學(xué)知識(shí)了，高中學(xué)了那么多數(shù)學(xué)，現(xiàn)在終于可以派上用場(chǎng)了。下面讓我們穿越到高中數(shù)學(xué)課堂：
數(shù)學(xué)老師在講臺(tái)上使勁敲黑板，大聲喊到：同學(xué)們，安靜安靜，開始做題了，請(qǐng)?jiān)谑昼娭畠?nèi)提交答案，先做完的先下課：
有一個(gè)從1到12的原始序列，請(qǐng)根據(jù)此序列，找到一算式，分別計(jì)算出下面三個(gè)序列
序列一：
1、1、1、1、2、2、2、2、3、3、3、3、4、4......
序列二：
1、3、5、7、1、3、5、7、1、3、5、7、1、3......
序列三：
2、4、6、8、2、4、6、8、2、4、6、8、2、4......
（1）生成序列一：
序列一是每個(gè)數(shù)字重復(fù)四次一遞增，那么我們可以將其除以4。為了讓其精確從1開始重復(fù)四次，因而，將原始序列加3，然后除4再取整數(shù)。用Excel公式表示
即為=INT((n+3)/4)
要讓公式往下拖動(dòng)時(shí)依次遞增，可以用ROW函數(shù)直接生成：
=INT((ROW(A1)+3)/4)
注：ROW函數(shù)的作用是取行號(hào)，ROW（A1）即取A1單元格的行號(hào)，即1，公式往下填充時(shí)，由于使用的是相對(duì)引用，會(huì)自動(dòng)變?yōu)?/p>
=INT((ROW(A2)+3)/4)
=INT((ROW(A3)+3)/4)
（2）生成序列二
下面我們來看如何生成序列二：
序列二1、3、5、7、1、3、5、7、1、3、5、7、是循環(huán)序列。看到這個(gè)循環(huán)的結(jié)構(gòu)，我們感覺它和取余數(shù)有點(diǎn)接近。比如，將1到8分別除以4，取余數(shù)，其余數(shù)分別為：
1、2、3、0、1、2、3、0.......
因而我們應(yīng)該首先想到取余數(shù)。我們先將原序列減1，除以4，然后再取余數(shù)。也就是將0到7，分別除以4，取余數(shù)。其余數(shù)的序列為：
0、1、2、3、0、1、2、3
然后將上面的序列乘以2，再加1
即，=2*N+1
序列就變成了：
1、3、5、7、1、3、5、7
將上面的過程寫成Excel公式，就是：
=2*MOD(N-1,4)+1
將N換成ROW函數(shù)，公式為：
=2*MOD(ROW（A1）-1,4)+1
（3）生成序列三
序列三的公式參考序列二的公式，不贅述。
三、編制公式
先來看前面已經(jīng)提到的簡單傻瓜化的公式：
=INDEX($A$1:$H$3,x,y)
將序列一的公式INT((ROW(A1)+3)/4)代入到上面公式的x
將序列二的公式2*MOD(ROW（A1）-1,4)+1代入到上面公式的y，
代入后，公式為：
=INDEX($A$1:$H$3,INT((ROW(A1)+3)/4),2*MOD(ROW(A1)-1,4)+1)
此公式往下拖動(dòng)填充沒問題，但是往右填充，無法自動(dòng)引用相應(yīng)的數(shù)字呢，要手工修改為：
=INDEX($A$1:$H$3,INT((ROW(A1)+3)/4),2*MOD(ROW(A1)-1,4)+2)
為了能用一個(gè)公式往下和往右拖動(dòng)完成數(shù)據(jù)的引用，將公式修改完善為：
=INDEX($A$1:$H$3,INT((ROW(A1)+3)/4),2*MOD(ROW(A1)-1,4)+COLUMN()-9)
說明：
COLUMN()表示取公式所在單元格的列號(hào)。在J列其計(jì)算結(jié)果為10，在K列其計(jì)算結(jié)果為11。
四、知識(shí)點(diǎn)回顧
本案例最關(guān)鍵的知識(shí)點(diǎn)就是序列的構(gòu)造：
1、重復(fù)N個(gè)遞增一的序列（1、1、1、1、2、2、2、2、3、3、3、3）的構(gòu)造方法
如果構(gòu)造1、1、1、1、2、2、2、2、3、3、3、3這種每重復(fù)N個(gè)遞增一的序列，可以將其除以N，然后取整，公式為：
=INT((ROW(A1)+N-1)/N)
2、N個(gè)連續(xù)數(shù)字的重復(fù)序列（1、2、3、4、1、2、3、4、1、2、3、4）的構(gòu)造方法
這種序列用取余數(shù)的方法來構(gòu)造，其公式為：
=MOD(ROW(A1)-1,N)+1
大家如果想學(xué)習(xí)更多的函數(shù)知識(shí)和Excel實(shí)戰(zhàn)經(jīng)驗(yàn)，歡迎購買《“偷懶”的技術(shù)：打造財(cái)務(wù)Excel達(dá)人》
《“偷懶”的技術(shù)》穩(wěn)踞當(dāng)當(dāng)網(wǎng)辦公類暢銷榜前五名，
好評(píng)率99.7%的Excel暢銷書，你值得擁有！
購買地址：
http://product.dangdang.com/23626444.html
－－－－－－－－－－－－－－－－－－－－
本文首發(fā)于微信公眾號(hào)“Excel偷懶的技術(shù)“，
本公眾號(hào)堅(jiān)持分享原創(chuàng)Excel文章，求實(shí)用、接地氣、不炫技。歡迎大家關(guān)注！
如果本文對(duì)你有幫助，歡迎點(diǎn)贊、轉(zhuǎn)發(fā)分享！
