excel技巧

🍓选中

1. 📌将鼠标放在你要选中的边界单元格的框线上
2. 📌按照shift，然后双击鼠标左键

🍓填充

1. 📌快速向下填充：ctrl + d

2. 📌快速向右填充：ctrl + r

3. 📌快速向右和向下填充：

   1. 选中填充区域
   2. 编写填充模板
   3. ctrl + enter
   

4. 📌顺序填充1-10000序列

   1. 在单元格输入初始值
   2. 选择填充-序列
   3. 序列产生在列，步长1，终止值10000
   

5. 📌选中单元格填充：ctrl + enter
   

🍓日期

🍒🍒 日期快捷方式

1. 📌快速输入当前日期：ctrl + ;
2. 📌快速输入当前时分：ctrl + shift + ;
3. 📌快速输入当前日期 + 时分：

   1. ctrl + ;
   2. 按空格
   3. ctrl + shift + ;
   

🍒🍒 日期计算

	前提条件：A2、B2、C2 都要设置成日期格式
	B2 = A2 - 5
	C2 = A2 + 5

🍒🍒时间相减

将两个日期格式的单元格相减得到天数，注意，结果单元格需要设置成常规格式。

🍓求和

1. 📌选中需要求和的数据以及求和空白列
2. 📌快捷键ALT + =
   

🍓去除小数点

1. 📌选中数据
2. 📌快捷键ctrl + shift + 1，默认四舍五入
   

🍓美化表格

🍒🍒快捷方式

1. 📌全选表格
2. 📌快捷键ctrl + T

🍒🍒单元格隔行自定义变色

1. 先给A2单元格填充自定义颜色
2. 选中A2A3，点击格式刷
3. 按住shift格式刷全表

🍓合并单元格

🍒🍒合并两个单元格数据

C2 = A2&B2

🍒🍒合并多个单元格数据

1. 填充B列
2. 使用phonetic函数，目标选中A1-B9

🍓定位

🍒🍒找出两列数据的差异

1. 全选两列
2. 快捷键ctrl + G
3. 行内容差异单元格
4. 填充颜色

🍒🍒空白区域填充

1. 选中区域
2. 快捷键ctrl + G
3. 选中空值
4. 输入需要填充的值
5. ctrl + enter
   

🍒🍒复制单元格不复制隐藏区域

1. ctrl + g
2. 选择可见单元格
3. 复制粘贴

🍓自定义单元格格式

🍒🍒编码字典

1. 选中性别列
2. ctrl + 1
3. 自定义，类型输入下列代码
	[=1]"男";[=2]"女"

🍒🍒批量添加单位

1. 选中数据
2. ctrl + 1
3. 自定义
4. 在通用格式后面输入单位

🍒🍒其他常用的自定义格式

1. 📌手机号格式：000-0000-0000
2. 📌科学计数法转为正常数字：0
3. 📌数字后面添加百分比：0\%
4. 📌自动在单元格后面添加内容：@""
   例：@"@qq.com"添加QQ邮箱尾号
5. 📌自动在单元格两端添加内容：@
   例：《@》添加书名号

🍓交换两个列的位置

1. 📌选中需要交换的其中一个列
2. 📌鼠标往选中的列边缘移动直到他变成了十字型，按住shift键向需要调换的列边缘拉伸即可。

🍓文字

🍒🍒横排文字转竖排文字

🍒🍒横文字转竖文字

1. 📌选中对应的单元格
2. 📌在字体前面输入@
   

🍓转换表格行列

1. 📌全选复制表格
2. 📌在一个空白的表格里右键粘贴-选择性粘贴-转置
   

🍓辅助列思维

🍒🍒制作工资条

1. 在F2-F10之间生成序号
2. 将序号拷贝到F11-F19列下面
3. 拷贝标题到A11-E19
4. 按照F列排序就可以了

🍒🍒隔行插入空行

1. 在F2-F10之间生成序号
2. 拷贝F2-F10序号到F11-F19
3. 按照F列排序

🍓筛选

1. 📌全选表格，输入快捷方式ctrl + t创建超级表
2. 📌在表格工具中插入切片器，选择对应的列
   

