導(dǎo)讀：如何在一個(gè)單元格中,統(tǒng)計(jì)某個(gè)字符出現(xiàn)的次數(shù),例如:單元格A1中填有:張三/李四/,，我想用公式知道,另一個(gè)表中"A"列最下面一個(gè)數(shù)是多少,就行了,，以下公式對(duì)數(shù)字有效：，都是數(shù)組公式,，IF函數(shù)替換法總結(jié),，大家最先想到IF函數(shù)，但用IF一般要長(zhǎng)長(zhǎng)的公式，現(xiàn)總結(jié)一下IF之替換公式,，其中部分公式通用,，部分公式有局限性，(前18個(gè)條件公式,，新增公式：,，查找的函數(shù)（查找末位詞組），（數(shù)組

)

=IF(ISERROR(VLOOKUP(C2,k!B2:Z2189,2,FALSE)),"",VLOOKUP(C2,k!B2:Z2189,2,FALSE))

怎樣對(duì)號(hào)入座（查找）

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

一個(gè)文本查找的問題

如何在一個(gè)單元格中,統(tǒng)計(jì)某個(gè)字符出現(xiàn)的次數(shù),例如:單元格A1中填有:張三/李四/王五",如何通過公式來計(jì)算此單元格中共填有幾個(gè)人姓名,每個(gè)人姓名之間用"/"符號(hào)分開,煩請(qǐng)相告.

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

查找一列中最后一個(gè)數(shù)值

我想用公式知道,另一個(gè)表中"A"列最下面一個(gè)數(shù)是多少,就行了.用不定值的,因?yàn)檫€有數(shù)據(jù)有增加,

=LOOKUP(9E+307,Sheet2!A:A)——最后一個(gè)數(shù)值

=LOOKUP(REPT("座",255),Sheet2!A:A)——最后一個(gè)文本

或

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

查找重復(fù)字符

兩組數(shù)值

A B

1245689 0134578

查找單元格Ａ和Ｂ里重復(fù)及不重復(fù)的字符

正確答案：重復(fù)字符－1458

不重復(fù)字符－023679

以下公式對(duì)數(shù)字有效：

重復(fù)數(shù)字：

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

不重復(fù)數(shù)字：

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

都是數(shù)組公式,，按Ctrl+shift+enter結(jié)束,。

重復(fù)數(shù)字：

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

不重復(fù)數(shù)字：

=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,) 請(qǐng)教查找替換問題

把表1中字符在4個(gè)以上的字段(含4個(gè))查找出來,替換成表2中的人名,最好在原位置修改,或者在新的一列上生成也成,只要其他內(nèi)容保持不變并按原來的順序即可。

=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函數(shù)替換法總結(jié)

條件說明：小于10返回500,，小于20返回800,，小于30返回1100,，小于40返回1400,，大于40返回1700

類似于以上要求，大家最先想到IF函數(shù),，這也本屬IF專長(zhǎng),。但用IF一般要長(zhǎng)長(zhǎng)的公式，且計(jì)算較慢?，F(xiàn)總結(jié)一下IF之替換公式,，望能拋磚引玉，在我的倡導(dǎo)下各位提供更完善的方案,。其中部分公式通用,，部分公式有局限性，請(qǐng)看說明,。(前18個(gè)條件公式,，根據(jù)速度，排名如下)

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

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)

新增公式：

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

查找的函數(shù)（查找末位詞組）

（數(shù)組公式：）=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,) （數(shù)組公式：）=RIGHT(A2,MATCH(1,FIND(" ",RIGHT(" "&A2,ROW($1:$100))),)-1) =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)) （好）

其實(shí)這個(gè)公式的思路, 是可以變化的,，改變REPT( )中的數(shù)值, 可以返回, 指定空格位置後的數(shù)據(jù),，比如：

A1 =一 二 三 四 五 六 七 八 九

10個(gè)普通公式, 分別為 :

1=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",100)),100)) 返回第0空格位置後的數(shù)據(jù)>一 二 三 四 五

六 七 八 九

2=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100)) 返回第8 空格位置後的數(shù)據(jù)>九3=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",40)),100)) 返回第7 空格位置後的數(shù)據(jù)>八 九

4=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),100)) 返回第6 空格位置後的數(shù)據(jù)>七 八 九 5=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",23)),100)) 返回第5空格位置後的數(shù)據(jù)>六 七 八 九 6=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",18)),100)) 返回第4 空格位置後的數(shù)據(jù)>五 六 七 八

九

7=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",14)),100)) 返回第3 空格位置後的數(shù)據(jù)>四 五 六 七

八 九

8=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",12)),100)) 返回第2 空格位置後的數(shù)據(jù)>三 四 五 六

七 八 九

9=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",11)),100)) 返回第1 空格位置後的數(shù)據(jù)>二 三 四 五

六 七 八 九

10=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),100)) 返回第0空格位置後的數(shù)據(jù)>一 二 三 四 五

六 七 八 九

怎樣從原始數(shù)據(jù)中自動(dòng)獲取最后一個(gè)數(shù)據(jù)

原始數(shù)據(jù)

a 12

b 1221

c 12

d 33

a 33 自動(dòng)獲取

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)

兩列數(shù)據(jù)查找相同值對(duì)應(yīng)的位置

