Excel之家ExcelHome

其他

学函数公式,这是最重要的基本功

小伙伴们好啊,今天咱们一起学习一下公式中的引用方式。在说引用方式之前,有必要先说说公式的复制。当咱们输入一个公式后,能够快速将公式复制到具有相同运算规则的其他单元格。比如下图中,在D2单元格里使用公式“=B2*C2”计算出土豆的金额。那底下的白菜、辣椒怎么算呢?如果像下面这样操作,那肯定是二逼青年了:D3输入公式=B3*C3,回车D4输入公式=B4*C4,回车……要将D2单元格的公式快速向下复制到其他单元格,可以使用以下任意一种方法:方法1,光标靠近D2单元格右下角,拖动填充柄:方法2,光标靠近D2单元格右下角,双击填充柄,可快速填充到当前数据区域的最后一行。方法3,选中D2:D6,按住Ctrl不放,再按D。你看,公式向下复制时,能自动计算当前行的数量和单价,Excel够智能吧?咱们回过头来看看不同单元格中的公式是啥样的:公式复制到D3单元格,公式中的B2*C2自动变成了B3*C3公式复制到D4单元格,公式中的B3*C3又自动变成了B4*C4这种随着公式所在单元格的不同而自动变化引用位置的特性,就是单元格的引用方式。前面咱们用到的=B2*C2,就是Excel默认的单元格引用方式——相对引用。那啥是相对引用,相对于谁呢?其实就是相对于公式所在单元格的位置的。你看D2单元格里,B2是在D2单元格的左侧两列,C2是在D2单元格的左侧一列。当公式向下复制到其他单元格里,Excel也会根据公式所在单元格的不同,自动将公式左侧两列的数值与公式左侧一列的数值相乘了。既然有相对引用,那就有绝对引用。如下图,要在数量乘以单价的基础上,根据G1单元格中指定的税率来计算所得税金额。这里,咱们不仅要计算每一行中的数量乘以对应单价,还要乘以固定不变的G1单元格。D2单元格的公式可以写成这样,然后向下复制到D6单元格:=B2*C2*$G$1公式中的G1,在行号和列号前都加上了一个美元符号$,当公式在不同单元格里复制时,对G1单元格的引用会始终保持不变,这种写法就表示是绝对引用。那这个美元符号是怎么加上的呢?除了手工输入,还可以在公式输入状态下,将光标放到单元格地址中间或是选中一段单元格地址,依次按F4键,每按一次F4,美元符号就会和行号列号形成不同的组合形式。从上图中咱们可以看到,除了相对引用形式G1和绝对引用形式$G$1,还有两种特殊的显示形式:G$1和$G1。这样的写法又是什么意思呢?这种只在行号行号或列号前加上美元符号,表示仅对行或列方向之一使用绝对引用,剩下的另一半则使用相对引用,也叫做混合引用。先来看看G$1这种只在行号前加上了美元符号的写法,当公式向下复制时,引用的行号固定不变,当公式向右复制时,引用的列号依次递增。再来看$G1这种只在列号前加上美元符号的写法,当公式向下复制时,行号依次递增,当公式向右复制时,引用的列号固定不变。最后给大家留一个思考题,如下图所示,要分别计算各个菜品在不同点餐份数下的金额是多少,B3单元格中的公式该怎么写呢?图文制作:祝洪忠
2023年3月11日
其他

12个常用Excel文本函数

