- 博客(34)
- 收藏
- 关注
RSS订阅原创 EXCEL跨固定列数求和，你不得不学！
SUMPRODUCT((MOD(COLUMN(求和区域),n+1)=0)*求和区域)=SUMPRODUCT((MOD(COLUMN(求和区域),n+1)=0)*求和区域)=SUMPRODUCT((MOD(COLUMN(求和区域),2)=0)*求和区域)=SUMPRODUCT((MOD(COLUMN(求和区域),3)=0)*求和区域)=SUMPRODUCT((MOD(COLUMN(求和区域),4)=0)*求和区域)其中，求和区域就是我们要实际进行求和的数据区域，n+1中的n就是要隔几列求和。
2025-08-25 16:09:56
862
原创 Vlookup函数最经典的12种用法
公式说明：Vlookup也可以使用IF({1,0}的数组形式实现多条件查找，但运行效率太慢，所以建议使用辅助列连接再查找的方法。在公式中有通配符用法，遇到被查找的字符串中含有这3个字符就用Substitute替换为~~、~*和~？： 这里把H列日期&第1行的数字作为查找内容，在A列中进行查找，然后返回 B列 & C列的数据，注：Colum(b1)的结果是2，当公式向右复制时可以生成3，4，5，..公式说明：用Match函数查找表1的标题在表2中的位置。公式说明：根据序号从工资表中查找对应的信息。
2025-08-01 14:36:23
3288
原创 几个简单高效的Excel函数公式
本例中MATCH函数省略了第三参数，其计算规则与使用参数1时相同，当查找不到对应的内容时，会以小于查找值的最接近的一个进行匹配，并返回对应的位置信息。MATCH函数在常量数组{0,4,7,10}中找不到5，因此以小于5的最接近值4进行匹配，并返回4在常量数组{0,4,7,10}中的位置，结果为2。公式的意思就是在B列单元格区域中查找D2单元格的姓名，并返回A列单元格区域中与之对应的部门。XLOOKUP函数的查询区域和返回区域是分开的两个参数，使用时不需要考虑查询的方向问题，可以实现任意方向的数据查询。
2025-07-31 15:27:43
508
原创 Excel双向查找函数公式大全
注：利用=号对比把符合条件的变成True，不符合条件的变为false，再用（行数据）*（列数据）转换成N行N列的数组并把不符合条件的全部变成0，而符合条件的会变成1，然后再*值区域把1转换成符合条件的数值，最后用sumprouct提取值。注：用match函数根据给出的姓名和月份查找行、列数，然后用address（行数,列数,绝对引用方式,是否为r1c1,表格名）组合成引用地址，然后用indirect(单元格地址）函数提取值。注：原理同公式6，只是这里用了sum函数提取唯一符合条件（大于0）的值。
2025-07-28 14:54:15
1088
原创 用Vlookup函数完成数字的提取
首先，用mid函数+Row函数从第1个字符开始，截20次并得到20个结果（如果字符串比较长，可以用一个足够大的数字替代20，比如99），其中总有一个是11位的纯数字。本例也只是用了Vlookup的最基本查找用法，难的是很多人想不到这样使用。公式中值得我们学习还有*{}一列变两列的技巧，非常的巧妙。Vlookup函数常用于查找和核对，可今天就介绍的Vlookup用途，你可能真的没见过。【例】如下图所示，要求从A列的地址中提取出手机号码，如B列所示。这样，就可以用0查找出第2列的电话号码了。
2025-07-23 14:42:20
483
原创 Vlookup函数如何才能忽略中/英文符号进行查找？
【问题】如下图所示，在用VLOOKKUP查找时出错了，原因是A列输入的是中文括号“（ ）”，而D列却是英文括号“( )”。有一个冷门的Excel函数恰好可以解决这个问题：ASC函数，它可以把双字节转换为单字节。还有一种情况，如果A列和D列都有中英文的符号, 就需要lookup函数出马了。公式原理：把A列和D列的值都转换成单字节的，查找就可以正常进行了。问：Vlookup函数如何才能忽略中/英文符号进行查找？公式中添加asc后,Vlookup就查找正常了。
2025-07-21 08:51:01
453
原创 Excel查找函数中的No.1——Filter函数
Filter函数的第2个参数，借助其他函数可以设置复杂的条件。这也让filter函数功能变得无比强大。
2025-07-21 08:48:32
473
原创 简单实用的EXCEL公式
第一参数是开始日期，第二参数是结束日期，第三参数使用 y，表示间隔类型为年。如下图所示，B列是员工出生年月，需要以2022年11月14为节点，计算员工年龄。首先使用MATCH函数，查询F2中的姓名在B列所处的相对位置，结果为5。如下图所示，要根据F2单元格的部门名称和G2单元格的职务，查询对应的姓名。如下图所示，希望根据F2单元格中的姓名，在左侧的数据表中查询对应的工号。格式代码中的m，表示日期中的月份，[DBnum1]表示中文数字。如下图所示 需要将B列的日期格式转换为中文格式的月份。
2025-07-18 08:31:42
480
原创 vlookup+Indirect轻松实现跨表查询（适用于统计每天或者每月及其他多表查询）
单表查询是VLOOKUP函数最常用的查询查询方式。今天来介绍VLOOKUP函数跨多表批量查询。跨多表：查找对象在多个表中都存在例如：汇总每天每个供应商的质量情况。如下图每个子表中，都有多个供应商，每个供应商都对应有不良率。如下图用常规的VLOOKUP函数查询，公式如下图：那么公式中的7月11日，需要更改很多次。比如7月份有31天，要查询31天的数据，就需要手动修改31次，如下图（简单随即举例要查询的两天的公式）给实际应用带来极大的不便。
2025-07-17 09:23:13
1394
原创 XLOOKUP双重条件查询
2、XLOOKUP(R$2&$Q3,$C:$C&$D:$D,...,"",0)：将日期（R$2）与名字（$Q$3）使用连接符连接起来，组合成查找值，在查找数组C:C&D:D中进行查找，找到则返回对应的值，反之，则返回空值（“”）。查找匹配张三对应的出勤天数，可以在R3输入下面的函数公式：=XLOOKUP(R$2&$Q3,$C:$C&$D:$D,INDEX($I:$I,,MATCH($I$1,$I$1:$I$1)),"",0)XLOOKUP 函数用于从一个数组中查找值，并从另一个数组中返回对应的值。
2025-07-16 10:10:56
825
原创 Excel一对多查询，典型解法合集
备注：想数据横向输出；=TOROW(IF(B2:B16=D2,A2:A16,x),3)D3单元格输入以下公式，按按Ctrl+Shift+回车，向下复制到出现空白为止。小伙伴们好啊，今天咱们说说Excel一对多查询的典型解法。D3单元格输入以下公式，按Ctrl+Shift+回车。如下图所示，希望返回销售部的全部人员姓名。首先在C2单元格输入以下公式，向下复制。
2025-07-12 11:09:56
410
原创 按条件求和，六种方法随你选
第一参数是求和区域，后面的参数两两一对，分别是对应的求和区域和求和条件。然后再使用这个内存数组与D2:D9中的数值对应相乘，如果内存数组中的某个元素是TRUE，与D列相乘后仍然是D列的原有数值，否则相乘后是0。SUMIF的作用是根据指定的条件来求和，第一参数是条件区域，第二参数是指定的条件，第三参数是求和区域。第二参数用于指定要返回哪一列的数据，可以是与数据库区域中相同的列标题，也可以用数字来表示第几列。如果A2:A9单元格中的品类等于G2单元格的内容，就对D2:D9区域中对应的单元格求和。
2025-07-12 10:58:21
454
原创 二分钟，做一个简单漂亮的Excel条形图
单击任意一个数据标签，设置数据标签格式，在标签选项中去掉“值”的勾选。然后选中“单元格中的值”，在弹出的对话框中选择B2:B7单元格区域的完成率。在这个图表中，深色部分表示完成率，浅色部分表示任务剩余，数据标签显示在最右侧。D列的辅助列用来添加数据标签，直接输入一个比较小的数值，比如5%，就可以了。接下来将“辅助1”系列设置成浅一些的颜色，将“辅助2”系列设置成无填充。再来调整一下数据标签位置，设置为轴内侧。右键单击“辅助2”系列，添加数据标签。同样的方法，删除水平轴标签和网格线。首先需要建立两个辅助列。
2025-07-12 10:49:32
389
原创 Excel控件动态图表，简单又好看
三，选中其中一个数据系列，设置数据标记为淡黄色。再依次单击其他数据系列，按F4键，数据点全部设置为淡黄色。这些数据是不同部门、不同月份的销售情况，要以此数据制作图表来展示各部门的业绩变动。十、将控件拖动到图表右上角，按住Ctrl键不放 ，依次点击控件和图表，右键→组合。十一、选中图表标题，在编辑栏内输入等号 =，然后单击A13单元格，再按回车。二、选中图表中的任意一个数据系列，设置形状轮廓颜色为淡黄色。六、选中图表，添加数据系列，把刚刚定义的名称添加到图表里。最后拖动图表标题的位置，设置一下图表字体。
2025-07-12 10:19:02
236
原创 EXCEL统计多行数据时，去除空值后数据前移
数字转为为大写（文章中的第一步）和表格中数据统一除以某个值（文章中的第三步）是举例说明时遇到的问题，也顺手写出来了。在实际使用过程中，需要删除空值，后面的数值前移（文章中的第二步），才是今天写这篇文章的重点。
2025-07-12 10:02:09
427
原创 两表差异对比，TEXT函数少不了
A:G,6,)-VLOOKUP(A3,'3月'!A:G,6,),"比上月多0元;因为职工的ID是唯一的，所以，咱们可以使用这个ID作为查找值，分别从3月和4月两个工作表中找到对应的奖金，然后相减即可得出差异。A:G,6,)-VLOOKUP(A3,'3月'!咱们要对比的是两个月的工资表，看看不同员工两个月的奖金有什么变化。但是，这样的结果还不够直观，如何显示成更加直观的文字描述的结果呢？如果两个VLOOKUP相减大于0，就显示为“比上月多N元”。格式代码里的0有特殊的含义，表示要处理的数值本身。
2023-08-21 10:42:27
403
1
原创 带迷你图的透视表，简单上档次
最后适当调整一下切片器的位置，OK，现在只要单击选择切片器中的地区，不仅可以查看不同地区各客户上半年各月份的销售总额，而且还能借助柱形图，查看各月份的销售变化情况。调整透视表字段布局，将“客户名”和“趋势”拖动到行区域，将金额拖动到值区域，将日期拖动到列区域。选中透视表中1~6月份的数据区域，插入迷你图，图表类型选择柱形图，然后设置“高点”颜色为红色。然后增加趋势列的列宽，右键设置透视表属性，去掉“更新时调整列宽”的勾勾。依次单击【分析】→【切片器】，然后设置切片器的样式和切片器的显示列数。
2023-08-18 14:37:51
272
1
原创 动态下拉菜单，学会不蒙圈
如下图所示，A、B列是客户城市和县区的对照表，在D列已经生成一级下拉菜单，要求在E列生成二级下拉菜单，要求能随着D列所选不同的一级菜单，E列下拉菜单中的内容也会自动调整。OFFSET以A2作为基点，向下偏移0行，向右偏移0列，新引用的行数为COUNTA函数统计到的A列非空单元格个数，结果-1，是因为A1是表头，计数要去掉。如下图所示，要根据A列的对照表，在D列生成下拉菜单，要求能随着A列数据的增减，下拉菜单中的内容也会自动调整。选中要输入内容的D2:D10单元格区域，数据→数据验证→序列，输入以下公式。
2023-08-17 08:52:23
387
1
原创 拆分单元格中的姓名，哪一种是你的最爱？
公式的意思是先把各个单元格中的字符连接到一起，然后将每个间隔符“顿号”都替换成199个空格，这样就相当于拉大了各个姓名之间的间隔距离（和做核酸类似，越远越好~~）先使用CONCAT(B2:B9&"、")，把B2:B9单元格的内容连接到一起，每个单元格之间用逗号隔开，得到的结果作为待拆分的字符串。单击姓名列，选择【拆分列】→【按分隔符】，此时Excel会自动分析咱们的数据，并给出拆分建议。如果希望将数据拆分成一行显示，公式可以换成这样：。太长了，左右拖动滑块，可查看完整公式~~。本例中省略了最后两个参数。
2023-08-17 08:49:29
379
1
原创 TEXT函数用的好，年终发奖少不了
这个时候，已经有了日期的模样，但是本身还是文本型的，所以再加上两个负号，也就是计算负数的负数，通过这么一折腾，就变成真正的日期序列了。首先使用TEXT函数，把B列的日期变成具有特定样式的字符串，然后再与A列的姓名连接，就变成了最终需要的样式。格式代码“m”用于提取B2单元格中的月份，再使用格式代码[DBnum1]将其转换为中文小写数字格式。再使用TEXT函数，将这个字符串变成"0-00-00"的样式，结果为"1975-12-26"。如果C2-B2的结果小于0，就显示“比上年少n元”。
2023-08-14 15:17:24
169
原创 多列数据 秒变两列 效率速速滴
2、然后单击“部门”所在列的列标，在【转换】选项卡下单击【填充】→【向下】。如果数据源中增加了数据，只要单击【数据】选项卡下的【全部刷新】按钮，就可以获得最新的拆分结果。A列是带有合并单元格的部门名称，右侧是各部门的人员姓名，每个部门的人数也没有规律。依次单击【数据】→【自文件】→【自工作簿】，打开数据查询编辑器。5、最后在【开始】选项卡下单击【关闭并上载】，OK。在导航器对话框中选中工作表名称，点击【编辑】。3、依次单击【逆透视列】→【逆透视其他列】。4、再单击“属性”所在列的列标，右键删除。
2023-08-14 15:12:13
854
原创 提取不重复记录，这个函数实在好用
如果你使用的是Excel 2019或者2021版本，可以使用UNIQUE函数从数据源中提取出不重复的记录，而且计算结果能随着数据源的变化自动更新。先使用UNIQUE函数提取出C$2:C$9单元格区域中的不重复记录，再判断去重后的成绩是否大于C2。首先使用FILTER函数，筛选出所有A区的值班经理名单，再使用UNIQUE函数提取出不重复的记录。UNIQUE函数第二参数省略参数，第三参数使用TRUE，表示在同一列中提取仅出现一次的值。如下图所示，希望从B列的值班名单中提取出仅出现一次的记录。
2023-08-11 11:29:02
1054
1
原创 修剪平均，这个函数实在好用！
打分过程中，有两名裁判的打分设备出了故障，最终的打分变成了0。举个例子，下图是某体育比赛的打分表，一个选手由多名裁判同时打分，去掉一个最高分，再去掉一个最低分，然后计算出的平均数就是最终得分。第一个参数是要计算平均值的数据区域，第二参数是要排除的数据点个数。先使用IF函数判断一下，如果B2:H2单元格区域中的分数大于0，就返回原有分数，否则返回一个空文本。TRIMMEAN的第二参数，这里不能再使用2/7了，需要设置成2/大于0的单元格个数。这部分咱们先留着，一会儿用作TRIMMEAN的第一参数。
2023-08-10 18:16:42
558
1
原创 VLOOKUP的大家庭
由于XLOOKUP函数的查询区域和返回区域是分开的两个参数，这样就不用考虑查询的方向问题了，不仅能实现从左到右，还能从右到左、从下到上、从上到下等任意方向的查询。这个公式里的第三参数选择了多列的范围，由于Office 365中的公式有溢出功能，所以只要输入一个公式，就可以返回B~D列的多项信息。公式表示在1:3这个区域的首行中查找指定内容“池海东”，并返回该区域第三行的信息，第四参数使用FALSE，也表示使用精确匹配的方式。如下图所示，要根据G1的部门，在B列查询该部门，并返回A列对应的负责人姓名。
2023-08-09 17:48:18
157
原创 逆向查询其实很简单
最后使用LOOKUP函数，以1作为查找值，在以上内存数组中查找到0的位置，并返回要返回内容区域中与之位置相对应的信息。先使用MATCH函数在一行或一列的区域中找到查询值的位置，再使用INDEX函数，返回对应位置的内容。如下图，希望根据E2单元格指定的客户名，从左侧的数据表中查询对应的客户等级。所谓逆向查询，就是关键字在数据表的右侧，而要得到内容在数据表的左侧。=INDEX(要返回内容的区域,MATCH(要找谁,查找区域,0))=LOOKUP(1,0/(查找区域=查找内容),要返回内容的区域)
2023-08-08 16:00:37
965
原创 常用舍入函数公式
如下图，需要将A2单元格中的时间以半小时为单位四舍五入，不足15分钟的按0记录，超过15分钟不足半小时的按半小时。如下图，需要根据A2和B2单元格的加班起止时间，计算加班时长，不足半小时的部分不计算。如下图，需要根据A2和B2单元格的通话起止时间，计算通话时长，不足1分钟的按1分钟。如下图所示，使用=ROUND(A2,-1)，则可以将A2中的数值四舍五入到十位数。如下图所示，使用=ROUND(A2,2)，能够将A2中的数值四舍五入到两位小数。作用是抹零的，只保留数值的整数部分，小数位后的统统不要。
2023-08-08 15:54:26
265
原创 计算频次最拿手，收藏备用
6}为指定间隔值，统计签到对应的列号{3;最终返回的数组中的元素会比间隔值的元素多一个，多出来的这个表示最高区间之上的数值个数。然后使用IF函数进行判断，如果FREQUENCY的计算结果大于0，就返回B2:B10单元格区域中对应的数值，否则返回逻辑值FALSE。FREQUENCY函数还有一个特点，如果指定间隔中的数值有重复，会仅统计第一次出现时的间隔，后面重复出现的间隔值，计算结果会显示为0。也就是99及以上的有4个，97~99以下的有1个，95~97以下的有1个，85~95以下的有1个……
2023-08-08 15:50:48
1215
原创 SUMIF函数的另类用法，值得收藏
因为我们要求是对每种商品的最后一季销售数量计算平均值，首先就要计算出B3:E6区域中每一列的最后一个值。如果B3:E6单元格区域中满足等于空值的条件，就去计算与空值对应的上一行的和，这样就变相的得到了B2:E6区域中每一列最后一个值的和。本例中的SUMIF函数第二参数使用了多个单元格，计算在条件区域A$8:A$11中分别符合条件B2:D2的、对应的B$8:B$11的值。第3参数是B2:E5，注意这里的引用区域和第一参数的条件区域形成了一个错行的效果。SUMIF函数常用于对区域中符合指定的单个条件的值求和。
2023-08-07 09:02:00
198
1
原创 超简单的动态图表，一学就会
HLOOKUP函数的作用是根据指定的查询内容（本例为J2），在数据区域（本例为$B$2:$G$7）的首行内找到查询值，并根据第三参数指定的值（本例为ROW(A2)生成的递增序号）返回不同行的内容，也就是从上向下查询数据。选中B2:B7单元格区域，然后单击格式刷，将格式复制到J列。选中A列的销售部门区域，复制后粘贴到右侧空白区域，如I列。在J3单元格输入以下公式，向下复制到J7单元格。调整一下图表比例，选择一种内置的图表样式。接下来咱们就说说具体的操作步骤。单击I2单元格，插入柱形图。
2023-08-05 09:38:58
176
1
原创 筛选状态下的计算
接下来使用SUBTOTAL函数，第一参数使用3，即依次统计E4~E16每个单元格中的可见单元格个数，如果单元格处于显示状态，则对这个单元格的统计结果为1，否则统计结果为0。注意，注意，这个公式如果换成从=SUBTOTAL(3,E$2:E2)，也就是从公式所在行开始的话，序号结果虽然没问题，但是筛选时最后一行会被Excel当成汇总行而始终显示。第二参数使用一个动态扩展的范围E$1:E2，随着公式的下拉，这个范围会依次变成E$1:E3，E$1:E4，E$1:E5，……
2023-08-02 08:55:59
380
1
原创 COUNTIF函数的典型应用
COUNTIF函数的第一参数为“C$1:C2”，其中的“C$1”部分锁定了行号，“C2”部分未锁定行号。当公式学习复制时，会依次变成“C$1:C3”、“C$1:C4”、“C$1:C5”……COUNTIF函数在计算文本型数字时，会默认按数值型进行处理，但是Excel中的数字精度只有15位，并且身份证号码是18位，因此会把前15位相同的身份证全部识别为相同的内容。先使用COUNTIF(F2:F5,B2:B17)，统计B2:B17单元格区域中的姓名，在F列经理亲戚列表中出现的次数，结果返回1或者0。
2023-08-01 16:25:11
1665
1
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人