=MATCH(B1,A:A，0)

查找數(shù)據(jù)公式兩個(gè)(基本查找函數(shù)為VLOOKUP,MATCH)

（1）,、根據(jù)符合行列兩個(gè)條件查找對(duì)應(yīng)結(jié)果

=VLOOKUP(H1,，A1：E7，MATCH(I1,，A1：E1,，0)，F(xiàn)ALSE)

（2）,、根據(jù)符合兩列數(shù)據(jù)查找對(duì)應(yīng)結(jié)果(為數(shù)組公式)

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

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

【輸入數(shù)據(jù)的技巧】

談?wù)凟xcel輸入的技巧

在Excel工作表的單元格中，可以使用兩種最基本的數(shù)據(jù)格式：常數(shù)和公式,。常數(shù)是指文字,、數(shù)字,、日期和時(shí)間等數(shù)據(jù)，還可以包括邏輯值和錯(cuò)誤值,，每種數(shù)據(jù)都有它特定的格式和輸入方法,，為了使用戶對(duì)輸入數(shù)據(jù)有一個(gè)明確的認(rèn)識(shí)，有必要來介紹一下在Excel中輸入各種類型數(shù)據(jù)的方法和技巧,。

【1】輸入文本

Excel單元格中的文本包括任何中西文文字或字母以及數(shù)字,、空格和非數(shù)字字符的組合，每個(gè)單元格中最多可容納32000個(gè)字符數(shù),。雖然在Excel中輸入文本和在其它應(yīng)用程序中沒有什么本質(zhì)區(qū)別,，但是還是有一些差異，比如我們?cè)赪ord,、PowerPoint的表格中,，當(dāng)在單元格中輸入文本后，按回車鍵表示一個(gè)段落的結(jié)束,，光標(biāo)會(huì)自動(dòng)移到本單元格中下一段落的開頭,，在Excel的單元格中輸入文本時(shí)，按一下回車鍵卻表示結(jié)束當(dāng)前單元格的輸入,，光標(biāo)會(huì)自動(dòng)移到當(dāng)前單元格的下一個(gè)單元格,，出現(xiàn)這種情況時(shí)，如果你是想在單元格中分行,，則必須在單元格中輸入硬回車,，即按住Alt鍵的同時(shí)按回車鍵。

【2】輸入分?jǐn)?shù)

幾乎在所有的文檔中,，分?jǐn)?shù)格式通常用一道斜杠來分界分子與分母,，其格式為“分子/分母”，在Excel中日期的輸入方法也是用斜杠來區(qū)分年月日的,，比如在單元格中輸入“1/2”,，按回車鍵則顯示“1月2日”，為了避免將輸入的分?jǐn)?shù)與日期混淆,，我們?cè)趩卧裰休斎敕謹(jǐn)?shù)時(shí),，要在分?jǐn)?shù)前輸入“0”（零）以示區(qū)別，并且在“0”和分子之間要有一個(gè)空格隔開,，比如我們?cè)谳斎?/2時(shí),，則應(yīng)該輸入“0 1/2”。如果在單元格中輸入“8 1/2”,，則在單元格中顯示“8 1/2”,，而在編輯欄中顯示“8.5”。

【3】輸入負(fù)數(shù)

在單元格中輸入負(fù)數(shù)時(shí)，可在負(fù)數(shù)前輸入“-”作標(biāo)識(shí),，也可將數(shù)字置在（）括號(hào)內(nèi)來標(biāo)識(shí),，比如在單元格中輸入“（88）”，按一下回車鍵,，則會(huì)自動(dòng)顯示為“-88”,。

【4】輸入小數(shù)

在輸入小數(shù)時(shí)，用戶可以向平常一樣使用小數(shù)點(diǎn),，還可以利用逗號(hào)分隔千位,、百萬位等，當(dāng)輸入帶有逗號(hào)的數(shù)字時(shí),，在編輯欄并不顯示出來,，而只在單元格中顯示。當(dāng)你需要輸入大量帶有固定小數(shù)位的數(shù)字或帶有固定位數(shù)的以“0”字符串結(jié)尾的數(shù)字時(shí),，可以采用下面的方法：選擇“工具”,、“選項(xiàng)”命令，打開“選項(xiàng)”對(duì)話框,，單擊“編輯”標(biāo)簽,，選中“自動(dòng)設(shè)置小數(shù)點(diǎn)”復(fù)選框,，并在“位數(shù)”微調(diào)框中輸入或選擇要顯示在小數(shù)點(diǎn)右面的位數(shù),，如果要在輸入比較大的數(shù)字后自動(dòng)添零，可指定一個(gè)負(fù)數(shù)值作為要添加的零的個(gè)數(shù),，比如要在單元格中輸入“88”后自動(dòng)添加3個(gè)零,，變成“88 000”，就在“位數(shù)”微調(diào)框中輸入“-3”,，相反,，如果要在輸入“88”后自動(dòng)添加3位小數(shù)，變成“0.088”,，則要在“位數(shù)”微調(diào)框中輸入“3”,。另外，在完成輸入帶有小數(shù)位或結(jié)尾零字符串的數(shù)字后,，應(yīng)清除對(duì)“自動(dòng)設(shè)置小數(shù)