知識模塊1-4

數據信息加工與處理數據可視化數據分析高效排版數據輸入目

錄能力目標能夠從源頭控制好數據的輸入能夠美化數據表能夠對數據進行有效分析能夠將數據可視化輸出學習目標3知識目標了解數據輸入了解高效排版了解數據分析了解數據可視化目標分析教學重點教學難點Excel的數據輸入Excel的高效排版Excel的數據分析Excel的數據可視化公式和函數排序篩選數據透視表數據透視圖項目概述電子表格處理是信息化辦公的重要組成部分，在數據分析和處理中發揮著重要的作用。某公司是一家集生產和銷售為一體的小型口罩生產企業，因生產的口罩質量良好，疫情期間口罩生產供不應求。隨著口罩生產企業的增多和國內疫情形勢的好轉，企業面臨產能過剩問題。其中一個原因是企業信息化水平較低，銷售數據分析不透徹，導致了目前的困境。本項目以該公司2020年口罩銷售表、客戶表和員工表3個Excel文檔（本素材參考秒可督學，圖1.4.1）為主要內容展開，針對該公司面臨的問題，使用Excel建立簡易、輕巧、高效的小型數據處理系統，助力企業實時掌握口罩銷售、客戶和員工情況，以便為下一步的管理決策提供依據。情境1

數據輸入知識模塊1-4

數據信息加工與處理任務1-規范數據的錄入

規范數據錄入包括兩部分：

一是錄入不同類型的Excel數據；二是設置數據驗證，即在錄入數據前給相關單元格預設數據的有效性，當用戶錄入不符合限定條件的數據時，系統限制錄入，并出現錯誤提示，通過此種約束錄入行為的方法來實現提高錄入準確率的效果。

本任務以“員工表”工作簿中的“員工信息表”（圖1.4.2）為例，介紹規范錄入部分數據的方法。職工號是員工在公司的標識，需設置為唯一。

選中A列，單擊“數據”選項卡“數據工具”組中的“數據驗證”按鈕，在打開的“數據驗證”對話框的“允許”項中選擇“自定義”，在“公式”項中錄入“=COUNTIF(A:A,A1)=1”，然后單擊“確定”按鈕，如圖1.4.3所示。１.錄入職工號任務1-規范數據的錄入注意：本題目中出現的COUNTIF()函數是有條件計數，這里是限制A列中不能出現重復值。在“數據驗證”對話框中“輸入信息”項的“輸入信息”內輸入“請輸入唯一的職工號，謝謝。”

在“出錯警告”項的“錯誤信息”內輸入“您輸入的信息重復，請重新輸入。”如圖1.4.4所示。１.錄入職工號任務1-規范數據的錄入在“數據驗證”對話框中“輸入信息”項的“輸入信息”內輸入“請輸入唯一的職工號，謝謝。”

在“出錯警告”項的“錯誤信息”內輸入“您輸入的信息重復，請重新輸入。”效果如圖1.4.5

所示。１.錄入職工號任務1-規范數據的錄入企業內員工的文化程度有高中、大專、本科、研究生和博士5種類型，部門有生產部、銷售部、財務部和人事部4個部門，職位有經理、助理和職員3種類型。２.錄入文化程度、部門和職位任務1-規范數據的錄入選中E2:E21單元格區域，單擊“數據”選項卡“數據工具”組中的“數據驗證”按鈕，在打開的“數據驗證”對話框的“允許”項中選擇“序列”，在“來源”項中錄入“高中,大專,本科,研究生,博士”5種類型的文化程度（注意分隔符號是英文狀態下的逗號），然后單擊“確定”按鈕，如圖1.4.6所示。２.錄入文化程度、部門和職位任務1-規范數據的錄入選中E2:E21單元格區域，單擊“數據”選項卡“數據工具”組中的“數據驗證”按鈕，在打開的“數據驗證”對話框的“允許”項中選擇“序列”，在“來源”項中錄入“高中,大專,本科,研究生,博士”5種類型的文化程度（注意分隔符號是英文狀態下的逗號），然后單擊“確定”按鈕，如圖1.4.6所示。２.錄入文化程度、部門和職位任務1-規范數據的錄入同理，可對F2:F21、G2:G21單元格區域設置部門、職位數據驗證，此處不再贅述。２.錄入文化程度、部門和職位任務1-規范數據的錄入同理，可對F2:F21、G2:G21單元格區域設置部門、職位數據驗證，此處不再贅述。２.錄入文化程度、部門和職位任務1-規范數據的錄入

