office excel最常用函數公式技巧搜集大全(13.12.09更新)20

  office excel最常用函數公式技巧搜集大全(13.12.09更新)

導讀:如何在一個單元格中,統計某個字符出現的次數,例如:單元格A1中填有:張三/李四/,我想用公式知道,另一個表中”A”列最下面一個數是多少,就行了,以下公式對數字有效:,都是數組公式,IF函數替換法總結,大家最先想到IF函數,但用IF一般要長長的公式,現總結一下IF之替換公式,其中部分公式通用,部分公式有局限性,(前18個條件公式,新增公式:,查找的函數(查找末位詞組),(數組

office excel最常用函數公式技巧搜集大全(13.12.09更新)20

)

=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

查找單元格A和B裡重復及不重復的字符

正確答案:重復字符-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;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

查找的函數(查找末位詞組)

(數組公式:)=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))

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

【輸入數據的技巧】

談談Excel輸入的技巧

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

【1】輸入文本

Excel單元格中的文本包括任何中西文文字或字母以及數字、空格和非數字字符的組合,每個單元格中最多可容納32000個字符數。雖然在Excel中輸入文本和在其它應用程序中沒有什麼本質區別,但是還是有一些差異,比如我們在Word、PowerPoint的表格中,當在單元格中輸入文本後,按回車鍵表示一個段落的結束,光標會自動移到本單元格中下一段落的開頭,在Excel的單元格中輸入文本時,按一下回車鍵卻表示結束當前單元格的輸入,光標會自動移到當前單元格的下一個單元格,出現這種情況時,如果你是想在單元格中分行,則必須在單元格中輸入硬回車,即按住Alt鍵的同時按回車鍵。

【2】輸入分數

幾乎在所有的文檔中,分數格式通常用一道斜杠來分界分子與分母,其格式為「分子/分母」,在Excel中日期的輸入方法也是用斜杠來區分年月日的,比如在單元格中輸入「1/2」,按回車鍵則顯示「1月2日」,為了避免將輸入的分數與日期混淆,我們在單元格中輸入分數時,要在分數前輸入「0」(零)以示區別,並且在「0」和分子之間要有一個空格隔開,比如我們在輸入1/2時,則應該輸入「0 1/2」。如果在單元格中輸入「8 1/2」,則在單元格中顯示「8 1/2」,而在編輯欄中顯示「8.5」。

【3】輸入負數

在單元格中輸入負數時,可在負數前輸入「-」作標識,也可將數字置在()括號內來標識,比如在單元格中輸入「(88)」,按一下回車鍵,則會自動顯示為「-88」。

【4】輸入小數

在輸入小數時,用戶可以向平常一樣使用小數點,還可以利用逗號分隔千位、百萬位等,當輸入帶有逗號的數字時,在編輯欄並不顯示出來,而只在單元格中顯示。當你需要輸入大量帶有固定小數位的數字或帶有固定位數的以「0」字符串結尾的數字時,可以採用下面的方法:選擇「工具」、「選項」命令,打開「選項」對話框,單擊「編輯」標籤,選中「自動設置小數點」復選框,並在「位數」微調框中輸入或選擇要顯示在小數點右面的位數,如果要在輸入比較大的數字後自動添零,可指定一個負數值作為要添加的零的個數,比如要在單元格中輸入「88」後自動添加3個零,變成「88 000」,就在「位數」微調框中輸入「-3」,相反,如果要在輸入「88」後自動添加3位小數,變成「0.088」,則要在「位數」微調框中輸入「3」。另外,在完成輸入帶有小數位或結尾零字符串的數字後,應清除對「自動設置小數