TP Excel N°3 Les fonctions logiques

!"#$%&'(## # # )*+,-)*+.#
/012'34156#7804590510'#)#
:6;935'<'05#4&1'0&'4#='#>'45180#
#
TP#Excel#N°3#
Les#fonctions#logiques#
:904#&'#!"#28?4#9(('@#='2813#3648?=3'#(A'%'3&1&'#+*#B?1#&8051'05#41%#C'?1(('4#='#&9(&?(D#7E9B?'#C'?1(('#
'45#?0#'%'3&1&'#F#3648?=3'D#
$%;(1&95180#4?3#('#=638?('<'05#='4#!"#$%&'(D#:904#(A'%'3&1&'#+*#28?4#92'@#('4#C'?1(('4#G##
+D H80&5180#I#JK#L#
)D J1#1<M31B?6#
,D H80&51804#(8>1B?'4#I#$!#L#
.D H80&5180#JK#$!#
ND OPJK#
QD J8<<'#&80=151800'(('##
R8?4#9(('@#'449S'3#='#<8=1C1'3#&'4#C'?1(('4#;8?3#8M5'013#?0#364?(595#C109(#B?1#3'44'<M('#9?#&8331>6#
'%'3&1&'#+*D#
+D 736'3#?0'#&8;1'#='#(A'%'3&1&'+*D#
)D J?12'@#('4#10453?&51804#4?12905'4#;8?3#3648?=3'#('4#'%'3&1&'4D#
a. Fonction#«#SI#»#
:904#&'5#'%'3&1&'#28?4#92'@#='?%#59M('9?%#F#3648?=3'D#
+D :904#('#;3'<1'3#59M('9?#28?4#='2'@#&9(&?('3#(9#&8<<144180#='#&E9B?'#2'0='?3D#"8?3#
&'(9T#
o #28?4#9(('@#6&313'#=904#(9#&'((?('#I#7.#L#(9#C80&5180#B?1#&833'4;80=#F#(A9(>8315E<'#
4?12905##'5#28?4#C915'4#3'&8;1'3#2'34#('#M94#;8?3#('4#&'((?('4#U$NG7++V#
#
#
#
#
#
)D :904#('#4'&80=#59M('9?#28?4#9(('@#='2813#6&313'#?0'#C80&5180#B?1#538?2'#('#<'1(('?3#
2'0='?3D#
o :904#(9#&'((?('#I#P),#L#&9(&?('3#('#WXY#='4#7X#U&E1CC3'#=A9CC913'VD#
o :800'3#('#08<#I#<9%1<?<#F#(9#&'((?('#P),D#
o :904#(9#&'((?('#7+Z#6&312'@#(9#C80&5180#B?1#&833'4;80=#F#(A9(>8315E<'#4?12905#'5#
28?4#C915'4#3'&8;1'3#2'34#('#M94#;8?3#('4#&'((?('4#UP+[GP))V#G##
#
#
#
#
#
#
#
#
#
J1#7X#\#)N**#9(834#
###78<<144180#!#7X#%#+*]#^#
J1080##
##78<<144180#!#*#^#
#
J1#7X#\#<9%1<?<##9(834#
##### #$&313'#U_>9>0905_V#^#
J1080##
##$&313'#U__V#^#
#

!"#$%&'(## # # )*+,-)*+.#
b. Si#imbriqué#
:904#&'5#'%'3&1&'#28?4#92'@#`#(145'4#='#;38=?154D#R8?4#9(('@#='2813#538?2'3#('#;31%#?015913'#='#
&E9B?'#(145'#'0#C80&5180#='#(9#B?905156#9&E'56'D##
o $&312'@#(9#C80&5180#1<M31B?6'#JK#B?1#&833'4;80=##F#(A9(>8315E<'#4?12905#=904#(9#
&'((?('#7.#'5#28?4#C915'4#3'&8;1'3#2'34#('#M94#;8?3#('4#&'((?('4#U7NG7+*V#GG#
#
#
#
#
#
#
#
#
#
#
c. Fonction#logique#«#ET#»#
:904#&'5#'%'3&1&'#28?4#9(('@#='2813#538?2'3#;93<1#?0'#(145'#='#Z#;'34800'4#&'?%#B?1#
<6315'05#?0'#9?><'0595180D#
o $&312'@#=904#(9#&'((?('#$.#(9#C80&5180#4?12905'#'5#28?4#C915'4#3'&8;1'3#2'34#('#M94#;8?3#
('4#&'((?('4#U$NG$++V#G#
$.#a#$!U49(913'#b[**#^#90&1'00'56#\a,#^;81054#\N*#V#^#
#Remarque#:#cette#fonction#est#une#condition#composée.#
#
d. Fonction#«##SI#ET#»#
:904#&'5#'%'3&1&'#28?4#9(('@#?51(14'3#(9#C80&5180#I#JK#L#92'&#?0'#&80=15180#&8<;846'#I#$!#L#
7'5#'%'3&1&'#3'44'<M('#F#(A'%'3&1&'#;36&6='05T#49?C#B?'#&'55'#C814c&1T#28?4#9(('@#calculer#
(A9?><'0595180#;8?3#('4#;'34800'4#B?1#<6315'05#&'55'#9?><'0595180D#
"8?3#&'(9#6&312'@#(9#C80&5180#B?1#&833'4;80=#F#(A9(>8315E<'#4?12905#=904#(9#&'((?('#$.'5#28?4#
C915'4#3'&8;1'3#2'34#('#M94#;8?3#('4#&'((?('4#U$NG$++V#G#
#
#
#
#
#
#
#
e. NBSI#
1. :904#(9#&'((?('#H.#28?4#9(('@#&9(&?('3#('#08<M3'#='#<805905#B?1#4805#4?;631'?3#F#+***D#
o :800'@#('#08<#<805905#F#(9#;(9>'#U:)#G:)`V#
o $&312'@#(9#C80&5180#4?12905'#=904#H.#G#
H.#a#OPJK#U<805905#^#_\+***_V#^#
)D :904#(9#&'((?('#H+,#&9(&?('@#(9#<8S'00'#='4#<8059054D#
,D :904#(9#&'((?('#HNT#&9(&?('@#('4#08<M3'#='#<8059054#B?1#4805#4?;631'?34#F#(9#<8S'00'#
UH+,V#
J1#d56#b#)N##9(834#
##### #"31%#!#NT**^#
J1080##
J1##d56#\#N*##9(834#
# # "31%#!#)T**^#
J1080##
"31%#!,T**#^#
#
J1#U49(913'#b[**#'5#90&1'00'56#\a,#'5#;81054#\N*V###9(834#
##### #X?><'0595180#!#49(913'#%#+T*N^#
J1080##
##X?><'0595180#!#*^#