excel中的一些常用計(jì)算公式

一、年齡計(jì)算公式：

=IF((YEAR(E2)=2008)*(MONTH(E2)>8),0,2008-YEAR(E2) IF(MONTH(E2)<=8,0,-1))

其中e2為單元格，2008為當(dāng)前年份

二、身份證號中提取出生年月= MID(J11, 7, 4) & \"年\" & MID(J11, 11, 2) & \"月\" & MID(J11, 13, 2)&\"日\"

三、 班級平均分公式

=IF(COUNTIF($C$2:$C$24,J26)=0,\"\C$2:$C$24,J26))

四、Excel表中身份證號碼提取出生年月、性別、年齡的使用技巧

如何從Excel表中的身份證號碼中提取出生年月、性別、年齡呢？看了本文就知道了。

方法一：

1.Excel表中用身份證號碼中取其中的號碼用:MID(文本,開始字符,所取字符數(shù));

2.15位身份證號從第7位到第12位是出生年月日，年份用的是2位數(shù)。

18位身份證號從第7位到第14位是出生的年月日，年份用的是4位數(shù)。

從身份證號碼中提取出表示出生年、月、日的數(shù)字，用文本函數(shù)MID()可以達(dá)到目的。MID()——從指定位置開始提取指定個(gè)數(shù)的字符（從左向右）。

對一個(gè)身份證號碼是15位或是18位進(jìn)行判斷，用邏輯判斷函數(shù)IF()和字符個(gè)數(shù)計(jì)算函數(shù)LEN()輔助使用可以完成。綜合上述分析，可以通過下述操作，完成形如1978-12-24樣式的出生年月日自動提?。?p>假如身份證號數(shù)據(jù)在A1單元格，在B1單元格中編輯公式

=IF(LEN(A1)=15,MID(A1,7,2)&\"-\"&MID(A1,9,2)&\"-\"&MID(A1,11,2),MID(A1,7,4)&\"-\"&MID(A1,11,2)&\"-\"&MID(A1,13,2))

回車確認(rèn)即可。

如果只要“年-月”格式，公式可以修改為

=IF(LEN(A1)=15,MID(A1,7,2)&\"-\"&MID(A1,9,2),MID(A1,7,4)&\"-\"&MID(A1,11,2))

3.這是根據(jù)身份證號碼（15位和18位通用）自動提取性別的自編公式，供需要的朋友參考：

說明：公式中的B2是身份證號

根據(jù)身份證號碼求性別：

=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,\"女\男

\"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,\"女\男\(zhòng)"),\"身份證錯\"))

根據(jù)身份證號碼求年齡：

=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),\"身份證錯\"))

4.Excel表中用Year\\Month\\Day函數(shù)取相應(yīng)的年月日數(shù)據(jù);

方法二：

這是根據(jù)身份證號碼（15位和18位通用）自動提取性別和出生年月的自編公式，供需要的網(wǎng)友參考：

說明：公式中的B2是身份證號

1、根據(jù)身份證號碼求性別：

=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,\"女\男

\"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,\"女\男\(zhòng)"),\"身份證錯\"))

2、根據(jù)身份證號碼求出生年月：

=IF(LEN(B2)=15,CONCATENATE(\"19\18,CONCATENATE(MID(B2,7,4),\".\身份證錯\"))

3、根據(jù)身份證號碼求年齡：

=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),\"身份證錯\"))

在Excel電子表格中，我們需要到達(dá)某一單元格，一般是使用鼠標(biāo)拖動滾動條來進(jìn)行，但如果數(shù)據(jù)范圍超出一屏幕顯示范圍或數(shù)據(jù)行數(shù)非常多時(shí)，想快速定位到某一單元格可要有點(diǎn)麻煩了。這時(shí)候我們可以使用“定位”（快捷鍵Ctrl G）功能迅速到達(dá)想要的單元格。

定位是一種選定單元格的方式，主要用來選定“位置相對無規(guī)則但條件有規(guī)則的單元格或區(qū)域”。

例1：需要選中Y2009單元格（或快速移動到Y(jié)2009單元格），我們可以使用“編輯/定位”菜單，在引用位置里輸入“Y2009”后按回車即可。

例2：需要選中Y列的2004～2009行的單元格，我們按照相同的方法，在引用位置里輸入“Y2004:Y2009”按回車即可。

例3：需要選中2009行的單元格，我們可以在引用位置里輸入“2009:2009”按回車即可。

例4：需要選中2004～2009行的單元格，我們可以在引用位置里輸入“2004:2009”按回車即可。

這是“定位”功能中最簡單的“引用位置”。

下面來看看“定位”的幾個(gè)應(yīng)用（“定位條件”）：

應(yīng)用一：定位“公式”

在實(shí)際工作中，我們會設(shè)許多公式，為防止一不小心修改了這些公式，我們會把公式單元格字體設(shè)置為其他顏色，一個(gè)個(gè)去設(shè)置當(dāng)然不符合高效辦公的原則。定位就算幫上大忙了。操作步驟如下：

1、 編輯/定位/定位條件；

2、 選擇“公式”/確定，所有的公式單元格被選中；

3、 格式/單元格/字體/顏色“紅色”/確定；

應(yīng)用二：定位“空值”，定位“常量”

在實(shí)際工作中，我們會為一個(gè)表格每隔一個(gè)固定的行數(shù)（本例中以一行為例）后面插入一行，一行一行的插入當(dāng)然也不符合高效辦公的原則。二次定位就能解決此問題，操作步驟如下：

1、 在表格第一列前面插入一列做為過渡列，在A1處輸入“1”，A2位置為空，選中“A?。篈2”，當(dāng)鼠標(biāo)移到右下角變?yōu)椤?”形狀時(shí)，向下拉，拉的比數(shù)據(jù)行多；

