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.

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

  • 1 Calcul de la quantité totale
    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
    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
    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

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 Nombre de ventes de l'article A
    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
    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é)
    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.

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.

  • Liste des Articles : 1 par ligne --> va servir de critères pour les fonctions NB.SI et SOMME.SI

  • Nombre d'articles vendus --> NB.SI

  • 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
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

SOMME.SI

ExempleQuantité totale vendue par article
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

Truc & astuceUtilité

Établir une table de vérité pour le ET et le OU.

Conseil

Créer deux tableaux pour gérer la table de vérité du ET et du OU