Verschillende staffel kortingen toevoegen aan bruto lijst

[SiorcGeal]

Mijn vraag
Ik ben bezig om een bruto lijst van onze producten te combineren met de netto staffelprijzen die wij van fabrikanten krijgen. Aangezien wij 25000 artikelen voeren en daarvan nog een 1200 netto prijsafspraken hebben, moet ik een gecombineerde Excel tabel maken die ik kan inlezen.

Ik heb geprobeerd met verticaal zoeken en met X.zoeken maar door de meerdere criteria loop ik vast.

Ik heb een simpele weergave van de excel data hier onder toegevoegd:

Ik moet in de Tabel brutoprijzen bij de staffel 2 per artikel de voorkomende nettoprijs invoegen. Als er geen staffel is moet niets weergegeven worden. Komt er een staffel van 5 stuks voor in de Tabel nettoprijzen dan moet die in de kolom staffel 5 zichtbaar zijn.

Kan iemand mij op weg helpen?

[g0tanks]

Makkelijkste is om een hulpkolom toe te voegen in de tabel met staffelprijzen, bestaande uit een combinatie van PG en aantal (dus bijv. AAA_2). Je moet dan die hulpwaarde gebruiken om verticaal te zoeken.

[loeberce]

Ik zou voor een index match combinatie kiezen.

Grofweg zou die oplossing er zo uit zien:

code:

{=INDEX(D12:D21;MATCH(1;(A12:A21=A4)*(B12:B21=B4)*(C12:C21=H3);0))}

Meer lezen kan je hier:
https://exceljet.net/form...ch-with-multiple-criteria

[Lustucru]

g0tanks schreef op vrijdag 12 februari 2021 @ 10:22:
Makkelijkste is om een hulpkolom toe te voegen in de tabel met staffelprijzen, bestaande uit een combinatie van PG en aantal (dus bijv. AAA_2). Je moet dan die hulpwaarde gebruiken om verticaal te zoeken.

Die is wel heel lelijk hè? Beetje jaren 80 oplossing.

Je mag aannemen dat elke combinatie van productnummer en staffel in de staffeltabel uniek is.
Dan haal je de staffelprijs op met sum.ifs(prijzencolumn, code, codecolumn, aantal, staffelcolumn)

Je kunt hem dan nog verfraaien met een check op countifs of if.error om uitzonderingen op te vangen.

[g0tanks]

Lustucru schreef op vrijdag 12 februari 2021 @ 10:56:
[...]

Die is wel heel lelijk hè? Beetje jaren 80 oplossing.

Eens hoor. Vandaar ook makkelijkste gezien de huidige denkrichting van TS en niet zozeer beste of meest efficiënte.

[SiorcGeal]

Wellicht ben ik niet geheel duidelijk geweest. Ik hoef onder staffel alleen maar de waarde in te voeren Er hoef geen berekening aan te hangen.

[Lustucru]

Wellicht snap je de suggesties niet geheel. Er is geen sprake van een berekening; er wordt alleen een waarde opgehaald.

[loeberce]

Lustucru schreef op vrijdag 12 februari 2021 @ 11:03:
Wellicht snap je de suggesties niet geheel. Er is geen sprake van een berekening; er wordt alleen een waarde opgehaald.

Maar met een SUMIFS formule suggereer je wel een berekening.

[SiorcGeal]

Lustucru schreef op vrijdag 12 februari 2021 @ 10:56:
[...]

Die is wel heel lelijk hè? Beetje jaren 80 oplossing.

Je mag aannemen dat elke combinatie van productnummer en staffel in de staffeltabel uniek is.
Dan haal je de staffelprijs op met sum.ifs(prijzencolumn, code, codecolumn, aantal, staffelcolumn)

Je kunt hem dan nog verfraaien met een check op countifs of if.error om uitzonderingen op te vangen.

Beste Lucustru,

Kun je mij op weg helpen met een de formule die dit mogelijk zou moeten maken?

[g0tanks]

