EXCEL常用函數(shù)公式技巧大全

221/221EXCEL常用函數(shù)公式技巧大全EXCEL常用函數(shù)公式技巧大全excel常用函數(shù)公式及技巧搜集

從身份證號碼中提取出生年月日

從身份證號碼中提取出性別

從身份證號碼中進(jìn)行年齡判斷

按身份證號號碼計算至今天年齡

以2006年10月31日為基準(zhǔn)日,按按身份證計算年齡(周歲)的公式

按身份證號分男女年齡段

【年齡和工齡計算】

根據(jù)出生年月計算年齡

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

如何求出一個人到某指定日期的周歲？

計算距離退休年齡的公式

求工齡

計算工齡

年齡及工齡計算自動算出工齡日期格式為(yyyy.mm.dd)

【時間和日期應(yīng)用】

自動顯示當(dāng)前日期公式

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

如何判斷某日是否星期天

某個日期是星期幾

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

求本月天數(shù)

顯示昨天的日期

關(guān)于取日期

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

如何獲取一個月的最大天數(shù)

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

【排名及排序篩選】

一個具有11項匯總方式的函數(shù)SUBTOTAL

自動排序

按奇偶數(shù)排序

自動生成序號

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

如何設(shè)置自動排序

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

按字符數(shù)量排序

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

隨機(jī)排序

排序的問題

怎樣才能讓數(shù)列自動加數(shù)

一個排序問題

數(shù)字的自動排序,插入后不變?

根據(jù)規(guī)律的重復(fù)的姓名列產(chǎn)生自動序號

排名的函數(shù)

自動排名公式

百分比排名的公式寫法為：

平均分及總分排名

求名次排名

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

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

依分?jǐn)?shù)比高低名次成績排名

美國式排名

中國式排名

求最精簡的自動排名公式

排序后排名

位次排名

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

在雙列間排名

等次排名

不等次排名(行小排先）

不等次排名(行大排先）

順次排名

有并列排名

無并列排名

有并列分段排名

無并列分段排名

成績排名

如何排名

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

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

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

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

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

篩選后自動產(chǎn)生序列號并匯總

如何篩選奇數(shù)行

函數(shù)篩選姓名

名次篩選

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

如何請在N列中列出A1:L9中每列都存在的數(shù)值

自動為性別編號的問題

【文本與頁面設(shè)置】

EXCEL中如何刪除*號

將字符串中的星號“*”替換為其它字符

去空格函數(shù)如何去掉字符和單元格里的空格

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

如何禁止輸入空格

代替單元格中字符串

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

把有六百多個單元格的一列，變成一頁的多列

將N列變M列公式歸納為

一列變四列

四列變一列

重復(fù)四次填充

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

將單元格一列分為多列

首寫字母大寫

把單元格編號中的小寫字母變成大寫字母

讓姓名左右對齊

數(shù)字居中而小數(shù)點又對齊

計算指定單元格編號組中非空單元格的數(shù)量

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

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

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

計算特定的一組單元格中，滿足條件的單元格的個數(shù)

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

設(shè)置頁碼

Excel表格里如何插入頁碼的?

如何設(shè)置頁腳首頁為第5頁

表格的頁腳問題

無拘無束的頁眉

打印表頭

Excel打印中如何不顯示錯誤值符號

對于一些不可打印的字符的處理

用那個函數(shù)可將個位數(shù)前面的零值顯示出來?

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

請問如何每隔30行粘貼一新行

在工作表里有連續(xù)10行數(shù)據(jù),現(xiàn)在要每行間格2行

一個大表每一行下面需要加一行空行，怎么加最方便

Excel中插入空白行

快速刪除工作表中的空行快速刪除空行一次刪完Excel里面多出很多的空白行

每30行為一頁并加上一個標(biāo)題如何實現(xiàn)如何實現(xiàn)隔行都加上標(biāo)題項

如何把標(biāo)簽頁去掉的?

去掉默認(rèn)的表格線(網(wǎng)線)表格的框線

列標(biāo)的標(biāo)識變了

符號的意義

雙擊格式刷竟也能COPY文本(不是文本格式)

查找+格式刷的妙用

光標(biāo)移動

最后一行為文本最后一行為數(shù)字

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

用SUN函數(shù)快速求和

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

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

怎樣同時改變多行行高

快速換行

讓文本換行

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

將原有列中的內(nèi)容倒置過來

快速回到A1單元格

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

一次選中批注單元格

一次在所有單位格中插入批注

在公式中插入批注

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

空白行的填充

怎樣用函數(shù)向下實現(xiàn)自動填充

怎么設(shè)置自動保存

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

單元格前面自動加了等號

加蓋公章

查找+格式刷的妙用

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

Excel中快速定位的技巧實例

在Excel中插入Flash時鐘的步驟

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

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

【字符截取與增減】

截取單元格里某個字符后的字符

截取字符的公式

如何確定*號后的數(shù)字

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

三個數(shù)中,如何取出中間那個

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

取數(shù)函數(shù)

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

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

用如何提取“-”前后的字符

怎樣刪去﹕后的文字

怎樣只取“.”之后的文字﹖

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

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

如何把一個單元格中的數(shù)字挑出來

分割文本

按照給定的位數(shù)，截斷小數(shù)點后的數(shù)字

單元格數(shù)字提取問題

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

如何把文本中的前幾個字符去除

對一列中的文字統(tǒng)一去掉最后一個字

討如何去掉單元格中的第一個數(shù)字？

論一下取最后一個單詞的方法

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

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

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

如何在數(shù)據(jù)前添加“*”號

數(shù)字前面加上數(shù)字

【數(shù)據(jù)拆分與合并】

數(shù)字如何拆分

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

單元格的拆分

如何拆分字組

用連字符“&”來合并文本怎樣把不同格式不同位置的內(nèi)容合并到一個單元格內(nèi)

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

合并與拆分

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

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

求拆解合并公式

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

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

多個工作表的單元格合并計算

【條件自定義格式】

通過條件格式將小計和總計的行設(shè)為不同的顏色

如何實現(xiàn)這樣的條件格式

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

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

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

條件格式

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

相同數(shù)據(jù)的提示？

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

如何根據(jù)數(shù)值的正負(fù)加上“+”“-”符號

120，000顯示為12.0

121，999顯示為12.2

自定義單元格格式將單元格中的數(shù)全部變成萬元表示

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

常用的自定義格式

自定義格式

怎樣定義格式

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

自定義區(qū)域為每一頁的標(biāo)題

一個單元格內(nèi)格式問題

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

巧用定位選條件單元格

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

隔行用不同顏色顯示，請問如何做

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

在Excel中設(shè)計彩色數(shù)字

定義名稱的妙處

零值問題

【匯總計算與統(tǒng)計】

個調(diào)稅公式

算物價的函數(shù)

自動計算應(yīng)收款滯納金

淘汰率

應(yīng)用公積金的一個函數(shù)

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

比高得分公式

自動評定獎級

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

對a列動態(tài)求和

動態(tài)求和公式

列的跳躍求和

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

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

單數(shù)行求和統(tǒng)計偶數(shù)單元格合計數(shù)值

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

隔列將相同項目進(jìn)行求和

隔行或隔列加總

請問如何在一百行內(nèi)做隔行相加

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

隔列求和（A、B列）

隔列求和的公式

隔列求和

關(guān)于隔行、隔列求和的問題

EXCEL中求兩列的對應(yīng)元素乘積之和

計算900~1000之間的數(shù)值之和

雙條件求和

如何實現(xiàn)這樣的條件求和

A1:A10數(shù)字顯為文本格式時，如何求和

如何分班統(tǒng)計男女人數(shù)

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

計算出A1里有幾個abc

有條件統(tǒng)計

如何統(tǒng)計各年齡段的數(shù)量

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

如何統(tǒng)計40-50歲的人的個數(shù)

要統(tǒng)計出7歲的女生人數(shù)

統(tǒng)計人數(shù)

如何統(tǒng)計A1:A10，D1:D10中的人數(shù)？

如何讓EXCEL自動從頭統(tǒng)計到當(dāng)前單元格

統(tǒng)計人數(shù)

統(tǒng)計人數(shù)

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

有什么方法統(tǒng)計偶數(shù)

如何顯示

工資統(tǒng)計中的問題

統(tǒng)計數(shù)據(jù)問題一例

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

十列數(shù)據(jù)合計成一列

統(tǒng)計漢字字符個數(shù)

關(guān)于取數(shù)

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

自動將銷量前十名的產(chǎn)品代號及銷量填入表中

統(tǒng)計最大的連續(xù)次數(shù)3個“不重復(fù)”個數(shù)統(tǒng)計在一列有重復(fù)的姓名中，如何統(tǒng)計出具體有幾人

計數(shù)的問題

不相同

如何分班統(tǒng)計男女人數(shù)

在幾百幾千個數(shù)據(jù)中發(fā)現(xiàn)重復(fù)項

統(tǒng)計互不相同的數(shù)據(jù)個數(shù)

多個工作表的單元格合并計算

單個單元格中字符統(tǒng)計

數(shù)據(jù)區(qū)包含某一字符的項的總和,該用什么公式

函數(shù)如何實現(xiàn)分組編碼

【數(shù)值取整及進(jìn)位】

取整數(shù)函數(shù)

數(shù)值取整

求余數(shù)的函數(shù)

四舍五入公式

對數(shù)字進(jìn)行四舍五入

如何實現(xiàn)“見分進(jìn)元”

四舍五入

如何四舍五入取兩位小數(shù)

根據(jù)給定的位數(shù)，四舍五入指定的數(shù)值

四舍六入

如何實現(xiàn)2舍3入

怎么設(shè)置單元格以千元四舍五入

ROUND函數(shù)的四舍五入不進(jìn)位的解決方法？

保留一位小數(shù)

如何三舍四入

另類四舍五入

想把小數(shù)點和后面的數(shù)字都去掉，不要四舍五入

求真正的四舍五入后的數(shù)

小數(shù)點進(jìn)位

個位數(shù)歸0或者歸5

【大小值或中間值】

求平均值

如何實現(xiàn)求平均值時只對不等于零的數(shù)求均值？

平均分的問題

怎樣求最大值（最小值或中間值）平均數(shù)怎么弄

去掉其中兩個最大值和兩個最小值的公式

去一行最高分最低分求平均值

在9個數(shù)值中去掉最高與最低然后求平均值

求最大值（n列）

如何實現(xiàn)求平均值時只對不等于零的數(shù)求均值？

得到單元格編號組中最大的數(shù)或最小的數(shù)

標(biāo)記出3個最大最小值

取前五名，后五名的方法

如何用公式求出最大值所在的行？

求多個最高分

如何求多條件的平均值

想求出第三大之?dāng)?shù)值

