RDIFramework.NET，基于.NET的快速信息化系统开发、整合框架，给用户和开发者最佳的.Net框架部署方案。请关注官方博客：http://blog.rdiframework.net/
11:10 by yonghu86, ... 阅读,
&(Excel)常用函数公式及操作技巧之九：
查询和查找引用
&&通过知识共享树立个人品牌。
&查找顺序公式
=LOOKUP(2,1/(A1:A20&&0),A1:A20)
=MATCH(7,A1:A20)
=VLOOKUP(7,A1:B11,2)
怎样实现精确查询
=VLOOKUP(B11,B3:F7,4,FALSE)
=LOOKUP(B11,B3:B7,E3:E7)
用MATCH+INDEX
=INDEX(E3:E7,MATCH(B11,B3:B7,0))
用INDIRECT+MATCH
=INDIRECT("E"&MATCH(B11,B3:B7,0)+2)
用OFFSET+MATCH
=OFFSET(E3,MATCH(B11,B3:B7,0)-1,0)
用INDIRECT+ADDRESS+MATCH
=INDIRECT(ADDRESS(MATCH(B11,B4:B7,0)+3,5))
用数组公式
=INDEX(E1:E7,MAX(IF((B4:B7=B11),ROW(B4:B7),0)))
查找及引用
如何查找并引用B2单元格中所显示日期当日的相应代码的值。
B3=IF(COUNTIF($E$3:$E$20,A3),VLOOKUP($A3,$E$2:$M$20,MATCH(B$2,$F$2:$M$2,)+1,),"")
查找函数的应用
我想在A5输入表的名称，B5自动跳出该表中B列的最后一个有效数值，请问B5的公式该如何设定？
=LOOKUP(9E+307,INDIRECT(A5&"!"&"B:B"))
B2 =IF(A2="","",LOOKUP(9E+307,INDIRECT(A2&"!B:B")))
怎么能方便的判断某个单元格中包含多少个指定的字符？
例：A1 中是&ASAFAG&，我希望计算出A1里面有多少个&A&......
=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
如何用查找函数
一、要求: 利用公式从左表中查询相应的地区，结果放在H14单元格
=VLOOKUP(G14,IF({1,0},D14:D18,C14:C18),2,)
h14=OFFSET(C14,MATCH(G14,D14:D18,0)-1,,,)
H14 =INDIRECT("c"&MATCH(G14,D:D,))
二、要求: 根据C25单元格的商品名称，查找该商品的最新单价，即该商品最后一条记录的单价（结果放在D25单元格）。用数组公式：
=INDIRECT("G"&MAX((D14:D22=C25)*ROW(D14:D22)))
D25 =LOOKUP(2,1/(D14:D22=C25),G14:G22)
日期查找的问题
我有一个日期比如:,我想知道它减去一个固定天数比如6后,最接近它的一个星期四(只能提前)是多少号
的答案应该是而不是
日期在A1处，B1处输入：
=MAX((WEEKDAY(A1-6-{1,2,3,4,5,6,7},2)=4)*(A1-6-{1,2,3,4,5,6,7}))
B1, 输入公式 :
=A1-6-MOD(WEEKDAY(A1-6,2)+3,7)
如何自动查找相同单元格内容
=SUMPRODUCT(($D$2:$D$15=A21)*($E$2:$E$15))
=IF(ISERROR(VLOOKUP(A6,$D$2:$E$15,2,0)),0,VLOOKUP(A6,$D$2:$E$15,2,0))
D3 =LOOKUP(2,1/(($G$3:$G$14=B3)*($H$3:$H$14=C3)),$I$3:$I$14)
=IF(ISERROR(VLOOKUP(A14,A:B:D:F,2,FALSE)),"",VLOOKUP(A14,A:B:D:F,2,FALSE))
=IF(ISERROR(VLOOKUP(C2,k!B2:Z2189,2,FALSE)),"",VLOOKUP(C2,k!B2:Z2189,2,FALSE))
怎样对号入座（查找）
=VLOOKUP(D2,$A$1:$B$5,2,FALSE)
=INDEX($B$2:$B$5,MATCH(D2,$A$2:$A$5,0))
=OFFSET($A$1,MATCH(D2,$A$2:$A$5,0),1)
=VLOOKUP(D2,$A$1:$B$16,2,)
=VLOOKUP(D2,IF({1,0},$A$1:$A$9,$B$1:$B$9),2,)
=LOOKUP(2,1/($A$1:$A$10=D2),$B$1:$B$10)
一个文本查找的问题
如何在一个单元格中,统计某个字符出现的次数,例如:单元格A1中填有:张三/李四/王五",如何通过公式来计算此单元格中共填有几个人姓名,每个人姓名之间用"/"符号分开,烦请相告.
=LEN(A1)-LEN(SUBSTITUTE(A1,"/",))+1
查找一列中最后一个数值
我想用公式知道,另一个表中"A"列最下面一个数是多少,就行了.用不定值的,因为还有数据有增加,
=LOOKUP(9E+307,Sheet2!A:A)&&最后一个数值
=LOOKUP(REPT("座",255),Sheet2!A:A)&&最后一个文本
=INDEX(Sheet2!A:A,MATCH(9E+307,Sheet2!A:A))
=INDEX(Sheet2!A:A,MATCH("*",Sheet2!A:A,-1))
＝Match(rept("座",255),sheet2!A:A)
查找重复字符
&&&&　　 A&&&&&&&&&&&&&&&&&& B
　　1245689&&&&&&& &&&&&&&&0134578
查找单元格Ａ和Ｂ里重复及不重复的字符
正确答案：重复字符－1458
　　　　　&&& 不重复字符－023679
以下公式对数字有效：
重复数字：
=IF(COUNT(FIND(0,A1:B1))=2,0,"")&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($1:$9),A1))+ISNUMBER(FIND(ROW($1:$9),B1))=2,ROW($1:$9)*10^(10-ROW($1:$9)))),0,)
不重复数字：
=IF(COUNT(FIND(0,A1:B1))=1,0,"")&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($1:$9),A1))+ISNUMBER(FIND(ROW($1:$9),B1))=1,ROW($1:$9)*10^(10-ROW($1:$9)))),0,)
都是数组公式，按Ctrl+shift+enter结束。
重复数字：
=IF(COUNT(FIND(0,A1:B1))=2,0,"")&SUBSTITUTE(SUM(IF(MMULT(COUNTIF(OFFSET(A1,,{0,1},),"*"&ROW($1:$9)&"*"),{1;1})&1,ROW($1:$9)*10^(9-ROW($1:$9)))),0,)
不重复数字：
=IF(COUNT(FIND(0,A1:B1))=1,0,"")&SUBSTITUTE(SUM(IF(MMULT(COUNTIF(OFFSET(A1,,{0,1},),"*"&ROW($1:$9)&"*"),{1;1})&2,ROW($1:$9)*10^(9-ROW($1:$9)))),0,)
请教查找替换问题
把表1中字符在4个以上的字段(含4个)出来,替换成表2中的人名,最好在原位置修改,或者在新的一列上生成也成,只要其他内容保持不变并按原来的顺序即可。
=IF(LEN(A2)&4,A2,OFFSET(表2!$A$1,SUMPRODUCT(--(LEN($A$2:A2)&3))-1,))
=IF(LEN(A2)&4,A2,INDEX(表2!A:A,COUNTIF($A$2:A2,"="&"????*")))
IF函数替换法总结
条件说明：小于10返回500，小于20返回800，小于30返回1100，小于40返回1400，大于40返回1700
类似于以上要求，大家最先想到IF函数，这也本属IF专长。但用IF一般要长长的公式，且计算较慢。现总结一下IF之替换公式，望能抛砖引玉，在我的倡导下各位提供更完善的方案。其中部分公式通用，部分公式有局限性，请看说明。(前18个条件公式，根据速度，排名如下)
1=SMALL({500;800;00},COUNTIF($A$9:$A$13,"&="&A1))
2=INDEX({500;800;00},COUNTIF($A$9:$A$13,"&="&A1))
3=CHOOSE(COUNTIF($A$9:$A$13,"&="&A1),500,800,00)
4=LOOKUP(A1,{0,10,20,30,40},{500,800,00})
5=MIN(4,INT(A1/10))*300+5006=MATCH(A1,{0,10,20,30,40})*300+200
7=MIN(40,FLOOR(A1,10))*30+500
8=HLOOKUP(A1,{0,10,20,30,40;500,800,00},2,1)
9=200+SUM((A1&={0;10;20;30;40})*300)
10=FREQUENCY({0,10,20,30,40},A1)*300+200
11=MAX((A1&={0,10,20,30,40})*{500,800,00})
12=INDEX({500;800;00},MATCH(A1,{0;10;20;30;40},1))
13=CHOOSE(MATCH(A1,{0;10;20;30;40},1),500,800,00)
14=500+SUM(IF(A1&={10,20,30,40},{300,300,300,300}))
15=IF(A1&10,500,IF(A1&20,800,IF(A1&30,1100,IF(A1&40,))))
16=CHOOSE(SUM((A1&={0;10;20;30;40})*1),500,800,00)
17=MAX((INT(A1/({10;20;30;40}))&0)*(ROW($1:$4)*300))+500
18=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,00)
　　新增公式：
19=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,00)
20{=MAX((INT(A1/(ROW($1:$4)*10))&0)*(ROW($1:$4)*300))+500}
21=500+MIN(4,MAX(0,INT(A1/10)))*300
22MAX((A1&={0,10,20,30,40})*{500,800,00})
23=MATCH(A1,{0,10,20,30,40})*300+200
24=MIN(40,FLOOR(A1,10))*30+500
25=FREQUENCY(ROW($1:$5)*10-10,A1)*300+200
查找的函数（查找末位词组）
（数组公式：）
=REPLACE(A2,1,MAX(IF(MID(A2,ROW($1:$100),1)=" ",ROW($1:$100))),)
=REPLACE(A2,1,LOOKUP(1,0/(MID(" "&A2,ROW($1:$100),1)=" "),ROW($1:$100))-1,)
（数组公式：）
=RIGHT(A2,MATCH(1,FIND(" ",RIGHT(" "&A2,ROW($1:$100))),)-1)
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))
其实这个公式的思路, 是可以变化的，改变REPT( )中的数值, 可以返回, 指定空格位置後的数据，比如：
A1&&=一 二 三 四 五 六 七 八 九
10个普通公式, 分别为 :
1=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",100)),100)) 返回第0空格位置後的数据&一 二 三 四 五 六 七 八 九
2=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100)) 返回第8 空格位置後的数据&九3=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",40)),100)) 返回第7 空格位置後的数据&八 九
4=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),100)) 返回第6 空格位置後的数据&七 八 九
5=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",23)),100)) 返回第5空格位置後的数据&六 七 八 九
6=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",18)),100)) 返回第4 空格位置後的数据&五 六 七 八 九
7=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",14)),100)) 返回第3 空格位置後的数据&四 五 六 七 八 九
8=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",12)),100)) 返回第2 空格位置後的数据&三 四 五 六 七 八 九
9=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",11)),100)) 返回第1 空格位置後的数据&二 三 四 五 六 七 八 九
10=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),100)) 返回第0空格位置後的数据&一 二 三 四 五 六 七 八 九
怎样从原始数据中自动获取最后一个数据
原始数据&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
a&&&&&&& 12&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
b&&&&&&& 1221&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
c&&& &&&&12&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
d&&&&&&& 33&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
a&&&&&&& 33&&&&&&&&&&&&&&&&&&&&&&& 自动获取&&&&&&&
a&& &&&&&432&&&&&&&&&&&&&&&&&&&&&& a&&&&&&& 432
b&&&&&&& 33&&&&&&&&&&&&&&&&&&&&&&& b&&&&&&& 33
c&&&&&&& 22&&&&&&&&&&&&&&&&&& &&&&&c&&&&&&& 44
c&&&&&&& 44&&&&&&&&&&&&&&&&&&&&&&& d&&&&&&& 23
d&&&&&&& 23&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
=LOOKUP(1,0/($A$1:$A$100=C2),$B$1:$B$100)
两列数据查找相同值对应的位置
=MATCH(B1,A:A，0)
查找数据公式两个(基本查找函数为VLOOKUP,MATCH)
（1）、根据符合行列两个条件查找对应结果
=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)
（2）、根据符合两列数据查找对应结果(为数组公式)
=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0))小孙村长 的BLOG
用户名：小孙村长
文章数：99
评论数：303
访问量：407880
注册日期：
阅读量：5863
阅读量：12276
阅读量：311813
阅读量：1026858
51CTO推荐博文
自从进入九月份就着手忙碌公司生产任务订单分发的小项目开发，项目小的根本就不值得外包，所以决定自己动手敲代码；每日里脑袋里想的全是程序，以前做梦还能梦见个花花草草的，现在一做梦就是0和1，甚是枯燥。敲代码敲得苦恼了，就写个小教程或是操作技巧发出来跟大家分享，一个月积攒下来竟有七篇，但做别的操作技巧时还需一步一步的测试、截图、配文，所以自今日起索性偷个懒，开启Excel2010的体验应用系例，先从函数应用开始，因为编程的缘故，函数应用操作起来也算简单，不必太过费神。
加减乘除的函数最为简单，从常用的引用函数INDEX说起。
在【公式】选项功能区的函数库中找到[查找与引用]函数，然后在下拉列表中找到index命令，点击后会弹出选定参数的提示框。
注：在Excel2003版本中，index命令在工具菜单[插入]---〉[函数]中。
INDEX函数的参数有两组，一组是对数组（Array）的引用，一组是对单元格数值的引用（Reference），相比较来说，后者Reference参数用的最多，所以先分享参数Reference的使用。
学习函数应用的不二法门就是记住语法，牢记参数，虽然微软大神给我们准备了便捷的视图导航模式，但我们也必须要理解参数的含义。所以在Excel函数应用的系列中，我们先熟记掌握语法，然后是应用举例。
【语法一】对单元格数值的引用：
INDEX(reference, row_num, [column_num], [area_num])
【参数说明】：
Reference 必需。对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须将其用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数 row_num 或 column_num 分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,,column_num)。
Row_num 必需。引用中某行的行号，函数从该行返回一个引用。
Column_num 可选。引用中某列的列标，函数从该列返回一个引用。
Area_num 可选。选择引用中的一个区域，以从中返回 row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，依此类推。如果省略 area_num，则函数 INDEX 使用区域 1。
例如，如果引用描述的单元格为 (A1:B4,D1:E4,G1:H4)，则 area_num 1 为区域 A1:B4，area_num 2 为区域 D1:E4，而 area_num 3 为区域 G1:H4。
示例一：在图3中，我们将数据分为两个区域A2:C6和A8:C11。
例题1：选择区域 A2:C6 中第二行和第三列的交叉处单元格的值。（结果：38）
实现语法：= INDEX（A2:C6，2，3）
【语法二】：对数组的引用
INDEX(array, row_num, [column_num])
【参数说明】：
Array 必需。单元格区域或数组常量。如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选参数。如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。
Row_num 必需。选择数组中的某行，函数从该行返回数值。如果省略 row_num，则必须有 column_num。
Column_num 可选。选择数组中的某列，函数从该列返回数值。如果省略 column_num，则必须有 row_num。
理解了对单元格数值的引用再来看学习对数组的引用就简单多了，先做一个例题
例题2：选择第二个区域 A8:C11 中第二行和第二列的交叉处单元格的值，即单元格 B9 的内容。(结果为：3.55)
实现语法：=INDEX((A2:C6,A8:C11),2,2,2)
函数在单独使用的范围还是很有限的，但如果几个函数综合应用其威力无比，下面举两个引用加求和的应用
例题3：对第一个区域 A1:C11 中的第三列求和，即对 C1:C11 求和（结果为：216）。
实现语法：=SUM(INDEX(A1:C11,0,3,1))
例题4：返回以单元格 B2 开始到单元格区域 A2:C6 中第五行和第二列交叉处结束的单元格区域的和，即单元格区域 B2:B6 的和（结果为：2.52）
实现语法：=SUM(B2:INDEX(A2:C6,5,2))
在例题3和例题4中，其实我们直接用求和函数SUM就完全可以实现要求，为了向大家演示函数的综合应用，所以把简单的问题稍稍复杂了一下。本文出自 “” 博客，请务必保留此出处
了这篇文章
类别：┆阅读(0)┆评论(0)}