你還在這樣使用Excel軟體嗎？

建立正確規劃數據管理的思路及簡單實用的電子表格

我在多年Excel軟體的教學實踐中發現，職場中很多時候製表的目標相對盲目，而且對數據的處理存在不規範認識，從而導致雖然很多人都會製作Excel表格，但很少有人能夠從其製作的表格中獲取有價值的管理信息，或者獲取的過程既麻煩又複雜，費時、費力還不易達到預期。

為此，本文針對目前職場上使用Excel軟體以及數據處理中的問題，說明規範化的數據處理思路、數據組織規則，以及靈活方便的信息獲取方法等。

一、應用現狀

本文以某養生館日常業務統計為例，說明職場中數據處理過程存在的普遍問題。

示例：該單位要針對本業務的服務人次、服務費用和服務消耗進行常規統計。目前通常三張紙介報表形式按月填寫。統計表的管理目標（以服務人次匯總表為例）為：按日期，查看每個業務員當日服務人次及累計數值，其結構見圖 1：

另外兩張報表與之結構相同，只是統計項目分別為「服務費用」和「服務消耗」。現階段上面三張報表全部（以紙介形式）手工填寫。包括先填寫「當日」值（原始數據），然後手工計算填寫「累計」值（統計結果）。每月一次，且每次要填寫三張（加上服務費用表和服務消耗表）。費時、費力且易錯。所以，通常這類單位都有改善此類工作方式的意願。

但是，常規改善方法無非就是將紙質報表電子化而已（即將紙介報表搬到Excel文檔中）。雖然這種改善可以解決一些簡單計算問題，但是對後期數據處理、靈活分析沒有太大的意義，甚至還會帶來不少的麻煩。所以，下面就針對上述情況，從舊錶改造和規劃新表兩個方面加以說明，從而在比較中明確管理目標與數據（表格）結構的關係，良好的製表規劃將為靈活的管理帶來哪些優勢等。

二、舊錶改造

（一）思路

由於用戶比較習慣舊的工作方式（包括舊報表的閱讀形式），所以首先在現有統計表（紙介）的基礎上將其改造為電子表格，並盡量減少手工輸入（避免產生錯誤）。

（二）處理方法

通過以下紙介報錶轉換為Excel電子報表的過程，可以體會一下製作的規範流程。看看製作過程包括哪些步驟，是否麻煩。在完成製作後，還可以體驗一下在管理要求有所改變時，該報表的擴展應用是否簡單、方便。

1. 建立表格結構

以某服務單位月服務人次報表為例，按紙報表結構直接搭建（見圖 2）。包括：上表頭按月日橫向展開，左表頭包括業務員姓名和待統計項目，包括：「當月」（人次值）和每日「累計」（人次值）。

2. 針對加工數據設置公式

完成表格結構搭建後，建議分析一下該表格，看哪些數據必須手工輸入（稱「原始數據」），哪些數據可以通過計算方法獲得（稱「加工數據」）。然後，針對可加工數據設置公式，就可以減少不必要的手工輸入，同時可建立數據的關聯、跟蹤關係，提升數據表的工作效率，且避免產生人為錯誤。

針對上述服務人次報表的分析，將加工數據及公式處理方法列表如下：

公式的設置，下面以第一們業務員某月2日的公式為例（見圖 3）。完成後應橫向複製到當月最後一天單元格。

說明：上述某業務員「累計」行公式設置完成後，需以此為準，再針對該表其他業務員行逐一設置，最後再處理橫向和縱向的匯總公式。這樣，才能完成該表格中所有加工數據的公式設置。

3. 突出顯示（如「累計」值）

由於該表格從管理者的角度看，更關心「累計」值。所以要求將各「累計」行突出顯示。為此應當通過修飾加以強化顯示。具體操作方法如下：

（1）用間隔取值方法（即按下快捷鍵「ctrl」），逐行拖拉圖 2中各「累計」行有效數據區，呈反白狀態。

（2）單擊「開始」選項卡「字體」區的「字體顏色」，選擇「紅色」即可（見圖 4）。

