Excel 2020: Douăsprezece beneficii ale XLOOKUP - Sfaturi Excel

Noua funcție XLOOKUP se lansează în Office 365 începând din noiembrie 2019. Joe McDaid din echipa Excel a proiectat XLOOKUP pentru a unifica persoanele care utilizează VLOOKUP și persoanele care utilizează INDEX / MATCH. Această secțiune va discuta cele 12 beneficii ale XLOOKUP:

  1. Potrivirea implicită este implicită.
  2. Al treilea argument al VLOOKUP bazat pe întreg este acum o referință adecvată.
  3. IFNA este încorporat pentru a gestiona valorile lipsă.
  4. XLOOKUP nu are nicio problemă să meargă spre stânga.
  5. Găsiți potrivirea următoare mai mică sau următoarea mai mare fără a sorta tabelul.
  6. XLOOKUP poate face HLOOKUP.
  7. Găsiți ultima potrivire căutând din partea de jos.
  8. Jokerurile sunt „dezactivate” în mod implicit, dar le puteți reporni.
  9. Returnează toate cele 12 luni într-o singură formulă.
  10. Poate returna o referință de celulă dacă XLOOKUP este lângă un punct, cum ar fi XLOOKUP (); XLOOKUP ()
  11. Poate face un meci în două direcții, cum poate face INDEX (, MATCH, MATCH).
  12. Poate însuma toate căutările într-o singură formulă, cum ar putea face CĂUTARE.

Iată sintaxa: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP Beneficiu 1: potrivire exactă implicită

99% din formulele mele VLOOKUP se termină cu, FALS sau, 0 pentru a indica o potrivire exactă. Dacă utilizați întotdeauna versiunea de potrivire exactă a VLOOKUP, puteți începe să lăsați match_mode dezactivat din funcția XLOOKUP.

În figura următoare, căutați W25-6 din celula A4. Doriți să căutați acel articol în L8: L35. Când este găsit, doriți prețul corespunzător din coloana N. Nu este necesar să specificați Fals ca meci de potrivire, deoarece XLOOKUP implicit este o potrivire exactă.

XLOOKUP valoarea în A4. Uită-te în L8: L35. Returnați prețul corespunzător de la N8: N35.

XLOOKUP Beneficiu 2: Results_Array este o referință în locul unui întreg

Gândiți-vă la formula VLOOKUP pe care ați folosi-o înainte de XLOOKUP. Al treilea argument ar fi fost un 3 pentru a indica faptul că doriți să returnați a treia coloană. A existat întotdeauna pericolul ca un coleg de muncă fără idei să fi introdus (sau șters) o coloană în tabelul dvs. Cu o coloană suplimentară în tabel, VLOOKUP care returnase un preț va începe să returneze o descriere. Deoarece XLOOKUP indica o referință de celulă, formula se rescrie singură pentru a continua să indice prețul care se află acum în coloana O.

VLOOKUP-ul vechi ar eșua dacă cineva ar introduce o coloană nouă în tabelul de căutare. XLOOKUP continuă să funcționeze.

XLOOKUP Beneficiu 3: IFNA este încorporat ca argument opțional

Eroarea temută # N / A este returnată atunci când valoarea de căutare nu este găsită în tabel. În trecut, pentru a înlocui # N / A cu altceva, ar trebui să utilizați IFERROR sau IFNA înfășurate în jurul VLOOKUP.

Când un articol nu este găsit, acesta returnează # N / A din VLOOKUP sau XLOOKUP …

Datorită unei sugestii de la Rico pe canalul meu de YouTube, echipa Excel a încorporat un al patrulea argument opțional pentru if_not_found. Dacă doriți să înlocuiți acele erori # N / A cu zero, pur și simplu adăugați 0 ca al patrulea argument. Sau, puteți utiliza un text, cum ar fi „Valoarea nu a fost găsită”.

Al patrulea argument opțional din XLOOKUP este „dacă nu este găsit”. Puneți 0 sau „Nu s-a găsit” acolo.

XLOOKUP Beneficiul 4: Nicio problemă privind în stânga câmpului cheie

VLOOKUP nu poate privi în stânga câmpului cheie fără a recurge la VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Cu XLOOKUP, nu există nicio problemă în a avea rezultatul_array în stânga Lookup_array.

Cu XLOOKUP, nu există nicio problemă la returnarea categoriei din coloana F în timp ce căutați numerele pieselor din coloana G. Aceasta a fost întotdeauna slăbiciunea VLOOKUP: nu putea privi în stânga.