SiorcGeal schreef op vrijdag 12 februari 2021 @ 12:13:
[...]


Beste Lucustru,

Kun je mij op weg helpen met een de formule die dit mogelijk zou moeten maken?

Volgens mij heeft hij bijna al het antwoord gegeven. Als je niet bekend bent met de functie, lees je dan eerst even in: SOMMEN.ALS, functie - Office-ondersteuning

[loeberce]

SiorcGeal schreef op vrijdag 12 februari 2021 @ 12:13:
[...]


Beste Lucustru,

Kun je mij op weg helpen met een de formule die dit mogelijk zou moeten maken?

Heb je überhaupt mijn oplossing geprobeerd? Of zit ik op het verkeerde spoor? Want dan hoor ik het ook graag.

[SiorcGeal]

Hey loeberce,

Ja, ik ben jouw oplossing aan het proberen. Ik heb de voorbeelden die op de door jouw genoemde site geprobeerd te reproduceren maar zelfs die lukken niet. Ik zie ook niet waar het mis gaat.

Morgen zal ik het nog eens thuis proberen.

Wordt vervolgd!

[xtrme]

als @loeberce z'n optie niet lukt heb je er dan wel een array van gemaakt
zoals op de gelinkte website
Note: this is an array formula, and must be entered with control + shift + enter, except in Excel 365.

[F_J_K]

loeberce schreef op vrijdag 12 februari 2021 @ 11:22:
[...]

Maar met een SUMIFS formule suggereer je wel een berekening.

Een manier om tot het doel te komen. De som van '4' is 4. De som van 0*2,5 + 0*5 + 1*4 is 4. Daar gaat het om

En waar relevant inderdaad als array-functie maar dat staat in het gelinkte artikel dus is vast gedaan.

@SiorcGeal zorgt dat je eerst begrijpt wat de functies doen, door met vereenvoudigde voorbeelden te testen wat er gebeurt. Daarna pas een toepassing voor jezelf proberen. Als je dat vervolgens de concrete geprobeerde formules geeft icm. waar het fout gaat, kunnen we vast meedenken.

[SiorcGeal]

Dat gaan we ook doen. Van proberen kun je leren!

[loeberce]

SiorcGeal schreef op vrijdag 12 februari 2021 @ 16:34:
Dat gaan we ook doen. Van proberen kun je leren!

Laat vooral hier zien welke formule je hebt gebruikt en waar het fout gaat, dan kunnen we je wat gerichter proberen te helpen. En zoals hierboven gezegd mijn voorbeeld is een array-formule. De { en } voer je dus niet met deze tekens in, maar je sluit het bewerken van de formule af door ctrl-shift-enter te drukken ipv enter. Dan zet Excel zelf de {} eromheen om aan te geven dat het een array formule is.

[SiorcGeal]

Problem solved!



Met deze formule: =SOMMEN.ALS(D12:D21;B12:B21;B4:B8;C12:C21;$H$3)

Bedankt voor de hints en tips mensen!

[SiorcGeal]

Ik heb helaas nog een vraag: Is het mogelijk om de waarden die nu netjes per kolom genoteerd staan en waar bij zoals rij 6 kolom J en K geen waardes staan de waarde uit kolom L netjes achter de waarde van I komt te staan? Zie het onderstaande voorbeeld. Ik ben in de weer geweest met de functie ALS en dan 16 geneste ALS functies, maar dan worden de lege waardes niet overgeslagen.



Alvast bedankt!

[g0tanks]

SiorcGeal schreef op woensdag 17 februari 2021 @ 08:46:
Ik heb helaas nog een vraag: Is het mogelijk om de waarden die nu netjes per kolom genoteerd staan en waar bij zoals rij 6 kolom J en K geen waardes staan de waarde uit kolom L netjes achter de waarde van I komt te staan?

Ik snap niet wat je bedoelt. Wil je nou dat bij een onbekende staffelprijs, de prijs uit kolom L (dus effectief de staffel van 50) wordt ingevuld?