【查詢和查找引用】

查找順序公式

怎樣實現(xiàn)精確查詢

查找及引用

查找函數(shù)的應(yīng)用

怎么能方便的判斷某個單元格中包含多少個指定的字符？如何用查找函數(shù)

日期查找的問題

如何自動查找相同單元格內(nèi)容

查找函數(shù)怎樣對號入座（查找）

一個文本查找的問題

查找一列中最后一個數(shù)值

查找重復(fù)字符

請教查找替換問題

IF函數(shù)替換法總結(jié)

查找的函數(shù)（查找末位詞組）

怎樣從原始數(shù)據(jù)中自動獲取最后一個數(shù)據(jù)

兩列數(shù)據(jù)查找相同值對應(yīng)的位置

查找數(shù)據(jù)公式兩個(基本查找函數(shù)為VLOOKUP,MATCH)

【輸入數(shù)據(jù)的技巧】

談?wù)凟xcel輸入的技巧

一列中不輸入重復(fù)數(shù)字

如果要查找重復(fù)輸入的數(shù)字

單元格輸入

大量0值輸入超級技巧

如何在C列中輸入工號在D列顯示姓名

輸入提示如何做

在信息輸入前就給予提示

數(shù)據(jù)有效性【身份證信息？提取】從身份證號碼中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,)顯示格式均為yyyy-m-d。（最簡單的公式，把單元格設(shè)置為日期格式）=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2))顯示格式為yyyy-mm-dd。（如果要求為“1995/03/29”格式的話，將”-”換成”/”即可）=IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000年00月00日"),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000年00月00日"))))顯示格式為yyyy年mm月dd日。（如果將公式中“0000年00月00日”改成“0000-00-00”,則顯示格式為yyyy-mm-dd）=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6))顯示格式為yyyymmdd。=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))=MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日"=IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))從身份證號碼中提取出性別=IF(MOD(MID(A1,15,3),2),"男","女")（最簡單公式）=IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女")=IF(A2<>””,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","女")從身份證號碼中進(jìn)行年齡判斷=IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”=DATEDIF（A1，TODAY（），“Y”）（以上公式會判斷是否已過生日而自動增減一歲）=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY())-VALUE(MID(B1,7,4))&"歲"=YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4))按身份證號號碼計算至今天年齡=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y")以2006年10月31日為基準(zhǔn)日,按按身份證計算年齡(周歲)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")按身份證號分男女年齡段按身份證號分男女年齡段，身份證號在K列，年齡段在J列（身份證號為18位）男性16周歲以下為1男性16周歲（含16周歲）以上至50周歲為2男性50周歲（含50周歲）以上至60周歲為3男性60周歲（含60周歲）以上為4女性16周歲以下為1女性16周歲（含16周歲）以上至45周歲為2女性45周歲（含45周歲）以上至55周歲為3女性55周歲（含55周歲）以上為4=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),"y"),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))=SUM(--(DATEDIF(MID(K1,7,4)&"/"&MID(K1,11,2)&"/"&MID(K1,13,2),TODAY(),"y")>={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【年齡和工齡計算】根據(jù)出生年月計算年齡＝DATEDIF(A1,TODAY(),"y")＝DATEDIF(A1,TODAY(),"y")&"周歲"＝DATEDIF(A1,NOW(),"y")根據(jù)出生年月推算生肖中國人有12生肖，屬什么可以推算出來。即用誕生年份除以12，再用除不盡的余數(shù)對照如下：0→猴，1→雞，2→狗，3→豬，4→鼠，5→牛，6→虎，7→兔，8→龍，9→蛇，10→馬，11→羊例如：XXX出生于1921年，即用1921年除以12，商得數(shù)為160，余數(shù)為1，對照上面得知余數(shù)1對應(yīng)生肖是雞，XXX就屬雞。=MID("猴雞狗豬鼠牛虎兔龍蛇馬羊",MOD(YEAR(A2),12)+1,1)（2007）如何求出一個人到某指定日期的周歲？=DATEDIF(起始日期,結(jié)束日期,"Y")計算距離退休年齡的公式=IF(E2="","",IF(E2>=V2,"已經(jīng)退休","距離退休還有"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"Y")&"年"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"YM")&"個月"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"Md")&"天"))其中E2為年齡（可用身份證號碼的公式生成）；V2為法定退休年齡（男60，女50）公式為：=IF(D2="","",IF(D2="男",60,50))D2為男或女（可用身份證號碼的公式生成）；U2為出生年月日（可用身份證號碼的公式生成）。求工齡=DATEDIF(B2,TODAY(),"y")=DATEDIF(B2,TODAY(),"ym")=DATEDIF(B2,TODAY(),"md")=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"月"&DATEDIF(B2,TODAY(),"md")&"日"計算工齡=DATEDIF(C6,C8,"y")求兩日期間的年數(shù)=DATEDIF(C6,C8,"ym")求兩日期間除去整年數(shù)剩余的月數(shù)=DATEDIF(C6,C8,"m")求兩日期間的總月數(shù)如果只需要算出周年的話，可以用=datedif("1978-8","2006-5","Y")年齡及工齡計算有出生年月如何求年齡？有工作時間如何求工齡？（求出的結(jié)果為多少年另幾個月，如：0303的形式，即3年零3個月）。a1是出生年月或工作時間：=datedif(a1,today(),"y")=text(datedif(a1,today(),"y"),"00")&text(datedif(a1,today(),"m"),"00")如[B2]=1964-9-1則:=TEXT(DATEDIF(B2,TODAY(),"y"),"00")&TEXT(MOD(DATEDIF(B2,TODAY(),"m"),12),"00")

