高考今天結束,預祝考生們都能取得理想的成績~
今天繼續分享實用幹貨,涉及到透視表分組、Vlookup函數模糊匹配。
工作當中經常遇到需要将數據分組的情況,我們來看兩個經典案例。
案例需求案例一,如何統計出每個分數區間的人數?
思路是先判斷每個分數屬于哪個區間,再統計每個區間的人數,如果用IF函數多層嵌套的話,區間太多,公式太長,容易出錯,并不推薦。
案例二,如何确定每位員工的績效提成點?
思路同上,先判斷每位員工的業績屬于提成規則表中的哪個區間,再返回對應的提成點。
這類判斷數據分組的需求,我們首先想到的是IF函數:
=IF(業績>90000,12%,IF(業績>8000,11%,IF(業績>7000,10%,IF(....))))
案例二的問題,至少得嵌套6個IF函數(累死人),繁瑣且容易出錯,在區間多的情況下,基本不考慮IF函數。
今天我們介紹兩個快速簡單的方法,實現數據分組的問題。
數據透視表數據透視表的功能不可謂不強大,那麼數據透視表如何實現分組?
這裡我們以案例1為基礎。
首先選中數據源,插入數據透視表,将字段“分數”拖動到行區域,字段“姓名”拖動到值區域。
這樣,就可以統計數,每個分數,有多少個學生。
接着右鍵分組列,選擇【組合】,Excel會默認給出數字範圍的最小/最大值;
這裡我們根據需求調整最大最小值與步長,步長10,表示分數每隔10分為一組。
點擊【确定】之後,數據分組完成。
從這裡可以看出,數據透視表比較适合等步長數據的分組,也就是說透視表并不适合非等步長的分組;
比如将成績區間分到60-80,80-90,90-100,第一個分數區間60-80的步長是20,後面兩個分數區間的步長是10,前後步長不一緻,透視表無法實現。
不等步長的問題,我們可以利用第二種方法實現。
Vlookup函數模糊匹配Vlookup函數在Excel中的地位,堪比數據透視表,數據匹配神器。
但在日常工作中,我們基本隻用到它的精準匹配模式,也就是最後一個參數是FALSE(0);
它還有種模式,模糊匹配,最後一個參數是TRUE(1)。
我們以案例二為演示基礎。
首先利用快速填充CTRL E,提取出提成規則表中的數字,記得放在列“提成點”的前面。
接着,在C2單元格内輸入公式:
=VLOOKUP(B2,E:G,3,1)
公式下拉,完成提成點的匹配。
這是什麼原理呢?
vlookup函數的第四個參數等于TRUE(0)的時候,表示模糊匹配。
當在查找B2單元格(14000)的時候,Vlookup會從匹配區域的首列,也就是E列,從上到下查找,當查找到第一個比它大的值時,停止查找,返回停止查找的上一個值;
先查找14000,在E列從上到下查找,當找到30000的時候,30000大于14000,停止查找,返回30000上面的數字10000,結合vlookup函數參數三(等于3),返回匹配區域的第三列,所以函數最終返回G2單元格,3%,返回正确結果。
前提是一定要對匹配區域的首列進行升序。
也就是說E列需要升序操作,我們來看個反例。
下圖中E列處于無序狀态,正常狀态下趙雲的業績是34000,返回的提成點應該是5%,但最終結果卻是3%。
這是因為模糊匹配從上向下查找,查找到50000的時候,50000大于34000,返回50000上面的一個值10000,再結合參數三,返回3%。
所以,一定要記得将E列升序操作,Vlookup函數模糊匹配才會返回正确的結果!
小結Excel數據透視表分組 Vlookup函數模糊匹配,分享給大家,希望對你有所幫助。
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!