淺談Excel 數(shù)據(jù)透視表在財(cái)務(wù)工作中的應(yīng)用

張磊

（中國(guó)水利水電第七工程局成都水電建設(shè)工程有限公司，四川 成都 611130）

一、Excel 中常用的函數(shù)

在項(xiàng)目的日常財(cái)務(wù)工作中發(fā)現(xiàn)，對(duì)Excel 表格的使用非常頻繁，分包等各類臺(tái)賬的登記、工資獎(jiǎng)金等統(tǒng)計(jì)表的制作、個(gè)稅申報(bào)等模板的錄入……隨處可見(jiàn)，但是在制作這些表格時(shí)，使用的Excel 函數(shù)幾乎都是簡(jiǎn)單的加減乘除，雖然這些簡(jiǎn)單的功能足夠應(yīng)對(duì)數(shù)據(jù)的簡(jiǎn)單錄入、統(tǒng)計(jì)。但是一旦遇到比較復(fù)雜、繁瑣的數(shù)據(jù)，這些簡(jiǎn)單的加減乘數(shù)顯的蒼白無(wú)力，僅僅靠復(fù)制粘貼，則會(huì)使工作效率大幅下降。下面介紹幾種在財(cái)務(wù)工作中常用的幾種函數(shù)。

（一）VLOOKUP 單條件查詢函數(shù)

在工作中，我們經(jīng)常會(huì)收到公司發(fā)送的統(tǒng)計(jì)表，要求各項(xiàng)目按要求填寫相關(guān)數(shù)據(jù)，公司會(huì)根據(jù)實(shí)際情況發(fā)送各種形式的統(tǒng)計(jì)表，這時(shí)，我們不可避免的會(huì)面臨公司發(fā)的統(tǒng)計(jì)表與我們自己做的統(tǒng)計(jì)臺(tái)賬會(huì)有些形式的不同，使得我們無(wú)法直接快速的復(fù)制粘貼，有時(shí)在表格項(xiàng)目少的情況下，我們可以一個(gè)一個(gè)的單獨(dú)復(fù)制粘貼來(lái)完成，但是當(dāng)表格項(xiàng)目多的時(shí)候，復(fù)制粘貼會(huì)極大地降低工作的效率和數(shù)據(jù)的準(zhǔn)確性，此時(shí)我們可以使用VLOOKUP 函數(shù)公式進(jìn)行快速的填寫。

公式1：=VLOOKUP(查找值,數(shù)據(jù)表,序列數(shù),匹配條件)，如圖1

圖1 數(shù)據(jù)匹配函數(shù)應(yīng)用表

VLOOKUP 函數(shù)

在工作中被廣泛應(yīng)用，特別是在進(jìn)行數(shù)據(jù)匹配時(shí)，比如我們?cè)诎l(fā)放工資時(shí)，經(jīng)常會(huì)遇到銀行系統(tǒng)的導(dǎo)入模板表與工資表中職工姓名及金額，卡號(hào)等順序不同的情況，此時(shí)用該公式也會(huì)大幅提高數(shù)據(jù)錄入的速度與準(zhǔn)確性。當(dāng)數(shù)據(jù)進(jìn)行部分更新時(shí)，要想找出其中更新的部分，與其一個(gè)個(gè)比對(duì)，不如在舊數(shù)據(jù)旁邊直接用VLOOKUP 函數(shù)公式，重新匹配對(duì)應(yīng)的新數(shù)據(jù)，再用新數(shù)據(jù)減舊數(shù)據(jù)，不為零的就是更新的數(shù)據(jù)，類似的應(yīng)用場(chǎng)景比比皆是。

（二）ROUND 四舍五入函數(shù)

數(shù)據(jù)在進(jìn)行運(yùn)算時(shí)，我們通常會(huì)保留兩位小數(shù)，常用的做法是通過(guò)設(shè)置單元格格式，選擇數(shù)值項(xiàng)并保留兩位小數(shù)，但是，這種做法存在一個(gè)問(wèn)題，那就是雖然我們?cè)O(shè)置成兩位小數(shù)，但該數(shù)字在進(jìn)行計(jì)算時(shí)還是按照原來(lái)的多個(gè)小數(shù)計(jì)算，結(jié)果是造成最終合計(jì)數(shù)存在誤差。特別是在發(fā)放工資時(shí)，會(huì)使得實(shí)際發(fā)放金額的合計(jì)數(shù)與工資表的合計(jì)數(shù)不同，不得不面臨重新制表的尷尬局面。

