Excel如何使用函数计算表格中的数据公式求助

来源：蜘蛛抓取(WebSpider) 时间：2024-02-26 07:30 标签： 如何使用函数计算表格中的数据
和大家分享一个很有趣的Excel公式用法，先来看题目：如下图，表格中记录的是一些户籍信息，A列是“和户主的关系”，B列是人员姓名。要求如果A列是户主，就在C列统计出这一户的人数。
跟着一起来，看看如何优雅地在牛A和牛C之间徘徊。
C2单元格输入公式：=IF(A2=”户主”,COUNTA(B2:B20)-SUM(C3:C20),””)然后双击公式C2右下角的填充柄，OK了：
接下来咱们一起说说这个公式的运算过程：大家都知道，COUNTA函数的作用是用来计数的，SUM函数的作用是用来求和的。本例中，关键的地方是计数的范围和求和的范围。
以C2的公式为例：COUNTA的范围是(B2:B20)，公式使用了相对引用，也就是从当前行的B列开始向下，统计不为空的单元格个数，也就是人员个数。SUM函数的范围是(C3:C20)，注意，这里的求和起始位置，是公式所在单元格C2的下一行，并且也使用了相对引用。如果公式向下复制，COUNTA的范围和SUM函数的范围都会发生变化，而后面的公式结果，又会被前面的公式再次使用。
为了更好的理解运算过程，咱们可以先从C列最后一个单元格的公式开始看，
C18单元格的公式是：=IF(A18=”户主”,COUNTA(B18:B36)-SUM(C19:C36),””)
COUNTA函数计算从B18往下的不为空单元格数量，结果是3。SUM函数计算从C19往下的总和，结果是0。3-0，最终统计结果是3。
再看C17单元格的公式：=IF(A17=”户主”,COUNTA(B17:B35)-SUM(C18:C35),””)
COUNTA函数计算从B17往下的不为空单元格数量，结果是4。SUM函数计算从C19往下的总和，结果是3。
4-3，最终统计结果是1。
其他单元格的统计过程也是这样的原理。这个公式的运算过程比较巧妙，需要慢慢消化才能理解和灵活运用。今天老祝特意为大家准备了练习文件，点击最后的【阅读原文】，就可以直达下载页了。建议大家先收藏一下或是动动手指转到朋友圈，日后可以再对照练习文件操作看看，否则老祝在周日忙活四个小时写的教程就白瞎了呢。学习Excel函数公式，不仅仅是衡量我们会使用多少个函数，更是要看看对基础函数的熟练程度。高手的境界，就是能把简单的函数运用到极致，正所谓：手中无刀，心中有刀。
2022年03月26日 10:40--浏览 ·
--喜欢 ·
--评论关注办公小课堂学习Excel技能，提升办公效率！一、财会必备技能：一般值的重复判断。目的：判断“地区”是否重复。方法：在目标单元格中输入公式：=IF(COUNTIF(E$3:E$9,E3)>1,"重复","")。解读：1、Countif函数是单条件计数函数，作用为计算指定区域中满足条件的单元格个数；语法结构为：=Countif(条件范围，条件)。2、公式：=IF(COUNTIF(E$3:E$9,E3)>1,"重复","")首先用Countif函数统计范围中指定值的个数，然后用IF函数判断，如果值的个数>1，则返回“重复”，否则返回空值。关注办公小课堂学习Excel技能，提升办公效率！二、财会必备技能：大于等于15位值的重复判断。目的：判断身份证是否重复。方法：在目标单元格中输入公式：=IF(COUNTIF(C$3:C$9,C3&"*")>1,"重复","")。解读：1、从示例中可以看出，所有的身份证号并没有重复值，但为什么公式：=IF(COUNTIF(C$3:C$9,C3)>1,"重复","")的判断结果有重复值？因为在Excel中，能最多存储的数据位数为15位，15位之后的数值全部按“0”处理。对比身份证号，发现判断重复的值都是末尾几个值不同，被按照“0”处理，所以显示重复。2、公式：=IF(COUNTIF(C$3:C$9,C3&"*")>1,"重复","")中，在Countif函数的判断条件后面添加了“*”（星号），就能得到正确的结果，是因为添加了“*”（星号）之后将原本的数值强制转换为文本，所以得到了正确的结果。关注办公小课堂学习Excel技能，提升办公效率！三、财会必备技能：提取出生年月目的：从身份证号码中提取出生年月。方法：在目标单元格中输入公式：=TEXT(MID(C3,7,8),"00-00-00")。解读：1、Mid函数的作用为：从指定字符串的指定位置提取指定长度的字符。语法结构为：=Mid(字符串，开始位置，长度)。而身份证号中的从第7位开始，长度为8的字符正好为出生年月。2、Text函数的作用为：根据指定的格式将数值转换为文本。语法结构为：=Text(字符串，格式代码)。3、公式：=TEXT(MID(C3,7,8),"00-00-00")首先利用Mid函数提取出生年月的8位数字，然后用Text函数将其设置为：XXXX-XX-XX的形式。关注办公小课堂学习Excel技能，提升办公效率！四、财会必备技能：计算年龄目的：根据身份证号码计算年龄。方法：在目标单元格中输入公式：=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")。解读：1、Datedif函数为系统隐藏函数，其作用为按照指定的类型计算两个日期之间的差值。语法结构为：=Datedif(开始日期，结束日期，统计方式)，常见的统计方式为：Y：年；M：月；D：日。2、公式：=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")首先提取出生年月，然后和当前的（Today()）的日期相比，计算相差的年份（Y），暨计算出年龄。关注办公小课堂学习Excel技能，提升办公效率！五、财会必备技能：提取性别目的：从身份证号中提取性别。方法：在目标单元格中输入公式：=IF(MOD(MID(C3,17,1),2),"男","女")。解读：1、Mod函数的作用为求余数，语法结构为：=Mod(被除数，除数)。2、身份证号码中的第17位代表的是性别，如果为计数，则为男性，否则为女性。3、利用Mid函数提取第17位，然后用Mod函数求余数，最后IF函数判断，如果为奇数，返回男，否则返回女。关注办公小课堂学习Excel技能，提升办公效率！六、财会必备技能：计算退休时间目的：根据身份证号计算退休时间。方法：在目标单元格中输入公式：=EDATE(TEXT(MID(C3,7,8),"0!/00!/00"),MOD(MID(C3,17,1),2)*120+600)。解读：1、EDAte函数的作用为：返回起始日期之前或之后的月份。语法结构为：=EDAte(起始日期，月份)。2、公式：=EDATE(TEXT(MID(C3,7,8),"0!/00!/00"),MOD(MID(C3,17,1),2)*120+600)中，首先获取出生年月，然后判断性别，如果为男性，则在出生年月的基础上加上720个月（60年），如果为女性，则在出生年月的基础上加上600个月（50年）。暨男同志60岁退休，女同志50岁退休哦！关注办公小课堂学习Excel技能，提升办公效率！七、财会必备技能：合同到期时间目的：根据实际情况，计算合同到期时间。方法：在目标单元格中输入公式：=EDATE(C3,D3*12)。解读：EDAte函数中，第二个参数为月份，而在“签约时长”中的时间为年份，所以需要*12（乘以12）。关注办公小课堂学习Excel技能，提升办公效率！八、财会必备技能：单条件求和目的：按“地区”统计销量。方法：在目标单元格中输入公式：=SUMIF(E3:E9,H3,D3:D9)。解读：Sumif函数的作用为：统计指定范围内符合条件的和值。语法结构为：=Sumif(条件范围，条件，[求和范围])。当条件范围和求和范围相同时，可以省略求和范围。关注办公小课堂学习Excel技能，提升办公效率！九、财会必备技能：多条件求和目的：按“地区”中销量大于50的销量和。方法：在目标单元格中输入公式：=SUMIFS(D3:D9,E3:E9,H3,D3:D9,">"&I3)。解读：Sumifs为多条件求和函数，暨统计符合多条件的下的和值。语法结构为：=Sumifs(求和范围，条件1范围，条件1，条件2范围，条件2……条件N范围，条件N)。关注办公小课堂学习Excel技能，提升办公效率！十、财会必备技能：计算日期所属季度方法：在目标单元格中输入公式：=LEN(2^MONTH(C3))&"季度"。关注办公小课堂学习Excel技能，提升办公效率！十一、财会必备技能：特定条件下的最小值目的：计算指定“地区”的最小销量。方法1：在目标单元格中输入公式：=MIN(IF(E3:E9=H3,D3:D9))。方法2：Ctrl+Shift+Enter填充。解读：由于需要多次判断，所以采用Ctrl+Shift+Enter填充。关注办公小课堂学习Excel技能，提升办公效率！十二、财会必备技能：特定条件下的最大值目的：计算指定“地区”的最高销量。方法1：在目标单元格中输入公式：=MAX(IF(E3:E9=H3,D3:D9))。方法2：Ctrl+Shift+Enter填充。解读：由于需要多次判断，所以采用Ctrl+Shift+Enter填充。关注办公小课堂学习Excel技能，提升办公效率！十三、财会必备技能：特定条件下的平均值目的：按照“地区”计算平均销量。方法：在目标单元格中输入公式：=AVERAGEIFS(D3:D9,E3:E9,H3)。解读：Averageifs函数为：对条件计算平均值函数。语法结构为：=Averageifs(设置范围，条件1范围，条件1，条件2范围，条件2……条件N范围，条件N)。关注办公小课堂学习Excel技能，提升办公效率！十四、财会必备技能：多条件计数目的：按“地区”统计销量大于50的笔数。方法：在目标单元格中输入公式：=COUNTIFS(E3:E9,H3,D3:D9,">"&I3)。解读：Countifs函数为多条件计数函数，语法结构为：=Countifs(条件1范围，条件1，条件2范围，条件2……条件N范围，条件N)。关注办公小课堂学习Excel技能，提升办公效率！十五、财会必备技能：多条件求和目的：按“地区”统计销量>50的和值。方法：在目标单元格中输入公式：=SUMPRODUCT((E3:E9=H3)*(D3:D9>I3)*D3:D9)。解读：Sumproduct函数的作用为：返回对应区域元素乘积的和值。语法结构为：=Sumproduct(区域1，区域2,……区域N)。关注办公小课堂学习Excel技能，提升办公效率！十六、财会必备技能：查询引用目的：查询销售员的销量。方法：在目标单元格中输入公式：=VLOOKUP(H3,B3:D9,3,0)。解读：Vlookup函数为查询匹配函数，语法结构为：=Vlookup(查询值，查询范围，返回值列数，匹配模式)。关注办公小课堂学习Excel技能，提升办公效率！

我要回帖

更多关于 如何使用函数计算表格中的数据 的文章

 

随机推荐