4. 保護工作表，以避免隨意輸入導致統計錯誤

保護工作表的主要目的，就是防止錄入員的誤操作更改了表頭結構信息，或在加工數據區手工輸入而覆蓋了公式。本例中除「當日」行需要手工輸入外，其他單元格均不允許被隨意更改。

工作表保護操作通常包括兩個步驟：第一步應當選擇表格中不需要被保護的單元格（如各「當日」行），並解除默認的鎖防寫狀態（以使鎖定工作表後，該區允許輸入）。第二步為該工作表設置保護密碼以執行保護狀態。此處只進行第一步操作：

（1）用間隔取值方法（按下快捷鍵「ctrl」），逐行拖拉圖 1中各「當日」行有效數據區，呈反白狀態（見圖 5）。

（2）單擊「開始」選項卡「格式」的下拉按鍵，再單擊「鎖定單元格」命令。

注意：此時並未真正執行鎖定保護（表格各行內仍然可輸入內容），因為該表格中還沒有完成相關設置。一旦完成，將形成基表，就可以此為基礎，完成全年各月報表的生成（複製工作表）。此後，才可以逐一對各個「月」份的工作表進行運行保護的第二步驟，即設置工作表保護密碼，工作表的鎖定保護過程見「為各個工作表設置鎖定保護」一節。

5. 生成其他各月的工作表（一組分報表）

上述四個步驟完成後，相當於完成了一個基表的製作。這樣，就可以將該工作表進行複製後生成十二個月的分報表（以避免逐月重複上述設置）。具體步驟如下：

（1）將滑鼠游標移至「四月」標籤位置，顯示左剪頭游標後，按住滑鼠左鍵橫向拖拉的過程中，加按Ctrl鍵，即可生成新工作表，如：Sheet 1（見圖 6）。

（2）在新生成的工作表標籤（如：Sheet 1）上雙擊後，即可重新命名，如：「三月」。依次類推，即可生成十二個月的子工作表。

6. 為各個工作表設置鎖定保護

為避免填表人因誤操作導致的錯誤，原則上應當為各工作表設置運行（鎖定）保護。由於複製工作表前，已經將基表中允許輸入的各行設置了解除鎖定狀態，此處就可以針對各工作表設置保護密碼了，操作如下（以「四月」工作表的保護為例）：

（1）在「四月」工作表標籤處單擊滑鼠右鍵，選擇列表中的「保護工作表」命令，顯示相應對話框（見圖 7左）。

（2）設置密碼（如「aaa」）後（見圖 7中），單擊「確定」按鈕，顯示密碼確認對話框（見圖 7右），重複輸入上述密碼後，單擊「確定」即可完成對當前工作表（如「四月」）的鎖定保護。

（3）然後，請逐一對各月份工作表設置鎖定保護。

7. 製作匯總統計表（按季度）

在完成各月統計表的製作後，如果還需要按季度進行匯總統計，可在上表製作基礎上，再添加一個新工作表，命名為「某年匯總」。具體操作如下：

（1）製作表格結構（上表頭為各各季度，左表頭為業務員姓名（見圖 8）。

（2）設置相應公式（以第一位員工第二季度匯總值為例）。單擊選擇「匯總」工作中的相應單元格（如C4）。

（3）單擊「開始」卡右側的「求和」按鈕，編輯欄顯示公式（見圖 9）。

（4）移動滑鼠游標至「四月」工作表標籤位置並單擊（進入「四月」工作表區），再單擊該員工當月累計值（如「AH5」），以確定求和的起始位置（見圖 10）

（5）然後，按住Shift鍵，單擊「六月」工作表標籤（以確定求和的終止位置）。

（6）編輯欄公式顯示為「=SUM(四月:六月!AH5)」後，按下「回車」鍵，返回原「匯總」表（C4單元格位置），同時某人一季度匯總值顯示該單元格中（見圖 8）。

此後，按上述方法，依次設置「匯總」工作表中其他各季度、各業務員的匯總公式即可。

