資料看板是什麼意思
回覆2016獲得office2016的下載連結
回覆2021獲得office2021的安裝攻略
前幾天有群友求助,說自己搞的年度預算執行表感覺不太好,想問問有什麼好的呈現方式,表格的樣子就是這樣的:
相信這樣的表格是大家平時最常見的,這種表結構就是我們平常說的二維表。
大致來說橫向是六個部門:
縱向是費用科目,得有將近四十個細項:
如果部門再多一些,這個表格的視覺效果就更差了。
回到一開始的問題,對於這樣的資料表怎麼排版效果更好看?
這裡涉及到一個標準的問題,如何定義好看和不好看,這是非常主觀的,一個人一個審美標準,因此怎麼排版好看可能沒有統一的標準。
但是做一些最佳化是沒問題的,對於這類表格來說,資料量太多,無法突出重點,無法一眼總覽全部的資料,這些都是可以最佳化的方向,今天就給大家介紹一個用控制元件來最佳化資料表的思路,先來看看效果。
說白了,就是透過點選選項按鈕來切換部門,而下面的資料會隨著變成對應部門的資料。
這樣最佳化後的好處是可以一眼就掌握每個部門的資料,避免了資料量太多而無法總覽全域性的弊端。
另外使用控制元件進行切換,看上去也比較高大上一點。至於其他方面的美化,本文不涉及,可以根據個人喜好去設定。
下面就來介紹一下這個看板的製作步驟,大體上來說分成三步。
資料結構設計、控制元件設計、公式填充。
第一步:資料結構設計思路
資料的呈現主體是費用型別和科目,從資料來源中可以瞭解到,一共涉及到39個科目。
所以考慮在看板中分成三欄進行呈現,每一欄13個專案,這就需要對費用型別重新組合,以滿足這種設計要求。
初步設計的效果是這樣的,這個純粹是手工活,過程就不細說了,在這一步可以根據自己的審美做一下美化,也不贅述了。
第二步:控制元件設計
本例中我們使用表單控制元件中的選項按鈕控制元件。
新增控制元件的方法非常簡單,選擇控制元件後在表格裡拖出一個矩形框就可以了。
控制元件是切換部門用的,一共有六個部門,所以需要插入六個控制元件,並且按照控制元件按鈕的新增順序以此命名為對應的部門名稱。
在控制元件上右鍵單擊就可以編輯文字。
然後將六個控制元件拖動擺放整齊即可。
繼續右鍵,點選設定控制元件格式。
只需要設定單元格連結,選中表格空白區域的任意單元格即可,另外在旁邊將部門名稱以此填入單元格,這都是為下一步設計公式做的準備工作。
選擇不同的控制元件,
這個單元格的數字會變
,這就是控制元件最核心的秘密了。
邊上的部門列表只是為了便於識別數字是否和選定部門的順序一致,並沒有太多的實際作用。
第三步:公式設計
設計公式需要先對資料來源做一下處理,原有的資料來源存在大量的合併單元格,這對使用公式是非常不方便的,另外考慮到公式引用的是同一個部門的不同型別的費用,所以對原資料進行轉置貼上,並且填充了合併單元格形成的空白,就得到了一個這樣的資料來源。
嚴格來說這個資料來源並不完美,因為還是一個二維表,完美的資料來源是一維表。
關於這方面的知識,可以看一下之前的教程:
【Excel基礎知識】關於Excel二維表和一維表的那點事,今天一次說明白……
要在一個二維表匹配資料,比較常用的是INDEX-MATCH組合,這裡直接給出公式:
年度預算的公式:
=INDEX(Sheet1!$C$3:$AP$16,Sheet2!$S$1*2+MATCH(Sheet2!D$2,Sheet1!$B$3:$B$4,),MATCH(Sheet2!C3,Sheet1!$C$2:$AP$2,))
← 左右滑動檢視完整公式 →
費用累計的公式:
=INDEX(Sheet1!$C$3:$AP$16,Sheet2!$S$1*2+MATCH(Sheet2!E$2,Sheet1!$B$3:$B$4,),MATCH(Sheet2!C3,Sheet1!$C$2:$AP$2,))
← 左右滑動檢視完整公式 →
完成後再將第一欄兩列的公式複製到後面兩欄就完成了公式的設計。
這個公式看起來挺長,原理並不複雜,搞清楚INDEX和MATCH函式的組合用法,再把例子中的資料結構關係弄清楚就理解了。
甚至還有其他的一些公式都可以達到同樣的效果,在這就不囉嗦了。
到這一步,這個看板已經基本搞定了,最後在選項裡設定兩個地方進行最佳化。
在選項的高階裡,將這兩個√去掉,這個看板就完成了。