第1章各類數據的獲取Accesstoallkindsofdata全套可編輯PPT課件1.1快速輸入各類數據1.2批量導入各類數據1.3智能提取各類數據1.4智能合并各類數據1.5一鍵匯總數據1.1快速輸入各類數據Quicklyentervarioustypesofdata實例1快速輸入超長數字在Excel中經常需要輸入超長數字，當輸入的數字位數為11位以內時，數字在單元格中完整顯示;當輸入的數字位數超過11位時，Excel自動用科學計數法表示。例如，輸入12位數字135782945708，則顯示為1.35783E+11，代表1.35783×1011。若將科學計數法表示的數字“1.35783E+11”轉換為常規數字格式“135,782,945,708”，按快捷鍵Ctrl＋Shift＋1即可實現轉換，如圖所示。借助此快捷鍵可將大量科學計數法表示的數字批量轉換為常規數值格式。實例2快速輸入超長文本在在工作中若要重復輸入多個超長文本，如圖所示的各商品名稱(B2:B9)，手工逐個輸入，既費時又容易出現錯誤，可通過以下3種方法快速輸入超長文本。實例2快速輸入超長文本方法1：利用下拉菜單選擇輸入首先將包含超長文本的單元格設置為下拉菜單，如圖所示。單擊單元格右側的下拉按鈕，打開的下拉菜單中，直接選擇即可輸入所需的文本。設置下拉菜單及選擇輸入內容的方法如下：實例2快速輸入超長文本步驟1：將各商品名稱預先輸入到Excel中，如D2:D9區域，如圖所示。實例2快速輸入超長文本步驟2：選中B2:B9區域，設置下拉菜單的操作步驟如左圖所示。步驟3：下拉菜單設置結束后，為了使表格美觀易讀，可將D列數據隱藏，B列已設置完成的下拉菜單效果如右圖所示。實例2快速輸入超長文本步驟4：單擊單元格右側下拉按鈕，打開的下拉菜單中即可便捷的選擇要輸入的超長商品名稱。使用該方法的前提是首先預先輸入指定的商品名稱(如D列)，然后將要輸入超長文本的單元格區域(如B列)設置為下拉菜單，即可在下拉菜單中快捷的選擇要輸入的內容。方法2：利用自動更正功能輸入若要重復輸入某個超長文本，如商品名稱“DellIns15CR-4528B15.6英寸筆記本電腦”，可利用自動更正功能將容易輸入的某一或某幾個字母、數字等設置為要輸入的商品名稱，設置結束后，在工作表中輸入已設置的字母或數字即可輸入商品名稱，操作步驟如下：實例2快速輸入超長文本步驟1：利用自動更正功能將某一字母(例如b)設置為商品名稱“DellIns15CR-4528B15.6英寸筆記本電腦”，設置方法如圖所示。(注：輸入的字母或數字不能與工作表中的其他內容重復，以免誤替換。)實例2快速輸入超長文本步驟2：設置結束后，在工作表中輸入字母b，按Enter鍵，即可輸入商品名稱“DellIns15CR-4528B15.6英寸筆記本電腦”，如圖所示。方法3：利用替換功能輸入在工作表中先輸入某一或某幾個字母、數字等代替超長文本(例如商品名稱)，輸入結束后，利用替換功能將該字母或數字替換為超長文本即可，操作步驟如下：步驟1：在需要輸入超長文本的單元格中輸入字母b(該字母不能與工作表中的其他內容重復，以免誤換)實例2快速輸入超長文本步驟2：按快捷鍵Ctrl＋H，打開“查找和替換”對話框，在“替換”選項卡中，進行如圖所示的設置，即可將工作表中的所有字母b替換為超長文本“DellIns15CR-4528B15.6英寸筆記本電腦”。實例3批量添加前綴和后綴若為已有的數據批量添加前綴或后綴，如圖所示批量添加前綴的效果，可使用文本連接符號&將前綴或后綴與已有數據進行連接，實現批量添加前綴或后綴的效果。操作方法如下：實例3批量添加前綴和后綴1.批量添加前綴方法1：前綴直接連接已有數據步驟1：在C2單元格中輸入公式：="研發部-"&A2，如圖所示，按Enter鍵確認輸入。步驟2：鼠標指向C2單元格右下角的填充柄，按住鼠標左鍵拖動填充柄至C15單元格，即可批量添加前綴。步驟3：若要更改前綴信息，需要更改C2單元格中的公式，然后拖動填充柄至指定位置即可。實例3批量添加前綴和后綴方法2：將前綴指定在某一單元格中。步驟1：在E2單元格中輸入前綴信息：研發部-，如左圖所示。步驟2：在C2單元格中輸入公式：=$E$2&A2，如右圖所示。公式中的$E$2是絕對引用，其作用是當拖動填充柄向下填充時，公式中的$E$2單元格地址始終保持不變。實例3批量添加前綴和后綴步驟3：按Enter鍵確認輸入，鼠標指向C2單元格右下角的填充柄，按住鼠標左鍵拖動填充柄至C15單元格，即可批量添加前綴，效果如左圖所示。步驟4：若要更改前綴信息，只需更改E2單元格中的信息，C列所有前綴信息自動批量同步更新，如右圖所示。實例3批量添加前綴和后綴綜上，兩種方法都可以實現批量添加前綴的效果，區別是：第一種方法修改前綴需要修改公式，再將公式填充到指定位置；第二種方法修改前綴只需修改放置前綴信息的指定單元格，即可同步更新公式中的所有前綴。因此，若要經常修改前綴，采用第二種方法更為便捷。實例3批量添加前綴和后綴2.批量添加后綴批量添加后綴方法與添加前綴方法相似，可按照添加前綴的方法添加后綴，方法如下：方法1：用已有數據直接連接后綴步驟1：在C2單元格中輸入公式：=A2&"-博士"，如圖所示，按Enter鍵確認輸入。實例3批量添加前綴和后綴步驟2：鼠標指向C2單元格右下角的填充柄，按住鼠標左鍵拖動填充柄至C15單元格，即可批量添加后綴，效果如圖所示。步驟3：若要更改后綴信息，需要更改C2單元格中的公式，然后拖動填充柄至指定位置即可。實例3批量添加前綴和后綴方法2：將后綴指定在某一單元格中步驟1：單擊E2單元格，將其設置為文本格式，然后在E2單元格中輸入后綴信息：-博士，如左圖所示。步驟2：在C2單元格中輸入公式：=A2&$E$2，如右圖所示。公式中的$E$2是絕對引用，其作用是當拖動填充柄向下填充時，公式中的$E$2單元格地址始終保持不變。實例3批量添加前綴和后綴步驟3：按Enter鍵確認輸入，鼠標指向C2單元格右下角的填充柄，按住鼠標左鍵拖動填充柄至C15單元格，即可批量添加后綴，效果如圖所示。步驟4：若要更改后綴信息，只需更改E2單元格中的信息，C列所有后綴信息批量同步更新。實例4輸入以0開頭的編號在實際工作中經常會輸入以0開始的編號，如001、002……。此類編號，不能直接在單元格中輸入001，否則，按Enter鍵確認后顯示為1，如圖所示。解決這個問題的途徑可通過設置“自定義”單元格格式將輸入的001顯示為001，設置步驟如下：實例4輸入以0開頭的編號步驟1：選中A列并右鍵單擊，彈出的快捷菜單中，選擇“設置單元格格式”命令，如左圖所示。步驟2：彈出“設置單元格格式”對話框，在“數字”選項卡中進行如右圖所示的設置。(注：在步驟3中，將光標定位在“類型”文本框中，刪除默認格式“G/通用格式”，并輸入“001”。)實例4輸入以0開頭的編號步驟3：在A3單元格中輸入001，按住Crtl鍵拖動填充柄至A11，即可輸入以0開始的編號，效果如圖中的A列所示。實例5輸入各種常用符號在Excel中輸入數據時，經常需要輸入一些常用符號，如√、×、※、≠、≥、±、℃等。輸入這些常用符號有多種方法，下面介紹一種快捷的輸入方法，即通過輸入法(以搜狗輸入法為例)輸入常用符號。如圖所示是一些常用的符號，使用輸入法輸入這些符號的方法如下：實例5輸入各種常用符號步驟1：將光標定位在B3單元格中，使用搜狗輸入法輸入A3單元格中的符號名稱“人民幣”的拼音，如圖所示，第5個選項即是“人民幣”對應的符號“￥”。步驟2：單擊符號“￥”或按數字5鍵，即可將該符號輸入到B3單元格中。步驟3：按照步驟1~2，使用輸入法依次輸入其他符號，并保存在相應的單元格中。實例6在多個單元格中同時輸入數據若要在多個連續或不連續的單元格中同時輸入相同數據時，可以使用批量輸入數據的方法快速輸入數據。例如，在如左圖所示的不連續的單元格區域A2:B3、D2:E3、C5:D7中同時輸入相同內容“Execl數據分析”，輸入方法如下：步驟1：選中A2:B3單元格區域，按住Ctrl鍵，依次選中D2:E3、C5:D7兩個單元格區域，如右圖所示。實例6在多個單元格中同時輸入數據步驟2：輸入“Execl數據分析”(注意此時不要按Ente鍵)，如上圖所示。步驟3：按Ctrl+Enter鍵，則選中的3個不連續的單元格區域中同時輸入了相同內容“Execl數據分析”，如下圖所示。實例7在多張工作表中同時輸入數據若要在多張工作表中同時輸入數據，首先選中要同時輸入數據的多張工作表，然后再輸入數據，即可一次性的為選中的多張工作表同時輸入數據。例如，如圖所示的工作簿包含了4張工作表，現在要在3張工作表“北區”、“南區”、“西區”中同時輸入列標題：“圖書名稱”、“單價”、“銷量”，并進行相應格式的設置。操作步驟如下：實例7在多張工作表中同時輸入數據步驟1：單擊“北區”工作表標簽，按住Shift鍵，再單擊“西區”工作表標簽，將3張工作表同時選中，此時工作簿標題欄的文件名后會出現“[工作組]”字樣，表示將3張工作表組合為一個組，如左圖所示。步驟2：輸入列標題，并設置其格式，如中間圖所示。因為3張工作表在一個組中，在這組中的任何操作都會同步應用到該組的每一張工作表，因此一次性在3張工作表“北區”、“南區”、“西區”中同時輸入內容和設置了相同格式，效果如右圖所示。實例7在多張工作表中同時輸入數據步驟3：在工作表標簽上右鍵單擊，彈出的快捷菜單中選擇“取消組合工作表”命令，取消已組合的工作表。此例中3張工作表是連續的，所以按住Shift鍵，單擊首尾工作表標簽，選中了連續的3張工作表。若要選中不連續的多張工作表，先單擊某張工作表標簽，然后按住Ctrl鍵，再分別單擊要選中的工作表標簽即可。1.2批量導入各類數據Importvarioustypesofdatainbatches實例8導入TXT文件中的數據TXT文件主要是從平臺或系統導出的數據，如圖所示。下面以將“學生檔案.txt”文本文件導入“成績”工作簿“Sheet1”工作表中為例，介紹將TXT文件中的數據導入Excel中的方法，操作步驟如下。實例8導入TXT文件中的數據步驟1：打開“成績”工作簿，在“Sheet1”工作表中單擊用于存放數據的起始單元格A1。步驟2：打開“數據”選項卡，進行如左圖所示的設置。步驟3：彈出“文本導入向導-第1步”對話框，進行如右圖所示的設置。實例8導入TXT文件中的數據步驟4：在“文本導入向導-第2步”對話框中，進行如左圖所示的設置。步驟5：在“文本導入向導-第3步”對話框中，進行如右圖所示的設置。實例9導入數據庫中的數據當工作中需要的數據來自于數據庫中的幾十條甚至幾萬條數據時，面對大量的數據，應該如何操作才能把數據庫中的數據導入Excel中，并且保持同步更新呢？下面通過一個實例講解將數據庫文件中大量數據批量導入Excel中的方法。如圖所示“停車場收費表”是Access數據庫中的部分數據，該數據庫共計2000多條記錄，將該數據庫中的數據批量導入Excel中方法如下：實例8導入數據庫中的數據方法1：通過獲取外部數據導入步驟1：打開要導入數據的Excel工作簿，選中A1單元格，單擊“數據”選項卡中的“自Access”按鈕，彈出“選取數據源”對話框，進行如左圖所示的設置。步驟2：在“導入數據”對話框中進行如右圖所示的設置，即可將Access數據庫文件-“停車場收費”中的數據批量導入Excel中。實例9導入數據庫中的數據步驟3：當數據庫中的數據更新后，只需單擊“表格工具””“設計”選項卡中的“刷新”按鈕或按快捷鍵Alt＋F5，如圖所示，即可同步更新Excel中的數據。實例9導入數據庫中的數據方法2：通過查詢功能導入數據步驟1：打開要導入數據的Excel工作簿，選中A1單元格，單擊“數據”選項卡中的“新建查詢”按鈕，打開的下拉列表中進行如左圖所示的設置。步驟2：彈出“導入數據”對話框，，選擇要導入的數據庫文件，單擊“導入”按鈕，如右圖所示。實例9導入數據庫中的數據步驟3：Excel啟動查詢與數據源連接，連接過程中彈出如左圖所示的提示框。數據量越大，連接過程所需要的時間越長。步驟4：在“導航器”對話框中，進行如右圖所示的設置。實例9導入數據庫中的數據步驟5：在“查詢編輯器”窗口中，如左圖所示，需要對數據格式進行轉換以符合后續數據處理要求，如選中“進場日期”列，將其設置為“日期”格式，按照相同的方法，依次將“進場時間”列設置為“時間”格式、“出場日期”列設置為“日期”格式、“出場時間”列設置為“時間”格式、“停放時間”列設置為“時間”格式，更改格式后的效果如右圖所示。實例9導入數據庫中的數據步驟6：單擊“關閉并上載”按鈕，將“查詢編輯器”窗口中轉換的結果導入Excel中，如圖所示。步驟7：當數據庫中的數據更新后，只需單擊“表格工具”“設計”選項卡中的“刷新”按鈕或按快捷鍵Alt＋F5，即可同步更新Excel中的數據。實例10從多個字段中導入部分字段前兩個實例是將TXT文本文件或數據庫文件中的所有字段數據批量導入Excel中，如果只導入文件中部分字段數據，可將文件中多余的字段刪除后再導入。如圖所示“商貿銷售統計.txt”是TXT文本文件，將文件中的“序號”、“商品代碼”、“品牌”、“銷量”字段導入Excel中，其余無須導入，導入方法有2種。實例10從多個字段中導入部分字段方法1：通過獲取外部數據導入部分字段步驟1：打開要導入數據的Excel工作簿，A1單元格，單擊“數據”選項卡中的“自文本”按鈕，彈出的對話框中選擇要導入的文本文件，如圖所示。步驟2：在文本導入向導第1步和第2步對話框中選擇默認設置，依次單擊“下一步”按鈕。實例10從多個字段中導入部分字段步驟3：在文本導入向導第3步對話框中，依次選中無須導入的字段所在列，然后選中“不導入此列（跳過）”單選按鈕，單擊“完成”按鈕，如左圖所示。步驟4：彈出的對話框中設置數據的放置位置，單擊“確定”按鈕，即可跳過無須導入的列，將需要列的數據導入到Excel中，如右圖所示。實例10從多個字段中導入部分字段方法2：通過查詢功能導入部分字段步驟1：打開要導入數據的Excel工作簿，選中A1單元格，單擊“數據”選項卡中的“新建查詢”按鈕，打開的下拉列表中進行如左圖所示的設置。步驟2：彈出的對話框中選擇要導入的TXT文本文件“商貿銷售統計”，單擊“導入”按鈕，進入“查詢編輯器”對話框，按住Ctrl鍵依次選中無須導入的3列，單擊“刪除列”按鈕，如右圖所示。實例10從多個字段中導入部分字段步驟3：單擊“關閉并上載”按鈕，如左圖所示，將查詢編輯器中的數據導入Excel中，效果如右圖所示。1.3智能提取各類數據Intelligentextractionofallkindsofdata實例11從登記表中提取姓名在下圖中左側表格是員工登記表，現在需要從登記表中的A列提取姓名信息，并將其放置在C列。操作方法：首先在C2單元格中輸入第一個員工的姓名“王一涵”，然后按快捷鍵Ctrl＋E，即可將其他員工的姓名填充到C列對應位置，如右圖表格所示。在C2單元格先輸入姓名后使用快捷鍵的原因是：先給Excel做出一個示范，明確提取規則，然后再按照規則使用快捷鍵進行一鍵填充。如果填充的數據較復雜，為了保證填充數據的準確性，可以多輸入幾個數據(一般不超過4個)作為示范規則，再使用快捷鍵Ctrl＋E填充即可。實例12從身份證號碼中提取出生日期下圖中左側表格A列表示身份證號碼，其中紅色數字(8位)代表出生日期，將出生日期提取出來并放置在B列對應單元格中。方法：首先在B2單元格中輸入第一個出生日期“19910902”，然后按快捷鍵Ctrl＋E，即可從A列批量提取出生日期填充到B列對應的單元格中。實例13提取姓名和地點信息并分行顯示如圖所示的表格中，A列每個單元格的內容較長，為了方便排版，將A列中內容提取并分兩行顯示，一行為報銷人姓名，另一行為活動地點信息，如圖所示中的B列所示。操作步驟如下：實例13提取姓名和地點信息并分行顯示步驟1：將A2單元格中的內容復制粘貼到B2單元格，如圖所示。實例13提取姓名和地點信息并分行顯示步驟2：將光標放置在“活動地點”前，按快捷鍵Alt＋Enter，將報銷人和活動地點分行顯示，如圖所示。步驟3：按快捷鍵Ctrl＋E，即可批量提取A列中的姓名和活動地點信息并換行顯示在B列對應的單元格中。1.4智能合并各類數據Intelligentlymergealltypesofdata實例14合并市、區、街道多列信息將圖左側表格中的A、B、C三列的市、區、街道進行合并，在C列街道名稱的后面添加“街道”，再進行全部合并，合并效果如圖右側表格所示。合并方法：在D2單元格輸入“南京市玄武區紅山街道”，然后按快捷鍵Ctrl＋E，即可將A、B、C三列批量合并并填充到D列對應的單元格中。的單元格中。實例15合并姓名和職位信息在左圖中，將A列姓氏與B列職務組合，將結果存放在C列，組合后的效果如右圖所示。組合方法：首先在C2單元格中輸入張院長，然后按快捷鍵Ctrl＋E即可將A列姓氏與B列職務組合并填充到C列對應的單元格中。1.5一鍵匯總數據Summarydatainoneclick實例16一鍵匯總多列數據某電力公司各銷售員1-4月銷售某種商品的情況如左圖所示，現要求匯總每個月銷售總量，即對每列數據進行求和。此類求和問題，可以在匯總行中使用SUM函數先求出B列數據的總和，然后再拖動填充柄至E列求出其他各列總和。這種方法雖然正確，但操作相對麻煩，更為便捷的匯總方法是使用快捷鍵Alt＋=進行一鍵匯總。操作方法如下：選中B2:E14單元格區域，如左圖所示，按快捷鍵Alt＋=，即可瞬間完成對多列數據的匯總，如右圖所示。實例17一鍵匯總多行數據某電力公司要查看各銷售員銷售情況，要求匯總出每位銷售員1-4月總銷售量，如左圖所示，即對每行數據進行求和，同樣可以使用快捷鍵Alt＋=，瞬間得到匯總結果。選中B2:G13單元格區域，按快捷鍵Alt＋=，即可瞬間完成對多行數據的匯總，如右圖所示。實例18一鍵匯總多行和多列數據在如圖所示的某商品的銷售表中，要求同時對3部分數據進行匯總：一是在每列下方匯總出每個月銷售總量；二是在每行的右側匯總出每位銷售員1-4月總銷售量；三是在右下角單元格中匯總出所有銷售員的總銷售量。本例既要對每列數據求和，還要對每行數據求和，最后還要在右下角單元格中對整個區域的數據求和。盡管是對3部分數據分別求和，同樣可以使用快捷鍵Alt＋=，瞬間完成對多行、多列、整個區域數據的匯總，相比于使用SUM函數逐個匯總更便捷更高效。實例18一鍵匯總多行和多列數據方法：選中B2:F14單元格區域，按快捷鍵Alt＋=，瞬間得到多行、多列、整個區域數據的匯總結果。如圖所示。實例19一鍵匯總多表多行和多列數據某電力公司各銷售員1-12月銷售某商品的數據分別存放在Sheet1至Sheet3工作表中，如圖所示。每張工作表中存放的是各銷售員與某商品的銷售數據，現要求對3張工作表中的數據進行匯總：一是在每列下方匯總出每個月銷售總量，匯總結果存放在第14行；二是在每行的右側匯總出每位銷售員1-4月總銷售量，匯總結果存放在F列；三是在右下角單元格中匯總出所有銷售員的總銷售量，結果存放在F14單元格。實例19一鍵匯總多表多行和多列數據步驟1：單擊Sheet1工作表標簽，按住Shift鍵，再單擊Sheet3工作表標簽，此時，3張工作表Sheet1、Sheet2、Sheet3同時被并組合為一個組，然后選中B2:F14單元格區域，如左圖所示。步驟2：按快捷鍵Alt＋=，一次性完成對3張工作表數據的快速匯總，此時，3張工作表Sheet1、Sheet2、Sheet3都完成了行、列及整個區域的數據匯總，如右圖所示。實例19一鍵匯總多表多行和多列數據步驟3：單擊任意一個工作表標簽，取消工作表的組合。步驟4：單擊工作表標簽即可看到該工作表的匯總結果，下圖是工作表Sheet2匯總結果。綜上，使用快捷鍵Alt＋=可以對一表或多表中選中的多列、多行、整個數據區域瞬間匯總，極大的簡化了匯總操作，使匯總更為便捷和高效，所以在進行匯總操作時優先推薦使用快捷鍵Alt＋=。第2章數據的清洗及美化Datacleaningandbeautification2.1數據的清洗2.2數據美化2.1數據清洗Datacleaning實例20批量刪除多余的行從系統中導出的數據經常出現多余的行，包括多余的標題行和空白行。多余的行影響數據統計和分析，需要將其刪除，刪除方法如下：1.刪除多余的標題行從系統導出的員工登記表如圖所示，其中有3個標題行，要求只保留第一個標題行，其余多余的2個標題行刪除，操作步驟如下：實例20批量刪除多余的行步驟1：在任意標題行上右鍵單擊，彈出的快捷菜單中選擇“篩選”|“按所選單元格值篩選”命令，如左圖所示，篩選出所有的標題行，如右圖所示。實例20批量刪除多余的行步驟2：選中第2個和第3個標題行，在選中的標題行上右鍵單擊，彈出的快捷菜單中選擇“刪除行”命令，如左圖所示，即可將多余的標題行刪除，效果如右圖所示，此時標題行處于篩選狀態。實例20批量刪除多余的行步驟3：單擊B列右側的按鈕，打開的列表框中選中“全選”復選框，如左圖所示，單擊“確定”按鈕，顯示全部數據，如右圖所示，此時每個字段名的右側仍有按鈕。實例20批量刪除多余的行步驟4：打開“數據”選項卡，單擊“篩選”按鈕，取消字段名右側的按鈕，得到規范數據表，如圖所示。實例20批量刪除多余的行2.刪除多余的空白行如果系統導出的數據包含多余的空白行，如圖所示，將數據區域間隔為多個區域，不利于數據的分析。現要求刪除多余的空白行，將數據存放在連續的區域中。操作步驟如下：實例20批量刪除多余的行步驟1：選中任何一列，例如C列，打開“數據”選項卡，單擊“篩選”按鈕，如左圖所示。此時，該列處于篩選狀態。步驟2：單擊C1單元格右側的按鈕，打開的下拉列表框中，只選中“空白”復選框，如右圖所示，單擊“確定”按鈕，即可篩選出所有的空白行。實例20批量刪除多余的行步驟3：選中所有空白行并右鍵單擊，彈出的快捷菜單中選擇“刪除行”命令，如圖所示，刪除所有空白行。實例20批量刪除多余的行步驟4：單擊C1單元格右側的按鈕，打開的下拉列表框中選中“全選”復選框，如左圖所示，單擊“確定”按鈕，將全部數據進行顯示，如右圖所示。實例20批量刪除多余的行步驟5：按快捷鍵Ctrl＋Shift＋L或者單擊“數據”選項卡中的“篩選”按鈕，取消C1右側的按鈕，得到規范的數據表，如圖所示。實例21批量刪除重復記錄如果數據源中有重復的記錄，如左圖所示，可利用Excel提供的刪除重復項功能，將重復的記錄刪除。操作步驟如下：步驟1：選中A1:A10單元格區域，打開“數據”選項卡，單擊“刪除重復項”按鈕，如右圖所示。實例21批量刪除重復記錄步驟2：彈出的對話框中，若要刪除標題行中的重復值，則選中“數據包含標題”復選框，反之，不選，單擊“確定”按鈕，如左圖所示。步驟3：彈出的提示框中顯示刪除的數據，單擊“確定”按鈕，完成刪除，效果如右圖所示。實例22批量轉換無法正常統計的數字數據源中有些數字在使用SUM函數進行匯總運算時，結果為0，如圖2-21所示，原因是這些數字是文本格式，所以在使用SUM函數匯總運算時結果為0。若要對這些數字進行匯總，首先要將文本格式的數字轉換為數值，轉換方法有2種。實例22批量轉換無法正常統計的數字方法1：利用“轉換為數字”命令轉換步驟1：選中數字所在的單元格區域，在單元格區域的左側出現一個黃色的提示符號，單擊此符號，打開的下拉列表中選擇“轉換為數字”命令，如左圖所示。步驟2：將文本格式的數字轉換為數值后，匯總結果隨之發生變化，如右圖所示。實例22批量轉換無法正常統計的數字方法2：利用選擇性粘貼功能步驟1：在任意一個空白單元格上右鍵單擊，彈出的快捷菜單中選擇“復制”命令，然后選中數字區域并右鍵單擊，彈出快捷菜單中選擇“選擇性粘貼”命令，如圖所示。實例22批量轉換無法正常統計的數字步驟2：彈出的對話框中，依次選中“數值”和“加”單選按鈕，如左圖所示，單擊“確定”按鈕，將選中區域的文本格式數字轉換為數值，匯總結果隨之發生變化，如右圖所示。若將大量文本格式數字轉換為數值，優先推薦使用方法2進行轉換。相比于方法1先行后列逐個轉換，方法2高效快捷瞬間完成轉換。實例23智能拆分合并單元格并填充數據在實際工作中，如果數據源中含有合并單元格，如圖所示，會導致排序、篩選、創建數據透視表等無法進行，給數據處理工作帶來一系列麻煩。解決此類問題的方法是先取消單元格的合并，填上相關數據后才能進行后續的數據處理。實例23智能拆分合并單元格并填充數據步驟1：選中合并單元格區域C2:C13，打開“開始”選項卡，單擊“合并后居中”按鈕，如左圖所示，取消單元格的合并。步驟2：按快捷鍵Ctrl＋G或F5鍵，彈出“定位”對話框，單擊“定位條件”按鈕，如右圖所示。實例23智能拆分合并單元格并填充數據步驟3：彈出的對話框中，選中“空值”復選框，如左圖所示，單擊“確定”按鈕，效果如右圖所示。實例23智能拆分合并單元格并填充數據步驟4：此時，當前活動單元格為C3,在編輯欄中輸入公式“=C2”(即活動單元格上方的單元格)，然后按快捷鍵Ctrl＋Enter批量填充公式，填充效果如圖所示，完成拆分合并單元格并填充數據。實例23智能拆分合并單元格并填充數據步驟5：因為批量填充是通過公式完成的，為了避免后續的操作(如排序等)破壞填充結果，需要將公式結果轉為實際值。選中C2:C13單元格區域并右鍵單擊，彈出的快捷菜單中選擇“復制”命令，然后在選中的區域上再次右鍵單擊，彈出的快捷菜單中選擇粘貼選項中的“值”，如左圖所示。即可將選中區域中的公式清除，并將公式結果轉為實際值，效果如右圖所示。實例24批量轉換不規則的日期數據在Excel中日期的規范格式是2023/3/15或2023-3-15的形式，而在如圖所示中，日期格式不規范，需要將其轉換為規范格式，才能進行后續的數據處理，如使用函數進行計算或者創建數據透視表等。下面通過實例介紹將不規范日期批量轉換為規范日期的方法。實例24批量轉換不規則的日期數據步驟1：選中日期所在的列A列，打開“數據”選項卡，單擊“分列”按鈕，彈出的對話框中單擊“下一步”按鈕，如圖所示。實例24批量轉換不規則的日期數據步驟2：彈出的對話框中單擊“下一步”按鈕，進入文本分列向導第3步，選中“日期”單選按鈕，如左圖所示，單擊“完成”按鈕，效果如右圖所示。實例24批量轉換不規則的日期數據步驟2：彈出的對話框中單擊“下一步”按鈕，進入文本分列向導第3步，選中“日期”單選按鈕，如左圖所示，單擊“完成”按鈕，效果如右圖所示。在本例中，使用了分列功能將不規范日期批量轉換為規范日期，操作簡單快捷。對于大批量不規范日期的轉換，此功能更為便捷。實例25批量隱藏數據工作表中的某些信息如果不希望被他人看到，例如價格、工資、成績等，可將單元格中的這些信息隱藏起來，隱藏的信息同樣可以參與計算，不影響計算結果。如圖所示是成績統計表，現要求將“寫作”列的信息隱藏，隱藏方法如下：實例25批量隱藏數據步驟1：選中要隱藏信息的單元格區域D2:D12，在選中的單元格上右鍵單擊，在彈出的快捷菜單中選擇“設置單元格格式...”命令，如圖所示，彈出“設置單元格格式”對話框。實例25批量隱藏數據步驟2：在“數字”選項卡的“分類”列表框中單擊“自定義”選項，“類型”由默認字符“G/通用格式”改為“；；；”，如左圖所示，單擊“確定”按鈕，選中單元格的信息被隱藏，效果如右圖所示。實例26快速固定標題行當工作表中的記錄幾十條甚至是幾萬條時，窗口不能完全顯示，需要向下拖動滾動條才能瀏覽其他數據，如圖所示。在向下拖動滾動條瀏覽數據時，頂端的標題行隨之滾動導致不可見，不利于對數據的理解。為了方便對數據理解，可讓標題行自動置頂，使其在上下移動滾動條瀏覽數據時始終顯示在窗口的頂端。設置標題行自動置頂的方法如下：實例26快速固定標題行步驟1：選中數據源中的任意一個單元格，按快捷鍵Ctrl＋T,彈出的對話框中選擇默認設置，單擊“確定”按鈕，如左圖所所示。步驟2：此時，上下移動滾動條瀏覽數據時標題行自動顯示在窗口的頂端始終可見，如右圖所示。實例26快速固定標題行步驟3：若要取消標題行自動置頂，打開“表格工具”中的“設計”選項卡，單擊“轉換為區域”按鈕，如圖所示，將其轉為普通的單元格區域即可。2.2數據美化Databeautification實例27利用快捷鍵隔行填充顏色隔行填充顏色不僅使表格美觀、易讀，而且醒目便于區分內容，以免看串行。如在圖2-47所示的數據表格中設置隔行填充顏色，操作步驟如下：實例27利用快捷鍵隔行填充顏色步驟1：單擊數據區域中的任意一個單元格，然后按快捷鍵Ctrl＋T，彈出的對話框中，選擇默認設置，單擊“確定”按鈕，如左圖所所示，即可瞬間自動隔行填充顏色，效果如右圖所示。實例27利用快捷鍵隔行填充顏色步驟2：若對填充的顏色或樣式不滿意，打開“表格工具”中的“設計”選項卡，在“表格樣式”組中可以選擇其他顏色或樣色，如圖所示，直到滿意為止。實例27利用快捷鍵隔行填充顏色步驟3：隔行填充顏色后，在每個字段名右側有一個下拉按鈕，這是因為將填充區域轉換為了表。若要取消字段名右側的下拉按鈕，打開“表格工具”中的“設計”選項卡，單擊“工具”組中的“轉換為區域”按鈕，如左圖所示，彈出的提示框中單擊“是”按鈕，將表轉換為普通的單元格區域，轉化后的效果如右圖所示。實例2快速輸入超長文本步驟1：利用自動更正功能將某一字母(例如b)設置為商品名稱“DellIns15CR-4528B15.6英寸筆記本電腦”，設置方法如圖所示。(注：輸入的字母或數字不能與工作表中的其他內容重復，以免誤替換。)實例28自定義隔行填充顏色隔行填充顏色除了使用快捷鍵一鍵填充外，也可以自定義顏色進行填充，此填充方式使表格顏色更豐富更具有個性化。下面結合實例講解自定義隔行填充顏色方法，具體要求如下：①標題行設置為深色背景②其余數據行用淺色隔行填充步驟1：選中標題行所在的單元格區域A1:E1，打開“開始”選項卡，單擊“填充顏色”右側下拉按鈕，打開的下拉列表中單擊“其他顏色”，彈出的對話框中，拖動滑塊或輸入RGB值自定義標題行填充顏色，如圖所示。實例28自定義隔行填充顏色步驟2：在第3行中選中A3:E3單元格區域，按照步驟1的設置方法，將其設置為如左圖所示的填充顏色，效果如右圖所示。實例28自定義隔行填充顏色步驟3：將第2行和第3行填充顏色復制給剩余數據行。選中A2:E3單元格區域，打開“開始”選項卡，單擊“格式刷”按鈕，如左圖所示。步驟4：鼠標指針指向A4單元格，按住鼠標左鍵拖動至E13單元格，如圖所示，批量復制填充格式，得到隔行填充效果，如右圖所示。實例29設置邊框默認情況下，工作表無邊框，工作表中的網格線是為了方便輸入、編輯而預設的，打印時網格線并不顯示。為了使工作表美觀和易讀，可通過設置工作表的邊框改變其視覺效果，使數據的顯示更加清晰直觀。例如，在圖2-59中，將左圖表格設置為如右圖所示的邊框，操作方法如下：實例29設置邊框步驟3：選中A2:A13單元格區域，單擊“框線”下拉按鈕，打開的下拉列表中單擊“右框線”，如左圖所示，為選中區域添加右框線。若要更改邊框線的顏色，打開“框線”下拉列表，從“線條顏色”中選擇需要的顏色即可，如右圖所示。實例29設置邊框方法2：使用筆繪制框線步驟1：選中A2:E13單元格區域，打開“開始”選項卡，單擊“框線”下拉按鈕，打開的下拉列表中單擊“無框線”，刪除原有的框線。步驟2：單擊“框線”下拉按鈕，打開的下拉列表中從“線型”中選擇一種實線，如圖2-65所示。此時光標變成筆形，在標題行的下方按住鼠標左鍵進行拖動即可繪制框線。按照相同的方法分別在最后一行的下方、首列的右側按住鼠標左鍵拖動繪制框線。步驟3：框線繪制結束后，單擊“開始”選項卡中的“框線”按鈕，取消筆形即可。實例30繪制斜線表頭斜線表頭為了說明行、列、數據區域的含義。在Excel工作表中，斜線表頭分為2種，一是單斜線表頭，二是多斜線表頭，如圖所示。繪制斜線表頭有多種方法，本例主要介紹利用插入直線和文本框的方法繪制多斜線表頭，該方法同樣適用于單斜線表頭的繪制。實例30繪制斜線表頭例如，為左圖所示的表格繪制多斜線表頭，繪制后的效果如右圖所示，操作步驟如下：步驟1：設置表頭的行高和列寬，將第1行的行高設置為50，A列的寬度設置為23。實例30繪制斜線表頭步驟2：選中A1單元格，打開“插入”選項卡，單擊“形狀”按鈕，選擇“直線”，如左圖所示。步驟3：在A1單元格中按住鼠標左鍵進行拖動，繪制直線并設置其顏色，如右圖所示。實例30繪制斜線表頭步驟4：按照如左圖所示操作，在A1單元格中再繪制一條直線并設置其顏色。步驟5：單擊“文本框”下拉按鈕，打開的下拉列表中選擇“橫排文本框”，在A1單元格中繪制如右圖所示的文本框，并輸入內容“月份”，調整文文本框的大小及位置，設置為無輪廓。實例30繪制斜線表頭步驟6：選中設置好的“月份”文本框，按復制快捷鍵Ctrl＋C，在如左圖所示的位置按粘貼快捷鍵Ctrl＋V將其復制，文本框的內容修改為“銷量”。步驟7：按照相同的方法在如右圖所示的位置創建“書名”文本框。至此，完成多斜線表頭的繪制。第3章經典函數讓你戰無不勝Classicalfunctionsmakeyouinvincible3.1統計函數3.3查找與引用函數3.2邏輯函數3.1統計函數Statisticalfunction實例31自動計算命令自動計算命令是指“開始”選項卡“編輯”組中的“自動求和”按鈕，能快速的進行一些簡單的統計操作，如求和、求平均值、最大/小值等，既簡化了運算了又大大提高了工作效率。下面介紹自動計算命令的使用方法。如圖所示，某個商店第2季度銷售記錄，使用自動計算命令求出每個月銷售的最大值，方法如下：實例31自動計算命令選定B3:D11單元格區域，打開“開始”選項卡，單擊“編輯”組中的“自動求和”按鈕，打開的下拉列表中選擇“最大值”，如左圖所示，即可求出每個月的銷售的最大值，如右圖所示。此方法簡單快捷，因此，在進行基礎計算時，優先推薦使用自動計算命令進行操作實例32統計個數COUNT函數COUNT函數用于計算某個區域中包含數字的單元格的個數。例如，在左圖所示的工作表中使用COUNT函數計算A2:A8區域中數字個數。單擊B2單元格，在B2單元格或編輯欄中輸入公式：=COUNT(A2:A8)，如中間圖所示，按Enter鍵確認后，即可計算出包含數字的單元格個數，對文本(A2、A8)、邏輯值(A6)，錯誤值(A7)不進行計算，如右圖所示。實例32統計個數COUNT函數綜上，可以看出COUNT函數語法結構如下：COUNT(Value1,[Value2],...)Value1,Value2…是1-255個參數，可以包含或引用各種不同類型的參數，最多可包含255個參數，各參數之間用逗號分割。需要注意的是，函數或公式中的符號都要使用英文半角形式，此處的逗號也不例外。注意:1.如果參數為數字、日期或者代表數字的文本（例如，用引號引起的數字，如"1"），則將被計算在內。2.邏輯值和直接輸入到參數列表中代表數字的文本被計算在內，如果參數為錯誤值或不能轉換為數字的文本，則不會被計算在內。3.如果參數是一個數組或引用，則只計算其中的數字。數組或引用中的空白單元格、邏輯值、文本或錯誤值將不計算在內。4.若要計算邏輯值、文本值或錯誤值的個數，使用COUNTA函數。5.若要只計算符合某一條件的數字的個數，使用COUNTIF函數或COUNTIFS函數。實例33單條件計數COUNTIF函數COUNTIF函數用于計算某個區域中滿足給定條件的單元格數目，其語法結構如下：COUNTIF(range，criteria)例如，使用COUNTIF函數在“請假統計”工作表中對每位員工的請假情況進行統計并以此填入“個人請假情況”工作表的相應單元格，操作步驟如下：實例33單條件計數COUNTIF函數步驟1：單擊“個人請假情況”工作表中的B2單元格，打開“公式”選項卡，單擊“插入函數”按鈕，彈出“插入函數”對話框。在“搜索函數”文本框中輸入“countif”，單擊“轉到”按鈕進行查找，查找結果顯示在“選擇函數”列表框中，單擊“確定”按鈕。彈出“函數參數”對話框。步驟2：將鼠標光標定位在“函數參數”對話框的“Range”文本框中，單擊“請假統計”工作表標簽，鼠標拖動選中D3:D25單元格區域，將鼠標光標定位在“Criteria”文本框，再單擊A2單元格，單擊“確定”按鈕，求出第一位員工的請假次數。步驟3：將鼠標指針指向B2單元格填充柄，雙擊填充柄或者按住鼠標左鍵進行拖動，自動求出其他員工的請假次數，如圖所示。實例33單條件計數COUNTIF函數如果公式使用熟練，可以直接輸入公式和參數求出每位員工的請假情況，操作步驟如下：步驟1：單擊“個人請假情況”工作表中的B2單元格，在編輯欄中輸入公式：=COUNTIF(請假統計!$D$3:$D$25,A2)，如圖所示，按Enter鍵，求出第一位員工的請假次數。步驟2：將鼠標指針指向B2單元格填充柄，雙擊填充柄或者按住鼠標左鍵進行拖動，求出其他員工的請假次數。實例34多條件計數COUNTIFS函數COUNTIFS函數用于計算多個區域中滿足給定條件的單元格的個數，給定的條件為2個及以上，其語法結構如下：COUNTIFS(criteria_range1,criteria1,…)下面通過實例介紹該函數的使用方法。在左圖所示的工作表中，要求根據“顧客資料”工作表中的數據，在“性別和年齡”工作表的B列中計算各年齡段男顧客人數，如右圖所示。實例34多條件計數COUNTIFS函數本例給定2個條件：條件1是各年齡段；條件2是男顧客，所以使用COUNTIFS函數進行求解，操作步驟如下：步驟1：在“性別和年齡”工作表中，單擊B2單元格，然后單擊功能區中的“插入函數”按鈕，彈出的對話框中，進行如圖所示的設置。實例34多條件計數COUNTIFS函數步驟2：彈出“函數參數”對話框，進行如左圖所示的設置。步驟3：將鼠標指針指向B2單元格填充柄，雙擊填充柄或者按住鼠標左鍵進行拖動，自動求出其他年齡段男顧客人數，如右圖所示。實例34多條件計數COUNTIFS函數如果公式使用熟練，可以直接輸入公式和參數求出各年齡段男顧客人數，方法如下：在"性別和年齡"工作表中，單擊B2單元格，輸入公式：=COUNTIFS(顧客資料!C2:C21,A2,顧客資料!B2:B21,"男")，按Enter鍵確認操作，然后利用自動填充功能對其他單元格進行填充。因為第1參數C2:C21單元格區域在C列，所以C2:C21可以簡寫C:C，代表整個C列。同理，第3參數B2:B21單元格區域在B列，所以B2:B21可以簡寫為B:B，代表整個B列。因此公式：=COUNTIFS(顧客資料!C2:C21,A2,顧客資料!B2:B21,"男")，可以簡化為：=COUNTIFS(顧客資料!C:C,A2,顧客資料!B:B,"男")。實例35單條件求和SUMIF函數SUMIF函數用于對滿足條件的單元格求和，其語法結構如下：SUMIF(range,criteria,[sum_range])某商店的銷售商品明細如圖3-20所示，要求按照B列中的類別，統計“日用品”銷售總額，將統計結果保存在H5單元格中。實例35單條件求和SUMIF函數步驟1：單擊H2單元格，然后單擊編輯欄中的“插入函數”按鈕，在“插入函數”對話框中搜索SUMIF函數，單擊“確定”按鈕，彈出“函數參數”對話框。步驟2：在“Range”文本框中輸入條件所在區域，“日用品”在B2:B20區域，因此輸入B2:B20，或者通過選中區域的方式輸入條件所在區域，方法：將鼠標光標定位在該文本框中，鼠標拖動選中工作表中的B2:B20區域，如圖所示。實例35單條件求和SUMIF函數步驟3：在“Criteria”文本框中輸入查找值，查找值位于G5單元格，所以單擊G5單元格或者直接輸入G5。步驟4：在“Sum_Range”文本框中輸入求和數據所在的區域。本例要對金額進行統計，金額位置在E列，因此輸入E2:E20，或者通過選中區域的方式輸入求和數據所在區域，如圖所示。實例35單條件求和SUMIF函數步驟5：單擊“確定”按鈕，“日用品”銷售總額顯示在H2單元格，如圖所示。如果公式使用熟練，可以直接輸入公式和參數求出“日用品”銷售總額，方法如下：單擊H5單元格，輸入公式：=SUMIF(B:B,G5,E:E)或者=SUMIF(B2:B20,G5,E2:E20)，按Enter鍵確認輸入，即可統計出“日用品”銷售總額。實例36多條件求和SUMIFS函數SUMIFS函數用于對區域中滿足多個條件的單元格求和，其語法結構如下：SUMIFS(sum_range,criteria_range1,criteria1,…)。如圖所示，要求統計“日用品”中“酸奶酪”銷售總額，將統計結果保存在I5單元格中。實例36多條件求和SUMIFS函數步驟1：單擊I5單元格，然后單擊編輯欄中的“插入函數”按鈕，在“插入函數”對話框中搜索SUMIFS函數，單擊“確定”按鈕，彈出“函數參數”對話框。步驟2：在“Sum_Range”文本框中輸入求和數據所在的區域。此例要對金額進行統計，金額位置在E列，因此輸入E2:E20，或者通過選中區域的方式輸入條件所在區域，方法：將鼠標光標定位在該文本框中，鼠標拖動選中工作表中的E2:E20區域，如圖所示。實例36多條件求和SUMIFS函數步驟3：在“Criteria_Range1”文本框中輸入條件1所在區域。此例首先按“日用品”進行統計，條件1所在位置是B列，因此輸入B2:B20，或者通過選中區域的方式輸入條件1所在區域，方法：將鼠標光標定位在該文本框中，鼠標拖動選中工作表中的B2:B20區域。步驟4：在“Criteria1”文本框中輸入查找值1，查找值1位于G5單元格，所以單擊G5單元格或者直接輸入G5，如圖所示。實例36多條件求和SUMIFS函數步驟5：在“Criteria_Range2”文本框中輸入條件2所在區域。條件2“酸奶酪”所在位置是A列，因此輸入A2:A20，或者通過選中區域的方式輸入條件2所在區域，如圖所示。步驟6：在“Criteria2”文本框中輸入查找值2，查找值2位于H5單元格，所以單擊H5單元格或者直接輸入H5實例36多條件求和SUMIFS函數步驟7：單擊“確定”按鈕，“日用品”中“酸奶酪”銷售總額顯示在I5單元格，如圖所示。如果公式使用熟練，可以直接輸入公式和參數求出“日用品”銷售總額，方法如下：單擊I5單元格，輸入公式：=SUMIFS(E:E,B:B,G5,A:A,H5)或者=SUMIFS(E2:E20,B2:B20,G5,A2:A20,H5)，按Enter鍵確認輸入，即可統計出“日用品”中“酸奶酪”銷售總額。實例37獲取排名:RANK函數RANK函數用于對指定區域內的數據進行排名，其語法結構為：RANK(number,ref,[order])。在圖中，按照由高到低的順序統計每種產品的“銷量”排名，以第1名、第2名、第3名……的形式標識名次并填入“銷量排名”列中。操作步驟如下：實例37獲取排名:RANK函數步驟1：單擊D2單元格，然后單擊編輯欄中的“插入函數”按鈕，彈出“插入函數”對話框，搜索RANK函數，如圖3-35所示，單擊“確定”按鈕，彈出“函數參數”對話框。步驟2：在“Number”文本框中輸入要排名的單元格。因要對“銷量”排名，所以單擊工作表中第一個“銷量”單元格C2。步驟3：在“Ref”文本框中指定排名所在的區域。本例要對C2:C14區域的數據排名，因此將光標定位在該文本框中，鼠標拖動選定工作表中的C2:C14區域。并按F4鍵在行號和列標前面加上絕對引用符號$，固定排名區域，如圖所示。實例37獲取排名:RANK函數步驟4：在“Order”文本框中設置排位方式。從大到小排序為降序，用0表示或省略不寫，默認降序；從小到大排序為升序，用非零值(如1)表示。本例省略不寫，降序排名。步驟5：設置完成后，單擊“確定”按鈕，第一個“銷量”排名顯示在D2單元格中，如圖所示實例37獲取排名:RANK函數步驟6：將光標定位在編輯欄“=”后面，輸入”第”&，在公式末尾“)”后輸入&”名”,如左圖所示，&是連接符號，將“第”、“RANK(E2,$E$2:$E$26)”、“名”三者聯系起來。按Enter鍵，效果如右圖所示。實例37獲取排名:RANK函數步驟7：將鼠標指針指向D2單元格的填充柄，雙擊填充柄或按住鼠標左鍵向下拖動，按照“銷量”自動排名，如圖所示。如果公式使用熟練，可以直接輸入公式和參數求出各年齡段男顧客人數，方法如下：單擊D2單元格，輸入公式：=”第”&RANK(C2,$C$2:$C$14)&”名”，按Enter鍵確認操作，拖動填充柄或雙擊填充柄對其他單元格進行填充。3.2邏輯函數Logicalfunction實例38單條件邏輯判斷IF函數If函數是邏輯判斷常用函數，它可以根據給定的條件進行判斷，判斷條件是否成立，然后返回對應的邏輯值，某商店對銷售量進行考核評定，要求將銷售量>=260評定為“銷售之星”，如圖所示。實例38單條件邏輯判斷IF函數步驟1：單擊E2單元格，然后單擊編輯欄中的“插入函數”按鈕，在“插入函數”對話框中搜索IF函數，彈出“函數參數”對話框。步驟2：在Logical_test”文本框中輸入判斷條件。首先對第一個銷售量進行判斷，判斷的條件是>=260，所以輸入D2>=260步驟3：在“Value_if_true”文本框中輸入條件成立時返回的結果，即“銷售之星”。步驟4：在“Value_if_false”文本框中輸入條件不成立時返回的結果，””，即空文本，如圖所示。實例38單條件邏輯判斷IF函數步驟5：單擊“確定”按鈕，判斷結果顯示在E2單元格，拖動填充柄或雙擊填充柄對其他單元格進行填充，效果如圖所示實例38單條件邏輯判斷IF函數步驟5：單擊“確定”按鈕，判斷結果顯示在E2單元格，拖動填充柄或雙擊填充柄對其他單元格進行填充，效果如圖所示。如果公式使用熟練，可以直接輸入公式和參數求出“日用品”銷售總額，方法如下：單擊E2單元格，輸入公式：=IF(E2>260,"銷售之星","")，按Enter鍵確認輸入，拖動填充柄或雙擊填充柄對其他單元格進行填充，效果如上圖所示。實例39多條件邏輯判斷:IF＋AND函數IF＋AND函數是對多個條件同時判斷，當所有條件全部滿足時則條件成立，返回邏輯真值TRUE；當有一個或多個條件不滿足時則條件不成立，返回邏輯假值FALSE。某商店僅對第4季度進行銷售考核評定，要求將季度為“4季度”且銷售量>=260評定為“銷售之星”，如圖所示。實例39多條件邏輯判斷:IF＋AND函數單擊E2單元格，輸入公式：=IF(AND(B2="4季度",D2>=26),"銷售之星","")，按Enter鍵確認輸入，拖動填充柄對其他單元格進行填充，評定結果如圖所示。此例中的AND(B2="4季度",D2>=26)也可以表示為AND(D2>=26,B2="4季度"),即參數的順序不影響判斷結果。這樣使用IF＋AND組合函數實現了同時滿足雙條件的判斷，同理，使用該組合函數也可以同時進行多條件判斷，最多可以有255個條件。實例40多條件邏輯判斷:IF＋OR函數IF＋OR函數是對多個條件進行邏輯判斷，只要有一個條件滿足則條件成立，返回邏輯真值TRUE；當所有條件都不滿足時則條件不成立，返回邏輯假值FALSE.。某商店對銷售量和銷售額進行考核評定，要求將銷售量>=260或者銷售額>1200000評定為“銷售之星”，如圖所示。實例40多條件邏輯判斷:IF＋OR函數在E2單元格中輸入公式：=IF(OR(D2>=260,E2>1200000),"銷售之星","")，按Enter鍵確認輸入，拖動填充柄對其他單元格進行填充，評定結果如圖所示。此例中的OR(D2>=260,E2>1200000)也可以表示為OR(E2>1200000,D2>=260)，即參數的順序不影響判斷結果。這樣使用IF＋OR組合函數解決了“或”關系多條件邏輯判斷。在處理多條件復雜邏輯判斷問題時，首先要區分多個條件之間是“或”關系還是“且”關系，“或”關系使用OR函數，“且”關系使用AND函數，確定使用哪個函數后，再輸入公式計算結果。實例41多層級條件邏輯判斷:IF嵌套函數IF函數嵌套是對數值區間多層級關系的條件判斷。例如，在圖中，對各店鋪的銷量進行評定，根據銷售量評定等級，評定規則如下：

