常用函數公式及技巧搜集

【身份證信息提取】從身份證號碼中提取出生年月日=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。（最簡單的公式，把單元格設置為日期格式）=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,"男","女")從身份證號碼中進行年齡判斷=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日為基準日,按按身份證計算年齡(周歲)的公式=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)))【年齡和工齡計算】根據出生年月計算年齡＝DATEDIF(A1,TODAY(),"y")＝DATEDIF(A1,TODAY(),"y")&"周歲"＝DATEDIF(A1,NOW(),"y")根據出生年月推算生肖中國人有12生肖，屬什麼可以推算出來。即用誕生年份除以12，再用除不盡的餘數對照如下：0→猴，1→雞，2→狗，3→豬，4→鼠，5→牛，6→虎，7→兔，8→龍，9→蛇，10→馬，11→羊例如：XXX出生於1921年，即用1921年除以12，商得數為160，餘數為1，對照上面得知餘數1對應生肖是雞，XXX就屬雞。=MID("猴雞狗豬鼠牛虎兔龍蛇馬羊",MOD(YEAR(A2),12)+1,1) （2007）如何求出一個人到某指定日期的周歲？=DATEDIF(起始日期,結束日期,"Y")計算距離退休年齡的公式=IF(E2="","",IF(E2>=V2,"已經退休","距離退休還有"&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")求兩日期間的年數=DATEDIF(C6,C8,"ym")求兩日期間除去整年數剩餘的月數=DATEDIF(C6,C8,"m")求兩日期間的總月數如果只需要算出周年的話，可以用=datedif("1978-8","2006-5","Y")年齡及工齡計算有出生年月如何求年齡？有工作時間如何求工齡？（求出的結果為多少年另幾個月，如：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 函數，也可以不用 DATEDIF 函數,如 [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）這樣的日期格式才能實現！你不妨把「.」替換成「-」，不就行了嗎，再說後者是日期的一種標準格式，=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,".","-")),2)&"年"&MOD(MONTH(NOW()-SUBSTITUTE(A1,".","-"))-1,12)&"個月","")【時間和日期應用】自動顯示當前日期公式=YEAR(NOW()) 當前年=MONTH(NOW()) 當前月=DAY((NOW())) 當前日如何在單元格中自動填入當前日期Ctrl+;如何判斷某日是否星期天=WEEKDAY(A2,2)=TEXT(A1,"aaaa")=MOD(A1,7)<2某個日期是星期幾比如2007年2月9日，在一單元格內顯示星期幾。=TEXT(A1,"aaa") （五）=TEXT(A1,"aaaa") （星期五）=TEXT(A1,"ddd") （Fri）=TEXT(A1,"dddd") （Friday）什麼函數可以顯示當前星期如：星期二 10:41:56=TEXT(NOW(),"aaaahh:mm:ss")求本月天數設A1為2006-8-4求本月天數A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))也有更簡便的公式：=DAY(EOMONTH(NOW(),0)) 需加載分析工具箱。當前月天數： =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-DATE(YEAR(TODAY()),MONTH(TODAY()),1)用公式算出除去當月星期六、星期日以外的天數=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)>1))顯示昨天的日期每天需要單元格內顯示昨天的日期，但雙休日除外。例如，今天是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)關於取日期怎麼設個公式使A1在年月日向後推5年,變成2011-7-15=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))=EDATE(A1,12*5)如何對日期進行上、中、下旬區分=LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"})如何獲取一個月的最大天數"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1為"2001-03-01日期格式轉換公式將 「01/12/2005」 轉換成「20050112」格式＝RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)＝YEAR($A2)&TEXT(MONTH($A2),"00")&TEXT(DAY($A2),"00") 該公式不用設置數據有效性,但要設置儲存格格式。也可以用下列兩方法：1、先轉換成文本, 然後再用字元處理函數。2、[數據]-[分列] [日期]-[MDY]將「2005年9月」轉換成「200509」格式先用公式：=text(a1,"yyyymm")+0 然後將單元格格式為常規。將「2005-8-6」格式轉換為「20050806」格式用公式：=TEXT(A1,"YYYYMMDD")反之，將20050806轉為日期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")，把單元格設置為日期格式 顯示：2005-8-6＝TEXT(20050806,"0000-00-00")*1，單元格設置日期型 顯示：2005-8-6＝VALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2)) 顯示：2005-8-6將「20060501」轉換為「2006-05-01」格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))將「199306」轉換為「1993-6」公式1：=LEFT(A3,4)&"-"&RIGHT(A3,2)*1公式2：=--TEXT(A3*100+1,"#-00-00") 公式2需要設置單元格格式，自定義：e-m公式3：=TEXT(TEXT(A3&"01","0000-00-00"),"e-m")把198405轉換成1984.05一、查找—1984，替換—1984.二、如果全部是年月的話，我個人建議，1、採取輔助＝mid(xxxxxx,1,4) & "." & right(xxxxxx,2)2、選中這列，用數據中的分列。然後……………三、單元格格式／數字／自定義，類型下面輸入：####"."##將文本「2004.01.02」 轉換為日期格式：2004-1-2=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))將2005-8-6轉換為2005年8月6日格式=TEXT(A1,"yyyy""年""m""月""d""日"";@")象22怎樣轉換成22日？轉成當年當月的日子公式為：=date(year(now()),month(now()),22)將「2006年5月」轉換成「2006年05月」公式為：=TEXT(A8,"yyyy""年""mm""月"";@")也可以這樣處理：選中單元格，設置單元格公式－數字－自定義，將yyyy「年」m「月」改為：yyyy「年」mm「月」，即可。但這方法列印出來顯示為：2006/5/將「1968年6月12日」轉換為「1968/6/12」格式=YEAR(A1)&"/"&MONTH(A1)&"/"&DAY(A1) 顯示：1968/6/12=TEXT(A1,"yyyy/mm/dd") 顯示：1968/06/12將「1968年6月12日」轉換為「1968-6-12」格式=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1) 顯示：1968-6-12=TEXT(A1,"yyyy-mm-dd") 顯示：1968-06-12將1993-12-28的日期格式轉換成1993年12月=CONCATENATE(YEAR(A1),"年",MONTH(A1),"月")=YEAR(A1)&"年"&MONTH(A1)&"月"也可以自定義格式 [$-404]e"年"m"月"將「1978-5-2」包含年月日的日期轉換成「197805」只有年月的格式＝year(A1)&text(month(A1),"00")要將「99.08.15」 格式轉換成「1999.08.15」如何做選中列，數據菜單中選分列，分列過程中「格式」選「日期YMD」，結束。要保持2005/8/6格式當輸入2005/8/6後系統自動變成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"))將「二○○三年十二月二十五日」轉為「2003-12-25」格式，1、可以用數組公式將中文日期轉化為日期系列數｛=14610+MATCH(SUBSTITUTE(A3,"元","一"),TEXT(ROW($14611:$55153),"[DBNum1]yyyy年m月d日"),0)｝該公式速度較慢。2、改進後的公式，速度要快的多：｛=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)))｝要設置為1900年的日期格式。日期格式轉換如A列是月份數為8，B列是日期數為18，如何在C列顯示「8月18日」=A1&"月"&B1&"日"反之，要將C列的「8月18日」 直接分別到D、E列，顯示月份和日期，月數份=LEFT(C5,FIND("月",C5)-1)日期數=MID(C5,FIND("月",C5)+1,FIND("日",C5)-FIND("月",C5)-1)也可分別用公式：=month(--c5)=day(--c5)日期格式轉換問題輸入的日期是:04-07-26. 與另一格的"001"合併,合併出來是:040726001.=TEXT(A1,"YYMMDD")&"001"要想自動取得「編製日期：XXXX年X月X日」可在該單元格輸入 ="編製日期："&TEXT(TODAY(),"yyyy年m月d日")【排名及排序篩選】一個具有11項匯總方式的函數SUBTOTAL=SUBTOTAL(9,$B$2:B2)在數據篩選求和上有意想不到的功能，11項功能為：1、求平均數，2、求計數，3、求計數值（自動篩選序列）4、求最大值，5、求最小值，6、求乘積，7、求總體標準偏差，8、求標準偏差、9、求和，10、求方差，11、求總體方差。自動排序=SUBTOTAL(3,$B$2:B2)*1=IF(A2<>A1,1,N(C1)+1)按奇偶數排序我想請教怎樣按奇數順序然後再按偶數順序排序=IF(MOD(A1,2),0,1)=IF(ROW()>50,(ROW()*2)-100,(ROW()*2)-1)=ROW()*2-1-(ROW()>50)*99自動生成序號比如在第二列中輸入內容回車後第一列的下一行自動生成序列號。=IF(B2<>"",A2+1,"")如何自動標示A欄中的數字大小排序？=RANK(A1,$A$1:$A$5)=RANK(A1,A:A)如何設置自動排序A列自動變成從小到大排列B=SMALL(A$2:A$28,ROW(1:1))A列自動變成從大到小排列B=LARGE(A$2:A$28,ROW(1:1))想得到數據的出現總數嗎（{1,2,2,3,4,4,5} 數據的出現總數為5）？解答：不需要插列,不需要很多的函數就行了. =RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1按字元數量排序製作歌曲清單時，習慣按字元數量來排列分類，但是EXCEL並不能直接按字數排序。需要先計算出每首歌曲的字數，然後再進行排序。如A、B列分別為「歌手」和「歌名」，在C1輸入「字數」，在C2輸入公式：＝LEN(B2) 下拖，單擊C2，單擊工具欄上的「升序排列」即可，刪除C列。排序字母與數字的混合內容日常使用中，表格經常會有包含字母和數字混合的數據，對此類數據排序時，通常是先比較字母的大小，再比較數字的大小，但EXCEL是按照對字元進行逐位比較來排序的，如下表：A7排在第5位，而不是第1位。排序結果無法令人滿意。A1A1222A293A3174A435A76B207B38C1449C510C33AB1A7A0072A29A0293A43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144如果希望EXCEL改變排序的規則，需要將數據做一些改變。在B1中輸入公式：LEFT(A1,1)& RIGHT("000"& RIGHT(A1,LEN(A1)-1),3) 下拖單擊B2，單擊工具欄上的「升序排列」即可。隨機排序如A、B列分別為「歌手」和「歌名」，在C1輸入「次序」，在C2輸入公式：＝RAND（），下拖，單擊C2，單擊工具欄上的「降序排列」即可對歌曲清單進行隨機排序。排序的問題我想要這樣的排序: 2001-20032004-20062007-20092010-2012;其實不是數據排序，應該是數據填充。輸入公式=LEFT(E3,4)+3&"-"&RIGHT(E3,4)+3 即可。怎樣才能讓數列自動加數怎樣做才能讓數列自動加數A A0001B B0001A A0002C C0001A A0003B B0002C C0002公式為=A1&"000"&COUNTIF(A$1:A1,A1)向下拖=TEXT(COUNTIF(A$1:A1,A1),"!"&A1&"0000")否則數字超過9就錯誤了。一個排序問題一個電子表格，格式是101、102... 999,10101、10102... 99901,1010101,1020201... 9990101,請問如何將它排列成101,10101,1010101,102,10201,1020101,... 999,99901,9990101 的形式。我在數字前加了個字母,比如"d"&"數字",然後用排序就可以把它們按你的需求排列了.最後再把字母"d"去掉。數字的自動排序,插入後不變?1 趙一 總經理2 趙二 副經理3 趙三 副經理4 趙四 技術員5 趙五6 趙六 員工如上的一個表,如何實現當我把趙六這一整行(第6行)插入到上面的表中時,A列的序列號不變?最後的效果如下:1 趙一 總經理2 趙二 副經理3 趙六 員工4 趙三 副經理5 趙四 技術員6 趙五A1單元格輸入公式 =row(),往下拉,然後再插入。=SUBTOTAL(3,$B$2:$B2)在A1中輸入公式：「=if(b1="","",counta($b$1:b1)」後下拉複製至A列各行即可（「」不必輸入）根據規律的重複的姓名列產生自動序號姓名 序號張三 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)))排名的函數用排名函數來對成績進行排名，用起來非常地方便。=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)求名次排名統計成績時遇到一個分別求班級和年級總分名次排名的問題，不曉得應該運用什麼公式來實現。班級名次：=SUMPRODUCT((BJ=A2)*(ZF>E2))+1年級名次：=RANK(E2,ZF) 公式下拖。排名次根據總分值大小，只將姓名排序後, 降序結果=INDEX(A$2:A$6,RANK(D2,D$2:D$6))根據總分值大小，只將姓名排序後, 升序=INDEX(A$2:A$6,RANK(D2,D$2:D$6,1))根據分數進行普通排名=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))對於普通排名分數相同時，按順序進行不重複排名=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)))依分數比高低名次成績排名=RANK($E3,$E$3:$E$22) 內建方式排名=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不重複排名=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))) 不重複排名=SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100+$C$3:$C$12/10000)>=(E3+B3/100+C3/10000))) 不重複排名=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（如果數據列中數值有相同）=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)))數組公式｛=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),)｝根據雙列成績進行共同排名=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成績排名序號姓名語文數學英語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))能否用一個公式直接找出所用考生中語文成績中按與考人數的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、對英語進行排名，缺考不計算在內。2、對英語進行排名，缺考計算在內。英語英語排名429623721485485721544429缺考缺考458467缺考不計算在內b2=IF(A2="缺考","",RANK(A2,$A$2:$A$13)) 然後按照B列排序缺考計算在內=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))數據排名（隔幾行排名）=IF(A2="","",RANK(A2,$A$2:$A$11,0))如果隔幾行排名，如下表，第五行、第九行和第十二行不參與排名。單位數據排名A18A57A66小計12B84B93B75小計24C181C112小計29=IF(A2="小計","",RANK(B2,(B$2:B$4,B$6:B$8,B$10:B$11))) 下拉根據分數進行倒排名=RANK($E3,$E$3:$E$22,1)=RANK(K60,$K$60:$K$83,1)=COUNTIF($K$60:$K$83,"<"&K60)+1倒數排名函數是什麼1為正排序，0為逆排序。倒數排名=RANK(A2,$A$2:$A$5,0)正數排名=RANK(A2,$A$2:$A$5,1)如何實現每日各車間產量的排名=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,,,))分數相同時按照一科的分數進行排名｛=MATCH(K308*100+D308,LARGE($K$308:$K$331*100+$D$308:$D$331,ROW($K$308:$K$331)-307),)｝篩選後自動產生序列號並匯總自動產生序列號：在A1輸入以下公式，往下拖。=SUBTOTAL(3,$B$2:B2)*1自動匯總，用以下公式：=SUBTOTAL(9,$B$2:B2)說明：匯總時，不要在「全選」狀態下進行，先「篩選」出某一單位，自動求和∑。然後再恢復到「全選」或者選擇任何單位，就能自動匯總了（在「篩選」出某一單位進行求和時，一般表格會自動產生以上匯總公式）。其它：如同時要在其它單元格顯示人數，在「全選」狀態下，選定單元格，點「fx」（用「sum」函數）再點擊序列號最末尾數，即可。如何篩選奇數行公式=MOD(A1,2)=1函數篩選姓名如何把兩列中只要包含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)))如何實現快速定位（篩選出不重複值）=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()))&""（數組公式）如何請在N列中列出A1:L9中每列都存在的數值｛=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如果你是已經輸入了其它信息，僅僅為快速輸入編碼的話。用篩選可以實現吧。先以「男」為關鍵字進行排序，然後在第一個男的編碼輸入10001，下拉複製到最後一單即可。同理再以「女」排序。完成目標。用公式：=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖【文本與頁面設置】EXCEL中如何刪除*號在錄入帳號是錄入了*號，如何刪除。可以用函數 SUBSTITUTE(a1,"*","")查找~*，替換為空。將字元串中的星號「*」替換為其它字元在查找欄輸入~*替換為「-」即可。去空格函數如何刪去單元格中的空格，如姓名前，中，後的空格，即單元格中是兩個字的人名中間有一個空格，想刪去有何方法。如：中國，改為：中國。1、用公式：=SUBSTITUTE(A2," ","") 註：第一對雙引號中有一空格。而第二個「」中是無空格的。2、利用查找－替換，一次性全部解決。「編輯」－「替換」（或Ctrl+H），在「查找」欄內輸入一空格，「替換」什麼也不輸入（空白）。然後「全部替換」即可。3、有一個專門刪除空格的函數: TRIM()在EXCEL編輯欄里，不管輸中文還是英文只能輸一個位元組的空格，但如果字與字中間是兩個位元組的空格，那麼TRIM（）就不起作用了，它就不認為是一個空格，而是一個漢字，怎麼去「TRIM」也沒用。如：單元格A1中有「中　　心　　是」，如果用TRIM則變成「中　心　是」， 想將空格全去掉，只能用SUBSTITUDE（）函數，多少空格都能去掉。如何去掉字元和單元格里的空格8900079501 8900079501~1900078801 1900078802~=SUBSTITUTE(B2,"~","")怎樣快速去除表中不同行和列的空格編輯-定位-定位條件-空值,可選中所有空單元格, 再刪除。如何禁止輸入空格在Excel中如何通過編輯「有效數據」來禁止錄入空格？煩請大俠們費心解答。解答：有效性公式。=COUNTIF(A1,"* *")=0(註：COUNTIF(A1,"* *") 在單元格有空格時結果為1,沒有空格時結果為0如希望第一位不能輸入空格:countif(a1," *")=0如希望最後一位不能輸入空格:countif(a1,"* ")=0)代替單元格中字元串單元格編號，開始位數，從開始位數算起第幾位數，要用於代替的的字元串。windows2000變成windows2K=REPLACE(B2,8,3,"K")單元格編號，要代替掉的字元，要用作代替的字元，第幾個。代替單元格B391中的全部TT，改為UU。EETTCCTTFF變成EEUUCCUUFF=SUBSTITUTE(B394,"TT","UU")只代替單元格B391中的第一次出現的TT，改為UU。EETTCCTTFF變成EEUUCCTTFF=SUBSTITUTE(B397,"TT","UU",1)把單元格中的數字轉變成為特定的字元格式函數中的第二個參數的雙引號一定不能是中文格式的（不能用任意中文輸入法輸入的雙引號。）實例： 20000 目的： 變成帶有美元符號的字元10000 變成帶有人民幣符號的字元151581 變成帶有歐元符號的字元1451451 變成中文繁體的字元15748415 變成中文簡體的字元操作步驟： =TEXT(B72,"$0.00") 結果： $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) 複製到其他單元格將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))重複四次填充=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")多行數據排成一列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)))｝將單元格一列分為多列如果有一列資料需要分為多列，只要先將此列選中，然後再選擇「數據」→「分列」，此時會出現一個對話框，選「固定寬度」或「分隔符號」。如為前者則下一步後只要用滑鼠輕點資料即可以按任意寬度進行分割了，如為後者則只要有明顯的分隔符號即可，下一步後就可以自定義剛分的列的格式了，定好後就算完成了。步驟：1、先確定1列的最適合的列寬,再將其寬度乘以分成列數,即分列前的列寬=最適合的列寬×需分成的列數.2、編輯—填充—內容重排。3、數據—分列。首寫字母大寫把單元格編號中的單詞首寫字母變成大寫字母，其餘字母變成小寫。如china - China=PROPER(B160)把單元格編號中的小寫字母變成大寫字母lafayette148 LAFAYETTE148=UPPER(B1)=LOWER(B1) （大寫字母變成小寫字母公式）讓姓名左右對齊姓名用字，有的是三個漢字，有的是兩個漢字，列印出來很不美觀，要使姓名用字是兩個字的與三個字的左右對齊也有兩種方法:方法一:格式設置法。選中我們已經刪除完空格的姓名單元格，單擊「格式→單元格」在打開的「單元格格式」對話框中的水平對齊方式中選擇「分散對齊」選項，確定退出後即可使學生姓名用字左右對齊。方法二:函數公式法。利用Excel中的「IF」、「LEN」、「MID」三種函數組合可使姓名用字左右對齊。具體示例為:在C3單元格中輸入公式:「=IF(LEN(B2)>=3,B2,(MID(B2,1,1)&&" "&&MID(B2,2,1)))」，確定後利用填充柄將該公式進行複製即可。數字居中而小數點又對齊可在小數點的任一邊替無效的零加入空間，以便當格式設定為固定寬字型，小數點可以對齊。格式－單元格－數字－自定義－???.???-確定請問：小數點後的「0」還有辦法顯示嗎？比如：2.012.001格式－單元格－數字－自定義－???.0?-確定計算指定單元格編號組中非空單元格的數量計算B252到B262之間的非空單元格的數量。=COUNTA(B252:B262)比較兩個單元格內容是否一致74P125148 74P125148比較單元格B53與C53中的內容是否一致。假如內容一致，那麼返回值為TRUE，不一致的話，返回值為FALSE。=EXACT(B53,C53)結果：TRUE怎麼樣設置才能讓這一列的每個單元格只能輸入12位怎麼樣設置才能讓某一列或某一行的每個單元格只能輸入12位,(阿拉伯數字和26個英文字母在內,沒有中文。)選中A列，設置數據有效性：自定義>公式：「=LEN(A1)=12」如何讓工作表奇數行背景是紅色偶數行背景是藍色用條件格式=ROW()/2=INT(ROW()/2) 設定顏色條件格式: 公式為 =MOD(ROW(),2)=0計算特定的一組單元格中，滿足條件的單元格的個數仍以上題為例，計算三個人在B307到B313中各自所佔的單元格數。李六的： =COUNTIF(B307:B313,B323)王武的： =COUNTIF(B307:B313,C323)陳豐的： =COUNTIF(B307:B313,D323)姓名： 李六 王武 陳豐結果： 3 2 2把文本格式的數字轉換成真正的數字=VALUE(B1)設置頁碼如何設置「第×頁，共×頁」頁碼。在頁腳中設置：第&[頁碼]頁，共&[總頁碼]頁 即可Excel表格里如何插入頁碼的?我想把表格中的第1頁的頁碼從第30頁開始編,不知道該如何實現,哪位高手能幫忙?在頁面設置的頁眉頁腳中設置。在插入頁腳中輸入＆[頁碼]+29即可。如何設置頁腳首頁為第5頁Excel頁腳設置頁碼是按順序來的，首頁為第1頁。如何設置首頁為第5頁？在頁腳輸入「第 &[頁碼]+4 頁」，結果本該顯示「第1頁」的就顯示第5頁了。（用於多個工作表全選）頁面設置—頁面—起始頁碼輸入5（用於單個工作表）。表格的頁腳問題是這樣的，我每個表格有4張，總共一個文件裡面有6個表格，相當於總共24頁，我希望它能夠自動打，而且我想設置頁腳為，共24頁，第？頁，怎麼辦？試一試選擇所有的工作表（工作組）然後再設置頁腳，列印的時候也是用工作組列印。把所有工作表選中就可以了然後你再點列印,或者你先瀏覽,再設置也行!按shift依次點表單的標籤。其實,就是在選擇瀏覽或者列印前,先選中你想要的工作表,然後再一個個的瀏覽,就相當於你的操作對所有工作表都已經起了作用似的。請樓主試一試，按以下步驟辦：1．文件→頁面設置→頁眉/頁腳→頁腳（F）,選自己需要的頁腳格式2．文件→列印→整個工作簿。無拘無束的頁眉頁眉和頁腳大家都用過吧？用得最多的莫過於當前第幾頁/總共第幾頁。但你是否想過將「第N頁/總M頁」無拘無束的放置，而不是只能置於頁眉頁腳中？，現教你一法，可以通用。到任何地方均可使用。首先：點CTRL+F3打開定義名稱，再在上面輸入「縱向當前頁」，在下面引用位置處輸入=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1,MATCH(ROW(),GET.DOCUMENT(64))+1)。然後再繼續添加第二個名稱：「橫向當前頁」，在下面引用位置處輸入=IF(ISNA(MATCH(column(),GET.DOCUMENT(65))),1,MATCH(column(),GET.DOCUMENT(65))+1)。再輸入「總頁」；引用位置處輸入：=GET.DOCUMENT(50)+RAND()*0。最後再定義「無拘無束的頁眉」；引用位置：="第"&IF(橫向當前頁=1,縱向當前頁,橫向當前頁+縱向當前頁)&"頁/共"&總頁&"頁"。現在你在工作表任何處輸入=無拘無束的頁眉即可。本公式核心在於GET.DOCUMENT,這是4.0宏函數,OFFICE 97及以前版專用,新版OFFICE中仍兼容,但只限定義名稱中使用。在幫助中說(64和65為其參數):64 行數的數組,相應於手動或自動生成頁中斷下面的行。65 列數的數組。相應於手動或自動生成的頁中斷右邊的列。"本公式中取64,用於計算當前行與分頁符之前後關係.GET.DOCUMENT(64)即返回分頁符所在行下一行之行號（亦即第二頁第一行）。判斷當前行是否大於分頁符所在行「=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1」此句利用MATCH之模糊查找功能將當前行號與分頁符下行（分頁符下一行是一個單元N行的一維數組，文檔有幾頁則有幾行，本實例文檔有三頁，請看公式求值之計算圖示）做比較，此處省略MATCH第三參數，即查找小於等於目標值，如果目標值大於當前行號，則MATCH返回錯誤值。那麼此處再用IF(ISNA(),1)加以判斷，即若找不到小於等於當前行號的值則顯示1，表示當前行處於第一頁。取得當前行所在頁＝MATCH(ROW(),GET.DOCUMENT(64))+1如果前一個MATCH返回FALSE，則取IF函數第三參數值即MATCH(ROW(),GET.DOCUMENT(64))+1此參數再用MATCH在GET.DOCUMENT(64)產生的數組中查找當小於等於前行號的數值，若數組中第N個值小於等於當前行號，則當前行在N+1頁。取得總頁=GET.DOCUMENT(50)+RAND()*0GET.DOCUMENT(50)即求當前設置下欲列印的總頁數，其中包括注釋，如果文件為圖表，值為1RAND（）*0作用是當文件分頁數改變時，本公式結果根隨變化，起公式結果刷新作用。獲取「橫向當前頁」橫向當前頁與縱向當前頁原理相同，改ROW（）為COLUMN（），並將GET.DOCUMENT參數改為65即可若你的工作表只有縱向分頁或者橫向分頁，那麼現在就可以使用前面的公式定義的名稱獲取當前頁及總頁了；但如果分頁方式為橫向多頁縱向也多頁呢？則在將以上「橫向當前頁」與「縱向當前頁」無縫接合方可使用，否則將返回錯誤結果。最後生成「無拘無束的頁眉」（或者改稱文件分頁）="第"&IF(橫向當前頁=1,縱向當前頁,橫向當前頁+縱向當前頁)&"頁/共"&總頁&"頁"公式解說完畢！各位可以用不同的文字定義名稱在各自的工作表中試用了。列印表頭在Excel中如何實現一個表頭列印在多頁上？請選擇文件-頁面設置-工作表-列印標題-頂端標題行，然後選擇你要列印的行。列印表尾，通過Excel直接提供的功能應該是無法實現的，需要用vba編製才行。Excel列印中如何不顯示錯誤值符號在「頁面設置」－「工作表」－「錯誤單元格列印為」中，將「顯示值」改為「空白」即可。對於一些不可列印的字元的處理對於一些不可列印的字元（在Excel顯示中類似空格），直接用替換方法不容易去掉。可以這麼做：=SUBSTITUTE(CLEAN(A1)," ","")用那個函數可將個位數前面的零值顯示出來?如果單元格A1的內容是5,在A2用那個函數可將A1的內容變為05?(Text或value也可,總之個位數的零也顯示,例:5變05,15則15)可以用＝TEXT(A2,"00")或將單元格格式自定義為00如果你要在A3的前面插入100行可以這樣:在名稱框輸入 3:103-回車-ctrl+shift+"+"（大鍵盤）請問如何每隔30行粘貼一新行偶在班上負責統計企業進出口業務量，領導要求每30家做一合計數，偶只有每隔30行插入複製單元格的方法來添加的，很是麻煩，請教各位大蝦有什麼快捷的方法呀在最後加一輔助列,輸入=INT((ROW()-1)/31)+1 (假設一個標題行)然後以該行分類欄位匯總.在工作表裡有連續10行數據, 現在要每行間格2行解答：1：如sheet1！$A$1:$D$10中有連續10行資料，在sheet2中把sheet1中的數據每行間隔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()),"")一個大表每一行下面需要加一行空行，怎麼加最方便方法一：增加輔助列，填充數據排序完成方法二：增加輔助列，函數完成=IF(MOD(ROW(),2),INDIRECT("a"&ROUNDUP(ROW()/2,0)),"")Excel中插入空白行如果想在某一行上面插入幾行空白行，可以用滑鼠拖動自此行開始選擇相應的行數，然後單擊右鍵，選擇插入。如果在每一行上面均插入一空白行，按住Ctrl鍵，依次單擊要插入新行的行標按鈕，單擊右鍵，選擇插入即可。快速刪除工作表中的空行如果用戶想刪除Excel工作表中的空行，一般的方法是需要將空行都找出來，然後逐行刪除，但這樣做操作量非常大，很不方便。下面提供二種快速刪除工作表中的空行的方法:1、首先打開要刪除空行的工作表，在打開的工作表中單擊「插入→列」命令，從而插入一新的列X，在X列中順序填入整數，然後根據其他任何一列將表中的行排序，使所有空行都集中到表的底部。刪去所有空行中X列的數據，以X列重新排序，然後刪去X列。2、如批量刪除空行，我們可以利用「自動篩選」功能，把空行全部找到，然後一次性刪除。做法：先在表中插入新的一個空行，然後按下Ctrl+A鍵，選擇整個工作表，用滑鼠單擊「數據」菜單，選擇「篩選」項中的「自動篩選」命令。這時在每一列的頂部，都出現一個下拉列表框，在典型列的下拉列表框中選擇「空白」，直到頁面內已看不到數據為止。在所有數據都被選中的情況下，單擊「編輯」菜單，選擇「刪除行」命令，然後按「確定」按鈕。這時所有的空行都已被刪去，再單擊「數據」菜單，選取「篩選」項中的「自動篩選」命令，工作表中的數據就全恢復了。插入一個空行是為了避免刪除第一行數據。如果想只刪除某一列中的空白單元格，而其它列的數據和空白單元格都不受影響，可以先複製此列，把它粘貼到空白工作表上，按上面的方法將空行全部刪掉，然後再將此列複製，粘貼到原工作表的相應位置上。快速刪除空行有時為了刪除Excel工作簿中的空行，你可能會將空行一一找出然後刪除，這樣做非常不方便。你可以利用自動篩選功能來實現，方法是:先在表中插入新的一行(全空)，然後選擇表中所有的行，單擊「數據→篩選→自動篩選」命令，在每一列的頂部，從下拉列表中選擇「空白」。在所有數據都被選中的情況下，單擊「編輯→刪除行」，然後按「確定」，所有的空行將被刪去。 注意:插入一個空行是為了避免刪除第一行數據。一次刪完Excel裡面多出很多的空白行1、用分面預覽看看2、用自動篩選然後刪除3、用自動篩選，選擇一列用非空白，空白行就看不到了，列印也不會打出來。但是實際上還是在的，不算刪除。或者用自動篩選選擇空白將空白行全顯出來一次刪完也可以。4、先插入一列，在這一列中輸入自然數序列，然後以任一列排序，排序完後刪除數據後面的空行，再以剛才輸入的一列排序，排序後刪除剛才插入的一列。每30行為一頁並加上一個標題如何實現每30行為一頁，並加上一個標題，如何實現。可以每30行加一個分頁符，標題就用「列印標題」來設置。1、 標題文件-頁面設置-工作表-列印標題-頂端標題行，設置一下就好了。2、 每頁30行也是在頁面設置中，設置上下頁邊距的調整可以實現，列印預覽看一下就可以看到是不是30行了，不到30行你可以將行距加寬，進行調整，以我的經驗，加標題的30行/頁大概行距是20，這樣連製表人的空間都留出來了。每頁30行－「插入》分頁符」；然後每向下移動30行，點菜單「插入》分頁符」。如何實現隔行都加上標題項在excel中,每條記錄都要加上標題(隔行都加),如何才能快速實現?(只要列印出來能實現就成)。在E列輸入2 4,然後選中這兩個單元格，拖住右下的點向下拉到底。把第一行標題項複製，在有數據區域的下部選中與數據行數相同的空行，粘貼。用同樣的方法填上奇數（如上），按E列排序即可。如何把標籤頁去掉的?工具→選項→視圖→點擊「工作表標籤」去掉（√）勾→確定。恢復時也照此操作工具→選項→視圖→點擊「工作表標籤」顯示（√）勾→確定。去掉默認的表格線(網線)單擊「工具」菜單中的「選項」，再單擊對話框中的「視圖」，找到「網格線」，使之失效(將左邊的「×」去掉)。表格的框線我們很喜歡為表格加上一道框線，不過這道框線又往往叫我們花掉很多時間來重畫，例如在下方多加一列時，Excel並不會把新列加在下方框線之上。又例如將上方的數據拷到最後一列時，下方的框線就會給蓋掉，變成穿了一個洞。我的技巧就是在表格的最後一列留一列空列，並把它的列高定得很小，我就叫這一列「緩衝列」好了。把列高定小一點，除了美觀之外，還可以用作提醒用戶不要把數據打到緩衝列。你可以試試在緩衝列上加列或拷數據到緩衝列之上，框線並不會給弄亂。列標的標識變了通常EXCEL的列標都是用大寫英文字母表示的，我的EXCEL的列標今天都變成了阿拉伯數字表示的了，請教這兩種表示方法有什麼不同，如果想恢復成字母表示的該怎麼辦。這是EXCEL的R1C1樣式。在這裡改回來：工具/選項/常規：不選R1C1樣式。符號的意義單元格自定義格式中"?" 和"#"代表的是什麼意思。「？」 一個字元，字元：可以是文本、也可以是數字；「#」 一個數值字元，數值字元：只能是數字。雙擊格式刷竟也能COPY文本(不是文本格式)步驟：選中「單元格」→雙擊格式刷→按住Ctrl鍵選擇需複製的不連續目標區域→按回車Enter鍵格式刷的作用其實沒變，複製文本其實只是按Enter的結果。分解動作分為三步：1.定位在原數據上，雙擊格式刷：複製所有內容2.在目標區域按格式刷：選擇性粘貼-格式3.按回車：粘貼所有內容。你會發現如果原單元格上有批註或其實Shape對象的話，一樣也複製了，跟原數據按Ctrl+C，選擇區域.再按Enter這個意思是一樣的。查找+格式刷的妙用通常在數據校對時要用到查找，找到之後就用不同格式區分(如字體為紅色、底紋為黃色等等)，如此重複。例如：先找到第一個數據並將字體改為紅色,然後雙擊格式刷,當查找到其它相同數據時,再按Ctrl+A，excel就會將新找到的數據自動改為紅色與之區分。樓主能不能做一個動畫演示？具體操作為：Ctrl+F→在查找欄輸入要查找的值→查找下一個→設置格式（不要退出查找對話框）→雙擊格式刷→查找全部→Ctrl+A→關閉另外，通常的方法是：Ctrl+F→在查找欄輸入要查找的值→查找全部→Ctrl+A，再設置格式。游標移動在一個Excel工作表中作業時，雙擊某單元格的四周（上、下、左、右），會迅速移動游標的位置，若是雙擊上方即刻回到單元格所在列的最頂端，雙擊下方則移動到最底端的編輯外，同樣雙擊左右也是到相對應的地方，雙擊單元格中間則變為輸入狀態。大家可以試試，這樣比移動工作表中的下拉圖標快捷。最後一行為文本=offset($1,MATCH(CHAR(65535),b:b)-1,)最後一行為數字=offset($1,MATCH(9.9999E+307,b:b)-1,)或者：=lookup(2,1/(b1:b1000<>""),b1:b1000)如何在EXCEL中快速定位最後一行數據如果「定位」就是選中的意思，可按CTRL+END鍵實現。CTRL+↓ 雙擊選取單元格之下框線用SUN函數快速求和如何用sum函數快速求和操作：將游標移到欲要求和的列或行，直按"Alt+"="，最後按一下「enter"鍵就可以。這樣我們不用輸入參數就可以快速求和。在Excel中快速查看所有工作表公式只需一次簡單的鍵盤點擊，即可可以顯示出工作表中的所有公式，包括Excel用來存放日期的序列值。要想在顯示單元格值或單元格公式之間來回切換，只需按下CTRL+`（位於TAB鍵上方）。在Excel中設置行間距想必大家都知道Excel中是沒有行間距設置功能的吧。利用拼音指南卻可以讓我們在Excel中輕鬆設置單元格中文字的行間距。在Excel 2003中選中需要設置行間距的單元格，單擊「格式」菜單，依次選擇「拼音指南/顯示或隱藏」，馬上可以看到單元格中文字行間距變大了。如果想再進一步調整行間距，可再單擊「格式」菜單，選擇「拼音指南/設置」打開「拼音屬性」窗口，切換到「字體」選項卡下，把字型大小設置大一點，確定後行間距就會相應增大，反之則減小。怎樣同時改變多行行高我們知道，通過拖動行或列間的分界線可以改變行高或列寬，但怎樣同時改變向行或幾列的高度或寬度呢？我們以改變行高為例，先選中要改變行高的列，按下Shift鍵再單擊行標題頭，可以選定連續的多行（如果要選中多個不連續行，可以按下Ctrl鍵）。選中多列後，拖動任意一個被選中的行標題間的分界線，到適當高度釋放滑鼠，所有被選中的行高都改變了。我們也可以精確地改變行高：選中多行後，單擊「格式」菜單，選擇「行」中的「行高」命令，設置行高為20，單擊「確定」，行高都被設置為20了。快速換行在Excel單元格中輸入數值後，按下Alt鍵不鬆開，再按下Enter鍵，即可快速換行。讓文本換行每次在Excel單元格中輸入一個值，再按下Enter鍵，活動單元格均默認下移一個單元格，非常不方便。不過，這時，可以選擇「工具」→「選項」→「編輯」，然後取消「按Enter鍵移動活動單元格標識框」複選框即可。在Excel中行列快速轉換如果需要要將Excel按行（列）排列的數據，轉換為按列（行）排列，可以通過「選擇性粘貼」來實現。選中需要轉換的數據區域，執行一下「複製」操作；選中保存數據的第一個單元格，執行「編輯選擇性粘貼」命令，打開「選擇性粘貼」對話框，選中其中的「轉置」選項，確定返回即可。將原有列中的內容倒置過來1524334251B1 =OFFSET(A$1,COUNTA(A:A)-ROW(A1),)快速回到A1單元格按下Ctrl+Home組合鍵，快速選中A1單元格。複製粘貼中回車鍵的妙用1、先選要複製的目標單元格，複製後，直接選要粘貼的單元格，回車OK；2、先選要複製的目標單元格，複製後，選定要粘貼的區域，回車OK；3、先選要複製的目標單元格，複製後，選定要粘貼的不連續單元格，回車OK。一次選中批註單元格按下Ctrl+Shift+O (字母 O)組合鍵，可以一次性選定所有帶批註的單元格。一次在所有單位格中插入批註1選擇你已經做批註的單元格2複製3選擇你要做相同批註的所有單元格4編輯〉選擇性粘貼〉批註在公式中插入批註如果要在公式中插入批註信息，可以利用「N（Value）」返回「0」的特點，因為文字屬於其它值。1．假如A1～F1單元格中是個別統計數字，G1則是它們的總和，那麼一般情況下其公式為「=SUM(A1:F1)」。2．如果要在公式中插入批註信息，可以將公式更改為「=SUM(A1:F1)+N("A1～F1的總和")」，如圖1所示。不連續單元格填充同一數據選中一個單元格，按住Ctrl鍵，用滑鼠單擊其他單元格，就將這些單元格全部都選中了。在編輯區中輸入數據，然後按住Ctrl鍵，同時敲一下回車，在所有選中的單元格中都出現了這一數據。空白行的填充各位，我一些同事喜歡在表格內使用合併單元格，而我的進行公式運算時需把單元格打散，但就出現了許多空白格，現在我想把空白格用臨近的非空白填充。EX：A1格為「張三」，A8為「李四」，A21為「王五」之類，現在我要把A2到A7填為張三，A9到A20為李四，A22之後為王五。在B1輸入=IF(A1<>"",A1,IF(ROW()>1,INDIRECT("b"&ROW()-1)))，向下複製到合適位置。然後用選擇性粘貼功能替換到A列中去。怎樣用函數向下實現自動填充各位高手，怎樣用函數實現如下的功能：把左邊的空格，用上面的A0001代碼填充，實現右邊的格式，謝謝解答！！！A0001白色 300| | A0001白色 300紅色500|--->|A0001紅色 500黃色300 | |A0001 黃色300如果你的第一個「A0001」在[A1]，A2=if(b2>0,a$1,"") 向下拖曳。最好用附件的形式來提問，這樣可以減少相互間猜題的麻煩。用絕對值是不行的，假如，我下面還有別的編號，這個功能就實現不了啊怎麼設置自動保存在「工具」菜單上，單擊「選項」，再單擊「保存」選項卡。選中「自動保存時間間隔」複選框。在「分鐘」框中，指定希望 Microsoft Office 程序保存文件的頻率。避免輸入網址和電子郵件地址時的超鏈接在單元格中輸入的網址或電子郵件地址，Excel在默認情況下會將其自動設為超級鏈接。如果想取消網址或電子郵件地址的超級鏈接，可以在單元格上單擊滑鼠右鍵，選擇「超級鏈接/取消超級鏈接」即可。此外，還有兩個有效辦法可以有效避免輸入內容成為超級鏈接形式：1、在單元格內的錄入內容前加入一個空格；2、單元格內容錄入完畢後按下「Ctrl＋z」組合鍵，撤消一次即可。單元格前面自動加了等號我的單元格怎麼輸入時間後前面自動加了等號,然後2005年就變成了1905年了呢?工具-選項-1-2-3幫助-轉換 lotus 123 公式有無打勾？去掉加蓋公章我們日常上送、下發的報表材料、通知等都要加蓋公章，如果把這項工作交給Excel或Word來完成，我們的工作就輕鬆多了。第一步：製作公章圖案首先我們要做出一個公章的圖案，最簡單的辦法是把公章圖案掃描到電腦中，然後處理成透明的GIF圖像。我們也可以直接用Excel來製作：把繪圖工具打開，選中「橢圓」工具，在按下「Shift」鍵的同時拖開滑鼠，就可以得到一個正圓了。雙擊這個正圓打開「設置自選圖形格式」對話框，在「顏色與線條」標籤中，填充顏色選「無填充顏色」，線條顏色設為紅色，選3磅粗的單線形（圖）。公章的文字用藝術字來製作，填充顏色和線條顏色都用紅色，並設成無陰影產。弧形文字和水平文字要分開來做，在做弧形文字時，把藝術字拖到圓形的上方，在藝術字工具中選「藝術字形狀－細上彎弧」，按住黃色的四方塊往下拉，再作適當的調整，就可以做出公章里的圓弧形的文字了。公章中間還有一個紅五星，用「自選圖形」的星形就可以做出來了，填充顏色和線條顏色用紅色。最後，按住「Shift」鍵把組成公章的文字、圖形全部選上，執行右鍵菜單中的「組合」命令，一個公章就做好了。如何把做好的公章保存出來？這裡有一方法：把工作表另存為Web頁，然後到保存目錄中找到*.files的文件夾，裡面有一個GIF圖片，這就是剛才做好的公章圖案了，它的背景是透明的，我們把它改名為gongzhang.gif保存下來即可。第二步：添加「蓋章」按鈕接下來我們給Excel添加一個蓋章按鈕，當一個工作表做好後，點擊這個蓋章按鈕，就可以為我們蓋上公章了。先把公章圖形複製出來（用來粘貼作為按鈕的圖標），然後打開「工具－自定義」對話框，選中「命令」標籤，在「類別」欄中找到「宏」，在右邊的「命令」欄里就會出現一項「自定義按鈕」。用滑鼠把這個笑臉圖標拖出到菜單欄或工具欄上放下，在笑臉圖標上擊右鍵，在彈出的菜單中把「命名」處的文字改為「加蓋公章」。接下來點擊「粘貼按鈕圖標」這個命令，就可以用剛和複製的公章圖形來代替笑臉圖標了。把滑鼠移下來選中「分配超級鏈接－插入圖片」，然後在「請鍵入文件名稱或Web頁名稱」欄里輸入公章圖片gongzhang.gif的文件名及路徑，然後按「確定」返回。好了，看到「加蓋公章」這個按鈕了吧，點擊一下看看，呵呵，頁面上就蓋上一個鮮紅的公章了，用滑鼠可以把它拖到任意的地方。在Word文檔中加蓋公章的方法與此大同小異，大家可以自己試一試。查找+格式刷的妙用通常在數據校對時要用到查找，找到之後就用不同格式區分(如字體為紅色、底紋為黃色等等)，如此重複。例如：先找到第一個數據並將字體改為紅色,然後雙擊格式刷,當查找到其它相同數據時,再按Ctrl+A，excel就會將新找到的數據自動改為紅色與之區分。具體操作為：Ctrl+F→在查找欄輸入要查找的值→查找下一個→設置格式（不要退出查找對話框）→雙擊格式刷→查找全部→Ctrl+A→關閉另外，通常的方法是：Ctrl+F→在查找欄輸入要查找的值→查找全部→Ctrl+A，再設置格式。但再找下個值時，又要重新設置格式，如果要找的值很多的話就顯得不是那麼方便了。當然按照自己的習慣做最好的。如果用格式刷+查找功能呢,就可以找到A並把A設成紅色,再雙擊格式刷,然後再找B、找C、找D…excel就會把找到的B、C、D自動的設為紅色以之區分，而不用找一個設一次字體了。Excel中滑鼠雙擊妙用1、雙擊單元格，就可以編輯單元格的內容（對應用快捷鍵——F2）。2、在行/列邊緣雙擊滑鼠，則可以得到此列的最適合的行高/列寬。3、雙擊工作表標籤，可以直接重命名工作表的名稱。4、在填充的時候，選定單元格再移動到這個區域的右下角，這時滑鼠會變成細十字。(1)當選擇區域當下方的單元格有內容時，雙擊會自動填充下方有數據的區域。(2)當選擇區域當下方的單元格為空時而左邊有數據時，雙擊會自動填充到與左邊有數據的區域齊。(3)當選擇區域當下方與左邊的單元格為空時而右邊有數據時，雙擊會自動填充到與右邊有數據的區域齊。5、將滑鼠移動到選定單元格的邊上，這時滑鼠會變成帶箭頭的十字。(1)這時雙擊，可以移動到數據區域的邊緣，相當於快捷鍵——Ctrl+方向鍵。(2)如果按住Shift再雙擊，可以快速選擇數據，相當於快捷鍵——Shift+Ctrl+方向鍵。6、雙擊工具欄的空白處，就可以調出自定義工具欄的對話框。7、雙擊左上角工具欄中工作簿的圖標，可以關閉當前工作簿；雙擊標題欄中的Excel圖標，可以關閉Excel。8、使用格式刷時，用雙擊而不是單擊就可以多次使用，再單擊一次格式刷結束。在使用繪圖工具欄時，如果雙擊線、矩形、圓等圖形時也可以連續繪圖。9、雙擊拆分窗格的分割條(上下滾動條的上方，左右滾動條的右邊，沒試過拆分窗口的朋友可以先從窗口/拆分里體驗一下)，可以按當前單元格上下左右拆分；拆分後再雙擊分割條的任意部分可以恢復。10.在菜單上雙擊，可將菜單中所有的菜單項(包括不常用的菜單項)全部展開。11.如果工具條浮動在工作表區，在工具條的標題欄雙擊，則該工具條返回工作表區上部或下部位置。在選中行或選中列（當然也可以是某一行、列）的邊緣雙擊，自動設置成合適的行高或列寬（偶常常在做完表後調整列寬、行高的時候把整個表選中，雙擊兩下就OK了）。12.雙擊還可以恢復被隱藏的行或列如要恢復被隱藏的第3行,先將滑鼠指針移動到第2行與第4行之間的位置,當滑鼠的指針變成橫向為「＝」號，縱向為黑色實心雙箭頭的十字形狀時,再雙擊操作，被隱藏的行就會顯示出來了。變為"="或"||"時直接拖動就可以了。13.在標題欄上雙擊，由最大化窗口（原始狀態）還原到原始狀態（最大化）大小 。14.雙擊EXCEL中的透視表中的數據,可在新的工作表中列出該數據的明細。Excel中快速定位的技巧實例在Excel中，我們需要到達某一單元格，一般是使用滑鼠拖動滾動條來進行，但如果數據範圍超出一屏幕顯示範圍或數據行數非常多時，想快速定位到某一單元格可要有點麻煩了。其實我們可以使用「定位」功能迅速到達想要的單元格。例1：需要選中Y2008單元格（或快速移動到Y2008單元格），我們可以使用「編輯/定位」菜單，在引用位置里輸入「Y2008」後按回車即可。例2：需要選中Y列的2004～2008行的單元格，我們按照相同的方法，在引用位置里輸入「Y2004:Y2008」按回車即可。例3：需要選中2008行的單元格，我們可以在引用位置里輸入「2008:2008」按回車即可。例4：需要選中2004～2008行的單元格，我們可以在引用位置里輸入「2004:2008」按回車即可。在Excel中插入Flash時鐘的步驟動態時鐘不是用函數運算、自動化功能製作出來的,這只是簡單的插入Flash文擋的功能而已，而且只要你有Flash文件，任何人都可以輕鬆自行製作。製作方法：第1步 首先打開一個空白Excel文件，點擊「視圖」 → 然後點選【控制項工具箱】，→點擊「其他控制項」。第2步 然後再點擊[Shockwave Flash Object]項目，表示要插入Flash物件。第3步 接下來，滑鼠會變成一個小十字，此時可以在Excel編輯區中畫一個大小適中的方框，這個方框就是用來顯示Flash時鐘的內容的。第4步 畫好方框後，接著點擊【屬性】，準備設置屬性。第5步 出現「屬性」對話框後，將DeviceFont設置成False；將Eebedmovie設置成True；將Enabled設置成True；將Locked設置成True；將Loop設置成True；將Menu設置成False；並在「Movie」右側填入時鐘的地址與名稱(如：G：22006.swf)。第6步 如下圖，退出設計模式，全部完成。小寫數字轉換成人民幣大寫方法1=IF(TRUNC(H16)=H16,TEXT(H16,"[DBNum2]G/通用格式")&"元整",TEXT(TRUNC(H16),"[DBNum2]G/通用格式"&"元"))&IF(AND(TRUNC(H16)<>H16,RIGHT(TRUNC(H16*10))<>"0"),TEXT(TRUNC(MOD(H16*10,10)),"[DBNum2]G/通用格式")&"角","")&IF(AND(RIGHT(TRUNC(H16*10))="0",TRUNC(H16)<>H16),"零","")&IF(TRUNC(H16*10)<>H16,TRUNC(H16*10)=H16*10), "整","")方法2=IF(F10=0,"",CONCATENATE(IF(INT(F10)=0,"",TEXT(INT(F10),"[DBNum2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"",IF(INT(F10)=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))方法3人民幣大寫的函數公式，可正負，最多兩位小數。=IF(A1<0,"負","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(A1),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A1)),"零",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A1),"[DBNum2]")&"分"))方法4修改一下4：根據劍魔兄的測試，發現有一個問題，如-100.05，現修正如下：=IF(A1<0,"負","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A1)),"零",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A1),"[DBNum2]")&"分"))方法5=IF(A1<0,"負",)&TEXT(TRUNC(ABS(A1)),"[DBNum2]G/通用格式")&"元"&IF(ROUND(A1,3)=ROUND(A1,),"整",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]G/通用格式")&"角"&IF(ROUND(A1,3)=ROUND(A1,1),"整",TEXT(RIGHT(ROUND((A1*100),),1),"[DBNum2]G/通用格式")&"分"))方法6無條件捨去： =CONCATENATE(IF(A1<0,"負",""),TEXT(IF(TRUNC(A1)=0,"零",TRUNC(ABS(A1))),"[DBNum2]")&"元",IF(OR(AND(ABS(A1)<0.1,TRUNC(A1)=A1),RIGHT(INT(ABS(A1)*100),2)="00"),"",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(A1*10),1)="0","","角"),IF(OR(TRUNC(A1*10)-(A1*10)=0,RIGHT(TRUNC(A1*100),1)="0"),"整",TEXT(RIGHT(TRUNC(A1*100),1),"[DBNum2]")&"分"))小數點後兩位四捨五入: =CONCATENATE(IF(A1<0,"負",""),TEXT(IF(TRUNC(ROUND(A1,2))=0,"零",TRUNC(ABS(ROUND(A1,2)))),"[DBNum2]")&"元",IF(TRUNC(ROUND(A1,2))=ROUND(A1,2),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(ROUND(A1,2)*10),1)="0","","角"),IF(OR(TRUNC(ROUND(A1,2)*10)-(ROUND(A1,2)*10)=0,RIGHT(ROUND(A1,2),1)="0",TRUNC(ROUND(A1,2))=ROUND(A1,2)),"整",TEXT(RIGHT(ROUND(A1,2),1),"[DBNum2]")&"分"))方法7無條件捨去： =IF(A1<0,"負","")&SUBSTITUTE(TEXT(TRUNC(A1),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".",TRUNC(A1,2))),TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]")&IF(ISNUMBER(FIND(".0",A1)),"","角"),"")&IF(LEFT(RIGHT(TRUNC(A1,2),3),1)=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&"分","整"),"-",)小數點後兩位四捨五入: =IF(A1<0,"負","")&SUBSTITUTE(TEXT(TRUNC(ROUND(A1,2)),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".",ROUND(A1,2))),TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]")&IF(ISNUMBER(FIND(".0",ROUND(A1,2))),"","角"),"")&IF(LEFT(RIGHT(TRUNC(ROUND(A1,2),2),3),1)=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分","整"),"-",)方法8再簡化如後，請大家試試。無條件捨去：=IF(A1<0,"負","")&TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元"&IF(ISERR(FIND(".",TRUNC(A1,2))),"",TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]"))&IF(RIGHT(TRUNC(A1*10))="0","","角")&IF(LEFT(RIGHT(TRUNC(A1,2),3))=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&"分","整")小數點後兩位四捨五入:=IF(A1<0,"負","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分","整")方法9=IF(ISTEXT(C2),"","人民幣："&TEXT(INT(C2),"[dbnum2]")&"元"&IF(INT(C2*10)-INT(C2)*10=0,"",TEXT(INT(C2*10)-INT(C2)*10,"[dbnum2]")&"角")&IF(INT(C2*100)-INT(C2*10)*10=0,"整",TEXT(INT(C2*100)-INT(C2*10)*10,"[dbnum2]")&"分"))輕輕鬆鬆製作超複雜Excel表頭在Excel中，經常會碰到要製作的複雜表頭，其中包含有斜線和文字(見下圖畫紅圈處)，許多初學者往往對此束手無策，還有的乾脆胡亂調整，由於採取的方法不當，結果卻是花了很長的時間卻達不到理想的效果。圖1 圖2許多初學者經常採用以下兩個操作來處理：1、 用邊框中的斜線來調整；2、 在單元格中直接輸入文字。現筆者分析一下其弊端：1、 用邊框中的斜線來調整，只能畫一條斜線，不能畫多條斜線，同時，畫出的斜線只能是單元格的對角線，不能隨意更改。見圖2：2、在單元格直接輸入文字，要分行，只能先讓單元格自動換行，然後用空格鍵移動相應的文字到第二、三、四行，這種方法費時費力，文字一多往往就做不到理想的效果。至此，不少初學者不盡望洋興嘆：做一個表頭怎麼那麼難!!其實，換一種思維，換一個方法，就「柳暗花明又一村」了。下面，筆者還是以圖1為例進行詳細說明。首先，輸入沒有斜線的單元格的內容，調整行列(見圖3)。圖3 圖5在這裡說明一下，調整行列很重要，這樣製作帶斜線表頭後表格的其它部分不用再調整了，否則調整表格的其它部分，斜線單元格的內容又挨重新調整。這點筆者千萬要注意。然後，單擊繪圖工具欄中的「直線」(圖4中畫紅圈處)。圖4將滑鼠移到單元格中，明確直線的起點和終點，從起點按住滑鼠拉到終點，即可畫出第一條直線。如果起點和終點有偏差，可將滑鼠移到直線的起點處(或終點)，滑鼠由空心十字形變成斜雙箭頭後，即可按住滑鼠往任意一個方向調整直線到合適的位置，上、下、左、右均可。這就是這種方法的好處。用這種方法做出第二條直線，效果見圖6：圖6 圖8現在是輸入文字了，怎麼輸入呢?用文本框工具。單擊繪圖工具欄中的「文本框」，見後頁圖(畫紅圈處)：圖7然後在單元格中按一下滑鼠，輸入第一個字「科」。效果見圖8：這裡要注意的是「按一下滑鼠」，不要拖動滑鼠。如果拖動滑鼠，文本框會出現黑邊框(見圖9)。圖9 圖10將圖8文本框中的「科」選中，調整其字體、字型大小等格式成合適的效果。這一點也要注意，到下面的複製操作時就不必再來調整各個文字的格式了。滑鼠點一下「科」文本框，然後將滑鼠移到該文本框邊緣，點滑鼠右鍵。見圖10：在彈出的菜單中選擇「複製」，在表格的任一個地方點滑鼠右鍵，選擇「粘貼」，就會出現另一個「科」文本框。效果見圖11：圖11 圖12將「科」字改為「目」字，將滑鼠移到該文本框邊緣，滑鼠變成十字箭頭形，按住滑鼠移動該文本框到單元格合適的位置。效果見圖12：如果「科」字和「目」字相對位置不理想，還可以繼續調，只要選擇相應的文本框移動就行了。這就是為什麼我們把兩個字分成兩個文本框，目的就是為了方便調整這兩個字之間的相對位置。通過採取相似的方法，把其餘的文字做出來。效果見下圖：至此，讀者一定會發現，再複雜的斜線表頭，利用直線和文本框工具，也就迎刃而解了!【字元截取與增減】=RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))截取字元的公式有一組數據101～103*,11～20*…我想截取~至*之間的數字。=MID($A3,FIND("~",$A3)+1,FIND("*",$A3)-(FIND("~",$A3)+1))如何確定*號後的數字=RIGHT(A1,LEN(A1)-FIND("*",A1,1))=REPLACE(A1,1,FIND("*",A1),)=TRIM(RIGHT(SUBSTITUTE(A1,"*",REPT(" ",LEN(A1))),LEN(A1)))=MID(A1,SEARCH("~*",A1)+1,100)=SUBSTITUTE(A1,LEFT(A1,FIND("*",A1)),"")｛=--MID(A1,MATCH("~*",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),)+1,100)｝=RIGHT(A1,LEN($A$1)-FIND(CHAR(CODE("*")),$A$1,1))（取最後三位）如何提取數字中間的數數據在A1單元格,則公式為:=MID(A1,5,3)解釋：「5」是從第5位開始提取，「3」是提取3個數。三個數中,如何取出中間那個比如1,2,3三個數字,我想取出2,用什麼方法?=LARGE(A1:A3,2)取數值後三位公式=RIGHT(A1,3)取數函數單元格中用函數單獨取出*號前（後）的數假如2.01*750位於A1=MID(A1,1,FIND("*",A1,1)-1) 取得*號前的數據=MID(A1,FIND("*",A1,1)+1,LEN(A1)-FIND("*",A1,1)+1) 取得*號後的數據對2.01*750*800的得數就是750*800，而不是800，怎麼辦呢=MID(B2,FIND("*",B2,FIND("*",B2,1)+1)+1,LEN(B2))=RIGHT(B2,LEN(B2)-FIND("/",SUBSTITUTE(B2,"*","/",LEN(B2)-LEN(SUBSTITUTE(B2,"*","")))))如何把單元格中的數字提取出來（字元串中不連續）數組公式=SUM(MID(0&A4,LARGE(ISNUMBER(--MID(A4,ROW($1:$20),1))*ROW($1:$20),ROW($1:$20))+1,1)*10^ROW($1:$20)/10)數字在字元串中不連續如何提取數字如：3k3mn249up結果：33249033k3mn249up結果：333249用數組公式：=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$1024),1))*ROW($1:$1024),ROW($1:$308))+1,1)*10^ROW($1:$308)/10)用如何提取「-」前後的字元5-0，4-2，0-6，取左邊=CHOOSE(TYPE($D4),MONTH($D4),LEFT($D4,FIND("-",$D4)-1))取右邊=CHOOSE(TYPE($D4),DAY($D4),RIGHT($D4,LEN($D4)-FIND("-",D$4)))怎樣刪去﹕後的文字格式都是 XXXX : YYYYYY,如何只保留XXXX，而全部刪下﹕ 後的文字呢﹖(XXXX 的長度是不一樣)這個用函數可輕易解決：=LEFT(A1,FIND(":",A1)-1)若沒有要求一定要用函數解，那也可以試試用[數據]>（數據剖析）怎樣只取「.」之後的文字﹖如：Q24-S4. Working Status 只取 Working Status如果「.」 前的字數固定=RIGHT(A1,LEN(A1)-7)如果不固定=RIGHT(A1,LEN(A1)-FIND(".",A1))=TRIM(RIGHT(A1,LEN(A1)-FIND(".",A1,1))獲取單元格內容中字元串08:25,18:25如：要取得單元格b5中的從左邊算起五位的字元串。即是08:25=LEFT(B5,5)如：要取得單元格b5中的從右邊算起五位的字元串。即是08:25=RIGHT(B18,5)單元格編號，起始位數，從起始位算起的第幾位數MEP090296=MID(B1,4,3) 結果：090如何提取一串數字中的幾位數字（字元）如：050326提取後3位數字=RIGHT(A1,3) 「3」是提取3位，如果改「4」，則提取4位。=RIGHT(A3,LEN(A3)-3)=MID(A3,4,3)=REPLACE(A3,1,3,"")提取中間的4位數字，「5032」=MID(A1,2,4)=MID(A3,(LEN(A3)-4)/2+1,4)要提取 050324 中的 502 怎麼提取？即：第二、三兩位和第五位數字=MID(A3,2,2)&MID(A3,5,1)=MID(A3,2,1)&MID(A3,3,1)&MID(A3,5,1)如何把一個單元格中的數字挑出來一個單元格中有數字、空格、漢字，如：「11210101 銀行存款/工行」，數字的位數不確定，但都從最左邊開始，數字和漢字中間有一個空格。如何只把數字顯示出來？1、如果都是這樣就簡單 "都從最左邊開始，數字和漢字中間有一個空格"假定在A1，公式為：=LEFT(A1,FIND(" ",A1)-1)2、數據分列不更簡單么？分列符號選中空格前面那個框。分割文本有一列數據，全部是郵箱的，現在想將@前面的賬號與@後面的域名分割開，分為兩列，如何做？採用函數分割：例如：A1: name@163.comB1:=LEFT(A1,FIND("@",A1)-1) --> nameC1:=RIGHT(A1,LEN(A1)-FIND("@",A1)) --> 163.com或：數據-分列-分列-分隔符號-@就可以了按照給定的位數，截斷小數點後的數字對整數無效，且這個函數沒有四捨五入的功能12512.2514 12512.25=TRUNC(B23,2)單元格數字提取問題單元格裡面填寫的CHIP(0601-2299),把0601-2299提取出來應該怎麼做。=IF(B3="","",SUBSTITUTE(MID(B3,FIND("(",B3)+1,100),")",""))我用IF函數是因為看到你給的表格當中,數據和數據之間都有一行空行,如果沒有空行的話,數據是連續的時候公式可以簡化為：=SUBSTITUTE(MID(B3,FIND("(",B3)+1,100),")","")以關鍵字提取名稱求當輸進球隊時，自動生成聯賽名稱聯賽球隊當輸進球隊時函數自動生成英超車路士車路士英超英超阿仙奴愛華頓英超英超曼聯祖雲達斯意甲英超利物浦麥斯納意甲英超愛華頓英超米杜士堡意甲祖雲達斯=INDEX(A$2:A$23,MATCH(D2,B$2:B$23,0))=INDIRECT("A"&MATCH(D2,B:B,))=VLOOKUP(D2,IF({1,0},$B$2:$B$23,$A$2:$A$23),2,0)如何把文本中的前幾個字元去除如將「第二班AAA」中的「第二班」三個字去除。1、用公式：=RIGHT($A2,3)=RIGHT($A2,LEN($A2)-FIND("班",$A2))=MID($A2,FIND("班",$A2)+1,LEN($A2))=RIGHT(B2,LENB(B2)-LEN(B2))=REPLACE(A1,1,3,"")=SUBSTITUTE(A1,"第二班",)如前幾個字元或後英文字數, 不相同或不固定，公式可用:{=MID(A2,MATCH(0,--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<65),0),255)}2、數據>分列>固定寬度,把上述數據分為兩列後,刪除第一班的列對一列中的文字統一去掉最後一個字能否對一列中的文字統一去掉最後一個字?這些文字不統一,有些字數多,有些字數少。如何處理?=REPLACE(A1,LEN(A1),1," ")(在過渡列進行)討如何去掉單元格中的第一個數字？=MID(A1,2,LEN(A1)-1) 或者 =RIGHT(A1,LEN(A1)-1)=REPLACE(A1,1,1,"")論一下取最後一個單詞的方法例如現在在A1中有一句「M. Henry Jackey」，如何用函數將最後的一個單詞取出來呢？當然，我們現在是知道最後的單詞是6個字元，可以用Right(A1,6)來計算，但如果最後一個單詞的字元數是不定的呢，如果做呢？請大家試下有幾種方法。方法1、用一列公式填充=IF(LEFT(RIGHT($A$1,ROW()),1)=CHAR(32),RIGHT($A$1,ROW()-1),「」)方法2、=MID(A1,FIND(" *",SUBSTITUTE(A1," "," *",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)-FIND(" ",A1))方法3、=IF(ISERROR(SEARCH("",TRIM(LEFT(B1)))),RIGHT($A$1,ROW()),"")拖出來的第一個字元就行。方法4、{=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*ROW(INDIRECT("1:"&LEN(A1)))))}嫌長就（假定最長100字元）{=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(1:100),1)=" ")*ROW(1:100)))}如何去掉單元格最後一位數字說明:單元格前面的數據不能改變，去掉最後一位數字。=LEFT(A2,LEN(A2)-IF(ISNUMBER(--RIGHT(A2)),LEN(LOOKUP(9E+307,--RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))))),))如果後面代的數值在兩位以內,也可以用以下方式實現,當然如果超過兩位的話,可以加語句 IF(ISNUMBER(RIGHT(TRIM(A2), X )+0)=TRUE,1,0) X 代表後面數值的個數。=LEFT(TRIM(A2),LEN(TRIM(A2))-IF(ISNUMBER(RIGHT(TRIM(A2),1)+0)=TRUE,1,0)+IF(ISNUMBER(RIGHT(TRIM(A2),2)+0)=TRUE,1,0))如何在一列已經輸入的數據前添加「p」比如一列數據 添加後變112234 p112234123435 p123435124355 p124355123545 p123545選中所有單元格，設置格式，自定義-〉在預設的「G/通用格式」前面加上「"p"」（半形的雙引號中間是p）即可加一列全是「p」，使用&=a1&b1 Shift+7="P"&A1在自定義中輸入"Q"#即可,很簡單的。什麼函數可以插入字元怎樣用第一列的數據形成第二列的數據，即在特定位置加上幾個相同字元串？解答：b1="04"& a1問：哪如果倒過來呢？答：用公式A1=MID(B1,3,13)或A1=SUBSTITUTE(B1,"04","",1)如何在數據前添加「*」號數據如在B列,在A列整列加「*」，C列C1輸入公式C1=A1&B1,下拉。用「選擇性粘貼」選「值」複製到D列，刪去A、B、C列。數字前面加上數字123012375223比如說上述的數字，我想在它們前面加上38910104，而且位置短的數字，會自動補0變成如下：389101040012338910104001233891010475223）=TEXT(A1,"3891010400000")3、查找替換，查找欄輸入：第*班，替換欄空置，全部替換【數據拆分與合併】數字如何拆分我有一組數據，如123，59等，假如這些數據均在A列，我現在需要將123或者59這樣的數據拆成到B,C,D列。B1=MID(TEXT($A1,"000"),COLUMN(A1),1) 往右拖=MID(REPT(0,3-LEN($A1))&$A1,COLUMN(A1),1)單元格中的數據拆分如何將一個單元格中的11位數據拆分11各單元格（每個單元格一個數字）。如：01234567890 變為：0，1，2，3，4，5，6，7，8，9，0（一個單元格一個數字），文字也同樣。用以下公式：=MID($A1,COLUMN(A1),1) 向右拖=MID($A$2,COLUMN()-1,1) 向右拖=MID($A$2,COLUMN(),1) 向右拖也可以，選中區域後「數據」-----分列，「固定列」-----看到尺寸的時候分別在01234567890兩數之間點擊一下-----完成就行了！單元格的拆分一個單元格數據即包含了物品名又包含其規格,兩者之間用"/"來隔開,現想把兩者單獨分開。如：軸承/SKF 62122R，外六角螺絲/M10*30....."/"前後都無標準長度。1、用「數據-分列」，不用公式的方法最簡單了。2、用公式：=LEFT(A1,FIND("/",A1)-1)=RIGHT(A1,FIND("/",A1)-1)如何拆分字組如何將一個單元格里的字組拆開來啊，1、你好啊→你好啊 要用什麼函數啊?=SUBSTITUTE(A1,"好"," 好 ")2、」你— 好 — 啊「改成「你好啊」 把中間的橫線去掉。要用什麼函數或怎樣在自定義裡面設置？①用查找替換功能，查找「—」，替換為空值②=SUBSTITUTE(A1,"— 好 — ","好")用連字元「&」來合併文本將B、C、D列合併。1.在E1單元格中輸入公式：=B1&C1&D1 下拉2.選中E列，執行「複製」操作，然後選中F列，執行「編輯→選擇性粘貼」命令，打開「選擇性粘貼」對話框，選中其中的「數值」選項，按下「確定」按鈕，E列的內容（不是公式）即被複制到F列中。3.將B、C、D、E列刪除，完成合併工作。怎樣把不同格式不同位置的內容合併到一個單元格內如：一個是文本格式一個是日期格式，怎麼合併呢？今天是： 2007-06-25＝A1&TEXT(B1,"yyyy-m-d h:mm;@")把不同單元格的內容合到一個單元格里比如：A1=好，A2=好，A3=學，A4=習在A5輸入公式，使A5單元格內容為：好好學習=TEXT(A1&A2&A3&A4,"")=CONCATENATE(A1,B1,C1,D1)合併與拆分兩列合併成一列如何做=OFFSET($A$2,TRUNC(ROW(A2)/2,0)-1,MOD(ROW(A2),2))向下拖如果是一列拆開分成兩列又如何做=OFFSET($C$2,ROW()*2-4+COLUMN(A:A)-1,)=INDEX($C:$C,(ROW(1:1))*2+COLUMN(A:A)-1)=INDEX($C:$C,(ROW(2:2)-1)*2+COLUMN(A:A)-1)向右拖一個再向下拖。=IF(ROW()>COUNT(A:A),INDEX(B:B,ROW()-COUNT(A:A)),A1)公式下拖。合併不同單元格的內容合併不同單元格的內容，可以利用CONCATENATE函數，此函數的作用是將若干文字串合併到一個字串中，具體操作為=CONCATENATE（B1,C1）比如，假設在某一河流生態調查工作表中，B2包含「物種」、B3包含「河鱒魚」，B7包含總數45，那麼： 輸入「=CONCATENATE（"本次河流生態調查結果："，B2，""，B3，"為"，B7，"條/公里"）」 計算結果為：本次河流生態調查結果：河鱒魚物種為45條/公里。關於文字在表格中的組合如：計算機計算機C1=A1&B1求拆解合併公式2/252/254/254/25若合併：A1=B1&"/"&C1若拆解：B1=left(A1,find("/",A1))C1=right(A1,find("/",A1))如何把字母和數字分開?想把A列中如A8中"n.m.1."分解成兩列"n.m."和"1.",有什麼好辦法嗎?可以用什麼公式把字母與數字和符號分開嗎?=IF(ISERR(FIND(1,A8)),"",RIGHT(A8,LEN(A8)-FIND(1,A8)+1))把分散在各單元格的內容合在一個單元格中75 P 128 66 75P12866=CONCATENATE(A1,B1,C1,D1)多個工作表的單元格合併計算=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)【條件自定義格式】通過條件格式將小計和總計的行設為不同的顏色答：輸入=RIGHT(RC,1)="計"；設定字體、邊框、圖案；確定。如何實現這樣的條件格式有一個excel表單，若當其中一欄數值超過某一值，使整個一行底色為某一顏色（比如紅色），用條件格式不能實現注意公式為=$A1>100,而不是=A1>100先選定整行再設置條件格式...列標"A"用絕對引用, 行標"1"用相對引用, 用"格式刷"刷下去...為方便和不易出錯起見, 先設置一行的條件格式, 再用格式刷將格式複製到需要的行。隔行不同字體顏色怎麼設置每隔一行就用不一樣的顏色，有什麼快速的辦法嗎？格式－條件格式(公式）：=MOD(ROW(A1),2)=0讓不同類型數據用不同顏色顯示在工資表中，如果想讓大於等於2000元的工資總額以「紅色」顯示，大於等於1500元的工資總額以「藍色」顯示，低於1000元的工資總額以「棕色」顯示，其它以「黑色」顯示，我們可以這樣設置。1.打開「工資表」工作簿，選中「工資總額」所在列，執行「格式→條件格式」命令，打開「條件格式」對話框。單擊第二個方框右側的下拉按鈕，選中「大於或等於」選項，在後面的方框中輸入數值「2000」。單擊「格式」按鈕，打開「單元格格式」對話框，將「字體」的「顏色」設置為「紅色」。2.按「添加」按鈕，並仿照上面的操作設置好其它條件（大於等於1500，字體設置為「藍色」；小於1000，字體設置為「棕色」）。3.設置完成後（圖2），按下「確定」按鈕。看看工資表吧，工資總額的數據是不是按你的要求以不同顏色顯示出來了。有無辦法讓B2所在行都呈紅色字體。如何做這樣的EXCEL表（顏色交叉）省得看錯行了。設置二行不同的格式，同時選取這兩行，按右鍵複製，選複製格式。若只是要不同顏色間隔，應該這樣就行了：格式 > 自動格式設置 –我的條件格式公式為: =IF($B1="","",MOD(ROW(),2)).條件格式如何用條件格式實現數據表格的陰影間隔效果是條件格式 =MOD(INT((ROW()+0.5-$A$1)/$A$2),2)=0公式是：=MOD(COLUMN()-a,b*2)+1<=b=MOD(ROW()-rw,n*2)+1<=n=MOD(COLUMN()-a,b*2)+1<=ba =行列間隔顯示!$L$1b =行列間隔顯示!$L$2n =行列間隔顯示!$A$2rw =行列間隔顯示!$A$1使用條件格式設置顏色條紋在Excel97版本中, 你可以使用條件格式將你工作表中的行設置成間隔顯示的條紋, 製作出來的效果象會計的分類賬. 其原理和手工設置行背景色一樣, 如果你整理工作表時刪除或移動行，它並不移動. 更多關於條件格式的信息請點擊 這裡獲得.奇數和偶數行條紋左邊圖示的被稱作"奇數條紋". 方法是奇數行用底紋顏色顯示. 本例中 1, 3, 和 5 行用淡藍色顯示, 而偶數行 2, 4, 和6 沒有變化.同樣, 右邊圖示的稱作 "偶數條紋". 方法是偶數行2, 4,和 6 用底紋顏色顯示, 奇數行1, 3, 和 5沒有變化.應該注意的是 "奇數" 和 "偶數" 是針對一個行組合而言, 並非指彩色條紋中的行，也不是指工作表的行。顏色條效果公式「奇數條紋」和「偶數條紋」的公式非常相似, 「奇數條紋」可使用下面公式：=MOD(ROW()-Rw,N*2)+1<=N在這裡 Rw Rw用于格式化的範圍內起始行號, N 是每一組顏色條中包含的工作錶行數。在上方左圖的示例中, Rw等於8, N 等於 3.「偶數條紋 」使用公式=MOD(ROW()-Rw,N*2)+1>N在這裡 Rw用于格式化的範圍內起始行號, N 是每一組顏色條中包含的工作錶行數. 在上方右圖的示例中, Rw等於8, N 等於 3..如何在條件格式中使用這些公式的方法是：選擇你想格式化的單元格範圍. 然後在格式菜單中選擇條件格式. 在彈出的對話框中將條件設置為公式，並在右邊的框內輸入上面的公式並設置好格式的圖案顏色,確定後退出，看看自己的傑作吧!在這兩個公式中, 你可以直接輸入任意Rw 及N的值 ，也可以使用自定義名稱引用的值. 使用定義名稱在改變第一組顏色條包含的工作錶行數時非常容易。如，想要使得工作表中的行隔行顯示，可以簡單地改變 N 值為1. 此時如果你將一個記錄範圍的或一個無用的單元格定義為N，只需改變這個單元格的值即可達到快速更改的效果. 當然，你也可以以同樣的方法定義一個名稱Rw,這樣，你可以將公式照搬過去，更改樣式非常方便快捷。利用公式設置的這種效果不會因為插入和刪除行而改變，這是手工效果所達不到的。同樣，如果你因為某種特殊需要將列設置成這種格式，可以將公式改為：=MOD(column()-Rw,N*2)+1<=N或：=MOD(column()-Rw,N*2)+1>N相同數據的提示？A列是項目名稱，B列是金額。想在A列輸入時，如有相同項能給出提示或是字體顏色變為紅色。例如：A1是「聯想品牌」，如果在A23中輸入「聯想品牌」時，能給出提示或是字體變為紅色。不知要怎麼做？條件格式－＞選中A列－＞公式：=IF(COUNTIF(A:A,A1)>1,TRUE,FALSE)－＞將格式改為紅色。另外如果用條件格式設置公式為=if(A:A="聯想品牌",True,False) >紅色不起作用，而用=if(A1:A30="聯想品牌",True,False) >紅色則能用。是什麼原因？～～～～～～～～～～～～～～～～～～～～～～～～～～～～～如何做到小於10顯示二位小數，大於10顯示一位小數如何做到小於10顯示二位小數，大於10顯示一位小數公式：=IF(C5>10,TEXT(C5,"0.0"),TEXT(C5,"0.00"))使用自定義單元格格式[>10]0.0;[<10]0.00;0;@如何根據數值的正負加上「+」「-」符號選中單元格—點擊右鍵—單元格格式—自定義格式[>0]"+"#;[<0]"-"#;0這百分數只能另外設置了：[>0]"+"0.0%;[<0]"-"0.0%;0.0%120，000顯示為12.0自定義格式：#!.0,121，999顯示為12.2#!.#,自定義單元格格式[=0]"男";[=1]"女"; 則可實現輸入0顯示為「男」。輸入1顯示為「女」。將單元格中的數全部變成萬元表示自定義單元格格式：0"."0,或：0!.0000有何辦法實現將一張表中的數據由元的單位轉換為萬元也就是說將表格中的所有數據同時變為原來的1/10000.請問有什麼簡便的方法嗎?1.在任一格中(如B1)輸入100002.游標停在B1上,後按[複製]3.選取資料範圍4.按[編輯]>[選擇性貼上]5.選[除]6.按[確定]如果還要後面自動顯示"萬元"可以到格式→單元格→數字(卷標)→自訂把G/通用格式改成G/通用格式"萬元"選擇性粘貼還有這種用法，真神奇。我原來都是另選一列，用函數 round(B1/10000,0)，再用「選擇性粘貼>數值」復蓋原來數據，這樣處理有一個好處，就是小數點後面沒有那麼多的數字。可以採用=ROUND(D14/10000,0)& "萬元"直接得到所需格式。常用的自定義格式單元格屬性自定義中的「G/通用格式「和」@」作用有什麼不同？設定成「G/通用格式「的儲存格，你輸入數字1..9它自動認定為數字，你輸入文字a..z它自動認定為文字，你輸入數字1/2它會自動轉成日期。設定成「@「的儲存格，不管你輸入數字1..9、文字a..z、1/2，它一律認定為文字。文字與數字的不同在於數字會呈現在儲存格的右邊，文字會呈現在儲存格的左邊。常用的自定義格式拿出來大家分享我最常用的有：1. 0」文本」 、0.0」文本」、 0.00」文本」 等(輸入帶單位符號的數值);2. #」文本」、 #.#」文本」、 ###,###.##」文本」 等(同上);3. [DBNum1][$-804]G/通用格式、[DBNum2][$-804]G/通用格式 等(數值的大小寫格式)；4． @」文本」 (在原有的文本上加上新文本或數字);5． 0000000 （發票號碼等號碼輸入）;6. yyyy/mm7. yyyy/m/d aaaa -->ex. 2003/12/20 星期六8. m"月"d"日" (ddd) -->ex. 12月20日 (Sat)9. "Subject (Total: "0")" -->單純加上文字10. "Balance"* #,##0_ -->對齊功能11. [藍色]+* #,##0_ ;-* #,##0_ -->正負數的顏色變化12. **;**;**;** -->模擬密碼保護 (搭配sheet保護)13. [紅色][<0];[綠色][>0] （小於0時顯示紅色，大於0時綠色，都以絕對值顯示）14 [>0]#,##0.00;[<0]#,##0.00;0.00 （會計格式，以絕對值形式顯示）自定義格式Excel中預設了很多有用的數據格式，基本能夠滿足使用的要求，但對一些特殊的要求，如強調顯示某些重要數據或信息、設置顯示條件等，就要使用自定義格式功能來完成。 Excel的自定義格式使用下面的通用模型：正數格式，負數格式，零格式，文本格式，在這個通用模型中，包含三個數欄位和一個文本段：大於零的數據使用正數格式；小於零的數據使用負數格式；等於零的數據使用零格式；輸入單元格的正文使用文本格式。我們還可以通過使用條件測試，添加描述文本和使用顏色來擴展自定義格式通用模型的應用。（1）使用顏色 　要在自定義格式的某個段中設置顏色，只需在該段中增加用方括弧括住的顏色名或顏色編號。Excel識別的顏色名為：[黑色]、[紅色]、[白色]、[藍色]、[綠色]、[青色]和[洋紅]。Excel也識別按[顏色X]指定的顏色，其中X是1至56之間的數字，代表56種顏色(如圖5）。（2）添加描述文本 　要在輸入數字數據之後自動添加文本，使用自定義格式為：＂文本內容＂@；要在輸入數字數據之前自動添加文本，使用自定義格式為：@＂文本內容＂。@符號的位置決定了Excel輸入的數字數據相對於添加文本的位置。（3）創建條件格式 　可以使用六種邏輯符號來設計一個條件格式：>（大於）、>=（大於等於）、<（小於）、<=（小於等於）、=（等於）、<>（不等於），如果你覺得這些符號不好記，就乾脆使用「>」或「>=」號來表示。由於自定義格式中最多只有3個數欄位，Excel規定最多只能在前兩個數欄位中包括2個條件測試，滿足某個測試條件的數字使用相應段中指定的格式，其餘數字使用第3段格式。如果僅包含一個條件測試，則要根據不同的情況來具體分析。自定義格式的通用模型相當於下式：[>；0]正數格式；[<；0]負數格式；零格式；文本格式。下面給出一個例子：選中一列，然後單擊「格式」菜單中的「單元格」命令，在彈出的對話框中選擇「數字」選項卡，在「分類」列表中選擇「自定義」，然後在「類型」文本框中輸入「"正數:"($#,##0.00);"負數:"($ #,##0.00);"零";"文本:"@」，單擊「確定」按鈕，完成格式設置。這時如果我們輸入「12」，就會在單元格中顯示「正數：（$12.00）」，如果輸入「-0.3」，就會在單元格中顯示「負數：（$0.30）」，如果輸入「0」，就會在單元格中顯示「零」，如果輸入文本「this is a book」，就會在單元格中顯示「文本：this is a book」。 如果改變自定義格式的內容，「[紅色]"正數:"($#,##0.00);[藍色]"負數:"($ #,##0.00);[黃色]"零";"文本:"@」，那麼正數、負數、零將顯示為不同的顏色。如果輸入「[Blue];[Red];[Yellow];[Green]」，那麼正數、負數、零和文本將分別顯示上面的顏色。再舉一個例子，假設正在進行帳目的結算，想要用藍色顯示結餘超過$50，000的帳目，負數值用紅色顯示在括弧中，其餘的值用預設顏色顯示，可以創建如下的格式： 「[藍色][>50000] $#,##0.00_);[紅色][<0]( $#,##0.00); $#,##0.00_)」 使用條件運算符也可以作為縮放數值的強有力的輔助方式，例如，如果所在單位生產幾種產品，每個產品中只要幾克某化合物，而一天生產幾千個此產品，那麼在編製使用預算時，需要從克轉為千克、噸，這時可以定義下面的格式： 「[>999999]#,##0,,_m"噸"";[>999]##,_k_m"千克";#_k"克"」 可以看到，使用條件格式，千分符和均勻間隔指示符的組合，不用增加公式的數目就可以改進工作表的可讀性和效率。另外，我們還可以運用自定義格式來達到隱藏輸入數據的目的，比如格式＂；＃＃；0＂只顯示負數和零，輸入的正數則不顯示；格式「；；；」則隱藏所有的輸入值。 自定義格式只改變數據的顯示外觀，並不改變數據的值，也就是說不影響數據的計算。靈活運用好自定義格式功能，將會給實際工作帶來很大的方便。怎樣定義格式怎樣定義格式表示如00062920020001、00062920020002隻輸入001、002答：格式-單元格-自定義-"00062920020"@-確定在工具按鈕之間設置分隔線工具欄中只有不同組的工具按鈕才用分隔線來隔開，如果要在每一個工具按鈕之間設置分隔線該怎麼操作？答：先按住「Alt」鍵，然後單擊並稍稍往右拖動該工具按鈕，鬆開後在兩個工具按鈕之間就多了一根分隔線了。如果要取消分隔線，只要向左方向稍稍拖動工具按鈕即可。自定義區域為每一頁的標題自定義區域為每一頁的標題。方法：文件-頁面設置-工作表-列印標題-頂端標題行與左頂標題列這樣就可以每一頁都加上自己想要的標題。如果我做了一個表某一列是表示重量的，數值很多在1--------------1524745444444之間的數不等。這些表示重量的數。如果我想次給他們加上單位，但要求是單位是>999999噸，之下>999是千克，其餘的是克。如何辦答：[>9999]###.00,"噸";*,*.00"千克"定製單元格數字顯示格式，先選擇要定製的單元格或區域，》單擊滑鼠右鍵》單元格格式》選擇『數字』選項》選擇『自定義』》在「類型」中輸入自定義的數字格式。如何輸入自定義的數字格式：需要先知道自定義格式中那些常用符號的含意，具體可以先不選擇『自定義』，而選擇其它已有分類觀看『示例』，以便得知符號的意義。比如：先選擇『百分比』然後馬上選擇『自定義』，會發現『類型』中出現『0.00%』，這就是百分比的定義法，把它改成小數位3位的百分比顯示法只要把『0.00%』改成『0.000%』就好了，把它改成紅色的百分比顯示法只要把『0.00%』改成『[紅色]0.00%』就好了。巧用定位選條件單元格Excel表格中經常會有一些欄位被賦予條件格式。如果要對它們進行修改，那麼首先得選中它們。可是，在工作中，它們經常還是處在連續位置。按」Ctrl」健逐列選取恐怕有點太麻煩。其實，我們可以使用定位功能來迅速查找它們。方法是點擊「編輯—定位」單命令，在彈出的「定位」對話框中，點擊「定位條件」按鈕，在彈出的「定位條件」對話框中，選中「條件格式」單選項成為可選。選擇「相同」則所有被賦予相同條件格式的單元格會被選中。答：在桌面上點右鍵─內容─外觀，相關的設定都在此更改。工作表的A1、A2、A3單元格分別鏈接到sheet2、sheet3、sheet4解答：1、=indirect("sheet"&row()+1&"!a1")《程香宙的解釋：indirect是把文本變為單元格引用的函數row()是取當前行號。例如在a1輸入該公式，則row()=1，公式里的值變為indirect("sheet2!a1"),跟＝sheet2!a1同效，在a2輸入該公式，則row()=2，公式里的值變為indirect("sheet3!a1")》2、使用插入-超級鏈接-書籤-(選擇)-確定按「Ctrl+~」可以一次顯示所有公式(而不是計算結果)。再按一次回到計算結果。隔行用不同顏色顯示，請問如何做我想將隔行用不同顏色顯示，請問如何做？條件格式,自定義,公式, ... 格式 --> 自動套用格式，選擇你想要的格式，確定。我現找到了一種方法，即在上下兩單元格格中設計不同顏色，再選中兩單元格，用格式刷刷即可。條件格式中用公式，=mod(row()/2,color)依次類推即可，一次設置兩種、三種、四種等顏色。將單元格設置為有「凸出」的效果或「凹進去」的效果用條件格式=mod(row(),2)=mod(column(),2)方法是設定單元格的邊框3樓的辦法不錯，但是要一個格一個格地設定，數據多了很麻煩2樓的格式里設公式能不能搞成隔一行ao隔一行tu的形式呢？格式—自動套用格式里就有。湊個熱鬧。邊框用黑白的就可以了看來還是用條件格式更方便些！用黑白雙線邊框是最簡單的辦法在Excel中設計彩色數字用戶在使用Excel處理數據時，經常需要將某些數據以特殊的形式顯示出來，這樣可以起到醒目的作用，使瀏覽者一目了然。如在某用戶的Excel單元格中有「月工資」一欄，需要小於500的顯示為綠色，大於500的顯示為紅色，則可以採用以下的方法來操作：選中需要進行彩色設置的單元格區域，選擇「格式」→「單元格」，在彈出的對話框中單擊「數字」選項卡。然後選擇「分類」列表中的「自定義」選項，在「類型」框中輸入「［綠色］［＜500；［紅色］［＞＝500］」，最後單擊「確定」按鈕即可。小提示除了紅色和綠色外，用戶還可以使用六種顏色，它們分別是黑色、青色、藍色、洋紅、白色和黃色。另外，「［＞＝120］」是條件設置，用戶可用的條件運算符有：「＞」、「＜」、「＞＝」、「＜＝」、「＝」、「＜＞」。當有多個條件設置時，各條件設置以分號「；」作為間隔。定義名稱的妙處名稱的定義是EXCEL的一基礎的技能，可是，如果你掌握了，它將給你帶來非常實惠的妙處！1. 如何定義名稱插入-名稱-定義2. 定義名稱建議使用簡單易記的名稱，不可使用類似A1…的名稱，因為它會和單元格的引用混淆。還有很多無效的名稱，系統會自動提示你。引用位置：可以是工作表中的任意單元格，可以是公式，也可以是文本。在引用工作表單元格或者公式的時候，絕對引用和相對引用是有很大區別的，注意體會他們的區別 – 和在工作表中直接使用公式時的引用道理是一樣的。3. 定義名稱的妙處1 – 減少輸入的工作量如果你在一個文檔中要輸入很多相同的文本，建議使用名稱。例如：定義DATA = 「I LOVE YOU， EXCEL！」，你在任何單元格中輸入「=DATA」，都會顯示「I LOVE YOU， EXCEL！」4. 定義名稱的妙處2 – 在一個公式中出現多次相同的欄位例如公式=IF(ISERROR(IF(A1>B1,A1/B1,A1)),」」, IF(A1>B1,A1/B1,A1))，這裡你就可以將IF(A1>B1,A1/B1,A1)定義成名稱「A_B」，你的公式便簡化為=IF(ISERROR(A_B),」」,A_B)5. 定義名稱的妙處3 – 超出某些公式的嵌套例如IF函數的嵌套最多為七重，這時定義為多個名稱就可以解決問題了。也許有人要說，使用輔助單元格也可以。當然可以，不過輔助單元格要防止被無意間被刪除。6. 定義名稱的妙處4 – 字元數超過一個單元格允許的最大量名稱的引用位置中的字元最大允許量也是有限制的，你可以分割為兩個或多個名稱。同上所述，輔助單元格也可以解決此問題，不過不如名稱方便。7. 定義名稱的妙處5 – 某些EXCEL函數只能在名稱中使用例如由公式計算結果的函數，在A1中輸入』=1+2+3，然後定義名稱 RESULT = EVALUATE(Sheet1!$A1)，最後你在B1中寫入=RESULT，B1就會顯示6了。還有GET.CELL函數也只能在名稱中使用，請參考相關資料。8. 定義名稱的妙處6 – 圖片的自動更新連接例如你想要在一周內每天有不同的圖片出現在你的文檔中，具體做法是：8.1 找7張圖片分別放在SHEET1 A1至A7單元格中，調整單元格和圖片大小，使之恰好合適8.2 定義名稱MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)8.3 控制項工具箱 – 文字框，在編輯欄中將EMBED("Forms.TextBox.1","")改成MYPIC就大功告成了。這裡如果不使用名稱，應該是不行的。此外，名稱和其他，例如數據有效性的聯合使用，會有更多意想不到的結果。零值問題在工作表中隱藏所有零值在Excel默認情況下，零值將顯示為0，這個值是一個比較特殊的數值。如果工作表中包含了大量的零值，會使整個工作表顯得十分凌亂。如果要隱藏工作表中所有的零值，可以這樣操作：選擇「工具」→「選項」，打開「選項」對話框，單擊「視圖」標籤，在「窗口選項」里把「零值」複選框前面的對號去掉，單擊「確定」按鈕。此時，可以看到原來顯示有0的單元格全部變成了空白單元格。小提示若要在單元格里重新顯示0，用上述方法把「零值」複選框前面的打上對號即可。隱藏部分零值有些時候可能需要有選擇地隱藏部分零值，使隱藏的零值只會出現在編輯欄或正在編輯的單元格中，而不會被列印，這時候就要通過設置自定義數字格式來實現：先按住Ctrl鍵用滑鼠左鍵一一選定需要隱藏零值的單元格，然後選擇「格式」→「單元格」，在「單元格格式」對話框選擇「數字」選項卡，在「分類」列表框中選擇「自定義」選項，然後在右邊的「類型」文本框中輸入「0；_0；；@」，單擊「確定」按鈕。要將隱藏的零值重新顯示出來，可選定單元格，然後在「單元格格式」對話框的「數字」選項卡中，單擊「分類」列表中的「常規」選項，這樣就可以應用默認的格式，隱藏的零值就會顯示出來。條件隱藏零值利用條件格式也可以實現有選擇地隱藏部分零值：首先選中包含零值的單元格，選擇「格式」→「條件格式」，在「條件1」的第一個框中選擇「單元格數值」，第二個框中選擇「等於」，在第三個框中輸入0，然後單擊「格式」按鈕，設置「字體」的顏色為「白色」即可。如果要顯示出隱藏的零值，請先選中隱藏零值的單元格，然後選擇「格式」菜單中「條件格式」，單擊「刪除」按鈕，在彈出的「選定要刪除的條件」對話框中選擇「條件1」即可。使用公式將零值顯示為空白還可以使用IF函數來判斷單元格是否為零值，如果是的話就返回空白單元格，例如公式「=IF(A2-A3=0，""，A2-A3)」，如果A2等於A3，那麼它們相減的值為零，則返回一個空白單元格；如果A2不等於A3，則返回它們相減的差值。【匯總計算與統計】個調稅公式=MAX(($A1-1900)*{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45}-{0,0,25,125,375,1375,3375,6375,10375,15375}){0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} 為稅率{0,0,25,125,375,1375,3375,6375,10375,15375} 為稅收扣除數上列公式的簡化式 :=MAX(應納稅所得額*0.05*{1,2,3,4,5,6,7,8,9}-25*{0,1,5,15,55,135,255,415,615},0)算物價的函數物價的那個三七作五，二舍八入的尾數處理，做一個函數。就是小數點後面第二位如果是1，2的就舍掉，如果是3，4，5，6，7的都變為5，如果是8，9的小數點第一位加1，第二位就變為0。比如價格是3.32、3.31，作尾數處理就是3.3；價格是3.33、3.34、3.36、3.37，做尾數處理就是3.35；價格是3.38、3.39，做尾數處理就是3.4。=CEILING(A1-0.02,0.05)都是二位小數　B2=ROUND(2*A2,1)/2超過二位小數　B2=ROUND(2*ROUNDDOWN(A2,2),1)/2要求在給定的應收日期、滯納金率、當前日期（自動取）的基礎上自動計算出應收滯納金。解答：=(DATEDIF(應收日期,NOW(),"d"))*滯納金率（每天）*應收金額淘汰率題目如下：這個工廠有1000人，今天抽出十人來做調查，這十人一天的產量分別為101 102 105 106 98 95 96 104 110 103 （A3-A12）。1000人當中淘汰率為5%，以這十人為標準那麼這1000人他們的生產應該為多少才不會被淘汰，看看函數的幫助就知道了呀，返回數組K百分比值點，你要1000人淘汰5人就是5/1000=0.5%=0.005，就是你以這10個抽樣調查的數據為基準，只要產量達到這個數就不會被淘汰了。（95.45）公式=PERCENTILE(A3:A12,G1)應用公積金的一個函數我公司職工公積金比例為26％ 也就是個人和單位各13％，給公積金投繳人員製作了一個函數。直接用基數乘以比例基數*比例＝投繳額， 對於投繳額的要求是：取最接近「投繳額」的偶數。我製作的函數是「=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),CEILING(A1*B1,2))」註：A1=基數 B1＝投繳比例也可以改成這樣=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),INT(A1*B1)+1)或=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),EVEN(A1*B1))如何利用公式將數值轉為百分比格式如用公式將1.289675顯示為128.97%，不是用格式來達到的。公式＝ROUND(B1*100,1)&"%"比高得分公式=RANK(B4,$B$4:$B$26,1)自動評定獎級=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2)=LOOKUP(L179,{0,4,7,12,24},{"一等獎","二等獎","三等獎","紀念獎","紀念獎"})～～～～～～～～～～～～～～～～～～～～～～～～～～～～～對帶有單位的數據如何進行求和在數據後必須加入單位，到最後還要統計總和，請問該如何自動求和？（例如：A1：2KG，A2：6KG.....，在最後一行自動計算出總KG數）。=SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))&」KG」對a列動態求和可以隨著a列數據的增加，在「b1」單元格=sum(x)對a列動態求和。=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))動態求和公式自A列A1單元格到當前行前面一行的單元格求和。=SUM(INDIRECT("A1:A"&ROW()-1))列的跳躍求和若有20列（只有一行），需沒間隔3列求和，該公式如何做？假設a1至t1為數據（共有20列），在任意單元格中輸入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))按ctrl＋shift＋enter結束即可求出每隔三行之和。跳行設置：如有12行，需每隔3行求和=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12)))有規律的隔行求和要求就是在計劃、實際、差異三項中對後面的12個月求和。=SUMPRODUCT(--(MOD(COLUMN(F3:AO3)-CELL("Col",F3)+0,3)=0),F3:AO3)=SUMIF($F$2:$AO$2,C$2,$F3:$AO3)=SUMPRODUCT((MOD(COLUMN($F3:$AO3),3)=MOD(COLUMN(F3),3))*$F3:$AO3)也可以拖動填充，插入行、列也不影響計算結果。如何實現奇數行或偶數行求和假設數據在A1:A100奇數行:=SUMPRODUCT(MOD(ROW($A$1:$A$100),2)*$A$1:$A$100)偶數行:=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100))奇數行求和 =SUMPRODUCT((A1:A100)*MOD(ROW(A1:A100),2))偶數行求和 =SUMPRODUCT((A1:A100)*NOT(MOD(ROW(A1:A100),2)))單數行求和隔行求和用什麼函數，即：A1+A3+A5+A7+A9…公式如何用。{=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))}{=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,0))}統計F4到F62的偶數單元格合計數值。｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝隔行求和公式設置均為數組公式：=SUM(IF(MOD(ROW(A1:A110),2),A1:A110,0))=SUM(N(OFFSET($A$1,ROW(1:55)*2-2,,,)))=SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100))=SUM((MOD(ROW(A1:A100),2)=0)*(A1:A100))=SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*A1:A100)隔列將相同項目進行求和隔列將出勤日和工資分別進行求和數組公式=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0))或；=SUMPRODUCT(--(MOD(ROW(C5:C25),2)<>0),C5:C25)隔行或隔列加總隔2列加總=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)隔2欄加總=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)請問如何在一百行內做隔行相加數組公式A1+A3+……+A99 單=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))A2+A4+……+A100 雙=SUM(N(OFFSET(A1,ROW(1:50)*2-1,)))如何將間隔一定的列的數據相加呢碰到100多列的數據將間隔一定的數據用手工相加太煩了，也容易出錯。如果需要相加的數據均有相同的名稱（欄位），可以用Sumif()來求解，如果沒有，就需要用數組公式來解決了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)} 1、4、7……行相加。隔列求和（A、B列）=SUM(A:A,B:B)=SUM(A:A,B:B,C:C) （統計A、B、C列）隔列求和的公式品種及日期1月1日1月2日1月3日1月4日1月5日餘額進出進出進出進出進出A11253279813=SUMIF($B$2:$K$2,"進",B3:K3)-SUMIF($B$2:$K$2,"出",B3:K3)=SUM(SUMIF(B$2:K$2,{"進","出"},B3:K3)*{1,-1})隔列求和類別成品代碼單價安貞北辰長安長春合計庫存銷售庫存銷售庫存銷售庫存銷售庫存銷售皮帶V19201270.0012121212庫存合計=SUMIF($D$3:$BS$3,"庫存",$D$4:$BT$4),銷售合計=SUMIF($D$3:$BS$3,"銷售",$D$4:$BT$4)=SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)關於隔行、隔列求和的問題隔2列加總=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)隔2行加總=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)均為數組公式。EXCEL中求兩列的對應元素乘積之和如:a1*b1+a2*b2+b3*b3...的和=SUM(A1:A3*B1:B3) (數組公式)=SUMPRODUCT(A1:A10,B1:B10)計算900~1000之間的數值之和sumif函數的計算格式為: =sumif($a$1:$a$20,">1000")。即返回$a$1:$a$20中大於1000的數值的和,但如果想計算900~1000之間的數值之和,應該如何編寫。請參考：{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}或{=SUM((900<A1:A20)*(A1:A20<1000)*A1:A20)}2、=SUMIF(A1:A20,">900")-SUMIF(A1:A20,">1000")雙條件求和1、 求一班女生的個數 :=SUMPRODUCT((A2:A9=1)*(B2:B9=""女""))2、求一班成績的和 :=SUMIF(A2:A9,1,C2:C9) "3、求一班男生成績的和 :=SUMPRODUCT((A2:A9=1)*(B2:B9=""男""),C2:C9) "如何實現這樣的條件求和求型號中含BC但不含ABC的量:ABC型號數量1CRVABC121002CVABC131023CVBC121044CNVBC13106=SUMIF($A$2:$A$12,"*"&"bc"&"*",$B$2:$B$12)-SUMIF($A$2:$A$12,"*"&"abc"&"*",$B$2:$B$12)=SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12)))*B2:B12)A1:A10數字顯為文本格式時，如何求和=SUMPRODUCT(A1:A10)求和所有本範例所使用的數據都為引用以下綠色區域，並定義為對應的標題 。NameSexAgePositionSalary張無忌男26主角10000韋小寶男16主角13000滅絕女55配角3000周芷若女22主角8000鰲拜男62普通演員2000儀琳女18配角5000岳靈珊女19配角4500令狐沖男27主角15000性空男88普通演員2200東方不敗不詳45主角9000A 求所有演員工資總額71700 =SUM($G$7:$G$16)簡單求和B 求男演員工資總額42200 =SUMIF($D$7:$D$16,"男",$G$7:$G$16)單條件求和.1C 求年齡在20歲以下的演員工資22500 =SUMIF($E$7:$E$16,"<20",$G$7:$G$16)單條件求和.2D 求主角和配角的工資(不是普通演員)67500 =SUMIF($F$7:$F$16,"*角",$G$7:$G$16)單條件求和.3E 求20歲以下女演員工資9500 {=SUM(($D$7:$D$16="女")*($E$7:$E$16<20)*$G$7:$G$16)}多條件求和-同時滿足條件F 求男性或主角的工資59200 {=SUM(IF(($D$7:$D$16="男")+($F$7:$F$16="主角"),$G$7:$G$16))}多條件求和-只須滿足條件之一G 求男性非主角或主角非男性的工資(即除男主角外的男性和主角)g.1 21200 {=SUM(IF(($D$7:$D$16="男")-($F$7:$F$16="主角"),$G$7:$G$16))}g.2 21200 {=SUM(IF(($F$7:$F$16="主角")-($D$7:$D$16="男"),$G$7:$G$16))}多條件求和-只滿足條件之一而不能同時滿足H 啊~~~你不知道什麼是數組函數啊，可是你有時候也要用多條件求和？不要緊，教你用另外的方法：SUBTOTAL求20歲以下女演員工資71700 =SUBTOTAL(9,$G$7:$G$16)現在你看到的還不是最後結果，請按如下操作1、把數據區域設置成可篩選2、把SEX篩選成"=女", 把年齡篩選成<203、你再看上面的公式結果…去掉其中兩個最大值和兩個最小值，再求和請問如何去掉兩個最高分，兩個最低分，剩餘人員的分數求和，例如A1-A7中的7個分 ，去掉兩個最高分，兩個最低分，剩餘人員的分數求和。=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))=TRIMMEAN(A1:A7,4/7)*(7-4)=SUMPRODUCT(LARGE(A1:A7,ROW(A1:A7))*(ROW(1:7)>2)*(ROW(1:7)<6))=SUMPRODUCT((A$1:A$7<LARGE(A$1:A$7,2))*(A$1:A$7>SMALL(A$1:A$7,2))*A$1:A$7)=SUM (A!:A7)-LARGE(A!:A7,1)-LARGE(A!:A7,2)-SMALL(A!:A7,1)-SMALL(A!:A7,2)將此函數橫著使用（A1-G1）=TRIMMEAN(A1:G1,4/7)*(7-4)=SUMPRODUCT(LARGE(A1:G1,COLUMN(A1:G1))*(COLUMN(A:G)>2)*(COLUMN(A:G)<6))去掉兩個最高分、最低分，顯示出被去掉的分數被去掉的分數：最大兩個：=large(data,{1;2})最小兩個：=small(data,{1;2})1、=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以對A列數值自動求和。2、=SUM(INDIRECT("R2C:R[-1]C",FALSE))3、=SUM(INDIRECT("A2:A"&ROW()-1))=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))按字體顏色求和做法：G3={SUM(IF(($A$2:$A$19=E3)*($B$2:$B$19=F3),$C$2:$C$19))}G4:G11公式為G3公式下拖.～～～～～～～～～～～～～～～～～～～～～～～～～～～～～如何分班統計男女人數男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))=SUMPRODUCT(($B$2:$B$446=E3)*($C$2:$C$446=F$1))=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1))合計=COUNTIF($B$2:$B$446,E2)統計數值大於等於80的單元格數目在C17單元格中輸入公式：=COUNTIF(B1:B13,">=80")確認後，即可統計出B1至B13單元格區域中，數值大於等於80的單元格數目。計算出A1里有幾個abcA1: abc-ded-abc-def-abc-ded-ded-abc , 如何計算出A1里有幾個abc公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")有條件統計如何統計當A1<=15時，統計B列中<=8.5的累加值和個數，而>15時不進行統計？個數：=IF(A1>15,"",COUNTIF(B2:B10,"<=8.5"))累加值（求和）：=IF(A1>15,"",SUMIF(B2:B10,"<=8.5"))如何統計各年齡段的數量需分別統計20歲以下、21-30歲、31-40歲、41-50歲、50歲以上年齡段的數量。根據「出生日期」用以下公式，得到「自動顯示年齡」。先將F列的出生日期設置為「1976年5月」格式，在G列公式為：=DATEDIF(F2,TODAY(),"Y") （周歲，自動顯示年齡）=YEAR(TODAY())-YEAR(F2)再根據年齡段：20歲以下、21-30歲、31-40歲、41-50歲、50歲以上，用以下公式，求出不同年齡段人數。在J2公式為：=SUMPRODUCT(($G$2:$G$34>$H1)*($G$2:$G$34<=$H2)*($C$2:$C$34=J$1))｛=SUM(($G$2:$G$34<=VALUE(MID(I2,1,2)))*1)｝或數組公式：｛=SUM(($G$2:$G$34<=VALUE(MID(I3,4,2)))*1)-SUM($J$2:J2)｝如何計算20-50歲的人數?=COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50")=SUMPRODUCT((C3:C17>=20)*(C3:C17<=50))=FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19)｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝如何統計40-50歲的人的個數＝countif(a:a,">40")-countif(a:a,">50")＝SUM(COUNTIF(a:a,">"&{40,50})*{1,-1})數組公式{=sum((a1:a7>40)*(a1:a7<50))}＝SUMPRODUCT((A1:A7>40)*(A1:A7<50))要統計出7歲的女生人數=COUNTIF(D2:D12,D2)=SUMPRODUCT((B2:B12="女")*(D2:D12=7))統計人數=COUNTA(A:A)=COUNTIF(A:A,"> ")如何統計A1:A10，D1:D10中的人數？=COUNTA(A1:A10,D1:D10)如何讓EXCEL自動從頭統計到當前單元格情況如下： C列要根據A列的內容來統計B列的數據，範圍從A1：An，即當A列中An有數據時，Cn自動根據An的值，統計B1：Bn的數據。｛=SUM(INDIRECT("B1:B" & LARGE((A1:A65535<>"")*(ROW(A1:A65535)),1)))｝統計人數建議提建議人員姓名提建議人數建議1王、李、趙、孫、錢、胡6建議2張、王、李、趙、孫、錢、胡7建議3張、王、李、孫、錢、胡6=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1=LEN(SUBSTITUTE(B2,"、",""))統計人數見表：性別年齡男6女35男3男55男21男53.5女55女56男65女45女53男51如何計算20-50歲的人數?=COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50")=SUMPRODUCT((C3:C17>=20)*(C3:C17<=50))=FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19)｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝如何計算男20-50歲的人數?=SUMPRODUCT((B3:B17="男")*(C3:C17>=20)*(C3:C17<=50))求各分數段人數90—100 =COUNTIF(B2:B43,">=90")80—89 =COUNTIF(B2:B43,">=80")-COUNTIF(B2:B43,">=90")70—79 =COUNTIF(B2:B43,">=70")-COUNTIF(B2:B43,">=80")60—69 =COUNTIF(B2:B43,">=60")-COUNTIF(B2:B43,">=70")50—59 =COUNTIF(B2:B43,">=50")-COUNTIF(B2:B43,">=60")有什麼方法統計偶數例如：A1到E1有5個數如何統計著五個數中有幾個是偶數A B C D E F1 50 15 8 11 15 3在F1中的3要用什麼公式能統計出來統計偶數的個數｛=COUNT(1/MOD(A1:E1-1,2))｝｛=Sum(Mod(a1:e1+1,2))｝將偶數轉化成奇數，再求奇數的個數。請在編輯欄中選擇部分公式按F9觀察每一步的計算過程。{=SUM(--((A1:F1)/2=INT((A1:F1)/2)))} 算是一法,長了點=SUMPRODUCT((MOD(A1:E1,2)=0)*1)=SUMPRODUCT(1-MOD(A1:E1,2))如果D2>20那E2就顯示$200、如果D2>30那E2就顯示$300依此類推解答：=INT(D2/10)*100 ，當然，你的單元格格式設置成$格式就可以了。否則用，="$"&INT(D2/10)*100則該單元格成字元型 。當然，你也可以用IF函數，但它有7層的限制。= IF (D2>30, "300",IF(D2>20,"200"))工資統計中的問題問題：表一和表二中的職工姓名相同,但不在同一個位置上。怎樣用公式求出表一中職工在表二中對應的工資、獎金和值班費的總額。要求，不能用表二中先加入一列，然後求和，再用公式導入表一的方法。我想知道能否在表一中用一個公式就可實現，而表二不動。=SUMPRODUCT((表二!$B$3:$B$42=A3)*(表二!$C$3:$E$42)+(表二!$G$3:$G$42=A3)*(表二!$H$3:$J$42))=IF(COUNTIF(表二!$B$3:$B$42,A3),SUMPRODUCT(VLOOKUP(A3,表二!$B$3:$E$42,{2;3;4},)),SUMPRODUCT(VLOOKUP(A3,表二!$G$3:$J$42,{2;3;4},)))=IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0)),SUM(OFFSET(表二!$G$2,MATCH(A3,表二!$G$3:$G$42,0),1,,3)),SUM(OFFSET(表二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,,3)))=IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),SUM(INDIRECT("表二!H"&MATCH(A3,表二!$G$3:$G$42,0)+2&":J"&MATCH(A3,表二!$G$3:$G$42,0)+2)),SUM(INDIRECT("表二!C"&MATCH(A3,表二!$B$3:$B$42,0)+2&":J"&MATCH(A3,表二!$B$3:$B$42,0)+2)))=IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),VLOOKUP(A3,表二!$G$3:$J$42,4,0),VLOOKUP(A3,表二!$B$3:$F$42,4,0))如果我想統計50個數據中大於某個值的數據個數，（這個值是在使用時才輸入某個單元格的），請問用什麼函數。 如數據單元格為A1：E10，值的單元格為A11。1、使用下面的數組公式： {=SUM(IF($A$1:$E$10>$A$11,1))}2、輸入以下函數： =COUNTIF(A1：E10,">"&A11)根據給定的條件，對數據進行合計實例： 姓名 件數 （姓名在B307-B313中；件數在C307-C313中）李六 12王武 50李六 18陳豐 187李六 49王武 135陳豐 1584目的： 對上面三個人的件數分別進行統計分析步驟： 李六的： =SUMIF(B307:B313,B323,C307:C313)王武的： =SUMIF(B307:B313,C323,C307:C313)陳豐的： =SUMIF(B307:B313,D323,C307:C313)姓名： 李六 王武 陳豐（分別在B323、C323、D323單元格中）結果： 79 185 1771十列數據合計成一列=SUM(OFFSET($1,(ROW()-2)*10+1,,10,1))統計漢字字元個數中國 A1中"中國",A2中"人民",A3中是空白,A4中是"幸福",A5,A6中是空白人民258258幸福247大家好中國147函數 結果 說明=SUMPRODUCT(LENB(ASC(A1:A6))-LEN(A1:A6)) 11 僅統計漢字字元個數=SUMPRODUCT(LEN(A1:A6)) 23 如果還混雜有其它字元關於取數購進日期付款期7月5日2007-8-256月5日2007-7-257月18日2007-9-157月26日2007-9-15註：我想在B列的付款期中得到這樣的結果：付款期=（購進日期+45天），但我們的付款期只有每月15和25號，如果按購進日期加上45天後不正好是付款日，那就得再往後延到最近的一個付款日，也就是15或25號。｛=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70)))｝｛=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70),999999))｝｛=MIN(IF((DAY(A2+ROW($45:$67))=15)+(DAY(A2+ROW($45:$67))=25),A2+ROW($45:$67)))｝=IF(DAY(A2+45)<15,TEXT(A2+60-DAY(A2+45),"mm月dd日"),TEXT(A2+70-DAY(A2+45),"mm月dd日"))=DATE(YEAR(A2+45),IF(DAY(A2+45)>25,MONTH(A2+45)+1,MONTH(A2+45)),IF(DAY(A2+45)<=15,15,IF(DAY(A2+45)<=25,25,15)))統計單元格內不為空的格數如下圖，怎麼自動統計單元格內的「√」，而空白的單元格則不計入內？=counta(a2:a31),下拉=countif(a2:a31,"√")=COUNTIF(a2:a31,"<>")自動將銷量前十名的產品代號及銷量填入表中如：產品代號在「B」列，銷量在「C」列=INDIRECT("b"&MATCH(ROW(A1),$D$2:$D$20,0)+1)=INDIRECT("c"&MATCH(ROW(A1),$D$2:$D$20,0)+1)統計最大的連續次數如圖，請問如何編寫公式求出A1到A10單元格中數字4連在一起的次數，本例中答案應為3（A1到A3）和2（A9到A10）。[1] A1到A10單元格中, 數字4連在一起, 最大的連續次數, 公式為 :{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),1)}[2] 次大的連續長次數, 公式為 :{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),2)}3個「不重複」個數統計=SUM(--IF(MATCH(B$2:B$21,B$2:B$21,0)=ROW(B$2:B$21)-1,B$2:B$21>B2))+1=SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1=SUM(--(FREQUENCY(IF(B$2:B$21>B2,B$2:B$21),B$2:B$21)>0))+1在一列有重複的姓名中，如何統計出具體有幾人如果第一個張三在A1單元格，在B1處輸入：=IF(COUNTIF($A$1:A1,A1)>1,"",A1)向下複製即可用數組公式也可以解決呀:假設你要統計A1到A100可以這樣:=sum(1/countif(a1:a100,a1:a100),然後按住crtl,shift,和回車就可以了。計數的問題這個例子主要是計數的問題：共有三列數據，分別統計每列字母的個數、每列有幾個不同的字母，最後把它們分別列出來。對每列字母個數統計，字元用COUNTA()，數字可以用COUNT()和COUNTA()。公式分別為：=COUNT(A2:A12)=COUNTA(B2:B12)=COUNTA(C2:C12)每列不相同的字母，公式分別為：｛=SUM(1/COUNTIF(A$2:A$12,A$2:A$12))｝｛=SUM(1/COUNTIF(B$2:B$12,B$2:B$12))｝｛=SUM(1/COUNTIF(C$2:C$12,C$2:C$12))｝分別列出來，公式分別為：｛=IF(SUM(1/COUNTIF(A$2:A$12,A$2:A$12))>=ROW(A1),INDEX(A$2:A$12,SMALL(IF(ROW(A$2:A$12)-1=MATCH(A$2:A$12,A$2:A$12,0),ROW(A$2:A$12)-1,"0"),ROW(A1))),"END")｝｛=IF(SUM(1/COUNTIF(B$2:B$12,B$2:B$12))>=ROW(B1),INDEX(B$2:B$12,SMALL(IF(ROW(B$2:B$12)-1=MATCH(B$2:B$12,B$2:B$12,0),ROW(B$2:B$12)-1,"0"),ROW(B1))),"END")｝｛=IF(SUM(1/COUNTIF(C$2:C$12,C$2:C$12))>=ROW(C1),INDEX(C$2:C$12,SMALL(IF(ROW(C$2:C$12)-1=MATCH(C$2:C$12,C$2:C$12,0),ROW(C$2:C$12)-1,"0"),ROW(C1))),"END")｝列1列2列31mB2nB3mC1nD1mA2mB3nC2nD1mA2nA1mB對每列字母個數統計:111111每列不相同的字母有:324它們分別是:1mB2nC3ENDDENDAEND如何分班統計男女人數姓名班別性別高健麗1女蔡美燕2女張玉玫3女蔡文文4女陳嬌嬌5女吳振宇1男周婷婷6女肖欣6女梁麗寶5女邱曉雯4女李春梅3女龍玉樺2女阮梅英1女梁光昕2男………班別男女總人數129457423044743304474431437453044746304575男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1))男｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝女｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$G$1))｝男｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝女｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)｝增加d列，輸入公式：=B2&C2，合併數據後再利用countif公式對D列統計。=COUNTIF($B$2:$B$446,E2)在幾百幾千個數據中發現重複項我的意思不是查找功能,那個我會用,比如有幾百個人的名字輸入單元格中,但我面對那麼多名字真無法短時間內看出誰重複了,該如何辦?假設判斷區域為A1:D10,格式/條件格式，選公式(不是數值)，輸入：=COUNTIF($A$1:$D$10,A1)>1然後在格式中設置一個字體或圖案顏色,確定,這樣重複數據就變成了有色單元格。統計互不相同的數據個數例如,在 3 * 3 的區域中統計互不相同的數據個數,1 2 33 2 11 2 0結果應為 4 (4 個互不相同的數據)數組公式=sum(1/countif(a1:c3,a1:c3))還可以公式:=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))多個工作表的單元格合併計算=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)單個單元格中字元統計假設 A1單元格中有數據"sdfsfjksfhweofiefondsfljsdfisdofjei"如何用公式統計出A1單元格中有多個不重複的字元?=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1))數組公式=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),A1)),,1))這個公式只適用單元中的字元為小寫字母,給個通用點的=SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2)))))=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1))數據區包含某一字元的項的總和,該用什麼公式=sumif(a:a,"*"&"某一字元"&"*",數據區)函數如何實現分組編碼對數值進行分組編碼=A2&TEXT(COUNTIF($A$2:A2,A2),"00")【數值取整及進位】取整數函數907.5；1034.2；1500要改變為908；1035；1500公式為：=CEILING(A1,1)907；1034；1500要改變為910；1040；1500公式為：=CEILING(A1,10)如果要保留到百位數，即改變為1000；1100；1500公式為：=CEILING(A1,100)數值取整在單元格中要取整數（只取整數不用考慮四捨五入）用什麼函數呀？例如：10/4隻要顯示2就可以了！要考慮負數的因數呢？例如：(-10/4)要顯示－2而不是－3？怎麼辦？=TRUNC(A1,0)=ROUNDDOWN(A1,0)求餘數的函數比如：A1=28，A2=（A1÷6）的餘數=4，請問這個公式怎麼寫？解答：=MOD(28,6)四捨五入公式=ROUND()=ROUND($B$1*A1,2)=ROUND(B1*A1,2)=round(a1,0)=round(a1,0)*0.95對數字進行四捨五入對於數字進行四捨五入,可以使用INT（取整函數），但由於這個函數的定義是返回實數舍入後的整數值。因此，用INT函數進行四捨五入還是需要一些技巧的，也就是要加上0.5，才能達到取整的目的。公式應寫成：=INT(B2*100+0.5)/100如何實現「見分進元」在我們的工資中，有一項「合同補貼」，只要計算結果出現「分」值就在整數「元」進一位，也就是說3.01元進到4.00元，3.00元不變，整數「元」不變。=IF((A3-INT(A3))>=0.3,IF((A3-INT(A3))>=0.8,1,0.5),0)+INT(A3)＝IF(RIGHT(FIXED(A1,2),2)>B1,TRUNC(A2)+1,A2)說明一下：A1即是要轉換的目標；B2輸入00（文本格式，必須是00這兩個數） 。＝IF(INT(A1)<>A1,INT(A1)+1,A1)=ROUNDUP(A1,0)=CEILING(A9,1)=INT(A9+1)四捨五入如何將Excel 中的數據，希望把千位以下的數進行四捨五入，例如：3245 希望變成3000；3690 希望成為400=ROUND(C6*D6,2)=ROUND(A2*0.001,)*1000=ROUND(A2,-3)=--FIXED(A2,-3)=ROUND(A2/1000,0)*1000如何四捨五入取兩位小數如何四捨五入取兩位小數,如2.145為2.15,0.1449為0.14.=ROUND(A1,2)根據給定的位數，四捨五入指定的數值對整數無效。四捨五入B234的數值，變成小數點後一位。12512.2514 12512.3=ROUND(B23,1)四捨六入=IF(MOD(INT(A1),2)=0,IF(MOD(A1,1)=0.5,INT(A1),INT(A1+0.5)),INT(A1+0.5))=IF(AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE,IF(INT(A1)/2=INT(INT(A1)/2),INT(A1),ROUND(A1,0)),ROUND(A1,0))AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE 判斷是否為一位小數,且是0.5，如果不符合上術要條件,按普通四捨五入法則處理,否則判斷整數部分的奇偶。=IF(RIGHT(A1,1)*1<5,INT(A1),IF(RIGHT(A1,1)*1>5,INT(A1)+1,IF(MOD(ROUND(A1,),2)=0,ROUND(A1,),ROUNDDOWN(A1,))))=IF(ROUNDUP(A1*2,)=A1*2,IF(MOD(ROUND(A1,),2)=1,ROUNDDOWN(A1,),ROUNDUP(A1,)),ROUND(A1,))如何實現2舍3入做工資時，常遇到：3.2元要捨去0.2元變為3.00元,而3.3元要把0.3元入為0.5元變為3.5元.請教,該如何實現?=ROUND(A1*2,0)/2=CEILING(A1,0.5)=IF((A1-INT(A1))<=0.2,INT(A1),IF((A1-INT(A1))<=0.5,INT(A1)+0.5,IF((A1-INT(A1))<=0.7,INT(A1),INT(A1)+1)))=CEILING(A1-0.2,0.5)=FLOOR(A1+0.2,0.5)怎麼設置單元格以千元四捨五入比如輸入123456，顯示出來123，000=CEILING(ROUND(A1/1000,0),1)*1000=round(a1,-3)=mround(A1,1000)ROUND函數的四捨五入不進位的解決方法？計算一：A2=1345.3 B2=1232.4 C3=A2-B2=112.9 D=0.05 E=ROUND(B2*D2,2)=5.64 （計算結果為5.645，此運算沒有進位）。計算二：A2=1225.4 B2=1112.5 C3=A2-B2=112.9 D=0.05 E=ROUND(B2*D2,2)=5.65（計算結果為5.645，此運算進位）。以上兩式中C3結果都為112.9，而為什麼應用ROUND函數後結果卻不一樣。請教高手有什麼函數能保證四捨五入不會出錯。可將C列先變成文本性數據，再進行後面的運算，以達到計算的目的。如：C列可改成C1=TRIM(A1-B1)，以此類推，只要是更改成文本性數據就行。保留一位小數我需要保留一位小數,不管後面是什麼數字,超過5或不超過5,都向前進一位.例如:329.99-->330.00329.84----->329.90329.86----->329.90=roundup(*,2)或=round(a1+0.04,1)如何三舍四入=round(原數值+0.001,2)另類四捨五入我用Excle給別人算帳，由於要對上百家收費，找零卻是個問題。於是我提出四捨五入，收整元。但是領導不同意，要求收取0.5元。例如：某戶為123.41元，就收123.50元;如果是58.72元，就收58.5元。這可難壞了我。經過研究，我發現，可以在設置單元格中，設成分數，以2為分母，可以解決問題。但是列印出來的卻是分數不好看，而且求和也不對。請各位高手給予指點。是這樣的，如果是57.01元，則省去，即收57.00元；如果是57.31元，則進為57.50元；如果是57.70元，也收57.50元；要是57.80元，則收58.00元。假設數據在A1=INT(A1)+IF((A1-INT(A1)<=0.3),0,IF((A1-INT(A1)>0.7),1,0.5))簡化一下：=INT(A1)+0.5*((A1-INT(A1)>0.3)+(A1-INT(A1)>0.7))int函數取整數部分，A1-int(A1)取小數部分，根據你的意思：<=0.3按0算，0.3~0.7(含)按0.5算，0.7~0.99……按+1算則：第一個公式不難理解了簡化公式中：「*((A1-INT(A1)>0.3)+(A1-INT(A1)>0.7))」即(小數部分>0.3)+(小數部分>0.7)我們知道這是省略if的判斷語句，條件為真返回true（也就是1）否在為false（0），那麼如果小數<=0.3，則兩個條件都為0，即整數部分+0.5*0=整數部分，介於0.3~0.7，則為整數部分+0.5*（1+0），大於0.7肯定也大於0.3啦，則為整數部分+0.5*（1+1）。請問，如果是由幾個分表匯總的總表想如此處理，該如何做。例：e112位置=SUM(一庫入庫!G112,二庫入庫!G112,四庫入庫!G112,保健酒基地入庫!G112,下陸倉庫入庫!G112)匯總的結果為100.24，而我要求如果小數為24的話自動視為1累加，否則不便。就是小數為0.24才加1，否則都舍掉？若是：=if（sum公式-int（sum公式）=0.24，int（sum公式）+1，sum公式）想把小數點和後面的數字都去掉，不要四捨五入比如：12.30 變成 12.0045.32 45.0025.38 25.006.54 6.0013.02 13.0059.68 59.0023.62 23.00=Rounddown(A1,0)你要把A1換成你要轉換的那個單元格啊，然後拖動就可以了！我那裡用的那個A1隻是告訴你一個例子而已，你要根據你的實際情況來修改一下才能用的。=INT(A1)=TRUNC(A1,0)求真正的四捨五入後的數請教如何在Excel中，求「金額合計」（小數點後二位數）時，所取的數值應是所求單元格中寫的數字（四捨五入後的數字），而不是（四捨五入前）的數字。因為只有這樣行和列及關聯的工作表才能對得上，例如：表上的數值分別是：(1.802/2=0.901)0.90(A1); (1.604/2=0.802)0.80(A2);(1.406/2=0.703)0.70(A3);（因取小數點後二位）。合計數(A4)表中自己計算和顯示是：(0.901+0.802+0.703=2.406)2.41（四捨五入後的數值）。但照表中的數值人工計算卻是：(0.9+0.8+0.7=)2.4,有矛盾，還有許多例子，故請教各高手，如何設置公式，使得人工計算結果同表中一致。請指教。十分感謝！工具》選項》重新計算》以顯示精度為準 前打鉤也可以用函數 ROUND() 使結果四捨五入 。如ROUND（算式，2）代表保留兩位小數，如ROUND（算式，1）代表保留一位小數。小數點進位小數點進位如何把1.4進成2或1.3進成2=Ceiling(A1,1)=Roundup(A1,0)=INT(A1+0.9)=int(a1)+1如何把1.4進成2，而1.2不進位=ROUND(A1+0.1,0)個位數歸0或者歸5A*B後想得到C的結果值，用什麼函數比較好A B C(想得到的數值)320 1.1 3551140 1.2 137050 1.3 6516 1.4 25=FLOOR(A1*B1+5*(MOD(A1*B1,5)<>0),5)=CEILING(A1*B1,5)【大小值或中間值】求平均值如在列中有一組數字：10、7、9、27、2=AVERAGE(A2:A6) 上面數字的平均值為11行公式=AVERAGE(B2:D2)如何實現求平均值時只對不等於零的數求均值？＝AVERAGE (IF(A1:A5>0,A1:A5))平均分的問題假設一個班有60人，要統計出各個學科排名前50的學生的平均分，用公式應該如何寫？如果用排序再來算的話很麻煩，能不能直接用公式找出前50名進行計算？{=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:50"))))}怎樣求最大值（最小值或中間值）=IF(A2="","",MAX(OFFSET(C2,,,MIN(IF(A3:$A$15<>"",ROW(3:$15),15))-MAX(($A$2:A2<>"")*ROW($2:2)))))=IF(A2="","",MAX((LOOKUP(ROW($A$2:$A$14),IF($A$2:$A$14<>"",ROW($A$2:$A$14)),$A$2:$A$14)=A2)*$C$2:$C$14))=IF(A2="","",LOOKUP(2,1/FIND(A2,$B$2:$B$1000),$C$2:$C$1000))=IF(A2="","",MAX(IF(ISNUMBER(FIND(A2,$B$2:$B$1000)),$C$2:$C$1000)))平均數怎麼弄如在列中有一組數字：10、7、9、27、2公式為：=AVERAGE(A2:A6) 上面數字的平均值為11=AVERAGE(A2:A6,5)上面數字與5的平均值為10去掉其中兩個最大值和兩個最小值的公式我要將一行數據進行處理。要去掉其中兩個最大值和兩個最小值，不知道怎樣運用公式，應該是：=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)這個只能減去1個最大和1個最小值，不符合題意。可用下面的公式。=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))去一行最高分最低分求平均值去一行中一個最高分和一個最低分求平均值公式為：=(SUM(A5:E5)-MAX(A5:E5)-MIN(A5:E5))/(COUNTIF(A5:E5,">0")-2)但另用TRIMMEAN ()函數較好。=TRIMMEAN($A$5:$E$5,2/COUNT($A$5:$E$5))為需要進行整理並求平均值的數組或數值區域。TRIMMEAN(array,percent)為計算時所要除去的數據點的比例，例如，如果 percent = 0.2，在 20 個數據點的集合中，就要除去 4 個數據點 (20 x 0.2)：頭部除去 2 個，尾部除去 2 個。用活了TRIMMEAN函數，這個問題易如反掌。在9個數值中去掉最高與最低然後求平均值假設9個數值所在的區域為A1：A9=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/7=TRIMMEAN(A1:A9,2/COUNTA(A1:A9))=TRIMMEAN(A1:A9,2/9)｛=AVERAGE(SMALL(A1:A9,ROW(2:8)))｝=ROUND((SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2),3)=TRIMMEAN(A1:A9,0.286)求最大值（n列）{=MAX(($A$2:$A$16=$D$2)*($B$2:$B$16))}｛=LARGE(IF(FREQUENCY(N3:AT3,N3:AT3),TRANSPOSE(N3:AT3)),ROW(A1))｝｛=LARGE(IF(FREQUENCY(TRANSPOSE(N3:AT3),TRANSPOSE(N3:AT3)),(N3:AT3)),ROW(A1))｝如何實現求平均值時只對不等於零的數求均值？= TRIMMEAN (IF(A1:A5>0,A1:A5))得到單元格編號組中最大的數或最小的數對字元格式的數字不起作用。=MAX(B16:B25)=MIN(B16:B25) （得到最小的數的公式）標記出3個最大最小值=RANK(B4,$B4:$Q4)+COUNTIF($B4:B4,B4)<=4=RANK(B4,$B4:$Q4,2)+COUNTIF(B4:$Q4,B4)<=4=(COUNTIF($B3:$Q3,">"&B3)+COUNTIF($B3:B3,B3))<=3=(COUNTIF($B3:$Q3,">"&B3)+COUNTIF(B3:$B3,B3))>COUNT($B3:$Q3)-3=SMALL(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,{1,2,3})=LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,{1,2,3})=RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1<=3=RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1>COUNT($B8:$Q8)-3＝C4+COLUMN(C4)/10000>LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,4)取前五名，後五名的方法{=LARGE(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}{=SMALL(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}{=LARGE(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))}{=SMALL(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))}=LARGE(B$2:B$57,ROW(A1))=SMALL(B$2:B$57,ROW(A1)+COUNTIF(B$2:B$57,0))=LARGE(D$2:D$57,ROW(A1))=SMALL($D$2:$D$57,5-MOD(ROW(A5),5))如何用公式求出最大值所在的行？如A1：A10中有10個數，怎麼求出最大的數在哪個單元格？=MATCH(LARGE(A1:A10,1),A1:A10,0)=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1)=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1){=ADDRESS(MATCH(MAX(LEN(A1:A10)),LEN(A1:A10),FALSE),1)}{=ADDRESS(SUM(($A$1:$A$10=MAX($A$1:$A$10))*(ROW($A$1:$A$10))),SUM(($A$1:$A$10=MAX($A$1:$A$10))*(COLUMN($A$1:$A$10))))}如有多個最大值的話呢？如何一一顯示其所在的單元格？{=IF(ROW(1:1)<=COUNTIF($A$1:$A$100,MAX($A$1:$A$100)),ADDRESS(LARGE(IF($A$1:$A$100=MAX($A$1:$A$100),ROW($A$1:$A$100)),ROW(1:1)),1),"")}求多個最高分語文成績有多個最高分,如何用公式的方法把他們抽出來(動態)?B15=INDEX(A:A,SMALL(IF(B$2:B$10=MAX(B$2:B$10),ROW($2:$10),65536),ROW(1:1)))&""數組公式，按下Ctrl+Shift+Enter結束。如果增加一個條件,就是在姓名前加一個類別,例如前5個人是A類的,後4個是B類的,請分類找出A類和B類的對應姓名的最高分=INDEX(B:B,SMALL(IF(C$2:C$10=MAX(IF($A$2:$A$10="A",$C$2:C$10)),ROW($2:$10),IF(C$2:C$10=MAX(IF($A$2:$A$10="B",$C$2:$C$10)),ROW($2:$10),65536)),ROW(1:1)))&""如何求多條件的平均值應如何求下表中1月份400g重量的平均值月份 規格 重量1 400g 4011 400g 4032 400g 4022 400g 4041 200g 2011 200g 2032 200g 202試試這個行不行=SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"),($C$4:$C$10))/SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"))比較土的辦法{=SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),C1:C7,0))/SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),1,0))}數組公式：{=AVERAGE(IF(B2:B8="400g")*(A2:A8=1),(C2:C8),""))}另一個數組公式試試：=Average(if((a1:a10=1)*(b1:b10="400g"),c1:c10))=SUMIF(B1:B7,B1,C1:C7)/COUNTIF(B1:B7,B1) 這個也可以想求出第三大之數值如A1：A4分別為1,2,2,3.想求出第三大之數值"1",應如何設公式。=large(if(frequency(a1:a4,a1:a4),a1:a4),3)數組公式的解法=LARGE((MATCH(A1:A10,A1:A10,)=ROW(1:10))*A1:A10,3)【查詢和查找引用】查找順序公式=LOOKUP(2,1/(A1:A20<>0),A1:A20)=MATCH(7,A1:A20)=VLOOKUP(7,A1:B11,2)怎樣實現精確查詢用VLOOKUP=VLOOKUP(B11,B3:F7,4,FALSE)用LOOKUP=LOOKUP(B11,B3:B7,E3:E7)用MATCH+INDEX=INDEX(E3:E7,MATCH(B11,B3:B7,0))用INDIRECT+MATCH=INDIRECT("E"&MATCH(B11,B3:B7,0)+2)用OFFSET+MATCH=OFFSET(E3,MATCH(B11,B3:B7,0)-1,0)用INDIRECT+ADDRESS+MATCH=INDIRECT(ADDRESS(MATCH(B11,B4:B7,0)+3,5))用數組公式=INDEX(E1:E7,MAX(IF((B4:B7=B11),ROW(B4:B7),0)))查找及引用如何查找並引用B2單元格中所顯示日期當日的相應代碼的值。B3=IF(COUNTIF($E$3:$E$20,A3),VLOOKUP($A3,$E$2:$M$20,MATCH(B$2,$F$2:$M$2,)+1,),"")查找函數的應用我想在A5輸入表的名稱，B5自動跳出該表中B列的最後一個有效數值，請問B5的公式該如何設定？=LOOKUP(9E+307,INDIRECT(A5&"!"&"B:B"))B2 =IF(A2="","",LOOKUP(9E+307,INDIRECT(A2&"!B:B")))怎麼能方便的判斷某個單元格中包含多少個指定的字元？例：A1 中是「ASAFAG」，我希望計算出A1裡面有多少個「A」......=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))如何用查找函數一、要求: 利用公式從左表中查詢相應的地區，結果放在H14單元格=VLOOKUP(G14,IF({1,0},D14:D18,C14:C18),2,)h14=OFFSET(C14,MATCH(G14,D14:D18,0)-1,,,)H14 =INDIRECT("c"&MATCH(G14,D:D,))二、要求: 根據C25單元格的商品名稱，查找該商品的最新單價，即該商品最後一條記錄的單價（結果放在D25單元格）。用數組公式：=INDIRECT("G"&MAX((D14:D22=C25)*ROW(D14:D22)))D25 =LOOKUP(2,1/(D14:D22=C25),G14:G22)日期查找的問題我有一個日期比如:2007/02/12,我想知道它減去一個固定天數比如6後,最接近它的一個星期四(只能提前)是多少號2007/02/12的答案應該是2007/02/01而不是2007/02/08日期在A1處，B1處輸入：=MAX((WEEKDAY(A1-6-{1,2,3,4,5,6,7},2)=4)*(A1-6-{1,2,3,4,5,6,7}))A1=2007/02/12B1, 輸入公式 :=A1-6-MOD(WEEKDAY(A1-6,2)+3,7)如何自動查找相同單元格內容=SUMPRODUCT(($D$2:$D$15=A21)*($E$2:$E$15))=IF(ISERROR(VLOOKUP(A6,$D$2:$E$15,2,0)),0,VLOOKUP(A6,$D$2:$E$15,2,0))查找函數D3 =LOOKUP(2,1/(($G$3:$G$14=B3)*($H$3:$H$14=C3)),$I$3:$I$14)=IF(ISERROR(VLOOKUP(A14,A:B:D:F,2,FALSE)),"",VLOOKUP(A14,A:B:D:F,2,FALSE))=IF(ISERROR(VLOOKUP(C2,k!B2:Z2189,2,FALSE)),"",VLOOKUP(C2,k!B2:Z2189,2,FALSE))怎樣對號入座（查找）=VLOOKUP(D2,$A$1:$B$5,2,FALSE)=INDEX($B$2:$B$5,MATCH(D2,$A$2:$A$5,0))=OFFSET($A$1,MATCH(D2,$A$2:$A$5,0),1)=VLOOKUP(D2,$A$1:$B$16,2,)=VLOOKUP(D2,IF({1,0},$A$1:$A$9,$B$1:$B$9),2,)=LOOKUP(2,1/($A$1:$A$10=D2),$B$1:$B$10)一個文本查找的問題如何在一個單元格中,統計某個字元出現的次數,例如:單元格A1中填有:張三/李四/王五",如何通過公式來計算此單元格中共填有幾個人姓名,每個人姓名之間用"/"符號分開,煩請相告.=LEN(A1)-LEN(SUBSTITUTE(A1,"/",))+1查找一列中最後一個數值我想用公式知道,另一個表中"A"列最下面一個數是多少,就行了.用不定值的,因為還有數據有增加,=LOOKUP(9E+307,Sheet2!A:A)——最後一個數值=LOOKUP(REPT("座",255),Sheet2!A:A)——最後一個文本或=INDEX(Sheet2!A:A,MATCH(9E+307,Sheet2!A:A))=INDEX(Sheet2!A:A,MATCH("*",Sheet2!A:A,-1))＝Match(rept("座",255),sheet2!A:A)查找重複字元兩組數值A B1245689 0134578查找單元格Ａ和Ｂ里重複及不重複的字元正確答案：重複字元－1458不重複字元－023679以下公式對數字有效：重複數字：=IF(COUNT(FIND(0,A1:B1))=2,0,"")&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($1:$9),A1))+ISNUMBER(FIND(ROW($1:$9),B1))=2,ROW($1:$9)*10^(10-ROW($1:$9)))),0,)不重複數字：=IF(COUNT(FIND(0,A1:B1))=1,0,"")&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($1:$9),A1))+ISNUMBER(FIND(ROW($1:$9),B1))=1,ROW($1:$9)*10^(10-ROW($1:$9)))),0,)都是數組公式，按Ctrl+shift+enter結束。重複數字：=IF(COUNT(FIND(0,A1:B1))=2,0,"")&SUBSTITUTE(SUM(IF(MMULT(COUNTIF(OFFSET(A1,,{0,1},),"*"&ROW($1:$9)&"*"),{1;1})>1,ROW($1:$9)*10^(9-ROW($1:$9)))),0,)不重複數字：=IF(COUNT(FIND(0,A1:B1))=1,0,"")&SUBSTITUTE(SUM(IF(MMULT(COUNTIF(OFFSET(A1,,{0,1},),"*"&ROW($1:$9)&"*"),{1;1})<2,ROW($1:$9)*10^(9-ROW($1:$9)))),0,)請教查找替換問題把表1中字元在4個以上的欄位(含4個)查找出來,替換成表2中的人名,最好在原位置修改,或者在新的一列上生成也成,只要其他內容保持不變並按原來的順序即可。=IF(LEN(A2)<4,A2,OFFSET(表2!$A$1,SUMPRODUCT(--(LEN($A$2:A2)>3))-1,))=IF(LEN(A2)<4,A2,INDEX(表2!A:A,COUNTIF($A$2:A2,"="&"????*")))IF函數替換法總結條件說明：小於10返回500，小於20返回800，小於30返回1100，小於40返回1400，大於40返回1700類似於以上要求，大家最先想到IF函數，這也本屬IF專長。但用IF一般要長長的公式，且計算較慢。現總結一下IF之替換公式，望能拋磚引玉，在我的倡導下各位提供更完善的方案。其中部分公式通用，部分公式有局限性，請看說明。(前18個條件公式，根據速度，排名如下)1=SMALL({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1))2=INDEX({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1))3=CHOOSE(COUNTIF($A$9:$A$13,"<="&A1),500,800,1100,1400,1700)4=LOOKUP(A1,{0,10,20,30,40},{500,800,1100,1400,1700})5=MIN(4,INT(A1/10))*300+5006=MATCH(A1,{0,10,20,30,40})*300+2007=MIN(40,FLOOR(A1,10))*30+5008=HLOOKUP(A1,{0,10,20,30,40;500,800,1100,1400,1700},2,1)9=200+SUM((A1>={0;10;20;30;40})*300)10=FREQUENCY({0,10,20,30,40},A1)*300+20011=MAX((A1>={0,10,20,30,40})*{500,800,1100,1400,1700})12=INDEX({500;800;1100;1400;1700},MATCH(A1,{0;10;20;30;40},1))13=CHOOSE(MATCH(A1,{0;10;20;30;40},1),500,800,1100,1400,1700)14=500+SUM(IF(A1>={10,20,30,40},{300,300,300,300}))15=IF(A1<10,500,IF(A1<20,800,IF(A1<30,1100,IF(A1<40,1400,1700))))16=CHOOSE(SUM((A1>={0;10;20;30;40})*1),500,800,1100,1400,1700)17=MAX((INT(A1/({10;20;30;40}))>0)*(ROW($1:$4)*300))+50018=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,1100,1400,1700)新增公式：19=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,1100,1400,1700)20{=MAX((INT(A1/(ROW($1:$4)*10))>0)*(ROW($1:$4)*300))+500}21=500+MIN(4,MAX(0,INT(A1/10)))*30022MAX((A1>={0,10,20,30,40})*{500,800,1100,1400,1700})23=MATCH(A1,{0,10,20,30,40})*300+20024=MIN(40,FLOOR(A1,10))*30+50025=FREQUENCY(ROW($1:$5)*10-10,A1)*300+200查找的函數（查找末位片語）（數組公式：）=REPLACE(A2,1,MAX(IF(MID(A2,ROW($1:$100),1)=" ",ROW($1:$100))),)=REPLACE(A2,1,LOOKUP(1,0/(MID(" "&A2,ROW($1:$100),1)=" "),ROW($1:$100))-1,)（數組公式：）=RIGHT(A2,MATCH(1,FIND(" ",RIGHT(" "&A2,ROW($1:$100))),)-1)=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)) （好）其實這個公式的思路, 是可以變化的，改變REPT( )中的數值, 可以返回, 指定空格位置後的數據，比如：A1=一 二 三 四 五 六 七 八 九10個普通公式, 分別為 :1=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",100)),100)) 返回第0空格位置後的數據>一 二 三 四 五 六 七 八 九2=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100)) 返回第8 空格位置後的數據>九3=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",40)),100)) 返回第7 空格位置後的數據>八 九4=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),100)) 返回第6 空格位置後的數據>七 八 九5=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",23)),100)) 返回第5空格位置後的數據>六 七 八 九6=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",18)),100)) 返回第4 空格位置後的數據>五 六 七 八 九7=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",14)),100)) 返回第3 空格位置後的數據>四 五 六 七 八 九8=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",12)),100)) 返回第2 空格位置後的數據>三 四 五 六 七 八 九9=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",11)),100)) 返回第1 空格位置後的數據>二 三 四 五 六 七 八 九10=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),100)) 返回第0空格位置後的數據>一 二 三 四 五 六 七 八 九怎樣從原始數據中自動獲取最後一個數據原始數據a 12b 1221c 12d 33a 33 自動獲取a 432 a 432b 33 b 33c 22 c 44c 44 d 23d 23公式=LOOKUP(1,0/($A$1:$A$100=C2),$B$1:$B$100)兩列數據查找相同值對應的位置=MATCH(B1,A:A，0)查找數據公式兩個(基本查找函數為VLOOKUP,MATCH)（1）、根據符合行列兩個條件查找對應結果=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)（2）、根據符合兩列數據查找對應結果(為數組公式)=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0))【輸入數據的技巧】談談Excel輸入的技巧在Excel工作表的單元格中，可以使用兩種最基本的數據格式：常數和公式。常數是指文字、數字、日期和時間等數據，還可以包括邏輯值和錯誤值，每種數據都有它特定的格式和輸入方法，為了使用戶對輸入數據有一個明確的認識，有必要來介紹一下在Excel中輸入各種類型數據的方法和技巧。【1】輸入文本Excel單元格中的文本包括任何中西文文字或字母以及數字、空格和非數字字元的組合，每個單元格中最多可容納32000個字元數。雖然在Excel中輸入文本和在其它應用程序中沒有什麼本質區別，但是還是有一些差異，比如我們在Word、PowerPoint的表格中，當在單元格中輸入文本後，按回車鍵表示一個段落的結束，游標會自動移到本單元格中下一段落的開頭，在Excel的單元格中輸入文本時，按一下回車鍵卻表示結束當前單元格的輸入，游標會自動移到當前單元格的下一個單元格，出現這種情況時，如果你是想在單元格中分行，則必須在單元格中輸入硬回車，即按住Alt鍵的同時按回車鍵。【2】輸入分數幾乎在所有的文檔中，分數格式通常用一道斜杠來分界分子與分母，其格式為「分子/分母」，在Excel中日期的輸入方法也是用斜杠來區分年月日的，比如在單元格中輸入「1/2」，按回車鍵則顯示「1月2日」，為了避免將輸入的分數與日期混淆，我們在單元格中輸入分數時，要在分數前輸入「0」（零）以示區別，並且在「0」和分子之間要有一個空格隔開，比如我們在輸入1/2時，則應該輸入「0 1/2」。如果在單元格中輸入「8 1/2」，則在單元格中顯示「8 1/2」，而在編輯欄中顯示「8.5」。【3】輸入負數在單元格中輸入負數時，可在負數前輸入「-」作標識，也可將數字置在（）括弧內來標識，比如在單元格中輸入「（88）」，按一下回車鍵，則會自動顯示為「-88」。【4】輸入小數在輸入小數時，用戶可以向平常一樣使用小數點，還可以利用逗號分隔千位、百萬位等，當輸入帶有逗號的數字時，在編輯欄並不顯示出來，而只在單元格中顯示。當你需要輸入大量帶有固定小數位的數字或帶有固定位數的以「0」字元串結尾的數字時，可以採用下面的方法：選擇「工具」、「選項」命令，打開「選項」對話框，單擊「編輯」標籤，選中「自動設置小數點」複選框，並在「位數」微調框中輸入或選擇要顯示在小數點右面的位數，如果要在輸入比較大的數字後自動添零，可指定一個負數值作為要添加的零的個數，比如要在單元格中輸入「88」後自動添加3個零，變成「88 000」，就在「位數」微調框中輸入「-3」，相反，如果要在輸入「88」後自動添加3位小數，變成「0.088」，則要在「位數」微調框中輸入「3」。另外，在完成輸入帶有小數位或結尾零字元串的數字後，應清除對「自動設置小數點」符選框的選定，以免影響後邊的輸入；如果只是要暫時取消在「自動設置小數點」中設置的選項，可以在輸入數據時自帶小數點。【5】輸入貨幣值Excel幾乎支持所有的貨幣值，如人民幣（￥）、英鎊（￡）等。歐元出台以後，Excel2000完全支持顯示、輸入和列印歐元貨幣符號。用戶可以很方便地在單元格中輸入各種貨幣值，Excel會自動套用貨幣格式，在單元格中顯示出來，如果用要輸入人民幣符號，可以按住Alt鍵，然後再數字小鍵盤上按「0165」即可。快速輸入歐元符號 先按下Alt鍵，然後利用右面的數字鍵盤(俗稱小鍵盤)鍵入0128這4個數字，鬆開Alt鍵，就可以輸入歐元符號。【6】輸入日期Excel是將日期和時間視為數字處理的，它能夠識別出大部分用普通表示方法輸入的日期和時間格式。用戶可以用多種格式來輸入一個日期，可以用斜杠「/」或者「-」來分隔日期中的年、月、日部分。比如要輸入「2001年12月1日」，可以在單元各種輸入「2001/12/1」或者「2001-12-1」。如果要在單元格中插入當前日期，可以按鍵盤上的Ctrl+;組合鍵。【7】輸入時間在Excel中輸入時間時，用戶可以按24小時制輸入，也可以按12小時制輸入，這兩種輸入的表示方法是不同的，比如要輸入下午2時30分38秒，用24小時制輸入格式為：2:30:38，而用12小時制輸入時間格式為：2:30:38 p，注意字母「p」和時間之間有一個空格。如果要在單元格中插入當前時間，則按Ctrl+Shift+;鍵。【8】輸入比值如何在excel中輸入比值(1:3)，單元格式設置為文本即可。先設成文本格式，再輸入。【9】輸入0開頭在Excel單元格中，輸入一個以「0」開頭的數據後，往往在顯示時會自動把「0」消除掉。要保留數字開頭的「0」，其實是非常簡單的。只要在輸入數據前先輸入一個「『 」（單引號），這樣跟在後面的以「0」開頭的數字的「0」就不會被系統自動消除。還有更好的辦法，就是設置單元格格式為自定義「000000#「，0的個數依編碼長度定，這樣可以進行數值運算。如果這帶0開頭的字串本身是文本，或者是不定長的，那乾脆先設該部分單元格格式為文本好了。另外還可用英語逗號開頭再輸就可以了。【10】輸入百分數在單元格中輸入一個百分數（如60％），按下回車鍵後顯示的卻是0.6。出現這種情況的原因是因為所輸入單元格的數據被強制定義成數值類型了，只要更改其類型為「常規」或「百分數」即可。操作如下：選擇該單元格，然後單擊「格式」菜單中的「單元格」命令，在彈出的對話框中選擇「數字」選項卡，再在「分類」欄中把其類型改為上述類型中的一種即可。如果我要求為負值的百分數自動顯示成紅色，可以再利用條件格式進行設置，格式-條件格式-單元格數值-小於-0(格式-圖案-紅色)，選中要設置的單元格-----ctrl+1---分類---自定義---輸入 0.00%;[紅色]-0.00%【11】勾怎麼輸入1、按住ALT鍵輸入41420後放開ALT鍵√2、首先選擇要插入「√」的單元格，在字體下拉列表中選擇「Marlett」字體，輸入a或b，即在單元格中插入了「√」。【12】輸入無序數據在Excel數據表中，我們經常要輸入大批量的數據，如學生的學籍號、身份證號等。這些數值一般都無規則，不能用「填充序列」的方法來完成。通過觀察後我們發現，這些數據至少前幾位是相同的，只有後面的幾位數值不同。通過下面的設置，我們只要輸入後面幾位不同的數據，前面相同的部分由系統自動添加，這樣就大大減少了輸入量。例如以學籍號為例，假設由8位數值組成，前4位相同，均為0301，後4位為不規則數字，如學籍號為03010056、03011369等。操作步驟如下：選中學籍號欄位所在的列，單擊「格式」菜單中的「單元格」命令，在「分類」中選擇「自定義」，在「類型」文本框中輸入「03010000」。不同的4位數字全部用「0」來表示，有幾位不同就加入幾個「0」，[確定]退出後，輸入「56」按回車鍵，便得到了「03010056」，輸入「1369」按回車便得到了「03011369」。身份證號的輸入與此類似。【13】快速輸入拼音選中已輸入漢字的單元格，然後單擊「格式→拼音信息→顯示或隱藏」命令，選中的單元格會自動變高，再單擊「格式→拼音信息→編輯」命令，即可在漢字上方輸入拼音。單擊「格式→拼音信息→設置」命令，可以修改漢字與拼音的對齊關係。【14】快速輸入自定義短語使用該功能可以把經常使用的文字定義為一條短語，當輸入該條短語時，「自動更正」便會將它更換成所定義的文字。定義「自動更正」項目的方法如下:單擊「工具→自動更正選項」命令，在彈出的「自動更正」對話框中的「替換」框中鍵入短語，如「電腦報」，在「替換為」框中鍵入要替換的內容，如「電腦報編輯部」，單擊「添加」按鈕，將該項目添加到項目列表中，單擊「確定」退出。以後只要輸入「電腦報」，則「電腦報編輯部」這個短語就會輸到表格中。具體步驟：1.執行「工具→自動更正」命令，打開「自動更正」對話框。2.在「替換」下面的方框中輸入「pcw」（也可以是其他字元，「pcw」用小寫），在「替換為」下面的方框中輸入「《電腦報》」，再單擊「添加」和「確定」按鈕。3.以後如果需要輸入上述文本時，只要輸入「pcw」字元此時可以不考慮「pcw」的大小寫，然後確認一下就成了。【15】填充條紋如果想在工作簿中加入漂亮的橫條紋，可以利用對齊方式中的填充功能。先在一單元格內填入「*」或「~」等符號，然後單擊此單元格，向右拖動滑鼠，選中橫向若干單元格，單擊「格式」菜單，選中「單元格」命令，在彈出的「單元格格式」菜單中，選擇「對齊」選項卡，在水平對齊下拉列表中選擇「填充」，單擊「確定」按鈕。【16】上下標的輸入在單元格內輸入如103類的帶上標(下標)的字元的步驟:(1)按文本方式輸入數字(包括上下標)，如103鍵入"103；(2)用滑鼠在編輯欄中選定將設為上標(下標)的字元，上例中應選定3；(3)選中格式菜單單元格命令，產生[單元格格式]對話框；(4)在[字體]標籤中選中上標(下標)複選框，再確定。【17】文本類型的數字輸入證件號碼、電話號碼、數字標碩等需要將數字當成文本輸入。常用兩種方法:一是在輸入第一個字元前，鍵入單引號"""；二是先鍵入等號"="，並在數字前後加上雙引號"""。請參考以下例子:鍵入"027，單元格中顯示027；鍵入="001"，單元格申顯示001；鍵入="""3501"""，單元格中顯示"3501"。(前後加上三個雙撇號是為了在單元格中顯示一對雙引號)；鍵入="9"30""，單元格中顯示9"30"；【18】多張工作表中輸入相同的內容幾個工作表中同一位置填入同一數據時，可以選中一張工作表，然後按住Ctrl鍵，再單擊窗口左下角的Sheet1、Sheet2......來直接選擇需要輸入相同內容的多個工作表，接著在其中的任意一個工作表中輸入這些相同的數據，此時這些數據會自動出現在選中的其它工作表之中。輸入完畢之後，再次按下鍵盤上的Ctrl鍵，然後使用滑鼠左鍵單擊所選擇的多個工作表，解除這些工作表的聯繫，否則在一張表單中輸入的數據會接著出現在選中的其它工作表內。【19】不連續單元格填充同一數據選中一個單元格，按住Ctrl鍵，用滑鼠單擊其他單元格，就將這些單元格全部都選中了。在編輯區中輸入數據，然後按住Ctrl鍵，同時敲一下回車，在所有選中的單元格中都出現了這一數據。【20】利用Ctrl＋*選取文本如果一個工作表中有很多數據表格時，可以通過選定表格中某個單元格，然後按下Ctrl＋*鍵可選定整個表格。Ctrl＋*選定的區域為：根據選定單元格向四周輻射所涉及到的有數據單元格的最大區域。這樣我們可以方便準確地選取數據表格，並能有效避免使用拖動滑鼠方法選取較大單元格區域時屏幕的亂滾現象。【21】快速清除單元格的內容如果要刪除內容的單元格中的內容和它的格式和批註，就不能簡單地應用選定該單元格，然後按Delete鍵的方法了。要徹底清除單元格,可用以下方法：選定想要清除的單元格或單元格範圍；單擊「編輯」菜單中「清除」項中的「全部」命令，這些單元格就恢復了本來面目。【22】在Excel中插入斜箭頭經常使用Excel的朋友會遇到這樣一個問題：在Excel中想插入斜箭頭，但Excel本身沒有這樣的功能，是不是就沒有其他辦法了呢？答案是否定的。我們要想在Excel中插入斜箭頭，首先我們在要插入斜箭頭的單元格里調整好大小（為了方便插入斜箭頭），然後打開Word，插入一個表格（一個框即可），調整好表格大小，在這個框里插入一個斜箭頭，然後把這個框複製到Excel要插入斜箭頭的單元格中，再調整大小，便大功告成。我們在調整斜箭頭的時候，可以先把複製過來的斜箭頭打散，方法是：選中斜箭頭，按右鍵，「取消組合」，注意調整好大小後，調整斜線使之適合單元格，方法是：點擊右鍵，選擇「編輯頂點」，這時線條兩端會變成兩個小黑點，我們可以自由編輯線條了。至於文字，選中文本框，移動位置，直至適合位置即可。我們趕快試試吧。【23】其它輸入補充※在同一單元格內連續輸入多個測試值一般情況下，當我們在單元格內輸入內容後按回車鍵，滑鼠就會自動移到下一單元格，如果我們需要在某個單元格內連續輸入多個測試值以查看引用此單元格的其他單元格的動態效果時，就需要進行以下操作:單擊「工具→選項→編輯」，取消選中「按Enter鍵後移動」選項（），從而實現在同一單元格內輸人多個測試值。※輸入數字、文字、日期或時間單擊需要輸入數據的單元格，鍵入數據並按Enter或Tab鍵即可。如果是時間，用斜杠或減號分隔日期的年、月、日部分，例如，可以鍵入 9/5/96 或 Jun-96。如果按12小時制輸入時間，請在時間數字後空一格，並鍵入字母 a(上午) 或 p(下午)，例如，9:00 p。否則，如果只輸入時間數字，Excel將按 AM(上午)處理。※將單元格區域從公式轉換成數值有時，你可能需要將某個單元格區域中的公式轉換成數值，常規方法是使用「選擇性粘貼」中的「數值」選項來轉換數據。其實，有更簡便的方法:首先選取包含公式的單元格區域，按住滑鼠右鍵將此區域沿任何方向拖動一小段距離(不鬆開滑鼠)，然後再把它拖回去，在原來單元格區域的位置鬆開滑鼠 (此時，單元格區域邊框變花了)，從出現的快捷菜單中選擇「僅複製數值」。※快速輸入有序文本 如果你經常需要輸入一些有規律的序列文本，如數字（1、2……）、日期（1日、2日……）等，可以利用下面的方法來實現其快速輸入:先在需要輸入序列文本的第1、第2兩個單元格中輸入該文本的前兩個元素（如「甲、乙」）。同時選中上述兩個單元格，將滑鼠移至第2個單元格的右下角成細十字線狀時（我們通常稱其為「填充柄」），按住滑鼠左鍵向後（或向下）拖拉至需要填入該序列的最後一個單元格後，鬆開左鍵，則該序列的後續元素（如「丙、丁、戊……」）依序自動填入相應的單元格中。※輸入有規律數字 有時需要輸入一些不是成自然遞增的數值（如等比序列:2、4、8……），我們可以用右鍵拖拉的方法來完成:先在第1、第2兩個單元格中輸入該序列的前兩個數值（2、4）。同時選中上述兩個單元格，將滑鼠移至第2個單元格的右下角成細十字線狀時，按住右鍵向後（或向下）拖拉至該序列的最後一個單元格，鬆開右鍵，此時會彈出一個菜單（），選「等比序列」選項，則該序列（2、4、8、16……）及其「單元格格式」分別輸入相應的單元格中（如果選「等差序列」，則輸入2、4、6、8……）。※巧妙輸入常用數據 有時我們需要輸入一些數據，如單位職工名單，有的職工姓名中生僻的字輸入極為困難，如果我們一次性定義好「職工姓名序列」，以後輸入就快多了。具體方法如下:將職工姓名輸入連續的單元格中，並選中它們，單擊「工具→選項」命令打開「選項」對話框，選「自定義序列」標籤（），先後按「導入」、「確定」按鈕。以後在任一單元格中輸入某一職工姓名（不一定非得是第一位職工的姓名），用「填充柄」即可將該職工後面的職工姓名快速填入後續的單元格中。※快速輸入特殊符號 有時候我們在一張工作表中要多次輸入同一個文本，特別是要多次輸入一些特殊符號（如※），非常麻煩，對錄入速度有較大的影響。這時我們可以用一次性替換的方法來克服這一缺陷。先在需要輸入這些符號的單元格中輸入一個代替的字母（如X，注意:不能是表格中需要的字母），等表格製作完成後，單擊「編輯→替換」命令，打開「替換」對話框（），在「查找內容」下面的方框中輸入代替的字母「X」，在「替換為」下面的方框中輸入「※」，將「單元格匹配」前面的鉤去掉（否則會無法替換），然後按「替換」按鈕一個一個替換，也可以按「全部替換」按鈕，一次性全部替換完畢。※快速輸入相同文本 有時後面需要輸入的文本前面已經輸入過了，可以採取快速複製（不是通常的「Ctrl+C」、「Ctrl+X」、「Ctrl+V」）的方法來完成輸入: 1.如果需要在一些連續的單元格中輸入同一文本（如「有限公司」），我們先在第一個單元格中輸入該文本，然後用「填充柄」將其複製到後續的單元格中。 2.如果需要輸入的文本在同一列中前面已經輸入過，當你輸入該文本前面幾個字元時，系統會提示你，你只要直接按下Enter鍵就可以把後續文本輸入。 3.如果需要輸入的文本和上一個單元格的文本相同，直接按下「Ctrl＋D（或R）」鍵就可以完成輸入，其中「Ctrl+D」是向下填充，「Ctrl+R」是向右填充。 4.如果多個單元格需要輸入同樣的文本，我們可以在按住Ctrl鍵的同時，用滑鼠點擊需要輸入同樣文本的所有單元格，然後輸入該文本，再按下「Ctrl+Enter」鍵即可。※快速給數字加上單位 有時我們需要給輸入的數值加上單位（如「立方米」等），少量的我們可以直接輸入，而大量的如果一個一個地輸入就顯得太慢了。我們用下面的方法來實現單位的自動輸入:先將數值輸入相應的單元格中（注意:僅限於數值），然後在按住Ctrl鍵的同時，選取需要加同一單位的單元格，單擊「格式→單元格」命令，打開「單元格格式」對話框（），在「數字」標籤中，選中「分類」下面的「自定義」選項，再在「類型」下面的方框中輸入「#」「立」「方」「米」，按下確定鍵後，單位（立方米）即一次性加到相應數值的後面。※巧妙輸入位數較多的數字 大家知道，如果向Excel中輸入位數比較多的數值（如身份證號碼），則系統會將其轉為科學計數的格式，與我們的輸入原意不相符，解決的方法是將該單元格中的數值設置成「文本」格式。如果用命令的方法直接去設置，也可以實現，但操作很慢。其實我們在輸入這些數值時，只要在數值的前面加上一個小「"」就可以了（注意:"必須是在英文狀態下輸入）。※快速在多個單元格中輸入相同公式先選定一個區域，再鍵入公式，然後按「Ctrl+Enter」組合鍵，可以在區域內的所有單元格中輸入同一公式。※同時在多個單元格中輸入相同內容選定需要輸入數據的單元格，單元格可以是相鄰的，也可以是不相鄰的，然後鍵入相應數據，按「Ctrl+Enter」鍵即可。※快速輸入日期和時間 當前日期選取一個單元格，並按「Ctrl+;」 當前時間 選取一個單元格，並按「Ctrl+Shift+;」 當前日期和時間 選取一個單元格，並按「Ctrl+;」，然後按空格鍵，最後按「Ctrl+Shift+;」 注意:當你使用這個技巧插入日期和時間時，所插入的信息是靜態的。要想自動更新信息，你必須使用TODAY和NOW函數。※快速輸入無序數據在Excel數據表中，我們經常要輸入大批量的數據，如學生的學籍號、身份證號等。這些數值一般都無規則，不能用「填充序列」的方法來完成。通過觀察後我們發現，這些數據至少前幾位是相同的，只有後面的幾位數值不同。通過下面的設置，我們只要輸入後面幾位不同的數據，前面相同的部分由系統自動添加，這樣就大大減少了輸入量。例如以學籍號為例，假設由8位數值組成，前4位相同，均為0301，後4位為不規則數字，如學籍號為03010056、03011369等。操作步驟如下：選中學籍號欄位所在的列，單擊「格式」菜單中的「單元格」命令，在「分類」中選擇「自定義」，在「類型」文本框中輸入「03010000」（如圖2）。不同的4位數字全部用「0」來表示，有幾位不同就加入幾個「0」，[確定]退出後，輸入「56」按回車鍵，便得到了「03010056」，輸入「1369」按回車便得到了「03011369」。身份證號的輸入與此類似。※輸入公式單擊將要在其中輸入公式的單元格，然後鍵入＝(等號)，若單擊了「編輯公式」按鈕或「粘貼函數」按鈕，Excel將插入一個等號，接著輸入公式內容，按Enter鍵。※輸入人名時使用「分散對齊」在Excel表格中輸入人名時為了美觀，我們一般要在兩個字的人名中間空出一個字的間距。按空格鍵是一個辦法，但是我們這裡有更好的方法。我們以一列為例，將名單輸入後，選中該列，點擊「格式→單元格→對齊」，在「水平對齊」中選擇「分散對齊」，最後將列寬調整到最合適的寬度，整齊美觀的名單就做好了。※如何在excel單元格中輸入01這個函數很管用...值得一試哦!例:=TEXT(A1,"00000")把單元格設置為文本格式再輸入數據，或輸入"(撇號)再輸入數據，或根據要顯示的數字位數自定義單元格格式：如要顯示5位，不足5位的前面用0填足，自定義單元格格式：00000輸入123顯示00123，輸入1顯示00001，輸入12345，顯示12345※在EXCEL中增加自動填充序列在Excel中提供了自動填充功能，我們在使用時，可以通過拖動「填充柄」來完成數據的自動填充。例如要輸入甲、乙、丙、丁……，可以先在指定單元格輸入甲，然後將滑鼠移至單元格的右下角的小方塊處，直至出現「＋」字，按住滑鼠左鍵，向下（右）拖動至目的單元格，然後鬆開即完成了自動填充。可是有時我們會發現有一些數據序列不能自動填充，例如車間一、車間二、車間三等，填充方法有兩種：第一種：單擊「菜單」欄上的「工具」，選「選項」→「自定義序列」，這時就可以在「輸入序列」欄輸入要定義的序列。需要注意的是每輸入完成一項就要回車一次，表示一項已經輸入完畢，全部輸入完成以後單擊「添加」→「確定」，這樣我們自定義的序列就可以使用了。第二種：首先把你要添加的序列輸入到一片相臨的單元格內，例如要定義一個序列：車間一、車間二、車間三，把這三項分別輸入到單元H1：H3，單擊「工具」→「選項」→「自定義序列」→「導入」，在「導入序列所在的單元格」所指的對話框中輸入H1：H3，單擊「導入」→「添加」→「確定」，這樣新序列就產生了。定義的序列如果不再使用，還可刪除，方法是：單擊「工具」→「選項」→「自定義序列」，在「自定義序列」框中，單擊要刪除的序列，再單擊「刪除」→「確定」。※如何輸入假分數1又2分之1怎麼輸入單元格格式設成」分數「，單元格中輸入1.5，先輸入1，再按空白鍵；再輸入1/2，輸入後是這樣「11/2 」，不是內行人看不懂的。二分之一,四分之一, 四分之三 可用ALT+189(188,190)獲得。先輸入0，空格，再輸入3/2。※錄入准考證號碼有妙招最近在學校參加招生報名工作，每位新生來校報到時，我們先請他們填寫一張信息表，例如姓名、性別、准考證號碼、聯繫電話、郵編等內容，然後在Excel中進行填寫，這樣無論是數據統計還是分班都方便多了。准考證號碼是類似於「04360101」的8位數字，如果直接輸入的話，Excel會自作聰明地去除最前面的0，常規的做法是在錄入數字時手工輸入一個半形的單引號作為前導引號，但由於需要錄入的數據量太大，因此便將這一列設置成「文本」格式。很快，我便發覺本地所有考生的准考證號碼中前4位數字都是相同的，是否可以想一個辦法讓Excel自動錄入最前面的「0436」呢？選定「准考證號碼」列，打開「格式→單元格格式→數字」對話框，如圖所示，在「分類」下拉列表框中選擇「自定義」項，在右側的「類型」欄中輸入「"0436"@」，這裡的「0436」是准考證號碼最前面的4位數字，錄入時注意不要忘記前後的半形雙引號，最後點擊「確定」按鈕退出。現在只需要錄入准考證號碼後面的4位數字，Excel會自動添加前面的「0436」，這樣效率明顯提高。編輯提示：如果需要錄入的准考證號碼位數非常長，這樣可能會出現其他的顯示錯誤，因為Excel的預設設置是單元格中輸入的數字被限制在11位，一旦超過將會以科學記數格式顯示所輸入的數字，例如「3365201740520301」將被顯示為「3．65202E＋14」；當輸入的數字超過15位時，第15位以後的數字將顯示為0。其實，除了將該列設置為「文本」格式外，此時我們還可以採取上述同樣的方法簡化錄入操作，畢竟最前面的幾位數字總是相同的。※向上填充的快捷鍵我只會向下填充的快捷鍵，向上-向左-向右的都是什麼呢？解答：向上-Alt+E,I,U。向左-Alt+E,I,L。向右-CTRL+R一列中不輸入重複數字[數據]--[有效性]--[自定義]--[公式]輸入=COUNTIF(A:A,A1)=1如果要查找重複輸入的數字條件格式》公式》=COUNTIF(A:A,A5)>1》格式選紅色單元格輸入我想在A1單元格內輸入1而A1自動會乘1000。格式寫為: #"000"工具—選項—編輯—自動設置小數點：-3大量0值輸入超級技巧在單元格中輸入「=450**3」會等於450000單元格 =45**N 時出現 45000任一數字**N ， 數字後面的**N 表示加 N 個零如何在C列中輸入工號在D列顯示姓名比如在A、B列中建立了工號對應的姓名，如何在C列中輸入工號在D列顯示姓名。假設你的數據區域在A1：B100，A列為工號，B列為姓名，C列為要輸入的工號，D列輸入以下公式：d1=vlookup(C1,$a$1:$b$100,2,false)輸入提示如何做輸入提示是怎麼做出來的，好像不是附註吧!用數據有效性中的輸入信息功能就可實現自動跟蹤。「數據>有效性>輸入信息」。在信息輸入前就給予提示在單元格輸入信息時，希望系統能自動的給予一些必要的提示，這樣不但可以減少信息輸入的錯誤，還可以減少修改所花費的時間。請問該如何實現？答：可以按如下操作：首先選擇需要給予輸入提示信息的所有單元格。然後執行「數據」菜單中的「有效性」命令，在彈出的對話框中選擇「輸入信息」選項卡。接著在「標題」和「輸入信息」文本框中輸入提示信息的標題和內容即可。提示顯示在屏幕的右上角，離左邊的單元格太遠，一般人注意不到，達不到提示的目的。如何設置讓提示跟單元格走？數據有效性只能輸入以"楊"開頭的字元串,或者是含有"龍"的字元串=OR(LEFT(D35,1)="楊",NOT(ISERROR(FIND("龍",D35))))簡化=(Left(a1)="楊")+Countif(a1,"*龍*")=(LEFT(A:A)="a")+COUNTIF(A:A,"*b*")
推薦閱讀：