Comment calculer une somme en fonction de critères partiels (début, milieu ou fin de cellule) Excel ?

Dans ce tutoriel, je vais vous montrer comment sommer des cellules en fonction d'un critère partiel.

Pour cela, nous allons utiliser des fonctions Excel classique, telles que SOMME.SI ou SOMMEPROD.

Celles-ci vont en effet nous permettre de réaliser des sommes basées sur un critère spécifique d’une cellule, lequel pouvant être situé au début, à la fin, ou à l’intérieur du contenu de cette cellule.

Pour finir, nous verrons également comment adapter ces formules pour effectuer d’autres types de calculs, comme des multiplications ou des calculs de moyenne.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Création du tableau structuré

Pour illustrer ce tutoriel, nous allons nous appuyer sur le tableau suivant, qui présente le détail des ventes réalisées par une entreprise auprès de l’un de ses clients.

Dans ce tableau, nous retrouvons les colonnes suivantes :

• Le numéro de chaque facture,
• La référence des articles vendus,
• Les quantités vendues,
• Le prix unitaire de chaque produit,
• Le montant total HT de chaque ligne.

Avant d’entrer dans le vif du sujet, nous souhaitons convertir cette base Excel en un tableau structuré. Comme nous l’avons déjà abordé lors de tutoriels précédents, il s’agit d’une fonctionnalité très puissante d’Excel qui permet en seulement quelques clics d'ajouter des fonctionnalités améliorées sur une base de données classique.

Pour effectuer la conversion, il suffit de sélectionner l’une des cellules de la base, puis d’utiliser le raccourci clavier [Ctrl]+[L]. Excel affiche alors une boîte de dialogue, sur laquelle nous allons pouvoir confirmer que les cellules sélectionnées automatiquement par Excel correspondent effectivement aux cellules de la base, et nous confirmons également que notre tableau comporte des en-têtes.

Lorsque nous validons en appuyant sur le bouton « Ok », Excel convertit les données en tableau.

Maintenant, comme nous pouvons le constater, l’apparence de notre base de données a changé.

Nous voyons que les colonnes sont colorées et que des filtres sont appliqués aux en-têtes.

En plus de cette apparence enrichie, les autres atouts des tableaux structurés sont :

• L’insertion de références aux noms de cellules automatiques : lorsque nous créons des formules, Excel utilise le nom des colonnes plutôt que des références de cellules, ce qui facilite la lecture et la compréhension.
• La sélection facilitée des colonnes : en cliquant sur l'en-tête, nous pouvons sélectionner l'ensemble de la colonne, ce qui simplifie l'édition des données comme nous le verrons juste après.
• Des références dynamiques en cas d’ajouts de nouvelles lignes : si nous ajoutons de nouvelles lignes à notre tableau, les formules s'ajustent automatiquement pour inclure ces nouvelles données.
• La mise en forme conditionnelle : avec les tableaux structurés, nous pouvons facilement appliquer une mise en forme conditionnelle à tout le tableau, ce qui nous permet de visualiser rapidement des tendances ou des exceptions.
• L'analyse rapide : en sélectionnant une cellule de notre tableau, Excel propose des outils d'analyse rapide qui permettent d'obtenir des informations précieuses en quelques clics.
• Etc.

2. Sommer les cellules commençant par un nombre spécifique

Maintenant, nous allons souhaiter obtenir le montant des CA réalisés auprès de ce client au cours du mois de janvier 2023.

Pour cela, nous allons pouvoir utiliser le numéro de facturation lequel est en effet codifié en fonction de l’année sur 4 caractères, puis le mois et enfin l’ordre d’établissement de la facture sur les 4 quatre caractères de la fin.

La formule que nous allons mettre en place va reposer sur un détournement de l’usage basic que la fonction SOMMEPROD.

En effet, la fonction SOMMEPROD dans est initialement utilisée pour multiplier des ensembles de matrices correspondantes et ensuite retourner la somme de ces produits.

Cependant, nous pouvons la détourner pour effectuer une somme conditionnelle, un peu comme le fait la fonction SOMME.SI, mais avec une flexibilité accrue.

Dans ce contexte, la fonction SOMMEPROD s'utilise de la manière suivante :

=SOMMEPROD((Plage1=Critère)*(Plage2))

Où :

• Plage1 est la plage de cellules sur lesquelles la condition sera appliquée,
• Critère est la condition à respecter pour effectuer la somme. Critère peut être un nombre (exemple : « 12 »), une expression (exemple : « <12 ») ou un texte (exemple : « France »).
• Plage2 est la plage de cellules sur lesquelles la somme doit être appliquée une fois la condition vérifiée.

