數(shù)據(jù)實戰(zhàn)技能培訓(xùn)-excel使用技能提高

1、數(shù)據(jù)分析實戰(zhàn)技能培訓(xùn)Office技能幫助QQ群：24274733（已滿）31366936 講師：陳理一、數(shù)據(jù)錄入數(shù)據(jù)類型：單元格的數(shù)據(jù)類型和單元格內(nèi)數(shù)據(jù)的數(shù)據(jù)類型是獨立的1、在輸入數(shù)據(jù)之前，改變單元格的類型，再輸入數(shù)據(jù)，可以達(dá)到輸入數(shù)據(jù)類型和單元格設(shè)置類型一致的結(jié)果。2、在已有數(shù)據(jù)情況下，并不能通過改單元格格式來改變數(shù)據(jù)格式。建議采用分列的方法（數(shù)據(jù)>分列），在最后一步的“列數(shù)據(jù)格式”進(jìn)行選擇就可以將數(shù)據(jù)格式進(jìn)行改變。【案例1】Excel里面最多支持15位數(shù)字，超過15位后面數(shù)字丟失變成0，如身份證號碼等，不能直接敲，可以選擇的方法：1） 將單元格格式改成文本后再敲數(shù)據(jù)2） 建議英文的單

2、引號+直接輸入總結(jié)：敲之前，單引號；敲之后，用分列【案例2】要求數(shù)據(jù)是上下行文本類型編號連續(xù)的，但輸入時長文本格式數(shù)據(jù)不能通過“拖”的方式序次增加發(fā)現(xiàn)“文本+數(shù)字”的形式，向下托，數(shù)字可以變，可以在需要變和不變的之間敲個空格或者“A”等，強制將后面的數(shù)據(jù)變成數(shù)值，再采用“拖”的方式完成數(shù)據(jù)錄入，再用替換的方法去除加入的多余字符二、行列轉(zhuǎn)換【案例1】數(shù)據(jù)文件中有特殊符號，如何替換特殊符號：將特殊符號ctrl+C，到替換位置ctrl+V，再進(jìn)行替換【案例2】數(shù)據(jù)行列轉(zhuǎn)換1、 一列變成多列：ø 需要借助word，將數(shù)據(jù)拷貝到word文檔中，選擇“表格>轉(zhuǎn)換>將表格轉(zhuǎn)換成文本（選

3、擇段落標(biāo)記）；ø “表格>轉(zhuǎn)換>將文本轉(zhuǎn)換為表格，選擇需要轉(zhuǎn)換的列數(shù)；ø 將轉(zhuǎn)換完成的數(shù)據(jù)拷貝回Excel。 2、 多列變成一列ø 借助word表格，將數(shù)據(jù)拷貝到word文檔中，選擇“表格>轉(zhuǎn)換>將表格轉(zhuǎn)換成文本（選擇段落標(biāo)記）就可以完成注意如果需要保持一定順序，可以借助先在excel中用“選擇性粘貼”將數(shù)據(jù)轉(zhuǎn)置，再進(jìn)行上一步操作（如果數(shù)據(jù)塊行數(shù)不一樣時可能有問題）；另外可以使用添加標(biāo)識的方式，如給數(shù)據(jù)塊中數(shù)據(jù)添加以數(shù)據(jù)所在行列的標(biāo)識（通過函數(shù)column（）、row（），來定位此列的位置，然后再word中轉(zhuǎn)換，貼到excel中再排序，隨后

4、進(jìn)行分列來完成。三、利用選擇性粘貼，進(jìn)行數(shù)據(jù)運算【案例1】某一列數(shù)字固定加減乘除另外一個固定數(shù)字，如圖中所示的一列數(shù)據(jù)同除以100。復(fù)制固定數(shù)字100>選擇準(zhǔn)備運算的列>選擇“選擇性粘貼” ，在“運算”塊內(nèi)選擇相應(yīng)操作即可?！景咐?】如果數(shù)據(jù)是兩列或者兩個表，把兩列的結(jié)果進(jìn)行加減乘除運算，結(jié)果放到第一列。復(fù)制被預(yù)算數(shù)字列>選擇準(zhǔn)備運算的列>選擇“選擇性粘貼” ，在“運算”塊內(nèi)選擇相應(yīng)操作即可?！景咐?】利用“選擇性粘貼”將交錯的兩列數(shù)據(jù)合并。 “選擇性粘貼>選擇跳過空單元；這樣對于前一列數(shù)據(jù)空的地方不貼，只粘貼存在的數(shù)據(jù)。四、定位空值【案例1】數(shù)據(jù)如下圖所示， 要

