Hoe op kleur optellen in Excel? (2 handige methoden)
Top 2-methoden om op kleur te sommen in Excel zijn als volgt -
- Gebruik van de SUBTOTAAL-formule in Excel en filter op kleurfunctie.
- De GET.CELL-formule toepassen door de naam op het formuletabblad te definiëren en de SUMIF-formule in Excel toe te passen om de waarden op kleurcodes samen te vatten.
Laten we ze allemaal in detail bespreken -
U kunt deze Som per kleur Excel-sjabloon hier downloaden - Som per kleur Excel-sjabloon# 1 - Som op kleur met behulp van de subtotaalfunctie
Om de benadering voor het berekenen van de som van waarden op basis van achtergrondkleuren te begrijpen, kijken we naar de onderstaande gegevenstabel die details geeft van bedragen in US $ per regio en maand.
- Stap 1: Stel dat we die cellen willen markeren die negatief zijn in waarden voor indicatiedoeleinden, dit kan worden bereikt door voorwaardelijke opmaak toe te passen of door de cellen handmatig te markeren, zoals hieronder wordt weergegeven.
- Stap 2: Voer nu de formule voor SUBTOTAAL onder de gegevenstabel in om de som van cellen te verkrijgen die in Excel zijn gekleurd. De syntaxis voor de SUBTOTAAL- formule wordt hieronder weergegeven.
De formule die wordt ingevoerd om de sommatie te berekenen is
= SUBTOTAAL (9, D2: D13)
Hier verwijst nummer '9' in het functie_getalargument naar de somfunctionaliteit en wordt het referentieargument gegeven als het celbereik dat moet worden berekend. Hieronder is de schermafbeelding voor hetzelfde.
- Stap 3: Zoals te zien is in de bovenstaande schermafbeelding, is een optelling van het USD-bedrag berekend. Om de bedragen te berekenen die zijn gemarkeerd in een lichtrode achtergrondkleur. Pas het filter toe op de gegevenstabel door naar Gegevens te gaan en vervolgens een filter te selecteren.
- Stap 4: Selecteer vervolgens filteren op kleur en kies de lichtrode celkleur onder 'Filter op celkleur'. Hieronder ziet u de schermafbeelding om het filter beter te beschrijven.
- Stap 5: Nadat het Excel-filter is toegepast, wordt de gegevenstabel alleen gefilterd op lichtrode achtergrondcellen en de subtotaalformule die onder aan de gegevenstabel wordt toegepast, geeft de som van de gekleurde cellen weer die zijn gefilterd zoals hieronder weergegeven.
Zoals te zien is in de bovenstaande schermafbeelding, wordt de berekening van de gekleurde cel bereikt in cel E17, subtotaalformule.
# 2 - Som op kleur met behulp van de Get.Cell-functie
De tweede benadering wordt uitgelegd om te komen tot de som van de kleurcellen in Excel wordt besproken in het onderstaande voorbeeld. Overweeg de gegevenstabel om de methodologie beter te begrijpen.
- Stap 1: Laten we nu de lijst met cellen in de kolom USD Bedrag markeren die we bereid zijn te komen tot de gewenste som van gekleurde cellen, zoals hieronder weergegeven.
- Stap 2: Zoals we kunnen zien in de bovenstaande schermafbeelding, hebben we in tegenstelling tot het eerste voorbeeld hier meerdere kleuren. Daarbij zullen we de formule = GET.CELL gebruiken door deze in het naamvak te definiëren en niet direct in Excel te gebruiken.
- Stap 3: Zodra het dialoogvenster voor de 'Naam definiëren' verschijnt, voert u de naam en de formule voor GET.CELL in 'Verwijs naar' in zoals weergegeven in de onderstaande schermafbeelding.
Zoals te zien is in de bovenstaande schermafbeelding, is de naam die voor de functie is ingevoerd 'CellColor' en moet de formule = GET.CELL (38, 'Voorbeeld 2! $ D2) worden ingevoerd in' Verwijst naar '. Binnen de formule verwijst het cijfer 38 naar de celcode-informatie en het tweede argument is het celnummer D2 verwijst naar de referentiecel. Klik nu op OK.
- Stap 4: Voer nu de functie Naam 'CellColor' in de cel naast de kleurgecodeerde cel die werd gedefinieerd in het dialoogvenster zoals uitgelegd in stap 3.
Zoals te zien is in de bovenstaande schermafbeelding, wordt de functie 'CellColor' ingevoerd die de kleurcode voor de achtergrondcelkleur retourneert.
Op dezelfde manier wordt de formule voor de hele kolom gesleept.
- Stap 5: Om nu tot de som van de waarden per kleur in Excel te komen, gaan we de SUMIF-formule invoeren. De syntaxis voor de SUMIF-formule is als volgt: -
Zoals te zien is in de bovenstaande schermafbeelding, worden de volgende argumenten ingevoerd in de SUMIF-formule: -
- Het bereikargument wordt ingevoerd voor celbereik E2: E13
- De criteria worden ingevoerd als G2 waarvan de samengevatte waarden moeten worden opgehaald.
- Het celbereik wordt ingevoerd als D2: D13
De SUMIF-formule wordt naar beneden gesleept voor alle kleurcodenummers waarvoor waarden bij elkaar moeten worden opgeteld.
Dingen om te onthouden
- Bij het gebruik van de formule benadering door SUBTOTAAL, stelt deze functionaliteit de gebruikers in staat om slechts op één filterkleur tegelijk te filteren. Bovendien kan deze functionaliteit worden gebruikt om slechts één kolom met waarden toe te voegen op basis van filterkleuren. Als er meer dan één kolom is met verschillende kleuren per rijen in de verschillende kolom, geeft het SUBTOTAAL mogelijk alleen het juiste resultaat weer voor één filter op kleur voor alleen een specifieke kolom.
- De GET.CELL-formule in combinatie met de SUMIF-benadering elimineert deze beperking, aangezien deze functionaliteit kan worden gebruikt om samen te vatten op basis van kleuren voor meerdere kleurcodes in de celachtergrond.
