Excel 2020: Divpadsmit XLOOKUP priekšrocības - Excel padomi

Satura rādītājs

Jaunā XLOOKUP funkcija Office 365 tiek izlaista, sākot ar 2019. gada novembri. Joe McDaid no Excel komandas izstrādāja XLOOKUP, lai apvienotu cilvēkus, kuri izmanto VLOOKUP, un cilvēkus, kuri izmanto INDEX / MATCH. Šajā sadaļā tiks aplūkotas 12 XLOOKUP priekšrocības:

  1. Pēc noklusējuma ir precīza atbilstība.
  2. VLOOKUP trešais arguments, kas balstīts uz veselo skaitli, tagad ir pareiza atsauce.
  3. IFNA ir iebūvēta, lai apstrādātu trūkstošās vērtības.
  4. XLOOKUP nav problēmu doties pa kreisi.
  5. Atrodiet nākamo-mazāko vai nākamo-lielāko atbilstību, nešķirojot tabulu.
  6. XLOOKUP var veikt HLOOKUP.
  7. Atrodiet pēdējo spēli, meklējot no apakšas.
  8. Aizstājējzīmes pēc noklusējuma ir "izslēgtas", taču jūs varat tās atkal ieslēgt.
  9. Atgrieziet visus 12 mēnešus vienā formulā.
  10. Var atgriezt šūnas atsauci, ja XLOOKUP atrodas blakus kolai, piemēram, XLOOKUP (); XLOOKUP ()
  11. Var veikt divvirzienu spēli, piemēram, INDEX (, MATCH, MATCH).
  12. Var apkopot visus uzmeklējumus vienā formulā, piemēram, LOOKUP.

