- 博客(19)
- 收藏
- 关注
RSS订阅原创 两表差异对比，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
208
1
原创 带迷你图的透视表，简单上档次
最后适当调整一下切片器的位置，OK，现在只要单击选择切片器中的地区，不仅可以查看不同地区各客户上半年各月份的销售总额，而且还能借助柱形图，查看各月份的销售变化情况。调整透视表字段布局，将“客户名”和“趋势”拖动到行区域，将金额拖动到值区域，将日期拖动到列区域。选中透视表中1~6月份的数据区域，插入迷你图，图表类型选择柱形图，然后设置“高点”颜色为红色。然后增加趋势列的列宽，右键设置透视表属性，去掉“更新时调整列宽”的勾勾。依次单击【分析】→【切片器】，然后设置切片器的样式和切片器的显示列数。
2023-08-18 14:37:51
116
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
210
1
原创 拆分单元格中的姓名，哪一种是你的最爱？
公式的意思是先把各个单元格中的字符连接到一起，然后将每个间隔符“顿号”都替换成199个空格，这样就相当于拉大了各个姓名之间的间隔距离（和做核酸类似，越远越好~~）先使用CONCAT(B2:B9&"、")，把B2:B9单元格的内容连接到一起，每个单元格之间用逗号隔开，得到的结果作为待拆分的字符串。单击姓名列，选择【拆分列】→【按分隔符】，此时Excel会自动分析咱们的数据，并给出拆分建议。如果希望将数据拆分成一行显示，公式可以换成这样：。太长了，左右拖动滑块，可查看完整公式~~。本例中省略了最后两个参数。
2023-08-17 08:49:29
208
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
58
原创 多列数据 秒变两列 效率速速滴
2、然后单击“部门”所在列的列标，在【转换】选项卡下单击【填充】→【向下】。如果数据源中增加了数据，只要单击【数据】选项卡下的【全部刷新】按钮，就可以获得最新的拆分结果。A列是带有合并单元格的部门名称，右侧是各部门的人员姓名，每个部门的人数也没有规律。依次单击【数据】→【自文件】→【自工作簿】，打开数据查询编辑器。5、最后在【开始】选项卡下单击【关闭并上载】，OK。在导航器对话框中选中工作表名称，点击【编辑】。3、依次单击【逆透视列】→【逆透视其他列】。4、再单击“属性”所在列的列标，右键删除。
2023-08-14 15:12:13
348
原创 提取不重复记录，这个函数实在好用
如果你使用的是Excel 2019或者2021版本，可以使用UNIQUE函数从数据源中提取出不重复的记录，而且计算结果能随着数据源的变化自动更新。先使用UNIQUE函数提取出C$2:C$9单元格区域中的不重复记录，再判断去重后的成绩是否大于C2。首先使用FILTER函数，筛选出所有A区的值班经理名单，再使用UNIQUE函数提取出不重复的记录。UNIQUE函数第二参数省略参数，第三参数使用TRUE，表示在同一列中提取仅出现一次的值。如下图所示，希望从B列的值班名单中提取出仅出现一次的记录。
2023-08-11 11:29:02
414
1
原创 修剪平均，这个函数实在好用！
打分过程中，有两名裁判的打分设备出了故障，最终的打分变成了0。举个例子，下图是某体育比赛的打分表，一个选手由多名裁判同时打分，去掉一个最高分，再去掉一个最低分，然后计算出的平均数就是最终得分。第一个参数是要计算平均值的数据区域，第二参数是要排除的数据点个数。先使用IF函数判断一下，如果B2:H2单元格区域中的分数大于0，就返回原有分数，否则返回一个空文本。TRIMMEAN的第二参数，这里不能再使用2/7了，需要设置成2/大于0的单元格个数。这部分咱们先留着，一会儿用作TRIMMEAN的第一参数。
2023-08-10 18:16:42
274
1
原创 VLOOKUP的大家庭
由于XLOOKUP函数的查询区域和返回区域是分开的两个参数，这样就不用考虑查询的方向问题了，不仅能实现从左到右，还能从右到左、从下到上、从上到下等任意方向的查询。这个公式里的第三参数选择了多列的范围，由于Office 365中的公式有溢出功能，所以只要输入一个公式，就可以返回B~D列的多项信息。公式表示在1:3这个区域的首行中查找指定内容“池海东”，并返回该区域第三行的信息，第四参数使用FALSE，也表示使用精确匹配的方式。如下图所示，要根据G1的部门，在B列查询该部门，并返回A列对应的负责人姓名。
2023-08-09 17:48:18
50
原创 逆向查询其实很简单
最后使用LOOKUP函数，以1作为查找值，在以上内存数组中查找到0的位置，并返回要返回内容区域中与之位置相对应的信息。先使用MATCH函数在一行或一列的区域中找到查询值的位置，再使用INDEX函数，返回对应位置的内容。如下图，希望根据E2单元格指定的客户名，从左侧的数据表中查询对应的客户等级。所谓逆向查询，就是关键字在数据表的右侧，而要得到内容在数据表的左侧。=INDEX(要返回内容的区域,MATCH(要找谁,查找区域,0))=LOOKUP(1,0/(查找区域=查找内容),要返回内容的区域)
2023-08-08 16:00:37
803
原创 常用舍入函数公式
如下图，需要将A2单元格中的时间以半小时为单位四舍五入，不足15分钟的按0记录，超过15分钟不足半小时的按半小时。如下图，需要根据A2和B2单元格的加班起止时间，计算加班时长，不足半小时的部分不计算。如下图，需要根据A2和B2单元格的通话起止时间，计算通话时长，不足1分钟的按1分钟。如下图所示，使用=ROUND(A2,-1)，则可以将A2中的数值四舍五入到十位数。如下图所示，使用=ROUND(A2,2)，能够将A2中的数值四舍五入到两位小数。作用是抹零的，只保留数值的整数部分，小数位后的统统不要。
2023-08-08 15:54:26
109
原创 计算频次最拿手，收藏备用
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
738
原创 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
73
1
原创 超简单的动态图表，一学就会
HLOOKUP函数的作用是根据指定的查询内容（本例为J2），在数据区域（本例为$B$2:$G$7）的首行内找到查询值，并根据第三参数指定的值（本例为ROW(A2)生成的递增序号）返回不同行的内容，也就是从上向下查询数据。选中B2:B7单元格区域，然后单击格式刷，将格式复制到J列。选中A列的销售部门区域，复制后粘贴到右侧空白区域，如I列。在J3单元格输入以下公式，向下复制到J7单元格。调整一下图表比例，选择一种内置的图表样式。接下来咱们就说说具体的操作步骤。单击I2单元格，插入柱形图。
2023-08-05 09:38:58
99
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
164
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
1033
1
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人