【Excel技巧必知必会】

一、 常用函

围绕中运营工作中经常遇到的数据处理场景，从函数和功能两个方面，讲解几个excel必知必会的小技巧。

1. vlookup()

首先给大家介绍一下excel的万金油函数——VLOOKUP(查找值，查找区域，列序数，[匹配条件])，它既可以正向查找、逆向查找，还可以多条件查找，它的语法如下：
vlookup函数共有四个参数：
查找值：即用谁找，想要看什么条件下的值
查找区域：在哪里找，查找值在哪个数据表范围内可以找到目标值
列序数：目标值在数据表的第几列，是大于等于1的正整数
匹配条件：精准匹配和模糊匹配。0：精准匹配，1：模糊匹配。

（1）正向查找

场景一：已知上海公司9月份和10月份各客户经理的收入，如下图1，现在想知道10月份每个客户经理收入涨跌幅是多少？
　　　　　　　　　　　　　　　　　　表1 上海公司九、十月客户经理收入情况
　　　　　　　　　　　注：签单人名称和收入均为杜撰
这时候你可能会说，就这么七八个人，我一个个复制粘贴过来就好了，那如果有七八百个人呢？vlookup()就派上用场了！在单元格H2输入【=VLOOKUP(E2,$A$2:$C$8,3,0))】，即可得到孙尚香同学9月的收入，下拉得到所有销售9月的收入。然后计算增幅即可（如表3）
需要注意的问题：①查找值一定要在查找区域的第一列；②在选中查找区域后（第二个参数），按下F4键，固定查找区域
　　　　　　　　　　　　　　　　　　　　　　　表2 vlookup正向查找

　　　　　　　　　　　　　　　　　　　　　　　表3 vlookup正向查找结果

（２）多条件查找

场景二：需要匹配上海公司9月和10月，销售收入和下单数量
当然可以用两次vlookup，但是本次介绍一个更为简便的方式，只需要在单条件查找上稍微改动一下即可。
多条件查找公式：

需要注意的是：①第一个参数：用美元符号（$）固定查找值的列；②第三个参数：列序数由固定的数字改成相对引用column(B31),当我们往右拖动填充时，里面的B31变成了C31。另外，column函数只与列有关，与行无关。

（3）反向查找

场景三：想知道9月成交订单为22单的客户经理是谁？
增加一个辅助签单人列，后续操作和正向查找一致。

（4）多重条件查找

场景四：整个集团的收入数据混在了一张表里，现在要查找北京的宋江同学9月的收入。
当然可以筛选出北京公司，然后在查找到宋江同学，但是今天介绍一个可以批量处理大量数据的方式。还是使用vlookup函数。
公式如下：

需要注意的是：
①查找条件，将条件1和条件2用【&】连接起来；
②查找范围：需要重新构造查找区域。查找区域公式 IF({1,0}, 条件1所在区域&条件2所在区域, 结果所在区域)
③列序数：由之前的实际所在列，改成2。因为次数两个查找条件合并成一个，且结果所在列是直接选择出来的；
④出结果的方式：因为含有数组运算，所以需要按【ctrl+shift+enter】才能出结果

2. sumprduct()

函数功能：将数组间对应的元素相乘，并返回乘积之和
语法：sumproduct(数组1，数组2，数组2, …)

3. 时间函数

（1） datediff(起始日期，结束日期，返回类型)：用指定的单位计算起始日和结束日之间的间隔。
间隔天数：DATEDIF(“2021-11-11”,“2021-12-12”,“d”) – 结果值：31
间隔月份：DATEDIF(“2021-11-11”,“2021-12-12”,“m”) – 结果值：1
（2） weekday（日期，返回类型）：返回指定日期对应的星期数。返回类型为1或省略时，则1-7表示星期天到星期六；当返回类型为2时，则1-7表示星期一到星期天。
示例1：WEEKDAY(“2021-12-12”,1) – 结果值：1
示例2：WEEKDAY(“2021-12-12”,2) --结果值： 7
（3） weeknum（日期，类型）：返回日期对应1年中的第几周；类型=1，则一周从周日开始，类型=2，则一周从周一开始。
示例1：WEEKNUM(“2021-12-12”,1) – 结果值：51
示例2：WEEKNUM(“2021-12-12”,2) – 结果值：50
（4） workday：获取间隔若干工作日后的日期
示例：WORKDAY(“2021-12-10”,3） – 返回值：2021-12-15

二、 常用功能

1. 数据透视表

数据透视表是excel最基础，功能也非常强大的一个功能，它可以汇总表格、处理不规范数据、制作动态交互图表、代替复杂公式等等功能。

（1）合并汇总表

场景一：需要统计上海公司9~12月，各签单人的收入总和。
如果用普通的函数或者手动汇总的话，需要一张张表去统计，耗费大量时间。利用数据透视表的多重合并计算，可以快速将这四张表合并起来，并且提供可视化的数据筛选。

第一步：插入选项卡，选择插入数据透视表，再选择【使用多重合并计算区域】（途中红框）；

第二步：点击选择区域–>创建单页字段–>下一步–>选择sheet1表所有数据–>添加–>选择sheet2表所有数据–>添加（依次类推，将所需计算的sheet都添加进去）–>点击完成；

第三步：得到下表，按需统计数据

（2）动态交互图表

利用数据透视表的切片器功能，可以实现图表的交互效果：
光标放在透视表任一地方，选择分许工具栏下【插入切片器】，选择签单人和类型两个字段，得出结果如下：
场景一：选择所有的签单人，以及所有的订单类型，得到图表如下如所示：

场景二：选择部分签单人，只看收入的情况下，得到图表如下图所示：

（3）计算字段

场景三：除了知道各签单人的收入和签单数量，还想知道客单价。
这时候可以通过添加计算字段来实现：分析选项卡下，选择【字段、项目】–> 计算字段。再填入计算公式，点击添加、确认即可。

2. 分列

按照固定长度和字符，对字符串进行拆分。
例：从记账流水中提取花费金额，计算本周消费多少？
第一步：从【数据】选项卡，选择分列功能，使用【分割符号】方式进行分列，分割符号选择其他，具体字符选择【费】，如下图所示：

第二步：得到花费数据，在此进行分列，本次分割符号填写【元】，如下图所示：

第三步：得到下图最右边的结果

三、 快捷键

1. ctrl+e

智能填充：如下图所示：要给每一个年份后面增加一个【年】字样。只需在右边空白处写上增加后的样式（1999年），然后将鼠标放在下一单元格，键盘按下Ctrl + E，剩余单元格即可完成填充

智能提取：下表是一个流水账单，现在需要计算本周共花费多少钱？由于流水账记得比较随意，没有标准化，若要计算总花费，需要将蓝色部分的花费金额提取出来，然后求和。同样地，在右边对应空格输入需要提取的金额【20】，然后在下方单元格按下Ctrl+E，即可完成智能提取。

2. ctrl+home

无论你在表格的何处，只要按下【ctrl+home】，光标1秒钟回到A1单元格；如果只按【home】，则回到本行A列。

3. ctrl+向下箭头

光标会滚到最下面一行，同理ctrl和向上、向左、向右依次可到达最上一行，最左一列，最右一列。

4. ctrl+D

如果相邻列是连续的区域，双击公式所在单元格的右下角即可复制公式。如果相邻列不连续，可以选取公式区域后，按下【ctrl+D】快速复制公式。