Yaliyomo
Hivi majuzi tuliweka nakala kwa moja ya vitendaji muhimu vya Excel vinavyoitwa VPR na ilionyesha jinsi inavyoweza kutumika kutoa maelezo yanayohitajika kutoka kwa hifadhidata hadi kwenye kisanduku cha lahakazi. Pia tulitaja kuwa kuna kesi mbili za utumiaji wa chaguo la kukokotoa VPR na ni moja tu kati yao inayohusika na maswali ya hifadhidata. Katika makala hii, utajifunza njia nyingine isiyojulikana ya kutumia kazi VPR katika Excel.
Ikiwa bado haujafanya hivyo, basi hakikisha kusoma makala ya mwisho kuhusu kazi VPR, kwa sababu habari zote hapa chini zinadhani kuwa tayari unajua kanuni zilizoelezwa katika makala ya kwanza.
Wakati wa kufanya kazi na hifadhidata, kazi VPR kitambulisho cha kipekee kinapitishwa, ambacho hutumika kutambua maelezo tunayotaka kupata (kwa mfano, msimbo wa bidhaa au nambari ya kitambulisho cha mteja). Nambari hii ya kipekee lazima iwepo kwenye hifadhidata, vinginevyo VPR itaripoti hitilafu. Katika makala hii, tutaangalia njia hii ya kutumia kazi VPRwakati kitambulisho hakipo kwenye hifadhidata hata kidogo. Kama utendaji VPR imebadilishwa hadi hali ya kukadiria, na huchagua data ya kutupa tunapotaka kupata kitu. Katika hali fulani, hii ndiyo hasa inahitajika.
Mfano kutoka kwa maisha. Tunaweka kazi
Hebu tuonyeshe makala haya kwa mfano halisi - kukokotoa kamisheni kulingana na anuwai ya vipimo vya mauzo. Tutaanza na chaguo rahisi sana, na kisha tutaichanganya polepole hadi suluhisho pekee la busara la shida ni kutumia kazi. VPR. Hali ya awali ya kazi yetu ya uwongo ni kama ifuatavyo: ikiwa muuzaji atapata mauzo zaidi ya $ 30000 kwa mwaka, basi tume yake ni 30%. Vinginevyo, tume ni 20% tu. Wacha tuiweke kwa namna ya meza:
Muuzaji huingiza data yake ya mauzo katika seli B1, na fomula katika seli B2 huamua kiwango cha kamisheni sahihi ambacho muuzaji anaweza kutarajia. Kwa upande wake, kiwango kinachotokana kinatumika katika seli B3 kuhesabu jumla ya tume ambayo muuzaji anapaswa kupokea (kuzidisha seli B1 na B2).
Sehemu ya kuvutia zaidi ya meza iko katika kiini B2 - hii ndiyo fomula ya kuamua kiwango cha tume. Fomula hii ina kitendakazi cha Excel kinachoitwa IF (KAMA). Kwa wale wasomaji ambao hawajui kazi hii, nitaelezea jinsi inavyofanya kazi:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Hali ni hoja ya utendakazi ambayo inachukua thamani ya mojawapo KANUNI YA KWELI (KWELI), au UONGO (UONGO). Katika mfano hapo juu, usemi B1
Je, ni kweli kwamba B1 ni chini ya B5?
Au unaweza kusema tofauti:
Je, ni kweli kwamba jumla ya mauzo kwa mwaka ni chini ya thamani ya kizingiti?
Ikiwa tutajibu swali hili YES (TRUE), kisha chaguo la kukokotoa linarudi thamani kama kweli (thamani kama TRUE). Kwa upande wetu, hii itakuwa thamani ya seli B6, yaani kiwango cha kamisheni wakati mauzo ya jumla yapo chini ya kizingiti. Ikiwa tutajibu swali HAPANA (FALSE) kisha inarudi thamani kama si kweli (thamani kama FALSE). Kwa upande wetu, hii ni thamani ya seli B7, yaani kiwango cha kamisheni wakati mauzo ya jumla yanazidi kiwango cha juu.
Kama unavyoona, tukichukua jumla ya mauzo ya $20000, tunapata kiwango cha kamisheni cha 2% katika seli B20. Ikiwa tutaweka thamani ya $40000, basi kiwango cha kamisheni kitabadilika kwa 30%:
Hivi ndivyo meza yetu inavyofanya kazi.
Tunachanganya kazi
Wacha tufanye mambo kuwa magumu zaidi. Wacha tuweke kizingiti kingine: ikiwa muuzaji atapata zaidi ya $40000, basi kiwango cha kamisheni kinaongezeka hadi 40%:
Kila kitu kinaonekana kuwa rahisi na wazi, lakini fomula yetu katika seli B2 inakuwa ngumu zaidi. Ukiangalia kwa karibu formula, utaona kwamba hoja ya tatu ya kazi IF (IF) iligeuzwa kuwa kazi nyingine kamili IF (KAMA). Ujenzi huu unaitwa nesting ya kazi ndani ya kila mmoja. Excel inaruhusu kwa furaha miundo hii, na hata inafanya kazi, lakini ni ngumu zaidi kusoma na kuelewa.
Hatutaingia katika maelezo ya kiufundi - kwa nini na jinsi inavyofanya kazi, na hatutaingia katika nuances ya kuandika kazi zilizowekwa. Baada ya yote, hii ni makala iliyotolewa kwa kazi VPR, sio mwongozo kamili wa Excel.
Kwa hali yoyote, formula inakuwa ngumu zaidi! Je, ikiwa tutaanzisha chaguo jingine kwa kiwango cha kamisheni cha 50% kwa wauzaji hao wanaopata mauzo zaidi ya $50000. Na ikiwa mtu ameuza zaidi ya $ 60000, atalipa kamisheni 60%?
Sasa fomula katika seli B2, hata ikiwa iliandikwa bila makosa, imekuwa isiyoweza kusomeka kabisa. Nadhani ni wachache wanaotaka kutumia fomula zilizo na viwango 4 vya kuweka viota katika miradi yao. Lazima kuwe na njia rahisi?!
Na kuna njia kama hiyo! Kazi itatusaidia VPR.
Tunatumia kitendakazi cha VLOOKUP ili kutatua tatizo
Wacha tubadilishe muundo wa meza yetu kidogo. Tutaweka sehemu na data zote sawa, lakini tuzipange kwa njia mpya, iliyoshikana zaidi:
Chukua muda na uhakikishe kuwa kuna meza mpya Jedwali la Viwango inajumuisha data sawa na jedwali la awali la kizingiti.
Wazo kuu ni kutumia kitendakazi VPR kuamua kiwango cha ushuru kinachohitajika kulingana na jedwali Jedwali la Viwango kulingana na kiasi cha mauzo. Tafadhali kumbuka kuwa muuzaji anaweza kuuza bidhaa kwa kiasi ambacho si sawa na moja ya vizingiti vitano kwenye jedwali. Kwa mfano, angeweza kuuza kwa $34988, lakini hakuna kiasi hicho. Hebu tuone jinsi kazi VPR inaweza kukabiliana na hali kama hiyo.
Inaingiza kitendakazi cha VLOOKUP
Chagua kiini B2 (ambapo tunataka kuingiza fomula yetu) na upate VLOOKUP (VLOOKUP) katika Maktaba ya Kazi za Excel: Aina (fomula) > Maktaba ya Kazi (Maktaba ya kazi) > Kutafuta & Rejea (Marejeleo na safu).
Sanduku la mazungumzo linaonekana Hoja za Kazi (Hoja za kazi). Tunajaza maadili ya hoja moja baada ya nyingine, kuanzia na Tafuta_thamani (Thamani_ya_kutafuta). Katika mfano huu, hii ni jumla ya kiasi cha mauzo kutoka kwa seli B1. Weka mshale kwenye shamba Tafuta_thamani (Lookup_value) na uchague kisanduku B1.
Ifuatayo, unahitaji kutaja kazi VPRwapi kutafuta data. Katika mfano wetu, hii ni meza Jedwali la Viwango. Weka mshale kwenye shamba Jedwali_safu (Jedwali) na uchague meza nzima Jedwali la Viwangoisipokuwa kwa vichwa.
Ifuatayo, tunahitaji kubainisha safu wima gani ya kutoa data kutoka kwa kutumia fomula yetu. Tunavutiwa na kiwango cha tume, kilicho kwenye safu ya pili ya meza. Kwa hivyo, kwa hoja Nambari_ya_kielezo (Nambari_ya_safu) weka thamani 2.
Na hatimaye, tunatanguliza hoja ya mwisho - Utafutaji_wa_mbalimbali (Utaftaji_wa_muda).
Muhimu: ni matumizi ya hoja hii ambayo hufanya tofauti kati ya njia mbili za kutumia kazi VPR. Wakati wa kufanya kazi na hifadhidata, hoja Utafutaji_wa_mbalimbali (range_lookup) lazima iwe na thamani kila wakati UONGO (FALSE) kutafuta inayolingana kabisa. Katika matumizi yetu ya kazi VPR, lazima tuache uga huu wazi, au tuweke thamani KANUNI YA KWELI (KWELI). Ni muhimu sana kuchagua chaguo hili kwa usahihi.
Ili kuifanya iwe wazi zaidi, tutaanzisha KANUNI YA KWELI (KWELI) uwanjani Utafutaji_wa_mbalimbali (Utaftaji_wa_muda). Ingawa, ukiacha shamba tupu, hii haitakuwa kosa, kwani KANUNI YA KWELI ni thamani yake chaguomsingi:
Tumejaza vigezo vyote. Sasa tunasisitiza OK, na Excel hutuundia fomula yenye chaguo za kukokotoa VPR.
Ikiwa tutajaribu na maadili kadhaa tofauti kwa jumla ya mauzo, basi tutahakikisha kuwa fomula inafanya kazi kwa usahihi.
Hitimisho
Wakati kazi VPR inafanya kazi na hifadhidata, hoja Utafutaji_wa_mbalimbali (utafutaji_wa_masafa) lazima ukubali UONGO (UONGO). Na thamani iliingia kama Tafuta_thamani (Lookup_value) lazima iwepo kwenye hifadhidata. Kwa maneno mengine, inatafuta mechi kamili.
Katika mfano ambao tumeangalia katika makala hii, hakuna haja ya kupata mechi halisi. Hii ndio kesi wakati utendaji VPR lazima ubadili hadi modi ya kukadiria ili kurudisha matokeo unayotaka.
Kwa mfano: Tunataka kubainisha ni kiwango gani cha kutumia katika hesabu ya tume kwa muuzaji aliye na kiasi cha mauzo cha $34988. Kazi VPR inaturudishia thamani ya 30%, ambayo ni sahihi kabisa. Lakini kwa nini fomula ilichagua safu iliyo na 30% haswa na sio 20% au 40%? Nini maana ya takriban utafutaji? Hebu tuwe wazi.
Wakati hoja Utafutaji_wa_mbalimbali (interval_lookup) ina thamani KANUNI YA KWELI (TRUE) au imeachwa, kazi VPR inarudia kupitia safu wima ya kwanza na kuchagua thamani kubwa zaidi ambayo haizidi thamani ya utafutaji.
Jambo muhimu: Ili mpango huu ufanye kazi, safu ya kwanza ya jedwali lazima ipangwe kwa mpangilio wa kupanda.