前言：转眼工作已近10年。虽然没有什么大的成就，所幸从中学到了很多。其中我认为学到最有用的一部分就是Excel的使用。从最初的公式到后来的VBA，也是因为有了这些基础，在接触Python后开始使用pandas才会觉得如鱼得水吧。特借这篇文章，对过往所学进行归纳总结，方便自己以后查阅使用，如果有幸可以帮助有需要的人，那就算意外收获了。最近事情较多，所以决定分开写，先将快要写完的公式部分，整理输出，等有时间了，再整理VBA部分。 Q1：如何获得公式中指定部分的运算结果？1) 选中公式中希望展示结果的部分
2) F9
3) 展示选中部分的运算结
使用场景：使用嵌套公式进行复杂运算时，获取指定位置的运算结果Q2：如何快速固定引用单元格的行和列？1) 选中待编辑公式的单元格
2) 在编辑栏中选中待固定的单元格地址（如A1）
3) 按下‘F4’
4) 选中单元格变为：$A$1
使用场景：套用公式时，需要引用固定列（$A）或者引用固定行（$1）或者固定单元格（$A$1）Q3：通过主键在工作簿内或者本地不同工作簿间，获取或者同步数据的函数，VLOOKUP语法格式Vlookup（要查找的值，查找区域，要返回的结果再查找区域的第几列，精确匹配0-FALSE或者近似匹配1-TRUE）特殊说明1) 存在多个满足条件的值时，只返回当前排序第一位的值
2) 查找值的格式必须一致，转换方式：
a) 转列（目标列）
b) 设置单元格格式（条件值）
c) 公式内条件值连接空转换成文本：value&””
d) 公式内条件值连接空转换成数值：value^1或者--、+0、-0、*1、/1等等
3) 含有通配符~时，需要使用替换函数，将~替换成~~Q4：查找值在表格中的位置，MatchMatch（查找值，查找范围，匹配方式）
1) 查找范围为，某一行或者某一列
2) 范围为某一行时，返回相对列数；为某一列时，返回相对行数。
3) 匹配方式，0-精确匹配，1-小于，-1-大于
4) 返回在查找范围内的相对位置
5) 一般配合vlookup或者index使用Q5：查找位置对应的值，IndexIndex（查找范围，行数，列数）
在查找位置内，相对位置为（行数，列数）单元格，的值Q6：返回字符串地址对应的值，INDIRECTINDIRECT(A3)
A3,也可以使用字符拼接获得“A”&”3”Q7：替换字符，SubstituteSubstitute(A1,”~”,”~~”)
将A1单元格内的~替换成~~Q8：返回字符长度Len（A1）Q9：保留小数点后位数1) 四舍五入，保留小数点后2位
ROUND（A1，2）
2) 向下舍入，保留小数点后2位
ROUNDDOWN（A1,2）
3) 向上舍入，保留小数点后2位
ROUNDUP（A1,2）Q10：去重统计，sumproductsumproduct（1/countif（选定列范围，选定列范围））举例,统计A中非重复值的个数AC苗李苗李刘Ps:sumproduct
1) 相同维数的数组，元素进行相乘并求和
2) 将非数值型的数组元素作为 0 处理Q11：单条件计数，countifcountif（A:A,A2）
在A列中，A2的个数Q12：多条件计数，COUNTIFSCOUNTIFS(A:A,$C$1,B:B,$D$1)
条件1：在A:A范围内，值为$C$1
条件2：在B:B范围内，值为$D$1
结果：同时满足条件1、条件2的数据个数（行数）
Ps：条件可扩展，格式：范围，条件Q13：拼接多个单元格的值，PHONETICPHONETIC(C:C)
将C列的值，拼接在同一单元格内Q14：一年中第几周，WEEKNUM1) WEEKNUM（A2）, A2单元格内的日期处在一年中的第几周数
2) WEEKNUM（date（年，月，日）），使用date函数直接输入日期
3) 1或缺省，默认一周第一天为周日；
其他2-周一、11-周周一、12-周二、13-周三、14-周四、15-周五、16-周六、17-周日Q15：一周中的第几天，weekday1) weekday（A2）, A2单元格内的日期处在一周中的第几天
2) weekday（date（年，月，日）），使用date函数直接输入日期
3) 缺省或1（周日是1，其他依次递增），2（周一是1，其他依次递增）Q16：绝对值，ABS1) ABS（1-9），8
2) Abs（A2）,返回单元格A2值的绝对值Q17：is函数，检查指定值，根据结果返回，为真则返回TRUE，否则返回FALSE1)
ISERROR，值为错误（任意错误）
2)
Isnumber，值为数字
3)
ISBLANK, 值为空单元格
4)
ISERR， 值为#N/A以外的任意错误
5)
ISLOGICAL，值为逻辑值
6)
ISNA, 值为#N/A（值不存在）
7)
ISNONTEXT, 值为不是文本的任意项。（请注意，此函数在值为空单元格时返回
TRUE）。
8)
ISREF,值为引用
9)
ISTEXT，值为文本Q18：根据身份证号计算年龄获取第7位开始的8个数字，即为出生日期方法一： 使用：MID(text, start_num, num_chars)，截取文本，从start_num开始的，num_chars个字符
公式为：MID(身份证号,7,8)方法二： 使用：left和right两个函数组合
公式：RIGHT(LEFT(身份证号,14),8)，先用left获取身份证号左起第1~14位，
再用right函数从结果中取右起1~8位
or
LEFT(RIGHT(身份证号,12),8)，先用right获取身份证号右起第1~12位，
再用left函数从结果中取左起1~8位DATEDIF配合text和today函数，使用上一步获取的出生日期，计算年龄DATEDIF(TEXT(出生日期,"#-00-00"),TODAY(),"Y")
公式解析;
TEXT,将从身份证号中获取的出生日期，格式化成Excel识别的日期
TODAY，实时获取系统时间
DATEDIF，获取text格式化的时间和today获取的时间之间的差值，参数"Y"表示只获取时间差值的年Q19：根据身份证号判断男女（身份证号第17位，奇数-男，偶数-女）获取身份证号第17位方法一：
使用：MID(text, start_num, num_chars)，截取文本，从start_num开始的，num_chars个字符
公式为：MID(身份证号,17,1) 方法二：
使用：left和right两个函数组合
公式：RIGHT(LEFT(身份证号,17),1)，先用left获取身份证号左起第1~17位，
再用right函数从结果中取右起第1位
or
LEFT(RIGHT(身份证号,2),1)，先用right获取身份证号右起第1~2位，
再用left函数从结果中取左起第1位判断奇数偶数MOD（身份证号第17位，2）
公式解析：
身份证号第17位如为奇数，公式返回1
身份证号第17位如为偶数，公式返回0使用if函数返回男或者女if（MOD（身份证号第17位，2）=0，"女","男"）Q20：在存在合并单元格的列，自动填充序号1) 选中需要编号的区域
2) 编辑栏输入：=MAX($A$1:A1)+1
3) Ctrl+回车键Q21：根据排序结果取值 最大MAX（取值范围）正序取值large（A:A,2）
在A列中由大到小排序，取值排序位置第2的值最小MIN（取值范围）倒序取值small（A:A,2）
在A列中由小到大排序，取值排序位置第2的值Q22：合并单元格筛选1) 复制需要筛选的列
2) 选中需要筛选的列，ctrl+g定位条件选择空值
3) 定位选择的默认单元格内输入=上一单元格
4) Ctrl+enter
5) 复制列以数值形式重新粘贴Q23：引用某列作为下拉列表，并去除下拉列表内的空白行（即，数据验证或者数据有效性）假设要为A列的单元格设置下拉列表，B列为下拉列表的列表值，具体操作如下
1) 选中A列
2) 数据→数据验证→数据验证（V）...→设置
3) 允许（A），选择“序列”
4) 来源（S），输入：OFFSET($B$1，，，SUMPRODUCT(
N(LEN($B:$B)>0))，)
公式解析：
$B$1，为下拉列表值实际开始的单元格
$B:$B，为下拉列表值所在的范围，允许值之间存在空白行
5) 确定Q24：下拉列表存在多行时，通过模糊查询查找所需下拉值假设要为A列的单元格设置下拉列表，B列为下拉列表的列表值，具体操作如下
1) 选中A列
2) 数据→数据验证→数据验证（V）...→设置
3) 允许（A），选择“序列”
4) 来源（S），输入：OFFSET($b$1,MATCH("*"&a1&"*",$b:$b,0)-1,,COUNTIF($b:$b,"*"&a1&"*"),)
公式解析：
$b$1，为下拉列表值实际开始的单元格
$b:$b，为下拉列表值所在的范围，允许值之间存在空白行
a1,为使用下拉列表的单元格
5) 数据→数据验证→数据验证（V）...→出错警告—取消勾选，输入无效数据时显示出错警告
6) 确定
7) 使用时，直接点击三角会弹出全部列表值；
输入查询值（如，张三丰，输入张三或者三丰）后回车，点击三角返回筛选后包含查询项的下拉列表
ps：
仅支持简单的模糊查询，如，张三丰，输入“张三”或者“三丰”，不支持“张丰”
如果符合模糊值得实际值有多个，查询返回列表靠上的一个。应尽量提高模糊查询的精度Q25：如何使用变量拼接、引用指定sheetINDIRECT(B4&"!D:D")公式解析：
B4，为存储sheet名称的单元格
"!D:D"，为指定sheet内需要被使用的range区域 使用举例：COUNTIF(INDIRECT(B4&"!D:D"),J3)
公式解析：
在表‘B4’的D列，查找，值与J3单元格的值，相同的单元格个数Q26：随机生成1个汉字CHAR((INT(16+RAND()*38+160)*256)+INT(94*RAND())+160)Q27：将符合特定条件的行设定为指定格式判断是否含有指定字符countif（$B1,"*退货*"）公式解析：
B1中如果包含“退货”两个字，返回TRUE，否则返回FALSEisnumber（find（"退货",$B2））公式解析：
B2中如果包含“退货”两个字，返回TRUE，否则返回FALSE判断公式返回的逻辑值（TRUE、FALSE或者0、1）举例：隔行设置格式IF(MOD(ROW(),2)=1,TRUE)
公式解析：
通过row函数获得实际所在行的位置（数值），使用mod函数，对row函数返回的行数进行奇偶判断，
如果是奇数最终由if函数返回TRUE否则返回FALSE。根据判断结果，设定行的格式1) 选中需要设定格式的单元格范围
2) 开始→条件格式→新建规则
3) 选择规则类型：使用公式确定要设置格式的单元格
4) 为符合此公式的值设置格式：在输入框内输入 上文提到的用于判断的公式
5) 点击格式按钮，为符合条件的单元格预设格式
6) 确定Q28：条件限制输入举例：若A1单元格内的值为“否”，则B1单元格不允许输入，实现方式如下1) 选中单元格B1
2) 数据→数据验证（即数据有效性）→设置
3) 允许：自定义
4) 公式，输入框内输入：countif或者find函数，判断A1中是否为“否”，用法详见Q27
5) 参照Q27设定B1的条件格式：当A1的值为“否”时，B1的格式为白字、白色填充，实现录入不可见Q29：条件限制下拉选项场景：A列、B列均使用下拉菜单，且B列下拉菜单的值由A列所选值决定
即：A1下拉菜单的值为，a和b
当A1的值为a时，B1的下拉菜单的值为1和2
当A1的值为b时，B1的下拉菜单的值为3和4实现：1)
AA、AB，分别选中，需要作为下拉项的区域。AA1到AA3分别为a、1、2；AB1到AB3分别为，b、3、4
2)
功能栏：公式→定义名称
3)
名称分别设置为a,b
4)
引用位置为选中范围减去a,b所在的单元格。即，AA2：AA3、AB2：AB3
5)
选中B列
6)
功能栏：数据→数据验证→设置→允许：序列
7)
来源处，输入=INDIRECT($A1)
Ps，该处也可以使用if语句，判处输出不同INDIRECT()的值
8)
勾上，对有同样设置的所有其他单元格应用这些更改Q30：根据条件自动添加日期场景：当A2填入值时，A1内自动填入当天日期，且该日期不会自动更新实现：1） 开启迭代计算功能：
文件→选项→公式→勾选，启动迭代计算
2） 在A1填入公式：
=IF(A2="","",IF(A1="",today(),A1))

课程介绍课程目录用户评论课程介绍课程目录用户评论你将获得PowerQuery_基础应用PowerQuery_函数语法PowerQuery_M语言基础教学服务学习群答疑授课老师1V1答疑辅导WPS高级应用培训；Microsoft Office 应用专家；在线教育培训讲师；畅销书作者；精通Excel各领域的综合应用；平实接地气幽默的授课风格；辅导用心、细致。学习课程截止日期第八期: 2029年9月1日第七期: 2027年1月1日温馨提示请勿私下交易请勿在平台外交易。与机构和老师私下交易造成的任何损失及纠纷，腾讯课堂不承担任何责任听课说明1、电脑：访问腾讯课堂官网 ke.qq.com 查看我的课表或下载win/mac客户端听课2、手机/平板：下载腾讯课堂APP, 进入学习页面听课