身份證號碼是18位，聯系方式是11位的手機號碼，為了保證錄入的準確性，可以分別設置其數據驗證。３.錄入身份證號碼、聯系方式任務1-規范數據的錄入

選中H2:H21單元格區域，單擊“數據”選項卡“數據工具”組中的“數據驗證”按鈕，在打開的“數據驗證”對話框的“允許”項中選擇“文本長度”，在“數據”項中選擇“等于”，在“長度”項中輸入“18”，然后單擊“確定”按鈕。３.錄入身份證號碼、聯系方式任務1-規范數據的錄入

同理，對I2:I21單元格區域設置手機號碼數據驗證（如果手機號碼輸入位數不是11位，即出現如圖所示的“出錯警告”提示）。３.錄入身份證號碼、聯系方式任務1-規范數據的錄入在統計人員信息時，身份證號、出生年月、年齡是常見的信息之一。

利用Excel中的函數，可以方便地從身份證號中獲取出生年月信息并計算出年齡。職場技巧

（1）獲取出生年月日獲取出生年月日是使用了從字符串中間取值的MID函數。

其基本原理是，從身份證號中獲取中間的一段數字。出生年月日在身份證號碼中是從第7位開始的，長度是8位。

公式：=MID(C2,7,8)，如圖1.4.8所示。

公式的含義：C2表示身份證號碼所在的單元格地址，7,8表示從身份證號碼第7個字符開始取8個字符。職場技巧（2）計算年齡

年齡每年會變，所以Excel表格中儲存的人員年齡每年都會不同，要保證表格中人員年齡的準確性，需要每年對表格內容進行更新，但要是每年都手動逐個更新表格中的年齡，工作量會比較大。如果表格中存有身份證號碼，我們可以根據身份證號碼中的出生年月日信息，計算出這個人的實際年齡，而且這個年齡會根據電腦中的系統時間自動更新，從而避免手動更新的麻煩。職場技巧（2）計算年齡

使用的函數是獲取年份的函數YEAR、獲取當前系統日期和時間的函數NOW、從字符串左邊取值的函數LEFT。公式：=YEAR(NOW())-LEFT(D2,4)，如圖1.4.9所示。

公式的含義：YEAR(NOW())表示取當前日期的年份；LEFT(D2,4)表示從D2（出生年月日）單元格中取最左邊的4個字符，即出生年份。職場技巧任務2-數據填充

在實際工作中，經常需要錄入大量的有序數據，如錄入1～100，那么如何高效錄入呢？可以使用自動填充功能迅速完成。

本任務以“員工表”工作簿“員工信息表”中的“職工號”為例，講解數據填充的常用方法。

在A2單元格中輸入“2021001”，A3單元格中輸入“2021002”，然后選中A2:A3兩個單元格，用鼠標指向填充柄，當鼠標指針變為黑十字時，如圖1.4.10所示，按住鼠標左鍵拖動到所需位置，然后松開鼠標，即可完成數據填充。１.填充柄填充任務2-數據填充

在數值型數據填充時，按Ctrl鍵可以實現自動填充，如圖1.4.11所示。２.使用Ctrl鍵輔助填充任務2-數據填充

選中A2:A21的單元格區域，在A2單元格輸入“2021001”的職工編號，單擊“開始”選項卡“編輯”組中的“填充”按鈕，在彈出的下拉菜單中選擇“序列”項，在打開的“序列”對話框的“序列產生在”項中選擇“列”，“類型”項中選擇“等差序列”，“步長值”項中選擇“1”，然后單擊“確定”按鈕，如圖1.4.12所示。３.使用“填充”按鈕填充任務2-數據填充任務3-數據清洗

