contingut
- La funció BUSCARV distingeix entre majúscules i minúscules
- Funció LOOKUP per a la cerca sensible a majúscules i minúscules
- SUMPRODUCT: troba valors de text, distingeix entre majúscules i minúscules, però només retorna números
- INDEX + MATCH: cerca que distingeix entre majúscules i minúscules per a qualsevol tipus de dades
Aquest petit tutorial explica com fer una funció VPR (VLOOKUP) distingeix entre majúscules i minúscules, mostra diverses altres fórmules que Excel pot cercar de manera sensible a majúscules i minúscules i assenyala els punts forts i febles de cada funció.
Suposo que cada usuari d'Excel sap quina funció realitza la cerca vertical. És cert, és una funció VPR. No obstant això, poca gent ho sap VPR no distingeix entre majúscules i minúscules, és a dir, els caràcters minúscules i majúscules són idèntics per a això.
Aquí teniu un exemple ràpid que demostra la incapacitat VPR registre de reconeixement. Suposem en una cel·la A1 conté el valor "factura" i la cel·la A2 – “Bill”, fórmula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... aturarà la cerca a "factura" ja que aquest valor és el primer a la llista i extreu el valor de la cel·la B1.
Més endavant en aquest article, us mostraré com fer-ho VPR cas delicat. A més, aprendrem algunes funcions més que poden realitzar cerques sensibles a majúscules i minúscules a Excel.
Començarem pel més senzill: MÉS INFO (CERCAR) i SUMPRODUCT (SUMPRODUCT), que, malauradament, tenen diverses limitacions importants. A continuació, analitzarem la fórmula una mica més complexa ÍNDEX+CONFIG (INDEX+MATCH), que funciona perfectament en qualsevol situació i amb qualsevol conjunt de dades.
La funció BUSCARV distingeix entre majúscules i minúscules
Com ja sabeu, la funció habitual VPR no distingeix entre majúscules i minúscules. Tanmateix, hi ha una manera de distingir entre majúscules i minúscules. Per fer-ho, cal afegir una columna auxiliar a la taula, tal com es mostra a l'exemple següent.
Suposem en una columna B hi ha identificadors de producte (Article) i voleu extreure el preu del producte i el comentari corresponent de les columnes C и D. El problema és que els identificadors contenen caràcters tant en minúscules com en majúscules. Per exemple, els valors de les cel·les B4 (001Tvci3u) i B5 (001Tvci3U) només difereixen en el cas de l'últim caràcter, u и U respectivament.
Com us podeu imaginar, la fórmula de cerca habitual
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
tornarà $ 90, ja que el valor 001Tvci3u està a l'interval de cerca abans de 001Tvci3U. Però això no és el que necessitem, oi?
Per cercar amb una funció VPR amb la distinció entre majúscules i minúscules d'Excel, haureu d'afegir una columna auxiliar i omplir les seves cel·les amb la fórmula següent (on B és la columna de cerca):
=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));"")
Aquesta fórmula divideix el valor desitjat en caràcters separats, substitueix cada caràcter pel seu codi (per exemple, en lloc de A als 65, en canvi a codi 97) i després combina aquests codis en una cadena única de números.
Després d'això, fem servir una funció senzilla VPR per a la cerca sensible a majúscules i minúscules:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Funcionament correcte de la funció VPR La distinció entre majúscules i minúscules depèn de dos factors:
- La columna d'ajuda ha de ser la columna més a l'esquerra de l'interval visible.
- El valor que cerqueu ha de contenir un codi de caràcters en lloc del valor real.
Com utilitzar correctament la funció CODI
La fórmula inserida a les cel·les de la columna auxiliar suposa que tots els vostres valors de cerca tenen el mateix nombre de caràcters. Si no, heu de conèixer els nombres més petits i més grans i afegir tantes funcions SI ERROR (IFERROR) quants caràcters és la diferència entre el valor cercat més curt i el més llarg.
Per exemple, si el valor de cerca més curt és de 3 caràcters i el més llarg és de 5 caràcters, utilitzeu aquesta fórmula:
=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 la funció PSTR (MID) Proporcioneu els arguments següents:
- 1r argument - text (text) és la referència de text o cel·la que conté els caràcters que s'han d'extreure (en el nostre cas és B2)
- 2r argument - nombre_inici (posició_inici) és la posició del primer d'aquests caràcters a extreure. entres 1 en la primera funció PSTR, 2 – en la segona funció PSTR etcètera...
- 3r argument - nombre_caràters (nombre_de_caràcters): especifica el nombre de caràcters que cal extreure del text. Com que només necessitem 1 caràcter tot el temps, en totes les funcions escrivim 1.
LIMITACIONS: function VPR no és la millor solució per a cerques que distingeixen entre majúscules i minúscules a Excel. En primer lloc, cal afegir una columna auxiliar. En segon lloc, la fórmula només funciona bé si les dades són homogènies o es coneix el nombre exacte de caràcters dels valors cercats. Si aquest no és el vostre cas, és millor utilitzar una de les solucions que us mostrem a continuació.
Funció LOOKUP per a la cerca sensible a majúscules i minúscules
function MÉS INFO (CERCA) relacionat VPR, però la seva sintaxi permet cerques sensibles a majúscules i minúscules sense afegir una columna auxiliar. Per fer-ho, utilitzeu MÉS INFO combinat amb la funció EXACT (EXACTE).
Si agafem les dades de l'exemple anterior (sense columna auxiliar), la fórmula següent farà front a la tasca:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Cerques de fórmules a l'interval A2: A7 coincidència exacta amb el valor de la cel·la F2 distingeix entre majúscules i minúscules i retorna el valor de la columna B de la mateixa fila.
like VPRfunction MÉS INFO funciona igual amb valors de text i numèrics, com podeu veure a la captura de pantalla següent:
Important Per a la funció MÉS INFO funcionat correctament, els valors de la columna de cerca s'han d'ordenar en ordre ascendent, és a dir, del més petit al més gran.
Permeteu-me explicar breument com funciona la funció EXACT en la fórmula que es mostra més amunt, ja que aquest és el punt clau.
function EXACT compara els dos valors de text en el 1r i el 2n argument i retorna TRUE si són exactament iguals, o FALSE si no ho són. És important per a nosaltres que la funció EXACT cas delicat.
Vegem com funciona la nostra fórmula VISUALITZAR+EXACTE:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- function EXACT compara el valor de la cel·la F2 amb tots els elements en una columna A (A2:A7). Retorna TRUE si es troba una coincidència exacta, en cas contrari, FALSE.
- Com que doneu l'argument de la primera funció MÉS INFO valor TRUE, extreu el valor corresponent de la columna especificada (en el nostre cas, columna B) només si es troba una coincidència exacta, distingeix entre majúscules i minúscules.
Espero que aquesta explicació sigui clara i ara entengueu la idea principal. Si és així, no tindreu cap dificultat amb altres funcions que analitzarem més endavant, perquè. tots funcionen amb el mateix principi.
LIMITACIONS: Les dades de la columna de cerca s'han d'ordenar en ordre ascendent.
SUMPRODUCT: troba valors de text, distingeix entre majúscules i minúscules, però només retorna números
Com ja has entès pel títol, SUMPRODUCT (SUMPRODUCT) és una altra funció d'Excel que us ajudarà a fer una cerca que distingeix entre majúscules i minúscules, però només retornarà valors numèrics. Si aquesta opció no us convé, podeu passar immediatament al paquet ÍNDEX+CONFIG, que dóna una solució per a qualsevol cas i per a qualsevol tipus de dades.
En primer lloc, permeteu-me explicar breument la sintaxi d'aquesta funció, això us ajudarà a entendre millor la fórmula que distingeix entre majúscules i minúscules que segueix.
function SUMPRODUCT multiplica els elements de les matrius donades i retorna la suma dels resultats. La sintaxi té aquest aspecte:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Com que necessitem una cerca que distingeix entre majúscules i minúscules, fem servir la funció EXACT (EXACTE) de l'exemple anterior com un dels multiplicadors:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Com recordeu, EXACT compara el valor de la cel·la F2 amb tots els elements en una columna A. Retorna TRUE si es troba una coincidència exacta, en cas contrari, FALSE. En operacions matemàtiques, Excel pren TRUE com a 1, i FALSE per 0Més lluny SUMPRODUCT multiplica aquests nombres i suma els resultats.
Els zeros no es compten perquè quan es multipliquen sempre donen 0. Mirem més de prop què passa quan una coincidència exacta en una columna A trobat i retornat 1… Funció SUMPRODUCT multiplica el nombre de la columna B on 1 i retorna el resultat, exactament el mateix número! Això es deu al fet que els resultats dels altres productes són zero i no afecten la suma resultant.
Malauradament la funció SUMPRODUCT no pot treballar amb valors de text i dates ja que no es poden multiplicar. En aquest cas, rebreu un missatge d'error #VALUE! (#VALOR!) com en una cel·la F4 a la imatge següent:
LIMITACIONS: Retorna només valors numèrics.
INDEX + MATCH: cerca que distingeix entre majúscules i minúscules per a qualsevol tipus de dades
Finalment, estem a prop d'una fórmula de cerca il·limitada i sensible a majúscules i minúscules que funciona amb qualsevol conjunt de dades.
Aquest exemple és el darrer, no perquè quedi el millor per a les postres, sinó perquè els coneixements adquirits amb els exemples anteriors us ajudaran a entendre millor i més ràpidament la fórmula que distingeix entre majúscules i minúscules. ÍNDEX+CONFIG (ÍNDEX+COMPARACIÓ).
Com probablement heu endevinat, la combinació de funcions MÉS EXPOSADA и ÍNDEX s'utilitza a Excel com una alternativa més flexible i potent per a VPR. L'article Ús d'INDEX i MATCH en lloc de VLOOKUP explicarà perfectament com funcionen aquestes funcions juntes.
Només resumiré els punts clau:
- function MÉS EXPOSADA (MATCH) cerca un valor en un interval determinat i retorna la seva posició relativa, és a dir, el número de fila i/o columna;
- A continuació, la funció ÍNDEX (INDEX) retorna un valor d'una columna i/o fila especificada.
Per formular ÍNDEX+CONFIG podríeu cercar amb distinció entre majúscules i minúscules, només heu d'afegir-hi una funció. No és difícil endevinar què és de nou EXACT (EXACTE):
=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))
En aquesta fórmula EXACT funciona de la mateixa manera que en conjunció amb la funció MÉS INFO, i dóna el mateix resultat:
Tingueu en compte que la fórmula ÍNDEX+CONFIG tancada entre claus hi ha una fórmula de matriu i l'heu de completar prement Ctrl + Maj + Retorn.
Per què INDEX+MATCH és la millor solució per a la cerca que distingeix entre majúscules i minúscules?
Els principals avantatges del paquet ÍNDEX и MÉS EXPOSADA:
- No requereix afegir una columna auxiliar, a diferència VPR.
- No requereix que la columna de cerca estigui ordenada, a diferència MÉS INFO.
- Funciona amb tot tipus de dades: números, text i dates.
Aquesta fórmula sembla perfecta, no? De fet, no ho és. I per això.
Suposem que la cel·la de la columna del valor de retorn associada al valor de cerca està buida. Quin resultat retornarà la fórmula? No? Vegem què retorna realment la fórmula:
Vaja, la fórmula retorna zero! Pot ser que això no sigui un gran problema si treballeu amb valors de text purs. Tanmateix, si la taula conté nombres, inclosos zeros "reals", això es converteix en un problema.
De fet, totes les altres fórmules de cerca (LOOKUP, LOOKUP i SUMPRODUCT) que hem comentat anteriorment es comporten de la mateixa manera. Però vols la fórmula perfecta, oi?
Per fer que una fórmula distingeix majúscules i minúscules ÍNDEX+CONFIG perfecte, posa-ho en funció IF (SI) que provarà una cel·la amb un valor de retorn i retornarà un resultat buit si està buida:
=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));"")
En aquesta fórmula:
- B és una columna amb valors de retorn
- 1+ és un nombre que gira la posició relativa de la cel·la que retorna la funció MÉS EXPOSADA, a l'adreça real de la cel·la. Per exemple, a la nostra funció MÉS EXPOSADA matriu de cerca donada A2: A7, és a dir, la posició relativa de la cèl·lula A2 voluntat 1, perquè és el primer de la matriu. Però la posició real de la cèl·lula A2 a la columna és 2, així que afegim 1compensar la diferència i tenir la funció INDIRECTE (INDIRECT) ha recuperat el valor de la cel·la desitjada.
Les imatges següents mostren la fórmula corregida que distingeix entre majúscules i minúscules ÍNDEX+CONFIG En acció. Retorna un resultat buit si la cel·la retornada està buida.
Vaig reescriure la fórmula en columnes B:Dper ajustar-se a la barra de fórmules de la captura de pantalla.
Torna la fórmula 0si la cel·la retornada conté zero.
Si vols l'enllaç ÍNDEX и MÉS EXPOSADA mostra algun missatge quan el valor de retorn està buit, podeu escriure'l a les últimes cometes ("") de la fórmula, per exemple, així:
=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.")
