近些年各行業(yè)信息系統(tǒng)使用頻率越來越高,，數(shù)據(jù)量變大,，分析、使用數(shù)據(jù)變得不可或缺,。我自己做數(shù)據(jù)分析幾年,，有些小小心得，近期做一點(diǎn)簡短分享,，希望在數(shù)據(jù)分析使用上對大家有幫助,。

通常，數(shù)據(jù)分析可分為6個步驟：確定分析思路,、數(shù)據(jù)準(zhǔn)備,、數(shù)據(jù)處理、數(shù)據(jù)分析,、數(shù)據(jù)展現(xiàn),、報(bào)告撰寫。

六步驟工具使用：

第1步確定分析思路,，建議使用思維導(dǎo)圖工具,。

可將腦中構(gòu)思的想法：分析目標(biāo)、數(shù)據(jù)項(xiàng)目,、分析方法等,，畫成圖，由抽象轉(zhuǎn)變?yōu)榫唧w,。得以檢視分析思路合理性,、數(shù)據(jù)項(xiàng)目是否遺漏重合、分析方法能否達(dá)到預(yù)期效果,。推薦軟件xmind,，操作簡單，只用enter,、tab,、F2三個鍵就能畫出一幅導(dǎo)圖。

第2步數(shù)據(jù)準(zhǔn)備到第5步數(shù)據(jù)展現(xiàn),，常用工具是Excel,。

第6步報(bào)告撰寫,，使用頻率較高的工具是PPT、PDF/word還有Excel,。

Excel使用知識大致分為3類：基礎(chǔ)功能,、常用公式、常用技巧,。

數(shù)據(jù)分析各步驟Excel功能使用：

第2步數(shù)據(jù)準(zhǔn)備,，主要用Excel基礎(chǔ)功能，比如復(fù)制,、轉(zhuǎn)職,、抽取。

第3,、4步使用Excel公式較多,，這是今天文章的主題。第5步一般在Excel制作好,，后續(xù)可粘貼到其他的格式文件中,。

Excel常用公式可分成5類，具體是：清洗處理,、關(guān)聯(lián)匹配,、邏輯運(yùn)算、計(jì)算統(tǒng)計(jì),、時(shí)間序列,，每類可二級細(xì)分。一級分類參考了一位數(shù)據(jù)分析大牛,，二級分類是我的思路,。清洗處理類主要用于數(shù)據(jù)分析第3步，關(guān)聯(lián)匹配類在第3,、第4步都有使用,，后三類公式在數(shù)據(jù)分析這一步應(yīng)用較多。

第一類：清洗處理

常用公式有：trim,、value,、text、concatenate,；len,、substitute、replace,、left,、right、mid；find,、search

第1組公式：trim、value,、text,、concatenate主要處理格式、合并文本,。

trim,，去除單元格文本兩端的空格，語法為trim(text),。

value,，將文本格式數(shù)字轉(zhuǎn)換為數(shù)字格式，語法為value(text),。

text,，將單元格內(nèi)容轉(zhuǎn)換為指定單元格格式，語法為text(value,fomat_text),。第一個參數(shù)value可以引用單元格,，也可手輸內(nèi)容。第二個參數(shù)fomat_text,，意思是格式文本,。每個格式對應(yīng)一個代碼，可在Excel單擊右鍵——設(shè)置單元格格式——自定義格式,，找到目標(biāo)格式復(fù)制格式代碼,，粘貼到text公式中。注意需在格式代碼外加英文引號,。如想將格式改為數(shù)字格式,、保留1位小數(shù)，fomat_text輸'0',。

第二組公式：len,、substitute、replace,、left,、right、mid主要抽取替換,。

len,，統(tǒng)計(jì)單元格內(nèi)文本長度，語法為len(text),。理解單元格內(nèi)文本長度這個基礎(chǔ)概念,，才好理解后面的抽取、查找公式。

substitute,，將單元格文本指定文本替換為新文本,，語法substitute(text,old_text,new_text,[instance_num])。第一個參數(shù)text可引用單元格,、手輸,。第二個參數(shù)old_text，意思是需替換的老文本,，老文本是數(shù)字不需加英文引號,，其他類型則需要。第三個參數(shù)new_text,，意思是需替換后的新文本,，是否需加引號和老文本規(guī)則一樣。第是個參數(shù)instance_num可省略,。

