文章目录
- 1.INDIRECT跨表引用
- 2.常用功能快速使用（快捷键）
- 3.excel数据透视表，非重复计数
- 4.透视表计算字段、计算项
- 5.条件格式，A列是条件，B列因A列条件显示格式
- 6.快速全表替换某一范围的数
- 7.批量合并单元格
- 8.快速拆分单元格
- 9.以另一个区域的条件格式展示
- 10.倒序查找
- 10.多条件按分组TOP/LAST
- 11.多条件中位数（构造medianif）
- 12.多条件TOPn%均值
- 13.分组汇总
- 14.分组排序
- 15.对颜色单元格计数
- 16.多条件列转字符串
- 17.包含字符的模糊匹配
- 18.批量添加超链接
- 19.将0不显示
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.多条件列转字符串
17.包含字符的模糊匹配
Excel文件，里面有两个表：Sheet2和Sheet3。E列系列名称的清单，希望A列中，如果某个“名称”里包含任何一个系列名称，就在B列显示对应的系列名称。
=IFERROR(INDEX($E2 : 2:2:E5 , M A T C H ( T R U E , I S N U M B E R ( S E A R C H ( 5, MATCH(TRUE, ISNUMBER(SEARCH(5,MATCH(TRUE,ISNUMBER(SEARCH(E2 : 2:2:E$5, A2)), 0)), “”)
18.批量添加超链接
19.将0不显示
看你想要“只是显示为空，实际值还是 0”，还是“实际值也要变成空”。常用方法如下：
- 1、只是不想显示 0，实际值仍为 0（推荐）
局部设置：自定义单元格格式
- 选中区域。
- 按
Ctrl + 1打开“设置单元格格式”。 - 选“数字” → “自定义”。
- 输入：
英文版 Excel 用：G/通用格式;-G/通用格式;;@General;-General;;@ - 确定。这样 0 会显示为空，其他数字正常。
如果要保留两位小数，可用：
0.00;-0.00;;@整个工作表隐藏 0：
文件 → 选项 → 高级 → 找到“此工作表的显示选项” → 取消勾选“在具有零值的单元格中显示零”。
- 2、用条件格式隐藏 0
选中区域 → 开始 → 条件格式 → 新建规则 → 使用公式确定要设置格式的单元格 → 输入：
=A1=0注意：A1改成你选区左上角单元格。然后 → 格式 → 数字 → 自定义 → 输入：
;;;确定即可。
- 3、实际值也要为空
如果单元格是公式结果，可以套IF：
=IF(原公式=0,"",原公式)例如：
=IF(SUM(A1:A10)=0,"",SUM(A1:A10))注意：这样结果变成空文本，不再是数字 0，可能影响后续求和、图表或判断。
总结：只想看起来为空，用自定义格式或取消“显示零值”；想实际为空，用IF(...,"",...)。
