excel函数 - 宝哥软件园

excel函数

频率功能段计数
办公
EXCEL做数据分析的时候，经常会遇到分段统计数量的问题。今天以学生成绩分析为例，来述如何使用FREQUENCY函数简单方便的统计各分数段的人数。示例数据如下图：关键操作函数实现选中E2:E6单元格区域，输入公式： “=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”，

2021-09-17 20:16:35【办公】
立即下载
你能使用逻辑函数如IF OR AND吗？3354以闰年为例
办公
关键操作提示：判断Ａ列年份是否为闰年,公式为： =IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0),'闰年','平年') 判断当年年份是否为闰年，如果返回值是TRUE或者FLASE,公式为： =OR(AND(MOD(YEAR(TODAY())

2021-09-17 20:16:28【办公】
立即下载
IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可以处理评级
办公
等级评定是EXCEL数据管理经常遇到的一种数据分析方法，一般是给出不同等级评定的标准数据区间，根据某项数据，批量生成每行的等级。以下面一组数据为例，给大家总结一下等级评定的五种常的方法：一般，在比较正规的表格中，不提倡添加辅助列，那就不加辅助列，直接写结果。视频解关键操作IF函数在C2单元格输入

2021-09-17 20:16:22【办公】
立即下载
INDEX RANDBENT rand函数的随机分组
办公
问题来源某公司对应聘面试者进行分组，为避免人为因素干扰，实行随机分组。今天给大家两种随机分组的方法。关键步骤提示第一种方法：INDEX+RANDBETWEEN函数在B2单元格输入公式“=INDEX({'第一组','第二组','第三组'},RANDBETWEEN(1,3))” INDEX函数：语

2021-09-17 20:16:16【办公】
立即下载
索引匹配搜索上帝组合(3) ——提取行和列的交集值
办公
上篇讲述的INDEX+MATCH，查找神组合（二），只是完成了查找特定值对应的姓名，其实，还没有触及到一对神组合的精髓——提取行与列交叉点的值！如下样表：查找指定姓名指定产品的销售量，即是查找“姓名”所在行与“产品”所在列交叉点的数值。这才是INDEX+MATCH组合的精髓。公式实现 C13输入公式：

2021-09-17 20:15:54【办公】
立即下载
索引匹配搜索上帝组合(4) ——提取整行整列
办公
INDEX+MATCH神组合，还可以提取整行整列数据，比如以下样表：查找指定姓名所有产品的销量、查找所有姓名指定产品的销量，都可以用这一组合来完成。公式实现 1、查找指定姓名所有产品的销量选中B13:E13区域，输入公式： =INDEX(B2:E10,MATCH(A13,A2:A10,0),0)

2021-09-17 20:15:46【办公】
立即下载
INDEX SMALL完成大动作:符合条件的数据自动“运行”到其他工作表
办公
问题来源学习群里，有朋友提出的问题：如下两个数据表，一个是“全部”工作表，一个是“已对”（已经核对）工作表，如何使得“全部”工作中，每核对一条，数据会自动从“全部”工作表“自动”跑到“已对”工作表，即：结果如下动图：公式实现在A2单元格输入公式： =INDEX(全部!A:A,SMALL(I

2021-09-17 20:15:31【办公】
立即下载
索引匹配寻找上帝组合(2)
办公
之前INDEX+MATCH,查找神组合（一），内容仅涉及到了INDEX+MATCH的简单组合。其实真正的数据处理中，远非这么简单。比如，如下数据样表：查找销售业绩最高的姓名，或者销售业绩前三的姓名，就需要对MATCH函数的参数稍加变化。查找业绩最高姓名 D2单元格输入公式： =INDEX(A2:A1

2021-09-17 20:04:33【办公】
立即下载
INDEX、VLOOKUP、HLOOKUP、LOOKUP、查询功能可选
办公
数据查询是EXCEL数据处理的重要方面。EXCEL提供了很多数据查询函数，今天来给大家分享四个经常用于查找“行列交叉处”数据的函数。关键步骤提示第一个函数：INDEX函数语法：INDEX(array,row_num, [column_num]) 中文语法：INDEX(单元格区域或数组常量,数组中的某行,[

2021-09-17 20:04:12【办公】
立即下载
索引匹配寻找上帝组合(一)
办公
问题来源原来，过很多VLOOKUP查询函数的使用。 VLOOKUP查询有两个不便之处： 1、查阅值（第一个参数）始终位于查找区域（第二个参数）的第一列； 2、从左向右查找容易，但从右向左查找需要IF或CHOOSE构建新的查找区域。今天，来述另一功能更强大的组合： INDEX+MATCH，这一组合可以不受

2021-09-17 20:02:46【办公】
立即下载
间接地址找到每个订单的订单模型
办公
问题情境如下样表：每个订单号产生订货量的型号规格不同，需要把每个订单号订货的型号规格填写到G列对应型号列。例如：第一个单号，有订货量的型号规格为1-2#，要求把1-2#写入G4单元格。公式实现在G4单元格输入公式： =INDIRECT(ADDRESS(2,MATCH(MAX(C4:F4),4:

2021-09-17 20:02:20【办公】
立即下载
你知道间接函数有多少妙用？
办公
函数介绍功能返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。语法 INDIRECT(ref_text, [a1]) 参数 Ref_text 必需。对单元格的引用。 a1 可选。一个逻辑值，用于指定包含在单元格 ref_text 中的引用的类型。如果 a1为 TRUE或省略，

2021-09-17 20:01:42【办公】
立即下载
间接函数将每个仓库的总额汇总到销售汇总表中
办公
问题情境如下样表：分仓库数据表中，既有销售额明细，又有合计。要求：只汇总各仓库的合计到“销售合计”表中。公式实现在销售合计”表B2单元格输入公式： =MAX(INDIRECT(A2&'!B:B')) 确定，并向下填充，即可汇总各各仓库的合计。如下图：公式解析 A2&am

2021-09-17 19:47:20【办公】
立即下载
LEN函数计算参与项目的员工人数
办公
问题情境如下样表：统计每一位员工参与的项目数。如何用公式实现？公式实现 C2单元格输入公式： =(LEN(B2)-LEN(SUBSTITUTE(B2,'、',))+1)*(B2<>'') 确定，得第一位员工参与项目数量，公式向下填充，得所有员工参与的项目数。如下图：公式解析

2021-09-17 19:47:12【办公】
立即下载
LOOKUP DATEDIF计算账户年龄哪些账户应该结算？
办公
问题情境如下样表：费用发生在不同的日期，要统计每笔费用的账龄。要求把账龄分为'6个月内','6-12个月','1-2年','超过2年'四个时间段。公式实现 D2单元格输入公式： =LOOKUP(DATEDIF(B2,TODAY(),'M'),{0,6,12,24},{'6个月内','6-12个月',

2021-09-17 19:47:04【办公】
立即下载
查找查询函数十种用法的集合收藏起来备用！
办公
最近过不少查询函数的用法，有朋友说能不能把每种函数的用法做个归纳对比？好吧，今天从LOOKUP函数起…… 关键步骤分析第一种用法：普通查找在H2中输入公式“=LOOKUP(1,0/(C2:C12=G2),E2:E12)”：其中： (C2:C12=G2)： {FALSE;FALSE;FALSE;FA

2021-09-17 19:46:55【办公】
立即下载
查找函数——是合并单元格拆分和查找计算的利器
办公
这个问题来也来自我们的微信群。我们的群里经常会有爱动脑筋的朋友，时不时提出新颖的题目，大家商榷方便的解法。相信，我们群友们在不久的后来，都会成为所向披靡的Office高手！今天题目是这样的：关键在于：不能增加辅助列！其实，数据量小，增加辅助列，还可以，如果数据量大，手工增加辅助列，终究不可行！视频解

2021-09-17 19:46:48【办公】
立即下载
结合MAX、MIN、IF 统计多个部门同一部门的最大、最小数据
办公
问题来源一位做电商数据分析的朋友说，他要对所管理的六个仓库的销售额进行对比统计，统计出每个仓库的最高与最低销售额。他有几万行的数据，简化到下面几行，以方便清楚统计公式。关键操作公式实现在E2单元格输入公式：“=MAX(IF($A$2:$A$16=D2,$B$2:$B$16))”，按“Ctrl+Shift

2021-09-17 19:46:42【办公】
立即下载
MID FIND摘录括号中的年份
办公
问题情境如下数据表： A列的“政案名称”中大都在括号“云 ”内包含年份信息，如“财税（2012）75号”中的“2012”即为年份。通过A列中的年份信息获取年份并将其填到“年份”列中，显示为“2012年”形式，如果政策中没有年份则显示为空。公式实现在B2单元格输入公式“=IFERROR(MID(A2,FIN

2021-09-17 19:46:33【办公】
立即下载
结合MID和FIND函数提取括号中的内容
办公
问题来源朋友传来样表，要取出单元格中括号内的内容：这种情况需要找到左括号“（” 与右括号“）”位置，再利用MID函数取出两个位置中间的字符。公式实现在B2单元格输入公式： =MID(A2,FIND('（',A2)+1,FIND('）',A2)-1-FIND('（',A2))，确认以后，即可取出A2

2021-09-17 19:46:27【办公】
立即下载
网络日 INTL函数工作日模式算法
办公
甲说：我们单位周六、周日双休，从没有节假日；乙说：我们单位周日单休，从没有节假日；丙说：我们单位周六、周日双休，还有节假日；丁说：我们单位周日单休，还有节假日；但，我们都有同一个问题：怎么算两个日期之间的工作日？公式实现这四种情况的实现，一个函数就能解决，这个函数就是： NETWORKDAYS

2021-09-17 19:46:22【办公】
立即下载
NUMBERSTRING和TEXT功能:阿拉伯数字和中文数字的转换
办公
我们经常在进行数据处理的时候，经常会遇到阿拉伯数字与中文数字之间的转换，尤其遇到“钱”的问题时。而EXCEL提供的设置单元格格式，根本满足不了这种需求。今天跟大家利用NUMBERSTRING和TEXT函数实现数字在阿拉伯与中文格式之间的转变。阿拉伯转中文数字阿拉伯数字转中文数字常用的两种函数是NUMBE

2021-09-17 19:46:14【办公】
立即下载
Excel图表n多产品月度销售报告选择销量最大的月份
办公
做电商数据分析的朋友，提出的问题：他们有很多产品，要分析每种产品哪个月销售量最多，以改进明年的销售计划。简化数据如下：用公式提取每种产品销量最大的月份。公式实现在N2单元格输入公式： =OFFSET($A$1,,MATCH(MAX(B2:M2),B2:M2,0)) 公式向下填充，即得所有产品

2021-09-17 19:32:21【办公】
立即下载
n许多人分组完成了m个项目 excel如何统计每个人都参与了哪些项目？
办公
一位朋友留言，说他们项目部所有的人，每五人为一小组，完成了很多项目。现在，要论功行赏，按分组名单，统计每人参与了哪些项目。他问有没有公式，一次完成统计。为了好述，将数据简化如下：最终要完成：按照表一项目分组，完成二人员参与项目统计。公式实现在H2单元格输入公式： =IFERROR(INDEX(

2021-09-17 19:32:12【办公】
立即下载
n多商品 excel图表计算同一商品对应数据的最大值和最小值
办公
朋友传给我一张EXCEL表格，里面有各个城市的销售数据，城市一列有很多行城市名是相同的。朋友要计算同一城市销售量的最大值和最小值。换了相似的数据，计算相同商品的最大和最小出货量：视频解关键操作计算最大出货量 F2单元格输入公式“=MAX(($A$2:$A$28=E2)*($B$2:$B$28))”

2021-09-17 19:32:04【办公】
立即下载