2、 選中第一列，“定位”/“定位條件”/“空值”/“確定”，此時(shí)你會發(fā)現(xiàn)第一列的

偶數(shù)單元格全部被選中；

3、 “插入”/“行”

4、 選中第一列，“定位”/“定位條件”/“常量”/“確定”，此時(shí)你會發(fā)現(xiàn)第一列的有數(shù)值的單元格全部被選中；

5、 “插入”/“行”

這時(shí)你會發(fā)現(xiàn)除了過渡列的數(shù)據(jù)外，其他數(shù)據(jù)行每行下面都多出了一個(gè)空白行，這時(shí)我們只需刪除這個(gè)過渡列，預(yù)想的目的就達(dá)到了。

應(yīng)用三：定位“最后一個(gè)單元格”

大的表格中，使用此功能，實(shí)現(xiàn)快速移動光標(biāo)到“最后一個(gè)單元格”

應(yīng)用四：定位“可見單元格”

在分類匯總下，選定我們需要的單元格后，選擇“復(fù)制”按鈕，到指定位置后，選擇“粘貼”按鈕，粘貼的結(jié)果不是顯示的結(jié)果，而依然是原始數(shù)據(jù)，這是因?yàn)槲覀冊谶x定的時(shí)候，不僅選定了顯示的單元格，而且隱藏的單元格也被選中了，我們使用“定位”功能就能很好的解決這個(gè)問題。

1、“定位”/“定位條件”/“可見單元格”；

2、“編輯”/“復(fù)制”；

3、到指定位置后，“編輯”/“粘貼”；

4、選中粘貼后的數(shù)據(jù)，然后“數(shù)據(jù)”/“分類匯總”/“全部刪除”，我們要的可見單元格即被成功的復(fù)制出來了。

通過這幾個(gè)例子，我們不難明白“定位”是選中單元格的一種方法，只不過它選中的這些單元格區(qū)域不集中，用傳統(tǒng)的鼠標(biāo)選擇效率低難度大，這些單元格雖然分散但本身具有共性或分散的有規(guī)律，這些共性或規(guī)律就是定位條件窗口下的選擇條件。

下面來看一個(gè)關(guān)于定位空值的小flash教程：

然后看一個(gè)定位空值的具體活用（設(shè)計(jì)工資條）：

再看一個(gè)活用定位“公式”，禁止編輯已設(shè)定的公式的方法：

當(dāng)EXCEL中含有大量公式時(shí)，不僅但心自己或他人修改了這些公式，而且不想讓他人看見這些公式，而其他無公式單元格允許隨意編輯。

1、 選中表格，當(dāng)然也可以選中整張工作表；

2、 選擇“格式”菜單的“單元格”功能的“保護(hù)”選項(xiàng)，然后取消“鎖定”；

3、 再打開“編輯”菜單的“定位”功能（或者直接按Ctrl G）；

4、 在彈出的對話框中選擇“定位條件”；

5、 選擇“公式”，然后點(diǎn)擊“確定”；

6、 此時(shí)，將看到所有帶有公式的單元格都被選中了；

7、 再次打開“格式”菜單的“單元格”功能的“保護(hù)”選項(xiàng)，然后將“鎖定”和“隱藏”全都選中；

8、 由于只有在工作表被保護(hù)的狀態(tài)下，保護(hù)單元格和隱藏公式才能生效，因此，我們還需要將這張工作表給保護(hù)起來，選擇“工具”菜單的“保護(hù)”中的“保護(hù)工作表”，設(shè)置保護(hù)密碼。

當(dāng)工作表被保護(hù)后，如果在已經(jīng)設(shè)定為鎖定狀態(tài)的單元格點(diǎn)擊的話，就會出現(xiàn)工作表被保護(hù)的提示，在編輯欄也看不到公式了。但是如果點(diǎn)擊的是未鎖定的單元格，看看是不是仍然可以修改？！