Excel中的數據處理是根據數據分析的目的將收集到的數據進行加工、整理，使數據保持準確性、一致性和有效性。數據處理是數據分析前必不可少的工作，占據了整個數據分析工作量的大部分。

常見的數據處理方法包括數據清洗、數據合并、數據抽取、數據計算、數據轉換等。其中，數據清洗就是將多余重復的數據篩選清除，將缺失的數據補充完整，將錯誤的數據糾正或者刪除，為后續數據分析提供完整、準確的數據。

常見的數據清洗方法有空格數據處理、重復數據處理和缺失數據處理。

空格數據的處理除了在Word中借助“替換”功能實現外，還可以利用Excel的“替換”功能實現。只不過，“替換”功能會把所有空格去除。例如，英文名字“VivianBrown”中間的空格在使用“替換”功能時也會被刪除，事實上這個空格必須存在。在這種情況下，可以使用函數TRIM刪除字符串前后的空格，而字符串中間的空格不會被刪除，如圖1.4.13所示。１.空格數據的處理任務3-數據清洗

重復數據的處理在工作中非常常見。如果依靠人工統計，效率低，錯誤率高。Excel自帶刪除重復值功能，可以輕而易舉地刪除重復值。２.重復數據的處理任務3-數據清洗方法：選中帶有重復值的數據，在“數據”選項卡“數據工具”組中選擇“刪除重復項”，如圖1.4.14所示，然后在彈出的“刪除重復項”對話框中，選擇“全選”以確保在選定區域內刪除重復值，最后單擊“確定”按鈕即可，如圖1.4.15所示。２.重復數據的處理任務3-數據清洗

在實際應用中，相同的數據我們經常采用合并單元格的方式呈現，但用于數據分析的表格不能有合并的單元格，應該是由相同行和列組成的表格。取消合并及居中后會產生一些空值單元格，這就需要批量填充。３.缺失數據的處理任務3-數據清洗方法：選中需要取消合并的單元格區域，選擇“開始”選項卡“編輯”組“查找和選擇”下拉列表中的“定位條件”，如圖1.4.16所示，在彈出的“定位條件”對話框中選擇“空值”，如圖1.4.17所示，則此時取消了單元的合并。３.缺失數據的處理任務3-數據清洗

方法：一次性選中所有空值單元格，然后按鍵盤上的“=”，再按鍵盤方向鍵中的“↑”，最后按Ctrl+Enter組合鍵，則所有空值都填充所在組對應的第一個單元格的值。此時，填充的數據是由公式產生的，通過選擇性粘貼的“值”選項，可以批量去除公式，如圖1.4.18所示。３.缺失數據的處理任務3-數據清洗情境2高效排版知識模塊1-4

數據信息加工與處理任務1-表格排版的原則

表格一般包含行標簽和數據內容兩部分，但在打印時為了明確表明表格的主題，會在最上面加上一個表格標題。

表格標題在排版時應盡量相對于整個表格居中，標題內容應盡量簡明扼要地說明表格所呈現的內容。在排版時，表格的格式調整要考慮到整體效果，使之主題明確、美觀大方、重點突出，同時具有易讀性。

Excel中提供了非常多的字體，總結起來有兩類：襯線字體和無襯線字體。默認的等線是無襯線字體，而襯線字體在整體字號較小的情況下，會出現模糊不清的情況，不建議使用。１.字體任務1-表格排版的原則

參差不齊的數據會讓人很難快速看懂表格要表達的含義。可以將單元格內容設置為對齊，為使用者節省時間，并提供舒適的閱讀體驗。２.對齊任務1-表格排版的原則

了解一些色彩心理學，會讓表格更出彩。

在Excel表格設計中，建議采用“主題顏色”面板中同一列的顏色來設置表格的邊框、字體顏色、填充顏色。這樣可以使表格整體給人以協調、和諧的感覺。另外，應盡量避免大片使用飽和度較高的顏色，否則會讓使用者有視覺不適感。３.顏色任務1-表格排版的原則

隔行填充顏色能對數據行進行視覺上的區分，讓使用者更容易獲取信息。４.間隔填充任務1-表格排版的原則

