Siri zote za kazi ya Excel VLOOKUP ya kutafuta data kwenye jedwali na kuitoa hadi nyingine

2024 Mwandishi: Malcolm Clapton | [email protected]. Mwisho uliobadilishwa: 2023-12-17 04:11

Baada ya kusoma makala, hutajifunza tu jinsi ya kupata data katika lahajedwali ya Excel na kuiondoa kwenye nyingine, lakini pia mbinu zinazoweza kutumika kwa kushirikiana na kazi ya VLOOKUP.

Wakati wa kufanya kazi katika Excel, mara nyingi kuna haja ya kupata data kwenye meza moja na kuiondoa kwenye nyingine. Ikiwa bado haujui jinsi ya kufanya hivyo, basi baada ya kusoma kifungu hicho, hautajifunza tu jinsi ya kufanya hivyo, lakini pia ujue chini ya hali gani unaweza kufinya utendaji wa juu kutoka kwa mfumo. Mbinu nyingi zinazofaa sana zinazopaswa kutumiwa pamoja na kitendakazi cha VLOOKUP zinazingatiwa.

Hata kama umekuwa ukitumia chaguo la kukokotoa la VLOOKUP kwa miaka, basi kwa uwezekano wa kiwango kikubwa makala haya yatakuwa na manufaa kwako na hayatakuacha tofauti. Kwa mfano, kuwa mtaalamu wa IT, na kisha mkuu katika IT, nimekuwa nikitumia VLOOKUP kwa miaka 15, lakini niliweza tu kukabiliana na nuances yote sasa, nilipoanza kufundisha watu Excel kwa misingi ya kitaaluma.

VLOOKUP ni kifupi cha vwima NSukaguzi. Vivyo hivyo, VLOOKUP - Wima LOOKUP. Jina lenyewe la chaguo la kukokotoa linatudokeza kwamba hutafuta kwenye safu za jedwali (kwa wima - kurudia safu na kurekebisha safu), na sio kwenye safu wima (kwa usawa - ikirudia safu na kurekebisha safu). Ikumbukwe kwamba VLOOKUP ina dada - bata bata ambaye hatawahi kuwa swan - hii ndiyo kazi ya HLOOKUP. HLOOKUP, kinyume na VLOOKUP, hufanya utafutaji wa mlalo, lakini dhana ya Excel (na kwa hakika dhana ya kupanga data) inadokeza kuwa majedwali yako yana safu wima chache na safu mlalo nyingi zaidi. Ndiyo maana tunahitaji kutafuta kwa safu mlalo mara nyingi zaidi kuliko safu wima. Ikiwa unatumia kazi ya HLO mara nyingi sana katika Excel, basi kuna uwezekano kwamba haukuelewa kitu katika maisha haya.

Sintaksia

Kazi ya VLOOKUP ina vigezo vinne:

= VLOOKUP (;; [;]), hapa:

- thamani inayotakiwa (mara chache) au rejeleo la seli iliyo na thamani inayotakiwa (kesi nyingi);

- kumbukumbu ya safu ya seli (safu ya pande mbili), katika safu ya KWANZA (!) ambayo thamani ya parameta itatafutwa;

- nambari ya safu wima katika safu ambayo thamani itarejeshwa;

- hiki ni kigezo muhimu sana kinachojibu swali ikiwa safu wima ya kwanza ya masafa imepangwa kwa mpangilio wa kupanda. Ikiwa safu imepangwa, tunabainisha thamani TRUE au 1, vinginevyo - FALSE au 0. Ikiwa kigezo hiki kitaachwa, kitabadilika kuwa 1.

Ninaweka dau kuwa wengi wa wale wanaojua utendaji wa VLOOKUP kuwa dhaifu, baada ya kusoma maelezo ya kigezo cha nne, wanaweza kuhisi wasiwasi, kwa kuwa wamezoea kuiona katika hali tofauti kidogo: kwa kawaida wanazungumza kuhusu inayolingana kabisa wakati. kutafuta (FALSE au 0) au uchanganuzi wa masafa (TRUE au 1).

Sasa unahitaji kuchuja na kusoma aya inayofuata mara kadhaa hadi uhisi maana ya kile kilichosemwa hadi mwisho. Kila neno ni muhimu hapo. Mifano itakusaidia kujua.

Je, fomula ya VLOOKUP inafanya kazi vipi hasa?

• Aina ya fomula ya I. Ikiwa kigezo cha mwisho kimeachwa au kubainishwa sawa na 1, basi VLOOKUP inadhania kuwa safu wima ya kwanza imepangwa kwa mpangilio wa kupanda, kwa hivyo utafutaji unasimama kwenye safu mlalo ambayo mara moja hutangulia mstari ulio na thamani kubwa kuliko ile inayotakikana … Ikiwa hakuna mfuatano kama huo unaopatikana, safu mlalo ya mwisho ya masafa hurejeshwa.

  

• Mfumo II. Ikiwa kigezo cha mwisho kimebainishwa kama 0, basi VLOOKUP huchanganua safu wima ya kwanza ya safu mfuatano na inasimamisha utafutaji mara moja wakati ulinganifu wa kwanza na kigezo unapatikana, vinginevyo msimbo wa hitilafu # N / A (# N / A) inarudishwa.

  

