□ 唐惠立

使用sumproduct 函數(shù)進(jìn)行多條件分類匯總

由于勞資工資報(bào)表涉及全部專業(yè)，為了更好地掌握各個(gè)行業(yè)的指標(biāo)狀況，往往需要對(duì)數(shù)據(jù)進(jìn)行分行業(yè)、分地區(qū)匯總。然而，常用的匯總函數(shù)如count，sum，sumifs 等卻無法滿足分類匯總的需求，因此，我們需要借助于數(shù)組函數(shù)sumproduct完成此項(xiàng)任務(wù)。

（一）sumproduct 函數(shù)使用方法簡(jiǎn)介

sumproduct 函數(shù)返回相應(yīng)范圍（range）或數(shù)組（array）內(nèi)各個(gè)元素的累加之和。該函數(shù)運(yùn)行的原理為：首先對(duì)傳入的參數(shù)數(shù)組中對(duì)應(yīng)的元素進(jìn)行指定的運(yùn)算（默認(rèn)為乘法），計(jì)算完畢后將形成一個(gè)與傳入?yún)?shù)相同維數(shù)的結(jié)果數(shù)組，最后對(duì)該結(jié)果數(shù)組中的各項(xiàng)求和返回最終計(jì)算結(jié)果。

sumproduct 函數(shù)在對(duì)應(yīng)的元素之間進(jìn)行的默認(rèn)操作是乘法，但也可以執(zhí)行加減除運(yùn)算。其默認(rèn)格式為：

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

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

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

代表的意思是將數(shù)組1、數(shù)組2、數(shù)組3 對(duì)應(yīng)的元素相加后，再對(duì)結(jié)果進(jìn)行累加。其計(jì)算過程可用圖1表示。

圖1

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

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

圖2

第二步，對(duì)基礎(chǔ)表數(shù)據(jù)進(jìn)行分類匯總。

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

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

公式解析：

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

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

數(shù)據(jù)行標(biāo)$K $2 :$K $65535 行標(biāo) 65535 是一個(gè)習(xí)慣寫法，只要該值大于202-1 表導(dǎo)出數(shù)據(jù)里的最大行數(shù)即可。

根據(jù)此方法，以此類推，還可以對(duì)202-1 表里的其他指標(biāo)，如從業(yè)人數(shù)、平均工資等指標(biāo)進(jìn)行分類匯總。

二、利用日期函數(shù)計(jì)算報(bào)表所在季度

由于勞動(dòng)工資報(bào)表的報(bào)表期橫跨兩個(gè)季度，在進(jìn)行數(shù)據(jù)對(duì)比分析時(shí)，無法簡(jiǎn)單地通過系統(tǒng)記錄時(shí)間對(duì)數(shù)據(jù)進(jìn)行分季度操作。如果手動(dòng)添加報(bào)表所在的季度文字列，不僅操作繁瑣容易造成數(shù)據(jù)遺漏，且添加的文字信息不通用、難處理，二次利用十分繁瑣。

例如在分析跨年度數(shù)據(jù)時(shí)，如果采用手動(dòng)添加所在季度的基礎(chǔ)數(shù)據(jù)，往往無法有效與Excel 內(nèi)置的時(shí)間日期函數(shù)配合，造成不能滿足靈活分析的窘境。因此，我們可以靈活利用Excel 內(nèi)置的日期函數(shù)，對(duì)勞動(dòng)工資報(bào)表的“初次上報(bào)時(shí)間”進(jìn)行轉(zhuǎn)換處理，從而可以在分析中較容易地對(duì)基礎(chǔ)數(shù)據(jù)進(jìn)行分年度分季度的匯總。

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

EOMONTH(start_date,months)

圖3

圖4

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

于是，我們可以新建一個(gè)“期別”列，用于存儲(chǔ)計(jì)算后的期別日期，如圖3 所示。

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

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

上述計(jì)算公式得出的結(jié)果即為所在季度數(shù)，如圖4。

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

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

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

利用VBA 自定義函數(shù)計(jì)算行業(yè)門類

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

在Excel 中依次點(diǎn)擊開發(fā)工具-Visual Basic，打開VBA 編輯器，點(diǎn)擊插入-模塊，新建一個(gè)名為Hydm2Name 的方法，該方法接收一個(gè)4 位國(guó)民經(jīng)濟(jì)行業(yè)分類代碼的參數(shù)，以及一個(gè)可省略的用于控制返回格式的數(shù)字型參數(shù)，該函數(shù)的計(jì)算結(jié)果將返回相應(yīng)的門類名稱。

完整的代碼如下：

保存以上代碼，可到Excel 里進(jìn)行使用，如圖5。

圖5

圖6

圖7

這里列出了該方法的3 個(gè)用法，可根據(jù)自身需要靈活使用。AH 列使用默認(rèn)參數(shù)，返回值為門類代碼和門類名稱；AI 列使用返回值控制參數(shù)3，僅返回門類代碼；AJ 列使用返回值控制參數(shù)2，返回門類名稱。

根據(jù)以上自定義函數(shù)返回的門類信息，后續(xù)我們?cè)賹?duì)企業(yè)進(jìn)行分行業(yè)匯總將變得十分方便。

利用數(shù)據(jù)透視表進(jìn)行快速匯總和結(jié)果分析

經(jīng)過上述3 個(gè)步驟的處理，我們現(xiàn)在可以很方便地針對(duì)基礎(chǔ)數(shù)據(jù)做進(jìn)一步的處理和分析。這里筆者簡(jiǎn)要介紹一下利用Excel 數(shù)據(jù)透視表分析數(shù)據(jù)的方法。

首先在導(dǎo)出的202-1 基層表里點(diǎn)擊任一個(gè)單元格，點(diǎn)擊插入-數(shù)據(jù)透視表，在新建的透視表中，通過拖拽方式添加想要的指標(biāo)和分組類型如圖6。

由于勞動(dòng)工資報(bào)表是季報(bào)，所以需要在列字段里的期別指標(biāo)上右擊-創(chuàng)建組，選擇年和季度，如圖7。

這樣就可以得到想要的分析匯總表，如圖8。

圖8

使用此方法可以非常方便地對(duì)比跨期別、跨年度的數(shù)據(jù)，極大地方便了統(tǒng)計(jì)人員對(duì)數(shù)據(jù)進(jìn)行對(duì)比分析的操作。