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

導讀：請問如何編寫公式求出A1到A10單元格中數字4連在一起的次數，[1]A1到A10單元格中,數字4連在一起,最大的連續次數,公式為:，[2]次大的連續長次數,公式為:，用數組公式也可以解決呀:假設你要統計A1到A100可以這樣:，公式分別為：=COUNT(A2:A12)=COUNTA(B2:B12)=COUN，輸入公式：=B2&C2，合併數據後再利用countif公式對D列統計，選公式(

如圖，請問如何編寫公式求出A1到A10單元格中數字4連在一起的次數，本例中答案應為3（A1到A3）和2（A9到A10）。

[1] A1到A10單元格中, 數字4連在一起, 最大的連續次數, 公式為 :

{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),1)}

[2] 次大的連續長次數, 公式為 :

{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),2)}

3個「不重複」個數統計

=SUM(--IF(MATCH(B$2:B$21,B$2:B$21,0)=ROW(B$2:B$21)-1,B$2:B$21>B2))+1 =SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1 =SUM(--(FREQUENCY(IF(B$2:B$21>B2,B$2:B$21),B$2:B$21)>0))+1

在一列有重複的姓名中，如何統計出具體有幾人

如果第一個張三在A1單元格，在B1處輸入： =IF(COUNTIF($A$1:A1,A1)>1,"",A1)

向下複製即可

用數組公式也可以解決呀:假設你要統計A1到A100可以這樣:

=sum(1/countif(a1:a100,a1:a100),然後按住crtl,shift,和回車就可以了。

計數的問題

這個例子主要是計數的問題：共有三列數據，分別統計每列字母的個數、每列有幾個不

同的字母，最後把它們分別列出來。對每列字母個數統計，字元用COUNTA()，數字可以用COUNT()和COUNTA()。公式分別為： =COUNT(A2:A12) =COUNTA(B2:B12) =COUNTA(C2:C12)

每列不相同的字母，公式分別為：

｛=SUM(1/COUNTIF(A$2:A$12,A$2:A$12))｝ ｛=SUM(1/COUNTIF(B$2:B$12,B$2:B$12))｝ ｛=SUM(1/COUNTIF(C$2:C$12,C$2:C$12))｝

分別列出來，公式分別為：

｛=IF(SUM(1/COUNTIF(A$2:A$12,A$2:A$12))>=ROW(A1),INDEX(A$2:A$12,SMALL(IF(ROW(A$2:A$12)-1=MATCH(A$2:A$12,A$2:A$12,0),ROW(A$2:A$12)-1,"0"),ROW(A1))),"END")｝

｛=IF(SUM(1/COUNTIF(B$2:B$12,B$2:B$12))>=ROW(B1),INDEX(B$2:B$12,SMALL(IF(R

65

OW(B$2:B$12)-1=MATCH(B$2:B$12,B$2:B$12,0),ROW(B$2:B$12)-1,"0"),ROW(B1))),"END")｝

｛=IF(SUM(1/COUNTIF(C$2:C$12,C$2:C$12))>=ROW(C1),INDEX(C$2:C$12,SMALL(IF(ROW(C$2:C$12)-1=MATCH(C$2:C$12,C$2:C$12,0),ROW(C$2:C$12)-1,"0"),ROW(C1))),"

如何分班統計男女人數

66

=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) 女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1)) 男｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝ 女｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$G$1))｝

男｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝ 女｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)｝

增加d列，輸入公式：=B2&C2，合併數據後再利用countif公式對D列統計。 =COUNTIF($B$2:$B$446,E2)

在幾百幾千個數據中發現重複項

我的意思不是查找功能,那個我會用,比如有幾百個人的名字輸入單元格中,但我面對那麼多名字真無法短時間內看出誰重複了,該如何辦?

假設判斷區域為A1:D10,格式/條件格式，選公式(不是數值)，輸入： =COUNTIF($A$1:$D$10,A1)>1

然後在格式中設置一個字體或圖案顏色,確定,這樣重複數據就變成了有色單元格。

統計互不相同的數據個數

例如,在 3 * 3 的區域中統計互不相同的數據個數, 1 2 3 3 2 1 1 2 0

結果應為 4 (4 個互不相同的數據) 數組公式=sum(1/countif(a1:c3,a1:c3))

還可以公式:

=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))

多個工作表的單元格合併計算

=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)

單個單元格中字元統計

假設 A1單元格中有數據"sdfsfjksfhweofiefondsfljsdfisdofjei" 如何用公式統計出A1單元格中有多個不重複的字元?

=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1)) 數組公式=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),A1)),,1))

這個公式只適用單元中的字元為小寫字母,給個通用點的

=SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2)))))

=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1))

數據區包含某一字元的項的總和,該用什麼公式

=sumif(a:a,"*"&"某一字元"&"*",數據區)

67

函數如何實現分組編碼

對數值進行分組編碼

=A2&TEXT(COUNTIF($A$2:A2,A2),"00")

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 【數值取整及進位】 取整數函數

907.5；1034.2；1500要改變為908；1035；1500公式為： =CEILING(A1,1)

907；1034；1500要改變為910；1040；1500公式為： =CEILING(A1,10)

如果要保留到百位數，即改變為1000；1100；1500公式為： =CEILING(A1,100)

數值取整

在單元格中要取整數（只取整數不用考慮四捨五入）用什麼函數呀？例如：10/4隻要顯示2就可以了！要考慮負數的因數呢？例如：(-10/4)要顯示－2而不是－3？怎麼辦？

=TRUNC(A1,0)

=ROUNDDOWN(A1,0)

求餘數的函數

比如：A1=28，A2=（A1÷6）的餘數=4，請問這個公式怎麼寫？ 解答：=MOD(28,6)

四捨五入公式

=ROUND()

=ROUND($B$1*A1,2) =ROUND(B1*A1,2) =round(a1,0)

=round(a1,0)*0.95

對數字進行四捨五入

對於數字進行四捨五入,可以使用INT（取整函數），但由於這個函數的定義是返回實數舍入後的整數值。因此，用INT函數進行四捨五入還是需要一些技巧的，也就是要加上0.5，才能達到取整的目的。公式應寫成： =INT(B2*100+0.5)/100

如何實現「見分進元」

在我們的工資中，有一項「合同補貼」，只要計算結果出現「分」值就在整數「元」進一位，也就是說3.01元進到4.00元，3.00元不變，整數「元」不變。 =IF((A3-INT(A3))>=0.3,IF((A3-INT(A3))>=0.8,1,0.5),0)+INT(A3)

68

五星文庫wxphp.com包含總結彙報、考試資料、專業文獻、IT計算機、計劃方案、黨團工作、教學教材、旅遊景點、出國留學以及office excel最常用函數公式技巧搜集大全(13.12.09更新)等內容。

推薦閱讀：