Comment faire la somme 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 -
Vous pouvez télécharger ce modèle Excel Somme par couleur ici - Modèle Excel Somme par couleur# 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 entrée pour calculer la somme est
= SOUS-TOTAL (9, D2: D13)
Ici, le numéro '9' dans l'argument function_num fait référence à la fonctionnalité de somme et l'argument de référence est donné comme la 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 mis en évidence dans une couleur de fond rouge clair. Appliquez le filtre à la table de données en accédant à 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». Ci-dessous, 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 en bas du tableau de données affichera la somme des cellules colorées qui sont filtrées comme indiqué ci-dessous.
Comme le montre la capture d'écran ci-dessus, le calcul de la cellule colorée est effectué 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 est discutée 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, 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 pour GET.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« Se réfère à ». 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 sur OK.
- Étape 4: Entrez maintenant la fonction Nom '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 pour être D2: D13
La formule SUMIF est déplacée vers le bas pour tous les numéros de code de couleur pour lesquels les valeurs doivent être additionnées.
Choses à retenir
- Lors de l'utilisation de 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 la colonne différente, 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é peut être utilisée pour résumer par couleurs pour plusieurs codes de couleur dans l'arrière-plan de la cellule.
