秒殺Excel數(shù)據(jù)透視表! excel新函數(shù)GROUPBY真強(qiáng)大

今天跟大家分享一個(gè)非常強(qiáng)大的新函數(shù)——GROUPBY函數(shù),它不但能對(duì)數(shù)據(jù)分類匯總,還能進(jìn)行排序與篩選,功能非常的強(qiáng)大,下面我們就來一起學(xué)習(xí)下!
一、GROUPBY函數(shù)
GROUPBY:根據(jù)指定的字段對(duì)數(shù)據(jù)進(jìn)行分組、聚合、排序和篩選
語法:=GROUPBY (row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])
- 參數(shù)1:對(duì)那一列進(jìn)行分組統(tǒng)計(jì)
- 參數(shù)2:對(duì)那一列進(jìn)行計(jì)算
- 參數(shù)3:分類匯總的計(jì)算方式是什么
- 參數(shù)4:數(shù)據(jù)是否包含表頭,需要顯示嗎
- 參數(shù)5:總計(jì)需要顯示嗎,以及顯示的方式
- 參數(shù)6:是否需要排序
- 參數(shù)7:是否需要對(duì)結(jié)果進(jìn)行篩選
這個(gè)函數(shù)參數(shù)一共有7個(gè),雖然看起來多,但是也并不算復(fù)雜,只有第一到第三參數(shù)是必選參數(shù),其余的參數(shù)都是可以省略的。下面我們就來舉幾個(gè)例子簡(jiǎn)單的學(xué)習(xí)下這個(gè)函數(shù)
一、分類匯總
=GROUPBY(A1:A10,E1:E10,SUM)
- 參數(shù)1:A1:A10 采購方式所在的列
- 參數(shù)2:E1:E10 采購總價(jià)所在的列
- 參數(shù)3:SUM表示求和
第三參數(shù)是有很多匯總方式的,詳見下圖,可以選擇自己需要的,這個(gè)就是它的基本用法
二、多層匯總
公式:=GROUPBY(A1:B10,D1:E10,AVERAGE)
這個(gè)函數(shù)不是僅僅只能對(duì)一列進(jìn)行分類匯總,還能設(shè)置多個(gè)匯總字段。
下圖就是根據(jù)采購方式以及商品名稱的數(shù)據(jù)用戶金額的平均值
其實(shí)在這里我們可以簡(jiǎn)單的把第一參數(shù)看作是透視表的行區(qū)域,第二參數(shù)看做是透視表的值區(qū)域。第三參數(shù)說計(jì)算方式。
三、是否包含表頭
上面的案例中都有沒有表頭的,如果你想顯示表頭,就需要設(shè)置第四參數(shù),輸入對(duì)應(yīng)的代碼即可
- 0:數(shù)據(jù)源中沒有表頭
- 1:數(shù)據(jù)源中有表頭但是不顯示
- 2:數(shù)據(jù)源中沒有表頭,但是需要生成1個(gè)新的表頭
- 3:數(shù)據(jù)源中有表頭,并且顯示表頭
在這個(gè)參數(shù)中0,1,3都比較容易理解,關(guān)鍵是參數(shù)2,如果數(shù)據(jù)源中沒有表頭,就會(huì)生成新的表頭,以字段1,值1這樣的命名方式來添加新表頭,這個(gè)表頭也是無法修改的,效果如下圖所示
四、是否需要顯示總計(jì)與小計(jì)
GROUPBY不但能顯示總計(jì)還能顯示小計(jì),就需要設(shè)置第五參數(shù),也是需要輸入對(duì)應(yīng)的代碼來設(shè)置小計(jì)與總計(jì)
- 0:沒有總計(jì)
- 1:顯示總計(jì)
- 2:顯示總計(jì)與小計(jì)
- -1:在頂部顯示總計(jì)
- -2:在頂部顯示總計(jì)與小計(jì)
具體的效果如下圖所示,關(guān)鍵點(diǎn)需要明白,如果你想顯示小計(jì),第一參數(shù)至少需要設(shè)置2列數(shù)據(jù),否則的話就會(huì)顯示為錯(cuò)誤值 -1與-2是將小計(jì)與總計(jì)放在表格上方的就不再演示了,大家可以操作這試一下
五、是否需要排序
在這里正數(shù)表示升序,負(fù)數(shù)表示降序,只能根據(jù)1列數(shù)據(jù)來做升序或者降序的排序。排序列數(shù)是根據(jù)函數(shù)的結(jié)果來指定的
如下圖,將第六參數(shù)設(shè)置為2,就表示根據(jù)結(jié)果表的第2列進(jìn)行升序或者降序的排序
六、是否需要進(jìn)行篩選
比如現(xiàn)在我們不需要APP的數(shù)據(jù),就可以考慮將其篩選掉。就需要設(shè)置第七參數(shù)
公式:=GROUPBY(A1:A10,E1:E10,SUM,3,1,2,A1:A10<>"APP")
以上就是GROUPBY函數(shù)的使用方法,它更像是一個(gè)縫合怪,把subtoatl,sort,filter縫合在一起功能更加強(qiáng)大,大家可以試著操作下
相關(guān)文章
合同時(shí)間到期自動(dòng)提醒怎么實(shí)現(xiàn)? excel中Today函數(shù)做倒計(jì)時(shí)的技巧
公司人很多,經(jīng)常有合同到期續(xù)簽問題,我們需要隨時(shí)了解當(dāng)前時(shí)間哪些合同是屬于接近到期或者是已經(jīng)到期,以便我們及時(shí)進(jìn)行客戶跟進(jìn),下面我們就來看看excel做到期提醒的方2024-11-19excel mod函數(shù)什么意思? excel表格中mod函數(shù)的用法
使用 Excel2019 軟件編輯表格時(shí),可以使用 MOD 函數(shù)計(jì)算并返回兩數(shù)相除的余數(shù),今天小編就來說明一下具體步驟2024-06-11excel表格iferror函數(shù)怎么用? Excel中IFERROR函數(shù)典型用法
excel中可以使用 IFERROR 函數(shù)捕獲和處理公式中的錯(cuò)誤,該怎么使用IFERROR函數(shù)呢?詳細(xì)請(qǐng)看下文介紹2024-06-11計(jì)算優(yōu)秀率用哪個(gè)函數(shù)? 計(jì)算優(yōu)秀率的excel函數(shù)公式
在Excel中,計(jì)算優(yōu)秀率需要使用COUNTIF和COUNTA函數(shù),優(yōu)秀率計(jì)算需先設(shè)定優(yōu)秀標(biāo)準(zhǔn),再計(jì)算滿足標(biāo)準(zhǔn)的人數(shù)和總?cè)藬?shù),最后相除,詳細(xì)請(qǐng)看下文介紹2024-05-11lambda函數(shù)有什么作用? Excel中LAMBDA函數(shù)使用詳解和相關(guān)案例
剛剛我們介紹了LAMBDA函數(shù),上篇介紹的不是很詳細(xì),這篇文章我們從語法,使用方法結(jié)合實(shí)際案例來介紹LAMBDA函數(shù)在excel中的用法2024-04-16lambda函數(shù)怎么用? Excel使用LAMBDA函數(shù)來自定義函數(shù)的教程
LAMBDA允許用戶定義自己的函數(shù),并在工作表中使用,通過 LAMBDA,我們可以將復(fù)雜的計(jì)算邏輯轉(zhuǎn)換成簡(jiǎn)單的公式,比如英文函數(shù)轉(zhuǎn)換為中文,讓我們的工作表更容易解讀,操作也2024-04-16excel怎么利用SCAN函數(shù)在無輔助列情況下處理合并單元格查詢難題?
利用SCAN函數(shù),可以針對(duì)取消合并單元格后出現(xiàn)的空值進(jìn)行迭代處理,今天我們就來用實(shí)例演示在無輔助列情況下處理合并單元格查詢難題的過程2024-04-16SCAN函數(shù)怎么用? Excel中利用SCAN函數(shù)查找合并單元格的技巧
excel中合并單元格是常有的是,方法也很多,今天我們可以使用SCAN 函數(shù)快速合并單元格,詳細(xì)請(qǐng)看下文介紹2024-04-16excel比例函數(shù)是什么? Excel比例函數(shù)公式顯示幾比幾的技巧
在Excel中,比例函數(shù)是一種用于計(jì)算兩個(gè)數(shù)值之間的比例關(guān)系的公式,使用比例函數(shù),可以快速地計(jì)算出兩個(gè)數(shù)值之間的比例,并可以以百分比的形式顯示結(jié)果,詳細(xì)請(qǐng)看下文介紹2024-03-21常用的excel函數(shù)類別有哪些? Excel十三大類函數(shù)公式大全
excel中很多函數(shù)經(jīng)常使用,函數(shù)可以分為13類,大概有505個(gè)函數(shù),我們分別從13大類中調(diào)幾個(gè)常用的函數(shù)來介紹,詳細(xì)請(qǐng)看下文介紹2024-03-20