高效数据分析 你需掌握这几类Excel函数应用

高效数据分析 你需掌握这几类Excel函数应用

最近开始学习数据分析，而Excel软件作为数据分析的一个工具之一，虽说不是万能的，但是对于初学数据的人来说，一定是一个即快捷又容易掌握的工具。必竟跟其他专业技术类型软件相比较，用Excel做一个简单的表格以及简单的求和函数还是相当熟悉的。我此次学习的主攻方向是Excel里既神秘又神奇的一个应用—函数应用。接下来会陆续分享我的学习心得。

[if !supportLists]一、[endif]分类整理数据分析中常用的函数

根据手上所搜集到的关于函数的资料整理如下：

第一类：处理清洗类函数：

Trim：Trim函数主要用于把单元格内容前后的空格去掉，但并不去除字符之间的空格。其英文是修剪、整理的意思。

Len:LEN函数是计算单元格内字符串的个数

Lenb：公式=LENB(text)，其中text：表示要计算字节数的文本。如果参数text中包含空格，LENB函数也会计算空格的字节数。

使用方法：如下图，我在A1栏中输入 数据分析 四字，一前一后各加一个空格键，使用len函数检测A1中实际有6个字符。接下来，在C1输入公式：=Trim（A1）按下回车

Concatenate：用于字符连接的函数，使用频率相对很高，尤其是涉及到大量数值合并的情况。

使用方法：如图，A1输入文字，B1输入相应文字，C1输入公式Concatenate=（），先选中A1单元格，中间使用逗号分开，再选中B1单元格，回车确定

Replace：用新字符串替换旧字符串，而且替换的位置和数量都是指定的。

使用方式：=replace（要替换的字符串，开始位置，替换个数，新的文本）注意：第四个参数是文本，要加上引号.(公式说明：公式 =replace(a2,3,1,"--") 中，a2 为源文本，3 为替换开始位置，1 为替换字符数，"--" 为替换字符，由于编号中只有一横短杠，所以替换字符数要设置为 1，如果设置为 2，将会替换掉一个数字。）如图：

Left:用来对单元格内容进行截取.从左边第一个字符开始截取，截取指定的长度。

使用方法：公式基本结构：=left(text,num_chars) 。text代表用来截取的单元格内容，num_chars（图中方形圈中数值），代表从左开始截取的字符数。

Right：right是右侧提取函数

操作方法：同left函数相同操作方式。

Mid：也是从左向右提出，但不一定是从第一个起，可以从中间开始。如果想在指定的字符开始取值，可以使用MID函数。其公式是：MID(text, start_num, num_chars)，“text”指文本字符串，“start_num”指函数从第几个字符开始取值，“num_chars”指函数取几个字符。

如图1，我要从区域编号中取出ABC开头前3个字符，函数为“=mid(B2,1,3)”,意思是从第1位开始往后取3个字符；

图2要从区域编号中取出BCD0106个字符，函数为“=mid(B2,2,6)”,意思是从第2位开始往后取6个字符。

LEFT、RIGHT、MID又被誉为Excel中取值函数三剑客.

Find：对要查找的文本进行定位，以确定其位置。

使用方法：语法格式=find(find_text,within_text,start_num)；Find（要查找的文本，文本所在的单元格，从第几个字符开始查找[可选，省略默认为1，从第一个开始查找]）。

通过find函数定位，然后提取邮箱中的账号。输入公式=LEFT(A2,FIND("@",A2,1)-1)，如上图

Text：将指定单元格的内容转换成指定的格式。公式=Text(单元格,转换后的格式)

使用方法：如图所示

[if !supportLists]1. [endif]年、月、日的英文单词是“year”、“month”、“day”，所以和年有关的用字母“yyyy”和“yy”；和月有关的用字母“m”，1-3个“m”，分别可以得到的月份的格式不同；和天数有关的用字母“d”。

[if !supportLists]2. [endif]和星期有关的可以用“dd”、“ddd”、“aaa”和“aaaa”，字母短的将得到星期缩写，长的得到完整的星期名称。

[if !supportLists]3. [endif]小时、分钟、秒的英文单词分别是“hour”、"minute”、“second”，所以可以用单词首字母来提取相应的数值。

注意，不要用“Text(时间,"mm")”提取分钟，因为提取月份用的也是“mm”，这样在提取分钟时，就会出错。

Value：将代表数字的文本字符串转换成数字。也就是说，value函数可以将表示数字的文本字符串转换成可运算的数字。注，一般情况下较少使用该函数，可直接在“设置单元格格式”中将文本格式数字转换为数字。

使用方法：公式=VALUE(text)

第二类：逻辑运算类

and,or,false：or和and的区别：or是所列条件中，任一个成立（TRUE），返回TRUE；and是所列条件必须全部成立（TRUE），才返回TRUE

使用技巧：https://jingyan.baidu.com/article/d169e186152d95436711d85a.html（来源：百度经验）

if系列：判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。

使用技巧：https://jingyan.baidu.com/article/b2c186c8f7691fc46ef6ff20.html（来源：百度经验）

is系列（如isblank)：is函数是一个逻辑函数，可以用来判断一些特定的内容。种类包含：

