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

劉師良，王淑艷

（河南醫(yī)學(xué)高等?？茖W(xué)校，河南 鄭州 451191）

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

在計算機二級MS OFFICE 高級應(yīng)用26 套題中，需要考試?yán)霉ぷ鞅怼?2 月份工資”中的數(shù)據(jù)為每位員工制作如圖1 所示工資條。

圖1 工資條樣例

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

1 函數(shù)法

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

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

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

ROW(reference)

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

MOD(number,divisor)

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

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

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

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

2 改進后的函數(shù)法

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

VLOOPUP (lookup_value,table_array,Col_index_num,Range_lookup)

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

3 排序法

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

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

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

4 小結(jié)

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