Excel打造計算準確美觀大方的薪水條

  [日期:2009-09-18] 作者: 來源:電腦報 [字體:大 中 小]

企業的財務管理中總是少不了薪水計算和薪水條的設計,如果能夠掌握Excel設計薪水條的方法和技巧,即可事半功倍地快速設計好計算準確又美觀大方的薪水條,否則不僅效率低下而且無法保證準確性。

Excel打造計算準確美觀大方的薪水條

薪水條的特點分析

首先讓我們看看薪水條都有些什麼特點。

1.薪水條頭可能一行,也可能兩行,根據不同企業薪水欄目需求而定。但相同處是每一項條目(或者一個工人的薪水信息)具有一個條頭,條頭具有指定數目的重復性。

2.每一項條目中間有一個空行,方便裁剪。

根據以上特點,我們可以初步擬定薪水條頭部製作方案:

首先建立「薪水明細表」,用於儲存員工薪水表信息,也用於平時編輯和匯總。

其次建立「薪水條目表」,用於引用薪水信息,同時產生薪水條形式之薪水目。

需要用到的函數可以因人而異,有很多公式都可以達成目的,但最後選擇目標是效率最高又易於理解者。

現在,我們通過兩個實例向大家詳解薪水條頭的Excel設計過程,並分析其中技巧,希望望大家能從中受益,舉一反三,設計出適合各自需求的Excel薪水計算公式。

單行薪水條頭之設計

先看下面的數據,這是一個簡易的單行條頭薪水信息摘錄(數據隨機生成,非實際薪水),見圖1。

Excel打造計算準確美觀大方的薪水條

利用此表數據,我們可以通過函數公式來引用數據,生成需要的薪水條形式。

步驟與公式如下:

1.進入「單行表頭薪水條」工作表。

2.選中單元格B1,輸入以下公式:

=CHOOSE(MOD(ROW(),3)+1,「」,單行表頭薪水明細!A$1,OFFSET(單行表頭薪水明細!A$1,ROW()/3+1,))

3.選中單元格B1,滑鼠置於單元格右下角,當箭頭變成十字形時(圖2)

Excel打造計算準確美觀大方的薪水條

則向右拉至J1單元格。然後再選中B1:J1向下拉,直至公式結果出現空白。此時薪水條效果見圖3。

Excel打造計算準確美觀大方的薪水條

4.基本達到目的了,但表格還沒有邊框,列印則不太美觀。現在為它加上邊框:選中B1:J2,並點擊工具欄邊框按鈕中的田字形按鈕添加邊框;再點擊大方框按鈕(圖4),使薪水表條目四周邊框顯示粗一些,比較美觀。

Excel打造計算準確美觀大方的薪水條
  Excel打造計算準確美觀大方的薪水條

5.選擇B1:J3單元格,向下拖拉,直至有數據的最後一行。最後效果見圖5。

Excel打造計算準確美觀大方的薪水條

公式解釋:

=CHOOSE(MOD(ROW(),3)+1,「」,單行表頭薪水明細!A$1,OFFSET(單行表頭薪水明細!A$1,ROW()/3+1,))

1.CHOOSE函數是選擇函數,支持30個參數。第一個參數是選擇序號(數值),其餘參數是被選值。第一個參數是N則結果返回其餘參數中第N個數值。

2.MOD函數是求餘數函數,支持兩個參數,第一個參數是被除數,第二個參數是除數,結果返回餘數。

3.ROW函數是返回指定行行號之函數,若省略參數時則返回當前行。

4.OFFSET函數是返回偏移量之函數。支持五5參數,分別是[參照區域]、[行數]、[列數]、[高度]、[寬度]。

5.「」表示空白,返回空。

本公式巧妙動用MOD和ROW函數產生一個循環的序列2/3/1/2/3/1/2/3/1,再通過CHOOSE函數參數的變化動態地引用薪水明細表的數據,其中「」的作用是當前行行號為3的倍數時返回空,從而產生一個空白行,方便製作薪水條後裁剪。

當然,做到功能還有很多公式,如用以下IF函數做到等,各位用戶自己去多摸索吧:

=IF(MOD(ROW(),3)=1,單行表頭薪水明細!A$1,IF(MOD(ROW(),3)=2,OFFSET(單行表頭薪水明細!A$1,ROW()/3+1,0),「」))

雙行薪水條頭設計

先看數據如圖6。

Excel打造計算準確美觀大方的薪水條

雙行條頭薪水條和單行條頭做到方法基本相同,僅僅是公式有些差異。現暫列如下:

=CHOOSE(MOD(ROW(),4)+1,「」,雙行表頭薪水明細!A$1,雙行表頭薪水明細!A$2,OFFSET(雙行表頭薪水明細!A$1,ROW()/4+2,))

輸入公式後向後拉再向下拉至末行,然後通過前面介紹的方法設置邊框,其中部分單元格需去掉左邊框或者右邊框,使之顯得美觀。最後結果如下見圖7。

Excel打造計算準確美觀大方的薪水條

4.基本達到目的了,但表格還沒有邊框,列印則不太美觀。現在為它加上邊框:選中B1:J2,並點擊工具欄邊框按鈕中的田字形按鈕添加邊框;再點擊大方框按鈕(圖4),使薪水表條目四周邊框顯示粗一些,比較美觀。

Excel打造計算準確美觀大方的薪水條

5.選擇B1:J3單元格,向下拖拉,直至有數據的最後一行。最後效果見圖5。

Excel打造計算準確美觀大方的薪水條

公式解釋:

=CHOOSE(MOD(ROW(),3)+1,「」,單行表頭薪水明細!A$1,OFFSET(單行表頭薪水明細!A$1,ROW()/3+1,))

1.CHOOSE函數是選擇函數,支持30個參數。第一個參數是選擇序號(數值),其餘參數是被選值。第一個參數是N則結果返回其餘參數中第N個數值。

2.MOD函數是求餘數函數,支持兩個參數,第一個參數是被除數,第二個參數是除數,結果返回餘數。

3.ROW函數是返回指定行行號之函數,若省略參數時則返回當前行。

4.OFFSET函數是返回偏移量之函數。支持五5參數,分別是[參照區域]、[行數]、[列數]、[高度]、[寬度]。

5.「」表示空白,返回空。

本公式巧妙動用MOD和ROW函數產生一個循環的序列2/3/1/2/3/1/2/3/1,再通過CHOOSE函數參數的變化動態地引用薪水明細表的數據,其中「」的作用是當前行行號為3的倍數時返回空,從而產生一個空白行,方便製作薪水條後裁剪。

當然,做到功能還有很多公式,如用以下IF函數做到等,各位用戶自己去多摸索吧:

=IF(MOD(ROW(),3)=1,單行表頭薪水明細!A$1,IF(MOD(ROW(),3)=2,OFFSET(單行表頭薪水明細!A$1,ROW()/3+1,0),「」))

雙行薪水條頭設計Excel打造計算準確美觀大方的薪水條

先看數據如圖6。

Excel打造計算準確美觀大方的薪水條

雙行條頭薪水條和單行條頭做到方法基本相同,僅僅是公式有些差異。現暫列如下:

=CHOOSE(MOD(ROW(),4)+1,「」,雙行表頭薪水明細!A$1,雙行表頭薪水明細!A$2,OFFSET(雙行表頭薪水明細!A$1,ROW()/4+2,))

輸入公式後向後拉再向下拉至末行,然後通過前面介紹的方法設置邊框,其中部分單元格需去掉左邊框或者右邊框,使之顯得美觀。最後結果如下見圖7。

Excel打造計算準確美觀大方的薪水條

上一頁123 GO