公式2：=ROUND(數(shù)值，小數(shù)點(diǎn)后保留位數(shù))，如表1-表3

表1 原工資表

表2 設(shè)置單元格保留兩位小數(shù)后工資表

表3 使用round 公式后工資表

從以上三個(gè)表可以看出，設(shè)置單元格保留兩位小數(shù)的方法雖然達(dá)到了保留小數(shù)的目的，但卻在匯總求和時(shí)產(chǎn)生了誤差，造成這個(gè)誤差的原因其實(shí)還是小數(shù)點(diǎn)后幾位數(shù)要引起的，這也是僅僅通過(guò)單元格設(shè)置來(lái)保留小數(shù)的不足之處。而通過(guò)ROUND 函數(shù)公式計(jì)算出來(lái)的數(shù)值才是我們真正想要得到的，能更準(zhǔn)確地解決保留小數(shù)的問(wèn)題[1]。

公式1 和公式2 是我們?cè)谪?cái)務(wù)工作中使用非常多的兩個(gè)函數(shù)公式，實(shí)用性強(qiáng)，操作簡(jiǎn)單，應(yīng)用范圍廣。它能夠大幅提升工作的準(zhǔn)確性，避免了對(duì)數(shù)據(jù)的多次修改，提高工作效率。

二、Excel 數(shù)據(jù)透視表、數(shù)據(jù)透視圖

對(duì)財(cái)務(wù)人員來(lái)講，記賬只是基礎(chǔ)性工作，在掌握基礎(chǔ)工作的前提下，更重的就是對(duì)財(cái)務(wù)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析，通過(guò)橫向、縱向等多維度的分析評(píng)估項(xiàng)目的經(jīng)營(yíng)狀況，及時(shí)調(diào)整經(jīng)營(yíng)中存在的問(wèn)題。分析的基礎(chǔ)是對(duì)數(shù)據(jù)的統(tǒng)計(jì)，通過(guò)函數(shù)公式固然可以實(shí)現(xiàn)對(duì)數(shù)據(jù)的多種形式的統(tǒng)計(jì)，但函數(shù)的復(fù)雜多樣性使我們無(wú)法很快的掌握并應(yīng)用。因此如何快捷有效地對(duì)一組數(shù)據(jù)進(jìn)行不同角度的統(tǒng)計(jì)則顯得格外重要。

（一）數(shù)據(jù)透視表

數(shù)據(jù)透視表是一種交互式的表，可以根據(jù)自己的需要，動(dòng)態(tài)的改變他們的表格形式，以便按照不同的維度對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)。在項(xiàng)目上工作的這幾年，我越發(fā)覺(jué)得數(shù)據(jù)透視表在實(shí)際工作中的應(yīng)用的重要性，最突出的就是對(duì)職工工資的統(tǒng)計(jì)。

一般情況下，人資部或財(cái)務(wù)部會(huì)保留人資系統(tǒng)提供的職工月度工資表，該表能夠很好地反應(yīng)每一個(gè)職工當(dāng)月的收入明細(xì)，但是，隨著項(xiàng)目管理的需要，有時(shí)決策者會(huì)要求提供某部門或者某批次進(jìn)場(chǎng)人員的工資，比如要求提供參與項(xiàng)目科研活動(dòng)人員在研發(fā)期間的工資明細(xì)及匯總、在經(jīng)營(yíng)活動(dòng)分析中會(huì)要求提供近兩年新分學(xué)生的收入情況等等，此時(shí)，單純地依靠月度工資表中相關(guān)人員的加總，會(huì)使得統(tǒng)計(jì)工作量大幅增加，費(fèi)時(shí)費(fèi)力。然而數(shù)據(jù)透視表能很好地解決工作中這種突如其來(lái)的信息統(tǒng)計(jì)。只需要對(duì)每個(gè)月的工資表做簡(jiǎn)單的復(fù)制粘貼就可以輕松篩選所需人員的各種明細(xì)。

