Calc VLOOKUP vs XLOOKUP: Alegeți formula de căutare potrivită

Folosește XLOOKUP pentru majoritatea căutărilor noi dacă registrul de lucru va fi deschis în LibreOffice 24.8 sau o versiune ulterioară; implicit, se folosește o potrivire exactă și îți permite să alegi intervale separate de căutare și rezultate. Folosește VLOOKUP atunci când ai nevoie de compatibilitate cu versiuni mai vechi de Calc sau când valoarea de căutare se află în prima coloană a unui tabel compact. Dacă păstrezi VLOOKUP, include al patrulea argument ca 0 sau FALSE pentru o potrivire exactă.

Aceste funcții rezolvă aceeași problemă de bază: găsesc o cheie, cum ar fi un cod de produs, și returnează informații aferente dintr-o altă celulă. Diferențele importante constau în modul în care fiecare formulă își definește intervalele, ce presupune atunci când o opțiune este omisă și dacă fișierul trebuie să funcționeze în software-ul mai vechi.

Care este diferența practică?

Funcția VLOOKUP caută în prima coloană a unui tabel dreptunghiular și returnează o valoare dintr-o coloană numerotată din dreapta sa. Funcția XLOOKUP separă intervalul de căutare de intervalul returnat, astfel încât valorile returnate pot fi pe ambele părți ale cheilor. Funcția XLOOKUP acceptă, de asemenea, o valoare personalizată pentru o potrivire lipsă și poate căuta în ordine inversă.

De exemplu, să presupunem că A2:A5 conține ID-uri de produs și B2:B5 conține prețuri. Celula F2 conține ID-ul de găsit. În Calc, formula VLOOKUP cu potrivire exactă este:

=VLOOKUP(F2;A2:B5;2;0)

Formula echivalentă XLOOKUP este:

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

Ambele returnează prețul corespunzător. Calc afișează de obicei punct și virgulă între argumentele funcției în funcție de setările regionale; dacă formulele din instalarea dvs. utilizează virgule, utilizați separatorul așteptat de Calc. Exemplele utilizează rezultate dintr-o singură celulă și intervale de căutare unidimensionale.

Când ar trebui să alegeți XLOOKUP?

Alegeți XLOOKUP pentru un registru de lucru nou atunci când știți că destinatarii utilizează LibreOffice 24.8 sau o versiune mai nouă. Funcția a devenit disponibilă în LibreOffice 24.8, conform paginii oficiale de ajutor XLOOKUP . Modul său implicit de potrivire este exact, deci o cheie exactă lipsă nu returnează în mod silențios o valoare apropiată.

Modelul funcției este =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]). Primele trei argumente identifică cheia, intervalul unidimensional în care se va căuta și intervalul din care se va returna valoarea. Un al patrulea argument, cum ar fi , "Not found"înlocuiește rezultatul obișnuit #N/A atunci când nu există nicio potrivire.

Returnează o valoare dintr-o coloană din stânga

Funcția VLOOKUP nu poate returna o valoare în stânga primei coloane din tabel. Funcția XLOOKUP poate, deoarece intervalele de căutare și cele de rezultate sunt independente. De exemplu, dacă ID-urile sunt în C2:C100 și numele departamentelor sunt în A2:A100, utilizați:

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

Această dispunere evită și un index fragil al coloanei. Dacă cineva introduce o coloană într-un tabel VLOOKUP, indexul hardcoded poate indica un câmp diferit. Intervalul de rezultate explicit al lui XLOOKUP face ca coloana returnată să fie mai ușor de inspectat.

Returnează cea mai recentă intrare corespunzătoare

Când există chei duplicate și doriți ultima potrivire, XLOOKUP acceptă căutarea inversă cu modul său de căutare. Pentru un interval vertical, argumentul final -1caută de la ultimul la primul:

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

Aceasta returnează rezultatul asociat cu ultima apariție exactă. Se utilizează numai atunci când ordinea rândurilor are o semnificație definită, cum ar fi cele mai noi înregistrări care apar ultimele. Dacă duplicatele ar trebui tratate ca o eroare, curățați sau validați datele în loc să alegeți implicit o apariție.

Când este VLOOKUP încă alegerea mai bună?

Păstrați VLOOKUP atunci când fișierul trebuie deschis într-o versiune mai veche de LibreOffice care nu include XLOOKUP sau când aplicația de destinație poate să nu recunoască XLOOKUP. Documentația oficială Calc descrie argumentele tabel și index ale VLOOKUP și argumentul opțional sortated-range; consultați referința oficială a funcțiilor foilor de calcul .