Mitiririko ya kazi ya fomula

VPR aina I

VPR aina II

Michanganyiko ya fomula za fomu I

1. Fomula zinaweza kutumika kusambaza thamani katika safu mbalimbali.
2. Ikiwa safu wima ya kwanza ina thamani zilizorudiwa na imepangwa kwa usahihi, basi safu mlalo ya mwisho iliyo na nakala za nambari itarejeshwa.
3. Ikiwa unatafuta thamani ambayo ni wazi zaidi kuliko safu ya kwanza inaweza kuwa, basi unaweza kupata kwa urahisi safu ya mwisho ya meza, ambayo inaweza kuwa ya thamani kabisa.
4. Mwonekano huu utarudisha kosa la # N / A ikiwa tu halitapata thamani iliyo chini ya au sawa na inayohitajika.
5. Ni ngumu kuelewa kuwa fomula inarudisha maadili yasiyofaa ikiwa safu yako haijapangwa.

Corollaries kwa fomula za aina ya II

Ikiwa thamani inayotakiwa hutokea mara nyingi katika safu wima ya kwanza ya safu, basi fomula itachagua safu mlalo ya kwanza kwa urejeshaji data unaofuata.

Utendaji wa VLOOKUP

Umefikia kilele cha makala. Inaweza kuonekana, vizuri, ni tofauti gani ikiwa nitataja sifuri au moja kama paramu ya mwisho? Kimsingi, kila mtu anaonyesha, bila shaka, sifuri, kwa kuwa hii ni ya vitendo kabisa: huna haja ya kuwa na wasiwasi kuhusu kupanga safu ya kwanza ya safu, unaweza kuona mara moja ikiwa thamani ilipatikana au la. Lakini ikiwa una maelfu ya fomula za VLOOKUP kwenye laha yako, utagundua kuwa VLOOKUP II ni polepole. Wakati huo huo, kawaida kila mtu huanza kufikiria:

• Nahitaji kompyuta yenye nguvu zaidi;
• Nahitaji fomula ya haraka zaidi, kwa mfano, watu wengi wanajua kuhusu INDEX + MATCH, ambayo inadaiwa kuwa haraka kwa 5-10%.

Na watu wachache wanafikiri kwamba pindi tu unapoanza kutumia VLOOKUP ya aina ya I na kuhakikisha kuwa safu wima ya kwanza imepangwa kwa njia yoyote, kasi ya VLOOKUP itaongezeka mara 57. Ninaandika kwa maneno - MARA HAMSINI NA SABA! Sio 57%, lakini 5,700%. Niliangalia ukweli huu kwa uhakika kabisa.

Siri ya kazi hiyo ya haraka iko katika ukweli kwamba algorithm ya utafutaji yenye ufanisi sana inaweza kutumika kwenye safu iliyopangwa, ambayo inaitwa utafutaji wa binary (njia ya kupunguza nusu, njia ya dichotomy). Kwa hivyo VLOOKUP ya aina ya I huitumia, na VLOOKUP ya aina ya II hutafuta bila uboreshaji wowote. Vile vile ni kweli kwa chaguo la kukokotoa la MATCH, ambalo lina kigezo sawa, na kitendakazi cha LOOKUP, ambacho hufanya kazi tu kwenye safu zilizopangwa na kujumuishwa katika Excel kwa upatanifu na Lotus 1-2-3.

Hasara za formula

Hasara za VLOOKUP ni dhahiri: kwanza, hutafuta tu katika safu wima ya kwanza ya safu iliyobainishwa, na pili, upande wa kulia wa safu wima hii pekee. Na kama unavyoelewa, inaweza kutokea kwamba safu iliyo na habari muhimu itakuwa upande wa kushoto wa safu ambayo tutaangalia. Mchanganyiko ambao tayari umetajwa wa fomula INDEX + MATCH hauna dosari hii, ambayo inafanya kuwa suluhisho rahisi zaidi la kutoa data kutoka kwa jedwali kwa kulinganisha na VLOOKUP (VLOOKUP).

Baadhi ya vipengele vya kutumia fomula katika maisha halisi

Utafutaji wa anuwai

Kielelezo cha asili cha utafutaji wa anuwai ni kazi ya kuamua punguzo kwa ukubwa wa agizo.

Tafuta mifuatano ya maandishi

Bila shaka, VLOOKUP haitazamii nambari tu, bali pia maandishi. Inapaswa kukumbushwa katika akili kwamba fomula haitofautishi kati ya kesi ya wahusika. Ikiwa unatumia kadi-mwitu, unaweza kupanga utafutaji usio na maana. Kuna wildcards mbili: "?" - inachukua nafasi ya herufi moja katika mfuatano wa maandishi, "*" - inachukua nafasi ya nambari yoyote ya wahusika wowote.

Nafasi za mapigano

