6 formule da cercare in Excel

Anonim

Conosciamo tutti la famosa stella delle funzioni di Excel CERCA.VERT. È comunemente usato per cercare valori con un ID univoco. Ma questa non è l'unica funzione che può essere utilizzata per cercare valori in Excel. Esistono molte altre funzioni e formule che possono essere utilizzate per cercare il valore. In questo articolo, ti presenterò tutte queste funzioni e formule di ricerca di Excel. Alcuni sono persino migliori della funzione CERCA.VERT in Excel. Quindi, leggi fino alla fine.

1. La funzione CERCA.VERT di Excel

La prima funzione di ricerca excel è ovviamente la funzione CERCA.VERT. Questa funzione è famosa per un motivo. Possiamo usare questa funzione per fare più di una semplice ricerca. Ma il compito fondamentale di questa funzione è cercare i valori nella tabella, da sinistra a destra.

Sintassi della funzione CERCA.VERT:

=CERCA.VERT(valore di ricerca, matrice di tabella, col_index_number, [range_lookup])

Valore di ricerca: Il valore in base al quale si desidera eseguire la ricerca nella prima colonna di Table Array.

Matrice di tabella: La tabella in cui vuoi cercare/cercare

col_index_number: Il numero di colonna in Table Array da cui si desidera recuperare i risultati.

[range_lookup]: FALSE se vuoi cercare il valore esatto, TRUE se vuoi una corrispondenza approssimativa.

Vantaggi di CERCA.VERT:

  • Facile da usare.
  • Veloce
  • Uso multiplo
  • Ideale per cercare i valori in ordine verticale.

Svantaggi:

  • Viene utilizzato solo per la ricerca verticale
  • Restituisce solo il primo valore corrispondente.
  • Statico finché non viene utilizzato con la funzione CONFRONTA.
  • Impossibile cercare i valori a sinistra del valore di ricerca.

Puoi leggere in dettaglio questa formula di ricerca di Excel qui.

2. La funzione CERCA.ORIZZ di Excel

La funzione CERCA.VERT è la parte mancante della funzione CERCA.VERT. La funzione CERCA.ORIZZ viene utilizzata per cercare i valori orizzontalmente. In altre parole, quando si desidera cercare un valore in Excel abbinando il valore nelle colonne e ottenere i valori dalle righe, si usa la funzione CERCA.ORIZZ. Questo è esattamente l'opposto della funzione CERCA.VERT.

Sintassi di HLOOKUP

=HLOOKUP(valore di ricerca, array di tabelle, numero di indice di riga, [range_lookup] )
  • valore di ricerca : Il valore che stai cercando.
  • Matrice di tabella : La tabella in cui stai cercando il valore.
  • Numero indice riga: Il numero di riga nella tabella da cui si desidera recuperare i dati.
  • [range_lookup] : è il tipo di corrispondenza. 0 per la corrispondenza esatta e 1 per corrispondenza approssimativa.

Vantaggi della funzione CERCA.ORIZZ:

  • Può cercare i valori orizzontalmente.
  • Facile da usare.
  • Uso multiplo
  • Veloce

Svantaggi:

  • Viene utilizzato solo per la ricerca orizzontale
  • Restituisce solo il primo valore corrispondente.
  • Statico finché non viene utilizzato con la funzione CONFRONTA.
  • Impossibile cercare valori al di sopra dei valori di ricerca nella tabella.

Puoi conoscere questa funzione di ricerca di Excel qui.

3. La formula di ricerca INDEX-MATCH

Dove CERCA.VERT e CERCA.ORIZZ non possono raggiungere, questa formula può raggiungere. Questa è la migliore formula di ricerca in Excel fino a Excel 2016 (XLOOKUP è in arrivo).

La formula generica dell'INDEX MATCH

=INDICE (Result_Range,MATCH(lookup_value,lookup range,0))

Risultato_Range: è l'intervallo da cui si desidera recuperare il valore.

Valore di ricerca: è il valore che vuoi abbinare.

Lookup_Range:È l'intervallo in cui si desidera far corrispondere il valore di ricerca.

Vantaggi della formula di ricerca INDEX-MATCH:

  • Può cercare in quattro direzioni. Può cercare i valori a sinistra e in alto del valore di ricerca.
  • Dinamico.
  • Non è necessario definire l'indice di riga o colonna.

Svantaggi:

  • Potrebbe essere difficile per i nuovi utenti.
  • Utilizza due funzioni di Excel in combinazione. Gli utenti devono comprendere il funzionamento delle funzioni INDICE e CONFRONTA.

Puoi conoscere questa formula qui.

4: Formula di ricerca OFFSET-MATCH di Excel

Questa è un'altra formula che può essere utilizzata per cercare i valori in modo dinamico. Questa formula di ricerca Excel utilizza la funzione OFFSET come funzione di ancoraggio e MATCH come funzione di alimentazione. Usando questa formula, possiamo recuperare dinamicamente i valori da una tabella cercando in righe e colonne.

Formula generica,

=OFFSET(CellaInizio, CONFRONTA(ValoreRicerca,IntervalloRicerca,0), CONFRONTA(ValoreRicerca Col,IntervalloRicerca,0))

Cella iniziale:Questa è la cella iniziale della tabella di ricerca. Diciamo che se vuoi cercare nell'intervallo A2: A10, StartCell sarà A1.

RowLookupValue: Questo è il valore di ricerca che vuoi trovare nelle righe sotto ilStartCell.

Intervallo di ricerca riga:Questo è l'intervallo in cui si desidera cercare RowLookupValue. È la gamma sotto StartCell (A2:A10).

ColLookupValue: Questo è il valore di ricerca che vuoi trovare nelle colonne (intestazioni).

