加载中…【转】Excel Sumifs、Offset公式等
(2013-02-19 20:59:17)
标签：
sumifsumifsoffsetindirectexcel |
分类： BI_Excel |
国外的哥哥们很喜欢在Excel里头使用眼花缭乱的offset，indirect之类的函数。这里，设计一个简单示例，分析一下引用类查找函数在Excel中如何使用。
示例1：如表一所示，如何统计出active状态的订单金额，我们有几个方法：
表一
1.基本方法：Sumif
语法：SUMIF(range, criteria,
[sum_range]) = SUMIF(条件区域, 条件,
[求和区域])
=SUMIF($B$2:$B$8,"active",$C$2:$C$8)
若B列的Status为active，则Amount(C)列相加。
2.再多个条件求和怎么办？Sumifs
若需要按多个条件求和，还可以使用sumifs函数
将上述公式修改为：
=SUMIFS($C$2:$C$8,$B$2:$B$8,"active")
语法：SUMIFS(sum_range,
criteria_range1, criteria1, [criteria_range2,criteria2], …) =
SUMIFS(求和区域, 条件区域1, 条件1, [条件区域2,条件2], …)
3.使用Offset函数简化2.)中的单元格引用
一般，可以使用Offset函数简化单元格区域的引用，我们还可以将上述公式修改为
=SUMIFS(OFFSET($A$2:$A$8,0,2),OFFSET($A$2:$A$8,0,1),"active")
语法：
OFFSET(reference,rows,cols,height,width) =
Offset(引用区域，向上/下偏移，向右/左偏移，行高，列宽)
例如：OFFSET($A$2:$A$8,0,2)
指A2开始向右偏移2格，即C列Amount，若height,width省略，则行高、列宽等同于引用区域，即行高为7，列宽为1，返回的区域为C2:C8
4.除了Offset还有哪些引用函数可用的？Indirect
我们还可以使用Indirect函数进一步简化引用或实现动态引用，将上述公式修改为
=SUMIFS(OFFSET(INDIRECT($D$10),0,2),OFFSET(INDIRECT($D$10),0,1),"active")
语法：INDIRECT(ref_text,a1)，按文本返回指向的区域
例如：INDIRECT($D$14)，返回D14列所指的区域，即A2:A8区域，Order（A）列。
Indirect更好的一个应用示例，如图二所示，实现城市下拉框按省份动态显示，例如，选择江苏省，显示江苏省所有的城市，简化用户交互。如何实现？卖个小关
，最简单的方法：命名 + 数据有效性 + indirect函数
图二
喜欢
0
赠金笔