Resumé
For at konvertere en udgift i en tidsenhed (dvs. dagligt, ugentligt, månedligt osv.) Til andre tidsenheder kan du bruge en tovejs INDEX- og MATCH-formel. I det viste eksempel er formlen i E5 (kopieret over og ned):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
hvor data (O5: S9), vunits (N5: N9) og jagter (O4: S4) er navngivet områder, som forklaret nedenfor.
Forklaring
For at konvertere en udgift i en tidsenhed (dvs. dagligt, ugentligt, månedligt osv.) Til andre tidsenheder kan du bruge en tovejs INDEX- og MATCH-formel. I det viste eksempel er formlen i E5 (kopieret over og ned):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Denne formel bruger en opslagstabel med navngivne områder som vist nedenfor:
Navngivne områder: data (O5: S9), vunits (N5: N9) og jagter (O4: S4).
Introduktion
Målet er at konvertere en udgift i en tidsenhed til en tilsvarende udgift i andre tidsenheder. For eksempel, hvis vi har en månedlig udgift på $ 30, vil vi beregne en årlig udgift på $ 360, en ugentlig udgift på $ 7,50 osv.
Som så mange udfordringer i Excel afhænger meget af, hvordan du nærmer dig problemet. Du kan først blive fristet til at overveje en kæde af indlejrede IF-formler. Dette kan gøres, men du ender med en lang og kompliceret formel.
En renere tilgang er at opbygge en opslagstabel, der indeholder konverteringsfaktorer for alle mulige konverteringer, og derefter bruge en tovejs INDEX- og MATCH-formel til at hente den krævede værdi til en given konvertering. Når du har værdien, kan du simpelthen gange med det oprindelige beløb.
Konverteringstabellen
Konverteringstabellen har de samme værdier for både lodrette og vandrette etiketter: dagligt, ugentligt, hver uge, månedligt og årligt. Enhederne "fra" er anført lodret, og enhederne "til" vises vandret. Med henblik på dette eksempel ønsker vi at matche rækken først og derefter kolonnen. Så hvis vi vil konvertere en månedlig udgift til en årlig udgift, matcher vi rækken "månedlig" og kolonnen "årlig" og returnerer 12.
For at udfylde selve tabellen bruger vi en blanding af enkle formler og konstanter:
Bemærk: Tilpas konverteringsværdier for at imødekomme dine specifikke behov. Indtastning af en værdi som = 1/7 er en nem måde at undgå at indtaste lange decimalværdier.
Opslagsformlen
Da vi skal finde en konverteringsværdi baseret på to indgange, en "fra" tidsenhed og en "til" tidsenhed, har vi brug for en tovejs opslagsformel. INDEX og MATCH giver en god løsning. I det viste eksempel er formlen i E5:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Arbejder indefra og ud, den første MATCH-funktion finder den rigtige række:
MATCH($D5,vunits,0) // find row, returns 4
Vi trækker den originale "fra" tidsenhed fra kolonne D, som vi bruger til at finde den rigtige række i det navngivne interval vunits (N5: N9). Bemærk $ D5 er en blandet reference med kolonnen låst, så formlen kan kopieres over.
Den anden MATCH-funktion finder kolonnen:
MATCH(F$4,hunits,0) // find column, returns 5
Her får vi opslag værdi fra kolonneoverskriften i række 4, og bruge dette til at finde den rigtige "til" kolonne i det navngivne område hunits (O4: S4). Igen er note F $ 4 en blandet reference med rækken låst, så formlen kan kopieres ned.
Efter at begge MATCH-formler har returneret resultater til INDEX, har vi:
=$C5*INDEX(data,4,5)
Grupperingen billede til INDEX er det navngivne område data , (O5: S9). Med en række på 4 og kolonne på 5 returnerer INDEX 12, så vi får et endeligt resultat på 12000 som dette:
=$C5*INDEX(data,4,5) =1000*12 =12000
