Kaundan
- Pagpangita sa Excel pinaagi sa daghang mga pamatasan
- Gikuha namo ang 2nd, 3rd, etc. values gamit ang VLOOKUP
- Kuhaa ang tanan nga mga pagbalik-balik sa gitinguha nga kantidad
- XNUMXD nga pagpangita pinaagi sa nahibal-an nga linya ug kolum
- Paggamit sa daghang mga VLOOKUP sa usa ka pormula
- Dinamikong pagpuli sa datos gikan sa lain-laing mga lamesa gamit ang VLOOKUP ug INDIRECT
Sa ikaduhang bahin sa among tutorial sa function VPR (VLOOKUP) sa Excel, among analisahon ang pipila ka mga pananglitan nga makatabang kanimo sa pagdumala sa tanan nga gahum VPR aron masulbad ang labing ambisyoso nga mga buluhaton sa Excel. Ang mga pananglitan nagtuo nga aduna ka nay sukaranan nga kahibalo kung giunsa kini nga bahin molihok. Kung dili, mahimong interesado ka sa pagsugod sa una nga bahin sa kini nga panudlo, nga nagpatin-aw sa syntax ug sukaranan nga paggamit. VPR. Aw, magsugod na ta.
Pagpangita sa Excel pinaagi sa daghang mga pamatasan
function VPR sa Excel usa ka gamhanan nga himan alang sa pagpangita alang sa usa ka partikular nga bili sa usa ka database. Bisan pa, adunay usa ka hinungdanon nga limitasyon - ang syntax niini nagtugot kanimo sa pagpangita alang sa usa ra ka kantidad. Unsa kaha kung gusto nimo pangitaon pinaagi sa daghang mga kondisyon? Makita nimo ang solusyon sa ubos.
Pananglitan 1: Pangitaa pinaagi sa 2 ka lain-laing criteria
Ibutang ta nga naa tay listahan sa mga order ug gusto namong pangitaon Gidaghanon sa mga butang (Qty.), base sa duha ka criteria - Ngalan sa kliyente (Customer) ug Ngalan sa produkto (Produkto). Ang butang komplikado sa kamatuoran nga ang matag usa sa mga pumapalit nag-order sa pipila ka mga matang sa mga butang, ingon sa makita gikan sa lamesa sa ubos:
regular nga function VPR dili molihok sa kini nga senaryo tungod kay ibalik niini ang una nga kantidad nga nakit-an nga katumbas sa gihatag nga kantidad sa pagpangita. Pananglitan, kung gusto nimo mahibal-an ang gidaghanon sa usa ka butang Mga tam-is'gi-order sa pumapalit Jeremy Hill, isulat ang mosunod nga pormula:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– kini nga pormula ibalik ang resulta 15katumbas sa produkto mansanas, tungod kay kini ang unang bili nga motakdo.
Adunay usa ka yano nga workaround - paghimo usa ka dugang nga kolum diin isagol ang tanan nga gusto nga pamatasan. Sa among pananglitan, kini ang mga kolum Ngalan sa kliyente (Customer) ug Ngalan sa produkto (Produkto). Ayaw kalimti nga ang gihiusa nga kolum kinahanglan kanunay nga ang pinakawala nga kolum sa hanay sa pagpangita, tungod kay kini ang wala nga kolum nga ang function VPR motan-aw sa dihang mangita og bili.
Busa, magdugang ka ug auxiliary nga kolum sa lamesa ug kopyaha ang mosunod nga pormula sa tanang mga selula niini: =B2 ug C2. Kung gusto nimo nga mas mabasa ang hilo, mahimo nimong ibulag ang hiniusa nga mga kantidad nga adunay usa ka wanang: =B2&» «&C2. Human niana, mahimo nimong gamiton ang mosunod nga pormula:
=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;ЛОЖЬ)
Asa ang selda B1 naglangkob sa concatenated nga bili sa argumento lookup_value (lookup_value) ug 4 – Pangatarungan col_index_num (column_number), ie ang numero sa column nga adunay sulod nga data nga makuha.
Panig-ingnan 2: VLOOKUP pinaagi sa duha ka criteria nga ang lamesa gitan-aw sa laing sheet
Kung kinahanglan nimo nga i-update ang main table (Main table) pinaagi sa pagdugang sa datos gikan sa ikaduhang lamesa (Lookup table), nga nahimutang sa laing sheet o sa laing Excel workbook, nan mahimo nimong kolektahon ang gusto nga bili direkta sa pormula nga imong gisal-ot ngadto sa main table.
Sama sa miaging pananglitan, magkinahanglan ka og auxiliary column sa Lookup table nga adunay hiniusa nga mga kantidad. Kini nga kolum kinahanglan nga ang pinakawala nga kolum sa hanay sa pagpangita.
Busa ang pormula sa VPR mahimong ingon niini:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Dinhi, ang mga kolum B ug C adunay mga ngalan sa kustomer ug mga ngalan sa produkto, matag usa, ug ang link Mga order!$A&$2:$D$2 naghubit sa usa ka lamesa nga pangitaon sa laing panid.
Aron mahimo nga mas mabasa ang pormula, mahimo nimong hatagan ang sakup sa pagtan-aw usa ka ngalan, ug unya ang pormula mas simple tan-awon:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Aron molihok ang pormula, ang mga kantidad sa labing wala nga kolum sa lamesa nga imong gitan-aw kinahanglan nga gihiusa sa parehas nga paagi sama sa mga pamatasan sa pagpangita. Sa numero sa ibabaw, gihiusa namon ang mga kantidad nga u2bu2band nagbutang usa ka wanang sa taliwala nila, sa parehas nga paagi nga kinahanglan nimo buhaton sa una nga argumento sa function (BXNUMX& "" & CXNUMX).
Hinumdomi! function VPR limitado sa 255 ka mga karakter, dili kini makapangita sa usa ka kantidad nga labaw sa 255 ka mga karakter ang gitas-on. Hinumdumi kini ug siguroha nga ang gitas-on sa gitinguha nga kantidad dili molapas sa kini nga limitasyon.
Mouyon ko nga ang pagdugang og auxiliary column dili ang pinaka elegante ug dili kanunay madawat nga solusyon. Mahimo nimo ang parehas nga wala ang kolum sa katabang, apan magkinahanglan kana usa ka labi ka komplikado nga pormula nga adunay kombinasyon sa mga gimbuhaton INDEX (INDEX) ug MATCH (MORE EXPOSED).
Gikuha namo ang 2nd, 3rd, etc. values gamit ang VLOOKUP
Nahibal-an na nimo kana VPR makabalik lang ug usa ka katumbas nga kantidad, mas tukma, ang una nga nakit-an. Apan unsa man kon kini nga bili gisubli sa makadaghang higayon sa gitan-aw nga laray, ug gusto nimo nga makuha ang ika-2 o ika-3 niini? Unsa kaha kung ang tanan nga mga kantidad? Ang problema morag komplikado, apan ang solusyon anaa!
Ibutang ta nga ang usa ka kolum sa lamesa adunay mga ngalan sa mga kustomer (Ngalan sa Kustomer), ug ang lain nga kolum adunay mga produkto (Produkto) nga ilang gipalit. Atong sulayan pagpangita ang ika-2, ika-3 ug ika-4 nga butang nga gipalit sa usa ka kustomer.
Ang pinakasayon nga paagi mao ang pagdugang og auxiliary column sa atubangan sa column ngalan sa kustomer ug pun-a kini sa mga ngalan sa kustomer sa numero sa pagbalik-balik sa matag ngalan, pananglitan, John Doe1, John Doe2 ug uban pa. Atong buhaton ang lansis sa pag-numero gamit ang function COUNTIF (COUNTIF), tungod kay ang mga ngalan sa kustomer anaa sa kolum B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Human niana mahimo nimong gamiton ang normal nga function VPRaron makit-an ang gikinahanglan nga order. Pananglitan:
- Pangitaa ang 2-ika butang nga gi-order sa kustomer Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Pangitaa ang 3-ika butang nga gi-order sa kustomer Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Sa tinuud, mahimo nimong isulod ang usa ka reference sa cell ingon nga kantidad sa pagpangita imbis nga teksto, ingon sa gipakita sa mosunod nga numero:
Kung nangita ka lang 2-e pagsubli, mahimo nimo kini nga wala ang auxiliary nga kolum pinaagi sa paghimo og mas komplikado nga pormula:
=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;ИСТИНА);"")
Niini nga pormula:
- $F$2 - usa ka cell nga adunay ngalan sa pumapalit (wala kini mausab, palihug timan-i - ang link hingpit);
- $B $ - kolum ngalan sa kustomer;
- Table4 – Ang imong lamesa (kini nga dapit mahimo usab nga usa ka regular nga range);
- $C16 – ang end cell sa imong lamesa o range.
Kini nga pormula makit-an ra ang ikaduha nga katumbas nga kantidad. Kung kinahanglan nimo nga makuha ang nahabilin nga mga pagbalik-balik, gamita ang miaging solusyon.
Kung kinahanglan nimo ang usa ka lista sa tanan nga mga posporo - ang function VPR kini dili usa ka katabang, tungod kay kini nagbalik lamang sa usa ka kantidad sa usa ka panahon - yugto. Apan ang Excel adunay function INDEX (INDEX), nga daling makasagubang niini nga buluhaton. Kung unsa ang hitsura sa ingon nga pormula, mahibal-an nimo sa mosunod nga pananglitan.
Kuhaa ang tanan nga mga pagbalik-balik sa gitinguha nga kantidad
Sama sa gihisgutan sa ibabaw VPR dili makuha ang tanan nga mga doble nga kantidad gikan sa gi-scan nga sakup. Aron mahimo kini, kinahanglan nimo ang usa ka gamay nga labi ka komplikado nga pormula, nga gilangkoban sa daghang mga gimbuhaton sa Excel, sama sa INDEX (INDEX), TANAN (GAMAY) ug ROW (LINYA)
Pananglitan, ang pormula sa ubos makit-an ang tanan nga pagbalik-balik sa kantidad gikan sa cell F2 sa range B2: B16 ug ibalik ang resulta gikan sa parehas nga mga linya sa kolum C.
{=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));"")}
Isulod kini nga pormula sa array ngadto sa daghang kasikbit nga mga selula, sama sa mga selula F4: F8ingon sa gipakita sa hulagway sa ubos. Ang gidaghanon sa mga selula kinahanglan nga katumbas o mas dako pa sa labing taas nga posible nga gidaghanon sa mga pagbalik-balik sa gipangita nga bili. Ayaw kalimot sa pag-klik Ctrl + Shift + Entersa pagsulod sa array formula sa hustong paagi.
Kung interesado ka nga masabtan kung giunsa kini molihok, susiha ang mga detalye sa pormula sa gamay:
Bahin 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - itandi ang kantidad sa cell F2 sa matag usa sa mga kantidad sa range B2:B16. Kung makit-an ang usa ka posporo, nan ang ekspresyon STRING(C2:C16)-1 ibalik ang numero sa katumbas nga linya (value -1 nagtugot kanimo nga dili iapil ang linya sa ulohan). Kung walay mga tugma, ang function IF (IF) mibalik og walay sulod nga pisi.
Resulta sa function IF (KUNG) adunay ingon nga pinahigda nga laray: {1,"",3,"",5,"","","","","","",12,"","",""}
Bahin 2:
ROW()-3
СТРОКА()-3
Dinhi ang function ROW (LINE) naglihok isip dugang counter. Tungod kay ang pormula gikopya ngadto sa mga selyula F4:F9, atong ibawas ang numero 3 gikan sa function nga resulta aron makakuha og bili 1 sa selda F4 (linya 4, ibawas 3) aron makuha 2 sa selda F5 (linya 5, ibawas 3) ug uban pa.
Bahin 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
function TANAN (GAMAY) mibalik n-oh ang pinakagamay nga bili sa data array. Sa among kaso, kung unsang posisyon (gikan sa pinakagamay) ang ibalik gitino sa function ROW (LINYA) (tan-awa ang Bahin 2). Busa, alang sa usa ka cell F4 function GAMAY({array},1) mobalik 1-ika (labing gamay) nga elemento sa array, ie 1. Para sa cell F5 mobalik 2-ika ang pinakagamay nga elemento sa array, nga mao 3, Ug uban pa
Bahin 4:
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))
function INDEX (INDEX) yano nga nagbalik sa kantidad sa usa ka piho nga cell sa usa ka array C2:C16. Para sa cell F4 function INDEX($C$2:$C$16) mobalik mansanasKay F5 function INDEX($C$2:$C$16) mobalik Mga tam-is' ug sa ingon sa.
Bahin 5:
IFERROR()
ЕСЛИОШИБКА()
Sa katapusan, gibutang namon ang pormula sa sulod sa function IFERROR (IFERROR), tungod kay dili ka malipay sa mensahe sa sayup #AT (#N/A) kung ang gidaghanon sa mga cell diin ang pormula gikopya mas gamay kaysa sa gidaghanon sa mga duplicate nga kantidad sa range nga gitan-aw.
XNUMXD nga pagpangita pinaagi sa nahibal-an nga linya ug kolum
Ang paghimo sa XNUMXD nga pagpangita sa Excel naglangkit sa pagpangita sa usa ka kantidad pinaagi sa nahibal-an nga numero sa linya ug kolum. Sa laing pagkasulti, imong gikuha ang kantidad sa cell sa intersection sa usa ka partikular nga laray ug kolum.
Busa, mobalik kita sa atong lamesa ug magsulat og pormula nga adunay function VPR, nga makit-an ang kasayuran bahin sa kantidad sa mga lemon nga gibaligya sa Marso.
Adunay daghang mga paagi aron mahimo ang pagpangita sa XNUMXD. Tan-awa ang mga kapilian ug pilia ang usa nga labing angay kanimo.
VLOOKUP ug MATCH functions
Mahimo nimong gamiton ang usa ka hugpong sa mga gimbuhaton VPR (VLOOKUP) ug MAS EXPOSED (MATCH) aron makit-an ang kantidad sa intersection sa mga uma Ngalan sa produkto (string) ug Month (kolum) sa array nga gipangutana:
=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);ЛОЖЬ)
Ang pormula sa ibabaw kay usa ka regular nga function VPR, nga nangita ug eksaktong tugma sa balor nga “Lemons” sa mga selula A2 hangtod sa A9. Apan tungod kay wala ka mahibal-an kung unsang kolum ang gibaligya sa Marso, dili nimo mabutang ang numero sa kolum alang sa ikatulo nga argumento sa function. VPR. Hinuon, gigamit ang function MAS EXPOSEDsa paghubit niini nga kolum.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Gihubad ngadto sa tawhanong pinulongan, kini nga pormula nagpasabot:
- Gipangita namo ang mga karakter nga "Mar" - argumento lookup_value (lookup_value);
- Pagtan-aw sa mga cell gikan sa A1 hangtod I1 - argumento lookup_array (lookup_array);
- Pagbalik sa eksaktong tugma - argumento match_type (match_type).
Pinaagi sa paggamit sa 0 sa ikatulo nga argumento, giingon nimo nga functions MAS EXPOSED pangitaa ang unang bili nga mohaum gayod sa bili nga imong gipangita. Katumbas kini sa bili SAYOP (FALSE) para sa ikaupat nga argumento VPR.
Mao kini ang paagi nga makahimo ka og two-way search formula sa Excel, nailhan usab nga two-dimensional search o bidirectional search.
SUMPRODUCT function
function SUMPRODUCT (SUMPRODUCT) mibalik sa sum sa mga produkto sa pinili nga mga array:
=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)
INDEX ug MATCH functions
Sa sunod nga artikulo akong ipatin-aw kini nga mga function sa detalye, mao nga sa pagkakaron mahimo nimong kopyahon kini nga pormula:
=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))
Ginganlan nga mga range ug ang intersection operator
Kung dili ka sa tanan nga komplikado nga mga pormula sa Excel, mahimo nimong gusto kini nga biswal ug dili makalimtan nga paagi:
- Pilia ang lamesa, ablihi ang tab Mga pormula (Formula) ug i-klik Paghimo gikan sa Pagpili (Paghimo gikan sa pagpili).
- Susiha ang mga kahon Ibabaw nga laray (sa linya sa ibabaw) ug Wala nga kolum (sa kolum sa wala). Ang Microsoft Excel maghatag ug mga ngalan sa mga han-ay gikan sa mga kantidad sa taas nga laray ug wala nga kolum sa imong spreadsheet. Karon makapangita ka gamit kini nga mga ngalan direkta nga wala maghimo mga pormula.
- Sa bisan unsang walay sulod nga cell, isulat =row_name column_name, pananglitan sama niini:
= Lemon Mar
… o vice versa:
= Mar Lemon
Hinumdumi nga ang mga ngalan sa laray ug kolum kinahanglan ibulag sa usa ka wanang, nga sa kini nga kaso molihok sama sa operator sa intersection.
Kung magsulod ka ug ngalan, ang Microsoft Excel magpakita ug tooltip nga adunay lista sa magkaparehas nga mga ngalan, sama sa imong pagsulod sa usa ka pormula.
- press Pagsulod ug susiha ang resulta
Sa kinatibuk-an, bisan asa sa mga pamaagi sa ibabaw ang imong pilion, ang resulta sa usa ka two-dimensional nga pagpangita mahimong pareho:
Paggamit sa daghang mga VLOOKUP sa usa ka pormula
Nahitabo nga ang main table ug ang lookup table wala'y usa ka kolum nga managsama, ug kini nagpugong kanimo sa paggamit sa naandan nga function VPR. Bisan pa, adunay lain nga lamesa nga wala maglangkob sa kasayuran nga among interesado, apan adunay usa ka sagad nga kolum nga adunay panguna nga lamesa ug lamesa sa pagpangita.
Atong tan-awon ang mosunod nga pananglitan. Naa mi Main table nga naay column SKU (bag-o), diin gusto nimong idugang ang usa ka kolum nga adunay katugbang nga mga presyo gikan sa laing lamesa. Dugang pa, kami adunay 2 nga lookup tables. Ang una (Lookup table 1) adunay gi-update nga mga numero SKU (bag-o) ug mga ngalan sa produkto, ug ang ikaduha (Lookup table 2) – mga ngalan sa produkto ug daan nga numero SKU (daan).
Aron makadugang og mga presyo gikan sa ikaduhang lookup table ngadto sa main table, kinahanglan nga mohimo ka og aksyon nga gitawag og double VPR o gisalag VPR.
- Pagsulat ug function VPR, nga makit-an ang ngalan sa produkto sa lamesa Talaan sa pagpangita 1sa paggamit sa SKU, ingon sa gitinguha nga kantidad:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)dinhi Bag-ong_SKU – ginganlan range $A:$B sa lamesa Talaan sa pagpangita 1, 2 - kini ang kolum B, nga adunay mga ngalan sa mga butang (tan-awa ang litrato sa ibabaw)
- Pagsulat og pormula aron isulod ang mga presyo gikan sa lamesa Talaan sa pagpangita 2 base sa iladong mga ngalan sa produkto. Aron mahimo kini, idikit ang pormula nga imong gihimo sa sayo pa ingon ang kantidad sa pagpangita alang sa bag-ong function VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)dinhi Presyo – ginganlan range $A:$C sa lamesa Talaan sa pagpangita 2, 3 mao ang kolum C nga adunay mga presyo.
Ang numero sa ubos nagpakita sa resulta nga gibalik sa pormula nga among gibuhat:
Dinamikong pagpuli sa datos gikan sa lain-laing mga lamesa gamit ang VLOOKUP ug INDIRECT
Una, atong klaruhon unsa ang atong gipasabot sa ekspresyong “Dynamic substitution of data from different tables” aron masiguro nga husto ang atong pagsabot sa usag usa.
Adunay mga sitwasyon kung adunay daghang mga sheet nga adunay data sa parehas nga format, ug kinahanglan nga makuha ang kinahanglan nga kasayuran gikan sa usa ka piho nga sheet, depende sa kantidad nga gisulod sa usa ka cell. Sa akong hunahuna mas dali nga ipasabut kini sa usa ka pananglitan.
Hunahunaa nga ikaw adunay mga taho sa pagpamaligya alang sa daghang mga rehiyon nga adunay parehas nga mga produkto ug parehas nga format. Gusto nimo nga makit-an ang mga numero sa pagbaligya alang sa usa ka piho nga rehiyon:
Kung ikaw adunay duha ra nga mga taho, nan mahimo nimong gamiton ang usa ka makauulaw nga yano nga pormula nga adunay mga gimbuhaton VPR и IF (KUNG) aron mapili ang gusto nga taho nga pangitaon:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Asa:
- $D $2 usa ka cell nga adunay ngalan sa produkto. Timan-i nga gigamit namo ang hingpit nga mga pakisayran dinhi aron malikayan ang pagbag-o sa bili sa pagpangita sa dihang gikopya ang pormula ngadto sa ubang mga selula.
- $D3 maoy usa ka selula nga adunay ngalan sa rehiyon. Gigamit namo ang usa ka hingpit nga kolum nga reperensiya ug usa ka relatibong row nga reperensiya tungod kay kami nagplano sa pagkopya sa pormula ngadto sa ubang mga selula sa samang kolum.
- FL_Sales и CA_Sales – ang mga ngalan sa mga lamesa (o ginganlan nga mga han-ay) nga adunay katugbang nga mga taho sa pagbaligya. Mahimo nimo, siyempre, mogamit sa naandan nga mga ngalan sa sheet ug mga reference sa cell range, pananglitan 'FL Sheet'!$A$3:$B$10, apan ang ginganlan nga mga han-ay mas sayon.
Bisan pa, kung adunay daghang ingon nga mga lamesa, ang function IF dili ang pinakamaayong solusyon. Hinuon, mahimo nimong gamiton ang function KINAHANGLAN (INDIRECT) aron ibalik ang gitinguha nga hanay sa pagpangita.
Sama sa imong nahibal-an, ang function KINAHANGLAN gigamit sa pagbalik sa usa ka link nga gihatag sa usa ka text string, nga mao gayud ang atong gikinahanglan karon. Busa, maisugon nga ilisan sa pormula sa ibabaw ang ekspresyon nga adunay function IF sa pag-link sa function KINAHANGLAN. Ania ang usa ka kombinasyon VPR и KINAHANGLAN maayo ang pagtrabaho sa:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Asa:
- $D $2 - kini usa ka cell nga adunay ngalan sa produkto, kini wala mausab tungod sa hingpit nga link.
- $D3 mao ang selula nga naglangkob sa unang bahin sa ngalan sa rehiyon. Sa atong pananglitan, kini FL.
- _Pagbaligya – ang komon nga bahin sa ngalan sa tanang ginganlan nga mga han-ay o mga lamesa. Kung gihiusa sa kantidad sa cell D3, kini nagporma sa hingpit nga kwalipikado nga ngalan sa gikinahanglan nga range. Sa ubos mao ang pipila ka mga detalye alang sa mga bag-o sa function KINAHANGLAN.
Giunsa pagtrabaho ang INDIRECT ug VLOOKUP
Una, pahinumdoman ko ikaw sa syntax sa function KINAHANGLAN (INDIREKTO):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Ang unang argumento mahimong usa ka cell reference (A1 o R1C1 style), usa ka range name, o usa ka text string. Ang ikaduha nga argumento nagtino unsa nga estilo sa link ang anaa sa unang argumento:
- A1kung ang argumento mao TINUOD NGA KODE (TINUOD) o wala gipiho;
- R1C1, kung FAS E (BAKAK).
Sa among kaso, ang link adunay istilo A1, aron mabiyaan nimo ang ikaduhang argumento ug ipunting ang una.
Mao nga mobalik kita sa among mga taho sa pagpamaligya. Kung nahinumduman nimo, nan ang matag report usa ka lahi nga lamesa nga nahimutang sa usa ka lahi nga sheet. Aron ang pormula mogana sa saktong paagi, kinahanglan nimong nganlan ang imong mga lamesa (o mga han-ay), ug ang tanang mga ngalan kinahanglang adunay komon nga bahin. Pananglitan, sama niini: CA_Sales, FL_Sales, TX_Sales ug uban pa. Sama sa imong makita, ang "_Sales" anaa sa tanan nga mga ngalan.
function KINAHANGLAN nagkonektar sa bili sa column D ug sa text string nga "_Sales", sa ingon nagsulti VPR asa nga lamesa pangitaon. Kung ang cell D3 adunay kantidad nga "FL", ang pormula mangita sa lamesa FL_Sales, kung "CA" - sa lamesa CA_Sales ug sa ingon sa.
Ang resulta sa mga gimbuhaton VPR и KINAHANGLAN mahimong mosunod:
Kung ang datos nahimutang sa lainlaing mga libro sa Excel, nan kinahanglan nimo nga idugang ang ngalan sa libro sa wala pa ang ginganlan nga range, pananglitan:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Kung ang function KINAHANGLAN nagtumong sa laing workbook, kana nga workbook kinahanglang bukas. Kung kini sirado, ang function magreport sa usa ka sayup. #REF! (#SSYL!).