Opgelost Draaitabel Kostprijs per Recept

[Georgyboy]

Hallo ieder,

Heb een tabblad "gegevens"
Tabblad "Resultaat" (draaitabel)
Tabblad "gewenst"

De optelsom per subtotaal in kolom "D " is correct.

Som van HoeveelHeid

Echter De optelsom van kolom "E" is niet correct.

Max van Iart.InkoopPrijs

Daar zijn nu handmatig 2 Kolommen (Kolom G en kolom H) toegevoegd waar nu 3 subtotalen zijn berekend.
Neem aan dit dit niet kan via een draaitabel en dit waarschijnlijk met VBA wel kan?

Wat zou er mogelijk zijn?
PS Voorbeeld in bijlage is een kleine selectie uit een met Query ververst (vernieuwen) tabblad.
De echte tabel bevat bijna 22.000 regels en meer dan 6700 recepten.

Alvast bedankt!
Groeten Georgyboy

 

[peter59]

Ik heb het verdere verloop ook met power query proberen op te lossen.
Het kan nog veel mooier maar voor te beginnen.
Kijk eens of dit in beetje in de richting komt.

 

[Georgyboy]

Dag Peter, ben ik héél tevreden mee super bedankt!
Voor mij al een heel mooie oplossing!

Bekijk deze week hoe je dit stap voor stap hebt opgelost.
Het resultaat (uitkomst) is juist en dit is wat telt.

Met Power Query is er zo te zien veel mogelijk.
Benieuwd welke oplossingen nog mogelijk zijn binnen Excel of Via VBA.

 

[peter59]

Benieuwd welke oplossingen nog mogelijk zijn binnen Excel of Via VBA.

Daar zijn gegarandeerd ook nog oplossingen voor want binnen excel zijn er vele wegen mogelijk die naar Rome leiden.

 

[cow18]

een VBA-oplossing

 

[Georgyboy]

Geweldig Cow18 in 20 sec
> 22.000 regels en meer dan 6700 recepten.
Hartelijk dank weer een sterk staaltje van je kunnen!

@peter59
Dank voor je fantastische oplossing met Power Query
Mag ik vragen als het kan hoe je dit stap voor stap hebt opgelost?
Heb dit bekeken en geraak niet ver.......

let
Bron = Table.NestedJoin(Samenvoegen2, {"RecNummer"}, GroupSum_RecNummer, {"RecNummer"}, "Samenvoegen2 (2)", JoinKind.LeftOuter),
#"Samenvoegen2 (2) uitgevouwen" = Table.ExpandTableColumn(Bron, "Samenvoegen2 (2)", {"Tot Hoeveelheid"}, {"Tot Hoeveelheid"}),
#"Deling ingevoegd" = Table.AddColumn(#"Samenvoegen2 (2) uitgevouwen", "Delen", each [Som hoeveelheid] / [Tot Hoeveelheid], type number),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Deling ingevoegd",{{"Delen", Percentage.Type}, {"RecNummer", type number}, {"GrondStof", type number}, {"Iart.NaamRek", type text}, {"Max lart.InkoopPrijs", Currency.Type}}),
#"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Type gewijzigd",{{"Delen", "Percentage"}}),
#"Vermenigvuldiging ingevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd", "Valuta", each [Max lart.InkoopPrijs] * [Percentage], Currency.Type),
#"Andere kolommen verwijderd" = Table.SelectColumns(#"Vermenigvuldiging ingevoegd",{"RecNummer", "GrondStof", "Iart.NaamRek", "Som hoeveelheid", "Max lart.InkoopPrijs", "Percentage", "Valuta"})
in
#"Andere kolommen verwijderd"

 

[peter59]

Mag ik vragen als het kan hoe je dit stap voor stap hebt opgelost?