Šeit ir sintakse: = XLOOKUP (Meklēt_Vērtība, Meklēt_Array, Rezultāti_Array, (ja_nav atrasts) ((Match_mode), (search_mode)).

XLOOKUP 1. ieguvums: precīza atbilstība pēc noklusējuma

99% no manām VLOOKUP formulām beidzas ar vērtību FALSE vai 0, lai norādītu precīzu atbilstību. Ja jūs vienmēr izmantojat precīzās atbilstības VLOOKUP versiju, varat sākt atstāt spēles_mode funkciju XLOOKUP.

Šajā attēlā jūs meklējat W25-6 no A4 šūnas. Jūs vēlaties meklēt šo vienumu L8: L35. Kad tas ir atrasts, vēlaties norādīt atbilstošo cenu no slejas N. Nav nepieciešams norādīt False kā match_mode, jo XLOOKUP pēc noklusējuma ir precīza atbilstība.

XLOOKUP vērtību A4. Meklējiet L8: L35. Atgrieziet atbilstošo cenu no N8: N35.

XLOOKUP 2. ieguvums: Rezultātu masīvs ir atsauce nevis vesels skaitlis

Padomājiet par VLOOKUP formulu, kuru izmantosit pirms XLOOKUP. Trešais arguments būtu bijis 3, lai norādītu, ka vēlaties atgriezt 3. kolonnu. Vienmēr pastāvēja briesmas, ka nenoderīgs kolēģis jūsu tabulā būtu ievietojis (vai izdzēsis) kolonnu. Ja tabulā ir papildu kolonna, VLOOKUP, kas bija atgriezis cenu, sāks atgriezt aprakstu. Tā kā XLOOKUP norādīja uz šūnas atsauci, formula pārraksta sevi, lai turpinātu norādīt uz cenu, kas tagad atrodas O slejā.

Vecais VLOOKUP neizdosies, ja kāds uzmeklēšanas tabulā ievietos jaunu kolonnu. XLOOKUP turpina darboties.

XLOOKUP 3. ieguvums: IFNA ir iebūvēts kā izvēles arguments

Bailes # N / A kļūda tiek atgriezta, ja jūsu uzmeklēšanas vērtība nav atrodama tabulā. Iepriekš, lai aizstātu # N / A ar kaut ko citu, jums būtu jāizmanto IFERROR vai IFNA, kas apvilkta ap VLOOKUP.

Ja vienums nav atrasts, tas atgriež # N / A no VLOOKUP vai XLOOKUP…

Pateicoties Rico ieteikumam manā YouTube kanālā, Excel komanda iekļauj izvēles ceturto argumentu if_not_found. Ja vēlaties aizstāt šīs # N / A kļūdas ar nulli, vienkārši pievienojiet 0 kā ceturto argumentu. Vai arī varat izmantot tekstu, piemēram, “Vērtība nav atrasta”.

XLOOKUP izvēles ceturtais arguments ir "ja nav atrasts". Ievietojiet 0 vai “Not found”.

XLOOKUP 4. labums: nav problēmu skatīties pa kreisi no atslēgas lauka

VLOOKUP nevar skatīties pa kreisi no atslēgas lauka, neizmantojot VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Izmantojot funkciju XLOOKUP, nav problēmu, ja Rezultātu_loks ir pa kreisi no Uzmeklēšanas_loksnes.

Izmantojot XLOOKUP, nav problēmu atgriezt kategoriju no F slejas, vienlaikus meklējot detaļu numurus slejā G. Tas vienmēr bija VLOOKUP vājums: tas nevarēja skatīties pa kreisi.

XLOOKUP 5. pabalsts: Nākamā mazākā vai nākamā lielāka atbilstība bez šķirošanas

VLOOKUP bija iespēja meklēt precīzu atbilstību vai tikai mazāku vērtību. Jūs varat vai nu atstāt ceturto argumentu no VLOOKUP, vai arī mainīt False uz True. Lai tas darbotos, uzmeklēšanas tabula bija jāsakārto augošā secībā.

VLOOKUP aptuvenās atbilstības versijas piemērs. Jebkura pārdošana no 10 tūkstošiem līdz 20 tūkstošiem saņem bonusu 12 ASV dolāru apmērā.

Bet VLOOKUP nebija iespējas atgriezt precīzu atbilstību vai nākamo lielāku vienumu. Lai to izdarītu, jums bija jāpārslēdzas uz MATCH izmantošanu ar -1 kā match_mode un jums jābūt uzmanīgam, lai uzmeklēšanas tabula būtu sakārtota dilstošā secībā.

XLOOKUP izvēles piektais arguments match_mode var meklēt tikai precīzu atbilstību, vienāda ar vai mazāka, vienāda vai tikai lielāka. Ņemiet vērā, ka XLOOKUP vērtībām ir lielāka jēga nekā MATCH:

  • -1 atrod vērtību, kas vienāda vai tikai mazāka
  • 0 atrast precīzu atbilstību
  • 1 atrod vērtību, kas vienāda vai tikai lielāka.

Bet pārsteidzošākā daļa: uzmeklēšanas tabula nav jākārto un jebkura match_mode darbosies.

Zemāk match_mode no -1 atrodiet nākamo mazāko vienumu.

XLOOKUP piektais arguments ir Match_Mode. 0 ir precīza atbilstība. Negatīvais tiek izmantots precīzai atbilstībai vai nākamajam mazākajam vienumam. 1. pozitīvais ir precīza atbilstība vai nākamais lielākais vienums. 2 ir paredzēts aizstājējzīmei. Lai atspoguļotu to, ko darītu VLOOKUP ar True ceturtajā argumentā, XLOOKUP ievietojiet negatīvu kā match_mode argumentu.

Šeit match_mode no 1 atrodams nepieciešamais transportlīdzeklis atkarībā no cilvēku skaita ballītē. Ņemiet vērā, ka uzmeklēšanas tabula nav sakārtota pēc pasažieriem un transportlīdzekļa nosaukums atrodas pa kreisi no atslēgas.

XLOOKUP var izdarīt kaut ko, ko VLOOKUP nevarēja: atrast precīzu atbilstību vai vienkārši lielāku. Šajā gadījumā tūrisma uzņēmumam ir rezervāciju saraksts. Pamatojoties uz pasažieru skaitu, uzmeklēšanas tabulā parādīts, kāds transportlīdzeklis jums ir vajadzīgs šiem cilvēkiem.

Tabulā teikts:

  • Autobusā ir 64 cilvēki
  • Automašīnā ir 4 cilvēki
  • Motociklā ir 1 persona
  • Tour Van ir paredzēts 12 cilvēkiem
  • Furgonā ir 6 cilvēki.

Bonusā dati tiek sakārtoti pēc transportlīdzekļa (vecajā risinājumā, izmantojot MATCH, tabula būtu jāšķiro dilstošā secībā pēc ietilpības. Turklāt: transportlīdzeklis atrodas pa kreisi no jaudas.

XLOOKUP 6. priekšrocība: XLOOKUP uz sāniem aizstāj HLOOKUP

Izmantojot XLOOKUP, uzmeklēšanas_numurs un rezultātu_mērījums var būt horizontāls, padarot HLOOKUP aizstāšanu vienkārši.

Šeit uzmeklēšanas tabula ir horizontāla. Agrāk tam būtu nepieciešams HLOOKUP, bet XLOOKUP var tikt galā ar tabulu, kas iet uz sāniem.

XLOOKUP 7. priekšrocība: meklējiet jaunāko spēli no apakšas

Man vietnē YouTube ir vecs video, kurā atbildu uz jautājumu no Lielbritānijas zirgu fermas. Viņiem bija transportlīdzekļu parks. Katru reizi, kad transportlīdzeklis ieradās pēc degvielas vai apkopes, viņi izklājlapā reģistrēja transportlīdzekli, datumu un nobraukumu. Viņi vēlējās atrast jaunāko zināmo nobraukumu katram transportlīdzeklim. Kaut arī Excel-2017 laikmets MAXIFS to varētu atrisināt šodien, pirms daudziem gadiem risinājums bija paslēpta formula, izmantojot LOOKUP un iesaistot dalīšanu ar nulli.

Šodien XLOOKUP izvēles sestais arguments ļauj norādīt, ka meklēšana jāsāk no datu kopas apakšas.

Sarakstā atrodiet pēdējo spēli.

Piezīme

Lai gan tas ir lielisks uzlabojums, tas ļauj atrast tikai pirmo vai pēdējo maču. Daži cilvēki cerēja, ka tas ļaus jums atrast otro vai trešo spēli, taču tas nav nolūks argumentam search_mode.

Uzmanību

Iepriekš redzamais attēls parāda, ka ir meklēšanas režīmi, izmantojot veco bināro meklēšanu. Džo Makdeids neiesaka tos izmantot. Pirmkārt, uzlabotais uzmeklēšanas algoritms, sākot ar 2018. gadu, ir pietiekami ātrs, ka nav nozīmīga ātruma ieguvuma. Otrkārt, jūs riskējat, ka bezjēdzīgs kolēģis kārtos uzmeklēšanas tabulu un ieviesīs nepareizas atbildes.

XLOOKUP 8. priekšrocība: aizstājējzīmes pēc noklusējuma ir "izslēgtas"

Lielākā daļa cilvēku neapzinājās, ka VLOOKUP apzīmē zvaigznīti, jautājuma zīmi un tildi kā aizstājējzīmes, kā aprakstīts sadaļā "# 51 Izmantot aizstājējzīmi VLOOKUP" 143. lpp. Izmantojot funkciju XLOOKUP, aizstājējzīmes pēc noklusējuma tiek izslēgtas. Ja vēlaties, lai XLOOKUP šīs rakstzīmes traktētu kā aizstājējzīmi, izmantojiet 2 kā Match_Mode.

Ļoti maz cilvēku saprata, ka VLOOKUP uzmeklēšanas vērtības zvaigznītes uzskata par aizstājējzīmi. Pēc noklusējuma XLOOKUP neizmanto aizstājējzīmes, taču jūs varat piespiest to rīkoties tāpat kā VLOOKUP, ja izmantojat spēles režīmu 2: aizstājējzīmju rakstzīmju atbilstība.

XLOOKUP 9. priekšrocība: atgrieziet visus 12 mēnešus vienā formulā!

Tas patiešām ir dinamisko masīvu ieguvums, taču tas ir mans iecienītākais iemesls mīlēt XLOOKUP. Kad jāatmeklē visi 12 mēneši uzmeklēšanas laikā, viena formula, kas ievadīta B6 ar taisnstūrveida return_array, atgriezīs vairākus rezultātus. Šie rezultāti nonāks blakus esošajās šūnās.

Zemāk redzamajā attēlā viena B7 ievadītā formula atgriež visas 12 atbildes, kas parādītas B7: M7.

Viena XLOOKUP slejā Janvāris atgriež skaitļus no janvāra līdz decembrim. Tas tiek darīts, norādot result_array ar 12 kolonnām.

XLOOKUP 10. priekšrocība: var atgriezt šūnas atsauci, ja tā atrodas blakus resnajai zarnai

Šis ir sarežģīts, bet skaists. Agrāk bija septiņas funkcijas, kas mainījās no šūnas vērtības atgriešanas uz šūnas atsauces atgriešanu, ja funkcija pieskārās kolai. Piemēram, skatiet Izmantot A2: INDEX () kā nepastāvīgu OFFSET. XLOOKUP ir astoņu funkciju funkcija, lai piedāvātu šādu rīcību, pievienojoties IZVĒLĒTIES, IF, IFS, INDEKSTS, NETIEŠI, OFFSET un SWITCH.

Apsveriet šādu attēlu. Kāds izvēlas ķiršu E4 un Fig E5. Jūs vēlaties formulu, kas summēs visu, sākot no B6 līdz B9.

Attēlā redzamas divas XLOOKUP formulas divās šūnās. Pirmais atgriež 15 no šūnas B6. Otrais atkārtojas 30 no B9. Bet tad trešajā šūnā ir formula, kas savieno abas XLOOKUP formulas ar kolu un pēc tam to iesaiņo SUM funkcijā. Rezultāts ir B6: B9 SUMMA, jo XLOOKUP var atgriezt šūnas atsauci, ja funkcija parādās blakus operatoram, piemēram, kolai. Lai pierādītu, ka tas darbojas, nākamajos vairākos skaitļos šī formula tiks parādīta dialoglodziņā Formulas novērtēšana.

Iepriekš redzamajā attēlā jūs varat redzēt, ka E4 XLOOKUP atgriezīs 15 no šūnas B6. XLOOKUP no E5 atgriezīs 30 no B9. Tomēr, ja jūs ņemat divas funkcijas XLOOKUP no šūnām D9 un D10 un saliekat tās kopā ar resnās zarnas starplaiku, XLOOKUP darbība mainās. Tā vietā, lai atgrieztu 15, pirmais XLOOKUP atgriež šūnas adresi B6!

Lai to pierādītu, esmu izvēlējies D7 un izmantoju Formulas, Evaluate Formula. Pēc divas reizes nospiežot Novērtēt, nākamā aprēķināmā daļa ir XLOOKUP ("Cherry", A4: A29, B4: B29), kā parādīts šeit.

Tas parāda dialoglodziņu Formulas novērtēšana tieši pirms pirmā XLOOKUP novērtēšanas. Šis XLOOKUP parādās tieši pirms resnās zarnas.

Nospiediet Novērtēt vēlreiz un pārsteidzoši, formula XLOOKUP atgriež $ B $ 6, nevis 15, kas glabājas B6. Tas notiek tāpēc, ka tieši pēc šīs XLOOKUP formulas ir kols.

Noklikšķiniet uz Novērtēt, un pirmais XLOOKUP atgriež $ B $ 6, nevis 15.

Nospiediet Novērtēt vēl divas reizes, un starpposma formula būs = SUM (B6: B9).

Pēc otrā XLOOKUP novērtēšanas pagaidu formula ir = SUM (B6: B9).

Šī ir pārsteidzoša uzvedība, par kuru lielākā daļa cilvēku nezina. Excel MVP Čārlzs Viljamss man saka, ka to var aktivizēt ar jebkuru no šiem trim operatoriem blakus XLOOKUP:

  • Resnās zarnas
  • Kosmoss (krustojuma operators)
  • Komats (Savienības operators)

XLOOKUP 11. ieguvums: divvirzienu spēle, piemēram, INDEX (, MATCH, MATCH)

Visiem maniem VLOOKUP draugiem INDEX / MATCH cilvēki gaidīja, lai redzētu, vai XLOOKUP var tikt galā ar divvirzienu spēli. Lieliskas ziņas: tas to var. Sliktā ziņa: metodika ir nedaudz atšķirīga, nekā INDEX / MATCH fani varētu gaidīt. Tas varētu būt nedaudz pār viņu galvām. Bet esmu pārliecināts, ka viņi var izmantot šo metodi.

Divvirzienu mačam vēlaties atrast, kurā rindā ir konta numurs A621, kas parādīts J3. Tātad, XLOOKUP sākas pietiekami viegli: = XLOOKUP (J3, A5: A15. Bet pēc tam jums ir jānorāda results_array. Varat izmantot to pašu triku, kas norādīts sadaļā XLOOKUP Benefit 9: Visus 12 mēnešus atgrieziet vienā formātā iepriekš, bet izmantojiet to, lai atgrieztu vertikālu vektoru. Iekšējais XLOOKUP mēneša virsrakstos B4: G4 meklē J4 mēnesi. Return_array ir norādīts kā B5: G15. Rezultāts ir tāds, ka iekšējais XLOOKUP atgriež masīvu, kā parādīts I10 : I20 zemāk. Tā kā A621 ir atrodams uzmeklēšanas_zarojuma piektajā šūnā un 104 ir atrasts rezultātu_parametra piektajā šūnā, jūs saņemat pareizo atbildi no formulas. Zemāk J6 parāda veco ceļu. J7 atgriež jauno ceļu.

XLookup J3 kontu sarakstā A5: A15. Rezultātu masīvā izmantojiet XLOOKUP (J4, B4: G4, B5: G15). Šajā formulā B4: G4 ir mēnešu saraksts. B5: G15 ir visu kontu visu mēnešu taisnstūrveida vērtību masīvs. Citā šūnā tikai iekšējais XLOOKUP parāda, kā tas atgriež visu maija vērtību kolonnu.

XLOOKUP 12. priekšrocība: apkopojiet visas uzmeklēšanas vērtības vienā formulā

Senā funkcija LOOKUP piedāvāja divus dīvainus trikus. Pirmkārt, ja jūs mēģināt noskaidrot kopējo uzkrājamo prēmiju summu, jūs varētu lūgt LOOKUP meklēt visas vērtības vienā formulā. Zemāk redzamajā attēlā LOOKUP (C4: C14 veic 11 uzmeklējumus. Bet funkcija LOOKUP nepiedāvāja precīzu atbilstību un pieprasīja sakārtot uzmeklēšanas tabulu.

Uzmeklējiet 13 vērtības un summējiet tās. Kādreiz tas darbojās ar LOOKUP, bet tas darbojas arī ar XLOOKUP. Kā pirmo argumentu norādiet visas uzmeklēšanas vērtības C4: C14. Aptiniet XLOOKUP funkciju SUM.

Izmantojot XLOOKUP, jūs varat norādīt diapazonu kā uzmeklēšanas_vērtība, un XLOOKUP atgriezīs visas atbildes. Ieguvums ir tāds, ka XLOOKUP var veikt precīzas atbilstības.

Viltība izmantot LOOKUP, lai summētu visus uzmeklēšanas rezultātus, darbojās tikai ar aptuveno uzmeklēšanas atbilstības versiju. Šeit XLOOKUP veic precīzu atbilstību visiem L4: L14 nosaukumiem un iegūst visus rezultātus.

Bonusa padoms: kā ar Twisted LOOKUP?

Excel MVP Maiks Girvins bieži parāda funkcijas LOOKUP triku, kur Lookup_Vector ir vertikāls un Result_Vector horizontāls. XLOOKUP neatbalstīs šo triku dabiski. Bet, ja jūs nedaudz krāpjat un iesaiņojat rezultātu_marjumu funkcijā TRANSPOSE, varat pārvaldīt savītu meklēšanu.

Šeit uzmeklēšanas masīvs ir vertikāls, un rezultātu masīvs ir horizontāls. Vecā funkcija LOOKUP to var apstrādāt, taču, lai to izdarītu ar XLOOKUP, jums jāiesaiņo vai nu masīvs TRANSPOSE.

Interesanti raksti...