在自动筛选时，会按实际数据进行大小对比，比如，0.0999999，当筛选条件为大于等于0.1时，这个数据无法被筛选出来，按0.0999999<0.1对待。
简单引用，使用Indirect($a$1)；
表示列号和行号的方式同时为文本时，使用”a1”样式；
不同时为文本，比如一个是文本一个为引用（或函数），或者两者都是引用（或函数），可以用连字符&，连接列号和行号，如”a”&b1；”a”&row()；a1&”2”；a1&row()。
如：需要引用a1的值，则为=indirect(“a1”)，如果需要使用a1单元格内的内容作为参数，则=indirect(a1&a2),其中，a1中应为a、b等字母，a2中应为数字。
Reference 对一个或多个单元格区域的引用，格式为：(A1:B4,D1:E4,G1:H4)，然后利用area_num参数表示需要返回的区域数，用1、2、3等数字表示。
=index(a:a,1)表示a1单元格。
=index(1:1,2)表示b1单元格。
abs_num：1或省略，绝对引用；2 绝对行号，相对列标；3 相对行号，绝对列标；4 相对引用
indirect可以返回单一的单元格，没有数组功能
index函数的行号和列号是相对于引用范围而言的
address利用列号和行号就可以引用某个单元格，这里的行号和列号是相对于整个表格的
因为在trim公式中，两个字符之间有一个单元格的ASCII码值为160，而其他普通空格是32。
=trim()。
需使用substitute函数，先替换出里面的保留空格。
char(32)普通空格
char(160)特殊空格
CLEAN 函数被设计为删除文本中7位ASCII码的前32个非打印字符（值为 0 到 31）。在Unicode字符集（Unicode：Unicode Consortium 开发的一种字符编码标准。该标准采用多（于一）个字节代表每一字符，实现了使用单个字符集代表世界上几乎所有书面语言。）中，有附加的非打印字符（值为 127、129、141、143、144 和 157）。CLEAN函数自身不删除这些附加的非打印字符。
clean可以清除0到31的非打印符号，所以可用以下公式替换单元格中所有的空格变成非打印符号以清除空格。其中的7可以换成0到31中的任意数。
=clean(substitute(a1,char(32),char(7)))。
=REPLACE(D2,FIND("-",D2)-1,1,MID(D2,FIND("-",D2)-1,1)+2)。
2) substitute()替换掉指定字符：=SUBSTITUTE(A1,"123","456",3)。第三个参数用来确定替换掉第几次出现的指定字符，例中指第三次123，用456替换。如不指定，将替换所有的123。
例如：随机生成身份证号码中的月、日
=REPLACE(A1,IF(LEN(A1)=15,9,11),4,TEXT(INT(RAND()*("12-31"-"1-1")),"MMDD"))
利用文本函数得日期格式，返回由rand函数随机产生日期，由替换函数replace替换原来的日期数据。
REPLACE(old_text,start_num,num_chars,new_text)
第二个参数确定从原文本的第几个字符开始搜索。
这里用replace就是因为，它利用旧数据在原文本中的相对位置为依据，替换掉指定数目的数据，符合本题只能确定替换文本的相对位置的要求。
SUBSTITUTE(text,old_text,new_text,instance_num)
SUBSTITUTE函数是利用查找到的文本出现的次数为依据来替换的，比如a在abcabc中出现两次，那么如果只替换第二次出现的a为d，那么函数可以用=SUBSTITUTE("abcabc","a","d",2)，返回值为abcdbc。当确切知道要替换的旧数据出现的次数时，用此函数。当然，如果不指定第四个参数，那么它将替换掉所有符合条件的数据。
在C1中输入下面公式，找出b中有a中也有的，每有一个重复增加1。
=countif(a:a,b1)
注：countif第一个参数没法对直接输入或者通过引用计算得到的数组进行操作，必须是单元格区域才行。
Countif第二个参数可以设置通配符，但是查找范围内的格式就必须设置成非数字格式。
最后一个参数为0时，可以设置通配符查找。默认为1，这时需对查找区域排序，否则可能出现错误。
要执行此任务，请使用 ROUNDUP、EVEN 或 ODD 函数。
ROUNDup(number,num_digits) 远离零值，向上（绝对值增加的方向）舍入数字。
EVEN 或 ODD都是向上舍入到最接近的偶数或奇数。即返回沿绝对值增大方向取整后最接近的偶数（奇数）。
ROUNDDOWN(number,num_digits) 靠近零值，向下（绝对值减小的方向）舍入数字。
与int功能类似，区别在于，（1）负数时，int远离0舍入，rounddown接近0舍入；（2）它功能更强大一点，可以指定舍入的位数，即规定舍入到小数点前后几位。
INT(number)将数字向下舍入到最接近的整数。
注意，负数舍入时也是向下，即远离0的方向，而正数时是接近0的方向。
ROUND(number,num_digits) 返回某个数字按指定位数取整后的数字。按绝对值四舍五入。
TRUNC(number,num_digits) 函数 TRUNC 直接去除数字的小数部分，而函数 INT 则是依照给定数的小数部分的值，将其四舍五入到最接近的整数。函数 INT 和函数 TRUNC 在处理负数时有所不同：TRUNC(-4.3) 返回 -4，而 INT(-4.3) 返回 -5，因为 -5 是较小的数。
要执行此任务，请使用 ROUND、ROUNDUP、ROUNDDOWN、LEN 和 INT 函数。
MROUND(number,multiple)将第一个参数四舍五入到最接近第二个参数倍数的数，如果数值 number 除以基数的余数大于或等于基数的一半，则函数 MROUND 向远离零的方向舍入。
CEILING(number,significance) 将参数 Number 向上舍入（沿绝对值增大的方向）为最接近的 significance 的倍数。无论数字符号如何，都按远离 0 的方向向上舍入，但是第一个参数和第二个参数的符号必须保持一致，与他相反的函数是FLOOR(number,significance)。
=SUMPRODUCT(--(COUNTIF($A$1:$A$10,$A$1:$A$10)=COLUMN(A:A)))/COLUMN(A:A)
输入到b1，向右拖拉，依次能够得到出现1、2、3次等等的数据统计。这里，用COLUMN(A:A)来控制统计的次数，以便实现向右拖拉时可以自动增加。
比如1234，和4567，其中在出现一次一栏中显示123567等几个数字。
需借助辅助列，在e:l行里面输入公式
=MID($A2,COLUMN(A:A),1)
拖拉到所有区域，最大可以统计8位数据。
然后下面为主公式。
=SUMPRODUCT(LARGE(IF(FREQUENCY(LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF(($E$1:$L$1000)="",0,$E$1:$L$1000),ROW(1:10)),LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF(($E$1:$L$1000)="",0,$E$1:$L$1000),ROW(1:10)))=0,0,LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF(($E$1:$L$1000)="",0,$E$1:$L$1000),ROW(1:10))),ROW(1:10))*10^(ROW(1:10)-1))
上面公式输入到出现一次的栏里面，然后向右拖拉，一次可以显示出现两次、三次，等等。
[=1]"早餐";[=2]"午餐";"晚餐"
如需要取10±7内的随机数，函数：
=10+(rand()-rand())*7
如果10+7不包含10，可以写成：
=17-rand()*7
如果是10±7内的随机数，不包含10，
其中odd为四舍五入取奇数（无论数字符号如何，都按远离 0 的方向向上舍入。），目的为了得到正负一。
=10+ODD(RAND()-RAND())*(1-RAND())*7
或者：=10+ODD(RANDBETWEEN(-1,1))*(1-RAND())*7
=IF(ISNUMBER(A1),A1+2,TEXT(LEFT(A1,3)+2,"000")&"-"&TEXT(RIGHT(A1,3)+2,"000"))
如果有8位数字，则需要进行为数判断，使用find()函数
效果为
A |
1 |
11 |
111 |
Aa |
依此类推。
=OFFSET(Sheet2!$A$1,INT((ROW()-1)/5),MOD(ROW()-1,5))
然后向下拖拉
效果为：
a |
b |
c |
1 |
2 |
3 |
d |
e |
f |
4 |
5 |
6 |
g |
h |
i |
7 |
8 |
9 |
公式
=OFFSET(Sheet3!$A$1,COLUMN(A:A)-1+3*INT((ROW(1:1)-1)/2),MOD((ROW(1:1)-1),2))
先向右拖拉3列，再向下拖拉
例一：
有四列数据，如A B C D，A与B是对应的，C与D是对应的
要求当C=A时，D=B列中“X”的个数
问题补充：B列有“X”，有的没有，只用统计"X"的个数！不是显示“X”
比如
A B C D
1 X 1
2 X 2
3 3
1 4
2 X 5
1 X
2 X
3 X
现在要D中显示“1”对应的“X”有多少个
我的回答: D1 =COUNTIFS(B:B,"=X",A:A,C1)此公式的缺点是只能用在office2007以上的版本。
别人的回答:D1=SUMPRODUCT(($A$1:$A$100=C1)*($B$1:$B$100="X"))
例二：
计算出A列姓名如"张三"，在B列状态如所有显示“完成”的行项目数.
用countifs。A2为张三。
C2=COUNTIFS(A:A,A2,B:B,"完成")
用sumproduct函数：=SUMPRODUCT((a2:a100="张三")*(b2:b100="完成"))
解析：countifs是多条件统计，第一个参数是范围一，第二个参数是条件一，第三个参数是范围二，第四个参数是条件二，一次类推，可以多条件选择统计。此函数在2003版本中没有。
Sumproduct函数是返回对应的数组元素的成绩的和，即{1，2，3}{4，5，6}，结果是1*4+2*5+3*6=32。
这里用来多条件统计的原理是，把每个条件作为乘积的因子，函数用数组来处理，和对应区域中的每个单元格对比，如果条件成立时，返回true，如果不成立返回false，这样得到一对数组{false，true，false}{true，true，false}，接着进行计算false*true+ truetrue+false*false=0+1+0,因为两个逻辑函数相乘，只有当两个都是true的时候乘积为1，否则都为0，依此计算出来的结果，就是这些条件都满足的单元格的行数。
因此上述两行的数组元素不但要维数相等，而且一定要对应。即选择范围是要在行间对应，否则求出的值就会出错。如条件一从A1到A10，条件二从B2到B11，虽然也是维数相同的数组，但是得数不正确，因为错行，它会把A1和B2相比较。
上述公式因为是直接定义的数组，所以输入公式时不用单独应用数组公式。
Sumif是对符合条件地单元格求和，第一个参数为需要符合条件的单元格区域范围，第二个参数是条件，第三个参数是实际需要计算的单元格区域，按与第一参数相等的区域进行计算。
这里需要注意的是，参数一满足条件后会返回区域里面单元格所在的行数，求和时，按照参数三里面区域对应行数进行。
如参数一区域选的A3:A10，参数三是B4到B11，假如a4，a5符合条件，那么将返回的行数是2、3，而不是4、5，然后，将对b5、b6进行求和，而不是b4、b5。
=SUMIF(B:B,"条件",A:A)
本公式的意义是，当b列中有单元格等于“条件”两个字时，计算对应a列单元格的和。其中条件参数可以使用通配符，*，？，实现模糊查找。
宏表函数实际上是现在广泛使用的VBA的“前身”，虽然后来的各版本已经不再使用它，但还能支持。
在公式中使用宏表函数，最重要的一点就是：不能在公式中直接使用，而必须定义成名称。很多宏表函数即使按F9也不能自动更新，而必须按Alt+Ctrl+F9才能更新。解决的办法：在定义名称时加入一个易失函数，利用其易失性强迫结果更新，例如，定义X=GET.CELL(63,A1)当背景颜色改变时，公式结果不能自动更新，必须按Alt+Ctrl+F9才能更新，而如果定义为 X=GET.CELL(63,A1)&T(NOW()), 则只需按F9或激活当前工作表的任一单元格即可以立即更新。连接的易失函数有多种形式，除了上面的例子，还有 &T(RAND())<适用文本>+TODAY()*0<适用数值>等等。
宏表函数对公式长度有限制。特别是EVALUATE，它的长度限制为251字符。在对长公式求值的时候这点往往不能满足要求。
宏表函数的运行速度是比较慢的，这在应用时应予注意。
http://www.xdcad.net/forum/showthread.php?postid=2684943
这里用到了宏表函数,宏表函数在excel中只能在自定义名称中使用,Get.Cell(类型号,单元格(或范围)),其中类型号,中关于颜色的有24(返回单元格内字符的颜色值),63(填充颜色),64(传回图样前景色彩),
例：1 A 变为 1 A BC
1 B 2 D EFG
1 C 3 H
2 D . . . .
2 E
2 F
2 G
3 H
现在假设数据在A、B两列，从第一行开始。
现在在C1、D1、E1等中输入你的开头序号是1的数据，也就是把你举例中的“1 A B C ”先人工输入。
在C2单元格输入公式：
=IF($A2=$A1,"",A2)
向下拖拉，到你的数据的末尾。
在D2中输入公式：
=IF($A2=$A1,"",IF(ISERR(INDIRECT("b"&IF(INDIRECT("A"&ROW()+COLUMN(A2)-1)=$A2,ROW()+COLUMN(A2)-1,""))),"",INDIRECT("b"&IF(INDIRECT("A"&ROW()+COLUMN(A2)-1)=$A2,ROW()+COLUMN(A2)-1,""))))
向右拖拉（注意向右时要拖拉到足够的列数，保证出来的每行数据后面的单元格都拖拉不出数据为止），再向下拖拉到数据的底部。
我现在要把B123合并起来，与前面的单元格数一样，一个单词与对应的意思占有同样的单元格数。下面还有很多这样的，并不是连续的，要怎么操作？ 不要告诉我一个一个来，因为有很多！
首先新建一个表，假设你的数据在sheet1，那么在sheet2进行
第一步，将sheet1中复制所有需要的数据，在sheet2中，a1单元格右击，选择选择性粘贴，选数值，确定。
第二步，按你的图给的数据，我在c1先输入1，在C2输入公式：
=IF(A2="",INDIRECT("C"&ROW()-1),C1+1)
然后向下拖拉到数据最后一行，这样会形成一列数字1，2，3，等等
第三步，因为是条件合并单元格，需借助自定义函数
按Atl+F11进入VBE编辑器，选择“插入”—“模块”，粘贴下面代码，关闭VBE编辑器。
Function Joinif(R1 As Range, tj As String, R2 As Range) As String
Dim X() As String, i As Integer, ii As Integer '声明变量
ii = 0 '初始化变量
For i = 1 To R1.Cells.Count '循环R1单元格
If R1.Cells(i) = tj Then '条件
ii = ii + 1
ReDim Preserve X(1 To ii)
X(ii) = R2.Cells(i) '赋值到X()数组
End If
Next
Joinif = Join(X, "；") '将X()各元素用；相连赋值给Joinif
End Function
倒数第二行代码中，“；”可以变成你需要的间隔，这个是把你的两个单元格合并后中间插入的符号，可以变成空格或逗号等等。
第五步，在D1中输入公式
=joinif($C$1:$C$100,C1,B$1:B$100)
这里假设你的数据有100行，如果有更多，将上面两个100都改过来。
拖拉上面公式到数据最后一行。
第六步，选择所有数据，选择自动筛选，在A列选择所有空格的行，删除这些行，自动筛选出所有数据，删除掉B、C两列。
最后剩下的两列数据就是所要的。
思路方法：（1）column(a:a)*2-2利用列号得到包括0在内的偶数，可以实现行向拖拉。行号同理。Row(1:1)*2-1利用行号得到奇数，可以实现列向拖拉。列号同理。（2）mod(row(),2)，用以判断当前行是奇数还是偶数行，等于0为偶数，等于1为奇数。（3）int((row(1:1)-1)/n)得数每n行增加1，解决跳行填充的思路之一。
示例：
隔行
=IF(MOD(ROW()-3,30)=0,"√","")每隔30个填充一个.
=IF(MOD(ROW(),2)=1,INDIRECT("d"&INT(ROW()/2)+1),"")
单数单元格填充，双数为空值。
如果从第5个单元格填充第六行的数字，公式变成：
=IF(MOD(ROW(),2)=1,INDIRECT("d"&INT(ROW()/2)+4),"")
后面的相加的数字等于公式输入行的行数除以2再加1，row()/2+1，最后引用的行必须等于公式输入行。如果不等于，则公式后面要加上错位的行数（区分正负数）。
上面公式可以变成：
=IF(MOD(ROW(),2)=1,INDIRECT("d"&INT(ROW()/2)+row()/2+1),"")
跳行
=INDEX(a:a,row(1:1)*5)
=offset($a1,,column(a:a)*2-2) 行填充，引用区域为第一行，填充奇数列
=INDIRECT(ADDRESS(1,COLUMN(a:a)*2-1)) 行填充，引用区域为第一行，填充奇数列
=INDIRECT(ADDRESS(ROW(1:1)*2-1,1)) 列填充，引用区域为第一列a列，填充奇数行
公式法：
综合利用了隔行和跳行填充的问题，利用choose语句来选择不同的情况，比if判断要简洁。因为假设第一行为标题行，
工资条、成绩单等类似问题，向来备受关注，方法也众多，什么邮件合并啦，什么借助辅助列重新排序，当然还有vba，公式法等等。这些方法中最简洁易操作的当然还是公式法，但网上流行的公式法都没有留出空行，给将来裁剪带来不便，追根究底，工资条是要剪开然后发给大家的，方便为主。
笔者重新编写了公式，解决了裁剪的问题。
首先假设工资或成绩数据都在sheet1，第一行为标题行。本公式考虑到裁剪的问题，结果会是：第一行是标题，第二行是工资或成绩，第三行是空行，向下以此类推。
=CHOOSE(MOD((ROW(1:1)-1),3)+1,Sheet1!A$1,INDEX(Sheet1!A:A,INT((ROW(1:1)-1)/3)+2),"")
将上面公式输入到sheet2，任选一个单元格作为起始单元格，（或者输入到sheet1数据表格，但一定要选没有数据的列作为起始列），当然可以选sheet2中的a1作为起始，回车后选中a1，鼠标移到右下角，出现黑色十字时，向右拖拉到g列（当然由你的数据决定），然后再统一向下拖拉，直到数据完结。
此公式当数据完结后，会在第二行工资处出现零值，为了不显示零值，可以再上述公式后面加上&“”，但是这样会将原来的数字格式的数据变成文本格式，所以依据使用者的需要，请自行选择添加。
当然，具体的格式、字体、边框等等可以自行设置，如要设置边框，可以利用筛选剔除空白行，然后统一设置好边框。
=MID(A1,FIND("（", A1),FIND("）", A1))
如果前面的数字的位数一样，就用如下公式
=RIGHT(TRIM(A1),LEN(TRIM(A1))-5)
=odd()取绝对值大的舍入的奇数，即正（负）数向上（下）舍入
=even()
=isodd()判断是否为奇数
=ISEVEN()
是的话返回true，否则返回false。
=SUM(IF(MOD(A1:F1,2)=0,A1:F1,""))
此为数组公式，按ctrl+shift+enter结束
=SUMPRODUCT((MOD(A1:F1,2)=0)*A1:F1)
在excel表中，如果A列是日期，B列名称，C列是数量
要求出，A列符合某个日期以前的以及B列符合某个名称的C的数量总和
=SUMIFS(C:C,A:A,"日期",B:B,"名称")
=SUM(IF(((A:A="日期")*(B:B="名称"))=1,C:C,""))
数组公式
其中，在具体例子中，可以将INDIRECT("a"&ROW())变成a2，a3等等实际的单元格，可以实现一样的功能，可以不必排序。
=IF(COUNTIF($a$1:INDIRECT("a"&ROW()),INDIRECT("a"&ROW()))>1,"",INDIRECT("a"&ROW()))
选出的通过筛选删除。
=INDEX(a:a,SMALL(IF(countif((A$1:A$200),(A$1:A$200))>1,65536,ROW(a$1:a$200)),ROW(1:1)))&””
数组公式
=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH($A$1:$A$10,$A$1:$A$10,),MATCH($A$1:$A$10,$A$1:$A$10,))>0,ROW($A$1:$A$10),65536),ROW(1:1)))&""
数组公式
=INDEX(A:A,SMALL(IF(COUNTIF($A$1:$A$10,$A$1:$A$10)>1,ROW($1:$10),65536),ROW(1:1)))&""
数组公式，可以向右拖拉以便得到其他各列的相应数据。
这个问题如果不剔除空格，其实很简单，左右对比，if语句，如果相等即显示空格，如想剔除空格，则相对复杂，要用到small函数来从数组里取数。
=INDEX(b:b,SMALL(IF((A$1:A$200)<>(B$1:B$200),ROW(B$1:B$200),65536),ROW(1:1)))&""
有两种数据，一种是TSDG112A,TSDG112B,TSDG113C类型，要求去掉所有的最后一个字母，然后筛选重复数据，还有一种是TSMIN08类型，后面没有字母，要求保留整个数据。数据从a2开始，公式输入到b2，如下：
=IF(COUNTIF($A$2:A2,IF(CODE(RIGHT(A2,1))<58,A2,LEFT(A2,6)&"?"))>1,"",LEFT(A2,6))
其中利用code函数判断最后一位是不是字母，数字的代码是48-57，大写字母是65-90，小写字母是97-122。
利用countif函数可以使用通配符，模糊查找数据。
将5679变成9765，位数不规则
=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))
将54671，变成14567，位数不规则
=SUM(LARGE(--MID(A1,ROW(INDIRECT("$1:"&LEN(A1))),1),ROW(INDIRECT("$1:"&LEN(A1))))*10^(ROW(INDIRECT("$1:"&LEN(A1)))-1))
数组公式
比如0141253645267变成01234567，以下均为数组公式。
2003版本中，因为没有函数iferror，使工作繁琐了很多。用if语句又导致公式太长，无法输入，解决办法：
在工具栏，选插入——名称——自定义，在名称的地方输入名字，我这里用了数组序列的缩写szxl，在对话框的下面可以输入公式
=IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1)
回车确定以后，在任意单元格里输入公式：
=IF(ISERR(SMALL(SZXL,1)),"",SMALL(SZXL,1))&IF(ISERR(SMALL(SZXL,2)),"",SMALL(SZXL,2))&IF(ISERR(SMALL(SZXL,3)),"",SMALL(SZXL,3))&IF(ISERR(SMALL(SZXL,4)),"",SMALL(SZXL,4))&IF(ISERR(SMALL(SZXL,5)),"",SMALL(SZXL,5))&IF(ISERR(SMALL(SZXL,6)),"",SMALL(SZXL,6))&IF(ISERR(SMALL(SZXL,7)),"",SMALL(SZXL,7))&IF(ISERR(SMALL(SZXL,8)),"",SMALL(SZXL,8))&IF(ISERR(SMALL(SZXL,9)),"",SMALL(SZXL,9))&IF(ISERR(SMALL(SZXL,10)),"",SMALL(SZXL,10))
如果是2007版本，iferror语句
=IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),1),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),2),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),3),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),4),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),5),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),6),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),7),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),8),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),9),"")&IFERROR(SMALL(IF(ISERR(FIND(ROW($1:$10)-1,$A1)),"",ROW($1:10)-1),10),"")
上面的解决办法公式太长容易出错，采用10的指数方法，解决：
=SUMPRODUCT(LARGE(IF(NOT(ISERR(FIND(ROW($1:$10)-1,A1))),ROW($1:$10)-1),ROW(INDIRECT("$1:"&COUNT(FIND(ROW($1:$10)-1,A1)))))*10^(ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,A1))))-1))
上式未能包含0值，因为采用数字算法，首位为零会自动省略，在实际应用中，还要加入0值判断：
=IF(ISERROR(FIND("0",A1)),,"0")&SUMPRODUCT(LARGE(IF(NOT(ISERR(FIND(ROW($1:$10)-1,A1))),ROW($1:$10)-1),ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,A1)))))*10^(ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,A1))))-1))
或者
=IF(ISERROR(FIND("0",A1)),,"0")&SUMPRODUCT(LARGE(ISNUMBER(FIND(ROW($1:$10)-1,A1))*(ROW($1:$10)-1),ROW(1:10))*10^(ROW(1:10)-1))
其中count函数用来统计不重复数字的个数。
如果是横向三个单元格,a1,b1,c1,可以先对其合并，再用下面公式，由于横向没法用frequency函数，所以还得先合并数据，再用find函数查找。
=IF(iserror(FIND(0,T(CONCATENATE(A1,B1,C1)))),"","0")&SUMPRODUCT(LARGE(IF(NOT(ISERR(FIND(ROW($1:$10)-1,CONCATENATE(A1,B1,C1)))),ROW($1:$10)-1),ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,CONCATENATE(A1,B1,C1))))))*10^(ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,CONCATENATE(A1,B1,C1)))))-1))
上面公式可以计算一个单元格中多个数字的，如果三个单元格中只有单个数字,可以用下面公式。
经过考虑，可以不用合并，直接用match函数进行查找，简化公式变成（但不能用hlookup，不支持第一参数数组查找）：
=IF(ISERROR(MATCH(0,A1:C1,0)),"","0")&SUMPRODUCT(LARGE(IF(ISERROR(MATCH(ROW($1:$10)-1,A1:C1,0)),"",ROW($1:$10)-1),ROW(INDIRECT("1:"&COUNT(MATCH(ROW($1:$10)-1,A1:C1,0)))))*10^(ROW(INDIRECT("1:"&COUNT(MATCH(ROW($1:$10)-1,A1:C1,0))))-1))
A1，b1中字符为非数字形式，所以无法用10的指数形式显示，下面公式必须依赖在几个单元格中输入整体数组公式才能实现，属于半成品。
=IF(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1),B1)),MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1),"")
如果a1长度不定，可以利用多个辅助列来处为，可以可以解决问题：
因为公式超出嵌套的问题，需借用名称定义，定义一个名称cd，
=ROW(INDIRECT("$1:$"&IF(COLUMN()=3,LEN($A1),LEN(B1))))
然后输入数组公式：
C1=REPLACE(IF(COLUMN()=3,$A1,B1),MIN(IF(ISNUMBER(FIND(MID(IF(COLUMN()=3,$A1,B1),cd,1),$B1)),cd,"")),1,"")
向右拖拉，直到出现错误为止，因为嵌套问题，所以无法再添加判断最后的错误。
如果a1中字符有重复，则此公式将会出现错误。可以尝试用substitute函数进行全面搜索替换。因函数过长，涉及到两个名称定义，未予考虑。
选择a1单元格，然后在工具栏中选择格式——条件格式，弹出的对话框里，条件下拉框选择公式，在右边输入公式
=not(iserror(find("-",a1))
在格式里面选择要的颜色，然后确定，用格式刷，把格式刷到所有的数据单元格。
选择所有数据，在工具栏选开始，条件格式，突出显示单元格规则——文本保含，弹出对话框输入-，选择你想要的颜色，确定。
=INDEX(A2:B5,MATCH("梨",A2:A5,0),2)
此法可以在查找区域内任意范围内查找。
=OFFSET(A1,MATCH("梨",A2:A5, 0),1)
此法可以在查找区域内任意范围内查找。
MATCH，返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP 函数。
=VLOOKUP(c3,IF({1,0},a2:a7,b2:b7),2,0)
=VLOOKUP(c3,choose({1,2},a2:a7,b2:b7),2,0)
如果在数据只有两列，在B列查找区域，c列输入查找条件，返回a列值得首位字符，用以下公式：
= INDEX(a:a,MATCH(c1,b:b,0))
如果多列查找，A,B,C,D,E
=INDEX(a:a,MIN(IF(a:e=f1,ROW(a:e))))
Small，返回数据中第k个值，从最小值开始，按从小到大的顺序。
Large，和small相反
这两个函数，可以把不连续查找得到的数据在连续单元格区域中输出，中间不会出现重复数据或断行，主要原理是先用数组找出所有符合条件的数据（比如用if函数返回所有符合数据的行号），然后利用small函数，将k设置成变量，步进值为1的自然数，一次可以罗列出所有符合条件的函数。
具体方法见下面几个例子。
A列为姓名，b列为分数。
=INDEX(A:A,SMALL(IF(B$1:B$19<60,ROW($1:$19),65536),ROW(A1)))
需要对公司员工生日在10月的员工进行总。在新的表格中全部列出来。
员工生日的格式为：1990-4-10 00:00:00
=INDEX(Sheet1!A:A,SMALL(IF(MONTH(Sheet1!$B:$B)="10",ROW(A:A)),ROW(1:1)))
在sheet2的B2开始，显示出所有满足sheet2中a列数据的sheet1中的b列数据。Sheet2中的a列在sheet1的a列中查找。
=INDEX(Sheet1!B:B,SMALL(IF(Sheet1!A:A=A2,ROW(A:A)),ROW(1:1)))
A列 B列
小芳 80
小钢 70
小芳 70
小李 80
要统计第n个小芳的分数
=index(b:b,small(if($a$1:$a$100="小芳",row($a$1:$a$100),65536),n))&""
其中100假设数据行数，n为你要的第几个数，第五个就输入5，数组公式，按ctrl+shift+enter结束。如果是2007版本，公式可以简单写成：
=index(b:b,small(if($a:$a="小芳",row($a:$a),65536),n))&""
要把全级800个学生按成绩的高低分到15个教室，平均每个教室50人，怎样用EXCEL编排考试座位表？人名在a列，成绩在b列，教室在c列，从第二行开始数据。
C2=INT(RANK(b2,$b$2:$b$800,1)/50)+1
=SUMPRODUCT((A$2:A$10=A2)*(C$2:C$10>C2))+1
班级名称在a列，姓名b列，成绩c列，rank函数因为不能对数组操作，所以不能使用。
=a1-int(a1/1000)*100
主要考虑去掉零头，满一千整才返钱。
表格如下，一次为a,b,c,d,e等列。
订单编号 |
品名 |
订购数量 |
最低限价 |
实际成交价 |
第一个条件和第二个条件互不干扰。第一个条件是满千返80。第二个条件是满5000返400。
第三个条件，只要是ABCDE五个新品中的一款，即减200。
同一订单编号购买数量超过3个减200，没有超过3个就是减100。
求实际成交价!
=SUMPRODUCT(($A$2:$A$200=A2)*($C$2:$C$200)*($D$2:$D$200))-IF(SUMPRODUCT(($A$2:$A$200=A2)*(C2:C200))>2,"200","100")-IF(OR(B2={"a","b","c","d","e"}),"200","0")-INT(SUMPRODUCT(($A$2:$A$200=A2)*($C$2:$C$200)*($D$2:$D$200))/1000)*80-INT(SUMPRODUCT(($A$2:$A$200=A2)*($C$2:$C$200)*($D$2:$D$200))/5000)*400
这里，数组与数据对比时，如果有一项不满足，则返回false，属于and类型的逻辑关系，所以加or函数，变成或地逻辑关系。
Sumproduct是个比较好用的函数，它既可以计算满足单个条件或多个条件的数据的和，也可以统计，满足条件的数据的个数。
A列是1、2、3、4、5、6、7，然后让他们不重复地两两组合出现在B列，比如12、13、14、23、24
Sub 简单组合()
Dim a(), i As Integer, j As Integer, k As Integer
a = [A1:A7].Value
For i = 1 To 6
For j = i + 1 To 7
k = k + 1
Cells(k, 2) = a(i, 1) & a(j, 1)
Next j
Next i
End Sub
五个数字的组合:
Sub 简单组合()
Dim a(), i As Integer, j As Integer, r As Integer, l As Integer, m As Integer, n As Integer
a = [A1:A7].Value
For i = 1 To 3
For j = i + 1 To 4
For l = j + 1 To 5
For m = l + 1 To 6
For n = m + 1 To 7
r = r + 1
Cells(r, 2) = a(i, 1) & a(j, 1) & a(l, 1) & a(m, 1) & a(n, 1)
Next n
Next m
Next l
Next j
Next i
End Sub
数组利用.value被赋值为单元格区域以后，数组的第一个参数表示该区域内的行标，第二个参数表示该区域内的列标。类似cells(1,1)的形式。
=INDEX($C:$C,COLUMN(B:B))&INDEX($C:$C,ROW()+COLUMN(A:A))
将上述公式输入到b列，先向下拖拉，直到出现一个数的时候为止，在统一向右拖拉，直到最上面一行出现一个数字时为止。然后删除里面所有的1个数组的单元格，会出现一个三角形。
这里利用行和列会随着拖拉自动增加的原理，合理的调整，可以满足很多需要。
将下述公式输入到d1,不符合条件的会显示空白
=IF(OR(MATCH(A1,A$1:A1)<>MATCH(B1,B$1:B1),MATCH(A1,A$1:A1)=ROW()),"",INDEX($C:$C,MATCH(A1,A$1:A1))&INDEX($C:$C,ROW()))
本公式只适合于有两个重复值
在A1中输入小定金额，将这一小写金额上的每一位数字分别填充于B1，B2，B3，B4，B5....中,且前面始终有人民币符号"￥"。其中要求去掉小数点。
=if(row()<=len($a$1)-1,"￥"&mid(SUBSTITUTE($a$1,".",""),row(1:1),1),"")
J09100514001这串字符在excel中以序列方式填充
="J09100514"&text(row(1:1),"000")
此公式可以添加到999。
09232622050035；09230622050472；09230223010897，像这样的数字前后都不太有规律，筛选出倒数第5，6位是05的数据
两种思路：
=IF(ISERR(LEN(A1)-FIND("05",A1)),"",IF((LEN(A1)-FIND("05",A1))=5,A1,""))
=IF(mid(A1,len(a1)-4,2)="05",a1,"")
第一个参数是数据库包含的区域，包括列标志。
第二个参数是要统计的列，用双引号””引用列标志，如”产量”。
第三个参数为条件所在的区域。包含一行列标志，和下面的多行条件区域。
Criteria为一组包含给定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。确定条件区域没有与数据清单相重叠。
建立条件区域的基本要求：（1）在可用作条件区域的数据清单上插入至少三个空白行。（2）条件区域必须具有列标志。（3）请确保在条件值与数据清单之间至少留了一个空白行。
输入的条件为空白时，表示该列标志下的所有行。
当输入条件为公式时，列标志处置空（如果非要输入相应标志时，要用双引号””括起来），在下面一个单元格输入条件公式，类似于=d4/c4>0.7，=产量/使用年数>=0.7，在条件参数引用时，即使列标志为空，也必须包括列标志所在的单元格和下面的条件单元格。
简单情况下，显示最后一行数据或列数据，如无空格，使用：
=INDEX(a:a,COUNTA(a:a))
=INDEX(1:1,COUNTA(1:1))
如有空格：
=INDEX(a:a,MAX((a2:a200<>0)*row(a2:a200)))
因为2003版不支持一些数组公式的a:a输入，所以这里使用了2—200的数据行，但是在2007中可以简化公式。
=INDEX(1:1,MAX((B2:I2<>0)*COLUMN(B:I)))
当A列在A50输入结束后，B1=A50，当转换到E1输入时，也是最后一个数据，到第五十结束。多余部分也有文字或数据。
当中间不出现空单元格的时候，公式如下：
=IF(AND(COUNTA(A1:A50)=50,COUNTA(E1:E50)>0),INDIRECT("e"&COUNTA(E1:E50)),INDIRECT("a"&COUNTA(A1:A50)))
如果中间存在空单元格，则使用如下公式：
=IF(COUNTA(E1:E50)<1,INDEX(A:A,MAX(IF(A1:A50<>"",ROW(1:50),""))),INDEX(E:E,MAX(IF(E1:E50<>"",ROW(1:50),""))))
=IF(INDIRECT(ADDRESS(MOD(ROW(1:1)-1,COUNT($A:$A))+1,INT((ROW(1:1)-1)/COUNT($A:$A))+1))="","",TEXT(INDIRECT(ADDRESS(MOD(ROW(1:1)-1,COUNT($A:$A))+1,INT((ROW(1:1)-1)/COUNT($A:$A))+1)),"@"))
其中if函数屏蔽了最后数据完结时出现的0值，count计算数值数据的行数n。
Mod函数实现从1到n的递增取值，得到单元格的所在的行；
int函数实现从1开始，每隔n自动增加的一系列数，得到单元格所在的列。
最后通过address函数得到相应行列的单元格位置。
都是数组公式
数据来自于名称为“数据源样表”的工作表，其中，数据在g3:z103，要求筛选出所有大于100的数据，在当前表格中显示，并且按从小到大显示在A列，然后在b列显示A列数据在“数据源样表”中所在列第二行的数据，在c列显示它所在行A列的数据。
解决思路是：
根据条件得到数据a列并从小到大显示比较容易，small函数和if函数配合就可以解决。
但如果数据中有重复，同时按同样顺序显示相应的行、列比较困难，这里如果继续用small函数，得到的顺序是分别按行号大小和列号大小顺序排列的一列数据。
这里利用当前单元格数据比如a5，它与前面的数据（包括它本身a2:a5）里面，如果存在重复，重复个数可以统计为COUNTIF($A$2:$A5,$A5)，假设为n。注意a5要用相对行号，绝对列标。
事先利用“IF(数据源样表!$G$3:$Z$103=$A2,COLUMN(数据源样表!$G$3:$Z$103))”函数得出所有等于a5的源数据所对应的列号（或行号）组成数组。比如有三个重复，分别在第g、h、i列。
然后根据small函数里面的k参数，可以得到第k个数，这里n为3，所以得到第三个数，也就是第i列的列号9。
得到了列号，同一列上的指定行的数据也就很好求了。
第一个If语句用来判断是否数据全部显示完，如果显示完后，则显示为空值。
A2=IF(ROW(1:1)>COUNTIF(数据源样表!$G$3:$Z$103,">100"),"",SMALL(IF(数据源样表!$G$3:$Z$103>100,数据源样表!$G$3:$Z$103),ROW(1:1)))
B2=IF(ROW(1:1)>COUNTIF(数据源样表!$G$3:$Z$103,">100"),"",INDEX(数据源样表!$2:$2,SMALL(IF(数据源样表!$G$3:$Z$103=$A2,COLUMN(数据源样表!$G$3:$Z$103)),COUNTIF($A$2:$A2,$A2))))
C2=IF(ROW(1:1)>COUNTIF(数据源样表!$G$3:$Z$103,">100"),"",INDEX(数据源样表!$AA:$AA,SMALL(IF(数据源样表!$G$3:$Z$103=$A2,ROW(数据源样表!$G$3:$Z$103)),COUNTIF($A$2:$A2,$A2))))
上述公式中，选择所有大于100的数据，按从小到大在a列显示，然后在b列显示它所在列第二行的数据，在c列显示它所在行a列的数据。
如图所示：
A18:A31为14家公司的排列序号
B18:B31为14家公司的名称
D18:D31为14家公司的数值。有的大，有的小。
通过函数把在D18:D31当中的最高的那个数值和与之对应的公司名称显示在B1:B17当中。
比如说D23当中的6在D18:D31当中是最大的。
那么通过函数他出现在C8单元格中。而和他对应的公司BWIN则显示在B8单元格中。
B9和C9以此类推，数值大小从上到下排列，以D18:D31为基准，如果出现重复的，则以先出现的数字为大。
最后显示的结果是
B8显示BWIN，C8显示6
B9显示威廉，C9显示4
B10显示立博，C10显示立博（威廉和立博数值大小相同，但是威廉先出现4）
按照要求，b1中公式为（注意下面公式都是数组公式，输入公式后，需要按三个键ctrl+shif+回车键确认）：
=INDEX(B:B,SMALL(IF(C1=D$18:D$31,ROW(D$18:D$31)),COUNTIF($C$1:$C1,$C1)))
c1中公式为：
=LARGE($D$18:$D$31,ROW(1:1))
|
A |
B |
1 |
得数：6 |
|
2 |
||
3 |
||
4 |
||
5 |
||
6 |
a1 |
1 |
7 |
a2 |
2 |
8 |
A2 |
2 |
9 |
a3 |
3 |
10 |
a3 |
3 |
B1中公式为：
=SUM(IF(FREQUENCY(MATCH($a6:$a10,$a6:$a10,),MATCH($a6:$a10,$a6:$a10,))>0,b6:b10))
|
A |
B |
C |
D |
1 |
可移植性强的公式，里面的6、10两个数字自行更改成需要的数据行 |
辅助列（可以选中整列，右击，选隐藏） |
||
2 |
得数：11 |
|||
3 |
||||
4 |
||||
5 |
||||
6 |
a1 |
3 |
3 |
|
7 |
a2 |
3 |
3 |
|
8 |
a4 |
1 |
1 |
|
9 |
a3 |
4 |
4 |
|
10 |
a3 |
1 |
4 |
其中c6到c10为辅助列，公式为（数组公式）：
C6=MAX(IF(MATCH($A$6:$A$10,$A$6:$A$10,)=MATCH(A6,$A$6:$A$10,),$B$6:$B$10,0))
B2中公式为：
=SUM(IF(ROW(INDIRECT("$1:$"&(ROW($A$10)-ROW($A$6)+1)))=MATCH($A$6:$A$10,$A$6:$A$10,),C6:C10,0))
此公式较简洁，其中的5是数据的行数，需要计算：
=SUM(IF(ROW($1:$5)=MATCH($A$6:$A$10,$A$6:$A$10,),C6:C10,0))
A B C三列数据，要求转成一列，并且第一行为C2，第二行为A2，第三行为B2，第四行为C3，第五行为A3，第六行为B3，依次类推，公式如下：
=INDEX(A:C,INT((ROW(1:1)-1)/3)+2,CHOOSE(MOD(ROW(1:1)-1,3)+1,3,1,2))
数据从a1开始的话，可以在b1输入：
=IF(OR(ISNUMBER(FIND(ROW(1:10)-1,A1))),A1,"")
这个是数组公式，输入完毕后要按ctrl+shift+enter结束，如果嫌麻烦，可以用下面公式：
=IF(OR(ISNUMBER(FIND(1,A1)),ISNUMBER(FIND(2,A1)),ISNUMBER(FIND(3,A1)),ISNUMBER(FIND(4,A1)),ISNUMBER(FIND(5,A1)),ISNUMBER(FIND(6,A1)),ISNUMBER(FIND(7,A1)),ISNUMBER(FIND(8,A1)),ISNUMBER(FIND(9,A1)),ISNUMBER(FIND(0,A1))),A1,"")
=CHOOSE(WEEKDAY(A1,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日")
=LEN(A1)-LEN(SUBSTITUTE(A1,"+",))