Pentru o potrivire exactă, modelul este =VLOOKUP(key;table;column_index;0). Indexul numără coloanele de la marginea stângă a tabelului selectat: 1 înseamnă prima coloană, 2 înseamnă a doua. Cheia de căutare trebuie să fie în prima coloană, iar câmpul returnat trebuie să fie în dreapta sa.

Al patrulea argument contează. Dacă îl omiteți sau îl setați la TRUE, VLOOKUP utilizează potrivirea aproximativă și se așteaptă ca prima coloană să fie sortată în ordine crescătoare. Acest comportament poate fi util pentru intervale precum categoriile de impozitare, unde fiecare rând marchează o limită inferioară. Este riscant pentru ID-uri, nume și liste nesortate. Pentru acestea, specificați 0 sau FALSE.

Cum se compară comportamentul la potrivire și cel la eroare?

• Potrivire exactă: XLOOKUP utilizează în mod implicit potrivirea exactă. VLOOKUP necesită ca al patrulea argument să fie setat la 0 sau FALSE pentru o căutare exactă garantată.
• Cheie lipsă: XLOOKUP poate returna un mesaj sau o valoare aleasă în al patrulea argument. VLOOKUP returnează în general #N/A; încadrați-l cu IFNA dacă aveți nevoie de un mesaj personalizat.
• Potrivire aproximativă: Ambele pot accepta potrivirea bazată pe interval, dar datele și modul selectat trebuie să se încadreze în regula dorită. Sortați datele după cum este necesar și verificați cazurile limită.
• Direcția de căutare: XLOOKUP poate căuta de jos în sus cu argumentul său de mod de căutare. VLOOKUP returnează primul rând corespondent în utilizarea sa normală de sus în jos.

Pentru a oferi un răspuns personalizat pentru cheia lipsă cu VLOOKUP, utilizați =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found"). Aceasta menține vizibilă setarea de potrivire exactă și gestionează doar eroarea indisponibilă. Evitați să încadrați formula într-un tracker de erori larg, cu excepția cazului în care doriți să ascundeți și probleme fără legătură, cum ar fi o referință de interval nevalidă.

Ce ar trebui să verificați înainte de a partaja un registru de lucru?

Ajutorul LibreOffice precizează că XLOOKUP este disponibil începând cu versiunea 24.8. De asemenea, menționează că XLOOKUP nu face parte din standardul OpenFormula din versiunea 1.3 a OpenDocument și identifică un spațiu de nume pentru funcții Microsoft pentru interoperabilitate. Aceasta înseamnă că formatul fișierului în sine nu garantează că fiecare aplicație de foi de calcul mai veche sau terță parte va calcula funcția. Pentru mediile software mixte, testați registrul de lucru în cea mai veche aplicație țintă sau utilizați o formulă stabilită, cum ar fi VLOOKUP cu potrivire exactă, acolo unde se potrivește aspectul său.

De asemenea, rețineți o condiție documentată pentru formula matrice: atunci când matricea rezultată a lui XLOOKUP este un interval de celule, funcția trebuie introdusă ca o formulă matrice. Exemplele simple de mai sus returnează o singură valoare corespunzătoare. Dacă doriți ca o formulă să returneze mai multe celule, consultați ajutorul specific versiunii și testați modul în care aplicația țintă gestionează rezultatul matricei.

Cum poți verifica rezultatul?

1. Testați o cheie existentă și comparați valoarea returnată cu rândul sursă.
2. Testați o cheie care nu există. Confirmați dacă XLOOKUP afișează opțiunea de rezervă aleasă sau dacă VLOOKUP returnează mesajul #N/A sau IFNA așteptat.
3. Pentru potrivirea exactă VLOOKUP, confirmați că argumentul final este 0 sau FALSE. Dacă utilizați intenționat potrivirea aproximativă, verificați ordinea sortată și valorile imediat deasupra, pe și dedesubt unei limite.
4. Pentru XLOOKUP cu o coloană de returnare la stânga sau o căutare inversă, testați o cheie cu un rezultat cunoscut și, atunci când există duplicate, confirmați ce apariție ar trebui returnată.
5. Salvați și redeschideți registrul de lucru în cea mai veche aplicație Calc sau de calcul tabelar utilizată de destinatari. Un rezultat corect pe computer nu dovedește că o versiune mai veche acceptă aceeași funcție.

Pentru majoritatea foilor Calc noi, XLOOKUP oferă intervale mai clare și valori implicite de potrivire exactă mai sigure. Folosește VLOOKUP atunci când compatibilitatea inversă este importantă sau aspectul său cu prima coloană la dreapta este deja adecvat și explică modul său de potrivire.