100个Excel实用技巧大全(3)
本文是Excel实用技巧大全的第三部分,重点涵盖数据处理、常用函数、查找匹配以及视图保护与打印四大模块。这些技巧旨在帮助用户解决数据清洗、复杂计算、跨表引用及打印输出等实际办公问题,通过系统化的操作指南提升工作效率。
01数据处理与清洗
- 删除重复项
操作:数据 → 删除重复项 → 勾选列 → 确定
用途:名单、订单、账号一键去重。 - 分列功能
操作:数据 → 分列 → 分隔符 / 固定宽度
用途:将“张三 13800138000”拆分至两列。 - 数据验证(下拉菜单)
操作:数据 → 数据验证 → 允许:序列 → 来源:男,女
用途:制作下拉选项,规范数据录入。 - 限制输入范围
操作:数据验证 → 整数 / 小数 / 日期 → 设置范围
用途:防止输错年龄、分数、日期。 - 快速筛选
操作:选中表头 → Ctrl+Shift+L
用途:快速开启或关闭筛选模式。 - 筛选后批量修改
操作:筛选 → 选中区域 → 输入内容 → Ctrl+Enter
用途:仅修改筛选出来的可见单元格内容。 - 数据排序
操作:数据 → 排序 → 设置条件
用途:按金额、日期、部门进行多条件排序。 - 数据分组
操作:选中行 → 数据 → 分组
用途:折叠或展开明细,便于查看汇总数据。 - 清除多余空格
操作:=TRIM(A1)
用途:删除首尾空格及多余中间空格,解决VLOOKUP失效问题。 - 英文大小写转换
操作:=UPPER() 大写 / =LOWER() 小写 / =PROPER() 首字母大写
用途:统一英文文本格式。
02常用统计与逻辑函数
| 函数名称 | 操作语法 | 主要用途 |
|---|---|---|
| 一键求和 | Alt+= | 自动对下方数据求和 |
| SUM | =SUM(区域) | 合计金额、数量 |
| AVERAGE | =AVERAGE(区域) | 计算平均分、平均工资 |
| MAX/MIN | =MAX(区域)/=MIN(区域) | 查找最高/最低分、业绩 |
| COUNT/COUNTA | =COUNT(区域)/=COUNTA(区域) | 统计数字个数/非空单元格个数 |
| COUNTIF | =COUNTIF(区域,条件) | 单条件计数(如及格人数) |
| SUMIF | =SUMIF(条件区域,条件,求和区域) | 按部门、产品等单条件求和 |
| COUNTIFS/SUMIFS | =COUNTIFS(区1,条1,区2,条2) | 多条件统计与求和 |
| IF | =IF(条件,满足,不满足) | 判断及格/不及格、达标/不达标 |
| IFERROR | =IFERROR(公式,"") | 屏蔽错误值,保持表格整洁 |
常用统计与逻辑函数速查表
03查找匹配与文本处理
- VLOOKUP 基础查找
操作:=VLOOKUP(查找值,区域,返回第几列,0)
用途:按姓名查电话、按学号查成绩。 - XLOOKUP 新版万能查找
操作:=XLOOKUP(查找值,查找列,返回列)
用途:功能更强,支持反向查找。 - INDEX+MATCH 万能组合
操作:=INDEX(返回列,MATCH(查找值,查找列,0))
用途:任意位置查找,不限制从左到右。 - 文本截取函数
操作:=LEFT(单元格,位数) / =RIGHT(单元格,位数) / =MID(单元格,起始位,位数)
用途:提取前几位、后几位或中间几位字符。 - LEN 统计字符长度
操作:=LEN(A1)
用途:检查手机号、身份证位数是否正确。 - CONCAT 文本拼接
操作:=CONCAT(A1,"-",B1)
用途:合并姓名、部门、城市等信息。 - TEXT 格式化
操作:=TEXT(A1,"yyyy-mm-dd")
用途:统一日期或数字显示格式。 - TODAY 动态日期
操作:=TODAY()
用途:每天自动更新为当天日期。 - DATEDIF 计算工龄/年龄
操作:=DATEDIF(开始日期,TODAY(),"Y")
用途:自动计算年龄或工龄。 - 批量填充公式
操作:选中区域 → 输入公式 → Ctrl+Enter
用途:一次性给所有选中单元格填入公式。
04视图、保护与打印优化
- 冻结首行+首列
操作:点B2单元格 → 视图 → 冻结窗格
用途:固定表头和第一列,滚动时保持可见。 - 拆分窗格
操作:视图 → 拆分
用途:同时查看表格的上下或左右部分。 - 隐藏/取消隐藏工作表
操作:右键工作表 → 隐藏/取消隐藏
用途:隐藏辅助表或原始数据。 - 保护工作表
操作:审阅 → 保护工作表 → 设置密码
用途:防止他人误改公式或表格结构。 - 打印标题行
操作:页面布局 → 打印标题 → 顶端标题行 → 选中表头
用途:确保每一页都打印出表头。 - 调整为一页宽
操作:页面布局 → 宽度:1页
用途:保证表格横向不被切成多页。 - 打印网格线
操作:页面布局 → 勾选 打印 网格线
用途:打印出带格子的表格。 - 分页预览
操作:视图 → 分页预览 → 拖动蓝色分页线
用途:手动调整打印范围。 - 插入/编辑批注
操作:选中单元格 → Shift+F2
用途:添加备注或说明信息。 - 设置自动保存
操作:文件 → 选项 → 保存 → 自动保存间隔5分钟
用途:最大程度避免文件丢失。
当前文章无评论,是时候发表评论了