如何用excel製作工資條?

時光也會念舊O


只需要新增一列輔助列,Excel製作工作條將變得無比簡單。


首先我們要知道一個概念:什麼是輔助列?

在Excel中,存在這樣“具有極大價值的”神操作,它就是輔助列。Excel中的輔助列思想講究的是:退一步海闊天空!指的是添加輔助列往往會增加解決問題的步驟,而增加的解決步驟卻會帶來每一步難度的大幅降低。


如圖所示,是存放員工工資的表格,要把這樣的表格中每一行數據添加上表頭,形成如下圖所示的工資條。

圖1:員工工資表


圖2:由工資表生成的工資條


解決Excel問題,常用的一個思路是倒推法,這這個工資條的問題,從想要實現的效果來看,它相比原始數據,有什麼樣的變化?

①每一行需要插入的數據是相同的(都是姓名、基本工資、績效工資……)
②插入的規律是相同的:隔一行插入一行


因此,如果我們能在現有的基礎上間隔一行插入一個空行,那麼利用Ctrl+G定位空行、Ctrl+Enter批量錄入就能解決此問題。再把思維進行擴散,事實上,Excel中本身已經存在大量的空行,何必再插入空行呢?我們只需要將數據行下面的空行翻轉到數據行之間即可!


Step1:在最後一列創建輔助列,並填充序號,如圖3所示。

圖3:創建輔助列,並填充序號


Step2:在輔助列下方,再創建一列數據

通常直接複製已有的輔助列數據即可,這是為了給下面而空行加上序號,如圖4所示。

圖4:輔助列下方再創建一列數據


Step3:將首行標題粘貼至空白處,並填充,如圖5所示

圖5:在空白行處填充標題行


Step4:對輔助列按升序排序

就實現了我們預想工資條的效果,如圖6所示。

圖6:生成的工資條效果


解決這個問題,用到的是轉化的思維:把插入空行轉化為利用空行(因為Excel表格中,數據區域之外全部是空行)。然後通過創建輔助列建立了通向解題的橋樑,使得已知條件(Excel中的空行)和目標答案(將空行和數據行進行穿插)有效的聯繫起來,問題迎刃而解。

輔助列思想在Excel中具有非常大的威力,能夠以小博大,你學會了麼?


「精進Excel」系頭條簽約作者,關注我,如果任意點開三篇文章,沒有你想要的知識,算我耍流氓!

精進Excel


用VLOOKUP函數製作工資條

工資表原始數據,如下圖示:

製作方法很簡單,只需3步!

步驟1:新建一工作表,命名為“工資條”,並把工資表表頭複製到"A2:O2"單元格區域,在"A3"單元格中,輸入工號"A0001",如下圖示:

步驟2:單擊"B3"單元格,在編輯欄中輸入公式【=VLOOKUP($A3,工資表!$A$1:$O$19,COLUMN(B$1),0)】,然後,向右拖動鼠標至"O3"單元格。如下圖示:

步驟3:選擇"A2:O4"單元格區域,拖動鼠標右下角,向下填充。如下圖示:

鳴謝:若喜歡本篇文章,請點贊·、轉發和分享!

有問必答,來信必回!!


ExcelVBAHome


簡單的單表頭工資條其他回答已經很清楚了,這裡補充一中複雜工資條的製作思路:

製作工資條,可謂是人資財務最常用的EXCEL技巧了,今天將給大家帶來稍微複雜一點的工資條製作方法!如圖所示工資條,看似簡單,可是難住不少同學呢:

我們先來看一下工資明細表:

每名員工的工資單都佔4行,而明細表一名員工只佔一行,那麼工資條的行號和明細表的行號存在一個固定的數學關係:

設A為工資條行號,B為明細表行號,那麼可以得到:B=(A+2)/4+1

我們先獲取姓名,只需輸入以下公式:

=INDEX(明細表!C:C,(ROW()+2)/4+1)

其他的明細,只需更改引用列,例如:日薪在明細表的E列,那麼獲取日薪的公式為:

