[Excel 2016] Welke functies zoek ik? Meerdere uitkomsten

[Denizz]

Versie:
Excel 2016 Engels op Windows 10 Engels

Situatie:

Ik heb een spreadsheet waarin de (toekomstige) gebruiker aan de hand van een invoerblad waardes invult. Aan de hand van deze waardes wordt een berekening op een aparte sheet, waarvan de berekening afhankelijk is wat de gebruiker invoert op de eerste pagina. Deze berekening bestaat uit vijf stappen waarvan de waardes van elke stap afhankelijk is van de vorige uitkomsten. Dit werkt zoals het hoort, echter voor slechts 1 situatie. Ter illustratie wil ik graag kunnen aangeven wat de uitkomsten zijn van verschillende situaties (in dit geval verschillende debieten).

Specifiek voor nu concentreer ik mij op slechts een invoerwaarde, debiet. Als ik dit werkend krijg vind ik zelf wel uit hoe dat voor andere invoerwaarden te regelen.

Wat ik nu heb: 20 kopieën van het invoerblad + berekeningblad met 20 verschillende debieten als invoer onder elkaar op één aparte pagina, waarvan de uitkomsten stuk voor stuk in een tabel gekopieerd worden op het voorblad . Dit is extreem onhandig, want elke keer als ik een wijziging moet maken in de daadwerkelijke berekening, mag ik het ook nog even 20x op dat tabblad gaan doen. En dan nog niet te spreken over als ik later grafieken en dergelijke wil maken van andere invoerwaarden welke de gebruiker kan opgeven.

Wat ik wil hebben: Een manier waarop ik aan de hand van bijvoorbeeld een tabel met de te gebruiken invoerwaarden voor het debiet (welke ik nu op 20 stappen heb staan met percentages van 10-200% van de gebruiker ingevoerde debiet), de berekening op het calculatieblad kan laten doorlopen en daarvan de uitkomsten laat presenteren in die tabel. En dan zonder alles 20x op een apart tabblad te kopiëren .

Wat ik nodig heb: Ik weet totaal niet waarnaar te zoeken omdat ik helaas niet heel erg ervaren ben met Excel, dus namen van functies in zowel 'gewone' Excel als VBA zouden mij een heel eind kunnen helpen om zelf tot een oplossing te komen via het alleswetende Google. Ik kom qua VBA op het moment niet verder dan knoppen, maar ik wat kennis in VBA opdoen staat al een tijdje op mijn 'verlanglijst'.

Wat ik al gevonden of geprobeerd heb
Mijn zoekgeschiedenis van Google staat vol met gegokte Excel gerelateerde termen en kreten. Dezelfde vraag op Reddit gesteld. Helaas was het uitschrijven van het probleem wat lastiger dan in het Nederlands, en begreep hij niet helemaal wat ik bedoel, maar diegene zei:

Another way would be to create a range of input columns and run a macro that inputs them into your existing sheet, re-calculates everything, and then copies the output to another range and then repeats. This would actually be pretty simple code to write most likely.

Dit klinkt heel erg als wat ik wil hebben, maar helaas is mijn verzoek om relevante zoektermen/functies onbeantwoord gebleven en weet ik dus niet hoe verder te gaan op dit.

Alvast bedankt en als ik onduidelijk ben (wat vast zo zou zijn), mijn excuses.

[Anoniem: 33272]

Je wilt dus meerdere scenario's overzichtelijk maken? Hiervoor heb je in Excel de mogelijkheid via Wat-als-analyse (What-If Analysis). Daarin kun je verschillende scenario's maken met één of meerdere variabelen en daarna hiervan een overzicht genereren. Ik zou in ieder geval hier eens mee beginnen.

Daarnaast zou het misschien ook handig zijn om hiervan een screenshot of voorbeeld neer te zetten omdat het lastig is om een goed beeld te krijgen van de situatie aan de hand van alleen tekst.

[ Voor 29% gewijzigd door Anoniem: 33272 op 17-10-2016 11:33 ]

[Dreamvoid]