LENB(“函数课”),结果等于6;8、LOWER:描述:将一个文字串中的所有大写字母转换为小写字母解释:=LOWER(字符串)。把大写字母转化成小写字母用法举例:=LOWER("AOIEUTVPO
2023年3月11日
其他

【免费直播】轻松学会用数据透视表做数据分析

主题:轻松学会用数据透视表做数据分析
2023年3月10日
其他

数据查询,几个模式化公式请收好

小伙伴们好啊,今天和大家分享几个数据查询的典型公式应用。1、一对多查询所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。如下图所示,希望根据F2单元格中指定的部门,提取出左侧列表中“生产部”的所有人员姓名。Excel
2023年3月9日
其他

这些公式用不好,加班肯定跑不了

小伙伴们好啊,今天咱们分享一组数据查询的常用公式,看看这些公式你会几种?如下图所示,需要根据H2单元格中的月份,以及H4单元格的城市名,在左侧数据表中来查询同时符合两个条件的数据。数据查询,自然离不开VLOOKUP,在I3单元格输入以下公式:=VLOOKUP(H2,A:F,MATCH(H4,A1:F1,0),0)公式中的“H2”,是VLOOKUP要查询的关键字,“A:F”是要查询的数据区域,至于要在数据区域中返回第几列的内容,这里咱们使用MATCH函数来帮个忙。MATCH(H4,A1:F1,0)
2023年3月9日
其他

Excel批量插入图片,以后就归函数管了

Excel批量插入图片,归函数管了?对,你没看错。Microsoft
2023年3月8日
其他

四舍五入函数

ROUND函数是常用的四舍五入函数之一,用于将数字四舍五入到指定的位数。该函数对需要保留位数的右边1位数值进行判断,若小于5则舍弃,若大于等于5则进位。其语法结构为:ROUND(number,num_digits)第2个参数num_digits是小数位数。若为正数,则对小数部分进行四舍五入;若为负数,则对整数部分进行四舍五入。以下公式将数值728.492四舍五入保留2位小数,结果为728.49。=ROUND(728.492,2)以下公式将数值-257.1四舍五入到十位,结果为-260。=ROUND(-257.1,-1)此外,FIXED函数也可将数字四舍五入到指定的位数。该函数的舍入规则与ROUND函数一致,不同的是FIXED函数的返回结果是文本,且能返回带千位分隔符的格式文本。其语法结构为:decimals参数是四舍五入的位数。若为正数,则对小数部分进行四舍五入;若为负数,则number从小数点往左按相应位数四舍五入。若省略该参数,则按其值为2进行四舍五入。no_commas参数是一个逻辑值。若为TRUE,则返回不包含千位分隔符的结果文本;若为FALSE或省略,则返回带千位分隔符的结果文本。分别使用以下几个公式将数值28359.476四舍五入保留两位小数。=ROUND(28359.476,2)该公式结果为数值28359.48。=FIXED(28359.476)该公式结果为带千位分隔符的文本28,359.48。=FIXED(28359.476,2,TRUE)该公式结果为不带千位分隔符的文本28359.48。分别使用以下几个公式将数值-5782.3四舍五入到十位:=ROUND(-5782.3,-1)该公式结果为数值-5780。=FIXED(-5782.3,-1)该公式结果为带千位分隔符的文本-5,780。=FIXED(-5782.3,-1,TRUE)该公式结果为不带千位分隔符的文本-5780。特定条件下的舍入在实际工作中,不仅需要按照常规的四舍五入法来进行取舍计算,而且需要更灵活的特定舍入方式,下面介绍两则算法技巧。按0.5单位取舍:将目标数值乘以2,按其前1位置数值进行四舍五入后,所得数值再除以2。按0.2单位取舍:将目标数值乘以5,按其前1位置数值进行四舍五入后,所得数值再除以5。另外,MROUND函数可返回参数按指定基数四舍五入后的数值,语法结构为:MROUND(number,multiple)如果数值number除以基数multiple的余数大于或等于基数的一半,则MROUND函数向远离零的方向舍入。注意:当MROUND函数的两个参数符号相反时,函数返回错误值#NUM!。示例:特定条件下的舍入计算如图15-3所示,分别使用不同的公式对数值进行按条件取舍运算。图15-3按指定条件取舍C4单元格使用ROUND函数的公式为:=ROUND(B4*5,0)/5D4单元格使用MROUND函数的公式为:=MROUND(B4,SIGN(B4)*0.2)其中SIGN函数取得数值的符号,如果数字为正数,则返回1;如果数字为0,则返回零(0);如果数字为负数,则返回-1。目的是确保MROUND函数的两个参数符号相同,避免返回错误值。利用上述原理,可以将数值舍入至0.5单位。E4单元格公式为:=ROUND(B4*2,0)/2F4单元格公式为:=MROUND(B4,SIGN(B4)*0.5)四舍六入五成双常规的四舍五入直接进位,从统计学的角度来看会偏向大数,误差积累而产生系统误差。而四舍六入五成双的误差均值趋向于零。因此是一种比较科学的计数保留法,是较为常用的数字修约规则。四舍六入五成双,具体讲就是保留数字后一位小于等于4时舍去,大于等于6时进位,等于5且后面有非零数字时进位,等于5且后面没有非零数字时分两种情况:保留数字为偶数时舍去,保留数字为奇数时进位。示例:利用取舍函数解决四舍六入五成双问题如图15-4所示,对B列的数值根据E3单元格指定的位数按四舍六入五成双法则进行修约计算。C3单元格修约的通用公式如下:=ROUND(B3,E$3)-(MOD(B3*10^(E$3+1),20)=5)*10^(-E$3)对于保留位数字为偶数、保留位后一位为5且后面无非零数字的情况,四舍五入法会进位,而四舍六入五成双的方法则不需要进位。因此公式先将数值按四舍五入法则修约,然后针对上述情况减去10^(-E$3),即可完成四舍六入五成双的修约。图15-4利用ROUND函数实现四舍六入五成双END推荐阅读北京大学出版社Excel2019应用大全京东3.8大促Excel
2023年3月8日
其他

条件格式用不好,奖金数你发的少

插入控件在【开发工具】选项卡下,插入数值调节钮。右键单击控件→设置控件格式设置完成后,单击调节钮,就可以调整F2单元格的数值了。步骤2
2023年3月8日
其他

序号填充真挺棒,还不会的没对象

小伙伴们好啊,先问个问题:如果要输入1到10的序号,你会怎么操作呢?哈哈,还在一个个输入的同学自觉去检讨二分钟。接下来咱们就一起说说Excel中的序号填充那些事儿。1、输入1到10的序号先输入1,然后按住Ctrl不放,向下拖动。2、输入1到1000的序号如果要输入1到1000的序号,再去拖动可有点没面子了。先在名称框输入要填入序号的单元格地址A2:A1001,按回车。然后在编辑栏输入=row(A1)
2023年3月7日
其他

SUMPRODUCT,这个函数还不错

哈喽,表亲们早上好,我是星光,咱们又见面了。今天和大家分享一个名字拖沓,但是应用非常广泛的函数——SUMPRODUCT。开门见山,咱们通过几个例子,一起来看下SUMPRODUCT具体能干什么样的工作。假设上面这张图,是某个公司工资发放的部分记录表,A列是工资发放的时间,B列是员工所属的部门,C列是员工姓名,D列是相关员工领取的工资金额。那么,问题来了:一,西门庆同志领取了几次工资?这是一个单条件计数的问题,通常我们使用COUNTIF函数,如果使用SUMPRODUCT函数,一般写成这样:=SUMPRODUCT((C2:C13="西门庆")*1)先判断C2:C13的值是否等于”西门庆”,相等则返回TRUE,不等则返回FALSE,由此建立一个有逻辑值构成的内存数组。SUMPRODUCT有一个特性,它会将非数值型的数组元素作为0处理,逻辑值自然是属于非数值型的数组元素,我们使用*1的方式,把逻辑值转化为数值,TRUE转化为1,FALSE转化为0,最后统计求和。二,西门庆同志一共领取了多少工资?这是一个单条件求和的问题,通常我们使用SUMIF函数,如果使用SUMPRODUCT函数,我们可以写成这样:=SUMPRODUCT((C2:C13="西门庆")*D2:D13)依然首先判断C2:C13的值是否等于”西门庆”,得到逻辑值FALSE或TRUE,再和D2:D13的值对应相乘,最后统计求和得出结果。看完了上面两个问题,有些表亲心里就嘀咕了,貌似SUMPRODUCT能干的事,SUMIF和COUNTIF也能做到,而且做的更好,那么还要SUMPRODUCT干啥?乡亲们呐,话不能这么说,SUMPRODUCT可以说是上得厅堂下得厨房,迷住过西门庆,暗恋过武二郎。对工作环境不挑不拣,它对参数类型没有啥特别要求,COUNTIF和SUMIF就不同咧,他俩要求个别参数,必须是单元格的区域,比如下面这两个问题,COUNTIF和SUMIF就犯难了。三,3月份财务部发放了几次工资?3月份财务部发放了几次工资,这是一个多条件计数的问题哈:第一个条件,发放工资的时间必须是3月份;第二个条件,发放工资的部门必须是财务部。如果使用多条件计数函数COUNTIFS,判断发放工资的时间是否属于3月份,会简单问题复杂化。而使用SUMPRODUCT函数,咱们可以简单利索的写成这样:=SUMPRODUCT((MONTH(A2:A13)=3)*(B2:B13="财务部"))四、统计3月份财务部发放了多少工资?表亲们都晓得,这是一个常见的多条件求和问题。如果使用SUMIFS函数,判断发放工资的时间是否属于3月份,和COUNTIFS类似,也会简单问题复杂化。SUMPRODUCT跃然而至:=SUMPRODUCT((MONTH(A2:A13)=3)*(B2:B13="财务部"),D2:D13)上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦,可以归纳为:=SUMPRODUCT((条件一)*(条件二)……,求和区域)五,3月份财务部和市场部合计发放了多少工资?通过上面的问题,表亲们已经晓得如何计算3月份财务部发了多少工资,那么3月份财务部和市场部合计发了多少工资,又当怎么计算呢?我们可以写成这样:=SUMPRODUCT((MONTH(A2:A13)=3)*(B2:B13={"财务部","市场部"})*D2:D13)好吧,唠唠叨叨说了这么多,是到了该结束的时候啦。我是男神,我是星光,仅以此文献给我已逝去的青涩小时光,以及时光里的某个人……练手文件:http://caiyun.feixin.10086.cn/dl/1B5CvauiakGMa提取码:e5Ag图文作者:看见星光
2023年3月7日
其他

99.9%的人不知道的秘密,Excel已经会自己写公式了

昏黄的灯光下,林小野正在对着电脑埋头苦干。偌大的办公室,只有这个孤单的娇小身躯上方亮着一盏灯,显得有点不协调。“搞定!”林小野重重的点了一下文件的保存按钮,如释重负吐了一口气。她扭动了一下僵硬的脖子,把双手搓热,然后轻轻按在了自己的眼睛上,以此缓解一下疲劳。林小野已经当了6年表妹,每天的工作就是折腾Excel。或者说,被Excel折腾。她的水平在公司不算差,奈何工作实在太多,不得不经常加班应对。“Excel啊Excel,现在都2023年了,话说你都奔四了,啥时候才会自己写公式呢?”林小野嘟囔了一句。最近有很多关于AI的新闻,尤其一个叫ChatGPT的东西特别火,据说啥都会。ExcelHome公众号上面也发表过一篇文章,但是林小野还没搞的很明白,也没有时间亲自试试。“要是你能自己写公式,我就轻松多了啊!”“我会自己写公式啊!”突如其来的的声音直接响在林小野的脑海中。林小野吓了一跳,赶紧站起来四周环顾,办公室确实没别人。“林酱,别找了,没别人!我是你电脑里面的Excel!”这声音又出现在林小野的脑海里面了。林小野看向自己的电脑屏幕,果然发现Excel的窗口在抖动。呦呵,Excel成精了?林小野从小就胆大,而且超迷二次元,对奇异事件不但不害怕,反而有点兴奋,于是大方的坐了下来。“你真是我电脑里面的Excel?”“是呀,你没看我正在抖动示意嘛。”林小野来了兴致,和Excel成精相比,搞定工作任务更重要。“那你说说,你真的会自己写公式吗?”“当然会!”“是要借助那个什么ChatGPT吗?”“别提那个东西!我不用它,自己就会!”“我咋从来没听说过呢?”“90%的人都只知道我不到10%的功能,不知道我的终极奥义太正常了。看在六年来我们朝夕相处的份上,我才告诉你!”“那你现在展示展示?”“没问题!”例1▼“来,先给你展示用公式从长字符串里面提取公司名!”“看到没有,只要先给我两三个例子,我就知道你要计算什么,然后帮你把公式写出来!”“不管是提取文本,还是提取数字+文本都手到擒来,看我提取货品的单位!”
2023年2月22日
其他

难以置信,已经有人用ChatGPT做Excel报表了,效果让高手惊掉下巴

要问2023年初科技领域什么最火,那自然是ChatGPT。ChatGPT由人工智能研究实验室OpenAI于11月30日推出。上线短短5天,用户数量已突破100万,到今天,用户数量已经突破1亿。ChatGPT是一个超级智能聊天机器人,他能听懂你的话,并且生成高质量的内容来满足你的需求,包括但不限于写论文、写小说、写文案、写合同、写代码、做作业、改bug……Kevin我从春节前就开始玩ChatGPT,有少许心得和体会。今天,只说说咱们Excel用户,如果用上ChatGPT,会有些什么样的事情发生。01有问必答,ChatGPT让小白也能轻松做表首先我现在有个Excel表格,长的是这个样子滴。假设我是个小白,连求和公式也不会,想要对金额进行总计,我就可以找ChatGPT帮忙。只见ChatGPT以迅雷不及掩耳盗铃之速度就给出了答案,我们只要把这个公式复制到Excel里面就好了。你可能会说,就这?别急,我们把问题难度提高一点点。我们来计算1月份的金额合计,ChatGPT能不能搞定?咱们不确定这个答案对不对,所以复制这个公式,粘到单元格里面看看结果。
2023年2月9日
其他

阶梯电价公式详解

阶梯电价,是指把户的用电量设置为若干个阶梯,分段或分档次定价计算费用。具体计算方式为:第一阶梯为基础电量,此阶梯内的电价最低;第二阶梯是对用电量高出基础电量的一部分,执行的电价会比基础电价高一些;第三阶梯是对高出第二阶梯部分的剩余用电量,这部分执行的电价会更高。下面咱们就用一个实例给大家说说用到的公式以及计算过程。如下图所示,分别是各档的分档标准和相应的电价:如下图所示,要根据B列的用电量来计算电费。C2单元格输入以下公式,向下复制:=SUM(TEXT(B2-{0,260,600},"0;!0")*{0.68,0.05,0.25})下面咱们简单说说这个公式的意思。公式中的B2是本月用电量。{0,260,600}中的260和600分别是第一档和第二档的用电量止码。{0.68,0.05,0.25}中的0.68是第一档的电价;0.05是第二档和第一档的差价(0.73-0.68=0.05);0.25是第三档和第二档的差价(0.98-0.73=0.25)。可以这样来理解公式的计算方式:1、在所有的用电量中,执行第1档价格的是601,即A2-0千瓦时。2、执行第2档与第1档差价的是总电量减第1档用电量止码,即A2-260,结果为341。3、执行第3档与第2档差价的是总电量减第2档用电量止码,即A2-600,结果为1。最后用执行不同档的用电数乘以对应的差价,也就是:0.25*1、0.05*341、0.68*601再用SUM函数求和,就可以得到电费数了。公式中的TEXT部分是什么意思呢?TEXT(B2-{0,260,600},"0;!0")因为当用电总量在最低档的时候,再以用电总量减去各档的止码会出现负数。比如说,用电总量为200,用200-{0,260,600},得到这样一个内存数组:{200,-60,-400}也就是说执行第1档电价的是200,而执行第2档和第3档变成了负数。将TEXT函数第二参数写成"0;!0",目的是将负数强制转换为0,就是将内存数组{200,-60,-400}变成{200,0,0}。这样处理后,执行第1档电价的用电量是200,执行第2档和第3档的用电量就是0了。实际应用中,大家可以根据数据情况修改公式中的常量参数{0,260,600}和各档之间的差额{0.68,0.05,0.25}。好了,今天的内容就是这些,小伙伴们,明天见!图文作者:祝洪忠
2022年9月29日
自由知乎 自由微博
其他

12个常用Excel文本函数

LENB(“函数课”),结果等于6;8、LOWER:描述:将一个文字串中的所有大写字母转换为小写字母解释:=LOWER(字符串)。把大写字母转化成小写字母用法举例:=LOWER("AOIEUTVPO
2022年9月29日
其他

一组常用公式,效率提升2.3%

1、根据出生年月计算年龄如下图所示,需要根据B列出生年月计算年龄,C2单元格公式为:=DATEDIF(B2,TODAY(),"y")TODAY函数返回系统当前的日期。DATEDIF函数以B2的出生年月作为开始日期,以系统日期作为结束日期,第三参数使用“Y”,表示计算两个日期之间的整年数。2、近似查询如下图所示,需要根据右侧的对照表,返回B列分数对应的等级。C2公式为:=INDEX(F$3:F$6,MATCH(B2,E$3:E$6))MATCH函数负责找出位置,INDEX函数负责根据这个位置找到对应的值。本例中MATCH函数省略第三参数,表示在E3:E6这个区域中查找小于或等于B2单元格(75)的最大值。在E3:E6这个区域中,没有75这个值,她就找到所有几个弟弟当中最大的一个弟弟,也就是60。然后返回60在E3:E6这个区域中的位置2,INDEX函数再根据这个位置返回F3:F6单元格中对应的值。注意这里有一个前提:查询区域首列的值必须以升序排序,否则就乱了。3、忽略错误值求和如下图所示,C列数据区域中包含有错误值,如何进行求和呢?E2单元格公式为:=SUMIF(C:C,"
2022年9月29日
其他

高级筛选普及贴,收藏备用啦

小伙伴们好啊,今天咱们来说说筛选功能的进阶版——高级筛选。1、筛选指定条件的记录如下图所示,要从数据表中筛选出“产品归类”字段中是“信用贷”的所有记录。首先在空白单元格中写入要筛选的字段名称和要筛选的条件。注意字段名称要和数据表中的字段名称完全一样,否则就筛选不出来了。单击数据区域任意单元格,在【数据】选项卡下单击【高级】按钮,然后根据提示选择条件区域和存放数据的区域。2、筛选符合条件的部分记录如下图所示,要筛选“产品归类”字段中是“信用贷”的所有“客户姓名“。首先写入要筛选的字段名称和要筛选的条件,然后在目标区域写上字段标题“客户姓名”。单击数据区域任意单元格,【数据】→【高级】。在【高级筛选】对话框中选择条件区域,在“复制到”文本框中单击一下,选择刚刚输入了字段标题的单元格。勾选上“选择不重复的记录”。3、筛选符合两个条件的记录高级筛选的条件区域有点讲究。在同一行中分别写上条件,表示要筛选同时符合两个条件的记录。如下图中的条件写法,就表示筛选出“产品归类”为“信用贷”,并且“经办部门”为“业务四部”的全部记录。在不同行中分别写上条件,则表示筛选两个条件符合其一的记录。如下图中的条件写法,就表示筛选出“产品归类”为“信用贷”,或者“经办部门”为“业务四部”的全部记录。同一行筛选条件的关系是“并且”,不同行筛选条件的关系是“或者”,大家只要记住这个规律即可。4、设置精确匹配条件高级筛选默认使用模糊匹配,只要单元格中包含关键字就会被视作符合条件,如果要得到和筛选条件完全一样的内容,需要做一点小动作。如下图所示,如果只需要“按摩椅”的记录,条件需要写成
2022年9月27日
其他

一组Excel实用技巧,懒人专用

小伙伴们好啊,今天老祝为大家继续一组简单实用的Excel技巧,让效率再高一点点。1、快速标记完成率干巴巴的数字总是显得太过于单调,用用下面这一招儿,马上牛牪犇逼装起来。选中需要设置的单元格区域,设置自定义格式代码:[红色]↑0.0%;[绿色]↓0.0%英文分号前面是正数的显示方式,就是用红色字体加上一个↑。英文分号后面是负值,用绿色字体加上一个↓
2022年9月27日
其他

数据查询的好搭档

小伙伴们好啊,今天和大家分享一个数据查询的最佳搭档——INDEX函数和MATCH函数。这两个函数组合,能够完成VLOOKUP函数和HLOOKUP函数的全部查找功能,并且可以实现任意方向的查询以及多条件查询等。先来说说INDEX函数的作用:INDEX函数用于在一个区域中,根据指定的行、列号来返回内容。该函数通常使用两个或是三个参数,三个参数的写法是:INDEX(单元格区域,指定的行数,指定的列数)例如以下公式,用于返回A1:D4单元格区域第3行和第4列交叉处的单元格,即D3单元格。=INDEX(A1:D4,3,4)两个参数的写法是:INDEX(一行或一列数据,指定要返回第几个元素)例如以下公式,用于返回A1:A4单元格区域中的第3个元素,即A3单元格的内容。=INDEX(A1:A4,3)接下来咱们再说说MATCH函数的作用。MATCH
2022年9月24日
其他

带下拉按钮的动态图表,很简单

小伙伴们好啊,今天和大家一起分享一个图表制作的技巧,先来看看效果:这个图表里,其实有三个数据系列,分别是一深一浅两个颜色的条形图,再就是大大的圆圈儿,其实是用散点图模拟出来的。先来看数据源:各车间不同月份的优质率指标:首先建立一个下拉菜单。单击H2,【数据】
2022年9月24日
其他

【免费直播】Excel暨Power BI数据整理与可视化案例实战

BI系列组件便更加博大精深!但做数据分析前需要对前期准备的数据,进行合理分析,提取出有用的信息,形成结论,做出最佳决策。Power
2022年9月23日
其他

这些Excel序号技巧,你都会用吗?

序号对于大家来说都不会陌生,今天咱们就一起聊聊序号那些事儿。上面这个图中的A列,就是咱们经常看到的序号形式之一。有朋友会说,这很简单啊,就是一些数字的罗列而已。但在实际工作当中,远不止数字罗列这么简单。咱们经常会遇到不同样式的序号排列要求,比如说按部门填写序号、筛选后填写序号、不连续的数据填写序号等等。1、生成常规序号一般咱们填写序号,只要在第一个单元格内输入数字1,然后按住单元格右下角的填充柄向下拖动,然后在【填充选项】中选择【序列填充】就可以了:如果相邻列已经输入了内容,可以分别输入1、2,然后选中两个单元格,双击单元格右下角的填充柄,就可以快速完成序号的填充。如果相邻列没有输入内容,而我们需要生成的序号又非常多,是不是就要一拖到底了呢?假如说要在A列生成1至10000的连续序号,可以先在A1单元格输入数字1,然后在名称框内输入“A1:A10000”,按Enter键。接下来依次单击【开始】→【填充】→【序列】,在【序列】对话框中,终止值输入10000,单击【确定】。就可以快速的生成1至10000的连续序号了。2、按部门添加序号以下图为例,要求按部门单独填写序号,不同部门都要从1开始编号,按顺序递增。A2单元格输入以下公式,向下复制:=COUNTIF(B$2:B2,B2)COUNTIF函数对区域中满足单个指定条件的单元格进行计数。第一参数B$2:B2中的B$2是行绝对引用,在公式向下复制时,就会变成下面这样不断扩大的引用区域:COUNTIF(B$2:B3,B3)COUNTIF(B$2:B4,B4)COUNTIF(B$2:B5,B5)……也就是统计自B2单元格开始,到公式所在行的这个区域内,有多少个与B列内容相同的单元格。3、筛选后保持连续的序号如果按常规方法输入序号后,一旦数据经过筛选,序号就会发生错乱。如何处理才能使序号在筛选后也能保持连续呢?A2单元格输入以下公式向下复制:=SUBTOTAL(3,B$1:B2)-1这时候再进行筛选,序号就始终保持连续了。SUBTOTAL函数只统计可见单元格的内容,通过给定不同的第一参数,可以完成计数、求和、平均值、乘积等等多种汇总方式。在本例中,第一参数是3,就是告诉SUBTOTAL函数要执行的汇总方式是COUNTA。COUNTA函数用于计算区域中非空单元格的个数。SUBTOTAL(3,区域)就是计算区域中可见非空单元格的个数。第二参数B$1:B2的B$1使用了行绝对引用,当公式向下复制时会变成B$1:B3、B$1:B4、B$1:B5……也就是在这样一个逐行递增的引用区域中,统计可见非空单元格的个数。注意这里有一个问题,A2单元格的公式如果使用下面的公式:=SUBTOTAL(3,B$2:B2)这样虽然在筛选时虽然序号没有问题了,但是在工作表中使用了SUBTOTAL函数后,Excel会默认把最后一行作为汇总行,所以会始终显示,因此影响到筛选操作了。我们只要记得在处理序号时,需要将SUBTOTAL函数的第二参数引用起始位置写成公式所在行的上一行,再将结果减1就可以了。4、生成间断的序号在下图示的数据表中,会不规律的出现一些空行,要求我们在生成序号的时候自小到大排列,但是空行不显示内容。A2单元格输入以下公式,向下复制:=IF(B2="","",MAX(A$1:A1)+1)先判断B2是否为空值,如果B2是空值则返回空,否则计算当前单元格以上的区域中的最大值,加1后得到新的序号。5、合并单元格添加序号使用COUNTA函数,能够实现对带有合并单元格的表格快速添加序号。同时选中A2:A13单元格区域,编辑栏输入公式,按Ctrl+回车:=COUNTA(B$2:B2)好啦,今天的内容就这些吧,祝各位小伙伴们一天好心情。图文制作:祝洪忠
2022年9月21日
其他

五分钟,学会三个常用函数公式

小伙伴们好啊,今天咱们再学习几个简单实用的函数公式用法。1、MID函数MID函数的用法是在某个单元格中的指定的位置开始,提取指定长度的字符。该函数的用法为:MID(要处理的字符,在什么位置开始,截取几个字符)常用示例:如下图所示,要根据身份证号码提取性别,B2单元格公式为:=IF(MOD(MID(B2,17,1),2),"男","女")先使用MID函数,从B2单元格的第17位开始提取1个字符,这个字符就是性别码。然后使用MOD函数,计算这个性别码与2相除的余数。如果IF函数的第一个参数等于0,IF函数将其按FALSE处理,返回第三参数指定的内容“女”。如果不等于0,则按TRUE处理,返回第二参数指定的内容“男”。2、AVERAGEIF函数这个函数的作用是按条件统计平均值的,用法与SUMIF函数类似。第一个参数是要要判断条件的区域,第二参数是指定的条件,第三参数是要计算平均值的区域。如果第一参数符合指定的条件,就计算与之对应的第三参数的平均值。如下图,要统计女性平均年龄。公式为:=AVERAGEIF(B2:B12,"女",C2:C12)3、FREQUENCY函数计算数值在某个区域内的出现频次,这个函数的用法为:FREQUENCY(要统计的数据区域,指定不同区间的间隔点)常用示例:如下图,要统计各分数段的人数。同时选中E2:E6,输入以下公式,按住Shift+Ctrl不放,按回车。=FREQUENCY(B2:B11,D2:D5)第一参数B2:B11是数值所在区域,第二参数D2:D5是用于计算频率的间隔。返回的结果比指定间隔数会多出一个,因此本例中需要同时选中五个单元格。返回的结果分别是小于等于60的个数,61~70的个数,71~80的个数,81~90的个数,最后一个是大于90部分的个数。图文制作:祝洪忠
2022年7月19日
其他

如何将二维表转换为一维表

是否想要学会一招顶“一万招”的“懒人技法”?快跟着小编学起来吧!在日常工作中,经常会有一些布局比较特殊的表格,使用PowerQuery可以将其转换为便于统计汇总的数据列表。表格根据结构的不同,可以分为一维表和二维表。所谓一维表就是字段和记录的简单罗列,每一行都是一条完整的记录,每一列用来存放一个字段,多用于流水表和明细表。如果将一维表的每一条记录看作一条线,二维表中的一条记录则相当于一张网,其特点是在多列中都有相同属性的数值,如图5-1所示,左侧为一维表样式,右侧为二维表样式。图
2022年7月18日
其他

Excel中的高级筛选,你用过吗?

小伙伴们好啊,今天咱们来说说筛选功能的进阶版——高级筛选。1、筛选指定条件的记录如下图所示,要从数据表中筛选出“产品归类”字段中是“信用贷”的所有记录。首先在空白单元格中写入要筛选的字段名称和要筛选的条件。注意字段名称要和数据表中的字段名称完全一样,否则就筛选不出来了。单击数据区域任意单元格,在【数据】选项卡下单击【高级】按钮,然后根据提示选择条件区域和存放数据的区域。2、筛选符合条件的部分记录如下图所示,要筛选“产品归类”字段中是“信用贷”的所有“客户姓名“。首先写入要筛选的字段名称和要筛选的条件,然后在目标区域写上字段标题“客户姓名”。单击数据区域任意单元格,【数据】→【高级】。在【高级筛选】对话框中选择条件区域,在“复制到”文本框中单击一下,选择刚刚输入了字段标题的单元格。勾选上“选择不重复的记录”。3、筛选符合两个条件的记录高级筛选的条件区域有点讲究。在同一行中分别写上条件,表示要筛选同时符合两个条件的记录。如下图中的条件写法,就表示筛选出“产品归类”为“信用贷”,并且“经办部门”为“业务四部”的全部记录。在不同行中分别写上条件,则表示筛选两个条件符合其一的记录。如下图中的条件写法,就表示筛选出“产品归类”为“信用贷”,或者“经办部门”为“业务四部”的全部记录。同一行筛选条件的关系是“并且”,不同行筛选条件的关系是“或者”,大家只要记住这个规律即可。4、设置精确匹配条件高级筛选默认使用模糊匹配,只要单元格中包含关键字就会被视作符合条件,如果要得到和筛选条件完全一样的内容,需要做一点小动作。如下图所示,如果只需要“按摩椅”的记录,条件需要写成
2022年7月18日
其他

【免费直播】考验Excel水平的面试常用题集~第二季

我是空空老师,在多年的职场生涯中带过不少新人。我们部门对Excel的水平有一定的要求,于是招助理的时候通常要专项考核。曾经有一位求职者,面试的时候跟我说精通Excel。我只能微笑着说,请你准备好,把这几道面试小题拿去做一下。做不出来?只会两题?要不还是回去温习一下再来吧,姐没空带太菜的新人。或者,参加我直播课程,听完课程看看你有哪些不足。重要的事情再说一遍!看直播,请提前微信扫码加入直播学习群,以便在开课前获取直播的随堂练习文件。更多精彩的视频学习内容,请点击左下角【阅读原文】。
2022年7月15日
其他

二分钟,把数据合并到一个单元格

工作中总会有一些特殊的特殊需求,最让人头疼的莫过于将符合条件的多个结果全部放到一个单元格内。举个例子,看下图。A列是某公司部门名称,B列是人员姓名。要求将相同部门的人员姓名填入F列对应单元格,不同人名之间以逗号间隔。说说这道题的解法:首先在C2输入公式:=IF(A2=A1,C1&","&B2,B2)向下复制填充。F2输入公式:=LOOKUP(1,0/(E2=$A$2:$A$9),C$2:C$9)向下复制填充,得到最终结果。这个解法使用了辅助列的方式。C列为辅助列,是一个简单的IF函数。以C2的公式为例:=IF(A2=A1,C1&","&B2,B2)先判断A2和A1的值是否相等,如果相等,则返回C1&","&B2,如果不等,则返回B2。此处A2和A1的值不相等,因而公式返回B2的值"祝洪忠"。在公式向下复制填充的过程中,该公式得出的结果,将被公式所在单元格下方的下一个公式所使用,于是形成人名累加的效果。比如C3单元格公式:=IF(A3=A2,C2&","&B3,B3)A3和A2的值相等,返回真值C2&","&B3。C2为上个公式所返回的结果B2(祝洪忠),B3的值是"星光",所以C3最后结果为"祝洪忠,星光"。辅助列公式输入完成后,在F列使用了一个常用的LOOKUP函数套路,得到最终结果:=LOOKUP(1,0/(E2=$A$2:$A$9),C$2:C$9)LOOKUP的这个套路,忽略错误值,总是取得最后一个符合条件的结果,我们可以总结为:=LOOKUP(1,0/(条件区域=指定条件),要返回的目标区域)该公式以0/(E2=$A$2:$A$9)构建了一个由0和错误值#DIV/0!组成的内存数组,再用永远大于0的1作为查找值,于是查找出最后一个满足部门等于E2的C列结果,即A列最后一个广告部所对应的C列值:C2。如果你使用的是Excel2019及以上版本,那就可以使用TEXTJOIN函数了,这个函数在最新版的WPS表格中也有。在F2单元格输入以下公式,按住SHift+Ctrl不放,按回车,OK了。=TEXTJOIN(",",1,IF(A$2:A$9=E2,B$2:B$9,""))TEXTJOIN函数的用法为:=TEXTJOIN(间隔符号,是否忽略空单元格,要合并的内容)公式中要合并的内容为:
2022年7月13日
其他

打开Excel,做个蚊香图

小伴们好啊,今天咱们学习一个有趣好玩的图表制作,用Excel做一个蚊香图。先来准备数据。第一步:在A1输入1,在B1输入=A1+360,向右复制到E1单元格。第二步:在名称框中输入A2:E361
2022年7月13日
其他

这样的项目进度图,看着揪心

小伙伴们好啊,如果给你下面这样的一个30行×30列的表格,你会想到什么?如果每个单元格代表一个月,这张图中的900个单元格,就足以容下大多数的人生。在这个表格基础上设置条件格式,可以直观展示人生的剩余格子还剩多少。步骤一:选中A1:G1单元格,设置合并单元格,然后填上自己的出生日期。从A2开始选中30行*30列的单元格区域,调整行高和列宽,添加边框。步骤二:设置条件格式选中A2:AD31单元格,依次单击【开始】→【条件格式】→【新建规则】,按下图所示步骤设置条件格式。条件格式公式为:=(ROW(A1)-1)*30+COLUMN(A1)
2022年7月13日
其他

这样的折线图,5.5%的人还不会

小伙伴们好啊,今天咱们分享一个小而多的折线图。下图为某段时间内,意大利、美国、西班牙以及德国的新冠肺炎新增确诊病例数据(数据来源:新浪新闻)。接下来我们用这些数据来制作一个折线图,展示各国新增病例的趋势:操作步骤如下:步骤1先按下图所示,构建图表数据源:步骤2选中B1:E25单元格区域,插入带数据标记的折线图。步骤3
2022年7月12日
其他

合并单元格中加序号,三种方法随你挑

工作中常常碰到这种表格:数据有不规则的合并单元格。如果需要填充序号,应该怎么办呢?这种情况下常规的向下拖动方法失灵了,十行八行的话,咱们可以逐个手工填写,如果是成百上千行呢?这里介绍三个函数,可以方便地解决此类问题。一、COUNTA函数1、从A3开始向下选中需要填充的区域。2、在编辑栏中输入以下公式,按住Ctrl不放按回车:=COUNTA($A$2:A2)
2022年6月28日
其他

神奇的快捷键:Ctrl+Enter

今天我们来学习一个神奇的快捷键——【Ctrl+Enter】,下面举例演示它的几种功能:一、批量填充例如,我们要在所有空白单元格中填充内容,如果这些空白单元格都是相邻的,我们可以先选中他们,然后输入内容,接着按快捷键【Ctrl+Enter】,这样所有单元格就填充好了,如图:但是,如果空白单元格都不相邻呢?可以先按【Ctrl+G】定位,在定位选项卡中选择“空值”,点击“定位”,这样所有空白单元格将会被全部选中,然后直接输入内容,按快捷键【Ctrl+Enter】,这样,所有的空白单元格就会填充完成了。二、批量填充公式我们来看一个变态的表格,表中有一些合并单元格,如果想在最后一列复制公式,用复制粘贴和填充柄都会显示错误,怎么办呢?这时我们可以选中E列,直接在公式栏输入公式后按【Ctrl+Enter】,OK:=SUM(D2:D9)-SUM(E3:E9)三、填充合并单元格要取消合并单元格并填充内容,我们可以这样操作:首先选中需要操作的单元格,点击取消合并单元格,然后按【Ctrl+G】定位,依旧选择空值后定位。在公式栏输入等号后按方向键的上箭头,再按【Ctrl+Enter】,这样取消合并后形成的空白单元格就填充好了:彩蛋:我们在单元格输入内容后按【Enter】键,光标会跳向下一个单元格,如何让光标还留在原位呢,试试按快捷键【Ctrl+Enter】吧!【Ctrl+Enter】的其他功能有待大家继续探索发现哦~~清丰农商银行:王
2022年6月3日
其他

一对多数据查询,万金油公式请拿好

小伙伴们好啊,今天老祝和大家分享两个数据查询的经典公式用法。先来说说一对多查询。所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。下面咱们就说说一对多查询的典型用法,先看数据源:A~D列是一些员工信息,要根据F2单元格指定的学历,提取出所有“本科”的人员姓名。G2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:=INDEX(C:C,SMALL(IF(B$2:B$11=F$2,ROW($2:$11),4^8),ROW(A1)))&""公式看起来好长啊,不用担心,咱们一点点的拆解。先来看这部分:IF(B$2:B$11=F$2,ROW($2:$11),4^8)IF函数的意思是判断一个条件是不是成立,如果成立返回第二参数,否则返回第三参数。本例中,要判断的条件是B$2:B$11=F$2,如果B列的学历等于F2单元格中指定的“本科”,就返回2~11行对应的行号ROW($2:$11),否则返回4^8的结果65536,最终得到一个内存数组的计算结果:{2;65536;65536;65536;6;65536;65536;65536;10;65536}把这个结果放到示意图中,会更直观:有小伙伴会说:为啥用65536啊,先不要着急,咱们一会儿再说。接下来,再用SMALL函数,在这个内存数组中提取内容。SMALL函数的作用是返回一组数值中的第n个最小值,比如公式SMALL(A:A,3),就是返回A列中的第三个最小值了。本例中,SMALL函数用IF函数的计算结果作为第一参数,要在这个内存数组中提取第n个最小值,这里的n由谁来指定呢?就是公式最后部分的ROW(A1)。ROW(A1)的作用是返回A1单元格的行号,结果是1。当公式向下复制时,参数会依次变成ROW(A2)、ROW(A3)、……,也就是得到从1开始、依次递增的序号。最终的目的是给SMALL函数一个动态的参数,依次从内存数组中提取出第1至n个最小值。咱们回头再看看上面的示意图,SAMLL函数先提取出内存数组中的第1个最小值,结果是2。这个2有啥用呢?继续往下看:下面该轮到INDEX函数出场了,这个函数的作用是根据指定的位置信息,从数据区域返回对应位置的内容。刚刚的2就是位置信息,INDEX函数从C列中返回第二个单元格的内容,结果就是第一个符合条件的姓名“刘一山”。公式向下复制到G3单元格,ROW(A1)变成了ROW(A2),返回A2的行号2,SMALL函数再从内存数组中提取第2个最小值,结果是几呢?再看看上面的示意图,OK,是6。INDEX函数最终再返回C列中的第6个单元格中的内容“王希建”。如果所有符合条件的行号都提取完了,公式还向下复制,这个时候SMALL函数的结果就是65536了,最终INDEX函数返回C列第65536个单元格中的内容。通常情况下,咱们的工作表没有这么多数据,也就是65536是空白单元格,INDEX函数引用空白单元格时,会返回一个无意义的0,所以咱们在公式的最后部分加上一个&"",使无意义的0不再显示。这里的65536可以是其他任意一个较大的数值,只不过人们使用习惯了,只要你高兴,换成63565、65356都没问题。还没有看晕的小伙伴,接下来咱们再说说多对多查询的公式。多对多查询通常分为两种情况:一是要提取出同时符合多个条件的所有记录。二是要提取出多个条件符合其一的所有记录。如下图所示,要提取出性别为“女”,部门为“财务”的所有人员姓名:这里变成性别和部门两个条件了,两个条件要同时符合,公式怎么用呢?H2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:=INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&""这个公式看起来更长了,不用担心,咱们仔细看看,公式中的大部分内容和前面讲过的公式几乎是一样的,有所不同的地方就是这里:($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2)也就是把多个条件分别写到括号内,再用乘号把多个条件对应相乘。只有这几组条件同时符合了,对应相乘后的结果才是1,否则相乘结果是0。在IF函数的第一参数中,0的作用相当于逻辑值FALSE,不等于0的数值则相当于逻辑值TRUE。也就是两个条件同时符合了,就返回对应的行号,否则返回65536。公式的其他部分,和前面讲的第一个公式计算过程都是相同的。多对多查询的第二种情况是多个条件符合其一。比如下图中,要提取性别为“女”或是部门为“财务”的所有人员姓名。可以在G2单元格输入以下公式:=INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)+($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&""这里告诉大家一个处理的小窍门:多个条件分别写到括号内,再用加号把多个条件对应相加就好。这几组条件中只有一个符合了,对应相加后的结果就不是0,如果所有条件都不符合,相加结果才是0。其他计算过程和第二个公式是完全一样的,细节咱们不再展开说了。好了,今天咱们的分享就是这些吧,祝小伙伴们一天好心情!图文制作:祝洪忠
2020年9月11日
其他

从0到1:十招入门Excel数据透视表

计算字段极大扩展了数据透视表的计算功能,属于神一般的存在。比如原始数据表中有一列数据为目标值,有一列数据为实际值,那么在数据透视表中可以通过计算字段输入公式=实际值/目标值,来求出汇总的达成率。
2019年1月5日
其他

红楼外传<Excel版>

宝玉得意洋洋的说:“如果B列等于主人,则计算A列从这一行开始向下的人员个数,下拉之后面的结果又会被前面的公式使用。这样各房从主人算起一共有多少人就出来了”
2018年10月14日
其他

动态图表还能做成这样,你咋不上天呢

2018-02-07课程详情:http://t.excelhome.net/thread-45235-1-1.html点击【阅读原文】报名免费公开课
2018年1月11日
其他

浮点误差,学函数公式必须知道这个梗

用2乘以十进制小数,将得到的整数部分取出,再用2乘以余下的小数部分,然后再将积的整数部分取出。如此往复,直到积中的小数部分为0或者达到所要求的精度为止,最后把取出的整数部分按顺序排列。
2018年1月9日
其他

1小时学会PPT文字使用,让你的PPT美观大方

ExcelHome每日分享excel操作技巧、excel函数公式、透视表、excel图表和VBA教程,助您轻松提高办公效率,搞定数据分析!
2018年1月9日
其他

VBA常用小代码106:自定义排序的三种方法

ExcelHome每日分享excel操作技巧、excel函数公式、透视表、excel图表和VBA教程,助您轻松提高办公效率,搞定数据分析!
2018年1月9日
其他

加班加点加到哭,莫怪领导二百五

如果表格中有多个图片,要选中它们,没那么简单。按Ctrl+G定位“对象”的方法已经过时啦。没有最快,只有更快。只要单击任意一个图片,然后按Ctrl+A,就可以选中全部图片:
2018年1月9日
其他

DLOOKUP听说过吗?VLOOKUP和它赛跑被甩了几条街

1、假设有两个表格:表2存放订单号及部门、类型、金额、日期、负责人、完成状态等相关信息,共100万条记录;表1存放待查找的订单号及金额、完成状态信息,共50万条记录。
2018年1月4日
其他

十个简单实用的Excel技巧,必须收藏!

先用DAY函数,判断A2单元格的日期是几号,然后用LOOKUP函数近似匹配,如果在0~10之间就是上旬,在11~20之间就是中旬,其他就是下旬。
2018年1月4日
其他

美女教图表:智能标记最大值的折线图

ExcelHome每日分享excel操作技巧、excel函数公式、透视表、excel图表和VBA教程,助您轻松提高办公效率,搞定数据分析!
2018年1月3日
其他

LOOKUP入门,不看是你的损失

ExcelHome每日分享excel操作技巧、excel函数公式、透视表、excel图表和VBA教程,助您轻松提高办公效率,搞定数据分析!
2018年1月3日
其他

混合内容中提取数据,原理都有了

进一步,如果这里单单只用SEARCH显然是无法解决问题的,那么就得召唤他的兄弟SEARCHB出场。在文本函数中,有n多带有B的函数,他们的计算都是按照“字节”,而不是“字符”来统计的。
2018年1月3日
其他

8小时,成为VBA高手

ExcelHome每日分享excel操作技巧、excel函数公式、透视表、excel图表和VBA教程,助您轻松提高办公效率,搞定数据分析!
2018年1月2日
其他

学会9个函数,让你“事半功倍”

ExcelHome每日分享excel操作技巧、excel函数公式、透视表、excel图表和VBA教程,助您轻松提高办公效率,搞定数据分析!
2018年1月2日
其他

ExcelHome微课全解读

是由资深务总监郭辉老师专为财务同学准备的进阶培训课,Excel对财务的重要性不言而喻,郭辉老师综合他在Excel与财务方面的经验,旨在帮助财会类职业的同学数倍地提升工作效率
2017年12月31日