【Office知识】日常常用Excel操作记录

黑就黑到底

已于 2022-05-28 23:18:46 修改

阅读量1.2k

点赞数

分类专栏： Office 文章标签： Office Excel

于 2022-04-19 00:34:41 首次发布

本文链接：https://blog.csdn.net/u013430110/article/details/124219596

版权

1 篇文章 0 订阅

订阅专栏

一、前言

Microsoft Excel是Office套件中最通用和最有用的程序之一，无论你是需要Excel来设定下一财年的预算、跟踪公司库存、规划就餐还是创建梦幻足球选秀单，甚至每周家务日程安排或家庭待办事项列表都不在话下。——摘自Microsoft官网介绍

于是，不论我做实施、开发还是维护，结果我发现永远都逃脱不了Office的使用，尤其是Excel表格的使用，本来以为学点Python会不再需要它了，可惜……我还是太天真了，哈哈~~

是否为空单元格：
=IF(ISBLANK(A1),"是空单元格","单元格内有内容")
判断是否是数字：
=IF(ISNUMBER(A1),"是数字","不是数字")
判断是否包含数字【通过Ctrl+Shift+Enter将其转为数组公式方可见效】：
=IF(COUNT(FIND(CHAR(ROW($48:$57)),A1))>=1,"是","单元格内没有数字")
判断是否包含大小写字母【通过Ctrl+Shift+Enter将其转为数组公式方可见效】：
=IF(COUNT(FIND(CHAR(ROW($65:$122)),A1))>=1,"是","单元格内没有大小写字母")

XLOOKUP函数：Office 2021中新增的逐行查找表或区域中的单元格值。使用这个函数可以在一列中查找搜索词，并从另一列中的同一行返回结果，而不管返回列位于哪一边，可以看作是HLOOKUP和VLOOKUP两个函数的合并升级版。

=XLOOKUP(A1, B:B,C:C,"未找到符合条件的值",0) ：从B列中查找A1的值，并获取符合条件的同一行的C列对应行的单元格值，如果找不到有效的匹配项，则显示“未找到符合条件的值”，0则是完全匹配(默认为0可忽略不写)。

指定两个数之间的随机数（保留两位小数）：
=ROUND(RAND()*(B1-A1)+A1,2)
=RANDBETWEEN((A1*100),(B1*100))/100
累加A列中所有数字（即排除非数字）：
=SUMIF(A:A,"<9E+307")

Office 2021新增了SUMIFS、COUNTIFS以及AVERAGEIFS等计算同时判断的函数。

提取单元格左侧数字：
=LEFT(A1,2*LEN(A1)-LENB(A1))
提取单元格右侧数字：
=RIGHT(A1,2*LEN(A1)-LENB(A1))
提取单元格内字母或数字（不支持混合隔开的内容）：
=MIDB(A1,SEARCHB("?",A1),2*LEN(A1)-LENB(A1))
提取单元格内所有数字【通过Ctrl+Shift+Enter将其转为数组公式方可见效】（缺陷：会忽略小数点及负号等所有字符）：
=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$1024),1))*ROW($1:$1024),ROW($1:$308))+1,1)*10^ROW($1:$308)/10)

从Excel 2013开始，新增了一个快速填充快捷键：Ctrl+E！Excel 2019中更是将其优化完善了下，通过它可以一键快速按照已手动提取的模糊规则提取出单元格内容，因此也并不能100%完美处理所有的内容，具体参见官网：使用Excel中的“快速填充”

借助序列下拉选则实现条件规则动态改变底色，需要先将目标单元格设置为下拉序列：选中某列单元格数据，点击数据 → 数据验证 → 数据验证后，在设置中允许选择序列选项，来源可以选择一列非重复数据的单元格，也可以手动使用“,”间隔数据，例如：“选项一,选项二,选项三”，如下图：
选中已设置了数据验证的单元格，点击开始 → 条件格式 → 新建规则并选择使用公式确定要设置格式的单元格这条规则，如下图：
如上图输入=IF($A1="选项一",TRUE,FALSE)公式，即当第一步的下拉选择框选择选项一时，需要设置的格式，点击确定后，该规则会默认应用于当前所选单元格；
需要应用于指定范围单元格，请选择条件格式下的管理规则菜单，如下图：
默认应用于当前单元格，即$A$1单元格，此时可手动选择指定范围单元格，或者手动输入指定范围单元格，比如输入=$A$1:$G$1范围，即当A1单元格选择选项一时第一行从A到G底色变成红色，如下图：