《Excel數(shù)據(jù)獲取與處理實(shí)戰(zhàn)》 課件 第2章 外部數(shù)據(jù)的獲取

外部數(shù)據(jù)的獲取學(xué)習(xí)目標(biāo)1、獲取文本數(shù)據(jù)2、獲取網(wǎng)站數(shù)據(jù)3、獲取其他來源數(shù)據(jù)1獲取網(wǎng)站數(shù)據(jù)目錄獲取文本數(shù)據(jù)2獲取MySQL數(shù)據(jù)庫中的數(shù)據(jù)3常見的文本數(shù)據(jù)的格式為TXT和CSV。以“客戶信息.txt”數(shù)據(jù)和“客戶信息.csv”數(shù)據(jù)為例，在Excel2016中獲取文本數(shù)據(jù)。在Excel2016中導(dǎo)入“客戶信息.txt”數(shù)據(jù)的具體操作步驟如下。（1）打開【導(dǎo)入文本文件】對話框新建一個空白工作簿，在【數(shù)據(jù)】選項(xiàng)卡的【獲取外部數(shù)據(jù)】命令組中，單擊【自文本】圖標(biāo)，如圖所示，彈出【導(dǎo)入文本文件】對話框。1、獲取TXT文本數(shù)據(jù)（2）選擇要導(dǎo)入數(shù)據(jù)的TXT文件在【導(dǎo)入文本文件】對話框中，選擇“客戶信息.txt”文件，如圖所示，單擊【導(dǎo)入】按鈕，彈出【文本導(dǎo)入向?qū)?第1步，共3步】對話框。1、獲取TXT文本數(shù)據(jù)（3）選擇最合適的數(shù)據(jù)類型在【文本導(dǎo)入向?qū)?第1步，共3步】對話框中，默認(rèn)選擇【分隔符號】單選框，如圖所示，單擊【下一步】按鈕，彈出【文本導(dǎo)入向?qū)?第2步，共3步】對話框。1、獲取TXT文本數(shù)據(jù)（4）選擇合適的分隔符號在【文本導(dǎo)入向?qū)?第2步，共3步】對話框中，勾選【Tab鍵】【空格】復(fù)選框，系統(tǒng)會自動勾選【連續(xù)分隔符號視為單個處理（R）】，如圖所示，單擊【下一步】按鈕，彈出【文本導(dǎo)入向?qū)?第3步，共3步】對話框中。1、獲取TXT文本數(shù)據(jù)（5）選擇數(shù)據(jù)格式在【文本導(dǎo)入向?qū)?第3步，共3步】對話框中，默認(rèn)選擇【常規(guī)】單選框，如圖所示。1、獲取TXT文本數(shù)據(jù)（6）設(shè)置數(shù)據(jù)的放置位置并確定導(dǎo)入數(shù)據(jù)單擊上圖中所示的【完成】按鈕，在彈出的【導(dǎo)入數(shù)據(jù)】對話框中默認(rèn)選擇【現(xiàn)有工作表】單選框，單擊按鈕，選擇單元格A1，再單擊按鈕，如圖所示，單擊【確定】按鈕。導(dǎo)入數(shù)據(jù)后，Excel會將導(dǎo)入的數(shù)據(jù)作為外部數(shù)據(jù)區(qū)域，當(dāng)原始數(shù)據(jù)有改動時，可以單擊【連接】命令組的【全部刷新】圖標(biāo)刷新數(shù)據(jù)，此時Excel中的數(shù)據(jù)會變成改動后的原始數(shù)據(jù)。1、獲取TXT文本數(shù)據(jù)在Excel2016中導(dǎo)入CSV文本數(shù)據(jù)的步驟與導(dǎo)入TXT文本數(shù)據(jù)的步驟類似，導(dǎo)入“客戶信息.csv”數(shù)據(jù)的具體操作步驟如下。（1）打開【導(dǎo)入文本文件】對話框新建一個空白工作簿，在【數(shù)據(jù)】選項(xiàng)卡的【獲取外部數(shù)據(jù)】命令組中，單擊【自文本】圖標(biāo)，彈出【導(dǎo)入文本文件】對話框。2、獲取CSV文本數(shù)據(jù)（2）選擇要導(dǎo)入數(shù)據(jù)的CSV文件在【導(dǎo)入文本文件】對話框中，選擇“客戶信息.csv”文件，如圖所示，單擊【導(dǎo)入】按鈕，彈出【文本導(dǎo)入向?qū)?第1步，共3步】對話框。2、獲取CSV文本數(shù)據(jù)（3）選擇最合適的數(shù)據(jù)類型在【文本導(dǎo)入向?qū)?第1步，共3步】對話框中，默認(rèn)選擇【分隔符號】單選框，如圖所示，單擊【下一步】按鈕，彈出【文本導(dǎo)入向?qū)?第2步，共3步】對話框。2、獲取CSV文本數(shù)據(jù)（4）選擇合適的分隔符號在【文本導(dǎo)入向?qū)?第2步，共3步】對話框中，勾選【Tab鍵】【逗號】復(fù)選框，如圖所示，單擊【下一步】按鈕，彈出【文本導(dǎo)入向?qū)?第3步，共3步】對話框。2、獲取CSV文本數(shù)據(jù)（5）選擇數(shù)據(jù)格式在【文本導(dǎo)入向?qū)?第3步，共3步】對話框中，默認(rèn)選擇【常規(guī)】單選框，如圖所示，單擊【完成】按鈕。2、獲取CSV文本數(shù)據(jù)（6）設(shè)置數(shù)據(jù)的放置位置并確定導(dǎo)入數(shù)據(jù)單擊獲取TXT文本數(shù)據(jù)中步驟（5）中的圖所示的【完成】按鈕，在彈出的【導(dǎo)入數(shù)據(jù)】對話框中默認(rèn)選擇【現(xiàn)有工作表】單選框，單擊按鈕，選擇單元格A1，再單擊按鈕，如圖所示，單擊【確定】按鈕。2、獲取CSV文本數(shù)據(jù)1獲取網(wǎng)站數(shù)據(jù)目錄獲取文本數(shù)據(jù)2獲取MySQL數(shù)據(jù)庫中的數(shù)據(jù)3網(wǎng)站數(shù)據(jù)是獲取數(shù)據(jù)的一個重要來源。在Excel2016中分別用兩種方法導(dǎo)入北京市統(tǒng)計(jì)局網(wǎng)站和上海市統(tǒng)計(jì)局網(wǎng)站的相關(guān)數(shù)據(jù)。獲取網(wǎng)站數(shù)據(jù)Excel2016從網(wǎng)站導(dǎo)入數(shù)據(jù)，檢索到的數(shù)據(jù)包括網(wǎng)頁上的單個表格、多個表格或所有文件，不包括圖片和腳本內(nèi)容，所以有些網(wǎng)站的圖片信息等無法獲取。在Excel2016中導(dǎo)入北京市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)的具體操作步驟如下。（1）打開【新建web查詢】對話框打開【新建Web查詢】對話框。新建一個空白工作簿，在【數(shù)據(jù)】選項(xiàng)卡的【獲取外部數(shù)據(jù)】命令組中，單擊【自網(wǎng)站】命令，如圖所示，彈出【新建web查詢】對話框。1、獲取北京市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)（2）打開北京市統(tǒng)計(jì)局網(wǎng)站打開【新建Web查詢】對話框的【地址】文本框收到輸入或者復(fù)制相關(guān)地址/nj/main/2018-tjnj/zk/indexch.htm，單擊【轉(zhuǎn)到】進(jìn)行跳轉(zhuǎn)。1、獲取北京市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)（3）選擇網(wǎng)站數(shù)據(jù)利用鼠標(biāo)進(jìn)行數(shù)據(jù)選擇。（4）設(shè)置格式設(shè)置數(shù)據(jù)導(dǎo)入格式。（5）選擇位置并導(dǎo)入到數(shù)據(jù)單擊【導(dǎo)入】按鈕，設(shè)置導(dǎo)入位置完成數(shù)據(jù)的導(dǎo)入。1、獲取北京市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)在Excel2016中導(dǎo)入上海市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)的具體操作步驟如下。（1）打開【從Web】對話框新建一個空白工作簿，在【數(shù)據(jù)】選項(xiàng)卡的【獲取外部數(shù)據(jù)】命令組中選擇【新建查詢】命令，在下拉菜單中依次選擇【從其他源】和【自網(wǎng)站】命令，如圖所示。2、獲取上海市市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)在Excel2016中導(dǎo)入上海市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)的具體操作步驟如下。（1）打開【從Web】對話框新建一個空白工作簿，在【數(shù)據(jù)】選項(xiàng)卡的【獲取外部數(shù)據(jù)】命令組中選擇【新建查詢】命令，在下拉菜單中依次選擇【從其他源】和【自網(wǎng)站】命令，如圖所示。2、獲取上海市市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)（2）打開上海市統(tǒng)計(jì)局網(wǎng)站在【從Web】對話框的【URL】文本框中收到輸入或者復(fù)制粘貼網(wǎng)址，如圖所示。2、獲取上海市市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)（3）選擇和導(dǎo)入數(shù)據(jù)表在【導(dǎo)航器】對話框中的【顯示選項(xiàng)】列表框中選擇【Table0】，單擊【加載】完成導(dǎo)入數(shù)據(jù)，如圖所示。2、獲取上海市市統(tǒng)計(jì)局網(wǎng)站數(shù)據(jù)1獲取網(wǎng)站數(shù)據(jù)目錄獲取文本數(shù)據(jù)2獲取MySQL數(shù)據(jù)庫中的數(shù)據(jù)3Excel2016可以獲取外部數(shù)據(jù)庫的數(shù)據(jù)，如MySQL、Access等，但在此之前需新建數(shù)據(jù)源并進(jìn)行連接。以MySQL數(shù)據(jù)庫為例，會員信息存在“data”數(shù)據(jù)庫的“info”表中，需要新建與連接一個MySQL數(shù)據(jù)源，而后在Excel2016中導(dǎo)入“info”數(shù)據(jù)。新建與連接MySQL數(shù)據(jù)源的具體操作步驟如下。1、新建與連接MySQL數(shù)據(jù)源（1）打開【ODBC數(shù)據(jù)源管理程序（64位）】對話框在電腦【開始】菜單中打開【控制面板】窗口，依次選擇【系統(tǒng)和安全】→【管理工具】，彈出【管理工具】窗口，如圖所示。1、新建與連接MySQL數(shù)據(jù)源雙擊上圖中的【ODBC數(shù)據(jù)源（64位）】程序，彈出【ODBC數(shù)據(jù)源管理程序（64位）】對話框，如圖所示。如果是64位操作系統(tǒng)的計(jì)算機(jī)，那么選擇【ODBC數(shù)據(jù)源（32位）】或【ODBC數(shù)據(jù)源（64位）】程序都可以。如果是32位操作系統(tǒng)的計(jì)算機(jī)，那么只能選擇【ODBC數(shù)據(jù)源（32位）】程序。1、新建與連接MySQL數(shù)據(jù)源（2）打開【創(chuàng)建新數(shù)據(jù)源】對話框在【ODBC數(shù)據(jù)源管理程序（64位）】對話框中單擊【添加】按鈕，彈出【創(chuàng)建新數(shù)據(jù)源】對話框，如圖所示。1、新建與連接MySQL數(shù)據(jù)源（3）打開【MySQLConnector/ODBCDataSourceConfiguration】對話框在【創(chuàng)建新數(shù)據(jù)源】對話框中，選擇【選擇您想為其安裝數(shù)據(jù)源的驅(qū)動程序】列表框中【MySQLODBC8.0UnicodeDriver】，單擊【完成】按鈕，彈出【MySQLConnector/ODBCDataSourceConfiguration】對話框，如圖所示，其中每個英文名詞的解釋如下。1、新建與連接MySQL數(shù)據(jù)源上圖中的各文本框含義解釋如下。DataSourceName是數(shù)據(jù)源名稱，在【DataSourceName】文本框中可輸入自定義名稱。Description是描述，在【Description】文本框中可輸入對數(shù)據(jù)源的描述。TCP/IPServer是TCP/IP服務(wù)器，如果數(shù)據(jù)庫在本機(jī)，就在【TCP/IPServer】單選框的第一個文本框中輸入localhost（本機(jī)）；如果數(shù)據(jù)庫不在本機(jī)，就輸入數(shù)據(jù)庫所在的IP。User和Password分別為用戶名和密碼，在下載MySQL時自定義設(shè)置。Database是數(shù)據(jù)庫，在【Database】下拉框中可選擇所需連接的數(shù)據(jù)庫。1、新建與連接MySQL數(shù)據(jù)源（4）設(shè)置參數(shù)在【MySQLConnector/ODBCDataSourceConfiguration】對話框的【DataSourceName】文本框中輸入“會員信息”，在【Description】文本框中輸入“某餐飲企業(yè)的會員信息”，在【TCP/IPServer】單選框的第一個文本框中輸入“l(fā)ocalhost”，在【User】文本框中輸入用戶名，在【Password】文本框中輸入密碼，在【Database】下拉框中選擇【data】，如圖所示。1、新建與連接MySQL數(shù)據(jù)源（5）測速連接單擊上圖中所示的【Test】按鈕，彈出【TestResult】對話框，若顯示【ConnectionSuccessful】則說明連接成功，如圖所示。單擊【確定】按鈕返回到【MySQLConnector/ODBCDataSourceConfiguration】對話框。1、新建與連接MySQL數(shù)據(jù)源（6）確定添加數(shù)據(jù)源單擊新建與連接MySQL數(shù)據(jù)源步驟（4）圖中所示的【OK】按鈕，返回到【ODBC數(shù)據(jù)源管理程序（64位）】對話框，如圖所示，單擊【確定】按鈕即可成功添加數(shù)據(jù)源。1、新建與連接MySQL數(shù)據(jù)源在Excel2016中導(dǎo)入MySQL數(shù)據(jù)庫的數(shù)據(jù)，具體的操作步驟如下。（1）打開【數(shù)據(jù)連接向?qū)?歡迎使用數(shù)據(jù)連接向?qū)А繉υ捒騽?chuàng)建一個空白工作簿，在【數(shù)據(jù)】選項(xiàng)卡的【獲取外部數(shù)據(jù)】命令組中，單擊【自其他來源】圖標(biāo)，在下拉菜單中選擇【來自數(shù)據(jù)連接向?qū)А棵?，如圖所示。2、導(dǎo)入MySQL數(shù)據(jù)庫的數(shù)據(jù)完成選擇之后，即可彈出【數(shù)據(jù)連接向?qū)?歡迎使用數(shù)據(jù)連接向?qū)А繉υ捒?，如圖所示。2、導(dǎo)入MySQL數(shù)據(jù)庫的數(shù)據(jù)（2）選擇要連接的數(shù)據(jù)源在【數(shù)據(jù)連接向?qū)?歡迎使用數(shù)據(jù)連接向?qū)А繉υ捒虻摹灸胍B接哪種數(shù)據(jù)源】列表框中選擇【ODBCDSN】，單擊【下一步】按鈕，彈出【數(shù)據(jù)連接向?qū)?連接ODBC數(shù)據(jù)源】對話框，如圖所示。2、導(dǎo)入MySQL數(shù)據(jù)庫的數(shù)據(jù)（3）選擇要連接的ODBC數(shù)據(jù)源在【數(shù)據(jù)連接向?qū)?連接ODBC數(shù)據(jù)源】對話框的【ODBC數(shù)據(jù)源】列表框中選擇【會員信息】，單擊【下一步】按鈕，彈出【數(shù)據(jù)連接向?qū)?選擇數(shù)據(jù)庫和表】對話框，如圖所示。2、導(dǎo)入MySQL數(shù)據(jù)庫的數(shù)據(jù)（4）選擇包含所需數(shù)據(jù)的數(shù)據(jù)庫和表在【數(shù)據(jù)連接向?qū)?選擇數(shù)據(jù)庫和表】對話框的【選擇包含您所需的數(shù)據(jù)的數(shù)據(jù)庫】列表框中單擊