replace作用與substitute類似作用,，語法(old_text,start_num,num_chars,

new_text)。第一個參數(shù)和sbstitute的第一個參數(shù)輸入內(nèi)容一樣,。第2個參數(shù)意思是開始替換的文本位置,，第三個參數(shù)是替換的文本長度，兩個參數(shù)均輸入數(shù)字,。第四個參數(shù)輸入替換后的新文本,，和substitute第三個參數(shù)輸入內(nèi)容一樣。

left,，作用是取出單元格內(nèi)左側(cè)的文本,，語法為left(text,[num_chars])。第一個參數(shù)可引用,、手輸,，第二個參數(shù)意思是抽取文本長度。第二個參數(shù)可省略,，省略則只取左側(cè)第一個文本,。

right，作用是取出單元格內(nèi)右側(cè)的文本,，語法為right(text,[num_chars]),。參數(shù)輸入和left函數(shù)一樣，區(qū)別是從右側(cè)取,。

mid,，作用是是取出單元格中間的文本，語法為(text,start_num,num_chars),。第二個參數(shù)是開始取的文本位置,，第三個參數(shù)是抽取文本長度。

第三組公式：find、search用于查找單元格內(nèi)文本,，返回文本位置,。

find，語法是find(find_text,within_text,[star_num]),。第一個參數(shù)是要查找指定的文本,，與replace和substitute的第一個參數(shù)類似。第二個參數(shù)是查找范圍,，可以是一個單元格或手輸內(nèi)容。第三個參數(shù)是開始查找的文本位數(shù),，可省略,，省略則從查找范圍的第一位開始查找。需要注意,，即使指定開始查找的文本位置,，返回的文本位置也從文本第一位開始數(shù)。

search,，語法和find一樣,，區(qū)別是search不區(qū)分英文大小寫。

第二類：關(guān)聯(lián)匹配

常用公式有：vlookup,、hlookup,、lookup、match,；row,、column、index,、offset

vlookup能解決平時(shí)大部分問題,，今天詳講，其他公式先略過,。

vlookup,，語法(lookup_value,table_array,col_index_num,[range_lookup])。在指定范圍查找一個單元格的值,，找到后,，在找到單元格的行往后數(shù)幾列，返回指定行指定列交叉處單元格的內(nèi)容,。

第三類：邏輯運(yùn)算

常用公式有：if,、and、or,、is,，常與其他公式嵌套使用。

if語法(logical_test,[value_if_ture],[value_if_false])，滿足條件時(shí)執(zhí)行參數(shù)3,、不滿足條件執(zhí)行參數(shù)4,。

and、or是邏輯函數(shù),，用來判斷給定所有條件是否為TRUE,。給定條件全為真and返回true，一個為假返回false,。條件一個為真返回true,，全為假返回false。

is函數(shù)是一些函數(shù)的統(tǒng)稱,，可以判斷指定值是否滿足公式的條件,，滿足返回true、不滿足返回false,。常用的is函數(shù)有：isblank,、istext、isnumber,、isna,、iserror。

第四類：計(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對指定內(nèi)容求和,、計(jì)數(shù),，如對文本單元格計(jì)數(shù)則使用counta。

countifs對滿足指定條件的單元格計(jì)數(shù),，語法是(criteria_range1, criteria1, [criteria_range2, criteria2],…),，不慌，參數(shù)一個個拆開看,。前2個參數(shù)criteria_range1, criteria1必需,，后面參數(shù)可選。criteria_range1意思是條件區(qū)域1,，是查找區(qū)域,；criteria1意思是條件1，是給定的條件,，形式可為數(shù)字,、表達(dá)式,、單元格引用或文本,。countifs運(yùn)行在一個區(qū)域查找滿足給定條件的單元格個數(shù)，統(tǒng)計(jì)有多少個單元格滿足條件,。還可添加更多查找區(qū)域,、查找條件。寫在參數(shù)3,、參數(shù)4......,。

sumifs語法(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2], ...)，比countifs語法多第一個參數(shù)sum_range,，第二個參數(shù)開始與countifs類似,。意思是對滿足條件的單元格求和，countifs為什么不需要這個參數(shù),，因?yàn)檎业綕M足條件單元格的同時(shí)即可對他們計(jì)數(shù),。

