利用OFFSET函數(shù)將一列數(shù)據(jù)轉(zhuǎn)為多列數(shù)據(jù)的原理與應(yīng)用

周永新，陳曉平

（長沙通信職業(yè)技術(shù)學(xué)院，湖南長沙 410015）

1 認識OFFSET函數(shù)

用途：以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以是一個單元格或單元格區(qū)域，并可以指定返回的行數(shù)或列數(shù)。

語 法 ： OFFSET（reference， rows， cols，height，width）。

參數(shù)：Reference是作為偏移量參照系的引用區(qū)域，它必須是單元格或相連單元格區(qū)域的引用；Rows是相對于偏移量參照系的左上角單元格，上（下）偏移的行數(shù)。如果使用5作為參數(shù)Rows，則說明目標引用區(qū)域的左上角單元格比reference低5行。行數(shù)可為正數(shù)（代表在起始引用的下方）或負數(shù)（代表在起始引用的上方）；Cols是相對于偏移量參照系的左上角單元格，左（右）偏移的列數(shù)。如果使用5作為參數(shù)Cols，則說明目標引用區(qū)域的左上角的單元格比reference靠右5列。列數(shù)可為正數(shù)（代表在起始引用的右邊）或負數(shù)（代表在起始引用的左邊）；Height是要返回的引用區(qū)域的行數(shù)，Height必須為正數(shù)；Width是要返回的引用區(qū)域的列數(shù)，Width必須為正數(shù)。

實例1：如果A1=68、A2=76、A3=85、A4=90，則公式“=SUM(OFFSET(A1:A2，2，0，2，1))”返回177。

2 OFFSET函數(shù)的執(zhí)行過程

從圖1中，可以清晰地看到“實例1”中的各參數(shù)數(shù)據(jù)說明，如圖1所示。

第一步：首先定位“作為參照系的引用區(qū)域”，即圖示中的紅色區(qū)域，實例中該區(qū)域為A1：A2，如圖①所示。

第二步：找到“相對于參照系的偏移行數(shù)”，實例中的該參數(shù)為“2”，即在“參照系的引用區(qū)域”的基礎(chǔ)上偏移2行，如圖②所示。

第三步：找到“相對于參照系的偏移列數(shù)”，實例中的該參數(shù)為“0”，即在“參照系的引用區(qū)域”的基礎(chǔ)上沒有列偏移，光標停留的位置如圖②所示；假如本例中該參數(shù)為“1”，則會在“參照系的引用區(qū)域”的基礎(chǔ)上偏移1列，效果如圖③所示。

第四步：經(jīng)過第二步和第三步，系統(tǒng)已經(jīng)成功的將“引用區(qū)域”的地址定位到了A3單元格，至于這個“引用區(qū)域”將有多大，是一個單元格，還是由連續(xù)的多個單元格組成的一個區(qū)域，則取決于OFFSET函數(shù)中“height”和“width”兩個參數(shù)。實例中height參數(shù)為“2”、width參數(shù)為“1”，即返回的引用區(qū)域的是一個兩行一列的連續(xù)單元格區(qū)域，效果如圖④所示。假如實例中的“height”和“width”參數(shù)分別取值為“2”和“2”，則返回的引用區(qū)域的是一個兩行兩列的連續(xù)單元格區(qū)域，效果如圖⑤所示。

第五步：對引用區(qū)域的數(shù)據(jù)進行運算。實例中即為：對引用區(qū)域為A3:A4中的數(shù)據(jù)進行求和運算，所以返回的結(jié)果為：177。

幾點說明：

1）實例中“作為參照系的引用區(qū)域”為A1：A2，根據(jù)Excel的特點，“相連單元格區(qū)域”的地址為“左上角單元格”的地址，所以在這里，“A1：A2”區(qū)域等價于“A1”單元格。即實例中的函數(shù)=SUM(OFFSET(A1:A2，2，0，2，1))，也可以改寫為：=SUM(OFFSET(A1，2，0，2，1))。

