Excel常用函數(shù)公式及技巧

《常用函數(shù)公式及技巧》

常

用

函

數(shù)

公式及技巧

《常用函數(shù)公式及技巧》

目錄

從身份證號(hào)碼中提取出生年月日.................................................-1-

從身份證號(hào)碼中提取出性別......................................................-1-

從身份證號(hào)碼中進(jìn)行年齡判斷...................................................-2-

按身份證號(hào)號(hào)碼計(jì)算至今天年齡.................................................-2-

按身份證號(hào)分男女年齡段........................................................-2-

根據(jù)出生年月計(jì)算年齡..........................................................-3-

根據(jù)出生年月推算生肖..........................................................-3-

如何求出一個(gè)人到某指定日期的周歲？...........................................-3-

計(jì)算距離退休年齡的公式........................................................-3-

求工齡.........................................................................-3-

計(jì)算工齡.......................................................................-4-

年齡及工齡計(jì)算................................................................-4-

自動(dòng)顯示當(dāng)前H期公式..........................................................-5-

如何在單元格中自動(dòng)填入當(dāng)前日期...............................................-5-

如何判斷某日是否星期天........................................................-5-

某個(gè)日期是星期兒..............................................................-5-

什么函數(shù)可以顯示當(dāng)前星期.....................................................-5-

求本月天數(shù)....................................................................-5-

用公式算出除去當(dāng)月星期六、星期日以外的天數(shù)..................................-6-

顯示昨天的日期................................................................-6-

關(guān)于取日期....................................................................-6-

如何對(duì)日期進(jìn)行上、中、下旬區(qū)分...............................................-6-

如何獲取一個(gè)月的最大天數(shù).....................................................-6-

日期格式轉(zhuǎn)換公式..............................................................-6-

把198405轉(zhuǎn)換成1984.05...............................................................................................................-7-

象22怎樣轉(zhuǎn)換成22日？轉(zhuǎn)成當(dāng)年當(dāng)月的日子....................................-8-

將“2006年5月”轉(zhuǎn)換成“2006年05月”...........................................-8-

將“1968年6月12日”轉(zhuǎn)換為“1968/6/12”格式.....................................-8-

將“1968年6月12日”轉(zhuǎn)換為“1968-6-12”格式.....................................-8-

將1993-12-28的日期格式轉(zhuǎn)換成1993年12月....................................-8-

將“1978-5-2”包含年月日的日期轉(zhuǎn)換成“197805”只有年月的格式.....................-8-

要將“99.08.15”格式轉(zhuǎn)換成“1999.08.15”如何做....................................-8-

要保持2005/8/6格式............................................................-8-

將“二OO三年十二月二十五日”轉(zhuǎn)為"2003-12-25”格式，...........................-9-

日期格式轉(zhuǎn)換..................................................................-9-

日期格式轉(zhuǎn)換問(wèn)題..............................................................-9-

要想自動(dòng)取得“編制日期：XXXX年X月X日”....................................-9-

自動(dòng)排序......................................................................-10-

按奇偶數(shù)排序.................................................................-10-

自動(dòng)生成序號(hào).................................................................-10-

如何自動(dòng)標(biāo)示A欄中的數(shù)字大小排序？.........................................-10-

如何設(shè)置自動(dòng)排序.............................................................-lO-

II

《常用函數(shù)公式及技巧》

重復(fù)數(shù)據(jù)得到唯一的排位序列..................................................-10-

按字符數(shù)量排序..............................................................-11-

排序字母與數(shù)字的混合內(nèi)容....................................................-11-

隨機(jī)排序.....................................................................-11-

排序的問(wèn)題...................................................................-11-

怎樣才能讓數(shù)列自動(dòng)加數(shù)......................................................-12-

一個(gè)排序問(wèn)題................................................................-12-

數(shù)字的自動(dòng)排序,插入后不變?..................................................-12-

根據(jù)規(guī)律的重復(fù)的姓名列產(chǎn)生自動(dòng)序號(hào).........................................-13-

排名的函數(shù)...................................................................-14-

自動(dòng)排名公式................................................................-14-

平均分及總分排名............................................................-14-

求名次排名...................................................................-14-

排名次.......................................................................-14-

根據(jù)分?jǐn)?shù)進(jìn)行普通排名........................................................-15-

對(duì)于普通排名分?jǐn)?shù)相同時(shí)，按順序進(jìn)行不重復(fù)排名...............................-15-