'顯示4009=TEXT(DATEDIF(B2,TODAY(),"y"),"00年")&TEXT(MOD(DATEDIF(B2,TODAY(),"m"),12),"00月")

'顯示40年09月如果你找不到DATEDIF函數(shù)，也可以不用DATEDIF函數(shù),如[B2]=1964-9-1則:=TEXT(RIGHT(YEAR(NOW()-B2),2),"00")&TEXT(MOD(MONTH(NOW()-B2)-1,12),"00")

'顯示4009=TEXT(RIGHT(YEAR(NOW()-B2),2)&"年"&MOD(MONTH(NOW()-B2)-1,12)&"個月","")

'顯示40年09個月自動算出工齡日期格式為(yyyy.mm.dd)能否用：（yyyy.mm.dd）這種格式來計算出工齡有多長呢~？以前用這樣一段（=TEXT(RIGHT(YEAR(NOW()-A1),2)&"年"&MOD(MONTH(NOW()-A1)-1,12)&"個月","")）。但這種方法只能用：（yyyy-mm-dd）這樣的日期格式才能實現(xiàn)！你不妨把“.”替換成“-”，不就行了嗎，再說后者是日期的一種標(biāo)準(zhǔn)格式，=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,".","-")),2)&"年"&MOD(MONTH(NOW()-SUBSTITUTE(A1,".","-"))-1,12)&"個月","")㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【時間和日期應(yīng)用】自動顯示當(dāng)前日期公式=YEAR(NOW())當(dāng)前年=MONTH(NOW())當(dāng)前月=DAY((NOW()))當(dāng)前日如何在單元格中自動填入當(dāng)前日期Ctrl+;如何判斷某日是否星期天=WEEKDAY(A2,2)=TEXT(A1,"aaaa")=MOD(A1,7)<2某個日期是星期幾比如2007年2=TEXT(A1,"aaa")（五）=TEXT(A1,"aaaa")（星期五）=TEXT(A1,"ddd")（Fri）=TEXT(A1,"dddd")（Friday）什么函數(shù)可以顯示當(dāng)前星期如：星期二10:41:56=TEXT(NOW(),"aaaa

hh:mm:ss")求本月天數(shù)設(shè)A1為2006-8-4

