VLOOKUP. Amfani VLOOKUP. Excel - CDF

A aikace-aikace shirin Excel ne m saboda ta affordability da kuma sauki, saboda shi ba ya bukatar wani na musamman ilmi da basira. Table view of mas'ala ta bayanai zuwa duk wani mai amfani, da kuma fadi da kewayon kayan aikin, ciki har da "Master aiki" damar wani magudi da kuma lissafin tare da bayar da bayanai.

Daya daga cikin sanannun Excel dabarbari ne a tsaye view. Amfani VLOOKUP farko duba alama quite rikitarwa, amma yana da kawai farkon.

Ta yaya Excel VLOOKUP

Lokacin aiki tare da CDF dabara ya kamata ya yi la'akari da cewa shi zai bincika da ake so dabi'u kawai a ginshikan maimakon layuka. Don amfani da aiki na bukatar wani m yawan ginshikan - biyu, matsakaicin samuwa.

CDF aiki fayyace qaddara rarrabẽwa wadda za ta iya zama da wani format (rubutu, Tazarar, kudi, kwanan wata da lokaci, t. D.) A tebur. A cikin hali na rikodi shi jimloli wani binciken (abun da ake sakawa) da darajar rubuce a cikin wannan jere, amma tare da title na shafi, Ina nufin m zuwa ba rarrabẽwa. Idan darajar ba a samu, nan da wani kuskure da aka bayar # N / A (a cikin Turanci version # N / A).

bukatar yin amfani da

VLOOKUP zo da taimakon afareta, idan kana so ka da sauri sami da kuma amfani da a kara lissafin, analysis ko Hasashen na wani daraja ya fi girma tebur. Babban abu a lokacin da ka yi amfani da wannan dabara - tabbatar da cewa kayyade ikon yinsa, daga cikin search An daidai zaba. Ya kamata hada da duk records, wato, daga farko zuwa ƙarshe.

A mafi m amfani da CDF (Excel aiki) - da kwatanta da Bugu da kari na data a cikin alluna biyu, ta amfani da takamaiman sharudda. Bugu da ƙari, cikin search kewayon iya zama manyan da kuma dauke da dubban filayen, sanya a kan daban-daban da zanen gado ko littattafai.

Nuna da aiki na CDF, yadda za a yi amfani da shi, yadda za a gudanar da wani lissafin, a matsayin misali a cikin adadi sama. Ga mun yi la'akari da tebur retails masu girma dabam dangane da yankin da kuma manajan. Search rarrabẽwa ne takamaiman manajan (ya full name), da kuma so darajar ne Naira Miliyan Xari da tallace-tallace.

A sakamakon da CDF aiki (VLOOKUP), wani sabon tebur a cikin abin da takamaiman so manajan sauri dace da duka tallace-tallace.

Ciko algorithm dabara

Located dabara CDF a cikin "Aiki Wizard" da kuma "References da kuma iri-iri." Maganganu zaɓi ne kamar haka:

a cikin dabara na muhawara da aka shigar a cikin wani jerin gwano:

• Da ake so darajar - cewa shi ne a sami wani aiki, da kuma bambance-bambancen karatu wanda su ne dabi'u na cell, ta adireshin, sunan da aka ba shi ta sadarwarka. A cikin yanayin - shi ne sunan da sunan manajan.
• Table - kewayon layuka da kuma ginshikan, cikinsa da rarrabẽwa da aka nema.
• Column lambar - ta serial number, wanda shi ne Naira Miliyan Xari da tallace-tallace, da cewa shi ne sakamakon da dabara.
• Tazara view. Ya riqe wani darajar ko dai KARYA ko GASKIYA. Kuma KARYA dawo kawai ya dace, GASKIYA - damar bincika kimanin dabi'u.

Misali na aiki

VLOOKUP misali amfani iya zama kamar haka: lokacin da akayi ciniki sha'anin a Excel maƙunsar a shafi A rubuta sunan samfur, da kuma a shafi B - daidai farashin. Domin jawo sama da bada shawarwari a shafi C kana bukatar ka sami kudin da wani samfurin cewa kana so ka nuna a shafi D.

Da dabara, rubuta a D, zai yi kama da wannan: = VLOOKUP (C1, A1: B5, 2, 0), watau, = VLOOKUP (nemo darajar, tebur data iyaka, da serial number na shafi; 0). Kamar yadda a karo na hudu shaida za a iya amfani maimakon 0 ne KARYA.

Don cika wannan dabara yayi wani tebur, dole ne ka kwafa dukan shafi D.

Secure data yankin na aiki iyaka ne zai yiwu, ta hanyar da cikakkar nassoshi. Don yin wannan da hannu tabulated $ ãyõyi a gaban alphabetic da lamba dabi'u da matsananci hagu da kuma dama sel da tebur. A wannan yanayin da dabara zama: = CDF (C1. $ A $ 1: $ B $ 5, 2, 0).

ta amfani da kuskure

CDF aiki bai yi aiki ba, sa'an nan kuma wani sakon bayyana a cikin shafi fitarwa sakamakon wani kuskure (# N / A ko # N / A). Wannan ya faru a irin haka:

