Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

  文/無訟學院 田臻奕

大家好,我是小田,新春愉快!

今天是無訟學院周日專欄在新年裡第一次和大家見面,今年,我們將繼續在周日為大家奉上乾貨文章、精彩講座和培訓課程,也將努力讓有價值的思想更好地傳播,期待大家的關注。

去年年底一個偶然的機會,我與一位青年律師在一起辦公。當時正值各個律所進行年終總結,這位律師承擔了統計全所業務數據的工作。擺在他面前的,是這樣一張表格:

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

表格共有一千餘條數據,包含律所內各個業務團隊全年接觸的案件名稱,該案是否委托該所代理,若未委托,未委托的原因為何,若已委托,案件在哪一級法院審理,最終的審理結果怎樣等多個維度的數據。而這位律師的工作,就是統計出全所全年接觸的案件數量、委托比例、各種未委托原因所占比例、全所承辦的案件在各級法院的分布情況、各級法院的勝訴率、及全所案件的勝訴率等。

我觀察了一下,發現,例如統計『各類未委托原因所占比例』這一數據,他的操作步驟為:

1.設置篩選,篩選出『是否委托』一欄為『否』的數據

2.數出數據數量,並記錄下來

3.再次篩選,篩選出『未委托原因』一欄為『有利沖』的數據

4.數出數據數量,並記錄下來

5.再次篩選,篩選出『未委托原因』一欄為『無意委托』的數據

6.數出數據數量,並記錄下來

7.再次篩選,篩選出『未委托原因』一欄為『費用不符合預期』的數據

8.數出數據數量,並記錄下來

9.再次篩選,篩選出『未委托原因』一欄為『不屬於受案範圍』的數據

10.數出數據數量,並記錄下來

僅僅統計這一條數據,就要進行四次篩選,數數據數量的工作。記錄下所有數據之後,再復核總量,確認無誤後製作餅圖。用這樣的方法,完成統計所內及各個律師團隊的各項業務指標、製作圖表和PPT,通常需要他在工作之餘花費一周左右時間加班完成。

我們再回顧一下這個場景,不難發現,讓這件事情費時費力的主要原因在於『多次篩選』和『計數』。在我們日常統計數據時,經常會遇到需要先進行篩選,再統計篩選結果的情況,當篩選的維度和選項增多時,一次一次篩出之後『數』,無疑是非常困難的。

所以今天,我要和大家分享一個Excel的進階功能,數據透視表。通俗地說,數據透視表,就是對不同維度、種類的數據根據需求進行求和、計數等操作的表格。下面我們用數據透視表,再來統計一下『各類未委托原因所占比例』這一數據。

首先,我們選中已經整理好的數據表,點擊插入,選擇『數據透視表』按鈕,並在彈出的對話框中點擊『確定』。

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

確定後我們就會跳轉到數據透視表的操作界面,如下圖所示:

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

工作簿中出現了一個叫『數據透視表生成器』的對話框,其中包含五個組成部分,分別為字段名稱、篩選器、列、行和值。在字段名稱一欄,顯示的是我們剛剛選中,用來生成數據透視表的源數據的各個維度。

要統計各個團隊及整個律所的未委托案件情況,我們需要的字段都有哪些呢?

1.『團隊名稱』,用於區分不同團隊

2.『是否委托』中『否』選項數據

3.『未委托原因』,並統計出各類未委托原因的數量

首先,我們在字段名稱欄中,選擇『團隊名稱』,並放入生成器中『行』一欄的方框中。

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

三個律師團隊的名稱就分三行出現在了圖表當中,接下來,我們再將『是否委托』一欄拖進『篩選器』的方框當中,將『未委托原因』拖入『列』的方框當中。

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

在圖表的第一行,多了一個帶有下拉菜單的『是否委托』選項,而在生成的圖表中,又出現了『/』、『不屬於受案範圍』、『費用不符合預期』、『無意委托』、『有利沖』五列。接下來我們需要統計數量了,我們再將『未委托原因』一欄,從字段名稱下面的列表中,拖入『值』所在的方框內。我們就得到了一張如下圖所示的透視表:

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

從這張表中,我們可以清楚地看到,李律師團隊共有案件9個,其中委托代理5個,不屬於受案範圍1個,費用不符合預期1個,無意委托2個,沒有利沖案件。而全所共計33個案件,成功委托19個,未委托14個,其中不屬於受案範圍2個,費用不符合預期3個,無意委托6個,利沖案件3個。之前需要依次篩選計數的數據,只需要幾下拖動,就被自動統計出來了。

