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

2017年10月28日21:47:31 评论 12,742 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

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

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

01、错位求和:SUMIF

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

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

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

这函数烂大街了??

是时候来点花活了!!

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

用法延伸:

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

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

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

02、多条件求和:SUMIFS

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

基本用法:

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

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

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

03、BUG函数:SUMPRODUCT

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

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

万能求和公式:

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

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

04、可见单元格求和:SUBTOTAL

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

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

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

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

05、颜色求和:GET.CELL

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

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

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

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

24,首字符的字体颜色;

63,单元格填充颜色

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

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

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

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

其中,

GET.CELL用来提取颜色

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

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

06、大道至朴:SUM

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

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

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

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

07、点睛之笔:SUM+IF

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

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

如何做这样的条件求和?

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

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

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

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

Excel中看似简单但是非常实用的一组常用组合公式技巧分享

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

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

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

发表评论

您必须才能发表评论!