Kokkuvõte
Ühe ajaühiku (st päeva-, nädala-, kuu- jne) kulude teisendamiseks muudeks ajaühikuteks saate kasutada kahesuunalist valikut INDEX ja MATCH. Näidatud näites on valem E5-s (üle ja alla kopeeritud):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
kus andmed (O5: S9), vunits (N5: N9) ja hunits (O4: S4) nimetatakse vahemikeks, nagu allpool selgitatud.
Selgitus
Ühe ajaühiku (st päeva-, nädala-, kuu- jne) kulude teisendamiseks muudeks ajaühikuteks saate kasutada kahesuunalist valikut INDEX ja MATCH. Näidatud näites on valem E5-s (üle ja alla kopeeritud):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Selles valemis kasutatakse allpool näidatud nimega vahemikega otsingu tabelit:
Nimetatud vahemikud: andmed (O5: S9), vunitsid (N5: N9) ja jahitid (O4: S4).
Sissejuhatus
Eesmärk on teisendada kulu ühes ajaühikus samaväärseks kuluks teistes ajaühikutes. Näiteks kui meil on igakuine kulu 30 dollarit, tahame arvutada aastase kulu 360 dollarit, nädalase kulutuse 7,50 dollarit jne.
Nagu nii paljud Exceli väljakutsed, sõltub palju ka sellest, kuidas probleemile lähenete. Kõigepealt võib tekkida kiusatus kaaluda pesastatud IF-valemite ahelat. Seda saab teha, kuid saate lõpuks pika ja keeruka valemi.
Puhtam lähenemisviis on luua otsingutabel, mis sisaldab kõigi võimalike konversioonide teisendustegureid, seejärel kasutada kahepoolset valemit INDEX ja MATCH antud teisenduse vajaliku väärtuse hankimiseks. Kui väärtus on käes, saate selle korrutada algsummaga.
Teisendustabel
Konversioonitabelis on nii vertikaalsete kui ka horisontaalsete siltide jaoks samad väärtused: päevane, nädalane, kaks nädalat, kuu ja aasta. Üksused "alates" on loetletud vertikaalselt ja "kuni" ühikud on loetletud horisontaalselt. Selles näites tahame kõigepealt sobitada rea, seejärel veeru. Niisiis, kui soovime teisendada igakuised kulud aastakuludeks, sobitame veerud "kuu" ja "iga-aastane" ning tagastame 12.
Tabeli enda täitmiseks kasutame lihtsate valemite ja konstantide segu:
Märkus. Kohandage konversiooniväärtused vastavalt oma konkreetsetele vajadustele. Väärtuse sisestamine = 1/7 on lihtne viis pikkade kümnendarvude sisestamise vältimiseks.
Otsingu valem
Kuna peame leidma teisendusväärtuse, mis põhineb kahel sisendil, ajaühikul "alates" ja ajaühikul "kuni", vajame kahesuunalise otsingu valemit. INDEX ja MATCH pakuvad kena lahenduse. Näidatud näites on valem E5-s järgmine:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Seestpoolt töötades leiab esimene funktsioon MATCH õige rea:
MATCH($D5,vunits,0) // find row, returns 4
Tõmbame veerust D algse ajaühiku "alates", mille abil leiame õige vahemiku nimega vahemiku ühikutest (N5: N9). Märkus $ D5 on segatud viide, mille veerg on lukustatud, nii et valemit saab üle kopeerida.
Teine funktsioon MATCH otsib veeru:
MATCH(F$4,hunits,0) // find column, returns 5
Siit saame otsinguväärtuse 4. rea veeru päisest ja kasutame seda, et leida õige veerg "kuni" nimega vahemiku jadid (O4: S4). Jällegi, märkus F $ 4 on segatud viide lukustatud reaga, nii et valemit saab alla kopeerida.
Pärast seda, kui mõlemad MATCH-valemid tagastavad tulemused INDEX-i, on meil:
=$C5*INDEX(data,4,5)
INDEXile edastatud massiiv on vahemiku andmed (O5: S9). Nelja rea ja 5 veeruga tagastab INDEX 12, seega saame 12000 lõpptulemuse järgmiselt:
=$C5*INDEX(data,4,5) =1000*12 =12000
