Excel 工資條制作的兩類方法匯總＊

劉師良，王淑艷

（河南醫學高等?？茖W校，河南 鄭州 451191）

在計算機二級的考試過程中，Excel 所占的比重是非常高的，而且難度相對來說也最大，尤其是對于非計算機專業的學生來說，理解Excel 中的公式和函數有一定的困難，所以對備考的學生來說，Excel 是學習的重中之重。而在這些Excel 題目中，工資條的制作是其中難度較大的，參考資料中給出的方法是函數法，在本文中會對函數法進行詳細介紹，并對資料中的函數法進行改進，讓其更為簡便。另外，在本文中會介紹幾種其他的非函數法解決方案。

在計算機二級MS OFFICE 高級應用26 套題中，需要考試利用工作表“12 月份工資”中的數據為每位員工制作如圖1 所示工資條。

圖1 工資條樣例

本文所使用的方法分為兩類：一是函數法。主要是利用函數來解決工資表的創建，通過函數不斷從“12 月份工資”工作表中獲取數據，并通過函數控制數據的位置來生成工資條。二是排序法。排序法主要是對編號進行排序，使得工資條標題行、數據行和空行按照圖示的方式進行排序，以完成工資條的制作[1]。

1 函數法

在本文中所用到的函數法中使用了4 個函數，下面先對這四個函數的功能進行介紹[2]。

CHOOSE(index_num,value1,[value2]……)

CHOOSE 函數是根據索引值的內容，從參數中選出相對應的操作，也就是根據index_num 值的不同，選擇不同的value 值。如果index_num 的值為1，則返回value1 的值或進入value1 相應的操作，如果index_num 的值為2 則返回value2 的值或進入相應的操作，以此類推。

ROW(reference)

ROW 函數的功能是得到reference 參數的行的值，也就是參數的行號。

MOD(number,divisor)

MOD 函數的功能是返回兩個數字相除后的余數，number 是被除數，divisor 是除數。在本文的應用是MOD(ROW(),3)，也就是把行號除以3 取余數。以A2 單元格為例，經過MOD 函數處理后，得到的余數為2。

OFFSET(reference,rows,cols,[height],[width])

OFFSET 函數的功能是以參數reference 為起始位置，通過rows、cols、height 和width 等四個參數來計算出偏移量，從而得到新的引用位置。rows 表示偏移的行數，cols 表示偏移的列數，height 表示新引用區域的行數，width 表示新引用區域的列數。在本文的應用中，新的引用只是一個單元格，所以OFFSET 函數的最后兩個函數缺省，只用到了前面三個參數。以工資表A2 單元格為例，其中的函數為OFFSET('12 月工資表'！A$3,ROW()/3,0)，其中的ROW()/3 的返回值為0，也就是說在此處的函數引用值應為12 月工資表'！A$3，沒有偏移量，此行向后填充會以此引用“12 月份工資”表的表頭行。在該函數中妙用了ROW()/3，可以保證每3 行引用一次“12 月份工資”表的表頭行。A3 單元格則由于ROW()/3的返回值為1，會產生1 行的偏移量，正好是第一個員工的具體數據，隨著公式的向下填充，ROW()/3 的返回值也會相應的增加，如圖1 所示，第六行的A6單元格中，ROW()/3 的返回值為2，則OFFSET 函數會向下產生兩行的偏移量，也就是把第二位員工的具體數據引用到此處。以此類推，不斷向下偏移。

在上述四種函數的結合下，首先新建“工資條”工作表，在A1 單元格中輸入如下函數：CHOOSE(MOD(ROW(),3)+1,OFFSET('12 月工資表'！A$3,ROW()/3,0),"",'12 月工資表'！A$3)，然后拖動“填充柄”依次向右向下填充，直至產生所有員工的工資條為止。在這個公式中，MOD(ROW(),3)+1 首先返回一個值，通過前面的介紹，以A2 單元格為例，則會返回3，也就是在A2 單元格會進入參數value3 的相應操作，也就是12 月工資表'！A$3。A3 單元格中，由于MOD(ROW(),3)+1 返回1，所有進入OFFSET('12 月工資表'！A$3,ROW()/3,0)相應的操作，根據前面的介紹，可以得到此處應出現第一位員工的工資數據。以此類推，可以很容易獲取其他數據產生的原因。

