In dit artikel zullen we leren om de SUM-prijs op weekdagen in Excel te retourneren.
In eenvoudige bewoordingen, terwijl u werkt met prijsgevulde gegevens. Soms moeten we de som van de waarde met een voorwaarde vinden. Voorwaarde is om de som van prijswaarden in gescheiden kolommen te krijgen.
Voor dit artikel hebben we de volgende functies nodig:
- SOMPRODUCT-functie
- KOLOM-functie
- MOD-functie
Nu gaan we een formule maken van deze functies. Hier krijgen we een lijst met waarden en we moeten de som van de waarden in elk veelvoud van de gegeven n-de kolom krijgen.
Gebruik de formule:
=SOMPRODUCT ( -- ( MOD ( KOLOM ( lijst ) - KOLOM ( eerste ) + 1 , n ) = 0 ) , lijst )
lijst : lijst met waarden langs de kolom
eerst : eerste cel van de lijst die het kolomnummer retourneert
N : nde kolomwaarden om op te tellen.
Voorbeeld:
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus testen door deze in het onderstaande voorbeeld uit te voeren.
Hier hebben we gegevens gegeven die getallen bevatten. Hier moeten we de SOM van getallen in elk veelvoud van de 3e kolom vinden. Dus daarvoor hebben we lijsten toegewezen in een kolom voor de formule.
Nu zullen we de onderstaande formule gebruiken om de SOM van getallen op 3e, 6e, 9e, 12e enzovoort te krijgen totdat de lijst eindigt
Formule:
=SOMPRODUCT ( -- ( MOD ( KOLOM ( lijst ) - KOLOM ( C5 ) + 1 , P5 ) = 0 ), lijst )
lijst : lijst met waarden langs de kolom die wordt gegeven als het gebruik van de benoemde bereiken.
C5 : eerste cel van de lijst die het kolomnummer retourneert dat als celverwijzing is opgegeven
P5 : waarde van n gegeven als celverwijzing
Uitleg:
- MOD(COLUMN(lijst)-COLUMN(C5)+1,P5) geeft het relatieve kolomnummer voor elk n interval. Leuk vinden
{ 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 }
- De bovenstaande array wordt gekoppeld aan een 0-waarde en retourneert een array met True & False.
{ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, WAAR, ONWAAR}
- De functie SOMPRODUCT houdt rekening met waarde 1 voor elke ontvangen TRUE-waarde en 0 voor FALSE-waarde, zoals hieronder weergegeven.
=SOMPRODUCT ( -- ( { 0 , 0 , 1 , 0 , 0 , 1 , 0 , 0 , 1 , 0 , 0 , 1 , 0 } ),{ 40 , 36 , 51 , 33 , 42 , 30 , 92 , 67 , 34 , 54 , 69 , 56 , 50 } )
- -- converteert WAAR naar 1 en ONWAAR naar 0.
- Waarden die overeenkomen met 1s worden alleen opgeteld zoals weergegeven in de hierboven uitgelegde formule.
- Nu telt de SOMPRODUCT-functie alle waarden op, zoals hieronder wordt weergegeven.
=SOMPRODUCT ( { 0 , 0 , 51 , 0 , 0 , 30 , 0 , 0 , 34 , 0 , 0 , 56 , 0 } )
De SOM van de waarden { 51 + 30 + 34 + 56 } moet 171 teruggeven.
Hier wordt de array naar de functie gegeven als genoemd bereik en cel als celverwijzing. Druk op Enter om het resultaat te krijgen.
Zoals u in de bovenstaande momentopname kunt zien, retourneert de som van de waarden die bij elk 3e interval zijn geëxtraheerd 171. De waarden zijn gemarkeerd onder rode vakken zoals weergegeven in de bovenstaande momentopname.
Of u kunt de lijstarray gebruiken met behulp van de onderstaande formule:
=SOMPRODUCT ( -- ( MOD ( KOLOM ( C5:O5 ) - KOLOM ( C5 ) + 1 , P5 ) = 0 ), C5:O5 )
De bovenstaande formule geeft hetzelfde resultaat. Er is slechts één vervangingslijst met de naam bereik die wordt vervangen door het arraybereik (C5:O5).
Zoals je kunt zien, werkt de formule prima. Nu moeten we de formule voor de andere lijsten kopiëren. Dus gebruik de CTRL + D of sleep de optie van Excel naar beneden.
Zoals u aan de bovenstaande formule kunt zien, kunt u voorwaardelijke waarden krijgen.
Opmerkingen:
- De functie SOMPRODUCT beschouwt niet-numerieke waarden als nullen.
- De functie SOMPRODUCT beschouwt de logische waarde TRUE als 1 en False als 0.
- De argumentarray moet dezelfde lengte hebben, anders retourneert de formule een fout.
Ik hoop dat dit artikel over het retourneren van SUM alleen elke n-de kolom met formules in Excel verklarend is. Vind hier meer artikelen over SUMPRODUCT-functies. Deel uw vraag hieronder in het opmerkingenveld. Wij zullen u helpen.
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.
SOM als datum tussen ligt : Retourneert de SOM van waarden tussen bepaalde datums of periode in Excel.
Som als datum groter is dan opgegeven datum: Retourneert de SOM van waarden na de opgegeven datum of periode in Excel.
2 manieren om per maand op te tellen in Excel: Retourneert de SOM van waarden binnen een bepaalde specifieke maand in Excel.
Meerdere kolommen optellen met voorwaarde: Retourneert de SOM van waarden over meerdere kolommen met voorwaarde in Excel
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen
Een vervolgkeuzelijst bewerken
Absolute referentie in Excel
Indien met voorwaardelijke opmaak
Indien met jokertekens
Opzoeken op datum
Converteer inches naar feet en inches in Excel 2016
Voeg voor- en achternaam toe in Excel
Tel cellen die overeenkomen met A of B