Werrej
- Fittex fl-Excel minn kriterji multipli
- Aħna estratt it-tieni, it-2, eċċ valuri bl-użu VLOOKUP
- Irkupra r-repetizzjonijiet kollha tal-valur mixtieq
- XNUMXD tfittxija b'ringiela u kolonna magħrufa
- Bl-użu ta 'VLOOKUPs multipli f'formula waħda
- Sostituzzjoni dinamika ta' data minn tabelli differenti bl-użu ta' VLOOKUP u INDIRETT
Fit-tieni parti tat-tutorja tagħna dwar il-funzjoni VPR (VLOOKUP) f'Excel, aħna se nanalizzaw ftit eżempji li jgħinuk tidderieġi s-setgħa kollha VPR biex issolvi l-kompiti l-aktar ambizzjużi ta 'Excel. L-eżempji jassumu li diġà għandek għarfien bażiku dwar kif taħdem din il-karatteristika. Jekk le, jista 'jkun interessat li tibda bl-ewwel parti ta' dan it-tutorja, li tispjega s-sintassi u l-użu bażiku. VPR. Ukoll, ejja nibdew.
Fittex fl-Excel minn kriterji multipli
funzjoni VPR f'Excel hija għodda tassew qawwija biex isiru tfittxijiet għal valur partikolari f'database. Madankollu, hemm limitazzjoni sinifikanti - is-sintassi tagħha tippermettilek tfittex għal valur wieħed biss. X'jiġri jekk trid tfittex minn kundizzjonijiet multipli? Issib is-soluzzjoni hawn taħt.
Eżempju 1: Fittex b'2 kriterji differenti
Ejja ngħidu li għandna lista ta 'ordnijiet u rridu nsibu Kwantità ta' merkanzija (Qty.), ibbażata fuq żewġ kriterji - Isem il-klijent (Klijent) и Isem tal-prodott (Prodott). Il-kwistjoni hija kkumplikata mill-fatt li kull wieħed mix-xerrejja ordna diversi tipi ta 'oġġetti, kif jidher mit-tabella hawn taħt:
funzjoni regolari VPR mhux se jaħdem f'dan ix-xenarju għaliex se jirritorna l-ewwel valur li jsib li jaqbel mal-valur ta' tfittxija mogħti. Per eżempju, jekk trid tkun taf il-kwantità ta 'oġġett Ħelu'ordnat mix-xerrej Jeremy Hill, ikteb il-formula li ġejja:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– din il-formula se tirritorna r-riżultat 15korrispondenti għall-prodott tuffieħ, għax huwa l-ewwel valur li jaqbel.
Hemm soluzzjoni sempliċi - toħloq kolonna addizzjonali li fiha tgħaqqad il-kriterji kollha mixtieqa. Fl-eżempju tagħna, dawn huma l-kolonni Isem il-klijent (Klijent) и Isem tal-prodott (Prodott). Tinsiex li l-kolonna magħquda għandha dejjem tkun il-kolonna l-aktar fuq ix-xellug fil-medda tat-tfittxija, peress li hija l-kolonna tax-xellug li l-funzjoni VPR iħares 'il fuq meta jfittex valur.
Allura, żid kolonna awżiljarja mat-tabella u tikkopja l-formula li ġejja fuq iċ-ċelloli kollha tagħha: =B2&C2. Jekk trid li s-sekwenza tinqara aktar, tista’ tissepara l-valuri magħquda bi spazju: =B2&» «&C2. Wara dan, tista 'tuża l-formula li ġejja:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Fejn hi ċ-ċellula B1 fih il-valur konkatinat tal-argument lookup_value (lookup_value) u 4 – Argument col_index_num (column_number), jiġifieri n-numru tal-kolonna li fiha d-dejta li trid tiġi rkuprata.
Eżempju 2: LOOKUP b'żewġ kriterji b'tabella tidher fuq folja oħra
Jekk għandek bżonn taġġorna t-tabella prinċipali (Tabella prinċipali) billi żżid dejta mit-tieni tabella (Tabella Lookup), li tinsab fuq folja oħra jew f'ktieb tax-xogħol Excel ieħor, allura tista 'tiġbor il-valur mixtieq direttament fil-formula li ddaħħal fit-tabella prinċipali.
Bħal fl-eżempju preċedenti, ser ikollok bżonn kolonna awżiljarja fit-tabella Lookup bil-valuri magħquda. Din il-kolonna trid tkun il-kolonna l-aktar fuq ix-xellug fil-firxa tat-tfittxija.
Allura l-formula ma VPR jista 'jkun bħal dan:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Hawnhekk, il-kolonni B u C fihom ismijiet tal-klijenti u ismijiet tal-prodotti, rispettivament, u l-link Ordnijiet!$A&$2:$D$2 jiddefinixxi tabella biex tfittex f'folja oħra.
Biex tagħmel il-formula aktar leġibbli, tista 'tagħti isem lill-firxa tal-vista, u allura l-formula tidher ħafna aktar sempliċi:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Biex il-formula taħdem, il-valuri fil-kolonna l-aktar fuq ix-xellug tat-tabella li qed tħares lejn għandhom jiġu kkombinati eżattament bl-istess mod bħal fil-kriterji tat-tfittxija. Fil-figura ta 'hawn fuq, għaqqadna l-valuri u2bu2band poġġi spazju bejniethom, bl-istess mod li għandek bżonn tagħmel fl-ewwel argument tal-funzjoni (BXNUMX& "" & CXNUMX).
Ftakar! funzjoni VPR limitata għal 255 karattru, ma tistax tfittex valur li jkun twil aktar minn 255 karattru. Żomm dan f'moħħok u kun żgur li t-tul tal-valur mixtieq ma jaqbiżx dan il-limitu.
Naqbel li ż-żieda ta 'kolonna awżiljarja mhix l-aktar soluzzjoni eleganti u mhux dejjem aċċettabbli. Tista 'tagħmel l-istess mingħajr il-kolonna helper, iżda dan ikun jeħtieġ formula ħafna aktar kumplessa b'kombinazzjoni ta' funzjonijiet INDIĊI (INDIĊI) u MATCH (AKTAR ESPOSTI).
Aħna estratt it-tieni, it-2, eċċ valuri bl-użu VLOOKUP
Diġà taf li VPR jista 'jirritorna valur ta' tqabbil wieħed biss, b'mod aktar preċiż, l-ewwel wieħed misjub. Imma x'jiġri jekk dan il-valur jiġi ripetut diversi drabi fil-firxa meqjusa, u trid tiġbed it-2 jew it-3 minnhom? X'jiġri jekk il-valuri kollha? Il-problema tidher ikkumplikata, imma s-soluzzjoni teżisti!
Ejja ngħidu kolonna waħda tat-tabella fiha l-ismijiet tal-klijenti (Isem tal-Klijent), u l-kolonna l-oħra fiha l-prodotti (Prodott) li xtraw. Ejja nippruvaw insibu t-tieni, it-2 u r-3 oġġetti mixtrija minn klijent partikolari.
L-eħfef mod huwa li żżid kolonna awżiljarja qabel il-kolonna isem tal-klijent u imla bl-ismijiet tal-klijenti bin-numru ta 'ripetizzjoni ta' kull isem, pereżempju, John Doe1, John Doe2 eċċ Aħna se nagħmlu l-trick bin-numerazzjoni bl-użu tal-funzjoni COUNTIF (COUNTIF), peress li l-ismijiet tal-klijenti huma fil-kolonna B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Wara dan tista 'tuża l-funzjoni normali VPRbiex issib l-ordni meħtieġa. Pereżempju:
- Sib 2-th oġġett ordnat mill-klijent Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Sib 3-th oġġett ordnat mill-klijent Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Fil-fatt, tista 'ddaħħal referenza taċ-ċellula bħala l-valur ta' tfittxija minflok it-test, kif muri fil-figura li ġejja:
Jekk qed tfittex biss 2-e ripetizzjoni, tista 'tagħmel dan mingħajr il-kolonna awżiljarja billi toħloq formula aktar kumplessa:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
F'din il-formula:
- $F$2 – ċellula li fiha l-isem tax-xerrej (mhux mibdula, jekk jogħġbok innota – ir-rabta hija assoluta);
- $ B $ – kolonna isem tal-klijent;
- Table4 – Il-mejda tiegħek (dan il-post jista 'jkun ukoll firxa regolari);
- $ C16 – iċ-ċellula tat-tarf tat-tabella jew tal-firxa tiegħek.
Din il-formula ssib biss it-tieni valur ta' tqabbil. Jekk għandek bżonn tiġbed ir-repetizzjonijiet li fadal, uża s-soluzzjoni preċedenti.
Jekk għandek bżonn lista tal-logħbiet kollha - il-funzjoni VPR dan mhuwiex helper, peress li jirritorna biss valur wieħed kull darba – perjodu. Iżda Excel għandu funzjoni INDIĊI (INDIĊI), li jistgħu faċilment ilaħħqu ma' dan il-kompitu. Kif se tidher formula bħal din, titgħallem fl-eżempju li ġej.
Irkupra r-repetizzjonijiet kollha tal-valur mixtieq
Kif imsemmi hawn fuq VPR ma tistax tiġbed il-valuri duplikati kollha mill-firxa skennjata. Biex tagħmel dan, għandek bżonn formula kemmxejn aktar kumplessa, magħmula minn diversi funzjonijiet Excel, bħal INDIĊI (INDIĊI), ŻGĦAR (ŻGĦAR) u ROW (LINJA)
Pereżempju, il-formula hawn taħt issib ir-repetizzjonijiet kollha tal-valur miċ-ċellula F2 fil-medda B2: B16 u tirritorna r-riżultat mill-istess ringieli fil-kolonna Ċ.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Daħħal din il-formula tal-firxa f'ċelluli multipli maġenb, bħaċ-ċelloli F4: F8kif muri fil-figura hawn taħt. In-numru ta' ċelluli għandu jkun daqs jew akbar min-numru massimu possibbli ta' repetizzjonijiet tal-valur imfittex. Tinsiex tikklikkja Ctrl + Shift + Daħħalbiex iddaħħal il-formula tal-firxa b'mod korrett.
Jekk int interessat li tifhem kif taħdem, ejja ngħaddu ftit fid-dettalji tal-formula:
1 Parti:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – qabbel il-valur fiċ-ċellula F2 ma’ kull wieħed mill-valuri fil-medda B2:B16. Jekk tinstab taqbila, allura l-espressjoni STRING(C2:C16)-1 jirritorna n-numru tal-linja korrispondenti (valur -1 jippermettilek li ma tinkludix il-linja tal-header). Jekk ma jkunx hemm logħbiet, il-funzjoni IF (IF) jirritorna string vojta.
Riżultat tal-funzjoni IF (JEKK) se jkun hemm firxa orizzontali bħal din: {1,"",3,"",5,"","","","","","",12,"","",""}
2 Parti:
ROW()-3
СТРОКА()-3
Hawnhekk il-funzjoni ROW (LINE) taġixxi bħala counter addizzjonali. Peress li l-formula hija kkupjata fiċ-ċelloli F4: F9, nnaqqsu n-numru 3 mir-riżultat tal-funzjoni biex tikseb valur 1 fiċ-ċellula F4 (linja 4, naqqas 3) biex tikseb 2 fiċ-ċellula F5 (linja 5, naqqas 3) u l-bqija.
3 Parti:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
funzjoni ŻGĦAR (ŻGĦAR) jirritorna n-oh l-iżgħar valur fil-firxa tad-dejta. Fil-każ tagħna, liema pożizzjoni (mill-iżgħar) li tirritorna hija determinata mill-funzjoni ROW (LINJA) (ara Parti 2). Allura, għal ċellula F4 funzjoni ŻGĦAR({array},1) Denunzji 1-th (iżgħar) element array, ie 1. Għaċ-ċellula F5 Denunzji 2-th l-iżgħar element fil-firxa, jiġifieri 3, Eċċ
4 Parti:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
funzjoni INDIĊI (INDIĊI) sempliċement jirritorna l-valur ta 'ċellula speċifika f'firxa C2:C16. Għaċ-ċellula F4 funzjoni INDIĊI($C$2:$C$16) jirritorna tuffieħgħall F5 funzjoni INDIĊI($C$2:$C$16) jirritorna Ħelu' u l-bqija.
5 Parti:
IFERROR()
ЕСЛИОШИБКА()
Fl-aħħarnett, inpoġġu l-formula ġewwa l-funzjoni JEKK Żball (IFERROR), għax mhux probabbli li tkun kuntent bil-messaġġ ta' żball #AT (#N/A) jekk in-numru ta’ ċelloli li fihom tiġi kkupjata l-formula huwa inqas min-numru ta’ valuri duplikati fil-medda li qed tara.
XNUMXD tfittxija b'ringiela u kolonna magħrufa
It-twettiq ta 'tfittxija XNUMXD f'Excel jinvolvi tfittxija għal valur b'numru ta' ringiela u kolonna magħrufa. Fi kliem ieħor, qed tiġbed il-valur taċ-ċellula fl-intersezzjoni ta 'ringiela u kolonna partikolari.
Allura, ejja nduru għat-tabella tagħna u ikteb formula b'funzjoni VPR, li se ssib informazzjoni dwar l-ispiża tal-lumi mibjugħa f'Marzu.
Hemm diversi modi kif twettaq tfittxija XNUMXD. Iċċekkja l-għażliet u agħżel dik li jaqbillek l-aħjar.
Funzjonijiet VLOOKUP u MATCH
Tista 'tuża mazz ta' funzjonijiet VPR (LOOKUP) u AKTAR ESPOSTI (MATCH) biex issib il-valur fl-intersezzjoni tal-oqsma Isem tal-prodott (sekwenza) u Xahar (kolonna) tal-firxa inkwistjoni:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Il-formula hawn fuq hija funzjoni regolari VPR, li tfittex taqbila eżatta tal-valur "Lumi" fiċ-ċelloli A2 sa A9. Imma peress li ma tafx f'liema kolonna hemm il-bejgħ ta 'Marzu, ma tkunx tista' tissettja n-numru tal-kolonna għat-tielet argument tal-funzjoni. VPR. Minflok, tintuża l-funzjoni AKTAR ESPOSTIbiex tiddefinixxi din il-kolonna.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Tradotta fil-lingwa umana, din il-formula tfisser:
- Qegħdin infittxu l-karattri “Mar” – argument lookup_value (lookup_value);
- Tfittex fiċ-ċelloli minn A1 sa I1 – argument lookup_array (lookup_array);
- Ritorn taqbila eżatta - argument match_type (match_type).
Bl-użu 0 fit-tielet argument, inti tgħid funzjonijiet AKTAR ESPOSTI tfittex l-ewwel valur li jaqbel eżatt mal-valur li qed tfittex. Dan huwa ekwivalenti għall-valur FALZ (FALZ) għar-raba' argument VPR.
Dan huwa kif tista 'toħloq formula ta' tfittxija f'żewġ direzzjonijiet f'Excel, magħrufa wkoll bħala tfittxija bidimensjonali jew tfittxija bidirezzjonali.
Funzjoni SUMPRODUCT
funzjoni SUMPRODUCT (SUMPRODUCT) jirritorna s-somma tal-prodotti tal-matriċi magħżula:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
funzjonijiet INDIĊI u MATCH
Fl-artiklu li jmiss se nispjega dawn il-funzjonijiet fid-dettall, għalhekk għalissa tista 'tikkopja din il-formula:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Firxiet imsemmija u l-operatur tal-intersezzjoni
Jekk m'intix f'dawk il-formuli Excel kumplessi kollha, tista 'tħobb dan il-mod viżwali u memorabbli:
- Agħżel it-tabella, iftaħ it-tab Formuli (Formuli) u kklikkja Oħloq mill-Għażla (Oħloq mill-għażla).
- Iċċekkja l-kaxxi Ringiela ta’ fuq (fuq il-linja hawn fuq) u Kolonna tax-xellug (fil-kolonna fuq ix-xellug). Microsoft Excel se jassenja ismijiet lill-firxiet mill-valuri fir-ringiela ta 'fuq u l-kolonna tax-xellug tal-ispreadsheet tiegħek. Issa tista 'tfittex billi tuża dawn l-ismijiet direttament mingħajr ma toħloq formuli.
- Fi kwalunkwe ċella vojta, ikteb =row_name isem_kolonna, pereżempju bħal dan:
=Lumi Mar
… jew viċi versa:
=Mar Lemons
Ftakar li l-ismijiet tar-ringieli u tal-kolonni għandhom ikunu separati bi spazju, li f'dan il-każ jaħdem bħall-operatur tal-intersezzjoni.
Meta ddaħħal isem, Microsoft Excel se juri tooltip b'lista ta' ismijiet li jaqblu, bħal meta ddaħħal formula.
- istampa Ikteb u iċċekkja r-riżultat
B'mod ġenerali, liema mill-metodi ta 'hawn fuq tagħżel, ir-riżultat ta' tfittxija bidimensjonali se jkun l-istess:
Bl-użu ta 'VLOOKUPs multipli f'formula waħda
Jiġri li t-tabella prinċipali u t-tabella ta' tfittxija m'għandhomx kolonna waħda komuni, u dan ma jħallikx tuża l-funzjoni tas-soltu VPR. Madankollu, hemm tabella oħra li ma fiha l-informazzjoni li aħna interessati fiha, iżda għandha kolonna komuni mat-tabella prinċipali u t-tabella ta 'tfittxija.
Ejja nagħtu ħarsa lejn l-eżempju li ġej. Għandna tabella Prinċipali b'kolonna SKU (ġdid), fejn trid iżżid kolonna bil-prezzijiet korrispondenti minn tabella oħra. Barra minn hekk, għandna 2 tabelli lookup. L-ewwel waħda (Tabella Lookup 1) fiha numri aġġornati SKU (ġdid) u l-ismijiet tal-prodotti, u t-tieni (Tabella Lookup 2) - ismijiet tal-prodotti u numri qodma SKU (antik).
Biex iżżid il-prezzijiet mit-tieni tabella ta' tfittxija mat-tabella prinċipali, trid twettaq azzjoni magħrufa bħala doppja VPR jew ibejt VPR.
- Ikteb funzjoni VPR, li ssib l-isem tal-prodott fit-tabella Tabella ta' tfittxija 1jużaw SKU, bħala l-valur mixtieq:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Hawnhekk New_SKU – firxa msemmija $A:$B fit-tabella Tabella ta' tfittxija 1, 2 – din hija l-kolonna B, li fiha l-ismijiet tal-merkanzija (ara l-istampa hawn fuq)
- Ikteb formula biex daħħal il-prezzijiet minn tabella Tabella ta' tfittxija 2 ibbażat fuq ismijiet ta’ prodotti magħrufa. Biex tagħmel dan, waħħal il-formula li ħloqt qabel bħala l-valur ta 'tfittxija għall-funzjoni l-ġdida VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Hawnhekk prezz – firxa msemmija $A:$C fit-tabella Tabella ta' tfittxija 2, 3 hija l-kolonna Ċ li fiha l-prezzijiet.
Il-figura hawn taħt turi r-riżultat ritornat mill-formula li ħloqna:
Sostituzzjoni dinamika ta' data minn tabelli differenti bl-użu ta' VLOOKUP u INDIRETT
L-ewwel, ejja niċċaraw xi ngħidu bl-espressjoni "Sostituzzjoni dinamika ta' data minn tabelli differenti" biex niżguraw li nifhmu lil xulxin b'mod korrett.
Hemm sitwazzjonijiet meta hemm diversi folji b'dejta tal-istess format, u huwa meħtieġ li tiġi estratta l-informazzjoni meħtieġa minn ċerta folja, skont il-valur li jiddaħħal f'ċellula partikolari. Naħseb li huwa aktar faċli li tispjega dan b'eżempju.
Immaġina li għandek rapporti tal-bejgħ għal diversi reġjuni bl-istess prodotti u fl-istess format. Trid issib ċifri tal-bejgħ għal reġjun speċifiku:
Jekk għandek biss żewġ rapporti bħal dawn, allura tista 'tuża formula disgracefully sempliċi b'funzjonijiet VPR и IF (IF) biex tagħżel ir-rapport mixtieq biex tfittex:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
fejn:
- $D$2 hija ċellola li fiha l-isem tal-prodott. Innota li aħna nużaw referenzi assoluti hawn biex nevitaw li nbiddlu l-valur ta 'tfittxija meta nikkopja l-formula għal ċelloli oħra.
- $D3 hija ċellola bl-isem tar-reġjun. Qed nużaw referenza ta 'kolonna assoluta u referenza ta' ringiela relattiva għaliex qed nippjanaw li nikkopjaw il-formula għal ċelloli oħra fl-istess kolonna.
- FL_Sales и CA_Bejgħ – l-ismijiet tat-tabelli (jew firxiet bl-isem) li fihom ir-rapporti tal-bejgħ korrispondenti. Tista', ovvjament, tuża l-ismijiet tal-folji tas-soltu u r-referenzi tal-firxa taċ-ċelluli, pereżempju 'FL Sheet'!$A$3:$B$10, iżda firxiet imsemmija huma ħafna aktar konvenjenti.
Madankollu, meta jkun hemm ħafna tabelli bħal dawn, il-funzjoni IF mhix l-aħjar soluzzjoni. Minflok, tista 'tuża l-funzjoni INDIRETTA (INDIRETT) biex tirritorna l-firxa ta’ tfittxija mixtieqa.
Kif probabilment taf, il-funzjoni INDIRETTA huwa użat biex jirritorna link mogħtija minn string ta 'test, li huwa eżattament dak li għandna bżonn issa. Allura, ibdel bil-kuraġġ fil-formula ta 'hawn fuq l-espressjoni bil-funzjoni IF li torbot mal-funzjoni INDIRETTA. Hawnhekk hawn kombinazzjoni VPR и INDIRETTA jaħdem tajjeb ma':
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
fejn:
- $D$2 – din hija ċellola bl-isem tal-prodott, mhix mibdula minħabba r-rabta assoluta.
- $D3 hija ċ-ċellula li fiha l-ewwel parti tal-isem tar-reġjun. Fl-eżempju tagħna, dan FL.
- _Bejgħ – il-parti komuni tal-isem tal-firxiet jew it-tabelli kollha msemmija. Meta kkombinat mal-valur fiċ-ċellula D3, tifforma l-isem kwalifikat bis-sħiħ tal-firxa meħtieġa. Hawn taħt hawn xi dettalji għal dawk li huma ġodda għall-funzjoni INDIRETTA.
Kif jaħdmu INDIRETTI u VLOOKUP
L-ewwel, ħalluni nfakkarkom is-sintassi tal-funzjoni INDIRETTA (INDIRETT):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
L-ewwel argument jista 'jkun referenza ta' ċellula (stil A1 jew R1C1), isem ta 'firxa, jew sekwenza ta' test. It-tieni argument jiddetermina liema stil ta 'rabta jinsab fl-ewwel argument:
- A1jekk l-argument hu KODIĊI VERU (VERU) jew mhux speċifikat;
- R1C1, Jekk FKIF E (FALZ).
Fil-każ tagħna, il-link għandha l-istil A1, sabiex inti tista 'tħalli barra t-tieni argument u tiffoka fuq l-ewwel.
Mela ejja nerġgħu lura għar-rapporti tal-bejgħ tagħna. Jekk tiftakar, allura kull rapport huwa tabella separata li tinsab fuq folja separata. Biex il-formula taħdem b'mod korrett, trid issemmi t-tabelli (jew il-firxiet) tiegħek, u l-ismijiet kollha għandu jkollhom parti komuni. Per eżempju, bħal dan: CA_Bejgħ, FL_Bejgħ, TX_Bejgħ u l-bqija. Kif tistgħu taraw, "_Sales" hija preżenti fl-ismijiet kollha.
funzjoni INDIRETTA jgħaqqad il-valur fil-kolonna D u s-sekwenza tat-test "_Sales", u b'hekk tgħid VPR f'liema tabella tfittex. Jekk iċ-ċellula D3 fiha l-valur "FL", il-formula tfittex it-tabella FL_Bejgħ, jekk “CA” – fit-tabella CA_Bejgħ u l-bqija.
Ir-riżultat tal-funzjonijiet VPR и INDIRETTA se jkun dan li ġej:
Jekk id-dejta tinsab f'kotba Excel differenti, allura trid iżżid l-isem tal-ktieb qabel il-firxa msemmija, pereżempju:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Jekk il-funzjoni INDIRETTA jirreferi għal ktieb tax-xogħol ieħor, dak il-ktieb tax-xogħol irid ikun miftuħ. Jekk tkun magħluqa, il-funzjoni tirrapporta żball. # REF! (#SSIL!).