Contenido
- La función BUSCARV distingue entre mayúsculas y minúsculas
- Función LOOKUP para búsqueda sensible a mayúsculas y minúsculas
- SUMPRODUCT: encuentra valores de texto, distingue entre mayúsculas y minúsculas, pero solo devuelve números
- ÍNDICE + COINCIDIR: búsqueda sensible a mayúsculas y minúsculas para cualquier tipo de datos
Este pequeño tutorial explica cómo hacer una función. VPR (BUSCARV) distingue entre mayúsculas y minúsculas, muestra varias otras fórmulas en las que Excel puede buscar distinguiendo entre mayúsculas y minúsculas y señala las fortalezas y debilidades de cada función.
Supongo que todos los usuarios de Excel saben qué función realiza la búsqueda vertical. Así es, es una función. VPR. Sin embargo, pocas personas saben que VPR no distingue entre mayúsculas y minúsculas, es decir, las mayúsculas y minúsculas son idénticas para él.
Aquí hay un ejemplo rápido que demuestra la incapacidad VPR reconocer el registro. Supongamos que en una celda A1 contiene el valor “factura” y la celda A2 – “Factura”, fórmula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… detendrá su búsqueda en “factura” ya que ese valor viene primero en la lista, y extraerá el valor de la celda B1.
Más adelante en este artículo, le mostraré cómo hacer VPR distingue mayúsculas y minúsculas. Además, aprenderemos algunas funciones más que pueden realizar búsquedas que distinguen entre mayúsculas y minúsculas en Excel.
Comenzaremos con el más simple: VER (BUSCAR) y SUMPRODUCT (SUMPRODUCT), que, lamentablemente, tienen varias limitaciones importantes. A continuación, veremos más de cerca la fórmula un poco más compleja. ÍNDICE+COINCIDIR (ÍNDICE+COINCIDIR), que funciona perfectamente en cualquier situación y con cualquier conjunto de datos.
La función BUSCARV distingue entre mayúsculas y minúsculas
Como ya sabes, la función habitual VPR es insensible a mayúsculas y minúsculas. Sin embargo, hay una manera de hacerlo sensible a mayúsculas y minúsculas. Para hacer esto, debe agregar una columna auxiliar a la tabla, como se muestra en el siguiente ejemplo.
Supongamos que en una columna B hay identificadores de producto (Item) y se quiere extraer el precio del producto y el comentario correspondiente de las columnas C и D. El problema es que los identificadores contienen caracteres tanto en minúsculas como en mayúsculas. Por ejemplo, valores de celda B4 (001Tvci3u) y B5 (001Tvci3U) difieren solo en el caso del último carácter, u и U respectivamente.
Como puedes imaginar, la fórmula de búsqueda habitual
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
regresará US$ 90, ya que el valor 001tvci3u está en el rango de búsqueda antes de 001Tvci3U. Pero eso no es lo que necesitamos, ¿verdad?
Para buscar con una función VPR en Excel que distingue entre mayúsculas y minúsculas, tendrá que agregar una columna auxiliar y llenar sus celdas con la siguiente fórmula (donde B es la columna de búsqueda):
=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 el valor deseado en caracteres separados, reemplaza cada carácter con su código (por ejemplo, en lugar de A a los 65, en cambio a código 97) y luego combina estos códigos en una cadena única de números.
Después de eso, usamos una función simple. VPR para búsqueda sensible a mayúsculas y minúsculas:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Correcto funcionamiento de la función. VPR la distinción entre mayúsculas y minúsculas depende de dos factores:
- La columna auxiliar debe ser la columna más a la izquierda en el rango visible.
- El valor que está buscando debe contener un código de carácter en lugar del valor real.
Cómo usar la función CÓDIGO correctamente
La fórmula insertada en las celdas de la columna auxiliar asume que todos sus valores de búsqueda tienen la misma cantidad de caracteres. De lo contrario, debe conocer los números más pequeños y más grandes y agregar tantas características IFERROR (IFERROR) cuántos caracteres es la diferencia entre el valor buscado más corto y más largo.
Por ejemplo, si el valor de búsqueda más corto es de 3 caracteres y el más largo es 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));"")
para la función PSTR (MID) Usted proporciona los siguientes argumentos:
- 1er argumento - texto (texto) es el texto o referencia de celda que contiene los caracteres a extraer (en nuestro caso es B2)
- 2er argumento - start_num (start_position) es la posición del primero de esos caracteres que se extraerá. tu entras 1 en la primera función PSTR, 2 – en la segunda función PSTR etc.
- 3er argumento - num_chars (number_of_characters): especifica la cantidad de caracteres que se extraerán del texto. Como solo necesitamos 1 carácter todo el tiempo, en todas las funciones escribimos 1.
LIMITACIONES: Función VPR no es la mejor solución para búsquedas que distinguen entre mayúsculas y minúsculas en Excel. Primero, se requiere la adición de una columna auxiliar. En segundo lugar, la fórmula hace un buen trabajo solo si los datos son homogéneos o si se conoce el número exacto de caracteres en los valores buscados. Si no es tu caso, es mejor que utilices alguna de las soluciones que te mostramos a continuación.
Función LOOKUP para búsqueda sensible a mayúsculas y minúsculas
Función VER (BUSCAR) relacionado VPR, sin embargo, su sintaxis permite realizar búsquedas que distinguen entre mayúsculas y minúsculas sin agregar una columna auxiliar. Para hacer esto, use VER combinado con la función EXACT (EXACTO).
Si tomamos los datos del ejemplo anterior (sin una columna auxiliar), la siguiente fórmula hará frente a la tarea:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Búsquedas de fórmulas en rango A2: A7 coincidencia exacta con el valor de la celda F2 distingue entre mayúsculas y minúsculas y devuelve el valor de la columna B de la misma fila.
Me gusta VPRfunción VER funciona igualmente con valores de texto y numéricos, como puede ver en la siguiente captura de pantalla:
¡Importante! Para que la función VER funcionó correctamente, los valores en la columna de búsqueda deben ordenarse en orden ascendente, es decir, de menor a mayor.
Permítanme explicar brevemente cómo funciona la función. EXACT en la fórmula que se muestra arriba, ya que este es el punto clave.
Función EXACT compara los dos valores de texto en los argumentos 1 y 2 y devuelve VERDADERO si son exactamente iguales, o FALSO si no lo son. Para nosotros es importante que la función EXACT distingue mayúsculas y minúsculas.
Veamos cómo funciona nuestra 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 el valor de la celda F2 con todos los elementos en una columna A (A2:A7). Devuelve VERDADERO si se encuentra una coincidencia exacta, de lo contrario FALSO.
- Ya que das el primer argumento de función VER value TRUE, extrae el valor correspondiente de la columna especificada (en nuestro caso, la columna B) solo si se encuentra una coincidencia exacta, distingue entre mayúsculas y minúsculas.
Espero que esta explicación haya sido clara y ahora entiendas la idea principal. Si es así, entonces no tendrá ninguna dificultad con otras funciones que analizaremos más a fondo, porque. todos trabajan en el mismo principio.
LIMITACIONES: Los datos en la columna de búsqueda deben ordenarse en orden ascendente.
SUMPRODUCT: encuentra valores de texto, distingue entre mayúsculas y minúsculas, pero solo devuelve números
Como ya entendiste por el título, SUMPRODUCT (SUMAPRODUCTO) es otra función de Excel que lo ayudará a realizar una búsqueda que distingue entre mayúsculas y minúsculas, pero solo devolverá valores numéricos. Si esta opción no le conviene, puede proceder inmediatamente al paquete ÍNDICE+COINCIDIR, que da una solución para cualquier caso y para cualquier tipo de datos.
Primero, déjame explicarte brevemente la sintaxis de esta función, esto te ayudará a comprender mejor la fórmula que distingue entre mayúsculas y minúsculas que sigue.
Función SUMPRODUCT multiplica los elementos de las matrices dadas y devuelve la suma de los resultados. La sintaxis se ve así:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Como necesitamos una búsqueda que distinga entre mayúsculas y minúsculas, usamos la función EXACT (EXACTO) del ejemplo anterior como uno de los multiplicadores:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Como recuerdas EXACT compara el valor de la celda F2 con todos los elementos en una columna A. Devuelve VERDADERO si se encuentra una coincidencia exacta, de lo contrario FALSO. En operaciones matemáticas, Excel toma VERDADERO como 1y FALSO para 0Promover SUMPRODUCT multiplica estos números y suma los resultados.
Los ceros no se cuentan porque cuando se multiplican siempre dan 0. Echemos un vistazo más de cerca a lo que sucede cuando una coincidencia exacta en una columna A encontrado y devuelto 1… Función SUMPRODUCT multiplica el número en la columna B on 1 y devuelve el resultado: ¡exactamente el mismo número! Esto se debe a que los resultados de los otros productos son cero y no afectan la suma resultante.
Desafortunadamente la función SUMPRODUCT no puede trabajar con valores de texto y fechas ya que no se pueden multiplicar. En este caso, recibirá un mensaje de error. #¡VALOR! (¡#VALOR!) como en una celda F4 en la imagen de abajo:
LIMITACIONES: Devuelve solo valores numéricos.
ÍNDICE + COINCIDIR: búsqueda sensible a mayúsculas y minúsculas para cualquier tipo de datos
Finalmente, estamos cerca de una fórmula de búsqueda ilimitada y que distingue entre mayúsculas y minúsculas que funciona con cualquier conjunto de datos.
Este ejemplo viene en último lugar, no porque lo mejor se deje para el postre, sino porque el conocimiento obtenido de los ejemplos anteriores lo ayudará a comprender mejor y más rápido la fórmula que distingue entre mayúsculas y minúsculas. ÍNDICE+COINCIDIR (ÍNDICE+COINCIDIR).
Como probablemente hayas adivinado, la combinación de funciones MÁS EXPUESTO и ÍNDICE utilizado en Excel como una alternativa más flexible y poderosa para VPR. El artículo Usar ÍNDICE y COINCIDIR en lugar de BUSCARV explicará perfectamente cómo funcionan juntas estas funciones.
Voy a recapitular los puntos clave:
- Función MÁS EXPUESTO (MATCH) busca un valor en un rango dado y devuelve su posición relativa, es decir, el número de fila y/o columna;
- A continuación, la función ÍNDICE (ÍNDICE) devuelve un valor de una columna y/o fila especificada.
A la fórmula ÍNDICE+COINCIDIR podría buscar con distinción entre mayúsculas y minúsculas, solo necesita agregarle una función. No es difícil adivinar qué es de nuevo. 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))
En esta fórmula EXACT funciona de la misma manera que en conjunto con la función VER, y da el mismo resultado:
Tenga en cuenta que la fórmula ÍNDICE+COINCIDIR encerrada entre llaves es una fórmula matricial y debe completarla presionando Ctrl + Shift + Enter.
¿Por qué INDEX+MATCH es la mejor solución para la búsqueda que distingue entre mayúsculas y minúsculas?
Las principales ventajas del paquete. ÍNDICE и MÁS EXPUESTO:
- No requiere agregar una columna auxiliar, a diferencia de VPR.
- No requiere que la columna de búsqueda esté ordenada, a diferencia de VER.
- Funciona con todo tipo de datos: números, texto y fechas.
Esta fórmula parece perfecta, ¿no? En realidad, no lo es. Y es por eso.
Suponga que la celda de la columna de valor de retorno asociada con el valor de búsqueda está vacía. ¿Qué resultado arrojará la fórmula? ¿No? Veamos qué devuelve realmente la fórmula:
¡Uy, la fórmula devuelve cero! Esto puede no ser un gran problema si está trabajando con valores de texto puro. Sin embargo, si la tabla contiene números, incluidos ceros "reales", esto se convierte en un problema.
De hecho, todas las demás fórmulas de búsqueda (BUSCARV, BUSCAR y SUMPRODUCTO) que analizamos anteriormente se comportan de la misma manera. Pero quieres la fórmula perfecta, ¿verdad?
Para hacer que una fórmula distinga entre mayúsculas y minúsculas ÍNDICE+COINCIDIR perfecto, ponlo en una funcion IF (IF) que probará una celda con un valor de retorno y devolverá un resultado vacío si está vacío:
=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 esta fórmula:
- B es una columna con valores de retorno
- 1+ es un número que convierte la posición relativa de la celda devuelta por la función MÁS EXPUESTO, a la dirección real del celular. Por ejemplo, en nuestra función MÁS EXPUESTO matriz de búsqueda dada A2: A7, es decir, la posición relativa de la celda A2 seguirá 1, porque es el primero de la matriz. Pero la posición real de la celda A2 en la columna esta 2, entonces agregamos 1para compensar la diferencia y tener la función INDIRECT (INDIRECTO) recuperó el valor de la celda deseada.
Las siguientes imágenes muestran la fórmula corregida que distingue entre mayúsculas y minúsculas ÍNDICE+COINCIDIR En acción. Devuelve un resultado vacío si la celda devuelta está vacía.
Reescribí la fórmula en columnas. segundo:repara ajustar la barra de fórmulas en la captura de pantalla.
Devuelve la fórmula 0si la celda devuelta contiene cero.
Si quieres el enlace ÍNDICE и MÁS EXPUESTO muestra algún mensaje cuando el valor de retorno está vacío, puede escribirlo en las últimas comillas ("") de la fórmula, por ejemplo, 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.")
