Come usare CERCA.VERT, INDICE, ... in Excel dovresti sapere

Le funzioni CERCA.VERT, INDICE, CONFRONTA, ... sono funzioni piuttosto complesse ma sono efficaci nel data mining in Excel . In questo articolo, ti guiderò in dettaglio come utilizzare VLOOKUP e le funzioni correlate in modo efficace.

1. Funzione CERCA.VERT

Utilizzo : trova un valore specificato nella prima colonna ed estrae i dati corrispondenti dalla stessa riga in un'altra colonna. Questa è una delle funzioni più popolari e fornisce un ottimo supporto per complesse attività di ufficio.

Formula : = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

In cui :

+ Lookup_value : il valore da cercare.

+ Table_array : due o più colonne di dati.

+ Col_index_num : numero di colonne per estrarre i dati.

+ Range_lookup : determina se la ricerca è completamente accurata (FALSE) o solo relativamente accurata (TRUE o omessa).

Esempio : utilizziamo la funzione CERCA.VERT per trovare il bonus di ogni dipendente. Il valore da cercare è Posizione cella C2; L'array da rilevare è l'array F1: G3. Quando si immette la formula = CERCA.VERT (C2, $ F $ 1: $ G $ 3,2,0) (1) il risultato è STAFF 2000 e ROOMER 5000 (2).

2. Funzione INDICE

Utilizzo : restituisce un riferimento a una cella all'interno di un array in base al numero di righe e colonne specificato.

Formula : = INDEX (array, row_num, [column_num])

Di cui : Matrice (obbligatorio): un intervallo di celle o una costante di matrice.

 

 

Esempio : quando abbiamo bisogno di ottenere il valore della prima riga, la seconda colonna della tabella, usiamo la funzione INDICE . L'array è l'array che dobbiamo rilevare è B2: C5; Row_num è la riga 1; Column_num è la colonna 2. Quando si immette la formula INDEX (B2: C5,1,2,1) (1) il risultato è "HEAD OF ROOM" (2).

3. Funzione MATCH

Utilizzo : cerca un valore specifico in un intervallo di celle e mostra la posizione relativa di quel valore.

Formula : = MATCH (lookup_value, lookup_array, [match_type])

In cui :

+ lookup_value (obbligatorio) : il valore che vuoi trovare nella matrice di ricerca. Modificare il valore di ricerca Il numero può essere un valore (numero, testo o valore logico) o un riferimento di cella a un numero, testo o valore logico.

+ lookup_array (obbligatorio) : l'intervallo di celle in cui cercare.

+ match_type (opzionale) : Number -1, 0 o 1. L'argomento del tipo di corrispondenza specifica il modo in cui Excel abbina il valore di ricerca ai valori nella matrice di ricerca. Il valore predefinito per questo argomento è 1.

Ad esempio : quando abbiamo bisogno di sapere in quale posizione si trova il PANNO, usiamo la funzione CONFRONTA . Quando si immette la formula = MATCH (FF2 & "*", "B2: B7", 0) (1), il risultato è 3 (2).

3. Funzione INDIRETTA

Utilizzo : restituisce un riferimento di cella o un intervallo specificato da una stringa di testo. Utilizzare la funzione INDIRETTO quando si desidera modificare il riferimento a una cella in una formula senza modificare la formula stessa.

Formula : = INDIRECT (ref_text, [a1])

In cui :

+ Ref_text (obbligatorio) : riferimento a una cella contenente lo stile di riferimento A1, lo stile di riferimento R1C1 o un riferimento alla cella come stringa di testo.

+ Se il testo di riferimento non è un riferimento di cella valido, INDIRETTO restituisce il valore di errore #RIF!. .

+ Se ref_text fa riferimento a un'altra cartella di lavoro (un riferimento esterno), quella cartella di lavoro deve essere aperta. Se la cartella di lavoro di origine non è aperta, INDIRETTO restituisce il valore di errore #RIF!. .

 

 

Ad esempio : quando devi solo calcolare la commissione e vuoi trascinare, rilasciare o spostare la casella dei risultati in un'altra posizione senza modificare la formula del denaro, utilizziamo INDIRETTO . Quando si immette la formula