🍓圈出不符合条件的数据

1. 选中B2-G11
2. 数据-数据验证-设置-允许整数-大于-60
3. 数据-数验证-圈释无效数据

🍓保护工作表不被他人修改

1. 📌全选表格
2. 📌审阅-保护工作表-输入密码
3. 📌其他人若想修改，点击审阅-撤销工作表保护-输入密码

🍓excel下拉列表

• 📌选中需要制作下拉列表的列
• 📌数据-数据验证-允许序列-来源男,女
  

🍓#号显示数据

#号显示的数据是由于单元格内容的宽度比单元格展示的宽度小造成的，我们只要增加单元格的宽度就可以使内容正常显示

🍓智能拆分

核心技术：ctrl + e

🍒🍒获取身份证的出生日期

1. 输入第一个身份证的出生日期
2. 选中B列
3. ctrl + e

🍒🍒提取带有单位的数字

1. 输入第一个单元格的数字
2. 选中B列
3. ctrl + e

🍒🍒添加前缀后缀

1. 输入第一个单元格的前缀后缀
2. 选中B列
3. ctrl + e

🍓逆向查找

涉及技术：

• 📌INDEX函数
• 📌MATCH函数
  

=INDEX(A2:A9,MATCH(I2, B2:B9, 0), 1)

🍓输入内容自动添加边框

1. 选中需要自动添加边框的区域
2. 开始-条件格式-新建规则-使用公式确定要设置格式的单元格
3. 编辑公式-选择第一个单元格A2，删除数字前面的$符号
4. 点击格式-边框-外边框

🍓条形图

🍒🍒数据条

1. 选中数据列
2. 开始-条件格式-数据条

🍒🍒折线图

1. 插入-折线图
2. 选中折线图数据列

🍒🍒自定义迷你条形图

涉及技术：

• 📌rept函数
• 📌内容拼接&
  

🍓SUMIF函数

计算姓张的总成绩：

SUMIF(B2:B12, "张*", C2:C12)

🍓excel常用函数

函数名称	中文释义	参数	作用
upper	字母大写	目标单元格	字母大写转换
lower	字母小写	目标单元格	字母小写转换
proper	首字母大写	目标单元格	首字母大写
int	向下取整	目标单元格	去除小数，向下取整
text	文本格式化	目标单元格,格式	文本格式化
rank	排名计算	目标单元格,参与排名的单元格区域,[0（默认值）降序1升序]	获取名次
index	数组取值	目标单元格数组区域，索引(下标从1开始)	根据索引显示数组中的值
match	返回匹配目标的行号	目标数据,匹配区域,匹配类型（0精确匹配，1小于，-1大于）	返回匹配目标的行号
RANDBETWEEN	随机数	最小值，最大值	生成最小和最大之间的随机数，包含边界
VLOOKUP	纵向查找	查找目标单元格,查找区域和结果区域,结果区域序号（序号默认从1开始计算）,匹配模式	纵向查找匹配，按列查找
HLOOKUP	横向查找	查找目标单元格,查找区域和结果区域,结果区域序号（序号默认从1开始计算）,匹配模式	横向查找匹配，按行查找
IF	条件判断	条件,满足条件返回值,不满足条件返回值	条件判断
IFERROR	错误判断	条件表达式，错误值	当条件表达式返回为错误值如#N\A，#NAME?,则返回我们设置的错误值
SUM	求和	目标区域	求和
SUMIF	条件求和	条件判断区域,条件(条件内容需要加双引号),求和区域	条件求和
SUMIFS	多条件求和	求和区域,条件1判断区域,条件1(条件内容需要加双引号),[条件2判断区域,条件2…]	多条件求和
MAX	最大值	目标区域	最大值
MIN	最小值	目标区域	最小值
ABS	绝对值	目标区域	绝对值
AVERAGE	平均值	目标区域	平均值
ROUND	四舍五入	目标单元格	四舍五入
LEFT	从左起截取字符	目标单元格，截取个数	从左起截取字符
RIGHT	从右起截取字符	目标单元格，截取个数	从右起截取字符
MID	从指定位置截取字符	目标单元格，指定位置（下标从1开始计算）,截取个数	从指定位置截取字符
count	统计个数	目标区域	只有目标内容为数字的单元格才参与计数
countA	统计个数	目标区域	目标单元格为空的不计数
countif	条件统计	条件区域,条件（条件内容需要加双引号）	条件统计
countifs	多条件统计	条件1区域,条件1（条件内容需要加双引号）,[条件2区域,条件2…]	多条件统计
row	获取行号		获取单元格行号
column	获取列号		获取单元格列号
today	获取当前日期		获取当前日期
now	获取当前日期时间		获取当前日期时间，精确到分
year	获取年份	目标单元格	获取年份
month	获取月份	目标单元格	获取月份
day	获取日	目标单元格	获取日
weekday	获取星期	目标单元格	按照日-六，1-7返回
weeknum	获取目标周在一年中属于第几周	目标单元格	获取目标周在一年中属于第几周
rept	指定文本重复次数	文本内容,重复次数	指定文本重复次数
trim	清除字符串里的多余空格	文本内容	清除字符串里的多余空格