依分?jǐn)?shù)比高低名次成績(jī)排名....................................................-15-

美國(guó)式排名...................................................................-16-

中國(guó)式排名...................................................................-16-

求最精簡(jiǎn)的自動(dòng)排名公式......................................................-16-

排序后排名...................................................................-17-

位次排名.....................................................................-17-

根據(jù)雙列成績(jī)進(jìn)行共同排名....................................................-17-

在雙列間排名................................................................-17-

等次排名.....................................................................-17-

不等次排名（行小排先）.......................................................-18-

不等次排名（行大排先）.......................................................-18-

順次排名.....................................................................-18-

有并列排名...................................................................-18-

無(wú)并列排名...................................................................-19-

有并列分段排名..............................................................-19-

無(wú)并列分段排名..............................................................-19-

成績(jī)排名.....................................................................-19-

如何排名.....................................................................-20-

數(shù)據(jù)排名（隔幾行排名）......................................................-20-

根據(jù)分?jǐn)?shù)進(jìn)行倒排名..........................................................-21-

倒數(shù)排名函數(shù)是什么..........................................................-21-

如何實(shí)現(xiàn)每日各車間產(chǎn)量的排名...............................................-21-

分?jǐn)?shù)相同時(shí)按照一科的分?jǐn)?shù)進(jìn)行排名...........................................-21-

篩選后自動(dòng)產(chǎn)生序列號(hào)并匯總..................................................-21-

如何篩選奇數(shù)行..............................................................-22-

函數(shù)篩選姓名................................................................-22-

名次篩選.....................................................................-22-

如何實(shí)現(xiàn)快速定位（篩選出不重復(fù)值）.........................................-22-

III

《常用函數(shù)公式及技巧》

如何請(qǐng)?jiān)贜列中列出A1:L9中每列都存在的數(shù)值................................-22-

自動(dòng)為性別編號(hào)的問(wèn)題........................................................-22-

EXCEL中如何刪除*號(hào)........................................................-23-

去空格函數(shù)...................................................................-23-

如何去掉字符和單元格里的空格................................................-24-

怎樣快速去除表中不同行和列的空格...........................................-24-

如何禁止輸入空格............................................................-24-

代替單元格中字符串..........................................................-24-

把單元格中的數(shù)字轉(zhuǎn)變成為特定的字符格式.....................................-25-

把有六百多個(gè)單元格的一列，變成一頁(yè)的多列...................................-25-

將N列變M列公式歸納為....................................................-25-

一列變四列...................................................................-25-

四列變一列...................................................................-26-

重復(fù)四次填充.................................................................-26-

多行數(shù)據(jù)排成一列............................................................-26-

將單元格一列分為多列........................................................-26-

把單元格編號(hào)中的小寫字母變成大寫字母.......................................-27-

讓姓名左右對(duì)齊..............................................................-27-

數(shù)字居中而小數(shù)點(diǎn)又對(duì)齊......................................................-27-

格式一單元格一數(shù)字一自定義一???.???-確定.....................................-27-

格式一單元格一數(shù)字一自定義一???.0?-確定......................................-28-

比較兩個(gè)單元格內(nèi)容是否一致..................................................-28-

怎么樣設(shè)置才能讓這一列的每個(gè)單元格只能輸入12位............................-28-

如何讓工作表奇數(shù)行背景是紅色偶數(shù)行背景是藍(lán)色...............................-28-

計(jì)算特定的一組單元格中，滿足條件的單元格的個(gè)數(shù).............................-28-

把文本格式的數(shù)字轉(zhuǎn)換成真正的數(shù)字...........................................-29-

設(shè)置頁(yè)碼.....................................................................-29-

Excel表格里如何插入頁(yè)碼的？..................................................-29-

如何設(shè)置頁(yè)腳首頁(yè)為第5頁(yè)....................................................-29-

表格的頁(yè)腳問(wèn)題..............................................................-29-

無(wú)拘無(wú)束的頁(yè)眉..............................................................-30-

判斷當(dāng)前行是否大于分頁(yè)符所在行.............................................-30-

取得當(dāng)前行所在頁(yè)............................................................-30-

取得總頁(yè).....................................................................-31-

打印表頭.....................................................................-31-

Excel打印中如何不顯示錯(cuò)誤值符號(hào)............................................-31-

