Comment additionner par couleur dans Excel? (2 méthodes utiles)
Les 2 principales méthodes à additionner par couleurs dans Excel sont les suivantes:
- Utilisation de la formule SOUS-TOTAL dans Excel et filtre par fonction de couleur.
- Application de la formule GET.CELL en définissant le nom dans l'onglet de formule et en appliquant la formule SUMIF dans Excel pour résumer les valeurs par codes de couleur.
Laissez-nous discuter de chacun d'eux en détail -
# 1 - Somme par couleur en utilisant la fonction de sous-total
Pour comprendre l'approche de calcul de la somme des valeurs par couleurs d'arrière-plan, considérons le tableau de données ci-dessous, qui fournit des détails sur les montants en USD par région et par mois.
- Étape 1: Supposons que nous souhaitons mettre en évidence les cellules dont les valeurs sont négatives à des fins d'indication, cela peut être réalisé en appliquant une mise en forme conditionnelle ou en mettant en évidence les cellules manuellement, comme indiqué ci-dessous.
- Étape 2: Maintenant, pour obtenir la somme des cellules colorées dans Excel, entrez la formule pour SOUS-TOTAL sous le tableau de données. La syntaxe de la formule SOUS - TOTAL est indiquée ci-dessous.
La formule saisie pour calculer la somme est
= SOUS-TOTAL (9, D2: D13)
Ici, le numéro '9' dans l'argument no_fonction fait référence à la fonctionnalité de somme , et l'argument de référence est donné comme plage de cellules à calculer. Voici la capture d'écran pour le même.
- Étape 3: Comme le montre la capture d'écran ci-dessus, une somme du montant en USD a été calculée afin de calculer les montants surlignés dans une couleur de fond rouge clair. Appliquez le filtre à la table de données en allant à Données puis en sélectionnant un filtre.
- Étape 4: Sélectionnez ensuite le filtre par couleur et choisissez la couleur de cellule rouge clair sous «Filtrer par couleur de cellule». Voici la capture d'écran pour mieux décrire le filtre.
- Étape 5: Une fois le filtre Excel appliqué, le tableau de données sera filtré uniquement pour les cellules de fond rouge clair, et la formule de sous-total appliquée au bas du tableau de données affichera la somme des cellules colorées, qui sont filtrées comme indiqué au dessous de.
Comme le montre la capture d'écran ci-dessus, le calcul de la cellule colorée est réalisé dans la cellule E17, formule du sous-total.
# 2 - Somme par couleur à l'aide de la fonction Get.Cell
La deuxième approche est expliquée pour arriver à la somme des cellules de couleur dans Excel, comme indiqué dans l'exemple ci-dessous. Considérez le tableau de données pour mieux comprendre la méthodologie.
- Étape 1: Maintenant, mettons en évidence la liste des cellules dans la colonne Montant en USD, que nous sommes prêts à arriver à la somme souhaitée de cellules colorées, comme indiqué ci-dessous.
- Étape 2: Comme nous pouvons le voir dans la capture d'écran ci-dessus, contrairement au premier exemple ici, nous avons plusieurs couleurs. Ainsi, nous utiliserons la formule = GET.CELL en la définissant dans la zone de nom et en ne l'utilisant pas directement dans Excel.
- Étape 3: Maintenant, une fois que la boîte de dialogue pour le 'Définir le nom' apparaît, entrez le nom et la formule en oubliant .CELL dans 'Se référer à' comme indiqué dans la capture d'écran ci-dessous.
Comme le montre la capture d'écran ci-dessus, le nom entré pour la fonction est «CellColor» et la formule = GET.CELL (38, «Exemple 2! $ D2) doit être entrée dans« Refers to ». Dans la formule, le chiffre 38 fait référence aux informations de code de cellule et le deuxième argument est le numéro de cellule D2 fait référence à la cellule de référence. Cliquez maintenant, OK.
- Étape 4: Entrez maintenant le nom de la fonction 'CellColor' dans la cellule à côté de la cellule à code couleur, qui a été définie dans la boîte de dialogue, comme expliqué à l'étape 3.
Comme on peut le voir dans la capture d'écran ci-dessus, la fonction 'CellColor' est entrée, qui renvoie le code de couleur pour la couleur de la cellule d'arrière-plan.
De même, la formule est déplacée pour toute la colonne.
- Étape 5: Maintenant, pour arriver à la somme des valeurs par couleurs dans Excel, nous allons entrer la formule SUMIF. La syntaxe de la formule SUMIF est la suivante: -
Comme le montre la capture d'écran ci-dessus, les arguments suivants sont entrés dans la formule SUMIF: -
- L'argument de plage est entré pour la plage de cellules E2: E13.
- Les critères sont entrés comme G2, dont les valeurs résumées doivent être récupérées.
- La plage de cellules est entrée comme D2: D13.
La formule SUMIF est déplacée vers le bas pour tous les numéros de code couleur pour lesquels les valeurs doivent être additionnées.
Choses dont il faut se rappeler
- En utilisant l'approche par formule SOUS-TOTAL, cette fonctionnalité permet aux utilisateurs de filtrer pour une seule couleur de filtre à la fois. De plus, cette fonctionnalité peut être utilisée pour ajouter une seule colonne de valeurs par couleurs de filtre. S'il y a plus d'une colonne avec des couleurs différentes par lignes dans les différentes colonnes, le SOUS-TOTAL peut uniquement afficher le résultat correct pour un filtre par couleur à une colonne spécifique uniquement.
- La formule GET.CELL, en combinaison avec l'approche SUMIF, élimine cette limitation, car cette fonctionnalité pourrait être utilisée pour résumer par couleurs pour plusieurs codes de couleur dans l'arrière-plan de la cellule.
