• 注册
  • 你转发,我送钱!

    • 查看作者
    • Excel中看似简单但是非常实用的一组常用组合公式技巧分享 图文

      这8个条件求和公式没用过,别说你会Excel!

      我来给你讲一组求和公式。工作中,我们会经常对数据进行求和,出于不同的需求会按不同的条件去汇总。为此,Excel为我们提供了多种求和方式,例如数据透视表,再例如分类汇总,当然也少不了函数。

      01、错位求和:SUMIF

      作为科班出身,SUMIF身上流淌着最正宗的条件求和之血。

      =SUMIF(条件区域,条件,求和区域)

      SUMIF函数的基本用法想必小伙伴们都已经是信手拈来。

      这函数烂大街了??

      是时候来点花活了!!

      看例子,如此排列的一张表怎么条件求和?

      SUMIF,专治各种错位求和,不服来辩!

      用法延伸:

      ① 当遭遇隔行求和难题,用SUMIF可破逐一相加的困境。

      ② SUMIF函数在通配符的协助下,还可以实现模糊查找

      " * ":任意个字符 “ ? ”:单个字符

      02、多条件求和:SUMIFS

      作为SUMIF函数的加强版,07及以上版本Excel为我们提供了多条件求和函数SUMIFS。

      基本用法:

      =SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2……)

      如图,求解不同月份不同部门的预算之和:

      SUMIFS与SUMIF非常接近,不再赘述。

      03、BUG函数:SUMPRODUCT

      要论最BUG最不讲道理的EXCEL函数,SUMPRODUCT绝对是排得上号。

      在条件求和方面,它也能露一手。

      万能求和公式:

      =SUMPRODUCT((条件区域1=条件1)*(条件区域N=条件N)*(求和区域))

      这个函数比较难掌握。不怕不怕,小花曾用三个篇幅精讲过这个函数(文末有链接),大家可以去复习下哦。

      04、可见单元格求和:SUBTOTAL

      如果你对数据进行了筛选,要如何计算筛选后的单元格之和呢?这时你需要用SUBTOTAL来完成(SUBTOTAL可以忽略因筛选而隐藏的值)。

      =SUBTOTAL(功能代码,求和区域1,求和区域2……)

      其中,求和用到的功能代码是9和109。

      (区别:当有隐藏单元格时,9包含隐藏值求和,109忽略隐藏值求和)

      05、颜色求和:GET.CELL

      Excel对颜色处理的能力一直很薄弱,好在老版本还遗留的一个宏表函数–GET.CELL可以用来做颜色条件求和。

      GET.CELL可以返回应用单元格的信息,其基本用法如下:

      =GET.CELL(信息类型,引用单元格或区域)

      其中,信息类型用数字表示,范围为1-66,我们做颜色求和会用到两个数字:

      24,首字符的字体颜色;

      63,单元格填充颜色

      遗憾的是,这个函数只能在定义名称中使用,无法直接在单元格中使用、

      以按字体颜色(信息类型24)求和为例

      Step1: 定义名称,构建提取颜色对应的值的工具

      =GET.CELL(24,B2)+0/NOW()

      其中,

      GET.CELL用来提取颜色

      B2表示引用当前单元格C2左边的单元格,因此必须选中C2后做定义名称

      0/NOW()保证宏表函数即时更新。

      Step2:利用定义好的名称,提取颜色条件区域对应的颜色值,形成辅助列

      Step3:运用SUMIF函数做条件求和

      06、大道至朴:SUM

      所谓万变不离其宗,SUM作为求和的本家,在一些高阶函数或数组中,有时比SUMIF更受欢迎。

      比如同样是条件求和,使用SUM函数也可以实现:

      {=SUM((条件区域N=条件N)*(求和区域))}

      (数组公式,需要按Ctrl+Shift+Enter三建输入)

      07、点睛之笔:SUM+IF

      相比于SUM的数组应用,SUM+IF组成的数组公式具有更多变化,应用面更广,更为强大!

      例如,我们在预测销额时,采用这样的原则:如果实际已发生,取实际发生额,否则取预算发生额。

      如何做这样的条件求和?

      {=SUM(IF($B$3:$G$3>0,$B$3:$G$3,$B$2:$G$2))}

      08、最值求和:SUM+LARGE/SMALL

      求最大或最小的几个数之和,怎么破?

      LARGE(最大)或SMALL(最小)函数显神功。

      看吧,这些都是最简单的函数,放平时你应该都会用,但是组合成这些公式之后,你能熟练使用么?

      原文来自 头条号 精进Excel 悟空问答答案

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