XLOOKUP Beneficiul 5: potrivire următoare-mai mică sau următoare-mai mare fără sortare

VLOOKUP avea o opțiune de a căuta potrivirea exactă sau doar o valoare mai mică. Puteți fie să lăsați al patrulea argument din VLOOKUP, fie să schimbați False în True. Pentru ca acest lucru să funcționeze, tabelul de căutare a trebuit să fie sortat în ordine crescătoare.

Un exemplu de versiune de potrivire aproximativă a VLOOKUP. Orice vânzare de la 10 mii la 20 mii primește un bonus de 12 dolari.

Dar VLOOKUP nu a avut capacitatea de a returna potrivirea exactă sau următorul articol mai mare. Pentru aceasta, a trebuit să treceți la utilizarea MATCH cu un -1 ca match_mode și trebuia să aveți grijă ca tabelul de căutare să fie sortat descendent.

Al cincilea argument opțional XLOOKUP match_mode poate căuta doar potrivirea exactă, egală sau doar mai mică, egală sau doar mai mare. Rețineți că valorile din XLOOKUP au mai mult sens decât în ​​MATCH:

  • -1 găsește valoarea egală sau doar mai mică
  • 0 găsesc o potrivire exactă
  • 1 găsește valoarea egală sau doar mai mare.

Dar, cea mai uimitoare parte: tabelul de căutare nu trebuie să fie sortat și orice match_mode va funcționa.

Mai jos, un match_mode de -1 găsește următorul element mai mic.

Al cincilea argument al XLOOKUP este Match_Mode. 0 este pentru potrivirea exactă. Unul negativ este utilizat pentru potrivirea exactă sau următorul articol mai mic. Pozitivul 1 este pentru potrivirea exactă sau următorul articol mai mare. 2 este pentru Meciul Wildcard. Pentru a reflecta ceea ce ar face VLOOKUP cu True în al patrulea argument, puneți unul negativ ca argument match_mode în XLOOKUP.

Aici, un match_mode de 1, găsește ce vehicul este necesar în funcție de numărul de persoane din petrecere. Rețineți că tabelul de căutare nu este sortat după pasageri și numele vehiculului este în stânga cheii.

XLOOKUP poate face ceva ce VLOOKUP nu a putut face: găsiți potrivirea exactă sau doar mai mare. În acest caz, o companie de turism are o listă de rezervări. Pe baza numărului de pasageri, tabelul de căutare arată ce vehicul aveți nevoie pentru acele persoane.

Tabelul spune:

  • Autobuzul are 64 de persoane
  • Mașina are 4 persoane
  • Motorcyle deține o persoană
  • Tour Van are 12 persoane
  • Vanul poate ține 6 persoane.