5、求第一列空格處填充向上的內(nèi)容ø 選中需要填充數(shù)據(jù)的區(qū)域，選擇“編輯>定位>定位條件>空值”；ø 選中所有空格后，直接輸入公式（注意不要再點擊單元格，已經(jīng)選中區(qū)域了），隨后ctrl+enter（目的是在選黑區(qū)域內(nèi)輸入相同內(nèi)容）；ø 用選擇性粘貼將公式變?yōu)閿?shù)值，便于以后計算。五、選擇不重復(fù)記錄【案例1】選擇不重復(fù)記錄ø 選中需要進(jìn)行不重復(fù)記錄的數(shù)據(jù)區(qū)域>“數(shù)據(jù)>篩選>高級篩選”；ø 按照下圖“選擇不重復(fù)的記錄”；ø 得到的結(jié)果是將重復(fù)的紀(jì)錄隱藏的方式，選擇“可見單元格按鈕”，將數(shù)據(jù)復(fù)制到新的工作表中。完

6、成。ø 如果需要恢復(fù)所有數(shù)據(jù)：“數(shù)據(jù)>篩選>全部顯示”不同需求時，注意和排序結(jié)合使用“可見單元格按鈕”顯示在菜單欄的方法：ø 右擊菜單欄，選擇“自定義”，如下圖所示ø 將選定的按鈕拖到菜單欄上階段練習(xí)一下表轉(zhuǎn)換，轉(zhuǎn)換成下圖中右邊的格式。做法與步驟：ø 多列變一列：將除表頭外數(shù)據(jù)拷貝粘貼到word中，參照上面的做法將其變成一列，替換掉空格后，拷回excelø 空值中填入上一行值：將是號碼的數(shù)據(jù)在前一列標(biāo)示出來（用if函數(shù)），然后再用選中空格進(jìn)行相同公式輸入，將首行均為號碼的進(jìn)行篩選刪除。注意：操作過程中，定位可能不成功，將公式運算后的

7、結(jié)果粘貼成數(shù)值，或者用分列完成。階段練習(xí)二對“號碼”該列記錄相同的進(jìn)行匯總。做法如下：再采用排序，篩選不重復(fù)的，得到最后結(jié)果。六、Excel常用函數(shù)1、文本處理left、right、mid、len、rept、find（找的內(nèi)容，在哪里找）找指定在指定單元格中出現(xiàn)的位置Find函數(shù)：在字符串中查找某字符所在位置，如果出錯，則是沒有該字符Left函數(shù)：Rept函數(shù)：加幾位固定數(shù)字2、數(shù)字函數(shù)：Round（數(shù)字，小數(shù)位數(shù)）：四舍五入Roundup（數(shù)字，保留小數(shù)位數(shù)）：有數(shù)就入Rounddown（數(shù)字，保留小數(shù)位數(shù)）：有數(shù)就舍Rand（），01之間的隨機數(shù)【案例1】頭60s算3毛錢，后面每6s算1毛

8、錢，不足6s算6s=IF(通話時長<60,0.3,ROUNDUP(C4-60)/6,0)*0.1+0.3)【案例2】抽取一堆數(shù)字生成隨機數(shù)，選取最大的前多少個3、日期函數(shù)：Year（）返回日期年Month（）返回日期月Day（）返回日期日Date（年，月，日）將非日期變成日期DateDif(小日期，大日期，“Y”) 求日期相距（小日期表示在前的日期，大日期表示之后的日期），“Y”表示相距的年，“M”表示相距的月，“D”表示相距的日Today（）返回當(dāng)前日期注意：1900-1-1之前的日期，excel無法計算；在excel中日期相當(dāng)于數(shù)字，從1900-1-1為1，增加一天加1【案例1】相距

9、的分鐘，兩個時間直接相減，結(jié)果乘14404、匯總函數(shù)：Countif（哪列找，找什么），按條件求個數(shù)；求重復(fù)個數(shù)；找兩個表中的區(qū)別特殊應(yīng)用，按照指定號碼編先后次序，相對地址和絕對地址之間的轉(zhuǎn)換：F4【案例1】Sumif（在哪里找，找什么，把哪列加總）按條件求和【案例2】Vlookup（找的條件，在哪列找，選擇區(qū)域的第幾列，0）查找函數(shù)注意：誰不動，給誰錢（F4進(jìn)行切換），Vlookup函數(shù)可以和column（）函數(shù)綜合使用/-/七、文本文件數(shù)據(jù)導(dǎo)入到excel如何把數(shù)據(jù)拿到excel中來1、如何將文本文件放入excel中來直接在新建的excel中打開 “*.txt”文件：可以直接進(jìn)入