求本月天數(shù)A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))也有更簡便的公式：=DAY(EOMONTH(NOW(),0))需加載分析工具箱。當(dāng)前月天數(shù)：=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-DATE(YEAR(TODAY()),MONTH(TODAY()),1)用公式算出除去當(dāng)月星期六、星期日以外的天數(shù)=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)>1))顯示昨天的日期每天需要單元格內(nèi)顯示昨天的日期，但雙休日除外。例如，今天是7月3號的話，就顯示7月2號，如果是7月9號，就顯示7月6號。=IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,IF(TEXT(TODAY(),"AAA")="日",TODAY()-2,TODAY()-1))=IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,TODAY()-1)關(guān)于取日期怎么設(shè)個公式使A1在年月日向后推5年,變成2011-7-15=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))=EDATE(A1,12*5)如何對日期進(jìn)行上、中、下旬區(qū)分=LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"})如何獲取一個月的最大天數(shù)"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1為"2001-03-01日期格式轉(zhuǎn)換公式將“01/12/2005”＝RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)＝Y(jié)EAR($A2)&TEXT(MONTH($A2),"00")&TEXT(DAY($A2),"00")該公式不用設(shè)置數(shù)據(jù)有效性,但要設(shè)置儲存格格式。也可以用下列兩方法：1、先轉(zhuǎn)換成文本,然后再用字符處理函數(shù)。2、[數(shù)據(jù)]-[分列][日期]-[MDY]將“2005年9月”轉(zhuǎn)換成“200509”格式先用公式：=text(a1,"yyyymm")+0然后將單元格格式為常規(guī)。將“2005-8-6”格式轉(zhuǎn)換為“20050806”格式用公式：=TEXT(A1,"YYYYMMDD")反之，將20050806轉(zhuǎn)為日期2005-8-6格式，可用公式：=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))另四種公式：＝text(a1,"0000-00-00")顯示：2005-08-06＝--TEXT(A1,"#-00-00")，把單元格設(shè)置為日期格式顯示：2005-8-6＝TEXT(20050806,"0000-00-00")*1，單元格設(shè)置日期型顯示：2005-8-6＝VALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2))顯示：2005-8-6將“20060501”轉(zhuǎn)換為“2006-05-01”格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))將“199306”轉(zhuǎn)換為“1993-6”公式1：=LEFT(A3,4)&"-"&RIGHT(A3,2)*1公式2：=--TEXT(A3*100+1,"#-00-00")公式2需要設(shè)置單元格格式，自定義：e-m公式3：=TEXT(TEXT(A3&"01","0000-00-00"),"e-m")把198405轉(zhuǎn)換成1984.05一、查找—1984，替換—1984.二、如果全部是年月的話，我個人建議，1、采取輔助＝mid(xxxxxx,1,4)&"."&right(xxxxxx,2)2、選中這列，用數(shù)據(jù)中的分列。然后……………三、單元格格式／數(shù)字／自定義，類型下面輸入：####"."##將文本“2004.01.02”轉(zhuǎn)換為日期格式：2004-1-2=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))將2005-8-6轉(zhuǎn)換為2005年8=TEXT(A1,"yyyy""年""m""月""d""日"";@")象22怎樣轉(zhuǎn)換成22日？轉(zhuǎn)成當(dāng)年當(dāng)月的日子公式為：=date(year(now()),month(now()),22)將“2006年5月”轉(zhuǎn)換成“2006年05月”公式為：=TEXT(A8,"yyyy""年""mm""月"";@")也可以這樣處理：選中單元格，設(shè)置單元格公式－數(shù)字－自定義，將yyyy“年”m“月”改為：yyyy“年”mm“月”，即可。但這方法打印出來顯示為：2006/5/將“1968年6月12日”轉(zhuǎn)換為“1968/6/12”格式=YEAR(A1)&"/"&MONTH(A1)&"/"&DAY(A1)顯示：1968/6/12=TEXT(A1,"yyyy/mm/dd")顯示：1968/06/12將“1968年6月12日”轉(zhuǎn)換為“1968-6-12”格式=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1)顯示：1968-6-12=TEXT(A1,"yyyy-mm-dd")顯示：1968-06-12將1993-12-28的日期格式轉(zhuǎn)換成1993年12月=CONCATENATE(YEAR(A1),"年",MONTH(A1),"月")=YEAR(A1)&"年"&MONTH(A1)&"月"也可以自定義格式[$-404]e"年"m"月"

將“1978-5-2”包含年月日的日期轉(zhuǎn)換成“197805”只有年月的格式＝y(tǒng)ear(A1)&text(month(A1),"00")要將“99.08.15”格式轉(zhuǎn)換成“1999.08.15選中列，數(shù)據(jù)菜單中選分列，分列過程中“格式”選“日期YMD”，結(jié)束。要保持2005/8/6格式當(dāng)輸入2005/8/6后系統(tǒng)自動變成2005-8-6，要保持2005/8/6格式，可以使用強(qiáng)制文本（前面加'號）或使用公式=TEXT(A1,"YYYY/MM/DD")。也可以用另一種公式：=IF(ISERROR(TEXT(A1,"yyyy/mm/dd")),TEXT(A1,"0000!/00!/00"),TEXT(A1,"yyyy/mm/dd"))將“二○○三年十二月二十五日”轉(zhuǎn)為“2003-12-25”格式，1、可以用數(shù)組公式將中文日期轉(zhuǎn)化為日期系列數(shù)｛=14610+MATCH(SUBSTITUTE(A3,"元","一"),TEXT(ROW($14611:$55153),"[DBNum1]yyyy年m月d日"),0)｝該公式速度較慢。2、改進(jìn)后的公式，速度要快的多：｛=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),"[DBNum1]0000"),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),"元","一"),TEXT(ROW($1:$366),"[DBNum1]m月d日"),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),"元","一"),TEXT(ROW($1:$366),"[DBNum1]m月d日"),0)))｝要設(shè)置為1900年的日期格式。日期格式轉(zhuǎn)換如A列是月份數(shù)為8，B列是日期數(shù)為18，如何在C列顯示“8月18日”=A1&"月"&B1&"日"反之，要將C列的“8月18日”直接分別到D、E列，顯示月份和日期，月數(shù)份=LEFT(C5,FIND("月",C5)-1)日期數(shù)=MID(C5,FIND("月",C5)+1,FIND("日",C5)-FIND("月",C5)-1)也可分別用公式：=month(--c5)=day(--c5)日期格式轉(zhuǎn)換問題輸入的日期是:04-07-26.與另一格的"001"合并,合并出來是:040726001.=TEXT(A1,"YYMMDD")&"001"要想自動取得“編制日期：XXXX年X月X日”可在該單元格輸入="編制日期："&TEXT(TODAY(),"yyyy年m月d日")㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【排名及排序篩選】一個具有11項匯總方式的函數(shù)SUBTOTAL=SUBTOTAL(9,$B$2:B2)在數(shù)據(jù)篩選求和上有意想不到的功能，11項功能為：1、求平均數(shù)，2、求計數(shù)，3、求計數(shù)值（自動篩選序列）4、求最大值，5、求最小值，6、求乘積，7、求總體標(biāo)準(zhǔn)偏差，8、求標(biāo)準(zhǔn)偏差、9、求和，10、求方差，11、求總體方差。自動排序=SUBTOTAL(3,$B$2:B2)*1=IF(A2<>A1,1,N(C1)+1)按奇偶數(shù)排序我想請教怎樣按奇數(shù)順序然后再按偶數(shù)順序排序=IF(MOD(A1,2),0,1)=IF(ROW()>50,(ROW()*2)-100,(ROW()*2)-1)=ROW()*2-1-(ROW()>50)*99自動生成序號比如在第二列中輸入內(nèi)容回車后第一列的下一行自動生成序列號。=IF(B2<>"",A2+1,"")如何自動標(biāo)示A欄中的數(shù)字大小排序？=RANK(A1,$A$1:$A$5)=RANK(A1,A:A)如何設(shè)置自動排序A列自動變成從小到大排列B=SMALL(A$2:A$28,ROW(1:1))A列自動變成從大到小排列B=LARGE(A$2:A$28,ROW(1:1))重復(fù)數(shù)據(jù)得到唯一的排位序列想得到數(shù)據(jù)的出現(xiàn)總數(shù)嗎（{1,2,2,3,4,4,5}數(shù)據(jù)的出現(xiàn)總數(shù)為5）？解答：不需要插列,不需要很多的函數(shù)就行了.=RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1按字符數(shù)量排序制作歌曲清單時，習(xí)慣按字符數(shù)量來排列分類，但是EXCEL并不能直接按字?jǐn)?shù)排序。需要先計算出每首歌曲的字?jǐn)?shù)，然后再進(jìn)行排序。如A、B列分別為“歌手”和“歌名”，在C1輸入“字?jǐn)?shù)”，在C2輸入公式：＝LEN(B2)下拖，單擊C2，單擊工具欄上的“升序排列”即可，刪除C列。排序字母與數(shù)字的混合內(nèi)容日常使用中，表格經(jīng)常會有包含字母和數(shù)字混合的數(shù)據(jù)，對此類數(shù)據(jù)排序時，通常是先比較字母的大小，再比較數(shù)字的大小，但EXCEL是按照對字符進(jìn)行逐位比較來排序的，如下表：A7排在第5位，而不是第1位。排序結(jié)果無法令人滿意。A1A1222A293A3174A435A76B207B38C1449C510C33AB1A7A0072A29A0293A43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144如果希望EXCEL改變排序的規(guī)則，需要將數(shù)據(jù)做一些改變。在B1中輸入公式：LEFT(A1,1)&RIGHT("000"&RIGHT(A1,LEN(A1)-1),3)下拖單擊B2，單擊工具欄上的“升序排列”即可。隨機(jī)排序如A、B列分別為“歌手”和“歌名”，在C1輸入“次序”，在C2輸入公式：＝RAND（），下拖，單擊C2，單擊工具欄上的“降序排列”即可對歌曲清單進(jìn)行隨機(jī)排序。排序的問題我想要這樣的排序:2001-20032004-20062007-20092010-2012;其實不是數(shù)據(jù)排序，應(yīng)該是數(shù)據(jù)填充。輸入公式=LEFT(E3,4)+3&"-"&RIGHT(E3,4)+3即可。怎樣才能讓數(shù)列自動加數(shù)怎樣做才能讓數(shù)列自動加數(shù)A

