office excel最常用函數(shù)公式技巧搜集大全(13.12.09更新)導讀:數(shù)組公式{=sum((a1:a7>40)*(a1:a7<50))},在F1中的3要用什么公式能統(tǒng)計出來,請在編輯欄中選擇部分公式按F9觀察每一步的計算過程,你也可以用IF函數(shù),怎樣用公式求出表一中職工在表二中對應的工資、獎金和值班費的總額,再用公式導入表一的方法,我想知道能否在表一中用一個公式就可實現(xiàn),請問用什么函數(shù),1、使用下面的數(shù)組公式:{=SUM(IF($A$1:$E$10& 如何統(tǒng)計40-50歲的人的個數(shù) =countif(a:a,">40")-countif(a:a,">50") =SUM(COUNTIF(a:a,">"&{40,50})*{1,-1}) 數(shù)組公式{=sum((a1:a7>40)*(a1:a7<50))} =SUMPRODUCT((A1:A7>40)*(A1:A7<50)) 要統(tǒng)計出7歲的女生人數(shù) =COUNTIF(D2:D12,D2) =SUMPRODUCT((B2:B12="女")*(D2:D12=7)) 統(tǒng)計人數(shù) =COUNTA(A:A) =COUNTIF(A:A,"> ") 如何統(tǒng)計A1:A10,D1:D10中的人數(shù)? =COUNTA(A1:A10,D1:D10) 如何讓EXCEL自動從頭統(tǒng)計到當前單元格 情況如下: C列要根據A列的內容來統(tǒng)計B列的數(shù)據,范圍從A1:An,即當A列中An有數(shù)據時,Cn自動根據An的值,統(tǒng)計B1:Bn的數(shù)據。 {=SUM(INDIRECT("B1:B" & LARGE((A1:A65535<>"")*(ROW(A1:A65535)),1)))} 統(tǒng)計人數(shù) =LEN(SUBSTITUTE(B2,"、","")) 統(tǒng)計人數(shù) =COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50") =SUMPRODUCT((C3:C17>=20)*(C3:C17<=50)) =FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19) {=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})} 如何計算男20-50歲的人數(shù)? =SUMPRODUCT((B3:B17="男")*(C3:C17>=20)*(C3:C17<=50)) 求各分數(shù)段人數(shù) 90—100 =COUNTIF(B2:B43,">=90") 80—89 =COUNTIF(B2:B43,">=80")-COUNTIF(B2:B43,">=90") 70—79 =COUNTIF(B2:B43,">=70")-COUNTIF(B2:B43,">=80") 60—69 =COUNTIF(B2:B43,">=60")-COUNTIF(B2:B43,">=70") 50—59 =COUNTIF(B2:B43,">=50")-COUNTIF(B2:B43,">=60") 有什么方法統(tǒng)計偶數(shù) 例如:A1到E1有5個數(shù)如何統(tǒng)計著五個數(shù)中有幾個是偶數(shù) A B C D E F 1 50 15 8 11 15 3 在F1中的3要用什么公式能統(tǒng)計出來 統(tǒng)計偶數(shù)的個數(shù) {=COUNT(1/MOD(A1:E1-1,2))} {=Sum(Mod(a1:e1+1,2))} 將偶數(shù)轉化成奇數(shù),再求奇數(shù)的個數(shù)。 請在編輯欄中選擇部分公式按F9觀察每一步的計算過程。 {=SUM(--((A1:F1)/2=INT((A1:F1)/2)))} 算是一法,長了點 =SUMPRODUCT((MOD(A1:E1,2)=0)*1) =SUMPRODUCT(1-MOD(A1:E1,2)) 如何顯示 如果D2>20那E2就顯示$200、如果D2>30那E2就顯示$300依此類推 解答:=INT(D2/10)*100 ,當然,你的單元格格式設置成$格式就可以了。否則用,="$"&INT(D2/10)*100 則該單元格成字符型 。當然,你也可以用IF函數(shù),但它有7層的限制。= IF (D2>30, "300",IF(D2>20,"200")) 工資統(tǒng)計中的問題 問題:表一和表二中的職工姓名相同,但不在同一個位置上。怎樣用公式求出表一中職工在表二中對應的工資、獎金和值班費的總額。要求,不能用表二中先加入一列,然后求和,再用公式導入表一的方法。我想知道能否在表一中用一個公式就可實現(xiàn),而表二不動。 =SUMPRODUCT((表二!$B$3:$B$42=A3)*(表二!$C$3:$E$42)+(表二!$G$3:$G$42=A3)*(表二!$H$3:$J$42)) =IF(COUNTIF(表二!$B$3:$B$42,A3),SUMPRODUCT(VLOOKUP(A3,表 二!$B$3:$E$42,{2;3;4},)),SUMPRODUCT(VLOOKUP(A3,表二!$G$3:$J$42,{2;3;4},))) =IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0)),SUM(OFFSET(表二!$G$2,MATCH(A3,表二!$G$3:$G$42,0),1,,3)),SUM(OFFSET(表二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,,3))) =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),SUM(INDIRECT("表 二!H"&MATCH(A3,表二!$G$3:$G$42,0)+2&":J"&MATCH(A3,表 二!$G$3:$G$42,0)+2)),SUM(INDIRECT("表二!C"&MATCH(A3,表 二!$B$3:$B$42,0)+2&":J"&MATCH(A3,表二!$B$3:$B$42,0)+2))) =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),VLOOKUP(A3,表 二!$G$3:$J$42,4,0),VLOOKUP(A3,表二!$B$3:$F$42,4,0)) 統(tǒng)計數(shù)據問題一例 如果我想統(tǒng)計50個數(shù)據中大于某個值的數(shù)據個數(shù),(這個值是在使用時才輸入某個單元格的),請問用什么函數(shù)。 如數(shù)據單元格為A1:E10,值的單元格為A11。 1、使用下面的數(shù)組公式: {=SUM(IF($A$1:$E$10>$A$11,1))} 2、輸入以下函數(shù): =COUNTIF(A1:E10,">"&A11) 根據給定的條件,對數(shù)據進行合計 實例: 姓名 件數(shù) (姓名在B307-B313中;件數(shù)在C307-C313中) 李六 12 王武 50 李六 18 陳豐 187 李六 49 王武 135 陳豐 1584 目的: 對上面三個人的件數(shù)分別進行統(tǒng)計分析 步驟: 李六的: =SUMIF(B307:B313,B323,C307:C313) 王武的: =SUMIF(B307:B313,C323,C307:C313) 陳豐的: =SUMIF(B307:B313,D323,C307:C313) 姓名: 李六 王武 陳豐(分別在B323、C323、D323單元格中) 結果: 79 185 1771 十列數(shù)據合計成一列 =SUM(OFFSET($1,(ROW()-2)*10+1,,10,1)) 統(tǒng)計漢字字符個數(shù) 中國 A1中"中國",A2中"人民",A3中是空白,A4中是"幸福",A5,A6中是空白 人民258 258 幸福 247大家好 中國147 函數(shù) 結果 說明 =SUMPRODUCT(LENB(ASC(A1:A6))-LEN(A1:A6)) 11 僅統(tǒng)計漢字字符個數(shù) =SUMPRODUCT(LEN(A1:A6)) 23 如果還混雜有其它字符 關于取數(shù)
付款期=(購進日期+45天),但我們的付款期只有每月15和25號,如果按購進日期加上45天后不正好是付款日,那就得再往后延到最近的一個付款日,也就是15或25號。 {=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70)))} {=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70),999999))} {=MIN(IF((DAY(A2+ROW($45:$67))=15)+(DAY(A2+ROW($45:$67))=25),A2+ROW($45:$6 7)))} =IF(DAY(A2+45)<15,TEXT(A2+60-DAY(A2+45),"mm月dd日 "),TEXT(A2+70-DAY(A2+45),"mm月dd日")) =DATE(YEAR(A2+45),IF(DAY(A2+45)>25,MONTH(A2+45)+1,MONTH(A2+45)),IF(DAY(A2+45)<=15,15,IF(DAY(A2+45)<=25,25,15))) 統(tǒng)計單元格內不為空的格數(shù) 如下圖,怎么自動統(tǒng)計單元格內的―√‖,而空白的單元格則不計入內?
=counta(a2:a31),下拉 =countif(a2:a31,"√") =COUNTIF(a2:a31,"<>") 自動將銷量前十名的產品代號及銷量填入表中 如:產品代號在“B”列,銷量在“C”列 =INDIRECT("b"&MATCH(ROW(A1),$D$2:$D$20,0)+1) =INDIRECT("c"&MATCH(ROW(A1),$D$2:$D$20,0)+1) 統(tǒng)計最大的連續(xù)次數(shù) |
|