第8章Excel綜合案例分析處理8.1任務書8.2任務示范8.3知識拓展

8.1任務書

1.創建數據透視表

本任務是通過對工資表設計創建數據透視表，利用切片器進行分類查看，并轉化為數據透視圖。通過本任務可熟練掌握數據透視圖及分列函數的使用。

2.使用分列規范日期

本任務是通過對工資表中的日期信息進行分列來提取出年、月、日，對不規范的日期格式進行規范化處理。

3.凍結和拆分窗格

本任務是對工資表中的窗口進行拆分和凍結，以方便查看比較大的數據區域。

4.插入頁眉頁腳

本任務是對工資表中的頁眉和頁腳進行設置，以打印出符合規范的報表。

5.調整工資

本任務是對工資表中的基本工資增加500元，并通過選擇性粘貼來實現。

6.生成工資條并打印輸出

本任務是對工資表進行美化，然后通過排序生成工資條，并打印輸出。

7.錯誤屏蔽

本任務是對計算表內的常見錯誤進行屏蔽，掌握錯誤的處理方法。

8.2任務示范

1.創建數據透視表

【任務實施】

(1)插入數據透視表，統計不同地區、不同部門、不同職務等級的人數。數據透視表最終效果如圖8-1所示。圖8-1數據透視表最終效果

操作步驟1：新建一個工作表，取名“數據透視表”，選擇“插入”選項卡，單擊“數據透視表”按鈕，在彈出的“創建數據透視表”窗口進行設置。首先選擇數據源，將“工資表”所有的數據都選中，可以單擊其中一個標題行單元格，然后按下快捷鍵“Ctrl?+?A”即可選中整個列表區域。接著選擇放置數據表的位置為現有工作表，在位置區域單擊“選擇”按鈕，選中“數據透視表”的A1單元格，如圖8-2所示。設置完成后，單擊“確定”按鈕。

圖8-2選擇分析數據，選擇數據透視表放置位置

操作步驟2：在該工作表右側會出現數據透視表字段列表，勾選需要進行數據透視的字段(地區、部門、職務等級和姓名)，并分別放置到報表篩選、行標簽、列標簽和數值框中，如圖8-3所示。因為我們要統計人數，因此通過單擊右鍵對“姓名”字段進行值字段設置，設置匯總方式為“計數”，如圖8-4所示。

圖8-3數據透視表字段列表1

圖8-4值字段設置

(2)根據數據透視表，按不同地區、部門和職務統計其實發金額的合計，并使用切片器工具對各類數據進行選擇。切片器最終效果如圖8-5所示。

圖8-5切片器最終效果

操作步驟1：選擇“插入”選項卡，單擊“數據透視表”按鈕，在彈出的“創建數據透視表”窗口進行設置。首先選擇數據源，將“工資表”所有的數據都選中，可以單擊其中一個標題行單元格，然后按下快捷鍵“Ctrl?+?A”即可選中整個列表區域。接著選擇放置數據表的位置為現有工作表，在位置區域單擊“選擇”按鈕，選中“數據透視表”的A12單元格，如圖8-6所示。設置完成后，單擊“確定”按鈕。

圖8-6創建數據透視表

操作步驟2：在該工作表右側會出現數據透視表字段列表，勾選需要進行數據透視的字段(地區、部門、職務等級和實發金額)，并分別放置到報表篩選、行標簽、列標簽和數值框中，如圖8-7所示。默認的匯總方式為求和。

圖8-7數據透視表字段列表2

操作步驟3：選中該數據透視表，選擇數據透視表工具中的“選項”，單擊“插入切片器”按鈕，設置切片器。勾選地區、部門和職務等級，創建3個切片器，如圖8-8所示。

圖8-8切片器設置

操作步驟4：要求顯示杭州地區，職務等級為科級的銷售人員實發金額和。分別設置地區切片器中的杭州，部門切片器中的銷售部，職務等級切片器中的科級，就可以統計出相應的金額了，如圖8-9所示。

圖8-9根據需要顯示透視信息

(3)根據所得的數據透視表產生數據透視圖。

操作步驟1：光標單擊已經存在的數據透視表某一個單元格，在數據透視表工具中的“選項”選項卡中單擊“數據透視圖”按鈕，選擇圖表類型為簇狀柱形圖，單擊“確定”按鈕。

操作步驟2：選中該圖表，適當調整大小，該圖表會根據數據透視表的篩選而更新變化，也可以自己進行選擇顯示，如圖8-10所示。

圖8-10數據透視圖

2.使用分列規范日期

【任務實施】

(1)規范化日期數據，利用分列來拆分出生日期中的年、月、日，分成3列顯示。

操作步驟1：單擊“分列”工作表，在身份證后面有3列，分別提取年、月和日的信息。選中身份證下的數據，選擇第一個身份證號碼，按快捷鍵“Ctrl?+?Shift?+↓”即可，再選擇“數據”選項卡，單擊“分列”按鈕，打開分列向導，第一步設置分列方式，這里選擇“固定寬度”，如圖8-11所示。