[SiorcGeal]

g0tanks schreef op woensdag 17 februari 2021 @ 09:42:
[...]


Ik snap niet wat je bedoelt. Wil je nou dat bij een onbekende staffelprijs, de prijs uit kolom L (dus effectief de staffel van 50) wordt ingevuld?

Ik moet een prijslijst met 17000 artikelen in lezen in ons ERP systeem. daarvan hebben 1439 artikelen een of meerdere staffel prijzen. Met de SOMMEN.ALS formule heb ik nu alle staffels er netjes per artikel regel achter kunnen zetten. Maar ik kan maar 5 staffels in lezen. Ik heb in totaal 16 verschillende staffels verschillend van 1, 2, 3, 5 tot 1000 stuks. Er komen dus artikelen voor die 3 staffels bezitten: bijvoorbeeld een nettoprijs per 2 stuks, per 5 stuks en per 50 stuks. Nu moet ik nu dus de verschillende staffels netjes vanaf de eerste toegepaste staffel met de volgende op dezelfde regel aan laten sluiten om zo het maximale aantal te importeren staffels niet te overschrijden.

Kortom: de verschillende staffels per artikelregel moeten netjes op diezelfde regel achter elkaar aansluitend ongeacht welke staffel opgesomd worden.

[loeberce]

SiorcGeal schreef op woensdag 17 februari 2021 @ 12:15:
[...]


Ik moet een prijslijst met 17000 artikelen in lezen in ons ERP systeem. daarvan hebben 1439 artikelen een of meerdere staffel prijzen. Met de SOMMEN.ALS formule heb ik nu alle staffels er netjes per artikel regel achter kunnen zetten. Maar ik kan maar 5 staffels in lezen. Ik heb in totaal 16 verschillende staffels verschillend van 1, 2, 3, 5 tot 1000 stuks. Er komen dus artikelen voor die 3 staffels bezitten: bijvoorbeeld een nettoprijs per 2 stuks, per 5 stuks en per 50 stuks. Nu moet ik nu dus de verschillende staffels netjes vanaf de eerste toegepaste staffel met de volgende op dezelfde regel aan laten sluiten om zo het maximale aantal te importeren staffels niet te overschrijden.

Kortom: de verschillende staffels per artikelregel moeten netjes op diezelfde regel achter elkaar aansluitend ongeacht welke staffel opgesomd worden.

En je wil je de staffel kolommen (H, J en L en dus verder) in de tabel automatisch laten vullen met verschillende staffels die voor dat product toegekend zijn? Met in een kolom erachter de nettoprijs voor dat staffel?

[SiorcGeal]

loeberce schreef op woensdag 17 februari 2021 @ 12:23:
[...]

En je wil je de staffel kolommen (H, J en L en dus verder) in de tabel automatisch laten vullen met verschillende staffels die voor dat product toegekend zijn? Met in een kolom erachter de nettoprijs voor dat staffel?

Dat is wat ik nodig heb. Ik kom er alleen niet uit. Wellicht dat een van jullie guru's mij op weg kan helpen.

[g0tanks]

Ik denk dan aan een formule met INDEX-MATCH die eerst alle staffels per productgroep uitsplitst naar kolommen. Iets zoals dit: https://exceljet.net/form...hes-into-separate-columns

Vervolgens kan je de prijs er bij opzoeken.

[Lustucru]

De staffels in volgorde k kun je op twee manieren ophalen:
- met een matrixformule kleinste([bereik]*[voorwaarde],k) of met minimum.als.voorwaarden, waarbij je bij tweede en volgende staffels als extra voorwaarde opgeeft dat de staffel groter is dan de vorige. Het laatste is alleen o365. Welke versie gebruik je?

[SiorcGeal]

Wij gebruiken 365.

[Lustucru]

Dan gebruik je dus voor de kolommen H,J,L etc. een minimum.als.voorwaarden() constructie en het ophalen van de bijbehorende staffelprijs ken je inmiddels.