|
28.如何分班統(tǒng)計男女人數(shù)
男=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，合并數(shù)據(jù)后再利用countif公式對D列統(tǒng)計。 =COUNTIF($B$2:$B$446,E2) 29.在幾百幾千個數(shù)據(jù)中發(fā)現(xiàn)重復(fù)項 我的意思不是查找功能,那個我會用,比如有幾百個人的名字輸入單元格中,但我面對那么多名字真無法短時間內(nèi)看出誰重復(fù)了,該如何辦? 假設(shè)判斷區(qū)域為A1:D10,格式/條件格式，選公式(不是數(shù)值)，輸入： =COUNTIF($A$1:$D$10,A1)>1 然后在格式中設(shè)置一個字體或圖案顏色,確定,這樣重復(fù)數(shù)據(jù)就變成了有色單元格。 30.統(tǒng)計互不相同的數(shù)據(jù)個數(shù) 例如,在 3 * 3 的區(qū)域中統(tǒng)計互不相同的數(shù)據(jù)個數(shù), 1 2 3 3 2 1 1 2 0 結(jié)果應(yīng)為 4 (4 個互不相同的數(shù)據(jù)) 數(shù)組公式=sum(1/countif(a1:c3,a1:c3)) 還可以公式: =COUNT(IF(FREQUENCY(A1:C3,A1:C3),1)) 31.多個工作表的單元格合并計算 =Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4) 32.單個單元格中字符統(tǒng)計 假設(shè) A1單元格中有數(shù)據(jù)"sdfsfjksfhweofiefondsfljsdfisdofjei" 如何用公式統(tǒng)計出A1單元格中有多個不重復(fù)的字符?=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1)) 數(shù)組公式=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)) 33.數(shù)據(jù)區(qū)包含某一字符的項的總和,該用什么公式 =sumif(a:a,"*"&"某一字符"&"*",數(shù)據(jù)區(qū)) 34.函數(shù)如何實現(xiàn)分組編碼 對數(shù)值進行分組編碼 =A2&TEXT(COUNTIF($A$2:A2,A2),"00")
十三、【數(shù)值取整及進位】 1.取整數(shù)函數(shù) 907.5；1034.2；1500要改變?yōu)?span style="font-size: 12pt;">908；1035；1500公式為： =CEILING(A1,1) 907；1034；1500要改變?yōu)?span style="font-size: 12pt;">910；1040；1500公式為： =CEILING(A1,10) 如果要保留到百位數(shù)，即改變?yōu)?span style="font-size: 12pt;">1000；1100；1500公式為： =CEILING(A1,100) 2.數(shù)值取整 在單元格中要取整數(shù)（只取整數(shù)不用考慮四舍五入）用什么函數(shù)呀？例如：10/4只要顯示2就可以了！要考慮負數(shù)的因數(shù)呢？例如：(-10/4)要顯示－2而不是－3？怎么辦？ =TRUNC(A1,0) =ROUNDDOWN(A1,0) 3.求余數(shù)的函數(shù) 比如：A1=28，A2=（A1÷6）的余數(shù)=4，請問這個公式怎么寫？ 解答：=MOD(28,6) 4.四舍五入公式 =ROUND() =ROUND($B$1*A1,2) =ROUND(B1*A1,2) =round(a1,0) =round(a1,0)*0.95 5.對數(shù)字進行四舍五入 對于數(shù)字進行四舍五入,可以使用INT（取整函數(shù)），但由于這個函數(shù)的定義是返回實數(shù)舍入后的整數(shù)值。因此，用INT函數(shù)進行四舍五入還是需要一些技巧的，也就是要加上0.5，才能達到取整的目的。公式應(yīng)寫成： =INT(B2*100+0.5)/100 6.如何實現(xiàn)“見分進元” 在我們的工資中，有一項“合同補貼”，只要計算結(jié)果出現(xiàn)“分”值就在整數(shù)“元”進一位，也就是說3.01元進到4.00元，3.00元不變，整數(shù)“元”不變。 =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即是要轉(zhuǎn)換的目標；B2輸入00（文本格式，必須是00這兩個數(shù)） 。 ＝IF(INT(A1)<>A1,INT(A1)+1,A1) =ROUNDUP(A1,0) =CEILING(A9,1) =INT(A9+1) 7.四舍五入 如何將Excel 中的數(shù)據(jù)，希望把千位以下的數(shù)進行四舍五入，例如：3245 希望變成3000；3690 希望成為400 =ROUND(C6*D6,2) =ROUND(A2*0.001,)*1000 =ROUND(A2,-3) =--FIXED(A2,-3) =ROUND(A2/1000,0)*1000 8.如何四舍五入取兩位小數(shù) 如何四舍五入取兩位小數(shù),如2.145為2.15,0.1449為0.14. =ROUND(A1,2)9.根據(jù)給定的位數(shù)，四舍五入指定的數(shù)值 對整數(shù)無效。四舍五入B234的數(shù)值，變成小數(shù)點后一位。 12512.2514 12512.3 =ROUND(B23,1) 10.四舍六入 =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 判斷是否為一位小數(shù),且是0.5，如果不符合上術(shù)要條件,按普通四舍五入法則處理,否則判斷整數(shù)部分的奇偶。 =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,)) 11.如何實現(xiàn)2舍3入 做工資時，常遇到：3.2元要舍去0.2元變?yōu)?span style="FONT-SIZE: 12pt;">3.00元,而3.3元要把0.3元入為0.5元變?yōu)?span style="FONT-SIZE: 12pt;">3.5元.請教,該如何實現(xiàn)? =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) 12.怎么設(shè)置單元格以千元四舍五入 比如輸入123456，顯示出來123，000 =CEILING(ROUND(A1/1000,0),1)*1000=round(a1,-3) =mround(A1,1000) 13.ROUND函數(shù)的四舍五入不進位的解決方法？ 計算一：A2=1345.3 B2=1232.4 C3=A2-B2=112.9 D=0.05 E=ROUND(B2*D2,2)=5.64 （計算結(jié)果為5.645，此運算沒有進位）。 計算二：A2=1225.4 B2=1112.5 C3=A2-B2=112.9 D=0.05 E=ROUND(B2*D2,2)=5.65（計算結(jié)果為5.645，此運算進位）。 以上兩式中C3結(jié)果都為112.9，而為什么應(yīng)用ROUND函數(shù)后結(jié)果卻不一樣。 請教高手有什么函數(shù)能保證四舍五入不會出錯。 可將C列先變成文本性數(shù)據(jù)，再進行后面的運算，以達到計算的目的。 如：C列可改成C1=TRIM(A1-B1)，以此類推，只要是更改成文本性數(shù)據(jù)就行。 14.保留一位小數(shù) 我需要保留一位小數(shù),不管后面是什么數(shù)字,超過5或不超過5,都向前進一位. 例如:329.99-->330.00 329.84----->329.90 329.86----->329.90 =roundup(*,2)或=round(a1+0.04,1) 15.如何三舍四入 =round(原數(shù)值+0.001,2) 16.另類四舍五入 我用Excle給別人算帳，由于要對上百家收費，找零卻是個問題。于是我提出四舍五入，收整元。但是領(lǐng)導(dǎo)不同意，要求收取0.5元。例如：某戶為123.41元，就收123.50元;如果是58.72元，就收58.5元。這可難壞了我。經(jīng)過研究，我發(fā)現(xiàn)，可以在設(shè)置單元格中，設(shè)成分數(shù)，以2為分母，可以解決問題。但是打印出來的卻是分數(shù)不好看，而且求和也不對。請各位高手給予指點。是這樣的，如果是57.01元，則省去，即收57.00元；如果是57.31元，則進為57.50元；如果是57.70元，也收57.50元；要是57.80元，則收58.00元。 假設(shè)數(shù)據(jù)在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函數(shù)取整數(shù)部分，A1-int(A1)取小數(shù)部分，根據(jù)你的意思：<=0.3按0算，0.3~0.7(含)按0.5算，0.7~0.99……按+1算 則：第一個公式不難理解了 簡化公式中：“*((A1-INT(A1)>0.3)+(A1-INT(A1)>0.7))”即(小數(shù)部分>0.3)+(小數(shù)部分>0.7) 我們知道這是省略if的判斷語句，條件為真返回true（也就是1）否在為false（0），那么如果小數(shù)<=0.3，則兩個條件都為0，即整數(shù)部分+0.5*0=整數(shù)部分，介于0.3~0.7，則為整數(shù)部分+0.5*（1+0），大于0.7肯定也大于0.3啦，則為整數(shù)部分+0.5*（1+1）。 請問，如果是由幾個分表匯總的總表想如此處理，該如何做。 例：e112位置=SUM(一庫入庫!G112,二庫入庫!G112,四庫入庫!G112,保健酒基地入庫!G112,下陸倉庫入庫!G112) 匯總的結(jié)果為100.24，而我要求如果小數(shù)為24的話自動視為1累加，否則不便。 就是小數(shù)為0.24才加1，否則都舍掉？ 若是：=if（sum公式-int（sum公式）=0.24，int（sum公式）+1，sum公式） 17.想把小數(shù)點和后面的數(shù)字都去掉，不要四舍五入 比如： 12.30 變成 12.00 45.32 45.00 25.38 25.00 6.54 6.00 13.02 13.00 59.68 59.00 23.62 23.00 =Rounddown(A1,0) 你要把A1換成你要轉(zhuǎn)換的那個單元格啊，然后拖動就可以了！ 我那里用的那個A1只是告訴你一個例子而已，你要根據(jù)你的實際情況來修改一下才能用的。 =INT(A1) =TRUNC(A1,0)18.求真正的四舍五入后的數(shù) 請教如何在Excel中，求“金額合計”（小數(shù)點后二位數(shù)）時，所取的數(shù)值應(yīng)是所求單元格中寫的數(shù)字（四舍五入后的數(shù)字），而不是（四舍五入前）的數(shù)字。因為只有這樣行和列及關(guān)聯(lián)的工作表才能對得上，例如：表上的數(shù)值分別是：(1.802/2=0.901)0.90(A1); (1.604/2=0.802)0.80(A2); (1.406/2=0.703)0.70(A3);（因取小數(shù)點后二位）。合計數(shù)(A4)表中自己計算和顯示是：(0.901+0.802+0.703=2.406)2.41（四舍五入后的數(shù)值）。但照表中的數(shù)值人工計算卻是：(0.9+0.8+0.7=)2.4,有矛盾，還有許多例子，故請教各高手，如何設(shè)置公式，使得人工計算結(jié)果同表中一致。請指教。十分感謝！ 工具》選項》重新計算》以顯示精度為準 前打鉤 也可以用函數(shù) ROUND() 使結(jié)果四舍五入 。如ROUND（算式，2）代表保留兩位小數(shù)，如ROUND（算式，1）代表保留一位小數(shù)。 19.小數(shù)點進位 小數(shù)點進位如何把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) 20.個位數(shù)歸0或者歸5 A*B后想得到C的結(jié)果值，用什么函數(shù)比較好 A B C(想得到的數(shù)值) 320 1.1 355 1140 1.2 1370 50 1.3 65 16 1.4 25 =FLOOR(A1*B1+5*(MOD(A1*B1,5)<>0),5)=CEILING(A1*B1,5)
十四、【大小值或中間值】 1.求平均值 如在列中有一組數(shù)字：10、7、9、27、2 =AVERAGE(A2:A6) 上面數(shù)字的平均值為11 行公式=AVERAGE(B2:D2) 2.如何實現(xiàn)求平均值時只對不等于零的數(shù)求均值？ ＝AVERAGE (IF(A1:A5>0,A1:A5)) 3.平均分的問題 假設(shè)一個班有60人，要統(tǒng)計出各個學(xué)科排名前50的學(xué)生的平均分，用公式應(yīng)該如何寫？如果用排序再來算的話很麻煩，能不能直接用公式找出前50名進行計算？ {=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:50"))))} 4.怎樣求最大值（最小值或中間值） =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))) 5.平均數(shù)怎么弄 如在列中有一組數(shù)字：10、7、9、27、2 公式為： =AVERAGE(A2:A6) 上面數(shù)字的平均值為11 =AVERAGE(A2:A6, 5) 上面數(shù)字與 5 的平均值為10 6.去掉其中兩個最大值和兩個最小值的公式 我要將一行數(shù)據(jù)進行處理。要去掉其中兩個最大值和兩個最小值，不知道怎樣運用公式，應(yīng)該是： =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})) 7.去一行最高分最低分求平均值 去一行中一個最高分和一個最低分求平均值 公式為：=(SUM(A5:E5)-MAX(A5:E5)-MIN(A5:E5))/(COUNTIF(A5:E5,">0")-2) 但另用TRIMMEAN ()函數(shù)較好。=TRIMMEAN($A$5:$E$5,2/COUNT($A$5:$E$5)) 為需要進行整理并求平均值的數(shù)組或數(shù)值區(qū)域。TRIMMEAN(array,percent) 為計算時所要除去的數(shù)據(jù)點的比例，例如，如果 percent = 0.2，在 20 個數(shù)據(jù)點的集合中，就要除去 4 個數(shù)據(jù)點 (20 x 0.2)：頭部除去 2 個，尾部除去 2 個。 用活了TRIMMEAN函數(shù)，這個問題易如反掌。 8.在9個數(shù)值中去掉最高與最低然后求平均值 假設(shè)9個數(shù)值所在的區(qū)域為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) 9.求最大值（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))｝ 10.如何實現(xiàn)求平均值時只對不等于零的數(shù)求均值？ = TRIMMEAN (IF(A1:A5>0,A1:A5)) 11.得到單元格編號組中最大的數(shù)或最小的數(shù) 對字符格式的數(shù)字不起作用。 =MAX(B16:B25) =MIN(B16:B25) （得到最小的數(shù)的公式） 12.標記出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) 13.取前五名，后五名的方法 {=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)) 14.如何用公式求出最大值所在的行？ 如A1：A10中有10個數(shù)，怎么求出最大的數(shù)在哪個單元格？ =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))))} 15.如有多個最大值的話呢？如何一一顯示其所在的單元格？ {=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),"")} 16.求多個最高分 語文成績有多個最高分,如何用公式的方法把他們抽出來(動態(tài))? B15=INDEX(A:A,SMALL(IF(B$2:B$10=MAX(B$2:B$10),ROW($2:$10),65536),ROW(1:1)))&""數(shù)組公式，按下Ctrl+Shift+Enter結(jié)束。 如果增加一個條件,就是在姓名前加一個類別,例如前5個人是A類的,后4個是B類的,請分類找出A類和B類的對應(yīng)姓名的最高分 =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)))&"" 17.如何求多條件的平均值 應(yīng)如何求下表中1月份400g重量的平均值 月份 規(guī)格 重量 1 400g 401 1 400g 403 2 400g 402 2 400g 404 1 200g 201 1 200g 203 2 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))} 數(shù)組公式：{=AVERAGE(IF(B2:B8="400g")*(A2:A8=1),(C2:C8),""))} 另一個數(shù)組公式試試：=Average(if((a1:a10=1)*(b1:b10="400g"),c1:c10)) =SUMIF(B1:B7,B1,C1:C7)/COUNTIF(B1:B7,B1) 這個也可以 18.想求出第三大之數(shù)值 如A1：A4分別為1,2,2,3. 想求出第三大之數(shù)值"1",應(yīng)如何設(shè)公式。 =large(if(frequency(a1:a4,a1:a4),a1:a4),3) 數(shù)組公式的解法 =LARGE((MATCH(A1:A10,A1:A10,)=ROW(1:10))*A1:A10,3)
十五、【查詢和查找 引用】1.查找順序公式 =LOOKUP(2,1/(A1:A20<>0),A1:A20) =MATCH(7,A1:A20) =VLOOKUP(7,A1:B11,2) 2.怎樣實現(xiàn)精確查詢 用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)) 用數(shù)組公式 =INDEX(E1:E7,MAX(IF((B4:B7=B11),ROW(B4:B7),0))) 3.查找及引用 如何查找并引用B2單元格中所顯示日期當日的相應(yīng)代碼的值。 B3=IF(COUNTIF($E$3:$E$20,A3),VLOOKUP($A3,$E$2:$M$20,MATCH(B$2,$F$2:$M$2,)+1,),"") 4.查找函數(shù)的應(yīng)用 我想在A5輸入表的名稱，B5自動跳出該表中B列的最后一個有效數(shù)值，請問B5的公式該如何設(shè)定？ =LOOKUP(9E+307,INDIRECT(A5&"!"&"B:B"))B2 =IF(A2="","",LOOKUP(9E+307,INDIRECT(A2&"!B:B"))) 5.怎么能方便的判斷某個單元格中包含多少個指定的字符？ 例：A1 中是“ASAFAG”，我希望計算出A1里面有多少個“A”...... =LEN(A1)-LEN(SUBSTITUTE(A1,"A","")) 6.如何用查找函數(shù) 一、要求: 利用公式從左表中查詢相應(yīng)的地區(qū)，結(jié)果放在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,)) 二、要求: 根據(jù)C25單元格的商品名稱，查找該商品的最新單價，即該商品最后一條記錄的單價（結(jié)果放在D25單元格）。用數(shù)組公式： =INDIRECT("G"&MAX((D14:D22=C25)*ROW(D14:D22))) D25 =LOOKUP(2,1/(D14:D22=C25),G14:G22) 7.日期查找的問題 我有一個日期比如:2007/02/12,我想知道它減去一個固定天數(shù)比如6后,最接近它的一個星期四(只能提前)是多少號 2007/02/12的答案應(yīng)該是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/12 B1, 輸入公式 : =A1-6-MOD(WEEKDAY(A1-6,2)+3,7)8.如何自動查找相同單元格內(nèi)容 =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)) 9.查找函數(shù) 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)) 10.怎樣對號入座（查找） =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) 11.一個文本查找的問題 如何在一個單元格中,統(tǒng)計某個字符出現(xiàn)的次數(shù),例如:單元格A1中填有:張三/李四/王五",如何通過公式來計算此單元格中共填有幾個人姓名,每個人姓名之間用"/"符號分開,煩請相告. =LEN(A1)-LEN(SUBSTITUTE(A1,"/",))+112.查找一列中最后一個數(shù)值 我想用公式知道,另一個表中"A"列最下面一個數(shù)是多少,就行了.用不定值的,因為還有數(shù)據(jù)有增加, =LOOKUP(9E+307,Sheet2!A:A)——最后一個數(shù)值 =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) 13.查找重復(fù)字符 兩組數(shù)值 A B 1245689 0134578 查找單元格Ａ和Ｂ里重復(fù)及不重復(fù)的字符 正確答案：重復(fù)字符－1458 不重復(fù)字符－023679 以下公式對數(shù)字有效： 重復(fù)數(shù)字： =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,) 不重復(fù)數(shù)字： =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,) 都是數(shù)組公式，按Ctrl+shift+enter結(jié)束。 重復(fù)數(shù)字： =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,) 不重復(fù)數(shù)字： =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,) 14.請教查找替換問題 把表1中字符在4個以上的字段(含4個)查找出來,替換成表2中的人名,最好在原位置修改,或者在新的一列上生成也成,只要其他內(nèi)容保持不變并按原來的順序即可。 =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,"="&"????*")))15.IF函數(shù)替換法總結(jié) 條件說明：小于10返回500，小于20返回800，小于30返回1100，小于40返回1400，大于40返回1700 類似于以上要求，大家最先想到IF函數(shù)，這也本屬IF專長。但用IF一般要長長的公式，且計算較慢?，F(xiàn)總結(jié)一下IF之替換公式，望能拋磚引玉，在我的倡導(dǎo)下各位提供更完善的方案。其中部分公式通用，部分公式有局限性，請看說明。(前18個條件公式，根據(jù)速度，排名如下) 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+500 6=MATCH(A1,{0,10,20,30,40})*300+200 7=MIN(40,FLOOR(A1,10))*30+500 8=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+200 11=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))+500 18=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)))*300 22MAX((A1>={0,10,20,30,40})*{500,800,1100,1400,1700}) 23=MATCH(A1,{0,10,20,30,40})*300+200 24=MIN(40,FLOOR(A1,10))*30+500 25=FREQUENCY(ROW($1:$5)*10-10,A1)*300+200 16.查找的函數(shù)（查找末位詞組） （數(shù)組公式：）=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,) （數(shù)組公式：）=RIGHT(A2,MATCH(1,FIND(" ",RIGHT(" "&A2,ROW($1:$100))),)-1) =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)) （好） 其實這個公式的思路, 是可以變化的，改變REPT( )中的數(shù)值, 可以返回, 指定空格位置後的數(shù)據(jù)，比如： A1 =一 二三 四 五 六 七 八 九 10個普通公式, 分別為 : 1=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",100)),100)) 返回第0空格位置後的數(shù)據(jù)>一 二 三 四 五 六 七 八 九 2=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100)) 返回第8 空格位置後的數(shù)據(jù)>九3=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",40)),100)) 返回第7 空格位置後的數(shù)據(jù)>八 九 4=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),100)) 返回第6 空格位置後的數(shù)據(jù)>七 八九 5=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",23)),100)) 返回第5空格位置後的數(shù)據(jù)>六 七八 九 6=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",18)),100)) 返回第4 空格位置後的數(shù)據(jù)>五 六 七 八 九 7=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",14)),100)) 返回第3 空格位置後的數(shù)據(jù)>四 五 六 七 八 九 8=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",12)),100)) 返回第2 空格位置後的數(shù)據(jù)>三 四 五 六 七 八 九 9=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",11)),100)) 返回第1 空格位置後的數(shù)據(jù)>二 三 四 五 六 七 八 九 10=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),100)) 返回第0空格位置後的數(shù)據(jù)>一 二三 四 五 六 七 八 九 17.怎樣從原始數(shù)據(jù)中自動獲取最后一個數(shù)據(jù)
18.兩列數(shù)據(jù)查找相同值對應(yīng)的位置 =MATCH(B1,A:A，0) 19.查找數(shù)據(jù)公式兩個(基本查找函數(shù)為VLOOKUP,MATCH) （1）、根據(jù)符合行列兩個條件查找對應(yīng)結(jié)果 =VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE) （2）、根據(jù)符合兩列數(shù)據(jù)查找對應(yīng)結(jié)果(為數(shù)組公式) =INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0))
十六、【輸入數(shù)據(jù)的技巧 】談?wù)凟xcel輸入的技巧 在Excel工作表的單元格中，可以使用兩種最基本的數(shù)據(jù)格式：常數(shù)和公式。常數(shù)是指文字、數(shù)字、日期和時間等數(shù)據(jù)，還可以包括邏輯值和錯誤值，每種數(shù)據(jù)都有它特定的格式和輸入方法，為了使用戶對輸入數(shù)據(jù)有一個明確的認識，有必要來介紹一下在Excel中輸入各種類型數(shù)據(jù)的方法和技巧。 【1】輸入文本 Excel單元格中的文本包括任何中西文文字或字母以及數(shù)字、空格和非數(shù)字字符的組合，每個單元格中最多可容納32000個字符數(shù)。雖然在Excel中輸入文本和在其它應(yīng)用程序中沒有什么本質(zhì)區(qū)別，但是還是有一些差異，比如我們在Word、PowerPoint的表格中，當在單元格中輸入文本后，按回車鍵表示一個段落的結(jié)束，光標會自動移到本單元格中下一段落的開頭，在Excel的單元格中輸入文本時，按一下回車鍵卻表示結(jié)束當前單元格的輸入，光標會自動移到當前單元格的下一個單元格，出現(xiàn)這種情況時，如果你是想在單元格中分行，則必須在單元格中輸入硬回車，即按住Alt鍵的同時按回車鍵。 【2】輸入分數(shù) 幾乎在所有的文檔中，分數(shù)格式通常用一道斜杠來分界分子與分母，其格式為“分子/分母”，在Excel中日期的輸入方法也是用斜杠來區(qū)分年月日的，比如在單元格中輸入“1/2”，按回車鍵則顯示“1月2日”，為了避免將輸入的分數(shù)與日期混淆，我們在單元格中輸入分數(shù)時，要在分數(shù)前輸入“0”（零）以示區(qū)別，并且在“0”和分子之間要有一個空格隔開，比如我們在輸入1/2時，則應(yīng)該輸入“0 1/2”。如果在單元格中輸入“8 1/2”，則在單元格中顯示“8 1/2”，而在編輯欄中顯示“8.5”。 【3】輸入負數(shù) 在單元格中輸入負數(shù)時，可在負數(shù)前輸入“-”作標識，也可將數(shù)字置在（）括號內(nèi)來標識，比如在單元格中輸入“（88）”，按一下回車鍵，則會自動顯示為“-88”。 【4】輸入小數(shù) 在輸入小數(shù)時，用戶可以向平常一樣使用小數(shù)點，還可以利用逗號分隔千位、百萬位等，當輸入帶有逗號的數(shù)字時，在編輯欄并不顯示出來，而只在單元格中顯示。當你需要輸入大量帶有固定小數(shù)位的數(shù)字或帶有固定位數(shù)的以“0”字符串結(jié)尾的數(shù)字時，可以采用下面的方法：選擇“工具”、“選項”命令，打開“選項”對話框，單擊“編輯”標簽，選中“自動設(shè)置小數(shù)點”復(fù)選框，并在“位數(shù)”微調(diào)框中輸入或選擇要顯示在小數(shù)點右面的位數(shù)，如果要在輸入比較大的數(shù)字后自動添零，可指定一個負數(shù)值作為要添加的零的個數(shù)，比如要在單元格中輸入“88”后自動添加3個零，變成“88 000”，就在“位數(shù)”微調(diào)框中輸入“-3”，相反，如果要在輸入“88”后自動添加3位小數(shù)，變成“0.088”，則要在“位數(shù)”微調(diào)框中輸入“3”。另外，在完成輸入帶有小數(shù)位或結(jié)尾零字符串的數(shù)字后，應(yīng)清除對“自動設(shè)置小數(shù)點”符選框的選定，以免影響后邊的輸入；如果只是要暫時取消在“自動設(shè)置小數(shù)點”中設(shè)置的選項，可以在輸入數(shù)據(jù)時自帶小數(shù)點。 【5】輸入貨幣值 Excel幾乎支持所有的貨幣值，如人民幣（￥）、英鎊（￡）等。歐元出臺以后，Excel2000完全支持顯示、輸入和打印歐元貨幣符號。用戶可以很方便地在單元格中輸入各種貨幣值，Excel會自動套用貨幣格式，在單元格中顯示出來，如果用要輸入人民幣符號，可以按住Alt鍵，然后再數(shù)字小鍵盤上按“0165”即可。快速輸入歐元符號 先按下Alt鍵，然后利用右面的數(shù)字鍵盤(俗稱小鍵盤)鍵入0128這4個數(shù)字，松開Alt鍵，就可以輸入歐元符號。 【6】輸入日期 Excel是將日期和時間視為數(shù)字處理的，它能夠識別出大部分用普通表示方法輸入的日期和時間格式。用戶可以用多種格式來輸入一個日期，可以用斜杠“/”或者“-”來分隔日期中的年、月、日部分。比如要輸入“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)，單元格式設(shè)置為文本即可。先設(shè)成文本格式，再輸入。 【9】輸入0開頭 在Excel單元格中，輸入一個以“0”開頭的數(shù)據(jù)后，往往在顯示時會自動把“0”消除掉。要保留數(shù)字開頭的“0”，其實是非常簡單的。只要在輸入數(shù)據(jù)前先輸入一個“‘ ”（單引號），這樣跟在后面的以“0”開頭的數(shù)字的“0”就不會被系統(tǒng)自動消除。還有更好的辦法，就是設(shè)置單元格格式為自定義“000000#“，0的個數(shù)依編碼長度定，這樣可以進行數(shù)值運算。如果這帶0開頭的字串本身是文本，或者是不定長的，那干脆先設(shè)該部分單元格格式為文本好了。另外還可用英語逗號開頭再輸就可以了。 【10】輸入百分數(shù) 在單元格中輸入一個百分數(shù)（如60％），按下回車鍵后顯示的卻是0.6。出現(xiàn)這種情況的原因是因為所輸入單元格的數(shù)據(jù)被強制定義成數(shù)值類型了，只要更改其類型為“常規(guī)”或“百分數(shù)”即可。操作如下：選擇該單元格，然后單擊“格式”菜單中的“單元格”命令，在彈出的對話框中選擇“數(shù)字”選項卡，再在“分類”欄中把其類型改為上述類型中的一種即可。如果我要求為負值的百分數(shù)自動顯示成紅色，可以再利用條件格式進行設(shè)置，格式-條件格式-單元格數(shù)值-小于-0(格式-圖案-紅色)，選中要設(shè)置的單元格-----ctrl+1---分類---自定義---輸入 0.00%;[紅色]-0.00% 【11】勾怎么輸入 1、按住ALT鍵輸入41420后放開ALT鍵√ 2、首先選擇要插入“√”的單元格，在字體下拉列表中選擇“Marlett”字體，輸入a或b，即在單元格中插入了“√”。 【12】輸入無序數(shù)據(jù) 在Excel數(shù)據(jù)表中，我們經(jīng)常要輸入大批量的數(shù)據(jù)，如學(xué)生的學(xué)籍號、身份證號等。這些數(shù)值一般都無規(guī)則，不能用“填充序列”的方法來完成。通過觀察后我們發(fā)現(xiàn)，這些數(shù)據(jù)至少前幾位是相同的，只有后面的幾位數(shù)值不同。通過下面的設(shè)置，我們只要輸入后面幾位不同的數(shù)據(jù)，前面相同的部分由系統(tǒng)自動添加，這樣就大大減少了輸入量。例如以學(xué)籍號為例，假設(shè)由8位數(shù)值組成，前4位相同，均為0301，后4位為不規(guī)則數(shù)字，如學(xué)籍號為03010056、03011369等。操作步驟如下：選中學(xué)籍號字段所在的列，單擊“格式”菜單中的“單元格”命令，在“分類”中選擇“自定義”，在“類型”文本框中輸入“03010000”。不同的4位數(shù)字全部用“0”來表示，有幾位不同就加入幾個“0”，[確定]退出后，輸入“56”按回車鍵，便得到了“03010056”，輸入“1369”按回車便得到了“03011369”。身份證號的輸入與此類似。 【13】快速輸入拼音 選中已輸入漢字的單元格，然后單擊“格式→拼音信息→顯示或隱藏”命令，選中的單元格會自動變高，再單擊“格式→拼音信息→編輯”命令，即可在漢字上方輸入拼音。單擊“格式→拼音信息→設(shè)置”命令，可以修改漢字與拼音的對齊關(guān)系。 【14】快速輸入自定義短語 使用該功能可以把經(jīng)常使用的文字定義為一條短語，當輸入該條短語時，“自動更正”便會將它更換成所定義的文字。定義“自動更正”項目的方法如下:單擊“工具→自動更正選項”命令，在彈出的“自動更正”對話框中的“替換”框中鍵入短語，如“電腦報”，在“替換為”框中鍵入要替換的內(nèi)容，如“電腦報編輯部”，單擊“添加”按鈕，將該項目添加到項目列表中，單擊“確定”退出。以后只要輸入“電腦報”，則“電腦報編輯部”這個短語就會輸?shù)奖砀裰?。具體步驟： 1.執(zhí)行“工具→自動更正”命令，打開“自動更正”對話框。 2.在“替換”下面的方框中輸入“pcw”（也可以是其他字符，“pcw”用小寫），在“替換為”下面的方框中輸入“《電腦報》”，再單擊“添加”和“確定”按鈕。 3.以后如果需要輸入上述文本時，只要輸入“pcw”字符此時可以不考慮“pcw”的大小寫，然后確認一下就成了。 【15】填充條紋 如果想在工作簿中加入漂亮的橫條紋，可以利用對齊方式中的填充功能。先在一單元格內(nèi)填入“*”或“~”等符號，然后單擊此單元格，向右拖動鼠標，選中橫向若干單元格，單擊“格式”菜單，選中“單元格”命令，在彈出的“單元格格式”菜單中，選擇“對齊”選項卡，在水平對齊下拉列表中選擇“填充”，單擊“確定”按鈕。 【16】上下標的輸入 在單元格內(nèi)輸入如103類的帶上標(下標)的字符的步驟: (1)按文本方式輸入數(shù)字(包括上下標)，如103鍵入\'103； (2)用鼠標在編輯欄中選定將設(shè)為上標(下標)的字符，上例中應(yīng)選定3； (3)選中格式菜單單元格命令，產(chǎn)生[單元格格式]對話框； (4)在[字體]標簽中選中上標(下標)復(fù)選框，再確定。 【17】文本類型的數(shù)字輸入 證件號碼、電話號碼、數(shù)字標碩等需要將數(shù)字當成文本輸入。常用兩種方法:一是在輸入第一個字符前，鍵入單引號"\'"；二是先鍵入等號"="，并在數(shù)字前后加上雙引號"""。請參考以下例子: 鍵入\'027，單元格中顯示027； 鍵入="001"，單元格申顯示001； 鍵入="""3501"""，單元格中顯示"3501"。(前后加上三個雙撇號是為了在單元格中顯示一對雙引號)； 鍵入="9\'30""，單元格中顯示9\'30"； 【18】多張工作表中輸入相同的內(nèi)容 幾個工作表中同一位置填入同一數(shù)據(jù)時，可以選中一張工作表，然后按住Ctrl鍵，再單擊窗口左下角的Sheet1、Sheet2......來直接選擇需要輸入相同內(nèi)容的多個工作表，接著在其中的任意一個工作表中輸入這些相同的數(shù)據(jù)，此時這些數(shù)據(jù)會自動出現(xiàn)在選中的其它工作表之中。輸入完畢之后，再次按下鍵盤上的Ctrl鍵，然后使用鼠標左鍵單擊所選擇的多個工作表，解除這些工作表的聯(lián)系，否則在一張表單中輸入的數(shù)據(jù)會接著出現(xiàn)在選中的其它工作表內(nèi)。 【19】不連續(xù)單元格填充同一數(shù)據(jù) 選中一個單元格，按住Ctrl鍵，用鼠標單擊其他單元格，就將這些單元格全部都選中了。在編輯區(qū)中輸入數(shù)據(jù)，然后按住Ctrl鍵，同時敲一下回車，在所有選中的單元格中都出現(xiàn)了這一數(shù)據(jù)。 【20】利用Ctrl＋*選取文本 如果一個工作表中有很多數(shù)據(jù)表格時，可以通過選定表格中某個單元格，然后按下Ctrl＋*鍵可選定整個表格。Ctrl＋*選定的區(qū)域為：根據(jù)選定單元格向四周輻射所涉及到的有數(shù)據(jù)單元格的最大區(qū)域。這樣我們可以方便準確地選取數(shù)據(jù)表格，并能有效避免使用拖動鼠標方法選取較大單元格區(qū)域時屏幕的亂滾現(xiàn)象。 【21】快速清除單元格的內(nèi)容 如果要刪除內(nèi)容的單元格中的內(nèi)容和它的格式和批注，就不能簡單地應(yīng)用選定該單元格，然后按Delete鍵的方法了。要徹底清除單元格,可用以下方法：選定想要清除的單元格或單元格范圍；單擊“編輯”菜單中“清除”項中的“全部”命令，這些單元格就恢復(fù)了本來面目。 【22】在Excel中插入斜箭頭 經(jīng)常使用Excel的朋友會遇到這樣一個問題：在Excel中想插入斜箭頭，但Excel本身沒有這樣的功能，是不是就沒有其他辦法了呢？答案是否定的。我們要想在Excel中插入斜箭頭，首先我們在要插入斜箭頭的單元格里調(diào)整好大小（為了方便插入斜箭頭），然后打開Word，插入一個表格（一個框即可），調(diào)整好表格大小，在這個框里插入一個斜箭頭，然后把這個框復(fù)制到Excel要插入斜箭頭的單元格中，再調(diào)整大小，便大功告成。我們在調(diào)整斜箭頭的時候，可以先把復(fù)制過來的斜箭頭打散，方法是：選中斜箭頭，按右鍵，“取消組合”，注意調(diào)整好大小后，調(diào)整斜線使之適合單元格，方法是：點擊右鍵，選擇“編輯頂點”，這時線條兩端會變成兩個小黑點，我們可以自由編輯線條了。至于文字，選中文本框，移動位置，直至適合位置即可。我們趕快試試吧。 【23】其它輸入補充 ※在同一單元格內(nèi)連續(xù)輸入多個測試值 一般情況下，當我們在單元格內(nèi)輸入內(nèi)容后按回車鍵，鼠標就會自動移到下一單元格，如果我們需要在某個單元格內(nèi)連續(xù)輸入多個測試值以查看引用此單元格的其他單元格的動態(tài)效果時，就需要進行以下操作:單擊“工具→選項→編輯”，取消選中“按Enter鍵后移動”選項（），從而實現(xiàn)在同一單元格內(nèi)輸人多個測試值。 ※輸入數(shù)字、文字、日期或時間 單擊需要輸入數(shù)據(jù)的單元格，鍵入數(shù)據(jù)并按Enter或Tab鍵即可。如果是時間，用斜杠或減號分隔日期的年、月、日部分，例如，可以鍵入 9/5/96 或 Jun-96。如果按12小時制輸入時間，請在時間數(shù)字后空一格，并鍵入字母 a(上午) 或 p(下午)，例如，9:00 p。否則，如果只輸入時間數(shù)字，Excel將按 AM(上午)處理。 ※將單元格區(qū)域從公式轉(zhuǎn)換成數(shù)值 有時，你可能需要將某個單元格區(qū)域中的公式轉(zhuǎn)換成數(shù)值，常規(guī)方法是使用“選擇性粘貼”中的“數(shù)值”選項來轉(zhuǎn)換數(shù)據(jù)。其實，有更簡便的方法:首先選取包含公式的單元格區(qū)域，按住鼠標右鍵將此區(qū)域沿任何方向拖動一小段距離(不松開鼠標)，然后再把它拖回去，在原來單元格區(qū)域的位置松開鼠標 (此時，單元格區(qū)域邊框變花了)，從出現(xiàn)的快捷菜單中選擇“僅復(fù)制數(shù)值”。 ※快速輸入有序文本 如果你經(jīng)常需要輸入一些有規(guī)律的序列文本，如數(shù)字（1、2……）、日期（1日、2日……）等，可以利用下面的方法來實現(xiàn)其快速輸入:先在需要輸入序列文本的第1、第2兩個單元格中輸入該文本的前兩個元素（如“甲、乙”）。同時選中上述兩個單元格，將鼠標移至第2個單元格的右下角成細十字線狀時（我們通常稱其為“填充柄”），按住鼠標左鍵向后（或向下）拖拉至需要填入該序列的最后一個單元格后，松開左鍵，則該序列的后續(xù)元素（如“丙、丁、戊……”）依序自動填入相應(yīng)的單元格中。 ※輸入有規(guī)律數(shù)字 有時需要輸入一些不是成自然遞增的數(shù)值（如等比序列:2、4、8……），我們可以用右鍵拖拉的方法來完成:先在第1、第2兩個單元格中輸入該序列的前兩個數(shù)值（2、4）。同時選中上述兩個單元格，將鼠標移至第2個單元格的右下角成細十字線狀時，按住右鍵向后（或向下）拖拉至該序列的最后一個單元格，松開右鍵，此時會彈出一個菜單（），選“等比序列”選項，則該序列（2、4、8、16……）及其“單元格格式”分別輸入相應(yīng)的單元格中（如果選“等差序列”，則輸入2、4、6、8……）。 ※巧妙輸入常用數(shù)據(jù) 有時我們需要輸入一些數(shù)據(jù)，如單位職工名單，有的職工姓名中生僻的字輸入極為困難，如果我們一次性定義好“職工姓名序列”，以后輸入就快多了。具體方法如下:將職工姓名輸入連續(xù)的單元格中，并選中它們，單擊“工具→選項”命令打開“選項”對話框，選“自定義序列”標簽（），先后按“導(dǎo)入”、“確定”按鈕。以后在任一單元格中輸入某一職工姓名（不一定非得是第一位職工的姓名），用“填充柄”即可將該職工后面的職工姓名快速填入后續(xù)的單元格中。 ※快速輸入特殊符號 有時候我們在一張工作表中要多次輸入同一個文本，特別是要多次輸入一些特殊符號（如※），非常麻煩，對錄入速度有較大的影響。這時我們可以用一次性替換的方法來克服這一缺陷。先在需要輸入這些符號的單元格中輸入一個代替的字母（如X，注意:不能是表格中需要的字母），等表格制作完成后，單擊“編輯→替換”命令，打開“替換”對話框（），在“查找內(nèi)容”下面的方框中輸入代替的字母“X”，在“替換為”下面的方框中輸入“※”，將“單元格匹配”前面的鉤去掉（否則會無法替換），然后按“替換”按鈕一個一個替換，也可以按“全部替換”按鈕，一次性全部替換完畢。 ※快速輸入相同文本 有時后面需要輸入的文本前面已經(jīng)輸入過了，可以采取快速復(fù)制（不是通常的“Ctrl+C”、“Ctrl+X”、“Ctrl+V”）的方法來完成輸入: 1.如果需要在一些連續(xù)的單元格中輸入同一文本（如“有限公司”），我們先在第一個單元格中輸入該文本，然后用“填充柄”將其復(fù)制到后續(xù)的單元格中。 2.如果需要輸入的文本在同一列中前面已經(jīng)輸入過，當你輸入該文本前面幾個字符時，系統(tǒng)會提示你，你只要直接按下Enter鍵就可以把后續(xù)文本輸入。 3.如果需要輸入的文本和上一個單元格的文本相同，直接按下“Ctrl＋D（或R）”鍵就可以完成輸入，其中“Ctrl+D”是向下填充，“Ctrl+R”是向右填充。 4.如果多個單元格需要輸入同樣的文本，我們可以在按住Ctrl鍵的同時，用鼠標點擊需要輸入同樣文本的所有單元格，然后輸入該文本，再按下“Ctrl+Enter”鍵即可。 ※快速給數(shù)字加上單位 有時我們需要給輸入的數(shù)值加上單位（如“立方米”等），少量的我們可以直接輸入，而大量的如果一個一個地輸入就顯得太慢了。我們用下面的方法來實現(xiàn)單位的自動輸入:先將數(shù)值輸入相應(yīng)的單元格中（注意:僅限于數(shù)值），然后在按住Ctrl鍵的同時，選取需要加同一單位的單元格，單擊“格式→單元格”命令，打開“單元格格式”對話框（），在“數(shù)字”標簽中，選中“分類”下面的“自定義”選項，再在“類型”下面的方框中輸入“#”“立”“方”“米”，按下確定鍵后，單位（立方米）即一次性加到相應(yīng)數(shù)值的后面。 ※巧妙輸入位數(shù)較多的數(shù)字 大家知道，如果向Excel中輸入位數(shù)比較多的數(shù)值（如身份證號碼），則系統(tǒng)會將其轉(zhuǎn)為科學(xué)計數(shù)的格式，與我們的輸入原意不相符，解決的方法是將該單元格中的數(shù)值設(shè)置成“文本”格式。如果用命令的方法直接去設(shè)置，也可以實現(xiàn)，但操作很慢。其實我們在輸入這些數(shù)值時，只要在數(shù)值的前面加上一個小“'”就可以了（注意:'必須是在英文狀態(tài)下輸入）。 ※快速在多個單元格中輸入相同公式 先選定一個區(qū)域，再鍵入公式，然后按“Ctrl+Enter”組合鍵，可以在區(qū)域內(nèi)的所有單元格中輸入同一公式。 ※同時在多個單元格中輸入相同內(nèi)容 選定需要輸入數(shù)據(jù)的單元格，單元格可以是相鄰的，也可以是不相鄰的，然后鍵入相應(yīng)數(shù)據(jù)，按“Ctrl+Enter”鍵即可。 ※快速輸入日期和時間 當前日期 選取一個單元格，并按“Ctrl+;” 當前時間 選取一個單元格，并按“Ctrl+Shift+;” 當前日期和時間 選取一個單元格，并按“Ctrl+;”，然后按空格鍵，最后按“Ctrl+Shift+;” 注意:當你使用這個技巧插入日期和時間時，所插入的信息是靜態(tài)的。要想自動更新信息，你必須使用TODAY和NOW函數(shù)。 ※快速輸入無序數(shù)據(jù) 在Excel數(shù)據(jù)表中，我們經(jīng)常要輸入大批量的數(shù)據(jù)，如學(xué)生的學(xué)籍號、身份證號等。這些數(shù)值一般都無規(guī)則，不能用“填充序列”的方法來完成。通過觀察后我們發(fā)現(xiàn)，這些數(shù)據(jù)至少前幾位是相同的，只有后面的幾位數(shù)值不同。通過下面的設(shè)置，我們只要輸入后面幾位不同的數(shù)據(jù)，前面相同的部分由系統(tǒng)自動添加，這樣就大大減少了輸入量。例如以學(xué)籍號為例，假設(shè)由8位數(shù)值組成，前4位相同，均為0301，后4位為不規(guī)則數(shù)字，如學(xué)籍號為03010056、03011369等。操作步驟如下：選中學(xué)籍號字段所在的列，單擊“格式”菜單中的“單元格”命令，在“分類”中選擇“自定義”，在“類型”文本框中輸入“03010000”（如圖2）。不同的4位數(shù)字全部用“0”來表示，有幾位不同就加入幾個“0”，[確定]退出后，輸入“56”按回車鍵，便得到了“03010056”，輸入“1369”按回車便得到了“03011369”。身份證號的輸入與此類似。 ※輸入公式 單擊將要在其中輸入公式的單元格，然后鍵入＝(等號)，若單擊了“編輯公式”按鈕或“粘貼函數(shù)”按鈕，Excel將插入一個等號，接著輸入公式內(nèi)容，按Enter鍵。 ※輸入人名時使用“分散對齊” 在Excel表格中輸入人名時為了美觀，我們一般要在兩個字的人名中間空出一個字的間距。按空格鍵是一個辦法，但是我們這里有更好的方法。我們以一列為例，將名單輸入后，選中該列，點擊“格式→單元格→對齊”，在“水平對齊”中選擇“分散對齊”，最后將列寬調(diào)整到最合適的寬度，整齊美觀的名單就做好了。 ※如何在excel單元格中輸入01 這個函數(shù)很管用...值得一試哦!例: =TEXT(A1,"00000") 把單元格設(shè)置為文本格式再輸入數(shù)據(jù)，或輸入'(撇號)再輸入數(shù)據(jù)，或根據(jù)要顯示的數(shù)字位數(shù)自定義單元格格式：如要顯示5位，不足5位的前面用0填足，自定義單元格格式：00000 輸入123顯示00123，輸入1顯示00001，輸入12345，顯示12345 ※在EXCEL中增加自動填充序列 在Excel中提供了自動填充功能，我們在使用時，可以通過拖動“填充柄”來完成數(shù)據(jù)的自動填充。例如要輸入甲、乙、丙、丁……，可以先在指定單元格輸入甲，然后將鼠標移至單元格的右下角的小方塊處，直至出現(xiàn)“＋”字，按住鼠標左鍵，向下（右）拖動至目的單元格，然后松開即完成了自動填充。可是有時我們會發(fā)現(xiàn)有一些數(shù)據(jù)序列不能自動填充，例如車間一、車間二、車間三等，填充方法有兩種： 第一種：單擊“菜單”欄上的“工具”，選“選項”→“自定義序列”，這時就可以在“輸入序列”欄輸入要定義的序列。需要注意的是每輸入完成一項就要回車一次，表示一項已經(jīng)輸入完畢，全部輸入完成以后單擊“添加”→“確定”，這樣我們自定義的序列就可以使用了。 第二種：首先把你要添加的序列輸入到一片相臨的單元格內(nèi)，例如要定義一個序列：車間一、車間二、車間三，把這三項分別輸入到單元H1：H3，單擊“工具”→“選項”→“自定義序列”→“導(dǎo)入”，在“導(dǎo)入序列所在的單元格”所指的對話框中輸入H1：H3，單擊“導(dǎo)入”→“添加”→“確定”，這樣新序列就產(chǎn)生了。 定義的序列如果不再使用，還可刪除，方法是：單擊“工具”→“選項”→“自定義序列”，在“自定義序列”框中，單擊要刪除的序列，再單擊“刪除”→“確定”。 ※如何輸入假分數(shù) 1又2分之1怎么輸入 單元格格式設(shè)成”分數(shù)“，單元格中輸入1.5，先輸入1，再按空白鍵；再輸入1/2， 輸入后是這樣 “1 1/2 ” ，不是內(nèi)行人看不懂的。 二分之一,四分之一, 四分之三可用ALT+189(188,190)獲得。 先輸入0，空格，再輸入3/2。 ※錄入準考證號碼有妙招 最近在學(xué)校參加招生報名工作，每位新生來校報到時，我們先請他們填寫一張信息表，例如姓名、性別、準考證號碼、聯(lián)系電話、郵編等內(nèi)容，然后在Excel中進行填寫，這樣無論是數(shù)據(jù)統(tǒng)計還是分班都方便多了。 準考證號碼是類似于“04360101”的8位數(shù)字，如果直接輸入的話，Excel會自作聰明地去除最前面的0，常規(guī)的做法是在錄入數(shù)字時手工輸入一個半角的單引號作為前導(dǎo)引號，但由于需要錄入的數(shù)據(jù)量太大，因此便將這一列設(shè)置成“文本”格式。 很快，我便發(fā)覺本地所有考生的準考證號碼中前4位數(shù)字都是相同的，是否可以想一個辦法讓Excel自動錄入最前面的“0436”呢？ 選定“準考證號碼”列，打開“格式→單元格格式→數(shù)字”對話框，如圖所示，在“分類”下拉列表框中選擇“自定義”項，在右側(cè)的“類型”欄中輸入“"0436"@”，這里的“0436”是準考證號碼最前面的4位數(shù)字，錄入時注意不要忘記前后的半角雙引號，最后點擊“確定”按鈕退出。 現(xiàn)在只需要錄入準考證號碼后面的4位數(shù)字，Excel會自動添加前面的“0436”，這樣效率明顯提高。 編輯提示：如果需要錄入的準考證號碼位數(shù)非常長，這樣可能會出現(xiàn)其他的顯示錯誤，因為Excel的缺省設(shè)置是單元格中輸入的數(shù)字被限制在11位，一旦超過將會以科學(xué)記數(shù)格式顯示所輸入的數(shù)字，例如“3365201740520301”將被顯示為“3．65202E＋14”；當輸入的數(shù)字超過15位時，第15位以后的數(shù)字將顯示為0。其實，除了將該列設(shè)置為“文本”格式外，此時我們還可以采取上述同樣的方法簡化錄入操作，畢竟最前面的幾位數(shù)字總是相同的。 ※向上填充的快捷鍵 我只會向下填充的快捷鍵，向上-向左-向右的都是什么呢？ 解答：向上-Alt+E,I,U。向左-Alt+E,I,L。向右-CTRL+R 一列中不輸入重復(fù)數(shù)字 [數(shù)據(jù)]--[有效性]--[自定義]--[公式] 輸入=COUNTIF(A:A,A1)=1 如果要查找重復(fù)輸入的數(shù)字 條件格式》公式》=COUNTIF(A:A,A5)>1》格式選紅色 單元格輸入 我想在A1單元格內(nèi)輸入1而A1自動會乘1000。格式寫為: #"000" 工具—選項—編輯—自動設(shè)置小數(shù)點：-3 大量0值輸入超級技巧 在單元格中輸入“=450**3”會等于450000 單元格 =45**N 時出現(xiàn) 45000 任一數(shù)字**N ，數(shù)字后面的**N 表示加 N 個零 如何在C列中輸入工號在D列顯示姓名 比如在A、B列中建立了工號對應(yīng)的姓名，如何在C列中輸入工號在D列顯示姓名。 假設(shè)你的數(shù)據(jù)區(qū)域在A1：B100，A列為工號，B列為姓名，C列為要輸入的工號，D列輸入以下公式： d1=vlookup(C1,$a$1:$b$100,2,false)輸入提示如何做 輸入提示是怎么做出來的，好像不是附注吧! 用數(shù)據(jù)有效性中的輸入信息功能就可實現(xiàn)自動跟蹤。 “數(shù)據(jù)>有效性>輸入信息”。 在信息輸入前就給予提示 在單元格輸入信息時，希望系統(tǒng)能自動的給予一些必要的提示，這樣不但可以減少信息輸入的錯誤，還可以減少修改所花費的時間。請問該如何實現(xiàn)？ 答：可以按如下操作：首先選擇需要給予輸入提示信息的所有單元格。然后執(zhí)行“數(shù)據(jù)”菜單中的“有效性”命令，在彈出的對話框中選擇“輸入信息”選項卡。接著在“標題”和“輸入信息”文本框中輸入提示信息的標題和內(nèi)容即可。 提示顯示在屏幕的右上角，離左邊的單元格太遠，一般人注意不到，達不到提示的目的。如何設(shè)置讓提示跟單元格走？ 數(shù)據(jù)有效性 只能輸入以"楊"開頭的字符串,或者是含有"龍"的字符串 =OR(LEFT(D35,1)="楊",NOT(ISERROR(FIND("龍",D35)))) 簡化 =(Left(a1)="楊")+Countif(a1,"*龍*")=(LEFT(A:A)="a")+COUNTIF(A:A,"*b*") |
|
|
