Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de grootste celwaarde kunt vinden die aan specifieke voorwaarden voldoet in Excel en Google Spreadsheets.
Max als array-functie
De MAX-functie identificeert de grootste waarde in een reeks getallen.
We kunnen de MAX-functie gebruiken in combinatie met een ALS-functie om de grootste waarde te identificeren die aan een gespecificeerde voorwaarde voldoet.
Gebruikers van Google Spreadsheets en Excel 2022 of hoger wordt aangeraden de eenvoudigere MAXIFS-functie te gebruiken. Dit wordt in een later gedeelte uitgelegd.
In dit voorbeeld worden de MAX- en IF-functies in een matrixformule gebruikt om de grootste te identificeren Bestelgrootte: voor elk Winkel naam
| 1 | {=MAX(IF(B3:B8="A",D3:D8))} |
In Office 365 en versies van Excel na 2022 kunt u de bovenstaande formule gewoon invoeren zoals u normaal zou doen (door op ENTER te drukken).
Voor Excel 2022 en eerder moet u de formule echter invoeren door op CTRL + SHIFT + ENTER te drukken. Nadat u dit hebt gedaan, ziet u dat er accolades rond de formule verschijnen.
Laten we, om te laten zien hoe deze formule werkt, deze in stappen opsplitsen.
Dit is onze definitieve formule (weergegeven zonder de automatisch toegevoegde matrixformule-haakjes):
| 1 | =MAX(ALS(B3:B8="A",D3:D8)) |
Eerst worden de celbereikwaarden als arrays aan de formule toegevoegd:
| 1 | =MAX(ALS({"A"; "B"; "A"; "B"; "A"; "B"}="A",{500; 400; 300; 700; 600; 200})) |
volgende de Winkel naam =”A”-voorwaarde produceert een reeks TRUE/FALSE-waarden:
| 1 | =MAX(ALS({WAAR; ONWAAR; WAAR; ONWAAR; WAAR; ONWAAR},{500; 400; 300; 700; 600; 200})) |
Dan verandert de IF-functie alle TRUE-waarden in de relevante Bestelgrootte:
| 1 | =MAX({500; ONWAAR; 300; ONWAAR; 600; ONWAAR}) |
De MAX-functie identificeert het grootste getal in de array, terwijl eventuele FALSE-waarden worden genegeerd, om het grootste weer te geven Bestelgrootte: voor de Winkel naam = "A":
| 1 | =600 |
Max als - meerdere criteria
We kunnen ook de grootste waarde identificeren op basis van meerdere criteria met behulp van Booleaanse logica.
Dit voorbeeld toont de grootste Bestelgrootte: voor elk Winkel naam, maar voor Besteldata vóór 30-4-2021 met behulp van de MAX-, IF- en DATE-functies:
| 1 | {=MAX(IF((B3:B8="A")*(C3:C8 |
Merk op dat we hier twee sets TRUE/FALSE-criteria met elkaar vermenigvuldigen:
| 1 | (B3:B8="A") * (C3:C8 |
Als beide criteria WAAR zijn, wordt de totale voorwaarde als WAAR berekend, maar als een (of meer) criteria ONWAAR is, wordt deze als ONWAAR berekend.
Met behulp van deze methodiek is het mogelijk om veel verschillende criteria aan deze formule toe te voegen.
Max als - Meerdere criteria met celverwijzingen
Gewoonlijk is het geen goede gewoonte om waarden hard in formules te coderen. In plaats daarvan is het flexibeler om afzonderlijke cellen te gebruiken om de criteria te definiëren.
Om overeen te komen met de Winkel naam naar de waarde die wordt weergegeven in kolom F, kunnen we de formule bijwerken als:
| 1 | {=MAX(ALS((B3:B8=F3)*(C3:C8 |
Celverwijzingen vergrendelen
Om onze formules leesbaarder te maken, hebben we de formules weergegeven zonder vergrendelde celverwijzingen:
| 1 | {=MAX(ALS((B3:B8=F3)*(C3:C8 |
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 | {=MAX(IF(($B$3:$B$8=F3)*($C$3:$C$8 |
Lees ons artikel over het vergrendelen van celreferenties voor meer informatie.
MAXIFS-functie
Gebruikers van Google Spreadsheets en Excel 2022 of hoger kunnen de enkele MAXIFS-functie gebruiken om het gedrag van de MAX- en IF-functies uit de eerdere voorbeelden te repliceren.
In dit volgende voorbeeld worden de MAXIFS- en DATE-functies gebruikt om de grootste Bestelgrootte: voor elk Winkel naam voor Besteldata voor 30-4-2021:
| 1 | =MAXIFS(D3:D8,B3:B8,"A",C3:C8,"<"&DATUM(2021,4,30)) |
De MAXIFS-functie vereist niet dat de gebruiker op CTRL + SHIFT + ENTER drukt bij het invoeren van de formule.
Max If (Max. waarde met voorwaarde) in Google Spreadsheets
De bovenstaande voorbeelden werken precies hetzelfde in Google Spreadsheets als in Excel, maar aangezien de MAXIFS-functie beschikbaar is, is het raadzaam om deze enkele functie te gebruiken in plaats van de MAX- en IF-functies te combineren.
Als de voorbeelden met MAX- en IF-functies moeten worden gebruikt, vereist Google Spreadsheets dat u deze invoert als matrixformules. In plaats van de formule met Excel-array-haakjes { } weer te geven, wordt door op CTRL + SHIFT + ENTER te drukken automatisch de ARRAYFORMULA-functie rond de formule toegevoegd:
| 1 | =ARRAYFORMULA(MAX(IF((B3:B8="A")*(C3:C8 |
