Kwa kuchanganya chaguo za kukokotoa za VLOOKUP za Excel na chaguo za kukokotoa za COLUMN unaweza kuunda fomula ya kutafuta ambayo inarudisha thamani nyingi kutoka kwa safu mlalo moja ya hifadhidata au jedwali la data. Jifunze jinsi ya kuunda fomula ya utafutaji ambayo inarudisha thamani nyingi kutoka kwa rekodi moja ya data.
Maagizo katika makala haya yanatumika kwa Excel 2019, 2016, 2013, 2010; na Excel kwa Microsoft 365.
Mstari wa Chini
Mfumo wa kutafuta unahitaji chaguo za kukokotoa za COLUMN ili kuwekwa ndani ya VLOOKUP. Kuweka kitendakazi kunahusisha kuingiza chaguo za kukokotoa la pili kama mojawapo ya hoja za chaguo za kukokotoa za kwanza.
Ingiza Data ya Mafunzo
Katika somo hili, chaguo la kukokotoa la COLUMN limeingizwa kama hoja ya nambari ya safu wima ya VLOOKUP. Hatua ya mwisho katika somo inahusisha kunakili fomula ya utafutaji kwenye safu wima za ziada ili kupata thamani za ziada za sehemu iliyochaguliwa.
Hatua ya kwanza katika somo hili ni kuingiza data kwenye lahakazi ya Excel. Ili kufuata hatua katika somo hili, weka data iliyoonyeshwa kwenye picha hapa chini kwenye visanduku vifuatavyo:
- Ingiza safu ya juu ya data kwenye visanduku D1 hadi G1.
- Ingiza safu ya pili kwenye visanduku vya D4 hadi G10.
Vigezo vya utafutaji na fomula ya utafutaji iliyoundwa katika mafunzo haya imeingizwa katika safu mlalo ya 2 ya lahakazi.
Mafunzo haya hayajumuishi umbizo msingi la Excel lililoonyeshwa kwenye picha, lakini hii haiathiri jinsi fomula ya utafutaji inavyofanya kazi.
Unda Masafa Iliyopewa Jina kwa Jedwali la Data
Fungu la visanduku lililotajwa ni njia rahisi ya kurejelea anuwai ya data katika fomula. Badala ya kuandika marejeleo ya kisanduku cha data, andika jina la masafa.
Faida ya pili ya kutumia safu iliyotajwa ni kwamba marejeleo ya kisanduku cha safu hii hayabadiliki hata wakati fomula inakiliwa kwenye visanduku vingine katika lahakazi. Majina ya safu ni mbadala wa kutumia marejeleo kamili ya seli ili kuzuia hitilafu wakati wa kunakili fomula.
Jina la safu halijumuishi vichwa au majina ya sehemu za data (kama inavyoonyeshwa katika safu mlalo ya 4), data pekee.
-
Angazia kisanduku D5 hadi G10 katika laha kazi.
Image -
Weka kishale kwenye Kisanduku cha Jina kilicho juu ya safu wima A, andika Jedwali, kisha ubonyeze Enter. Visanduku D5 hadi G10 vina jina la masafa ya Jedwali.
Image - Jina la masafa kwa hoja ya safu ya jedwali ya VLOOKUP itatumika baadaye katika mafunzo haya.
Fungua Sanduku la Maongezi ya VLOOKUP
Ingawa inawezekana kuchapa fomula ya kutafuta moja kwa moja kwenye kisanduku katika lahakazi, watu wengi wanaona vigumu kuweka sintaksia sawa - hasa kwa fomula changamano kama ile iliyotumika katika mafunzo haya.
Kama njia mbadala, tumia kisanduku cha mazungumzo cha Hoja za Kazi ya VLOOKUP. Takriban vitendaji vyote vya Excel vina kisanduku cha mazungumzo ambapo kila hoja za chaguo za kukokotoa huwekwa kwenye mstari tofauti.
-
Chagua seli E2 ya laha kazi. Hapa ndipo mahali ambapo matokeo ya fomula ya kuangalia pande mbili yataonyeshwa.
Image -
Kwenye utepe, nenda kwenye kichupo cha Mfumo na uchague Tafuta na Rejelea..
Image -
Chagua VLOOKUP ili kufungua Hoja za Kazi kisanduku cha mazungumzo.
Image - Kisanduku cha mazungumzo cha Hoja za Kazi ndipo vigezo vya chaguo la kukokotoa vya VLOOKUP huingizwa.
Ingiza Hoja ya Thamani ya Kutafuta
Kwa kawaida, thamani ya utafutaji inalingana na sehemu ya data katika safu wima ya kwanza ya jedwali la data. Katika mfano huu, thamani ya kuangalia inarejelea jina la sehemu unayotaka kupata habari. Aina zinazoruhusiwa za data kwa thamani ya utafutaji ni data ya maandishi, thamani za kimantiki, nambari na marejeleo ya seli.
Marejeleo Kabisa ya Kisanduku
Fomula zinaponakiliwa katika Excel, marejeleo ya seli hubadilika ili kuonyesha eneo jipya. Hili likitokea, D2, rejeleo la kisanduku cha thamani ya utafutaji, hubadilika na kuunda hitilafu katika visanduku F2 na G2.
Marejeleo kamili ya seli hayabadiliki fomula zinaponakiliwa.
Ili kuzuia hitilafu, badilisha rejeleo la kisanduku D2 kuwa rejeleo kamili la kisanduku. Ili kuunda rejeleo kamili la seli, bonyeza kitufe cha F4. Hii huongeza ishara za dola karibu na rejeleo la seli kama vile $D$2.
-
Kwenye kisanduku cha mazungumzo cha Hoja za Kazi, weka kishale kwenye kisanduku cha maandishi cha thamani_ya_tafuta. Kisha, katika laha ya kazi, chagua seli D2 ili kuongeza rejeleo hili la kisanduku kwenye thamani_ya_kutafuta. Seli D2 ndipo jina la sehemu litawekwa.
Image -
Bila kusogeza sehemu ya kuwekea, bonyeza F4 kitufe ili kubadilisha D2 hadi marejeleo kamili ya kisanduku $D$2.
Image - Acha kisanduku kidadisi cha chaguo za kukokotoa cha VLOOKUP wazi kwa hatua inayofuata katika mafunzo.
Ingiza Hoja ya Mkusanyiko wa Jedwali
Mkusanyiko wa jedwali ni jedwali la data ambalo fomula ya utafutaji hutafuta ili kupata taarifa unayotaka. Safu ya jedwali lazima iwe na angalau safu wima mbili za data.
Safu wima ya kwanza ina hoja ya thamani ya utafutaji (ambayo iliwekwa katika sehemu iliyotangulia), huku safu wima ya pili hutafutwa kwa fomula ya utafutaji ili kupata maelezo unayobainisha.
Hoja ya safu ya jedwali lazima iwekwe kama safu iliyo na marejeleo ya seli kwa jedwali la data au kama jina la safu.
Ili kuongeza jedwali la data kwenye chaguo la kukokotoa la VLOOKUP, weka kishale kwenye kisanduku cha maandishi cha meza_ya_jedwali kwenye kisanduku cha mazungumzo na uandike Jedwalikuingiza jina la safu ya hoja hii.
Nest the COLUMN Function
Kwa kawaida, VLOOKUP hurejesha tu data kutoka safu wima moja ya jedwali la data. Safu wima hii imewekwa na hoja ya nambari ya safu wima. Katika mfano huu, hata hivyo, kuna safu wima tatu, na nambari ya faharasa ya safuwima inahitaji kubadilishwa bila kuhariri fomula ya kuangalia. Ili kukamilisha hili, weka kitendakazi cha COLUMN ndani ya kitendakazi cha VLOOKUP kama hoja_ya_idadi_ya_nambari.
Wakati wa kuunda kiota, Excel haifungui kisanduku cha mazungumzo cha chaguo la pili ili kuweka hoja zake. Chaguo za kukokotoa za COLUMN lazima ziingizwe wewe mwenyewe. Chaguo la kukokotoa la COLUMN lina hoja moja tu, hoja ya Marejeleo, ambayo ni marejeleo ya ngeli.
Kitendakazi cha COLUMN hurejesha nambari ya safu wima iliyotolewa kama hoja ya Marejeleo. Inabadilisha herufi ya safu wima kuwa nambari.
Ili kupata bei ya bidhaa, tumia data iliyo katika safu wima ya 2 ya jedwali la data. Mfano huu unatumia safu wima B kama Rejeleo ili kuingiza 2 kwenye hoja_ya_num_nambari.
-
Kwenye kisanduku cha mazungumzo cha Hoja za Kazi, weka kishale kwenye Nambari_ya_index_nambari kisanduku cha maandishi na uandike COLUMN(. (Hakikisha umejumuisha mabano ya mviringo yaliyo wazi.)
Image -
Kwenye laha ya kazi, chagua seli B1 ili kuingiza rejeleo hilo la kisanduku kama hoja ya Marejeleo.
Image - Charaza mabano ya duara ya kufunga ili kukamilisha kitendakazi cha COLUMN.
Ingiza Hoja ya Kutafuta Masafa ya VLOOKUP
Hoja_ya_Utafutaji_wa_VLOOKUP ni thamani ya kimantiki (TRUE au FALSE) ambayo inaonyesha kama VLOOKUP inapaswa kupata thamani halisi au takriban inayolingana na thamani_ya_Kutafuta.
- TRUE au Imeachwa: VLOOKUP hurejesha uwiano wa karibu kwa thamani_ya_Kutafuta. Ikiwa inayolingana kabisa haijapatikana, VLOOKUP hurejesha thamani inayofuata kubwa zaidi. Data katika safu wima ya kwanza ya Table_array lazima ipangwe kwa mpangilio wa kupanda.
- SIYO: VLOOKUP hutumia ulinganifu kamili na thamani_ya_Kutafuta. Ikiwa kuna thamani mbili au zaidi katika safu wima ya kwanza ya Table_array inayolingana na thamani ya utafutaji, thamani ya kwanza inayopatikana inatumiwa. Ikiwa haipatikani inayolingana kabisa, hitilafu ya N/A itarejeshwa.
Katika somo hili, maelezo mahususi kuhusu kipengee fulani cha maunzi yataangaliwa, kwa hivyo Range_lookup imewekwa kuwa FALSE.
Kwenye kisanduku cha mazungumzo cha Hoja za Kazi, weka kishale kwenye kisanduku cha maandishi cha Range_lookup na uandike Si kweli ili kuwaambia VLOOKUP irudishe data inayolingana kabisa.
Chagua Sawa ili kukamilisha fomula ya kutafuta na kufunga kisanduku cha mazungumzo. Kisanduku E2 kitakuwa na hitilafu ya N/A kwa sababu kigezo cha kutafuta hakijawekwa kwenye kisanduku D2. Hitilafu hii ni ya muda. Itasahihishwa wakati kigezo cha utafutaji kitakapoongezwa katika hatua ya mwisho ya mafunzo haya.
Nakili Mfumo wa Kutafuta na Uweke Vigezo
Mfumo wa kutafuta hurejesha data kutoka safu wima nyingi za jedwali la data kwa wakati mmoja. Ili kufanya hivyo, fomula ya utafutaji lazima iwe katika sehemu zote ambazo ungependa habari kutoka.
Ili kuepua data kutoka safu wima 2, 3, na 4 za jedwali la data (bei, nambari ya sehemu, na jina la mtoa huduma), weka jina nusu kama Thamani_ya_Kutafuta.
Kwa kuwa data imewekwa katika mchoro wa kawaida katika lahakazi, nakili fomula ya kutafuta katika kisanduku E2 hadi seli F2 na G2 Formula inaponakiliwa, Excel husasisha rejeleo la kisanduku linganishi katika chaguo za kukokotoa za COLUMN (kisanduku B1) ili kuonyesha eneo jipya la fomula. Excel haibadilishi rejeleo kamili la kisanduku (kama vile $D$2) na fungu la visanduku lililotajwa (Jedwali) jinsi fomula inavyonakiliwa.
Kuna zaidi ya njia moja ya kunakili data katika Excel, lakini njia rahisi ni kutumia Fill Handle.
-
Chagua seli E2, mahali fomula ya kutafuta inapatikana, ili kuifanya kisanduku amilifu.
Image -
Buruta mpini wa kujaza hadi kwenye seli G2. Visanduku F2 na G2 vinaonyesha hitilafu ya N/A ambayo ipo katika kisanduku E2.
Image -
Ili kutumia fomula za kutafuta kupata taarifa kutoka kwa jedwali la data, katika lahakazi chagua seli D2, andika Widget, na ubonyeze Ingiza.
Image Maelezo yafuatayo yanaonekana katika visanduku E2 hadi G2.
- E2: $14.76 - bei ya wijeti
- F2: PN-98769 - nambari ya sehemu ya wijeti
- G2: Widgets Inc. - jina la msambazaji wa wijeti
-
Ili kujaribu fomula ya safu ya VLOOKUP, andika jina la sehemu zingine kwenye seli D2 na uangalie matokeo katika seli E2 hadi G2.
Image - Kila seli iliyo na fomula ya kutafuta ina data tofauti kuhusu kipengee cha maunzi ulichotafuta.
Kitendo cha kukokotoa cha VLOOKUP chenye vitendaji vilivyowekwa kiota kama COLUMN hutoa mbinu thabiti ya kutafuta data ndani ya jedwali, kwa kutumia data nyingine kama marejeleo ya utafutaji.
