contents
Nou fèk dedye yon atik nan youn nan fonksyon Excel ki pi itil yo rele VPR epi li montre kijan li ka itilize pou ekstrè enfòmasyon ki nesesè yo nan yon baz done nan yon selil Fèy travay. Nou te mansyone tou ke gen de ka itilize pou fonksyon an VPR epi sèlman youn nan yo fè fas ak demann baz done. Nan atik sa a, ou pral aprann yon lòt fason mwens li te ye pou itilize fonksyon an VPR nan Excel.
Si ou pa fè sa ankò, Lè sa a, asire w ke ou li atik ki sot pase a sou fonksyon an VPR, paske tout enfòmasyon ki anba yo sipoze ke ou deja abitye ak prensip ki dekri nan premye atik la.
Lè w ap travay ak baz done, fonksyon VPR yo pase yon idantifyan inik, ki itilize pou idantifye enfòmasyon nou vle jwenn (pa egzanp, yon kòd pwodwi oswa yon nimewo idantifikasyon kliyan). Kòd inik sa a dwe prezan nan baz done a, otreman VPR pral rapòte yon erè. Nan atik sa a, nou pral gade fason sa a pou itilize fonksyon an VPRlè id la pa egziste nan baz done a ditou. Kòm si fonksyon an VPR chanje nan mòd apwoksimatif la, epi li chwazi ki done yo bay nou lè nou vle jwenn yon bagay. Nan sèten sikonstans, sa a se egzakteman sa ki nesesè.
Yon egzanp nan lavi. Nou mete travay la
Ann ilistre atik sa a ak yon egzanp nan lavi reyèl - kalkile komisyon ki baze sou yon pakèt mezi lavant. Nou pral kòmanse ak yon opsyon trè senp, ak Lè sa a, nou pral piti piti konplike li jiskaske sèlman solisyon an rasyonèl nan pwoblèm nan se sèvi ak fonksyon an. VPR. Scénario inisyal pou travay fiktiv nou an se jan sa a: si yon vendeur fè plis pase $30000 nan lavant nan yon ane, Lè sa a, komisyon li se 30%. Sinon, komisyon an se sèlman 20%. Ann mete l sou fòm yon tab:
Vandè a antre done lavant yo nan selil B1, ak fòmil la nan selil B2 detèmine pousantaj komisyon kòrèk ke vandè a ka atann. Nan vire, pousantaj ki kapab lakòz yo itilize nan selil B3 pou kalkile komisyon total vandè a ta dwe resevwa (senpleman miltipliye selil B1 ak B2).
Pati ki pi enteresan nan tab la genyen nan selil B2 - sa a se fòmil pou detèmine pousantaj komisyon an. Fòmil sa a gen yon fonksyon Excel rele IF (SI). Pou lektè sa yo ki pa abitye ak fonksyon sa a, mwen pral eksplike kijan li fonksyone:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Kondisyon se yon agiman fonksyon ki pran valè swa VRE KÒD (VRÈ), oswa FO (FO). Nan egzanp ki anwo a, ekspresyon B1
Èske se vre ke B1 se mwens pase B5?
Oswa ou ka di li yon fason diferan:
Èske se vre ke kantite total lavant pou ane a se mwens pase valè papòt la?
Si nou reponn kesyon sa a WI (VRÈ), Lè sa a, fonksyon an retounen valè si vre (valè si VRE). Nan ka nou an, sa a pral valè selil B6, sa vle di pousantaj komisyon lè total lavant yo anba papòt la. Si nou reponn kesyon an NON (FALSE) Lè sa a, retounen valè si fo (valè si FO). Nan ka nou an, sa a se valè selil B7, sa vle di pousantaj komisyon lè total lavant yo pi wo pase papòt la.
Kòm ou ka wè, si nou pran lavant total de $ 20000, nou jwenn yon pousantaj komisyon 2% nan selil B20. Si nou antre yon valè de $40000, Lè sa a, pousantaj komisyon an ap chanje pa 30%:
Men ki jan tab nou an ap travay.
Nou konplike travay la
Ann fè bagay yo yon ti kras pi difisil. Ann fikse yon lòt papòt: si vandè a touche plis pase $40000, Lè sa a, pousantaj komisyon an ogmante a 40%:
Tout bagay sanble senp epi klè, men fòmil nou an nan selil B2 vin pi konplike. Si ou gade ak anpil atansyon nan fòmil la, ou pral wè ke agiman an twazyèm nan fonksyon an IF (SI) tounen yon lòt fonksyon plen véritable IF (SI). Yo rele konstriksyon sa a nidifikasyon nan fonksyon youn nan lòt. Excel san pwoblèm mwen tap pèmèt konstwi sa yo, e yo menm travay, men yo ap pi difisil pou li ak konprann.
Nou pa pral fouye nan detay teknik yo - poukisa ak ki jan li fonksyone, epi nou pa pral antre nan nuans yo nan ekri fonksyon enbrike. Apre yo tout, sa a se yon atik dedye a fonksyon an VPR, pa yon gid konplè sou Excel.
Kèlkeswa ka a, fòmil la vin pi konplike! E si nou prezante yon lòt opsyon pou yon pousantaj komisyon 50% pou vandè sa yo ki fè plis pase $50000 nan lavant yo. Men, si yon moun te vann plis pase $ 60000, yo pral peye 60% komisyon?
Koulye a, fòmil la nan selil B2, menm si li te ekri san erè, te vin konplètman lizib. Mwen panse ke gen kèk ki vle sèvi ak fòmil ak 4 nivo nan nidifikasyon nan pwojè yo. Dwe gen yon fason pi fasil?!
Epi gen yon fason konsa! Fonksyon an ap ede nou VPR.
Nou aplike fonksyon VLOOKUP pou rezoud pwoblèm nan
Ann chanje konsepsyon tab nou an yon ti jan. Nou pral kenbe tout menm jaden yo ak done yo, men fè aranjman yo nan yon nouvo fason ki pi kontra:
Pran yon ti moman epi asire w ke nouvo tab la Tablo pousantaj gen ladan done yo menm jan ak tablo papòt anvan an.
Lide prensipal la se sèvi ak fonksyon an VPR detèmine to tarif yo vle dapre tablo a Tablo pousantaj depann sou volim lavant yo. Tanpri sonje ke vandè a ka vann machandiz pou yon kantite lajan ki pa egal a youn nan senk papòt ki nan tablo a. Pou egzanp, li te kapab vann pou $ 34988, men pa gen okenn kantite lajan sa yo. Ann wè ki jan fonksyon an VPR ka fè fas ak yon sitiyasyon konsa.
Mete yon fonksyon VLOOKUP
Chwazi selil B2 (kote nou vle mete fòmil nou an) epi jwenn VCHACHUP (VLOOKUP) nan Bibliyotèk Fonksyon Excel: Fòmil (fòmil) > Fonksyon Bibliyotèk (Fonksyon Bibliyotèk) > Lookup & Referans (Referans ak etalaj).
Yon bwat dyalòg parèt Fonksyon Agiman (Fonksyon agiman). Nou ranpli valè agiman yo youn pa youn, kòmanse ak Lookup_value (Lookup_value). Nan egzanp sa a, sa a se kantite total lavant ki soti nan selil B1. Mete kurseur a nan jaden an Lookup_value (Lookup_value) epi chwazi selil B1.
Apre sa, ou bezwen presize fonksyon yo VPRki kote pou chèche done. Nan egzanp nou an, sa a se yon tab Tablo pousantaj. Mete kurseur a nan jaden an Table_etalaj (Tablo) epi chwazi tout tab la Tablo pousantajeksepte pou headers.
Apre sa, nou bezwen presize ki kolòn yo ekstrè done nan lè l sèvi avèk fòmil nou an. Nou enterese nan to komisyon an, ki se nan dezyèm kolòn nan tablo a. Se poutèt sa, pou agiman an Col_index_num (Column_number) antre valè 2.
Epi finalman, nou prezante dènye agiman an - Range_lookup (Entèval_lookup).
Enpòtan: se itilizasyon agiman sa a ki fè diferans ant de fason pou aplike fonksyon an VPR. Lè w ap travay ak baz done, agiman an Range_lookup (range_lookup) dwe toujou gen yon valè FO (FALSE) pou chèche yon match egzak. Nan itilizasyon nou an nan fonksyon an VPR, nou dwe kite jaden sa a vid, oswa antre yon valè VRE KÒD (VRE). Li trè enpòtan pou chwazi opsyon sa a kòrèkteman.
Pou fè li pi klè, nou pral prezante VRE KÒD (VRÈ) nan jaden an Range_lookup (Entèval_lookup). Malgre ke, si ou kite jaden an vid, sa a pa pral yon erè, depi VRE KÒD se valè default li:
Nou te ranpli nan tout paramèt yo. Koulye a, nou peze OK, ak Excel kreye yon fòmil pou nou ak yon fonksyon VPR.
Si nou fè eksperyans ak plizyè valè diferan pou kantite lajan total lavant, Lè sa a, nou pral asire w ke fòmil la travay kòrèkteman.
konklizyon
Lè fonksyon an VPR travay ak baz done, agiman Range_lookup (range_lookup) dwe aksepte FO (FO). Ak valè a te antre kòm Lookup_value (Lookup_value) dwe egziste nan baz done a. Nan lòt mo, li ap chèche pou yon match egzak.
Nan egzanp nou te gade nan atik sa a, pa gen okenn nesesite pou jwenn yon match egzak. Sa a se ka a lè fonksyon an VPR dwe chanje nan mòd apwoksimatif retounen rezilta a vle.
Pou egzanp: Nou vle detèmine ki pousantaj pou itilize nan kalkil komisyon an pou yon vandè ak yon volim lavant de $34988. Fonksyon VPR retounen nou yon valè de 30%, ki se absoliman kòrèk. Men, poukisa fòmil la chwazi ranje ki gen egzakteman 30% epi li pa 20% oswa 40%? Kisa rechèch apwoksimatif vle di? Se pou nou klè.
Lè agiman an Range_lookup (interval_lookup) gen yon valè VRE KÒD (VRÈ) oswa omisyon, fonksyon VPR itere nan premye kolòn nan epi chwazi pi gwo valè ki pa depase valè rechèch la.
Pwen enpòtan: Pou konplo sa a travay, premye kolòn nan tablo a dwe klase nan lòd monte.