（1）打印前要設置邊框，Excel界面下方顯示的灰色網格線并不是單元格邊框，若不設置邊框，打印出來的表格是沒有邊框的。（2）設置的線框、邊框顏色不要喧賓奪主，可以采用較細的線條、比文字淡的顏色來弱化線框對表格內容的視覺沖擊。職場技巧任務2-設置表格格式

本任務以“客戶表”工作簿中的“客戶檔案”工作表為例來講解手動排版的方法。任務2-設置表格格式1.單擊左上角的選中按鈕，然后單擊“開始”選項卡“對齊方式”組中的“水平居中”和“垂直居中”按鈕，實現所有數據的水平居中和垂直居中。任務2-設置表格格式2.選中A1:G19單元格區域，單擊“開始”選項卡“字體”組中的“下框線”按鈕，在彈出的下拉菜單中選擇“所有框線”選項，為表格添加邊框。任務2-設置表格格式3.選中第1行，單擊“開始”選項卡的“單元格”組中的“格式”按鈕，在出現的下拉菜單中選擇“行高”選項，并在彈出的“行高”對話框中將“行高”設置為“22.5”，如圖1.4.19所示。同理，選中第2～19行，將其“行高”設置為“16.5”。任務2-設置表格格式4.選中A列和E列，單擊“開始”選項卡“單元格”組中的“格式”按鈕，在彈出的下拉菜單中選擇“列寬”選項，并在彈出的“列寬”對話框中將“列寬”設置為“9”，如圖1.4.20所示。同理，分別將B列、C列和F列的列寬設置為“12”，G列的列寬設置為“15”，D列的列寬設置為“45”。任務2-設置表格格式5.選中A2:G19單元格區域，單擊“開始”選項卡“字體”組中的“字體設置”按鈕，在打開的“設置單元格格式”對話框中將“字體”設置為“等線”，“字形”設置為默認“常規”，“字號”設置為“10”，如圖1.4.21所示。任務2-設置表格格式6.選中A1:G1單元格區域，單擊“開始”選項卡“字體”組中的“填充顏色”按鈕，在彈出的下拉菜單中選擇“其他顏色”選項，在打開的“顏色”對話框中的“自定義”選項卡中將“十六進制”設置為“#196B5B”，并將“字體”設置為“微軟雅黑”，“字號”設置為“11”，“字體顏色”設置為“白色”，加粗顯示，設置后的效果如圖1.4.22所示。任務2-設置表格格式7.選中A2:G2單元格區域，單擊“開始”選項卡“字體”組中的“填充顏色”按鈕，在彈出的下拉菜單中選擇“其他顏色”選項，在打開的“顏色”對話框中的“自定義”選項中將“十六進制”設置為“#ECF6F4”，并將“字體”設置為“微軟雅黑”，“字號”設置為“10”，“字體顏色”設置為“黑色”，然后將D2單元格（快遞地址）的對齊方式設置為“左對齊”，F2單元格（客戶等級的星級標）的顏色設置為“#196B5B”，設置后的效果如圖1.4.23所示。任務2-設置表格格式8.選中A3:G3單元格區域，單擊“開始”選項卡“字體”組中的“填充顏色”按鈕，在彈出的下拉菜單中選擇“無填充顏色”選項，并將“字體”設置為“微軟雅黑”，“字號”設置為“10”，“字體顏色”設置為“黑色”，然后將D3單元格（快遞地址）的對齊方式設置為“左對齊”，F3單元格（客戶等級的星級標）的顏色設置為“#196B5B”，效果如圖1.4.24所示。任務2-設置表格格式9.選中A2:G3單元格區域，雙擊“開始”選項卡“剪貼板”組中的“格式刷”按鈕，將第4～19行刷成同樣的格式，最終效果如圖1.4.25所示。任務3-優化表格排版

