Excel的公式技巧3

1.23 数组的应用
（1）数组公式的实现方法：
其实这些都是数组公式，数组公式的输入方法是将公式输入后，不要直接按回车键（Enter），而是要同时按Ctrl Shift Enter，这时电脑自动会为你添加“{ }”的。
在论坛上，为了告诉大家这是数组公式，故在公式的头尾都加上了“{ }”。如果不小心按回车了，可以用鼠标点一下编辑栏中的公式，再按Ctrl Shift Enter。
编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消失，然后编辑公式，最后按Ctrl＋Shift＋Enter键。选取数组公式所占有的全部区域后，按Delete键即可删除数组公式。
数组常量的使用数组公式中还可使用数组常量，但必须自己键入花括号“{ }”将数组常量括起来，并且用“，”和“；”分离元素。其中“，”分离不同列的值，“；”分离不同行的值。
2、数组公式的原理：
数组公式，说白了就是同时对一组或几组数同时处理，然后得到需要的答案。运用数组公式的最重要的原理是数于数之间一一对应。
1、假设要将A1：A50区域中的所有数值舍入到2位小数位，然后对舍入的数值求和。很自然地就会想到使用公式：=ROUND（A1，2）＋ROUND（A2，2）＋…＋ROUND（A50，2）。或者添加ROUND辅助列（A1=ROUND（A1，2）），然后对辅助用SUM函数合计（=SUM（A1：A50））。
如果用数组公式就不要这么麻烦，公式为：
{=SUM（ROUND（A1：A50，2））}，它的意思即为在数组A1：A50用ROUND函数进行二位小数的四舍五入，然后进行合计。
2、假设一题为A1：A10区域中为商品单价，B1：B10为对应的销售数量，需要统计总销售额，常规做法需要添加辅助列C列，在C列中计算出C1：C10的每个单价的销售额（C1=A1*B1），然后进行SUM合计（C11=SUM（C1：C10））。
而数组公式为：
{=SUM（A1：A10*B1：B10）}
3、注意：关于常数项的数组可以直接手工添加{ }，如此公式 = SUM({1,2,3} {4,5,6}），这也是数组公式的一种形式。
需要统计如下图所示销量的频率分布，即分别统计销量在5000以下、5000到10000、10000到50000以及大于50000的销售点数量
a2b2C2
销售点 销售额 分段点
城北001 4100 5000
城北002 15890 10000
城南001 8700 50000
城南002 25900
城南003 5800
城东001 15300
城东002 38000
城东003 9800
城西001 56000
城西002 72050
城中001 130000
城中002 60400
城中003 48700
步骤：
"1、制作如上图所示的表格
2、选中单元格G7：G10，直接输入公式：=FREQUENCY（B4：B14，c4：c6）
3、输入公式后，按CTRL SHIFT ENTER键结束
"
类型 日期 单价 销售数量
A 2005-6-15 1000 10
B 2005-6-20 1000 15
B 2005-7-1 4000 10
C 2005-7-10 4000 11
B 2005-8-15 9000 13
C 2005-8-20 9000 15
A 2005-9-30 1000 14
A 2005-10-10 1000 20
B 2005-10-15 4000 25
类型从B1格开始
计算B产品8月份销量
13{=SUM(IF(($B$2:$B$10="B")*(MONTH($C$2:$C$10)=8),($E$2:$E$10),0))}
13 {=SUM(($B$2:$B$10="B")*(MONTH($C$2:$C$10)=8)*($E$2:$E$10))}
计算A产品和B产品的销量
107 {=SUM(IF(($B$2:$B$10="A") ($B$2:$B$10="B"),($E$2:$E$10),0))}
107 {=SUM((($B$2:$B$10="A") ($B$2:$B$10="B"))*($E$2:$E$10))}
计算8月份前不包括B产品销量和8月后不包括C产品销量
49 {=SUM(IF(((MONTH($C$2:$C$10)<8)<>($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)<>($B$2:$B$10="C")),$E$2:$E$10))}
49 {=SUM(IF(((MONTH($C$2:$C$10)<8)-($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)-($B$2:$B$10="C")),$E$2:$E$10))}
49 {=SUM(((MONTH($C$2:$C$10)<8)<>($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)<>($B$2:$B$10="C"))*$E$2:$E$10)}
以上公式中*的意思为AND, 的意思为OR,-的意思为<> "不等于"
1.24 求一个单元格数值中的最大数字和个数字之和
我们平时都是对不同单元格之间的数字进行计算，但是在一个单元格内部，各数字之间有什么关系？这是一个很有创新意识的命题。例如A1中的数字为389732，求其中最大的数字9，求这和6个数字之和为32。
（1）求其中最大的数字，利用数组公式：
{=MAX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)}
先输入=MAX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)，再按Ctrl Shift Enter。
（2）求其中数字之和，利用下面的公式：
=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)
1.25 逻辑函数的非逻辑表现
例如，求取范围Data中小于0或大于5的数值之和:
正确用法：
{=SUM(IF((Data<0) (Data>5),Data))}
错误用法：
{=SUM(IF(OR(Data<0,Data>5),Data))}
1.26 在EXCEL的数组公式中ROW函数的用法
在EXCEL的数组公式中，ROW()是一个非常有用的函数，现在举个例子来说明。
（1）返回一列中最后一个数值
{=INDEX(A:A,MAX(ROW(A1:A100)*(A1:A100<>"")))}
在这个公式中用ROW函数返回A1:A100<>""即A1格到A100中不为空的单元格，它是一组数据，然后用MAX确定最大的一个行号，即最后一格不为空的单元格，然后用INDEX，来返回A1到A100中A列最大行号的那个数据。
（2）同理如果要返回一行中最后一个数值则为
{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))}
（3）下面出一个小题目，如果有兴趣想学数组的可以试一下，返回A列100行中最后一个有数值的行号的公式是什么？
{=MAX(IF(A1:A100<>"",ROW(A1:A100),""))}
1.27 返回最大值的行号和地址
返回最大值的行号：
{=min(if(A1:A100=max(A1:A100),row(A1:A100),"")}
返回最大值的地址：
{=ADDRESS(MIN(IF(A1:A100=max(A1:A100),ROW(A1:A100),"")),COLUMN(A1:A100))}
{=MAX(IF((A1:A100<>"")*ISNUMBER(A1:A100),ROW(A1:A100),""))}
1.28 Excel常见错误及解决办法
经常用Excel可能都会遇到一些错误值信息，如：# N/A！、#VALUE！、#DIV/O！等等，出现这些错误的原因有很多种，如果公式不能计算正确结果，Excel将显示一个错误值，例如，在需要数字的公式中使用文本、删除了被公式引用的单元格，或者使用了宽度不足以显示结果的单元格。以下是几种常见的错误及其解决方法。
（1）#####！
原因：如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#####！错误。
解决方法：如果单元格所含的数字、日期或时间比单元格宽，可以通过拖动列表之间的宽度来修改列宽。如果使用的是1900年的日期系统，那么Excel中的日期和时间必须为正值，用较早的日期或者时间值减去较晚的日期或者时间值就会导致#####！错误。如果公式正确，也可以将单元格的格式改为非日期和时间型来显示该值。
（2）#VALUE!