=INDEX(明細表!E:E,(ROW()+2)/4+1)

依此類推,得到一個完整的工資單:

最後,將這個完整的工資單往下拉就完成啦!

本文中主要是INDEX函數的應用:index函數的作用是返回指定區域的值或引用,

具體語法為:=index(引用區域,行號,[列號]),其中,列號是可以省略的,默認值為1;

例如:index(A:A,2,1) 和 index(A:A,2) 均表示引用A列第2行的數據

本文的示例文件,關注小編並回復 “工資條”即可獲取哦


Excel精選技巧


一般來說工資條就是表頭+工資數據,不想講的太過複雜,這裡就簡單介紹一下常見的兩種製作方法!

一、用排序製作

步驟:在工資表右側添加重複序列,複製對應數量的表頭,點擊【數據】,進行排序即可。

二、用公式製作

1、VLOOKUP函數(=VLOOKUP($F2,$A$1:$D$9,COLUMN(B1),))

步驟:製作一個工資條,輸入公式填充數據,選中相應區域,進行拖拉填充即可。

2、IF函數

=IF(MOD(ROW(A1),2),OFFSET($A$1,(MOD(ROW(A1)-1,3)>0)*ROUND(ROW(A1)/3,),COLUMN(A2)-1),"")

步驟:直接套用公式,依舊拖拉填充即可。

可根據需要隨意選擇對應方法使用!


辦公技巧小達人


工資條製作方法很多,我們一起看看最簡單的一種製作工資條的方法!

工資條製作具體步驟:

1、首先看看我們最後完成的效果。

2、在H列添加1列輔助列並複製一份,如下圖所示。

3、複製工資條的標題到圖片中的位置。

4、選中輔助列的數字“1” -- 點擊“編輯”菜單欄的“排序和篩選”,在下拉菜單選擇“升序”。

5、可以看到每個員工姓名上方對應的工資條,刪除輔助列即可。

6、演示動圖。

上面方法是不是很簡單?用過這種方法的在評論區留言點贊吧,沒用過的轉發收藏吧!

給小編一個關注鼓勵吧,小編會努力寫出好的回答的~


跟小小筱學電腦技能


工資條製作需要每一個人的數據都要有一個表頭,說兩個比較快的方法:

巧用升序功能

以下賬圖為例,咱們從系統中導出原始數據,簡單的篩選後可以得到一張雙維度的表格。我的同事就是要將這張表格拆成每個人的工資條。excel技術高的夥伴一般碰到雙維度表格拆分時都會採用VB來做,但是宏不是每個小夥伴會用的。現在給大家介紹第一個方法:

【第一步】

將表頭中合併單元格拆分,變成統一的格式:

【第二步】

複製第一列序號,接著在最後一個序號下方粘貼:

【第三步】

複製表頭單元格,粘貼在序號後邊:

【第四步】

選擇序號列選擇升序排列即可。

通過以上四個簡單的步驟就可以完成工資條拆解,是不是比一條一條的複製要來的快。

套表模版

方法一可以大大提高效率,但是需要每個月按照四個步驟來一次,平均下來還是需要30分鐘完成,接下來的方法我們通過套表來做,這樣我們只需要導取數據,粘貼數據即可完成,把工作縮短在5分鐘內完成:

【第一步】

新建一張插頁,並且將數據源表中的表頭複製粘貼,在編號列第一個單元格填寫1:

【第二步】

用vlookup公式獲取數據,在B4單元格填寫公式:=VLOOKUP($A$4,工資明細!$A$4:$P$17,COLUMN(),0)

【第三步】

選中B4單元格,鼠標向右拖動向右填充所有單元公式:

【第四步】

將每個單元格中公式中$A$4絕對引用去掉A4:=VLOOKUP(A4,工資明細!$A$4:$P$17,COLUMN(),0)

【第五步】

選中所有單元格,向下拖動複製,直到生成每位同事的工資條:

