Zamkatimu
Posachedwa tapereka nkhani ku imodzi mwazofunikira kwambiri za Excel zomwe zimatchedwa VPR ndipo adawonetsa momwe angagwiritsire ntchito kuchotsa zidziwitso zofunika kuchokera ku database kupita ku cell sheet. Tidanenanso kuti pali mitundu iwiri yogwiritsira ntchito ntchitoyi VPR ndipo ndi imodzi yokha yomwe imayankha mafunso a database. M'nkhaniyi, muphunzira njira ina yocheperako yogwiritsira ntchito ntchitoyi VPR mu Excel.
Ngati simunachite izi, onetsetsani kuti mwawerenga nkhani yomaliza yokhudza ntchitoyi VPR, chifukwa mfundo zonse zimene zili m’munsizi zikusonyeza kuti mukuzidziwa kale mfundo zimene zafotokozedwa m’nkhani yoyamba ija.
Mukamagwira ntchito ndi database, gwiritsani ntchito VPR chizindikiritso chapadera chimadutsa, chomwe chimagwiritsidwa ntchito kuzindikiritsa zomwe tikufuna kupeza (mwachitsanzo, nambala yamalonda kapena nambala yozindikiritsa kasitomala). Khodi yapaderayi iyenera kupezeka mu database, apo ayi VPR adzanena cholakwika. M'nkhaniyi, tiwona njira iyi yogwiritsira ntchito ntchitoyi VPRpamene id palibe mu database konse. Monga ntchito VPR sinthani ku mawonekedwe oyerekeza, ndikusankha deta yomwe ingatipatse tikafuna kupeza china chake. Nthawi zina, izi ndizomwe zimafunikira.
Chitsanzo cha moyo. Timayika ntchito
Tiyeni tifotokozere nkhaniyi ndi chitsanzo chenicheni - kuwerengera ma komishoni potengera kuchuluka kwa zogulitsa. Tiyamba ndi njira yosavuta kwambiri, kenako tidzayisokoneza pang'onopang'ono mpaka njira yokhayo yothetsera vutoli ndiyo kugwiritsa ntchito ntchitoyi. VPR. Chochitika choyambirira cha ntchito yathu yopeka ndi motere: ngati wogulitsa akupanga ndalama zoposa $ 30000 pakugulitsa pachaka, ndiye kuti ntchito yake ndi 30%. Apo ayi, Commission ndi 20% yokha. Tiyeni tiyike mu mawonekedwe a tebulo:
Wogulitsa amalowetsa zomwe amagulitsa mu cell B1, ndipo fomula mu cell B2 imatsimikizira mulingo woyenera womwe wogulitsa angayembekezere. Zotsatira zake, mulingo wotsatirawu umagwiritsidwa ntchito mu cell B3 kuwerengera kuchuluka kwa ntchito yomwe wogulitsa ayenera kulandira (kungochulukitsa maselo B1 ndi B2).
Gawo losangalatsa kwambiri la tebulo lili mu cell B2 - iyi ndiye njira yodziwira kuchuluka kwa Commission. Fomula ili ndi ntchito ya Excel yotchedwa IF (IF). Kwa owerenga omwe sadziwa bwino ntchitoyi, ndikufotokozera momwe imagwirira ntchito:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Ulili ndi mkangano wantchito womwe umatenga mtengo wa chilichonse KODI YOONA (ZOONA), kapena ZONYENGA (ZABODZA). Mu chitsanzo pamwambapa, mawu akuti B1
Kodi ndizowona kuti B1 ndi yocheperapo kuposa B5?
Kapena munganene mosiyana:
Kodi ndizowona kuti chiwerengero chonse cha malonda a chaka ndi chocheperapo kusiyana ndi mtengo?
Ngati tiyankha funso ili INDE (ZOONA), ndiye ntchitoyo imabwerera mtengo ngati zoona (mtengo ngati TRUE). Kwa ife, izi zidzakhala mtengo wa cell B6, mwachitsanzo, kuchuluka kwa ntchito pamene malonda onse ali pansi pa malire. Ngati tiyankha funso Ayi (ZABODZA) kenako amabwerera mtengo ngati zabodza (mtengo ngati ZABODZA). Kwa ife, uwu ndi mtengo wa cell B7, mwachitsanzo, kuchuluka kwa ntchito pamene kugulitsa kwathunthu kuli pamwamba pa malire.
Monga mukuonera, ngati titenga malonda onse a $ 20000, timapeza 2% commission rate mu cell B20. Tikayika mtengo wa $40000, ndiye kuti kuchuluka kwa Commission kudzasintha ndi 30%:
Umu ndi momwe tebulo lathu limagwirira ntchito.
Timasokoneza ntchitoyi
Tiyeni tipangitse zinthu kukhala zovuta. Tiyeni tiyikenso malo ena: ngati wogulitsa apeza ndalama zoposa $40000, ndiye kuti chiwongola dzanja chimakwera mpaka 40%:
Chilichonse chikuwoneka chosavuta komanso chomveka, koma mawonekedwe athu mu cell B2 amakhala ovuta kwambiri. Ngati muyang'anitsitsa ndondomekoyi, mudzawona kuti mkangano wachitatu wa ntchitoyo IF (IF) idasandulika kukhala ntchito ina yokwanira IF (IF). Kumanga kumeneku kumatchedwa nesting of functions in each other. Excel imalola kuti izi zipangidwe, ndipo zimagwiranso ntchito, koma zimakhala zovuta kuwerenga ndikumvetsetsa.
Sitidzafufuza zambiri zaukadaulo - chifukwa chake komanso momwe zimagwirira ntchito, ndipo sitingalowe muzolemba zolembera zisa. Kupatula apo, iyi ndi nkhani yoperekedwa ku ntchitoyi VPR, osati chiwongolero chonse cha Excel.
Mulimonse momwe zingakhalire, ndondomekoyi imakhala yovuta kwambiri! Nanga bwanji ngati tiyambitsa njira ina yopangira 50% kwa ogulitsa omwe amapanga ndalama zoposa $50000 pakugulitsa. Ndipo ngati wina wagulitsa zoposa $60000, kodi adzalipira 60% commission?
Tsopano formula mu cell B2, ngakhale idalembedwa popanda zolakwika, yakhala yosawerengeka. Ndikuganiza kuti ndi ochepa omwe akufuna kugwiritsa ntchito mafomu okhala ndi magawo 4 a zisa pamapulojekiti awo. Payenera kukhala njira yosavuta?!
Ndipo pali njira yotere! Ntchitoyi idzatithandiza VPR.
Timagwiritsa ntchito VLOOKUP kuti tithetse vutoli
Tiyeni tisinthe mapangidwe a tebulo lathu pang'ono. Tidzasunga magawo onse ndi deta, koma tizikonza mwanjira yatsopano, yophatikizika:
Tengani kamphindi ndikuwonetsetsa tebulo latsopano Mtengo Table imaphatikizapo deta yofanana ndi tebulo lakale lakale.
Lingaliro lalikulu ndikugwiritsa ntchito ntchitoyi VPR kuti mudziwe mtengo wamtengo wapatali womwe mukufuna malinga ndi tebulo Mtengo Table kutengera kuchuluka kwa malonda. Chonde dziwani kuti wogulitsa akhoza kugulitsa katundu ndi ndalama zomwe sizili zofanana ndi chimodzi mwa magawo asanu omwe ali patebulo. Mwachitsanzo, amatha kugulitsa $34988, koma palibe ndalama zotere. Tiyeni tiwone momwe ntchitoyo VPR akhoza kuthana ndi vuto ngati limeneli.
Kuyika ntchito ya VLOOKUP
Sankhani cell B2 (komwe tikufuna kuyika chilinganizo chathu) ndikupeza VLOOKUP (VLOOKUP) mu Excel Functions Library: Mafomu (mafomu) > Ntchito Laibulale (Laibulale Yantchito) > Kufufuza & Kutchula (Maumboni ndi magulu).
Bokosi la zokambirana likuwonekera Ntchito Zokangana (Zotsutsana za ntchito). Timadzaza mfundo za mikangano imodzi ndi imodzi, kuyambira Lookup_value (Lookup_value). Mu chitsanzo ichi, ichi ndi chiwerengero chonse cha malonda kuchokera ku selo B1. Ikani cholozera m'munda Lookup_value (Lookup_value) ndikusankha cell B1.
Kenako, muyenera kufotokoza ntchito VPRkomwe mungayang'ane deta. Mu chitsanzo chathu, ili ndi tebulo Mtengo Table. Ikani cholozera m'munda Table_array (Table) ndikusankha tebulo lonse Mtengo Tablekupatula mitu.
Kenako, tiyenera kufotokoza ndime yomwe tingachotsere deta pogwiritsa ntchito fomula yathu. Tili ndi chidwi ndi kuchuluka kwa Commission, komwe kuli mugawo lachiwiri la tebulo. Chifukwa chake, kwa mkangano Col_index_nambala (Column_nambala) lowetsani mtengo 2.
Ndipo pomaliza, tikuyambitsa mkangano womaliza - Range_kuyang'ana (Interval_lookup).
zofunika: ndiko kugwiritsa ntchito mkanganowu komwe kumapangitsa kusiyana pakati pa njira ziwiri zogwiritsira ntchito ntchitoyi VPR. Pamene mukugwira ntchito ndi databases, mkangano Range_kuyang'ana (range_lookup) iyenera kukhala ndi mtengo nthawi zonse ZONYENGA (FALSE) kuti mufufuze zofananira ndendende. Mukugwiritsa ntchito kwathu VPR, tiyenera kusiya malowa opanda kanthu, kapena kuyika mtengo KODI YOONA (ZOONA). Ndikofunikira kwambiri kusankha njira iyi molondola.
Kuti zimveke bwino, tidzayambitsa KODI YOONA (ZOONA) m'munda Range_kuyang'ana (Interval_lookup). Ngakhale, ngati mutasiya munda wopanda kanthu, izi sizingakhale zolakwika, popeza KODI YOONA mtengo wake wokhazikika:
Tadzaza magawo onse. Tsopano ife tikanikiza OK, ndipo Excel imatipangira fomula yokhala ndi ntchito VPR.
Ngati tiyesa mitundu ingapo pazambiri zonse zogulitsa, ndiye kuti tidzaonetsetsa kuti fomulayo imagwira ntchito moyenera.
Kutsiliza
Pamene ntchito VPR imagwira ntchito ndi databases, kukangana Range_kuyang'ana (range_lookup) ikuyenera kuvomereza ZONYENGA (ZABODZA). Ndipo mtengo unalowa ngati Lookup_value (Lookup_value) iyenera kukhalapo pankhokwe. Mwa kuyankhula kwina, ikuyang'ana yofanana ndendende.
Muchitsanzo chomwe tawona m'nkhaniyi, palibe chifukwa chofuna kufanana kwenikweni. Izi ndizochitika pamene ntchito VPR ikuyenera kusinthira kumachitidwe oyerekeza kuti mubweze zotsatira zomwe mukufuna.
Mwachitsanzo: Tikufuna kudziwa mtengo woti tigwiritse ntchito pakuwerengera komisheni kwa wogulitsa yemwe ali ndi ndalama zogulitsa $34988. Ntchito VPR imatibwezera mtengo wa 30%, zomwe zili zolondola. Koma nchifukwa ninji chilinganizocho chinasankha mzere womwe uli ndi 30% ndendende osati 20% kapena 40%? Kodi kusaka pafupifupi kumatanthauza chiyani? Tiyeni timveke bwino.
Pamene mkangano Range_kuyang'ana (interval_lookup) ili ndi mtengo KODI YOONA (ZOONA) kapena zasiyidwa, ntchito VPR imabwerezanso ndime yoyamba ndikusankha mtengo waukulu kwambiri womwe sudutsa mtengo wowonera.
Mfundo yofunikira: Kuti dongosololi ligwire ntchito, gawo loyamba la tebulo liyenera kusanjidwa mokwera.