注意：完成上述工作後記得保存一下此電子文檔，如「服務人次統計表.xlsx」，以避免工作內容的丟失！並為後續使用者提供相對簡化的填表工具。

8. 舊錶改造工作小結

以上舊錶改造工作經歷了7個過程，若干具體步驟。雖然完成了該單位三大管理業務之一（服務人次）的匯總統計表製作。但是，另外兩項業務（即「服務費用」和「服務消耗」）的管理，是否還需要重複上述工作重新製作呢？

不用！因為三項管理報表的結構相同，只是統計內容存在差異。所以，可以在文檔「服務人次統計表.xlsx」的基礎上，通過「另存為」命令，更換名稱（如「服務費用統計表.xlsx」和「服務消耗統計表.xlsx」），再分別更換各個表文檔中各工作表區的標題名稱即可，甚至其中各個表格的表頭結構都可以不動。這不是挺簡單的嗎？

注意：如果希望更改新表文檔中各工作表的內容，原則上應當先逐一解除各工作表的鎖定保護，處理後再逐一進行鎖定，才能有效保證輸入狀態的安全性！這無形中給工作增加了一些麻煩。

另外，如果再需要對三項業務進行綜合管理，數據比對，以發現問題尋求解決方案時，就更加麻煩了。例如：希望按季度查看各業務員三項業務的對比信息（見圖 11）。

這時的麻煩就相對比較大了些，它包括了跨文檔、按條件調用數據一類複雜工作，分析相關性過程相對複雜、公式設置量相對較大，且需要數據能同步跟蹤，對Excel的普通使用者而言，就會形成一定的工作障礙。因為，這時就要求專業的知識和嫻熟的技能。

首先上述麻煩現象的原因，其實很簡單。就是沒有按照數據管理的規律，規劃和組織表體結構。導致表格雖然都製作了，但各個數據表間的關聯性十分離散，很容易形成「數據孤島」狀態。為解決這一類問題，最規範的方法就是按管理目標，合理規劃表體結構，然後運用Excel提供的相關工具，就可以實現後期數據處理既簡單、方便，又可以應對靈活、萬變的管理需求了。

所以，下面我們就來看一看，在前面紙介報表組的基礎上，如何分析管理需求、如何規劃表體結構、如何統籌運算關係、如何靈活獲取不同管理目標的管理報表。

三、規劃並使用新表

從上面舊錶改造的過程中可以看到，操作過程相對麻煩，且有些公式、格式等都需要重複設置。而且上述工作也只完成了三項日常匯總中的一項（人次統計），其他相關統計（如服務費用、服務消耗等），雖然統計方法相同，但仍需手工重複處理。既費時、費力，還易出錯。

為改善此類工作狀態，建議按管理目標針對最初的若干報表進行分析，建立起原始數據表，盡量讓周期性的匯總統計可以用最簡單的方法靈活獲取。

（一）分析並規劃表結構

明確管理目標，確定原始數據表頭結構

首先，我們看到日常匯總統計三張報表的結構完全相同，目標為：按各月日期，看每位員工當日和累計的數值。其中，統計的內容分為：人次、費用 和消耗三項（表格）；統計內容包括：當日和累計；統計對象為若干員工；統計形式為按日。

所以原始數據的上表頭應當包括以下欄位：編號、姓名、日期、日、人次、人次累計、消耗、消耗累計、服務、服務累計（見圖 12）。

（二）統籌建立運算關係

在確定表頭結構的前提下，以一名員工的記錄為例，設置各列內容，包括格式、公式等。

1. 設置「日期」列數據

因為該組管理是針對每個員工365天的業務進行記錄。所以先以某員工為例，製作出一年的基礎日期數據。具體步驟如下：

（1）在「日期」列表頭下（如「C4」單元格）輸入「2018年1月1日」，按「回車」鍵。

（2）再單擊C4單元格後，移動滑鼠游標至該單元格右下角「填充柄」位置，向下拖拉至C368單元格，以生成各行日期值（確認最後一行為2018年12月31日）。