2 改進后的函數法

在第一種函數法解決方案中，函數的應用有些復雜，特別是最后使用的公式復雜度更高，對于初學計算機基礎的學生尤其是非計算機專業的學生而言，有一定的難度。針對這一問題，本文對上面的函數進行優化和簡化，經過簡化后的函數基本上只用了一個函數——VLOOKUP。具體操作是這樣的。首先將“12 月份工資”表的表頭行復制到“工資表”中，以A2 為起始單元格。然后在A3 單元格中鍵入“=OFFSET('12 月工資表'！A$3,ROW()/3,0)”，根據前面函數法的介紹中，讀者很容易就可以得出這樣的結論：在此鍵入此函數的目的是把“12 月份工資”表中的第一位員工的數據引用至此。然后在B3 單元格中鍵入“=VLOOKUP($A3,'12 月工資表'！$A$4:$M$71,COLUMN(工資條樣例！B2),0)”，然后拖動B3 單元格的填充柄至M3，即可完成員工“刀白鳳”的工資條數據的填充。接下來需要選中單元格區域A2:M4，拖動此區域的填充柄至M205，即可生成所有員工的工資條[3]。

VLOOPUP (lookup_value,table_array,Col_index_num,Range_lookup)

VLOOPUP 函數的功能是在table_array 的首列中搜索lookup_value 的值，找到后得到一個行號，在本文的應用中，是要在“12 月份工資”表的第一列查找A3 單元格中的員工編號，找到后得到一個行號，然后結合Col_index_num 的列號確定一個單元格，應用到B3 單元格。Range_lookup 確定匹配模式，在本文中要求精確匹配，所以第四個參數是0。在VLOOKUP 函數中嵌套了一個函數COLUMN (工資條樣例！B2)，這個函數會返回個數字，也就是參數列的序列，在此處會得到2，也可也直接使用數字“2”，但是為了方便后面的填充時返回數值列的變化需要，在此處使用了這個函數，也可以使用數字“2，3，4，5，6，7……”。教師在講解的時候也可先用數字，然后再使用函數來替換，這樣更方便學生理解[4]。

3 排序法

根據圖1 所示，員工的工資條分為三個部分，第一部分是表頭部分，每位員工的工資條都有相同的表頭。第二部分是數據部分，每位員工都擁有自己不同的工資數據。第三部分是空行，為了顯示和裁剪方便，每位員工工資條數據的下方都有一行空行。根據這樣的特點，工資條完全可以用排序法來完成[5]。

第一步現將“12 月份工資”表中“工資條”表中，以B2 為起始單元格。通過觀察發現數據共68 行，接下來錄入工資條中表頭行，也可將“12 月份工資”表中的表頭行進行復制，并填充68 行。第二步進行編號。在A 列進行編號，先將數據單元格填充以2為起始，步長為3 的序列，序列的最后一個數字是203。再將表頭行填充以1 為起始，步長為3 的序列，序列的最后一個數字是202。最后在下面的空行處填充上以3 為起始，步長為3 的序列，序列的最后一個數字是204。最后一步是排序，以A2：A205區域內的數字為基準進行升序排列，將A 列刪除后即可得到圖1 所示工資條[6-8]。

上述的排序法最為直接簡單，此方法正是利用了工資條中的規律，通過編輯數字序列并將序列進行排序得到的。

4 小結

以上是筆者在日常教學的過程中，針對工資條的制作總結的不同的方法，方法各有利弊，排序法最為簡單，學生接受容易，但是應對變化能力不足。函數法雖然理解起來不易，但是，其變化多樣，可以讓學生更好地掌握公式和函數使用，是一個較好的復雜公式函數應用的案例。當然，Excel 的功能十分強大，還有很多其他的方法能夠解決這一問題。