Combinaties
Functies worden pas echt sterk als je ze combineert. Elke pagina hier neemt één controletaak, lost die op met meerdere functies in één formule, en haalt de formule uit elkaar zodat je ziet wat elk onderdeel doet.
- SOMMEN.ALSSOMOuderdomsanalyse debiteuren die aansluit op de controlerekeningJe telt het openstaande bedrag per ouderdomsklasse op met één SOMMEN.ALS over een klassentabel, telt de klassen op met SOM en controleert of het totaal aansluit op de controlerekening.
=SOM(SOMMEN.ALS($C$2:$C$9, $B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6))=SOM(SOMMEN.ALS($C$2:$C$9; $B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6)) - FILTERSOMAfgrenzingstoets rond het jaareinde met FILTER en SOMMarkeer de verkoopfacturen waarvan de boekingsdatum en de leverdatum aan verschillende kanten van het jaareinde liggen en tel ze op: twee vergelijkingen met de jaareinddatum geven per datum WAAR of ONWAAR, FILTER houdt de facturen over waarbij die twee verschillen en SOM telt ze op.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=SOM(FILTER(E2:E8, (C2:C8<=H1)<>(D2:D8<=H1), 0))=SOM(FILTER(E2:E8; (C2:C8<=H1)<>(D2:D8<=H1); 0)) - TEKST.COMBINERENFILTERAlle facturen van één leverancier in één cel met TEKST.COMBINEREN en FILTERKies met FILTER de factuurnummers van één leverancier en voeg ze met TEKST.COMBINEREN samen tot één regel, met een melding als de leverancier geen facturen heeft.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=TEKST.COMBINEREN(", ", WAAR, FILTER(B2:B9, A2:A9=H1, "Geen facturen"))=TEKST.COMBINEREN(", "; WAAR; FILTER(B2:B9; A2:A9=H1; "Geen facturen")) - TEKST.NATEKST.VOORX.ZOEKENDe klant vinden via een bankomschrijvingKnip het factuurnummer uit een bankomschrijving met TEKST.NA en TEKST.VOOR en zoek de klant daarna op in de debiteurenlijst met X.ZOEKEN.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=X.ZOEKEN(TEKST.VOOR(TEKST.NA(H1, "REF:"), " ", 1, 0, 1), A2:A7, B2:B7, "Niet in debiteuren", 0)=X.ZOEKEN(TEKST.VOOR(TEKST.NA(H1; "REF:"); " "; 1; 0; 1); A2:A7; B2:B7; "Niet in debiteuren"; 0) - NEMENSORTERENFILTERDe vijf grootste handmatige boekingen met NEMEN, SORTEREN en FILTERToon de vijf grootste handmatige (MEM) journaalregels voor de journaalpostentest: FILTER houdt de MEM-regels over, SORTEREN zet het grootste bedrag bovenaan en NEMEN houdt de eerste vijf rijen.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=NEMEN(SORTEREN(FILTER(A2:E9, A2:A9="MEM"), 5, -1), 5)=NEMEN(SORTEREN(FILTER(A2:E9; A2:A9="MEM"); 5; -1); 5) - TEKST.SPLITSENSPATIES.WISSENNAAR.KOLOMEen geplakte lijst splitsen en opschonen tot één kolomSplits een in één cel geplakte lijst factuurnummers op de komma's, haal de losse spaties weg met SPATIES.WISSEN en zet de stukjes met NAAR.KOLOM onder elkaar, klaar om te vergelijken.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2, ",")))=NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2; ","))) - FILTERISNBX.VERGELIJKENFacturen die in het grootboek ontbreken met FILTER, ISNB en X.VERGELIJKENToon de facturen op de specificatie van de debiteuren die niet in het grootboek staan: X.VERGELIJKEN zoekt elke factuur op, ISNB maakt van elke misser WAAR en FILTER houdt die rijen over, met een melding als er niets ontbreekt.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=FILTER(A2:C8, ISNB(X.VERGELIJKEN(A2:A8, D2:D8)), "Niets ontbreekt")=FILTER(A2:C8; ISNB(X.VERGELIJKEN(A2:A8; D2:D8)); "Niets ontbreekt") - NEMENSORTEREN.OPASELECT.MATRIXRIJENAselecte steekproef van journaalregels met NEMEN, SORTEREN.OP en ASELECT.MATRIXTrek n journaalregels aselect, nooit twee keer dezelfde regel: ASELECT.MATRIX geeft elke regel een willekeurige sleutel, SORTEREN.OP zet de regels in die volgorde en NEMEN houdt de eerste n over.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=NEMEN(SORTEREN.OP(A2:A13, ASELECT.MATRIX(RIJEN(A2:A13))), G1)=NEMEN(SORTEREN.OP(A2:A13; ASELECT.MATRIX(RIJEN(A2:A13))); G1) - FILTERWEEKDAGAANTAL.ALSBoekingen in het weekend en op feestdagen met FILTER, WEEKDAG en AANTAL.ALSToon de journaalposten met een datum op zaterdag, zondag of een Nederlandse feestdag: WEEKDAG markeert de weekenden, AANTAL.ALS toetst de datum aan een feestdagenlijst en FILTER houdt de gemarkeerde rijen over.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=FILTER(A2:D9, (WEEKDAG(B2:B9, 2)>5)+(AANTAL.ALS(E2:E8, B2:B9)>0)>0, "Geen weekend- of feestdagboekingen")=FILTER(A2:D9; (WEEKDAG(B2:B9; 2)>5)+(AANTAL.ALS(E2:E8; B2:B9)>0)>0; "Geen weekend- of feestdagboekingen") - FILTERRESTAFRONDENABSBoekingen met ronde bedragen vinden met FILTER, REST, AFRONDEN en ABSMarkeer grootboekregels waarvan het bedrag een exact veelvoud van 1.000 is (en boven een minimum ligt): ABS haalt het teken weg, AFRONDEN haalt kleine rekenverschillen weg, REST toetst het veelvoud en FILTER toont de regels.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=FILTER(A2:E9, (REST(AFRONDEN(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2), "Geen regels met ronde bedragen")=FILTER(A2:E9; (REST(AFRONDEN(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2); "Geen regels met ronde bedragen") - LETUNIEKSOMMEN.ALSFILTERHOR.STAPELENBoekstukken waarbij debet niet gelijk is aan creditToon de boekstukken waarvan het debet- en creditbedrag verschillen: je pakt de unieke boekstuknummers, telt debet en credit per boekstuk op met SOMMEN.ALS en houdt met FILTER over HOR.STAPELEN de ongelijke over, alles benoemd in één LET.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=LET(boeking, UNIEK(A2:A9), debet, SOMMEN.ALS(C2:C9, A2:A9, boeking), credit, SOMMEN.ALS(D2:D9, A2:A9, boeking), FILTER(HOR.STAPELEN(boeking, debet, credit, debet-credit), debet<>credit, "Alle boekstukken sluiten"))=LET(boeking; UNIEK(A2:A9); debet; SOMMEN.ALS(C2:C9; A2:A9; boeking); credit; SOMMEN.ALS(D2:D9; A2:A9; boeking); FILTER(HOR.STAPELEN(boeking; debet; credit; debet-credit); debet<>credit; "Alle boekstukken sluiten")) - X.ZOEKENREGEXEXTRACTREGEXTESTDe klant opzoeken met een regexHaal het factuurnummer met REGEXEXTRACT uit een bankomschrijving, maak er een patroon van en laat X.ZOEKEN dat patroon vergelijken met een factuurlijst waarin nummers op verschillende manieren zijn getypt.Excel voor Microsoft 365 (Deze combinatie vereist Excel voor Microsoft 365)
=ALS(REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}"), LET(sleutel, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), X.ZOEKEN("(?i)^inv[- ]?"&LINKS(sleutel, 2)&"-?"&RECHTS(sleutel, 4)&"$", A2:A7, B2:B7, "Niet in debiteuren", 3)), "Geen factuurnummer in tekst")=ALS(REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}"); LET(sleutel; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); X.ZOEKEN("(?i)^inv[- ]?"&LINKS(sleutel; 2)&"-?"&RECHTS(sleutel; 4)&"$"; A2:A7; B2:B7; "Niet in debiteuren"; 3)); "Geen factuurnummer in tekst") - VERT.STAPELENUNIEKFILTERX.VERGELIJKENISNBDubbele factuurnummers in twee exportsZet twee maandelijkse inkoopexports onder elkaar en bewaar elk factuurnummer dat vaker dan één keer voorkomt, met VERT.STAPELEN, UNIEK, X.VERGELIJKEN, ISNB en FILTER.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=FILTER(VERT.STAPELEN(A2:A7, B2:B7), ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7, B2:B7), UNIEK(VERT.STAPELEN(A2:A7, B2:B7), , WAAR))), "Geen dubbelen")=FILTER(VERT.STAPELEN(A2:A7; B2:B7); ISNB(X.VERGELIJKEN(VERT.STAPELEN(A2:A7; B2:B7); UNIEK(VERT.STAPELEN(A2:A7; B2:B7); ; WAAR))); "Geen dubbelen") - FILTERREEKSX.VERGELIJKENISNBHiatentest op factuurnummers met FILTER, REEKS en X.VERGELIJKENVind de ontbrekende nummers in een reeks verkoopfacturen: REEKS maakt elk nummer van het laagste tot het hoogste, X.VERGELIJKEN zoekt ze op in het grootboek, ISNB markeert de missers en FILTER toont ze, met een melding als er geen hiaten zijn.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)), "Geen hiaten")=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)); "Geen hiaten") - HOR.STAPELENUNIEKSOMMEN.ALSSORTERENMini-saldibalans uit journaalregels met HOR.STAPELEN, UNIEK en SOMMEN.ALSMaak uit ruwe journaalregels een lijst met rekeningen en hun saldo aan boekingen: UNIEK geeft de rekeningen, SOMMEN.ALS telt elke rekening op, HOR.STAPELEN zet beide naast elkaar en SORTEREN zet ze op volgorde.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=SORTEREN(HOR.STAPELEN(UNIEK(B2:B9), SOMMEN.ALS(D2:D9, B2:B9, UNIEK(B2:B9))))=SORTEREN(HOR.STAPELEN(UNIEK(B2:B9); SOMMEN.ALS(D2:D9; B2:B9; UNIEK(B2:B9)))) - SOMMEN.ALSZELFDE.DAGREEKSOmzet per maand voor twaalf maanden in één formuleTotaal omzet voor elk van twaalf maanden met één SOMMEN.ALS: REEKS telt de maanden, ZELFDE.DAG maakt van elk getal de eerste dag van een maand, en die datums vormen de criteria.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=SOMMEN.ALS(C2:C9, A2:A9, ">="&ZELFDE.DAG(F1, REEKS(12, 1, 0)), A2:A9, "<"&ZELFDE.DAG(F1, REEKS(12, 1, 1)))=SOMMEN.ALS(C2:C9; A2:A9; ">="&ZELFDE.DAG(F1; REEKS(12; 1; 0)); A2:A9; "<"&ZELFDE.DAG(F1; REEKS(12; 1; 1))) - INDEXREEKSAFRONDEN.NAAR.BENEDENRIJENSystematische steekproef van facturen met een vaste startwaardeTrek elke k-de factuur uit een populatie: het interval komt uit AFRONDEN.NAAR.BENEDEN en RIJEN, REEKS telt de trekkingen vanaf een vaste startwaarde die je in een cel typt, en INDEX toont de gekozen rijen.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=INDEX(A2:A25, G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1, 0))=INDEX(A2:A25; G2+(REEKS(G1)-1)*AFRONDEN.NAAR.BENEDEN(RIJEN(A2:A25)/G1; 0)) - X.ZOEKENSOMTotaal debiteuren per ouderdomsklasse van 30 dagenJe zet elke vervaldatum met X.ZOEKEN en overeenkomstmodus -1 om in een klasse uit een klassentabel en laat SOM daarna de openstaande bedragen van één klasse optellen.Excel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
=SOM((X.ZOEKEN($H$3-B2:B8, E2:E5, E2:E5, 0, -1)=H1)*C2:C8)=SOM((X.ZOEKEN($H$3-B2:B8; E2:E5; E2:E5; 0; -1)=H1)*C2:C8) - LETFILTERHOR.STAPELENABSVerschillen boven de rapportagegrens met LET, FILTER, HOR.STAPELEN en ABSStem het grootboek per debiteur af op de specificatie van de cliënt en toon alleen de verschillen boven de rapportagegrens: LET geeft de verschillen één keer een naam, HOR.STAPELEN zet er de debiteur naast, FILTER houdt de rijen over waarvan ABS boven de grens uitkomt.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=LET(verschil, B2:B7-C2:C7, FILTER(HOR.STAPELEN(A2:A7, verschil), ABS(verschil)>F1, "Geen verschillen boven de grens"))=LET(verschil; B2:B7-C2:C7; FILTER(HOR.STAPELEN(A2:A7; verschil); ABS(verschil)>F1; "Geen verschillen boven de grens")) - X.ZOEKENNAAR.KOLOMZoeken op rij en kolom met één X.ZOEKEN en NAAR.KOLOMVind de waarde waar een rijlabel en een kolomkop elkaar kruisen: je maakt van beide voorwaarden één raster met nullen en enen en zet dat met NAAR.KOLOM in één kolom.Excel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
=X.ZOEKEN(1, NAAR.KOLOM((H2=B1:D1)*(H1=A2:A6)), NAAR.KOLOM(B2:D6), "Niet gevonden", 0)=X.ZOEKEN(1; NAAR.KOLOM((H2=B1:D1)*(H1=A2:A6)); NAAR.KOLOM(B2:D6); "Niet gevonden"; 0)