對(duì)于一些不可打印的字符的處理................................................-31-

用那個(gè)函數(shù)可將個(gè)位數(shù)前面的零值顯示出來(lái)？....................................-32-

如果你要在A3的前面插入100行..............................................-32-

請(qǐng)問(wèn)如何每隔30行粘貼一新行.................................................-32-

在工作表里有連續(xù)10行數(shù)據(jù)，現(xiàn)在要每行間隔2行..............................-32-

一個(gè)大表每一行下面需要加一行空行，怎么加最方便.............................-32-

Excel中插入空白行...........................................................-33-

快速刪除工作表中的空行......................................................-33-

IV

《常用函數(shù)公式及技巧》

快速刪除空行.................................................................-33-

一次刪完Excel里面多出很多的空白行..........................................-34-

每30行為一頁(yè)并加上一個(gè)標(biāo)題如何實(shí)現(xiàn)........................................-34-

如何實(shí)現(xiàn)隔行都加上標(biāo)題項(xiàng)....................................................-34-

如何把標(biāo)簽頁(yè)去掉的?.........................................................-34-

去掉默認(rèn)的表格線（網(wǎng)線）.......................................................-35-

表格的框線...................................................................-35-

列標(biāo)的標(biāo)識(shí)變了..............................................................-35-

符號(hào)的意義...................................................................-35-

雙擊格式刷竟也能COPY文本（不是文本格式）....................................-35-

查找+格式刷的妙用...........................................................-36-

光標(biāo)移動(dòng).....................................................................-36-

最后一行為文本..............................................................-36-

最后行為數(shù)字..............................................................-36-

如何在EXCEL中快速定位最后一行數(shù)據(jù)........................................-36-

如何用SUN函數(shù)快速求和.....................................................-36-

在Excel中快速查看所有工作表公式............................................-37-

在Excel中設(shè)置行間距.........................................................-37-

怎樣同時(shí)改變多行行高........................................................-37-

快速換行.....................................................................-37-

讓文本換行...................................................................-37-

在Excel中行列快速轉(zhuǎn)換.......................................................-38-

將原有列中的內(nèi)容倒置過(guò)來(lái)....................................................-38-

快速回到A1單元格...........................................................-38-

復(fù)制粘貼中回車鍵的妙用......................................................-38-

一次選中批注單元格..........................................................-38-

一次在所有單元格中插入批注..................................................-38-

在公式中插入批注............................................................-38-

不連續(xù)單元格填充同一數(shù)據(jù)....................................................-39-

空白行的填充.................................................................-39-

怎樣用函數(shù)向下實(shí)現(xiàn)自動(dòng)填充..................................................-39-

怎么設(shè)置自動(dòng)保存............................................................-39-

避免輸入網(wǎng)址和電子郵件地址時(shí)的超鏈接.......................................-40-

單元格前面自動(dòng)加了等號(hào)......................................................-40-

加蓋公章.....................................................................-40-

查找+格式刷的妙用...........................................................-41-

Excel中鼠標(biāo)雙擊妙用.........................................................-41-

Excel中快速定位的技巧實(shí)例...................................................-43-

在Excel中插入Flash時(shí)鐘的步驟...............................................-43-

小寫數(shù)字轉(zhuǎn)換成人民幣大寫....................................................-43-

輕輕松松制作超復(fù)雜Excel表頭................................................-46-

截取單元格里某個(gè)字符后的字符................................................-48-

截取字符的公式..............................................................-48-

如何確定*號(hào)后的數(shù)字.........................................................-48-

V

《常用函數(shù)公式及技巧》

如何提取數(shù)字中間的數(shù)........................................................-48-

三個(gè)數(shù)中，如何取出中間那個(gè)....................................................-48-

取數(shù)值后三位公式.............................................................-48-

取數(shù)函數(shù).....................................................................-48-

如何把單元格中的數(shù)字提取出來(lái)（字符串中不連續(xù)）.............................-49-

數(shù)字在字符串中不連續(xù)如何提取數(shù)字............................................-49-

用如何提取前后的字符......................................................-49-

怎樣刪去：后的文字..........................................................-49-

怎樣只取之后的文字？......................................................-50-

獲取單元格內(nèi)容中字符串......................................................-50-

如何提取一串?dāng)?shù)字中的幾位數(shù)字（字符）.......................................-50-

如何把一個(gè)單元格中的數(shù)字挑出來(lái)..............................................-51-