🍓excel常用快捷方式

• 📌ALT快捷方式
  • 🍁ALT + = 求和
  • 🍁ALT + F1生成图表
  • 🍁ALT + enter强制换行
  • 🍁ALT + ↓下拉列表
  • 🍁ALT + 鼠标左键拖动表格到另外一个工作簿
  • 🍁ALT + ;定位可见单元格，可以在复制的时候使用，避免复制隐藏单元格
• 📌填充快捷键
  • 🍁连续区域填充
    • 🌷ctrl + D 向下填充
    • 🌷ctrl + R 向右填充
  • 🍁不连续区域填充
    • ctrl + enter
• 📌定位
  • 🍁ctrl + G
• 📌ctrl + t一键美化单元格
• 📌ctrl + \一键选择两列中有差异的数据
• 📌ctrl + e智能拆分数据
• 📌ctrl + shift + +快速插入一行或一列
• 📌ctrl + -快速删除一行或一列
• 📌F4锁定函数参数的选中区域
• 📌F9重新执行一遍函数
• 📌ctrl + 9隐藏行
• 📌ctrl + 0隐藏列
• 📌ctrl + k插入超链接
• 📌ctrl + shift + 1：四舍五入
• 📌ctrl + 4：添加下划线
• 📌ctrl + 5：添加删除线

🍓text函数的格式

单元格格式	数字	TEXT(A,B)	说明
Format_text	Value	值
G/通用格式	10	10	常规格式
000.0	10.25	010.3	小数点前面不够三位以0补齐，保留1位小数，不足一位以0补齐
####	10.00	10	没用的0一律不显示
00.##	1.253	01.25	小数点前不足两位以0补齐，保留两位，不足两位不补位
正数；负数；零	1	正数	大于0，显示为“正数”
	0	零	等于0，显示为“零”
	-1	负数	小于0，显示为“负数”
0000-00-00	19820506	1982-05-06	按所示形式表示日期
0000年00月00日		1982年05月06日
aaaa	2014/3/1	星期六	显示为中文星期几全称
aaa	2014/3/1	六	显示为中文星期几简称
dddd	2007-12-31	Monday	显示为英文星期几全称
[>=90]优秀；[>=60]及格；不及格	90	优秀	大于等于90，显示为“优秀”
	60	及格	大于等于60，小于90，显示为“及格”
	59	不及格	小于60，显示为“不及格”
[DBNum1][$-804]G/通用格式	125	一百二十五	中文小写数字
[DBNum2][$-804]G/通用格式元整		壹佰贰拾伍元整	中文大写数字，并加入“元整”字尾
[DBNum3][$-804]G/通用格式		1百2十5	中文小写数字
[DBNum1][$-804]G/通用格式	19	一十九	中文小写数字，11-19无设置
[>20][DBNum1];[DBNum1]d	19	十九	11-显示为十一而不是一十一
0.00,K	12536	12.54K	以千为单位
#!.0000万元		1.2536万元	以万元为单位，保留4位小数
#!.0,万元		1.3万元	以万元为单位，保留1位小数

技术活 必须要学会的3个统计函数