Ca bonus, datele sunt sortate în funcție de vehicul (în vechea soluție, folosind MATCH, tabelul ar trebui să fie sortat descendent în funcție de capacitate. De asemenea: vehiculul este în stânga capacității.

XLOOKUP Beneficiul 6: lateral XLOOKUP înlocuiește HLOOKUP

Look_array și result_array pot fi orizontale cu XLOOKUP, simplificând înlocuirea HLOOKUP.

Aici tabelul de căutare este orizontal. În trecut, acest lucru ar necesita HLOOKUP, dar XLOOKUP se poate ocupa de o masă care se deplasează lateral.

XLOOKUP Beneficiu 7: Căutați din partea de jos pentru cea mai recentă potrivire

Am un videoclip vechi pe YouTube care răspunde la o întrebare de la o fermă britanică de cai. Aveau o flotă de vehicule. De fiecare dată când un vehicul a intrat pentru combustibil sau service, au înregistrat vehiculul, data și kilometrajul într-o foaie de calcul. Au vrut să găsească ultimul kilometraj cunoscut pentru fiecare vehicul. În timp ce era Excel-2017 MAXIFS ar putea rezolva acest lucru astăzi, soluția cu mulți ani în urmă a fost o formulă arcane folosind LOOKUP și a implicat divizarea la zero.

Astăzi, al șaselea argument opțional XLOOKUP vă permite să specificați căutarea ar trebui să înceapă din partea de jos a setului de date.

Găsiți ultimul meci din listă.

Notă

Deși aceasta este o mare îmbunătățire, vă permite doar să găsiți primul sau ultimul meci. Unii oameni sperau că acest lucru vă va permite să găsiți a doua sau a treia potrivire, dar aceasta nu este intenția argumentului search_mode.

Prudență

Figura de mai sus arată că există moduri de căutare folosind vechea căutare binară. Joe McDaid nu recomandă utilizarea acestora. În primul rând, algoritmul de căutare îmbunătățit din 2018 este suficient de rapid încât să nu existe un avantaj semnificativ de viteză. În al doilea rând, riscați ca un coleg de muncă fără idee să sorteze tabelul de căutare și să introducă răspunsuri greșite.

XLOOKUP Beneficiul 8: wildcard-urile sunt „dezactivate” în mod implicit

Majoritatea oamenilor nu și-au dat seama că VLOOKUP tratează asteriscul, semnul întrebării și tilde ca caractere wildcard, așa cum este descris în "# 51 Utilizați un wildcard în VLOOKUP" la pagina 143. Cu XLOOKUP, comodele sunt dezactivate în mod implicit. Dacă doriți ca XLOOKUP să trateze aceste caractere ca un wildcard, utilizați 2 ca Match_Mode.

Foarte puțini oameni și-au dat seama că VLOOKUP tratează asteriscurile din valoarea de căutare ca pe un wildcard. În mod implicit, XLOOKUP nu folosește metacaractere, dar îl poți forța să se comporte ca VLOOKUP dacă folosești un Mod de potrivire 2: Potrivire cu caractere wildcard

XLOOKUP Beneficiu 9: Întoarceți toate cele 12 luni într-o singură formulă!

Acesta este într-adevăr un beneficiu al Dynamic Arrays, dar este motivul meu preferat de a iubi XLOOKUP. Când trebuie să returnați toate cele 12 luni într-o căutare, o singură formulă introdusă în B6 cu un return_array dreptunghiular va returna mai multe rezultate. Aceste rezultate se vor revărsa în celulele adiacente.

În figura de mai jos, o singură formulă introdusă în B7 returnează toate cele 12 răspunsuri prezentate în B7: M7.

Un singur XLOOKUP din coloana ianuarie returnează numerele din ianuarie până în decembrie. Acest lucru se face prin specificarea unui result_array cu 12 coloane.

XLOOKUP Beneficiul 10: poate returna o referință de celulă dacă este adiacentă la colon

Acesta este complex, dar frumos. În trecut, au existat șapte funcții care s-ar schimba de la returnarea unei valori de celulă la returnarea unei referințe de celulă dacă funcția atingea un colon. Pentru un exemplu, consultați Utilizarea A2: INDEX () ca OFFSET non-volatil. XLOOKUP este funcția opt pentru a oferi acest comportament, alăturându-se CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET și SWITCH.

Luați în considerare următoarea figură. Cineva selectează Cireș în E4 și Fig în E5. Doriți o formulă care să rezume totul, de la B6 la B9.

Figura prezintă două formule XLOOKUP în două celule. Primul returnează 15 din celula B6. Al doilea retransmite 30 din B9. Dar apoi într-o a treia celulă, există o formulă care unește cele două formule XLOOKUP cu un colon și apoi le înfășoară într-o funcție SUM. Rezultatul este SUMA lui B6: B9, deoarece XLOOKUP poate returna o referință de celulă dacă funcția apare lângă un operator, cum ar fi un punct. Pentru a demonstra că acest lucru funcționează, următoarele cifre vor afișa această formulă în dialogul Evaluare formulă.

În figura de mai sus, puteți vedea că o XLOOKUP de E4 va returna 15 din celula B6. Un XLOOKUP de E5 va returna 30 de la B9. Cu toate acestea, dacă luați cele două funcții XLOOKUP din celulele D9 și D10 și le puneți împreună cu un colon între ele, comportamentul XLOOKUP se schimbă. În loc să returneze 15, primul XLOOKUP returnează adresa celulei B6!

Pentru a demonstra acest lucru, am selectat D7 și am folosit Formule, Evaluare formulă. După apăsarea Evaluare de două ori, următoarea parte care se calculează este XLOOKUP („Cireș”, A4: A29, B4: B29), așa cum se arată aici.

Aceasta arată dialogul Evaluare formulă chiar înainte de evaluarea primului XLOOKUP. Acest XLOOKUP apare chiar înainte de două puncte.

Apăsați din nou Evaluare și uimitor, formula XLOOKUP returnează $ 6 $ în loc de 15 stocate în B6. Acest lucru se întâmplă deoarece există un colon imediat după această formulă XLOOKUP.

Faceți clic pe Evaluare și primul XLOOKUP returnează $ 6 $ în loc de 15.

Apăsați Evaluare încă de două ori, iar formula intermediară va fi = SUM (B6: B9).

După evaluarea celui de-al doilea XLOOKUP, formula intermediară este = SUM (B6: B9).

Acesta este un comportament uimitor pe care majoritatea oamenilor nu îl cunosc. MVP Excel Charles Williams îmi spune că poate fi declanșat cu oricare dintre acești trei operatori lângă XLOOKUP:

  • Colon
  • Spațiu (operator de intersecție)
  • Virgulă (operator al Uniunii)

XLOOKUP Beneficiul 11: meci în două sensuri, cum ar fi INDEX (, MATCH, MATCH)

Pentru toți prietenii mei VLOOKUP, oamenii INDEX / MATCH așteptau să vadă dacă XLOOKUP poate rezolva un meci în două sensuri. Vestea minunată: o poate face. Vestea proastă: metodologia este puțin diferită decât s-ar aștepta fanii INDEX / MATCH. S-ar putea să fie puțin peste capul lor. Dar sunt sigur că pot ajunge la această metodă.

Pentru o potrivire în două sensuri, doriți să găsiți care rând conține numărul de cont A621 prezentat în J3. Deci, XLOOKUP începe destul de ușor: = XLOOKUP (J3, A5: A15. Dar atunci trebuie să furnizați un rezultat_array. Puteți utiliza același truc ca în XLOOKUP Beneficiu 9: Returnează toate cele 12 luni într-o singură formulă de mai sus, dar folosiți-l pentru a returna un vector vertical. Un XLOOKUP interior caută luna J4 din titlurile lunii din B4: G4. Return_array este specificat ca B5: G15. Rezultatul este că XLOOKUP interior returnează o matrice ca cea prezentată în I10 : I20 de mai jos. Deoarece A621 se găsește în a cincea celulă a lookup_array și 104 se găsește în a cincea celulă a rezultatului_array, veți obține răspunsul corect din formulă. Mai jos, J6 arată modul vechi. J7 returnează noul mod.

XLCăutați J3 în lista de conturi din A5: A15. Pentru matricea de rezultate, utilizați XLOOKUP (J4, B4: G4, B5: G15). În această formulă, B4: G4 este o listă de luni. B5: G15 este matricea dreptunghiulară de valori pentru toate conturile pentru toate lunile. Într-o altă celulă, doar XLOOKUP interior arată cum returnează întreaga coloană de valori pentru luna mai.

XLOOKUP Beneficiu 12: Sumați toate valorile de căutare într-o singură formulă

Funcția antică de căutare a oferit două trucuri ciudate. În primul rând, dacă încercați să aflați suma totală a cheltuielilor bonus de acumulat, puteți solicita CĂUTĂRII să caute toate valorile într-o singură formulă. În imaginea de mai jos, CĂUTARE (C4: C14 efectuează 11 căutări. Dar funcția CĂUTARE nu a oferit o potrivire exactă și a necesitat sortarea tabelului de căutare.

Căutați 13 valori și sumați-le. Acest lucru a funcționat cu LOOKUP, dar funcționează și cu XLOOKUP. Specificați toate valorile de căutare C4: C14 ca primul argument. Înfășurați XLOOKUP într-o funcție SUM.

Cu XLOOKUP, puteți specifica un interval deoarece lookup_value și XLOOKUP vor returna toate răspunsurile. Avantajul este că XLOOKUP poate face potriviri exacte.

Trucul utilizării LOOKUP pentru a rezuma toate rezultatele căutării a funcționat numai cu versiunea de potrivire aproximativă a Căutării. Aici, XLOOKUP face o potrivire exactă cu toate numele din L4: L14 și obține un total al tuturor rezultatelor.

Sfat bonus: Ce zici de o CAUTARE Twisted?

MVP-ul Excel, Mike Girvin, arată adesea un truc al funcției CĂUTARE în care Lookup_Vector este vertical și Result_Vector este orizontal. XLOOKUP nu va susține în mod nativ acest truc. Dar, dacă înșelați puțin și înfășurați result_array în funcția TRANSPOSE, puteți gestiona o căutare răsucită.

Aici matricea de căutare este verticală, iar matricea de rezultate este orizontală. Vechea funcție LOOKUP poate gestiona acest lucru, dar pentru a face acest lucru cu XLOOKUP, trebuie să înfășurați fiecare matrice în TRANSPOSE.

Articole interesante...