Santrauka
Norėdami konvertuoti išlaidas per vieną laiko vienetą (ty dienos, savaitės, mėnesio ir kt.) Į kitus laiko vienetus, galite naudoti dvipusę INDEX ir MATCH formulę. Parodytame pavyzdyje formulė E5 (nukopijuota skersai ir žemyn) yra:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
kur duomenys (O5: S9), vunitai (N5: N9) ir hunitai (O4: S4) yra pavadinti diapazonais, kaip paaiškinta toliau.
Paaiškinimas
Norėdami konvertuoti išlaidas per vieną laiko vienetą (ty dienos, savaitės, mėnesio ir kt.) Į kitus laiko vienetus, galite naudoti dvipusę INDEX ir MATCH formulę. Parodytame pavyzdyje formulė E5 (nukopijuota skersai ir žemyn) yra:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Šioje formulėje naudojama paieškos lentelė su pavadintais diapazonais, kaip parodyta žemiau:
Pavadinti diapazonai: duomenys (O5: S9), vienetai (N5: N9) ir medžioklės (O4: S4).
Įvadas
Tikslas yra konvertuoti išlaidas per vieną laiko vienetą į lygiavertes išlaidas kituose laiko vienetuose. Pvz., Jei mūsų mėnesio išlaidos yra 30 USD, mes norime apskaičiuoti 360 USD metines išlaidas, 7,50 USD per savaitę ir kt.
Kaip ir tiek daug „Excel“ iššūkių, daug kas priklauso nuo to, kaip jūs priimsite problemą. Pirmiausia galite susigundyti apsvarstyti įdėtų IF formulių grandinę. Tai galima padaryti, bet galų gale turėsite ilgą ir sudėtingą formulę.
Švaresnis metodas yra sukurti paieškos lentelę, kurioje yra visų galimų konversijų konversijos koeficientai, tada naudokite dvipusę INDEX ir MATCH formulę, kad gautumėte reikiamą konkrečios konversijos vertę. Kai turėsite vertę, galite paprasčiausiai padauginti iš pradinės sumos.
Konversijos lentelė
Konversijos lentelėje yra tos pačios vertikalių ir horizontalių etikečių vertės: dienos, savaitės, kas dvi savaites, mėnesio ir metų. Vienetai „nuo“ pateikiami vertikaliai, o „iki“ - horizontaliai. Šiame pavyzdyje pirmiausia norime suderinti eilutę, tada stulpelį. Taigi, jei norime konvertuoti mėnesines išlaidas į metines, suderinsime „mėnesio“ ir „metinių“ stulpelius ir grąžinsime 12.
Norėdami užpildyti pačią lentelę, naudojame paprastų formulių ir konstantų derinį:
Pastaba: pritaikykite konversijų vertes, kad atitiktų jūsų specifinius poreikius. Vertės įvedimas kaip = 1/7 yra paprastas būdas išvengti ilgų dešimtainių reikšmių įvedimo.
Paieškos formulė
Kadangi turime rasti konversijos vertę, pagrįstą dviem įvestimis, laiko vienetu „nuo“ ir „laiko“, mums reikia dvipusio paieškos formulės. INDEX ir MATCH pateikia gražų sprendimą. Parodytame pavyzdyje formulė E5 yra:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Dirbant iš vidaus, pirmoji MATCH funkcija nustato teisingą eilutę:
MATCH($D5,vunits,0) // find row, returns 4
Iš D stulpelio mes ištraukiame originalų laiko vienetą „nuo“, kurį naudojame norėdami surasti reikiamą eilę pavadintuose diapazono vienetuose (N5: N9). Pastaba $ D5 yra mišri nuoroda, kai stulpelis užrakintas, todėl formulę galima nukopijuoti.
Antroji funkcija MATCH nustato stulpelį:
MATCH(F$4,hunits,0) // find column, returns 5
Čia mes gauname paieškos vertę iš stulpelio antraštės 4 eilutėje ir naudokite ją norėdami rasti tinkamą stulpelį „iki“ pavadintame diapazono medžioklėje (O4: S4). Vėlgi, pastaba F $ 4 yra mišri nuoroda, kai eilutė užrakinta, todėl formulę galima nukopijuoti žemyn.
Po to, kai abi MATCH formulės grąžins rezultatus į INDEX, turime:
=$C5*INDEX(data,4,5)
INDEX pateiktas masyvas yra įvardyti diapazono duomenys (O5: S9). Pateikdami 4 eilutę ir 5 stulpelį, INDEX grąžina 12, taigi gauname tokį 12000 galutinį rezultatą:
=$C5*INDEX(data,4,5) =1000*12 =12000
