表要怎麼做

作者 l Skye

來自優秀學員投稿

Skye是我Excel進階訓練營裡的優秀學員。

第1期訓練營的時候，Skye和其他的Excel小白一樣，提個問題能把我氣死：

1- 有高手在嗎？怎麼快速提取出一天最早和最晚的時間呢？

2- 我要是自己去百度，還要老師幹嘛？

3- 誰能給我個Excel安裝包？

經過了2期Excel進階訓練營，無微不至的「蹂躪」，你可能想象不到，這個差點把我氣死的學員，開始在公司內部組織培訓了。

從簡單入門的快捷鍵，高效操作的小技巧，到表格設計思維和方法，Skye成為了一個名副其實的Excel高手。

不囉嗦了，說多了，你可能覺得我為學員自吹自擂，以下是Skye姐的投稿，大家一起來感受一下吧。

同事靜靜跑來說：

skye姐，我媽說現在都有年度賬單，要我也每天記賬，年底要交年度賬單給她。

我媽給了我一張表讓我記錄自己的日常開支，可是我覺得用起來特別不方便，你給看看。

接著靜靜開始抱怨起來：

你看我，要查詢一筆記錄久了查詢起來很費時間

每天的收支用計算器算好還要驗算幾遍就怕出錯不好找

要是哪天收入支出專案一多，格子都不夠寫

skye姐你快幫我想想辦法吧。

靜靜的抱怨，你看著肯定很眼熟吧？在設計表格的時候，只想著如何看著更直觀，完全沒有考慮到後面的查詢和統計。

就像上面這個門鎖，縱使我們設定了兩個不同型別的鎖，如果一開始「沒有搞清楚」門的開啟方式，再多門鎖也是徒勞。

表格的設計一定要從全域性考慮，保證下面3點，才能做出好用的表格：

1- 記錄表，方便記錄的資料表

2- 查詢表，可以方便的查詢不同狀態的資料

3- 統計表，可以快速的根據各種不同口徑，統計資料

2-分鐘後，Skye姐把設計好的收支表格發給了靜靜。

正如你所看到的，設計後的表格，只需要依次新增資料，然後再右邊重新整理一下，就可以快速更新統計結果。

- 資料記錄起來更方便，不用根據日期去找填寫位置。

- 資料統計起來更高效，只要重新整理一下就可以了

表格的設計過程，主要包含兩個部分：

收支明細表（資料來源）：

記錄每一筆收支，遺漏的專案繼續新增即可。透過篩選可以方便的進行單筆查詢，透過資料有效性，規範資料輸入，避免誤操作造成資料統計誤差

彙總表（資料彙總）：

彙總各類收支，透過條件格式，收支為負值會示警

具體怎麼實現的呢？

Skye姐把製作過程，逐字逐句的講給了靜靜。

01 | 分析需求

Excel設計表格，資料來源使用一維表會更方便函式和資料透視表的使用

根據靜靜的需求，需要以下2類資料

1- 收支明細（資料來源）：日期、專案、收入支出金額

2- 彙總（資料透視表）：總收入、總支出、收支合計

02 | 製作步驟

第1步，設計表頭

收支明細和彙總好比是食材和菜品的關係，收支明細的食材是

「日期」「收支專案」「收支金額」，再新增一個「備註欄」可以對收支做一些說明，查詢的時候也方便回憶。

設計好的表頭是這樣的：

第2步，輸入資料

透過1，2這2個步驟，明細表就完成了

這樣在新增資料的時候，只需要在最後一樣下面，逐一的新增資料就可以了，不用再根據日期，查詢填寫的位置了。

第3步，彙總（資料透視表）

之所以要把資料記錄的表格，設計成從上往下新增的資料結構，實際上是在為統計資料做準備。

因為Excel中有一個高效的資料統計神器：資料透視表。

使用透視表，整個統計過程，不費吹灰之力就可以輕鬆完成，大致的步驟是這樣的：

1- 選中資料來源-【插入】-【資料透視表】。

2- 設定行欄位【專案】列欄位「收支合計」「收入」「支出」，總計行輸出所需總收入、總支出、收支合計。

只是滑鼠拖一拖、拽一拽就完成了資料統計。

整個設計和統計過程，10分鐘都不到，靜靜看的目瞪口呆

靜靜：Skye姐，你太厲害了，還有還有。。。

- 這怎麼彙總裡有餐費 午餐 晚餐……

- 香港預訂酒店也可以歸類到旅行吧……

- 自學這筆支出跑到收入裡了……

- 還有還有，能不能看出每筆支出後的餘額？

- 每次新增收支，彙總表重新整理不出來？

Skye姐：咦，這麼多問題，這是我考慮不周，你稍等，再給我10分鐘最佳化一下。

03 | 最佳化方案

根據靜靜的要求，需要對錶格進行3個方面最佳化

1- 專案類別太雜亂，可以透過資料有效性規範

2- 顯示餘額增加一個小公式就可以啦

3- 透視表實時更新可以將資料來源收支明細表升級為超級表格

第1步：增加欄位

首先在表頭增加一個餘額的欄位，實時的計算餘額。

第2步：設定超級表格

接下來，為了讓透視表的統計結果，能夠隨著原始資料自動更新，需要把收支明細表格設定為超級表格

