【身份證信息提取】
從身份證號(hào)碼中提取出生年月日
=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。（最簡(jiǎn)單的公式，把單元格設(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”格式的話(huà)，將”-” 換成”/”即可）
=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"))
從身份證號(hào)碼中提取出性別
=IF(MOD(MID(A1,15,3),2),"男","女") （最簡(jiǎn)單公式）
=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,"男","女")
從身份證號(hào)碼中進(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”）
（以上公式會(huì)判斷是否已過(guò)生日而自動(dòng)增減一歲）
=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))
按身份證號(hào)號(hào)碼計(jì)算至今天年齡
=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y")
以2006年10月31日為基準(zhǔn)日,按按身份證計(jì)算年齡(周歲)的公式
=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")
按身份證號(hào)分男女年齡段
按身份證號(hào)分男女年齡段，身份證號(hào)在K列，年齡段在J列（身份證號(hào)為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ì)算】
根據(jù)出生年月計(jì)算年齡
＝DATEDIF(A1,TODAY(),"y")
＝DATEDIF(A1,TODAY(),"y")&"周歲"
＝DATEDIF(A1,NOW(),"y")
根據(jù)出生年月推算生肖
中國(guó)人有12生肖，屬什么可以推算出來(lái)。即用誕生年份除以12，再用除不盡的余數(shù)對(duì)照如下：0→猴，1→雞，2→狗，3→豬，4→鼠，5→牛，6→虎，7→兔，8→龍，9→蛇，10→馬，11→羊例如：XXX出生于1921年，即用1921年除以12，商得數(shù)為160，余數(shù)為1，對(duì)照上面得知余數(shù)1對(duì)應(yīng)生肖是雞，XXX就屬雞。
=MID("猴雞狗豬鼠?；⑼谬埳唏R羊",MOD(YEAR(A2),12)+1,1) （2007）
如何求出一個(gè)人到某指定日期的周歲？
=DATEDIF(起始日期,結(jié)束日期,"Y")
計(jì)算距離退休年齡的公式
=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")&"個(gè)月"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"Md")&"天"))
其中E2為年齡（可用身份證號(hào)碼的公式生成）；
V2為法定退休年齡（男60，女50）公式為：=IF(D2="","",IF(D2="男",60,50))
D2為男或女（可用身份證號(hào)碼的公式生成）；U2為出生年月日（可用身份證號(hào)碼的公式生成）。
求工齡
=DATEDIF(B2,TODAY(),"y")
=DATEDIF(B2,TODAY(),"ym")
=DATEDIF(B2,TODAY(),"md")
=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"月"&DATEDIF(B2,TODAY(),"md")&"日"
計(jì)算工齡
=DATEDIF(C6,C8,"y")求兩日期間的年數(shù)
=DATEDIF(C6,C8,"ym")求兩日期間除去整年數(shù)剩余的月數(shù)
=DATEDIF(C6,C8,"m")求兩日期間的總月數(shù)
如果只需要算出周年的話(huà)，可以用=datedif("1978-8","2006-5","Y")
年齡及工齡計(jì)算
有出生年月如何求年齡？
有工作時(shí)間如何求工齡？（求出的結(jié)果為多少年另幾個(gè)月，如：0303的形式，即3年零3個(gè)月）。
a1是出生年月或工作時(shí)間：
=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)&"個(gè)月","") '顯示 40年09個(gè)月
自動(dòng)算出工齡日期格式為(yyyy.mm.dd)
能否用：（yyyy.mm.dd）這種格式來(lái)計(jì)算出工齡有多長(zhǎng)呢~？
以前用這樣一段（ =TEXT(RIGHT(YEAR(NOW()-A1),2)&"年"&MOD(MONTH(NOW()-A1)-1,12)&"個(gè)月","") ）。
但這種方法只能用：（yyyy-mm-dd）這樣的日期格式才能實(shí)現(xiàn)！
你不妨把“.”替換成“-”，不就行了嗎，再說(shuō)后者是日期的一種標(biāo)準(zhǔn)格式，
=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,".","-")),2)&"年"&MOD(MONTH(NOW()-SUBSTITUTE(A1,".","-"))-1,12)&"個(gè)月","")
【時(shí)間和日期應(yīng)用】
自動(dòng)顯示當(dāng)前日期公式
=YEAR(NOW()) 當(dāng)前年
=MONTH(NOW()) 當(dāng)前月
=DAY((NOW())) 當(dāng)前日
如何在單元格中自動(dòng)填入當(dāng)前日期
Ctrl+;
如何判斷某日是否星期天
=WEEKDAY(A2,2)
=TEXT(A1,"aaaa")
=MOD(A1,7)<2
某個(gè)日期是星期幾
比如2007年2月9日，在一單元格內(nèi)顯示星期幾。
=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))
也有更簡(jiǎn)便的公式：=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號(hào)的話(huà)，就顯示7月2號(hào)，如果是7月9號(hào)，就顯示7月6號(hào)。
=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è)個(gè)公式使A1在年月日向后推5年,變成2011-7-15
=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))
=EDATE(A1,12*5)
如何對(duì)日期進(jìn)行上、中、下旬區(qū)分
=LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"})
如何獲取一個(gè)月的最大天數(shù)
"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1為"2001-03-01
日期格式轉(zhuǎn)換公式
將 “01/12/2005” 轉(zhuǎn)換成“20050112”格式
＝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è)置儲(chǔ)存格格式。
也可以用下列兩方法：
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.
二、如果全部是年月的話(huà)，我個(gè)人建議，
1、采取輔助＝mid(xxxxxx,1,4) & "." & right(xxxxxx,2)
2、選中這列，用數(shù)據(jù)中的分列。然后……………
三、單元格格式／數(shù)字／自定義，類(lèi)型下面輸入：####"."##
將文本“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月6日格式
=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“月”，即可。但這方法打印出來(lái)顯示為：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ù)菜單中選分列，分列過(guò)程中“格式”選“日期YMD”，結(jié)束。
要保持2005/8/6格式
當(dāng)輸入2005/8/6后系統(tǒng)自動(dòng)變成2005-8-6，要保持2005/8/6格式，可以使用強(qiáng)制文本（前面加'號(hào)）或使用公式=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)換問(wèn)題
輸入的日期是:04-07-26. 與另一格的"001"合并,合并出來(lái)是:040726001.
=TEXT(A1,"YYMMDD")&"001"
要想自動(dòng)取得“編制日期：XXXX年X月X日”
可在該單元格輸入 ="編制日期："&TEXT(TODAY(),"yyyy年m月d日")
【排名及排序篩選】
一個(gè)具有11項(xiàng)匯總方式的函數(shù)SUBTOTAL
=SUBTOTAL(9,$B$2:B2)
在數(shù)據(jù)篩選求和上有意想不到的功能，11項(xiàng)功能為：1、求平均數(shù)，2、求計(jì)數(shù)，3、求計(jì)數(shù)值（自動(dòng)篩選序列）4、求最大值，5、求最小值，6、求乘積，7、求總體標(biāo)準(zhǔn)偏差，8、求標(biāo)準(zhǔn)偏差、9、求和，10、求方差，11、求總體方差。
自動(dòng)排序
=SUBTOTAL(3,$B$2:B2)*1
=IF(A2<>A1,1,N(C1)+1)
按奇偶數(shù)排序
我想請(qǐng)教怎樣按奇數(shù)順序然后再按偶數(shù)順序排序
=IF(MOD(A1,2),0,1)
=IF(ROW()>50,(ROW()*2)-100,(ROW()*2)-1)
=ROW()*2-1-(ROW()>50)*99
自動(dòng)生成序號(hào)
比如在第二列中輸入內(nèi)容回車(chē)后第一列的下一行自動(dòng)生成序列號(hào)。
=IF(B2<>"",A2+1,"")
如何自動(dòng)標(biāo)示A欄中的數(shù)字大小排序？
=RANK(A1,$A$1:$A$5)
=RANK(A1,A:A)
如何設(shè)置自動(dòng)排序
A列自動(dòng)變成從小到大排列
B=SMALL(A$2:A$28,ROW(1:1))
A列自動(dòng)變成從大到小排列
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ù)量排序
制作歌曲清單時(shí)，習(xí)慣按字符數(shù)量來(lái)排列分類(lèi)，但是EXCEL并不能直接按字?jǐn)?shù)排序。需要先計(jì)算出每首歌曲的字?jǐn)?shù)，然后再進(jìn)行排序。
如A、B列分別為“歌手”和“歌名”，在C1輸入“字?jǐn)?shù)”，在C2輸入公式：
＝LEN(B2) 下拖，單擊C2，單擊工具欄上的“升序排列”即可，刪除C列。
排序字母與數(shù)字的混合內(nèi)容
日常使用中，表格經(jīng)常會(huì)有包含字母和數(shù)字混合的數(shù)據(jù)，對(duì)此類(lèi)數(shù)據(jù)排序時(shí)，通常是先比較字母的大小，再比較數(shù)字的大小，但EXCEL是按照對(duì)字符進(jìn)行逐位比較來(lái)排序的，如下表：A7排在第5位，而不是第1位。排序結(jié)果無(wú)法令人滿(mǎn)意。
A
1
A122
2
A29
3
A317
4
A43
5
A7
6
B20
7
B3
8
C144
9
C5
10
C33
A
B
1
A7
A007
2
A29
A029
3
A43
A043
4
A122
A122
5
A317
A317
6
B3
B003
7
B20
B020
8
C5
C005
9
C33
C033
10
C144
C144
如果希望EXCEL改變排序的規(guī)則，需要將數(shù)據(jù)做一些改變。
在B1中輸入公式：LEFT(A1,1)& RIGHT("000"& RIGHT(A1,LEN(A1)-1),3) 下拖
單擊B2，單擊工具欄上的“升序排列”即可。
隨機(jī)排序
如A、B列分別為“歌手”和“歌名”，在C1輸入“次序”，在C2輸入公式：
＝RAND（），下拖，單擊C2，單擊工具欄上的“降序排列”即可對(duì)歌曲清單進(jìn)行隨機(jī)排序。
排序的問(wèn)題
我想要這樣的排序: 2001-2003
2004-2006
2007-2009
2010-2012;
其實(shí)不是數(shù)據(jù)排序，應(yīng)該是數(shù)據(jù)填充。
輸入公式=LEFT(E3,4)+3&"-"&RIGHT(E3,4)+3 即可。
怎樣才能讓數(shù)列自動(dòng)加數(shù)
怎樣做才能讓數(shù)列自動(dòng)加數(shù)
A A0001
B B0001
A A0002
C C0001
A A0003
B B0002
C C0002
公式為=A1&"000"&COUNTIF(A$1:A1,A1)向下拖
=TEXT(COUNTIF(A$1:A1,A1),"!"&A1&"0000")否則數(shù)字超過(guò)9就錯(cuò)誤了。
一個(gè)排序問(wèn)題
一個(gè)電子表格，格式是101、102... 999,10101、10102... 99901,1010101,1020201... 9990101,請(qǐng)問(wèn)如何將它排列成101,10101,1010101,102,10201,1020101,... 999,99901,9990101 的形式。
我在數(shù)字前加了個(gè)字母,比如"d"&"數(shù)字",然后用排序就可以把它們按你的需求排列了.最后再把字母"d"去掉。
數(shù)字的自動(dòng)排序,插入后不變?
1 趙一 總經(jīng)理
2 趙二 副經(jīng)理
3 趙三 副經(jīng)理
4 趙四 技術(shù)員
5 趙五
6 趙六 員工
如上的一個(gè)表,如何實(shí)現(xiàn)當(dāng)我把趙六這一整行(第6行)插入到上面的表中時(shí),A列的序列號(hào)不變?最后的效果如下:
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)生自動(dòng)序號(hào)
姓名 序號(hào)
張三 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ù)來(lái)對(duì)成績(jī)進(jìn)行排名，用起來(lái)非常地方便。
=IF(ISERR(RANK(M3,M:M)),"",RANK(M3,M:M))
A列是成績(jī),B列是排名
=SUMPRODUCT((A$1:A$9>A1)/COUNTIF(A$1:A$9,A$1:A$9))+1
自動(dòng)排名公式
=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
百分比排名的公式寫(xiě)法為：
=PERCENTRANK($C$3:$C$12,C3)
平均分及總分排名
=AVERAGE(B2:E2)
=RANK(F2,$F$2:$F$65536)
求名次排名
統(tǒng)計(jì)成績(jī)時(shí)遇到一個(gè)分別求班級(jí)和年級(jí)總分名次排名的問(wèn)題，不曉得應(yīng)該運(yùn)用什么公式來(lái)實(shí)現(xiàn)。
班級(jí)名次：
=SUMPRODUCT((BJ=A2)*(ZF>E2))+1
年級(jí)名次：
=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))
對(duì)于普通排名分?jǐn)?shù)相同時(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ù)比高低名次成績(jī)排名
=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) 倒排序
美國(guó)式排名
=RANK(K247,$K$247:$K$270)
=RANK(B1,$B1:$H1)
中國(guó)式排名
=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｝
求最精簡(jiǎn)的自動(dòng)排名公式
=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ī)進(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))
無(wú)并列排名
=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｝（需輔助列）
無(wú)并列分段排名
｛=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
成績(jī)排名
序號(hào)
姓名
語(yǔ)文
數(shù)學(xué)
英語(yǔ)
1
楊增海
135
136
146
2
郭愛(ài)玲
138
137
141
3
華志鋒
134
138
141
4
袁文飛
134
143
135
能否用一個(gè)公式直接找出所用考生中語(yǔ)文成績(jī)中第100名的成績(jī)是多少?
=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))
能否用一個(gè)公式直接找出所用考生中語(yǔ)文成績(jī)中按與考人數(shù)的35%切線(xiàn)中位于第35%的成績(jī)是多少?
升冪
=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、對(duì)英語(yǔ)進(jìn)行排名，缺考不計(jì)算在內(nèi)。
2、對(duì)英語(yǔ)進(jìn)行排名，缺考計(jì)算在內(nèi)。
英語(yǔ)
英語(yǔ)排名
42
9
62
3
72
1
48
5
48
5
72
1
54
4
42
9
缺考
缺考
45
8
46
7
缺考不計(jì)算在內(nèi)
b2=IF(A2="缺考","",RANK(A2,$A$2:$A$13)) 然后按照B列排序
缺考計(jì)算在內(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ù)
排名
A
1
8
A
5
7
A
6
6
小計(jì)
12
B
8
4
B
9
3
B
7
5
小計(jì)
24
C
18
1
C
11
2
小計(jì)
29
=IF(A2="小計(jì)","",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)
如何實(shí)現(xiàn)每日各車(chē)間產(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ù)相同時(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),)｝
篩選后自動(dòng)產(chǎn)生序列號(hào)并匯總
自動(dòng)產(chǎn)生序列號(hào)：在A1輸入以下公式，往下拖。
=SUBTOTAL(3,$B$2:B2)*1
自動(dòng)匯總，用以下公式：
=SUBTOTAL(9,$B$2:B2)
說(shuō)明：匯總時(shí)，不要在“全選”狀態(tài)下進(jìn)行，先“篩選”出某一單位，自動(dòng)求和∑。然后再恢復(fù)到“全選”或者選擇任何單位，就能自動(dòng)匯總了（在“篩選”出某一單位進(jìn)行求和時(shí)，一般表格會(huì)自動(dòng)產(chǎn)生以上匯總公式）。
其它：如同時(shí)要在其它單元格顯示人數(shù)，在“全選”狀態(tài)下，選定單元格，點(diǎn)“fx”（用“sum”函數(shù)）再點(diǎn)擊序列號(hào)最末尾數(shù)，即可。
如何篩選奇數(shù)行
公式=MOD(A1,2)=1
函數(shù)篩選姓名
如何把兩列中只要包含A和A+的人員篩選出來(lái)
=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)))
如何實(shí)現(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ù)組公式）
如何請(qǐng)?jiān)贜列中列出A1:L9中每列都存在的數(shù)值
｛=IF(ROW()>SUM(--x),"",INDEX(A:A,SMALL(IF(x,ROW($A$1:$A$9)),ROW())))｝
自動(dòng)為性別編號(hào)的問(wèn)題
有一個(gè)編碼,5位，第1位,1為男,2為女，后面4位,代表他的編號(hào),從0001-9999，如何達(dá)到下表：
性別 編碼
男 10001
男 10002
女 20001
男 10003
女 20002
男的也是從0001-9999
女的也是從0001-9999
如果你是已經(jīng)輸入了其它信息，僅僅為快速輸入編碼的話(huà)。用篩選可以實(shí)現(xiàn)吧。
先以“男”為關(guān)鍵字進(jìn)行排序，然后在第一個(gè)男的編碼輸入10001，下拉復(fù)制到最后一單即可。同理再以“女”排序。完成目標(biāo)。
用公式：=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖
【文本與頁(yè)面設(shè)置】
EXCEL中如何刪除*號(hào)
在錄入賬號(hào)是錄入了*號(hào)，如何刪除。
可以用函數(shù) SUBSTITUTE(a1,"*","")
查找~*，替換為空。
將字符串中的星號(hào)“*”替換為其它字符
在查找欄輸入~*
替換為“-”即可。
去空格函數(shù)
如何刪去單元格中的空格，如姓名前，中，后的空格，即單元格中是兩個(gè)字的人名中間有一個(gè)空格，想刪去有何方法。如：中 國(guó)，改為：中國(guó)。
1、用公式：=SUBSTITUTE(A2," ","") 注：第一對(duì)雙引號(hào)中有一空格。而第二個(gè)“”中是無(wú)空格的。
2、利用查找－替換，一次性全部解決。
“編輯”－“替換”（或Ctrl+H），在“查找”欄內(nèi)輸入一空格，“替換”什么也不輸入（空白）。然后“全部替換”即可。
3、有一個(gè)專(zhuān)門(mén)刪除空格的函數(shù): TRIM()
在EXCEL編輯欄里，不管輸中文還是英文只能輸一個(gè)字節(jié)的空格，但如果字與字中間是兩個(gè)字節(jié)的空格，那么TRIM（）就不起作用了，它就不認(rèn)為是一個(gè)空格，而是一個(gè)漢字，怎么去“TRIM”也沒(méi)用。如：?jiǎn)卧馎1中有“中 心 是”，如果用TRIM則變成“中 心 是”， 想將空格全去掉，只能用SUBSTITUDE（）函數(shù)，多少空格都能去掉。
如何去掉字符和單元格里的空格
8900079501 8900079501~
1900078801 1900078802~
=SUBSTITUTE(B2,"~","")
怎樣快速去除表中不同行和列的空格
編輯-定位-定位條件-空值,可選中所有空單元格, 再刪除。
如何禁止輸入空格
在Excel中如何通過(guò)編輯“有效數(shù)據(jù)”來(lái)禁止錄入空格？煩請(qǐng)大俠們費(fèi)心解答。
解答：有效性公式。=COUNTIF(A1,"* *")=0
(注：COUNTIF(A1,"* *") 在單元格有空格時(shí)結(jié)果為1,沒(méi)有空格時(shí)結(jié)果為0
如希望第一位不能輸入空格:countif(a1," *")=0
如希望最后一位不能輸入空格:countif(a1,"* ")=0)
代替單元格中字符串
單元格編號(hào)，開(kāi)始位數(shù)，從開(kāi)始位數(shù)算起第幾位數(shù)，要用于代替的的字符串。
windows2000變成windows2K
=REPLACE(B2,8,3,"K")
單元格編號(hào)，要代替掉的字符，要用作代替的字符，第幾個(gè)。
代替單元格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ù)中的第二個(gè)參數(shù)的雙引號(hào)一定不能是中文格式的（不能用任意中文輸入法輸入的雙引號(hào)。）
實(shí)例： 20000 目的： 變成帶有美元符號(hào)的字符
10000 變成帶有人民幣符號(hào)的字符
151581 變成帶有歐元符號(hào)的字符
1451451 變成中文繁體的字符
15748415 變成中文簡(jiǎn)體的字符
操作步驟： =TEXT(B72,"$0.00") 結(jié)果： $20000.00
=TEXT(B73,"￥0.00") ￥10000.00
=TEXT(B74,"€0.00") €151581.00
=TEXT(B75,"[DBNum2]G/通用格式") 壹佰肆拾伍萬(wàn)壹仟肆佰伍拾壹
=TEXT(B76,"[DBNum1]G/通用格式") 一千五百七十四萬(wàn)八千四百一十五
把有六百多個(gè)單元格的一列，變成一頁(yè)的多列
有一張表，共有14頁(yè)，但每頁(yè)只有一列，如何把他們整合在一起，變成一頁(yè)（按每頁(yè)的順序），如果使用剪切和粘貼的方式，那樣太麻煩。
=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ù)排成一列
a1
b1
c1
d1
e1
f1
g1
h1
i1
a2
b2
c2
d2
e2
g2
h2
i2
a3
c3
d3
g3
h3
i3
a4
c4
g4
h4
i4
A5
c5
g5
h5
g6
a1
a2
a3
a4
A5
b1
｛=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ù)”→“分列”，此時(shí)會(huì)出現(xiàn)一個(gè)對(duì)話(huà)框，選“固定寬度”或“分隔符號(hào)”。如為前者則下一步后只要用鼠標(biāo)輕點(diǎn)資料即可以按任意寬度進(jìn)行分割了，如為后者則只要有明顯的分隔符號(hào)即可，下一步后就可以自定義剛分的列的格式了，定好后就算完成了。
步驟：
1、先確定1列的最適合的列寬,再將其寬度乘以分成列數(shù),即
分列前的列寬=最適合的列寬×需分成的列數(shù).
2、編輯—填充—內(nèi)容重排。
3、數(shù)據(jù)—分列。
首寫(xiě)字母大寫(xiě)
把單元格編號(hào)中的單詞首寫(xiě)字母變成大寫(xiě)字母，其余字母變成小寫(xiě)。
如china - China
=PROPER(B160)
把單元格編號(hào)中的小寫(xiě)字母變成大寫(xiě)字母
lafayette148 LAFAYETTE148
=UPPER(B1)
=LOWER(B1) （大寫(xiě)字母變成小寫(xiě)字母公式）
讓姓名左右對(duì)齊
姓名用字，有的是三個(gè)漢字，有的是兩個(gè)漢字，打印出來(lái)很不美觀，要使姓名用字是兩個(gè)字的與三個(gè)字的左右對(duì)齊也有兩種方法:
方法一:格式設(shè)置法。選中我們已經(jīng)刪除完空格的姓名單元格，單擊“格式→單元格”在打開(kāi)的“單元格格式”對(duì)話(huà)框中的水平對(duì)齊方式中選擇“分散對(duì)齊”選項(xiàng)，確定退出后即可使學(xué)生姓名用字左右對(duì)齊。
方法二:函數(shù)公式法。利用Excel中的“IF”、“LEN”、“MID”三種函數(shù)組合可使姓名用字左右對(duì)齊。具體示例為:在C3單元格中輸入公式:“=IF(LEN(B2)>=3,B2,(MID(B2,1,1)&&" "&&MID(B2,2,1)))”，確定后利用填充柄將該公式進(jìn)行復(fù)制即可。
數(shù)字居中而小數(shù)點(diǎn)又對(duì)齊
可在小數(shù)點(diǎn)的任一邊替無(wú)效的零加入空間，以便當(dāng)格式設(shè)定為固定寬字型，小數(shù)點(diǎn)可以對(duì)齊。
格式－單元格－數(shù)字－自定義－???.???-確定
請(qǐng)問(wèn)：小數(shù)點(diǎn)后的“0”還有辦法顯示嗎？比如：
2.0
12.001
格式－單元格－數(shù)字－自定義－???.0?-確定
計(jì)算指定單元格編號(hào)組中非空單元格的數(shù)量
計(jì)算B252到B262之間的非空單元格的數(shù)量。
=COUNTA(B252:B262)
比較兩個(gè)單元格內(nèi)容是否一致
74P125148 74P125148
比較單元格B53與C53中的內(nèi)容是否一致。
假如內(nèi)容一致，那么返回值為T(mén)RUE，不一致的話(huà)，返回值為FALSE。
=EXACT(B53,C53)
結(jié)果：TRUE
怎么樣設(shè)置才能讓這一列的每個(gè)單元格只能輸入12位
怎么樣設(shè)置才能讓某一列或某一行的每個(gè)單元格只能輸入12位,(阿拉伯?dāng)?shù)字和26個(gè)英文字母在內(nèi),沒(méi)有中文。)
選中A列，設(shè)置數(shù)據(jù)有效性：自定義>公式：“=LEN(A1)=12”
如何讓工作表奇數(shù)行背景是紅色偶數(shù)行背景是藍(lán)色
用條件格式
=ROW()/2=INT(ROW()/2) 設(shè)定顏色
條件格式: 公式為 =MOD(ROW(),2)=0
計(jì)算特定的一組單元格中，滿(mǎn)足條件的單元格的個(gè)數(shù)
仍以上題為例，計(jì)算三個(gè)人在B307到B313中各自所占的單元格數(shù)。
李六的： =COUNTIF(B307:B313,B323)
王武的： =COUNTIF(B307:B313,C323)
陳豐的： =COUNTIF(B307:B313,D323)
姓名： 李六 王武 陳豐
結(jié)果： 3 2 2
把文本格式的數(shù)字轉(zhuǎn)換成真正的數(shù)字
=VALUE(B1)
設(shè)置頁(yè)碼
如何設(shè)置“第×頁(yè)，共×頁(yè)”頁(yè)碼。
在頁(yè)腳中設(shè)置：第&[頁(yè)碼]頁(yè)，共&[總頁(yè)碼]頁(yè) 即可
Excel表格里如何插入頁(yè)碼的?
我想把表格中的第1頁(yè)的頁(yè)碼從第30頁(yè)開(kāi)始編,不知道該如何實(shí)現(xiàn),哪位高手能幫忙?
在頁(yè)面設(shè)置的頁(yè)眉頁(yè)腳中設(shè)置。
在插入頁(yè)腳中輸入＆[頁(yè)碼]+29即可。
如何設(shè)置頁(yè)腳首頁(yè)為第5頁(yè)
Excel頁(yè)腳設(shè)置頁(yè)碼是按順序來(lái)的，首頁(yè)為第1頁(yè)。如何設(shè)置首頁(yè)為第5頁(yè)？
在頁(yè)腳輸入“第 &[頁(yè)碼]+4 頁(yè)”，結(jié)果本該顯示“第1頁(yè)”的就顯示第5頁(yè)了。（用于多個(gè)工作表全選）
頁(yè)面設(shè)置—頁(yè)面—起始頁(yè)碼輸入5（用于單個(gè)工作表）。
表格的頁(yè)腳問(wèn)題
是這樣的，我每個(gè)表格有4張，總共一個(gè)文件里面有6個(gè)表格，相當(dāng)于總共24頁(yè)，我希望它能夠自動(dòng)打，而且我想設(shè)置頁(yè)腳為，共24頁(yè)，第？頁(yè)，怎么辦？
試一試選擇所有的工作表（工作組）然后再設(shè)置頁(yè)腳，打印的時(shí)候也是用工作組打印。
把所有工作表選中就可以了然后你再點(diǎn)打印,或者你先瀏覽,再設(shè)置也行!
按shift依次點(diǎn)表單的標(biāo)簽。
其實(shí),就是在選擇瀏覽或者打印前,先選中你想要的工作表,然后再一個(gè)個(gè)的瀏覽,就相當(dāng)于你的操作對(duì)所有工作表都已經(jīng)起了作用似的。
請(qǐng)樓主試一試，按以下步驟辦：
1．文件→頁(yè)面設(shè)置→頁(yè)眉/頁(yè)腳→頁(yè)腳（F）,選自己需要的頁(yè)腳格式
2．文件→打印→整個(gè)工作簿。
無(wú)拘無(wú)束的頁(yè)眉
頁(yè)眉和頁(yè)腳大家都用過(guò)吧？用得最多的莫過(guò)于當(dāng)前第幾頁(yè)/總共第幾頁(yè)。但你是否想過(guò)將“第N頁(yè)/總M頁(yè)”無(wú)拘無(wú)束的放置，而不是只能置于頁(yè)眉頁(yè)腳中？，現(xiàn)教你一法，可以通用。到任何地方均可使用。
首先：點(diǎn)CTRL+F3打開(kāi)定義名稱(chēng)，再在上面輸入“縱向當(dāng)前頁(yè)”，在下面引用位置處輸入=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1,MATCH(ROW(),GET.DOCUMENT(64))+1)。然后再繼續(xù)添加第二個(gè)名稱(chēng)：“橫向當(dāng)前頁(yè)”，在下面引用位置處輸入=IF(ISNA(MATCH(column(),GET.DOCUMENT(65))),1,MATCH(column(),GET.DOCUMENT(65))+1)。再輸入“總頁(yè)”；引用位置處輸入：=GET.DOCUMENT(50)+RAND()*0。最后再定義“無(wú)拘無(wú)束的頁(yè)眉”；引用位置：="第"&IF(橫向當(dāng)前頁(yè)=1,縱向當(dāng)前頁(yè),橫向當(dāng)前頁(yè)+縱向當(dāng)前頁(yè))&"頁(yè)/共"&總頁(yè)&"頁(yè)"。
現(xiàn)在你在工作表任何處輸入=無(wú)拘無(wú)束的頁(yè)眉即可。
本公式核心在于GET.DOCUMENT,這是4.0宏函數(shù),OFFICE 97及以前版專(zhuān)用,新版OFFICE中仍兼容,但只限定義名稱(chēng)中使用。
在幫助中說(shuō)(64和65為其參數(shù)):64 行數(shù)的數(shù)組,相應(yīng)于手動(dòng)或自動(dòng)生成頁(yè)中斷下面的行。65 列數(shù)的數(shù)組。相應(yīng)于手動(dòng)或自動(dòng)生成的頁(yè)中斷右邊的列。"
本公式中取64,用于計(jì)算當(dāng)前行與分頁(yè)符之前后關(guān)系.GET.DOCUMENT(64)即返回分頁(yè)符所在行下一行之行號(hào)（亦即第二頁(yè)第一行）。
判斷當(dāng)前行是否大于分頁(yè)符所在行
“=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1”此句利用MATCH之模糊查找功能將當(dāng)前行號(hào)與分頁(yè)符下行（分頁(yè)符下一行是一個(gè)單元N行的一維數(shù)組，文檔有幾頁(yè)則有幾行，本實(shí)例文檔有三頁(yè)，請(qǐng)看公式求值之計(jì)算圖示）做比較，此處省略MATCH第三參數(shù)，即查找小于等于目標(biāo)值，如果目標(biāo)值大于當(dāng)前行號(hào)，則MATCH返回錯(cuò)誤值。那么此處再用IF(ISNA(),1)加以判斷，即若找不到小于等于當(dāng)前行號(hào)的值則顯示1，表示當(dāng)前行處于第一頁(yè)。
取得當(dāng)前行所在頁(yè)
＝MATCH(ROW(),GET.DOCUMENT(64))+1
如果前一個(gè)MATCH返回FALSE，則取IF函數(shù)第三參數(shù)值即MATCH(ROW(),GET.DOCUMENT(64))+1
此參數(shù)再用MATCH在GET.DOCUMENT(64)產(chǎn)生的數(shù)組中查找當(dāng)小于等于前行號(hào)的數(shù)值，若數(shù)組中第N個(gè)值小于等于當(dāng)前行號(hào)，則當(dāng)前行在N+1頁(yè)。
取得總頁(yè)
=GET.DOCUMENT(50)+RAND()*0
GET.DOCUMENT(50)即求當(dāng)前設(shè)置下欲打印的總頁(yè)數(shù)，其中包括注釋?zhuān)绻募閳D表，值為1
RAND（）*0作用是當(dāng)文件分頁(yè)數(shù)改變時(shí)，本公式結(jié)果根隨變化，起公式結(jié)果刷新作用。
獲取“橫向當(dāng)前頁(yè)”
橫向當(dāng)前頁(yè)與縱向當(dāng)前頁(yè)原理相同，改ROW（）為COLUMN（），并將GET.DOCUMENT參數(shù)改為65即可
若你的工作表只有縱向分頁(yè)或者橫向分頁(yè)，那么現(xiàn)在就可以使用前面的公式定義的名稱(chēng)獲取當(dāng)前頁(yè)及總頁(yè)了；但如果分頁(yè)方式為橫向多頁(yè)縱向也多頁(yè)呢？則在將以上“橫向當(dāng)前頁(yè)”與“縱向當(dāng)前頁(yè)”無(wú)縫接合方可使用，否則將返回錯(cuò)誤結(jié)果。
最后生成“無(wú)拘無(wú)束的頁(yè)眉”（或者改稱(chēng)文件分頁(yè)）
="第"&IF(橫向當(dāng)前頁(yè)=1,縱向當(dāng)前頁(yè),橫向當(dāng)前頁(yè)+縱向當(dāng)前頁(yè))&"頁(yè)/共"&總頁(yè)&"頁(yè)"
公式解說(shuō)完畢！各位可以用不同的文字定義名稱(chēng)在各自的工作表中試用了。
打印表頭
在Excel中如何實(shí)現(xiàn)一個(gè)表頭打印在多頁(yè)上？
請(qǐng)選擇文件-頁(yè)面設(shè)置-工作表-打印標(biāo)題-頂端標(biāo)題行，然后選擇你要打印的行。
打印表尾，通過(guò)Excel直接提供的功能應(yīng)該是無(wú)法實(shí)現(xiàn)的，需要用vba編制才行。
Excel打印中如何不顯示錯(cuò)誤值符號(hào)
在“頁(yè)面設(shè)置”－“工作表”－“錯(cuò)誤單元格打印為”中，
將“顯示值”改為“空白”即可。
對(duì)于一些不可打印的字符的處理
對(duì)于一些不可打印的字符（在Excel顯示中類(lèi)似空格），直接用替換方法不容易去掉。
可以這么做：
=SUBSTITUTE(CLEAN(A1)," ","")
用那個(gè)函數(shù)可將個(gè)位數(shù)前面的零值顯示出來(lái)?
如果單元格A1的內(nèi)容是5,在A2用那個(gè)函數(shù)可將A1的內(nèi)容變?yōu)?5?
(Text或value也可,總之個(gè)位數(shù)的零也顯示,例:5變05,15則15)
可以用＝TEXT(A2,"00")
或?qū)卧窀袷阶远x為00
如果你要在A3的前面插入100行
可以這樣:在名稱(chēng)框輸入 3:103-回車(chē)-ctrl+shift+"+"（大鍵盤(pán)）
請(qǐng)問(wèn)如何每隔30行粘貼一新行
偶在班上負(fù)責(zé)統(tǒng)計(jì)企業(yè)進(jìn)出口業(yè)務(wù)量，領(lǐng)導(dǎo)要求每30家做一合計(jì)數(shù)，偶只有每隔30行插入復(fù)制單元格的方法來(lái)添加的，很是麻煩，請(qǐng)教各位大蝦有什么快捷的方法呀
在最后加一輔助列,輸入=INT((ROW()-1)/31)+1 (假設(shè)一個(gè)標(biāo)題行)
然后以該行分類(lèi)字段匯總.
在工作表里有連續(xù)10行數(shù)據(jù), 現(xiàn)在要每行間格2行
解答：1：如sheet1！$A$1:$D$10中有連續(xù)10行資料，在sheet2中把sheet1中的數(shù)據(jù)每行間隔2行 ,sheet2！A1中公式可用:
=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$D$10,INT((ROW()-1)/2)+1,COLUMN()),""))
然后填充公式（注意公式在SHEET2中的填充范圍，超過(guò)范圍會(huì)出錯(cuò)?。?div style="height:15px;">
如果想在某一行上面插入幾行空白行，可以用鼠標(biāo)拖動(dòng)自此行開(kāi)始選擇相應(yīng)的行數(shù)，然后單擊右鍵，選擇插入。如果在每一行上面均插入一空白行，按住Ctrl鍵，依次單擊要插入新行的行標(biāo)按鈕，單擊右鍵，選擇插入即可。
如果用戶(hù)想刪除Excel工作表中的空行，一般的方法是需要將空行都找出來(lái)，然后逐行刪除，但這樣做操作量非常大，很不方便。下面提供二種快速刪除工作表中的空行的方法:
1、首先打開(kāi)要?jiǎng)h除空行的工作表，在打開(kāi)的工作表中單擊“插入→列”命令，從而插入一新的列X，在X列中順序填入整數(shù)，然后根據(jù)其他任何一列將表中的行排序，使所有空行都集中到表的底部。刪去所有空行中X列的數(shù)據(jù)，以X列重新排序，然后刪去X列。
2、如批量刪除空行，我們可以利用“自動(dòng)篩選”功能，把空行全部找到，然后一次性刪除。做法：先在表中插入新的一個(gè)空行，然后按下Ctrl+A鍵，選擇整個(gè)工作表，用鼠標(biāo)單擊“數(shù)據(jù)”菜單，選擇“篩選”項(xiàng)中的“自動(dòng)篩選”命令。這時(shí)在每一列的頂部，都出現(xiàn)一個(gè)下拉列表框，在典型列的下拉列表框中選擇“空白”，直到頁(yè)面內(nèi)已看不到數(shù)據(jù)為止。
在所有數(shù)據(jù)都被選中的情況下，單擊“編輯”菜單，選擇“刪除行”命令，然后按“確定”按鈕。這時(shí)所有的空行都已被刪去，再單擊“數(shù)據(jù)”菜單，選取“篩選”項(xiàng)中的“自動(dòng)篩選”命令，工作表中的數(shù)據(jù)就全恢復(fù)了。插入一個(gè)空行是為了避免刪除第一行數(shù)據(jù)。
如果想只刪除某一列中的空白單元格，而其它列的數(shù)據(jù)和空白單元格都不受影響，可以先復(fù)制此列，把它粘貼到空白工作表上，按上面的方法將空行全部刪掉，然后再將此列復(fù)制，粘貼到原工作表的相應(yīng)位置上。
有時(shí)為了刪除Excel工作簿中的空行，你可能會(huì)將空行一一找出然后刪除，這樣做非常不方便。你可以利用自動(dòng)篩選功能來(lái)實(shí)現(xiàn)，方法是:先在表中插入新的一行(全空)，然后選擇表中所有的行，單擊“數(shù)據(jù)→篩選→自動(dòng)篩選”命令，在每一列的頂部，從下拉列表中選擇“空白”。在所有數(shù)據(jù)都被選中的情況下，單擊“編輯→刪除行”，然后按“確定”，所有的空行將被刪去。 注意:插入一個(gè)空行是為了避免刪除第一行數(shù)據(jù)。
3、用自動(dòng)篩選，選擇一列用非空白，空白行就看不到了，打印也不會(huì)打出來(lái)。但是實(shí)際上還是在的，不算刪除。或者用自動(dòng)篩選選擇空白將空白行全顯出來(lái)一次刪完也可以。
4、先插入一列，在這一列中輸入自然數(shù)序列，然后以任一列排序，排序完后刪除數(shù)據(jù)后面的空行，再以剛才輸入的一列排序，排序后刪除剛才插入的一列。
也是在頁(yè)面設(shè)置中，設(shè)置上下頁(yè)邊距的調(diào)整可以實(shí)現(xiàn)，打印預(yù)覽看一下就可以看到是不是30行了，不到30行你可以將行距加寬，進(jìn)行調(diào)整，以我的經(jīng)驗(yàn)，加標(biāo)題的30行/頁(yè)大概行距是20，這樣連制表人的空間都留出來(lái)了。
在excel中,每條記錄都要加上標(biāo)題(隔行都加),如何才能快速實(shí)現(xiàn)?(只要打印出來(lái)能實(shí)現(xiàn)就成)。
單擊“工具”菜單中的“選項(xiàng)”，再單擊對(duì)話(huà)框中的“視圖”，找到“網(wǎng)格線(xiàn)”，使之失效(將左邊的“×”去掉)。
我們很喜歡為表格加上一道框線(xiàn)，不過(guò)這道框線(xiàn)又往往叫我們花掉很多時(shí)間來(lái)重畫(huà)，例如在下方多加一列時(shí)，Excel并不會(huì)把新列加在下方框線(xiàn)之上。又例如將上方的數(shù)據(jù)拷到最后一列時(shí)，下方的框線(xiàn)就會(huì)給蓋掉，變成穿了一個(gè)洞。
我的技巧就是在表格的最后一列留一列空列，并把它的列高定得很小，我就叫這一列「緩沖列」好了。把列高定小一點(diǎn)，除了美觀之外，還可以用作提醒用戶(hù)不要把數(shù)據(jù)打到緩沖列。你可以試試在緩沖列上加列或拷數(shù)據(jù)到緩沖列之上，框線(xiàn)并不會(huì)給弄亂。
通常EXCEL的列標(biāo)都是用大寫(xiě)英文字母表示的，我的EXCEL的列標(biāo)今天都變成了阿拉伯?dāng)?shù)字表示的了，請(qǐng)教這兩種表示方法有什么不同，如果想恢復(fù)成字母表示的該怎么辦。
你會(huì)發(fā)現(xiàn)如果原單元格上有批注或其實(shí)Shape對(duì)象的話(huà)，一樣也復(fù)制了，跟原數(shù)據(jù)按Ctrl+C，選擇區(qū)域.再按Enter這個(gè)意思是一樣的。
例如：先找到第一個(gè)數(shù)據(jù)并將字體改為紅色,然后雙擊格式刷,當(dāng)查找到其它相同數(shù)據(jù)時(shí),再按Ctrl+A，excel就會(huì)將新找到的數(shù)據(jù)自動(dòng)改為紅色與之區(qū)分。
具體操作為：Ctrl+F→在查找欄輸入要查找的值→查找下一個(gè)→設(shè)置格式（不要退出查找對(duì)話(huà)框）→雙擊格式刷→查找全部→Ctrl+A→關(guān)閉
在一個(gè)Excel工作表中作業(yè)時(shí)，雙擊某單元格的四周（上、下、左、右），會(huì)迅速移動(dòng)光標(biāo)的位置，若是雙擊上方即刻回到單元格所在列的最頂端，雙擊下方則移動(dòng)到最底端的編輯外，同樣雙擊左右也是到相對(duì)應(yīng)的地方，雙擊單元格中間則變?yōu)檩斎霠顟B(tài)。大家可以試試，這樣比移動(dòng)工作表中的下拉圖標(biāo)快捷。
如果想再進(jìn)一步調(diào)整行間距，可再單擊“格式”菜單，選擇“拼音指南/設(shè)置”打開(kāi)“拼音屬性”窗口，切換到“字體”選項(xiàng)卡下，把字號(hào)設(shè)置大一點(diǎn)，確定后行間距就會(huì)相應(yīng)增大，反之則減小。
我們知道，通過(guò)拖動(dòng)行或列間的分界線(xiàn)可以改變行高或列寬，但怎樣同時(shí)改變向行或幾列的高度或?qū)挾饶兀?div style="height:15px;">
