原創作者： 盧子 轉自：Excel不加班

學員在制作收入儀表盤分析的時候，遇到了一個難題，要按照部門引用數據做成圖表。

這種屬于動态圖表的進階版，部門對應着多個值，比較麻煩。

今天，盧子分成基礎版、進階版兩部分說明。

1.基礎版

按照業務員動态引用數據，因為隻有唯一的對應值，很簡單。

Step 01 在空白的區域，用VLOOKUP函數将每個月的數據引用過來。

=VLOOKUP($A15,$B$2:$H$10,COLUMN(B1),0)

Step 02 再根據引用過來的數據，創建柱形圖即可。

Step 03 再自己調整大小和美化，現在選擇業務員，就可以動态獲取圖表。

2.進階版

每個部門都有N個業務員，人數又不一樣。難點在于要同時引用所有數據，引用後又能自适應行數，比如商務部就引用4行，招商部就引用5行。

于是，盧子想到了定義名稱法，公式有點小複雜。

Step 01 點公式→定義名稱，輸入名稱業務員，在引用位置将公式複制進去，确定。用同樣的方法，定義另外一個名稱。

合計：

=OFFSET(進階!$I$1,MATCH(進階!$A$15,進階!$A$2:$A$10,0),0,COUNTIF(進階!$A$2:$A$10,進階!$A$15))

業務員：

=OFFSET(進階!$B$1,MATCH(進階!$A$15,進階!$A$2:$A$10,0),0,COUNTIF(進階!$A$2:$A$10,進階!$A$15))

OFFSET函數語法：

=OFFSET(起點,向下幾行,向右幾列,多少行,多少列)

MATCH函數查找部門的首次位置，商務部為1，也就是向下1行，招商部為5，也就是向下5行。

COUNTIF函數統計部門的業務員有多少個，商務部為4，也就是4行，招商部為5，也就是5行。

而OFFSET函數得到的是一個動态區域，需要定義名稱才可以。

Step 02 按住Ctrl鍵，選擇業務員、合計的區域，點插入柱形圖。

Step 03 右鍵，選擇區域，編輯軸區域，改成=進階!業務員。

Step 04 編輯數據序列，改成=進階!合計。

Step 05 這樣就可以按部門獲取動态圖表。

動态圖表，核心部分是公式，公式學好了，其他都不是問題。

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部