Dat wordt een moeilijke.
Ik dacht dat je al een beetje op de hoogte was v.w.b. Power Query aangezien in je voorbeeldje (#1) al query's in waren verwerkt.
Indeze heb je 2 keer een samenvoeging gedaan.


Op "Samenvoegen2" heb ik mijn voorbeeld van #2 op verder geborduurd.


Zoals je ziet bestaat deze uit nog 2, op de achtergrond draaiende query's.

Query "Samenvoegen2"

let
    Bron = Excel.CurrentWorkbook(){[Name="Samenvoegen2"]}[Content],
    #"Rijen gegroepeerd" = Table.Group(Bron, {"RecNummer", "GrondStof", "Iart.NaamRek"}, {{"Som hoeveelheid", each List.Sum([HoeveelHeid]), type number}, {"Max lart.InkoopPrijs", each List.Max([Iart.InkoopPrijs]), type number}}),
    Afgerond = Table.TransformColumns(#"Rijen gegroepeerd",{{"Som hoeveelheid", each Number.Round(_, 3), type number}}),
    #"Rijen gesorteerd" = Table.Sort(Afgerond,{{"RecNummer", Order.Ascending}, {"GrondStof", Order.Ascending}})
in
    #"Rijen gesorteerd"

Query "GroupSum_RecNummer"

let
    Bron = Excel.CurrentWorkbook(){[Name="Samenvoegen2"]}[Content],
    #"Rijen gegroepeerd" = Table.Group(Bron, {"RecNummer"}, {{"Tot Hoeveelheid", each List.Sum([HoeveelHeid]), type number}, {"Tot lart.InkoopPrijs", each List.Sum([Iart.InkoopPrijs]), Currency.Type}}),
    Afgerond = Table.TransformColumns(#"Rijen gegroepeerd",{{"Tot Hoeveelheid", each Number.Round(_, 3), type number}})
in
    Afgerond

En query "Berek_Perc_Valuta"

let
    Bron = Table.NestedJoin(Samenvoegen2, {"RecNummer"}, GroupSum_RecNummer, {"RecNummer"}, "Samenvoegen2 (2)", JoinKind.LeftOuter),
    #"Samenvoegen2 (2) uitgevouwen" = Table.ExpandTableColumn(Bron, "Samenvoegen2 (2)", {"Tot Hoeveelheid"}, {"Tot Hoeveelheid"}),
    #"Deling ingevoegd" = Table.AddColumn(#"Samenvoegen2 (2) uitgevouwen", "Delen", each [Som hoeveelheid] / [Tot Hoeveelheid], type number),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Deling ingevoegd",{{"Delen", Percentage.Type}, {"RecNummer", type number}, {"GrondStof", type number}, {"Iart.NaamRek", type text}, {"Max lart.InkoopPrijs", Currency.Type}}),
    #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Type gewijzigd",{{"Delen", "Percentage"}}),
    #"Vermenigvuldiging ingevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd", "Valuta", each [Max lart.InkoopPrijs] * [Percentage], Currency.Type),
    #"Andere kolommen verwijderd" = Table.SelectColumns(#"Vermenigvuldiging ingevoegd",{"RecNummer", "GrondStof", "Iart.NaamRek", "Som hoeveelheid", "Max lart.InkoopPrijs", "Percentage", "Valuta"})
in
    #"Andere kolommen verwijderd"

Zo goed als alle query's welke ik heb gebruik zijn tot stand gekomen met de UserInterface van Power Query. Dus geen geschreven query's.
Ik weet niet of het geheel het voor jou duidelijker maakt maar niet geschoten is altijd mis.

 

[peter59]

En dan nu in 1 query.

let
  Bron = Excel.CurrentWorkbook(){[Name = "Samenvoegen2"]}[Content],
  Groep1 = Table.Group(
    Bron,
    {"RecNummer", "GrondStof", "Iart.NaamRek"},
    {
      {"Som hoeveelheid", each List.Sum([HoeveelHeid]), type number},
      {"Max lart.InkoopPrijs", each List.Max([Iart.InkoopPrijs]), type number}
    }
  ),
  Groep2 = Table.Group(
    Bron,
    {"RecNummer"},
    {
      {"Tot Hoeveelheid", each List.Sum([HoeveelHeid]), type number},
      {"Tot lart.InkoopPrijs", each List.Sum([Iart.InkoopPrijs]), Currency.Type}
    }
  ),
  CombiGrp1Grp2 = Table.NestedJoin(
    Groep1,
    {"RecNummer"},
    Groep2,
    {"RecNummer"},
    "TotaalGegevens",
    JoinKind.LeftOuter
  ),
  Uitgevouwen = Table.ExpandTableColumn(CombiGrp1Grp2, "TotaalGegevens", {"Tot Hoeveelheid"}),
  BerekeningPercValuta = Table.AddColumn(
    Uitgevouwen,
    "Percentage",
    each [Som hoeveelheid] / [Tot Hoeveelheid],
    type number
  ),
  MetValuta = Table.AddColumn(
    BerekeningPercValuta,
    "Valuta",
    each [Max lart.InkoopPrijs] * [Percentage],
    type number
  ),
  Afgerond = Table.TransformColumns(
    MetValuta,
    {{"Som hoeveelheid", each Number.Round(_, 3), type number}}
  ),
  Resultaat = Table.Sort(Afgerond, {{"RecNummer", Order.Ascending}, {"GrondStof", Order.Ascending}}),
  KolVerw = Table.SelectColumns(
    Resultaat,
    {
      "RecNummer",
      "GrondStof",
      "Iart.NaamRek",
      "Som hoeveelheid",
      "Max lart.InkoopPrijs",
      "Percentage",
      "Valuta"
    }
  ),
  TypeGew = Table.TransformColumnTypes(
    KolVerw,
    {
      {"Max lart.InkoopPrijs", Currency.Type},
      {"Valuta", Currency.Type},
      {"Percentage", Percentage.Type}
    }
  )
in
  TypeGew

 

[Georgyboy]

Dankjewel Peter,

Zie dat Je Group By gebruikte, zal dit deze week verder proberen te begrijpen en toe te passen, zo ver ben ik nog niet, maar een ik het door heb is dit ook zeer handig.
Dank voor je tijd en uitleg.

 

[Georgyboy]

O zie net je extra Code ! Hartelijk dank

 

[cow18]

Hey, ivm. die VBA-code, anders nog een screenupdating=false aan toevoegen en misschien laten triggeren door een table-update, zou anders eens moeten kijken hoe precies, maar dit is toch maar éénmalig of niet ?
Kan met of zonder die ene lege regel tussen 2 recepten

 

[Georgyboy]

Hey Cow18,
Getest met je tip, werkt sneller dan een knipoog.
Hartelijk dank!

 

[peter59]

Ik heb je query verwerkt in jouw voorbeeld van je eerste vraag (#1).
Kijk eens of deze bij jou functioneert.

 

[Georgyboy]

Dag Peter,
Dank voor je ook werkende oplossing, knap gedaan.
Heb een vraag gesteld aan Copilot hoe je het vorige stap voor stap hebt aangepakt.
Leerrijk ! Probeer dit deze week eens van een nieuw "tabblad" (Tabel) om onderstaande stappen toe te passen zonder M-code.

1. Open Power Query Editor:
   
   • Ga naar het tabblad Gegevens in Excel.
   • Klik op Query's en verbindingen om het venster Query's en verbindingen te openen.
   • Dubbelklik op de query "Samenvoegen2" om de Power Query Editor te openen.
2. Groeperen op kolommen:
   
   • Selecteer de kolommen "RecNummer", "GrondStof" en "Iart.NaamRek" door de kolomkoppen te selecteren terwijl je de Ctrl-toets ingedrukt houdt.
   • Ga naar het tabblad Transformeren en klik op Groeperen op.
   • In het dialoogvenster dat verschijnt, selecteer je Geavanceerd.
   • Voeg de volgende groeperingen toe:
     • Nieuwe kolomnaam: "Som hoeveelheid", Bewerking: Som, Kolom: "HoeveelHeid".
     • Nieuwe kolomnaam: "Max lart.InkoopPrijs", Bewerking: Max, Kolom: "Iart.InkoopPrijs".
   • Klik op OK.
3. Nogmaals groeperen op "RecNummer":
   
   • Selecteer alleen de kolom "RecNummer".
   • Ga naar het tabblad Transformeren en klik op Groeperen op.
   • In het dialoogvenster dat verschijnt, selecteer je Geavanceerd.
   • Voeg de volgende groeperingen toe:
     • Nieuwe kolomnaam: "Tot Hoeveelheid", Bewerking: Som, Kolom: "HoeveelHeid".
     • Nieuwe kolomnaam: "Tot lart.InkoopPrijs", Bewerking: Som, Kolom: "Iart.InkoopPrijs".
   • Klik op OK.
4. Samenvoegen van de gegroepeerde tabellen:
   
   • Ga naar het tabblad Start en klik op Nieuwe bron > Query's samenvoegen.
   • Selecteer de eerste gegroepeerde tabel als primaire tabel en de tweede gegroepeerde tabel als secundaire tabel.
   • Koppel de tabellen op basis van de kolom "RecNummer".
   • Kies voor een Left Outer Join.
   • Klik op OK.
5. Uitvouwen van de samengevoegde tabel:
   
   • Klik op het uitbreidingsicoon naast de nieuwe kolom "TotaalGegevens".
   • Selecteer alleen de kolom "Tot Hoeveelheid" om uit te vouwen.
   • Klik op OK.
6. Toevoegen van berekende kolommen:
   
   • Ga naar het tabblad Toevoegen en klik op Aangepaste kolom.
   • Voeg een kolom "Percentage" toe met de formule [Som hoeveelheid] / [Tot Hoeveelheid].
   • Voeg een kolom "Valuta" toe met de formule [Max lart.InkoopPrijs] * [Percentage].
7. Kolommen afronden:
   
   • Selecteer de kolom "Som hoeveelheid".
   • Ga naar het tabblad Transformeren en klik op Afronden > Aantal decimalen > 3.
8. Sorteren van de tabel:
   
   • Selecteer de kolommen "RecNummer" en "GrondStof".
   • Ga naar het tabblad Start en klik op Sorteren oplopend.
9. Kolommen selecteren:
   
   • Ga naar het tabblad Start en klik op Kolommen kiezen.
   • Selecteer de kolommen die je wilt behouden: "RecNummer", "GrondStof", "Iart.NaamRek", "Som hoeveelheid", "Max lart.InkoopPrijs", "Percentage", "Valuta".
10. Kolomtypen instellen:
    
    • Selecteer de kolom "Max lart.InkoopPrijs" en stel het type in op Valuta.
    • Selecteer de kolom "Valuta" en stel het type in op Valuta.
    • Selecteer de kolom "Percentage" en stel het type in op Percentage.
11. Query voltooien:
    

1. Klik op Sluiten en laden om de bewerkingen toe te passen en de gegevens terug te laden naar Excel.

 

[Georgyboy]

Dank Peter59 en Cow18 voor jullie leerrijke werkende oplossingen.

 

[peter59]

Heb een vraag gesteld aan Copilot hoe je het vorige stap voor stap hebt aangepakt.

Beter kan ik het niet uitleggen.