2015年1月13日 星期二

最齊全最實用的Excel公式大全,學會以後不但可以省時還輕鬆方便許多!


1.          查找重復內容公式:=IFCOUNTIFA:AA2>1"重復""")。


2.          用出生年月來計算年齡公式:=TRUNC((DAYS360H6"2009/8/30"FALSE))/3600


3.          求和: =SUMK2:K56  -對K2K56這一區域進行求和;


4.          平均數: =AVERAGEK2:K56  -對K2 K56這一區域求平均數;


5.          排名: =RANKK2K$2:K$56  -對55名學生的成績進行排名;


6.          等級: =IFK2>=85""IFK2>=74""IFK2>=60"及格""不及格")))


7.          學期總評: =K2*0.3+M2*0.3+N2*0.4 -假設K列、M列和N列分別存放著學生的「平時總評」、「期中」、「期末」三項成績;


8.          最高分: =MAXK2:K56 -求K2K56區域(55名學生)的最高分;


9.          最低分: =MINK2:K56 -求K2K56區域(55名學生)的最低分;


10.     優秀率: =SUMK57:K60/55*100


11.     及格率: =SUMK57:K62/55*100


12.     標準差: =STDEVK2:K56 -求K2K56區域(55人)的成績波動情況(數值越小,說明該班學生間的成績差異較小,反之,說明該班存在兩極分化);


13.     條件求和: =SUMIFB2:B56""K2:K56 -假設B列存放學生的性別,K列存放學生的分數,則此函數返回的結果表示求該班男生的成績之和;


14.     多條件求和: =SUMIFC3:C322=""IFG3:G322=110)))} -假設C列(C3:C322區域)存放學生的性別,G列(G3:G322區域)存放學生所在班級代碼(12345),則此函數返回的結果表示求一班的男生人數;這是一個數組函數,輸完後要按CtrlShiftEnter組合鍵(產生「{……}」)。「{}」不能手工輸入,只能用組合鍵產生。


15.     根據出生日期自動計算周歲:=TRUNC((DAYS360D3NOW )))/3600


16.     Word中三個小竅門:連續輸入三個「~」可得一條波浪線。


                                                連續輸入三個「-」可得一條直線。


連續輸入三個「=」可得一條雙直線。


 EXCEL中如何控制每列數據的長度並避免重復錄入


1、用數據有效性定義數據長度。


用鼠標選定你要輸入的數據範圍,點"數據"->"有效性"->"設置""有效性條件"設成"允許""文本長度""等於""5"(具體條件可根據你的需要改變)。


還可以定義一些提示訊息、出錯警告訊息和是否打開中文輸入法等,定義好後點"確定"


2、用條件格式避免重復。


選定A列,點"格式"->"條件格式",將條件設成「公式=COUNTIF$A:$A$A1>1」,點"格式"->"字體"->"顏色",選定紅色後點兩次"確定"


這樣設定好後你輸入數據如果長度不對會有提示,如果數據重復字體將會變成紅色。


 EXCEL中怎樣批量地處理按行排序


假定有大量的數據(數值),需要將每一行按從大到小排序,如何操作?


由於按行排序與按列排序都是只能有一個主關鍵字,主關鍵字相同時才能按次關鍵字排序。所以,這一問題不能用排序來解決。解決方法如下:


1、假定你的數據在AE列,請在F1單元格輸入公式:


=LARGE$A1:$E1COLUMNA1))


用填充柄將公式向右向下復制到相應範圍。


你原有數據將按行從大到小排序出現在FJ列。如有需要可用「選擇性粘貼/數值」復制到其他地方。


注:第1步的公式可根據你的實際情況(數據範圍)作相應的修改。如果要從小到大排序,公式改為:=SMALL$A1:$E1COLUMNA1))


 、巧用函數組合進行多條件的計數統計


例:第一行為表頭,A列是「姓名」,B列是「班級」,C列是「語文成績」,D列是「錄取結果」,現在要統計「班級」為「二」,「語文成績」大於等於104,「錄取結果」為「重本」的人數。統計結果存放在本工作表的其他列。


公式如下:


=SUMIF((B2:B9999=""*C2:C9999>=104*D2:D9999="重本"),10))


輸入完公式後按Ctrl+Shift+Enter鍵,讓它自動加上數組公式符號"{}"


 三,如何判斷單元格裡是否包含指定文本?


假定對A1單元格進行判斷有無"指定文本",以下任一公式均可:


=IFCOUNTIFA1"*"&"指定文本"&"*"=1""""


=IFISERRORFIND"指定文本"A11)),""""


求某一區域內不重復的數據個數


例如求A1:A100範圍內不重復數據的個數,某個數重復多次出現只算一個。有兩種計算方法:


一是利用數組公式:


=SUM1/COUNTIFA1:A100A1:A100))


輸入完公式後按Ctrl+Shift+Enter鍵,讓它自動加上數組公式符號"{}"


二是利用乘積求和函數:


=SUMPRODUCT1/COUNTIFA1:A100A1:A100))


學會以上這些公式後,製作Excel表格真的變得超級簡單。雖然看起來很複雜,但是使用一段時間後就會明白這些公式的方便之處。


來源: http://www.bomb01.com/article/7012/%E6%9C%80%E9%BD%8A%E5%85%A8%E6%9C%80%E5%AF%A6%E7%94%A8%E7%9A%84Excel%E5%85%AC%E5%BC%8F%E5%A4%A7%E5%85%A8%EF%BC%8C%E5%AD%B8%E6%9C%83%E4%BB%A5%E5%BE%8C%E4%B8%8D%E4%BD%86%E5%8F%AF%E4%BB%A5%E7%9C%81%E6%99%82%E9%82%84%E8%BC%95%E9%AC%86%E6%96%B9%E4%BE%BF%E8%A8%B1%E5%A4%9A%EF%BC%81