Word:
һ =سõһ˫ֱߣ
 ~سõһߣ
 * - #سϲࣻ
ڵԪ=now   ʾ
ڵԪ=CHOOSE(WEEKDAY(I3,2),"һ","ڶ","","","","","")         ʾڼ
Excelúȫ




1ABS 
ƣABS 
ҪܣӦֵľֵ 
ʹøʽABS(number) 
˵numberҪֵֵõĵԪ 
ӦþB2Ԫ빫ʽ=ABS(A2)A2Ԫ100Ǹ-100B2оʾ100 
رѣnumberֵһЩַAȣB2зشֵ#VALUE
2AND 
ƣAND 
Ҫܣ߼ֵвֵΪ߼棨TRUE򷵻߼棨TRUE֮߼٣FALSE 
ʹøʽAND(logical1,logical2, ...) 
˵Logical1,Logical2,Logical3ʾԵֵʽ30 
ӦþC5Ԫ빫ʽ=AND(A5>=60,B5>=60)ȷϡC5зTRUE˵A5B5еֵڵ60FALSE˵A5B5еֵһС60 
رѣָ߼а߼ֵʱشֵ#VALUE!#NAME
3AVERAGE 
ƣAVERAGE 
Ҫܣвƽֵ 
ʹøʽAVERAGE(number1,number2,) 
˵number1,number2,ҪƽֵֵõԪ򣩣30 
ӦþB8Ԫ빫ʽ=AVERAGE(B77,F7:H7,7,8)ȷϺ󣬼B7D7F7H7еֵ78ƽֵ 
رѣа0ֵԪڣаհ׻ַԪ򲻼ڡ
4COLUMN  
ƣCOLUMN 
ҪܣʾõԪбֵ 
ʹøʽCOLUMN(reference) 
˵referenceΪõĵԪ 
ӦþC11Ԫ빫ʽ=COLUMN(B11)ȷϺʾΪ2BУ 
رѣB11Ԫ빫ʽ=COLUMN()Ҳʾ2֮ӦĻһбֵĺROW(reference)
5CONCATENATE 
ƣCONCATENATE 
ҪܣַıԪеһʾһԪС 
ʹøʽCONCATENATE(Text1Text) 
˵Text1Text2ΪҪӵַıõĵԪ 
ӦþC14Ԫ빫ʽ=CONCATENATE(A14,"@",B14,".com")ȷϺ󣬼ɽA14Ԫַ@B14Ԫеַ.comӳһ壬ʾC14ԪС 
رѣõĵԪΪıʽģӢ״̬µ˫ţʽΪ=A14&"@"&B14&".com"ҲܴﵽͬĿġ
6COUNTIF 
ƣCOUNTIF 
ҪܣͳĳԪзָĵԪĿ 
ʹøʽCOUNTIF(Range,Criteria) 
˵RangeҪͳƵĵԪCriteriaʾָʽ 
ӦþC17Ԫ빫ʽ=COUNTIF(B1:B13,">=80")ȷϺ󣬼ͳƳB1B13ԪУֵڵ80ĵԪĿ 
رѣõĵԪпհ׵Ԫ֡
7DATE 
ƣDATE 
Ҫܣֵָڡ 
ʹøʽDATE(year,month,day) 
˵yearΪֵָС9999monthΪָ·ֵԴ12dayΪָ 
ӦþC20Ԫ빫ʽ=DATE(2003,13,35)ȷϺʾ2004-2-4 
رѣʽУ·Ϊ13һ£˳20041£Ϊ3520041µʵֶ4죬˳200424ա
8ƣDATEDIF
Ҫܣ㷵ڲĲֵ
ʹøʽ=DATEDIF(date1,date2,"y")=DATEDIF(date1,date2,"m")=DATEDIF(date1,date2,"d")
˵date1ǰһڣdate2һڣymdҪ󷵻꣨¡죩
ӦþC23Ԫ빫ʽ=DATEDIF(A23,TODAY(),"y")ȷϺ󷵻ϵͳǰ[TODAY()ʾA23ԪڵĲֵ
رѣExcelеһغںҲģֱʹãڼ䡢ȷǳЧ
9DAY
ƣDAY
ҪܣָڻõԪеڵ
ʹøʽDAY(serial_number)
˵serial_numberָڻõĵԪ
Ӧþ빫ʽ=DAY("2003-12-18")ȷϺʾ18
رѣǸڣӢ˫С
10DCOUNT
ƣDCOUNT
ҪܣݿбָҰֵĵԪĿ
ʹøʽDCOUNT(database,field,criteria)
˵DatabaseʾҪͳƵĵԪFieldʾʹõУڵһбҪб־CriteriaĵԪ
Ӧþͼ1ʾF4Ԫ빫ʽ=DCOUNT(A111,"",F1:G2)ȷϺ󼴿ġУɼڵ70С80ֵԪĿ൱ڷ

رѣʽ޸Ϊ=DCOUNT(A111,,F1:G2)ҲԴﵽͬĿġ
11FREQUENCY
ƣFREQUENCY
Ҫܣһдֱ鷵ĳݵƵʷֲ
ʹøʽFREQUENCY(data_array,bins_array)
˵Data_arrayʾƵʵһݻԪBins_arrayʾΪǰзָһֵ
Ӧþͼ2ʾͬʱѡB32B36Ԫ빫ʽ=FREQUENCY(B2:B31,D2:D36)ɺ¡Ctrl+Shift+EnterϼȷϣB2B31УD2D36зָĸֵĳƵĿ൱ͳƸ
  
رѣһ鹫ʽɺҪͨCtrl+Shift+EnterϼȷϣȷϺʽ˳һԴţ{}˴Ųֱ롣
12IF
ƣIF
Ҫܣݶָ߼жϵٽӦݡ
ʹøʽ=IF(Logical,Value_if_true,Value_if_false)
˵Logical߼жϱʽValue_if_trueʾжΪ߼棨TRUEʱʾݣԷءTRUEValue_if_falseʾжΪ߼٣FALSEʱʾݣԷءFALSE
ӦþC29Ԫ빫ʽ=IF(C26>=18,"Ҫ","Ҫ")ȷԺC26Ԫеֵڻ18C29ԪʾҪ֮ʾҪ
رѣơC29Ԫ빫ʽָĵԪ񣬶ʹʱҪԼ˴ֻϱʵҪӦԪ񣬾Ҳοʵļ
13INDEX
ƣINDEX
ҪܣбеԪֵԪźŵֵȷ
ʹøʽINDEX(array,row_num,column_num)
˵ArrayԪ鳣Row_numʾָţʡrow_num column_numColumn_numʾָţʡcolumn_num row_num
Ӧþͼ3ʾF8Ԫ빫ʽ=INDEX(A1:D11,4,3)ȷϺʾA1D11ԪУ4к͵3н洦ĵԪ񣨼C4еݡ

رѣ˴Ųrow_numŲcolumn_numõĵԪԵģExcelелš
    14INT
ƣINT
ҪܣֵȡΪӽ
ʹøʽINT(number)
˵numberʾҪȡֵֵõԪ񡣡
Ӧþ빫ʽ=INT(18.89)ȷϺʾ18
رѣȡʱ룻ĹʽΪ=INT(-18.89)򷵻ؽΪ-19
   
    15ISERROR
ƣISERROR
ҪܣڲԺʽصֵǷддúTRUE֮FALSE
ʹøʽISERROR(value)
˵ValueʾҪԵֵʽ
Ӧþ빫ʽ=ISERROR(A35/B35)ȷԺB35ԪΪջ0A35/B35ִ󣬴ʱǰTRUE֮FALSE
رѣ˺ͨIFʹãʽ޸Ϊ=IF(ISERROR(A35/B35),"",A35/B35)B35Ϊջ0ӦĵԪʾΪգ֮ʾA35/B35
Ľ
16LEFT
ƣLEFT
ҪܣһıַĵһַʼȡָĿַ
ʹøʽLEFT(text,num_chars)
˵textҪַַnum_charsĽȡĿ
ӦþٶA38Ԫбˡϲ켫ַC38Ԫ빫ʽ=LEFT(A38,3)ȷϺʾϲַ
رѣ˺Ӣ˼Ϊ󡱣߽ȡExcelܶຯȡӢĵ˼
17LEN
ƣLEN
ҪܣͳıַַĿ
ʹøʽLEN(text)
˵textʾҪͳƵıַ
ӦþٶA41Ԫбˡҽ28ꡱַC40Ԫ빫ʽ=LEN(A40)ȷϺʾͳƽ6
رѣLENҪͳʱȫַǰַÿַΪ1֮ӦһLENBͳʱַΪ1ȫַΪ2
18MATCH
ƣMATCH
ҪܣָʽֵָƥԪصӦλá
ʹøʽMATCH(lookup_value,lookup_array,match_type)
˵Lookup_valueҪݱвҵֵ
Lookup_arrayʾܰҪҵֵԪ
Match_typeʾҷʽֵ-101
match_typeΪ-1Ҵڻ lookup_valueСֵLookup_array 밴У
match_typeΪ1Сڻ lookup_value ֵLookup_array 밴У
match_typeΪ0ҵlookup_value ĵһֵLookup_array ԰κ˳Уʡmatch_typeĬΪ1
Ӧþͼ4ʾF2Ԫ빫ʽ=MATCH(E2,B1:B11,0)ȷϺ򷵻زҵĽ9

رѣLookup_arrayֻΪһлһС
19MAX
ƣMAX
Ҫܣһеֵ
ʹøʽMAX(number1,number2)
˵number1,number2ҪֵֵõԪ򣩣30
Ӧþ빫ʽ=MAX(E44:J44,7,8,9,10)ȷϺ󼴿ʾE44J44Ԫֵ78910еֵ
رѣı߼ֵԡ
20MID
ƣMID
ҪܣһıַָλÿʼȡָĿַ
ʹøʽMID(text,start_num,num_chars)
˵textһıַstart_numʾָʼλãnum_charsʾҪȡĿ
ӦþٶA47Ԫбˡϲ켫ַC47Ԫ빫ʽ=MID(A47,4,3)ȷϺʾ켫ַ
رѣʽи䣬ҪӢ״̬µĶš,
21MIN
ƣMIN
ҪܣһеСֵ
ʹøʽMIN(number1,number2)
˵number1,number2ҪСֵֵõԪ򣩣30
Ӧþ빫ʽ=MIN(E44:J44,7,8,9,10)ȷϺ󼴿ʾE44J44Ԫֵ78910еСֵ
رѣı߼ֵԡ
22MOD
ƣMOD
Ҫܣ
ʹøʽMOD(number,divisor)
˵numberdivisor
Ӧþ빫ʽ=MOD(13,4)ȷϺʾ1
رѣdivisorΪ㣬ʾֵ#DIV/0!MODԽúINTʾʽ޸Ϊ=13-4*INT(13/4)
23MONTH
ƣMONTH
ҪܣָڻõԪеڵ·ݡ
ʹøʽMONTH(serial_number)
˵serial_numberָڻõĵԪ
Ӧþ빫ʽ=MONTH("2003-12-18")ȷϺʾ11
رѣǸڣӢ˫Уʽ޸Ϊ=YEAR("2003-12-18")򷵻ݶӦֵ2003
24NOW
ƣNOW
Ҫܣǰϵͳںʱ䡣
ʹøʽNOW()
˵úҪ
Ӧþ빫ʽ=NOW()ȷϺ󼴿ʾǰϵͳںʱ䡣ϵͳںʱ䷢˸ı䣬ֻҪһF9ܼ֮ı䡣
رѣʾںʱʽͨԪʽá
25OR
ƣOR
Ҫܣ߼ֵвֵΪ߼٣FALSEʱغ߼٣FALSE򶼷߼棨TRUE
ʹøʽOR(logical1,logical2, ...)
˵Logical1,Logical2,Logical3ʾԵֵʽ30
ӦþC62Ԫ빫ʽ=OR(A62>=60,B62>=60)ȷϡC62зTRUE˵A62B62еֵһڻ60FALSE˵A62B62еֵС60
رѣָ߼а߼ֵʱشֵ#VALUE!#NAME
26RANK
ƣRANK
Ҫܣĳһֵһֵеֵλ
ʹøʽRANKNumber,ref,order
˵NumberҪֵrefֵĵԪorderʽΪ0ߺԣ򰴽ֵԽֵԽСΪǡ0ֵֵԽֵԽ󣻣
ӦþC2Ԫ빫ʽ=RANK(B2,$B$2B$31,0)ȷϺ󼴿ɵó1ͬѧĳɼȫɼе
رѣʽУNumberȡʽrefȡ˾ʽһ$ţúѡC2Ԫ񣬽õԪ½ǣϸʮ״ʱ֮ͨΪסɽʽٸƵCĵԪУͬѧĳɼͳơ
27RIGHT
ƣRIGHT
ҪܣһıַһַʼȡָĿַ
ʹøʽRIGHT(text,num_chars)
˵textҪַַnum_charsĽȡĿ
ӦþٶA65Ԫбˡϲ켫ַC65Ԫ빫ʽ=RIGHT(A65,3)ȷϺʾ켫ַ
رѣNum_charsڻ0ԣĬΪ1num_charsıȣı
28SUBTOTAL
ƣSUBTOTAL
Ҫܣбݿеķܡ
ʹøʽSUBTOTAL(function_num, ref1, ref2, ...)
˵Function_numΪ111ֵ101111ֵָ֣֮ʹʲôбнзܼ㣨ͼ6ref1, ref2,Ҫзã29
Ӧþͼ7ʾB64C64Ԫзֱ빫ʽ=SUBTOTAL(3,C2:C63)=SUBTOTAL103,C2:C63)ҽ61ȷϺǰʾΪ62صУʾΪ61صС


