泰勒公式中EXCEL长公式之小整理

EXCEL长公式之小整理

2009-03-27 12:02:54| 分类： 理财 |举报 |字号大中小 订阅
在与网友的交流中，发现EXCEL应用中很 多时候要用到长公式，而长公式
的设计很多时候不能一蹴而就。现就交流中得出的部分公式作一整理，以 备后用。

1、排名
我们知道EXCEL的排名函数是RANK()，但它遇 到相同的成绩后，下面就会“跳
跃式”排名。那么怎么才能不跳呢？我几乎没多想，就下了个结论：一个 公式实
现是不可能的。悟空师弟告诉他用了多个辅助列，实现了，然后给我讲了他的思
路。然后 我觉得依照他的也许能做出个公式来解决这个问题。于是一天、两天……
终于，有结果了，公式范例 如下：
=SMALL(IF(LARGE(IF(COUNTIF(OFFSET($$A$$1,0, 0,ROW($$1:$$10),1),$$A$$1:$$
A$$10)=1,$$A$$1:$$A$$10,0),R OW($$1:$$10))=A1,ROW($$1:$$10)),1)
是个数组公式，需要按[Shift]+[Ctrl]+[Enter]完成输入。
虽然是 本人辛苦所得，但不推荐用这个公式，因为悟空师弟遇到高人，得到一个
数组公式，如下：
=SUM(IF($$A$$1:$$A$$10>A1,1COUNTIF($$A$$1:$$A$$10,$$A$$1:$$A$$ 10)))+1
嚯嚯，相当的精简哦。出于运行效率的考虑，我修改如下：
=SUM( IF($$A$$1:$$A$$10>=A1,1COUNTIF($$A$$1:$$A$$10,$$A$$1:$$A$$10)) )

※ 见到了另一种排名需求：同一分数的要分配不同名次。设计公式如下：
=RANK(A1,A:A)+COUNTIF($$A$$1:$$A1,A1)-1
此公式不是数组公式，能将相同分数的按出现的先后次序赋以不同名次。

2、找自然数序列中缺失的数
方法一、用普通公式
=IF(COUNTIF(A:A,ROW(1:1))<1,ROW(1:1),

该方法优点：方便输入，执行效率高。
该方法缺点：会生成空行，使得到的数据结果不连续。

方法二、用数组公式
=SMALL(IF(COUNTIF(A:A,ROW($$A$$1:$$A$$8))<1,ROW($$A $$1:$$A$$8),
1:1))

该方法优点：不会生成空行，得到的数据结果是连续显示的。
该方法缺点：输入相对麻烦， 且执行效率低，尤其是$$A$$8改为更大时，如$$A$$2000,
将会在更新数据时明显感到很卡。

3、九九乘法表
一、若从A1开始,可用以下公式:
=IF(COLUMN()>ROW(),×
COLUMN()*ROW())

二、以下公式可从任意位置开始拖：
=IF(COLUMN(A1)>ROW(1:1), ×
COLUMN(A1)*ROW(1:1))

4、金额转人民币大写
=TEXT(INT(B2),元

角零

&分整

5、随机编号

A1用最简单的公式 =int(RAND()*99)+1
A2开始用数组公式
=LARGE(IF(COUNTIF($$A$$1:$$A1,ROW( $$A$$1:$$A$$99))=0,ROW($$A$$1:$$A$$99),
0),INT(RAND()*( 100-ROW())+1))


这样可以在A1~A99随机无重复地填入1~99之间的数。

6、提取不重复的值（即：重复的值只提取一次）
这个公式也花了我不少周折，以下是数组 公式范例，从A1~A20中提取不重复的
值，有个缺憾，就是拖公式时的最后单元格是未知的，不过， 还是能控制的，当
拖到末尾出现重复数据时就表示该停了。
=INDEX($$A:$$A,S MALL(IF(MATCH($$A$$1:$$A$$20,$$A:$$A,0)=ROW($$1:$$20),ROW($$1:$$20),21),ROW(1:1)))
其中$$A:$$A为原始数据所在列, $$ A$$1:$$A$$20为原始数据有效区域,ROW($$1:$$20)
为原始有效数据所有行号,21是大 于有效数据最大行号的任意数字. 有效数据区
域不能有空单元格.

为了处理原始数据区域中可能含有空单元格的情况，公式改进如下：
=INDEX($$A:$$ A,SMALL(IF(IF(ISNA(MATCH($$A$$1:$$A$$20,$$A:$$A,0)=ROW($$ 1:$$
20)),FALSE,MATCH($$A$$1:$$A$$20,$$A:$$A,0)=ROW($$1 :$$20)),ROW($$1:$$20),21)
,ROW(1:1)))


7、四舍六入五单双
有网友提问：
修约为两位小数
0.3330→0.33 第三位小于5，第二位不加1
0.3340→0.33 第三位小于5，第二位不加1
0.3360→0.34 第三位小于5，第二位不加1
0.3350→0.34 第三位等于5，第四位为零，第二位是单数（奇数），第二位加1
0.3450→0.34 第三位等于5，第四位为零，第二位是双数（偶数），第二位不加
1
0.3456→0.35 第三位等于5，第四位不为零，第二位加1

我想这应 该是会计要处理的问题，有点实用价值。因此我把我的解决方案也贴到
这里。看官正好是会计的话，不妨 留心一下。
=IF(MOD(INT(A1*1000),5)=0,IF(MOD(INT(A1 *100),2)=0,IF(MOD(INT(A1*100
00),10)=0,ROUNDDOW N(A1,2),ROUND(A1,2)),ROUND(A1,2)),ROUND(A1,2
))