（3）設置此列為日期格式。選擇該列有效數據，單擊「開始」卡「數字」組「常規」選擇按鈕，行選擇一種合適的日期格式（如：「長日期」或「短日期」）即可（見圖 13）。

2. 填充某員工「姓名」列記錄值

（1）在「姓名」列表頭下（如「B4」單元格）輸入第一位員工姓名，並按下「回車」鍵。

（2）再次單擊B4單元格，並移動滑鼠游標至該單元格右下角的「填充柄」位置（顯示十字形游標），雙擊（滑鼠左鍵），可將該業務員姓名快速複製到當前列最後一行位置，即2018年12月31日（如「B368」）。

3. 建立「日」列公式並處理顯示格式

如果後期報表需要按「日」統計，即將某月各日期值顯示於統計報表的上表頭行中，可通過基礎表「日期」列換算是日期值對應的各月「日」值，避免手工輸入，操作如下：

（1）在表格「日」列表頭下（如「D4」單元格）輸入公式：=DAY(C4)，然後按下「回車」鍵，即可從日期「2108/1/1」中提取「日」值（如「1」）。

（2）為了讓數值「1」後顯示文字「日」，需要為該單元格設置「掩碼」。選擇該單元格（如「C4」，通過「開始」選項卡單擊「數字」組右下角開關，顯示相應對話框。再單擊對話框中「數字」卡選擇「自定義」。選擇樣式列表中的「0」樣式，在「類型」區「0」後輸入一對英文半形狀態的雙引號（字元串掩碼），並在其中輸入漢字「日」。單擊對話框中的「確定」按鈕返回工作表區，即可將原來的數值「1」顯示為「1日」格式（見圖 14）。

（3）重新單擊D4單元格，移動滑鼠游標至D4單元格右下角，雙擊「填充柄」將該公式複製到底。

4. 設置累計列公式（以「人次累計」為例，其他相同）

由於匯總顯示報表要求每月「人次」從1次開始（中累計上月次數），即每月首行累計，應等於同行（日期）的「人次」數，而當月第二天開始，累計數均為當日「人次」數+前日「累計」數。所以，原始數據表跨月「累計」值，應逐月分兩行設置。具體步驟：

（1）先設置當月首日的「累計」公式（以F列第1行為例）。等於前日「人次」（即F4=E4）。

（2）再設置當月第2日的「累計」公式（見圖 15）。等於當日「人次」值+前日「人次累計」值。即：F5=E5+F4。

（3）查看編輯欄公式正確後，按下「回車」鍵。再次單擊F5單元格，雙擊複製按鈕（將該公式複製到該業務員的12月31日單元格位置）。

（4）為按管理要求，每月為獨立統計單位，即不接續上月統計人次數量。所以上述公式設置後，還需要逐月設置每月的月首日「累計」公式（以5月1日某業務員「人次累計」為例（建議從1月1日開始），具體操作如下：

單擊相應單元格（如「F34」），按下等於號「=」，再單擊同行「人次」單元格（如「E34」）。最後，按下「回車」鍵即可（見圖 16）。

注意：此後應移動滑鼠游標至各月1日單元格，重複上述處理（至12月1日行為止），也包括原始數據表其他相關列（如「服務累計」H列和「消耗累計」J列）。

5. 突出「累計」列格式

為突出「累計」列的數據，可將相關列修飾為綠色背景，紅色數值格式。操作步驟：

（1）單擊列標F，再按住Ctrl鍵分別單擊列標H和J（實現間隔取值）。

（2）分別通過「填充顏色」和「字體顏色」選擇「綠色」和「紅色」。

（3）再用「格式刷」工具，將數據區以上的區域，恢復原來格式即可。

（三）運行測試

完成上述原始數據表的規劃設計、格式設置後，可以先進行一下基本測試。一般在原始數據（如「人次」列）輸入具體數值，觀察對應的加工數據區（如「人次累計」列），如果正確即可交付下一工作流程，如果錯誤則查找並修改。

1. 輸入基礎數據驗證匯總統計結果