A0001B

B0001A

A0002C

C0001A

A0003B

B0002C

C0002公式為=A1&"000"&COUNTIF(A$1:A1,A1)向下拖=TEXT(COUNTIF(A$1:A1,A1),"!"&A1&"0000")否則數(shù)字超過9就錯誤了。一個排序問題一個電子表格，格式是101、102...999,10101、10102...99901,1010101,1020201...9990101,請問如何將它排列成101,10101,1010101,102,10201,1020101,...999,99901,9990101的形式。我在數(shù)字前加了個字母,比如"d"&"數(shù)字",然后用排序就可以把它們按你的需求排列了.最后再把字母"d"去掉。數(shù)字的自動排序,插入后不變?1趙一總經(jīng)理2趙二副經(jīng)理3趙三副經(jīng)理4趙四技術(shù)員5趙五6趙六員工如上的一個表,如何實現(xiàn)當(dāng)我把趙六這一整行(第6行)插入到上面的表中時,A列的序列號不變?最后的效果如下:1趙一總經(jīng)理2趙二副經(jīng)理3趙六員工4趙三副經(jīng)理5趙四技術(shù)員6趙五A1單元格輸入公式=row(),往下拉,然后再插入。=SUBTOTAL(3,$B$2:$B2)在A1中輸入公式：“=if(b1="","",counta($b$1:b1)”后下拉復(fù)制至A列各行即可（“”不必輸入）根據(jù)規(guī)律的重復(fù)的姓名列產(chǎn)生自動序號姓名 序號張三 1張三 1李四 2李四 2趙五 3趙五 3趙五 3王六 4王六 4=(A1<>A2)+N(B1)=IF(A3=A2,B2,B2+1)姓名已排序：B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2))姓名未排序：B2=IF(COUNTIF(A$2:A2,A2)>1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)))～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～排名的函數(shù)用排名函數(shù)來對成績進(jìn)行排名，用起來非常地方便。=IF(ISERR(RANK(M3,M:M)),"",RANK(M3,M:M))A列是成績,B列是排名=SUMPRODUCT((A$1:A$9>A1)/COUNTIF(A$1:A$9,A$1:A$9))+1自動排名公式=RANK(C3,$C$3:$C$12)=RANK(A2,$A$2:$A$11,0)=RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)-1百分比排名的公式寫法為：=PERCENTRANK($C$3:$C$12,C3)平均分及總分排名=AVERAGE(B2:E2)=RANK(F2,$F$2:$F$65536)求名次排名統(tǒng)計成績時遇到一個分別求班級和年級總分名次排名的問題，不曉得應(yīng)該運用什么公式來實現(xiàn)。班級名次：=SUMPRODUCT((BJ=A2)*(ZF>E2))+1年級名次：=RANK(E2,ZF)公式下拖。排名次根據(jù)總分值大小，只將姓名排序后,降序結(jié)果=INDEX(A$2:A$6,RANK(D2,D$2:D$6))根據(jù)總分值大小，只將姓名排序后,升序=INDEX(A$2:A$6,RANK(D2,D$2:D$6,1))根據(jù)分?jǐn)?shù)進(jìn)行普通排名=RANK(A2,$A$2:$A$12)=RANK(A2,A$2:A$12)+COUNTIF(A$2:A2,A2)-1=SUMPRODUCT(1*($E$3:$E$12>=E3))=RANK(K3,$K$3:$K$26)=RANK(A2,A$2:A$12)=SUM((A$2:A$12>=A2)/COUNTIF(A$2:A$12,A$2:A$12))=COUNTIF($K$3:$K$26,">"&K3)+1=INDEX($A$2:$A$7,MATCH(LARGE($C$2:$C$7,ROW(A1)),$C$2:$C$7,0),1)=SUMPRODUCT(($A$2:$A$12>A2)/COUNTIF($A$2:$A$12,$A$2:$A$12&""))+1=RANK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)-1,3,COUNTIF($A:$A,$A2),1))對于普通排名分?jǐn)?shù)相同時，按順序進(jìn)行不重復(fù)排名=RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)-1=COUNTIF($K$32:K32,K32)-1+COUNTIF($K$3:$K$26,">"&K32)+1=SUMPRODUCT(1*(($E$3:$E$12+ROW($E$3:$E$12)/100>=($E3+ROW(E3)/100))))=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1=SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100)>=(E3+B3/100)))依分?jǐn)?shù)比高低名次成績排名=RANK($E3,$E$3:$E$22)內(nèi)建方式排名=SUMPRODUCT(1*($E$3:$E$12>=E3))一般方式排名｛=RANK(E3,$E$3:$E$22)+SUM(IF($E$3:$E$22>E3,1/COUNTIF($E$3:$E$22,$E$3:$E$22),0))-COUNTIF($E$3:$E$22,">"&E3)｝一般方式排名=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1不重復(fù)排名=SUMPRODUCT(1*(($E$3:$E$12+ROW($E$3:$E$12)/100>=($E3+ROW(E3)/100))))=SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100)>=(E3+B3/100)))不重復(fù)排名=SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100+$C$3:$C$12/10000)>=(E3+B3/100+C3/10000)))不重復(fù)排名=RANK($E3,$E$3:$E$22,1)倒排序美國式排名=RANK(K247,$K$247:$K$270)=RANK(B1,$B1:$H1)中國式排名=RANK(B2,$B$2:$B$21,0)=RANK(B1,$B1:$H1)+COUNTIF($B$1:B1,B1)-1=SUM(IF($A$1:$E$1>=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),""))=SUMPRODUCT(($B$2:$B$21>=B2)/COUNTIF($B$2:B$21,B$2:B$21))=SUMPRODUCT((B$3:B$21>B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)))+1（升序）=SUMPRODUCT((B$3:B$21<B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)))+1（降序）｛=SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1｝｛=SUM(IF($B$3:$B$21<=B3,"",1/(COUNTIF($B$3:B$21,B$3:B$21))))+1｝（升序）｛=SUM(IF($B$3:$B$21<=B3,1/(COUNTIF($B$3:B$21,B$3:B$21)),""))｝（降序）｛=SUM(IF($B$2:$B$21>B2,1/COUNTIF($B$2:B$21,B$2:B$21)))+1｝｛=SUM(IF($A$1:$E$1>=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),""))｝｛=SUM(($B$2:$B$21>B2)*(MATCH($B$2:B$21,B$2:B$21,)=ROW($1:$20)))+1｝｛=SUM(IF($B$1:$H$1<=B1,"",1/(COUNTIF($B$1:$H$1,$B$1:$H$1))))+1｝求最精簡的自動排名公式=RANK(E2,$E$2:$E$21)=RANK(A2,$A$2:$A$9,0)=RANK(A2,$A$2:$A$10)+COUNTIF($A$2:$A2,$A2)-1（如果數(shù)據(jù)列中數(shù)值有相同）=RANK(F10,$F10:$Q10)+COUNTIF($F10:F10,F10)-1=INDEX(A:A,1/MOD(LARGE(E$2:E$21+1/ROW($2:$21),ROW(1:1)),1))=LOOKUP(1,0/(($F$2:$F$21=A27)*(COUNTIF(D$26:D26,$A$2:$A$21)=0)),$A$2:$A$21)=INDIRECT("A"&RIGHT(LARGE(($E$2:$E$21*100+ROW($A$2:$A$21)),ROW(A1)),2))=RANK(C2,OFFSET($C$1,MATCH(E2,$E$2:$E$768,),,COUNTIF($E$2:$E$768,E2)))數(shù)組公式｛=INDEX(A:A,MOD(LARGE(E$2:E$21*100+ROW($2:$21),ROW(1:1)),100))｝｛=OFFSET($A$1,RIGHT(LARGE($E$2:$E$21*1000+ROW($E$2:$E$21),ROW()-25),3)-1,,)｝=OFFSET($A$1,RIGHT(LARGE(($E$2:$E$21*100+ROW($A$1:$A$20)),ROW(A3)),2),)=TEXT(SUMPRODUCT(($E$2:$E$21>=E2)/COUNTIF($E$2:$E$21,$E$2:$E$21)),"第[DBNUM1]G/通用格式名")排序后排名｛=SUM(IF($B$2:$B$15>=B2,1/COUNTIF($B$2:$B$15,$B$2:$B$15)))｝=SUMPRODUCT((B$2:B$15>=B2)/COUNTIF(B$2:B$15,B$2:B$15))位次排名｛=IF($B2:$O2>=0,RANK($B2:$O2,$B2:$O2,0),)｝根據(jù)雙列成績進(jìn)行共同排名=RANK(C345,($C$345:$C$356,$H$345:$H$356))在雙列間排名=RANK(B2,($B$2:$B$26,$E$2:$E$16))等次排名由大到小排名=RANK(B3,$B$3:$B$12)=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16))+1由小到大排名=RANK(B3,$B$3:$B$12,1)=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16))+1不等次排名(行小排先）由大到小=RANK(B3,$B$3:$B$12)+COUNTIF($B$3:B3,B3)-1=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000>B16-ROW(B16)/10000))+1由小到大=RANK(B3,$B$3:$B$12,1)+COUNTIF($B$3:B3,B3)-1=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000<B16+ROW(B16)/10000))+1不等次排名(行大排先）由大到小=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12,1)-COUNTIF($B$3:B3,B3)+2=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000>B16+ROW(B16)/10000))+1由小到大=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12)-COUNTIF($B$3:B3,B3)+2=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000<B16-ROW(B16)/10000))+1順次排名由大到小=SUMPRODUCT((B$3:B$12>B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1由小到大=SUMPRODUCT((B$3:B$12<B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1有并列排名=RANK(B2,$B$2:$B$20)=SUMPRODUCT(1*($B$3:$B$21>B3))+1=COUNTIF($B$3:$B$21,">"&B3)+1｛=SUM(IF($B$3:$B$21>B3,1,0))+1｝=19-FREQUENCY($B$3:$B$21,B3)+1=SUMPRODUCT(($B$2:$B$20>=B2)/COUNTIF($B$2:$B$20,$B$2:$B$20))無并列排名=RANK(B3,$B$3:$B$21)+COUNTIF($B$3:$B3,B3)-1=SUMPRODUCT((B3-ROW()/1000<$B$3:$B$21-ROW($B$3:$B$21)/1000)*1)+1=19-FREQUENCY($B$3:$B$21-ROW($B$3:$B$21)/1000,B3-ROW()/1000)+1｛=SUM(IF($B$3:$B$21-ROW($B$3:$B$21)/1000>B3-ROW()/1000,1,0))+1｝有并列分段排名=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3))+1=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21),C3)+1｛=MATCH(C3,LARGE(OFFSET($C$2,IF($A$3:$A$21=A3,ROW($A$3:$A$21)-2),),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)｝｛=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)｝｛=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3)/COUNTIF($N$3:$N$21,$N$3:$N$21))+1｝（需輔助列）無并列分段排名｛=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/10000>C3-ROW(C3)/10000))+1｝=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/1000),C3-ROW()/1000)+1成績排名序號姓名語文數(shù)學(xué)英語1楊增海1351361462郭愛玲1381371413華志鋒1341381414袁文飛134143135能否用一個公式直接找出所用考生中語文成績中第100名的成績是多少?=LARGE(C2:C417,100)=PERCENTILE(C2:C417,(416-100)/416)=PERCENTILE($C$2:$C$417,(COUNTA($C$2:$C$417)-100)/COUNTA($C$2:$C$417))能否用一個公式直接找出所用考生中語文成績中按與考人數(shù)的35%切線中位于第35%的成績是多少?升冪=SMALL(C2:C417,416*0.35)=PERCENTILE($C$2:$C$417,0.35)降冪=LARGE(C2:C417,416*0.35)=PERCENTILE($C$2:$C$417,1-0.35)如何排名1、對英語進(jìn)行排名，缺考不計算在內(nèi)。

