[Excel] Verschillende formule op basis van andere cel

[Kurgan]

Ik zit met het volgende probleem: ik heb een lijst van een paar honderd gemeten waarden (druk en flowrate) die in een grafiek moeten worden gezet. De metingen zijn altijd in dezelfde eenheden (Bar en m³/h) maar de eenheden voor de grafiek kunnen door de gebruiker worden gekozen. Alle gemeten waarden moeten dus omgerekend worden en de formule daarvoor is afhankelijk van de gekozen eenheid. Dit kan ik natuurlijk doen via een geneste IF-functie maar dat wordt een gedrocht van een formule. Voor de druk heb ik 10 mogelijkheden en voor de flowrate 15 dus ga maar na hoeveel geneste IF's dat worden. Vanwege de beveiligingsinstellingen kan ik geen macro's gebruiken, is er nog een andere manier waarop ik op basis van de waarde in één cel een andere berekening kan toepassen?

[ Voor 4% gewijzigd door Kurgan op 06-01-2021 09:55 ]

[Roozzz]

Kan je een hulpkolom of tabel gebruiken? Dan zou je eerst filteren op eenheid en wegschrijven (basale IF), vervolgens hiervandaan verder rekenen met de druk of flowrate formules.

Dat maakt de wereld een stuk eenvoudiger.

[ Voor 11% gewijzigd door Roozzz op 06-01-2021 10:00 ]

[Kurgan]

Ja dat kan en ben ik nu inderdaad in elkaar aan het zetten. Ik vroeg me alleen af of er geen elegantere oplossing is. Een goede techneut is ten slotte lui en zoekt altijd naar de makkelijkste oplossing.

[F_J_K]

Hulpkolom is juist het makkelijkste IMHO. Geen geneste formules, maar verticaal zoeken. Misschien dat alsnog 1x genest, maar dat kan ik niet inschatten zonder details over de huidige formules.

[Kurgan]

Ja hulpkolommen werkt wel, en vervolgens met MATCH de kolom met de gewenste eenheid selecteren om de grafiek te maken. Ik vind het alleen zo weinig elegant dus vandaar dat ik me afvroeg of er geen andere mogelijkheid was, misschien iets met INDIRECT of zo.

[g0tanks]

Wellicht dat we beter kunnen helpen als je laat zien hoe die formules eruitzien en doorgerekend moeten worden (met screenshots?).

[Kurgan]

