|
感恩每一个支持我的你 | 写给前面： 因为计算机二级会考EXCEL的一些常用函数，再加上我自己偶尔也会用，这些东西长时间不用容易忘记，所以这次函数的总结就当温故知新了。 曾经我也看过秦路的文章，他说基础的数据分析都要会excel,重点掌握“4+1”,即4 vlookup函数,sum函数,if函数,sumif函数和数据透视表。如果你以后想做数据分析师这以按这个路径多练习一下。 注：为了保证数据完整性,表格按照原始数剧（sheet1）、加工数据（sheet2），图表（sheet3）的类型管理。 Excel的函数大致分为5类： 清洗处理类 关键匹配类 逻辑运算类 计算统计类 格式转换类 清洗处理类：大部分数据不是直接拿来就能用的,文本、格式的转换调整需要用到以下函数 ↓↓↓ 1. Trim：清除点字符串两边的空格,excel中只能把单元格内容前后的空格去掉,并不能去除字符之间的空格。 2. Concatenate(2016版后改为concat)：可把多个字符、文本、数值连接起来,实现合并的功能。 注：等同于&,当参数少时可用& 3. Replace/Substitute：
＝replace（要替换的字符串,开始位置,替换个数,新的文本） 注：第四个参数是文本格式,在英文输入法的状态下输入引号
＝Substitute（需要替换的单元格,旧文本,新文本,0/1）） 注：substitute常用来去掉文本之间的空格, ＝substitute（需要替换的单元格,“ ”,“”） 4. Left／Right／Mid,帮助我们取得某个数值/文本数据中我们需要的特定值,分别表示左边第一位开始取值,右边第一位开始取值,从指定位置开始取值
＝left（text,从左开始算提取几个字符）
＝right（text,从右开始算提取几个字符）
＝mid（text,从第几位开始提取,从指定位置开始算提几个字符） 5. Len／Lenb：计算文本字符串字符个数的函数
例如=LEN('12AB')和LENB('12AB')的结果都是4，如果里面有中文，LEN('1A好')=3，但是LEBN('1A好')=4 6. Find：对查找的文本进行定位,确定其位置,精确查找,区分大小写 ＝find（要查找的文本,文本所在的单元格,从第几个字符开始查找） 注：查找的文本所谓英文需要在英文输入状态下加双引号,如果是数字就不需要 例：提取扣扣邮箱1348887@qq.com中的扣扣号 ＝left（text,find('@',text,1)-1） 7. Search：指定字符在字符串中第一次出现的位置,从左到右查找,模糊查找,不区分大小写 ＝search（要查找的字符,字符所在文本,从第几个字符开始查找） 8. Text：将数值转为自己想要的文本格式 语法＝text（数字值,想要的文本格式） 9. Clean用于清除无法打印的字符或换行符号 关键匹配类：在进行多表关联或者行列比对时用到的函数，越复杂的表用得越多。 1. Lookup：查询单一行/单一列中的值并返回对应选定区域相同格式另一行或列中的同一位置的值。 ＝lookup(需要查找的值,需要查找值的某列或某行,设定输出的某列或某行) 注：表格区域的数值必须以升序排列,否则结果可能会出现错误 例：要查找3对应的科目 数学 语文 英语 历史 地理 2. Vlookup ：首列查找函数,以某个要查找的条件,在区域的首列里面查找,若有匹配的,则返回该数据对应的第几列数据,主要用于多表查询 ＝vlookup（要查找得值,要查找的区域,查找区域对应的哪一列,精确匹配/模糊匹配）
固定公式=VLOOKUP(检索关键字，IF({1,0},检索关键字所在列，查找值所在列)，2，0) 注：所有使用了数组的公式，不能直接回车，需要使用Ctrl+Shift+Enter，否则会出错。 3. Index：在指定区域位置查询到位置所对应的区域 语法＝index（要返回值的单元格区域,返回值所在行,返回值所在列） 4. Match：返回指定内容所在的位置 语法：＝match（查找目标,查找范围,查找方式） 0为精确查找 1为升序查找,-1为降序查找 5. Row：返回指定单元格的行号 6. Column：返回指定单元格的列号 逻辑运算类 逻辑运算返回的均是布尔类型，True和False。很多复杂的数据分析会牵扯到较多的逻辑运算。 Excel中将0认为false,非0值等价于true 1. If单层判断： 语法＝if（条件判断,结果为是返回值,结果为都返回值） if多条件判断时,可多层嵌套： ＝if（条件1,结果1,（条件2,结果是2,（条件3,结果3,…））） 注：if函数第一个判断的是最外层表达式,而非最内部的嵌套公式 2. And：全部参数为true.则返回true 3. Or：只要有一个参数true,就返回true 4. Is系列,计算统计类：常用的基础计算、分析、统计函数，以描述性统计为准。 1. Sum／Sumif／Sumifs 对指定范围值进行求和运算 Sumif：对指定区域中符合指定条件的单元格相应的数值进行累加 语法＝（条件区域,指定求和条件,求和区域） sumifs对指定范围内同时满足多个条件的值进行求和 语法＝（求和范围,条件范围1,条件1,条件范围2,条件2…条件范围n,条件n） 2. Sumproduct.统计总和 3. Count／Countif／Countifs统计满足条件的字符串个数 4. Max 5. Min 6. Rank＝（单元格,某区域）排序，返回指定值在引用区域的排名，重复值同一排名。 7. Rand／Randbetween常用随机抽样，前者返回0~1之间的随机值，后者可以指定范围。 8. Averagea 9. Quartile 10. Stdev 求标准差，统计型函数，后续数据分析再讲到 11. Subtotal =subtotal（参数，引用区域） 汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化，换言之，只要会了这个函数，上面的都可以抛弃掉了 12.Int／Round 取整函数，int向下取整，round按小数位取数。 round(3.1415,2) =3.14 ; round(3.1415,1)=3.1 时间顺序类 专门用于处理时间格式以及转换，时间序列在金融、财务等数据分析中占有较大比重 1. Year 2. Month 3. Weekday返回指定时间为一周中的第几天，参数为1代表从星期日开始算作第一天，参数为2代表从星期一开始算作第一天（中西方差异）。我们中国用2为参数即可。 4. Weeknum返回一年中的第几个星期，后面的参数类同weekday，意思是从周日算还是周一。 5. Day 6. Date时间转换函数，等于将year()，month()，day()合并  你点的每个赞，都是我坚持分享的动力！ |
|
|
来自： hercules028 > 《excel》