目的：輸入真實數據（原始，如「人次」的各日數據），檢驗匯總（各列）統計公式設置是否正確。

示例：以某人「人次」統計為例。

（1）在E4單元格輸入「1」，F列同行應顯示「1」；在E5單元格再輸入「1」，則F5顯示「2」；在E6單元格單擊方向鍵「向下」（無值跳過），則F5仍然顯示為「2」（見圖 17）。

從上述三個單元格數值的輸入情況看，已經滿足了常規統計要求（通過！）。

（2）刪除上述測試內容，恢復預填寫狀態，便形成了某業務員全年365天的基礎數據表。此後，每天依次輸入原始數據行的具體值，便自動生成當日累計行的計算值。

2. 複製基表內容生成全員、全年基礎數據表

目的：生成全員、全年基礎數據表。

針對前面某業務員、全年的基礎數據表，可以通過複製、粘貼、更名一系列操作，生成全員、全年的基礎數據表。最終為填表人預留輸入的空間（避免其在賽程中改動表體的結構）。

示例：由於前面已經完成了黃剛（業務員）、全年（365天）、三項管理內容（人次、費用和消耗）的基礎數據表製作。將其所有記錄行內容複製後，在數據區尾部粘貼，即可生成其他業務員的全年記錄，依次類推即可。具體操作如下：

（1）選擇某業務員、全年的數據區（以「黃剛」為例）：如起始數據的位置為A4單元格，則單擊此單元格，然後按住Shift鍵再單擊表格數據有效區最後一個單元格（如：J369）。

（2）Ctrl + c（複製被選中區域內容）。

（3）單擊上述選區下方第一個單元格（如：A370）後，Ctrl + V（粘貼）。

（4）單擊B370單元格，輸入第二位業務員的姓名，並雙單該單元格填充柄，將名稱複製到第二位業務員記錄的底部。

（5）重複步驟(1)至步驟(4)，直至完成每一伴業務員記錄行的添加。

3. 為數據源區命名

為方便快速選擇原始數據的有效數據區，原則上應當為工作表和數據源區命名。具體操作如下：

為工作表命名的方法（前面已經介紹）：雙擊該工作表標籤，輸入名稱（如「數據源區」），按下「回車」鍵即可。

為數據區命名方法：

（1）選擇完整、有效數據區：單擊表體區域首個單元格，如「A3」，按住Shift鍵單擊完整有效數據最後一個單元格，如「J2912」（假設有6位業務員）。

（2）命名：單擊編輯欄左側的單元格地址區（又稱「名稱框」），直接輸入名稱，如「數據源區」，按下「回車」鍵即可。

4. 設置運行保護（解除輸入區的默認鎖定設置）

為避免人為輸入的誤操作，該表格應當解除「人次」「消耗」和「服務」各列默認的鎖定狀態，再對工作表設置鎖定保護密碼。具體步驟：

（1）單擊列標E，再按住Ctrl鍵分別單擊列標G和I實現間隔取值（見圖 18）；

（2）通過「開始」選項卡「格式」組中「格式」下拉按鈕，單擊「鎖定單元格」命令。

（3）再恢復表頭行及以上各單元格（3行）的原格式。

（4）再次單擊「格式」下拉按鈕，選擇「保護工作表」命令，顯示相應對話框。

向下拖拉滾動條選擇「使用自動複選」複選框（見圖 19左）。輸入密碼後單擊對話框中「確定」按鈕，顯示確認對話框。

（5）重複輸入密碼後，單擊「確定」按鈕即可（見圖 19圖 18右）。

（6）測試

完成上述設置後，原始數據表就可以進入運行狀態了。

可以在E、G和I列中輸入具體數據，觀察F、H和J列自動生成的數值，且確認這三列不能人為輸入內容。

（四）靈活使用

1. 製作數據透視表

目標（以人次統計為例）；按日期查看每人當日人次數量和人次累計統計值。

製作：

（1）通過「名稱框」選擇有效數據區。如「數據源」。

