Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de SOMPRODUCT- en SUMIFS-functies kunt gebruiken om gegevens op te tellen die aan bepaalde criteria voldoen op meerdere bladen in Excel en Google Spreadsheets.
Regelmatige som over meerdere bladen
Soms kunnen uw gegevens meerdere werkbladen in een Excel-bestand beslaan. Dit is gebruikelijk voor gegevens die periodiek worden verzameld. Elk blad in een werkmap kan gegevens bevatten voor een bepaalde tijdsperiode. We willen een formule die de gegevens op twee of meer bladen optelt.
Met de SOM-functie kunt u eenvoudig gegevens over meerdere bladen optellen met behulp van een 3D-referentie:
| 1 | =SOM(Blad1:Blad2!A1) |
Dit is echter niet mogelijk met de SOMMEN-functie. In plaats daarvan moeten we een meer gecompliceerde formule gebruiken.
Som indien over meerdere bladen
Dit voorbeeld somt de op Aantal geplande leveringen voor elk Klant over meerdere werkbladen, elk met gegevens met betrekking tot een andere maand, met behulp van de SOMMEN, SOMPRODUCT en INDIRECT-functies:
| 1 | =SOMPRODUCT(SOMMEN(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Laten we deze formule eens doornemen.
Stap 1: Maak een SUMIFS-formule voor alleen 1 invoerblad:
We gebruiken de SOMMEN-functie om de op te tellen Aantal geplande leveringen door Klant voor een enkel invoergegevensblad:
| 1 | =SOMMEN(D3:D7,C3:C7,H3) |
Stap 2: Voeg een bladverwijzing toe aan de formule
We houden het resultaat van de formule hetzelfde, maar we specificeren dat de invoergegevens in het blad staan met de naam 'Stap 2'
| 1 | =SOMMEN.('Stap 2'!D3:D7,'Stap 2'!C3:C7,H3) |
Stap 3: Nest in een SOMPRODUCT-functie
Om de formule voor te bereiden om SUMIFS-berekeningen over meerdere bladen uit te voeren en vervolgens de resultaten bij elkaar op te tellen, voegen we een SOMPRODUCT-functie toe rond de formule
| 1 | =SOMPRODUCT(SOMMEN('Stap 3'!D3:D7,'Stap 3'!C3:C7,H3)) |
Het gebruik van de SOMMEN-functie op één blad levert één enkele waarde op. Over meerdere bladen voert de SOMMEN-functie een reeks waarden uit (één voor elk werkblad). We gebruiken de SOMPRODUCT-functie om de waarden in deze array op te tellen.
Stap 4: Vervang de bladverwijzing door een lijst met bladnamen
Wij willen de vervangen Bladnaam: deel van de formule met een gegevenslijst met de waarden: Jan, februari, maart, en april. Deze lijst wordt opgeslagen in de cellen F3:F6.
De INDIRECT-functie om ervoor te zorgen dat de tekstlijst die wordt weergegeven: Bladnamen wordt behandeld als onderdeel van een geldige celverwijzing in de SOMMEN-functie.
| 1 | =SOMPRODUCT(SOMMEN(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
In deze formule is de eerder geschreven bereikverwijzing:
| 1 | 'Stap 3'!D3:D7 |
Is vervangen door:
| 1 | INDIRECT("'"&F3:F6&"'!"&"D3:D7") |
De aanhalingstekens maken de formule moeilijk leesbaar, dus hier wordt deze weergegeven met toegevoegde spaties:
| 1 | INDIRECTE ( " ' " & F3:F6 & " ' ! " & "D3:D7") |
Door deze manier van verwijzen naar een lijst met cellen te gebruiken, kunnen we ook gegevens samenvatten van meerdere bladen die geen numerieke lijststijl volgen. Een standaard 3D-referentie zou vereisen dat de bladnamen in de stijl zijn: Input1, Input2, Input3, enz., maar in het bovenstaande voorbeeld kunt u een lijst gebruiken van alle Bladnamen en om ernaar te verwijzen in een aparte cel.
Celverwijzingen vergrendelen
Om onze formules leesbaarder te maken, hebben we de formules weergegeven zonder vergrendelde celverwijzingen:
| 1 | =SOMPRODUCT(SOMMEN(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Maar deze formules werken niet goed wanneer ze ergens anders in uw bestand worden gekopieerd en geplakt. Gebruik in plaats daarvan vergrendelde celverwijzingen zoals deze:
| 1 | =SOMPRODUCT(SOMMEN(INDIRECT("'"&$F$3:$F$6&"'!"&"D3:D7"),INDIRECT("'"&$F$3:$F$6&"'!"& "C3:C7"), H3)) |
Lees ons artikel over het vergrendelen van celreferenties voor meer informatie.
Som indien over meerdere bladen in Google Spreadsheets
Het gebruik van de INDIRECT-functie om te verwijzen naar een lijst met werkbladen in een SOMPRODUCT- en SUMIFS-functie is momenteel niet mogelijk in Google Spreadsheets.
In plaats daarvan kunnen afzonderlijke SUMIFS-berekeningen worden gemaakt voor elk invoerblad en de resultaten bij elkaar optellen:
| 1234 | =SOMMEN(Jan!D3:D7,Jan!C3:C7,H3)+SOMMEN(Feb!D3:D7,Feb!C3:C7,H3)+SUMIFS(mrt!D3:D7,mrt!C3:C7,H3)+SOMMEN(Apr!D3:D7,Apr!C3:C7,H3) |