為了使制作的數(shù)據(jù)透視表能夠很好的反應(yīng)我們的需求，首要前提也即最重要的條件就是對(duì)基礎(chǔ)數(shù)據(jù)的處理，即數(shù)據(jù)源的處理，數(shù)據(jù)源的處理很大程度上決定了統(tǒng)計(jì)的維度，需要注意的問(wèn)題就是在選擇的數(shù)據(jù)源中不要出現(xiàn)空值或合并單元格，如表4，

表4 《工資表模板》

將每個(gè)月匯總成同一個(gè)表，注明所屬年份、月份及各個(gè)項(xiàng)目，注意表格需要套用Excel 表格格式，這樣的方便之處在于，每次添加的數(shù)據(jù)可以自動(dòng)匯總在數(shù)據(jù)透視表中。只需對(duì)數(shù)據(jù)透視表進(jìn)行刷新即可。

在插入數(shù)據(jù)透視表時(shí)，可以根據(jù)自己的需要選擇不同的統(tǒng)計(jì)維度，比如篩選某職工或某組職工的工資明細(xì)，可以選擇姓名為篩選項(xiàng)，年份及月份為行項(xiàng)，所需要匯總的工資構(gòu)成項(xiàng)為為值。也可以以時(shí)間為篩選項(xiàng)，姓名為行項(xiàng)等等。如圖2

除了人員工資統(tǒng)計(jì)外，還經(jīng)常會(huì)統(tǒng)計(jì)分析項(xiàng)目經(jīng)營(yíng)的成本，在建造合同中，成本項(xiàng)多由直接材料，機(jī)械使用費(fèi)，間接費(fèi)用等構(gòu)成，我們也可以通過(guò)數(shù)據(jù)透視表對(duì)這項(xiàng)項(xiàng)目進(jìn)行統(tǒng)計(jì)分析。如圖3，可以通過(guò)對(duì)字段的選擇，隨時(shí)統(tǒng)計(jì)各類成本的明細(xì)。

（二）數(shù)據(jù)透視圖

數(shù)據(jù)透視圖就是將數(shù)據(jù)透視表的數(shù)據(jù)轉(zhuǎn)化為圖的形式，即數(shù)據(jù)可視化，使得數(shù)據(jù)更直觀的展示出來(lái)。由其在財(cái)務(wù)分析報(bào)告上，數(shù)據(jù)透視圖的使用可以讓報(bào)告使用者對(duì)數(shù)的增減變動(dòng)一目了然，如圖4，通過(guò)調(diào)整左上角的年份按鈕可以分別看出2017 年和2018 年各季度直接材料和機(jī)械使用費(fèi)的增減變動(dòng)情況[2]

三、總結(jié)

數(shù)據(jù)透視表的功能不僅僅限于簡(jiǎn)單的匯總，還可以進(jìn)行數(shù)據(jù)的運(yùn)算，與數(shù)據(jù)透視圖的結(jié)合，使得數(shù)據(jù)統(tǒng)計(jì)更直觀，更易理解。此外，通過(guò)切片器，日程表，數(shù)據(jù)連接器等功能，可以使得圖與圖之間形成牽一發(fā)而動(dòng)全圖的聯(lián)動(dòng)效果。

Excel 功能極為強(qiáng)大，而我們?nèi)粘Ｓ玫街皇潜揭唤?，但僅僅是這冰山一角也有許多財(cái)務(wù)人員也無(wú)法熟練掌握，在工作中甚至還遇到完全不會(huì)Excel 辦公軟件的。因此，我認(rèn)為作為財(cái)務(wù)人員，除了扎實(shí)的專業(yè)知識(shí)以外，還應(yīng)該學(xué)習(xí)類似于Excel 這樣的辦公軟件，雖然有時(shí)候這不是必要的，但會(huì)使用能極大提高工作效率。

本文簡(jiǎn)單介紹了Excel 辦公軟件中非常實(shí)用的統(tǒng)計(jì)功能——數(shù)據(jù)透視表，目的是希望更多的財(cái)務(wù)人員能夠認(rèn)識(shí)它，了解它，能夠結(jié)合實(shí)際將它應(yīng)用到自己的工作中。此外還介紹了我在工作中經(jīng)用的兩個(gè)函數(shù)公式——VLOOKUP 和ROUND，在此分享給大家，因本人能力有限，不足的地方請(qǐng)大家批評(píng)指正。