(Excel)常用函数公式及操作技巧之九：查询和查找引用

 (Excel)常用函数公式及操作技巧之九：

查询和查找引用

——通过知识共享树立个人品牌。

 查找顺序公式

?

1 2 3

=LOOKUP(2,1/(A1:A20<>0),A1:A20) =MATCH(7,A1:A20) =VLOOKUP(7,A1:B11,2)

怎样实现精确查询

用VLOOKUP

?

1	=VLOOKUP(B11,B3:F7,4,FALSE)

用LOOKUP

?

1	=LOOKUP(B11,B3:B7,E3:E7)

用MATCH+INDEX

?

1	=INDEX(E3:E7,MATCH(B11,B3:B7,0))

用INDIRECT+MATCH

?

1	=INDIRECT("E"&MATCH(B11,B3:B7,0)+2)

用OFFSET+MATCH

?

1	=OFFSET(E3,MATCH(B11,B3:B7,0)-1,0)

用INDIRECT+ADDRESS+MATCH

?

1	=INDIRECT(ADDRESS(MATCH(B11,B4:B7,0)+3,5))

用数组公式

?

1	=INDEX(E1:E7,MAX(IF((B4:B7=B11),ROW(B4:B7),0)))

查找及引用

如何查找并引用B2单元格中所显示日期当日的相应代码的值。

?

1	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的公式该如何设定？

?

1 2 3

=LOOKUP(9E+307,INDIRECT(A5&"!"&"B:B"))   B2 =IF(A2="","",LOOKUP(9E+307,INDIRECT(A2&"!B:B")))

怎么能方便的判断某个单元格中包含多少个指定的字符？

例：A1 中是“ASAFAG”，我希望计算出A1里面有多少个“A”......

?

1	=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))

如何用查找函数

一、要求: 利用公式从左表中查询相应的地区，结果放在H14单元格

?

1 2 3

=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单元格）。用数组公式：

?

1 2 3

=INDIRECT("G"&MAX((D14:D22=C25)*ROW(D14:D22)))   D25 =LOOKUP(2,1/(D14:D22=C25),G14:G22)

日期查找的问题

我有一个日期比如:2007/02/12,我想知道它减去一个固定天数比如6后,最接近它的一个星期四(只能提前)是多少号

2007/02/12的答案应该是2007/02/01而不是2007/02/08

日期在A1处，B1处输入：

?

1	=MAX((WEEKDAY(A1-6-{1,2,3,4,5,6,7},2)=4)*(A1-6-{1,2,3,4,5,6,7}))

A1  =2007/02/12

B1, 输入公式 :

?

1	=A1-6-MOD(WEEKDAY(A1-6,2)+3,7)

如何自动查找相同单元格内容

?

1 2	=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))

查找函数

?

1 2 3

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))

怎样对号入座（查找）

?

1 2 3 4 5 6

=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中填有:张三/李四/王五",如何通过公式来计算此单元格中共填有几个人姓名,每个人姓名之间用"/"符号分开,烦请相告.

?

1	=LEN(A1)-LEN(SUBSTITUTE(A1,"/",))+1

查找一列中最后一个数值

我想用公式知道,另一个表中"A"列最下面一个数是多少,就行了.用不定值的,因为还有数据有增加,

?

1 2	=LOOKUP(9E+307,Sheet2!A:A)——最后一个数值 =LOOKUP(REPT("座",255),Sheet2!A:A)——最后一个文本

或

?

1 2 3

=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

以下公式对数字有效：

重复数字：

?

1	=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,)

不重复数字：

?

1	=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结束。

重复数字：

?

1	=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,)

不重复数字：

?

1	=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中的人名,最好在原位置修改,或者在新的一列上生成也成,只要其他内容保持不变并按原来的顺序即可。

?

1 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

1=SMALL({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1)) 2=INDEX({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1)) 3=CHOOSE(COUNTIF($A$9:$A$13,"<="&A1),500,800,1100,1400,1700) 4=LOOKUP(A1,{0,10,20,30,40},{500,800,1100,1400,1700}) 5=MIN(4,INT(A1/10))*300+500<br>6=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,1100,1400,1700},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,1100,1400,1700}) 12=INDEX({500;800;1100;1400;1700},MATCH(A1,{0;10;20;30;40},1)) 13=CHOOSE(MATCH(A1,{0;10;20;30;40},1),500,800,1100,1400,1700) 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,1400,1700)))) 16=CHOOSE(SUM((A1>={0;10;20;30;40})*1),500,800,1100,1400,1700) 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,1100,1400,1700)

　　新增公式：

?

1 2 3 4 5 6 7

19=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,1100,1400,1700) 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,1100,1400,1700}) 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

查找的函数（查找末位词组）

（数组公式：）

?

1 2	=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,)

（数组公式：）

?

1 2	=RIGHT(A2,MATCH(1,FIND(" ",RIGHT(" "&A2,ROW($1:$100))),)-1) =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))   （好）

其实这个公式的思路, 是可以变化的，改变REPT( )中的数值, 可以返回, 指定空格位置後的数据，比如：

A1  =一 二 三 四 五 六 七 八 九

10个普通公式, 分别为 :

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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                               

公式

?

1	=LOOKUP(1,0/($A$1:$A$100=C2),$B$1:$B$100)

两列数据查找相同值对应的位置

?

1	=MATCH(B1,A:A，0)

查找数据公式两个(基本查找函数为VLOOKUP,MATCH)

（1）、根据符合行列两个条件查找对应结果

?

1	=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)

（2）、根据符合两列数据查找对应结果(为数组公式)

?

1	=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0))