Ik denk niet dat ik de daadwerkelijke sheet mag laten zien, sommige delen bevatten namelijk vertrouwelijke informatie en het bedrijf waar ik werk is daar tamelijk strikt in (zie ook het niet toestaan van macro's). Ik zal kijken of ik even iets vergelijkbaars als voorbeeld in elkaar kan zetten.

[g0tanks]

Ik vraag me vooral af waarom die verschillende eenheden zoveel complexiteit toevoegen, behalve dat het er zoveel zijn. Zit er geen logica in?

[Lustucru]

Het lijkt me dat de verschillende omrekenformules wel te herleiden zijn naar één generieke formule met een beperkt aantal termen waarbij alleen de coefficienten verschillen. Die kun je dan invullen dmv verticaal zoeken of vergelijken.

[Kurgan]

Hieronder een vereenvoudigd voorbeeld:


Uiteindelijk moeten dus zowel de druk als de flow omgerekend worden naar de door de gebruiker geselecteerde eenheden en dat moet weer in een grafiek worden weergegeven.

Het lijkt me dat de verschillende omrekenformules wel te herleiden zijn naar één generieke formule met een beperkt aantal termen waarbij alleen de coefficienten verschillen.

Dat kan inderdaad maar zoals gezegd krijg je dan een gigantische formule met een heleboel geneste IF-functies. Ik heb in dit voorbeeld het aantal eenheden beperkt maar als ik alles in één formule zou willen stoppen dan wordt die ongeveer drie regels lang. Niet onmogelijk, wel onoverzichtelijk. Met hulpkolommen is het ook te doen maar ik vraag me af of er geen elegantere / efficiëntere manier bestaat.

[g0tanks]

Kurgan schreef op donderdag 7 januari 2021 @ 10:40:
Met hulpkolommen is het ook te doen maar ik vraag me af of er geen elegantere / efficiëntere manier bestaat.

Een aparte rekensheet met daarin voor elke formule een aparte kolom is naar mijn mening het meest elegant en vooral het makkelijkst te volgen voor iemand anders.

Een alternatief is het gebruiken van SWITCH om de juiste formule te selecteren (beschikbaar in recente versies van Excel): https://exceljet.net/excel-functions/excel-switch-function Het blijft een formule met veel termen, maar je hebt in ieder geval geen geneste IFs.

[KabouterSuper]

Named ranges zijn je vriend hier. Je kunt namelijk je data dynamisch omzetten met behulp van named ranges en vervolgens deze ranges in een grafiek zetten.

Voorbeeld: in tab "Sheet1" cel D1 staat een factor, bijvoorbeeld 288.15/3600.
in cellen A1:A100 staat je data met druk P.
Je maakt een named range P_converted aan met de volgende formule: =Sheet1!$D$1*Sheet1!A1:A100
Vervolgens maak je een grafiek met data Sheet1!P_converted (let op dat hier de sheetname altijd verplicht is)

Je kunt nu met D1 je data schalen zonder dat je je grafiek hoeft aan te passen.

Vervolgens zorg je dat je gebruikers via een dropdown lijst oid een keuze kunnen maken voor de eenheid. De dropdownlijst vertaal je naar een getal in cel D1. Eventueel kan je de waarde van de dropdownlijst zelfs in de named range stoppen, maar daar wordt het niet overzichtelijker van.

[ Voor 22% gewijzigd door KabouterSuper op 07-01-2021 13:30 ]

[Kurgan]

@g0tanks

Een alternatief is het gebruiken van SWITCH

Die functie zit helaas niet in de versie van Excel die op mijn werk wordt gebruikt.

@KabouterSuper
Het maken van de grafiek is geen probleem want met INDEX en MATCH heb je zo de juiste kolommen te pakken. Named ranges gebruik ik al heel veel want iets als PRESSURE_TABLE is ten slotte een stuk duidelijker dan $C$5:$C$435. De eenheden heb ik inderdaad in een dropdown lijst staan. De toegestane eenheden zijn namelijk afhankelijk van of het medium gas, vloeistof of 2-fasen is dus daar heb ik een dynamische pulldown voor gebruikt.

Het gaat dus allemaal wel lukken, ik vroeg me alleen af of er geen elegantere manier bestond dan hulpkolommen en zo. Blijkbaar helaas dus niet. Iedereen in elk geval bedankt voor de antwoorden en suggesties.

[KabouterSuper]

Kurgan schreef op donderdag 7 januari 2021 @ 14:13:
@KabouterSuper
Het maken van de grafiek is geen probleem want met INDEX en MATCH heb je zo de juiste kolommen te pakken. Named ranges gebruik ik al heel veel want iets als PRESSURE_TABLE is ten slotte een stuk duidelijker dan $C$5:$C$435. De eenheden heb ik inderdaad in een dropdown lijst staan. De toegestane eenheden zijn namelijk afhankelijk van of het medium gas, vloeistof of 2-fasen is dus daar heb ik een dynamische pulldown voor gebruikt.

Het gaat dus allemaal wel lukken, ik vroeg me alleen af of er geen elegantere manier bestond dan hulpkolommen en zo. Blijkbaar helaas dus niet. Iedereen in elk geval bedankt voor de antwoorden en suggesties.

Met de named ranges kan je hulpkolommen vermijden, omdat je in de named range formule extra data manipulaties kunt gebruiken (maar dan op een hele kolom in één keer). Zeker als het alleen een vermenigvuldiging is, heeft dit zeker voordelen boven hulpkolommen.

[Lustucru]

Kurgan schreef op donderdag 7 januari 2021 @ 10:40:
[...]


Dat kan inderdaad maar zoals gezegd krijg je dan een gigantische formule met een heleboel geneste IF-functies.

Dat bedoel ik niet. Juist geen if's. Je versimpelde voorbeeld maakt het sowieso al duidelijk: de drukconversies zijn allemaal te schrijven als f(x)=(x-c)*d. In combinatie met named ranges heb je dus één formule zonder één if:



Sommige conversie zullen nog een term hebben maar dan houdt het denk ik wel op.:) Het enige wat je misschien nog moet doen is dat je voorafgaand aan de indirect een subsitutie moet doen: n/m2 is als naam niet bruikbaar omdat namen geen '/' mogen bevatten. Je krijgt dan iets als indirect(replace([TxtEenheid],'\','_')), maar ook dat is generiek.

[Kurgan]

Hallo iedereen, excuses voor de late reactie maar ik werd tijdelijk op een ander project gezet. Ik heb inmiddels met jullie suggesties een eerste versie van de sheet kunnen maken. De conversies schrijven in de vorm f(x)=(x+a)*b maakt het een heel stuk eenvoudiger en overzichtelijker. Het is dan nog slechts een kwestie van via een lookup de waarden voor a en b te bepalen op basis van de gekozen eenheid maar in de output tabel zelf staat altijd dezelfde formule. Iedereen bedankt voor de hulp!