排版后的數據易讀性大大提高，故應注意結合排版原則對其進行排版。本任務以“員工表”工作簿中的“員工信息表”（圖1.4.2）為例，講解使用套用表格格式快速實現表格排版的方法。任務3-優化表格排版1.選中A1:I21單元格區域，單擊“開始”選項卡“樣式”組中的“套用表格格式”按鈕，在彈出的下拉菜單中選擇“中等色”中第1行第2列的表格樣式，整個表格即自動更改為該表格的格式效果，如圖1.4.26所示。任務3-優化表格排版2.單擊選中全表按鈕，然后單擊“開始”選項卡“對齊方式”組中的“水平居中”和“垂直居中”按鈕，實現所有數據的水平居中和垂直居中，如圖1.4.27所示。任務3-優化表格排版3.單擊“開始”選項卡“字體”組中的“下框線”按鈕，在彈出的下拉菜單中選擇“所有框線”選項，為表格添加邊框，如圖1.4.28所示。最終效果如圖1.4.29所示。任務3-優化表格排版3.單擊“開始”選項卡“字體”組中的“下框線”按鈕，在彈出的下拉菜單中選擇“所有框線”選項，為表格添加邊框，如圖1.4.28所示。最終效果如圖1.4.29所示。情境3

數據分析知識模塊1-4

數據信息加工與處理

數據分析是指用適當的統計分析方法對收集來的數據進行分析，并將它們加以匯總、理解與消化，以求最大化地開發數據的功能，發揮數據的作用。數據分析的目的是提取數據中有用的信息。

下面簡單介紹一些Excel中常用的分析方法。情境導入任務1-公式

本任務以“客戶表”工作簿中的“客戶檔案表”為例，介紹通過使用VLOOKUP函數及數據驗證功能，實現快速查詢任一客戶檔案資料的效果，如圖1.4.30所示。任務1-公式1.單擊“學生操作區”工作表，選中B4單元格，輸入“李鑫”，如圖1.4.31所示，希望能根據客戶姓名查找到聯系電話、省份、快遞地址等相關的客戶檔案信息。任務1-公式2.選中A8單元格，先查詢李鑫的“聯系電話”：單擊“公式”選項卡“函數庫”組中的“插入函數”按鈕，在打開的“插入函數”對話框中選擇“VLOOKUP”函數，然后單擊“確定”按鈕，如圖1.4.32所示。任務1-公式3.在“函數參數”對話框中分別設置“Lookup_value（查找值）”為“B4”，“Table_array（數據表）”為“客戶檔案!A1:G19”，“Col_index_num（列序數）”為“2”，“Range_lookup（匹配條件）”為“0”（0代表精確匹配，1代表模糊匹配），這樣即可完成跨表查詢李鑫“聯系電話”的信息，如圖1.4.33所示。任務1-公式4.使用復制函數的功能，將A8單元格的公式復制到B8單元格，修改參數“Col_index_num（列序數）”為“3”，其余的參數不變，單擊“確認”按鈕，即可得到客戶李鑫所在的“省份”信息，如圖1.4.34所示。任務1-公式5.同理，復制公式到C8單元格，修改參數“Col_index_num（列序數）”為“4”，其余的參數不變，單擊“確認”按鈕，即可得到客戶李鑫的“快遞地址”信息。分別復制公式到D8、E8、F8單元格，對應修改參數“Col_index_num（列序數）”為“5”“6”“7”，其余的參數不變，單擊“確認”按鈕，可得到客戶李鑫的“是否開票”“客戶等級”“備注”信息。最終效果如圖1.4.35所示。對于客戶檔案信息的查詢，肯定不只是希望了解客戶李鑫的檔案信息，還希望可以查詢所有客戶的檔案信息。將“客戶姓名”改為“王強”，則所有客戶檔案信息隨之改變，這是VLOOKUP函數的智能所在。但是手動輸入客戶姓名的方式不太靈活，可以通過數據驗證功能，把客戶姓名處做成下拉菜單的方式：單擊“學生操作區”工作表，選中B4單元格，單擊“數據”選項卡“數據工具”組中的“數據驗證”按鈕，在打開的“數據驗證”對話框的“允許”項中選擇“序列”，在“來源”項中選擇“=客戶檔案!$A$2:$A$19”，然后單擊“確定”按鈕，如圖1.4.36所示，即可從下拉菜單中選擇任一客戶進行檔案查詢。最終效果如圖1.4.37所示。職場技巧職場技巧任務2-排序