通過以上方式我們就完成了套表製作,後期我們只需要每個月導取數據,將數據粘貼至表工資明細即可,如果有新增同事只需繼續向下複製即可(因為是以序號匹配,所以套表不受人員離職影響)

以上便是工資條製作的兩個小技巧,關注公眾號“菜鳥辦公”回覆“工資條”獲得文章中案例製作表格文件。


午後電商


謝謝,非常高興回答這個問題!

用excel製作工資條的方法有很多,但對於剛進入職場的新同學而言;還是比較陌生的。前幾天還分享一個技巧中就有一個關於:如何用excel製作工資條的技巧?

操作方式:今天分享一個簡單的操作方法,原數據:

首先,需要從輔助列內輸入一組序號,然後複製序號,粘貼到已有的序號之下。

其次,複製列標題,粘貼到數據區域後。

最後,鼠標任意點擊一個序號,在“數據”選項卡下面點擊“升序按鈕”。

來看看效果吧!

希望能夠幫助到大家!

關注我的微信公眾號:IT微技能加油站(點擊微信添加朋友——公眾號——IT微技能加油站),定期分享你想要的職場生存技能及自媒體相關知識!

也歡迎關注:睡前讀書十分鐘(點擊微信添加朋友——公眾號——睡前讀書十分鐘),定期分享如何把控心態及工作生方式的文章;及時瞭解世界、讀懂人生!


office微技能加油站


上面的答案都有點複雜~ 作為HR老司機,這裡來分享一個我覺得超級簡單的工資條製作方法:利用函數快速用excel製作工資條

一般而言,HR製作的工資表表單是這樣的:表頭+不同員工的工資數據


但是呢,我們實際需要的工資表示這樣的:每條工資數據都有一個表頭。

遇到這樣的情況,很多人說:一個個複製表頭就好了。這個辦法,對於公司人少管用,但要是人數較多,達到成百上千的人數就不好用了。這裡,盟主我就告訴大家兩個簡單的函數操作方法

,快速製作工資條。

排序法

在工資條表格之外的單列中輸入1.2.3...序號,用作排序。再複製該序號複製到下面的行。在表格下粘貼相同行數的標題行,然後根據序號坐在的列排序即可。

原理:排序的時候,相同的序號會被排在一起,也就是複製的表頭1會和工資數據1排列在一起,因而一張工資條就做好了。


公式法

這是一個比較通用的公式(工資條隔一行):

=IF(MOD(ROW(A1),2),OFFSET($A$1,(MOD(ROW(A1)-1,3)>0)*ROUND(ROW(A1)/3,),COLUMN(A2)-1),"")

套用公式方法:只需要修改上面公式的$A$1 ,它是工資表的標題行的第1個單元格地址(如果引用其他表,需要加上“表名!”,如工資表!$A$1)。


是不是很簡單?必須的!

如果你覺得這兩個辦法都有點麻煩,盟主這次還準備了一份大禮包:送你415個帶公式的Excel模板自動生成工資/個稅/社保/報表/可視圖表,帶公式、帶格式、拿來即用!禮包領取方式請見下方:



薪人薪事百萬HR聯盟,HR乾貨擔當,天賦異稟的職場老司機。

薪人薪事百萬HR聯盟


用excel製作工資條的方法非常多,比如輔助列排序插空法、神長公式法、VBA法等等,但是,每個方法都有些問題,比如:

  • 輔助列排序插空法:每次數據更新得重新搞一次,雖然不復雜,但也很煩;

  • 神長公式法:公式比較難懂,數據增加後得手動擴展公式,數據量很大時可能出現卡頓;

  • VBA法:得學好VBA,這個對很多Excel用戶來說比較痛苦。

因此,以下給出Excel2016的新功能Power Query(Excel2010或Excel2013可到微軟官方下載相應的插件)的解法,雖然步驟有點兒多,而且也用到了Power Query裡的函數,但是,總體操作不復雜,每個函數也是單獨使用,比Excel中的多個函數嵌套使用更容易理解,關鍵是,通過Power Query實現的方案可以一鍵刷新,一勞永逸!具體過程如下:

