Nilalaman
Inilaan namin kamakailan ang isang artikulo sa isa sa mga pinakakapaki-pakinabang na function ng Excel na tinatawag VPR at ipinakita kung paano ito magagamit upang kunin ang kinakailangang impormasyon mula sa isang database papunta sa isang worksheet cell. Nabanggit din namin na mayroong dalawang kaso ng paggamit para sa function VPR at isa lamang sa mga ito ang tumatalakay sa mga query sa database. Sa artikulong ito, matututo ka ng isa pang hindi gaanong kilalang paraan ng paggamit ng function VPR sa Excel.
Kung hindi mo pa ito nagagawa, siguraduhing basahin ang huling artikulo tungkol sa function VPR, dahil ipinapalagay ng lahat ng impormasyon sa ibaba na pamilyar ka na sa mga prinsipyong inilarawan sa unang artikulo.
Kapag nagtatrabaho sa mga database, mga function VPR ipinasa ang isang natatanging identifier, na ginagamit upang tukuyin ang impormasyong gusto naming hanapin (halimbawa, isang code ng produkto o numero ng pagkakakilanlan ng customer). Ang natatanging code na ito ay dapat na naroroon sa database, kung hindi man VPR mag-uulat ng error. Sa artikulong ito, titingnan natin ang ganitong paraan ng paggamit ng function VPRkapag ang id ay wala sa database. Parang ang function VPR lumipat sa approximate mode, at pinipili kung anong data ang ibibigay sa amin kapag may gusto kaming mahanap. Sa ilang mga pangyayari, ito mismo ang kailangan.
Isang halimbawa mula sa buhay. Itinakda namin ang gawain
Ilarawan natin ang artikulong ito gamit ang totoong buhay na halimbawa – pagkalkula ng mga komisyon batay sa malawak na hanay ng mga sukatan ng benta. Magsisimula tayo sa isang napaka-simpleng opsyon, at pagkatapos ay unti-unti nating gagawing kumplikado hanggang sa ang tanging makatwirang solusyon sa problema ay ang paggamit ng function. VPR. Ang unang senaryo para sa aming gawa-gawang gawain ay ang mga sumusunod: kung ang isang salesperson ay kumita ng higit sa $30000 sa mga benta sa isang taon, ang kanyang komisyon ay 30%. Kung hindi, ang komisyon ay 20% lamang. Ilagay natin ito sa anyo ng isang talahanayan:
Ilalagay ng nagbebenta ang kanilang data sa pagbebenta sa cell B1, at tinutukoy ng formula sa cell B2 ang tamang rate ng komisyon na maaaring asahan ng nagbebenta. Sa turn, ang resultang rate ay ginagamit sa cell B3 upang kalkulahin ang kabuuang komisyon na dapat matanggap ng nagbebenta (pa-multiply lang ng mga cell B1 at B2).
Ang pinaka-kagiliw-giliw na bahagi ng talahanayan ay nakapaloob sa cell B2 - ito ang formula para sa pagtukoy ng rate ng komisyon. Ang formula na ito ay naglalaman ng isang Excel function na tinatawag IF (KUNG). Para sa mga mambabasa na hindi pamilyar sa function na ito, ipapaliwanag ko kung paano ito gumagana:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Voorwaarde ay isang function argument na kumukuha ng halaga ng alinman TUNAY NA CODE (TAMA), o FALSE (MALI). Sa halimbawa sa itaas, ang expression na B1
Totoo ba na ang B1 ay mas mababa sa B5?
O maaari mong sabihin ito nang iba:
Totoo ba na ang kabuuang halaga ng mga benta para sa taon ay mas mababa kaysa sa halaga ng threshold?
Kung sasagutin natin ang tanong na ito OO (TRUE), pagkatapos ay bumalik ang function halaga kung totoo (halaga kung TOTOO). Sa aming kaso, ito ang magiging halaga ng cell B6, ibig sabihin, rate ng komisyon kapag ang kabuuang benta ay mas mababa sa threshold. Kung sasagutin natin ang tanong HINDI (MALI) pagkatapos ay bumalik halaga kung mali (halaga kung MALI). Sa aming kaso, ito ang halaga ng cell B7, ibig sabihin, rate ng komisyon kapag ang kabuuang benta ay nasa itaas ng threshold.
Gaya ng nakikita mo, kung kukunin natin ang kabuuang benta na $20000, makakakuha tayo ng 2% na rate ng komisyon sa cell B20. Kung maglalagay kami ng halagang $40000, magbabago ang rate ng komisyon ng 30%:
Ganito gumagana ang table namin.
Ginagawa naming kumplikado ang gawain
Gawin nating mas mahirap ang mga bagay. Magtakda tayo ng isa pang threshold: kung kumikita ang nagbebenta ng higit sa $40000, tataas ang rate ng komisyon sa 40%:
Ang lahat ay tila simple at malinaw, ngunit ang aming formula sa cell B2 ay nagiging kapansin-pansing mas kumplikado. Kung titingnan mong mabuti ang formula, makikita mo na ang ikatlong argumento ng function IF (IF) naging isa pang ganap na function IF (KUNG). Ang konstruksiyon na ito ay tinatawag na nesting ng mga function sa bawat isa. Masayang pinapayagan ng Excel ang mga konstruksyon na ito, at gumagana pa nga ang mga ito, ngunit mas mahirap basahin at unawain ang mga ito.
Hindi namin susuriin ang mga teknikal na detalye - bakit at paano ito gumagana, at hindi kami pupunta sa mga nuances ng pagsulat ng mga nested function. Pagkatapos ng lahat, ito ay isang artikulo na nakatuon sa pag-andar VPR, hindi isang kumpletong gabay sa Excel.
Anuman ang kaso, ang formula ay nagiging mas kumplikado! Paano kung magpakilala kami ng isa pang opsyon para sa rate ng komisyon na 50% para sa mga nagbebenta na kumikita ng higit sa $50000 sa mga benta. At kung may nagbenta ng higit sa $60000, magbabayad ba sila ng 60% na komisyon?
Ngayon ang formula sa cell B2, kahit na isinulat ito nang walang mga error, ay naging ganap na hindi nababasa. Sa tingin ko, kakaunti ang gustong gumamit ng mga formula na may 4 na antas ng nesting sa kanilang mga proyekto. Dapat may mas madaling paraan?!
At may ganoong paraan! Ang function ay makakatulong sa amin VPR.
Inilapat namin ang VLOOKUP function upang malutas ang problema
Baguhin natin ng kaunti ang disenyo ng ating mesa. Pananatilihin namin ang lahat ng parehong field at data, ngunit ayusin ang mga ito sa isang bago, mas compact na paraan:
Maglaan ng ilang sandali at siguraduhin na ang bagong talahanayan Talaan ng Rate kasama ang parehong data tulad ng nakaraang talahanayan ng threshold.
Ang pangunahing ideya ay ang paggamit ng function VPR upang matukoy ang nais na rate ng taripa ayon sa talahanayan Talaan ng Rate depende sa dami ng benta. Pakitandaan na ang nagbebenta ay maaaring magbenta ng mga kalakal sa halagang hindi katumbas ng isa sa limang threshold sa talahanayan. Halimbawa, maaari siyang magbenta ng $34988, ngunit walang ganoong halaga. Tingnan natin kung paano ang function VPR kayang harapin ang ganitong sitwasyon.
Paglalagay ng VLOOKUP function
Piliin ang cell B2 (kung saan namin gustong ipasok ang aming formula) at hanapin VLOOKUP (VLOOKUP) sa Excel Functions Library: Formula (mga formula) > Function Library (Function Library) > Paghanap at Sanggunian (Mga sanggunian at array).
May lalabas na dialog box Pag-andar ng Mga Argumento (Mga argumento ng function). Pinupuno namin ang mga halaga ng mga argumento nang paisa-isa, simula sa Lookup_value (Lookup_value). Sa halimbawang ito, ito ang kabuuang halaga ng mga benta mula sa cell B1. Ilagay ang cursor sa field Lookup_value (Lookup_value) at piliin ang cell B1.
Susunod, kailangan mong tukuyin ang mga pag-andar VPRkung saan maghahanap ng data. Sa aming halimbawa, ito ay isang talahanayan Talaan ng Rate. Ilagay ang cursor sa field Table_array (Talahanayan) at piliin ang buong talahanayan Talaan ng Ratemaliban sa mga header.
Susunod, kailangan naming tukuyin kung aling column ang kukuha ng data mula sa paggamit ng aming formula. Interesado kami sa rate ng komisyon, na nasa ikalawang hanay ng talahanayan. Samakatuwid, para sa argumento Col_index_num (Column_number) ilagay ang value 2.
At sa wakas, ipinakilala namin ang huling argumento - Range_lookup (Interval_lookup).
Mahalaga: ang paggamit ng argumentong ito ang gumagawa ng pagkakaiba sa pagitan ng dalawang paraan ng paglalapat ng function VPR. Kapag nagtatrabaho sa mga database, ang argument Range_lookup (range_lookup) ay dapat palaging may halaga FALSE (FALSE) para maghanap ng eksaktong tugma. Sa aming paggamit ng function VPR, dapat nating iwanang blangko ang field na ito, o maglagay ng value TUNAY NA CODE (TAMA). Napakahalaga na piliin nang tama ang pagpipiliang ito.
Para mas malinaw, ipapakilala namin TUNAY NA CODE (TAMA) sa field Range_lookup (Interval_lookup). Bagaman, kung iiwan mong blangko ang field, hindi ito magiging error, dahil TUNAY NA CODE ay ang default na halaga nito:
Napunan namin ang lahat ng mga parameter. Ngayon pinindot namin OK, at gumagawa ang Excel ng formula para sa amin na may function VPR.
Kung mag-eksperimento kami sa maraming iba't ibang mga halaga para sa kabuuang halaga ng mga benta, pagkatapos ay titiyakin namin na ang formula ay gumagana nang tama.
Konklusyon
Kapag ang function VPR gumagana sa mga database, argumento Range_lookup (range_lookup) ay dapat tanggapin FALSE (MALI). At ang halaga ay ipinasok bilang Lookup_value (Lookup_value) ay dapat na umiiral sa database. Sa madaling salita, naghahanap ito ng eksaktong tugma.
Sa halimbawang tiningnan namin sa artikulong ito, hindi na kailangang makakuha ng eksaktong tugma. Ito ang kaso kapag ang function VPR dapat lumipat sa approximate mode upang maibalik ang nais na resulta.
Halimbawa: Gusto naming tukuyin kung anong rate ang gagamitin sa pagkalkula ng komisyon para sa isang salesperson na may dami ng benta na $34988. Function VPR nagbabalik sa amin ng halagang 30%, na talagang tama. Ngunit bakit pinili ng formula ang hilera na naglalaman ng eksaktong 30% at hindi 20% o 40%? Ano ang ibig sabihin ng tinatayang paghahanap? Linawin natin.
Kapag ang argumento Range_lookup (interval_lookup) ay may halaga TUNAY NA CODE (TRUE) o inalis, function VPR umuulit sa unang column at pinipili ang pinakamalaking value na hindi lalampas sa lookup value.
Mahalagang punto: Para gumana ang scheme na ito, dapat na ayusin ang unang column ng talahanayan sa pataas na pagkakasunud-sunod.