¿Alguna vez te has encontrado en medio de un mar de datos en Excel, buscando la manera más efectiva de sumar y analizar información específica? Si es así, ¡has llegado al lugar indicado! En este artículo, descubriremos cómo combinar la poderosa función BUSCARV con las versátiles SUMA y SUMAR.SI para que puedas transformar cifras dispersas en insights valiosos. A través de ejemplos prácticos, aprenderás a dominar estas herramientas y a optimizar tu flujo de trabajo, convirtiéndote en un experto en Excel. Prepárate para desatar todo el potencial de tus hojas de cálculo y llevar tus habilidades al siguiente nivel. ¡Vamos a sumergirnos en el fascinante mundo de las fórmulas de Excel!
¿Te has preguntado cómo simplificar el proceso de búsqueda y suma de datos en Excel? ¡No te preocupes! En este artículo te mostraremos una herramienta imprescindible: BUSCARV con función SUMA o SUMAR.SI. Descubre cómo puedes utilizar estas fórmulas en diferentes situaciones y agiliza tus tareas en Excel. Acompáñanos a explorar ejemplos prácticos y descubre todo el potencial de estas poderosas funciones. ¡No te lo pierdas!
En este tutorial, encontrará algunos ejemplos de fórmulas avanzadas que demuestran cómo usar las funciones BUSCARV y SUMA o SUMAR.SI de Excel para buscar y sumar valores según uno o varios criterios.
¿Está intentando crear un archivo de resumen en Excel que identifique todas las instancias de un valor en particular y luego sume otros valores asociados con esas instancias? ¿O necesita encontrar todos los valores en una matriz que cumplan con la condición que especifique y luego sumar los valores relacionados de otra hoja de trabajo? ¿O tal vez se enfrenta a un desafío más concreto, como revisar una tabla de facturas de su empresa, identificar todas las facturas de un proveedor en particular y luego sumar todos los valores de las facturas?
Las tareas pueden variar, pero la esencia es la misma: desea buscar y sumar valores con uno o varios criterios en Excel. ¿Qué tipo de valores? Cualquier valor numérico. ¿Qué tipo de criterios? Cualquiera : ) Comenzando desde un número o referencia a una celda que contiene el valor correcto y terminando con operadores lógicos y resultados devueltos por fórmulas de Excel.
Entonces, ¿Microsoft Excel tiene alguna funcionalidad que pueda ayudar con las tareas anteriores? ¡Claro que lo hace! Puede encontrar una solución combinando BUSCARV o BUSCAR de Excel con las funciones SUMA o SUMAR.SI. Los ejemplos de fórmulas que aparecen a continuación le ayudarán a comprender cómo funcionan estas funciones de Excel y cómo aplicarlas a datos reales.
Tenga en cuenta que estos son ejemplos avanzados que implican que está familiarizado con los principios generales y la sintaxis de la función BUSCARV. De lo contrario, la primera parte de nuestro tutorial BUSCARV para principiantes ciertamente merece su atención: sintaxis y usos generales de BUSCARV de Excel.
BUSCARV y SUMA de Excel: encuentre la suma de valores coincidentes
Si trabaja con datos numéricos en Excel, muy a menudo no sólo tendrá que extraer los valores asociados de otra tabla, sino también sumar números en varias columnas o filas. Para hacer esto, puede usar una combinación de las funciones SUMA y BUSCARV como se muestra a continuación.
Datos fuente:
Supongamos que tiene una lista de productos con cifras de ventas de varios meses, una columna por cada mes. Los datos de origen están en la hoja denominada Ventas mensuales:
>Ahora desea hacer una tabla resumen con las ventas totales de cada producto.
La solución es utilizar una matriz en los 3tercero parámetro (col_index_num) de la función BUSCARV de Excel. Aquí hay una fórmula genérica:
SUMA(BUSCARV(valor de búsqueda, rango de búsqueda{2,3,…,n}, FALSO))
Como puede ver, usamos una constante de matriz en el tercer argumento para realizar varias búsquedas dentro de la misma fórmula BUSCARV para obtener la suma de los valores en las columnas 2,3 y 4.
Y ahora, ajustemos esta combinación de funciones BUSCARV y SUMA para nuestros datos para encontrar el total de ventas en las columnas B – M en la tabla anterior:
=SUM(VLOOKUP(B2, ‘Monthly sales’! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))
¡Importante! Dado que está creando una fórmula matricial, asegúrese de presionar Ctrl + Mayús + Intro en lugar de un simple Ingresar pulsación de tecla cuando haya terminado de escribir. Cuando haces esto, Microsoft Excel encierra tu fórmula entre llaves como esta:
{=SUM(VLOOKUP(B2, ‘Monthly sales’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}
Si presionas el Ingresar key como de costumbre, solo se procesará el primer valor de la matriz, lo que producirá resultados incorrectos.
>
Consejo. Quizás tengas curiosidad por saber por qué se muestra la fórmula. [@Product] como el valor de búsqueda en la captura de pantalla anterior. Esto se debe a que convertí mis datos a una tabla (Insertar pestaña > Mesa). Me resulta muy conveniente trabajar con tablas de Excel completamente funcionales y sus referencias estructuradas. Por ejemplo, cuando escribes una fórmula en una celda, Excel la copia automáticamente en toda la columna y de esta manera te ahorra unos preciosos segundos 🙂
Como puede ver, usar las funciones BUSCARV y SUMA en Excel es fácil. Sin embargo, esta no es la solución ideal, especialmente si trabaja con mesas grandes. El punto es que el uso de fórmulas de matriz puede afectar negativamente el rendimiento del libro, ya que cada valor de la matriz realiza una llamada separada a la función BUSCARV. Entonces, cuantos más valores tenga en la matriz y más fórmulas de matriz tenga en su libro de trabajo, más lento funcionará Excel.
Puede evitar este problema utilizando una combinación de las funciones ÍNDICE y COINCIDIR en lugar de SUMA y BUSCARV, y le mostraré algunos ejemplos de fórmulas en el próximo artículo.
Descarga esto Muestra BUSCARV y SUMA
Cómo realizar otros cálculos con la función BUSCARV de Excel
Hace un momento analizamos un ejemplo de cómo se pueden extraer valores de varias columnas en la tabla de búsqueda y calcular la suma de esos valores. De la misma manera, puedes realizar otros cálculos matemáticos con los resultados devueltos por la función BUSCARV. Aquí hay algunos ejemplos de fórmulas:
OperaciónEjemplo de fórmulaDescripciónCalcular promedio{=PROMEDIO(BUSCARV(A2, ‘Tabla de búsqueda’$A$2:$D$10, {2,3,4}, FALSO))}La fórmula busca el valor de la celda A2 en la ‘Tabla de búsqueda’ y calcula el promedio de los valores en las columnas B, C y D en la misma fila.Encuentra el valor máximo{=MAX(BUSCARV(A2, ‘Tabla de búsqueda’$A$2:$D$10, {2,3,4}, FALSO))}La fórmula busca el valor de la celda A2 en la ‘tabla de búsqueda’ y encuentra el valor máximo en las columnas B, C y D de la misma fila.Encuentra el valor mínimo{=MIN(BUSCARV(A2, ‘Tabla de búsqueda’$A$2:$D$10, {2,3,4}, FALSO))}La fórmula busca el valor de la celda A2 en la ‘tabla de búsqueda’ y encuentra el valor mínimo en las columnas B, C y D de la misma fila.Calcular % de la suma{=0.3*SUM(BUSCARV(A2, ‘Tabla de búsqueda’$A$2:$D$10, {2,3,4}, FALSO))}La fórmula busca el valor de la celda A2 en la ‘Tabla de búsqueda’, suma los valores de las columnas B, C y D en la misma fila y luego calcula el 30% de la suma.
Nota. Dado que todas las fórmulas anteriores son fórmulas matriciales, recuerde presionar Ctrl+Mayús+Entrar para ingresarlos correctamente en una celda.
Si agregamos las fórmulas anteriores a la tabla ‘Resumen de ventas’ del ejemplo anteriorel resultado será similar a este:
>Descarga esto Ejemplo de cálculos de BUSCARV
BÚSQUEDA Y SUMA: busque en una matriz y sume valores coincidentes
En caso de que su parámetro de búsqueda sea una matriz en lugar de un valor único, la función BUSCARV no sirve de nada porque no puede buscar en matrices de datos. En este caso, puede utilizar la función BUSCAR de Excel, que es análoga a BUSCARV pero funciona tanto con matrices como con valores individuales.
Consideremos el siguiente ejemplo, para que puedas entender mejor de qué estoy hablando. Supongamos que tiene una tabla que enumera los nombres de los clientes, los productos comprados y la cantidad (mesa principal). También tienes una segunda tabla que contiene los precios de los productos (Tabla de búsqueda). Su tarea es crear una fórmula que encuentre el total de todos los pedidos realizados por un cliente determinado.
>Como recordará, no puede utilizar la función BUSCARV de Excel ya que tiene varias instancias del valor de búsqueda (matriz de datos). En su lugar, utiliza una combinación de funciones SUMA y BUSCAR como esta:
=SUM(LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))
Dado que se trata de una fórmula matricial, recuerde presionar Ctrl + Mayús + Intro para completarlo.
>Y ahora, analicemos los ingredientes de la fórmula para que comprenda cómo funciona cada una de las funciones y pueda modificarla según sus propios datos.
Dejaremos de lado la función SUMA por un tiempo, porque su propósito es obvio, y nos centraremos en los 3 componentes que se multiplican:
Esta función de BÚSQUEDA busca los productos enumerados en la columna C de la tabla principal y devuelve el precio correspondiente de la columna B de la tabla de búsqueda.
Este componente devuelve la cantidad de cada producto comprado por cada cliente, que figura en la columna D de la tabla principal. Multiplicado por el precio, que devuelve la función BUSCAR anterior, le da el costo de cada producto comprado.
Esta fórmula compara los nombres de los clientes en la columna B con el nombre en la celda G1. Si se encuentra una coincidencia, devuelve «1», en caso contrario «0». Se utiliza simplemente para «cortar» los nombres de los clientes que no sean el nombre de la celda G1, ya que todos sabemos que cualquier número multiplicado por cero es cero.
Debido a que nuestra fórmula es una fórmula matricial, itera el proceso descrito anteriormente para cada valor en la matriz de búsqueda. Y finalmente, la función SUMA suma los productos de todas las multiplicaciones. Nada difícil, ¿verdad?
Nota. Para que la fórmula BUSCAR funcione correctamente, debe ordenar la columna de búsqueda en su tabla de búsqueda en orden ascendente (de la A a la Z). Si la clasificación de sus datos no es aceptable, consulte una increíble fórmula SUMA / TRANSPOSICIÓN sugerida por Leo.
Descarga esto Muestra de BÚSQUEDA y SUMA
BUSCARV y SUMAR.SI: buscar y sumar valores con criterios
La función SUMAR.SI de Excel es similar a SUMA que acabamos de comentar en el sentido de que también suma valores. La diferencia es que la función SUMAR.SI suma solo aquellos valores que cumplen con los criterios que usted especifica. Por ejemplo, la fórmula SUMAR.SI más simple =SUMIF(A2:A10,»>10″) suma los valores de las celdas A2 a A10 que son mayores que 10.
Esto es muy fácil, ¿verdad? Y ahora consideremos un escenario un poco más complejo. Suponga que tiene una tabla que enumera los nombres y números de identificación de los vendedores (Tabla de búsqueda). Tiene otra tabla que contiene los mismos ID y cifras de ventas asociadas (tabla_principal). Su tarea es encontrar el total de ventas realizadas por una persona determinada por su identificación. Además, hay 2 factores que complican la situación:
- La tabla de correo contiene varias entradas para el mismo ID en orden aleatorio.
- No puede agregar la columna «Nombres de vendedores» a la tabla principal.
>Y ahora, hagamos una fórmula que, en primer lugar, encuentre todas las ventas realizadas por una persona determinada y, en segundo lugar, sume los valores encontrados.
Antes de comenzar con la fórmula, déjame recordarte la sintaxis de la función SUMAR.SI:
SUMAR.SI(rango, criterios, [sum_range])
- range – este parámetro se explica por sí mismo, simplemente es un rango de celdas que desea evaluar según los criterios especificados.
- criteria – la condición que le dice a la fórmula qué valores sumar. Se puede proporcionar en forma de número, referencia de celda, expresión u otra función de Excel.
- sum_range – este parámetro es opcional, pero muy importante para nosotros. Define el rango donde se agregarán los valores de las celdas correspondientes. Si se omite, Excel suma los valores de las celdas que se especifican en el argumento de rango (1calle parámetro).
Teniendo en cuenta la información anterior, definamos los 3 parámetros para nuestra función SUMAR.SI. Como recordará, queremos sumar todas las ventas realizadas por una persona determinada cuyo nombre se ingresa en la celda F2 de la tabla principal (consulte la imagen de arriba).
Por supuesto, puede ingresar el nombre en los criterios de búsqueda de su función BUSCARV, pero usar una referencia de celda absoluta es un mejor enfoque porque esto crea una fórmula universal que funciona para cualquier entrada de nombre en una celda determinada.
Ahora, todo lo que necesitas es ensamblar las partes de la fórmula y tu fórmula SUMAR.SI + BUSCARV estará lista:
=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])
>
Descarga esto Muestra BUSCARV y SUMAR.SI
Manera sin fórmulas de realizar búsqueda virtual en Excel
Finalmente, permítame presentarle la herramienta que puede buscar, unir y fusionar sus tablas sin funciones ni fórmulas. La herramienta Combinar tablas incluida con nuestro Ultimate Suite para Excel fue diseñada y desarrollada como una alternativa fácil de usar y que ahorra tiempo a las funciones BUSCARV y BUSCAR de Excel, y puede ser muy útil tanto para principiantes como para usuarios avanzados.
En lugar de encontrar fórmulas, simplemente especifica sus tablas principal y de búsqueda, define una columna o columnas comunes y le dice al asistente qué datos desea recuperar.
>Luego, le permite al asistente unos segundos buscar, comparar y entregarle los resultados. Si cree que este complemento puede resultar útil en su trabajo, puede descargar una versión de prueba utilizando el siguiente enlace.
descargas disponibles
BUSCARV con SUMA y SUMAR.SI: ejemplos de fórmulas (archivo .xlsx)
Ultimate Suite – versión de prueba (archivo .exe)