627-did : Fonctions conditionnelles [Procédures (classement ordonné)]

627-did : Fonctions conditionnelles

Contexte de travail⚓

ÉcranPrésentation des données⚓

La société Kivantou dispose de la liste des produits vendus dans la journée à un certain prix et une certaine quantité.

Nous avons donc dans notre catalogue 3 produits différents (A,B,C) pour un total de 7 ventes réalisées selon le tableau ci-dessous. Le fichier à utiliser est le fichier 0627.ods.

Ci-dessous, vous pouvez visualiser les données de départ ainsi que les noms assignés aux différentes plages en cliquant sur chaque colonne.

AttentionPlage de travail⚓

Même si le tableau comporte 7 ventes, on anticipera sur des ventes futures en gérant les plages des lignes 3 à 14.

1 2 3

1 Articles (plage A3 :A14)
2 Prix (plage B3 :B14)
3 Quantité (plage C3 :C14)

1 Articles (plage A3 :A14)⚓
Assignez le nom^[1] lib_art la plage de cellule (A3 :A14)
2 Prix (plage B3 :B14)⚓
Assignez le nom^[1] prix la plage de cellule (B3 :B14)
3 Quantité (plage C3 :C14)⚓
Assignez le nom^[1] qte la plage de cellule (C3 :C14)

Partie 1 : comptage et cumul⚓

ÉcranDécouverte des fonctions de comptage, de cumul⚓

Remarque

La colonne formule en clair donne la syntaxe exacte de la formule dans la colonne résultat.

Si la formule est précédée d'un caractère ' devant le signe =, c'est uniquement pour que le tableur n'exécute pas la formule et la laisse en clair. Celle - ci étant exécutée dans la colonne Résultat. Vous ne devez pas mettre le caractère ' devant le signe =

ConseilPositionnement du tableau ci-dessous⚓

Vous pouvez placer le tableau ci-dessous où bon vous semble pour essayer les formules décrites

7B5_td-1. Les fonctions SOMME, NB, SOMMEPROD

1 2 3

1 Calcul de la quantité totale
2 Fonction NB
3 Fonction SOMMEPROD

1 Calcul de la quantité totale⚓
Fonction SOMME⚓
On utilise la fonction SOMME^[2] avec l'argument sous forme de référence ou en utilisant le nom qte donné à la plage.
Truc & astuceImportance des noms⚓
Lors d'une extension future du tableau des ventes, le fait de changer la plage affectée au nom^[1] qte permet de ne pas avoir besoin de modifier les formules qui utilisent le nom comme argument. Sinon, toutes les formules qui contiennent la référence A3 :A14 devront être modifiées.
2 Fonction NB⚓
La fonction NB^[3] compte le nombre de cellules contenant des valeurs numériques dans une plage.
Elle est utile lorsqu'on nomme une plage de données qui sera complétée progressivement dans le temps, ou encore lorsque l'on veut s'assurer que l'on prenne bien en compte les cellules dans lesquelles des valeurs numériques sont saisies.
ExempleRésultats⚓
la plage lib_art contient seulement du texte et non des valeurs numériques
la plage qte contient des valeurs numériques
Remarque
Quel résultat peut-on attendre de NBVAL(lib_art) et de NBVAL(qte) ?
3 Fonction SOMMEPROD⚓
La fonction SOMMEPROD^[4] permet d'éviter la colonne Prix*Qté ainsi que la somme des données de cette colonne.
Sans cette fonction, il faudrait procéder comme exposé ci-dessous : RÉALISEZ BIEN SûR LE TRAITEMENT ALTERNATIF CI-DESSOUS
ExempleSans la fonction SOMMEPROD⚓
Ajoutez dans le tableau d'origine une colonne nommée Prix*qté en D1.
En D2, saisir la formule =prix*qte
recopiez la formule jusqu'en D13,
en D14, saisir la formule =somme(D2 :D13).

Partie 2 : la fonction SI⚓

Nombre de vente et Qté totale vendue pour l'article A⚓

Pour cela, nous allons utiliser la fonction SI^[5] et

ajouter une colonne de titre "Nombre A" dans la colonne E, et faire apparaître des 1 pour les ventes de l'article "A".
ajouter une colonne de titre "Quantité A" dans la colonne F, et faire apparaître la quantité pour les ventes de l'article "A".

La somme de ces colonnes nous donnera le résultat attendu

ÉcranFonction SI⚓

1 2 3

1 Nombre de ventes de l'article A
2 Quantité vendue pour l'article A
3 Somme des 2 colonnes (nombre et quantité)

1 Nombre de ventes de l'article A⚓
Dans la colonne E, nous allons placer un 1 sur les lignes dont la vente concerne uniquement l'article A.
La fonction SI^[5] va nous permettre de tester cette condition.
2 Quantité vendue pour l'article A⚓
Le traitement est analogue à celui réalisé dans la colonne E, sauf qu'il faut faire apparaître la quantité vendue pour l'article A.
3 Somme des 2 colonnes (nombre et quantité)⚓
Au final, une simple fonction SOMME des colonnes E et F donne le résultat suivant : 3 ventes de l'article A pour 19 unités vendues.

RéglementaireDécomposition de la Fonction SI⚓

Dans un premier temps, en E3, saisir =SI(lib_art = E$2), recopiez la formule jusqu'à E14 : vous constatez que l'on obtient VRAI lorsque l'article est A et FAUX sinon.

