第1節(jié) 數(shù)據(jù)透視表技巧
通過(guò)使用數(shù)據(jù)透視表，可以匯總、分析、瀏覽和提供工作表數(shù)據(jù)或外部數(shù)據(jù)源的匯總數(shù)據(jù)。本節(jié)主要介紹如何創(chuàng)建數(shù)據(jù)透視表、數(shù)據(jù)透視表對(duì)數(shù)據(jù)的規(guī)范要求、數(shù)據(jù)透視表的基礎(chǔ)操作，幫助用戶一步一步地深入了解數(shù)據(jù)透視表的威力。
疑難1 如何創(chuàng)建一個(gè)簡(jiǎn)單數(shù)據(jù)透視表
某銷(xiāo)售部門(mén)2013年3月的銷(xiāo)售數(shù)據(jù)，現(xiàn)需要快速匯總各個(gè)地區(qū)的訂購(gòu)量，應(yīng)該如何操作呢？
解決方案1
以“銷(xiāo)售數(shù)據(jù)”為數(shù)據(jù)源，通過(guò)插入“數(shù)據(jù)透視表”功能來(lái)創(chuàng)建數(shù)據(jù)透視表。
操作方法
※ 插入數(shù)據(jù)透視表 ※
步驟1 單擊“銷(xiāo)售數(shù)據(jù)”工作表數(shù)據(jù)區(qū)域中的任意單元格→單擊“插入”選項(xiàng)卡→單擊“表格”組中的“數(shù)據(jù)透視表”按鈕。
步驟2 在彈出的“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中，此時(shí)的“表/區(qū)域”中已經(jīng)默認(rèn)選定了“銷(xiāo)售數(shù)據(jù)”中的數(shù)據(jù)區(qū)域，同時(shí)默認(rèn)自動(dòng)創(chuàng)建“新工作表”，單擊“確定”按鈕，關(guān)閉對(duì)話框。
步驟3 此時(shí)會(huì)自動(dòng)創(chuàng)建一個(gè)新工作表。在新工作表所出現(xiàn)的“數(shù)據(jù)透視表字段”窗格中，按下鼠標(biāo)左鍵并拖動(dòng)“地區(qū)”字段至“行”區(qū)域，拖動(dòng)“訂購(gòu)量”字段至“值”區(qū)域，即可實(shí)現(xiàn)快速匯總統(tǒng)計(jì)各個(gè)地區(qū)的訂購(gòu)量。
解決方案2
通過(guò)【Alt+D+P】組合鍵打開(kāi)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂?duì)話框，對(duì)“銷(xiāo)售數(shù)據(jù)”數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表。
操作方法
※ 利用向?qū)��?chuàng)建數(shù)據(jù)透視表 ※
步驟1 按下【Alt+D+P】組合鍵（此處首先按下【Alt】鍵，然后放掉，再按下【D】鍵、【P】鍵），在彈出的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)ЖD步驟1（共3步）”對(duì)話框中，選擇“數(shù)據(jù)透視表”的單選按鈕，單擊“下一步”按鈕。
步驟2 在彈出的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)ЖD第2步，共3步”對(duì)話框中，在“選定區(qū)域”中選擇銷(xiāo)售數(shù)據(jù)!$A$2:$E$19數(shù)據(jù)區(qū)域，然后單擊“下一步”按鈕。
步驟3 在彈出的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)ЖD步驟3（共3步）”對(duì)話框中，選擇“新工作表”單選按鈕→單擊“完成”按鈕。
步驟4 此時(shí)會(huì)自動(dòng)創(chuàng)建一個(gè)新工作表。在新工作表所出現(xiàn)的“數(shù)據(jù)透視表字段”窗格中，按下鼠標(biāo)左鍵并拖動(dòng)“地區(qū)”字段至“行”區(qū)域，拖動(dòng)“訂購(gòu)量”字段至“值”區(qū)域，即可實(shí)現(xiàn)快速匯總統(tǒng)計(jì)各個(gè)地區(qū)的訂購(gòu)量。
原理分析
利用“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂?duì)話框來(lái)創(chuàng)建數(shù)據(jù)透視表是Excel 2003工具菜單中最常見(jiàn)的創(chuàng)建數(shù)據(jù)透視表的方式。但從Excel 2007開(kāi)始，微軟對(duì)該工具菜單的功能進(jìn)行了刪減。若要使用“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂?duì)話框來(lái)創(chuàng)建數(shù)據(jù)透視表，只能通過(guò)【Alt+D+P】組合鍵的方式打開(kāi)該對(duì)話框。
知識(shí)擴(kuò)展
※ 數(shù)據(jù)透視表的發(fā)展歷程 ※
雖然數(shù)據(jù)透視表是Excel里面的一個(gè)強(qiáng)大功能，但其概念并不是由微軟最早提出的，而是由Lotus公司提出的。1993年Lotus公司開(kāi)發(fā)出了Windows版的透視表，稱(chēng)之為“Lotus Improv”。
微軟公司直到1995年才在Excel 5中開(kāi)發(fā)出了數(shù)據(jù)透視表。Excel 97增強(qiáng)了數(shù)據(jù)透視表向?qū)Чδ�，并�?duì)數(shù)據(jù)透視表的功能進(jìn)行了重大改善，如添加計(jì)算字段的能力，開(kāi)放數(shù)據(jù)透視表緩存等，從根本上改變了創(chuàng)建和管理數(shù)據(jù)透視表的能力。到Excel 2000時(shí)，引入數(shù)據(jù)透視圖，為用戶提供了用圖形的方式表現(xiàn)數(shù)據(jù)透視表的方法。到Excel 2007時(shí)，又在數(shù)據(jù)透視表的功能和設(shè)計(jì)方面進(jìn)行了新的改進(jìn)。Excel 2010的數(shù)據(jù)透視表增加了切片器。而目前的Excel 2013版本中，引入了推薦數(shù)據(jù)透視表、日程表、向上鉆取、向下鉆取等眾多新功能。本書(shū)主要基于Excel 2013版本進(jìn)行撰寫(xiě)。
疑難2 如何用含有合并單元格的數(shù)據(jù)來(lái)創(chuàng)建數(shù)據(jù)透視表
某公司2013年3月的銷(xiāo)售清單，數(shù)據(jù)里包含了合并單元格。現(xiàn)需創(chuàng)建數(shù)據(jù)透視表，但所得到的結(jié)果卻不是想要的，“行標(biāo)簽”里面出現(xiàn)了“（空白）”字段。那么如何對(duì)含有合并單元格的數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表呢？
解決方案
取消合并單元格，填充相同內(nèi)容項(xiàng)，然后創(chuàng)建數(shù)據(jù)透視表。
操作方法
步驟1 選中含有合并單元格的區(qū)域A2:A18 →單擊“開(kāi)始”選項(xiàng)卡→選擇“剪貼板”組→單擊“格式刷”按鈕，然后對(duì)單元格區(qū)域G2:G18使用格式刷功能，保留合并單元格格式。
步驟2 選中合并單元格區(qū)域A2:A18→單擊“開(kāi)始”選項(xiàng)卡→選擇“對(duì)齊方式”組→選擇“合并后居中”選項(xiàng)→選擇“取消單元格合并”選項(xiàng)，即可取消合并單元格。
步驟3 選中取消合并后的單元格區(qū)域A2:A18→單擊“開(kāi)始”選項(xiàng)卡→選擇“編輯”組→單擊“查找和選擇”按鈕→選擇下拉列表中的“定位條件”選項(xiàng)；在彈出的“定位條件”對(duì)話框中選擇“空值”單選按鈕→單擊“確定”按鈕，即可定位并選中單元格區(qū)域A2:A18中所有空值的單元格。
步驟4 在選中的空白單元格中輸入“=A2”，然后按【Ctrl+Enter】組合鍵，即可實(shí)現(xiàn)空白單元格填充相同項(xiàng)。
步驟5 選中備用合并單元格區(qū)域G2:G18→單擊“開(kāi)始”選項(xiàng)卡→選擇“剪貼板”組→單擊“格式刷”按鈕，重新刷回單元格區(qū)域A2:A18。
步驟6 清除G列單元格格式，以A2:E18單元格區(qū)域?yàn)閿?shù)據(jù)源，按照疑難1方法即可創(chuàng)建數(shù)據(jù)透視表。
原理分析
本例因?yàn)楹喜�單元格的存在，�?dǎo)致需要通過(guò)多個(gè)步驟對(duì)合并單元格進(jìn)行處理，才能創(chuàng)建所需的數(shù)據(jù)透視表。
※ 培養(yǎng)良好的數(shù)據(jù)錄入習(xí)慣 ※
就數(shù)據(jù)透視表而言，其對(duì)數(shù)據(jù)規(guī)范要求比較嚴(yán)格，應(yīng)盡量避免在數(shù)據(jù)源中使用合并單元格。所以要求用戶在數(shù)據(jù)錄入的時(shí)候，必須養(yǎng)成良好的數(shù)據(jù)錄入習(xí)慣，以有利于更好地管理數(shù)據(jù)，幫助用戶更好地進(jìn)行數(shù)據(jù)分析。
在日常工作中，不少企業(yè)為了讓數(shù)據(jù)顯示得更直觀，經(jīng)常采用二維表的數(shù)據(jù)錄入格式對(duì)數(shù)據(jù)進(jìn)行錄入；但在后期的數(shù)據(jù)統(tǒng)計(jì)匯總中，卻不能隨心所欲地使用數(shù)據(jù)透視表進(jìn)行匯總統(tǒng)計(jì)，大大削減了數(shù)據(jù)透視表的威力，且降低了工作效率。為了后期的基礎(chǔ)數(shù)據(jù)維護(hù)，必須養(yǎng)成良好的錄入一維表數(shù)據(jù)格式的習(xí)慣，避免使用二維數(shù)據(jù)表進(jìn)行數(shù)據(jù)錄入。
列舉了一維表與二維表格式對(duì)比圖，培養(yǎng)良好的數(shù)據(jù)分析習(xí)慣，從錄入一維數(shù)據(jù)表開(kāi)始。
注意
因?yàn)閿?shù)據(jù)透視表對(duì)數(shù)據(jù)要求比較嚴(yán)格，在進(jìn)行數(shù)據(jù)錄入的時(shí)候，必須按照數(shù)據(jù)庫(kù)的格式對(duì)數(shù)據(jù)進(jìn)行錄入，需要指定每一列字段的數(shù)據(jù)類(lèi)型。在同一字段下的數(shù)據(jù)，不能出現(xiàn)混合類(lèi)型（“日期”和“文本”、“數(shù)值”和“文本”等類(lèi)型），混合類(lèi)型不利于用戶養(yǎng)成良好的數(shù)據(jù)錄入習(xí)慣，不利于以后的數(shù)據(jù)管理和后期的數(shù)據(jù)分析。
疑難3 如何使用推薦的數(shù)據(jù)透視表
Excel的推薦數(shù)據(jù)透視表新功能能夠快速匯總大量數(shù)據(jù)并提供各種數(shù)據(jù)透視表選項(xiàng)的預(yù)覽，讓用戶可以選擇其中一種最能體現(xiàn)其觀點(diǎn)的數(shù)據(jù)透視表，而不必重新創(chuàng)建新的數(shù)據(jù)透視表。某公司的財(cái)務(wù)費(fèi)用支出數(shù)據(jù)，那么如何快速創(chuàng)建不同匯總分析的數(shù)據(jù)透視表？
解決方案
使用推薦的數(shù)據(jù)透視表功能對(duì)不同形式的數(shù)據(jù)透視表進(jìn)行預(yù)覽。
操作方法
※ 使用推薦的數(shù)據(jù)透視表 ※
步驟1 單擊“數(shù)據(jù)源”工作表數(shù)據(jù)區(qū)域中的任意單元格→單擊“插入”選項(xiàng)卡→選擇“表格”組中的“推薦的數(shù)據(jù)透視表”按鈕。
步驟2 在彈出的“推薦的數(shù)據(jù)透視表”對(duì)話框中，選擇一種最符合表達(dá)數(shù)據(jù)觀點(diǎn)的數(shù)據(jù)透視表，（如本例中選擇“求和項(xiàng)：總費(fèi)用，按部門(mén)”的推薦），最后單擊“確定”按鈕，即可成功創(chuàng)建合適的數(shù)據(jù)透視表。
原理分析
“推薦的數(shù)據(jù)透視表”是Excel 2013新增的功能。用戶使用該功能，能夠快速匯總報(bào)表數(shù)據(jù)并提供各種數(shù)據(jù)透視表字段布局預(yù)覽，幫助用戶提高數(shù)據(jù)分析效率，同時(shí)也讓Excel變得更聰明。
疑難4 編寫(xiě)VBA程序來(lái)創(chuàng)建數(shù)據(jù)透視表時(shí)，對(duì)數(shù)據(jù)透視表的名稱(chēng)識(shí)別尤其重要。創(chuàng)建數(shù)據(jù)透視表的時(shí)候，可得到創(chuàng)建的數(shù)據(jù)透視表名稱(chēng)為“數(shù)據(jù)透視表1”。但若要把該數(shù)據(jù)透視表名稱(chēng)重新命名為“匯總數(shù)據(jù)”，那么應(yīng)該如何重命名呢？
解決方案
修改“數(shù)據(jù)透視表名稱(chēng)”文本框中的內(nèi)容即可。
操作方法
※ 重命名數(shù)據(jù)透視表 ※
單擊數(shù)據(jù)透視表區(qū)域中任意單元格→單擊數(shù)據(jù)透視表工具中的“分析”選項(xiàng)卡，將“數(shù)據(jù)透視表”組的“數(shù)據(jù)透視表名稱(chēng)”文本框中的“數(shù)據(jù)透視表1”內(nèi)容修改為“匯總數(shù)據(jù)”。
原理分析
修改數(shù)據(jù)透視表名稱(chēng)，就像一個(gè)人修改自己的姓名需要去派出所將原來(lái)的姓名刪除然后填寫(xiě)新的姓名進(jìn)行備案一樣；在本例中關(guān)鍵是需要找到“數(shù)據(jù)透視表名稱(chēng)”文本框，然后對(duì)文本框的內(nèi)容進(jìn)行修改。
通過(guò)對(duì)本例的實(shí)踐，能夠更快地幫助用戶熟悉數(shù)據(jù)透視表。
疑難5 如何移動(dòng)數(shù)據(jù)透視表位置
創(chuàng)建完畢的數(shù)據(jù)透視表，現(xiàn)在需要把該表移動(dòng)至D4單元格位置，應(yīng)該如何操作呢？
解決方案
通過(guò)“移動(dòng)數(shù)據(jù)透視表”功能實(shí)現(xiàn)數(shù)據(jù)透視表的位置移動(dòng)。
操作方法
※ 移動(dòng)數(shù)據(jù)透視表 ※
步驟1 單擊數(shù)據(jù)透視表區(qū)域中任意單元格→選擇數(shù)據(jù)透視表工具中的“分析”選項(xiàng)卡→單擊“操作”組中的“移動(dòng)數(shù)據(jù)透視表”按鈕。
步驟2在彈出的“移動(dòng)數(shù)據(jù)透視表”對(duì)話框中，選擇移動(dòng)的位置為“現(xiàn)有工作表”的單元格“Sheet1!$D$4”。
步驟3 移動(dòng)后的數(shù)據(jù)透視表。
原理分析
借助“移動(dòng)數(shù)據(jù)透視表”功能能夠把創(chuàng)建好的數(shù)據(jù)透視表準(zhǔn)確地移動(dòng)到任何位置。