Excel函数学习随笔

2017/7/9

学习心得：

vlookup函数： VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

查找目标要在区域的第一列；查找范围要用绝对引用（$A$3:$F$19）；返回值列数以引用范围第一列为始；一般均使用精确查找（false/0）

查找目标的模糊搜索方法："*" & 字符 & "*"（即搜索包含该字符内容的所有目标）；

数字区间查找（模糊查找的应用）：使用前提：引用数字区域必须是从小到大排列。使用效果：给一定数，函数会找到和它最接近，但比它小的那个数。

多行查找时的技巧：使用COLUMN函数。COLUMN（A1）返回值为1，COLUMN（B1）返回值为2，向右复制时会自动变为B1、C1、D2...即改变返回值的列数。

避免返回值错误的方法：使用iferror函数（ IFERROR（VLOOKUP（），"") ）。

反向查找（用if函数将数组调换顺序）：VLOOKUP(查找目标,IF({1,0},区域2,区域1),返回值列数,0)。1表示返回区域2,0表示区域1，即将区域1、2进行了调换，即实现从右向左的反向查找。

多项查找：用if函数将多项查找的对应列合并成一个数组。=VLOOKUP(条件1&条件2,IF({1,0},条件1的查找列&条件2的查找列,其余列,返回值列数,0)

 

疑问：vlookup一次只能查找一个值，如果想要筛选出满足条件的所有返回值如何做到？

 

今晚还尝试做了网上的众多练习题（虽然大多没做出来...），最大的感触：

涉及数组一定要用三键（ctrl+shift+enter）来确认公式

 

 7/10

今天是针对网上训练的心得记录。

1、sumifs函数涉及多条件运算{(),()}时，运算结果为{a，b}，只能得到第一个数，需用sum（sumifs）才能将数组结果求和。sumproduct和sumifs大部分情况下可以替换功能使用。

如：=SUM(SUMIFS(D19:D29,B19:B29,"一公司",C19:C29,{"女式套裙","女式连衣裙"}))和SUMPRODUCT((B19:B29="一公司")*((C19:C29="女式连衣裙")+(C19:C29="女式套裙")),D19:D29)

2、遇到去除问题时，可用"<>XXX"指令。

3、日期转换中，自定义格式和查找替换可以规范格式。

4、当条件为关键字符对应求和时，用SUMPRODUCT（ISNUMBER（FIND（））*区域）指令

5、涉及日期统计时，透视图表更直观方便。

 

7/16

摸鱼好几天，基本在熟悉公式和完成练习。

开始接触分析数据。

步骤：观察-清洗（标准化、去除重复等）-分析

以网上的数据举例：（数据来源：全国数据分析师岗位需求表)

第一步，为了使创建透视表方便，翻译列标题，并隐藏不需要统计的列。

第二步，去除重复项，对工资、福利等进行一致化处理。

第三步，创建数据透视表，进行数据分析：

我首先分析的是城市岗位需求量的分析，北上广深果然不负一线城市的名声，杭州也异军突起，但显然北京的需求量远远领先其他城市，同时发现经验需求为【3-5年】的岗位量最多。

接着我观察的是薪水，用的是平均薪水来观察，公式还挺有趣：

SUM(LEFT(O2,FIND("k",O2,1)-1),LEFT(P2,FIND("k",P2,1)-1))/2

分析结果也很显而易见，北京工资也是傲视全国，以及从全国平均值和各城市平均值也可以看出北京的工作岗位之多，从数据上看从【应届毕业生】到【3-5年】工资就接近翻倍，说明工资涨幅还挺高。

同时还对职位标签进行了分析，其中分析师等核心标签出现最多。

尝试如上。