這樣，資料來源表（收支明細表）設定為超級表格後，新增資料後，對透視表進行重新整理就能及時反饋彙總。

1- 選擇表內任意單元格【Ctrl+T】-勾選表包含標題

或者，資料來源-【插入】-【表格】-勾選包含表標題-【確定】就得到下面這張表，新增專案公式自動填充

第3步：規範輸入

表格設計的一個重要環節就是「規範輸入」，因為你永遠都無法控制隊友，把表格填寫的五花八門。

Excel規範資料輸入行為，有一個非常好用的功能叫做：資料驗證。

以日期列為例，透過【資料】-【資料驗證】來完成規範輸入的操作如下：

1- 選中列-【資料】-【有效性】-【日期】-【大於等於】設定大於當前日期-【確定】

在Excel表格中，認可2種日期輸入方式：2019-1-1或者2019/1/1。

其他的輸入方式比如2019。1。1、20190101等等Excel都不認為是日期格式。

在進行透視表彙總時會造成不能識別的日期而統計資料不全，因此需要對日期進行輸入規範設定

參考日期列的規範方式，把收入、和支出列，也設定好對應的「資料驗證」規則。

收入：選中列-【資料】-【有效性】-【小數】-【大於等於】-0-【確定】

支出：選中列-【資料】-【有效性】-【小數】-【小於等於】-0-【確定】

對收入支出進行規範資料有效性規範也是為了減少錯誤發生，避免彙總統計錯誤

專案輸入規範：需要透過2個步驟完成，

1- 設定專案條件格式資料來源

2- 設定資料有效性

①設定輔助列專案資料來源：

選中資料來源-【插入】-【表格】-勾選包含表標題-【確定】或者【Ctrl+T】

②設定有效性：

選中列-【資料】-【有效性】-【序列】-【來源】=INDIRECT（$N$4）-【確定】

經過以上2步後，在輔助列新增新增專案後，資料來源表中專案下拉選單會自動更新

第4步：彙總（資料透視表）

選中資料來源【插入】-【資料透視表】-設定行欄位【專案】列欄位【收支合計、收入、支出】，總計行輸出所需總收入、總支出、收支合計。

在收支合計設定條件格式【開始】-【條件格式】-【突出顯示單元格規則】-【小於】0

當收支合計為負值會突出顯示報警

每天填寫完新增收支後，點選資料透視表-【右鍵】-【重新整理】完成資料彙總

第5步：說明

在設計表格的時候，一定不要把每個人都想象成自己，拿過來就會使用這個表格，這是不可能的。

把每個使用者都想象成超級Excel小白，他們不知道什麼條件格式，什麼資料有效性。

在表格裡寫上說明，是一個可以有效避免疑問和錯誤的方法！

04 | 最佳化後效果

跟著Skye姐學習完整個設計和製作過程之後，回過頭來再來看這個表格，你會更直觀的看到，這個表格的設計優點：

1- 每一筆新增支出後，重新整理透視表即可反饋彙總

2- 對彙總進行條件格式設定，如果收支合計為負值，顯示紅色預警

3- 遺漏的收支記錄隨時可以繼續新增

4- 日期、收支欄設定資料驗證後，避免誤輸入造成統計錯誤

5- 專案欄條件格式用輔助列進行資料驗證，既規範了輸入又隨時可以新增

6- 需要查詢某項支出透過日期、關鍵詞都能方便的查詢

7- 貼心的為表格寫上說明，輸入時遇到彈窗警告也不怕啦~

05 | 知識點總結

雖然收支表格是一個很簡單的需求，但是越是簡單的事情，越能清晰的體現你的邏輯能力。

武林高手過招，只需要一舉手一投足，就能感受到對方的功力。

本節課主要涉及到的知識點有：

1- 規範日期、金額輸入：透過【資料驗證】-【有效性】進行設定

2- 資料驗證序列欄位新增：【資料驗證】+函式【INDIRECT】引用超級表格

3- 超級表格。自動擴充套件資料區域，讓透視表自動更新。

06 | 表格還可以這樣做

這樣就夠了嗎？當然不是

一堆冷冰冰的數字，正數和負數縱橫交錯，考驗著我們的視力和耐力。

我想沒有人會拒絕Skye姐，把上面的統計結果，變成下面這樣的資料報告吧？

這個圖表是怎麼做出來的呢？

咱們明天繼續請Skye姐，做客拉小登，給我們聊一聊圖表的設計心得。

Skye，秋葉Excel進階訓練營優秀學員

好的表格是設計出來的，絕對不是用長公式堆砌出來的。

想學習表格設計思維？

想學習高效統計方法？

掃碼下面二維碼，新增秋小E免費預約諮詢。

我是拉小登，源於Excel，不只是Excel

= = 推薦文章 = =

這樣的人，永遠都學不好Excel

你的新年計劃落地，還差這一個表格

笨死了，被表格氣到哭，我才學會這個高效技巧

從零開始設計一個，貨源分配管理表

20年Excel老司機，被一個日報圖表愁到失眠，原來。。。

因為這個排期表，我把小編狠批了一頓

這幫小崽子們，做出來的圖表竟然感動到我了

用什麼圖表會讓差異更加直觀