近些年各行業(yè)信息系統(tǒng)使用頻率越來(lái)越高，數(shù)據(jù)量變大，分析、使用數(shù)據(jù)變得不可或缺。我自己做數(shù)據(jù)分析幾年，有些小小心得，近期做一點(diǎn)簡(jiǎn)短分享，希望在數(shù)據(jù)分析使用上對(duì)大家有幫助。
通常，數(shù)據(jù)分析可分為6個(gè)步驟：確定分析思路、數(shù)據(jù)準(zhǔn)備、數(shù)據(jù)處理、數(shù)據(jù)分析、數(shù)據(jù)展現(xiàn)、報(bào)告撰寫(xiě)。
六步驟工具使用：
第1步確定分析思路，建議使用思維導(dǎo)圖工具。
可將腦中構(gòu)思的想法：分析目標(biāo)、數(shù)據(jù)項(xiàng)目、分析方法等，畫(huà)成圖，由抽象轉(zhuǎn)變?yōu)榫唧w。得以檢視分析思路合理性、數(shù)據(jù)項(xiàng)目是否遺漏重合、分析方法能否達(dá)到預(yù)期效果。推薦軟件xmind，操作簡(jiǎn)單，只用enter、tab、F2三個(gè)鍵就能畫(huà)出一幅導(dǎo)圖。
第2步數(shù)據(jù)準(zhǔn)備到第5步數(shù)據(jù)展現(xiàn)，常用工具是Excel。
第6步報(bào)告撰寫(xiě)，使用頻率較高的工具是PPT、PDF/word還有Excel。
Excel使用知識(shí)大致分為3類(lèi)：基礎(chǔ)功能、常用公式、常用技巧。
數(shù)據(jù)分析各步驟Excel功能使用：
第2步數(shù)據(jù)準(zhǔn)備，主要用Excel基礎(chǔ)功能，比如復(fù)制、轉(zhuǎn)職、抽取。
第3、4步使用Excel公式較多，這是今天文章的主題。第5步一般在Excel制作好，后續(xù)可粘貼到其他的格式文件中。
Excel常用公式可分成5類(lèi)，具體是：清洗處理、關(guān)聯(lián)匹配、邏輯運(yùn)算、計(jì)算統(tǒng)計(jì)、時(shí)間序列，每類(lèi)可二級(jí)細(xì)分。一級(jí)分類(lèi)參考了一位數(shù)據(jù)分析大牛，二級(jí)分類(lèi)是我的思路。清洗處理類(lèi)主要用于數(shù)據(jù)分析第3步，關(guān)聯(lián)匹配類(lèi)在第3、第4步都有使用，后三類(lèi)公式在數(shù)據(jù)分析這一步應(yīng)用較多。
第一類(lèi)：清洗處理
常用公式有：trim、value、text、concatenate；len、substitute、replace、left、right、mid；find、search
第1組公式：trim、value、text、concatenate主要處理格式、合并文本。
trim，去除單元格文本兩端的空格，語(yǔ)法為trim(text)。
value，將文本格式數(shù)字轉(zhuǎn)換為數(shù)字格式，語(yǔ)法為value(text)。
text，將單元格內(nèi)容轉(zhuǎn)換為指定單元格格式，語(yǔ)法為text(value,fomat_text)。第一個(gè)參數(shù)value可以引用單元格，也可手輸內(nèi)容。第二個(gè)參數(shù)fomat_text，意思是格式文本。每個(gè)格式對(duì)應(yīng)一個(gè)代碼，可在Excel單擊右鍵——設(shè)置單元格格式——自定義格式，找到目標(biāo)格式復(fù)制格式代碼，粘貼到text公式中。注意需在格式代碼外加英文引號(hào)。如想將格式改為數(shù)字格式、保留1位小數(shù)，fomat_text輸'0'。
第二組公式：len、substitute、replace、left、right、mid主要抽取替換。
len，統(tǒng)計(jì)單元格內(nèi)文本長(zhǎng)度，語(yǔ)法為len(text)。理解單元格內(nèi)文本長(zhǎng)度這個(gè)基礎(chǔ)概念，才好理解后面的抽取、查找公式。
substitute，將單元格文本指定文本替換為新文本，語(yǔ)法substitute(text,old_text,new_text,[instance_num])。第一個(gè)參數(shù)text可引用單元格、手輸。第二個(gè)參數(shù)old_text，意思是需替換的老文本，老文本是數(shù)字不需加英文引號(hào)，其他類(lèi)型則需要。第三個(gè)參數(shù)new_text，意思是需替換后的新文本，是否需加引號(hào)和老文本規(guī)則一樣。第是個(gè)參數(shù)instance_num可省略。
replace作用與substitute類(lèi)似作用，語(yǔ)法(old_text,start_num,num_chars,
new_text)。第一個(gè)參數(shù)和sbstitute的第一個(gè)參數(shù)輸入內(nèi)容一樣。第2個(gè)參數(shù)意思是開(kāi)始替換的文本位置，第三個(gè)參數(shù)是替換的文本長(zhǎng)度，兩個(gè)參數(shù)均輸入數(shù)字。第四個(gè)參數(shù)輸入替換后的新文本，和substitute第三個(gè)參數(shù)輸入內(nèi)容一樣。
left，作用是取出單元格內(nèi)左側(cè)的文本，語(yǔ)法為left(text,[num_chars])。第一個(gè)參數(shù)可引用、手輸，第二個(gè)參數(shù)意思是抽取文本長(zhǎng)度。第二個(gè)參數(shù)可省略，省略則只取左側(cè)第一個(gè)文本。
right，作用是取出單元格內(nèi)右側(cè)的文本，語(yǔ)法為right(text,[num_chars])。參數(shù)輸入和left函數(shù)一樣，區(qū)別是從右側(cè)取。
mid，作用是是取出單元格中間的文本，語(yǔ)法為(text,start_num,num_chars)。第二個(gè)參數(shù)是開(kāi)始取的文本位置，第三個(gè)參數(shù)是抽取文本長(zhǎng)度。
第三組公式：find、search用于查找單元格內(nèi)文本，返回文本位置。
find，語(yǔ)法是find(find_text,within_text,[star_num])。第一個(gè)參數(shù)是要查找指定的文本，與replace和substitute的第一個(gè)參數(shù)類(lèi)似。第二個(gè)參數(shù)是查找范圍，可以是一個(gè)單元格或手輸內(nèi)容。第三個(gè)參數(shù)是開(kāi)始查找的文本位數(shù)，可省略，省略則從查找范圍的第一位開(kāi)始查找。需要注意，即使指定開(kāi)始查找的文本位置，返回的文本位置也從文本第一位開(kāi)始數(shù)。
search，語(yǔ)法和find一樣，區(qū)別是search不區(qū)分英文大小寫(xiě)。
第二類(lèi)：關(guān)聯(lián)匹配
常用公式有：vlookup、hlookup、lookup、match；row、column、index、offset
vlookup能解決平時(shí)大部分問(wèn)題，今天詳講，其他公式先略過(guò)。
vlookup，語(yǔ)法(lookup_value,table_array,col_index_num,[range_lookup])。在指定范圍查找一個(gè)單元格的值，找到后，在找到單元格的行往后數(shù)幾列，返回指定行指定列交叉處單元格的內(nèi)容。
第三類(lèi)：邏輯運(yùn)算
常用公式有：if、and、or、is，常與其他公式嵌套使用。
if語(yǔ)法(logical_test,[value_if_ture],[value_if_false])，滿足條件時(shí)執(zhí)行參數(shù)3、不滿足條件執(zhí)行參數(shù)4。
and、or是邏輯函數(shù)，用來(lái)判斷給定所有條件是否為T(mén)RUE。給定條件全為真and返回true，一個(gè)為假返回false。條件一個(gè)為真返回true，全為假返回false。
is函數(shù)是一些函數(shù)的統(tǒng)稱(chēng)，可以判斷指定值是否滿足公式的條件，滿足返回true、不滿足返回false。常用的is函數(shù)有：isblank、istext、isnumber、isna、iserror。
第四類(lèi)：計(jì)算統(tǒng)計(jì)
常用公式有：sum、sumifs、count、counta、countifs；max、min、average、rank；int、round、rand、randbetween；subtotal。
第1組公式：sum、sumifs、count、counta、countif，作用是求和、計(jì)數(shù)。
sum、count對(duì)指定內(nèi)容求和、計(jì)數(shù)，如對(duì)文本單元格計(jì)數(shù)則使用counta。
countifs對(duì)滿足指定條件的單元格計(jì)數(shù)，語(yǔ)法是(criteria_range1, criteria1, [criteria_range2, criteria2],…)，不慌，參數(shù)一個(gè)個(gè)拆開(kāi)看。前2個(gè)參數(shù)criteria_range1, criteria1必需，后面參數(shù)可選。criteria_range1意思是條件區(qū)域1，是查找區(qū)域；criteria1意思是條件1，是給定的條件，形式可為數(shù)字、表達(dá)式、單元格引用或文本。countifs運(yùn)行在一個(gè)區(qū)域查找滿足給定條件的單元格個(gè)數(shù)，統(tǒng)計(jì)有多少個(gè)單元格滿足條件。還可添加更多查找區(qū)域、查找條件。寫(xiě)在參數(shù)3、參數(shù)4......。
sumifs語(yǔ)法(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2], ...)，比countifs語(yǔ)法多第一個(gè)參數(shù)sum_range，第二個(gè)參數(shù)開(kāi)始與countifs類(lèi)似。意思是對(duì)滿足條件的單元格求和，countifs為什么不需要這個(gè)參數(shù)，因?yàn)檎业綕M足條件單元格的同時(shí)即可對(duì)他們計(jì)數(shù)。
第2組公式：max、min、average、rank，作用是求最值、平均值、排名。
max求給定區(qū)域的最大值，語(yǔ)法是(numbber1,[number2])。
min最小值、average平均值，語(yǔ)法與MAX一樣，average忽略空白單元格。
rank對(duì)給定區(qū)域值進(jìn)行排名，語(yǔ)法是(number,ref,[order])，參數(shù)1number是要找到其排位的數(shù)字，參數(shù)2ref是引用區(qū)域，參數(shù)3order為0或省略對(duì)數(shù)字降序排列、order非0對(duì)數(shù)字升序排列。
第3組公式對(duì)數(shù)字進(jìn)行取整、保留小數(shù)等處理。
Int( number )對(duì)數(shù)字取整。
round語(yǔ)法(number, num_digits)，參數(shù)1寫(xiě)需四舍五入的數(shù)字，參數(shù)2寫(xiě)保留小數(shù)位數(shù)。
rand()在(0,1]直接取隨機(jī)數(shù)，randbetween在給定區(qū)域內(nèi)取隨機(jī)數(shù)，語(yǔ)法(bottom, top)。（第三組公式：int、round、rand、randbetween）
第4組公式subtotal，可以替代本類(lèi)大部分公式。語(yǔ)法(function_num,ref1,[ref2],...)。參數(shù)1Function_num必需，寫(xiě)數(shù)字1-11或101-111，用于指定分類(lèi)匯總使用的函數(shù)，如果用101-111不計(jì)算隱藏的行。參數(shù)2必需，是要計(jì)算的區(qū)域。
第五類(lèi)：時(shí)間序列
常用公式有：year、month、day、hour、minute、second；today、now；date、time；days、datedif。
第1組公式語(yǔ)法都是：公式（serial_number），參數(shù)可以選擇單元格、或輸入內(nèi)容。根據(jù)公式不同分別返回：年、月、日、時(shí)、分、秒。
第2組公式語(yǔ)法都是：公式(），公式內(nèi)不用輸入?yún)?shù)，自動(dòng)返回今天日期、此刻時(shí)間。today返回今天的年月日，now返回此刻的年月日再加小時(shí)、分鐘。
第3組公式語(yǔ)法都是：公式（*,*,*），公式需輸入3個(gè)參數(shù)，用于返回指定的日期和時(shí)間。date語(yǔ)法date（year,month,day），公式可對(duì)年、月、日三個(gè)參數(shù)進(jìn)行加減。比如'=DATE(YEAR(A2)+2,MONTH(A2)+1,DAY(A2)+3)'，意思是在單元格A2日期加2年、1月、3天，生成新日期。
time語(yǔ)法（hour,minute,second），可對(duì)時(shí)、分、秒三個(gè)參數(shù)進(jìn)行加減。
第4組公式days、datedif作用是日期加減。days語(yǔ)法(end_day,start_day)，先輸結(jié)束日期、再輸開(kāi)始日期，算出兩個(gè)日期間的天數(shù)差。月份、年份的差異會(huì)換算成天，一起體現(xiàn)在天數(shù)差里面。datedif語(yǔ)法是(start_date,end_date,unit),先輸開(kāi)始日期、再輸結(jié)束日期，最后輸入要返回的信息類(lèi)型。
unit有6種選擇，Y、M、YM、D、YD、MD。Y計(jì)算兩個(gè)日期間的年份差；M計(jì)算月份差，YM計(jì)算忽略年份差異的月份差；D計(jì)算天數(shù)差，YM計(jì)算忽略年份差異的天數(shù)差（考慮月份差異），MD計(jì)算忽略年份月份差異的天數(shù)差。
寫(xiě)在最后：
學(xué)習(xí)Excel公式，最重要的是搜索能力。輸入公式名、公式功能，輕點(diǎn)搜索就能找到使用方法。我在這里列出的常用公式，大家可以按圖索驥，練習(xí)使用。
“學(xué)習(xí)”二字，學(xué)是一半，習(xí)更重要（練習(xí)）。
聯(lián)系客服