ColLookupRange:Questo è l'intervallo in cui si desidera cercare ColLookupValue. È l'intervallo sul lato destro di StartCell (come B1:D1).

Vantaggi di questa tecnica di ricerca di Excel:

  • Veloce
  • Può cercare orizzontalmente e verticalmente.
  • Dinamico

Svantaggi:

  • Complesso per alcune persone.
  • È necessario comprendere il funzionamento della funzione OFFSET e della funzione MATCH.

Puoi conoscere questa formula di ricerca qui in dettaglio.

5: Valori multipli della formula di RICERCA di Excel

Tutte le formule di ricerca precedenti restituiscono il primo valore trovato dall'array. Se ci sono più di una corrispondenza non restituiranno altre corrispondenze. In tal caso, questa formula entra in azione per salvare la giornata. Questa formula restituisce tutti i valori corrispondenti dall'elenco, anziché solo la prima corrispondenza.

Questa formula usa le funzioni INDICE, RIGA e SE come funzioni principali. La funzione IFERROR può essere utilizzata facoltativamente per gestire gli errori.

Formula generica

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(prima cella di lookup_value_range)+1),ROW(1:1)))}

Vettore: L'intervallo da cui si desidera recuperare i dati.
valore di ricerca: Il tuo lookup_value che vuoi filtrare.
intervallo_valore_ricerca: L'intervallo in cui si desidera filtrare lookup_value.
La prima cella nell'intervallo lookup_value: se il tuo intervallo lookup_value è $A$5:$A$100 allora è $A$5.
Importante: Tutto dovrebbe essere referenziato assoluto. valore di ricerca può essere relativo secondo il requisito.
Inseriscilo come formula di matrice. Dopo aver scritto la formula, premi CTRL+MAIUSC+INVIO per renderla una formula di matrice.

Come puoi vedere nella gif, restituisce tutte le corrispondenze dalla tabella excel.

Vantaggi:

  • Restituisce con più valori corrispondenti dalla tabella Excel.
  • Dinamico

Svantaggi:

  • È troppo complesso da capire per un nuovo utente.
  • Utilizza la formula dell'array
  • Necessità di definire il possibile numero di output e applicare questa formula come formula di matrice multicella (non in Excel 2019 e 365).
  • Lento.

Puoi conoscere questa formula qui in dettaglio.

6: CERCA.VERT-SCEGLI Formula Excel di ricerca

Pertanto, la maggior parte delle persone afferma che non è possibile cercare i valori a sinistra del valore di ricerca in Excel utilizzando la funzione CERCA.VERT. Beh, mi dispiace dirlo, ma si sbagliano. Possiamo cercare a sinistra del valore di ricerca in Excel utilizzando la funzione CERCA.VERT con l'aiuto della funzione SCEGLI.

Formula generica:

= CERCA.VERT ( valore_ricerca , SCEGLI ( { 1 , 2 } , intervallo_ricerca , intervallo_req ) , 2 , 0)

valore di ricerca : valore da cercare

intervallo_di ricerca : range, dove cercare lookup_value

req_range : intervallo, dove è richiesto il valore corrispondente

2 : seconda colonna, num che rappresenta l'intervallo_req

0 : cerca la corrispondenza esatta

In questa formula, creiamo fondamentalmente una tabella virtuale all'interno della formula utilizzando la funzione SCEGLI. La funzione SCEGLI crea una tabella di due colonne. La prima colonna contiene l'intervallo di ricerca e la seconda colonna contiene l'intervallo di risultati.

Vantaggi della formula di ricerca CERCA.VERT-SCEGLI:

  • Puoi cercare a sinistra del valore di ricerca
  • Veloce
  • Facile

Svantaggi:

  • La funzione SCEGLI è usata raramente. Gli utenti devono capirne il funzionamento.

Puoi conoscere questa formula di ricerca qui.

Quindi sì ragazzi, queste sono le diverse funzioni e formule di ricerca. Non è tutto. Ci possono essere molte più formule di ricerca in Excel che possono essere create utilizzando diverse combinazioni di formule Excel. Se disponi di tecniche di ricerca speciali, condividi nella sezione commenti qui sotto. Lo includeremo nel nostro articolo con il tuo nome.

Spero di essere stato utile e informativo. Se hai dubbi su questo articolo, chiedimelo nella sezione commenti qui sotto.

10+ nuove funzioni in Excel 2019 e 365Nota: sebbene le funzioni disponibili in Excel 2016 e precedenti siano sufficienti per eseguire qualsiasi tipo di calcolo e automazione, a volte le formule diventano complicate. Questi tipi di cose minori ma importanti sono risolte in Excel 2019 e 365.

17 cose su Excel VLOOKUP: VLOOKUP non è solo una formula di ricerca, è più di questo. Il VLOOKUP ha molte altre abilità e può essere utilizzato per molteplici scopi. In questo articolo, esploriamo tutti i possibili usi della funzione CERCA.VERT.

10+ grafici creativi avanzati di Excel per scuotere la tua dashboard: Excel è un potente strumento di visualizzazione dei dati che può essere utilizzato per creare grafici straordinari in Excel. Questi 15 grafici Excel avanzati possono essere utilizzati per ipnotizzare i tuoi colleghi e capi.

4 grafici creativi tra obiettivi e risultati in Excel: I grafici obiettivo vs risultato sono i grafici più basilari e importanti in qualsiasi attività commerciale. Quindi è meglio metterli nel modo più creativo possibile. Questi 4 grafici creativi possono rendere le tue dashboard rock la presentazione.

Articoli popolari:

50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.

Come utilizzare la funzione CERCA.VERT di Excel| Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.

Come usare Excel Funzione CONTA.SE| Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.

Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.