Aujourd'hui sur Formation Excel, vous allez découvrir 3 méthodes utilisées pour calculer des cumuls sous Excel, et vous allez voir la plus efficace parmi ces trois méthodes qui vous évitera d'avoir des erreurs dans vos cumuls. En plus, et à la fin de cet article, vous allez voir comment calculer des cumuls en pourcentage.
Méthode 1: Ce qui est habituel!
Regardez l’exemple suivant, c’est
la colonne Cumul qui va afficher les cumuls calculés.
La méthode la plus simple est la
suivante :
- Dans la cellule C2 tapez =B2. Pour afficher le premier cumul.
- Puis dans la cellule C3 tapez =B3+C2 et validez. Le deuxième cumul additionne le premier cumul contenant dans la cellule C2 à la valeur de la deuxième cellule B3.
- Copiez ensuite la formule précédente dans le reste des cellules de la colonne Cumul:
Mais le problème qui peut
survenir c'est que lorsque vous supprimez une ligne de votre tableau, Excel va renvoyer
une erreur de référence #REF. (Pour plus de détails sur cette erreur et les
erreurs dans Excel consultez l’article Les
erreurs dans Excel )
Comment résoudre donc ce
problème ?
Méthode 2: Calculer le cumul à l'aide de la fonction SOMME
Le mieux donc est d’utiliser la
fonction SOMME pour calculer vos cumuls, et voici comment procéder :
- Sélectionnez la cellule C2 et tapez =SOMME(B2:B2), c’est-à-dire que vous faites la somme de la plage B2:B2, ce qui va vous afficher la valeur contenue dans B2.
- Continuons...
- Vous allez maintenant copier cette formule dans le reste de la colonne Cumul, mais avant de le faire, vous devez figer la première référence B2.
- Fixez alors la première référence B2 en y ajoutant des dollars $B$2 ou cliquez sur elle et tapez la touche F4 , et laissez la deuxième B2 telle qu’elle est.
- En effet, la copie de la formule va incrémenter les références relatives des cellules (B3,B4,B5...B12), et la référence $B$2 (référence absolue) restera la même. Regardez l'image suivante :
- Cette méthode vous permet donc de calculer les sommes des plages B2:B3, B2:B4, B2:B5...B2:B12, et de les placer successivement dans les cellules C3,C4,C5...C12.
- Essayez à présent de supprimer une ligne de votre tableau et remarquez que votre cumul s'effectue de nouveau automatiquement.
Or, il se peut qu’un autre problème survienne encore ! C’est quand vous souhaiterez
filtrer vos données !
Par exemple, je veux afficher le cumul des montants des ventes du 2ème trimestre, je sélectionne donc les 3 mois Avril,Mai et Juin, ce qui donne le résultat suivant :
Excel ne calcule pas automatiquement les cumuls, n’est-ce pas ?!
Voici à mon avis la solution la plus efficace pour éviter ces erreurs de cumul:
Méthode 3: Calculer le cumul en utilisant la fonction SOUS.TOTAL
Vous vous êtes habitués maintenant avec l’utilisation de la fonction SOUS.TOTAL ! Oui, vous l’avez
vue dans les deux derniers articles.
Alors abordons-la également ici :
- Sélectionnez la cellule C2 et insérez la formule suivante : =SOUS.TOTAL(109;$B$2:B2)
- Copiez la formule dans les autres cellules de C3 à C13.
- Filtrez à présent vos données !
- Essayez même de supprimer une ligne de votre tableau.
- Ne trouvez-vous pas que c’est très efficace !
Vous arrivez maintenant à la fin de cet article, et vous allez voir deux exemples qui vont vous montrer comment calculer le cumul en pourcentage:
Dans cet exemple, nous avons filtré notre tableau précédent en sélectionnant les mois du deuxième trimestre:
Remarquez alors que les formules se trouvant dans la colonne %Cumul se mettent à jour automatiquement pour prendre en compte les nouvelles valeurs visibles:
Par exemple: la cellule D5 contient, dans ce cas, la formule =C5/$C$7
Calculer des cumuls en pourcentage ( Exemple 1)
C'est une opération très facile,
puisque vous avez déjà calculé les cumuls.
- Commencez par sélectionner la première cellule de la colonne %Cumul qui va afficher le cumul en pourcentage. Dans notre exemple c’est la cellule D2.
- Tapez la formule suivante : =C2/$C$13
- C2 contient le premier cumul et C13 contient le total des cumuls. Notez ici que vous devez figer la cellule C13 parce que vous allez copier la formule.
- Faites la copie donc de la formule dans les autres cellules.
- Sélectionnez à présent la plage de cellules D2:D13 et dans le groupe Nombre sous l’onglet Accueil choisissez le format pourcentage.
- Voici le résultat obtenu:
Calculer le cumul en pourcentage des cellules filtrées ( Exemple 2)
Remarquez alors que les formules se trouvant dans la colonne %Cumul se mettent à jour automatiquement pour prendre en compte les nouvelles valeurs visibles:
Par exemple: la cellule D5 contient, dans ce cas, la formule =C5/$C$7
Merci,c'est bien explicité.
RépondreSupprimermerci trés bonne explications
RépondreSupprimerSympa
RépondreSupprimerpour les cumuls % si on supprime la ligne 13 cela génère une erreur, peu probable sur cet exemple mais possible dans la vraie vie …..
la colonne C étant un cumul, la valeur la plus forte est sur la dernière ligne du tableau on peut utiliser max(C:C) au lieu de C13 cela dépanne parfois
Très précieuse votre intervention! Effectivement si ce cas que vous avez indiqué se produit, on optera pour cette solution.
SupprimerMerci Eric.
Bonjour,
SupprimerAu sujet des cumuls %, si nous voulons par exemple les mois de Mars Juin Septembre,
Comment peut-on faire ?
Bonjour,
SupprimerVous procédez de la même façon expliquée dans le cours en filtrant les mois selon votre choix.
Merci pour partager vos connaissances, trés utile
RépondreSupprimermerci
RépondreSupprimerJe vous en prie.
RépondreSupprimer