Contenuti
- La funzione CERCA.VERT fa distinzione tra maiuscole e minuscole
- Funzione CERCA per la ricerca con distinzione tra maiuscole e minuscole
- SUMPRODUCT – trova i valori di testo, con distinzione tra maiuscole e minuscole, ma restituisce solo numeri
- INDEX + MATCH – ricerca con distinzione tra maiuscole e minuscole per qualsiasi tipo di dati
Questo piccolo tutorial spiega come creare una funzione VPR (VLOOKUP) con distinzione tra maiuscole e minuscole, mostra diverse altre formule che Excel può cercare con distinzione tra maiuscole e minuscole e sottolinea i punti di forza e di debolezza di ciascuna funzione.
Immagino che ogni utente di Excel sappia quale funzione esegue la ricerca verticale. Esatto, è una funzione VPR. Tuttavia, poche persone lo sanno VPR non fa distinzione tra maiuscole e minuscole, ovvero i caratteri minuscoli e maiuscoli sono identici.
Ecco un rapido esempio che dimostra l'incapacità VPR riconoscere registro. Supponiamo in una cella A1 contiene il valore “fattura” e la cella A2 – “Fattura”, formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... interromperà la sua ricerca su "fattura" poiché quel valore è al primo posto nell'elenco ed estrae il valore dalla cella B1.
Più avanti in questo articolo, ti mostrerò come fare VPR maiuscole e minuscole. Inoltre, impareremo alcune altre funzioni che possono eseguire ricerche con distinzione tra maiuscole e minuscole in Excel.
Inizieremo con il più semplice – VISTA (CERCA) e MATR.SOMMA.PRODOTTO (SUMPRODUCT), che, purtroppo, presentano diverse limitazioni significative. Successivamente, daremo un'occhiata più da vicino alla formula leggermente più complessa INDICE+PARTENZA (INDEX+MATCH), che funziona perfettamente in ogni situazione e con qualsiasi dataset.
La funzione CERCA.VERT fa distinzione tra maiuscole e minuscole
Come già sapete, la solita funzione VPR non fa distinzione tra maiuscole e minuscole. Tuttavia, c'è un modo per fare distinzione tra maiuscole e minuscole. Per fare ciò, è necessario aggiungere una colonna ausiliaria alla tabella, come mostrato nell'esempio seguente.
Supponiamo in una colonna B ci sono identificatori di prodotto (Articolo) e vuoi estrarre il prezzo del prodotto e il commento corrispondente dalle colonne C и D. Il problema è che gli identificatori contengono sia caratteri minuscoli che maiuscoli. Ad esempio, i valori delle celle B4 (001Tvci3u) e B5 (001Tvci3U) differiscono solo nel caso dell'ultimo carattere, u и U rispettivamente.
Come puoi immaginare, la solita formula di ricerca
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
sarà di ritorno € 90, poiché il valore 001Tvci3u è nell'intervallo di ricerca prima di 001Tvci3U. Ma non è quello di cui abbiamo bisogno, vero?
Per cercare con una funzione VPR in Excel con distinzione tra maiuscole e minuscole, dovrai aggiungere una colonna helper e riempire le sue celle con la seguente formula (dove B è la colonna di ricerca):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Questa formula suddivide il valore desiderato in caratteri separati, sostituisce ogni carattere con il relativo codice (ad esempio, invece di A a 65 anni, invece a codice 97) e quindi combina questi codici in una stringa univoca di numeri.
Successivamente, utilizziamo una semplice funzione VPR per la ricerca con distinzione tra maiuscole e minuscole:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Corretto funzionamento della funzione VPR la distinzione tra maiuscole e minuscole dipende da due fattori:
- La colonna helper deve essere la colonna più a sinistra nell'intervallo visualizzabile.
- Il valore che stai cercando deve contenere un codice carattere invece del valore reale.
Come utilizzare correttamente la funzione CODICE
La formula inserita nelle celle della colonna ausiliaria presuppone che tutti i valori di ricerca abbiano lo stesso numero di caratteri. In caso contrario, è necessario conoscere i numeri più piccoli e più grandi e aggiungere tutte le funzionalità IFERROR (IFERROR) quanti caratteri è la differenza tra il valore cercato più breve e quello più lungo.
Ad esempio, se il valore di ricerca più breve è 3 caratteri e il più lungo è 5 caratteri, utilizzare questa formula:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Per la funzione PSTR (MID) Fornisci i seguenti argomenti:
- 1° argomento – testo (testo) è il testo o il riferimento di cella contenente i caratteri da estrarre (nel nostro caso è B2)
- 2° argomento – inizio_num (start_position) è la posizione del primo di quei caratteri da estrarre. tu entri 1 nella prima funzione PSTR, 2 – nella seconda funzione PSTR ecc.
- 3° argomento – num_caratteri (number_of_characters) – Specifica il numero di caratteri da estrarre dal testo. Poiché abbiamo bisogno solo di 1 carattere tutto il tempo, in tutte le funzioni scriviamo 1.
LIMITAZIONI: Funzione VPR non è la soluzione migliore per le ricerche con distinzione tra maiuscole e minuscole in Excel. Innanzitutto, è necessaria l'aggiunta di una colonna ausiliaria. In secondo luogo, la formula fa un buon lavoro solo se i dati sono omogenei o se si conosce il numero esatto di caratteri nei valori cercati. Se questo non è il tuo caso, è meglio utilizzare una delle soluzioni che mostriamo di seguito.
Funzione CERCA per la ricerca con distinzione tra maiuscole e minuscole
Funzione VISTA (CERCA) relativo VPR, tuttavia la sua sintassi consente ricerche con distinzione tra maiuscole e minuscole senza aggiungere una colonna ausiliaria. Per fare questo, usa VISTA combinato con la funzione ESATTO (ESATTO).
Se prendiamo i dati dall'esempio precedente (senza una colonna ausiliaria), la seguente formula farà fronte all'attività:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Ricerca di formule nell'intervallo LA2: LA7 corrispondenza esatta con il valore della cella F2 con distinzione tra maiuscole e minuscole e restituisce il valore dalla colonna B della stessa riga.
Come VPRfunction VISTA funziona allo stesso modo con valori di testo e numerici, come puoi vedere nello screenshot qui sotto:
Importante! In ordine per la funzione VISTA funzionato correttamente, i valori nella colonna di ricerca dovrebbero essere ordinati in ordine crescente, cioè dal più piccolo al più grande.
Lascia che ti spieghi brevemente come funziona la funzione ESATTO nella formula mostrata sopra, poiché questo è il punto chiave.
Funzione ESATTO confronta i due valori di testo nel 1° e 2° argomento e restituisce VERO se sono esattamente gli stessi, o FALSO se non lo sono. È importante per noi che la funzione ESATTO che tiene conto del maiuscolo o minuscolo.
Vediamo come funziona la nostra formula VISUALIZZA+ESATTO:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funzione ESATTO confronta il valore della cella F2 con tutti gli elementi in una colonna A (LA2:LA7). Restituisce TRUE se viene trovata una corrispondenza esatta, altrimenti FALSE.
- Dal momento che dai il primo argomento di funzione VISTA valore TRUE, estrae il valore corrispondente dalla colonna specificata (nel nostro caso, colonna B) solo se viene trovata una corrispondenza esatta, case sensitive.
Spero che questa spiegazione sia stata chiara e che ora tu capisca l'idea principale. In tal caso, non avrai alcuna difficoltà con altre funzioni che analizzeremo ulteriormente, perché. funzionano tutti secondo lo stesso principio.
LIMITAZIONI: I dati nella colonna di ricerca devono essere ordinati in ordine crescente.
SUMPRODUCT – trova i valori di testo, con distinzione tra maiuscole e minuscole, ma restituisce solo numeri
Come hai già capito dal titolo, MATR.SOMMA.PRODOTTO (SOMMAPRODOTTO) è un'altra funzione di Excel che ti aiuterà a fare una ricerca con distinzione tra maiuscole e minuscole, ma restituirà solo valori numerici. Se questa opzione non ti soddisfa, puoi procedere immediatamente al pacchetto INDICE+PARTENZA, che offre una soluzione per ogni caso e per qualsiasi tipo di dato.
Per prima cosa, lascia che ti spieghi brevemente la sintassi di questa funzione, questo ti aiuterà a capire meglio la formula che fa distinzione tra maiuscole e minuscole che segue.
Funzione MATR.SOMMA.PRODOTTO moltiplica gli elementi degli array dati e restituisce la somma dei risultati. La sintassi è simile a questa:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Poiché abbiamo bisogno di una ricerca con distinzione tra maiuscole e minuscole, utilizziamo la funzione ESATTO (ESATTO) dall'esempio precedente come uno dei moltiplicatori:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Come ricordi, ESATTO confronta il valore della cella F2 con tutti gli elementi in una colonna A. Restituisce TRUE se viene trovata una corrispondenza esatta, altrimenti FALSE. Nelle operazioni matematiche, Excel assume TRUE come 1, e FALSO per 0Inoltre MATR.SOMMA.PRODOTTO moltiplica questi numeri e somma i risultati.
Gli zeri non si contano perché moltiplicati danno sempre 0. Diamo un'occhiata più da vicino a cosa succede quando una corrispondenza esatta in una colonna A trovato e restituito 1… Funzione MATR.SOMMA.PRODOTTO moltiplica il numero nella colonna B on 1 e restituisce il risultato: esattamente lo stesso numero! Questo perché i risultati degli altri prodotti sono zero e non influiscono sulla somma risultante.
Purtroppo la funzione MATR.SOMMA.PRODOTTO non può funzionare con valori di testo e date in quanto non possono essere moltiplicati. In questo caso, riceverai un messaggio di errore #VALORE! (#VALORE!) come in una cella F4 nella foto qui sotto:
LIMITAZIONI: Restituisce solo valori numerici.
INDEX + MATCH – ricerca con distinzione tra maiuscole e minuscole per qualsiasi tipo di dati
Infine, siamo vicini a una formula di ricerca illimitata e con distinzione tra maiuscole e minuscole che funziona con qualsiasi set di dati.
Questo esempio viene per ultimo, non perché il meglio sia lasciato per il dessert, ma perché le conoscenze acquisite dagli esempi precedenti ti aiuteranno a capire meglio e più velocemente la formula case-sensitive. INDICE+PARTENZA (INDICE+PARTENZA).
Come probabilmente hai intuito, la combinazione di funzioni PIÙ ESPOSTO и INDICE utilizzato in Excel come alternativa più flessibile e potente per VPR. L'articolo Usare INDEX e MATCH invece di VLOOKUP spiegherà perfettamente come queste funzioni funzionano insieme.
Riassumo solo i punti chiave:
- Funzione PIÙ ESPOSTO (CONFRONTA) ricerca un valore in un determinato intervallo e ne restituisce la posizione relativa, ovvero il numero di riga e/o colonna;
- Successivamente, la funzione INDICE (INDICE) restituisce un valore da una colonna e/o riga specificata.
Alla formula INDICE+PARTENZA potrebbe cercare con distinzione tra maiuscole e minuscole, devi solo aggiungere una funzione ad esso. Non è difficile indovinare di nuovo di cosa si tratta ESATTO (ESATTO):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
In questa formula ESATTO funziona allo stesso modo della funzione VISTA, e dà lo stesso risultato:
Si noti che la formula INDICE+PARTENZA racchiusa tra parentesi graffe è una formula di matrice e devi completarla premendo CTRL + MAIUSC + INVIO.
Perché INDEX+MATCH è la soluzione migliore per la ricerca con distinzione tra maiuscole e minuscole?
I principali vantaggi del pacchetto INDICE и PIÙ ESPOSTO:
- Non richiede l'aggiunta di una colonna ausiliaria, a differenza di VPR.
- Non richiede l'ordinamento della colonna di ricerca, a differenza di VISTA.
- Funziona con tutti i tipi di dati: numeri, testo e date.
Questa formula sembra perfetta, vero? In realtà, non lo è. Ed ecco perché.
Si supponga che la cella nella colonna del valore restituito associata al valore di ricerca sia vuota. Quale risultato restituirà la formula? No? Vediamo cosa restituisce effettivamente la formula:
Ops, la formula restituisce zero! Questo potrebbe non essere un grosso problema se stai lavorando con valori di testo puro. Tuttavia, se la tabella contiene numeri, inclusi zeri "reali", questo diventa un problema.
In effetti, tutte le altre formule di ricerca (VLOOKUP, LOOKUP e SUMPRODUCT) di cui abbiamo discusso in precedenza si comportano allo stesso modo. Ma vuoi la formula perfetta, giusto?
Per rendere una formula case sensitive INDICE+PARTENZA perfetto, mettilo in una funzione IF (IF) che testerà una cella con un valore restituito e restituirà un risultato vuoto se è vuoto:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
In questa formula:
- B è una colonna con valori di ritorno
- 1+ è un numero che trasforma la posizione relativa della cella restituita dalla funzione PIÙ ESPOSTO, all'indirizzo reale della cella. Ad esempio, nella nostra funzione PIÙ ESPOSTO matrice di ricerca data LA2: LA7, ovvero la posizione relativa della cella A2 volere 1, perché è il primo nell'array. Ma la posizione effettiva della cella A2 nella colonna è 2, quindi aggiungiamo 1per fare la differenza e per avere la funzione INDIRETTI (INDIRETTO) ha recuperato il valore dalla cella desiderata.
Le immagini seguenti mostrano la formula corretta con distinzione tra maiuscole e minuscole INDICE+PARTENZA In azione. Restituisce un risultato vuoto se la cella restituita è vuota.
Ho riscritto la formula in colonne B:Dper adattare la barra della formula allo screenshot.
La formula ritorna 0se la cella restituita contiene zero.
Se vuoi il link INDICE и PIÙ ESPOSTO visualizzato un messaggio quando il valore restituito è vuoto, puoi scriverlo tra le ultime virgolette ("") della formula, ad esempio, in questo modo:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