2、對英語進(jìn)行排名，缺考計算在內(nèi)。英語英語排名429623721485485721544429缺考缺考458467缺考不計算在內(nèi)b2=IF(A2="缺考","",RANK(A2,$A$2:$A$13))然后按照B列排序缺考計算在內(nèi)=IF(A2="缺考",COUNTIF($A$2:$A$13,">=0")+1,RANK(A2,$A$2:$A$13))=IF(A2="缺考",COUNT($A$2:$A$13)+1,RANK(A2,$A$2:$A$13,0))數(shù)據(jù)排名（隔幾行排名）=IF(A2="","",RANK(A2,$A$2:$A$11,0))如果隔幾行排名，如下表，第五行、第九行和第十二行不參與排名。單位數(shù)據(jù)排名A18A57A66小計12B84B93B75小計24C181C112小計29=IF(A2="小計","",RANK(B2,(B$2:B$4,B$6:B$8,B$10:B$11)))下拉根據(jù)分?jǐn)?shù)進(jìn)行倒排名=RANK($E3,$E$3:$E$22,1)=RANK(K60,$K$60:$K$83,1)=COUNTIF($K$60:$K$83,"<"&K60)+1倒數(shù)排名函數(shù)是什么1為正排序，0為逆排序。倒數(shù)排名=RANK(A2,$A$2:$A$5,0)正數(shù)排名=RANK(A2,$A$2:$A$5,1)如何實現(xiàn)每日各車間產(chǎn)量的排名=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$1000,0)-1,,,))=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$33,0)-1,,,))分?jǐn)?shù)相同時按照一科的分?jǐn)?shù)進(jìn)行排名｛=MATCH(K308*100+D308,LARGE($K$308:$K$331*100+$D$308:$D$331,ROW($K$308:$K$331)-307),)｝～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～篩選后自動產(chǎn)生序列號并匯總自動產(chǎn)生序列號：在A1輸入以下公式，往下拖。=SUBTOTAL(3,$B$2:B2)*1自動匯總，用以下公式：=SUBTOTAL(9,$B$2:B2)說明：匯總時，不要在“全選”狀態(tài)下進(jìn)行，先“篩選”出某一單位，自動求和∑。然后再恢復(fù)到“全選”或者選擇任何單位，就能自動匯總了（在“篩選”出某一單位進(jìn)行求和時，一般表格會自動產(chǎn)生以上匯總公式）。其它：如同時要在其它單元格顯示人數(shù)，在“全選”狀態(tài)下，選定單元格，點“fx”（用“sum”函數(shù)）再點擊序列號最末尾數(shù)，即可。如何篩選奇數(shù)行公式=MOD(A1,2)=1函數(shù)篩選姓名如何把兩列中只要包含A和A+的人員篩選出來=IF(ISNUMBER(FIND("A",C2))+ISNUMBER(FIND("A",B2))>0,"OK","")名次篩選名次=RANK(K5,K$2:K$435)班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,)-2,,COUNTIF(A$1:A$500,A6)))如何實現(xiàn)快速定位（篩選出不重復(fù)值）=IF(COUNTIF($A$2:A2,A2)=1,A2,"")=IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,"")=INDEX(A:A,SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW(A$1:A$20),65536),ROW()))&""（數(shù)組公式）如何請在N列中列出A1:L9中每列都存在的數(shù)值｛=IF(ROW()>SUM(--x),"",INDEX(A:A,SMALL(IF(x,ROW($A$1:$A$9)),ROW())))｝自動為性別編號的問題有一個編碼,5位，第1位,1為男,2為女，后面4位,代表他的編號,從0001-9999，如何達(dá)到下表：性別