圖8-11分列固定寬度

操作步驟2：因為身份證從第7位到第13位數字為出生的年、月、日信息，對于這樣規范的信息，可以直接去分割。插入4條分列線，單擊“下一步”按鈕，如圖8-12所示。

圖8-12設置字段寬度1

操作步驟3：設置不導入的列，此處需要將第一列和最后一列設置為“不導入此列(跳過)”。這兩列忽略，目標區域設置為C2單元格，如圖8-13所示。

說明：如果不設置目標區域，分列效果將體現在選中的這列數據上。

圖8-13選擇不導入的列

操作步驟4：單擊“完成”按鈕即可完成分列，這里年、月、日3列均為常規格式。分好的效果如圖8-14所示。

圖8-14分列最終結果

(2)利用分列，從身份證提取年、月、日8位數字，轉化為YMD日期格式。

操作步驟1：復制身份證這列到H列，將列標題改成出生日期。選中下面的數據區域，單擊“分列”按鈕，設置分列類型為“固定寬度”，單擊“下一步”按鈕。

操作步驟2：設置兩條分列線，將8位日期隔出來，單擊“下一步”按鈕，如圖8-15所示。

圖8-15設置字段寬度2

操作步驟3：設置前后兩部分為“不導入此列(跳過)”，在下面預覽中顯示“忽略列”，然后設置中間的這部分為日期“YMD”格式，單擊“完成”按鈕，如圖8-16所示。

圖8-16設置日期格式

(3)對“年，月，日”或“月，日，年”等不規范的日期規范化。

操作步驟：選中不規范的日期單元格，選擇“分列”按鈕，直接選擇到第三步，在日期后面的下拉框中選擇格式，YMD表示年、月、日，MDY表示月、日、年，系統會自動去判斷分割符。設置目標區域，可以看到規范化的日期。

3.凍結和拆分窗格

【任務實施】

(1)凍結前兩行和前兩列。

操作步驟：選擇工資表，選中C3單元格，單擊“視圖”選項卡中的“凍結窗格”按鈕，選擇凍結和拆分窗口即可。這樣向下查看或向右查看時，前兩行和左兩列都是不變的，方便查看。如要取消凍結，可選擇凍結窗格的取消凍結窗格。

(2)拆分窗口。

操作步驟：因為表格比較大，需要拆成左、右兩部分查看。如果沒有標題合并單元格，那么只能選擇第1行某個單元格，單擊“視圖”選項卡中的“拆分”按鈕進行左、右拆分；如果現在有標題行，那么可以選擇第2行的某個單元格進行拆分，窗口會根據選中單元格的左上角作為拆分的交叉點進行窗口拆分，整個窗口會拆分成四部分，每一個區域都能通過垂直和水平滾動條進行整個表格的查看瀏覽。如要取消拆分，再次單擊“拆分”按鈕即可。

4.插入頁眉和頁腳

【任務實施】

(1)在頁眉處居中位置輸入“工資表”，設置字體為“楷體”“加粗”。

操作步驟：插入頁眉和頁腳會進入頁面視圖，而凍結窗格與頁眉視圖兩者不能共存，軟件會自動取消凍結。在頁眉區域居中位置輸入“工作表”，設置字體為“楷體”“加粗”。

(2)在頁腳處居中位置輸入“制表人：姓名”，右側插入“當天日期”。

操作步驟：轉到頁腳區域，在居中位置輸入“制表人：姓名”，右側單擊“當前日期”按鈕即可插入日期。該頁眉、頁腳內容只有在打印輸出或頁面視圖才能看到。

5.調整工資

【任務實施】

(1)新建一張工作表，取名“調整后工資表”，并復制原“工資表”的內容。

操作步驟：這里有兩種操作方法

①新建工作表，重命名為“調整后工資表”，選中整張表格，按快捷鍵“Ctrl?+?C”復制整張表，然后選擇調整后工資表的A1單元格，按快捷鍵“Ctrl?+?V”粘貼。

②選擇“工資表”標簽，單擊右鍵，在彈出的菜單中選擇“移動”或“復制”，在彈出的對話框中設置創建副本，選擇要創建在哪張工作表前或最后，創建的名稱默認為“工資表(2)”，雙擊工作表標簽重命名為“調整后工資表”。

(2)通過選擇性粘貼，每人的基本工資都增加500元。

操作步驟：在空白單元格輸入500，復制該單元格，整個單元格邊上會出現螞蟻線；然后選中所有基本工資數據單元格，單擊右鍵在彈出的菜單中選擇“選擇性粘貼”，打開對話框，設置粘貼為“公式”，運算為“加”，如圖8-17所示。

圖8-17選擇性粘貼

