Excel 函數、VBA 及數據透視表在勞動工資分類統計中的應用

2021-04-15 13:01:16唐惠立

統計科學與實踐 2021年8期

□ 唐惠立

使用sumproduct 函數進行多條件分類匯總

由于勞資工資報表涉及全部專業，為了更好地掌握各個行業的指標狀況，往往需要對數據進行分行業、分地區匯總。然而，常用的匯總函數如count，sum，sumifs 等卻無法滿足分類匯總的需求，因此，我們需要借助于數組函數sumproduct完成此項任務。

（一）sumproduct 函數使用方法簡介

sumproduct 函數返回相應范圍（range）或數組（array）內各個元素的累加之和。該函數運行的原理為：首先對傳入的參數數組中對應的元素進行指定的運算（默認為乘法），計算完畢后將形成一個與傳入參數相同維數的結果數組，最后對該結果數組中的各項求和返回最終計算結果。

sumproduct 函數在對應的元素之間進行的默認操作是乘法，但也可以執行加減除運算。其默認格式為：

=SUMPRODUCT (array1，[array2]，[array3]，...)

值得注意的是，傳入的參數需要都具有相同的維數。在執行其他運算時，需明確指定運算符號，將分隔數組參數的逗號替換為所需的算術運算符 (*、/、+、-)，如指定為加法時寫作：

=SUMPRODUCT (數組1+數組2+數組3)

代表的意思是將數組1、數組2、數組3 對應的元素相加后，再對結果進行累加。其計算過程可用圖1表示。

圖1

此外，sumproduct 函數還可以較簡單地添加分組條件，只需在數組參數后面加上必要的邏輯判斷語句即可，邏輯運算返回的結果是以FALSE、TRUE 組成的、與傳入的參數數組同維數的結果數組，此數組在參與四則運算時，Excel 會自動將FALSE、TRUE 轉換為0、1，如=SU MPRODUCT(({7,2,3,9}＞0)*{1,1,1,1}) 最終返回的結果為4。利用該特性，我們可以非常方便地進行分行業匯總。

第一步，準備行業門類的起止代碼和門類名稱。按圖2 方式在Excel表格中添加需要的門類信息。

圖2

第二步，對基礎表數據進行分類匯總。

假設基礎表202-1 表S 列為行業代碼所在列，K 列為我們需要匯總的工資總額數據列，則利用sumproduct函數，公式可編寫如下：

=SUMPRODUCT ((‘202-1 表數據’! $S $2 :$S $65535 ＞=’匯總表’!$A 8)*(‘202-1 表數據’! $S $2 :$S $65535 ＜=’匯總表’!$B 8)*(‘202-1表數據’! $K $2 :$K $65535))

公式解析：

(‘202-1 表數據’! $S $2 :$S$65535 ＞=’匯總表’! $A 8) *(‘202-1 表數據’! $S $2 :$S $65535＜=’匯總表’!$B8) 基礎表202-1 表S 列（行業代碼列）大于該門類所在的起始值（$A8 單元格），小于該門類所在的終止值（$B8 單元格）。此部分公式意在篩選出符合本行業門類的企業。

(‘202-1 表數據’! $K $2 :$K$65535) K 列為202-1 基礎表工資總額所在列，經過行業門類篩選的企業數據將與本列相乘，不符合本行業的企業數據全部自動計算為0，故最終返回結果為本行業門類的工資總額總和。

數據行標$K $2 :$K $65535 行標 65535 是一個習慣寫法，只要該值大于202-1 表導出數據里的最大行數即可。

根據此方法，以此類推，還可以對202-1 表里的其他指標，如從業人數、平均工資等指標進行分類匯總。

二、利用日期函數計算報表所在季度

由于勞動工資報表的報表期橫跨兩個季度，在進行數據對比分析時，無法簡單地通過系統記錄時間對數據進行分季度操作。如果手動添加報表所在的季度文字列，不僅操作繁瑣容易造成數據遺漏，且添加的文字信息不通用、難處理，二次利用十分繁瑣。

例如在分析跨年度數據時，如果采用手動添加所在季度的基礎數據，往往無法有效與Excel 內置的時間日期函數配合，造成不能滿足靈活分析的窘境。因此，我們可以靈活利用Excel 內置的日期函數，對勞動工資報表的“初次上報時間”進行轉換處理，從而可以在分析中較容易地對基礎數據進行分年度分季度的匯總。

根據勞動工資報表制度，季報的上報時間一般為季末月的27 日至次月的8 日左右截止，可見，上報時間的月份雖集中在2 個月份，但卻橫跨2 個季度，我們可以簡單地將“初次上報時間”月份減1，就可確保計算后的日期落在正確的季度內。因此，我們可以使用eomonth 函數完成此項工作，其語法如下：

EOMONTH(start_date,months)

圖3

圖4

該函數以start_date 指定的起始日期為基準，加減months 指定的月份數后，返回該日期所在月份的最后一天的日期值。

于是，我們可以新建一個“期別”列，用于存儲計算后的期別日期，如圖3 所示。

接下來我們可以借助其他函數計算該日期所在的季度，一種方法的公式如下：

=INT((MONTH(期別)+2)/3)

上述計算公式得出的結果即為所在季度數，如圖4。

另外也可以使用TEXT 函數生成以中文顯示的“第幾季度”的格式：

=TEXT(ROUNDUP(MONTH(期別)/3,),”[dbnum1]第0季度”)

綜合采用此種方法的優點是，得出的期別數據依舊保留了日期的格式，方便日后使用日期函數對其進行進一步分析利用，比如提取年份、使用數據透視表分析等操作。

利用VBA 自定義函數計算行業門類

在第一節的內容中，筆者介紹了使用固定行業分類和sumproduct函數的方法對數據進行分類匯總的操作，然而在其他的一些環境下，我們可能需要對單個企業進行行業門類的劃分，固定行業分類的方法顯然不能滿足這項要求。因此，為了簡單且快速地完成這項需求，我們可以借助VBA，編寫自定義函數，使用自定義函數對數據的行業代碼進行處理，從而計算出單個企業所在行業門類名稱。

在Excel 中依次點擊開發工具-Visual Basic，打開VBA 編輯器，點擊插入-模塊，新建一個名為Hydm2Name 的方法，該方法接收一個4 位國民經濟行業分類代碼的參數，以及一個可省略的用于控制返回格式的數字型參數，該函數的計算結果將返回相應的門類名稱。

完整的代碼如下：