10、數(shù)據(jù)導(dǎo)入向?qū)ё⒁猓浩渲袑?dǎo)入起始行，可以刪除廢數(shù)據(jù)；固定寬度分列時是左邊對齊的八、數(shù)據(jù)透視表范例數(shù)據(jù)：銷售數(shù)據(jù)D.xls建議將數(shù)據(jù)文件和透視表所在文件分開1、生成透視表新建空的excel>透視表>外部數(shù)據(jù)>獲取數(shù)據(jù)如果出現(xiàn)以上情況，將選項中的系統(tǒng)表選中。選中表格，生成透視表。透視表有四個部分：頁字段、行字段（匯總依據(jù)）、列字段（匯總依據(jù)），中間（匯總）；對于匯總后的數(shù)據(jù)還可以修改和刷新：ø 可以對匯總數(shù)據(jù)部分點擊右鍵，選擇字段設(shè)置；ø 雙擊匯總數(shù)據(jù)可以看明細(xì)ø 數(shù)據(jù)透視表相當(dāng)于數(shù)據(jù)庫的交叉表查詢ø 透視表位置選擇右鍵，選擇“刷新”（當(dāng)數(shù)據(jù)

11、源改變時可以直接刷新）ø 如果想周期更新數(shù)據(jù)透視表，做法如下圖?！景咐?】利用透視表可以直接計算百分比，如下：【案例2】利用透視表，可以匯總數(shù)據(jù)右鍵組及顯示明細(xì)數(shù)據(jù)每一種價位的總數(shù)量，同樣上述方法，選擇步長注意：在數(shù)據(jù)的同一個文件做透視表時，選擇一個單元格來建透視表就可以了。頁字段：相當(dāng)于條件區(qū)，可以拆開多個表顯示，如下圖。九、圖示化數(shù)據(jù)方法條件格式圖示化方法，數(shù)據(jù)更清晰條件格式，按照某種條件，將指定區(qū)域自動變成某種特定格式。條件格式可用格式刷，做好一個單元格，其他的單元格用格式刷來刷。條件格式的主要作用：數(shù)字比較多的情況，畫數(shù)據(jù)分布圖；90%情況下，列不動行動；與公式結(jié)合在一起，功

12、能更強大。注意：通過條件格式自動產(chǎn)生數(shù)據(jù)分布圖，一個表中最多支持3種條件格式刪除條件格式：格式>條件格式>刪除.條件格式范例演示：自動畫格線；自動查找是否和前面的數(shù)據(jù)重復(fù)；必填項目填空后藍(lán)底變白底，且星號去掉；銷售價格和出場價格比較，進(jìn)行不同顏色顯示?！景咐?】標(biāo)識“>50”的數(shù)據(jù)分布情況數(shù)據(jù)源：=ROUNDUP(RAND()*100,0)函數(shù)生成一個隨機數(shù)據(jù)區(qū)域塊，作為分析的數(shù)據(jù)源達(dá)到目標(biāo)條件的話，改變顯示形式【案例2】大于目標(biāo)的20%，小于目標(biāo)值分別用不同顏色表示出來?！景咐?】錄入的數(shù)據(jù)累計不能超過20，主要在公式上注意（起點固定，終點不固定）【案例4】輸入必填數(shù)據(jù)，格

13、式變化【案例5】錄入數(shù)據(jù)后自動劃線【案例6】判斷一個區(qū)域有內(nèi)容，則劃線（=countA（區(qū)域）>0）十、數(shù)據(jù)分析時的復(fù)雜圖表1、達(dá)標(biāo)線圖表首先做成標(biāo)準(zhǔn)柱形圖，雙擊縱坐標(biāo)，修改：刻度交叉于：填上目標(biāo)數(shù)可以將刻度線標(biāo)簽修改和圖案修改來進(jìn)行美化圖形2、目標(biāo)線圖表選中目標(biāo)，拖住邊框+鼠標(biāo)左鍵將加入的目標(biāo)值右擊選擇圖標(biāo)類型>改成散點圖雙擊散點誤差線X，選擇顯示方式和修改定值，然后對該散點和線進(jìn)行美化，將散點變成白色，線變成虛線。最后效果：3、 目標(biāo)線圖表2目標(biāo)線不同的情況。將目標(biāo)變成空心柱，實際值圖無框，采用雙坐標(biāo)的概念，然后再進(jìn)行美化工作選項>分類間距，調(diào)整間距雙擊小的坐標(biāo)軸把最大刻

