VLOOKUP. Kugwiritsa VLOOKUP. Excel - CDF

The ntchito pulogalamu kupambana ndi wotchuka chifukwa cha chodya ake ndi ophweka, chifukwa sikutanthauza nzeru zapadera ndi luso. Table Polingalira za chiphunzitso cha uthenga wosuta aliyense, ndi lonse osiyanasiyana zida, kuphatikizapo "Master ntchito" amalola mpheto iliyonse ndi kuwerengetsera deta ndi operekedwa.

Mmodzi wa odziwika bwino kupambana mitunduyi ndi view choimirira. Kugwiritsa VLOOKUP pa koyamba zikuoneka zovuta kwambiri, koma ndi chiyambi chabe.

Kodi kupambana VLOOKUP

Pamene akugwira CDF chilinganizo ayenera kuganizira kuti adzakhala kufufuza zikhulupiliro kufunika okha mizati osati mizere. Kutsatira ntchito pamafunika mlingo wa mizati - awiri, pazipita zilipo.

CDF ntchito amasanthula anakonzeratu muyezo omwe mwina chilichonse mtundu (malemba, M'ndandanda, ndalama, tsiku ndi nthawi, T. D.) Mu tebulo. Pankhani ya kujambula izo zotuluka ndi kupeza (oyika) kufunika olembedwa pa mzere womwewo, koma ndi mutu wa mzati wa, i.e. lolingana muyezo anapatsidwa. Ngati mtengo sapezeka, ndiye kulakwitsa ndi chikalata # N / A (mu English Version # N / A).

kufunika kokhala

VLOOKUP amathandiza a woyendetsa, pamene mukufuna kupeza mwachangu ndi ntchito kuwerengetsera zina, kusanthula kapena ulosi wa tebulo mtengo zina zazikulu. Chinthu chachikulu pamene inu ntchito chilinganizo ichi - onetsetsani kuti mwachindunji kuchuluka kwa kusaka wakhala molondola anasankha. Iwo pazikhalanso zolembedwa zonse, ndiko kuti, kuyambira poyamba mpaka pamapeto.

The ntchito zambiri za CDF (kupambana ntchito) - kufanana ndi Kuwonjezera deta mu magome awiri, pogwiritsa ntchito muyezo yeniyeni. Komanso, kufufuza osiyanasiyana zingakhale lalikulu ndipo ali zikwi za m'minda, anaika pa mapepala osiyana kapena mabuku.

Anatiphunzitsa ntchito ya CDF, momwe ntchito, mmene angagwirire anapezazo, monga chitsanzo chithunzi pamwambapa. Apa tikambirana tebulo retails zamitundu malingana ndi dera komanso bwana. Search muyezo ndi woyang'anira yeniyeni (dzina lake zonse), ndi mtengo wolakalakika ndi Uwerenge malonda ake.

Chifukwa cha ntchito CDF (VLOOKUP), tebulo latsopano yeniyeni woyang'anira anakhumba msanga chikufanana malonda ake okwana.

Kudzaza aligorivimu chilinganizo

Ili chilinganizo CDF mu "Ntchito mfiti" ndi "Zothandizira ndi magulu." Kukambirana njira ndi iyi:

mu chilinganizo cha mfundo omwe alowa mu ima pamzere ndi:

• Mtengo wolakalakika - ndiko kupeza ntchito, ndipo mitundu ndiwo mfundo za selo, adiresi ake, dzina chinapatsidwa mwa woyendetsa lapansi. Ifeyo - ndi dzina ndi dzina la bwana.
• Table - osiyanasiyana mizere ndi mizati, momwe muyeso ndi anafuna.
• Ndime chiwerengero - nambala yake siriyo, ndilo Uwerenge malonda, kuti chifukwa cha njira.
• view imeneyi. Iye ali ndi phindu kaya onyenga kapena WOONA. Ndipo akadzabweranso ZONYENGA chabe machesi enieni, WOONA - zimathandiza kufufuza zikhulupiliro idzatha.

Chitsanzo cha ntchito ya

VLOOKUP Mwachitsanzo ntchito akhale motere: pochita malonda ogwira ntchito mu kupambana spreadsheets mu ndime A dzina lolembedwa la mankhwala, ndipo mu ndime B - mtengo kake. Kukafola bwana mu ndime C muyenera kupeza mtengo wa mankhwala imeneyo mukufuna aziwoneka mu ndime D.

Chilinganizo, zinalembedwa D, lidzaonekera izi: = VLOOKUP (C1, A1: B5, 2, 0), mwachitsanzo, = VLOOKUP (mtengo osakira, tebulo deta osiyanasiyana, ndi siriyo nambala ya ndime ya; 0). Monga Mtsutso wachinayi angagwiritsidwe ntchito m'malo 0 ZONYENGA.

Kudzaza chilinganizo limasonyeza tebulo, muyenera kutengera ndime lonse D.

Otetezeka deta dera osiyanasiyana ntchito ndi kudzera mwa malemba Mtheradi. Kuti tichite zimenezi pamanja tabulated $ zizindikiro kutsogolo kwa mfundo alifabeti ndi n'zosangalatsa kumva mwa muyeso maselo lamanzere ndi lamanja pa tebulo. Pankhaniyi chilinganizo amakhala = CDF (C1; $ A $ 1: $ B $ 5, 2, 0).

ntchito zolakwa

CDF ntchito sagwira ntchito, kenako uthenga limapezeka zotsatira ndime linanena bungwe kulakwa (# N / A kapena # N / A). Izi zimachitika Zikatero:

1. Ndondomeko umayamba, ndipo kufunika i mzati ndi kanthu (mu nkhani iyi, ndime C).
2. Mu ndime C anapanga mtengo kuti palibe mu ndime A (kufufuza deta osiyanasiyana). Kuti muone phindu chofunika ayenera allocated ndi muyezo ndime menyu tsamba "Sinthani" - "Pezani" amaika nkhani imeneyi, kuyamba kusaka. Ngati pulogalamu sanapeze izo, ndiye kulibe.
3. Akamagwiritsa ndime ya maselo A ndi C (chofunika i) osiyana, mwachitsanzo, munthu - lemba, ndi zina - chiwerengero. Kusintha mtundu wa khungu akhoza kukhala, ngati ife kupita kusintha maselo (F2). mavuto zambiri zimachitika pamene inu kuitanitsa deta ku ntchito zina. Kupewa amenewa mu njira CDF n'zotheka kutchula zinthu zotsatirazi: MUZILEMEKEZA kapena LEMBA. Kukhazikitsa ma aligorivimu izi basi kutembenuza mtundu wa maselo.
4. Ntchito malamulo ili ndi zilembo sanali choti mudzakambirane monga kapena malo. Ndiye inu azitha fufuzani chilinganizo pamaso pa zolakwa athandizira.
5. Anapereka pafupifupi kufufuza, kuti ndi wachinayi mkangano VLOOKUP 1 zoona, koma tebulo si zosankhidwa ndi phindu likutuluka. Pankhaniyi, i lofunika ndime mukufuna kuthetsa kuti akwera.

Ndipo pamene watsopano mwachidule tebulo ya gulu ndi muyezo zinafunika akhoza kukhala kuti aliyense ndi ndondomeko osati kulandiridwa mndandanda wa (tsankho kusankha).

Features ntchito ngati imeneyi kuonera 1 kapena WOONA

Cholakwika pansi №5 ndi zachilendo ndipo yasonyeza mu chithunzi m'munsimu.

Mu chitsanzo ichi mndandanda dzina ndi zosankhidwa malinga ndi manambala si kuti akwera, ndi pa mtengo kugwa. Komanso, ngati imeneyi ntchito view WOONA muyezo (1), zomwe yomweyo kaye kusaka pamene detects ndi mtengo wake woposa chofunika, kotero kulakwitsa ndi chikalata.

Mu ntchito 1 zoona mu Mtsutso wachinayi wa kufunika kuonetsetsa kuti ndime akugwirizana kufunika wakhala zosankhidwa kuti akwera. Ntchito 0 kapena BODZA, izi si kufunika, komanso akusowa ndiye kuti n'zotheka ndi view imeneyi.

Monga kukumbukira kuti imeneyi n'kofunika kwambiri makamaka mtundu tebulo. Apo ayi VLOOKUP adzasonyeza mwapeza zolakwika mu chipinda.

mokoma ena a ntchito ndi VLOOKUP

Pakuti mayiko, chilinganizo otere kukhala oyenera tebulo osiyanasiyana amene ankafufuza (Mtsutso wachiwiri), monga tikuonera mkuyu.

Pankhaniyi, malonda m'dera la tebulo yakuti. Kuti zimenezi zitheke, m'pofunika kufufuza tebulo, kupatulapo mitu ya ndime, ndi m'munda dzina (pansi pa tsamba omwera kumanzere) akapatsidwa dzina.

Njira ina - mutu wakuti - zikutanthauza Kugawilidwa kwanthaka wa uthunthu deta, kenako kusinthana kwa menyu "Ikani" - "Dzina" - "Perekani".

Pofuna ntchito deta anaika pa pepala ina lolembamo, pogwiritsa ntchito PPS, m'pofunika mu Mtsutso wachiwiri wa chilinganizo m'kaundula malo deta osiyanasiyana. Mwachitsanzo, CDF = (A1; Mapepala 2 $ A $ 1: $ B $ 5; 2; 0), kumene Sheet2! - mawu onena kuti anapempha buku mndandanda, ndipo $ A $ 1: $ B $ 5 - adiresi ya deta kufufuza osiyanasiyana.

Chitsanzo cha ndondomeko maphunziro ndi CDF

yabwino kwambiri kupambana VLOOKUP ntchito kutsatira osati kwa Makampani kuchita malonda, komanso sukulu kuti konza ophunzira ofananira ndondomeko (ophunzira) ndi anene. Zitsanzo za ntchito izi amaoneka mu mawerengero pansipa.

Pali m'ndandanda wa ophunzira pa tebulo. Limodzi ndi anene, ndipo chachiwiri limasonyeza zaka. M'pofunika kuyerekeza matebulo awiri kuti pa ndime ndi zaka ophunzira anasonyeza ziyenera awo, kuti ndi kuwonjezera ndi ndime zina mu mndandanda wachiwiri.

VLOOKUP uzigwirizana ndi ntchito imeneyi. Mu "zizindikiro" G ndime lotchedwa olembedwa chilinganizo lolingana: = CDF (E4, B3: C13, 2, 0). Iye ayenera kutsatira ndime lonse pa tebulo.

Chifukwa cha ntchito VLOOKUP adzakhala Unikani akamagwira ophunzira ena.

Chitsanzo cha kufufuza injini ndi CDF

Chitsanzo china cha ntchito ya CDF ntchito - ndi gulu la kufufuza injini pamene Nawonso achichepere malinga ndi muyezo mwachindunji ayenera kupeza phindu lokwanira ndendende. Choncho, chithunzi limasonyeza mndandanda wa nicknames nyama ndi wachokera awo a mtundu winawake.

Mothandizidwa ndi CDF, tebulo latsopano limene zovuta kupeza, dzina nyama mawonekedwe ake. injini zogwirizana kufufuza ngati pamene ntchito ndi mndandanda waukulu. Pofuna pamanja tikambirane zolemba zonse, mukhoza msanga ntchito kufufuza ndi kupeza chimene mukufuna.