Excel 使用技巧
Excel
1、统计不重复项的个数
=SUMPRODUCT(1/COUNTIF(B2:B81,B2:B81))
统计合同名称中不重复的单位数量,也就是实际合同总数量,
SUMPRODUCT参数为一个数组时不做乘积,而是求和。
2、排名
= SUMPRODUCT((D2<$D$2:$D$8)*1)+1
或
= RANK(A2,($A$2:$A$4)
= RANK(A2,($A$2:$A$4,$C$2:$C$4))
在()中用逗号连接多个区域
3、实现自动编号,档筛选和隐藏时重新自动编号
=SUBTOTAL(103,B$2:B2)
4、SUMPRODUCT多条件求和的案例讲解
5、高级筛选的条件区域格式
范围 | 姓名 | 销售数量 |
---|---|---|
>=0.02 | 张三 | >1000 |
<2018-09-30 | 李四 | >5000 |
条件放在不同行是或
OR
的关系,同一行是与AND
的关系;
6、条件格式的公式应用
※ 实现整行记录标识
公式 =$A1="北京无线电测量研究所"
※ 满足多条件的数据突出显示
公式 = ($B2>=5000)*($C2>=300)
※ 关键词匹配
公式 = FIND ("股份",$A1)
注意:规则通过管理进行设置,格式要自行定义
7、如何同时显示日期和星期?
自定义格式: yyyy-y-d aaaa
8、自动生成报表目录并添加超链接
※ step1:Ctrl + F3
在弹了的窗口中输入名称getsh
, 在引用位置输入公式:
引用位置 = MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())
※ step2:在单元格输入公式并下拉即可生成工作表的目录。
公式 = IFERROR(HYPERLINK("#"&INDEX(getsh,ROW(A2))&"!a1",INDEX(getsh,ROW(A2))),"")
注意:这里的名称用的是
getsh
,公式都是在目录页输入的
## 9、数据验证和条件格式结合使用突出显示目标数据
※ step1:在H6设置数据验证列表
※ step2:需要突出显示的数据,设置条件格式 公式 = $A3=$H$6
类似的还可以:
公式 =A1<>""
公式 =AND($C3="女",YEAR($D3)>1980)
参考:Excel技巧精选
## 10、不改变数据源实现二级联动下拉菜单
提示:
※ 主要公式 = OFFSET(B$1,MATCH(G1,A:A,)-1,,COUNTIF(A:A,G1))
※ 数据-删除重复项
※ 取消合并单元格快速填充: F5 定位 - 选空值 - 输入第一个空单元格的取值公式(例如:=A2)- Ctrl + Enter
※ excel中合并单元格快速拉序号,输入公式 =MAX($A$1:A1)+1
本文内容的 13 合并单元格的取消和每行填充相同内容 提供了另一种解决方案!
## 11、一个关键字查找筛选对应的多行数据 `INDEX+SMALL+IF` 万金油函数
=INDEX(A:A,SMALL(IF(B$2:B$17=G$1,ROW($2:$17),99),ROW(A1)))
=INDEX(C:C,SMALL(IF(B$2:B$17=G$1,ROW($2:$17),999),ROW(A1)))
## 12、报价表文件,不等行的合并单元格对应数据快速求和
※ step1:选中求和列
※ step2:
= SUM( C2:$C$12 )- SUM( D3:$D$12 )
= SUM(数据首行:$数据尾行) - SUM(求和列次行:$求和列次行)
※ step3:Ctrl + Enter
NC报价表涉及并发数和单价不适合
## 13、合并单元格的取消和每行填充相同内容
公式 = LOOKUP("座", $D$3:D3)
主要为VLOOKUP查找扫清道路,去掉合并单元格
## 14、当数字变 E+ , 一个?让它现原形!
Ctrl + 1
设置单元格格式 - 自定义 - 输入数字 0
如果输入
0000000
当0的个数大于整数位,会用0在数字前补齐
还可以输入?
和#
达到同样效果,区别是:
-
当0的个数大于整数位时,会以0补齐位数
-
当?的个数大于整数位时,会以空格补齐位数
-
当#的个数大于整数位时,不补。
设置 e/mm/dd
日期对齐
## 15、按单元格颜色求和
※ step1:定义名称,为提取颜色值做准备
公式 - 定义名称 ,在定义名称窗口中输入名称 mycolor(可以自定义),然后在下面引用来源中输入公式:
=GET.CELL(63,Sheet2!B2)&T(NOW())
公式说明:
Get.cell(63,单元格) 可以获取单元格填充颜色值
&T(NOW()) 实现表格在更新时定义名称取值也可以更新
※ step2:获取颜色值
在C列和E列分别输入公式 =mycolor,即可获取B列和D列的单元格填充色。
※ step3:剩下的交给sumif函数吧
=SUMIF(C:C,E2,B:B)
## 16、制作二级联动下拉
※ step1:设置数据源区域。就是把手机名称和型号整理成如下图格式备用,存放的位置随意;
※ step2:批量定义名称。选取手机名称和型号区域后,公式选项卡 - 定义的名称组 - 根据所选内容创建,选取窗口上的“首行”复选框;
※ step3:设置数据有效性。选取型号列,打开数据有效性窗口,在来源中输入=indirect(D5)
## 17、实现自动到期提醒
要求:离到期日30天内行填充红色,60天内填充黄色。要求可以修改G2:G3的期间,提醒色会自动更新
※ step1、添加到期天数列,计算出离还款还有多少天。
公式 =D2-TODAY()
※ step2、添加辅助表,可以用来动态调整提醒的天数。
15表示15天内,45表示45天内。
※ step3、选取表格(标题行不选),开始 - 条件格式 - 新建规则。然后进行如下设置:
-
类型:使用公式确定要设置格式的单元格
-
设置格式框中输入公式 =$E2<$G$2(按E列到期日判断,所在E前要加$)
-
点格式按钮,设置填充色为红色。
※ step4、按※ step3方法,再添加一个规则。公式为=$E2<$G$3,格式设置为黄色。
※ step5、开始 - 条件格式 - 管理规则,在规则管理窗口中,把30天内规则提升到最上面。
设置完成!
16-17参考:Excel表格的精选39个技巧
## 18、Mlookup函数来了
=Mlookup(查找内容,查找区域,返回值所在的列数,第N个)
语法说明:
查找内容:除了单个值外,还可以选取多个单元格,进行多条件查找。
查找区域:同VLOOKUP
返回值的在列数:同VLOOKUP
第N个:值为1就返回第1个符合条件的,值为2就返回第2个符合条件的....当值为0值时,返回最后1个符合条件的值,值为-1时返回所有查找结果并用逗号连接。
举例:
※ 查找第2次电视的进货数量。
=Mlookup(A11,A2:D8,4,2)
※ 查找电视的最后一次入库数量
=Mlookup(A11,A2:D8,4,0)
※ 查找47寸电视的第1次进货数量。
=Mlookup(A11:B11,A2:D8,4,1)
※ 实现筛选功能。
=Mlookup($B$10:$B$11,$A$1:$D$8,4,A14)
※ 实现多结果查找功能。(把所有符合条件结果用逗号连接起来)
=MLOOKUP(A11,B$1:C$8,2,-1)
18 参考:Mlookup函数来了
## 19、获取文件路径
文件-信息-打开文档位置
## 20、通过批注添加图片
在制作产品介绍表或员工信息表时,常需要添加产品图片和员工照片,这时用批注插入图片是最好的选择。
选取批注 - 右键“设置批注格式” - 颜色 - 填充效果 - 图片 -选择图片
## 21、利用批注完成快速合并两列文字
先插入一批注,然后复制多列数据,再选取批注按ctrl+v粘贴,然后再复制批注文字粘贴到表格中即可。
注意:很多同学粘贴批注无效,是因为要点选批注边缘来选取批注,不要让批注进入文字编辑状态。
## 22、每页打印标题行
如果想在打印时每一页都显示标题,页面布局 - 打印标题 - 首端标题行:选取要显示的行
## 23、同时修改多个工作表
按shift或ctrl键选取多个工作表,然后在一个表中输入内容或修改格式,所有选中的表都会同步输入或修改。这样就不必逐个表修改了。
## 24、显示公式部分计算结果
选取公式中的要显示的部分表达式,按F9
## 25、选择性粘贴的计算(把所有数据进行计算)
按Ctrl + Alt + v
选择性粘贴
常用的把财务数据转为万元版
※ step1: 在一个单元格输入1000,并且复制这个单元格 Ctrl + C
※ step2:选择数据区域全部数据
※ step3:选择性粘贴,选 除
确定即可。
用【万】显示金额数另一种方法:
同样选中更改区域,在字体选项卡下的自定义,输入代码:0!.0,万
,然后确定即可
※ 显示万的自定义代码:0!.0,万
※ 百万元显示金额自定义格式代码为:0.00,,
如果改为
*1
的话,可以把文本格式的数字全部变为数值!
## 26、高亮显示选中行列:聚光灯效果
※ step1:条件格式设置公式: =OR(CELL("row")=ROW())
=OR(CELL("col")=COLUMN())
=(CELL("row")=ROW())+(CELL("col")=COLUMN())
※ step2:VBA 工作表选择 Worksheet SelectionChange 加入代码
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
条件格式设置时,范围要设置整个数据区域;
文件保存格式为 .xlsm ;
以上步骤完成之后,聚光灯效果已经实现了,但可能会出现无法将单个或多个单元格复制粘贴数据,解决办法就是:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then Exit Sub
ActiveSheet.Calculate
End Sub
纯VBA 实现方式:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Parent.Cells.Interior.ColorIndex = xlNone
.EntireRow.Interior.Color = RGB(230, 230, 250)
.EntireColumn.Interior.Color = RGB(250, 235, 215)
End With
End Sub
27、对齐的多种方式
※ 缩进对齐
按Ctrl选取区域,点工具栏中的“增加缩进量”可以批量向后缩进对齐
※ 文字竖排
选取表格的文字 - 开始 - 对齐 - 竖排文字
※ 跨列居中
选取标题和后面的空列,设置单元格格式 - 对齐 - 水平对齐 - 跨列居中
※ 姓名对齐
设置单元格格式 - 对齐 - 分散对齐 - 设置合适缩进量
※ 倾斜表头
开始- 对齐 - 逆时针角度
※ 填充对齐
可以重复输入的字符,常用于制作分隔线
※ 两端对齐
当单元格较少(一个格能容下),两端对齐默认为左对齐样式。如果需要换行,它和自动换行的区别是,两端对齐是每一行文字都会两端顶格显示。
※ 自带页签目录的使用
鼠标放在excel 标签最前面 有两个方向箭头 Ctrl+鼠标左键
会调到第一个和最后一个表页,放在第一个箭头 点右键
打开工作表目录
28、切片器快速制作动态图表视频
注意:切片的样式修改,需要先复制。
参考:透视表的常见错误
29、隐藏部分数据,便于截图
单元格式,自定义,设置一个格式;;;
再截图就可以了。
30、防止重复录入
选取要防止重复录入的单元格区域,数据 - 有效性 - 自定义 - 公式:
=COUNTIF(A:A,A2)=1
31、实现间隔行标识
公式 =(MOD(ROW(),2)=1) 首行有标识
公式 =MOD(ROW(),2)=0 首行无标识
公式=mod(row(),2)从内到外的解释:
ROW()为选中的所有列进行操作,返回该列所在的行号;
MOD为求余数,即ROW()返回的行号除以2的余数,这里判断余数是否为0,即选出了所有偶数行。
=mod(row(),3),表示对2列表标颜色
规则:=mod(column(),2),表示给一列标注颜色,标注多列则修改对应数值即可,如果给5列标颜色,将数值修改为6即可,以此类推。
31、实现间隔行插入空行
※ step1:首先我们在表格末尾,输入序列1、2、3……对需要在其后面插入空格的行标号。在最前面我们输入”序号“一方面后面排序。
※ step2:标号完之后,我们选择他们,然后点击【复制】
※ step3:在最后一个行号后面,选中后粘贴,
※ step4:然后我们头全部选择
※ step5:然后再点击【数据】,然后选择【排序】
※ step6:弹出排序的对话框,然后我们在主要关键词那输入序号,也就是我们编号1上方的名称,在排序依据那选择”数值“,在次序那选择升序,然后再点击【确定】
32、Ctrl+E 拆分数字和文字
20分钟
50秒
快速拆分数字和单位此功能是Excel 2013后的版本才有哒,超好用。在A列输入需要拆分的数据在B1输入A1里的数字,光标移到B1单元格右下角,变成“+”时双击,填充方式选择为【快速填充】同理,在C1输入A1中的数据单位,光标移到C1单元格右下角,变成“+”时双击,填充方式选择为【快速填充】
从Excel97至现在的最新版本2016,如果评选Excel中最简单、易用、功能最强大的技巧,非2013版新增的"快速填充"(快捷键 Ctrl+E)莫属。向下拖动复制后,打开下拉菜单,就可以看到它的身影【快速填充】
※ 拆分姓名和手机号码
先输入第一个姓名,选取它和下面的空行(也可以只选取下面的第一个空格),按Ctrl+E,一秒搞定拆分(提取手机号码同样方法)
即使手机号码位置不定,也可以提取出来
※ 字符中统一添加特定字符
每个数字后添加 万元,先输入一个带万元的,再按按Ctrl+E,同样一秒完成!
※ 合并姓名和手机号码
想合并在一起呢?先输入一行,再按按Ctrl+E,同样一秒完成!
※ 从身份证号码中提取生日
填充前要把单元格格式设置成文本格式,然后在前2个单元格中输入日期,选C4单元格按Ctrl+E填充。
其实,用分列更方便,不需要设置文本格式,提取出的是日期格式。
※ 高难度的字符串乱序重组
单元格内的文字顺序调整,先输入一行,剩下的按Ctrl+E
※ 提取括号内的字符和数字
※ 手机号码分段显示和加密
※ 提取市区名称
※ 银行账号添加空格
Ctrl+E很好用,但再次遗憾的告诉大家:只有excel2013和excel2016支持它。
33、剪贴板的调用
开始选项卡 右下角点击打开 剪贴板
多次复制后,可以选择全部粘贴 ,用于多次复制报表,合并粘贴到一起
34、多表合并
数据透视表向导,可以按Alt+D,然后再按P,切记不能一次性按!
参考:有多个表格,格式相同,但姓名人数不同,要快速将所有费用汇总
35、分段显示手机号码
=TEXE(B2,"000-0000-0000")
36、excel怎样快速修改全部字体
F5 定位常量 和 数字 再统一修改字体
37、图表突出显示最大值和最小值
在图表中,我们还可以突出显示最大值和最小值,如下图,我们对表格数据进行整理,建两列辅助列,分别输入公式:
=IF(B2=MAX($B$2:$B$11),B2,NA())和=IF(B2=MIN($B$2:$B$11),B2,NA())
,下拉填充。插入折线图,选中图形,点击鼠标右键选择“更改系列图表类型”,在组合里把最大值和最小值的图形更改为带数据标记的折线图,分别选中最大值和最小值系列,鼠标右键选择“添加数据标签”和“设置数据标签格式”,勾选“系列名称”。
38、图表以平均值为界限双色显示
如果我们要根据数据的平均值,把大于平均值和小于平均值的数据用不同颜色的图形来展示,如何做到?
这里我们需要对数据进行处理,添加两列辅助列,分别输入公式:
=IF(B2>AVERAGE($B$2:$B$11),B2,NA())=IF(B2<AVERAGE($B$2:$B$11),B2,NA())
下拉填充。选中C列和D列数据,插入柱形图。
39、选择性粘贴实现行列转换
复制后,Ctrl+Alt+v
打开选择性粘贴界面里面有 转置
选项
40、公司大事件时间轴 用Excel这么简单!
※ step1:添加辅助列
-
C列全部输入0
-
D输入正负交替的数字,数字大小要错开
※ step2:插入散点图
选取BC列插入 - 图表 - 散点图
※ step3:添加新系列
在图表上右键 - 选取数据 - 添加 :X轴选年份B列,Y轴数据选D列
※ step4:添加误差线
选取图表 - 设计 - 添加图表元素 - 误差线 - 基于系列2 - 其他误差选项 :
- 方向 - 负误差
- 误差量:100%
- 最后选取上面的横线按delete删除。
※ step5:添加文字
选取系列2 - 右键添加数据标签 - 分别选取标志(单独选中状态)逐个在编辑栏=引用B列的文字。(引用后数字变成对应的文字)
※ step6:美化图表
去掉图表上多余的部分(选取Y轴、网格钱按delete删除),对标签和Y轴进行美化
参考原文:公司大事件时间轴 用Excel这么简单!
41、所有图片自动编号
按Ctrl+H 打开查找替换窗口,查找:g,替换为:&pc
代码说明:
^g 代表图片
^& 代表查找的内容
^p 代表换行符(添加的编号在图片下一行显示)
^c 代表剪切版的内容
总结:查找图片,替换为 原来的图片 + 换行符 + 剪切版中的域代码
42、带复选框的选择求和的例子
如果你下载过老外做的Excel表格或模板,会发现表格中有很多控件。其中,用的最多的是复选框 ☑。
在费用项目前的方框内打勾后:
该行填充色和字体都会变色
该行D列的金额会汇总到总成本内(D7单元格)
制作步骤:
step1: 插入复选框
开发工具 - 插入 - 点击复选框后在表中拖画出来 - 删除复选框旁的文字
step2: 设置复选框链接的单元格
点击 复选框右键菜单中的设置控件格式,在弹出的窗口中输入单元格链接 B3
用同样的方法,分别在4~6行插入复选框,并分别链接B4、B5、B6单元格。当点击复选框后,在对应的B
列会输入TRUE,去掉勾选则输入FALSE。
step3: 设置选中行变色
选中B:D列数据 - 条件格式 - 新建规则,在弹出的【新建格式规则】窗口中进行如下设置:
公式框中输入公式 =$B3=TRUE
(注意$不可少)
点格式按钮,设置填充和字体颜色,本步设置完成后, B列方框画勾的行颜色发生了改变
step4: 隐藏B列的TRUE和FALSE
选取B3: B6 - 设置单元格格式 - 数字 - 自定义 - 在右侧代码框中输入 ;;; (3个分号)
step5: 设置总成本计算公式
在D7单元格中设置公式: =SUMPRODUCT(B3:B6*D3:D6)
完工!
原文参考:[口 内打 √,外企大公司都这么用.......](https://mp.weixin.qq.com/s?
__biz=MjM5NDYyNzAzNQ==&mid=2652913951&idx=1&sn=f99f7fccc5ccd7c9f8e01c5a0c85ad7c&chksm=bd50
0dab8a2784bd1e32d526866106a9be8d472b9c6900b23a74123befccd02aad9dae0e4ea2&mpshare=1&scene=1
&srcid=#rd)
43、Excel数据有效性
6个经典实例,带你重新认识Excel数据有效性
数据有效性(也叫数据验证)是我们使用频率最多的一个功能,比如下拉菜单的制作、限制性输入等,那你真的会用数据有效性吗?本期帮主通过6个经典案例与大家分享一下数据有效性的用法。
※ 下拉菜单的制作
下拉菜单是数据有效性最经典的用法,这里与大家分享一下相关的制作方法。
一级下拉菜单的制作:
选择区域,点击数据——数据验证——设置——验证条件——序列,之后选择来源区域即可。
注意:来源若是手动输入,要注意的是文本之间的分隔符是英文状态下的逗号。
二级下拉菜单的制作:
先选中区域,点击公式——定义名称——根据所选内容创建——勾选首行,之后建立一级菜单和二级菜单,做二级菜单时,来源写公式=INDIRECT(单元格地址)。
※ 在指定的区域输入数字
选中区域,打开验证对话框,在允许条件中选择整数,之后根据需要输入相应的区间。
※ 只允许输入数字
这时选择的条件是自定义,之后输入公式=ISNUMBER(B1)=TRUE。
※ 逐行输入内容
一样是选择自定义,在公式中输入=COUNTA(A$1:A13)=ROW(A1)
※ 输入唯一值
同理,选择自定义输入公式=COUNTIF(A:A,A1)=1
※ 输入当前时间
选中区域,之后按Ctrl+1打开设置单元格格式,然后选择自定义,输入数字格式H:MM:SS,点击确定。
这里需要在其他任意单元格输入公式=NOW()
完成以上操作之后,直接选中区域,打开数据验证,选择序列,来源设置=刚刚输入的公式的单元格。