分割文本.....................................................................-51-

按照給定的位數(shù)，截?cái)嘈?shù)點(diǎn)后的數(shù)字..........................................-51-

單元格數(shù)字提取問(wèn)題..........................................................-51-

以關(guān)鍵字提取名稱.............................................................-52-

如何把文本中的前幾個(gè)字符去除................................................-52-

對(duì)一列中的文字統(tǒng)一去掉最后一個(gè)字............................................-52-

如何去掉單元格中的第一個(gè)數(shù)字................................................-53-

論一下取最后一個(gè)單詞的方法..................................................-53-

如何去掉單元格最后一位數(shù)字..................................................-53-

如何在一列已經(jīng)輸入的數(shù)據(jù)前添加“P”......................................................................................-53-

什么函數(shù)可以插入字符........................................................-54-

如何在數(shù)據(jù)前添加“*”號(hào).......................................................-54-

數(shù)字前面加上數(shù)字.............................................................-54-

數(shù)字如何拆分.................................................................-55-

單元格中的數(shù)據(jù)拆分..........................................................-55-

單元格的拆分.................................................................-55-

如何拆分字組.................................................................-55-

用連字符“&”來(lái)合并文本.......................................................-56-

怎樣把不同格式不同位置的內(nèi)容合并到一個(gè)單元格內(nèi).............................-56-

把不同單元格的內(nèi)容合到一個(gè)單元格里..........................................-56-

合并與拆分...................................................................-56-

合并不同單元格的內(nèi)容........................................................-57-

關(guān)于文字在表格中的組合......................................................-57-

求拆解合并公式...............................................................-57-

如何把字母和數(shù)字分開?.......................................................-57-

把分散在各單元格的內(nèi)容合在一個(gè)單元格中.....................................-57-

多個(gè)工作表的單元格合并計(jì)算..................................................-58-

通過(guò)條件格式將小計(jì)和總計(jì)的行設(shè)為不同的顏色.................................-58-

如何實(shí)現(xiàn)這樣的條件格式......................................................-58-

隔行不同字體顏色怎么設(shè)置....................................................-58-

讓不同類型數(shù)據(jù)用不同顏色顯示................................................-58-

如何做這樣的EXCEL表（顏色交叉）............................................-59-

VI

《常用函數(shù)公式及技巧》

條件格式.....................................................................-59-

使用條件格式設(shè)置顏色條紋....................................................-59-

相同數(shù)據(jù)的提示..............................................................-60-

如何做到小于10顯示二位小數(shù)，大于10顯示一位小數(shù)...........................-61-

如何根據(jù)數(shù)值的正負(fù)加上'符號(hào)............................................-61-

自定義單元格格式............................................................-61-

將單元格中的數(shù)全部變成萬(wàn)元表示..............................................-61-

有何辦法實(shí)現(xiàn)將一張表中的數(shù)據(jù)由元的單位轉(zhuǎn)換為萬(wàn)元...........................-61-

常用的自定義格式............................................................-62-

自定義格式...................................................................-63-

怎樣定義格式.................................................................-64-

在工具按鈕之間設(shè)置分隔線....................................................-64-

自定義區(qū)域?yàn)槊恳豁?yè)的標(biāo)題....................................................-64-

一個(gè)單元格內(nèi)格式問(wèn)題........................................................-65-

定制單元格數(shù)字顯示格式......................................................-65-

巧用定位選條件單元格........................................................-65-

工作表的標(biāo)簽的字體和大小可以更改嗎.........................................-65-

sheet1工作表的Al、A2、A3單元格分別鏈接到sheet2、sheet3、sheet4.........................-65-

經(jīng)驗(yàn)技巧.....................................................................-66-

隔行用不同顏色顯示，請(qǐng)問(wèn)如何做..............................................-66-

將單元格設(shè)置為有“凸出”的效果或“凹進(jìn)去”的效果...............................-66-

在Excel中設(shè)計(jì)彩色數(shù)字.......................................................-66-

定義名稱的妙處..............................................................-67-

零值問(wèn)題.....................................................................-68-

個(gè)調(diào)稅公式...................................................................-69-

算物價(jià)的函數(shù).................................................................-69-

自動(dòng)計(jì)算應(yīng)收款滯納金........................................................-70-

淘汰率.......................................................................-70-

