Excel常用電子表格公式大全匯總
壹、Excel的基本公式
1，查找重復內容的公式:=IF(COUNTIF(A:A，A2)>1，“重復”，“重復”)。
2.用出生日期計算年齡公式:= trunc((天數360 (H6，“2009/8/30”，假))/360，0)。
3.18位身份證號的出生日期計算公式為= Concatenate (mid (e2，7，4)，"/"，mid (e2，11，2)，"/"，mid (e2，13。
4.讓系統自動從輸入的身份證號中提取性別。您可以輸入以下公式:
= if (len (C2) = 15，if (mod (mid (C2，15，1)，2) = 1，“男”，“女”)，if(mod(C2，6558)。C2？代表輸入ID號的單元格。
1，sum: =SUM(K2:K56)？對K2到K56的區域求和；
2.平均:=平均(K2:K56)？平均K2 K56的面積；
3.Rank: =RANK(K2，K$2:K$56)？對55名學生的成績進行排名；
4.等級:= IF(K2 & gt；=85，“優秀”，如果(K2 & gt=74，“好”，如果(K2 & gt=60，“通過”，“失敗”))
5.對該術語的總體評價:=K2*0.3+M2*0.3+N2*0.4？假設K、M、N列分別存儲學生的數據？平時壹般評論？、?期中考試？、?期末？三成就；
6.最高分:=MAX(K2:K56)？求K2到K56的最高分(55個學生)；
7.最低分:=MIN(K2:K56)？求K2到K56的最低分(55個學生)；
8.分數段人數統計:
(1) =COUNTIF(K2:K56，" 100 ")？用100積分求K2到K56的人數；假設結果存儲在K57細胞中；
(2) =COUNTIF(K2:K56，" & gt= 95”)-K57？用95 ~ 99.5分求K2到K56的人數；假設結果存儲在K58細胞中；
(3)=COUNTIF(K2:K56，" & gt=90")-SUM(K57:K58)？用90 ~ 94.5分求K2到K56的人數；假設結果存儲在K59細胞中；
(4)=COUNTIF(K2:K56，" & gt=85")-SUM(K57:K59)？用85 ~ 89.5分求K2到K56的人數；假設結果存儲在K60單元格中；
(5)=COUNTIF(K2:K56，" & gt=70")-SUM(K57:K60)？從K2到K56求70 ~ 84.5分的人數；假設結果存儲在K61單元格中；
(6)=COUNTIF(K2:K56，" & gt=60")-SUM(K57:K61)？用60 ~ 69.5分求K2到K56的人數；假設結果存儲在K62單元格中；
(7) =COUNTIF(K2:K56，" & lt60") ?求K2到K56的60分以下人數；假設結果存儲在K63單元格中；
註:COUNTIF函數還可以計算某個地區的男女生數量。
如=COUNTIF(C2:C351，"男")？求C2到C351的男性人數(共350人)；
9.優秀率:=SUM(K57:K60)/55*100。
10，通過率:=SUM(K57:K62)/55*100。
11，標準差:=STDEV(K2:K56)？求K2到K56 (55名學生)區域內的成績波動(數值越小，該班學生成績差異越小，反之，該班存在兩極分化)；
12，條件和:=SUMIF(B2:B56，“男”，K2:K56)？假設B列存儲學生性別，K列存儲學生成績，這個函數返回的結果就是求這個班男生成績之和；
13，多條件求和:{=SUM(IF(C3:C322= "男"，if (G3: G322 = 1，1，0))}？假設C列(C3:C322區)存儲學生的性別，G列(G3:G322區)存儲學生的班級代碼(1，2，3，4，5)，該函數返回的結果表示壹班男生人數；這是壹個數組函數。輸入後按Ctrl+Shift+Enter。{?}?)。？{}?不能手動輸入，只能用組合鍵。
14.根據出生日期自動計算年齡:= trunc((天數360 (d3，now ()))/360，0)
假設D列存儲了學生的出生日期，E列輸入這個函數來生成學生的全周歲。
15，Word中的三個小技巧:
(1)連續輸入三個？~?妳可以得到壹條波浪線。
(2)連續輸入三個？-?妳可以得到壹條直線。
連續輸入三個？=?妳可以得到壹條雙直線。
壹、當excel中的壹個單元格滿足壹定條件時，如何在另壹個單元格中顯示特定的顏色，例如:
當a1 > 1時，C1顯示紅色。
a 1 & lt；0，C1顯示黃色。
該方法如下:
1，單位擊中單元格C1，點擊？格式？& gt？條件格式？，條件1被設置為:
公式=A1=1
2.點？格式？-& gt；？字體？-& gt；？顏色？，點擊紅點？妳確定嗎？。
條件2設置為:
公式= and(a 1 >；0，a 1 & lt；1)
3.點？格式？-& gt；？字體？-& gt；？顏色？，點擊綠點？妳確定嗎？。
條件3設置為:
公式= a1
點？格式？-& gt；？字體？-& gt；？顏色？，點擊黃點？妳確定嗎？。
4.三個條件設置好之後，點擊？妳確定嗎？馬上出去。
第二，如何控制EXCEL中每列數據的長度，避免重復錄入。
1.用數據有效性定義數據長度。
用鼠標選擇要輸入的數據範圍，然後單擊“數據”-& gt；"有效性"->；“設置”和“有效性條件”為“允許”和“文本長度”等於“5”(具體條件可以根據您的需要更改)。
還可以定義壹些提示信息、錯誤警告信息以及是否打開中文輸入法，定義後點擊確定即可。
2.使用條件格式以避免重復。
選擇a列並點擊“格式”-->；“條件格式”，設置條件為？Formula =COUNTIF($A:$A，$A1)>1?，單擊“格式”-->；"字體"->；“顏色”，選擇紅色，點擊“確定”兩次。
此設置後，如果輸入的數據長度錯誤，會有提示，如果數據重復，字體會變紅。
三、如何識別EXCEL中B列和A列的區別？
(1)如果需要比較A列和B列同壹行的數據:
假設第壹行是標題，單擊單元格A2，然後單擊？格式？-& gt；？條件格式？，將條件設置為:
？單元格值不等於？=B2
點？格式？-& gt；？字體？-& gt；？顏色？，選擇紅色，點擊兩次？妳確定嗎？。
用格式畫筆將A2單元格的條件格式復制下來。
可以按照這種方法設置b列。
(2)如果將A列和B列作為壹個整體進行比較(即相同的數據不在同壹行):
假設第壹行是標題，單擊單元格A2，然後單擊？格式？-& gt；？條件格式？，將條件設置為:
？公式？=COUNTIF($B:$B，$A2)=0
點？格式？-& gt；？字體？-& gt；？顏色？，選擇紅色，點擊兩次？妳確定嗎？。
用格式畫筆將A2單元格的條件格式復制下來。
可以按照這種方法設置b列。
按照上述方法設置後，AB列的所有數據都不會被著色，A列有B列無A列或B列有A列無A列的數據會被標記為紅色字體。
四、EXCEL中批量按行排序怎麽處理？
假設有很多數據(數值)，每行都需要從大到小排序。如何操作？
因為按行排序和按列排序只能有壹個主關鍵字，所以只有在主關鍵字相同的情況下才能進行次關鍵字排序。所以這個問題不是靠排序就能解決的。解決方案如下:
1.假設您的數據在列A到E中，請在單元格F1中輸入公式:
=LARGE($A1:$E1，COLUMN(A1))
使用填充柄將公式向右下方復制到相應的範圍。
您的原始數據將按行的降序出現在F到J列中。如有需要可提供？粘貼特殊/值？復制到其他地方。
註意:步驟1中的公式可以根據妳的實際情況(數據範圍)進行修改。如果要從小到大排序，公式改為= small ($ a1: $ e1，column (a1))。
五、巧用函數組合計數和多條件計數。
例:第壹行是表頭，A列是？名字？b列是什麽？班級？，C列是什麽？語文成績？d列是什麽？錄取結果？，現在統計？班級？為了什麽？兩個？,?語文成績？大於等於104，？錄取結果？為了什麽？重這個？人數。統計結果存儲在該工作表的其他列中。
公式如下:
= SUM(IF((B2:b 9999 = " two ")*(C2:c 9999 & gt；= 104)*(D2:d 9999 = " duplicate ")，1，0))
輸入公式後，按Ctrl+Shift+Enter使其自動添加數組公式符號“{}”。
6.如何判斷單元格中是否包含指定的文本？
假設單元格A1具有“指定文本”，可以使用以下任何公式:
=IF(COUNTIF(A1，" * " & amp”指定文本”&；" *")=1，"是"，"無")
=IF(ISERROR(FIND("指定文本"，A1，1))，“無”，“是”)
找出某個區域中非重復數據的數量
比如求A1:A100範圍內不重復數據的個數，只有壹個數重復多次。有兩種計算方法:
壹種是使用數組公式:
= SUM(1/COUNTIF(A1:A100，a 1:a 100))
輸入公式後，按Ctrl+Shift+Enter使其自動添加數組公式符號“{}”。
第二種是使用乘積求和函數:
= sum product(1/COUNTIF(A1:A100，a 1:a 100))
七、壹個工作簿裏有很多工作表。如何快速整理出壹份目錄工作表？
1，用宏3.0取出每個工作表的名稱，方法:
Ctrl+F3顯示壹個名為X的自定義名稱對話框。參考位置？在框中輸入:
=MID(GET。工作簿(1)，查找("]"，獲取。工作簿(1))+1，100)
當然
2.用超鏈接功能批量插入鏈接，方法如下:
在目錄工作表(通常是第壹張工作表)中，在單元格A2中輸入公式:
=超鏈接(" # " & amp；INDEX(X，ROW())& amp；！A1 "，INDEX(X，ROW()))
向下填充公式，直到出現錯誤，然後生成目錄。
猜妳喜歡:
1匯總。18 Excel中最常用的公式操作技巧
2.excel函數公式大全下載excel函數公式學習
3.Excel常用電子表格公式大全匯總
4.史上最全面的excel函數匯總
5.Excel函數公式介紹