銷售量>=260，評定等級為“優秀”。

銷售量>=200，評定等級為“良好”。

銷售量<200，評定等級為“一般”。實例41多層級條件邏輯判斷:IF嵌套函數在E2單元格中輸入公式：=IF(D2>=260,"優秀",IF(D2>=200,"良好","一般"))，按Enter鍵確認輸入，拖動填充柄對其他單元格進行填充，評定結果如圖所示。按照上述方法可以對數值區間更多層級條件進行判斷。層級條件增加，IF函數嵌套層級也隨之增加。在使用IF函數嵌套時要注意按照數值區間的大小順序依次對每個層級進行判斷，可以按照從大到小的順序，或者從小到大順序，但不能從中間某個區間開始判斷，否則容易出錯。3.3查找與引用函數Findandreferencefunctions實例42垂直查找:VLOOKUP如圖所示是學生的部分成績表，包含學生的學號以及對應的各科成績和總分信息，要求按照給定的學號查找學生的總分。要查找的學號位于在L列，查找結果放置在M列。操作步驟如下：實例42垂直查找:VLOOKUP步驟1：單擊M2單元格，單擊編輯欄中的“插入函數”按鈕，在“插入函數”對話框中搜索VLOOKUP函數，單擊“確定”按鈕，彈出“函數參數”對話框。步驟2：在“Lookup_value”文本框中輸入查找值，查找值位于L2單元格，所以單擊L2單元格或者直接輸入L2。步驟3：在“Table_array”文本框中輸入查找區域。本例要在A2:J19區域查找，因此輸入$A$2:$J$19，如圖所示，或者通過選中區域的方式輸入查找區域，方法：將鼠標光標定位在該文本框中，鼠標拖動選中工作表中的A2:J19區域。按F4鍵在行號和列標前面加上絕對引用符號$將選區固定。實例42垂直查找:VLOOKUP函數步驟4：在“Col_index_num”文本框中輸入查找列數。這里的列數從查找范圍的第1列作為1，而要查找的總分在第10列，所以在該文本框中輸入10，如圖所示，表示查找列“總分”是查找范圍A2:J19區域的第10列。步驟5：在“Range_lookup”文本框中輸入精確匹配的值。此例按照學號精確查找總分，因此參數輸入為0或FALSE，如圖所示。實例42垂直查找:VLOOKUP函數步驟6：單擊“確定”按鈕，學號120304的總分顯示在M2單元格，雙擊M2單元格填充柄，自動填充其他學號的“總分”，效果如圖所示。如果公式使用熟練，在M2單元格中直接輸入公式：=VLOOKUP(L2,$A$2:$J$19,10,0)或者=VLOOKUP(L:L,$A$2:$J$19,10,0)，按Enter鍵確認輸入，雙擊M2單元格填充柄即可按照給定的學號查詢到對應的總分。實例43提取單元格數據:INDEX函數INDEX函數既可以從單列中提取數據，也可以從單行中提取數據，還可以從行列交叉區域中提取數據，下面結合實例介紹INDEX函數從單列、單行及其交叉區域中提取數據方法。1.從單列中提取單元格數據如圖所示是某單位部分工資表，要求按照工號查找獎金，查找條件位于H2單元格，查找結果放置在I2單元格。實例43提取單元格數據:INDEX函數2.從單行中提取單元格數據在圖中，要求按照名稱查找對應的金額，查找條件位于F2單元格，查找結果放置在G2單元格。此例是按照F2單元格中的查找條件在第2行中提取對應的數值，將提取的數值放置在G2單元格。所以在G2單元格中輸入公式：=INDEX(B2:D2,3)，按Enter鍵確認輸入，即可提取查找條件對應的數據，如圖所示。實例43提取單元格數據:INDEX函數3.從行列交叉區域中提取單元格數據在圖3-63所示的工資表中，要求按照工號和名稱雙條件查找對應的金額，查找條件分別位于H2和I2單元格，查找結果放置在J2單元格。實例43提取單元格數據:INDEX函數此例是按照H2和I2單元格中的查找條件在第4行第3列(D列)交叉處單元格中提取數據，將提取的數據放置在J2單元格。所以在J2單元格中輸入公式：=INDEX(B2:F11,4,3)，按Enter鍵確認輸入，即可提取查找條件對應的數據，如圖所示。以上是INDEX函數的3種基本用法。無論哪種用法，INDEX函數中的行、列參數數字都是手動輸入的，若要使行、列參數數字隨查找條件而變化，可以使用MATCH函數獲取行、列的位置。實例44查找數據位置:MATCH函數MATCH函數用于查找指定數據在指定區域中的位置，例如，在圖3-65中，查找“R0056”在“工號”區域中的位置，將查找結果放置在E2單元格，操作步驟如下：實例44查找數據位置:MATCH函數步驟1：單擊E2單元格，單擊編輯欄中的“插入函數”按鈕，在“插入函數”對話框中搜索MATCH函數，單擊“確定”按鈕，彈出“函數參數”對話框。步驟2：在“Lookup_value”文本框中輸入查找值，查找值位于D2單元格，所以單擊D2單元格或者直接輸入D2，如圖所示。實例44查找數據位置:MATCH函數步驟3：在“Lookup_array”文本框中輸入查找區域。本例要在A2:A11區域查找，因此輸入A2:A11，或者將鼠標光標定位在該文本框中，鼠標拖動選中工作表中的A2:A11區域。步驟4：在“Match_type”文本框中輸入匹配類型的值。通常輸入0，表示精確匹配。步驟5：設置完成后，單擊“確定”按鈕，工號“R0056”的相對位置顯示在E2單元格，如圖所示，這里的5表示工號“R0056”在A2:A11區域中的位置是第5位。實例44查找數據位置:MATCH函數如果公式使用熟練，在E2單元格中直接輸入公式：=MATCH(D2,A2:A11,0)，按Enter鍵，即可查找到“R0056”在A2:A11區域中的位置為第5位，返回結果為5。MATCH函數不僅能查找指定數據在列區域中的位置，還可以查找指定數據在行區域中的位置。如圖所示，查找名稱為“公積金”在第1行中的位置，在B5單元格中輸入公式：=MATCH(A5,B1:F1,0)，按Enter鍵，結果為4，表示“公積金”在B1:F1區域中位置是第4位。第4章專業大氣的可視化圖表Professionalatmosphericvisualizations實例45用折線圖查看數據趨勢折線圖是用多條直線段將各數據點連接起來的圖形。使用折線圖可以按照時間或類別顯示數據的變化趨勢。所以，若要展示數據隨時間(每年、每月、每日、每季等間隔相等)產生的變化趨勢，折線圖最為適合。某電商在京東店鋪銷售某一品牌服飾，全年各月銷售數據如左圖所示，要求根據該數據源創建如右圖所示的折線圖，目的是通過折線圖查看全年各月的銷售趨勢變化情況，以便進一步調整銷售策略。創建步驟如下：實例45用折線圖查看數據趨勢步驟1：單擊數據源中的任意單元格(例如B4)，打開“插入”選項卡，插入折線圖。步驟2：設置圖表背景。雙擊圖表的外邊框，彈出“設置圖表區格式”窗格，選擇圖表背景顏色，如圖所示。實例45用折線圖查看數據趨勢步驟3：取消網格線。雙擊網格線，彈出“設置主要網格線格式”窗格，進行如左圖所示的設置。步驟4：設置折線圖線條顏色。雙擊折線圖的線條，彈出“設置數據系列格式”窗格，設置為如右圖所示的顏色。實例45用折線圖查看數據趨勢步驟5：設置折線圖線條粗細。在“設置數據系列格式”窗格中，將線條寬度設置為3磅，如左圖所示。步驟6：設置標記選項。將每個時間拐點的標記設置為如右圖所示的內置類型、大小和填充顏色。標記邊框設置為無線條，將標記顏色和連接的直線顏色完美的融為一體。實例45用折線圖查看數據趨勢步驟7：添加數據標簽。打開“設計”選項卡，單擊“添加圖表元素”按鈕，打開的下拉列表中，進行如左圖所示的設置。步驟8：設置數據標簽字體格式。打開“開始”選項卡，將字體顏色設置為如右圖所示的格式。實例45用折線圖查看數據趨勢步驟9：為圖表添加垂直線以增加視覺感受。打開“設計”選項卡，單擊“添加圖表元素”按鈕，打開的下拉列表進行如左圖所示的設置。步驟10：設置垂直線的格式。雙擊垂直線，彈出“設置垂直線格式”窗格，設置為如右圖所示的格式。實例45用折線圖查看數據趨勢步驟11：設置坐標軸的刻度與折線圖上的數據標記一一對應。雙擊坐標軸，彈出“設置坐標軸格式”窗格，進行如左圖所示的設置。步驟12：取消坐標軸上的橫線增強視覺的通透感。在“設置坐標軸格式”窗格中，將線條設置為“無線條”，如右圖所示。實例45用折線圖查看數據趨勢步驟13：添加圖表標題。將圖表標題設置為如圖所示的內容和格式。步驟14：添加必要說明。在圖表標題的下方插入趨勢分析結果的說明文字，可以借助插入文本框自定義說明文字的內容實例45用折線圖查看數據趨勢步驟15：在文本框中輸入說明文字，然后在文本框邊框上右鍵單擊，彈出的快捷菜單中“設置形狀格式”，彈出“設置形狀格式”窗格，進行如左圖所示的設置。步驟16：如果要添加其他說明，使用同樣方法進行添加即可，最終效果如右圖所示。實例45用折線圖查看數據趨勢步驟15：在文本框中輸入說明文字，然后在文本框邊框上右鍵單擊，彈出的快捷菜單中“設置形狀格式”，彈出“設置形狀格式”窗格，進行如左圖所示的設置。步驟16：如果要添加其他說明，使用同樣方法進行添加即可，最終效果如右圖所示。實例46用柱形圖展示數據對比柱形圖是Excel默認的圖表類型，通常使用柱形圖展示各數據項之間的對比關系左圖是各車間在第一季度生產的兩種產品產量，要求根據該數據源對兩種產品的產量進行對比分析，使用柱形圖進行對比分析的效果如右圖所示。具體操作步驟如下：實例46用柱形圖展示數據對比步驟1：單擊數據源中的任意一個單元格(如C3單元格)，打開“插入”選項卡，單擊“插入柱形圖或條形圖”按鈕，打開的列表中單擊“簇狀柱形圖”，插入柱形圖。步驟2：取消網格線。雙擊網格線，彈出“設置主要網格線格式”窗格，選中“無線條”單選按鈕，如圖所示。實例46用柱形圖展示數據對比步驟3：刪除縱坐標軸。選中縱坐標軸，按Delete鍵或者在縱坐標軸上右鍵單擊，彈出快捷菜單中單擊“刪除”，如左圖所示。步驟4：設置數據系列的顏色。雙擊筆記本數據系列，彈出“設置數據系列格式”窗格，將數據系列設置為如右圖所示的顏色。實例46用柱形圖展示數據對比步驟5：按照相同的方法，將臺式機的數據系列設置為如左圖所示的顏色。步驟6：取消兩個數據系列(即兩個柱子)之間的間隙，在“設置數據系列格式”窗格中，將“系列重疊”設置為0%，如右圖所示。實例46用柱形圖展示數據對比步驟7：添加數據標簽。選中臺式機的數據系列，在數據系列上右鍵單擊，彈出的快捷菜單擊“添加數據標簽”|“添加數據標簽”，如左圖所示。按照相同的方法為筆記本的數據系列添加標簽。步驟8：設置圖表背景。雙擊圖表的外邊框，彈出“設置圖表區格式”窗格，選擇圖表背景顏色，如右圖所示。實例46用柱形圖展示數據對比步驟9：更改圖例的位置。選中圖例，單擊圖表右側的“圖表元素”按鈕，打開的列表框中進行如左圖所示的設置。步驟10：添加圖表標題。將圖表標題設置為如右圖所示的內容和格式。出“設置圖表區格式”窗格，選擇圖表背景顏色。實例46用柱形圖展示數據對比步驟11：設置圖例格式。將圖例設置為加粗、字體顏色和標題顏色相同。調整圖表大小和位置，效果如圖所示。實例47用條形圖展示數據對比數據對比除了使用柱形圖進行展示外，也可以使用條形圖進行展示。下面通過實例介紹使用條形圖進行數據對比的方法。在左圖所示的數據源中，對各車間第一季度筆記本產量進行對比分析，使用條形圖進行對比分析的效果如右圖所示。具體操作步驟如下：實例47用條形圖展示數據對比步驟1：插入條形圖。選中A1:B7單元格區域，打開“插入”選項卡，單擊“插入柱形圖或條形圖”按鈕，打開的列表中單擊“簇狀條形圖”。步驟2：應用圖表樣式。打開“設計”選項卡，在“圖表樣式”列表框中選擇一種圖表樣式，如圖所示，該樣式即可應用到該圖表中。實例47用條形圖展示數據對比步驟3：刪除網格線。選中網格線，如左圖所示，按Delete鍵。步驟4：刪除橫坐標軸。選中橫坐標軸，如右圖所示，按Delete鍵。實例47用條形圖展示數據對比步驟5：將縱坐標軸的數據名稱順序更改為與數據源順序一致。雙擊縱坐標軸，彈出“設置坐標軸格式”窗格，選中“逆序類