Excel+Power BI數(shù)據(jù)分析電子課件 第1章

第1章各類數(shù)據(jù)的獲取教師：Office助手Accesstoallkindsofdata1.1快速輸入各類數(shù)據(jù)1.2批量導入各類數(shù)據(jù)1.3智能提取各類數(shù)據(jù)1.4智能合并各類數(shù)據(jù)1.5一鍵匯總數(shù)據(jù)1.1快速輸入各類數(shù)據(jù)Quicklyentervarioustypesofdata實例1快速輸入超長數(shù)字在Excel中經(jīng)常需要輸入超長數(shù)字，當輸入的數(shù)字位數(shù)為11位以內(nèi)時，數(shù)字在單元格中完整顯示;當輸入的數(shù)字位數(shù)超過11位時，Excel自動用科學計數(shù)法表示。例如，輸入12位數(shù)字135782945708，則顯示為1.35783E+11，代表1.35783×1011。若將科學計數(shù)法表示的數(shù)字“1.35783E+11”轉(zhuǎn)換為常規(guī)數(shù)字格式“135,782,945,708”，按快捷鍵Ctrl＋Shift＋1即可實現(xiàn)轉(zhuǎn)換，如圖所示。借助此快捷鍵可將大量科學計數(shù)法表示的數(shù)字批量轉(zhuǎn)換為常規(guī)數(shù)值格式。實例2快速輸入超長文本在在工作中若要重復輸入多個超長文本，如圖所示的各商品名稱(B2:B9)，手工逐個輸入，既費時又容易出現(xiàn)錯誤，可通過以下3種方法快速輸入超長文本。實例2快速輸入超長文本方法1：利用下拉菜單選擇輸入首先將包含超長文本的單元格設置為下拉菜單，如圖所示。單擊單元格右側(cè)的下拉按鈕，打開的下拉菜單中，直接選擇即可輸入所需的文本。設置下拉菜單及選擇輸入內(nèi)容的方法如下：實例2快速輸入超長文本步驟1：將各商品名稱預先輸入到Excel中，如D2:D9區(qū)域，如圖所示。實例2快速輸入超長文本步驟2：選中B2:B9區(qū)域，設置下拉菜單的操作步驟如左圖所示。步驟3：下拉菜單設置結(jié)束后，為了使表格美觀易讀，可將D列數(shù)據(jù)隱藏，B列已設置完成的下拉菜單效果如右圖所示。實例2快速輸入超長文本步驟4：單擊單元格右側(cè)下拉按鈕，打開的下拉菜單中即可便捷的選擇要輸入的超長商品名稱。使用該方法的前提是首先預先輸入指定的商品名稱(如D列)，然后將要輸入超長文本的單元格區(qū)域(如B列)設置為下拉菜單，即可在下拉菜單中快捷的選擇要輸入的內(nèi)容。方法2：利用自動更正功能輸入若要重復輸入某個超長文本，如商品名稱“DellIns15CR-4528B15.6英寸筆記本電腦”，可利用自動更正功能將容易輸入的某一或某幾個字母、數(shù)字等設置為要輸入的商品名稱，設置結(jié)束后，在工作表中輸入已設置的字母或數(shù)字即可輸入商品名稱，操作步驟如下：實例2快速輸入超長文本步驟1：利用自動更正功能將某一字母(例如b)設置為商品名稱“DellIns15CR-4528B15.6英寸筆記本電腦”，設置方法如圖所示。(注：輸入的字母或數(shù)字不能與工作表中的其他內(nèi)容重復，以免誤替換。)實例2快速輸入超長文本步驟2：設置結(jié)束后，在工作表中輸入字母b，按Enter鍵，即可輸入商品名稱“DellIns15CR-4528B15.6英寸筆記本電腦”，如圖所示。方法3：利用替換功能輸入在工作表中先輸入某一或某幾個字母、數(shù)字等代替超長文本(例如商品名稱)，輸入結(jié)束后，利用替換功能將該字母或數(shù)字替換為超長文本即可，操作步驟如下：步驟1：在需要輸入超長文本的單元格中輸入字母b(該字母不能與工作表中的其他內(nèi)容重復，以免誤換)實例2快速輸入超長文本步驟2：按快捷鍵Ctrl＋H，打開“查找和替換”對話框，在“替換”選項卡中，進行如圖所示的設置，即可將工作表中的所有字母b替換為超長文本“DellIns15CR-4528B15.6英寸筆記本電腦”。實例3批量添加前綴和后綴若為已有的數(shù)據(jù)批量添加前綴或后綴，如圖所示批量添加前綴的效果，可使用文本連接符號&將前綴或后綴與已有數(shù)據(jù)進行連接，實現(xiàn)批量添加前綴或后綴的效果。操作方法如下：實例3批量添加前綴和后綴1.批量添加前綴方法1：前綴直接連接已有數(shù)據(jù)步驟1：在C2單元格中輸入公式：="研發(fā)部-"&A2，如圖所示，按Enter鍵確認輸入。步驟2：鼠標指向C2單元格右下角的填充柄，按住鼠標左鍵拖動填充柄至C15單元格，即可批量添加前綴。步驟3：若要更改前綴信息，需要更改C2單元格中的公式，然后拖動填充柄至指定位置即可。實例3批量添加前綴和后綴方法2：將前綴指定在某一單元格中。步驟1：在E2單元格中輸入前綴信息：研發(fā)部-，如左圖所示。步驟2：在C2單元格中輸入公式：=$E$2&A2，如右圖所示。公式中的$E$2是絕對引用，其作用是當拖動填充柄向下填充時，公式中的$E$2單元格地址始終保持不變。實例3批量添加前綴和后綴步驟3：按Enter鍵確認輸入，鼠標指向C2單元格右下角的填充柄，按住鼠標左鍵拖動填充柄至C15單元格，即可批量添加前綴，效果如左圖所示。步驟4：若要更改前綴信息，只需更改E2單元格中的信息，C列所有前綴信息自動批量同步更新，如右圖所示。實例3批量添加前綴和后綴綜上，兩種方法都可以實現(xiàn)批量添加前綴的效果，區(qū)別是：第一種方法修改前綴需要修改公式，再將公式填充到指定位置；第二種方法修改前綴只需修改放置前綴信息的指定單元格，即可同步更新公式中的所有前綴。因此，若要經(jīng)常修改前綴，采用第二種方法更為便捷。實例3批量添加前綴和后綴2.批量添加后綴批量添加后綴方法與添加前綴方法相似，可按照添加前綴的方法添加后綴，方法如下：方法1：用已有數(shù)據(jù)直接連接后綴步驟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開頭的編號在實際工作中經(jīng)常會輸入以0開始的編號，如001、002……。此類編號，不能直接在單元格中輸入001，否則，按Enter鍵確認后顯示為1，如圖所示。解決這個問題的途徑可通過設置“自定義”單元格格式將輸入的001顯示為001，設置步驟如下：實例4輸入以0開頭的編號步驟1：選中A列并右鍵單擊，彈出的快捷菜單中，選擇“設置單元格格式”命令，如左圖所示。步驟2：彈出“設置單元格格式”對話框，在“數(shù)字”選項卡中進行如右圖所示的設置。(注：在步驟3中，將光標定位在“類型”文本框中，刪除默認格式“G/通用格式”，并輸入“001”。)實例4輸入以0開頭的編號步驟3：在A3單元格中輸入001，按住Crtl鍵拖動填充柄至A11，即可輸入以0開始的編號，效果如圖中的A列所示。實例5輸入各種常用符號在Excel中輸入數(shù)據(jù)時，經(jīng)常需要輸入一些常用符號，如√、×、※、≠、≥、±、℃等。輸入這些常用符號有多種方法，下面介紹一種快捷的輸入方法，即通過輸入法(以搜狗輸入法為例)輸入常用符號。如圖所示是一些常用的符號，使用輸入法輸入這些符號的方法如下：實例5輸入各種常用符號步驟1：將光標定位在B3單元格中，使用搜狗輸入法輸入A3單元格中的符號名稱“人民幣”的拼音，如圖所示，第5個選項即是“人民幣”對應的符號“￥”。步驟2：單擊符號“￥”或按數(shù)字5鍵，即可將該符號輸入到B3單元格中。步驟3：按照步驟1~2，使用輸入法依次輸入其他符號，并保存在相應的單元格中。實例6在多個單元格中同時輸入數(shù)據(jù)若要在多個連續(xù)或不連續(xù)的單元格中同時輸入相同數(shù)據(jù)時，可以使用批量輸入數(shù)據(jù)的方法快速輸入數(shù)據(jù)。例如，在如左圖所示的不連續(xù)的單元格區(qū)域A2:B3、D2:E3、C5:D7中同時輸入相同內(nèi)容“Execl數(shù)據(jù)分析”，輸入方法如下：步驟1：選中A2:B3單元格區(qū)域，按住Ctrl鍵，依次選中D2:E3、C5:D7兩個單元格區(qū)域，如右圖所示。實例6在多個單元格中同時輸入數(shù)據(jù)步驟2：輸入“Execl數(shù)據(jù)分析”(注意此時不要按Ente鍵)，如上圖所示。步驟3：按Ctrl+Enter鍵，則選中的3個不連續(xù)的單元格區(qū)域中同時輸入了相同內(nèi)容“Execl數(shù)據(jù)分析”，如下圖所示。實例7在多張工作表中同時輸入數(shù)據(jù)若要在多張工作表中同時輸入數(shù)據(jù)，首先選中要同時輸入數(shù)據(jù)的多張工作表，然后再輸入數(shù)據(jù)，即可一次性的為選中的多張工作表同時輸入數(shù)據(jù)。例如，如圖所示的工作簿包含了4張工作表，現(xiàn)在要在3張工作表“北區(qū)”、“南區(qū)”、“西區(qū)”中同時輸入列標題：“圖書名稱”、“單價”、“銷量”，并進行相應格式的設置。操作步驟如下：實例7在多張工作表中同時輸入數(shù)據(jù)步驟1：單擊“北區(qū)”工作表標簽，按住Shift鍵，再單擊“西區(qū)”工作表標簽，將3張工作表同時選中，此時工作簿標題欄的文件名后會出現(xiàn)“[工作組]”字樣，表示將3張工作表組合為一個組，如左圖所示。步驟2：輸入列標題，并設置其格式，如中間圖所示。因為3張工作表在一個組中，在這組中的任何操作都會同步應用到該組的每一張工作表，因此一次性在3張工作表“北區(qū)”、“南區(qū)”、“西區(qū)”中同時輸入內(nèi)容和設置了相同格式，效果如右圖所示。實例7在多張工作表中同時輸入數(shù)據(jù)步驟3：在工作表標簽上右鍵單擊，彈出的快捷菜單中選擇“取消組合工作表”命令，取消已組合的工作表。此例中3張工作表是連續(xù)的，所以按住Shift鍵，單擊首尾工作表標簽，選中了連續(xù)的3張工作表。若要選中不連續(xù)的多張工作表，先單擊某張工作表標簽，然后按住Ctrl鍵，再分別單擊要選中的工作表標簽即可。1.2批量導入各類數(shù)據(jù)Importvarioustypesofdatainbatches實例8導入TXT文件中的數(shù)據(jù)TXT文件主要是從平臺或系統(tǒng)導出的數(shù)據(jù)，如圖所示。下面以將“學生檔案.txt”文本文件導入“成績”工作簿“Sheet1”工作表中為例，介紹將TXT文件中的數(shù)據(jù)導入Excel中的方法，操作步驟如下。實例8導入TXT文件中的數(shù)據(jù)步驟1：打開“成績”工作簿，在“Sheet1”工作表中單擊用于存放數(shù)據(jù)的起始單元格A1。步驟2：打開“數(shù)據(jù)”選項卡，進行如左圖所示的設置。步驟3：彈出“文本導入向?qū)?第1步”對話框，進行如右圖所示的設置。實例8導入TXT文件中的數(shù)據(jù)步驟4：在“文本導入向?qū)?第2步”對話框中，進行如左圖所示的設置。步驟5：在“文本導入向?qū)?第3步”對話框中，進行如右圖所示的設置。實例9導入數(shù)據(jù)庫中的數(shù)據(jù)當工作中需要的數(shù)據(jù)來自于數(shù)據(jù)庫中的幾十條甚至幾萬條數(shù)據(jù)時，面對大量的數(shù)據(jù)，應該如何操作才能把數(shù)據(jù)庫中的數(shù)據(jù)導入Excel中，并且保持同步更新呢？下面通過一個實例講解將數(shù)據(jù)庫文件中大量數(shù)據(jù)批量導入Excel中的方法。如圖所示“停車場收費表”是Access數(shù)據(jù)庫中的部分數(shù)據(jù)，該數(shù)據(jù)庫共計2000多條記錄，將該數(shù)據(jù)庫中的數(shù)據(jù)批量導入Excel中方法如下：實例8導入數(shù)據(jù)庫中的數(shù)據(jù)方法1：通過獲取外部數(shù)據(jù)導入步驟1：打開要導入數(shù)據(jù)的Excel工作簿，選中A1單元格，單擊“數(shù)據(jù)”選項卡中的“自Access”按鈕，彈出“選取數(shù)據(jù)源”對話框，進行如左圖所示的設置。步驟2：在“導入數(shù)據(jù)”對話框中進行如右圖所示的設置，即可將Access數(shù)據(jù)庫文件-“停車場收費”中的數(shù)據(jù)批量導入Excel中。實例9導入數(shù)據(jù)庫中的數(shù)據(jù)步驟3：當數(shù)據(jù)庫中的數(shù)據(jù)更新后，只需單擊“表格工具””“設計”選項卡中的“刷新”按鈕或按快捷鍵Alt＋F5，如圖所示，即可同步更新Excel中的數(shù)據(jù)。實例9導入數(shù)據(jù)庫中的數(shù)據(jù)方法2：通過查詢功能導入數(shù)據(jù)步驟1：打開要導入數(shù)據(jù)的Excel工作簿，選中A1單元格，單擊“數(shù)據(jù)”選項卡中的“新建查詢”按鈕，打開的下拉列表中進行如左圖所示的設置。步驟2：彈出“導入數(shù)據(jù)”對話框，，選擇要導入的數(shù)據(jù)庫文件，單擊“導入”按鈕，如右圖所示。實例9導入數(shù)據(jù)庫中的數(shù)據(jù)步驟3：Excel啟動查詢與數(shù)據(jù)源連接，連接過程中彈出如左圖所示的提示框。數(shù)據(jù)量越大，連接過程所需要的時間越長。步驟4：在“導航器”對話框中，進行如右圖所示的設置。實例9導入數(shù)據(jù)庫中的數(shù)據(jù)步驟5：在“查詢編輯器”窗口中，如左圖所示，需要對數(shù)據(jù)格式進行轉(zhuǎn)換以符合后續(xù)數(shù)據(jù)處理要求，如選中“進場日期”列，將其設置為“日期”格式，按照相同的方法，依次將“進場時間”列設置為“時間”格式、“出場日期”列設置為“日期”格式、“出場時間”列設置為“時間”格式、“停放時間”列設置為“時間”格式，更改格式后的效果如右圖所示。實例9導入數(shù)據(jù)庫中的數(shù)據(jù)步驟6：單擊“關(guān)閉并上載”按鈕，將“查詢編輯器”窗口中轉(zhuǎn)換的結(jié)果導入Excel中，如圖所示。步驟7：當數(shù)據(jù)庫中的數(shù)據(jù)更新后，只需單擊“表格工具”“設計”選項卡中的“刷新”按鈕或按快捷鍵Alt＋F5，即可同步更新Excel中的數(shù)據(jù)。實例10從多個字段中導入部分字段前兩個實例是將TXT文本文件或數(shù)據(jù)庫文件中的所有字段數(shù)據(jù)批量導入Excel中，如果只導入文件中部分字段數(shù)據(jù)，可將文件中多余的字段刪除后再導入。如圖所示“商貿(mào)銷售統(tǒng)計.txt”是TXT文本文件，將文件中的“序號”、“商品代碼”、“品牌”、“銷量”字段導入Excel中，其余無須導入，導入方法有2種。實例10從多個字段中導入部分字段方法1：通過獲取外部數(shù)據(jù)導入部分字段步驟1：打開要導入數(shù)據(jù)的Excel工作簿，A1單元格，單擊“數(shù)據(jù)”選項卡中的“自文本”按鈕，彈出的對話框中選擇要導入的文本文件，如圖所示。步驟2：在文本導入向?qū)У?步和第2步對話框中選擇默認設置，依次單擊“下一步”按鈕。實例10從多個字段中導入部分字段步驟3：在文本導入向?qū)У?步對話框中，依次選中無須導入的字段所在列，然后選中“不導入此列（跳過）”單選按鈕，單擊“完成”按鈕，如左圖所示。步驟4：彈出的對話框中設置數(shù)據(jù)的放置位置，單擊“確定”按鈕，即可跳過無須導入的列，將需要列的數(shù)據(jù)導入到Excel中，如右圖所示。實例10從多個字段中導入部分字段方法2：通過查詢功能導入部分字段步驟1：打開要導入數(shù)據(jù)的Excel工作簿，選中A1單元格，單擊“數(shù)據(jù)”選項卡中的“新建查詢”按鈕，打開的下拉列表中進行如左圖所示的設置。步驟2：彈出的對話框中選擇要導入的TXT文本文件“商貿(mào)銷售統(tǒng)計”，單擊“導入”按鈕，進入“查詢編輯器”對話框，按住Ctrl鍵依次選中無須導入的3列，單擊“刪除列”按鈕，如右圖所示。實例10從多個字段中導入部分字段步驟3：單擊“關(guān)閉并上載”按鈕，如左圖所示，將查詢編輯器中的數(shù)據(jù)導入Excel中，效果如右圖所示。1.3智能提取各類數(shù)據(jù)Intelligentextractionofallkindsofdata實例11從登記表中提取姓名在下圖中左側(cè)表格是員工登記表，現(xiàn)在需要從登記表中的A列提取姓名信息，并將其放置在C列。操作方法：首先在C2單元格中輸入第一個員工的姓名“王一涵”，然后按快捷鍵Ctrl＋E，即可將其他員工的姓名填充到C列對應位置，如右圖表格所示。在C2單元格先輸入姓名后使用快捷鍵的原因是：先給Excel做出一個示范，明確提取規(guī)則，然后再按照規(guī)則使用快捷鍵進行一鍵填充。如果填充的數(shù)據(jù)較復雜，為了保證填充數(shù)據(jù)的準確性，可以多輸入幾個數(shù)據(jù)(一般不超過4個)作為示范規(guī)則，再使用快捷鍵Ctrl＋E填充即可。實例12從身份證號碼中提取出生日期下圖中左側(cè)表格A列表示身份證號碼，其中紅色數(shù)字(8位)代表出生日期，將出生日期提取出來并放置在B列對應單元格中。方法：首先在B2單元格中輸入第一個出生日期“19910902”，然后按快捷鍵Ctrl＋E，即可從A列批量提取出生日期填充到B列對應的單元格中。實例13提取姓名和地點信息并分行顯示如圖所示的表格中，A列每個單元格的內(nèi)容較長，為了方便排版，將A列中內(nèi)容提取并分兩行顯示，一行為報銷人姓名，另一行為活動地點信息，如圖所示中的B列所示。操作步驟如下：實例13提取姓名和地點信息并分行顯示步驟1：將A2單元格中的內(nèi)容復制粘貼到B2單元格，如圖所示。實例13提取姓名和地點信息并分行顯示步驟2：將光標放置在“活動地點”前，按快捷鍵Alt＋Enter，將報銷人和活動地點分行顯示，如圖所示。步驟3：按快捷鍵Ctrl＋E，即可批量提取A列中的姓名和活動地點信息并換行顯示在B列對應的單元格中。1.4智能合并各類數(shù)據(jù)Intelligentlymergealltypesofdata實例14合并市、區(qū)、街道多列信息將圖左側(cè)表格中的A、B、C三列的市、區(qū)、街道進行合并，在C列街道名稱的后面添加“街道”，再進行全部合并，合并效果如圖右側(cè)表格所示。合并方法：在D2單元格輸入“南京市玄武區(qū)紅山街道”，然后按快捷鍵Ctrl＋E，即可將A、B、C三列批量合并并填充到D列對應的單元格中。的單元格中。實例15合并姓名和職位信息在左圖中，將A列姓氏與B列職務組合，將結(jié)果存放在C列，組合后的效果如右圖所示。組合方法：首先在C2單元格中輸入張院長，然后按快捷鍵Ctrl＋E即可將A列姓氏與B列職務組合并填充到C列對應的單元格中。1.5一鍵匯總數(shù)據(jù)Summarydatainoneclick實例16一鍵匯總多列數(shù)據(jù)某電力公司各銷售員1-4月銷售某種商品的情況如左圖所示，現(xiàn)要求匯總每個月銷售總量，即對每列數(shù)據(jù)進行求和。此類求和問題，可以在匯總行中使用SUM函數(shù)先求出B列數(shù)據(jù)的總和，然后再拖動填充柄至E列求出其他各列總和。這種方法雖然正確，但操作相對麻煩，更為便捷的匯總方法是使用快捷鍵Alt＋=進行一鍵匯總。操作方法如下：選中B2:E14單元格區(qū)域，如左圖所示，按快捷鍵Alt＋=，即可瞬間完成對多列數(shù)據(jù)的匯總，如右圖所示。實例17一鍵匯總多行數(shù)據(jù)某電力公司要查看各銷售員銷售情況，要求匯總出每位銷售員1-4月總銷售量，如左圖所示，即對每行數(shù)據(jù)進行求和，同樣可以使用快捷鍵Alt＋=，瞬間得到匯總結(jié)果。選中B2:G13單元格區(qū)域，按快捷鍵Alt＋=，即可瞬間完成對多行數(shù)據(jù)的匯總，如右圖所示。實例18一鍵匯總多行和多列數(shù)據(jù)在如圖所示的