EXCEL函数公式培训PPT
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
LLB¿@TRU
=ISERROR #N/A = TRU
4/4/2020
23
•Jjül@@@Ç{@%/#N/A@@
ISNA
6ISNA( : #N/AB¿Ê/TRU
=ISNA #N/A = TRU
4/4/2020
24
• țv@{Ç}Ş@șȘŻgźğ@Ø@AND
• jv@jçjâ@yfiîãÅg&oR
4/4/2020
14
• @@@@ p§§@{)}YEAR
• @@@@p§§@{))MONTH
• @@@@ p§§@@@DATE
• @@@,¿¿¿ @@@TODAY
• @@fi11WBM-1&3fiaJNOW
• @@7•]Q¿Q@@•@@@WEEKDAY
4/4/2020
l5
@ĞpÿŚ@{gYEAR
=NOT =NOT
= FALSE = TRU
VV
4/4/2020
28
4.o:Ğ«:ta øIF
&1žÂftÜ. &îžÂftfá
,2,3 =
,2,3 =
3,
2,6,7),0)
V
V V
V
4/4/2020
29
• @@Q@LEFT RIGHT MID • ¿Ț@@țȚ]€:@LEN • @@@țȚig¡@CONCATENATE
,0 =
4/4/2020
36
7.@@@@îȘ./țȘ//@@VALU
6VALU
=VALU
--
4/4/2020
37
•ȘkQ@•ț•ețg@ ya@țgțgEXACT
• ]v@{@$${\Ț/@:$:TEXT
• ț<@{ISgețgțg{gȚj@{IVALU
4/4/2020
30
z•aoa sLEFT RIGHT MID
aLEFT( @Ø , Ø @) ÍȚÑ@@@
aRIGHT Байду номын сангаас @Ø , @@) ȚȚ@@@@
OMID (@Ø .RØ {ü , 811) 1ń9läÄB
--
"abcdef“ ,2 = ab
=RIGHT "abcdef“ ,2 =ef
= MID "abcdef“ ,2,3 = bcd
4/4/2020
31
2.ÏȚ@Ÿ-îfi]@ÿ LEN
LEN =LEN "abcdef“ =
4/4/2020
EWCT(
TRUE,@Ę¿ØFALSE. =EXACT “a“ , “a“ =EXACT “a“ , “ab“ =EXACT “a“
-- TRU -- FALSE -- FALSE
4/4/2020
35
6.fi’o@@țfi//@gîTEXT
6TEXT(
@@@țgfgȚI@:&,@@—
@ȚI0.
=TEXT
4/4/2020
4/4/2020
2
SU (A1,B2,...)
4/4/2020
3
4/4/2020
V V V
4
Až $Až ÿÿ&@AğË A$ž
$A$ž ÿÿ&@AğËØ@žțȘ
4/4/2020
4/4/2020
6
• @@:SU
• @{ÇȘî@:SUMIF
• Șî•ț•@:COUNT • ȘîțȚ@@{Ç•ț•@:COUNTIF
4/4/2020
10
4./îñÔØț$•î•Ø:COUNTIF
6COUNTIF(
100 20 1000 200 40 2000 300 60 3000 =COUNTIF(A1:A3, “ ——200“
V
=COUNTIF(A1:A3, “ 200“
V
4/4/2020
11
5.M@@Ô JØ :AVERAG
• țv@{Ç{@@țË@Ø@NOT
4/4/2020
25
1.ță@Ț$Ț@@I?fi$îâ%dj@AN
6AND(
)g@@@țȚ(fitȘ@TRUE , ¿n/@TRUE; §§@ț£—•ț•@FALSE , ¿n/@FALSE
=AN
3,2 = TRU
=AN
3,2 = FALSE
VV VV
4/4/2020
A1=2003-07-29 Œț!g =WEEKDAY A1 =
4/4/2020
21
6g!ț@@@ȚjțȘQ@Q@ISERROR 6g!ț@@@ȚjțȘQ@#N/AQ@ ISNA
4/4/2020
22
1.gîȚ@@@/ț@î%/@@ISERR0R
6ISERROR(
: #N/A, #VALUE, #REF, #DIV/0, M, #NAM ?
300 60 3000
= MIF(A1:A3,
——200“ ,B1:B3)=
= MIF(A1:A3,
4/4/2020 zz ——200“ , C 1 : C 3 ) =
9
VV
3.fiŻú•@:CO NT
aCOUNT(
100 20 1000 200 40 2000 300 60 3000 =COUNT(A1:A3)=3 =COUNT(B1:B3)=3
aAVEMGE(
100 20 1000 200 40 2000 300 60 3000 =AVERAGE A1 : A3 = =AVERAGE A1 :A3 , =
4/4/2020
12
6.$$ @,£,J@$§:ROU
6ROUND( @@,r]‹@{€)
100 20 1000
200 40 2000
32
z•ao«»aø coNCATENATE
aCONCATENATE(
=CONCATENATE “a“ , "ef“ =
aef
4/4/2020
33
D(
--
“a“ , "abcaef“ ,1 =
=FIND “a“ , "abcaef“ ,2 =
4/4/2020
34
.ĘĘØ@•ț•Țż/@ØØA@/EXACT
18
4.@@.@źȘč¿/ @TODAY
aTODAY(
A1=2003-07-29 Œț!g
=TODAY = 2003-07-29
4/4/2020
19
NOW(
NOW
A1=2003-07-29 Œț!g
=NOW = 2003-07-29
4/4/2020
20
6.@@IÿĞB@@Ø@@WEEKDAY
aWEEKDAY(Ø @
• Șî@@@Ïg@:AVERAGE
• @@Œ,/\,Ø @:ROU
4/4/2020
7
OSU
A1=
A2= A3
= (A1,A3)= +8= = (A1:A3)= +7+
4/4/2020
8
2.@{$M@:SUMIF
6SUMIF(;o@,@{t,@¿?@;o@)
100 20 1000
200 40 2000
aYEAR(
A1=2003-07-29 Œț!g
=YEAR A1 =
4/4/2020
16
Ø @/p§@ÿ {UMONTH
MONTH
A1=2003-07-29 Œț!g
=MONTH A1 =
4/4/2020
17
DATE(
A1=2003-07-29 =DATE A1 =
DATE
Œț!g
4/4/2020
26
OOR(
FALSE , ¿n/@FALSE ; §p@ț£—•ț•@TRUE, ¿n/@TRUE
=OR 3,2 =TRU =OR 3,2 =TRU =OR 3,2 = FALSE
VVV VVV
4/4/2020
27
z•›« taaxeaæ NoT
NOT(
)g@Ø@țÇ(Ñ@FALSE , ¿n/@TRU ; §§@@TRU , ¿n/@FALSE
300 60 3000
=ROUND(A1/A3,2)=0.33 =ROUNDUP(A1/A3,2)=0.34 =ROUNDDOWN(A1/A3,2)=0.33
4/4/2020
13
7.)țț(“»:
100 20 1000 200 40 2000 300 60 3000
=WNK(A1,A1:A3,1)= =WNK(A1,A1:A3,0)=
=ISERROR #N/A = TRU
4/4/2020
23
•Jjül@@@Ç{@%/#N/A@@
ISNA
6ISNA( : #N/AB¿Ê/TRU
=ISNA #N/A = TRU
4/4/2020
24
• țv@{Ç}Ş@șȘŻgźğ@Ø@AND
• jv@jçjâ@yfiîãÅg&oR
4/4/2020
14
• @@@@ p§§@{)}YEAR
• @@@@p§§@{))MONTH
• @@@@ p§§@@@DATE
• @@@,¿¿¿ @@@TODAY
• @@fi11WBM-1&3fiaJNOW
• @@7•]Q¿Q@@•@@@WEEKDAY
4/4/2020
l5
@ĞpÿŚ@{gYEAR
=NOT =NOT
= FALSE = TRU
VV
4/4/2020
28
4.o:Ğ«:ta øIF
&1žÂftÜ. &îžÂftfá
,2,3 =
,2,3 =
3,
2,6,7),0)
V
V V
V
4/4/2020
29
• @@Q@LEFT RIGHT MID • ¿Ț@@țȚ]€:@LEN • @@@țȚig¡@CONCATENATE
,0 =
4/4/2020
36
7.@@@@îȘ./țȘ//@@VALU
6VALU
=VALU
--
4/4/2020
37
•ȘkQ@•ț•ețg@ ya@țgțgEXACT
• ]v@{@$${\Ț/@:$:TEXT
• ț<@{ISgețgțg{gȚj@{IVALU
4/4/2020
30
z•aoa sLEFT RIGHT MID
aLEFT( @Ø , Ø @) ÍȚÑ@@@
aRIGHT Байду номын сангаас @Ø , @@) ȚȚ@@@@
OMID (@Ø .RØ {ü , 811) 1ń9läÄB
--
"abcdef“ ,2 = ab
=RIGHT "abcdef“ ,2 =ef
= MID "abcdef“ ,2,3 = bcd
4/4/2020
31
2.ÏȚ@Ÿ-îfi]@ÿ LEN
LEN =LEN "abcdef“ =
4/4/2020
EWCT(
TRUE,@Ę¿ØFALSE. =EXACT “a“ , “a“ =EXACT “a“ , “ab“ =EXACT “a“
-- TRU -- FALSE -- FALSE
4/4/2020
35
6.fi’o@@țfi//@gîTEXT
6TEXT(
@@@țgfgȚI@:&,@@—
@ȚI0.
=TEXT
4/4/2020
4/4/2020
2
SU (A1,B2,...)
4/4/2020
3
4/4/2020
V V V
4
Až $Až ÿÿ&@AğË A$ž
$A$ž ÿÿ&@AğËØ@žțȘ
4/4/2020
4/4/2020
6
• @@:SU
• @{ÇȘî@:SUMIF
• Șî•ț•@:COUNT • ȘîțȚ@@{Ç•ț•@:COUNTIF
4/4/2020
10
4./îñÔØț$•î•Ø:COUNTIF
6COUNTIF(
100 20 1000 200 40 2000 300 60 3000 =COUNTIF(A1:A3, “ ——200“
V
=COUNTIF(A1:A3, “ 200“
V
4/4/2020
11
5.M@@Ô JØ :AVERAG
• țv@{Ç{@@țË@Ø@NOT
4/4/2020
25
1.ță@Ț$Ț@@I?fi$îâ%dj@AN
6AND(
)g@@@țȚ(fitȘ@TRUE , ¿n/@TRUE; §§@ț£—•ț•@FALSE , ¿n/@FALSE
=AN
3,2 = TRU
=AN
3,2 = FALSE
VV VV
4/4/2020
A1=2003-07-29 Œț!g =WEEKDAY A1 =
4/4/2020
21
6g!ț@@@ȚjțȘQ@Q@ISERROR 6g!ț@@@ȚjțȘQ@#N/AQ@ ISNA
4/4/2020
22
1.gîȚ@@@/ț@î%/@@ISERR0R
6ISERROR(
: #N/A, #VALUE, #REF, #DIV/0, M, #NAM ?
300 60 3000
= MIF(A1:A3,
——200“ ,B1:B3)=
= MIF(A1:A3,
4/4/2020 zz ——200“ , C 1 : C 3 ) =
9
VV
3.fiŻú•@:CO NT
aCOUNT(
100 20 1000 200 40 2000 300 60 3000 =COUNT(A1:A3)=3 =COUNT(B1:B3)=3
aAVEMGE(
100 20 1000 200 40 2000 300 60 3000 =AVERAGE A1 : A3 = =AVERAGE A1 :A3 , =
4/4/2020
12
6.$$ @,£,J@$§:ROU
6ROUND( @@,r]‹@{€)
100 20 1000
200 40 2000
32
z•ao«»aø coNCATENATE
aCONCATENATE(
=CONCATENATE “a“ , "ef“ =
aef
4/4/2020
33
D(
--
“a“ , "abcaef“ ,1 =
=FIND “a“ , "abcaef“ ,2 =
4/4/2020
34
.ĘĘØ@•ț•Țż/@ØØA@/EXACT
18
4.@@.@źȘč¿/ @TODAY
aTODAY(
A1=2003-07-29 Œț!g
=TODAY = 2003-07-29
4/4/2020
19
NOW(
NOW
A1=2003-07-29 Œț!g
=NOW = 2003-07-29
4/4/2020
20
6.@@IÿĞB@@Ø@@WEEKDAY
aWEEKDAY(Ø @
• Șî@@@Ïg@:AVERAGE
• @@Œ,/\,Ø @:ROU
4/4/2020
7
OSU
A1=
A2= A3
= (A1,A3)= +8= = (A1:A3)= +7+
4/4/2020
8
2.@{$M@:SUMIF
6SUMIF(;o@,@{t,@¿?@;o@)
100 20 1000
200 40 2000
aYEAR(
A1=2003-07-29 Œț!g
=YEAR A1 =
4/4/2020
16
Ø @/p§@ÿ {UMONTH
MONTH
A1=2003-07-29 Œț!g
=MONTH A1 =
4/4/2020
17
DATE(
A1=2003-07-29 =DATE A1 =
DATE
Œț!g
4/4/2020
26
OOR(
FALSE , ¿n/@FALSE ; §p@ț£—•ț•@TRUE, ¿n/@TRUE
=OR 3,2 =TRU =OR 3,2 =TRU =OR 3,2 = FALSE
VVV VVV
4/4/2020
27
z•›« taaxeaæ NoT
NOT(
)g@Ø@țÇ(Ñ@FALSE , ¿n/@TRU ; §§@@TRU , ¿n/@FALSE
300 60 3000
=ROUND(A1/A3,2)=0.33 =ROUNDUP(A1/A3,2)=0.34 =ROUNDDOWN(A1/A3,2)=0.33
4/4/2020
13
7.)țț(“»:
100 20 1000 200 40 2000 300 60 3000
=WNK(A1,A1:A3,1)= =WNK(A1,A1:A3,0)=