編碼男

10001男

10002女

20001男

10003女

20002男的也是從0001-9999女的也是從0001-9999如果你是已經(jīng)輸入了其它信息，僅僅為快速輸入編碼的話。用篩選可以實現(xiàn)吧。

先以“男”為關(guān)鍵字進(jìn)行排序，然后在第一個男的編碼輸入10001，下拉復(fù)制到最后一單即可。同理再以“女”排序。完成目標(biāo)。用公式：=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【文本與頁面設(shè)置】EXCEL中如何刪除*號在錄入帳號是錄入了*號，如何刪除?？梢杂煤瘮?shù)SUBSTITUTE(a1,"*","")查找~*，替換為空。將字符串中的星號“*”替換為其它字符在查找欄輸入~*替換為“-”即可。去空格函數(shù)如何刪去單元格中的空格，如姓名前，中，后的空格，即單元格中是兩個字的人名中間有一個空格，想刪去有何方法。如：中

國，改為：中國。1、用公式：=SUBSTITUTE(A2,"","")注：第一對雙引號中有一空格。而第二個“”中是無空格的。2、利用查找－替換，一次性全部解決。“編輯”－“替換”（或Ctrl+H），在“查找”欄內(nèi)輸入一空格，“替換”什么也不輸入（空白）。然后“全部替換”即可。3、有一個專門刪除空格的函數(shù):TRIM()在EXCEL編輯欄里，不管輸中文還是英文只能輸一個字節(jié)的空格，但如果字與字中間是兩個字節(jié)的空格，那么TRIM（）就不起作用了，它就不認(rèn)為是一個空格，而是一個漢字，怎么去“TRIM”也沒用。如：單元格A1中有“中心是”，如果用TRIM則變成“中心是”，想將空格全去掉，只能用SUBSTITUDE（）函數(shù)，多少空格都能去掉。如何去掉字符和單元格里的空格89000795018900079501~19000788011900078802~=SUBSTITUTE(B2,"~","")怎樣快速去除表中不同行和列的空格編輯-定位-定位條件-空值,可選中所有空單元格,再刪除。如何禁止輸入空格在Excel中如何通過編輯“有效數(shù)據(jù)”來禁止錄入空格？煩請大俠們費心解答。解答：有效性公式。=COUNTIF(A1,"**")=0(注：COUNTIF(A1,"**")在單元格有空格時結(jié)果為1,沒有空格時結(jié)果為0如希望第一位不能輸入空格:countif(a1,"*")=0如希望最后一位不能輸入空格:countif(a1,"*")=0)代替單元格中字符串單元格編號，開始位數(shù)，從開始位數(shù)算起第幾位數(shù)，要用于代替的的字符串。windows2000變成windows2K=REPLACE(B2,8,3,"K")單元格編號，要代替掉的字符，要用作代替的字符，第幾個。代替單元格B391中的全部TT，改為UU。EETTCCTTFF變成EEUUCCUUFF=SUBSTITUTE(B394,"TT","UU")只代替單元格B391中的第一次出現(xiàn)的TT，改為UU。EETTCCTTFF變成EEUUCCTTFF=SUBSTITUTE(B397,"TT","UU",1)把單元格中的數(shù)字轉(zhuǎn)變成為特定的字符格式函數(shù)中的第二個參數(shù)的雙引號一定不能是中文格式的（不能用任意中文輸入法輸入的雙引號。）實例： 20000 目的： 變成帶有美元符號的字符 10000 變成帶有人民幣符號的字符 151581 變成帶有歐元符號的字符 1451451 變成中文繁體的字符 15748415 變成中文簡體的字符操作步驟： =TEXT(B72,"$0.00") 結(jié)果： $20000.00 =TEXT(B73,"￥0.00") ￥10000.00 =TEXT(B74,"€0.00") €151581.00 =TEXT(B75,"[DBNum2]G/通用格式") 壹佰肆拾伍萬壹仟肆佰伍拾壹 =TEXT(B76,"[DBNum1]G/通用格式") 一千五百七十四萬八千四百一十五 把有六百多個單元格的一列，變成一頁的多列有一張表，共有14頁，但每頁只有一列，如何把他們整合在一起，變成一頁（按每頁的順序），如果使用剪切和粘貼的方式，那樣太麻煩。=INDIRECT("r"&(COLUMN()-3)*48+ROW()&"C1",0)復(fù)制到其他單元格將N列變M列公式歸納為=OFFSET($A$1,INT(((ROW(A1)-12)*m+COLUMN(A1)-1)/n),MOD((ROW(A1)-1)*m+COLUMN(A1)-1,n))=OFFSET($A$1,INT(((ROW(A1)-1)*7+COLUMN(A1)-1)/4),MOD((ROW(A1)-1)*7+COLUMN(A1)-1,4))四列變七列=OFFSET($A$1,INT(((ROW()-20)*10+COLUMN()-1)/7),MOD((ROW()-20)*10+COLUMN()-1,7))七列變十列一列變四列=OFFSET($A$1,ROW($A1)*4-COLUMNS(C:$F),)=OFFSET($A$1,(ROW()-3)*4+MOD(COLUMN()-8,4),)=OFFSET($A$1,ROW(A1)*4-4+MOD(COLUMN()-13,4),)四列變一列=OFFSET($F$1,INT(ROW(1:1)/4+3/4)-1,MOD(ROW()-1,4))=OFFSET($F$1,INT((ROW(1:1)-1)/4),MOD(ROW()-1,4))=OFFSET($F$1,ROUNDUP((ROW(1:1)/4),0)-1,MOD(ROW()-1,4))=OFFSET($F$1,(ROW()-1)/4,MOD(ROW()-1,4))重復(fù)四次填充=TEXT(INT(ROW()/4+3/4),"00")=IF(TRUNC((ROW()-1)/4,0)<9,"0"&TRUNC(ROW()/4-0.01,0)+1,TRUNC(ROW()/4-0.01,0)+1)=TEXT(ROUNDUP(ROW()/4,),"00")=TEXT(ROW(2:2)/4,"00")多行數(shù)據(jù)排成一列a1b1c1d1e1f1g1h1i1a2b2c2d2e2g2h2i2a3c3d3g3h3i3a4c4g4h4i4A5c5g5h5g6a1a2a3a4A5b1｛=IF(ROW()>COUNTA($A$1:$I$10),"",INDEX($A$1:$I$10,MOD(SMALL(IF($A$1:$I$10<>"",ROW($A$1:$I$10)+COLUMN($A$1:$I$10)*100000),ROW()),100000),INT(SMALL(IF($A$1:$I$10<>"",ROW($A$1:$I$10)+COLUMN($A$1:$I$10)*100000),ROW())/100000)))｝將單元格一列分為多列如果有一列資料需要分為多列，只要先將此列選中，然后再選擇“數(shù)據(jù)”→“分列”，此時會出現(xiàn)一個對話框，選“固定寬度”或“分隔符號”。如為前者則下一步后只要用鼠標(biāo)輕點資料即可以按任意寬度進(jìn)行分割了，如為后者則只要有明顯的分隔符號即可，下一步后就可以自定義剛分的列的格式了，定好后就算完成了。步驟：1、先確定1列的最適合的列寬,再將其寬度乘以分成列數(shù),即分列前的列寬=最適合的列寬×需分成的列數(shù).2、編輯—填充—內(nèi)容重排。3、數(shù)據(jù)—分列。首寫字母大寫把單元格編號中的單詞首寫字母變成大寫字母，其余字母變成小寫。如china-China=PROPER(B160)把單元格編號中的小寫字母變成大寫字母lafayette148LAFAYETTE148=UPPER(B1)=LOWER(B1)（大寫字母變成小寫字母公式）讓姓名左右對齊姓名用字，有的是三個漢字，有的是兩個漢字，打印出來很不美觀，要使姓名用字是兩個字的與三個字的左右對齊也有兩種方法:方法一:格式設(shè)置法。選中我們已經(jīng)刪除完空格的姓名單元格，單擊“格式→單元格”在打開的“單元格格式”對話框中的水平對齊方式中選擇“分散對齊”選項，確定退出后即可使學(xué)生姓名用字左右對齊。方法二:函數(shù)公式法。利用Excel中的“IF”、“LEN”、“MID”三種函數(shù)組合可使姓名用字左右對齊。具體示例為:在C3單元格中輸入公式:“=IF(LEN(B2)>=3,B2,(MID(B2,1,1)&&""&&MID(B2,2,1)))”，確定后利用填充柄將該公式進(jìn)行復(fù)制即可。數(shù)字居中而小數(shù)點又對齊可在小數(shù)點的任一邊替無效的零加入空間，以便當(dāng)格式設(shè)定為固定寬字型，小數(shù)點可以對齊。格式－單元格－數(shù)字－自定義－???.???-確定請問：小數(shù)點后的“0”還有辦法顯示嗎？比如：2.012.001格式－單元格－數(shù)字－自定義－???.0?-確定計算指定單元格編號組中非空單元格的數(shù)量計算B252到B262之間的非空單元格的數(shù)量。=COUNTA(B252:B262)比較兩個單元格內(nèi)容是否一致74P125148 74P125148比較單元格B53與C53中的內(nèi)容是否一致。假如內(nèi)容一致，那么返回值為TRUE，不一致的話，返回值為FALSE。=EXACT(B53,C53)結(jié)果：TRUE怎么樣設(shè)置才能讓這一列的每個單元格只能輸入12位怎么樣設(shè)置才能讓某一列或某一行的每個單元格只能輸入12位,(阿拉伯?dāng)?shù)字和26個英文字母在內(nèi),沒有中文。)選中A列，設(shè)置數(shù)據(jù)有效性：自定義>公式：“=LEN(A1)=12”如何讓工作表奇數(shù)行背景是紅色偶數(shù)行背景是藍(lán)色用條件格式=ROW()/2=INT(ROW()/2)設(shè)定顏色條件格式:公式為=MOD(ROW(),2)=0計算特定的一組單元格中，滿足條件的單元格的個數(shù)仍以上題為例，計算三個人在B307到