Samenvatting
Om een uitgave in één tijdseenheid (dwz dagelijks, wekelijks, maandelijks, enz.) Om te rekenen naar andere tijdseenheden, kunt u een tweewegs INDEX- en MATCH-formule gebruiken. In het getoonde voorbeeld is de formule in E5 (heen en weer gekopieerd):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
waarbij gegevens (O5: S9), vunits (N5: N9) en hunits (O4: S4) bereiken worden genoemd, zoals hieronder wordt uitgelegd.
Uitleg
Om een uitgave in één tijdseenheid (dwz dagelijks, wekelijks, maandelijks, enz.) Om te rekenen naar andere tijdseenheden, kunt u een tweewegs INDEX- en MATCH-formule gebruiken. In het getoonde voorbeeld is de formule in E5 (heen en weer gekopieerd):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Deze formule gebruikt een opzoektabel met benoemde bereiken zoals hieronder weergegeven:
Benoemde bereiken: gegevens (O5: S9), vunits (N5: N9) en hunits (O4: S4).
Invoering
Het doel is om een uitgave in één tijdseenheid om te rekenen naar een gelijkwaardige uitgave in andere tijdseenheden. Als we bijvoorbeeld een maandelijkse uitgave van € 30 hebben, willen we een jaarlijkse uitgave van € 360, een wekelijkse uitgave van € 7,50, enz. Berekenen.
Zoals zoveel uitdagingen in Excel, hangt er veel af van hoe u het probleem benadert. U zou in de verleiding kunnen komen om een reeks geneste IF-formules te overwegen. Dit is mogelijk, maar je krijgt een lange en ingewikkelde formule.
Een schonere benadering is om een opzoektabel samen te stellen die conversiefactoren bevat voor alle mogelijke conversies, en vervolgens een bidirectionele INDEX- en MATCH-formule te gebruiken om de vereiste waarde voor een bepaalde conversie op te halen. Zodra u de waarde heeft, kunt u deze eenvoudig vermenigvuldigen met het oorspronkelijke bedrag.
De conversietabel
De conversietabel heeft dezelfde waarden voor zowel verticale als horizontale labels: dagelijks, wekelijks, tweewekelijks, maandelijks en jaarlijks. De eenheden van "van" worden verticaal weergegeven en de eenheden van "tot" worden horizontaal weergegeven. Voor de doeleinden van dit voorbeeld willen we eerst de rij matchen en daarna de kolom. Dus als we een maandelijkse uitgave willen omrekenen naar een jaarlijkse uitgave, matchen we de rij "maandelijks" en de kolom "jaarlijks" en retourneren 12.
Om de tabel zelf te vullen, gebruiken we een mix van eenvoudige formules en constanten:
Opmerking: pas conversiewaarden aan om aan uw specifieke behoeften te voldoen. Het invoeren van een waarde als = 1/7 is een gemakkelijke manier om het invoeren van lange decimale waarden te vermijden.
De opzoekformule
Aangezien we een conversiewaarde moeten lokaliseren op basis van twee invoer, een "van" tijdseenheid en een "tot" tijdseenheid, hebben we een bidirectionele opzoekformule nodig. INDEX en MATCH bieden een mooie oplossing. In het getoonde voorbeeld is de formule in E5:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Werkend van binnen naar buiten, lokaliseert de eerste MATCH-functie de juiste rij:
MATCH($D5,vunits,0) // find row, returns 4
We halen de originele "van" tijdseenheid uit kolom D, die we gebruiken om de juiste rij te vinden in het genoemde bereik vunits (N5: N9). Opmerking $ D5 is een gemengde verwijzing waarbij de kolom is vergrendeld, zodat de formule eroverheen kan worden gekopieerd.
De tweede MATCH-functie lokaliseert de kolom:
MATCH(F$4,hunits,0) // find column, returns 5
Hier krijgen we de opzoekwaarde uit de kolomkop in rij 4, en gebruiken deze om de rechterkolom "naar" te vinden in de benoemde reeks hunits (O4: S4). Nogmaals, merk op dat F $ 4 een gemengde verwijzing is met de rij vergrendeld, dus de formule kan naar beneden worden gekopieerd.
Nadat beide MATCH-formules resultaten hebben geretourneerd naar INDEX, hebben we:
=$C5*INDEX(data,4,5)
De array die aan INDEX het benoemde bereik gegevens , (O5: S9). Met een rij van 4 en kolom van 5 retourneert INDEX 12, dus we krijgen een eindresultaat van 12000 als volgt:
=$C5*INDEX(data,4,5) =1000*12 =12000
