Excel數(shù)據(jù)分析（提升數(shù)據(jù)處理與分析技能）

Excel數(shù)據(jù)分析提升數(shù)據(jù)處理與分析技能目錄\h第01章只按一鍵，告別煩瑣重復(fù)的操作\h1.1瞬間實現(xiàn)報表數(shù)據(jù)匯總\h1.1.1多列數(shù)據(jù)瞬間匯總\h1.1.2多行數(shù)據(jù)瞬間匯總\h1.1.3一鍵實現(xiàn)分別按行、按列匯總數(shù)據(jù)\h1.2快速進行數(shù)據(jù)比對，找出差異值\h1.2.1兩列數(shù)據(jù)比對\h1.2.2多列數(shù)據(jù)比對\h1.3瞬間完成數(shù)據(jù)提取、數(shù)據(jù)合并\h1.3.1從文件編號中提取部門信息\h1.3.2從聯(lián)系方式中提取手機號\h1.3.3從身份證號碼中提取出生日期\h1.3.4批量合并多列信息\h1.3.5多列數(shù)據(jù)智能組合\h1.3.6智能合并地址信息\h1.4超級表，瞬間自動美化報表\h1.4.1讓報表自動隔行填充顏色\h1.4.2讓長報表的標題行自動置頂\h第02章輕點鼠標，批量導(dǎo)入數(shù)據(jù)\h2.1TXT文件中的數(shù)據(jù)，如何批量導(dǎo)入Excel\h2.1.1常規(guī)文本文件數(shù)據(jù)的導(dǎo)入\h2.1.2身份證號碼等長文本數(shù)據(jù)的導(dǎo)入\h2.1.3從十幾個文本字段中刪除部分字段再導(dǎo)入Excel\h2.1.4從字段中選擇性導(dǎo)入數(shù)據(jù)\h2.2網(wǎng)頁中的數(shù)據(jù)，如何批量導(dǎo)入Excel\h2.3圖片中的數(shù)據(jù)，如何快速導(dǎo)入Excel\h2.4數(shù)據(jù)庫文件中的數(shù)據(jù)，如何快速導(dǎo)入Excel\h第03章極速錄入法，讓你快20倍\h3.1手敲數(shù)字總出錯？一個妙招極速錄入超長數(shù)值\h3.2不想手動輸入長名稱？兩個妙招極速錄入超長文本\h3.3不想逐個添加前綴、后綴？這兩招都能批量完成\h3.4如何在多個單元格區(qū)域同時錄入數(shù)據(jù)\h3.5在多張工作表中同時錄入數(shù)據(jù)\h3.6將12張工作表中的數(shù)據(jù)快速匯總\h第04章數(shù)據(jù)管理，輕松清洗和轉(zhuǎn)換臟數(shù)據(jù)\h4.1系統(tǒng)導(dǎo)出的數(shù)據(jù)，如何快速清洗\h4.1.1刪除多余標題行\(zhòng)h4.1.2刪除多余空白行\(zhòng)h4.1.3刪除不可見字符\h4.2報表中的數(shù)字無法正常統(tǒng)計，如何批量轉(zhuǎn)換\h4.3整列的偽日期格式數(shù)據(jù)，如何批量轉(zhuǎn)換格式\h4.4整張報表格式錯亂，如何快速轉(zhuǎn)換為規(guī)范格式\h4.5使合并單元格變“乖”，批量拆分并智能填充數(shù)據(jù)\h第05章多表合并，處理散亂的數(shù)據(jù)\h5.1多表行合并，按條件將多表數(shù)據(jù)合并計算\h5.2多表列合并，按字段將多表數(shù)據(jù)合并計算\h5.3將100家分公司的數(shù)據(jù)合并到一張工作表中\(zhòng)h第06章表格美化術(shù)，提升數(shù)據(jù)說服力\h6.1別人做的表格為什么那么專業(yè)\h6.1.1行高、列寬調(diào)整\h6.1.2字體設(shè)置\h6.1.3字號設(shè)置\h6.1.4配色美化\h6.1.5邊框設(shè)置\h6.1.6數(shù)據(jù)可視化\h6.2雙欄表頭這樣做，表格清晰、干凈又漂亮\h6.3多欄表頭這樣做，輕松展示多維度數(shù)據(jù)\h6.4自動隱藏表格里的錯誤值，表格更清爽\h第07章這些經(jīng)典的函數(shù)用法，讓你攻無不克\h7.1邏輯判斷誰更強？讓表格按條件返回結(jié)果\h7.1.1單條件邏輯判斷：IF函數(shù)\h7.1.2雙條件邏輯判斷：IF+AND函數(shù)\h7.1.3多條件復(fù)雜邏輯判斷：IF+OR+AND函數(shù)\h7.1.4多層級按區(qū)間條件邏輯判斷：IF函數(shù)嵌套\h7.1.5多層級判斷公式的優(yōu)化：VLOOKUP函數(shù)+輔助列\(zhòng)h7.2VLOOKUP，職場必會的查找函數(shù)\h7.2.1按條件查詢單字段數(shù)據(jù)\h7.2.2按條件跨表查詢多字段數(shù)據(jù)\h7.2.3一個公式實現(xiàn)按條件跨表查詢11種字段數(shù)據(jù)\h7.3INDEX+MATCH函數(shù)組合，數(shù)據(jù)查詢最佳搭檔\h7.3.1INDEX函數(shù)基礎(chǔ)用法\h7.3.2MATCH函數(shù)基礎(chǔ)用法\h7.3.3INDEX+MATCH函數(shù)組合基礎(chǔ)用法\h7.3.4INDEX+MATCH函數(shù)組合的靈活使用\h7.45秒完成全年12張工作表的匯總\h7.5再難的條件匯總問題，都是小菜一碟\h7.5.1SUMIF函數(shù)基礎(chǔ)用法\h7.5.2單條件精確匹配匯總\h7.5.3單條件模糊匹配匯總\h7.5.4跨列條件精確匹配匯總\h7.5.5跨列條件模糊匹配匯總\h7.5.6SUMIFS函數(shù)基礎(chǔ)用法\h7.5.7多條件精確匹配匯總\h7.5.8多條件模糊匹配匯總\h7.6以一敵十的“萬能”函數(shù)：SUBTOTAL函數(shù)\h第08章數(shù)據(jù)可視化，讓你的報表“會說話”\h8.1帶選擇器的表頭，改表頭即自動更新整張報表\h8.2條形圖，讓數(shù)據(jù)和圖表渾然天成\h8.3圖標集，讓報表既清晰又美觀\h8.4迷你圖，輕松展示數(shù)據(jù)趨勢、對比、盈虧情況\h8.5智能可視化，按指令動態(tài)突出顯示目標記錄\h第09章數(shù)據(jù)透視表，海量數(shù)據(jù)匯總不用愁\h9.1上萬條數(shù)據(jù)，輕松實現(xiàn)分類匯總\h9.2僅需1分鐘將全年數(shù)據(jù)按季度、月份分類匯總\h9.3動態(tài)數(shù)據(jù)透視表，讓報表結(jié)果自動更新\h9.4這才是數(shù)據(jù)透視的真正奧秘，讓你指哪兒打哪兒\h9.5一鍵將總表拆分為多張分表，還能同步更新\h9.6一鍵植入選擇器，讓報表能夠交互動態(tài)更新\h第10章數(shù)據(jù)透視圖，數(shù)據(jù)匯總和圖表一舉兩得\h10.1讓你的數(shù)據(jù)分析能夠表格、圖表兼?zhèn)鋅h10.2一鍵美化數(shù)據(jù)透視圖\h10.3切片器，讓數(shù)據(jù)透視圖和數(shù)據(jù)透視表聯(lián)動更新\h第11章專業(yè)、大氣的商務(wù)圖表\h11.1查看數(shù)據(jù)趨勢，這張經(jīng)典的趨勢展現(xiàn)圖職場必備\h11.2查看數(shù)據(jù)對比，這兩種經(jīng)典圖表適用于各種場景\h11.2.1用柱形圖展示數(shù)據(jù)對比\h11.2.2用條形圖展示數(shù)據(jù)對比\h11.3查看數(shù)據(jù)占比，這兩種圖表輕松滿足各種要求\h11.3.1用餅圖展示數(shù)據(jù)占比\h11.3.2用圓環(huán)圖展示數(shù)據(jù)占比\h11.4高出鏡率的經(jīng)典組合圖，你也能輕松駕馭\h11.5錯誤匯報方式與正確匯報方式\h第12章數(shù)據(jù)看板，一頁展示多種數(shù)據(jù)\h12.1日報看板，自動完成數(shù)據(jù)計算、數(shù)據(jù)分析和展示\h12.1.1理解用戶需求\h12.1.2明確數(shù)據(jù)分析和展示要素，提煉核心指標\h12.1.3構(gòu)建數(shù)據(jù)分析看板布局\h12.1.4收集、整理、清洗、轉(zhuǎn)換原始數(shù)據(jù)\h12.1.5計算核心指標數(shù)據(jù)\h12.1.6根據(jù)需求插入動態(tài)圖表、數(shù)據(jù)可視化元素\h12.1.7整合并組織數(shù)據(jù)和圖表至看板中，調(diào)整配色并美化\h12.2周報看板，自動按選定期間進行數(shù)據(jù)分析和展示\h12.2.1理解用戶需求\h12.2.2明確數(shù)據(jù)分析和展示要素，提煉核心指標\h12.2.3構(gòu)建數(shù)據(jù)分析看板布局\h12.2.4收集、整理、清洗、轉(zhuǎn)換原始數(shù)據(jù)\h12.2.5計算核心指標數(shù)據(jù)\h12.2.6根據(jù)需求插入動態(tài)圖表、數(shù)據(jù)可視化元素\h12.2.7整合并組織數(shù)據(jù)和圖表至看板中，調(diào)整配色并美化\h12.3月報看板，用數(shù)據(jù)建模進行自動化數(shù)據(jù)分析和展示\h12.3.1理解用戶需求\h12.3.2明確數(shù)據(jù)分析和展示要素，提煉核心指標\h12.3.3構(gòu)建數(shù)據(jù)分析看板布局\h12.3.4收集、整理、清洗、轉(zhuǎn)換原始數(shù)據(jù)\h12.3.5計算核心指標數(shù)據(jù)\h12.3.6根據(jù)需求插入動態(tài)圖表、數(shù)據(jù)可視化元素\h12.3.7整合并組織數(shù)據(jù)和圖表至看板中，調(diào)整配色并美化\h第13章PowerBI，商務(wù)數(shù)據(jù)分析必備工具\h13.1PowerQuery超級查詢，智能提取、轉(zhuǎn)換、整合數(shù)據(jù)\h13.1.1提取數(shù)據(jù)\h13.1.2轉(zhuǎn)換數(shù)據(jù)\h13.1.3整合數(shù)據(jù)\h13.2PowerPivot超級數(shù)據(jù)透視表，智能數(shù)據(jù)分析、計算\h13.2.1PowerPivot加載方法\h13.2.2單表數(shù)據(jù)智能計算\h13.2.3多表數(shù)據(jù)智能分析\h第01章只按一鍵，告別煩瑣重復(fù)的操作身處大數(shù)據(jù)時代，數(shù)據(jù)已經(jīng)滲透到每個行業(yè)或領(lǐng)域，無論你從事什么工作，可能都會和數(shù)據(jù)打交道，需要完成數(shù)據(jù)匯總、數(shù)據(jù)比對、數(shù)據(jù)提取或者數(shù)據(jù)報表制作等工作。手動處理這類問題，不僅費時費力，而且容易出錯。其實Excel早就具備了對應(yīng)的功能，你只需按一次鍵，瞬間就可以完成這些工作！本章主要學(xué)習以下幾種常見場景中的快捷操作方法?！羲查g實現(xiàn)報表數(shù)據(jù)匯總◆快速進行數(shù)據(jù)比對◆瞬間完成數(shù)據(jù)提取、數(shù)據(jù)合并◆超級表，瞬間自動美化報表學(xué)完本章內(nèi)容，你在遇到相應(yīng)場景時能夠選擇最合適的方法，快速解決問題。\h1.1瞬間實現(xiàn)報表數(shù)據(jù)匯總?cè)粘９ぷ髦薪?jīng)常會遇到各種數(shù)據(jù)匯總的需求，有的人習慣用計算器計算，有的人習慣在Excel中編寫公式計算……其實很多數(shù)據(jù)匯總問題并不需要復(fù)雜的操作，有時只要掌握了正確的方法，僅需1秒即可完成。\h1.1.1多列數(shù)據(jù)瞬間匯總某公司主營4種商品，現(xiàn)要求匯總每種商品的銷售額（單位：萬元），如圖1-1所示。遇到這種對每列數(shù)據(jù)求和的問題，大多數(shù)人都是在匯總行中輸入SUM函數(shù)，對數(shù)據(jù)逐列求和。這樣做雖然也能得到正確結(jié)果，但比起下面這種方法，就顯得有點慢了。選中B2:E13單元格區(qū)域，然后按＜Alt+=＞組合鍵，瞬間完成對多列數(shù)據(jù)的匯總，如圖1-2所示。圖1-1圖1-2\h1.1.2多行數(shù)據(jù)瞬間匯總總公司想查看每個分公司的銷售情況，現(xiàn)要求匯總每個分公司中所有商品的銷售額，如圖1-3所示?，F(xiàn)在問題變成了對每行數(shù)據(jù)求和，同樣可以使用＜Alt+=＞組合鍵，瞬間得到匯總結(jié)果。圖1-3選中B2:F12單元格區(qū)域，然后按＜Alt+=＞組合鍵，瞬間完成對多行數(shù)據(jù)的匯總，如圖1-4所示。圖1-4雖然這種情況也可以通過在匯總列或行中輸入SUM函數(shù)來計算，但比起使用＜Alt+=＞組合鍵的方法，速度就慢很多了，所以推薦優(yōu)先使用組合鍵的操作。\h1.1.3一鍵實現(xiàn)分別按行、按列匯總數(shù)據(jù)本例要求在報表中每列下方匯總每種商品在所有分公司中的銷售額，在每行右側(cè)匯總每個分公司中所有商品的銷售額，在右下角匯總所有商品所有分公司的總銷售額，如圖1-5所示。現(xiàn)在問題升級為不但要對每列數(shù)據(jù)求和，而且要對每行數(shù)據(jù)求和，最后還要在右下角對整個區(qū)域的數(shù)據(jù)求和，這時如果手動輸入公式來計算會大費周章，但如果用下面的方法，瞬間即可完成以上所有計算。圖1-5選中B2:F13單元格區(qū)域，按＜Alt+=＞組合鍵，瞬間完成對多行、多列數(shù)據(jù)的匯總，如圖1-6所示。圖1-6在Excel中，選中數(shù)據(jù)區(qū)域后按＜Alt+=＞組合鍵，可以對數(shù)據(jù)進行求和，求和結(jié)果將顯示在數(shù)據(jù)區(qū)域的下方或右側(cè)。\h1.2快速進行數(shù)據(jù)比對，找出差異值日常工作中需要進行數(shù)據(jù)比對的場景很多，相應(yīng)的方法也很多，本節(jié)介紹一種經(jīng)典的數(shù)據(jù)比對方法。要想對數(shù)據(jù)進行差異比對，有時候僅需1秒就能完成，下面結(jié)合不同場景來介紹。\h1.2.1兩列數(shù)據(jù)比對在盤點庫存時，核對數(shù)據(jù)是經(jīng)常要做的工作。例如，要在庫存盤點報表中，以B列的賬存數(shù)為基準（基準列在左側(cè)），在C列的實盤數(shù)中標識出差異數(shù)據(jù)，如圖1-7所示。遇到這種情況，有的人會用肉眼逐行比對；有的人會做一個輔助列后將兩列數(shù)據(jù)相減，再篩選結(jié)果不為0（即存在差異）的數(shù)據(jù)。但這些都不是最合適的方法，用下面的方法進行比對僅需1秒即可完成。圖1-701選中B2:C20單元格區(qū)域，按＜Ctrl+\＞組合鍵，瞬間在C列定位差異數(shù)據(jù)所在的單元格，如圖1-8所示。圖1-802批量定位這些單元格之后，可以設(shè)置單元格背景顏色為黃色，使其更加醒目。這種方法不但適用于數(shù)值型數(shù)據(jù)的比對，而且同樣支持文本內(nèi)容的比對，十分方便。注意這里按的鍵是“\”而不是“/”。＜Ctrl+\＞組合鍵在金山WPS及微軟公司Excel2010以下版本中不支持。使用Excel2010以下版本的用戶，可以按＜Ctrl+G＞組合鍵，彈出“定位”對話框，然后按圖1-9所示步驟操作，代替＜Ctrl+\＞組合鍵的功能。圖1-9舉一反三本例中，若以C列的實盤數(shù)為基準（基準列在右側(cè)），在B列的賬存數(shù)中標識出差異數(shù)據(jù)，如圖1-10所示，該如何操作呢？先單擊C2單元格，按住鼠標左鍵不松開，向左下方拖曳鼠標指針，選中C2:B20單元格區(qū)域，然后按＜Ctrl+\＞組合鍵，即可瞬間在B列定位差異數(shù)據(jù)所在的單元格，如圖1-11所示。圖1-10圖1-11這個案例中雖然使用的組合鍵依然是＜Ctrl+\＞，但是要注意選中區(qū)域的順序，是從C2單元格開始選取的，這是和上個案例的重要區(qū)別。\h1.2.2多列數(shù)據(jù)比對兩列數(shù)據(jù)的比對很容易完成，如果有多列數(shù)據(jù)需要進行比對呢？例如，圖1-12所示的答題表中，要以B列的正確答案為基準（基準列在左側(cè)），找出每個學(xué)生（其他各列）答案有差異的單元格。01單擊B2單元格，從左上方向右下方拖曳鼠標指針，選中B2:G21單元格區(qū)域，按＜Ctrl+\＞組合鍵，如圖1-13所示，即可實現(xiàn)以左側(cè)的B列為基準列，瞬間定位右側(cè)所有的差異單元格，如圖1-14所示。圖1-12圖1-13圖1-1402設(shè)置單元格背景顏色為黃色，以突出顯示差異數(shù)據(jù)。舉一反三若以G列的正確答案為基準列（基準列在右側(cè)），找出其他各列中答案有差異的單元格，如圖1-15所示，該如何操作呢？圖1-15單擊G2單元格，從右上方向左下方拖曳鼠標指針，選中G2:B21單元格區(qū)域，按＜Ctrl+\＞組合鍵，如圖1-16所示，即可實現(xiàn)以右側(cè)的G列為基準列，瞬間定位左側(cè)所有差異單元格，如圖1-17所示。圖1-16圖1-17若以D列的正確答案為基準列（基準列在中間），找出其他各列中答案有差異的單元格，如圖1-18所示，該如何操作呢？圖1-18先以B2單元格作為活動單元格，從左上方向右下方拖曳鼠標指針，選中B2:G21單元格區(qū)域，再按兩次＜Tab＞鍵將選中區(qū)域中的活動單元格切換至D2單元格，然后按＜Ctrl+\＞組合鍵，如圖1-19所示，即可實現(xiàn)以中間的D列為基準列，瞬間定位左側(cè)和右側(cè)所有的差異單元格，如圖1-20所示。圖1-19圖1-20小結(jié)從以上幾個案例可以總結(jié)得出，選中單元格區(qū)域后將活動單元格移動至基準列，再按＜Ctrl+\＞組合鍵，即可瞬間實現(xiàn)差異比對。\h1.3瞬間完成數(shù)據(jù)提取、數(shù)據(jù)合并很多重復(fù)、煩瑣的工作都是由數(shù)據(jù)提取、數(shù)據(jù)合并這類問題造成的，在Excel早期版本中出現(xiàn)這類問題時需要使用函數(shù)和公式，甚至VBA編程來解決，從Excel2013開始，Excel新增的快速填充功能可以智能完成絕大多數(shù)工作中常見的數(shù)據(jù)提取和數(shù)據(jù)合并問題。下面介紹其具體用法，使你在學(xué)習之后，處理此類問題時能夠事半功倍！\h1.3.1從文件編號中提取部門信息圖1-21左圖所示的表格是一個文件記錄表，現(xiàn)在要從表格中的A列文件編號中提取所屬部門信息，并將其放置到C列。在C2單元格手動輸入“財務(wù)部”，然后按＜Ctrl+E＞組合鍵，即可將整個C列快速填充，如圖1-21所示。使用組合鍵之前在C2單元格手動輸入部門名稱，是為了給Excel做出一個示范，讓Excel知曉提取的規(guī)則和效果。如果遇到比較復(fù)雜的數(shù)據(jù)填充，僅輸入一個數(shù)據(jù)作為示范可能無法保證填充的準確性，這時可以手動輸入多個數(shù)據(jù)（一般不會超過4個），再按＜Ctrl+E＞組合鍵。圖1-21\h1.3.2從聯(lián)系方式中提取手機號有時我們拿到的通訊錄是圖1-22左圖所示的表格，人名和手機號存儲在同一列（A列），這時就要從A列提取手機號，得到圖1-22右圖所示的效果。在B2單元格手動輸入第一個手機號，然后按＜Ctrl+E＞組合鍵，即可將整個B列快速填充，如圖1-22所示。圖1-22使用快速填充功能時，要注意檢查結(jié)果的準確性。如果出現(xiàn)部分錯誤，可以在開始時多輸入幾個示范數(shù)據(jù)再使用＜Ctrl+E＞組合鍵。\h1.3.3從身份證號碼中提取出生日期從身份證號碼中提取代表出生日期的8位數(shù)字的方法也很簡單。在B2單元格手動輸入身份證號碼中代表出生日期的8位數(shù)字，然后按＜Ctrl+E＞組合鍵，即可將整個B列快速填充，如圖1-23所示。圖1-23\h1.3.4批量合并多列信息日常工作中，我們經(jīng)常會遇到將多列數(shù)據(jù)合并到一列的情況，如要將圖1-24左圖所示表格中的A、B、C三列數(shù)據(jù)合并在一起，并使用短橫線連接，該如何操作呢？在D2單元格手動輸入第一個示范數(shù)據(jù)，然后按＜Ctrl+E＞組合鍵，即可將整個D列快速填充，如圖1-24所示。圖1-24本案例中的連接符號也可以為其他符號，符號不會影響快速填充功能。\h1.3.5多列數(shù)據(jù)智能組合要求將圖1-25左圖所示表格中的A、B兩列數(shù)據(jù)智能組合，從A列中提取姓氏再與B列的職位組合，將結(jié)果放置在C列。在C2單元格手動輸入第一個示范數(shù)據(jù)“王經(jīng)理”，然后按＜Ctrl+E＞組合鍵，即可將整個C列快速填充，如圖1-25所示。圖1-25\h1.3.6智能合并地址信息要求將圖1-26左圖所示表格中的A、B、C三列的省、市、區(qū)信息智能合并為有效地址，在A列的省名稱后添加“省”，在B列的市名稱后添加“市”，在C列的區(qū)名稱后添加“區(qū)”，再全部合并。在D2單元格手動輸入第一個示范數(shù)據(jù)，然后按＜Ctrl+E＞組合鍵，即可將整個D列快速填充，如圖1-26所示。圖1-26\h1.4超級表，瞬間自動美化報表工作中使用的各種報表，不僅要做到數(shù)據(jù)準確，而且要盡量美觀、易讀，但完全不必花費過多的時間來美化報表，因為Excel早就為用戶準備了自動美化功能。\h1.4.1讓報表自動隔行填充顏色當報表包含的內(nèi)容較多時，為了避免閱讀報表的人看串行，可以為報表設(shè)置隔行填充顏色，以便于區(qū)分內(nèi)容。如對圖1-27所示的原始報表，可按如下步驟操作。圖1-2701將光標定位在報表中的任意一個單元格，按＜Ctrl+T＞組合鍵，彈出“創(chuàng)建表”對話框，單擊“確定”按鈕，如圖1-28所示。這樣即可瞬間讓報表實現(xiàn)隔行填充顏色，既美觀又快捷！02如果你對默認填充的顏色或樣式不滿意，還可以單擊“表格工具”下的“設(shè)計”選項卡，從表格樣式中選擇更豐富的樣式，如圖1-29所示。當然，也可以在現(xiàn)有表格樣式的基礎(chǔ)上進行自定義設(shè)置，直到令你滿意為止。圖1-28圖1-29\h1.4.2讓長報表的標題行自動置頂圖1-30所示為一張包含一萬條記錄的長報表，由于記錄太多，閱讀報表的人在瀏覽數(shù)據(jù)的過程中看不到頂部對應(yīng)的字段名稱，可能會導(dǎo)致對部分數(shù)據(jù)含義理解錯誤。怎樣才能讓報表的標題行始終置頂呢？選中報表中的任意一個單元格，按＜Ctrl+T＞組合鍵，在彈出的對話框中單擊“確定”按鈕，如圖1-31所示。圖1-30圖1-31這時再向下瀏覽報表，報表頂部始終顯示標題行字段信息，如圖1-32所示。圖1-32無論是自動隔行填充顏色，還是標題行自動置頂，都是借助＜Ctrl+T＞組合鍵將普通區(qū)域轉(zhuǎn)換為超級表實現(xiàn)的。提示如果想把超級表轉(zhuǎn)換為普通區(qū)域，可以按圖1-33所示步驟操作。圖1-33注意當超級表轉(zhuǎn)換為普通區(qū)域后，隔行填充顏色的效果不會消失，但是自動置頂標題行的效果會消失。\h第02章輕點鼠標，批量導(dǎo)入數(shù)據(jù)當今社會已經(jīng)進入大數(shù)據(jù)時代，我們幾乎每天都要從不同的渠道和來源獲取并處理數(shù)據(jù)，本章從以下幾個方面介紹批量導(dǎo)入數(shù)據(jù)的方法。◆TXT文件中的數(shù)據(jù)，如何批量導(dǎo)入Excel◆網(wǎng)頁中的數(shù)據(jù)，如何批量導(dǎo)入Excel◆圖片中的數(shù)據(jù)，如何快速導(dǎo)入Excel◆數(shù)據(jù)庫文件中的數(shù)據(jù)，如何快速導(dǎo)入Excel看似復(fù)雜的各種數(shù)據(jù)導(dǎo)入，無須掌握任何函數(shù)和編程知識，只需輕點幾下鼠標，即可讓數(shù)據(jù)乖乖導(dǎo)入Excel。\h2.1TXT文件中的數(shù)據(jù)，如何批量導(dǎo)入Excel在實際工作中，很多平臺和系統(tǒng)導(dǎo)出的數(shù)據(jù)都是TXT格式的，那么我們就從文本文件數(shù)據(jù)的導(dǎo)入開始介紹吧。為了能游刃有余地應(yīng)對各種情況，下面結(jié)合4個案例展開介紹。\h2.1.1常規(guī)文本文件數(shù)據(jù)的導(dǎo)入需要導(dǎo)入的文本文件如圖2-1所示。圖2-1要在Excel中導(dǎo)入文本文件中的數(shù)據(jù)，有兩種方法，一種是利用文本導(dǎo)入工具，另一種是借助PowerQuery工具，前者是Excel各個版本通用的方法，后者是Excel2016、Excel2019和Office365版本的內(nèi)置功能，如果使用的是Excel2013或Excel2010，需要從微軟公司官網(wǎng)下載并安裝PowerQuery插件。下面就這兩種方法，分別展開介紹?！龇椒ㄒ唬豪梦谋緦?dǎo)入工具導(dǎo)入在Excel2019版本中，文本導(dǎo)入工具位于“數(shù)據(jù)”選項卡下面的“獲取外部數(shù)據(jù)”組中，如圖2-2所示。我們可以調(diào)用此工具進行文本數(shù)據(jù)的導(dǎo)入，方法如下。圖2-201打開要放置文本數(shù)據(jù)的Excel工作簿，單擊A1單元格，然后單擊“數(shù)據(jù)”選項卡下的“自文本”按鈕，彈出“導(dǎo)入文本文件”對話框，選擇文本文件所在位置，單擊“導(dǎo)入”按鈕，如圖2-3所示。圖2-302在文本導(dǎo)入向?qū)У牡?步中，按圖2-4所示步驟操作。圖2-403進入文本導(dǎo)入向?qū)У牡?步，按圖2-5所示步驟操作。圖2-504進入文本導(dǎo)入向?qū)У牡?步，按圖2-6所示步驟操作。圖2-605完成文本導(dǎo)入的操作后，設(shè)置數(shù)據(jù)的放置位置，如圖2-7所示。圖2-706將數(shù)據(jù)導(dǎo)入Excel后的效果如圖2-8所示。圖2-8■方法二：借助PowerQuery工具導(dǎo)入。01單擊“數(shù)據(jù)”選項卡下的“新建查詢”按鈕→“從文件”→“從文本”，如圖2-9所示。圖2-902在彈出的對話框中選擇要導(dǎo)入的文本文件所在位置，單擊“打開”按鈕。03在彈出的PowerQuery導(dǎo)入界面中，按圖2-10所示步驟操作，加載數(shù)據(jù)。圖2-1004加載數(shù)據(jù)后的效果如圖2-11所示。圖2-11你會發(fā)現(xiàn)，Excel默認將數(shù)據(jù)創(chuàng)建為超級表而非普通區(qū)域。雖然以上兩種方法都可以導(dǎo)入文本文件中的數(shù)據(jù)，但是顯然方法二（借助PowerQuery導(dǎo)入數(shù)據(jù)）更加快捷。不僅如此，當文本文件中的數(shù)據(jù)變更或向其中追加新的數(shù)據(jù)時，使用方法二導(dǎo)入Excel中的結(jié)果還支持同步更新，僅需單擊“刷新”按鈕即可，如圖2-12所示。圖2-12小結(jié)推薦使用Excel2016、Excel2019或Office365版本的用戶優(yōu)先使用PowerQuery導(dǎo)入文本文件中的數(shù)據(jù)，低版本用戶使用方法一導(dǎo)入數(shù)據(jù)。\h2.1.2身份證號碼等長文本數(shù)據(jù)的導(dǎo)入除了常規(guī)的數(shù)據(jù)，實際工作中還可能遇到一些特殊數(shù)據(jù)，如身份證號碼或銀行賬號等位數(shù)較多的數(shù)字，這時如果還按照上一小節(jié)介紹的步驟導(dǎo)入，會導(dǎo)致部分數(shù)據(jù)丟失。下面結(jié)合一個案例說明關(guān)鍵步驟的設(shè)置方法。現(xiàn)在有大量18位數(shù)字的身份證號碼需要導(dǎo)入Excel，由于篇幅有限，僅展示前10行數(shù)據(jù)，如圖2-13所示（已對身份證號碼進行脫敏處理）。由于身份證號碼為18位數(shù)字，使用常規(guī)方法進行導(dǎo)入時，Excel默認只保留15位數(shù)字，這樣會導(dǎo)致所有身份證號碼的后3位數(shù)字變?yōu)?，如圖2-14所示。圖2-13圖2-14為了避免這種情況的發(fā)生，需要在導(dǎo)入數(shù)據(jù)時指定身份證號碼列按文本格式導(dǎo)入，下面分兩種方法介紹關(guān)鍵的設(shè)置步驟?！龇椒ㄒ唬豪梦谋緦?dǎo)入工具導(dǎo)入01參照2.1.1小節(jié)圖2-3~圖2-6所示的操作，在文本導(dǎo)入向?qū)У?步對應(yīng)的對話框中選中身份證號碼所在的列，將其設(shè)置為文本格式，單擊“完成”按鈕，如圖2-15所示。圖2-1502這樣設(shè)置后才能完整地導(dǎo)入身份證號碼，如圖2-16所示。圖2-16■方法二：借助PowerQuery工具導(dǎo)入01參照2.1.1小節(jié)圖2-9所示的操作，進入PowerQuery導(dǎo)入界面后，可見身份證號碼列的數(shù)字變?yōu)榭茖W(xué)記數(shù)法顯示，所以這時不能直接單擊“加載”按鈕，而要單擊“轉(zhuǎn)換數(shù)據(jù)”按鈕，如圖2-17所示。圖2-1702進入PowerQuery編輯器后，界面如圖2-18所示。圖2-1803選中身份證號碼所在的列，將其轉(zhuǎn)換為文本格式，如圖2-19所示。圖2-1904在彈出的對話框中單擊“替換當前轉(zhuǎn)換”按鈕，如圖2-20所示。圖2-2005轉(zhuǎn)換成功后，即可完整顯示18位身份證號碼，單擊“關(guān)閉并上載”按鈕，將PowerQuery中的轉(zhuǎn)換結(jié)果導(dǎo)入Excel中，如圖2-21所示。06將數(shù)據(jù)導(dǎo)入Excel中的結(jié)果如圖2-22所示。圖2-21圖2-22Excel中的這個結(jié)果同樣也是支持跟隨數(shù)據(jù)源刷新的，當文本文件中的數(shù)據(jù)源變動后，在Excel中單擊“設(shè)計”選項卡下的“刷新”按鈕即可同步更新。\h2.1.3從十幾個文本字段中刪除部分字段再導(dǎo)入Excel前面兩個案例都是將文本文件中所有字段數(shù)據(jù)導(dǎo)入Excel，實際工作中有時我們只需要數(shù)據(jù)中的一部分字段，所以可以從數(shù)據(jù)中刪除部分字段再導(dǎo)入。原始文本文件如圖2-23所示。其中的“退款額”和“退貨量”無須導(dǎo)入Excel。圖2-23下面依然分兩種方法展開介紹?！龇椒ㄒ唬豪梦谋緦?dǎo)入工具導(dǎo)入由于前面已經(jīng)介紹過文本導(dǎo)入工具，所以這里重復(fù)的步驟不贅述。01參照2.1.1小節(jié)圖2-3~圖2-6所示的操作，在文本導(dǎo)入向?qū)У?步對應(yīng)的對話框中，依次選中無須導(dǎo)入的字段所在的列，選中“不導(dǎo)入此列（跳過）”單選項，單擊“完成”按鈕，如圖2-24所示。圖2-2402這樣即可忽略無須導(dǎo)入的字段，將數(shù)據(jù)導(dǎo)入Excel中，如圖2-25所示。圖2-25■方法二：借助PowerQuery工具導(dǎo)入01在“數(shù)據(jù)”選項卡下單擊“從文本/CSV”按鈕，將文本文件中的數(shù)據(jù)導(dǎo)入PowerQuery。在PowerQuery導(dǎo)入界面單擊“轉(zhuǎn)換數(shù)據(jù)”按鈕，如圖2-26所示。圖2-2602在PowerQuery編輯器中，按住＜Ctrl＞鍵不松開并依次選中無須導(dǎo)入的兩列，單擊“刪除列”按鈕，如圖2-27所示。圖2-2703單擊“關(guān)閉并上載”按鈕將PowerQuery中的轉(zhuǎn)換結(jié)果導(dǎo)入Excel中，如圖2-28所示。圖2-2804Excel中的結(jié)果如圖2-29所示。圖2-29\h2.1.4從字段中選擇性導(dǎo)入數(shù)據(jù)當文本文件中需要刪除的字段太多時，我們可以僅選擇需要導(dǎo)入的字段進行導(dǎo)入。原始文件中包含幾十列數(shù)據(jù)，如圖2-30所示，僅需導(dǎo)入前面的從“日期”至“轉(zhuǎn)化率”的10個字段，后面的幾十個字段數(shù)據(jù)無須導(dǎo)入。圖2-30下面依然分兩種方法展開介紹?！龇椒ㄒ唬豪梦谋緦?dǎo)入工具導(dǎo)入01參照2.1.1小節(jié)圖2-3~圖2-6所示的操作，在文本導(dǎo)入向?qū)У?步對應(yīng)的對話框中（如圖2-31所示），先單擊“退款額”所在的列，再按住鼠標左鍵不松開并向右拖動底部的滾動條直至最后一列。圖2-3102按?。糞hift＞鍵不松開并單擊最后一列（“星期”字段所在的列），目的是選中從“退款額”至“星期”的連續(xù)幾十列，然后選中“不導(dǎo)入此列（跳過）”單選項，單擊“完成”按鈕，如圖2-32所示。圖2-3203這樣即可忽略無須導(dǎo)入的幾十列，僅導(dǎo)入有效數(shù)據(jù)，如圖2-33所示。圖2-33■方法二：借助PowerQuery工具導(dǎo)入01參照2.1.2小節(jié)圖2-9、圖2-10所示的操作，將數(shù)據(jù)導(dǎo)入PowerQuery編輯器后，按?。糞hift＞鍵不松開并依次單擊“日期”列和“轉(zhuǎn)化率”列，目的是選中這些需要導(dǎo)入的連續(xù)多列數(shù)據(jù)，然后單擊“刪除列”按鈕的下半部分，在彈出的下拉菜單中選擇“刪除其他列”，如圖2-34所示。圖2-3402在PowerQuery中轉(zhuǎn)換得到想要的結(jié)果后，單擊“關(guān)閉并上載”按鈕，如圖2-35所示。圖2-3503Excel中的效果如圖2-36所示。當然，借助PowerQuery導(dǎo)入的這些數(shù)據(jù)，可以借助“刷新”功能使之與數(shù)據(jù)源保持同步更新，這也是使用PowerQuery的極大優(yōu)勢所在。圖2-36\h2.2網(wǎng)頁中的數(shù)據(jù)，如何批量導(dǎo)入Excel當工作需要的數(shù)據(jù)來自網(wǎng)頁，并且網(wǎng)頁數(shù)據(jù)時常更新時，應(yīng)該如何操作才能把網(wǎng)頁中的數(shù)據(jù)導(dǎo)入Excel中，并且支持同步更新呢？下面結(jié)合外匯牌價查詢的實際案例，講解從網(wǎng)頁中抓取并更新數(shù)據(jù)的方法。某企業(yè)需要從某銀行網(wǎng)站的網(wǎng)頁中抓取各種貨幣的外匯牌價，網(wǎng)頁中的部分數(shù)據(jù)如圖2-37所示。圖2-3701首先復(fù)制網(wǎng)頁的網(wǎng)址，然后打開Excel，單擊“數(shù)據(jù)”選項卡→“自網(wǎng)站”，如圖2-38所示。02在彈出的對話框中粘貼網(wǎng)頁的網(wǎng)址，單擊“確定”按鈕，如圖2-39所示。圖2-38圖2-3903進入PowerQuery導(dǎo)航器后，選擇網(wǎng)頁中要導(dǎo)入的數(shù)據(jù)所在的表單Table0，單擊“轉(zhuǎn)換數(shù)據(jù)”按鈕，如圖2-40所示。圖2-4004將數(shù)據(jù)導(dǎo)入PowerQuery編輯器后，單擊“關(guān)閉并上載”按鈕，如圖2-41所示。圖2-4105當所有數(shù)據(jù)加載完成后，Excel中的數(shù)據(jù)如圖2-42所示。圖2-42這樣即可從網(wǎng)頁中抓取外匯牌價數(shù)據(jù)并導(dǎo)入Excel，當網(wǎng)頁數(shù)據(jù)更新后，只需單擊“表格工具”中的“刷新”按鈕，即可同步更新Excel中的數(shù)據(jù)。\h2.3圖片中的數(shù)據(jù)，如何快速導(dǎo)入Excel工作中常會遇到數(shù)據(jù)來自圖片的情況，這時候應(yīng)該如何操作才能將圖片中的數(shù)據(jù)批量導(dǎo)入Excel中，以便借助Excel的強大功能進行數(shù)據(jù)處理呢？當下比較常用的從圖片中提取數(shù)據(jù)的技術(shù)是OCR，OCR是OpticalCharacterRecognition（光學(xué)字符識別）的縮寫，是通過掃描等方式將各種票據(jù)、報刊、書籍、文稿及其他印刷品的文字轉(zhuǎn)化為圖像信息，再利用文字識別技術(shù)將圖像信息轉(zhuǎn)化為可以編輯的文字形式的輸入技術(shù)。OCR可應(yīng)用于大量文字資料、檔案卷宗、數(shù)據(jù)的批量錄入和處理，尤其適用于銀行、稅務(wù)等行業(yè)大量數(shù)據(jù)表格的自動掃描識別及長期存儲。當下市面上的OCR軟件有很多種，下面通過一個使用漢王OCR掃描圖片提取表格數(shù)據(jù)的案例，介紹OCR軟件的使用方法。圖2-43所示是一張某企業(yè)的招聘記錄表。01打開漢王OCR軟件，單擊“文件”→“打開圖像”，如圖2-44所示。圖2-43圖2-4402在彈出的對話框中選中要掃描的圖片，單擊“打開”按鈕，如圖2-45所示。圖2-4503在軟件中導(dǎo)入圖片后，為了提高識別的準確性，先進行版面分析，如圖2-46所示。圖2-4604經(jīng)過版面分析后單擊“識別”→“開始識別”，軟件會利用OCR技術(shù)識別下方的圖像信息，將其轉(zhuǎn)化為可使用的數(shù)據(jù)格式，如圖2-47所示。05將識別結(jié)果保存為Excel文件，操作步驟如圖2-48所示。圖2-47圖2-4806導(dǎo)入Excel中的效果如圖2-49所示。圖2-49至此，我們將圖片中的信息識別并轉(zhuǎn)化為Excel數(shù)據(jù)，便于后期的數(shù)據(jù)整理及統(tǒng)計。雖然其他OCR軟件的功能各不相同，但識別及轉(zhuǎn)化流程與此相似。需要說明的是，當圖片中包含的圖像信息較多或數(shù)據(jù)復(fù)雜時，免費的OCR軟件識別效果不盡如人意，可以使用付費OCR軟件進行精準識別，此處不贅述。\h2.4數(shù)據(jù)庫文件中的數(shù)據(jù)，如何快速導(dǎo)入Excel隨著大數(shù)據(jù)時代的來臨，數(shù)據(jù)的形態(tài)越來越多樣化，量級也日益增加，除了Excel文件中的數(shù)據(jù)，我們還經(jīng)常會遇到一些存儲著大量數(shù)據(jù)的數(shù)據(jù)庫文件，如Access數(shù)據(jù)庫文件。面對數(shù)萬行的數(shù)據(jù)庫文件記錄，很多人無從下手，下面通過一個案例介紹如何將數(shù)據(jù)庫文件中的大量數(shù)據(jù)快速導(dǎo)入Excel中。某企業(yè)2019年的全年銷售數(shù)據(jù)共計50000條記錄，對應(yīng)的數(shù)據(jù)庫文件如圖2-50所示。圖2-5001要將數(shù)據(jù)導(dǎo)入Excel中，首先打開要放置數(shù)據(jù)的Excel工作簿，然后按圖2-51所示的步驟獲取數(shù)據(jù)。02在彈出的“導(dǎo)入數(shù)據(jù)”對話框中選擇數(shù)據(jù)庫文件并導(dǎo)入Excel，如圖2-52所示。圖2-51圖2-5203Excel會啟動PowerQuery與數(shù)據(jù)源連接，導(dǎo)入過程會顯示圖2-53所示的提示。導(dǎo)入過程所耗費的時間與數(shù)據(jù)量大小、電腦硬件配置等相關(guān)。圖2-5304進入PowerQuery導(dǎo)航器后，按圖2-54所示步驟操作。圖2-5405數(shù)據(jù)導(dǎo)入PowerQuery編輯器后如圖2-55所示。這時我們要根據(jù)需求對數(shù)據(jù)進行整理及轉(zhuǎn)換，以符合后續(xù)的處理要求。圖2-5506當前案例的數(shù)據(jù)整理操作步驟如圖2-56所示。07將PowerQuery編輯器中的數(shù)據(jù)導(dǎo)入Excel，操作步驟如圖2-57所示。圖2-56圖2-5708導(dǎo)入Excel的效果如圖2-58所示。圖2-58值得一提的是，Excel中的數(shù)據(jù)可以與數(shù)據(jù)庫文件保持同步更新，這無疑是職場人的巨大福音。\h第03章極速錄入法，讓你快20倍在實際工作中，快捷、準確地錄入數(shù)據(jù)對后續(xù)的數(shù)據(jù)統(tǒng)計和數(shù)據(jù)分析具有非常重要的意義，本章從以下幾個方面介紹極速錄入數(shù)據(jù)的方法。◆手敲數(shù)字總出錯？一個妙招極速錄入超長數(shù)值◆不想手動輸入長名稱？兩個妙招極速錄入超長文本◆不想逐個添加前綴、后綴？這兩招都能批量完成◆如何在多個單元格區(qū)域同時錄入數(shù)據(jù)◆在多張工作表中同時錄入數(shù)據(jù)\h3.1手敲數(shù)字總出錯？一個妙招極速錄入超長數(shù)值在需要輸入尾數(shù)有很多0的數(shù)值時，很多人都是一邊數(shù)著0的數(shù)目一邊輸入，采用這樣的錄入方法不但速度慢，而且容易出錯。在Excel中可以借助科學(xué)記數(shù)法的原理極速錄入尾數(shù)有很多0的超長數(shù)值，如需要錄入一億，即數(shù)值100000000，在Excel中輸入“1**8”即可生成科學(xué)記數(shù)法形式的一億，即“1.00E+08”，它代表1乘以10的8次方，將其設(shè)置為常規(guī)格式即可轉(zhuǎn)換為“100000000”的形式。明白了這個原理，你就可以輕松、快捷地錄入各種超長數(shù)值了，如圖3-1所示。在Excel中，將數(shù)值格式由科學(xué)記數(shù)法的格式轉(zhuǎn)換為常規(guī)格式的組合鍵是＜Ctrl+Shift+1＞，當有大量超長數(shù)值采用科學(xué)記數(shù)法形式錄入完畢時，可以借助此組合鍵批量將其格式轉(zhuǎn)換為常規(guī)數(shù)值格式。圖3-1\h3.2不想手動輸入長名稱？兩個妙招極速錄入超長文本當工作中經(jīng)常需要輸入一些長名稱時，采用手動逐個輸入的方法不但速度慢，而且一不小心就可能出錯，更麻煩的是出了錯你還全然不知，導(dǎo)致后續(xù)出現(xiàn)更嚴重的錯誤?！龇椒ㄒ唬和ㄟ^下拉菜單輸入與其小心翼翼地手動輸入，不如將經(jīng)常要輸入的長名稱設(shè)置成Excel的下拉菜單，直接選擇即可錄入。某企業(yè)的工作要求經(jīng)常錄入一些合作單位的單位全稱，要求一字不差。在Excel中選中設(shè)置好下拉菜單的單元格后會顯示下拉按鈕，方便用戶從展開的下拉菜單中直接選擇內(nèi)容，如圖3-2所示。圖3-2下面介紹設(shè)置下拉菜單的具體操作步驟。01將合作單位的單位全稱整理到Excel中，如放置到C列，如圖3-3所示。圖3-302在Excel工作表里選中要錄入的區(qū)域（如A列），設(shè)置下拉菜單的操作步驟如圖3-4所示。圖3-4提示要輸入圖3-4中步驟5所示的內(nèi)容可這樣操作：將光標定位在“來源”文本框中，然后用鼠標指針框選工作表中的C2:C6單元格區(qū)域即可。03至此下拉菜單設(shè)置完畢，可以將C列隱藏起來使表格更加美觀，隱藏C列不會影響A列已設(shè)置好的下拉菜單，如圖3-5所示。這樣就可以在Excel中既快捷又準確地輸入超長單位名稱了，使用此方法的前提是在Excel工作表中預(yù)先輸入指定的單位名稱（如C列）。是否還有更快捷的方法呢？當然有，下面再介紹一種極速錄入法。圖3-5■方法二：借助自動更正輸入如現(xiàn)在想要輸入的單位名稱是“河北省石家莊市人生真正的價值就是為你所愛的人而努力打拼科技有限公司”，在Excel中單擊“文件”→“選項”，在“Excel選項”對話框中設(shè)置自動更正選項，如圖3-6所示。圖3-6圖3-6中步驟4中的“dd3”是快捷錄入時需要用戶手動輸入的文本，建議這個文本使用容易輸入且在工作中不與其他內(nèi)容重復(fù)的文本。提示要求“容易輸入”是為了方便用戶快捷輸入，如“dd3”都是左手中指鍵位，無須更換手指即可輕松輸入；要求“不與其他內(nèi)容重復(fù)”是為了避免誤替換，如僅使用“a”則會把所有出現(xiàn)“a”的位置全部替換為步驟5中的超長文本，造成誤替換。圖3-6中步驟5中的文本就是要輸入的單位全稱：河北省石家莊市人生真正的價值就是為你所愛的人而努力打拼科技有限公司。使用這種方法的好處在于，無須提前在Excel中輸入下拉菜單所需的數(shù)據(jù)，便可快速錄入超長文本，而且這種自動更正選項的設(shè)置不僅適用于Excel，而且適用于PowerPoint、Word、Outlook、Access、OneNote等所有Office組件，十分方便。\h3.3不想逐個添加前綴、后綴？這兩招都能批量完成當在工作中遇到需要在已有數(shù)據(jù)基礎(chǔ)上為其添加前綴或后綴的情況時，很多人會逐個在數(shù)據(jù)前后輸入前綴、后綴，這樣做費時費力且無法保證準確性。其實在Excel中是有方法批量添加前綴、后綴的，下面結(jié)合一個實際案例介紹兩種方法。某企業(yè)需要在已有的部門名稱基礎(chǔ)上增加品牌名的前綴信息，如圖3-7所示。圖3-7這時不必逐個手動添加，只需在C2單元格輸入以下公式：="跟李銳學(xué)Excel-"＆A2將公式向下填充即可批量添加品牌名前綴，如圖3-8所示。如果需要在源數(shù)據(jù)后面添加年份信息作為后綴，如圖3-9所示。圖3-8圖3-9同樣可以借助下面的Excel公式批量添加：=A2＆"-2020年"將公式向下填充后的效果如圖3-10所示。這樣即可在已有數(shù)據(jù)基礎(chǔ)上批量添加前綴或后綴。在實際工作中，當前綴或后綴需要經(jīng)常更新時，采用上面的方法需要經(jīng)常修改Excel公式，然后將公式重新填充到指定區(qū)域，為了避免這類重復(fù)操作，我們可以采用第二種方法。第二種方法的思路是，先將前綴或后綴信息放置到某個單元格中，然后在Excel公式中引用這個單元格，即可批量添加前綴或后綴。這樣設(shè)置的好處是，當需要更改前綴或后綴時，我們僅需要更改放置前綴或后綴信息的指定單元格，無須修改Excel工作表中的所有公式，就可以將所有公式結(jié)果同步更新了。圖3-10如果后綴信息需要經(jīng)常變動，可按下述方法設(shè)置。01在E2單元格中輸入后綴信息，如圖3-11所示。圖3-1102在C列的公式中引用放置了后綴信息的E2單元格，輸入公式如下：=A2＆$E$2注意公式中使用“$E$2”的目的是絕對引用E2單元格，使公式向下填充的過程中不會引用其他單元格。03將C2單元格的公式向下填充，效果如圖3-12所示。圖3-1204當需要變更后綴信息時，僅需要更改E2單元格中的內(nèi)容，無須更改C列公式即可使所有后綴批量同步更新，如圖3-13所示。圖3-13綜上，兩種方法都可以實現(xiàn)批量添加前綴或后綴的效果，大家可以根據(jù)工作中的實際需求進行選擇。\h3.4如何在多個單元格區(qū)域同時錄入數(shù)據(jù)當需要在多個單元格區(qū)域同時錄入數(shù)據(jù)時，可以使用批量錄入數(shù)據(jù)的方法。下面結(jié)合案例具體介紹。Excel中的B2:C3、B5:C7、E2:G3、E5:G7四個單元格區(qū)域互不相鄰，如圖3-14所示。要求在這四個單元格區(qū)域中都錄入品牌名“跟李銳學(xué)Excel”，應(yīng)該如何操作？圖3-1401選中B2:C3單元格區(qū)域，按住Ctrl鍵不松開，依次選中B5:C7、E2:G3、E5:G7三個單元格區(qū)域，如圖3-15所示。圖3-1502輸入“跟李銳學(xué)Excel”（注意不要按＜Enter＞鍵），如圖3-16所示。圖3-1603按＜Ctrl+Enter＞組合鍵即可輸入數(shù)據(jù)，效果如圖3-17所示。圖3-17＜Ctrl+Enter＞組合鍵的作用是在Excel中所有選中的單元格區(qū)域同時輸入數(shù)據(jù)，如果只按＜Enter＞鍵則只在當前單元格輸入數(shù)據(jù)。舉一反三此方法還可以用于批量輸入Excel函數(shù)。如要求將多個單元格區(qū)域中輸入1至100之間的隨機整數(shù)，應(yīng)該如何操作呢？前面選中多個單元格區(qū)域的操作不變，在輸入數(shù)據(jù)時改為輸入以下公式：=RANDBETWEEN(1,100)輸入公式后不要按＜Enter＞鍵，效果如圖3-18所示。圖3-18按＜Ctrl+Enter＞組合鍵輸入數(shù)據(jù)，效果如圖3-19所示。圖3-19由于RANDBETWEEN函數(shù)的作用是產(chǎn)生區(qū)間范圍內(nèi)的隨機整數(shù)，所以每次按＜F9＞鍵即可觸發(fā)Excel重新計算，可隨機生成1至100之間的隨機整數(shù)，如圖3-20所示。圖3-20\h3.5在多張工作表中同時錄入數(shù)據(jù)當需要在多張工作表中同時錄入數(shù)據(jù)時，我們可以先將要同時操作的多張工作表組合在一起，然后再輸入數(shù)據(jù)，這樣就可以一次性操作了。如要求在名為“北京”“上?！薄皬V州”的3張工作表中同時錄入報表表頭，可以這樣操作。01選中“北京”工作表標簽，按?。糞hift＞鍵不松開，單擊“上?！薄皬V州”工作表標簽，將“北京”“上?！薄皬V州”這3張工作表組成工作表組合，效果如圖3-21所示。圖3-2102錄入報表表頭數(shù)據(jù)，并設(shè)置好格式，如圖3-22所示。圖3-22由于是在工作表組合狀態(tài)下錄入表頭并設(shè)置格式的，所以這期間的所有操作都同步應(yīng)用于組合中的每一張工作表，這樣就一次性在“北京”“上?！薄皬V州”這3張工作表中批量錄入數(shù)據(jù)并設(shè)置格式，效果如圖3-23所示。圖3-23此案例中的3張工作表是連續(xù)放置的，所以按?。糞hift＞鍵并單擊首尾工作表標簽即可選中其間所有連續(xù)的工作表。如果工作表不是連續(xù)放置的，可以按?。糃trl＞鍵并分別單擊各個工作表標簽，將多張工作表組成工作表組合。\h3.6將12張工作表中的數(shù)據(jù)快速匯總將多張工作表組合，不僅可以實現(xiàn)批量錄入數(shù)據(jù)、設(shè)置格式，而且可以實現(xiàn)多張工作表批量匯總計算，下面結(jié)合一個案例介紹具體方法。某企業(yè)全年12個月的銷售數(shù)據(jù)分別放置在名為“1”至“12”的工作表中，如圖3-24所示（為了清晰查看，僅展示1月和12月工作表，其余月份的表結(jié)構(gòu)一致，僅數(shù)據(jù)不同）。圖3-24每張工作表中都已存放各個分公司與商品的銷售數(shù)據(jù)，需要分別對其進行求和，以完成如下3項要求：①對每個分公司所有商品的銷售數(shù)據(jù)求和，匯總結(jié)果放置在F列；②對每種商品所有分公司的銷售數(shù)據(jù)求和，匯總結(jié)果放置在13行；③對所有分公司所有商品的銷售數(shù)據(jù)求總和，結(jié)果放置在F13單元格。要想實現(xiàn)12張工作表所有數(shù)據(jù)按以上要求一次性批量匯總，需按如下步驟操作。01按住＜Shift＞鍵不松開，分別單擊“1”和“12”工作表標簽，將全年數(shù)據(jù)所在的12張工作表組合成工作表組合，然后選中B2:F13單元格區(qū)域，如圖3-25所示。圖3-2502按＜Alt+=＞組合鍵，一次性完成12張工作表的數(shù)據(jù)快速求和，效果如圖3-26所示。圖3-2603這時，從“1”至“12”的全部12張工作表都實現(xiàn)了行列快速求和，在工作表標簽上單擊鼠標右鍵，取消組合工作表，如圖3-27所示。圖3-27分別查看各張工作表，即可看到所有匯總的結(jié)果已全部完成。由于工作表數(shù)量較多，這里僅展示12月工作表的效果，如圖3-28所示。圖3-28綜上，我們可以在工作中根據(jù)不同情況，選擇最合適的方法，在多個單元格區(qū)域、多張工作表中同時錄入數(shù)據(jù)，同時計算數(shù)據(jù)，避免重復(fù)操作，大幅提高工作效率。\h第04章數(shù)據(jù)管理，輕松清洗和轉(zhuǎn)換臟數(shù)據(jù)我們在工作中接觸的數(shù)據(jù)不但來源渠道廣泛，而且結(jié)構(gòu)、格式各不相同，所以需要將各種不規(guī)范的數(shù)據(jù)轉(zhuǎn)換、整理為規(guī)范的數(shù)據(jù)。這個過程不但會影響工作效率，數(shù)據(jù)的準確性也會直接影響后續(xù)的數(shù)據(jù)統(tǒng)計和數(shù)據(jù)分析結(jié)果。為了讓大家掌握科學(xué)的方法，本章從以下幾個方面介紹數(shù)據(jù)管理和數(shù)據(jù)轉(zhuǎn)換技術(shù)。◆系統(tǒng)導(dǎo)出的數(shù)據(jù)，如何快速清洗◆報表中的數(shù)字無法正常統(tǒng)計，如何批量轉(zhuǎn)換◆整列的偽日期格式數(shù)據(jù)，如何批量轉(zhuǎn)換格式◆整張報表格式錯亂，如何快速轉(zhuǎn)換為規(guī)范格式◆使合并單元格變“乖”，批量拆分并智能填充數(shù)據(jù)\h4.1系統(tǒng)導(dǎo)出的數(shù)據(jù)，如何快速清洗進入大數(shù)據(jù)時代后，我們工作中需要分析的數(shù)據(jù)，80%以上的數(shù)據(jù)都是各種軟件或系統(tǒng)導(dǎo)出的，其中有些系統(tǒng)導(dǎo)出的數(shù)據(jù)中經(jīng)常會包含臟數(shù)據(jù)，需要經(jīng)過數(shù)據(jù)清洗后才能使用。本節(jié)結(jié)合以下3種工作中最常出現(xiàn)的臟數(shù)據(jù)情況，介紹經(jīng)典的數(shù)據(jù)清洗方法。①原始數(shù)據(jù)中包含多余標題行。②原始數(shù)據(jù)中包含多余空白行。③原始數(shù)據(jù)中包含不可見字符。\h4.1.1刪除多余標題行某企業(yè)從系統(tǒng)導(dǎo)出的2019年全年銷售數(shù)據(jù)如圖4-1所示。圖4-1要想刪除其中多余的標題行，只保留第一個標題行，應(yīng)該如何操作才最快捷呢？01任選其中一個標題行中的單元格（如A1單元格），單擊鼠標右鍵，然后按照圖4-2所示步驟操作。圖4-202這樣即可將所有的標題行全部篩選出來，如圖4-3所示。圖4-303選中第二個標題行所在的整行，如圖4-4所示，然后按＜Ctrl+Shift+↓＞組合鍵向下選中所有的標題行。圖4-404單擊鼠標右鍵，選擇“刪除行”，刪除多余的標題行，如圖4-5所示。圖4-505當多余的標題行被刪除之后，在篩選狀態(tài)下全選所有數(shù)據(jù)，如圖4-6所示。最終得到規(guī)范的表格，如圖4-7所示。圖4-6圖4-7\h4.1.2刪除多余空白行從系統(tǒng)中導(dǎo)出的原始數(shù)據(jù)中除了包含多余標題行，還可能包含多余空白行，如圖4-8所示。由于Excel的篩選功能默認是按照連續(xù)區(qū)域篩選的，空白行會將原始報表分割為多個連續(xù)區(qū)域，所以如果這時還按上一案例的方法操作，就無法篩選出所有空白行了。我們換一種方法刪除多余的空白行。圖4-801首先選擇數(shù)據(jù)類別比較少的一列（如B列），按＜Ctrl+Shift+L＞組合鍵，使該列數(shù)據(jù)處于篩選狀態(tài)，如圖4-9所示。圖4-902單擊B1單元格右側(cè)的篩選按鈕，僅選中“（空白）”復(fù)選框，如圖4-10所示。圖4-1003單擊“確定”按鈕，即可篩選出所有空白行，如圖4-11所示。圖4-1104選中第一個空白行，按＜Ctrl+Shift+↓＞組合鍵向下選中所有空白行，如圖4-12所示。圖4-1205單擊鼠標右鍵，選擇“刪除行”，刪除所有空白行，如圖4-13所示。圖4-1306單擊B1單元格右側(cè)的篩選按鈕，取消報表的篩選狀態(tài)，如圖4-14所示。圖4-14最終得到規(guī)范的數(shù)據(jù)報表，如圖4-15所示。圖4-15\h4.1.3刪除不可見字符除了包含多余標題行和多余空白行的情況，還會遇到包含不可見字符（如空格）的原始數(shù)據(jù)，下面結(jié)合案例講解正確的清洗方法。某企業(yè)從系統(tǒng)導(dǎo)出的招聘信息包含不可見字符，造成格式錯亂，如圖4-16所示。在Excel里選中數(shù)據(jù)所在的單元格，從編輯欄中逐個字符選中，即可看到不可見字符，如圖4-17所示。雖然單元格中的空格不影響Excel表格的顯示效果，但是對于Excel來說，“李銳”與“李銳”是不同的，所以應(yīng)將表格中多余的空格刪除。圖4-16圖4-1701選中空格，然后按＜Ctrl+C＞組合鍵將其復(fù)制。02下面借助Excel的替換功能批量清除空格。按＜Ctrl+H＞組合鍵，彈出“查找和替換”對話框，在“查找內(nèi)容”文本框中按＜Ctrl+V＞組合鍵，將剛才復(fù)制的空格粘貼在此處，“替換為”文本框保持為空，單擊“全部替換”按鈕將空格全部替換為空，如圖4-18所示。經(jīng)過替換操作，Excel會將剛才復(fù)制的空格全部刪除，如圖4-19所示。圖4-18用相同的方法繼續(xù)從編輯欄復(fù)制其他類型的不可見字符，將其替換為空，最后得到規(guī)范的數(shù)據(jù)報表，如圖4-20所示。圖4-19圖4-20\h4.2報表中的數(shù)字無法正常統(tǒng)計，如何批量轉(zhuǎn)換有的數(shù)據(jù)看起來明明是數(shù)字，但是使用SUM函數(shù)匯總得到的結(jié)果卻是0，如圖4-21所示。這是因為這些數(shù)字是文本格式的，使用SUM函數(shù)計算的結(jié)果當然為0了。在進行數(shù)據(jù)統(tǒng)計和數(shù)據(jù)分析之前，需要先將文本格式的數(shù)字轉(zhuǎn)換為數(shù)值格式，下面介紹兩種方法。圖4-21■方法一：直接轉(zhuǎn)換為數(shù)字01選中數(shù)字所在的單元格區(qū)域，此時在單元格區(qū)域的左側(cè)出現(xiàn)提示符號，單擊這個符號，然后選擇“轉(zhuǎn)換為數(shù)字”，如圖4-22所示。圖4-2202將文本格式的數(shù)字轉(zhuǎn)換為數(shù)值格式后，公式結(jié)果恢復(fù)正常，如圖4-23所示。這種方法雖然操作簡單，但是Excel的轉(zhuǎn)換方式是先行后列逐個轉(zhuǎn)換，當需要轉(zhuǎn)換的文本格式數(shù)字較多時，需要耗費的轉(zhuǎn)換時間較長，所以當遇到較多文本格式數(shù)字需要轉(zhuǎn)換時，建議使用下面的方法進行批量轉(zhuǎn)換。圖4-23■方法二：借助選擇性粘貼功能01首先復(fù)制任意空白單元格（如H1單元格），然后選中文本格式數(shù)字所在單元格區(qū)域并單擊鼠標右鍵，選擇“選擇性粘貼”，如圖4-24所示。圖4-2402彈出“選擇性粘貼”對話框，選中“數(shù)值”和“加”單選項，然后單擊“確定”按鈕，如圖4-25所示。圖4-25操作完成后的效果如圖4-26所示。圖4-26采用方法二進行轉(zhuǎn)換很快捷，即使遇到大量文本格式數(shù)字也可以瞬間完成轉(zhuǎn)換，推薦大家優(yōu)先使用這種方法。\h4.3整列的偽日期格式數(shù)據(jù)，如何批量轉(zhuǎn)換格式無論哪種工作崗位，都會經(jīng)常使用日期格式的數(shù)據(jù)，但由于很多原始數(shù)據(jù)中的日期格式錯亂，給后續(xù)的數(shù)據(jù)處理帶來一系列麻煩，如造成無法使用Excel函數(shù)公式計算、無法使用數(shù)據(jù)透視表組合等，所以掌握正確的日期轉(zhuǎn)換方法至關(guān)重要。下面結(jié)合一個案例，介紹批量轉(zhuǎn)換不規(guī)范的日期為規(guī)范日期格式的方法。圖4-27在原始數(shù)據(jù)中A列中的日期部是不規(guī)范格式的日期，如圖4-27所示。在Excel中規(guī)范的日期格式是“2019/6/16”或者“2019-6-16”的形式。通過觀察可以發(fā)現(xiàn)，數(shù)據(jù)中的日期情況各異，這時采用手動修改的方式逐一修改比較麻煩，建議按照下面介紹的方法批量修改。01選中日期所在列（如A列），然后按照圖4-28所示步驟操作。圖4-2802在對話框中單擊“下一步”按鈕兩次后，進入文本分列向?qū)У牡?步，然后按圖4-29所示步驟操作。圖4-29完成后的效果如圖4-30所示。借助Excel的分列工具，可以快捷地對整列日期進行批量轉(zhuǎn)換十分便利。圖4-30\h4.4整張報表格式錯亂，如何快速轉(zhuǎn)換為規(guī)范格式如果數(shù)據(jù)報表的格式錯亂，手動修改格式不僅效率低，還可能產(chǎn)生誤操作而引起數(shù)據(jù)偏差，所以這一節(jié)介紹快速規(guī)范報表格式的方法。某企業(yè)的原始數(shù)據(jù)報表（左側(cè)）和期望的規(guī)范格式（右側(cè)）如圖4-31所示。圖4-31下面借助Excel組合鍵來快速規(guī)范數(shù)據(jù)格式?！襁x中A列，按＜Ctrl+Shift+`＞組合鍵，將數(shù)據(jù)轉(zhuǎn)換為常規(guī)格式?！襁x中B列，按＜Ctrl+Shift+1＞組合鍵，將數(shù)據(jù)轉(zhuǎn)換為千位分隔符格式；如果數(shù)據(jù)含有小數(shù)，則會將小數(shù)四舍五入只保留整數(shù)位?！襁x中C列，按＜Ctrl+Shift+2＞組合鍵，將數(shù)據(jù)轉(zhuǎn)換為自定義格式中的h:mm時間格式。●選中D列，按＜Ctrl+Shift+3＞組合鍵，將數(shù)據(jù)轉(zhuǎn)換為規(guī)范的日期格式?！襁x中E列，按＜Ctrl+Shift+4＞組合鍵，將數(shù)據(jù)轉(zhuǎn)換為貨幣格式?！襁x中F列，按＜Ctrl+Shift+5＞組合鍵，將數(shù)據(jù)轉(zhuǎn)換為百分比格式。這些都是Excel中常用的格式轉(zhuǎn)換組合鍵，不必死記硬背，這是有規(guī)律的，都是＜Ctrl＞鍵和＜Shift＞鍵與＜Esc＞鍵下方的一排按鍵組合而成的。再遇到報表格式錯亂時，推薦使用以上Excel組合鍵完成轉(zhuǎn)換，單手操作即可完成，既方便又快捷。\h4.5使合并單元格變“乖”，批量拆分并智能填充數(shù)據(jù)Excel中的合并單元格不但會影響數(shù)據(jù)的正常排序、篩選，而且會給用Excel公式計算、制作數(shù)據(jù)透視表帶來一系列麻煩。在實際工作中，我們不可避免地會遇到包含合并單元格的數(shù)據(jù)報表，這時需要先將合并單元格取消合并，填好數(shù)據(jù)再進行后續(xù)操作。下面結(jié)合一個實際案例，介紹批量拆分合并單元格并智能填充數(shù)據(jù)的方法。某企業(yè)的原始數(shù)據(jù)報表中包含很多合并單元格，如圖4-32左側(cè)所示。先要將合并的單元格拆分，并根據(jù)實際情況填充數(shù)據(jù)，得到圖4-32右側(cè)所示的表格，操作方法如下。01選中包含合并單元格的區(qū)域（A2:D15單元格區(qū)域），按圖4-33所示步驟操作。圖4-32圖4-3302取消合并后，按＜F5＞鍵（有的筆記本電腦需要同時按＜Fn＞鍵），彈出“定位”對話框，然后單擊“定位條件”按鈕，如圖4-34所示。圖4-3403彈出“定位條件”對話框，然后按圖4-35所示步驟操作。圖4-3504定位空值后的效果如圖4-36所示。05當前活動單元格為C3，在編輯欄中輸入公式“=C2”（即引用活動單元格上方的單元格），這時不要按＜Enter＞鍵，如圖4-37所示。圖4-36圖4-3706按＜Ctrl+Enter＞組合鍵批量填充公式，效果如圖4-38所示。圖4-38此時雖然完成了合并單元格的拆分并批量填充好了數(shù)據(jù)，但是表格中有很多結(jié)果是公式生成的，為了避免后續(xù)操作過程中造成公式誤引用，建議清除所有公式，將公式結(jié)果保存為實際值。07選中A2:D15單元格區(qū)域，按＜Ctrl+C＞組合鍵復(fù)制數(shù)據(jù)，然后單擊鼠標右鍵，選擇粘貼選項中的“值”，如圖4-39所示。這樣就可以批量清除所有公式，同時將公式結(jié)果保存為實際值，效果如圖4-40所示。圖4-39圖4-40掌握這種方法，就可以快速處理包含合并單元格的表格了。不過還是建議在Excel中盡量少用合并單元格，特別是表格中的數(shù)據(jù)后繼可能要進行數(shù)據(jù)統(tǒng)計、匯總等操作。\h第05章多表合并，處理散亂的數(shù)據(jù)我們在工作中經(jīng)常遇到的表格大多是記錄某一屬性的，要么是某個期間，要么是某個區(qū)域，當需要將所有期間或全部區(qū)域的數(shù)據(jù)合并在一起分析時，面對分散在各處的多個表格，很多人都會束手無策。為了讓大家能夠順利解決這類問題，根據(jù)表格字段結(jié)構(gòu)和所處位置的不同，本章從以下幾個方面介紹在Excel中進行多表合并的方法和相關(guān)工具?！舳啾硇泻喜?，按條件將多表數(shù)據(jù)合并計算◆多表列合并，按字段將多表數(shù)據(jù)合并計算◆將100家分公司的數(shù)據(jù)合并到一張工作表中\(zhòng)h5.1多表行合并，按條件將多表數(shù)據(jù)合并計算當遇到需要將多個表格的行記錄合并在一起的時候，采用手動復(fù)制粘貼的方法會帶來很多重復(fù)、煩瑣的操作，而且容易出錯，這時候我們可以使用Excel中的合并計算工具快速完成操作。某企業(yè)不同月份的銷售記錄分散在不同表格中，如圖5-1中工作表A列、B列所示，為了后續(xù)統(tǒng)一進行數(shù)據(jù)分析，需要將所有表格合并在一起，如圖5-1中工作表D列、E列所示。01在使用Excel的合并計算工具之前，首先選中要放置結(jié)果的單元格（如D1單元格），然后進行后續(xù)操作，如圖5-2所示。圖5-1圖5-202在彈出的“合并計算”對話框中依次將A1:B4、A6:B10、A12:B15單元格區(qū)域中的數(shù)據(jù)添加到“所有引用位置”列表框中。首先將光標定位在“引用位置”文本框中，然后選中A1:B4單元格區(qū)域，單擊“添加”按鈕進行添加，如圖5-3所示。03仿照上一步的操作，將A6:B10、A12:B15單元格區(qū)域的數(shù)據(jù)全部添加到“所有引用位置”列表框中，設(shè)置標簽位置，如圖5-4所示。圖5-3圖5-404將所有表格合并以后的效果如圖5-5所示，可以看到D列顯示的是數(shù)值，而非日期。05在D1單元格輸入字段名稱“日期”，將D列的數(shù)字格式設(shè)置為“日期”，對D1、E1單元格進行對齊方式等的設(shè)置，效果如圖5-6所示。圖5-5圖5-6這樣就完成了多個表格行記錄數(shù)據(jù)的合并。此案例中的3個表格中，第2列的字段都是“金額”，所以在合并計算時將多表數(shù)據(jù)放置在同一列下。當多個表格中第2列的字段名稱不同時，合并計算工具會按字段進行多列數(shù)據(jù)合并，5.2節(jié)具體介紹相關(guān)內(nèi)容。\h5.2多表列合并，按字段將多表數(shù)據(jù)合并計算當需要將多個表格的列數(shù)據(jù)按行合并在一起的時候，我們依然可以借助Excel中的合并計算工具快速操作。某企業(yè)不同商品的銷售數(shù)據(jù)分散在不同表格中，如圖5-7中工作表A列、B列所示，為了后續(xù)統(tǒng)一進行數(shù)據(jù)分析，需要將所有商品的銷售數(shù)據(jù)合并在一起，如圖5-7中工作表D列~G列所示。01在Excel中使用合并計算工具，將A列、B列的3個單元格區(qū)域中的數(shù)據(jù)合并，操作步驟如圖5-8所示。圖5-8中步驟5的操作可參照5.1節(jié)內(nèi)容。圖5-7圖5-802操作完成以后，效果如圖5-9所示。圖5-903在D1單元格輸入字段名稱“日期”，設(shè)置D列的數(shù)字格式為“日期”，對D1~G1單元格進行對齊方式等的設(shè)置，效果如圖5-10所示。這樣就實現(xiàn)了同一張工作表中的多表不同字段的數(shù)據(jù)合并，即使需要合并的表格不在同一張工作表中，也可以利用合并計算進行合并，但僅適用于包含兩個字段的表格。所以，采用合并計算合并多表的方法適用于字段數(shù)量較少的多表合并，當表格中字段較多時，我們可以換一種方法進行處理，下一節(jié)具體介紹。圖5-10\h5.3將100家分公司的數(shù)據(jù)合并到一張工作表中當需要將包含較多字段的復(fù)雜表格合并時，我們可以借助Excel中的PowerQuery工具批量完成。PowerQuery在Excel2019、Excel2016和Office365版本中都是內(nèi)置功能，可以直接使用，Excel2013和Excel2010版本需要從微軟公司官網(wǎng)下載并安裝插件后才能使用。下面結(jié)合一個實際案例，介紹使用PowerQuery進行多表合并的操作方法。某集團按地區(qū)劃分有100家分公司，各家分公司的銷售數(shù)據(jù)分散放置在100張工作表中，每張工作表中存放1000條銷售數(shù)據(jù)。集團為了進行銷售數(shù)據(jù)分析，要求將所有分公司對應(yīng)的100張工作表中的數(shù)據(jù)合并到一起，總共10萬條。每家分公司的表格結(jié)構(gòu)一致，以北京分公司為例展示，北京市銷售數(shù)據(jù)表如圖5-11所示。圖5-11在此Excel工作簿文件中還包含另外99家分公司的數(shù)據(jù)，一共是100張工作表，如圖5-12所示。圖5-1201打開包含集團100家分公司銷售數(shù)據(jù)的工作簿，按照圖5-13所示步驟操作。02彈出“導(dǎo)入數(shù)據(jù)”對話框，選中數(shù)據(jù)所在的工作簿，單擊“導(dǎo)入”按鈕，如圖5-14所示。03彈出PowerQuery導(dǎo)航器，選中工作簿，單擊“轉(zhuǎn)換數(shù)據(jù)”按鈕，如圖5-15所示。圖5-13圖5-14圖5-1504將數(shù)據(jù)導(dǎo)入PowerQuery編輯器后，效果如圖5-16所示。圖5-1605由于需要合并的數(shù)據(jù)都在“Data”列中，所以應(yīng)該刪除其他列，操作步驟如圖5-17所示。圖5-1706單擊字段“Data”右側(cè)的擴展按鈕，將數(shù)據(jù)表展開，操作步驟如圖5-18所示。07這樣即可將100張工作表中的數(shù)據(jù)全部放在PowerQuery編輯器中展示，效果如圖5-19所示。下面需要將“城市”“日期”等字段所在的第一行設(shè)置為標題行。圖5-18圖5-1908單擊“將第一行用作標題”按鈕，如圖5-20所示。由于此界面中的數(shù)據(jù)是由100張工作表合并而來的，每張工作表中都有一行標題行，所以這里面包含99行多余的標題行，可以按以下方法將重復(fù)的標題行批量清除。圖5-2009為了方便操作，首先選擇一個下方分類項目較少的字段（如“渠道”），使其處于篩選狀態(tài)后單擊“加載更多”按鈕，如圖5-21所示。圖5-2110取消選中“渠道”復(fù)選框，如圖5-22所示。這樣就批量清除了所有的多余的標題行，得到了從100張工作表中合并的10萬條數(shù)據(jù)。圖5-2211將PowerQuery編輯器中的多表合并結(jié)果導(dǎo)入Excel，如圖5-23所示。圖5-2310萬條數(shù)據(jù)導(dǎo)入Excel僅需幾秒，效果如圖5-24所示。圖5-24綜上，借助PowerQuery工具即可輕松完成100張工作表中的10萬條數(shù)據(jù)合并。\h第06章表格美化術(shù)，提升數(shù)據(jù)說服力專業(yè)的表格不僅要數(shù)據(jù)準確，還要美觀、易讀，這樣不但可以提升工作成果的品質(zhì)，有效體現(xiàn)專業(yè)性，而且在一些正式的匯報、展示的場合，能極大提升數(shù)據(jù)的說服力。本章從以下幾方面講解Excel中的表格美化技術(shù)，讓表格秒變“高大上”?！魟e人做的表格為什么那么專業(yè)◆雙欄表頭這樣做，表格清晰、干凈又漂亮◆多欄表頭這樣做，輕松展示多維度數(shù)據(jù)◆自動隱藏表格里的錯誤值，表格更清爽\h6.1別人做的表格為什么那么專業(yè)同樣一個表格，有的人做出來的平淡無奇，有的人做出來的就專業(yè)、美觀，到底差別是什么呢？下面結(jié)合一個實際案例，解析表格美化的玄機所在。某企業(yè)全年產(chǎn)品銷售的原始報表如圖6-1所示。圖6-1這個表格在經(jīng)過簡單美化之后，效果如圖6-2所示。圖6-2可見同一個表格，美化前后的視覺效果差異是非常大的。雖然表格美化的過程很簡單，但是要按照正確的方法進行能事半功倍，下面介紹具體的表格美化步驟。\h6.1.1行高、列寬調(diào)整首先要調(diào)整表格中數(shù)據(jù)的間隙。原始的報表中數(shù)字密密麻麻一大片，容易給人帶來窒息的感覺。合理的表格間隙，能夠給人呼吸舒暢、舒服的感覺。這點可以通過表格行高、列寬的調(diào)整來實現(xiàn)。在調(diào)整表格行高、列寬時，還要根據(jù)表格展示的實際場合而定，普遍來說，表格展示時的場地越大、人數(shù)越多，表格間隙要求隨之增大，反之隨之減小。就當前案例來說，這個表格主要在使用計算機辦公時查閱，所以將行高從15.6調(diào)整為20就可以了。01單擊Excel工作表左上角的全選按鈕，單擊鼠標右鍵，彈出快捷菜單，選擇“行高”，在彈出的對話框中輸入行高，如圖6-3所示。02選中C列至O列，設(shè)置列寬為5，操作步驟如圖6-4所示。圖6-3圖6-4設(shè)置好行高、列寬之后，表格中的數(shù)據(jù)間隙更加合理，效果如圖6-5所示。圖6-5調(diào)整好表格的行高、列寬之后，我們還要設(shè)置合適的字體。\h6.1.2字體設(shè)置表格中的字體可以影響數(shù)據(jù)的清晰度，不合理的字體會使表格中的數(shù)據(jù)看起來模糊不清，尤其是在打印或投影后。選擇合理的字體對體現(xiàn)表格的專業(yè)性至關(guān)重要。字體按照在必要的結(jié)構(gòu)性筆畫之外是否有裝飾，可分為襯線字體和無襯線字體，有裝飾的叫襯線字體，無裝飾的叫無襯線字體。為了使表格更加專業(yè)、內(nèi)容更加清晰，建議使用無襯線字體。常用的無襯線字體有微軟雅黑、黑體、Arial等，建議將表格中的中文數(shù)據(jù)設(shè)置為微軟雅黑字體，英文和數(shù)字數(shù)據(jù)設(shè)置為ArialUnicodeMS字體。本案例中的表格包含中文數(shù)據(jù)和數(shù)字數(shù)據(jù)，應(yīng)該分別設(shè)置合適的字體，將中文數(shù)據(jù)設(shè)置為微軟雅黑字體，數(shù)字數(shù)據(jù)設(shè)置為ArialUnicodeMS字體，效果如圖6-6所示。圖6-6設(shè)置好字體之后，還要進行字號設(shè)置。\h6.1.3字號設(shè)置設(shè)置合理的字號能夠讓表格中的數(shù)據(jù)看起來更加清晰、易讀，字號是根據(jù)表格的展示需求而定的，在一般的辦公中，建議將表格中的字號設(shè)置為9至12號。為了使表格的重點突出，標題行和匯總行除了字號略大之外，還可以設(shè)置加粗顯示，如圖6-7所示。圖6-7設(shè)置好字體、字號之后，還要對表格進行配色美化。\h6.1.4配色美化適當?shù)呐渖阑丝梢蕴嵘砀竦囊鬃x性，還可以使表格看起來更加專業(yè)、美觀。本案例表格中的產(chǎn)品數(shù)量較多，為了避免讀表人在查閱數(shù)字時看串行，可以用淺色隔行填充數(shù)據(jù)行。為了突出表格的標題行及字段名稱，可以用深色背景和白色字體配合顯示。01選中表格標題行，設(shè)置背景顏色的RGB值為2、79、108，操作步驟如圖6-8所示。圖6-802將標題行字體的顏色設(shè)置為白色，效果如圖6-9所示。圖6-903將表格第3行的填充顏色設(shè)置為淺色，操作步驟如圖6-10所示。圖6-1004要將整個表格隔行填充顏色，可以借助格式刷快速完成，方法：選中B3:O4單元格區(qū)域，單擊“格式刷”按鈕，如圖6-11所示。圖6-1105按住鼠標左鍵不松開，拖曳鼠標指針選中B5:O14單元格區(qū)域，如圖6-12所示。批量刷新格式后，得到的效果如圖6-13所示。圖6-12圖6-13完成對表格的配色美化之后，還需要繼續(xù)完善邊框設(shè)置。\h6.1.5邊框設(shè)置設(shè)置表格中的邊框是為了區(qū)分數(shù)據(jù)區(qū)域，提升表格可讀性。在表格經(jīng)過行高和列寬的合理調(diào)整、字體與字號設(shè)置以及配色美化之后，不再需要設(shè)置全部邊框線，僅使用必要的邊框進行區(qū)域劃分即可。01選中整個表格，清除所有邊框線，操作步驟如圖6-14所示。圖6-1402僅對表格的標題行下方、匯總行上方、首列右側(cè)設(shè)置邊框線，效果如圖6-15所示。設(shè)置好表格邊框之后，還可以借助數(shù)據(jù)可視化技術(shù)提升表格展示效果。圖6-15\h6.1.6數(shù)據(jù)可視化一個專業(yè)的表格除了要干凈、美觀，還要能夠傳達制表人的展示目的，也就是為什么要做這個表格、表格中的數(shù)據(jù)重點在哪里、想突出表達什么觀點。這些內(nèi)