2）OFFSET函數(shù)的第二個參數(shù)rows，即“相對于參照系的偏移行數(shù)”參數(shù)必須為整數(shù)，該整數(shù)可正可負，正整數(shù)表示向下偏移的行數(shù)，負整數(shù)表示向上偏移的行數(shù)。

3） OFFSET函數(shù)的第三個參數(shù)cols，即“相對于參照系的偏移列數(shù)”參數(shù)，同上，該參數(shù)必須為整數(shù)，可正可負，正整數(shù)表示向右偏移的列數(shù)，負整數(shù)表示向左偏移的列數(shù)。

4）Height和Width參數(shù)必須為正整數(shù)。

3 利用OFFSET函數(shù)將一列數(shù)據(jù)轉(zhuǎn)換成多列數(shù)據(jù)

通過分析OFFSET函數(shù)的執(zhí)行過程可以知道：在以指定的引用為參照系的基礎(chǔ)上，通過修改偏移量參數(shù)的值，可以得到一個新的引用。這樣就為解決“將一列數(shù)據(jù)轉(zhuǎn)換成多列數(shù)據(jù)”的問題提供了可能。下面以圖3中的數(shù)據(jù)為例，看看如何利用OFFSET函數(shù)“將一列數(shù)據(jù)轉(zhuǎn)換成多列數(shù)據(jù)”。

第一步：定位“作為參照系的引用區(qū)域”，設(shè)定reference參數(shù)值為：A1，如圖3所示。

第二步：找到“相對于參照系的偏移行數(shù)”，設(shè)定rows參數(shù)。數(shù)值“1”的單元格地址為A1，故相對于參照系的偏移行數(shù)為“0”，數(shù)值“2”的單元格地址為A2，故相對于參照系的偏移行數(shù)為“1”，依次類推，數(shù)值3、4、……9、10的行偏移數(shù)分別為2、3、……8、9，故各數(shù)值的“相對于參照系的偏移行數(shù)”參數(shù)rows的取值分別為0、1、……8、9。

第三步：找到“相對于參照系的偏移列數(shù)”，設(shè)定cols參數(shù)。如圖3所示，數(shù)值1、2、……9、10均在A列上，故相對于參照系A(chǔ)1的偏移列數(shù)均取值為“0”。

第四步：確定“引用區(qū)域”的大小，設(shè)定“height”和“width”兩個參數(shù)。對于每一個數(shù)值單元格，轉(zhuǎn)換后的“引用區(qū)域”仍然是一個單元格，故“height”和“width”兩個參數(shù)均取值為“1”。

以圖3中的數(shù)據(jù)為例，各數(shù)值的OFFSET函數(shù)如表1所示。

表1 各數(shù)值的OFFSET函數(shù)

需要說明的是，轉(zhuǎn)換后的地址是可以根據(jù)用戶的需要任意指定的。例如在同樣的OFFSET函數(shù)參數(shù)設(shè)置下，轉(zhuǎn)換后的數(shù)據(jù)可以有如圖4中所示的多種排列方式。

到此為止，用戶可以根據(jù)自己的需要，利用OFFSET函數(shù)對原數(shù)據(jù)進行隨心所欲的排列。但是有個新的問題卻不容忽視：在只有十個數(shù)據(jù)量的情況下，用戶需要對函數(shù)進行至少十次修改，那么當數(shù)據(jù)量擴展到幾百個、幾千個呢？是否有更好的自動化方法呢？

4 利用自動填充柄，批量執(zhí)行OFFSET函數(shù)

自動填充柄是進行批量數(shù)據(jù)處理的利器。當它結(jié)合公式函數(shù)一起使用時，可分為兩個步驟：第一步，在選定單元格中定義一個可擴充的函數(shù)；第二步，以定義好函數(shù)的單元格為基礎(chǔ)，拖動自動填充柄即可。如何合理運用單元格地址的“相對引用”和“絕對引用”，定義一個可擴充的函數(shù)，是整個問題的關(guān)鍵所在。