在Excel數據處理中，排序是比較常見的方法。未進行排序的原始數據沒有規律，看不出數據趨勢，而排序可以讓數據之間的關系更清晰。

本任務使用“員工表”工作簿中的“員工信息表”，先將“部門”按升序進行單列排序，然后在此基礎上，將“職位”由高到低進行多條件排序。任務2-排序1.選中“部門”所在列的任一有效數據所在的單元格，單擊“數據”選項卡“排序和篩選”組中的“升序”按鈕，即可實現按單列排序，如圖1.4.38所示。任務2-排序2.上述方法只適用于按一個關鍵字進行排序，若要按多關鍵字排序，比如按“部門”升序排序后，會發現雖然員工在同一個部門，但是“職位”排序是無序的，此處需要將“職位”按“總經理、助理、職員”的順序進行排列。任務2-排序2.多列排序①選中A1:I21單元格區域，單擊“數據”選項卡“排序和篩選”組中的“排序”按鈕，則出現圖1.4.39所示的“排序”對話框。任務2-排序2.多列排序②單擊“添加條件”，將出現的“次要關鍵字”選擇“職位”選項，“次序”選擇“自定義序列”選項，如圖1.4.40所示。任務2-排序2.多列排序③在彈出的“自定義序列”對話框中，將“輸入序列”處依次輸入“總經理”“助理”和“職員”后，依次單擊“添加”→“確定”按鈕，如圖1.4.41所示，然后在返回的“排序”對話框中單擊“確定”按鈕，即可得到圖1.4.42所示的最終效果（部分）。任務3-篩選

在工作中，當面對大量的數據時，如果只想聚焦部分數據，可以使用“篩選”功能。

任務以“客戶表”工作簿中的“客戶檔案表”為例，對客戶進行單一條件及多條件篩選：根據“客戶等級”篩選出“四星級”和“五星級”的客戶資料，并按“降序”排列，如圖1.4.43所示；除了上述條件，再將“備注”信息中付款方式為“按季度結算”的客戶篩選出來，如圖1.4.44所示。任務3-篩選1.選中任意一個有數據的單元格，單擊“數據”選項卡“排序和篩選”組中的“篩選”按鈕，表格第1行單元格的右下角出現向下的箭頭，單擊“客戶等級”右下角的箭頭，在彈出的下拉列表中選擇“降序”，再勾選“客戶等級”中“四星”和“五星”前的復選框，如圖1.4.45所示，此時其他數據便被隱藏了。任務3-篩選2.繼續單擊“備注”右下角的箭頭，在彈出的下拉列表中勾選“備注”中“按季度結算”前的復選框即可，如圖1.4.46所示。

（1）篩選按鈕的外觀職場技巧職場技巧（2）取消篩選如果需要恢復所有數據都顯示的狀態，單擊“數據”選項卡的“排序和篩選”組中的“清除”按鈕即可，如圖1.4.47所示。情境4

數據可視化知識模塊1-4

數據信息加工與處理

將Excel中的數據進行可視化處理，可以大大提高查看數據的效率，有助于我們迅速掌握關鍵信息。情境導入任務1-數據透視表

本任務以“2020年口罩銷售表”為基礎，通過使用數據透視表、數據透視圖以及圖表等功能來實現動態分析各銷售員在不同城市的銷售金額總和，形成一個動態看板，如圖1.4.48所示。任務1-數據透視表1.在“銷售數據”工作表中單擊任何一個單元格，然后單擊“插入”選項卡“表格”組中的“數據透視表”，則出現圖1.4.49所示的“創建數據透視表”對話框。任務1-數據透視表2.選擇“現有工作表”后，單擊“位置”輸入框（此時不要關閉對話框或點擊“確定”按鈕），然后單擊“學生操作區”工作表，如圖1.4.50所示，并在“學生操作區”工作表中單擊C11單元格后，再單擊“創建數據透視表”對話框中的“確定”按鈕，此時會出現一個空的數據透視表。任務1-