Step-01:基礎的工資表數據獲取到PQ後,首先對基礎工資表加個索引列,方便後面合併了標題表和空白表後的排序。

為方便後續合併表時寫公式,添加後將步驟名稱改為“源加索引”:

Step-02:打開【高級編輯器】,通過M函數添加標題表

修改前代碼及需要修改的地方:

修改代碼,增加以下函數(公式):

標題=Table.FromList(

Table.ToRecords(源),

Record.FieldNames,

Table.ColumnNames(源)

)

修改後如下(注意其中上一步驟中增加的逗號和in後面要修改的內容):

點擊【完成】後,標題表就構建完成了,結果如下圖所示:

Step-03:同樣的,給標題表增加索引列

Step-04:類似的,添加空白行表

修改前代碼及需要修改的地方如下:

加入以下函數(公式):

空行=Table.FromList(

Table.ToRecords(源),

null,

Table.ColumnNames(源)

)

修改後代碼如下:

此時結果如下(全都是錯誤,不過沒有關係,PQ中的錯誤在Excel中就顯示為空白):

Step-05:給空白表加索引

Step-06:用M函數將添加了索引列的工資表、標題表和空白表合併在一起

(為寫合併表公式時含義明確,按照Step-01的方法分別修改標題表加索引的步驟和空白表加索引的步驟名稱為“標題加索引”和“空白加索引”)

修改前代碼及需要修改的地方如下:

增加合併表的公式:

合併 = Table.Combine(

{標題加索引,源加索引,空白加索引}

)

修改後代碼如下:

Step-07:最後,對索引列排序就OK了

小勤:嗯。過程很清晰,就是那幾個函數感覺挺複雜的。

大海:這裡面用到的函數的確比較多一點兒。其中:

  • Table.FromList、Table.ToRecords涉及表和行列結構類型的數據轉換,比較複雜一點兒,以後我專門針對不同數據結構之間轉換的一系列函數跟你講,對比著學;

  • Table.ColumnNames比較簡單,就是取一個表的所有的字段名稱;

  • Table.Combine也比較簡單,就是將多個表縱向追加到一起。

這幾個函數你先試著自己查一下文檔理解一下,後面2個函數應該不會有什麼問題的。

小勤:好的。關於數據結構之間的轉換出個系列吧,感覺這部分內容很重要啊。


更多精彩內容,敬請關注【Excel到PowerBI】

私信我即可下載60+Excel函數、數據透視10篇及Power系列功能95篇彙總訓練材料

我是大海,微軟認證Excel專家,企業簽約Power BI顧問

讓我們一起學習,共同進步!


Excel到PowerBI


通常HR製作的工資表單是這樣的:一個表頭,下面是不同員工的工資數據。

但我們想要的工資條卻是這樣的:每條工資數據都有一個表頭。

所以,你在做工資條的時候是一個個複製表頭?幾十個員工或許可以這樣做,幾百個幾千個員工怎麼辦?

告訴你兩個簡單的函數操作方法,幫你快速搞定工資條的製作。

排序法

在工資條表格之外的單列中輸入1.2.3...序號,用作排序。再複製該序號複製到下面的行。在表格下粘貼相同行數的標題行,然後根據序號坐在的列排序即可。

原理:排序的時候,相同的序號會被排在一起,也就是複製的表頭1會和工資數據1排列在一起,因而一張工資條就做好了。

公式法

這是一個比較通用的公式(工資條隔一行):

=IF(MOD(ROW(A1),2),OFFSET($A$1,(MOD(ROW(A1)-1,3)>0)*ROUND(ROW(A1)/3,),COLUMN(A2)-1),"")

套用公式方法:只需要修改上面公式的$A$1 ,它是工資表的標題行的第1個單元格地址(如果引用其他表,需要加上“表名!”,如工資表!$A$1)。

更多財稅職場學習資訊,關注秀財網


分享到:


相關文章: