內(nèi)容摘要：按照小伙伴的需要整理和共享這些Excel通用電子表格公式，并提供電子表格通用函數(shù)公式Excel源文件。

本教程列出了典型的Excel公用電子表格公式以及相應(yīng)的案例和Excel源文件。

首先，查找重復(fù)的內(nèi)容

在單元格C2中輸入公式=IF(COUNTIF(A:A，A2)1，' repeat '，'')，可以查找列a中的重復(fù)名稱(chēng)。

第二，Excel使用出生日期計(jì)算年齡

在儲(chǔ)存格C2中，輸入下列公式：=trunc ((days 360 (B2，today()，false))/360，0)

第三，Excel提取出生日期

從輸入的18位身份證號(hào)碼中提取出生日期計(jì)算公式，并在單元格C2中輸入以下公式：=concatenate (mid (B2，7，4)，'/'，mid (B2，11，2)，'

第四，Excel提取性別

如果希望Excel從輸入的社會(huì)安全號(hào)碼中自動(dòng)提取性別，請(qǐng)?jiān)趩卧馛2中輸入以下公式：=if (len (B2)=15，if(mid(B2，15，1)，2)=1，公式

五、excel合計(jì)、平均值、等級(jí)、排名、最高分?jǐn)?shù)、最低分?jǐn)?shù)。

儲(chǔ)存格D2總計(jì)公式：=總計(jì)(b23360b12)

E2單元格平均公式：=平均值(b23360b12)

尋找F2儲(chǔ)存格最大分割公式：=max (b23360b12)

查找單元格G2的最小子公式：=min (b23360b12)

列h中的排名公式：=rank (B2，$ b $23360 $ b $12)

I列中的排名算法公式：=IF(B2=85、“優(yōu)秀”、IF(B2=74、“良好”、IF(B2=60、“通過(guò)”、“不合格”)

六、使用條件格式

在Excel中，如果一個(gè)單元格滿(mǎn)足特定條件，則如何在另一個(gè)單元格中顯示特定顏色(例如，A1 & gt1)在C1中顯示為紅色的方法0A11中，C1顯示為綠色。在A10中，C1顯示黃色。

單擊單元格C1，然后單擊“格式”“條件格式”，執(zhí)行以下操作：

1、條件1設(shè)定：公式=A1=1、點(diǎn)“格式”-“字體”-“顏色”，單擊紅色后點(diǎn)“確認(rèn)”。

2、條件2設(shè)置：公式=AND(A10、A11)，點(diǎn)“格式”-“字體”-“顏色”，單擊綠色后點(diǎn)“確定”。

3、條件3設(shè)定：公式=A10，點(diǎn)“格式”-“字體”-“顏色”，黃色點(diǎn)擊后點(diǎn)“確定”。

4、設(shè)定三個(gè)條件后，點(diǎn)擊“確定”。

第七，使用數(shù)據(jù)有效性

在EXCEL中，如何控制每列的數(shù)據(jù)長(zhǎng)度并避免重復(fù)輸入？

1、使用數(shù)據(jù)有效性定義數(shù)據(jù)長(zhǎng)度。

想要用鼠標(biāo)輸入的數(shù)據(jù)范圍，點(diǎn)“數(shù)據(jù)”-“有效性”-“設(shè)置”，

"有效性條件"設(shè)成"允許""文本長(zhǎng)度""等于""5"（具體條件可根據(jù)你的需要改變）。

還可以定義一些提示信息、出錯(cuò)警告信息和是否打開(kāi)中文輸入法等，定義好后點(diǎn)"確定"。

2、用條件格式避免重復(fù)。

選定A列，點(diǎn)"格式"->"條件格式"，將條件設(shè)成“公式=COUNTIF($A:$A,$A1)>1”，點(diǎn)"格式"->"字體"->"顏色"，選定紅色后點(diǎn)兩次"確定"。

這樣設(shè)定好后你輸入數(shù)據(jù)如果長(zhǎng)度不對(duì)會(huì)有提示，如果數(shù)據(jù)重復(fù)字體將會(huì)變成紅色。

第八，巧用函數(shù)組合進(jìn)行多條件的計(jì)數(shù)統(tǒng)計(jì)

統(tǒng)計(jì)“班級(jí)”為“二班”，“語(yǔ)文成績(jī)”大于等于104，“錄取結(jié)果”為“重本”的人數(shù)。

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

輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動(dòng)加上數(shù)組公式符號(hào)"{}"。

第九，求某一區(qū)域內(nèi)不重復(fù)的數(shù)據(jù)個(gè)數(shù)

例如求A2:A12范圍內(nèi)不重復(fù)姓名的個(gè)數(shù)，某個(gè)姓名重復(fù)多次出現(xiàn)只算一個(gè)。有兩種計(jì)算方法：

一是利用數(shù)組公式：=SUM(1/COUNTIF(A2:A12,A2:A12))，輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動(dòng)加上數(shù)組公式符號(hào)"{}"。

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

第十，做Excel目錄工作表

一個(gè)Excel工作薄中有許多工作表如何快速整理出一個(gè)目錄工作表呢？

第一步，Ctrl+F3出現(xiàn)自定義名稱(chēng)對(duì)話(huà)框，取名為X，在“引用位置”框中輸入：

=MID(1),FIND("]",GET.WORKBOOK(1))+1,100)，確定。

第二步，用HYPERLINK函數(shù)批量插入連接，方法：在目錄工作表（一般為第一個(gè)sheet）的A2單元格輸入公式：=HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))，將公式向下填充，直到出錯(cuò)為止，目錄就生成了。

最后，輕松一下，小編再給大家分享一個(gè)word小技巧。在word中連續(xù)輸入三個(gè)“~”，按下回車(chē)鍵可得到一條波浪線。連續(xù)輸入三個(gè)“-”，按下回車(chē)鍵可得到一條直線。連續(xù)輸入三個(gè)“=”，按下回車(chē)鍵可得到一條雙直線。

收工！本篇教程的所有Excel源文件請(qǐng)到QQ群：231768146下載。

如果你覺(jué)得文章不錯(cuò)，請(qǐng)分享給你的朋友們，獨(dú)樂(lè)樂(lè)不如眾樂(lè)樂(lè)！大家的分享和鼓勵(lì)，將是小編工作的動(dòng)力。