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

2017年10月28日21:47:31 评论 13,375 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
微信公众号 【EXCEL880】 QQ群【273774246】

这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 悟空问答答案

微信公众号 【EXCEL880】 QQ群【273774246】
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

发表评论

您必须才能发表评论!