6.生成工資條并打印輸出

【任務實施】

(1)新建“工資條”工作表，方法同上。

(2)采用排序法，生成工資條。

操作步驟1：在后面增加一列輔助列，利用等差數列填充數值，1～66為有信息的區域，在66后輸入1.5、2.5，選中這兩個單元格，繼續以等差序列、步長為1填充，一直到66.5。

操作步驟2：將標題行復制到下面空白區域，以輔助列為主要關鍵字，進行重新排序，最后刪除輔助列。

操作步驟3：設置自動套用樣式為中等深淺2。

操作步驟4：頁面設置中，紙張方向為“橫向”。打印標題，進行設置打印區域和打印比例的調整。

7.錯誤屏蔽

【任務實施】

(1)屏蔽除零錯誤?#DIV/0！。

已知總價和數量計算單價，采用除法，如果數量為空，則會出現?#DIV/0！錯誤。為避免此類錯誤，可采用IF語句判斷是否有錯誤，如果有則顯示為空，即?“?”?。

操作步驟：選擇“錯誤屏蔽”工作表，在單價下的單元格中輸入公式“=IF(ISERROR(K12/L12),"",K12/L12)”。

(2)屏蔽無結果錯誤#N/A。

通過查找函數VLOOKUP可以根據姓名找到所對應的身份證號碼，當輸入的姓名不在表中時會出現?#N/A錯誤。為了屏蔽此類錯誤，可以采用ISNA()函數來判斷是否有結果，如沒有找到，可顯示為?“查無此人”。

操作步驟：在L18單元格輸入公式“=IF(ISNA(VLOOKUP(K18，A:B，2，0))，"查無此人"，VLOOKUP(K18，A:B，2，0)”。

8.3知識拓展

(1)會計中常會把0值表示為“-”。

此處要求對工資表中的0值或空白處填充為“-”。方法1設置單元格為會計專用，并設置貨幣符號為無。

操作步驟：選中所有金額單元格，設置單元格格式，按下快捷鍵“Ctrl?+?L”打開“單元格格式”對話框，選擇會計專用，設置貨幣符號為無。

方法2替換該區域的0值為“-”。

操作步驟：利用查找替換，選中所有金額單元格，按下快捷鍵“Ctrl?+?H”打開“替換”對話框，將查找內容設置為0，并替換為“-”。

(2)合并計算。

合并計算是指將多個相似格式的工作表或數據區域，按指定的方式如求和、計數、平均值、乘積等進行自動匹配計算。Excel提供了幾種方法來合并計算數據，最靈活的方法是創建公式，該公式引用的是將進行合并的數據區域中的每個單元格。引用了多張工作表中的單元格的公式被稱為三維公式。

實例：對“Excel案例(素材2).xlsx”中的“成績表”工作表進行計算，利用合并計算，計算出成績表中各選手的總成績。總成績為預賽、半決賽和決賽成績的平均成績。

操作步驟：選中B39:D47區域，單擊“數據”→“合并計算”調出“合并計算”對話框，選擇“函數(F)”為“求和”；將鼠標置于“引用位置(R)”文本框中，選中B3:D11區域，單擊“添加”按鈕；將鼠標置于“引用位置(R)”文本框中，選中B15:D23區域，單擊“添加”按鈕；將鼠標置于“引用位置(R)”文本框中，選中B27:D35區域，單擊“確定”按鈕。

(3)奇偶數個數的統計。

實例：對“Excel案例(素材2).xlsx”中的“奇偶數”工作表進行統計，統計A1:A10中奇數和偶數的個數并放到D2和D3單元格中。

操作步驟：選中D2單元格，輸入公式奇數個數統計的數組公式“=SUM(IF(MOD(A1:A10,2)<>0,1,0))”，同時按下“Ctrl?+?Shift?+?Enter”組合鍵鎖定數組公式。選中D3單元格，輸入偶數個數統計的數組公式“=SUM(IF(MOD(A1:A10,2)=0,1,0))”，同時按下“Ctrl?+?Shift?+?Enter”組合鍵鎖定數組公式。

(4)閏平年的判斷。

實例：對“Excel案例(素材2).xlsx”中的“奇偶數”工作表進行判斷，判斷A列的年份是否為閏年，填入B列。

使用函數判斷年份是否為閏年，如果是，則結果保存為“閏年”；如果不是，則結果保存為“平年”，并將結果保存在“是否為閏年”列中。

說明：閏年是指能被4整除但不能被100整除，或者能被400整除的年份。

操作步驟：選中A2單元格，輸入公式“=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0)，"閏年"，"平年")”，單擊“確定”按鈕，并雙擊填充柄完成閏平年的判斷。

(5)數據有效性。

實例1：下拉菜單輸入的實現。有時候在各列各行中都輸入同樣的幾個值，比如輸入學生的成績等級時需要輸入4個值：優秀、良好、合格、不合格。