isblank:值为空白单元格；

iserr：值为任意错误值（除去#N/A）

iserror：值为任意错误值（#N/A、#VALLE、#REF!、#DVI/0！、#NUM！、#NAME?或#NULL！）

islogical：值为逻辑值；

isna:值为错误值#N/A（值不存在）

isnontext:值为不是文本的任意项（注意此项函数在值为空白单元格时返回TRUE）

isnumber:值为数字

isref：值为引用

istext：值为文本

使用技巧：https://jingyan.baidu.com/article/f7ff0bfc24f0342e26bb138f.html（来源：百度经验）

第三类：统计计算类

Sumproduct：返回相应的数组或区域乘积的和

附使用技巧：https://jingyan.baidu.com/article/3c343ff7074afe0d36796353.html（来源：百度经验）

sum\sumif\sumifs：求和函数。

附使用技巧：https://jingyan.baidu.com/article/3c343ff7add2400d377963cb.html（来源：百度经验）

count\countif\countifs：count函数是为了某组数据的数量，countif、countifs函数则是在一定条件下统计满足这些条件的数据的个数。

使用技巧：https://jingyan.baidu.com/article/6525d4b15da775ac7d2e94b9.html（来源：百度经验）

Large：计算一组指定数据中指定排名的较大值；例如在指定的十个数据中，最大值是10，次之的最大值是8，就可以通过这个函数计算。

使用技巧：https://jingyan.baidu.com/article/f7ff0bfcca04e62e26bb13fc.html（来源：百度经验）

Small：计算最小值的函数

使用技巧：https://jingyan.baidu.com/article/d7130635e59e8113fdf47508.html（来源：百度经验）

Max：找最大值让函数

使用技巧：https://jingyan.baidu.com/article/a378c9608aa17eb3282830ec.html（来源：百度经验）

Min：求最小值函数

使用技巧：https://jingyan.baidu.com/article/63f236287b55390209ab3d55.html（来源：百度经验）

Rank：将数字的排名单独显示在另一列，而且可以去除重名次

使用技巧：https://jingyan.baidu.com/article/a501d80cea4dd4ec630f5ede.html（来源：百度经验）

 

averge\averageif\averageifs：根据指定的条件or多个条件计算平均数.

使用技巧：https://jingyan.baidu.com/article/d2b1d102cf858b5c7f37d45e.html（来源：百度经验）

第四类：查找/关联匹配类

Lookup：从单行或单列或丛数组中查找一个值。条件是向后兼容性

使用技巧：https://jingyan.baidu.com/article/a378c96090ae50b3282830e5.html（来源：百度经验）

Vlookup：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。公式=VLOOKUP(D:D,D:E,2)

使用技巧：https://jingyan.baidu.com/article/73c3ce28f3c68ce50343d9d1.html（来源：百度经验）

使用技巧：https://jingyan.baidu.com/article/48b37f8dd4e4b31a64648801.html（来源：百度经验）

Index：用来“返回表或区域中的值”或“对值的引用”。

Match：返回指定内容所在的位置

Match&index使用技巧：https://jingyan.baidu.com/article/54b6b9c0d8426f2d583b478d.html（来源：百度经验）

Row：返回所选择的某一个单元格的行数。

使用技巧：https://jingyan.baidu.com/article/2c8c281dcbd5cd0009252a5d.html（来源：百度经验）

Column：返回所选择的某一个单元格的列数。Offset：

使用技巧：https://jingyan.baidu.com/article/72ee561ab08932e16038df59.html（来源：百度经验）

 

第五类：时间函数

Year：返回对应于某个日期的年份；取值范围为：1900 - 9999 之间的整数。

使用技巧：https://jingyan.baidu.com/article/4ae03de3d1d5ce3eff9e6bf1.html（来源：百度经验）

Month：返回月份值，是一个1（一月）到12（十二月）之间的数字。

使用技巧：https://jingyan.baidu.com/article/e2284b2b62c5ece2e6118dd2.html（来源：百度经验）

Weekday：用于返回某个日期是一周中的星期几。默认情况下，天数是1（星期日）到7（星期六）之间的整数。

使用技巧：https://jingyan.baidu.com/article/6fb756ecbeb975241858fbe8.html（来源：百度经验）

Weeknum：返回一年中的周数。

使用技巧：https://jingyan.baidu.com/article/63f2362872f3c00208ab3df3.html（来源：百度经验）

Day：返回一个月中的第几天的数值，介于1到31之间。

使用技巧：https://jingyan.baidu.com/article/11c17a2c260833f446e39d02.html（来源：百度经验）

Date：返回在Excel日期时间代码中代表日期的数字

使用技巧：https://jingyan.baidu.com/article/ab69b2708739062ca7189fab.html（来源：百度经验）

Now：用于返回电脑设置的当前日期和时间的序列号。

使用技巧：https://jingyan.baidu.com/article/454316ab4c8267f7a7c03acd.html（来源：百度经验）

 

------------------------------------------------------------------------------------------------------------------------------------------------------------

 

以上几大类是目前个人正在学习中的几类应用函数，如有更多更好的经验，请多指教~学无止境！