1. Da dabara aka gabatar, da kuma so da sharudda shafi fanko (a wannan yanayin, shafi C).
2. A shafi C sanya wani darajar cewa shi ne ba ya nan a shafi A (search kewayon data). Don duba bukata darajar kamata a kasaftawa da shafi sharudda a cikin menu tab "Edit" - "Nemo" to saka wannan rikodin, fara bincike. Idan shirin ba su samu ba, to, shi ba ya wanzu.
3. Formats shafi na sel A da C (da ake bukata sharudda) ne daban-daban, misali, daya - da rubutu, da kuma sauran - lambar. Canja format na tantanin halitta zai iya zama, idan mun tafi a cikin cell tace (F2). Irin matsalolin yawanci faruwa lokacin da ka shigo data daga sauran aikace-aikace. Don kauce wa irin wannan kuskuren a cikin dabara CDF ne yiwu zuwa kunsa da wadannan siffofin: VALUE ko rubutu. Aiwatar da wadannan lissafi mai tsauri ta atomatik maida da format na Kwayoyin.
4. The aiki code ƙunshi ba na bugawan dutse haruffa ko sarari. Sa'an nan kuma ka duba ya kamata a hankali da dabara a gaban shigar da kurakurai.
5. Saita wani m search, cewa shi ne karo na hudu shaida VLOOKUP ne 1 ko GASKIYA, amma tebur da aka ba ana jerawa da mafitar darajar. A wannan yanayin, da ake bukata sharudda ga shafi kana so ka warware a hawa domin.

Kuma a lõkacin da wani sabon summary tebur na kungiyar ne da ake buƙata sharudda iya zama a cikin wani tsari da kuma jerin kuma ba dole ba ne a samu cikakken jerin (m selection).

Features na amfani da tazara duba 1 ko GASKIYA

Kuskuren karkashin №5 ne quite na kowa kuma a fili aka nuna a cikin adadi a kasa.

A wannan misali, sunan jerin ne ana jerawa bisa ga lambobin ba a hawa domin, da kuma a kan fadowa darajar. Bugu da ƙari, kamar yadda tazara amfani da view GASKIYA rarrabẽwa (1), wanda nan da nan tarnhayar da search a lõkacin da ta gano wani darajar fi bukata, don haka da wani kuskure da aka bayar.

A cikin aikace-aikace na 1 ko gaskiya ne a karo na hudu shawara na bukatar tabbatar da ganin cewa da shafi da ake so sharudda da aka jerawa a hawa domin. Lokacin amfani 0 ko ƙarya, wannan ne ba a bukatar, amma kuma bata nan da yiwuwar wani tazara view.

Just ka tuna cewa tazara ne da muhimmanci musamman don warware tebur. In ba haka ba VLOOKUP zai nuna ba daidai ba data a wani cell.

Sauran nuances na aiki tare da VLOOKUP

Domin saukaka, irin wannan dabara za a iya hakkin kewayon tebur wanda aka bincike (na biyu shaida), kamar yadda aka nuna a cikin siffa.

A wannan yanayin, da tallace-tallace yankin na tebur suna. Don wannan karshen, shi tsaye a tebur, tare da togiya na shafi headings, kuma a cikin sunan filin (karkashin shafin mashaya zuwa hagu) da aka sanya wani sunan.

Wani zabin - kanun labarai - yana nufin kasafi na data range, sa'an nan canzawa zuwa menu "Saka" - "Name" - "Sanya".

Domin amfani da data sanya a kan wata takardar na Kundin, ta amfani da PPS aiki, wajibi ne a karo na biyu hujjarsu na da dabara littãfi wuri data kewayo. Alal misali, CDF = (A1. Sheet 2 $ A $ 1: $ B $ 5; 2; 0), inda Sheet2! - wani tunani da nema littafin list, da kuma $ A $ 1: $ B $ 5 - adireshin da data search kewayo.

An misali na ilimi tsari da CDF

Quite dace don Excel VLOOKUP aiki don amfani ba kawai ga kamfanonin tsunduma a harkokin ciniki, amma kuma makarantu don inganta daidai da tsari almajiran (dalibai) tare da su kimomi. Misalan wadannan ayyuka aka nuna a cikin Figures a kasa.

Akwai biyu lists na dalibai na tebur. Daya tare da su kimomi, da kuma na biyu ya nuna shekaru. Wajibi ne a kwatanta alluna biyu haka cewa a kan wani par da shekaru na dalibai nuna su kima, da cewa shi ne don ƙara ƙarin shafi a karo na biyu jerin.

VLOOKUP copes tare da wannan aiki. A "alamomi" G shafi mai taken rubuta m dabara: = CDF (E4, B3: C13, 2, 0). Ta bukatar kwafa dukan shafi na tebur.

A sakamakon da VLOOKUP aiki zai tantance samu ta hanyar wasu dalibai.

Misali da wani search engine tare da CDF

Wani misali na aikace-aikace na CDF aiki - wani shiri na search engine idan a wani database bisa ga kayyade sharudda kasance a sami daidai darajar. Saboda haka, adadin ya nuna wani jerin sunayen laƙabi na dabbobi da su na da wani irin.

Da taimakon CDF, wani sabon tebur da yake da sauki a samu, lakabi da dabba halittarsa. Relevant search engines kamar lõkacin da aiki tare da manyan lists. Domin da hannu a duba duk shigarwar, za ka iya sauri amfani da search da kuma samun ake so sakamakon.