LibreOffice Calc vă oferă mai multe modalități de a găsi o valoare într-un tabel și de a returna date aferente. Ani de zile, VLOOKUP a fost alegerea obișnuită. Astăzi, Calc include și XLOOKUP , o funcție de căutare mai nouă care elimină mai multe limitări ale funcției VLOOKUP și este de obicei mai ușor de întreținut.
Acest ghid începe cu modelul mental de bază, apoi prezintă formule practice pe care le puteți copia într-o mică foaie de calcul. De asemenea, explică unde se mai potrivesc funcțiile INDEX/MATCH, ce poate merge prost și ce funcție are sens pentru un nou registru de lucru.
O căutare caută o cheie într-un interval, cum ar fi un ID de produs, apoi returnează o valoare corelată, cum ar fi prețul produsului. Cheia de căutare poate fi text, un număr, o dată sau o referință de celulă.
În Ajutorul LibreOffice actual, XLOOKUP este documentat ca un înlocuitor modern pentru VLOOKUP, HLOOKUP și LOOKUP. XLOOKUP este disponibil de la LibreOffice 24.8. Ajutorul LibreOffice 26.8 actual documentează potrivirea exactă și aproximativă, potrivirea cu wildcard și expresii regulate, căutarea inversă și căutarea binară. Consultați documentația oficială LibreOffice XLOOKUP .
Un detaliu care îi surprinde adesea pe utilizatorii care folosesc Microsoft Excel: exemplele de formule LibreOffice folosesc de obicei punct și virgulă între argumente. De exemplu, =XLOOKUP(F2;A2:A6;D2:D6).Calc poate adapta separatorii la setările regionale, așadar urmați separatorul pe care îl acceptă instalarea.
| Capacitate | CĂUTARE V | CĂUTARE EXTRA |
|---|---|---|
| Potrivire exactă | Da, dar în mod normal specificați 0/FALSE | Da; potrivirea exactă este implicită |
| Returnează o valoare în stânga coloanei de căutare | Nu, nu direct | Da |
| Intervale separate de căutare și returnare | Nu; folosește o matrice de tabel plus un index de coloană | Da |
| Rezultat încorporat atunci când nu se găsește nicio potrivire | Niciun argument dedicat | Da |
| Căutare de la ultimul element la primul | Niciun argument direct pentru modul de căutare | Da |
| Potrivire aproximativă | Da | Da, cu moduri de potrivire explicite |
| Disponibil în LibreOffice înainte de versiunea 24.8 | Da | Nu |
Creați coloanele de la A la D cu anteturile ID produs , Nume produs , Categorie și Preț . Adăugați câteva ID-uri, cum ar fi P001 până la P005. Puneți ID-ul pe care doriți să îl căutați în F2 și plasați formula în G2.
Formulele din Calc încep cu semnul egal. Le puteți introduce direct într-o celulă sau în bara de formule; ghidul oficial de introducere a formulelor LibreOffice descrie ambele metode.
Să presupunem că P004 se află în F2 și doriți prețul din coloana D. Introduceți:
=VLOOKUP(F2;$A$2:$D$6;4;0)
Argumentele înseamnă: căutați valoarea în F2, căutați în prima coloană a tabelului A2:D6, returnați valoarea din a patra coloană a tabelului respectiv și solicitați o potrivire exactă. Semnele dolar creează referințe absolute , care mențin intervalul de căutare fix dacă copiați formula în alte celule.
LibreOffice documentează funcția VLOOKUP ca o căutare verticală în care valoarea de căutare trebuie să fie în prima coloană a matricei tabelului, iar valoarea returnată provine dintr-o coloană din dreapta sa. De asemenea, avertizează că, dacă argumentul final este omis sau este TRUE, funcția presupune date sortate și poate returna o potrivire aproximativă. Consultați referința oficială a funcțiilor din foaia de calcul LibreOffice înainte de a vă baza pe comportamentul aproximativ al funcției VLOOKUP.
Acum rearanjați tabelul astfel încât Numele produsului să fie în coloana A, iar ID-ul produsului să fie în coloana C. Cu VLOOKUP, această structură este dificilă deoarece funcția caută doar prima coloană a matricei sale din tabel și returnează valorile din dreapta.
Funcția XLOOKUP separă matricea de căutare de matricea de rezultate . Pentru a găsi P003 în coloana C și a returna numele produsului din coloana A, utilizați:
=XLOOKUP(F2;$C$2:$C$6;$A$2:$A$6;"Not found")
Al patrulea argument este valoarea de returnat atunci când nu există nicio potrivire. Acest lucru face formula mai ușor de citit decât încadrarea unei căutări într-o funcție separată de tratare a erorilor pentru acest caz comun.
Dacă un utilizator introduce un ID care nu există, o căutare exactă simplă produce în mod normal #N/A. În XLOOKUP, plasați un rezultat prietenos în al patrulea argument:
=XLOOKUP(F2;$A$2:$A$6;$D$2:$D$6;"Not found";0)
Modul 0de potrivire solicită în mod explicit o potrivire exactă, deși potrivirea exactă este deja setarea implicită XLOOKUP. Scrierea acesteia poate fi în continuare utilă atunci când doriți ca intenția formulei să fie evidentă pentru cineva care revizuiește foaia.
Nu ascundeți automat fiecare eroare. Dacă un ID ar trebui să existe întotdeauna, #N/Apoate fi valoros deoarece expune date sursă greșite sau incomplete. Folosiți o soluție de rezervă prietenoasă atunci când se așteaptă chei lipsă, nu atunci când acestea indică o problemă de calitate a datelor.
Uneori, o cheie apare de mai multe ori. De exemplu, un jurnal de tranzacții poate conține mai multe rânduri pentru clientul C001. XLOOKUP are un argument Mod de căutare . O valoare a -1căutărilor de la ultimul element către primul.
Pentru a returna suma din ultimul rând care conține ID-ul clientului în F2, utilizați:
=XLOOKUP(F2;$A$2:$A$8;$D$2:$D$8;"Not found";0;-1)
Referința oficială XLOOKUP definește Modul de căutare 1ca fiind de la primul la ultimul și -1de la ultimul la primul. De asemenea, oferă moduri de căutare binară 2și -2, dar acestea necesită date sortate corect. Utilizarea căutării binare pe date nesortate poate returna rezultate nevalide.
Înainte de apariția XLOOKUP în Calc 24.8, o alternativă flexibilă comună era INDEX plus MATCH . MATCH găsește poziția relativă a unei chei; INDEX returnează valoarea din acea poziție. De exemplu:
=INDEX($D$2:$D$6;MATCH(F2;$A$2:$A$6;0))
Acest lucru rămâne util atunci când aveți nevoie de compatibilitate cu instalări mai vechi de LibreOffice sau când un registru de lucru existent folosește deja INDEX/MATCH în mod constant. Referința oficială a funcțiilor de calcul tabelar documentează atât INDEX, cât și MATCH .
Calc oferă și XMATCH începând cu LibreOffice 24.8. XMATCH adaugă moduri de potrivire și căutare similare cu XLOOKUP, inclusiv căutare inversă. Consultați documentația oficială LibreOffice XMATCH .
Cu VLOOKUP, omiterea argumentului final invocă comportamentul interval sortat. Pentru ID-uri de produs, numere de angajat, SKU-uri și alți identificatori discreți, utilizați 0sau FALSE pentru o potrivire exactă, cu excepția cazului în care potrivirea aproximativă face parte intenționat din design.
Indexul coloanei VLOOKUP este numărat începând cu prima coloană a matricei tabelului, nu începând cu literele coloanelor foii de calcul. În A2:D6, coloana D este indexul 4. Dacă inserați ulterior o coloană în acel tabel, este posibil ca indexul codificat hard să nu se mai refere la câmpul dorit.
Matricea de căutare și matricea de rezultate ar trebui să fie aliniate rând cu rând sau coloană cu coloană. Un interval de căutare precum A2:A100, asociat cu D2:D90, este o eroare de proiectare, deoarece pozițiile corespondente nu acoperă aceleași înregistrări.
Modul de căutare XLOOKUP 2 așteaptă ordine crescătoare, în timp ce -2 așteaptă ordine descrescătoare. Documentația LibreOffice avertizează explicit că datele nesortate pot produce rezultate nevalide. Dacă nu controlați sortarea, utilizați modurile normale de căutare înainte sau înapoi.
Calc acceptă potrivirea cu wildcard și expresii regulate, dar acestea sunt moduri de potrivire separate în XLOOKUP. Modul wildcard folosește caractere precum *și ?; modul expresie regulată folosește motorul de expresii regulate al Calc. Alegeți modul în mod deliberat, în loc să presupuneți că un model va fi interpretat automat.
Pentru o foaie de calcul nouă care rulează pe LibreOffice 24.8 sau o versiune ulterioară, începeți cu XLOOKUP. Intervalele separate de căutare și rezultate sunt mai ușor de citit, potrivirea exactă este implicită, retururile din stânga sunt simple, iar opțiunile încorporate de căutare inversă și negăsit reduc nevoia de soluții alternative pentru formule.
Folosește VLOOKUP atunci când menții un registru de lucru existent construit în jurul acestuia, îl partajezi cu medii în care suportul XLOOKUP este incert sau lucrezi cu un tabel simplu și stabil în care limitările sale nu contează. Folosește INDEX/MATCH atunci când compatibilitatea cu LibreOffice mai vechi este importantă sau registrul de lucru urmează deja acest model.
Cel mai important obicei este să nu memorezi fiecare argument. În schimb, identifică patru lucruri înainte de a scrie formula: ce valoare caut, unde caut, ce interval ar trebui să ofere răspunsul și ce ar trebui să se întâmple dacă cheia lipsește? Odată ce acestea sunt clare, alegerea între VLOOKUP, XLOOKUP și INDEX/MATCH devine mult mai ușoară.
Utilizați setările de validare a datelor din Excel pentru a seta o lungime maximă, minimă, exactă sau un interval de caractere, apoi adăugați solicitări clare și alerte de eroare.
Creați o invitație la o întâlnire Outlook, adăugați participanți obligatorii sau opționali, alegeți o oră, adăugați detalii Teams, setați recurența și verificați răspunsurile în Outlook nou sau clasic.
Remediați eroarea „Nu putem programa întâlnirea” în Outlook verificând asistența pentru conturi, actualizând conectarea la Teams și reparând programul de completare Outlook clasic.
Modificați meniurile Office 365, setările de ortografie și gramatică sau introducerea de text de la tastatură în aplicațiile desktop și Microsoft 365 pentru web.
Aflați cum să redirecționați automat toate e-mailurile sau anumite e-mailuri în Outlook 365 folosind setările sau regulile de redirecționare, plus cum să opriți redirecționarea și să remediați redirecționările externe blocate.
Utilizați Editorul Microsoft în Word pentru Microsoft 365 pentru a verifica ortografia, gramatica, claritatea și concizia, a revizui sugestiile și a ajusta setările de verificare.
Aflați cum să adăugați un flux RSS în Outlook 365 în Outlook clasic pentru Windows, să găsiți adresa URL corectă a fluxului, să gestionați abonamentele și să remediați problemele comune legate de RSS.
Accesați direct orice pagină din Word pentru Microsoft 365 cu Ctrl+G sau F5, numere relative de pagină sau panoul de navigare, cu comenzi rapide pentru Windows și Mac.
Aflați cum să adăugați contacte în Outlook pe web, în noul Outlook, în Outlook clasic și în Outlook pentru mobil, plus cum funcționează sincronizarea contactelor și importurile în fișiere CSV.
Schimbați culoarea unei pagini Word, adăugați o imagine de fundal, eliminați oricare dintre ele și activați imprimarea în fundal pe Windows sau Mac cu pași clari și actuali.
