Calc CERCA.VERT vs CERCA.X: scegli la formula di ricerca giusta

Se la cartella di lavoro verrà aperta in LibreOffice 24.8 o versioni successive, per la maggior parte delle nuove ricerche utilizza la funzione CERCA.X; per impostazione predefinita, cerca una corrispondenza esatta e ti permette di scegliere intervalli di ricerca e di risultati separati. Utilizza CERCA.VERT quando hai bisogno di compatibilità con le versioni precedenti di Calc o quando il valore di ricerca si trova nella prima colonna di una tabella compatta. Se mantieni CERCA.VERT, imposta il quarto argomento su 0 o FALSO per una corrispondenza esatta.

Queste funzioni risolvono lo stesso problema di base: trovare una chiave, come ad esempio un codice prodotto, e restituire le informazioni correlate da un'altra cella. Le differenze principali risiedono nel modo in cui ciascuna formula definisce i propri intervalli, nelle ipotesi che si pongono quando un'opzione viene omessa e nella necessità che il file sia compatibile con software meno recenti.

Qual è la differenza pratica?

La funzione CERCA.VERT cerca nella prima colonna di una tabella rettangolare e restituisce un valore dalla colonna numerata alla sua destra. CERCA.X separa l'intervallo di ricerca dall'intervallo di restituzione, in modo che i valori restituiti possano trovarsi su entrambi i lati delle chiavi. CERCA.X accetta anche un valore personalizzato per le corrispondenze mancanti e può effettuare ricerche in ordine inverso.

Ad esempio, supponiamo che le celle A2:A5 contengano gli ID dei prodotti e le celle B2:B5 i prezzi. La cella F2 contiene l'ID da trovare. In Calc, la formula CERCA.VERT per la corrispondenza esatta è:

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

La formula equivalente di CERCA.X è:

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

Entrambi restituiscono il prezzo corrispondente. Calc in genere visualizza i punti e virgola tra gli argomenti delle funzioni a seconda delle impostazioni locali; se le formule nella tua installazione utilizzano le virgole, usa il separatore previsto da Calc. Gli esempi utilizzano risultati in una singola cella e intervalli di ricerca unidimensionali.

Quando è opportuno utilizzare la funzione CERCA.X?

Scegli CERCA.VERT per una nuova cartella di lavoro quando sai che i destinatari utilizzano LibreOffice 24.8 o versioni successive. La funzione è disponibile da LibreOffice 24.8, secondo la pagina di aiuto ufficiale di CERCA.VERT . La modalità di corrispondenza predefinita è esatta, quindi se manca una chiave esatta, non viene restituito automaticamente un valore vicino.

Lo schema della funzione è =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]). I primi tre argomenti identificano la chiave, l'intervallo unidimensionale in cui cercare e l'intervallo da cui restituire. Un quarto argomento come "Not found"sostituisce il risultato usuale #N/A quando non viene trovata alcuna corrispondenza.

Restituisci un valore da una colonna a sinistra

La funzione CERCA.VERT non può restituire un valore a sinistra della prima colonna della tabella. CERCA.X può, perché gli intervalli di ricerca e di risultato sono indipendenti. Ad esempio, se gli ID si trovano in C2:C100 e i nomi dei reparti in A2:A100, utilizzare:

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

Questo layout evita anche un indice di colonna fragile. Se qualcuno inserisce una colonna in una tabella CERCA.VERT, l'indice codificato può puntare a un campo diverso. L'intervallo di risultati esplicito di CERCA.X rende più facile ispezionare la colonna di ritorno desiderata.

Restituisci la voce corrispondente più recente

Quando sono presenti chiavi duplicate e si desidera l'ultima corrispondenza, XLOOKUP supporta la ricerca inversa con la sua modalità di ricerca. Per un intervallo verticale, l'ultimo argomento -1cerca dall'ultimo al primo:

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

Questo metodo restituisce il risultato associato all'ultima occorrenza esatta. Utilizzatelo solo quando l'ordine delle righe ha un significato definito, ad esempio i record più recenti compaiono per ultimi. Se i duplicati devono essere trattati come un errore, pulite o validate i dati anziché scegliere implicitamente un'occorrenza.

Quando la funzione CERCA.VERT è ancora la scelta migliore?

È consigliabile utilizzare CERCA.VERT quando il file deve essere aperto con una versione precedente di LibreOffice che non include CERCA.X, oppure quando l'applicazione di destinazione potrebbe non riconoscere CERCA.X. La documentazione ufficiale di Calc descrive gli argomenti tabella e indice di CERCA.VERT e il suo argomento opzionale intervallo ordinato; consultare il riferimento ufficiale alle funzioni dei fogli di calcolo .