（2）通過「插入」選項卡單擊「數據透視表」，並「確定」。生成新工作表，並命名為「人次統計」。

（3）將「日」欄位拖到上表頭區，「姓名」欄位拖到左表頭區。再將「人次」和「人次累計」分別拖到匯總區（見圖 20）。

（4）單擊B5單元格，在編輯欄中輸入「當日：」，回車；單擊B6單元格輸入「人次累計：」，回車，以修改左表頭「值」列的內容。

（5）拖拉「日期」欄位至左表頭，並單擊右鍵選擇「組合」，在對話框中勾選「月」「季」和「年」）。確定後生出相關列。再分別將「月」「季」和「年」拖拉到「頁」欄位區。

（6）單擊B6（「人次累計：」單元格，一組累計行均反白顯示。單擊「開始」卡中「條件格式」-「新建規則」後顯示對話框，單擊「使用公式確定要設置格式的單元格」項，輸入公式=$b8=」人次累計：」，單擊「格式」按鈕，設置字體顏色為「紅」，格背景為「淡綠」。單擊對話框中的「確定」按鈕，返回工作表。各累計行顯示綠色背景紅色數值（見圖 21）。

完成上述操作後，針對人次統計報表就完成了。並且查看時可以按年、季度或月進行複選查看等（見圖 22）。

使用上述方法，再分別針對其他應用，建立另外兩個透視表，工作表標籤分別為「服務統計」和「消耗統計」。

2. 新數據的輸入及報表查看

完成上述工作後，每天的數據輸入只要通過「數據源」工作表輸入，即可通過三個工作「人次統計」「服務統計」和「消耗統計」查看統計結果了。

示例：假如目前要輸入五月1日的「人次」基礎數據，具體操作如下：

（1）單擊「原始」工作表，通過名稱框選擇「數據源2018」以選擇數據有效區（反白）。

（2）單擊「數據」選項卡中的「篩選」按鈕，表頭行顯示篩選器。

（3）單擊「日期」欄位的篩選器（下三角）顯示列表，單擊「全部」取消默認勾選狀態，再單擊「五月」項左側的「+」號，展示五月各個日期，單擊「01」複選框（見圖 23）。

（4）此後，可逐列（按人員）輸入當日數據，包括：人次（E列）、服務費用（G列）和消耗（I列），本例只輸入「人次」數（見圖 24左）。

（5）完成五月各員工日常數據的輸入後，可分別單擊統計工作表（如「人次統計」），即可顯示5月1日各員工的人次統計信息（見圖 24右）。

注意：如果輸入後沒有顯示相應的統計信息，只需在相關統計表（工作區）單擊滑鼠右鍵，選擇「刷新」即可。

3. 從原始數據表引入數據生成舊報表中的記錄行

如果習慣看舊報表的形式（如：工作表「五月」），也可以將「原始」表中的輸入值引入「五月」工作表的相關記錄行，從而避免二次輸入。

示例：如果「五月」工作表中某業務員一行「當日」數據希望從「原始」工作表中引入，具體操作如下：

（1）在「五月」工作表中，拖拉選擇第一名員「當日」記錄行區域（如「C4：AG4」）。

（2）單擊編輯欄輸入公式：=TRANSPOSE(

（3）單擊「原始」工作表，拖拉選擇相關人員「人次」列「五月」的31個（縱向）單元格（見圖 25上）。輸入反括弧（顯示：=TRANSPOSE(原始!E34:E64)），並Ctrl + Shift + 回車，返回「五月」工作表（見圖 25下）。

針對「五月」工作表各員工的「當日」行，重複上述步驟(1)至步驟(3)。可完成「五月報表的一次原始數據輸入，自動生成報表。還需要逐月設置（相對麻煩，不如使用數據透視）。

如果，通過本文您對數據管理有了一些新的認識，但是將其落實到自己的工作中尚存在一些困難，建議組織相關培訓，或進行專業諮詢。會比純自學的效率高一些。

相關視頻文檔和練習文檔可聯繫馬教師索取。

電子郵件：yzyatc@163.com