1、工作表單制作如何使用電子表格excel中的一些常用計算公式一、年齡計算公式IFYEARE22008MONTHE2802008-YEARE2IFMONTHE280-1其中e2為單元格2008為當前年份二、身份證號中提取出生年月MIDJ1174年MIDJ11112月MIDJ11132日三、班級平均分公式IFCOUNTIFCCJ260SUMIFCCJ26D/COUNTIFCCJ26四、Excel表中身份證號碼提取出生年月、性別、年齡的使用技巧如何從Excel表中的身份證號碼中提取出生年月、性別、年齡呢看了本文就知道了。方法一1.Excel表中用身份證號 TOC o 1-5 h z 碼中取其中的號碼用

2、mid文本開始字符所取字符數(shù)2.15位身份證號從第7位到第12位是出生年月日年份用的是2位數(shù)。18位身份證號從第7位到第14位是出生的年月日年份用的是4位數(shù)。從身份證號碼中提取出表示出生年、月、日的數(shù)字用文本函數(shù)MID可以達到目的。MID-從指定位置開始提取指定個數(shù)的字符從左向右。對一個身份證號碼是15位或是18位進行判斷用邏輯判斷函數(shù)IF和字符個數(shù)計算函數(shù)LEN輔助使用可以完成。綜合上述分析可以通過下述操作完成形如1978-12-24樣式的出生年月日自動提取假如身份證號數(shù)據(jù)在A1單元格在B1單元格中編輯公式IFLENA115MIDA172-MIDA192-MIDA1112MIDA174-MI

3、DA1112-MIDA1132回車確認即可。如果只要年-月格式公式可以修改為IFLENA115MIDA172-MIDA192MIDA174-MIDA11123.這是根據(jù)身份證號碼15位和18位通用自動提取性別的自編公式供需要的朋友參考說明公式中的B2是身份證號根據(jù)身份證號碼求性別IFLENB215IFMODVALUERIGHTB2320女男IFLENB218IFMODVALUEMIDB215120女男身份證錯根據(jù)身份證號碼求年齡IFLENB2152007-VALUEMIDB272ifLENB2182007-VALUEMIDB274身份證錯4.Excel表中用YearMonthDay函數(shù)取相應的

4、年月日數(shù)據(jù)方法二這是根據(jù)身份證號碼15位和18位通用自動提取性別和出生年月的自編公式供需要的網(wǎng)友參考說明公式中的B2是身份證號1、根據(jù)身份證號碼求性別IFLENB215IFMODVALUERIGHTB2320女男IFLENB218IFMODVALUEMIDB215320女男身份證錯2、根據(jù)身份證號碼求出生年月IFLENB215CONCATENATE19MIDB272.MIDB292IFLENB218CONCATENATEMIDB274.MIDB2112身份證錯3、根據(jù)身份證號碼求年齡IFLENB215yearnow-1900-VALUEMIDB272ifLENB218yearnow-VALUE

5、MIDB274身份證錯在Excel電子表格中我們需要到達某一單元格一般是使用鼠標拖動滾動條來進行但如果數(shù)據(jù)范圍超出一屏幕顯示范圍或數(shù)據(jù)行數(shù)非常多時想快速定位到某一單元格可要有點麻煩了。這時候我們可以使用定位快捷鍵CtrlG功能迅速到達想要的單元格。定位是一種選定單元格的方式主要用來選定位置相對無規(guī)則但條件有規(guī)則的單元格或區(qū)域。例1需要選中Y2009單元格或快速移動到Y2009單元格我們可以使用編輯/定位菜單在引用位置里輸入Y2009后按回車即可。例2需要選中Y列的20042009行的單元格我們按照相同的方法在引用位置里輸入Y2004Y2009按回車即可。例3需要選中2009行的單元格我們可以在

6、引用位置里輸入20092009按回車即可。例4需要選中20042009行的單元格我們可以在引用位置里輸入20042009按回車即可。這是定位功能中最簡單的引用位置。下面來看看定位的幾個應用定位條件應用一定位公式在實際工作中我們會設許多公式為防止一不小心修改了這些公式我們會把公式單元格字體設置為其他顏色一個個去設置當然不符合高效辦公的原則。定位就算幫上大忙了。操作步驟如下1、編輯/定位/定位條件2、選擇公式/確定所有的公式單元格被選中3、格式/單元格/字體/顏色紅色/確定應用二定位空值定位常量在實際工作中我們會為一個表格每隔一個固定的行數(shù)本例中以一行為例后面插入一行一行一行的插入當然也不符合高效

7、辦公的原則。二次定位就能解決此問題操作步驟如下1、在表格第一列前面插入一列做為過渡列在A1處輸入1A2位置為空選中AA2當鼠標移到右下角變?yōu)樾螤顣r向下拉拉的比數(shù)據(jù)行多2、選中第一列定位/定位條件/空值/確定此時你會發(fā)現(xiàn)第一列的偶數(shù)單元格全部被選中3、插入/行4、選中第一列定位/定位條件/常量/確定此時你會發(fā)現(xiàn)第一列的有數(shù)值的單元格全部被選中5、插入/行這時你會發(fā)現(xiàn)除了過渡列的數(shù)據(jù)外其他數(shù)據(jù)行每行下面都多出了一個空白行這時我們只需刪除這個過渡列預想的目的就達到了。應用三定位最后一個單元格大的表格中使用此功能實現(xiàn)快速移動光標到最后一個單元格應用四定位可見單元格在分類匯總下選定我們需要的單元格后選擇

8、復制按鈕到指定位置后選擇粘貼按鈕粘貼的結果不是顯示的結果而依然是原始數(shù)據(jù)這是因為我們在選定的時候不wZa.m精英博客0_WIERUq僅選定了顯示的單元格而且隱藏的單元格也被選中了我們使用定位功能就能很好的解決這個問題。1、定位/定位條件/可見單元格2、編輯/復制3、到指定位置后編輯/粘貼4、選中粘貼后的數(shù)據(jù)然后數(shù)據(jù)/分類匯總/全部刪除我們要的可見單元格即被成功的復制出來了。通過這幾個例子我們不難明白定位是選中單元格的一種方法只不過它選中的這些單元格區(qū)域不集中用傳統(tǒng)的鼠標選擇效率低難度大這些單元格雖然分散但本身具有共性或分散的有規(guī)律這些共性或規(guī)律就是定位條件窗口下的選擇條件。lzsy下面來看一個

9、關于定位空值的小flash教程lzsy然后看一個定位空值的具體活用設計工資條lzsy再看一個活用定位公式禁止編輯EXCEL中含有大量公式時不僅但心自己或他人修改了這些公式而且不想讓他人看見這些公式而其他無公式單元格允許隨意編輯。1、選中表格當然也可以選中整張工作表2、選擇格式菜單的單元格功能的保護選項然后取消鎖定3、再打開編輯菜單的定位功能或者直接按CtrlG4、在彈出的對話框中選擇定位條件5、選擇公式然后點擊確定6、此時將看到所有帶有公式的單元格都被選中了7、再次打開格式菜單的單元格功能的保護選項然后將鎖定和隱藏全都選中8、由于只有在工作表被保護的狀態(tài)下保護單元格和隱藏公式才能生效因此我們還需要將這張工作表給保護起來選擇工具菜單的保護中的保護工作表設置保護密碼。當工作表被保護后如果在已經(jīng)設定為鎖定狀態(tài)的單元格點擊的話就會出現(xiàn)工作表被保護的提示在編輯欄也看不到公式了。但是