Het is inderdaad nogal onduidelijk wat je precies wil, maar in dit soort gevallen (een workflow met meerdere stappen automatiseren) helpt het als VBA beginner om alle stappen in een macro te recorden ("record macro") en dan in de VBA code te duiken. Om bv data onderaan een steeds groeiende tabel toe te voegen gebruik je tijdens het opnemen van de macro dan sneltoetsen als Control-Down.

[Denizz]

Ugh, excuses. Schrijf ik een half boek en dan nog kan ik niet duidelijk maken wat ik bedoel .

Het gaat inderdaad om meerdere scenario's. Voor het overzicht gebruik ik nu alleen even 1 invoerwaarde, debiet, waarvan ik de verschillende uitkomsten wil zien. Daarbij wordt in de berekening in het calculatieblad het toerental en het koppel berekend. Het gehele calculatieblad is nodig voor de uitkomsten van het toerental (welke halverwege de calculatie naar voren komt) en koppel welke helemaal aan het eind berekend is, maar afhankelijk is van de eerdere berekeningen op het blad.

Ik wil een handigere manier om bij deze situaties/scenarios de uitkomsten te berekenen dan een kopie van invoerblad+calculatieblad x20. Dus stel de gebruiker voert een debiet in van 2000 m3/u, dat er dan in een (al dan niet zichtbare) tabel enkele stappen met hogere en lagere debieten (welke een percentage zijn van het gebruiker ingevoerde debiet in stappen van 10% op het moment).

Ik wil Excel dan de berekening doorlaten lopen op het calculatieblad alsof er dat getal staat in plaats van het gebruikersingevoerde debiet over het calculatieblad en daarbij de twee benodigde waardes welke daar berekend worden 'print' in die tabel. Het gebruikersingevoerde getal moet en bijbehorende uitkomsten moeten echter blijven staan op het invoer/uitkomstenblad, het is puur en alleen voor die tabel waaruit ik weer grafieken wil maken.

Ik heb even een versimpelde weergaven getracht te maken wat ik bedoel:

http://imgur.com/a/1dZDA

Helaas kan ik niet de werkmap zelf laten zien, zitten wat 'gevoelige' dingen in welke niet openbaar mogen komen, excuses.

Edit: Ik ga ondertussen even mijzelf verdiepen in de 'what if analysis' functie .

[ Voor 6% gewijzigd door Denizz op 17-10-2016 12:09 ]

[Lustucru]

Voor als what-if niet doet wat je wilt ook nog een voorbeeldje van een VBA-oplossing die precies doet wat je beschrijft:

- maak een sheetje met twee werkbladen, die je 'invoer' en 'calc' noemt
- Zet 'calc' zo op dat als je in A1 een getal invult, dat dan in B1 en C1 berekende waarden terechtkomen.
- Open de vba-editor en plak de volgende code in de module van werkblad invoer

Visual Basic:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rij As Integer
    Dim perc As Double
    ' Doe alleen iets als cel A1 is gewijzigd
    If Not (Intersect(Target, Sheets!invoer.[A1]) Is Nothing) Then
       'voer de berekening uit voor 70 tot 130% in stapjes van 10%
        For perc = 0.7 To 1.3 Step 0.1
            'vul de percentuele waarde in op het calc-blad
            Sheets!calc.[A1] = perc * Target.Value
            'schrijf de berekende waarden terug naar het invoerblad in oplopende rijen en kolommen
            Target.Offset(rij, 1) = Sheets!calc.[A1]
            Target.Offset(rij, 2) = Sheets!calc.[b1]
            Target.Offset(rij, 3) = Sheets!calc.[c1]
            'rijteller ophogen om de volgende rij te schrijven
            rij = rij + 1
        Next perc
    End If
End Sub

Zodra je nu op het blad Invoer cel A1 wijzigt worden de cellen B1:D7 automatisch gevuld.

[ Voor 6% gewijzigd door Lustucru op 17-10-2016 19:31 ]

[Denizz]

Geweldig, het werkt , en daar ben ik toch best wel heel blij mee. Je hebt mij behoorlijk wat werk bespaard. Heb je deze code zelf gemaakt? Zoja, dubbel bedankt.