[Excel] Laatste batchnummers uit een lijst opzoeken

[gwystyl]

Ik ben weer eens met Excel aan het stoeien. Deze keer probeer ik een snel voorraadoverzicht te krijgen

Ik heb een excel sheet met twee tabbladen: Productie en Orders. Ik wil nu op een derde tabblad een voorraad bijhouden.

Ik heb de tabellen voor de eenvoud op één tabblad gezet. Er zijn meer kolommen, maar die lijken me niet relevant voor mijn vraag.



Zoals je ziet worden de producties op volgorde geproduceerd, maar de orders zijn niet altijd de volledige productie. Het kan zijn dat er bijvoorbeeld 20 vaten van een batch, en 5 vaten van een een andere batch besteld worden. De volgorde van uitleveren is wel FIFO, dus in principe wordt eerst alles van batch 2108-04 uitgeleverd voor er iets van batch 2109-01 wordt uitgeleverd.
Deze lijsten worden steeds langer, dus ik zou graag een snel overzicht hebben van de voorraad van elk product. Hiervoor wil ik graag de laatste 5 geproduceerde batches zien, en daarnaast wat de voorraad daarvan is. Ik loop echter vast bij het weergeven/zoeken naar de laatste 5 batches.

Ik gebruik Excel 365, dus ik heb X.Zoeken (XLOOKUP) geprobeerd (zie plaatje). Hiermee vind ik wel de eerste batch, maar niet de laatste 5. Ook krijg ik nu de foutmelding "overloop". Wie kan me op weg helpen?

Ik heb ook "verschuiving" geprobeerd, maar daar krijg ik helemaal geen zinnige waarden uit

[g0tanks]

Je definitie van voorraad is voor mij niet helder. Kan je voor product A een compleet voorbeeld maken van het gewenste eindresultaat?

[gwystyl]

@g0tanks uiteraard.... ik zou voor product A een voorraad willen zien zoals hieronder:



Dus links een rijtje met de laatste 5 geproduceerde batches, met daarnaast de voorraad.
Om fouten in de formule makkelijker te kunnen zien heb ik nu een poging gedaan om het te splitsen in Geproduceerd, Besteld en Voorraad. Voorraad is "geproduceerd - besteld".
Bij "geproduceerd" wil ik het aantal vaten dat geproduceerd is te zien krijgen. Dit lukt als het batchnummer goed is. Dit doe ik met de volgende formule:

code:

=X.ZOEKEN([@Batch];ProductieTabel[batch];ProductieTabel[aantal])

Bij "besteld" wil ik het totaal aantal bestelde vaten van een bepaalde batch te zien krijgen. Hiervoor moet excel kijken in kolom "batch1" en "batch2", en dan het aantal vaten van een bepaalde batch bij elkaar optellen. Ook dit lukt, met de volgende formule:

code:

=ALS.FOUT(X.ZOEKEN([@Batch];OrderTabel[Batch1];OrderTabel[Aantal1]);0)+ALS.FOUT(X.ZOEKEN([@Batch];OrderTabel[Batch2];OrderTabel[Aantal2]);0)

Het probleem is dus dat ik in de kolom "batch" in de voorraad tabel maar 1 batchnummer te zien krijg, namelijk de eerste die hij tegen komt en voldoet aan "product A" in de productietabel.
Ik wil daar graag meerdere batchnummers hebben, en dan specifiek de laatste 5.

Ik vermoed dat ik met "verschuiven" aan de gang moet, maar weet niet precies hoe. Ik kan niet de x laatste batches pakken, want dat kunnen verschillende producten zijn

Ik hoop dat het zo duidelijker is?

[ Voor 14% gewijzigd door gwystyl op 13-10-2021 12:13 ]

[Tazzios]

draaittabel met top5 gesorteerd op batch?
Daarnaast kun je vervolgens je formule zetten voor de bestellingen

[gwystyl]

@Tazzios kan dat als productie en orders in verschillende tabellen staan? Ik zou de tabellen kunnen combineren en een kolom toevoegen waarin "productie" of "orders" staat, maar dan krijg ik een hoop lege cellen omdat beide tabellen nog andere kolommen bevatten (niet dezelfde)

[Tazzios]

ja het een haal je op met een draaitabel(je top5) daarnaast zoals vermeld plaats gewoon een formule

[g0tanks]

code:

=INDEX(FILTER($A$3:$C$12;$A$3:$A$12=D14);SORT(SEQUENCE(5;1;SUM(--($A$3:$A$12=D14));-1));SEQUENCE(1;COLUMNS($A$3:$C$12)))

[gwystyl]

@g0tanks Dank je, daar ga ik even mee stoeien.

Ik heb de formule vast vertaald naar de NL termen:

code:

=INDEX(FILTER(ProductieTabel;ProductieTabel[Product]=B24);SORTEREN(REEKS(3;1;SOM(--(ProductieTabel[Product]=B24));-1));REEKS(1;KOLOMMEN(ProductieTabel);1;1))

B24 is hier het product waar ik de voorraad van wil
dit levert in mijn voorbeeldsheet wel het gewenste aantal op, maar in mijn echte sheet zijn veel meer kolommen (12), waarvan ik de volgende kolommen bij de voorraad wil hebben:
Kolom 1: product (hoeft niet, maar is in jouw formule wel gebruikt)
Kolom 3: batch
Kolom 6: aantal
Kan je die nog apart aanwijzen of moet ik mijn uitgangstabel zo verbouwen dat ik alleen die drie kolommen over heb?

[g0tanks]

Ik zou dan handmatig elke keer één kolom ophalen in plaats van de hele tabel. In mijn voorbeeld wordt $A$3:$C$12 dan $A$3:$A$12 om alleen de product-kolom op te halen. Dat herhaal je dan een paar keer voor de gewenste kolommen.

[gwystyl]

Dank je, dat was het laatste zetje! In combinatie met de formule hierboven heb ik nu de zoekfunctie naar batchnummers maar naar één kolom laten zoeken (batch), en vervolgens kan ik daarnaast gewoon x.zoeken gebruiken om bijbehorende datum, aantal etc. te vinden



Ik kreeg eerst nog wat foutmeldingen, maar dat was blijkbaar omdat ik de voorraad als tabel had opgemaakt. Toen ik het had geconverteerd naar een normaal bereik, werkte het wel

edit: bij mijn orders ging het mis met x.zoeken, omdat hij dan alleen de eerste waarde teruggeeft die hij vindt. Hiervoor heb ik dus verder gezocht, en in [EXCEL]VERT.ZOEKEN waardes optellen kwam ik er achter dat som.als hier wel voldoet. De formule in C26 van mijn voorbeeld wordt dan:

code:

=SOM.ALS(OrderTabel[Batch1];A26;OrderTabel[Aantal1])+SOM.ALS(OrderTabel[Batch2];A26;OrderTabel[Aantal2])

[ Voor 20% gewijzigd door gwystyl op 14-10-2021 09:58 ]