数据分析概述及Excel常⽤公式
近些年各⾏业信息系统使⽤频率越来越⾼，数据量变⼤，分析、使⽤数据变得不可或缺。我⾃⼰做数据分析⼏年，有些⼩⼩⼼得，近期做⼀点简短分享，希望在数据分析使⽤上对⼤家有帮助。
通常，数据分析可分为6个步骤：确定分析思路、数据准备、数据处理、数据分析、数据展现、报告撰写。
六步骤⼯具使⽤：
第1步确定分析思路，建议使⽤思维导图⼯具。
可将脑中构思的想法：分析⽬标、数据项⽬、分析⽅法等，画成图，由抽象转变为具体。得以检视分析思路合理性、数据项⽬是否遗漏重合、分析⽅法能否达到预期效果。推荐软件xmind，操作简单，只⽤enter、tab、F2三个键就能画出⼀幅导图。
第2步数据准备到第5步数据展现，常⽤⼯具是Excel。
第6步报告撰写，使⽤频率较⾼的⼯具是PPT、PDF/word还有Excel。
Excel使⽤知识⼤致分为3类：基础功能、常⽤公式、常⽤技巧。
数据分析各步骤Excel功能使⽤：
第2步数据准备，主要⽤Excel基础功能，⽐如复制、转职、抽取。
第3、4步使⽤Excel公式较多，这是今天⽂章的主题。第5步⼀般在Excel制作好，后续可粘贴到其他的格式⽂件中。
round函数有几个参数Excel常⽤公式可分成5类，具体是：清洗处理、关联匹配、逻辑运算、计算统计、时间序列，每类可⼆级细分。⼀级分类参考了⼀位数据分析⼤⽜，⼆级分类是我的思路。清洗处理类主要⽤于数据分析第3步，关联匹配类在第3、第4步都有使⽤，后三类公式在数据分析这⼀步应⽤较多。
第⼀类：清洗处理
常⽤公式有：trim、value、text、concatenate；len、substitute、replace、left、right、mid；find、search
第1组公式：trim、value、text、concatenate主要处理格式、合并⽂本。
trim，去除单元格⽂本两端的空格，语法为trim(text)。
value，将⽂本格式数字转换为数字格式，语法为value(text)。
text，将单元格内容转换为指定单元格格式，语法为text(value,fomat_text)。第⼀个参数value可以引⽤单元格，也可⼿输内容。第⼆个参数fomat_text，意思是格式⽂本。每个格式对应⼀个代码，可在Excel单击右键——设置单元格格式——⾃定义格式，到⽬标格式复制格式代码，粘贴到text公式中。注意需在格式代码外加英⽂引号。如想将格式改为数字格式、保留1位⼩
数，fomat_text输'0'。
第⼆组公式：len、substitute、replace、left、right、mid主要抽取替换。
len，统计单元格内⽂本长度，语法为len(text)。理解单元格内⽂本长度这个基础概念，才好理解后⾯的抽取、查公式。
substitute，将单元格⽂本指定⽂本替换为新⽂本，语法substitute(text,old_text,new_text, [instance_num])。第⼀个参数text可引⽤单元格、⼿输。第⼆个参数old_text，意思是需替换的⽼⽂本，⽼⽂本是数字不需加英⽂引号，其他类型则需要。第三个参数new_text，意思是需替换后的新⽂本，是否需加引号和⽼⽂本规则⼀样。第是个参数instance_num可省略。replace作⽤与substitute类似作⽤，语法(old_text,start_num,num_chars,
new_text)。第⼀个参数和sbstitute的第⼀个参数输⼊内容⼀样。第2个参数意思是开始替换的⽂本位置，
第三个参数是替换的⽂本长度，两个参数均输⼊数字。第四个参数输⼊替换后的新⽂本，和substitute第三个参数输⼊内容⼀样。
left，作⽤是取出单元格内左侧的⽂本，语法为left(text,[num_chars])。第⼀个参数可引⽤、⼿输，第⼆个参数意思是抽取⽂本长度。第⼆个参数可省略，省略则只取左侧第⼀个⽂本。
right，作⽤是取出单元格内右侧的⽂本，语法为right(text,[num_chars])。参数输⼊和left函数⼀样，区别是从右侧取。
mid，作⽤是是取出单元格中间的⽂本，语法为(text,start_num,num_chars)。第⼆个参数是开始取的⽂本位置，第三个参数是抽取⽂本长度。
第三组公式：find、search⽤于查单元格内⽂本，返回⽂本位置。
find，语法是find(find_text,within_text,[star_num])。第⼀个参数是要查指定的⽂本，与replace 和substitute的第⼀个参数类似。第⼆个参数是查范围，可以是⼀个单元格或⼿输内容。第三个参数是开始查的⽂本位数，可省略，省略则从查范围的第⼀位开始查。需要注意，即使指定开始查的⽂本位置，返回的⽂本位置也从⽂本第⼀位开始数。
search，语法和find⼀样，区别是search不区分英⽂⼤⼩写。
第⼆类：关联匹配
常⽤公式有：vlookup、hlookup、lookup、match；row、column、index、offset
vlookup能解决平时⼤部分问题，今天详讲，其他公式先略过。
vlookup，语法(lookup_value,table_array,col_index_num,[range_lookup])。在指定范围查⼀个单元格的值，到后，在到单元格的⾏往后数⼏列，返回指定⾏指定列交叉处单元格的内容。
第三类：逻辑运算
常⽤公式有：if、and、or、is，常与其他公式嵌套使⽤。
if语法(logical_test,[value_if_ture],[value_if_false])，满⾜条件时执⾏参数3、不满⾜条件执⾏参数4。
and、or是逻辑函数，⽤来判断给定所有条件是否为TRUE。给定条件全为真and返回true，⼀个为假返回false。条件⼀个为真返回true，全为假返回false。
is函数是⼀些函数的统称，可以判断指定值是否满⾜公式的条件，满⾜返回true、不满⾜返回false。常⽤的is函数有：isblank、istext、isnumber、isna、iserror。
第四类：计算统计
常⽤公式有：sum、sumifs、count、counta、countifs；max、min、average、rank；int、round、rand、randbetween；subtotal。
第1组公式：sum、sumifs、count、counta、countif，作⽤是求和、计数。
sum、count对指定内容求和、计数，如对⽂本单元格计数则使⽤counta。
countifs对满⾜指定条件的单元格计数，语法是(criteria_range1, criteria1, [criteria_range2, criteria2],…)，不慌，参数⼀个个拆开看。前2个参数criteria_range1, criteria1必需，后⾯参数可选。criteria_range1意思是条件区域1，是查区域；criteria1意思是条件1，是给定的条件，形式可为数字、表达式、单元格引⽤或⽂本。countifs运⾏在⼀个区域查满⾜给定条件的单元格个数，统计有多少个单元格满⾜条件。还可添加更多查区域、查条件。写在参数3、参数4......。
sumifs语法(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2], ...)，⽐countifs语法多第⼀个参数sum_range，第⼆个参数开始与countifs类似。意思是对满⾜条件的单元格求和，countifs为什么不需要这个参数，因为到满⾜条件单元格的同时即可对他们计数。
第2组公式：max、min、average、rank，作⽤是求最值、平均值、排名。
max求给定区域的最⼤值，语法是(numbber1,[number2])。
min最⼩值、average平均值，语法与MAX⼀样，average忽略空⽩单元格。
rank对给定区域值进⾏排名，语法是(number,ref,[order])，参数1number是要到其排位的数字，参数2ref是引⽤区域，参数3order为0或省略对数字降序排列、order⾮0对数字升序排列。
第3组公式对数字进⾏取整、保留⼩数等处理。
Int( number )对数字取整。
round语法(number, num_digits)，参数1写需四舍五⼊的数字，参数2写保留⼩数位数。
rand()在(0,1]直接取随机数，randbetween在给定区域内取随机数，语法(bottom, top)。（第三组公式：int、round、rand、randbetween）
第4组公式subtotal，可以替代本类⼤部分公式。语法(function_num,ref1,[ref2],...)。参数
1Function_num必需，写数字1-11或101-111，⽤于指定分类汇总使⽤的函数，如果⽤101-111不计算隐藏的⾏。参数2必需，是要计算的区域。
第五类：时间序列
常⽤公式有：year、month、day、hour、minute、second；today、now；date、time；days、datedif。
第1组公式语法都是：公式（serial_number），参数可以选择单元格、或输⼊内容。根据公式不同分别返回：年、⽉、⽇、时、分、秒。
第2组公式语法都是：公式(），公式内不⽤输⼊参数，⾃动返回今天⽇期、此刻时间。today返回今天的年⽉⽇，now返回此刻的年⽉⽇再加⼩时、分钟。
第3组公式语法都是：公式（*,*,*），公式需输⼊3个参数，⽤于返回指定的⽇期和时间。date语法date（year,month,day），公式可对年、⽉、⽇三个参数进⾏加减。⽐
如'=DATE(YEAR(A2)+2,MONTH(A2)+1,DAY(A2)+3)'，意思是在单元格A2⽇期加2年、1⽉、3天，⽣成新⽇期。
time语法（hour,minute,second），可对时、分、秒三个参数进⾏加减。
第4组公式days、datedif作⽤是⽇期加减。days语法(end_day,start_day)，先输结束⽇期、再输开始⽇
期，算出两个⽇期间的天数差。⽉份、年份的差异会换算成天，⼀起体现在天数差⾥⾯。datedif语法是(start_date,end_date,unit)，先输开始⽇期、再输结束⽇期，最后输⼊要返回的信息类型。
unit有6种选择，Y、M、YM、D、YD、MD。Y计算两个⽇期间的年份差；M计算⽉份差，YM 计算忽略年份差异的⽉份差；D计算天数差，YM计算忽略年份差异的天数差（考虑⽉份差异），MD计算忽略年份⽉份差异的天数差。
写在最后：
学习Excel公式，最重要的是搜索能⼒。输⼊公式名、公式功能，轻点搜索就能到使⽤⽅法。我在这⾥列出的常⽤公式，⼤家可以按图索骥，练习使⽤。
“学习”⼆字，学是⼀半，习更重要（练习）。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。