【Excel】零碎技能积累

1.INDIRECT跨表引用

工作簿中有多张工作表，现在想把这些工作表的内容引用到另一个工作簿的一个工作表内。
如：《品类data.xlsx》里有多个工作表
这样就可以动态拖动就可以将不同工作表的数据引用

2.常用功能快速使用（快捷键）

将要使用的功能添加至《快速访问工具栏》
可以通过【文件-选项-自定义快速访问工具栏】添加

或者左上角

这样excel就自动给这个功能快捷键，按ALT查看

如升序快捷键就可以直接使用【ALT+5】快速调用

3.excel数据透视表，非重复计数

插入数据透视标的时候，勾选将此数据添加到数据模型（如果不能勾选，则注意文件格式），在值字段设置里面就会出现非重复计数选项

4.透视表计算字段、计算项

待补充

5.条件格式，A列是条件，B列因A列条件显示格式

在A2新建条件格式，如上，格式刷

6.快速全表替换某一范围的数

7.批量合并单元格

STEP1:要合并的单元格重复数据

快捷键CTRL+G调出定位，选择所有空值
输入公式，引用一个各空格的上一个单元格，ctrl+enter全部填充公式
STEP2 使用分类汇总找到要合并的行，合并后复制格式，最后取消分类汇总

8.快速拆分单元格

效果

实现

9.以另一个区域的条件格式展示

使用剪贴板

10.倒序查找

方法1：index+match，动态定位行、列

方法2：指定前后列序-if({1,0},列1，列2)

方法3：指定多列先后列序-choose({1,2,…n},列1，列2,…列n)

10.多条件按分组TOP/LAST

原理：使用数组判断条件结果，使得非组元素值归0

算倒一的时候，将0替换成空格，则0不参与min排序特性，不会干扰结果

11.多条件中位数（构造medianif）

12.多条件TOPn%均值

原理：使用统计函数计算百分位数，用于过滤数组范围

多条件需要对数据列用if数组过滤

13.分组汇总

14.分组排序

15.对颜色单元格计数

16.多条件列转字符串