Sammendrag
For å konvertere en utgift i en tidsenhet (dvs. daglig, ukentlig, månedlig osv.) Til andre tidsenheter, kan du bruke en toveis INDEKS- og MATCH-formel. I eksemplet vist er formelen i E5 (kopiert på tvers og nedover):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
der data (O5: S9), vunits (N5: N9) og hunts (O4: S4) er kalt områder, som forklart nedenfor.
Forklaring
For å konvertere en utgift i en tidsenhet (dvs. daglig, ukentlig, månedlig osv.) Til andre tidsenheter, kan du bruke en toveis INDEKS- og MATCH-formel. I eksemplet vist er formelen i E5 (kopiert på tvers og nedover):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Denne formelen bruker en oppslagstabell med navngitte områder som vist nedenfor:
Navngitte områder: data (O5: S9), vunits (N5: N9) og hunits (O4: S4).
Introduksjon
Målet er å konvertere en utgift i en tidsenhet, til en tilsvarende utgift i andre tidsenheter. For eksempel, hvis vi har en månedlig utgift på $ 30, vil vi beregne en årlig utgift på $ 360, en ukentlig utgift på $ 7,50 osv.
Som så mange utfordringer i Excel, avhenger mye av hvordan du nærmer deg problemet. Du kan først bli fristet til å vurdere en kjede av nestede IF-formler. Dette kan gjøres, men du vil ende opp med en lang og komplisert formel.
En renere tilnærming er å lage en oppslagstabell som inneholder konverteringsfaktorer for alle mulige konverteringer, og deretter bruke en toveis INDEX og MATCH-formel for å hente den nødvendige verdien for en gitt konvertering. Når du har verdien, kan du ganske enkelt multiplisere med det opprinnelige beløpet.
Konverteringstabellen
Konverteringstabellen har de samme verdiene for både vertikale og horisontale etiketter: daglig, ukentlig, to uker, månedlig og årlig. "Fra" -enhetene er oppført vertikalt, og "til" -enhetene er oppført vannrett. I dette eksemplet ønsker vi å matche raden først, deretter kolonnen. Så hvis vi vil konvertere en månedlig utgift til en årlig utgift, matcher vi "månedlig" -raden og "årlig" -kolonnen, og returnerer 12.
For å fylle ut selve tabellen bruker vi en blanding av enkle formler og konstanter:
Merk: Tilpass konverteringsverdier for å dekke dine spesifikke behov. Å skrive inn en verdi som = 1/7 er en enkel måte å unngå å angi lange desimalverdier.
Oppslagsformelen
Siden vi må finne en konverteringsverdi basert på to innganger, en "fra" tidsenhet og en "til" tidsenhet, trenger vi en toveis oppslagsformel. INDEX og MATCH gir en fin løsning. I eksemplet vist er formelen i E5:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Arbeider fra innsiden og ut, den første MATCH-funksjonen finner riktig rad:
MATCH($D5,vunits,0) // find row, returns 4
Vi trekker den originale "fra" tidsenheten fra kolonne D, som vi bruker for å finne den rette raden i det valgte området vunits (N5: N9). Merknad $ D5 er en blandet referanse med kolonnen låst, slik at formelen kan kopieres over.
Den andre MATCH-funksjonen finner kolonnen:
MATCH(F$4,hunits,0) // find column, returns 5
Her får vi oppslagsverdien fra kolonneoverskriften i rad 4 på, og bruke dette til å finne den rette "til" kolonnen i navngitt område hunits (O4: S4). Igjen, merknad F $ 4 er en blandet referanse med raden låst, slik at formelen kan kopieres ned.
Etter at begge MATCH-formlene har gitt resultatene til INDEX, har vi:
=$C5*INDEX(data,4,5)
Matrisen gitt til INDEX er det navngitte området data , (O5: S9). Med en rad på 4 og kolonne på 5 returnerer INDEX 12, så vi får et endelig resultat på 12000 slik:
=$C5*INDEX(data,4,5) =1000*12 =12000
