• 注册
  • 推广免费拿钻石

    • 查看作者
    • Excel函数很难学?掌握这8个必备场景就够了!多表求和 隔列求和 图文

      对于很多刚入门Excel的表亲来说,函数公式永远都是最难学的。原因有两个:

      • 第一,不知道这种场景有没有函数可以解决?也不知道该用什么函数。

      • 第二,即使知道了可以用函数,也搞不清楚函数公式的用法。

      所以,有不少人刚接触函数公式的时候,都是通过死记硬背的方式,把函数用法给记下来的。解决常用的工作场景的确没有问题,但场景一变,公式就失效了。

      要学好函数公式,透彻掌握它的用法,要求的是对函数的理解和灵活运用。但总有那么一些函数场景,只要掌握了,就能解决工作中大部分的问题。

      今天给大家带来了8个必备的Excel函数场景,只要认真记起来,相信能帮助你解决不少麻烦,但还是要提醒大家一句,记住场景虽然重要,但是灵活运用才是王道。

      01 多工作表快速汇总求和

      1-3月的产品数据分别记录在三个工作表里面,而工作表的格式是一样的。我们可以通过SUM函数,使用星号匹配符的方法实现多工作表的快速汇总。

      选中汇总表中的单元格区域,在B2单元格输入公式【 =SUM('*'!B2) 】,按键盘Ctrl+ENTER确定输入,即可实现多工作表的快速汇总。

      02 按条件隔列求和

      在工作表中,包括两个产品1-3月的销售数据,要分别统计出产品1和产品2的销售合计。由于产品数据是隔列存放的,我们可以通过SUMIF函数按条件隔列求和。

      在H3单元格输入公式

      【 =SUMIF($B$2:$G$2,H$2,$B3:$G3) 】,求出产品1院长三个月的销售合计为659,然后再填充公式,汇总其他结果。

      由于要考虑公式复制时的填充方向,这里第二、三个参数都使用了相对引用,保证公式复制后的正确性。

      03 按模糊条件求和

      在工作表中要统计出鼠标的销售总金额,由于商品名称中每种鼠标都有具体的型号,我们可以通过SUMIF函数,配合星号匹配符的方法实现按模糊条件求和。

      在D2单元格输入公式【 =SUMIF(A2:A11,"鼠标*",B2:B11) 】,鼠标后面的型号代表模糊条件,求出总金额为500。

      04 多条件求和

      在工作表中要统计出销售门店为A,单笔销售金额大于3500的销售总和,这里包含了两个条件,可以通过SUMIFS函数实现多条件求和。

      在E2单元格输入公式

      【 =SUMIFS(C2:C11,A2:A11,"A",C2:C11,">3500") 】,求和列为C列,分别输入两个条件销售门店和单笔销售金额,得到销售总和为17635。

      05 按条件查找引用数据

      在工作表中通过选择员工的姓名,即可得到员工所属部门、职位、基本工资等信息,可以使用VLOOKUP函数实现数据的查找和引用。

      在B9单元格输入公式

      【 =VLOOKUP($A$9,$B$1:$G$6,COLUMN(B1),0) 】,根据A9单元格所选择的姓名,查找出员工所属的部门,往右填充公式,查找出员工其他信息。

      公式中的第三个参数使用了COLUMN函数,用于取列号,B1所在列号为2,所以得出是所属部门,往右填充时,分别取第3、4、5列,得到其他员工信息。

      06 多条件交叉查询

      在工作表中每个地区分别有1-3月的销售数据,通过地区和月份的选择,快速查询出对应的销售额,可以使用Index+Match的函数组合实现。

      在G3单元格输入公式

      【 =INDEX(B2:D8,MATCH(G1,A2:A8,0),MATCH(G2,B1:D1,0)) 】,MATCH函数分别匹配地区和月份,配合INDEX函数实现交叉查询,取出所在行列的销售金额。

      07 检查身份证号码是否重复

      在同一列中要检查出身份证是否重复,可以使用COUNTIF函数统计身份证出现次数,再使用IF函数做重复判断。

      在B2单元格输入公式

      【 =IF(COUNTIF($A$2:$A$11,A2&"")>1,"重复","") 】,公式往下复制填充,查找出身份证是否重复。

      由于身份证长度达到18位数,系统会默认地把它当成是科学计数法,所以很多人在做重复性判断时会发现,明明两个身份证是不一样的,但公式判断出来的结果却是重复的,为了避免这种情况,公式中会在单元格后面连接一个空文本。

      08 根据权重计算最终得分

      每位员工都有四个考核项,而每个考核项都对应有权重比,要根据每个考核项和权重得出最终的得分,可以使用SUMPRODUCT函数快速计算。

      在F3单元格输入公式

      【 =SUMPRODUCT($B$2:$E$2*B3:E3) 】,SUMPRODUCT函数实现两个数组之间,先相乘后相加的计算,得出总分为5.8分。

    • 0
    • 0
    • 0
    • 3
    • 单栏布局 侧栏位置: