Excel常用公式和技巧公式

1、常用函數(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) 從身份證號碼中提取出性別=IF(MOD(MID(A1,15,3),2),男,女) 從身份證號碼中進行年齡判斷以2006年10月31日為基準日,按按身份證計算年齡(周歲)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1,2006-10-31,y)按身份證號分男女年齡段按身

2、份證號分男女年齡段，身份證號在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(-(DATE

3、DIF(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)根據(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(猴雞狗豬鼠?；?/p>

4、兔龍蛇馬羊,MOD(YEAR(A2),12)+1,1) （2007）如何統(tǒng)計各年齡段的數(shù)量如何計算20-50歲的人數(shù)?=COUNTIF(C3:C17,=20)-COUNTIF(C3:C17,50)如何統(tǒng)計40-50歲的人的個數(shù)countif(a:a,40)-countif(a:a,50)=COUNTA(A1:A10,D1:D10)如何讓EXCEL自動從頭統(tǒng)計到當(dāng)前單元格情況如下： C列要根據(jù)A列的內(nèi)容來統(tǒng)計B列的數(shù)據(jù)，范圍從A1：An，即當(dāng)A列中An有數(shù)據(jù)時，Cn自動根據(jù)An的值，統(tǒng)計B1：Bn的數(shù)據(jù)。=SUM(INDIRECT(B1:B & LARGE(A1:A65535)*(ROW(A1:

5、A65535),1) 統(tǒng)計人數(shù)見表：性別年齡男6女35男3男55男21男53.5女55女56男65女45女53男51如何計算20-50歲的人數(shù)?=COUNTIF(C3:C17,=20)-COUNTIF(C3:C17,50)如何計算男20-50歲的人數(shù)?=SUMPRODUCT(B3:B17=男)*(C3:C17=20)*(C3:C17=50)nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn【時間和日期應(yīng)用】自動顯示當(dāng)前日期公式=YEAR(NOW() 當(dāng)前年=MONTH(NOW() 當(dāng)前月=DAY(NOW() 當(dāng)前日如何在單元格中自動填入當(dāng)前日期Ctrl+;顯示

6、昨天的日期每天需要單元格內(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)日期格式轉(zhuǎn)換公式將 “01/12/2005” 轉(zhuǎn)換成“200

7、50112”格式RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)YEAR($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格式，可用公式：=

8、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-6TEXT(20050806,0000-00-00)*1，單元格設(shè)置日期型 顯示：2005-8-6VALUE(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”

9、轉(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=DA

10、TE(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“月”，即可。但這方法打印出來顯示為：2006/5/將“1968年6月12日”轉(zhuǎn)換為“1968/6/12”格式=YEAR(A1)

11、&/&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)&月也可以自定義格式 $-404e年m月將“1978-5-2”包含年月日的日期

12、轉(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格式，可以使用強制文本（前面加號）或使用公式=TEXT(A1,YYYY/MM/DD)。也可以用另一種公式：=IF(ISERROR(TEXT(A1,yyyy/mm/dd),TEXT(A1,0000!/00!/00),TEXT(A1,yyyy/mm/dd)將“二三年十二月二十五

13、日”轉(zhuǎn)為“2003-12-25”格式，1、可以用數(shù)組公式將中文日期轉(zhuǎn)化為日期系列數(shù)=14610+MATCH(SUBSTITUTE(A3,元,一),TEXT(ROW($14611:$55153),DBNum1yyyy年m月d日),0)該公式速度較慢。2、改進后的公式，速度要快的多：=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),DBNum10000),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),元,一),TEXT(ROW($1:$366),DBNum1m月d日),0),DAY(MATCH(SUBSTITUTE(M

14、ID(A7,6,7),元,一),TEXT(ROW($1:$366),DBNum1m月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.

15、=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、求總體標準偏差，8、求標準偏差、9、求和，10、求方差，11、求總體方差。自動排序=SUBTOTAL(3,$B$2:B2)*1=IF(A2A1,1,N(C1)+1)自動生成序號比如在第二列中

16、輸入內(nèi)容回車后第一列的下一行自動生成序列號。=IF(B2,A2+1,)如何自動標示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ù)量

17、來排列分類，但是EXCEL并不能直接按字數(shù)排序。需要先計算出每首歌曲的字數(shù)，然后再進行排序。 如A、B列分別為“歌手”和“歌名”，在C1輸入“字數(shù)”，在C2輸入公式：LEN(B2) 下拖，單擊C2，單擊工具欄上的“升序排列”即可，刪除C列。排序字母與數(shù)字的混合內(nèi)容日常使用中，表格經(jīng)常會有包含字母和數(shù)字混合的數(shù)據(jù)，對此類數(shù)據(jù)排序時，通常是先比較字母的大小，再比較數(shù)字的大小，但EXCEL是按照對字符進行逐位比較來排序的，如下表：A7排在第5位，而不是第1位。排序結(jié)果無法令人滿意。A1A1222A293A3174A435A76B207B38C1449C510C33AB1A7A0072A29A0293

18、A43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144如果希望EXCEL改變排序的規(guī)則，需要將數(shù)據(jù)做一些改變。在B1中輸入公式：LEFT(A1,1)& RIGHT(000& RIGHT(A1,LEN(A1)-1),3) 下拖單擊B2，單擊工具欄上的“升序排列”即可。隨機排序如A、B列分別為“歌手”和“歌名”，在C1輸入“次序”，在C2輸入公式：RAND（），下拖，單擊C2，單擊工具欄上的“降序排列”即可對歌曲清單進行隨機排序。排序的問題我想要這樣的排序: 2001-2003 2004-2006 2007-2009 20

19、10-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,1

20、0101,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:

21、$B2)在A1中輸入公式：“=if(b1=,counta($b$1:b1)”后下拉復(fù)制至A列各行即可（“”不必輸入）篩選后自動產(chǎn)生序列號并匯總自動產(chǎn)生序列號：在A1輸入以下公式，往下拖。=SUBTOTAL(3,$B$2:B2)*1自動匯總，用以下公式：=SUBTOTAL(9,$B$2:B2)說明：匯總時，不要在“全選”狀態(tài)下進行，先“篩選”出某一單位，自動求和。然后再恢復(fù)到“全選”或者選擇任何單位，就能自動匯總了（在“篩選”出某一單位進行求和時，一般表格會自動產(chǎn)生以上匯總公式）。其它：如同時要在其它單元格顯示人數(shù)，在“全選”狀態(tài)下，選定單元格，點“fx”（用“sum”函數(shù)）再點擊序列號最末尾數(shù)

22、，即可。如何篩選奇數(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:

23、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，如何達到下表：性別編碼男 10001男 10002女 20001男 10003女 20002男的也是從0001-9999女的也是從0001-9999如果你是已經(jīng)輸入了其它信息，僅僅為快速輸入編碼的話。用篩選可

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

25、。如：中國，改為：中國。1、用公式：=SUBSTITUTE(A2, ,) 注：第一對雙引號中有一空格。而第二個“”中是無空格的。2、利用查找替換，一次性全部解決?！熬庉嫛薄疤鎿Q”（或Ctrl+H），在“查找”欄內(nèi)輸入一空格，“替換”什么也不輸入（空白）。然后“全部替換”即可。3、有一個專門刪除空格的函數(shù): TRIM()在EXCEL編輯欄里，不管輸中文還是英文只能輸一個字節(jié)的空格，但如果字與字中間是兩個字節(jié)的空格，那么TRIM（）就不起作用了，它就不認為是一個空格，而是一個漢字，怎么去“TRIM”也沒用。如：單元格A1中有“中心是”，如果用TRIM則變成“中心是”， 想將空格全去掉，只能用SUB

26、STITUDE（）函數(shù)，多少空格都能去掉。如何去掉字符和單元格里的空格8900079501 89000795011900078801 1900078802=SUBSTITUTE(B2,)怎樣快速去除表中不同行和列的空格編輯-定位-定位條件-空值,可選中所有空單元格, 再刪除。如何禁止輸入空格在Excel中如何通過編輯“有效數(shù)據(jù)”來禁止錄入空格？煩請大俠們費心解答。解答：有效性公式。=COUNTIF(A1,* *)=0(注：COUNTIF(A1,* *) 在單元格有空格時結(jié)果為1,沒有空格時結(jié)果為0如希望第一位不能輸入空格:countif(a1, *)=0如希望最后一位不能輸入空格:counti

27、f(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ù)的雙引號一定不能是中文格

28、式的（不能用任意中文輸入法輸入的雙引號。）實例： 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,DBNum2G/通用格式)壹佰肆拾伍萬壹仟肆佰伍拾壹=TEXT(B76,DBNum1G/通用格式)一千五百七十四萬八千四百一十五把有六百多個單元格的一列，變成一頁的多列有一

29、張表，共有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

30、(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)=O

31、FFSET($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)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)

32、+COLUMN($A$1:$I$10)*100000),ROW()/100000)將單元格一列分為多列如果有一列資料需要分為多列，只要先將此列選中，然后再選擇“數(shù)據(jù)”“分列”，此時會出現(xiàn)一個對話框，選“固定寬度”或“分隔符號”。如為前者則下一步后只要用鼠標輕點資料即可以按任意寬度進行分割了，如為后者則只要有明顯的分隔符號即可，下一步后就可以自定義剛分的列的格式了，定好后就算完成了。步驟：1、先確定1列的最適合的列寬,再將其寬度乘以分成列數(shù),即分列前的列寬=最適合的列寬需分成的列數(shù).2、編輯填充內(nèi)容重排。3、數(shù)據(jù)分列。首寫字母大寫把單元格編號中的單詞首寫字母變成大寫字母，其余字母變成小寫。如ch

33、ina - China=PROPER(B160)把單元格編號中的小寫字母變成大寫字母lafayette148 LAFAYETTE148=UPPER(B1)=LOWER(B1) （大寫字母變成小寫字母公式）讓姓名左右對齊姓名用字，有的是三個漢字，有的是兩個漢字，打印出來很不美觀，要使姓名用字是兩個字的與三個字的左右對齊也有兩種方法:方法一:格式設(shè)置法。選中我們已經(jīng)刪除完空格的姓名單元格，單擊“格式單元格”在打開的“單元格格式”對話框中的水平對齊方式中選擇“分散對齊”選項，確定退出后即可使學(xué)生姓名用字左右對齊。方法二:函數(shù)公式法。利用Excel中的“IF”、“LEN”、“MID”三種函數(shù)組合可使姓

34、名用字左右對齊。具體示例為:在C3單元格中輸入公式:“=IF(LEN(B2)=3,B2,(MID(B2,1,1)& &MID(B2,2,1)”，確定后利用填充柄將該公式進行復(fù)制即可。數(shù)字居中而小數(shù)點又對齊可在小數(shù)點的任一邊替無效的零加入空間，以便當(dāng)格式設(shè)定為固定寬字型 ，小數(shù)點可以對齊。格式單元格數(shù)字自定義?.?-確定請問：小數(shù)點后的“0”還有辦法顯示嗎？比如： 2.0 12.001格式單元格數(shù)字自定義?.0?-確定計算指定單元格編號組中非空單元格的數(shù)量計算B252到B262之間的非空單元格的數(shù)量。=COUNTA(B252:B262)比較兩個單元格內(nèi)容是否一致74P125148 74P1251

35、48比較單元格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ù)行背景是藍色用條件格式=ROW()/2=INT(ROW()/2) 設(shè)定顏色條件格式: 公式為 =MOD(ROW(),2)=0計算特定的一組單元格中，滿足條件的單元格的個數(shù)仍以上題為例，計算三個人

36、在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è)置頁碼如何設(shè)置“第頁，共頁”頁碼。在頁腳中設(shè)置：第&頁碼頁，共&總頁碼頁 即可Excel表格里如何插入頁碼的?我想把表格中的第1頁的頁碼從第30頁開始編,不知道該如何實現(xiàn),哪位高手能幫忙?在頁面設(shè)置的頁眉頁腳中設(shè)置。在插入頁腳中輸入頁碼+29即可。如何設(shè)置頁腳首頁為第5頁Excel頁腳設(shè)置頁碼是按順序

37、來的，首頁為第1頁。如何設(shè)置首頁為第5頁？在頁腳輸入“第 &頁碼+4 頁”，結(jié)果本該顯示“第1頁”的就顯示第5頁了。（用于多個工作表全選）頁面設(shè)置頁面起始頁碼輸入5（用于單個工作表）。表格的頁腳問題是這樣的，我每個表格有4張，總共一個文件里面有6個表格，相當(dāng)于總共24頁，我希望它能夠自動打，而且我想設(shè)置頁腳為，共24頁，第？頁，怎么辦？試一試選擇所有的工作表（工作組）然后再設(shè)置頁腳，打印的時候也是用工作組打印。把所有工作表選中就可以了然后你再點打印,或者你先瀏覽,再設(shè)置也行!按shift依次點表單的標簽。其實,就是在選擇瀏覽或者打印前,先選中你想要的工作表,然后再一個個的瀏覽,就相當(dāng)于你的操作

38、對所有工作表都已經(jīng)起了作用似的。請樓主試一試，按以下步驟辦：1文件頁面設(shè)置頁眉/頁腳頁腳（F）,選自己需要的頁腳格式2文件打印整個工作簿。無拘無束的頁眉頁眉和頁腳大家都用過吧？用得最多的莫過于當(dāng)前第幾頁/總共第幾頁。但你是否想過將“第N頁/總M頁”無拘無束的放置，而不是只能置于頁眉頁腳中？，現(xiàn)教你一法，可以通用。到任何地方均可使用。 首先：點CTRL+F3打開定義名稱，再在上面輸入“縱向當(dāng)前頁”，在下面引用位置處輸入=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64),1,MATCH(ROW(),GET.DOCUMENT(64)+1)。然后再繼續(xù)添加第二個名稱：“橫向當(dāng)前

39、頁”，在下面引用位置處輸入=IF(ISNA(MATCH(column(),GET.DOCUMENT(65),1,MATCH(column(),GET.DOCUMENT(65)+1)。再輸入“總頁”；引用位置處輸入：=GET.DOCUMENT(50)+RAND()*0。最后再定義“無拘無束的頁眉”；引用位置：=第&IF(橫向當(dāng)前頁=1,縱向當(dāng)前頁,橫向當(dāng)前頁+縱向當(dāng)前頁)&頁/共&總頁&頁。 現(xiàn)在你在工作表任何處輸入=無拘無束的頁眉即可。本公式核心在于GET.DOCUMENT,這是4.0宏函數(shù),OFFICE 97及以前版專用,新版OFFICE中仍兼容,但只限定義名稱中使用。在幫助中說(64和65

40、為其參數(shù)):64 行數(shù)的數(shù)組,相應(yīng)于手動或自動生成頁中斷下面的行。65 列數(shù)的數(shù)組。相應(yīng)于手動或自動生成的頁中斷右邊的列。本公式中取64,用于計算當(dāng)前行與分頁符之前后關(guān)系.GET.DOCUMENT(64)即返回分頁符所在行下一行之行號（亦即第二頁第一行）。判斷當(dāng)前行是否大于分頁符所在行“=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64),1”此句利用MATCH之模糊查找功能將當(dāng)前行號與分頁符下行（分頁符下一行是一個單元N行的一維數(shù)組，文檔有幾頁則有幾行，本實例文檔有三頁，請看公式求值之計算圖示）做比較，此處省略MATCH第三參數(shù)，即查找小于等于目標值，如果目標值大于當(dāng)前

41、行號，則MATCH返回錯誤值。那么此處再用IF(ISNA(),1)加以判斷，即若找不到小于等于當(dāng)前行號的值則顯示1，表示當(dāng)前行處于第一頁。取得當(dāng)前行所在頁MATCH(ROW(),GET.DOCUMENT(64)+1如果前一個MATCH返回FALSE，則取IF函數(shù)第三參數(shù)值即MATCH(ROW(),GET.DOCUMENT(64)+1此參數(shù)再用MATCH在GET.DOCUMENT(64)產(chǎn)生的數(shù)組中查找當(dāng)小于等于前行號的數(shù)值，若數(shù)組中第N個值小于等于當(dāng)前行號，則當(dāng)前行在N+1頁。取得總頁=GET.DOCUMENT(50)+RAND()*0GET.DOCUMENT(50)即求當(dāng)前設(shè)置下欲打印的總頁

42、數(shù)，其中包括注釋，如果文件為圖表，值為1RAND（）*0作用是當(dāng)文件分頁數(shù)改變時，本公式結(jié)果根隨變化，起公式結(jié)果刷新作用。獲取“橫向當(dāng)前頁”橫向當(dāng)前頁與縱向當(dāng)前頁原理相同，改ROW（）為COLUMN（），并將GET.DOCUMENT參數(shù)改為65即可若你的工作表只有縱向分頁或者橫向分頁，那么現(xiàn)在就可以使用前面的公式定義的名稱獲取當(dāng)前頁及總頁了；但如果分頁方式為橫向多頁縱向也多頁呢？則在將以上“橫向當(dāng)前頁”與“縱向當(dāng)前頁”無縫接合方可使用，否則將返回錯誤結(jié)果。最后生成“無拘無束的頁眉”（或者改稱文件分頁）=第&IF(橫向當(dāng)前頁=1,縱向當(dāng)前頁,橫向當(dāng)前頁+縱向當(dāng)前頁)&頁/共&總頁&頁公式解說完畢

43、！各位可以用不同的文字定義名稱在各自的工作表中試用了。打印表頭在Excel中如何實現(xiàn)一個表頭打印在多頁上？請選擇文件-頁面設(shè)置-工作表-打印標題-頂端標題行，然后選擇你要打印的行。打印表尾，通過Excel直接提供的功能應(yīng)該是無法實現(xiàn)的，需要用vba編制才行。Excel打印中如何不顯示錯誤值符號在“頁面設(shè)置”“工作表”“錯誤單元格打印為”中，將“顯示值”改為“空白”即可。對于一些不可打印的字符的處理對于一些不可打印的字符（在Excel顯示中類似空格），直接用替換方法不容易去掉?？梢赃@么做：=SUBSTITUTE(CLEAN(A1), ,)用那個函數(shù)可將個位數(shù)前面的零值顯示出來?如果單元格A1的內(nèi)

44、容是5,在A2用那個函數(shù)可將A1的內(nèi)容變?yōu)?5?(Text或value也可,總之個位數(shù)的零也顯示,例:5變05,15則15)可以用TEXT(A2,00)或?qū)卧窀袷阶远x為00如果你要在A3的前面插入100行可以這樣:在名稱框輸入 3:103-回車-ctrl+shift+（大鍵盤）請問如何每隔30行粘貼一新行偶在班上負責(zé)統(tǒng)計企業(yè)進出口業(yè)務(wù)量，領(lǐng)導(dǎo)要求每30家做一合計數(shù)，偶只有每隔30行插入復(fù)制單元格的方法來添加的，很是麻煩，請教各位大蝦有什么快捷的方法呀在最后加一輔助列,輸入=INT(ROW()-1)/31)+1 (假設(shè)一個標題行)然后以該行分類字段匯總.在工作表里有連續(xù)10行數(shù)據(jù), 現(xiàn)在要

45、每行間格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中的填充范圍，超過范圍會出錯！）2：小修改 =IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$Z$500,INT(ROW()/3)+1,COLUMN(),)一個大表每一行下面需要加一行空行，

46、怎么加最方便方法一：增加輔助列，填充數(shù)據(jù)排序完成方法二：增加輔助列，函數(shù)完成=IF(MOD(ROW(),2),INDIRECT(a&ROUNDUP(ROW()/2,0),)一次刪完Excel里面多出很多的空白行1、用分面預(yù)覽看看2、用自動篩選然后刪除3、用自動篩選，選擇一列用非空白，空白行就看不到了，打印也不會打出來。但是實際上還是在的，不算刪除?；蛘哂米詣雍Y選選擇空白將空白行全顯出來一次刪完也可以。4、先插入一列，在這一列中輸入自然數(shù)序列，然后以任一列排序，排序完后刪除數(shù)據(jù)后面的空行，再以剛才輸入的一列排序，排序后刪除剛才插入的一列。每30行為一頁并加上一個標題如何實現(xiàn)每30行為一頁，并加上

47、一個標題，如何實現(xiàn)?？梢悦?0行加一個分頁符，標題就用“打印標題”來設(shè)置。標題文件-頁面設(shè)置-工作表-打印標題-頂端標題行，設(shè)置一下就好了。每頁30行也是在頁面設(shè)置中，設(shè)置上下頁邊距的調(diào)整可以實現(xiàn)，打印預(yù)覽看一下就可以看到是不是30行了，不到30行你可以將行距加寬，進行調(diào)整，以我的經(jīng)驗，加標題的30行/頁大概行距是20，這樣連制表人的空間都留出來了。每頁30行“插入分頁符”；然后每向下移動30行，點菜單“插入分頁符”。如何實現(xiàn)隔行都加上標題項在excel中,每條記錄都要加上標題(隔行都加),如何才能快速實現(xiàn)?(只要打印出來能實現(xiàn)就成)。在E列輸入2 4,然后選中這兩個單元格，拖住右下的點向下拉

48、到底。把第一行標題項復(fù)制，在有數(shù)據(jù)區(qū)域的下部選中與數(shù)據(jù)行數(shù)相同的空行，粘貼。用同樣的方法填上奇數(shù)（如上），按E列排序即可。雙擊格式刷竟也能COPY文本(不是文本格式)步驟：選中“單元格”雙擊格式刷按住Ctrl鍵選擇需復(fù)制的不連續(xù)目標區(qū)域按回車Enter鍵格式刷的作用其實沒變，復(fù)制文本其實只是按Enter的結(jié)果。分解動作分為三步：1.定位在原數(shù)據(jù)上，雙擊格式刷：復(fù)制所有內(nèi)容 2.在目標區(qū)域按格式刷：選擇性粘貼-格式3.按回車：粘貼所有內(nèi)容。你會發(fā)現(xiàn)如果原單元格上有批注或其實Shape對象的話，一樣也復(fù)制了，跟原數(shù)據(jù)按Ctrl+C，選擇區(qū)域.再按Enter這個意思是一樣的。查找+格式刷的妙用通常在

49、數(shù)據(jù)校對時要用到查找，找到之后就用不同格式區(qū)分(如字體為紅色、底紋為黃色等等)，如此重復(fù)。例如：先找到第一個數(shù)據(jù)并將字體改為紅色,然后雙擊格式刷,當(dāng)查找到其它相同數(shù)據(jù)時,再按Ctrl+A，excel就會將新找到的數(shù)據(jù)自動改為紅色與之區(qū)分。樓主能不能做一個動畫演示？具體操作為：Ctrl+F在查找欄輸入要查找的值查找下一個設(shè)置格式（不要退出查找對話框）雙擊格式刷查找全部Ctrl+A關(guān)閉另外，通常的方法是：Ctrl+F在查找欄輸入要查找的值查找全部Ctrl+A，再設(shè)置格式。光標移動在一個Excel工作表中作業(yè)時，雙擊某單元格的四周（上、下、左、右），會迅速移動光標的位置，若是雙擊上方即刻回到單元格所

50、在列的最頂端，雙擊下方則移動到最底端的編輯外，同樣雙擊左右也是到相對應(yīng)的地方，雙擊單元格中間則變?yōu)檩斎霠顟B(tài)。大家可以試試，這樣比移動工作表中的下拉圖標快捷。最后一行為文本=offset($1,MATCH(CHAR(65535),b:b)-1,)最后一行為數(shù)字=offset($1,MATCH(9.9999E+307,b:b)-1,)或者：=lookup(2,1/(b1:b1000),b1:b1000)如何在EXCEL中快速定位最后一行數(shù)據(jù)如果“定位”就是選中的意思，可按CTRL+END鍵實現(xiàn)。CTRL+ 雙擊選取單元格之下框線用SUN函數(shù)快速求和如何用sum函數(shù)快速求和操作：將光標移到欲要求和的

51、列或行，直按Alt+=，最后按一下“enter鍵就可以。這樣我們不用輸入?yún)?shù)就可以快速求和。在Excel中快速查看所有工作表公式只需一次簡單的鍵盤點擊，即可可以顯示出工作表中的所有公式，包括Excel用來存放日期的序列值。 要想在顯示單元格值或單元格公式之間來回切換，只需按下CTRL+（位于TAB鍵上方）?？焖贀Q行在Excel單元格中輸入數(shù)值后，按下Alt鍵不松開，再按下Enter鍵，即可快速換行。讓文本換行每次在Excel單元格中輸入一個值，再按下Enter鍵，活動單元格均默認下移一個單元格，非常不方便。不過，這時，可以選擇“工具”“選項”“編輯”，然后取消“按Enter鍵移動活動單元格標識

52、框”復(fù)選框即可。在Excel中行列快速轉(zhuǎn)換如果需要要將Excel按行（列）排列的數(shù)據(jù)，轉(zhuǎn)換為按列（行）排列，可以通過“選擇性粘貼”來實現(xiàn)。 選中需要轉(zhuǎn)換的數(shù)據(jù)區(qū)域，執(zhí)行一下“復(fù)制”操作；選中保存數(shù)據(jù)的第一個單元格，執(zhí)行“編輯選擇性粘貼”命令，打開“選擇性粘貼”對話框，選中其中的“轉(zhuǎn)置”選項，確定返回即可。 將原有列中的內(nèi)容倒置過來1524334251B1 =OFFSET(A$1,COUNTA(A:A)-ROW(A1),)空白行的填充各位，我一些同事喜歡在表格內(nèi)使用合并單元格，而我的進行公式運算時需把單元格打散，但就出現(xiàn)了許多空白格，現(xiàn)在我想把空白格用臨近的非空白填充。 EX：A1格為“張三”，

53、A8為“李四”，A21為“王五”之類，現(xiàn)在我要把A2到A7填為張三，A9到A20為李四，A22之后為王五。在B1輸入=IF(A1,A1,IF(ROW()1,INDIRECT(b&ROW()-1)，向下復(fù)制到合適位置。然后用選擇性粘貼功能替換到A列中去。怎么設(shè)置自動保存在“工具”菜單上，單擊“選項”，再單擊“保存”選項卡。選中“自動保存時間間隔”復(fù)選框。在“分鐘”框中，指定希望 Microsoft Office 程序保存文件的頻率。單元格前面自動加了等號我的單元格怎么輸入時間后前面自動加了等號,然后2005年就變成了1905年了呢?工具-選項-1-2-3幫助-轉(zhuǎn)換 lotus 123 公式有無打

54、勾？去掉加蓋公章我們?nèi)粘Ｉ纤?、下發(fā)的報表材料、通知等都要加蓋公章，如果把這項工作交給Excel或Word來完成，我們的工作就輕松多了。第一步：制作公章圖案首先我們要做出一個公章的圖案，最簡單的辦法是把公章圖案掃描到電腦中，然后處理成透明的GIF圖像。我們也可以直接用Excel來制作：把繪圖工具打開，選中“橢圓”工具，在按下“Shift”鍵的同時拖開鼠標，就可以得到一個正圓了。雙擊這個正圓打開“設(shè)置自選圖形格式”對話框，在“顏色與線條”標簽中，填充顏色選“無填充顏色”，線條顏色設(shè)為紅色，選3磅粗的單線形（圖）。公章的文字用藝術(shù)字來制作，填充顏色和線條顏色都用紅色，并設(shè)成無陰影產(chǎn)?；⌒挝淖趾退轿?/p>

55、字要分開來做，在做弧形文字時，把藝術(shù)字拖到圓形的上方，在藝術(shù)字工具中選“藝術(shù)字形狀細上彎弧”，按住黃色的四方塊往下拉，再作適當(dāng)?shù)恼{(diào)整，就可以做出公章里的圓弧形的文字了。公章中間還有一個紅五星，用“自選圖形”的星形就可以做出來了，填充顏色和線條顏色用紅色。最后，按住“Shift”鍵把組成公章的文字、圖形全部選上，執(zhí)行右鍵菜單中的“組合”命令，一個公章就做好了。如何把做好的公章保存出來？這里有一方法：把工作表另存為Web頁，然后到保存目錄中找到*.files的文件夾，里面有一個GIF圖片，這就是剛才做好的公章圖案了，它的背景是透明的，我們把它改名為gongzhang.gif保存下來即可。第二步：添

56、加“蓋章”按鈕接下來我們給Excel添加一個蓋章按鈕，當(dāng)一個工作表做好后，點擊這個蓋章按鈕，就可以為我們蓋上公章了。先把公章圖形復(fù)制出來（用來粘貼作為按鈕的圖標），然后打開“工具自定義”對話框，選中“命令”標簽，在“類別”欄中找到“宏”，在右邊的“命令”欄里就會出現(xiàn)一項“自定義按鈕”。用鼠標把這個笑臉圖標拖出到菜單欄或工具欄上放下，在笑臉圖標上擊右鍵，在彈出的菜單中把“命名”處的文字改為“加蓋公章”。接下來點擊“粘貼按鈕圖標”這個命令，就可以用剛和復(fù)制的公章圖形來代替笑臉圖標了。把鼠標移下來選中“分配超級鏈接插入圖片”，然后在“請鍵入文件名稱或Web頁名稱”欄里輸入公章圖片gongzhang.

57、gif的文件名及路徑，然后按“確定”返回。好了，看到“加蓋公章”這個按鈕了吧，點擊一下看看，呵呵，頁面上就蓋上一個鮮紅的公章了，用鼠標可以把它拖到任意的地方。在Word文檔中加蓋公章的方法與此大同小異，大家可以自己試一試。查找+格式刷的妙用通常在數(shù)據(jù)校對時要用到查找，找到之后就用不同格式區(qū)分(如字體為紅色、底紋為黃色等等)，如此重復(fù)。例如：先找到第一個數(shù)據(jù)并將字體改為紅色,然后雙擊格式刷,當(dāng)查找到其它相同數(shù)據(jù)時,再按Ctrl+A，excel就會將新找到的數(shù)據(jù)自動改為紅色與之區(qū)分。具體操作為：Ctrl+F在查找欄輸入要查找的值查找下一個設(shè)置格式（不要退出查找對話框）雙擊格式刷查找全部Ctrl+A

58、關(guān)閉另外，通常的方法是：Ctrl+F在查找欄輸入要查找的值查找全部Ctrl+A，再設(shè)置格式。但再找下個值時，又要重新設(shè)置格式，如果要找的值很多的話就顯得不是那么方便了。當(dāng)然按照自己的習(xí)慣做最好的。如果用格式刷+查找功能呢,就可以找到A并把A設(shè)成紅色,再雙擊格式刷,然后再找B、找C、找Dexcel就會把找到的B、C、D自動的設(shè)為紅色以之區(qū)分，而不用找一個設(shè)一次字體了。Excel中鼠標雙擊妙用1、雙擊單元格，就可以編輯單元格的內(nèi)容（對應(yīng)用快捷鍵F2）。 2、在行/列邊緣雙擊鼠標，則可以得到此列的最適合的行高/列寬。 3、雙擊工作表標簽，可以直接重命名工作表的名稱。 4、在填充的時候，選定單元格再移

59、動到這個區(qū)域的右下角，這時鼠標會變成細十字。 (1)當(dāng)選擇區(qū)域當(dāng)下方的單元格有內(nèi)容時，雙擊會自動填充下方有數(shù)據(jù)的區(qū)域。 (2)當(dāng)選擇區(qū)域當(dāng)下方的單元格為空時而左邊有數(shù)據(jù)時，雙擊會自動填充到與左邊有數(shù)據(jù)的區(qū)域齊。 (3)當(dāng)選擇區(qū)域當(dāng)下方與左邊的單元格為空時而右邊有數(shù)據(jù)時，雙擊會自動填充到與右邊有數(shù)據(jù)的區(qū)域齊。 5、將鼠標移動到選定單元格的邊上，這時鼠標會變成帶箭頭的十字。 (1)這時雙擊，可以移動到數(shù)據(jù)區(qū)域的邊緣，相當(dāng)于快捷鍵Ctrl+方向鍵。 (2)如果按住Shift再雙擊，可以快速選擇數(shù)據(jù)，相當(dāng)于快捷鍵Shift+Ctrl+方向鍵。 6、雙擊工具欄的空白處，就可以調(diào)出自定義工具欄的對話框。

60、7、雙擊左上角工具欄中工作簿的圖標，可以關(guān)閉當(dāng)前工作簿；雙擊標題欄中的Excel圖標，可以關(guān)閉Excel。 8、使用格式刷時，用雙擊而不是單擊就可以多次使用，再單擊一次格式刷結(jié)束。在使用繪圖工具欄時，如果雙擊線、矩形、圓等圖形時也可以連續(xù)繪圖。9、雙擊拆分窗格的分割條(上下滾動條的上方，左右滾動條的右邊，沒試過拆分窗口的朋友可以先從窗口/拆分里體驗一下)，可以按當(dāng)前單元格上下左右拆分；拆分后再雙擊分割條的任意部分可以恢復(fù)。 10.在菜單上雙擊，可將菜單中所有的菜單項(包括不常用的菜單項)全部展開。11.如果工具條浮動在工作表區(qū)，在工具條的標題欄雙擊，則該工具條返回工作表區(qū)上部或下部位置。在選中