Dans la colonne E, nous souhaitons faire apparaître un 1 lorsque la vente concerne l'article A, et rien dans le cas contraire.

	Expression	Commentaire
Condition	lib_art = E$2	le contenu de la cellule E1 (en absolu pour recopie vers le bas) est-il identique au libellé de l'article de la même ligne
Cas VRAI	1	Nous afficherons 1 lorsque la condition est est vérifiée (VRAI)
Cas FAUX	""	Le contenu de la cellule restera vide dans le cas FAUX --> d'où la présence du "". Attention il s'agit ici d'un double " et non d'une quadruple '.

Modifiez la formule en E3, recopiez vers le bas

Pour la colonne F, la cas VRAI doit faire référence à la cellule C3.

On obtiendra donc en F3 la formule =SI(lib_art = E$2 ;C3 ;"").

Partie 3 : comptage et cumul conditionnels⚓

ÉcranVentilation des Ventes par article⚓

Tableau de synthèse des ventes par article⚓

Ce tableau placé ou bon vous semble comporte 1 ligne pour chaque article, alors que le tableau de départ comportait autant de lignes que de ventes.

Cette liste est donc nommée lst_art.

7B5_td-1. Illustration des fonctions NB.SI et SOMME.SI

1 2 3

1⚓
Liste des Articles : 1 par ligne --> va servir de critères pour les fonctions NB.SI et SOMME.SI
2⚓
Nombre d'articles vendus --> NB.SI
3⚓
Quantité d'articles vendus --> SOMME.SI

RemarquePartie 2⚓

Vous avez remarqué qu'avec la fonction SI^[5] pour chaque ligne de vente et une fonction somme nous avons réussi à obtenir ce résultat avec l'article A en ajoutant 2 colonnes intermédiaires.

Il nous faudrait ajouter 4 colonnes pour traiter les articles B et C.

De plus le tableau obtenu à la fin fait apparaître la liste des articles en début de colonne du tableau des ventes, mais les résultats (NB et Qté) globaux figurent à la dernière ligne.

Un autre inconvénient est aussi de mélanger dans une même feuille les données des ventes et la synthèse de ces ventes pour chaque article : ce qui n'est pas très lisible et oblige à tout imprimer pour avoir des informations de synthèse par article

Truc & astuceNB.SI et SOMME.SI⚓

Les fonction NB.SI^[6] et SOMME.SI^[7] sont utilisées pour éviter de multiplier les colonnes intermédiaires et établir un tableau global plus conforme à ce que l'on attend : 1 ligne pour chaque article qui va chercher les données dans le tableau initial des ventes.

AttentionNe pas confondre lib_art et lst_art⚓

lib_art est une zone qui contient la liste de tous les articles vendus : soit 7 actuellement et 12 potentiellement dans notre cas --> une et une seule ligne par article vendu

lst_art est une zone qui contient la liste des articles différents : soit 3 dans notre cas--> une et une seule ligne par article

NB.SI⚓

Exemple

Pour chaque ligne de la plage lib_art , la fonction NB.SI compte le nombre de fois ou la cellule de la ligne lib_art est égale à la cellule de la ligne de la zone lst_art.

Pour trouver le nombre 3 pour l'article A, il suffit donc de parcourir la zone lib_art, et compter le nombre de "A"

ComplémentA tester ⚓

Testez dans le tableau des données de départ :

=NB.SI (prix ;">15") : nb de vente dont le prix est supérieur à 15 euros

=NB.SI (prix ;"<30")- NB.SI(prix ;"<15") : nb de vente dont le prix est compris entre 15 et 30

RéglementaireConcaténation de condition⚓

=NB.SI(prix;"<"&D20)-NB.SI(prix;"<"&C20) : donne le nombre de ventes dans l'intervalle dont les limites sont spécifiées dans l'intervalle C20 et D20.

L'opérateur < est saisi avec "<".

Pour lui ajouter que la comparaison doit être faite avec le contenu de la cellule D20 il faut concaténer < avec la valeur de la cellule D20. Si C20 contient la valeur 15 et D20 la valeur 30, alors la formule est interprétée comme =NB.SI(prix ;"<30")-NB.SI(prix ;"<15")

SOMME.SI⚓

ExempleQuantité totale vendue par article⚓

Pour chaque ligne de la plage lib_art , la fonction SOMME.SI^[7] additionne les valeurs contenue dans qte lorsque la cellule de la ligne lib_art est égale à la cellule de la ligne de la zone lst_art.

Pour trouver le nombre 19 pour l'article A, il suffit donc de parcourir la zone lib_art, et si la valeur est "A", alors la quantité de la zone qte est cumulée

Comme la syntaxe générale est SOMME.SI (plage ; critère ; plage_cumulée), on a :

plage --> lib_art
critère -->lst_art
plage_cumulée --> qte

ComplémentCritères ⚓

Testez

=SOMME.SI (prix ;">15" ;qte) : Qté totale vendue dont le prix est supérieur à 15 euros

=SOMME.SI (prix ;"<30" ;qte)- SOMME.SI(prix ;"<15" ;qte) : Quantité totale vendue dont le prix est compris entre 15 et 30

Partie 4 : fonctions logiques⚓

Les fonctions logique^[8] permettent de combiner des conditions complexes avec des ET ou de OU