Excel數(shù)據(jù)處理數(shù)據(jù)轉(zhuǎn)換與抽取

數(shù)據(jù)分析運(yùn)營(yíng)北京信息職業(yè)技術(shù)學(xué)院主講教師：馮雷前言/PREFACEExcel數(shù)據(jù)處理數(shù)據(jù)導(dǎo)入數(shù)據(jù)清洗數(shù)據(jù)轉(zhuǎn)化數(shù)據(jù)抽取數(shù)據(jù)計(jì)算目錄/Contents01數(shù)據(jù)轉(zhuǎn)化02數(shù)據(jù)抽取01數(shù)據(jù)轉(zhuǎn)化數(shù)據(jù)轉(zhuǎn)化觀察列的內(nèi)容是否是一個(gè)獨(dú)立的參數(shù)數(shù)據(jù)行列轉(zhuǎn)化不同類型數(shù)據(jù)的轉(zhuǎn)化二維表轉(zhuǎn)化為一維表若每一列都是獨(dú)立的參數(shù)，則該表為一維表若有兩列或兩列以上是同類參數(shù)，則該表屬于二維表數(shù)據(jù)轉(zhuǎn)化某品牌零售企業(yè)的線上銷售渠道除了自建的獨(dú)立網(wǎng)站外，還涉及京東、天貓、蘇寧、當(dāng)當(dāng)?shù)榷鄠€(gè)電商平臺(tái)。為便于今后對(duì)數(shù)據(jù)進(jìn)行處理與分析，請(qǐng)將圖左邊的二維表轉(zhuǎn)化成右邊的一維表。例數(shù)據(jù)轉(zhuǎn)化單擊二維表格中任一單元格，選擇“數(shù)據(jù)→從表格”，彈出創(chuàng)建表對(duì)話框，勾選“表包含標(biāo)題”，如圖所示。單擊“確定”后會(huì)彈出表的查詢編輯器。1數(shù)據(jù)轉(zhuǎn)化在該查詢編輯器中，選擇“轉(zhuǎn)換→逆透視列→逆透視其他列”，如圖所示2數(shù)據(jù)轉(zhuǎn)化隨后出現(xiàn)如圖所示的界面，單擊“開始—關(guān)閉并上載”，便自動(dòng)生成了一個(gè)一維表形式的查詢工作表。根據(jù)需要修改標(biāo)題字段的名稱，去掉篩選按鈕等不需要的外觀即可。302數(shù)據(jù)抽取數(shù)據(jù)抽取數(shù)據(jù)抽取數(shù)據(jù)拆分字段分列字段匹配抽取匹配數(shù)據(jù)截取某個(gè)字段的部分信息。字段分列常用菜單或函數(shù)完成從其他數(shù)據(jù)表中獲取相匹配的字段數(shù)據(jù)，常常通過函數(shù)VLOOKUP、MATCH和INDEX來實(shí)現(xiàn)數(shù)據(jù)抽取—字段分列對(duì)訂單數(shù)據(jù)中“收貨地址”字段進(jìn)行分列，抽取形成“省份”、“城市”兩列字段（如圖右邊兩列所示），以便后期進(jìn)行會(huì)員來源統(tǒng)計(jì)。例數(shù)據(jù)抽取—字段分列選擇數(shù)據(jù)表中要進(jìn)行字段分列的數(shù)據(jù)區(qū)域，單擊“數(shù)據(jù)→數(shù)據(jù)工具→分列”。12數(shù)據(jù)抽取—字段分列在彈出的“文本分列向?qū)б坏?步，共3步”對(duì)話框中，選擇“分隔符號(hào)”單選項(xiàng)，單擊“下一步”。2在“文本分列向?qū)б坏?步，共3步”對(duì)話框中，根據(jù)需要選擇分隔符號(hào)，在此例中，是以空格形式分開的，所以選中“空格”復(fù)選框，單擊“下一步”。在“文本分列向?qū)б坏?步，共3步”對(duì)話框中，根據(jù)需要選擇列數(shù)據(jù)格式為“常規(guī)”、目標(biāo)區(qū)域?yàn)椤?F$2”，在數(shù)據(jù)預(yù)覽區(qū)域忽略從第三列起的后面所有列，如圖所示。單擊“完成”按鈕，字段分列完成。數(shù)據(jù)抽取-字段匹配字段匹配是指從其他數(shù)據(jù)表中獲取相匹配的字段數(shù)據(jù)，這一操作過程常常通過函數(shù)VLOOKUP、MATCH和INDEX來實(shí)現(xiàn)圖中，左邊的兩張表分別為某店鋪和同行業(yè)平均店鋪不同流量渠道下的訪客數(shù)和下單買家數(shù)（數(shù)據(jù)可通過相關(guān)平臺(tái)工具直接下載），為進(jìn)一步觀察相同渠道下自身店鋪與同行業(yè)間這些指標(biāo)數(shù)據(jù)的差異，需形成圖中右邊的數(shù)據(jù)表形式。例數(shù)據(jù)抽取-字段匹配只需運(yùn)用VLOOKUP函數(shù)到同行數(shù)據(jù)表中抽取與自身店鋪對(duì)應(yīng)的各個(gè)流量渠道下的訪客數(shù)和下單買家數(shù)即可在本店數(shù)據(jù)表中，在E1和F1增加兩個(gè)存放同行數(shù)據(jù)的字段：訪客數(shù)A、下單買家數(shù)A。1數(shù)據(jù)抽取-字段匹配選擇E2單元格，輸入下列公式：“=VLOOKUP($B2,同行！$B$2:$D$47，2,FALSE)”。其中，同行！$B$2：$D$47是同行數(shù)據(jù)表中的后三列數(shù)據(jù)區(qū)域。23、在本店數(shù)據(jù)表中，對(duì)E列后面的單元格進(jìn)行公式復(fù)制填充。F2單元格中的公式相似，可從E2中直接拖拉再修改，或直接復(fù)制公式再修改。公式為：“=VLOOKUP($B2,同行！$B$2:$D$47,3，F(xiàn)ALSE)”。然后完成剩余單元格的公式填充即可。4數(shù)據(jù)抽取-字段匹配為幫助店鋪了解新上線商品的交易情況，請(qǐng)根據(jù)店鋪所有商品的相關(guān)交易數(shù)據(jù)，提取指定新品的相應(yīng)數(shù)據(jù)，如圖所示。例數(shù)據(jù)抽取-字段匹配通過數(shù)據(jù)驗(yàn)證的方式實(shí)現(xiàn)新品ID下拉框選取，其余三個(gè)字段的數(shù)據(jù)均可通過MATCH+INDEX函數(shù)提取。選擇F4單元格，單擊“數(shù)據(jù)→數(shù)據(jù)工具→數(shù)據(jù)驗(yàn)證”，在彈出的對(duì)話框中設(shè)置驗(yàn)證條件和數(shù)據(jù)來源（假設(shè)事先將指定新品ID存放在L2:L7區(qū)域中），如圖所示。單擊“確定”，完成指定新品ID下拉框的輸入選擇。1數(shù)據(jù)抽取-字段匹配選擇G4單元格，輸入下列公式：“=INDEX($A$1:$D$88，MATCH($F$4,$A$1:$A$88,0)，2)”。其中A1:D88為源數(shù)據(jù)區(qū)域，MATCH($F$4,$A$1:$A$88,0)返回的是F4中指定的新品ID在A列數(shù)據(jù)中的行號(hào)。2數(shù)據(jù)抽取-字段匹配數(shù)據(jù)抽取-字段匹配將上述公式直接復(fù)制到H4和I4兩個(gè)單元格，只需修改INDEX函數(shù)的最后一個(gè)列序號(hào)，即：H4=INDEX($A$1：$D$88,MATCH