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

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

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

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

[2] 次大的連續(xù)長次數(shù), 公式為 :

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

3個“不重復”個數(shù)統(tǒng)計

=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

在一列有重復的姓名中，如何統(tǒng)計出具體有幾人

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

向下復制即可

用數(shù)組公式也可以解決呀:假設你要統(tǒng)計A1到A100可以這樣:

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

計數(shù)的問題

這個例子主要是計數(shù)的問題：共有三列數(shù)據(jù)，分別統(tǒng)計每列字母的個數(shù)、每列有幾個不

同的字母，最后把它們分別列出來。對每列字母個數(shù)統(tǒng)計，字符用COUNTA()，數(shù)字可以用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))),"

如何分班統(tǒng)計男女人數(shù)

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，合并數(shù)據(jù)后再利用countif公式對D列統(tǒng)計。 =COUNTIF($B$2:$B$446,E2)

在幾百幾千個數(shù)據(jù)中發(fā)現(xiàn)重復項

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

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

然后在格式中設置一個字體或圖案顏色,確定,這樣重復數(shù)據(jù)就變成了有色單元格。

統(tǒng)計互不相同的數(shù)據(jù)個數(shù)

例如,在 3 * 3 的區(qū)域中統(tǒng)計互不相同的數(shù)據(jù)個數(shù), 1 2 3 3 2 1 1 2 0

結果應為 4 (4 個互不相同的數(shù)據(jù)) 數(shù)組公式=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)

單個單元格中字符統(tǒng)計

假設 A1單元格中有數(shù)據(jù)"sdfsfjksfhweofiefondsfljsdfisdofjei" 如何用公式統(tǒng)計出A1單元格中有多個不重復的字符?

=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1)) 數(shù)組公式=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))

數(shù)據(jù)區(qū)包含某一字符的項的總和,該用什么公式

=sumif(a:a,"*"&"某一字符"&"*",數(shù)據(jù)區(qū))

67

函數(shù)如何實現(xiàn)分組編碼

對數(shù)值進行分組編碼

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

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 【數(shù)值取整及進位】 取整數(shù)函數(shù)

907.5；1034.2；1500要改變?yōu)?08；1035；1500公式為： =CEILING(A1,1)

907；1034；1500要改變?yōu)?10；1040；1500公式為： =CEILING(A1,10)

如果要保留到百位數(shù)，即改變?yōu)?000；1100；1500公式為： =CEILING(A1,100)

數(shù)值取整

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

=TRUNC(A1,0)

=ROUNDDOWN(A1,0)

求余數(shù)的函數(shù)

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

四舍五入公式

=ROUND()

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

=round(a1,0)*0.95

對數(shù)字進行四舍五入

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

如何實現(xiàn)“見分進元”

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

68

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