Per una corrispondenza esatta, il modello è =VLOOKUP(key;table;column_index;0). L'indice conta le colonne dal bordo sinistro della tabella selezionata: 1 indica la prima colonna, 2 la seconda. La chiave di ricerca deve trovarsi nella prima colonna e il campo restituito deve essere alla sua destra.

Il quarto argomento è importante. Se lo ometti o lo imposti su VERO, CERCA.VERT utilizza la corrispondenza approssimativa e si aspetta che la prima colonna sia ordinata in ordine crescente. Questo comportamento può essere utile per intervalli come le fasce di imposta, dove ogni riga indica un limite inferiore. È rischioso per ID, nomi ed elenchi non ordinati. In questi casi, specifica 0 o FALSO.

Come si differenziano il comportamento di corrispondenza e quello di errore?

• Corrispondenza esatta: XLOOKUP utilizza la corrispondenza esatta per impostazione predefinita. VLOOKUP richiede che il quarto argomento sia impostato su 0 o FALSO per garantire una ricerca esatta.
• Chiave mancante: XLOOKUP può restituire un messaggio o un valore a scelta nel suo quarto argomento. VLOOKUP in genere restituisce #N/D; racchiudetelo tra IFNA se avete bisogno di un messaggio personalizzato.
• Corrispondenza approssimativa: entrambe le opzioni supportano la corrispondenza basata su intervalli, ma i dati e la modalità selezionata devono essere conformi alla regola prevista. Ordina i dati secondo necessità e verifica i casi limite.
• Direzione di ricerca: XLOOKUP può effettuare la ricerca dal basso utilizzando l'argomento search-mode. VLOOKUP restituisce la prima riga corrispondente nel suo normale utilizzo dall'alto verso il basso.

Per fornire una risposta personalizzata per la chiave mancante con CERCA.VERT, utilizzare =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found"). In questo modo l'impostazione di corrispondenza esatta rimane visibile e viene gestito solo l'errore di non disponibilità. Evitare di racchiudere la formula in un gestore di errori generico a meno che non si desideri nascondere anche problemi non correlati, come un riferimento di intervallo non valido.

Cosa bisogna controllare prima di condividere una cartella di lavoro?

La guida di LibreOffice afferma che la funzione CERCA.VERT è disponibile dalla versione 24.8. Precisa inoltre che CERCA.VERT non fa parte dello standard OpenFormula di OpenDocument versione 1.3 e identifica uno spazio dei nomi di funzioni Microsoft per garantire l'interoperabilità. Ciò significa che il solo formato del file non garantisce che ogni applicazione per fogli di calcolo meno recente o di terze parti sia in grado di calcolare la funzione. In ambienti software misti, è consigliabile testare la cartella di lavoro nell'applicazione di destinazione più vecchia oppure utilizzare una formula consolidata come CERCA.VERT con corrispondenza esatta, laddove il suo formato lo consenta.

Si noti inoltre una condizione documentata per le formule matriciali: quando la matrice dei risultati di CERCA.VERT è un intervallo di celle, la funzione deve essere inserita come formula matriciale. I semplici esempi sopra riportati restituiscono un singolo valore corrispondente. Se si desidera che una formula restituisca più celle, consultare la guida specifica per la versione e verificare come l'applicazione di destinazione gestisce il risultato della matrice.

Come si può verificare il risultato?

1. Verifica l'esistenza di una chiave e confronta il valore restituito con la riga di origine.
2. Prova una chiave inesistente. Verifica che XLOOKUP visualizzi il valore di fallback scelto oppure che VLOOKUP restituisca il messaggio previsto #N/D o IFNA.
3. Per la corrispondenza esatta di CERCA.VERT, verificare che l'ultimo argomento sia 0 o FALSO. Se si utilizza intenzionalmente la corrispondenza approssimativa, controllare l'ordinamento e i valori immediatamente sopra, sul e sotto un limite.
4. Per XLOOKUP con una colonna di ritorno a sinistra o una ricerca inversa, verifica una chiave con un risultato noto e, in presenza di duplicati, conferma quale occorrenza deve essere restituita.
5. Salva e riapri la cartella di lavoro con la versione più vecchia di Calc o del foglio di calcolo utilizzata dai destinatari. Un risultato corretto sul tuo computer non dimostra che una versione precedente supporti la stessa funzione.

Nella maggior parte dei nuovi fogli di calcolo, la funzione CERCA.X offre intervalli più chiari e impostazioni predefinite di corrispondenza esatta più sicure. Utilizza CERCA.VERT quando la compatibilità con le versioni precedenti è importante o quando il suo layout con la prima colonna a destra è già appropriato, e specifica esplicitamente la modalità di corrispondenza.