En este artículo, aprenderemos cómo buscar coincidencias exactas usando la función SUMPRODUCT en Excel.
Guión:
En palabras simples, mientras trabajamos con tablas de datos, a veces necesitamos buscar valores con letras que distinguen entre mayúsculas y minúsculas en Excel. Las funciones de búsqueda como las funciones BUSCARV o COINCIDIR no encuentran la coincidencia exacta ya que no son funciones sensibles a mayúsculas y minúsculas. Suponga que trabaja con datos en los que se diferencian 2 nombres en función de la distinción entre mayúsculas y minúsculas, es decir, Jerry y JERRY son dos nombres diferentes. Puede realizar tareas como operaciones en varios rangos utilizando la fórmula que se explica a continuación.
¿Como resolver el problema?
Para este problema, se nos pedirá que utilicemos la función SUMPRODUCT y la función EXACT. Ahora haremos una fórmula a partir de la función. Aquí se nos da una tabla y necesitamos encontrar los valores correspondientes a la coincidencia exacta en la tabla en Excel. La función SUMPRODUCT devuelve la SUMA de los valores VERDADEROS correspondientes (como 1) e ignora los valores correspondientes a los valores FALSOS (como 0) en la matriz devuelta
Fórmula genérica:
| = SUMPRODUCTO (- (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: valor a buscar.
lookup_array: matriz de búsqueda para el valor de búsqueda.
return_array: matriz, valor devuelto correspondiente a la matriz de búsqueda.
-: El carácter de negación (-) cambia los valores, VERDADERO o 1 a FALSO o 0 y FALSO o 0 a VERDADERO o 1.
Ejemplo:
Todos estos pueden resultar confusos de entender. Entonces, probemos esta fórmula ejecutándola en el ejemplo que se muestra a continuación. Aquí tenemos los datos que tienen la cantidad y el precio de los productos recibidos en las fechas. Si algún producto se compra dos veces, tiene 2 nombres. Aquí necesitamos encontrar el recuento de elementos para todos los elementos esenciales. Entonces, para eso, hemos asignado 2 listas como la lista recibida y los elementos esenciales requeridos en una columna para la fórmula. Ahora usaremos la fórmula siguiente para obtener la cantidad de "leche" de la tabla.
Usa la fórmula:
| = SUMPRODUCTO (- (EXACTO (F5, B3: B11)), (C3: C11)) |
F6: buscar valor en la celda F6
B3: B11: la matriz de búsqueda es Elementos
C3: C11: la matriz de retorno es Cantidad
-: El carácter de negación (-) cambia los valores, VERDADERO o 1 a FALSO o 0 y FALSO o 0 a VERDADERO o 1.
Aquí el rango se da como referencia de celda. Presione Enter para obtener la Cantidad.
Como puede ver, 58 es la cantidad correspondiente al valor "leche" en la celda B5, no a la "Leche" en la celda B9. Deberá buscar el valor "Leche" si necesita la cantidad "54" en la celda C9.
Puede buscar el precio correspondiente al valor "leche" simplemente usando la fórmula que se muestra a continuación.
Usa la fórmula:
| = SUMPRODUCTO (- (EXACTO (F5, B3: B11)), (D3: D11)) |
F6: buscar valor en la celda F6
B3: B11: la matriz de búsqueda es Elementos
D3: D11: la matriz de retorno es Precio
Aquí tenemos el 58 es el Precio correspondiente al valor "leche" en la celda B5, no al "Leche" en la celda B9. Deberá buscar el valor "Leche" si necesita el Precio "15.58" en la celda D9.
Valores únicos en la matriz de búsqueda
De manera similar, puede encontrar los valores únicos usando la fórmula. Aquí el valor de búsqueda "HUEVOS" utilizado como ejemplo.
En la imagen que se muestra arriba, obtuvimos los valores ajustando la misma fórmula. Pero el problema ocurre si tiene un valor único y no está buscando el valor de búsqueda adecuado. Como aquí usando el valor "Pan" en la fórmula para buscar en la tabla.
La fórmula devuelve 0 como cantidad y precio, pero esto no significa que la cantidad y el precio del pan sean 0. Es solo que la fórmula devuelve 0 como valor cuando no se encuentra el valor que está buscando. Por lo tanto, use esta fórmula solo para buscar la coincidencia exacta.
También puede realizar búsquedas de coincidencias exactas utilizando la función INDICE y COINCIDIR en Excel. Obtenga más información sobre cómo realizar una búsqueda sensible a mayúsculas y minúsculas mediante la función INDICE Y COINCIDIR en Excel. También puede buscar las coincidencias parciales utilizando los comodines en Excel.
Aquí hay algunas notas de observación que se muestran a continuación.
Notas:
- La fórmula solo funciona con solo buscar la coincidencia exacta.
- La función SUMPRODUCTO considera valores no numéricos como ceros.
- La función SUMPRODUCTO considera el valor lógico VERDADERO como 1 y Falso como 0.
- La matriz de argumentos debe tener el mismo tamaño; de lo contrario, la función devuelve un error.
- La función SUMPRODUCT devuelve el valor correspondiente a los valores VERDADEROS en la matriz devuelta después de tomar productos individuales en la matriz correspondiente.
- Los operadores como igual a ( = ), menor que igual a ( <= ), mas grande que ( > ) o no igual a () se puede realizar dentro de una fórmula aplicada, solo con números.
Espero que este artículo sobre cómo buscar coincidencias exactas usando la función SUMPRODUCT en Excel sea explicativo. Encuentre más artículos sobre fórmulas de conteo aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico
Cómo utilizar la función SUMPRODUCT en Excel: Devuelve la SUMA después de la multiplicación de valores en varias matrices en Excel.
Utilice ÍNDICE y COINCIDIR para buscar valor : La fórmula INDEX-MATCH se utiliza para buscar de forma dinámica y precisa un valor en una tabla determinada. Esta es una alternativa a la función BUSCARV y supera las deficiencias de la función BUSCARV.
Suma por grupos OFFSET en filas y columnas : La función OFFSET se puede utilizar para sumar grupos de celdas dinámicamente. Estos grupos pueden estar en cualquier parte de la hoja.
Cómo recuperar cada valor enésimo de un rango en Excel: Usando la función OFFSET de Excel podemos recuperar valores de filas o columnas alternas. Esta fórmula toma la ayuda de la función FILA para alternar entre filas y la función COLUMNA para alternar en columnas.
Cómo usar la función OFFSET en Excel : La función OFFSET es una poderosa función de Excel que muchos usuarios subestiman. Pero los expertos conocen el poder de la función OFFSET y cómo podemos usar esta función para hacer algunas tareas mágicas en la fórmula de Excel. Estos son los conceptos básicos de la función OFFSET.
Cómo usar comodines en Excel : Cuente las celdas que coinciden con las frases usando los comodines en Excel
articulos populares
50 atajos de Excel para aumentar su productividad : Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo usar tLa función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas en Excel.
Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos en Excel. La función CONTAR.SI es esencial para preparar su tablero.
Cómo usar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores con condiciones específicas.