رѣȡԶɸѡfunction_numѡʲôͣSUBTOTALκβɸѡеУSUBTOTALлֱ򣬲лˮƽ
29ƣSUM
Ҫܣвֵĺ͡
ʹøʽSUMNumber1,Number2
˵Number1Number2ҪֵǾֵõĵԪ򣩡߼ֵȡ
Ӧþͼ7ʾD64Ԫ빫ʽ=SUM(D2:D63)ȷϺ󼴿ĵܷ֡
رѣΪãֻеֽ㡣еĿհ׵Ԫ߼ֵıֵԣʽ޸Ϊ=SUM(LARGE(D2:D63,{1,2,3,4,5}))ǰ5ɼĺ͡
30SUMIF
ƣSUMIF
ҪܣָĵԪڵֵ͡
ʹøʽSUMIFRange,Criteria,Sum_Range
˵RangeжϵĵԪCriteriaΪָʽSum_RangeҪֵڵĵԪ
Ӧþͼ7ʾD64Ԫ빫ʽ=SUMIF(C2:C63,"",D2:D63)ȷϺ󼴿Сĳɼ͡
رѣʽ޸Ϊ=SUMIF(C2:C63,"Ů",D2:D63)ŮĳɼͣСС͡Ůı͵ģҪӢ״̬µ˫ţ"""Ů"С
31TEXT
ƣTEXT
ҪܣֵָʽӦתΪıʽ
ʹøʽTEXT(value,format_text)
˵valueҪתֵõĵԪformat_textΪָʽָʽ
ӦþB68Ԫбֵ1280.45C68Ԫ빫ʽ=TEXT(B68, "$0.00")ȷϺʾΪ$1280.45
رѣformat_textԸݡԪʽԻ֡ǩеͽȷ
32TODAY
ƣTODAY
Ҫܣϵͳڡ
ʹøʽTODAY()
˵úҪ
Ӧþ빫ʽ=TODAY()ȷϺ󼴿ʾϵͳںʱ䡣ϵͳںʱ䷢˸ı䣬ֻҪһF9ܼ֮ı䡣
رѣʾڸʽͨԪʽãμ
33VALUE
ƣVALUE
ҪܣһֵıַתΪֵ͡
ʹøʽVALUE(text)
˵textҪתıֵַ
ӦþB74ԪͨLEFTȺȡıַC74Ԫ빫ʽ=VALUE(B74)ȷϺ󣬼ɽתΪֵ͡
رѣıֵתúЩֵʱش
34VLOOKUP
ƣVLOOKUP
Ҫܣݱвֵָɴ˷ݱǰָдֵ
ʹøʽVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
˵Lookup_valueҪҵֵTable_arrayҪвݵĵԪCol_index_numΪtable_arrayдصƥֵţCol_index_numΪ2ʱ,table_array2еֵΪ3ʱص3еֵRange_lookupΪһ߼ֵΪTRUEʡԣ򷵻ؽƥֵҲ˵Ҳȷƥֵ򷵻Сlookup_valueֵΪFALSE򷵻ؾȷƥֵҲ򷵻شֵ#N/A
Ӧþμͼ7D65Ԫ빫ʽ=VLOOKUP(B65,B2:D63,3,FALSE)ȷϺֻҪB65Ԫһѧ綡48D65ԪмʾѧԳɼ
رѣLookup_valueμTable_arrayУRange_lookupTable_arrayбڴ˺УйRange_lookup÷Ǵġ
35WEEKDAY
ƣWEEKDAY
ҪܣָڵĶӦ
ʹøʽWEEKDAY(serial_number,return_type)
˵serial_numberָڻúڵĵԪreturn_typeڵıʾʽ[SundayգΪ1SaturdayΪ7ʱòΪ1MondayһΪ1SundayգΪ7ʱòΪ2й˵ϰߣMondayһΪ0SundayգΪ6ʱòΪ3]
Ӧþ빫ʽ=WEEKDAY(TODAY(),2)ȷϺ󼴸ϵͳڵ
رѣָڣӢ״̬µ˫У=WEEKDAY("2003-12-18",2)



һԪɫЧ
ȫѡ񣬸ʽʽѡ񡰹ʽʽ£ȻѡʽťͼѡҪɫ


1.ɫЧɫ
=MOD(ROW(),2)=1
2.ɫЧżɫ
=MOD(ROW(),2)=0
3.ϣøʽΪÿ3Ӧһεƣʹùʽ
=MOD(ROW(),3)=1
4.ϣżвͬƣֻҪѹʽеROW()ΪCOLUMN()ɣ磺
=MOD(COLUMN(),2)
5.ϣùʽ(ɫԶɫ):
=MOD(ROW()+COLUMN(),2) 
˵ʽĹʽжкк֮ͳ2ǷΪ0Ϊ0˵żͬ䵥ԪΪָɫͲ䡣ʽУʽһʱ0ּΪTRUE0ʹֵΪFALSEˣĹʽҲдΪ
=MOD(ROW()+COLUMN(),2)<>0
6.ϣùʽ(ԶɫԶɫ):
ʽ2
=MOD(ROW()+COLUMN(),2)=0 
ɫɸѡ
excel2007԰Ԫɫɫͼ
excel2003ҪӸУöƵķõӦɫţȻԸн
ɫԪAУѡB1,->->壬a빫ʽ =get.cell(24,$a1)
˷ɵõɫкš B1 =a ʽ¸ƵӦС
ȫB򼴿ɡñɫкţ24ĳ63ɡ
˵һ£
1.Excel2003ûӦĺExcel4.0һGet.Cellĺ꺯꺯ֱExcel2003ֱã붨Ʋʹ 
2.Get.Cell(Num,Cell)ĸʽGet.CellȡһԪеĶϢ߿ʽȣNumָľҪȡϢ24ȡԪɫϢȡһ156֣ʾĳɫı 
3.B1㶨ƲˣôB1е־A1еɫ룬B2е־ͽA2еɫ룬Դ 
4.ٰBʵϾǰAеɫˡ
Get.Cellĸ壺 
1 մľԵַ 
2 մк 
3 մ 
4  TYPE  
5 յַ 
6 ʾյַĹʽ 
7 յַĸʽʾ 
8 ʾյַĸʽ 
9 شʽʾ 
10 شҷʽʾ 
11 شʽʾ 
12 شʽʾ 
13 ڲͼʾ 
14 趨 locked True 
15 ʽ״̬ True 
16 ش 
17 ԵΪλش߶ 
18  
19 ԵΪλֺ 
20 лһַΪӴִ True 
21 лһַΪб崫 True 
22 лһַΪߴTrue 
23 лһַмһˮƽߴ True 
24 شһַɫ֣ 1  56趨ΪԶ 0 
25 MS Excelִ֧ٸʽ 
26 MS Excel֧Ӱʽ 
27 ʾֶķҳ趨 
28 ٵв 
29 ٵ 
30 ΧΪٵժҪΪ True 
31 ΧΪٵժҪΪ True 
32 ʾҳ͹ 
33 ʽΪΪ True 
34 شɫʣʾ趨ΪԶ 0 
35 شҷɫʣʾ趨ΪԶ 0 
36 شϷɫʣʾ趨ΪԶ 0 
37 ش·ɫʣʾ趨ΪԶ 0 
38 شǰӰɫʣʾ趨ΪԶ 0 
39 شӰӰɫʣʾ趨ΪԶ 0 
40 ʾʽ 
41 زյַԭʼʽ 
42 ԵΪλʹдˮƽ 
43 ԵΪλʹдϷϷֱ 
44 ԵΪλʹдҷˮƽ 
45 ԵΪλʹдϷ·ֱ 
46 вע True 
47 вʾ True 
48 в빫ʽ True 
49 鹫ʽķΧ True 
50 شֱ룬ʾ 
51 شֱʾ 
52 شǰ׺ַ 
53 ʾشʾ 
54 ش͸ӱ 
55 ش͸ӱλ 
56 Ŧ 
57 лһַΪϱ괫True 
58 ʾشлһַʽ 
59 شʽʾ 
60 лһַΪ±괫True 
61 Ŧ 
62 ʾҳ͹ 
63 شɫ 
64 ͼǰɫ 
65 Ŧ 
66 ʾҳ 
