Wanda ya yi kasala ko ba shi da lokacin karantawa - kalli bidiyon. Cikakkun bayanai da nuances suna cikin rubutun da ke ƙasa.
Samar da matsala
Don haka, muna da tebur guda biyu - oda tebur и price list:
Aikin shine musanya farashin daga jeri na farashi cikin tebur na umarni ta atomatik, mai da hankali kan sunan samfurin ta yadda daga baya zaku iya lissafin farashi.
Magani
A cikin saitin aikin Excel, ƙarƙashin rukuni Nassoshi da tsararru (Duba da tunani) akwai aiki VPR (VLOOKUP).Wannan aikin yana neman ƙimar da aka bayar (a cikin misalinmu, wannan shine kalmar "Apples") a cikin ginshiƙin hagu na ƙayyadaddun tebur (jerin farashin) yana motsawa daga sama zuwa ƙasa kuma, bayan gano shi, yana nuna abubuwan da ke cikin tantanin da ke kusa. (23 rubles) .Schematically, aikin wannan aikin za a iya wakilta Don haka:
Don sauƙin amfani da aikin, yi abu ɗaya lokaci ɗaya - ba da kewayon sel a cikin jerin farashin sunan ku. Don yin wannan, zaɓi duk sel na lissafin farashin ban da "kai" (G3: H19), zaɓi daga menu. Saka - Suna - Sanya (Saka - Suna - Ƙayyadaddun) ko latsa CTRL + F3 kuma shigar da kowane suna (babu sarari) kamar price… Yanzu, a nan gaba, zaku iya amfani da wannan sunan don haɗawa zuwa jerin farashin.
Yanzu muna amfani da aikin VPR… Zaɓi tantanin halitta inda za'a shigar dashi (D3) kuma buɗe shafin Formulas - Shigar Aiki (Formulas - Saka Aiki)… A cikin rukuni Nassoshi da tsararru (Duba da Magana) nemo aikin VPR (VLOOKUP) kuma latsa OK… Taga don shigar da hujja don aikin zai bayyana:
Muna cike su bi da bi:
- Ƙimar da ake so (Kimar Duba) – sunan samfurin da aikin yakamata ya samu a cikin ginshiƙin hagu na jerin farashin. A cikin yanayinmu, kalmar "Apple" daga cell B3.
- Table (Table Array) - tebur daga abin da ake so darajar uXNUMXbuXNUMXbare, wato, jerin farashin mu. Don tunani, muna amfani da sunan namu “Farashin” da aka bayar a baya. Idan baku bada suna ba, zaku iya zaɓar tebur kawai, amma kar a manta da danna maballin F4don haɗa haɗin tare da alamun dollar, domin in ba haka ba, zai zame ƙasa lokacin da ake kwafin tsarin mu zuwa sauran sel a cikin shafi D3:D30.
- Lambar_lambar (Lambar fihirisar shafi) - lambar serial (ba harafi ba!) Na ginshiƙi a cikin jerin farashin daga abin da za mu ɗauki ƙimar farashin. Rukunin farko na jerin farashin tare da sunaye an ƙidaya su 1, don haka muna buƙatar farashin daga shafi mai lamba 2.
- duba tazara (Duba Range) - Ana iya shigar da ƙima biyu kawai a cikin wannan filin: KARYA ko GASKIYA:
- Idan an shigar da ƙima 0 or KARYA (KARYA), to a gaskiya wannan yana nufin cewa bincike ne kawai aka yarda daidai wasa, watau idan aikin bai sami abin da ba daidai ba da aka ƙayyade a cikin jadawalin tsari a cikin jerin farashin (idan an shigar da "kwakwa", alal misali), zai haifar da kuskuren #N/A (babu bayanai).
- Idan an shigar da ƙima 1 or GASKIYA (GASKIYA), to wannan yana nufin cewa kun ƙyale binciken ba don ainihin ba, amma m daidaito, watau a cikin yanayin "kwakwa", aikin zai yi ƙoƙarin nemo samfurin da sunan da yake kusa da "kwakwa" kuma ya dawo da farashin wannan sunan. A mafi yawan lokuta, irin wannan kusantar musanyawa na iya taka wa mai amfani da dabara ta hanyar musanya ƙimar da ba daidai ba samfurin da yake a zahiri! Don haka ga mafi yawan matsalolin kasuwanci na gaske, ƙimanta bincike shine mafi kyawun kar a bari. Banda shi ne lokacin da muke neman lambobi ba rubutu ba - misali, lokacin ƙididdige Rangwamen Mataki.
Komai! Ya rage don danna OK kuma kwafi aikin da aka shigar zuwa gabaɗayan ginshiƙi.
Kurakurai # N / A da danne su
aiki VPR (VLOOKUP) ya dawo kuskuren #N/A (#N/A) idan a:
- An kunna ainihin bincike (hujja Duba tazara = 0) kuma sunan da ake so baya ciki Table.
- An haɗa babban bincike (Duba tazara = 1), amma Table, wanda binciken da ake yi ba a jera su a cikin jerin sunaye masu hawa ba.
- Tsarin tantanin halitta inda ƙimar da ake buƙata na sunan ta fito (misali, B3 a cikin yanayinmu) da tsarin sel na ginshiƙi na farko (F3: F19) na tebur sun bambanta (misali, lamba da rubutu). ). Wannan yanayin ya zama na musamman lokacin amfani da lambobin lambobi (lambobin lissafi, masu ganowa, kwanan wata, da sauransu) maimakon sunayen rubutu. A wannan yanayin, zaku iya amfani da ayyukan Ч и rubutu don canza tsarin bayanai. Zai yi kama da wani abu kamar haka:
=VLOOKUP(RUS(B3),farashi,0)
Kuna iya karanta ƙarin game da wannan anan.
- Aikin ba zai iya samun ƙimar da ake buƙata ba saboda lambar ta ƙunshi sarari ko haruffa marasa ganuwa (Ratsawar layi, da sauransu). A wannan yanayin, zaku iya amfani da ayyukan rubutu TASHIYA (TRIM) и BUGA(TSARKI) don cire su:
= VLOOKUP( TRIMSPACES(TSARKI(B3)),farashi,0)
= VLOOKUP (TRIM (TSARKI (B3)); farashin; 0)
Don murkushe saƙon kuskure # N / A (#N/A) a lokuta inda aikin ba zai iya samun madaidaicin wasa ba, zaka iya amfani da aikin IFEROR (FURTA)Don haka, alal misali, wannan ginin yana katse duk wani kurakurai da VLOOKUP ya haifar kuma ya maye gurbin su da sifili:
= IFERROR (VLOOKUP (B3, farashi, 2, 0), 0)
= IFERROR (VLOOKUP (B3; farashin; 2; 0); 0)
PS
Idan kana buƙatar cire ƙima ɗaya ba, amma duk saitin a lokaci ɗaya (idan akwai nau'ikan daban-daban), to lallai ne ku shamanize tare da tsarin tsararru. ko amfani da sabon fasalin XLOOKUP daga Office 365.
- Ingantacciyar sigar aikin VLOOKUP (VLOOKUP 2).
- Lissafin sauri na rangwame na mataki (kewayon) ta amfani da aikin VLOOKUP.
- Yadda ake yin "VLOOKUP na hagu" ta amfani da ayyukan INDEX da MATCH
- Yadda ake amfani da aikin VLOOKUP don cike fom tare da bayanai daga lissafin
- Yadda za a cire ba na farko ba, amma duk ƙimar daga tebur a lokaci ɗaya
- Ayyukan VLOOKUP2 da VLOOKUP3 daga PLEX add-on