Clár ábhair
Le déanaí rinneamar alt a thiomnú do cheann de na feidhmeanna Excel is úsáidí ar a dtugtar VPR agus thaispeáin sé conas is féidir é a úsáid chun an fhaisnéis riachtanach a bhaint as bunachar sonraí isteach i gcill bileog oibre. Luaíomar freisin go bhfuil dhá chás úsáide ann don fheidhm VPR agus ní dhéileálann ach ceann amháin acu le fiosruithe bunachar sonraí. San Airteagal seo, beidh tú ag foghlaim bealach eile nach bhfuil chomh aitheanta sin chun an fheidhm a úsáid VPR i Excel.
Mura bhfuil sé seo déanta agat go fóill, ansin bí cinnte an t-alt deiridh a léamh faoin bhfeidhm VPR, toisc go nglacann an fhaisnéis go léir thíos leis go bhfuil tú eolach cheana féin ar na prionsabail a thuairiscítear sa chéad alt.
Nuair a bhíonn tú ag obair le bunachair shonraí, feidhmeanna VPR cuirtear aitheantóir uathúil ar aghaidh, a úsáidtear chun an fhaisnéis a theastaíonn uainn a aimsiú a shainaithint (mar shampla, cód táirge nó uimhir aitheantais custaiméara). Caithfidh an cód uathúil seo a bheith i láthair sa bhunachar sonraí, mura ndéantar é VPR tuairisceoidh sé earráid. San Airteagal seo, féachfaimid ar an mbealach seo chun an fheidhm a úsáid VPRnuair nach bhfuil an t-aitheantas sa bhunachar sonraí ar chor ar bith. Amhail is dá mba an fheidhm VPR aistrigh go dtí an modh gar, agus roghnaíonn sé cad iad na sonraí a chur ar fáil dúinn nuair is mian linn rud éigin a aimsiú. I gcúinsí áirithe, is é seo go díreach atá ag teastáil.
Sampla ón saol. Leagamar an tasc
Léirímid an t-alt seo le sampla ón bhfíorshaol - ag ríomh coimisiúin bunaithe ar raon leathan méadracht díolacháin. Tosóimid le rogha an-simplí, agus ansin déanfaimid casta air de réir a chéile go dtí gurb é an t-aon réiteach réasúnach ar an bhfadhb ná an fheidhm a úsáid VPR. Is é seo a leanas an cás tosaigh dár dtasc bréige: má dhéanann díoltóir díolacháin níos mó ná $30000 i mbliain, is é a choimisiún ná 30%. Seachas sin, níl an coimisiún ach 20%. Cuirimis é i bhfoirm tábla:
Cuireann an díoltóir a gcuid sonraí díolacháin isteach i gcill B1, agus cinneann an fhoirmle i gcill B2 an ráta coimisiúin ceart is féidir leis an díoltóir a bheith ag súil leis. Ina dhiaidh sin, úsáidtear an ráta mar thoradh air sin i gcill B3 chun an coimisiún iomlán ba cheart don díoltóir a fháil a ríomh (ní iolrú ach cealla B1 agus B2).
Tá an chuid is suimiúla den tábla i gcill B2 – seo an fhoirmle chun an ráta coimisiúin a chinneadh. Tá feidhm Excel ar a dtugtar an fhoirmle seo IF (DÁ). Do na léitheoirí sin nach bhfuil eolach ar an bhfeidhm seo, míneoidh mé conas a oibríonn sé:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Coinníoll argóint feidhme a thógann luach ceachtar acu CÓD FÍOR (TRUE), or FALSE (FALSE). Sa sampla thuas, tá an slonn B1
An bhfuil sé fíor go bhfuil B1 níos lú ná B5?
Nó is féidir leat é a rá ar bhealach difriúil:
An bhfuil sé fíor go bhfuil méid iomlán na ndíolachán don bhliain níos lú ná an luach tairsí?
Má fhreagraímid an cheist seo TÁ (TRUE), ansin filleann an fheidhm luach más fíor (luach más TRUE). Is é ár gcás, is é seo luach cille B6, ie ráta coimisiúin nuair a bhíonn díolacháin iomlána faoi bhun na tairsí. Má fhreagraímid an cheist NÍL (FALSE) ar ais ansin luach más bréagach (luach más BRÉAGACH). Is é ár gcás, is é seo luach cille B7, ie ráta coimisiúin nuair a bhíonn díolacháin iomlána os cionn na tairsí.
Mar a fheiceann tú, má thógaimid na díolacháin iomlána de $20000, gheobhaimid ráta coimisiúin 2% i gcill B20. Má chuirimid luach $40000 isteach, athróidh an ráta coimisiúin 30%:
Seo mar a oibríonn ár mbord.
Déanaimid an tasc casta
Déanaimis rudaí beagán níos deacra. Socróimid tairseach eile: má thuilleann an díoltóir níos mó ná $40000, méadóidh an ráta coimisiúin go 40%:
Is cosúil go bhfuil gach rud simplí agus soiléir, ach éiríonn ár bhfoirmle i gcill B2 i bhfad níos casta. Má fhéachann tú go géar ar an bhfoirmle, feicfidh tú go bhfuil an tríú argóint na feidhme IF (IF) iompú isteach i bhfeidhm iomlán eile IF (DÁ). Tugtar neadú feidhmeanna ar a chéile ar an tógáil seo. Ceadaíonn Excel na tógálacha seo go sona sásta, agus oibríonn siad fiú, ach tá siad i bhfad níos deacra a léamh agus a thuiscint.
Ní dhéanfaimid mionscrúdú ar na sonraí teicniúla - cén fáth agus conas a oibríonn sé, agus ní rachaimid isteach sa nuances a bhaineann le feidhmeanna neadaithe a scríobh. Tar éis an tsaoil, is alt é seo atá tiomnaithe don fheidhm VPR, ní treoir iomlán ar Excel.
Cibé an cás, éiríonn an fhoirmle níos casta! Cad a tharlóidh má thugaimid isteach rogha eile le haghaidh ráta coimisiúin 50% do na díoltóirí sin a dhéanann níos mó ná $50000 i ndíolacháin. Agus má tá níos mó ná $60000 díolta ag duine, an íocfaidh siad coimisiún 60%?
Anois tá an fhoirmle i gcill B2, fiú má scríobhadh í gan earráidí, tar éis éirí go hiomlán do-léite. Sílim gur beag duine atá ag iarraidh foirmlí le 4 leibhéal neadaithe a úsáid ina dtionscadail. Caithfidh go bhfuil bealach níos éasca ann?!
Agus tá a leithéid de bhealach! Cabhróidh an fheidhm linn VPR.
Cuirimid an fheidhm VLOOKUP i bhfeidhm chun an fhadhb a réiteach
Déanaimis dearadh ár tábla a athrú beagán. Coimeádfaimid na réimsí agus na sonraí céanna go léir, ach socróimid iad ar bhealach nua níos dlúithe:
Tóg nóiméad agus déan cinnte an tábla nua Tábla Rátaí áirítear na sonraí céanna leis an tábla tairsí roimhe seo.
Is é an príomh-smaoineamh ná an fheidhm a úsáid VPR chun an ráta taraife atá ag teastáil a chinneadh de réir an tábla Tábla Rátaí ag brath ar an méid díolacháin. Tabhair faoi deara le do thoil gur féidir leis an díoltóir earraí a dhíol ar mhéid nach bhfuil comhionann le ceann de na cúig thairseach sa tábla. Mar shampla, d'fhéadfadh sé a dhíol ar $34988, ach níl aon méid den sórt sin. A ligean ar a fheiceáil conas an fheidhm VPR is féidir déileáil le cás den sórt sin.
Feidhm VLOOKUP a ionsá
Roghnaigh cill B2 (áit ar mhaith linn ár bhfoirmle a chur isteach) agus aimsigh VLOOKUP (VLOOKUP) sa Leabharlann Feidhmeanna Excel: Foirmlí (foirmlí) > Leabharlann Feidhme (Leabharlann Feidhme) > Cuardach & Tagairt (Tagairtí agus eagair).
Tá bosca dialóige le feiceáil Argóintí Feidhm (Argóintí feidhm). Líonaimid isteach luachanna na n-argóintí ceann ar cheann, ag tosú le Cuardach_luach (Luach_lookup). Sa sampla seo, is é seo méid iomlán na ndíolachán ó chill B1. Cuir an cúrsóir sa réimse Cuardach_luach (Lookup_value) agus roghnaigh cill B1.
Ansin, ní mór duit na feidhmeanna a shonrú VPRcá háit le sonraí a chuardach. In ár sampla, is tábla é seo Tábla Rátaí. Cuir an cúrsóir sa réimse Tábla_eagar (Tábla) agus roghnaigh an tábla ar fad Tábla Rátaíseachas ceanntásca.
Ansin, ní mór dúinn a shonrú cén colún chun sonraí a bhaint as ár bhfoirmle a úsáid. Tá suim againn sa ráta coimisiúin, atá sa dara colún den tábla. Dá bhrí sin, don argóint Col_innéacs_num (Column_number) cuir isteach an luach 2.
Agus ar deireadh, tugaimid isteach an argóint dheireanach - Raon_lookup (Interval_lookup).
Tábhachtach: is í úsáid na hargóinte seo a dhéanann an difríocht idir an dá bhealach chun an fheidhm a chur i bhfeidhm VPR. Nuair a bheidh ag obair le bunachair shonraí, an argóint Raon_lookup (range_lookup) ní mór go mbeadh luach i gcónaí FALSE (FALSE) chun meaitseáil bheacht a lorg. In ár n-úsáid na feidhme VPR, ní mór dúinn an réimse seo a fhágáil bán, nó cuir isteach luach CÓD FÍOR (TRUE). Tá sé thar a bheith tábhachtach an rogha seo a roghnú i gceart.
Chun é a dhéanamh níos soiléire, tabharfaimid isteach CÓD FÍOR (TRUE) sa réimse Raon_lookup (Interval_lookup). Cé, má fhágann tú an réimse bán, ní bheidh sé seo earráid, ós rud é CÓD FÍOR is é a luach réamhshocraithe:
Tá na paraiméadair go léir líonta againn. Anois brúigh muid OK, agus cruthaíonn Excel foirmle dúinn le feidhm VPR.
Má dhéanaimid turgnamh le roinnt luachanna difriúla don mhéid díolacháin iomlán, ansin déanfaimid cinnte go n-oibríonn an fhoirmle i gceart.
Conclúid
Nuair a bheidh an fheidhm VPR oibríonn sé le bunachair shonraí, argóint Raon_lookup Ní mór (range_lookup) glacadh leis FALSE (FALSE). Agus an luach isteach mar Cuardach_luach Ní mór (Lookup_value) a bheith sa bhunachar sonraí. I bhfocail eile, tá cluiche cruinn á lorg aige.
Sa sampla ar bhreathnaigh muid air san Airteagal seo, ní gá meaitseáil chruinn a fháil. Is é seo an cás nuair a bheidh an fheidhm VPR Ní mór athrú go mód gar chun an toradh inmhianaithe a thabhairt ar ais.
Mar shampla: Ba mhaith linn a chinneadh cén ráta atá le húsáid i ríomh an choimisiúin do dhíoltóir le méid díolacháin $34988. Feidhm VPR tugann sé luach 30% ar ais dúinn, rud atá fíor-cheart. Ach cén fáth ar roghnaigh an fhoirmle an tsraith ina bhfuil go díreach 30% agus ní 20% nó 40%? Cad is brí le neaschuardach? A ligean ar a bheith soiléir.
Nuair a bheidh an argóint Raon_lookup (interval_lookup) tá luach CÓD FÍOR (TRUE) nó fágtha ar lár, feidhm VPR atarlaíonn sé tríd an gcéad cholún agus roghnaíonn sé an luach is mó nach sáraíonn an luach cuardaigh.
Pointe tábhachtach: Chun go n-oibreoidh an scéim seo, ní mór an chéad cholún den tábla a shórtáil in ord ardaitheach.