Swali mara nyingi hufufuliwa jinsi ya kutatua tatizo la nafasi za ziada wakati wa kutafuta. Ikiwa jedwali la utafutaji bado linaweza kufutwa kutoka kwao, basi kigezo cha kwanza cha fomula ya VLOOKUP sio juu yako kila wakati. Kwa hiyo, ikiwa hatari ya kuziba seli zilizo na nafasi za ziada zipo, basi unaweza kutumia kazi ya TRIM ili kuifuta.

Muundo tofauti wa data

Ikiwa kigezo cha kwanza cha chaguo za kukokotoa cha VLOOKUP kinarejelea kisanduku kilicho na nambari, lakini ambacho kimehifadhiwa kwenye kisanduku kama maandishi, na safu wima ya kwanza ya safu ina nambari katika umbizo sahihi, basi utafutaji utashindwa. Hali ya kinyume pia inawezekana. Shida inaweza kutatuliwa kwa urahisi kwa kutafsiri parameta 1 katika muundo unaohitajika:

= VLOOKUP (-− D7; Bidhaa! $ A $ 2: $ C $ 5; 3; 0) - ikiwa D7 ina maandishi na jedwali lina nambari;

= VLOOKUP (D7 & ""); Bidhaa $ A $ 2: $ C $ 5; 3; 0) - na kinyume chake.

Kwa njia, unaweza kutafsiri maandishi kuwa nambari kwa njia kadhaa mara moja, chagua:

• Kukanusha mara mbili -D7.
• Kuzidisha kwa D7 * 1 moja.
• Nyongeza ya sifuri D7 + 0.
• Kuinua hadi nguvu ya kwanza D7 ^ 1.

Kubadilisha nambari hadi maandishi hufanywa kwa kuunganishwa na kamba tupu, ambayo inalazimisha Excel kubadilisha aina ya data.

Jinsi ya kukandamiza # N / A

Hii ni rahisi sana kufanya na kazi ya IFERROR.

Kwa mfano: = IFERROR (VLOOKUP (D7; Bidhaa! $ A $ 2: $ C $ 5; 3; 0); "").

Ikiwa VLOOKUP itarudisha msimbo wa makosa # N / A, basi IFERROR itaizuia na kubadilisha parameta 2 (katika kesi hii, kamba tupu), na ikiwa hakuna hitilafu hutokea, basi chaguo hili la kukokotoa litajifanya kuwa haipo kabisa, lakini. kuna VLOOKUP pekee iliyorejesha matokeo ya kawaida.

Safu

Mara nyingi husahau kufanya kumbukumbu ya safu kabisa, na wakati wa kunyoosha safu "inaelea". Kumbuka kutumia $ A $ 2: $ C $ 5 badala ya A2: C5.

Ni vyema kuweka safu ya kumbukumbu kwenye karatasi tofauti ya kitabu cha kazi. Haiingii chini, na itakuwa salama zaidi.

Wazo bora zaidi litakuwa kutangaza safu hii kama safu iliyotajwa.

Watumiaji wengi, wakati wa kubainisha safu, tumia ujenzi kama A: C, kubainisha safu wima nzima. Mbinu hii ina haki ya kuwepo, kwa kuwa umeokolewa kutokana na kufuatilia ukweli kwamba safu yako inajumuisha masharti yote yanayohitajika. Ukiongeza safu mlalo kwenye laha iliyo na safu asili, basi safu iliyobainishwa kama A: C haihitaji kurekebishwa. Bila shaka, muundo huu wa kisintaksia hulazimisha Excel kufanya kazi zaidi kuliko kubainisha masafa haswa, lakini kichwa hiki cha juu kinaweza kupuuzwa. Tunazungumza juu ya mamia ya sekunde.

Naam, kwenye ukingo wa fikra - kupanga safu katika fomu.

Kwa kutumia chaguo la kukokotoa COLUMN kubainisha safu wima ya kutoa

Ikiwa jedwali ambalo unarejesha data kwa kutumia VLOOKUP lina muundo sawa na jedwali la utafutaji, lakini lina safu mlalo chache tu, basi unaweza kutumia chaguo la kukokotoa la COLUMN () katika VLOOKUP ili kukokotoa nambari za safu wima kiotomatiki. Katika kesi hii, fomula zote za VLOOKUP zitakuwa sawa (kurekebishwa kwa parameter ya kwanza, ambayo inabadilika moja kwa moja)! Kumbuka kuwa parameta ya kwanza ina uratibu wa safu kamili.

Kuunda kitufe cha mchanganyiko na & "|" &

Ikiwa inakuwa muhimu kutafuta kwa nguzo kadhaa kwa wakati mmoja, basi ni muhimu kufanya ufunguo wa composite kwa ajili ya utafutaji. Ikiwa thamani ya kurejesha haikuwa ya maandishi (kama ilivyo kwa uga wa "Msimbo"), lakini nambari, basi fomula ya SUMIFS inayofaa zaidi ingefaa kwa hili na ufunguo wa safu wima haungehitajika kabisa.

Hii ni makala yangu ya kwanza kwa Lifehacker. Ikiwa uliipenda, ninakualika utembelee, na pia usome kwa furaha katika maoni kuhusu siri zako za kutumia kazi ya VLOOKUP na kadhalika. Asante.:)