Contidos
- A función VLOOKUP distingue entre maiúsculas e minúsculas
- Función LOOKUP para a busca sensible a maiúsculas e minúsculas
- SUMPRODUCT: atopa valores de texto, distingue entre maiúsculas e minúsculas, pero só devolve números
- INDEX + MATCH: busca sensible a maiúsculas e minúsculas para calquera tipo de datos
Este pequeno tutorial explica como facer unha función VPR (BUSQUEDA V) distingue entre maiúsculas e minúsculas, mostra outras fórmulas que Excel pode buscar de forma diferenciada entre maiúsculas e minúsculas e sinala os puntos fortes e débiles de cada función.
Supoño que todos os usuarios de Excel saben que función realiza a busca vertical. É certo, é unha función VPR. Porén, pouca xente o sabe VPR non distingue entre maiúsculas e minúsculas, é dicir, os caracteres minúsculas e maiúsculas son idénticos.
Aquí tes un exemplo rápido que demostra a incapacidade VPR recoñecer o rexistro. Supoña nunha cela A1 contén o valor "factura" e a cela A2 – “Factura”, fórmula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... parará a súa busca en "factura" xa que ese valor aparece primeiro na lista e extraerá o valor da cela B1.
Máis tarde neste artigo mostrarei como facelo VPR sensible a maiúsculas e minúsculas. Ademais, aprenderemos algunhas funcións máis que poden realizar buscas sensibles entre maiúsculas e minúsculas en Excel.
Comezaremos polo máis sinxelo - VER (BUSCAR) e SUMPRODUCT (SUMPRODUCT), que, por desgraza, teñen varias limitacións significativas. A continuación, analizaremos a fórmula un pouco máis complexa ÍNDICE+ COINCIDENCIA (INDEX+MATCH), que funciona perfectamente en calquera situación e con calquera conxunto de datos.
A función VLOOKUP distingue entre maiúsculas e minúsculas
Como xa sabedes, a función habitual VPR non distingue entre maiúsculas e minúsculas. Non obstante, hai unha forma de distinguir entre maiúsculas e minúsculas. Para iso, cómpre engadir unha columna auxiliar á táboa, como se mostra no seguinte exemplo.
Supoña nunha columna B hai identificadores de produto (Artículo) e queres extraer o prezo do produto e o comentario correspondente das columnas C и D. O problema é que os identificadores conteñen caracteres tanto en minúsculas como en maiúsculas. Por exemplo, os valores das celas B4 (001Tvci3u) e B5 (001Tvci3U) só difiren no caso do último carácter, u и U respectivamente.
Como podes imaxinar, a fórmula de busca habitual
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
volverá $ 90, xa que o valor 001Tvci3u está no intervalo de busca antes de 001Tvci3U. Pero iso non é o que necesitamos, non?
Para buscar cunha función VPR en Excel, terá que engadir unha columna auxiliar e cubrir as súas celas coa seguinte fórmula (onde B é a columna de busca):
=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));"")
Esta fórmula divide o valor desexado en caracteres separados, substitúe cada carácter polo seu código (por exemplo, en lugar de A en 65, en cambio a código 97) e despois combina estes códigos nunha única cadea de números.
Despois diso, usamos unha función sinxela VPR para a busca sensible a maiúsculas e minúsculas:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Funcionamento correcto da función VPR A distinción entre maiúsculas e minúsculas depende de dous factores:
- A columna auxiliar debe ser a columna máis á esquerda do intervalo visible.
- O valor que busca debe conter un código de carácter en lugar do valor real.
Como utilizar correctamente a función CODE
A fórmula inserida nas celas da columna auxiliar supón que todos os seus valores de busca teñen o mesmo número de caracteres. Se non, entón cómpre coñecer os números máis pequenos e maiores e engadir tantas características SE ERRO (IFERROR) cantos caracteres é a diferenza entre o valor buscado máis curto e máis longo.
Por exemplo, se o valor de busca máis curto é de 3 caracteres e o máis longo é de 5 caracteres, use esta 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));"")
Por función PSTR (MID) Proporcionas os seguintes argumentos:
- 1º argumento - texto (texto) é a referencia de texto ou cela que contén os caracteres que se van extraer (no noso caso é B2)
- 2º argumento - núm_inicio (posición_inicio) é a posición do primeiro deses caracteres a extraer. entras 1 na primeira función PSTR, 2 – na segunda función PSTR etc.
- 3º argumento - num_chars (número_de_caracteres) – Especifica o número de caracteres para extraer do texto. Como só necesitamos 1 carácter todo o tempo, en todas as funcións escribimos 1.
LIMITACIÓNS: función VPR non é a mellor solución para as buscas que distinguen maiúsculas e minúsculas en Excel. En primeiro lugar, é necesaria a adición dunha columna auxiliar. En segundo lugar, a fórmula fai un bo traballo só se os datos son homoxéneos ou se coñece o número exacto de caracteres dos valores buscados. Se non é o teu caso, é mellor utilizar unha das solucións que mostramos a continuación.
Función LOOKUP para a busca sensible a maiúsculas e minúsculas
función VER (BUSCAR) relacionado VPR, non obstante, a súa sintaxe permite realizar buscas que distinguen entre maiúsculas e minúsculas sen engadir unha columna auxiliar. Para iso, use VER combinado coa función EXACT (EXACTO).
Se tomamos os datos do exemplo anterior (sen unha columna auxiliar), entón a seguinte fórmula fará fronte á tarefa:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Buscas de fórmulas no rango A2: A7 coincidencia exacta co valor da cela F2 distingue entre maiúsculas e minúsculas e devolve o valor da columna B da mesma fila.
como VPRfunción VER funciona igual con valores de texto e numéricos, como podes ver na seguinte captura de pantalla:
Importante! Para a función VER funcionou correctamente, os valores da columna de busca deben ordenarse en orde ascendente, é dicir, de menor a maior.
Permítanme explicar brevemente como funciona a función EXACT na fórmula mostrada anteriormente, xa que este é o punto clave.
función EXACT compara os dous valores de texto nos argumentos 1 e 2 e devolve VERDADEIRO se son exactamente iguais, ou FALSO se non o son. É importante para nós que a función EXACT sensible a maiúsculas e minúsculas.
Vexamos como funciona a nosa fórmula VER + EXACTO:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- función EXACT compara o valor da cela F2 con todos os elementos nunha columna A (A2:A7). Devolve TRUE se se atopa unha coincidencia exacta, en caso contrario, FALSE.
- Xa que dá o primeiro argumento da función VER valor TRUE, extrae o valor correspondente da columna especificada (no noso caso, columna B) só se se atopa unha coincidencia exacta, distingue entre maiúsculas e minúsculas.
Espero que esta explicación fose clara e agora entendades a idea principal. Se é así, entón non terás ningunha dificultade con outras funcións que analizaremos máis adiante, porque. todos funcionan co mesmo principio.
LIMITACIÓNS: Os datos da columna de busca deben ordenarse en orde ascendente.
SUMPRODUCT: atopa valores de texto, distingue entre maiúsculas e minúsculas, pero só devolve números
Como xa entendes polo título, SUMPRODUCT (SUMAPRODUTO) é outra función de Excel que che axudará a facer unha busca que distingue entre maiúsculas e minúsculas, pero só devolverá valores numéricos. Se esta opción non che convén, podes pasar inmediatamente ao paquete ÍNDICE+ COINCIDENCIA, que dá unha solución para calquera caso e para calquera tipo de datos.
En primeiro lugar, déixeme explicar brevemente a sintaxe desta función, isto axudarache a comprender mellor a fórmula que distingue entre maiúsculas e minúsculas que segue.
función SUMPRODUCT multiplica os elementos das matrices dadas e devolve a suma dos resultados. A sintaxe ten este aspecto:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Dado que necesitamos unha busca que distinga entre maiúsculas e minúsculas, usamos a función EXACT (EXACTO) do exemplo anterior como un dos multiplicadores:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Como recordas, EXACT compara o valor da cela F2 con todos os elementos nunha columna A. Devolve TRUE se se atopa unha coincidencia exacta, en caso contrario, FALSE. Nas operacións matemáticas, Excel toma TRUE como 1, e FALSE para 0máis SUMPRODUCT multiplica estes números e suma os resultados.
Os ceros non se contan porque ao multiplicarse sempre dan 0. Vexamos máis de cerca o que ocorre cando unha coincidencia exacta nunha columna A atopado e devolto 1… Función SUMPRODUCT multiplica o número da columna B on 1 e devolve o resultado, exactamente o mesmo número! Isto débese a que os resultados dos outros produtos son cero e non afectan á suma resultante.
Desafortunadamente a función SUMPRODUCT non pode traballar con valores de texto e datas xa que non se poden multiplicar. Neste caso, recibirá unha mensaxe de erro #VALUE! (#VALOR!) como nunha cela F4 na imaxe de abaixo:
LIMITACIÓNS: Devolve só valores numéricos.
INDEX + MATCH: busca sensible a maiúsculas e minúsculas para calquera tipo de datos
Finalmente, estamos preto dunha fórmula de busca ilimitada e que distingue entre maiúsculas e minúsculas que funciona con calquera conxunto de datos.
Este exemplo é o último, non porque quede o mellor para a sobremesa, senón porque os coñecementos adquiridos dos exemplos anteriores axudarache a comprender mellor e máis rápido a fórmula que distingue entre maiúsculas e minúsculas. ÍNDICE+ COINCIDENCIA (ÍNDICE+ COINCIDENCIA).
Como probablemente adiviñaches, a combinación de funcións MÁIS EXPOÑA и Index usado en Excel como unha alternativa máis flexible e poderosa para VPR. O artigo Usando INDEX e MATCH en lugar de VLOOKUP explicará perfectamente como funcionan estas funcións xuntas.
Só vou resumir os puntos clave:
- función MÁIS EXPOÑA (MATCH) busca un valor nun intervalo determinado e devolve a súa posición relativa, é dicir, o número de fila e/ou columna;
- A continuación, a función Index (INDEX) devolve un valor dunha columna e/ou fila especificadas.
Para formular ÍNDICE+ COINCIDENCIA podería buscar distinguindo entre maiúsculas e minúsculas, só precisa engadirlle unha función. Non é difícil adiviñar o que é de novo EXACT (EXACTO):
=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))
Nesta fórmula EXACT funciona do mesmo xeito que en conxunto coa función VER, e dá o mesmo resultado:
Teña en conta que a fórmula ÍNDICE+ COINCIDENCIA entre chaves hai unha fórmula matricial e debes completala premendo Ctrl + Maiús + Entrar.
Por que INDEX+MATCH é a mellor solución para a busca que distingue entre maiúsculas e minúsculas?
As principais vantaxes do paquete Index и MÁIS EXPOÑA:
- Non require engadir unha columna auxiliar, a diferenza VPR.
- Non require que a columna de busca estea ordenada, a diferenza VER.
- Funciona con todo tipo de datos: números, texto e datas.
Esta fórmula parece perfecta, non? En realidade, non o é. E por iso.
Supoña que a cela da columna do valor de retorno asociada ao valor de busca está baleira. Que resultado devolverá a fórmula? Non? Vexamos o que realmente devolve a fórmula:
Vaia, a fórmula devolve cero. Isto pode non ser un gran problema se está a traballar con valores de texto puro. Non obstante, se a táboa contén números, incluídos ceros "reais", isto convértese nun problema.
De feito, todas as outras fórmulas de busca (LOOKUP, LOOKUP e SUMPRODUCT) que comentamos anteriormente se comportan do mesmo xeito. Pero queres a fórmula perfecta, non?
Para facer que unha fórmula distinga entre maiúsculas e minúsculas ÍNDICE+ COINCIDENCIA perfecto, ponlo nunha función IF (IF) que probará unha cela cun valor de retorno e devolverá un resultado baleiro se está baleiro:
=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));"")
Nesta fórmula:
- B é unha columna con valores de retorno
- 1+ é un número que xira a posición relativa da cela que devolve a función MÁIS EXPOÑA, ao enderezo real da cela. Por exemplo, na nosa función MÁIS EXPOÑA matriz de busca dada A2: A7, é dicir, a posición relativa da cela A2 vontade 1, porque é o primeiro da matriz. Pero a posición real da célula A2 na columna está 2, así que engadimos 1para compensar a diferenza e para ter a función INDIRECTO (INDIRECTO) recuperou o valor da cela desexada.
As imaxes seguintes mostran a fórmula corrixida que distingue entre maiúsculas e minúsculas ÍNDICE+ COINCIDENCIA En acción. Devolve un resultado baleiro se a cela devolta está baleira.
Reescribín a fórmula en columnas B:Dpara axustar a barra de fórmulas na captura de pantalla.
Retorna fórmula 0se a cela devolta contén cero.
Se queres o enlace Index и MÁIS EXPOÑA mostrou algunha mensaxe cando o valor de retorno está baleiro, pode escribilo nas últimas comiñas ("") da fórmula, por exemplo, así:
=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.")
