Excel常用電子表格公式大全(含案例、Excel源文件)

Excel常用電子表格公式大全(含案例、Excel源文件)
  內容提要:應小夥伴所需,小編整理了這些Excel常用電子表格公式分享給大家,並且提供了電子表格常用函數公式Excel源文件。

在本篇教程,小編羅列了咱們常見的Excel常用電子表格公式,並且附帶有相應的案例和Excel源文件提供給大家學習。

第一,查找重復內容

我們在C2單元格輸入公式:=IF(COUNTIF(A:A,A2)>1,’重復’,”),可以將A列重復的姓名尋找出來。

Excel常用電子表格公式大全(含案例、Excel源文件)

第二,Excel中用出生年月來計算年齡

在C2單元格輸入公式:=TRUNC((DAYS360(B2,TODAY(),FALSE))/360,0)

Excel常用電子表格公式大全(含案例、Excel源文件)

第三,Excel提取出生年月

從輸入的18位身份證號中提取出生年月計算公式,我們在C2單元格輸入公式:=CONCATENATE(MID(B2,7,4),’/’,MID(B2,11,2),’/’,MID(B2,13,2))

Excel常用電子表格公式大全(含案例、Excel源文件)

第四,Excel提取性別

從輸入的身份證號碼內讓Excel自動提取性別,我們在C2單元格輸入公式:=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,’男’,’女’),IF(MOD(MID(B2,17,1),2)=1,’男’,’女’))

Excel常用電子表格公式大全(含案例、Excel源文件)

第五,Excel求和、求平均、等級、排名、最高分、最低分

D2單元格求和公式:=SUM(B2:B12)

E2單元格求平均值公式:=AVERAGE(B2:B12)

F2單元格求最高分公式:=MAX(B2:B12)

G2單元格求最低分公式:=MIN(B2:B12)

H列的排名公式:=RANK(B2,$B$2:$B$12)

I列的等級算法公式:=IF(B2>=85,’優’,IF(B2>=74,’良’,IF(B2>=60,’及格’,’不及格’)))

Excel常用電子表格公式大全(含案例、Excel源文件)

第六,條件格式的使用

excel中當某一單元格符合特定條件,如何在另一單元格顯示特定的顏色比如:A1〉1時,C1顯示紅色;0<A1<1時,C1顯示綠色;A1<0時,C1顯示黃色。

方法如下:單擊C1單元格,點「格式」>「條件格式」:

1、條件1設為:公式 =A1=1,點「格式」->「字體」->「顏色」,點擊紅色後點「確定」。

2、條件2設為:公式 =AND(A1>0,A1<1),點「格式」->「字體」->「顏色」,點擊綠色後點「確定」。

3、條件3設為:公式 =A1<0,點「格式」->「字體」->「顏色」,點擊黃色後點「確定」。

4、三個條件設定好後,點「確定」即可。

第七,數據有效性的使用

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

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

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

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

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

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

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

第八,巧用函數組合進行多條件的計數統計

統計「班級」為「二班」,「語文成績」大於等於104,「錄取結果」為「重本」的人數。

公式:=SUM(IF((B2:B9999=’二班’)*(C2:C9999>=104)*(D2:D9999=’重本’),1,0))

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

Excel常用電子表格公式大全(含案例、Excel源文件)

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

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

一是利用數組公式:=SUM(1/COUNTIF(A2:A12,A2:A12)),輸入完公式後按Ctrl Shift Enter鍵,讓它自動加上數組公式符號'{}’。

二是利用乘積求和函數:=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))

Excel常用電子表格公式大全(含案例、Excel源文件)

第十,做Excel目錄工作表

一個Excel工作薄中有許多工作表如何快速整理出一個目錄工作表呢?

第一步,Ctrl F3出現自定義名稱對話框,取名為X,在「引用位置」框中輸入:

=MID(GET.WORKBOOK(1),FIND(‘]’,GET.WORKBOOK(1)) 1,100),確定。

Excel常用電子表格公式大全(含案例、Excel源文件)

第二步,用HYPERLINK函數批量插入連接,方法:在目錄工作表(一般為第一個sheet)的A2單元格輸入公式:=HYPERLINK(‘#”&INDEX(X,ROW())&”!A1’,INDEX(X,ROW())),將公式向下填充,直到出錯為止,目錄就生成了。

Excel常用電子表格公式大全(含案例、Excel源文件)

最後,輕鬆一下,小編再給大家分享一個word小技巧。在word中連續輸入三個「~」,按下回車鍵可得到一條波浪線。連續輸入三個「-」,按下回車鍵可得到一條直線。連續輸入三個「=」,按下回車鍵可得到一條雙直線。

收工!本篇教程的所有Excel源文件請到QQ群:231768146下載。

如果你覺得文章不錯,請分享給你的朋友們,獨樂樂不如眾樂樂!大家的分享和鼓勵,將是小編工作的動力。