數據透視表的另一個方便之處在於,我們可以依據自己的需求,自由地布置表格的行、列和字段分布。依舊是剛剛演示的這張圖,如果我們把『團隊名稱』和『未委托原因』兩個字段的行和列調換一下,就會生成下圖所示的表格了:

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

如果再將『審級』這一字段加入透視表的『行』的方框當中,就會生成下面這張表格:

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

我們可以清楚地看到,李律師團隊承辦的5個案件中,2個案件在高院,2個在基層法院,1個在中院。如果我們不再區分團隊,想看全所全年案件的審級情況,只需要將『團隊名稱』的字段從『行』的方框中刪掉(滑鼠在『行』所在方框中右單擊『團隊名稱』,選擇『刪除』)即可。

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

我們再來看一個數據透視表處理複雜數據的例子:

想必許多律師都遇見過這樣的情景,當事人拿來了一大堆與案件相關的交易憑證,但自己又說不清楚具體情況,期望律師給出專業的意見和建議。這個時候,整理數據,分析事實,是給出法律意見的前提條件。我們以下面這個案件為例:

在一起案件中共有五方當事人,彼此之間曾多次訂立合同,買賣同一種化工產品,留有多份交易單據。經過登記和整理,基本情況如下:

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

乍一看這些數據非常雜亂,五個公司都各自作為賣方賣出過貨品,也作為買方買入過貨品,每次買入的數量和金額都不相同。如果不進行進一步的數據處理,很難對案件進行下一步的分析。

這份表格共有六列,我們從第一列『時間』看起。目前的數據表中,時間前後混雜,我們可以先利用Excel的排序功能,將各次交易依照時間的順序進行排序。選中時間所在的列,再在工具欄中,單擊『排序與篩選』按鈕,選擇按升序排列。

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

此時會彈出一個對話框,提示是否要擴展排序區域,在這裡需要擴展,否則將只有第一列的日期數據被排序,後面的數據不會跟著動,整張表的數據就會出現錯誤。

我們再觀察一下這份數據表,五個公司都分別作為買方訂立過合同,也作為賣方訂立過合同,無論這個案子後續情況如何,將各個主體買入和賣出的總數量、總支出、總收入統計出來,都是必須要進行的工作。先分五次篩選出五個公司作為買方的交易,分別統計數量和金額,再分五次篩選出其作為賣方的交易,再統計數量和金額顯然是非常麻煩的。我們就用剛剛學習過的數據透視表,快速地分析一下這組數據。

同樣地,我們先選中這張表格,插入一個數據透視表。

我們要分析的幾個字段分別為賣方、賣方、數量和金額。其中數量和金額應當進行求和。在透視表生成器中,將這幾個字段分別拖入『行』和『列』下面的方框中。

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

我們立刻就能看出,雖然他們訂立了多份合同,但是兩方主體之間交易的化工產品的總量都是相同的,為5253噸。即海油公司賣給三零公司5253噸,華夏公司賣給南方公司的也是5253噸。海油公司作為買方,買入化工品時,支出了4173萬元,而作為賣方賣出同量化工品時,收入了4600萬元。

我們再將五個主體買賣產品時的收支情況單獨整理出來:

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

在收入與支出下方,再計算一下每個公司收入與支出之間的金額之差。在此前的文章中,我已經和大家分享過了公式的基本應用方法,我們點擊需要顯示計算結果的單元格,輸入『=』,再單擊上方顯示收入金額數字的單元格,輸入『-』,再單擊顯示支出金額數字的單元格,即可輸入如下圖所示的公式:

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

點擊回車得出結果。我們再使用拉動填充功能,將五個公司的數據都計算出來。將滑鼠停留在我們寫出公式的單元格右下角,在光標變為實心十字的時候向右拖動,即可填充完畢。

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

通過整理數據我們發現,這五方主體的彼此買賣,形成了一個交易閉環。其中匯首公司低買高賣,多支出了2000餘萬元,而三零公司高賣低買,賺取了1500餘萬元。其他三個公司在倒手的過程中賺取了少量利息。我們再用一個簡單的圖表表示一下他們之間的交易情況,一個循環貿易案件的基本框架就非常清楚了。

Excel只會篩選篩選再篩選?教你一招搞定案件複雜數據統計 | 無訟學院

總結一下,當我們需要對多個維度、多個選項的數據進行篩選和統計時,就可以使用數據透視表,只要將各個字段的數據在『行』和『列』進行合理的布局,幾分鐘就可以得到統計結果,再也不用挨個篩選再求和或計數啦。