14、度和右邊一樣。最后效果圖如下：4、 累計增長圖將累計的值次序改變調(diào)整間距、將累計值變成白色，美化后如下：5、動態(tài)圖表動態(tài)圖表：主要通過公式（vlookup）將選擇的數(shù)據(jù)先選擇出來，得到需要顯示的數(shù)據(jù)，做成圖表；將選擇出來的數(shù)據(jù)隱藏，就做出了動態(tài)圖表的形式。可以輸入數(shù)據(jù)的有效性來防止輸入超出范圍的值。6、動態(tài)圖表一段時間的情況（滾動條）十一、導(dǎo)入外部數(shù)據(jù)某些系統(tǒng)中導(dǎo)出數(shù)據(jù)，將其放到excel里面，如何應(yīng)對導(dǎo)出的數(shù)據(jù)經(jīng)常變化？合并多個數(shù)據(jù)文件的方法【案例1】無條件拿多個子文件的數(shù)據(jù)，合并成大文件流程和步驟：新建一個excel文件（total.xls）>數(shù)據(jù)>導(dǎo)入外部數(shù)據(jù)（包括列標(biāo)題）&

15、gt;導(dǎo)入一個文件后，移到最后一行的空單元格，重復(fù)上述過程當(dāng)單個文件發(fā)生改變后，打開total.xls后，點擊全部刷新，大文件的數(shù)據(jù)就可以隨之更新。如果不想要表頭，點擊表頭數(shù)據(jù)，點鼠標(biāo)右鍵>數(shù)據(jù)區(qū)域?qū)傩?gt;包含字段名（去掉勾即可）注意：如果目錄太深了，有可能不成功；另外導(dǎo)入時不要將源數(shù)據(jù)文件打開。【案例2】有條件拿數(shù)據(jù)（如，拿3月份、4月份、5月份銷售報表中的龍蝦的數(shù)據(jù)做報表）數(shù)據(jù)>導(dǎo)入外部數(shù)據(jù)>新建數(shù)據(jù)庫查詢>選擇excel>3月份銷售數(shù)據(jù).xls>在條件這里選擇龍蝦的數(shù)據(jù)【案例3】拿多個子表中指定條件的指定列的數(shù)據(jù)Excel中嵌套了Microsoft

16、 Query，子表結(jié)構(gòu)不一樣，可以通過Microsoft Query添加或刪除列?？呻S時回到編輯界面進(jìn)入Microsoft Query進(jìn)行編輯不同表指定的列，不用保存，直接關(guān)閉?！景咐?】動態(tài)條件來獲取多個子表的數(shù)據(jù)（數(shù)據(jù)源：銷售數(shù)據(jù)D.xls）首先按照條件輸入數(shù)據(jù)的格式將數(shù)據(jù)導(dǎo)入表格，如選擇“城市=北京”的數(shù)據(jù)導(dǎo)入到excel中。完成此部操作后，右擊導(dǎo)入的數(shù)據(jù)，選擇“編輯查詢”進(jìn)入Microsoft Query界面，將條件字段中的城市名改為一個提示框（注意：提示框的文字要用括起來）隨后直接關(guān)閉Microsoft Query，選擇“全部刷新”，按照如下格式選擇【案例4】復(fù)合條件查詢在此步驟，設(shè)

17、置多個條件（根據(jù)查詢的要求），設(shè)置完成后將數(shù)據(jù)導(dǎo)入到指定excel中。然后點擊“編輯查詢” ，進(jìn)入Microsoft Query中，按如下格式輸入在按照單條件查詢的進(jìn)行設(shè)置即可【案例5】模糊查詢、選擇性查詢與上述方式一致，只是在選擇條件時用“包含”來實現(xiàn)模糊查詢。Microsoft Query中查詢條件公式如下；%like%霸力建設(shè)%：固定格式Like '%' & 請輸入公司名 & '%'：可變格式【案例6】關(guān)聯(lián)查詢（數(shù)據(jù)源：3月銷售數(shù)據(jù).xls、客戶列表.xls） 根據(jù)公司名關(guān)聯(lián)可以在Microsoft Query中添加表 （如果出現(xiàn)無法訪問，將已有的表刪掉，把3月份表添加，再添加客戶列表），連上關(guān)聯(lián)數(shù)據(jù)（公司名稱）添加客戶列表時注意：表所在目錄不能太長，否則無法找到該表。關(guān)聯(lián)后選擇需要的列拖到下面，直接關(guān)閉就可以了注意：關(guān)聯(lián)時，多對多是錯誤的，一對多可以【案例7】包含查詢雙擊鏈接關(guān)系：可以進(jìn)行連接內(nèi)容的選擇選擇條件區(qū)域：銷售表公司名稱，如果為空（Is Null），三月份沒有發(fā)生過銷售的記錄范例： 五月份賣過東西，但是四月份沒有賣