= INDIRETTO ("B2", VERO) * INDIRETTO ("C2", VERO) (1) darà il risultato 700 * 100 = 70000 (2).

4. Funzione OFFSET

Utilizzo : restituisce il riferimento a un intervallo in base a un numero di righe specificato e un numero di colonne specificato da una cella o un intervallo di celle. Il riferimento restituito può essere una singola cella o un intervallo di celle. È possibile specificare il numero di righe e colonne da restituire.

Formula : = OFFSET (riferimento, righe, colonne, [altezza], [larghezza])

In cui :

+ Riferimento (obbligatorio) : l'area di riferimento su cui si desidera basare la distanza di riferimento. L'intervallo di riferimento deve fare riferimento a una cella o a un intervallo di celle adiacenti; altrimenti OFFSET restituisce il valore di errore #VALORE !. .

+ Righe (obbligatorio) : il numero di righe, in alto o in basso, a cui fare riferimento nella cella in alto a sinistra. Le righe possono essere positive (che significa sotto il riferimento di partenza) o negative (che significa sopra il riferimento di partenza).

+ Col (obbligatorio) : numero di colonne, a sinistra oa destra, a cui si desidera fare riferimento alla cella nell'angolo superiore sinistro del risultato. Le colonne possono essere positive o negative.

+ Altezza (opzionale) : l'altezza, in numero di righe, che si desidera avere per il riferimento restituito. L'altezza deve essere un numero positivo.

+ Larghezza (opzionale) : la larghezza, nel numero di colonne, che vuoi avere per il riferimento restituito. La larghezza deve essere un numero positivo.

Esempio : utilizziamo OFFSET per localizzare i valori, quindi utilizziamo la funzione SUM per calcolare la somma. La cella a cui ci riferiamo è A1; rispetto ad A1, le righe dell'intervallo di riferimento sono inferiori a 1 cella, quindi le righe sono 1; rispetto ad A1, il Cols sinistro è 1 cella, quindi Cols è 1; Altezza prendiamo 3 linee; Larghezza otteniamo 2 colonne. Quindi il risultato quando si sommano tutti i valori nella regione è 2025.

5. Funzione TRANSPOSE

Utilizzo : converte un intervallo orizzontale di celle in un intervallo verticale e viceversa, ovvero converte le righe in colonne e le colonne in righe.

Formula : = TRANSPOSE (array)

Fare

+ Passaggio 1 : seleziona celle vuote.

+ Passaggio 2 : Invio = TRANSPOSE (B2: C4).

+ Passaggio 3 : inserisci l'intervallo di celle originali.

+ Passaggio 4 : infine, premere CTRL + MAIUSC + INVIO.

Esempio : quando dobbiamo convertire l'intero listino prezzi del prodotto da verticale a orizzontale, utilizziamo la funzione TRANSPOSE con l'array di valori da B2 a C4. Quando si immette la formula = TRANSPOSE (B2: C4) (1) darà il risultato come mostrato (2).

6. Funzione HYPERLINK

Utilizza : crea un collegamento ipertestuale a un documento archiviato in Intranet o in Internet.

Formula : = HYPERLINK (link_location, [friendly_name]).

In cui :

Link_location può fare riferimento a una posizione in un documento, come una cella specifica o un intervallo denominato in un foglio di lavoro o in una cartella di lavoro di Excel, oppure a un segnalibro in un documento di Microsoft Word.

Il percorso può essere un file archiviato sul disco rigido. Il percorso può anche essere un percorso UNC (Universal Naming Convention) sul server (in Microsoft Excel per Windows).

 

 

 

 

Esempio : i collegamenti lunghi e confusi saranno fastidiosi per l'utente. Quindi usa HYPERLINK per rendere l'interfaccia più facile da usare. Inserendo la formula = HYPERLINK (A1, "LINK GOOGLE") (1) si otterrà LINK GOOGLE (2).

L'articolo precedente ha guidato CERCA.VERT e funzioni correlate. Spero che l'articolo di cui sopra ti sia utile.