《Excel數據獲取與處理實戰(zhàn)》 課件 陳青 第1、2章 Excel 2016概述、外部數據的獲取

Excel2016概述學習目標1、了解數據分析流程2、了解數據獲取與處理3、認識EXCEL的用戶界面4、了解工作簿、工作表、單元格的基本內容5、了解工作簿、工作表、單元格的基本操作1認識Excel2016目錄認識數據獲取與處理2數據分析作為大數據技術的重要組成部分，近年來隨著大數據技術的發(fā)展而逐漸成熟。數據分析技能的掌握是一個循序漸進的過程，了解數據分析的流程和應用場景是邁出數據分析的第一步。數據分析是指使用適當的分析方法對收集來的大量數據進行分析，提取數據中有用信息并形成結論，即對數據加以詳細研究和概括總結的過程。數據分析的一個典型的流程圖如圖所示。1、了解數據分析流程需求分析數據獲取數據處理數據分析數據可視化分析與可視化分析報告1、了解數據分析流程步驟內容需求分析需求分析的主要內容是根據業(yè)務、生產和財務等部門的需要，結合現(xiàn)有的數據情況，提出數據分析需求的整體分析方向和分析內容數據獲取數據獲取是數據分析工作的基礎，是指根據需求分析的結果，提取、收集數據，主要的獲取方式有兩種：獲取外部數據與獲取本地數據數據處理數據處理在Excel中是指對數據進行排序、篩選、分類匯總、計數、文字或函數處理等操作，以便于進行數據分析數據分析數據分析包括使用數據透視表、數據透視圖、趨勢線和模擬運算表等工具，發(fā)現(xiàn)數據中的右腳趾信息并得出結論數據可視化數據可視化的意思是將EXCEL中的數據以圖表等形式來展示數據之間的關聯(lián)信息，使用戶能從各個方面對數據進行觀察，從而對數據進行更深入的分析分析與可視化分析與可視化主要是指通過源數據處理得到的各個指標進行分析，發(fā)現(xiàn)數據中的規(guī)律，并借助圖表等可視化的方式來直觀地展現(xiàn)數據之間的關聯(lián)信息，使抽象的信息變得更加清晰、具體，易于觀察分析報告分析報告是以特定的形式把數據分析的過程和結果展示出來，便于需求者了解可以方便地將數據制作成電子表格工作表的容量大，可以存儲大量的數據不必進行編程就能對工作表中的數據進行檢索、分類、排序、篩選等操作利用系統(tǒng)提供的函數可完成各種數據的計算和分析利用各種圖表直觀地顯示數據通過互聯(lián)網可以方便地與任何地域的其他用戶共享數據2、了解數據獲取與處理獲取大量相關數據并通過分析處理，研究出數據發(fā)展規(guī)律，可以幫助企業(yè)快速做出決策，EXCEL2016對數據的獲取與處理的優(yōu)點如下：項目方法適用情形EXCEL2016中常用的獲取數據方法手動輸入數據擁有紙質數據等靜態(tài)數據，數據量較少獲取文本數據數據存儲在文本中，數據量交大獲取網站數據數據在網站上，數據量較大獲取數據庫數據數據存儲在數據庫中，數據量較大EXCEL2016中常用的處理數據方法求和統(tǒng)計數據的和求平均值統(tǒng)計數據的平均數求最大值或最小值突出顯示最大值或最小值排序通過升序和降序查看數據篩選查看特定數據分類匯總把數據進行分類并求和2、了解數據獲取與處理EXCEL2016對數據的獲取與處理的方法如下：3、了解數據分析的應用場景1.客戶分析2.營銷分析3.社交媒體分析4.網絡安全5.設備管理6.交通物流分析7.欺詐行為檢測1認識Excel2016目錄認識數據分析2在Windows10操作系統(tǒng)的計算機中，單擊【開始】選項卡，找到Excel圖標并單擊，或雙擊桌面Excel2016的圖標，打開的用戶界面如圖所示。1、認識用戶界面1）.啟動Excel2016Excel2016用戶界面包括標題欄、功能區(qū)、名稱框、編輯欄、工作表編輯區(qū)和狀態(tài)欄，如圖所示。1、認識用戶界面2）.用戶界面介紹（1）標題欄標題欄位于應用窗口的頂端，如圖所示，包括快速訪問工具欄、當前文件名、應用程序名稱和窗口控制按鈕。由圖所示，框1為快速訪問工具欄，框2為當前文件名，框3為應用程序名稱，框4為窗口控制按鈕。1、認識用戶界面快速訪問工具欄可以快速運行【保存】【撤銷】【恢復】等命令。如果快速訪問工具欄中沒有所需命令，那么可以單擊快速訪問工具欄的按鈕，選擇需要添加的命令，如圖所示。1、認識用戶界面（2）功能區(qū)標題欄的下方是功能區(qū)，如圖所示，由【開始】【插入】【頁面布局】等選項卡組成，每個選項卡又可以分成不同的組，如【開始】選項卡由【剪貼板】【字體】【對齊方式】等命令組組成，每個組又包含了不同的圖標。由圖所示，框1為選項卡，框2為命令組。1、認識用戶界面（3）名稱框和編輯欄功能區(qū)的下方是名稱框和編輯欄，如圖所示。其中，名稱框可以顯示當前活動單元格的地址和名稱，編輯欄可以顯示當前活動單元格中的數據或公式。由圖所示，框1為名稱框，框2為編輯欄。1、認識用戶界面（4）工作表編輯區(qū)名稱框和編輯欄的下方是工作表編輯區(qū)，如圖所示。由文檔窗口、標簽滾動按鈕、工作表標簽、水平滾動滑條和垂直滾動滑條組成。由圖所示，框1為標簽滾動按鈕，框2為工作表標簽，框3為水平滾動滑條，框4為垂直滾動滑條。1、認識用戶界面（5）狀態(tài)欄狀態(tài)欄位于用戶界面底部，如圖所示。由視圖按鈕和縮放模塊組成，用來顯示與當前操作相關的信息。由圖所示，框1為視圖按鈕，框2為縮放模塊。1、認識用戶界面單擊窗口控制按鈕中的【關閉】按鈕，如圖所示，或按組合鍵【Alt+F4】即可關閉Excel2016。1、認識用戶界面3.關閉Excel2016EXCEL中，用于存儲和處理各種數據的電子表格成為工作表，如【Sheet1】的工作表。EXCEL中創(chuàng)建的文件稱為工作簿，工作簿默認包含一個名為【Sheet1】的工作表。2、了解工作簿、工作表和單元格的基本內容1）.工作簿2）.工作表2、了解工作簿、工作表和單元格的基本內容3）.單元格在工作表表中，行和列相交構成單元格，單元格用于存儲公式和數據，可以通過單擊單元格使之成為活動單元格，，用于存儲和處理各種數據的電子表格成為工作表，如圖所示，框1為列，框2為行，圖中的活動單元格為C4。12（1）創(chuàng)建工作簿單擊【文件】選項卡，依次選擇【新建】命令和【空白工作簿】即可創(chuàng)建工作簿，如圖所示。也可以通過按組合鍵【Ctrl+N】的方式快速新建空白工作簿。3、掌握工作簿、工作表和單元格的基本操作1).工作簿的基本操作（2）保存工作簿單擊快速訪問工具欄的【保存】按鈕，即可保存工作簿，如圖左上角所示的第1個圖標。也可以通過按組合鍵【Ctrl+S】的方式快速保存工作簿。3、掌握工作簿、工作表和單元格的基本操作（3）打開和關閉工作簿單擊【文件】選項卡，選擇【打開】命令，或者通過按組合鍵【Ctrl+O】的方式彈出【打開】對話框，如圖所示，再選擇一個工作簿即可打開工作簿。3、掌握工作簿、工作表和單元格的基本操作（3）打開和關閉工作簿單擊【文件】選項卡，選擇【關閉】命令即可關閉工作簿，如圖所示。也可以通過按組合鍵【Ctrl+W】的方式關閉工作簿。3、掌握工作簿、工作表和單元格的基本操作（1）插入工作表在Excel中插入工作表有多種方法，以下介紹兩種常用的方法插入工作表。以【Sheet1】工作表為例，單擊工作表編輯區(qū)按鈕即可在現(xiàn)有工作表的末尾插入一個新的工作表【Sheet2】，如圖所示。3、掌握工作簿、工作表和單元格的基本操作2).工作表的基本操作以【Sheet1】工作表為例，右鍵單擊【Sheet1】工作表，選擇【插入】命令，如圖所示，彈出【插入】對話框，最后單擊【確定】按鈕即可在現(xiàn)有的工作表的之前插入一個新的工作表【Sheet3】，也可以通過組合鍵【Shift+F11】在現(xiàn)有的工作表的之前插入一個新的工作表。3、掌握工作簿、工作表和單元格的基本操作（2）重命名工作表以【Sheet1】工作表為例，右鍵單擊【Sheet1】標簽，選擇【重命名】命令，再輸入新的名字即可重命名，如圖所示。3、掌握工作簿、工作表和單元格的基本操作（3）設置標簽顏色以【Sheet1】標簽為例，右鍵單擊【Sheet1】標簽，選擇【工作表標簽顏色】命令，再選擇新的顏色即可設置標簽顏色，如圖所示。3、掌握工作簿、工作表和單元格的基本操作（4）移動或復制工作表以【Sheet1】工作表為例，單擊【Sheet1】標簽不放，向左或右拖動到新的位置即可移動工作表。以【Sheet1】工作表為例，右鍵單擊【Sheet1】標簽，選擇【移動或復制】命令，在彈出【移動或復制工作表】對話框中，選擇【Sheet1】標簽，勾選【建立副本】按鈕，如圖所示，最后單擊【確定】按鈕即可復制工作表。3、掌握工作簿、工作表和單元格的基本操作（5）隱藏和顯示工作表以【Sheet1】工作表為例，右鍵單擊【Sheet1】標簽，選擇【隱藏】命令，即可隱藏【Sheet1】工作表（注意，只有一個工作表時不能隱藏工作表），如圖所示。3、掌握工作簿、工作表和單元格的基本操作（5）隱藏和顯示工作表若要顯示隱藏的【Sheet1】工作表，則右鍵單擊任意標簽，選擇【取消隱藏】命令，彈出【取消隱藏】對話框，如圖所示，選擇【Sheet1】標簽，單擊【確定】按鈕，即可顯示之前隱藏的工作表【Sheet1】。3、掌握工作簿、工作表和單元格的基本操作（6）刪除工作表以【Sheet1】工作表為例，右鍵單擊【Sheet1】標簽，選擇【刪除】命令，即可刪除工作表，如圖所示。3、掌握工作簿、工作表和單元格的基本操作（1）選擇單元格單擊某單元格可以選擇該單元格，如單擊A1單元格即可選擇A1單元格，此時名稱框會顯示當前選擇的單元格地址為A1，如圖所示。也可以在名稱框中輸入單元格的地址來選擇單元格，如在名稱框中輸入“A1”即可選擇單元格A1。3、掌握工作簿、工作表和單元格的基本操作3).掌握單元格的基本操作（2）選擇單元格區(qū)域單擊要選擇的單元格區(qū)域左上角的第一個單元格不放，拖動鼠標到要選擇的單元格區(qū)域右下方最后一個單元格，松開鼠標即可選擇單元格區(qū)域。如單擊單元格A1不放，拖動鼠標到單元格D6，松開鼠標即可選擇單元格區(qū)域A1:D6，如圖所示。也可以在名稱框中輸入“A1:D6”來選擇單元格區(qū)域A1:D6。如果工作表中的數據太多，還可以通過組合鍵的方式快速批量選擇表格區(qū)域。按組合鍵【Ctrl+Shift+方向箭頭】，按下哪個方向箭頭，被選中的單元格或單元格區(qū)域沿該方向的數據就會被全部選中，直到遇到空白單元格。3、掌握工作簿、工作表和單元格的基本操作本章介紹了數據分析的流程和應用場景。還簡單介紹了電子表格軟件Excel2016，包括Excel2016的啟動方式、用戶界面介紹和Excel2016的關閉方式，以及工作簿、工作表、單元格的基本操作。小結外部數據的獲取學習目標1、獲取文本數據2、獲取網站數據3、獲取其他來源數據1獲取網站數據目錄獲取文本數據2獲取MySQL數據庫中的數據3常見的文本數據的格式為TXT和CSV。以“客戶信息.txt”數據和“客戶信息.csv”數據為例，在Excel2016中獲取文本數據。在Excel2016中導入“客戶信息.txt”數據的具體操作步驟如下。（1）打開【導入文本文件】對話框新建一個空白工作簿，在【數據】選項卡的【獲取外部數據】命令組中，單擊【自文本】圖標，如圖所示，彈出【導入文本文件】對話框。1、獲取TXT文本數據（2）選擇要導入數據的TXT文件在【導入文本文件】對話框中，選擇“客戶信息.txt”文件，如圖所示，單擊【導入】按鈕，彈出【文本導入向導-第1步，共3步】對話框。1、獲取TXT文本數據（3）選擇最合適的數據類型在【文本導入向導-第1步，共3步】對話框中，默認選擇【分隔符號】單選框，如圖所示，單擊【下一步】按鈕，彈出【文本導入向導-第2步，共3步】對話框。1、獲取TXT文本數據（4）選擇合適的分隔符號在【文本導入向導-第2步，共3步】對話框中，勾選【Tab鍵】【空格】復選框，系統(tǒng)會自動勾選【連續(xù)分隔符號視為單個處理（R）】，如圖所示，單擊【下一步】按鈕，彈出【文本導入向導-第3步，共3步】對話框中。1、獲取TXT文本數據（5）選擇數據格式在【文本導入向導-第3步，共3步】對話框中，默認選擇【常規(guī)】單選框，如圖所示。1、獲取TXT文本數據（6）設置數據的放置位置并確定導入數據單擊上圖中所示的【完成】按鈕，在彈出的【導入數據】對話框中默認選擇【現(xiàn)有工作表】單選框，單擊按鈕，選擇單元格A1，再單擊按鈕，如圖所示，單擊【確定】按鈕。導入數據后，Excel會將導入的數據作為外部數據區(qū)域，當原始數據有改動時，可以單擊【連接】命令組的【全部刷新】圖標刷新數據，此時Excel中的數據會變成改動后的原始數據。1、獲取TXT文本數據在Excel2016中導入CSV文本數據的步驟與導入TXT文本數據的步驟類似，導入“客戶信息.csv”數據的具體操作步驟如下。（1）打開【導入文本文件】對話框新建一個空白工作簿，在【數據】選項卡的【獲取外部數據】命令組中，單擊【自文本】圖標，彈出【導入文本文件】對話框。2、獲取CSV文本數據（2）選擇要導入數據的CSV文件在【導入文本文件】對話框中，選擇“客戶信息.csv”文件，如圖所示，單擊【導入】按鈕，彈出【文本導入向導-第1步，共3步】對話框。2、獲取CSV文本數據（3）選擇最合適的數據類型在【文本導入向導-第1步，共3步】對話框中，默認選擇【分隔符號】單選框，如圖所示，單擊【下一步】按鈕，彈出【文本導入向導-第2步，共3步】對話框。2、獲取CSV文本數據（4）選擇合適的分隔符號在【文本導入向導-第2步，共3步】對話框中，勾選【Tab鍵】【逗號】復選框，如圖所示，單擊【下一步】按鈕，彈出【文本導入向導-第3步，共3步】對話框。2、獲取CSV文本數據（5）選擇數據格式在【文本導入向導-第3步，共3步】對話框中，默認選擇【常規(guī)】單選框，如圖所示，單擊【完成】按鈕。2、獲取CSV文本數據（6）設置數據的放置位置并確定導入數據單擊獲取TXT文本數據中步驟（5）中的圖所示的【完成】按鈕，在彈出的【導入數據】對話框中默認選擇【現(xiàn)有工作表】單選框，單擊按鈕，選擇單元格A1，再單擊按鈕，如圖所示，單擊【確定】按鈕。2、獲取CSV文本數據1獲取網站數據目錄獲取文本數據2獲取MySQL數據庫中的數據3網站數據是獲取數據的一個重要來源。在Excel2016中分別用兩種方法導入北京市統(tǒng)計局網站和上海市統(tǒng)計局網站的相關數據。獲取網站數據Excel2016從網站導入數據，檢索到的數據包括網頁上的單個表格、多個表格或所有文件，不包括圖片和腳本內容，所以有些網站的圖片信息等無法獲取。在Excel2016中導入北京市統(tǒng)計局網站數據的具體操作步驟如下。（1）打開【新建web查詢】對話框打開【新建Web查詢】對話框。新建一個空白工作簿，在【數據】選項卡的【獲取外部數據】命令組中，單擊【自網站】命令，如圖所示，彈出【新建web查詢】對話框。1、獲取北京市統(tǒng)計局網站數據（2）打開北京市統(tǒng)計局網站打開【新建Web查詢】對話框的【地址】文本框收到輸入或者復制相關地址/nj/main/2018-tjnj/zk/indexch.htm，單擊【轉到】進行跳轉。1、獲取北京市統(tǒng)計局網站數據（3）選擇網站數據利用鼠標進行數據選擇。（4）設置格式設置數據導入格式。（5）選擇位置并導入到數據單擊【導入】按鈕，設置導入位置完成數據的導入。1、獲取北京市統(tǒng)計局網站數據在Excel2016中導入上海市統(tǒng)計局網站數據的具體操作步驟如下。（1）打開【從Web】對話框新建一個空白工作簿，在【數據】選項卡的【獲取外部數據】命令組中選擇【新建查詢】命令，在下拉菜單中依次選擇【從其他源】和【自網站】命令，如圖所示。2、獲取上海市市統(tǒng)計局網站數據在Excel2016中導入上海市統(tǒng)計局網站數據的具體操作步驟如下。（1）打開【從Web】對話框新建一個空白工作簿，在【數據】選項卡的【獲取外部數據】命令組中選擇【新建查詢】命令，在下拉菜單中依次選擇【從其他源】和【自網站】命令，如圖所示。2、獲取上海市市統(tǒng)計局網站數據（2）打開上海市統(tǒng)計局網站在【從Web】對話框的【URL】文本框中收到輸入或者復制粘貼網址，如圖所示。2、獲取上海市市統(tǒng)計局網站數據（3）選擇和導入數據表在【導航器】對話框中的【顯示選項】列表框中選擇【Table0】，單擊【加載】完成導入數據，如圖所示。2、獲取上海市市統(tǒng)計局網站數據1獲取網站數據目錄獲取文本數據2獲取MySQL數據庫中的數據3Excel2016可以獲取外部數據庫的數據，如MySQL、Access等，但在此之前需新建數據源并進行連接。以MySQL數據庫為例，會員信息存在“data”數據庫的“info”表中，需要新建與連接一個MySQL數據源，而后在Excel2016中導入“info”數據。新建與連接MySQL數據源的具體操作步驟如下。1、新建與連接MySQL數據源（1）打開【ODBC數據源管理程序（64位）】對話框在電腦【開始】菜單中打開【控制面板】窗口，依次選擇【系統(tǒng)和安全】→【管理工具】，彈出【管理工具】窗口，如圖所示。1、新建與連接MySQL數據源雙擊上圖中的【ODBC數據源（64位）】程序，彈出【ODBC數據源管理程序（64位）】對話框，如圖所示。如果是64位操作系統(tǒng)的計算機，那么選擇【ODBC數據源（32位）】或【ODBC數據源（64位）】程序都可以。如果是32位操作系統(tǒng)的計算機，那么只能選擇【ODBC數據源（32位）】程序。1、新建與連接MySQL數據源（2）打開【創(chuàng)建新數據源】對話框在【ODBC數據源管理程序（64位）】對話框中單擊【添加】按鈕，彈出【創(chuàng)建新數據源】對話框，如圖所示。1、新建與連接MySQL數據源（3）打開【MySQLConnector/ODBCDataSourceConfiguration】對話框在【創(chuàng)建新數據源】對話框中，選擇【選擇您想為其安裝數據源的驅動程序】列表框中【MySQLODBC8.0UnicodeDriver】，單擊【完成】按鈕，彈出【MySQLConnector/ODBCDataSourceConfiguration】對話框，如圖所示，其中每個英文名詞的解釋如下。1、新建與連接MySQL數據源上圖中的各文本框含義解釋如下。DataSourceName是數據源名稱，在【DataSourceName】文本框中可輸入自定義名稱。Description是描述，在【Description】文本框中可輸入對數據源的描述。TCP/IPServer是TCP/IP服務器，如果數據庫在本機，就在【TCP/IPServer】單選框的第一個文本框中輸入localhost（本機）；如果數據庫不在本機，就輸入數據庫所在的IP。User和Password分別為用戶名和密碼，在下載MySQL時自定義設置。Database是數據庫，在【Database】下拉框中可選擇所需連接的數據庫。1、新建與連接MySQL數據源（4）設置參數在【MySQLConnector/ODBCDataSourceConfiguration】對話框的【DataSourceName】文本框中輸入“會員信息”，在【Description】文本框中輸入“某餐飲企業(yè)的會員信息”，在【TCP/IPServer】單選框的第一個文本框中輸入“l(fā)ocalhost”，在【User】文本框中輸入用戶名，在【Password】文本框中輸入密碼，在【Database】下拉框中選擇【data】，如圖所示。1、新建與連接MySQL數據源（5）測速連接單擊上圖中所示的【Test】按鈕，彈出【TestResult】對話框，若顯示【ConnectionSuccessful】則說明連接成功，如圖所示。單擊【確定】按鈕返回到【MySQLConnector/ODBCDataSourceConfiguration】對話框。1、新建與連接MySQL數據源（6）確定添加數據源單擊新建與連接MySQL數據源步驟（4）圖中所示的【OK】按鈕，返回到【ODBC數據源管理程序（64位）】對話框，如圖所示，單擊【確定】按鈕即可成功添加數據源。1、新建與連接MySQL數據源在Excel2016中導入MySQL數據庫的數據，具體的操作步驟如下。（1）打開【數據連接向導-歡迎使用數據連接向導】對話框創(chuàng)建一個空白工作簿，在【數據】選項卡