Kush është shumë dembel ose nuk ka kohë për të lexuar - shikoni videon. Detajet dhe nuancat janë në tekstin më poshtë.
Formulimi i problemit
Pra, ne kemi dy tabela - tabela e porosive и Lista e Çmimeve:
Detyra është të zëvendësoni çmimet nga lista e çmimeve në tabelën e porosive automatikisht, duke u fokusuar në emrin e produktit në mënyrë që më vonë të mund të llogarisni koston.
Zgjidhje
Në grupin e funksioneve Excel, nën kategorinë Referencat dhe vargjet (Kërkim dhe referencë) ka një funksion VPR (VLOOKUP).Ky funksion kërkon një vlerë të caktuar (në shembullin tonë, kjo është fjala "Mollë") në kolonën më të majtë të tabelës së specifikuar (lista e çmimeve) duke lëvizur nga lart poshtë dhe, pasi e ka gjetur atë, shfaq përmbajtjen e qelizës ngjitur. (23 rubla) .Skematikisht, funksionimi i këtij funksioni mund të përfaqësohet kështu:
Për lehtësinë e përdorimit të mëtejshëm të funksionit, bëni një gjë menjëherë - jepni gamën e qelizave në listën e çmimeve emrin tuaj. Për ta bërë këtë, zgjidhni të gjitha qelizat e listës së çmimeve përveç "titullit" (G3: H19), zgjidhni nga menyja Fut – Emri – Cakto (Fut - Emri - Përcakto) ose shtypni CTRL + F3 dhe shkruani ndonjë emër (pa hapësira) si Çmimi… Tani, në të ardhmen, mund ta përdorni këtë emër për t'u lidhur me listën e çmimeve.
Tani ne përdorim funksionin VPR… Zgjidhni qelizën ku do të futet (D3) dhe hapni skedën Formulat – Futja e funksionit (Formulat - Futni funksionin)… Në kategori Referencat dhe vargjet (Kërkimi dhe referenca) gjeni funksionin VPR (VLOOKUP) dhe shtypni OK… Do të shfaqet një dritare për futjen e argumenteve për funksionin:
Ne i mbushim ato me radhë:
- Vlera e dëshiruar (Vlera e kërkimit) – emri i produktit që funksioni duhet të gjejë në kolonën më të majtë të listës së çmimeve. Në rastin tonë, fjala "Mollë" nga qeliza B3.
- Tryezë (Rreth tabelave) – një tabelë nga e cila janë marrë vlerat e dëshiruara uXNUMXbuXNUMX, domethënë lista jonë e çmimeve. Për referencë, ne përdorim emrin tonë "Çmimi" i dhënë më herët. Nëse nuk keni dhënë një emër, thjesht mund të zgjidhni tabelën, por mos harroni të shtypni butonin F4për të fiksuar lidhjen me shenjat e dollarit, sepse përndryshe, ajo do të rrëshqasë poshtë kur kopjojmë formulën tonë deri te pjesa tjetër e qelizave në kolonën D3:D30.
- Numri_kolona (Numri i indeksit të kolonës) – numri serial (jo një shkronjë!) i kolonës në listën e çmimeve nga do të marrim vlerat e çmimeve. Kolona e parë e listës së çmimeve me emrat është me numër 1, prandaj na duhet çmimi nga kolona me numër 2.
- interval_shikimi (Kërkimi i diapazonit) - vetëm dy vlera mund të futen në këtë fushë: FALSE ose TRUE:
- Nëse futet një vlerë 0 or GJENJE (I RREMË), atëherë në fakt kjo do të thotë se lejohet vetëm kërkimi përputhja e saktë, dmth nëse funksioni nuk gjen artikullin jo standard të specifikuar në tabelën e porosive në listën e çmimeve (nëse është futur "Coconut", për shembull), ai do të gjenerojë gabimin #N/A (pa të dhëna).
- Nëse futet një vlerë 1 or TRUE (E VERTETE), atëherë kjo do të thotë që ju lejoni kërkimin jo për të saktën, por përputhje e përafërt, pra në rastin e “kokosit”, funksioni do të përpiqet të gjejë një produkt me një emër sa më afër “kokosit” dhe të kthejë çmimin për këtë emër. Në shumicën e rasteve, një zëvendësim i tillë i përafërt mund të luajë një mashtrim me përdoruesin duke zëvendësuar vlerën e produktit të gabuar që ishte në të vërtetë atje! Pra, për shumicën e problemeve reale të biznesit, kërkimi i përafërt është më mirë të mos lejohet. Përjashtim është kur ne kërkojmë numra dhe jo tekst - për shembull, kur llogaritim zbritjet e hapave.
Gjithçka! Mbetet për të shtypur OK dhe kopjoni funksionin e futur në të gjithë kolonën.
# N / A gabimet dhe shtypja e tyre
funksion VPR (VLOOKUP) kthen gabim #N/A (# N/A) nese nje:
- Kërkimi i saktë është aktivizuar (argument Pamja e intervalit = 0) dhe emri i dëshiruar nuk është në Tryezë.
- Kërkimi i trashë i përfshirë (Pamja e intervalit = 1), por Tryezë, në të cilën po bëhet kërkimi nuk është renditur sipas renditjes rritëse të emrave.
- Formati i qelizës nga vjen vlera e kërkuar e emrit (për shembull, B3 në rastin tonë) dhe formati i qelizave të kolonës së parë (F3: F19) të tabelës janë të ndryshëm (për shembull, numerike dhe tekst ). Ky rast është veçanërisht tipik kur përdoren kodet numerike (numrat e llogarisë, identifikuesit, datat, etj.) në vend të emrave të tekstit. Në këtë rast, ju mund të përdorni funksionet Ч и TEXT për të kthyer formatet e të dhënave. Do të duket diçka si kjo:
=VLOOKUP(TEXT(B3),çmimi,0)
Ju mund të lexoni më shumë rreth kësaj këtu.
- Funksioni nuk mund të gjejë vlerën e kërkuar sepse kodi përmban hapësira ose karaktere të padukshme jo të printueshme (ndërprerje rreshtash, etj.). Në këtë rast, mund të përdorni funksionet e tekstit TRIM (TRIM) и PRINT(PASTER) për t'i hequr ato:
=VLOOKUP(HAPËSIRA TË PASTRA(B3)),çmimi,0)
=VLOOKUP(TRIM(CLEAN(B3));çmimi;0)
Për të shtypur mesazhin e gabimit # N / A (# N/A) në rastet kur funksioni nuk mund të gjejë një përputhje të saktë, mund ta përdorni funksionin IFERROR (IFEROR)… Kështu, për shembull, ky ndërtim kap çdo gabim të krijuar nga VLOOKUP dhe i zëvendëson ato me zero:
= IFERROR (VLOOKUP (B3, çmimi, 2, 0), 0)
= IFERROR (VLOOKUP (B3; çmimi; 2; 0); 0)
PS
Nëse ju duhet të nxirrni jo një vlerë, por të gjithë grupin menjëherë (nëse ka disa të ndryshme), atëherë do të duhet të shamanizoni me formulën e grupit. ose përdorni veçorinë e re XLOOKUP nga Office 365.
- Një version i përmirësuar i funksionit VLOOKUP (VLOOKUP 2).
- Llogaritja e shpejtë e zbritjeve të hapit (gamës) duke përdorur funksionin VLOOKUP.
- Si të bëni një "VLOOKUP majtas" duke përdorur funksionet INDEX dhe MATCH
- Si të përdorni funksionin VLOOKUP për të plotësuar formularët me të dhëna nga lista
- Si të nxirrni jo të parat, por të gjitha vlerat nga tabela menjëherë
- Funksionet VLOOKUP2 dhe VLOOKUP3 nga shtesa PLEX