La fonction va alors créer une matrice de valeurs 1 et 0 pour chaque condition (1 lorsque la condition est remplie, 0 sinon), puis en multipliant ces matrices par la Plage2.

Les produits sont ensuite sommés pour obtenir le résultat final.

Dans notre, exemple, nous allons combiner la fonction SOMMEPROD avec la fonction GAUCHE, qui permet comme son nom l’indique d’extraire un certain nombre de caractères à partir du début (c'est-à-dire de la gauche) d'une chaîne de texte.

Ici, nous allons récupérer les 6 premiers caractères du champ "N Facture" pour les comparer au mois de janvier, c’est-à-dire à la chaîne "202301".

GAUCHE(Tableau1[N  Facture];6)

Comme nous l’avons vu un peu plus tôt, pour insérer directement les coordonnées de la colonne « N Facture », il suffit de cliquer en haut de l’en-tête de cette colonne.

Ensuite, nous allons vérifier si le résultat retourné par cette fonction est égal à « 202301 », en sachant que le résultat retourné par la fonction GAUCHE sera toujours une chaîne de caractères, nous devons donc encadrer « 202301 » par des guillemets.

Si le numéro de facture commence par "202301", cette partie de la formule renvoie 1 (vrai); sinon, elle renvoie 0 (faux).

(GAUCHE(Tableau1[N  Facture];6)=202301)

Maintenant, nous allons encapsuler cette portion de formule en tant qu’argument de la fonction SOMMEPROD() afin de multiplier la matrice de 1 et 0 que nous avons obtenue dans l'étape précédente par les valeurs du champ "Montant HT". Cela a pour effet de ne sommer les montants HT que pour les lignes où le numéro de facture commence par "202301" :

=SOMMEPROD((GAUCHE(Tableau1[N   Facture];6)="202301")*(Tableau1[Montant HT]))

La fonction SOMMEPROD additionne ensuite ces produits pour donner le résultat final.

C'est donc une manière de sommer les montants HT pour un sous-ensemble spécifique de factures, basé sur les 6 premiers chiffres du numéro de facture.

Pour effectuer une somme en fonction de critères situés à la fin, ou au milieu d’une cellule, nous utiliserons les fonction DROITE() et STXT() à la place de la fonction GAUCHE().

Pour en savoir plus sur l’utilisation de ces fonctions, cliquez ici.

3. Sommer les cellules commençant par un texte spécifique

Il faut savoir qu’il existe une alternative encore plus simple à mettre en place pour effectuer une somme en fonction d’une portion de cellules.

Mais celle-ci ne pourra fonctionner que si l’analyse repose sur des textes, et non pas des valeurs numériques, comme nous venons de le faire.

Cette solution simplifiée, repose sur l’utilisation de la fonction SOMME.SI d’Excel.

Celle-ci permet en effet d’additionner les valeurs de chaque élément donné en paramètre lorsqu'une condition est remplie. Ces paramètres peuvent être des chiffres, des textes ou des références de cellules.

La fonction SOMME.SI s'utilise de la manière suivante :

=SOMME.SI(Plage;Critère;[Somme_plage])

Où :

• Plage est la plage de cellules sur lesquelles la fonction va être appliquée,
• Critère est la condition à respecter pour effectuer la somme. Critère peut être un nombre (exemple : « 12 »), une expression (exemple : « <12 ») ou un texte (exemple : « France »).
• Somme_plage est l'argument facultatif qui spécifie la plage des cellules sur lesquelles la somme doit être appliquée. Si cet argument est manquant, par défaut Somme_plage prend la valeur de Plage.

Dans notre, exemple, nous allons maintenant souhaiter connaître le nombre de produits de la gamme « AM » vendus au client.

Cette gamme étant en effet composé de deux produits, nous ne pourront pas utiliser la fonction SOMME.SI de manière classique.

Bien entendu, nous pourrions utiliser la fonction SOMMEPROD comme nous l’avons fait dans la partie précédente, cette méthode pouvant fonctionner sur tous types de données :

Mais sur ce cas spécifique, nous pouvons gagner du temps en utilisant la fonction SOMME.SI comme ceci :

La formule que vous avez fournie est une utilisation de la fonction SOMME.SI d'Excel.

=SOMME.SI(Tableau1[Référence  article];"AM*";Tableau1[Quantités])

Comme vous pouvez le constater, nous utilisons ici le critère "AM*" pour effectuer la somme. Dans ce cas, la condition est que la référence de l'article doit commencer par "AM". L'astérisque (*) est un caractère générique dans Excel qui peut représenter n'importe quel nombre de caractères.