根據(jù)表3中的OFFSET函數(shù)，用戶希望得到的各單元格函數(shù)為圖5中的左圖，而如果設(shè)定好C2單元格的函數(shù)為：OFFSET(A1,0,0,1,1)，通過拖動自動填充柄，會得到圖5中的右圖，下面通過分析二者之間的差距來改進該OFFSET函數(shù)。

經(jīng)過觀察不難發(fā)現(xiàn)，自動填充柄拖放OFFSET函數(shù)與手工輸入OFFSET函數(shù)的區(qū)別主要表現(xiàn)在函數(shù)的第一個參數(shù)reference和第二個參數(shù)rows。

第一個參數(shù)reference：觀察手工輸入OFFSET函數(shù)發(fā)現(xiàn)，參數(shù)reference中是一個絕對引用單元格。這個問題不難處理，將該單元格地址設(shè)為絕對引用皆可。設(shè)置后的效果，如圖6所示。

第二個參數(shù)rows：rows的設(shè)置相對于reference要復(fù)雜一些。經(jīng)過觀察，可以發(fā)現(xiàn)rows的取值與轉(zhuǎn)換后數(shù)據(jù)的排列方式和所在行列存在一定的關(guān)系，下面以圖3的數(shù)據(jù)為例，試著找一下這種關(guān)系，如表2所示。

表2 rows取值與轉(zhuǎn)換后矩陣數(shù)據(jù)的關(guān)系

以上關(guān)系可以抽象為一個表達式：

rows取值=（數(shù)據(jù)所在矩陣行-1）×數(shù)據(jù)矩陣的列數(shù)N+數(shù)據(jù)所在矩陣列-1

通過表3，可以驗證這個表達式的正誤（N=3）。

表3 驗證rows表達式

經(jīng)過以上驗證，可以確定：這個rows表達式可以準確地表示rows的取值，至于如何描述“數(shù)據(jù)所在矩陣行”和“數(shù)據(jù)所在矩陣列”，需要借助ROW函數(shù)和COLUMN函數(shù)。ROW(i:i)可以獲得數(shù)據(jù)所在矩陣第i行的引用；COLUMN(A:A)可以獲得數(shù)據(jù)所在矩陣第A列的引用（返回值是1）。如此一來，便可以將rows表達式描述為具體的應(yīng)用表達式，以C2單元格為例，它的應(yīng)用表達式為：

rows取值=(ROW(1:1)-1) ×3+COLUMN(A:A)-1

設(shè)置好reference和rows參數(shù)以后，其他的參數(shù)不需要進行修改，便可以使用自動填充柄批量應(yīng)用函數(shù)了。效果如圖7所示。

至此，便可以完整地做出圖3的效果了。

5 總結(jié)

本文中通過描述OFFSET函數(shù)的原理、執(zhí)行過程、簡單應(yīng)用和結(jié)合自動填充柄的應(yīng)用等四個部分，詳細介紹了利用OFFSET函數(shù)將一列數(shù)據(jù)轉(zhuǎn)換為多列數(shù)據(jù)的原理與應(yīng)用。在實際的應(yīng)用中，可能會有各種各樣的需求，例如“將一列數(shù)據(jù)轉(zhuǎn)換為指定行數(shù)的數(shù)據(jù)”、“將兩列數(shù)據(jù)轉(zhuǎn)換為指定列數(shù)的數(shù)據(jù)”等，這些都可以通過修改OFFSET函數(shù)中相應(yīng)的參數(shù)來實現(xiàn)。

[1]王興玲.計算機應(yīng)用基礎(chǔ)[M].北京:清華大學(xué)出版社,2011.

[2]希爾伯沙茨,楊冬青.數(shù)據(jù)庫系統(tǒng)概念[M].北京:機械工業(yè)出版社,2008.

[3]Excel Home.Excel應(yīng)用大全[M].北京:人民郵電出版社，2008.

[4]微軟公司.MSDN用戶手冊[Z].2011.

[5]Excel Home.Excel 2010應(yīng)用大全[M].北京：人民郵電出版社,2011.