VLOOKUP este o funcție puternică. Dar de multe ori primesc o întrebare la unul dintre seminariile mele Power Excel de la cineva care vrea să știe dacă VLOOKUP poate returna toate valorile potrivite. După cum știți, VLOOKUP cu False ca al patrulea argument va returna întotdeauna primul meci pe care îl găsește. În următoarea captură de ecran, celula F2 returnează 3623 deoarece este prima potrivire găsită pentru jobul J1199.
Întrebarea, atunci, poate VLOOKUP să returneze toate meciurile?
VLOOKUP nu o va face. Dar alte funcții pot.
Dacă doriți să totalizați toate costurile de la postul J1199, ați folosi =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Dacă aveți valori text și doriți să alăturați toate rezultatele într-o singură valoare, puteți utiliza =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Această formulă funcționează numai în Office 365 și Excel 2019.
Sau, poate fi necesar să returnați toate rezultatele pentru o singură lucrare într-o gamă nouă a foii de lucru. O =FILTER(B2:C53,A2:A53=K1,"None Found")funcție nouă , care vine la Office 365 în 2019, va rezolva problema:
Uneori, oamenii vor să efectueze toate VLOOKUP-urile și să le însumeze. Dacă tabelul dvs. de căutare este sortat, puteți utiliza =SUM(LOOKUP(B2:B53,M3:N5)).
Dacă trebuie să însumați toate VLOOKUP-urile cu versiunea Exact Match a VLOOKUP, va trebui să aveți acces la Dynamic Arrays pentru a le utiliza =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Pentru a afla mai multe despre tablourile dinamice, consultați tablourile dinamice Excel direct până la punctul.
Urmăriți videoclipul
Transcriere video
Learn Excel From, Podcast Episodul 2247: Puteți returna toate valorile VLookUp?
Hei. Bine ați venit înapoi pe netcast. Eu sunt Bill Jelen. Două întrebări au apărut în seminarul meu din Appleton, Wisconsin săptămâna trecută - ambele legate. Ei au spus, hei, cum returnăm toate VLOOKUP-urile, bine? În acest caz, ca J1199 are o grămadă de meciuri și ei, știi, vor să le returneze pe toate, iar prima mea întrebare ori de câte ori cineva mă întreabă este, ei bine, ce vrei să faci cu meciurile? Sunt numere pe care doriți să le adăugați sau este un text pe care doriți să îl concatenați? Și este amuzant. Cele două întrebări din același seminar, o persoană a dorit să le adune și cealaltă a dorit să concateneze rezultatele.
Deci, să aruncăm o privire la ambele. Verificați în descrierea YouTube un cuprins în care puteți să treceți la celălalt, dacă doriți să vedeți rezultatul textului.
Bine, deci, primul lucru, dacă vrem să le adăugăm pe toate, nu vom folosi deloc un VLOOKUP. Vom folosi o funcție numită SUMIF sau SUMIFS, care va însuma tot ceea ce se potrivește cu acest element. Deci, SUMIFE. Iată valorile numerice pe care vrem să le însumăm și voi apăsa F4 pentru a bloca asta. În acest fel, pe măsură ce copiez acest lucru, va continua să arate spre același interval și apoi vrem să verificăm dacă numărul JOB din coloana A, din nou F4 acolo, este = la valoarea din stânga noastră - în acest caz E2 - și pe măsură ce îl copiem, vom vedea TOTALUL pentru fiecare articol. (SUME ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Să facem o mică verificare aici. J1199. Totalul este 25365. Bine. Deci, asta funcționează. Dacă sunt numere și doriți să obțineți toate numerele și să le adăugați, comutați la SUMIF sau SUMIFS, dar dacă este text, bine, acum, această funcție este nouă în Office 365 în februarie 2017. Deci, dacă aveți Excel 2016 sau Excel 2013 sau Excel 2010 sau oricare dintre cele mai vechi, nu veți avea această funcție. Este o funcție numită TEXTJOIN. TEXTJOIN. Aceasta este o altă funcție de la (Joe McDade - 01:50) care tocmai ne-a adus toate acele formule extraordinare de matrice dinamice la Ignite în 2018 și Joe s-a asigurat că TEXTJOIN va funcționa cu tablouri, ceea ce este cu adevărat minunat.
Deci, delimitatorul de aici va fi, SPACE, ignora definitiv GOLUL. Vrem să ignorăm EMPTY aici, pentru că vom genera o mulțime de goluri în următoarea parte, declarația IF. DACĂ articolul respectiv peste un A2, F4, este = la acest număr de JOB aici, atunci vreau articolul corespunzător din coloana C, F4, altfel, vreau „” așa. Închideți acea declarație IF. Închideți TEXTJOIN. Trebuie să apăs pe CONTROL + SHIFT + ENTER? Nu, eu nu. Îmi aduce toate produsele care se potrivesc așa, bine? Deci, returnând toate VLOOKUP-urile, dacă vrem să le însumăm, da, dacă vrem să le concatenăm, da. (= TEXTJOIN („,”, Adevărat, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, „”)))
Bine, acum, există o altă posibilitate aici când oamenii mă întreabă dacă pot returna toate VLOOKUP-urile. S-ar putea să fie o problemă în care vrem să căutăm fiecare dintre aceste costuri aici și să ne dăm seama COSTUL DE MANIPULARE și apoi să le însumăm pe toate. Ca și cum, nu vreau să pun un VLOOKUP aici și un VLOOKUP aici și un VLOOKUP aici și un VLOOKUP aici. Vreau doar să le fac cu totul și, în acest caz, vom folosi funcția SUM și apoi vechea, vechea funcție LOOKUP. LOOKUP spune că vom căuta toate aceste valori în coloana B. Nu am nevoie de F4 aici pentru că nu o copiez nicăieri. ,. Iată tabelul nostru de căutare. ), închideți SUMA și se stinge și face fiecare VLOOKUP individual și apoi le rezumă pe toate așa. (= SUM (CĂUTARE (B2: B53, K3: L5)))
Ei bine, hei. Toate aceste subiecte sunt cartea mea LIV: Cele mai mari 54 de sfaturi din toate timpurile. Faceți clic pe i în colțul din dreapta sus pentru a afla mai multe.
Deci, întrebarea este că puteți returna toate VLOOKUP-urile? Ei bine, un fel de, dar nu de fapt folosind VLOOKUP. Fie vom folosi SUMIF, TEXTJOIN sau SUM sau LOOKUP pentru a le rezolva.
Ei bine, hei. Vreau să vă mulțumesc că ați trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Știi, bine, vorbesc despre aceste matrice dinamice de o săptămână. Am vrut să fac un videoclip în care nu am atins matricele dinamice pentru că știu că mulți oameni nu le au încă, dar iată-ne. Este extragerea. Știi, acestea nu sunt alfabetice. Acest lucru ar fi mult mai bine dacă le-am putea sorta și, dacă se întâmplă să aveți noile matrice dinamice, puteți trimite acest lucru în funcția SORT, SORT astfel, și apăsați ENTER, iar acum rezultatele vor fi sortate așa.
Știi, chiar și această formulă s-ar putea îmbunătăți cu matricele dinamice. Căutarea necesită să utilizați, ADEVĂRAT. Ce se întâmplă dacă ai vrea să folosești un, FALS? Am putea schimba acest lucru într-un VLOOKUP, căutați tot acest text în acel tabel, 2,. În acest caz, voi folosi TRUE dar, într-un alt caz, s-ar putea să folosiți FALSE. CONTROL + SHIFT + ENTER. Nu. Va merge doar, bine? (= SUM (VLOOKUP (B2: B53, K3: L5,2, True)))
Dynamic Arrays care vor apărea la începutul anului 2019 vor rezolva atâtea probleme.
Vă mulțumim că ați participat la ieșirea de aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul excel: can-you-return-all-vlookup-values.xlsx
Când cineva întreabă „VLOOKUP poate întoarce toate meciurile, răspunsul este nu. Dar există multe alte funcții care pot face în esență același lucru.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Normalizează-ți datele așa cum ai face ca alții să-și normalizeze datele pentru tine”
Kevin Lehrbass
