cuntenutu
Stu picculu tutoriale spiega cumu fà una funzione VPR (VLOOKUP) case-sensitive, mostra parechje altre formule chì Excel pò circà in una manera case-sensitive, è indica i punti di forza è debule di ogni funzione.
Pensu chì ogni utilizatore di Excel sapi quale funzione esegue a ricerca verticale. Hè propiu, hè una funzione VPR. Tuttavia, poche persone sanu chì VPR ùn hè micca sensible à u casu, vale à dì i caratteri minuscule è maiuscule sò identici per questu.
Eccu un esempiu rapidu chì mostra l'incapacità VPR ricunnosce u registru. Supponete in una cellula A1 cuntene u valore "bill" è a cellula A2 - "Bill", formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... cesserà a so ricerca nantu à "bill" postu chì quellu valore vene prima in a lista, è estrae u valore da a cellula B1.
Più tardi in questu articulu, vi mustraraghju cumu fà VPR sensible à u casu. In più, ampararemu uni pochi di più funzioni chì ponu fà ricerche di casu in Excel.
Cuminciamu cù u più simplice - Visu (CERCATE) è SUMPRODUCT (SUMPRODUCT), chì, sfurtunatamenti, anu parechje limitazioni significativu. Dopu, avemu da piglià un ochju più vicinu à a formula un pocu più cumplessa INDICE + MATCH (INDEX+MATCH), chì funziona perfettamente in ogni situazione è cù qualsiasi dataset.
A funzione VLOOKUP hè sensible à u casu
Comu sapete digià, a funzione di solitu VPR hè insensibile à u casu. Tuttavia, ci hè un modu per fà u casu sensitivu. Per fà questu, avete bisognu di aghjunghje una colonna ausiliaria à a tavula, cum'è mostra in l'esempiu seguente.
Supponete in una colonna B ci sò identificatori di u produttu (Articulu) è vulete caccià u prezzu di u pruduttu è u cumentu currispundente da e colonne C и D. U prublema hè chì l'identificatori cuntenenu caratteri minuscule è maiuscule. Per esempiu, i valori di cellula B4 (001Tvci3u) è B5 (001Tvci3U) sò diffirenti solu in u casu di l'ultimu caratteru, u и U rispittivamenti.
Comu pudete imagine, a formula di ricerca di solitu
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
ritornerà $ 90, postu chì u valore 001Tvci3u hè in a gamma di ricerca prima di 001Tvci3U. Ma ùn hè micca ciò chì avemu bisognu, hè?
Per circà cù una funzione VPR in Excel case sensitive, duverete aghjunghje una colonna d'aiutu è riempie e so celle cù a seguente formula (induve B hè a 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 rompe u valore desideratu in caratteri separati, rimpiazza ogni caratteru cù u so codice (per esempiu, invece di A à 65, invece a codice 97) e poi unisce questi codici in una catena unica di numeri.
Dopu quì, usemu una funzione simplice VPR per a ricerca sensible à u casu:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Funzionamentu propiu di a funzione VPR a sensibilità di u casu dipende da dui fattori:
- A colonna di l'aiutu deve esse a colonna più a manca in u intervallu visibile.
- U valore chì cercate deve cuntene un codice di caratteri invece di u valore veru.
Cumu aduprà a funzione CODE currettamente
A formula inserita in e cellule di a colonna ausiliaria assume chì tutti i vostri valori di ricerca anu u listessu numeru di caratteri. Se no, allora avete bisognu di cunnosce i numeri più chjuchi è più grande è aghjunghje quant'è funzioni IFERROR (IFERROR) quanti caratteri hè a diffarenza trà u valore cercatu u più curtu è u più longu.
Per esempiu, se u valore di ricerca più curtu hè 3 caratteri è u più longu hè 5 caratteri, utilizate sta 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 a funzione PSTR (MID) Fornite i seguenti argumenti:
- 1er argumentu - u testu (testu) hè u testu o riferenza di cellula chì cuntene i caratteri da esse estratti (in u nostru casu hè B2)
- 2er argumentu - start_num (start_position) hè a pusizione di u primu di quelli caratteri per esse estratti. tu entri 1 in a prima funzione PSTR, 2 - in a seconda funzione PSTR etc.
- 3er argumentu - num_chars (number_of_characters) - Specifica u numeru di caratteri per estrattà da u testu. Siccomu avemu solu bisognu di 1 caratteru tuttu u tempu, in tutte e funzioni scrivimu 1.
LIMITACI: funzioni VPR ùn hè micca a megliu suluzione per e ricerche di case-sensitive in Excel. Prima, l'aghjunzione di una colonna ausiliaria hè necessaria. Siconda, a formula faci un bonu travagliu solu se i dati sò homogenei, o u numeru esatta di caratteri in i valori cercati hè cunnisciutu. S'ellu ùn hè micca u vostru casu, hè megliu aduprà una di e suluzione chì avemu mostratu quì sottu.
Funzione LOOKUP per a ricerca sensible à u casu
funzioni Visu (LOOKUP) in relazione VPR, in ogni modu, a so sintassi permette e ricerche di case-sensitive senza aghjunghje una colonna ausiliaria. Per fà questu, utilizate Visu cumminata cù a funzione ESATTU (ESATTU).
Se pigliamu i dati da l'esempiu precedente (senza una colonna ausiliaria), allora a formula seguente hà da fà fronte à u compitu:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Ricerche di formule in u range A2: A7 corrispondenza esatta cù u valore di a cellula F2 case sensitive è torna u valore da a colonna B di a stessa fila.
cum'è VPRfunziunava Visu funziona ugualmente cù u testu è i valori numerichi, cum'è pudete vede in a screenshot sottu:
Impurtante! In ordine per a funzione Visu hà travagliatu bè, i valori in a colonna di ricerca deve esse ordinati in ordine crescente, vale à dì da u più chjucu à u più grande.
Lasciami spiegà brevemente cumu funziona a funzione ESATTU in a formula mostra sopra, cum'è questu hè u puntu chjave.
funzioni ESATTU compara i dui valori di testu in u 1u è u 2u argumentu è torna TRUE s'ellu sò esattamente listessi, o FALSE s'ellu ùn sò micca. Hè impurtante per noi chì a funzione ESATTU sensible à u casu.
Videmu cumu funziona a nostra formula VEDI + ESATTU:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- funzioni ESATTU compara u valore di a cellula F2 cù tutti l'elementi in una colonna A (A2: A7). Restituisce TRUE si trova una corrispondenza esatta, altrimenti FALSE.
- Siccomu dà u primu argumentu di funzione Visu valore TRUE, estrae u valore currispundente da a colonna specificata (in u nostru casu, a colonna B) solu s'ellu si trova una corrispondenza precisa, sensible à u casu.
Spergu chì sta spiegazione era chjara è avà capisce l'idea principale. Sì cusì, allora ùn avete micca difficultà cù altre funzioni chì analizaremu più, perchè. tutti travaglianu nantu à u listessu principiu.
LIMITACI: I dati in a colonna di ricerca deve esse ordinati in ordine crescente.
SUMPRODUCT - trova i valori di testu, sensible à u casu, ma torna solu numeri
Cume avete digià capitu da u titulu, SUMPRODUCT (SUMPRODUCT) hè una altra funzione di Excel chì vi aiuterà à fà una ricerca sensible à u casu, ma solu vultà i valori numerichi. Se sta opzione ùn vi cunvene micca, pudete immediatamente passà à u bundle INDICE + MATCH, chì dà una suluzione per ogni casu è per ogni tipu di dati.
Prima, lasciami spiegà brevemente a sintassi di sta funzione, questu vi aiuterà à capisce megliu a formula sensible à u casu chì seguita.
funzioni SUMPRODUCT multiplica l'elementi di e matrici datu è torna a somma di i risultati. A sintassi s'assumiglia cusì:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Siccomu avemu bisognu di una ricerca sensible à u casu, usemu a funzione ESATTU (EXACT) da l'esempiu precedente cum'è unu di i multiplicatori:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Cumu ricordi, ESATTU compara u valore di a cellula F2 cù tutti l'elementi in una colonna A. Restituisce TRUE si trova una corrispondenza esatta, altrimenti FALSE. In l'operazioni matematiche, Excel piglia TRUE cum'è 1, è FALSE per 0Più SUMPRODUCT multiplica sti numeri è somma i risultati.
Zeri ùn sò micca cuntatu perchè quandu si multiplicanu sempre dà 0. Fighjemu un ochju più vicinu à ciò chì succede quandu una partita esatta in una colonna A trovu è vultatu 1… Funzione SUMPRODUCT multiplica u numeru in a colonna B on 1 è torna u risultatu - esattamente u listessu numeru! Questu hè chì i risultati di l'altri prudutti sò cero, è ùn anu micca affettatu a summa resultante.
Sfurtunatamente a funzione SUMPRODUCT ùn pò micca travaglià cù i valori di testu è e date perchè ùn ponu esse multiplicate. In questu casu, riceverete un missaghju d'errore #VALU! (#VALORE!) cum'è in una cellula F4 in a stampa sottu:
LIMITACI: Ritorna solu i valori numerichi.
INDEX + MATCH - ricerca case-sensitive per ogni tipu di dati
Infine, simu vicinu à una formula di ricerca illimitata è case-sensitive chì travaglia cù qualsiasi data set.
Questu esempiu vene l'ultimu, micca perchè u megliu hè lasciatu per a dessert, ma perchè a cunniscenza acquistata da l'esempi precedenti vi aiuterà à capisce megliu è più veloce a formula sensible à u casu. INDICE + MATCH (INDICE+MATCH).
Cum'è probabilmente avete capitu, a cumminazzioni di funzioni PIÙ ESPOSTA и INDEX utilizatu in Excel cum'è una alternativa più flexible è putente per VPR. L'articulu Utilizendu INDEX è MATCH invece di VLOOKUP spiegherà perfettamente cumu queste funzioni funzionanu inseme.
Ricurdaraghju solu i punti chjave:
- funzioni PIÙ ESPOSTA (MATCH) cerca un valore in un intervallu determinatu è torna a so pusizioni relative, vale à dì u numeru di fila è / o colonna;
- Dopu, a funzione INDEX (INDICE) torna un valore da una colonna è / o fila specifica.
Per a formula INDICE + MATCH puderia cercà in casu di casu, basta à aghjunghje una funzione. Ùn hè micca difficiule di indovinà ciò chì hè di novu ESATTU (EXACTU):
=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 ESATTU travaglia in u listessu modu cum'è in cunghjunzione cù a funzione Visu, è dà u listessu risultatu:
Nota chì a formula INDICE + MATCH chjusu in parentesi ricci hè una formula array è duvete compie premendu Ctrl + Shift + Enter.
Perchè INDEX+MATCH hè a megliu suluzione per a ricerca sensible à u casu?
I vantaghji principali di u bundle INDEX и PIÙ ESPOSTA:
- Ùn hè micca bisognu di aghjunghje una colonna ausiliaria, à u cuntrariu VPR.
- Ùn hè micca bisognu di a colonna di ricerca per esse ordinata, à u cuntrariu Visu.
- Funziona cù tutti i tipi di dati - numeri, testu è date.
Sta formula pare perfetta, ùn hè micca? In fatti, ùn hè micca. È hè per quessa.
Assumimu chì a cellula in a colonna di u valore di ritornu assuciata cù u valore di ricerca hè viota. Chì risultatu serà a formula? Innò? Videmu ciò chì a formula torna veramente:
Oops, a formula torna zero! Stu pò micca esse un gran prublema s'è vo sò u travagliu cù valori testu puri. In ogni casu, se a tavula cuntene numeri, cumpresi zeri "veri", questu diventa un prublema.
In fatti, tutte e altre formule di ricerca (VLOOKUP, LOOKUP è SUMPRODUCT) chì avemu discututu prima si cumportanu a stessa manera. Ma vulete a formula perfetta, nò?
Per fà una formula sensible à u casu INDICE + MATCH perfettu, mette in una funzione IF (IF) chì testerà una cellula cù un valore di ritornu è restituverà un risultatu viotu s'ellu hè viotu:
=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 hè una colonna cù i valori di ritornu
- 1+ hè un numeru chì gira a pusizione relative di a cellula restituita da a funzione PIÙ ESPOSTA, à l'indirizzu veru di a cellula. Per esempiu, in a nostra funzione PIÙ ESPOSTA array di ricerca datu A2: A7, vale à dì a pusizioni relative di a cellula A2 sarà 1, perchè hè u primu in l'array. Ma a pusizione attuale di a cellula A2 in a colonna hè 2, cusì aghjustemu 1per fà a diferenza è per avè a funzione INDIRETTA (INDIRECT) hà recuperatu u valore da a cellula desiderata.
L'imaghjini quì sottu mostranu a formula curretta, sensible à u casu INDICE + MATCH In azzione. Ritorna un risultatu viotu se a cellula restituita hè viota.
Aghju riscritto a formula in colonne B: Dper adattà a barra di formula nantu à a screenshot.
A formula torna 0se a cellula restituita cuntene zero.
Se vulete u ligame INDEX и PIÙ ESPOSTA mostra un messagiu quandu u valore di ritornu hè viotu, pudete scrive in l'ultimi virgulette ("") di a formula, per esempiu, cusì:
=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.")