第2組公式：max、min,、average,、rank，作用是求最值,、平均值,、排名。

max求給定區(qū)域的最大值,，語法是(numbber1,[number2]),。

min最小值,、average平均值，語法與MAX一樣,，average忽略空白單元格,。

rank對給定區(qū)域值進(jìn)行排名，語法是(number,ref,[order]),，參數(shù)1number是要找到其排位的數(shù)字,，參數(shù)2ref是引用區(qū)域，參數(shù)3order為0或省略對數(shù)字降序排列,、order非0對數(shù)字升序排列,。

第3組公式對數(shù)字進(jìn)行取整、保留小數(shù)等處理,。

Int( number )對數(shù)字取整,。

round語法(number, num_digits)，參數(shù)1寫需四舍五入的數(shù)字,，參數(shù)2寫保留小數(shù)位數(shù),。

rand()在(0,1]直接取隨機(jī)數(shù)，randbetween在給定區(qū)域內(nèi)取隨機(jī)數(shù),，語法(bottom, top),。（第三組公式：int、round,、rand,、randbetween）

第4組公式subtotal，可以替代本類大部分公式,。語法(function_num,ref1,[ref2],...),。參數(shù)1Function_num必需，寫數(shù)字1-11或101-111,，用于指定分類匯總使用的函數(shù),，如果用101-111不計(jì)算隱藏的行。參數(shù)2必需,，是要計(jì)算的區(qū)域,。

第五類：時(shí)間序列

常用公式有：year、month,、day,、hour、minute,、second,；today、now,；date,、time,；days、datedif,。

第1組公式語法都是：公式（serial_number）,，參數(shù)可以選擇單元格、或輸入內(nèi)容,。根據(jù)公式不同分別返回：年,、月、日,、時(shí),、分、秒,。

第2組公式語法都是：公式(）,，公式內(nèi)不用輸入?yún)?shù)，自動返回今天日期,、此刻時(shí)間,。today返回今天的年月日，now返回此刻的年月日再加小時(shí),、分鐘,。

第3組公式語法都是：公式（*,*,*），公式需輸入3個參數(shù),，用于返回指定的日期和時(shí)間,。date語法date（year,month,day）,，公式可對年,、月、日三個參數(shù)進(jìn)行加減,。比如'=DATE(YEAR(A2)+2,MONTH(A2)+1,DAY(A2)+3)',，意思是在單元格A2日期加2年、1月,、3天,，生成新日期。

time語法（hour,minute,second）,，可對時(shí),、分、秒三個參數(shù)進(jìn)行加減,。

第4組公式days,、datedif作用是日期加減。days語法(end_day,start_day),，先輸結(jié)束日期,、再輸開始日期,，算出兩個日期間的天數(shù)差。月份,、年份的差異會換算成天,，一起體現(xiàn)在天數(shù)差里面。datedif語法是(start_date,end_date,unit),先輸開始日期,、再輸結(jié)束日期,，最后輸入要返回的信息類型。

unit有6種選擇,，Y,、M、YM,、D,、YD、MD,。Y計(jì)算兩個日期間的年份差,；M計(jì)算月份差，YM計(jì)算忽略年份差異的月份差,；D計(jì)算天數(shù)差,，YM計(jì)算忽略年份差異的天數(shù)差（考慮月份差異），MD計(jì)算忽略年份月份差異的天數(shù)差,。

寫在最后：

學(xué)習(xí)Excel公式,，最重要的是搜索能力。輸入公式名,、公式功能,，輕點(diǎn)搜索就能找到使用方法。我在這里列出的常用公式,，大家可以按圖索驥,，練習(xí)使用。

“學(xué)習(xí)”二字,，學(xué)是一半,，習(xí)更重要（練習(xí)）。