應(yīng)用公積金的一個(gè)函數(shù)........................................................-70-

如何利用公式將數(shù)值轉(zhuǎn)為百分比格式...........................................-70-

比高得分公式.................................................................-70-

自動(dòng)評(píng)定獎(jiǎng)級(jí).................................................................-71-

對(duì)帶有單位的數(shù)據(jù)如何進(jìn)行求和................................................-71-

對(duì)a列動(dòng)態(tài)求和..............................................................-71-

動(dòng)態(tài)求和公式.................................................................-71-

列的跳躍求和.................................................................-71-

有規(guī)律的隔行求和............................................................-71-

如何實(shí)現(xiàn)奇數(shù)行或偶數(shù)行求和..................................................-72-

單數(shù)行求和...................................................................-72-

統(tǒng)計(jì)偶數(shù)單元格合計(jì)數(shù)值......................................................-72-

隔行求和公式設(shè)置............................................................-72-

隔列將相同項(xiàng)目進(jìn)行求和......................................................-72-

隔行或隔列加總..............................................................-73-

請(qǐng)問(wèn)如何在一百行內(nèi)做隔行相加................................................-73-

VII

《常用函數(shù)公式及技巧》

如何將間隔一定的列的數(shù)據(jù)相加呢..............................................-73-

隔列求和(A、B歹U).........................................................-73-

隔列求和的公式...............................................................-73-

隔列求和.....................................................................-73-

關(guān)于隔行、隔列求和的問(wèn)題....................................................-74-

EXCEL中求兩列的對(duì)應(yīng)元素乘積之和...........................................-74-

計(jì)算900-1000之間的數(shù)值之和.................................................-74-

雙條件求和...................................................................-74-

如何實(shí)現(xiàn)這樣的條件求和......................................................-75-

ALA10數(shù)字顯為文本格式時(shí)，如何求和........................................-75-

求和.........................................................................-75-

去掉其中兩個(gè)最大值和兩個(gè)最小值，再求和.....................................-76-

去掉兩個(gè)最高分、最低分，顯示出被去掉的分?jǐn)?shù).................................-77-

永恒的求和...................................................................-77-

按字體顏色求和...............................................................-77-

如何分班統(tǒng)計(jì)男女人數(shù)........................................................-77-

統(tǒng)計(jì)數(shù)值大于等于80的單元格數(shù)目.............................................-78-

計(jì)算出A1里有兒個(gè)abc.......................................................-78-

有條件統(tǒng)計(jì)...................................................................-78-

如何統(tǒng)計(jì)各年齡段的數(shù)量......................................................-78-

如何計(jì)算20-50歲的人數(shù)?......................................................-79-

如何統(tǒng)計(jì)40-50歲的人的個(gè)數(shù)..................................................-79-

要統(tǒng)計(jì)出7歲的女生人數(shù)......................................................-79-

統(tǒng)計(jì)人數(shù).....................................................................-79-

如何統(tǒng)計(jì)Al:A10,Dl:D10中的人數(shù)？..........................................-79-

如何讓EXCEL自動(dòng)從頭統(tǒng)計(jì)到當(dāng)前單元格......................................-79-

統(tǒng)計(jì)人數(shù)(1)..................................................................-80-

統(tǒng)計(jì)人數(shù)(2)..................................................................-80-

求各分?jǐn)?shù)段人數(shù)...............................................................-80-

有什么方法統(tǒng)計(jì)偶數(shù)..........................................................-81-

如何顯示.....................................................................-81-

工資統(tǒng)計(jì)中的問(wèn)題............................................................-81-

統(tǒng)計(jì)數(shù)據(jù)問(wèn)題一例............................................................-82-

根據(jù)給定的條件，對(duì)數(shù)據(jù)進(jìn)行合計(jì)..............................................-82-

十列數(shù)據(jù)合計(jì)成一列..........................................................-83-

統(tǒng)計(jì)漢字字符個(gè)數(shù)............................................................-83-

關(guān)于取數(shù).....................................................................-83-

統(tǒng)計(jì)單元格內(nèi)不為空的格數(shù)....................................................-84-

自動(dòng)將銷量前十名的產(chǎn)品代號(hào)及銷量填入表中...................................-84-

統(tǒng)計(jì)最大的連續(xù)次數(shù)..........................................................-84-

在一列有重復(fù)的姓名中，如何統(tǒng)計(jì)出具體有幾人.................................