Hasta ahora hemos aprendido a sumar toda la columna coincidente de una tabla en Excel. Pero, ¿cómo sumamos valores cuando necesitamos hacer coincidir la columna y la fila? En este artículo, aprenderemos cómo hacer la suma de filas y columnas coincidentes.
Hay dos fórmulas para hacer esto pero primero, veamos el escenario.
Aquí, tengo una tabla que registra las ventas realizadas por los empleados en diferentes meses. El nombre de los empleados se puede repetir. Vea la siguiente figura:
Necesitamos obtener la suma del mes de mayo donde el vendedor es Donald.
Método 1: Resumir el encabezado de columna y el encabezado de fila coincidentes con la función SUMPRODUCTO.
los Función SUMPRODUCT es la función más versátil cuando se trata de sumar y contar valores con criterios complicados. La función genérica para sumar haciendo coincidir columna y fila es:
| = SUMPRODUCTO ((columnas) * (cabeceras_columna = cabecera_columna) * (cabeceras_fila = cabecera_fila) |
Columnas:Es el rango bidimensional de las columnas que desea sumar. No debe contener encabezados. En la tabla de arriba es C3: N7.
cabeceras_columna:Es el rango de encabezado decolumnas que quieres sumar. En los datos anteriores, es C2: N2.
encabezado de columna: Es el encabezado que desea hacer coincidir. En el ejemplo anterior, está en B13.
Sin más demora usemos la fórmula.
encabezados_fila:Es el rango de encabezado defilas que quieres sumar. En los datos anteriores, es B3: B10.
row_heading: Es el encabezado que desea hacer coincidir en filas. En el ejemplo anterior, está en F13.
Sin más demora usemos la fórmula.
Escriba esta fórmula en la celda D13 y deje que Excel haga su magia (no existe la magia) …
| = SUMPRODUCTO ((C3: N10) * (C2: N2 = B13) * (B3: B10 = E13)) |
Esto devuelve el valor:
Ahora, cuando cambie el mes o el vendedor, la suma cambiará según el encabezado de la fila y el encabezado de la columna.
¿Como funciona?
Esta simple lógica booleana.
(C2: N2 = B13): Esta declaración devolverá una matriz de VERDADERO y FALSO. Todos los valores coincidentes en la columna tienen verdadero y otros tendrán falso. En este caso, solo tendremos un Verdadero ya que el rango C2: N2 solo contiene una instancia de Mayo en 5th Localización.
(B3: B10 = E13): Esto funcionará igual que el anterior y devolverá una matriz VERDADERO y FALSO. Todos los valores coincidentes tendrán VERDADERO y otros tendrán FALSO. En este caso, tendremos 2 VERDADEROS ya que el rango B3: B10 tiene dos instancias de "Donald".
(C2: N2 = B13) * (B3: B10 = E13): Ahora estamos multiplicando las matrices devueltas por declaraciones. Esto implementará una lógica y obtendremos una matriz de 1 y 0. Ahora tendremos una matriz 2D que contendrá 2 1 y resto 0.
(C3: N10) *(C2: N2 = B13) * (B3: B10 = E13)= Finalmente multiplicamos la matriz 2D con la tabla 2D. Devolverá nuevamente una matriz de ceros y los números que coinciden con los criterios.
Finalmente, el SUMPRODUCTO La función resumirá la matriz que dará como resultado la salida deseada.
Método 2: resumir el encabezado de columna y el encabezado de fila coincidentes con las funciones SUMA e IF
La fórmula genérica para sumar filas y columnas coincidentes usando la función SUMA y SI de Excel es:
| = SUMA (SI (encabezados_columna = encabezado_columna, SI (encabezados_fila = encabezado_fila, columnas))) |
Todas las variables son las mismas que en el método explicado anteriormente. Aquí, solo deben usarse en un orden diferente.
Escribe esta fórmula en la celda D13:
| = SUMA (SI (C2: N2 = B13, SI (B3: B10 = E13, C3: N10))) |
Esto devuelve la respuesta correcta. Vea la captura de pantalla a continuación:
¿Como funciona?
La lógica es la misma que la del primer método SUMPRODCUT, solo que el mecanismo es diferente. Si lo explico brevemente, la función IF interna devuelve una matriz 2D de la misma dimensión que la tabla. Esta matriz contiene el número de dos filas coincidentes. Luego, la función IF interna coincide con los encabezados de dos columnas en esta matriz y devuelve la matriz 2D que solo contiene los números que coinciden con la columna y el encabezado. Todos los demás elementos de la matriz serán FALSE.
Finalmente, la función SUM suma esta matriz y obtenemos nuestra suma.
Así que sí, chicos, así es como pueden resumir las filas y columnas coincidentes de una tabla en Excel. Espero que te haya resultado útil y explicativo. Si tiene alguna duda con respecto a este tema o tiene alguna otra duda relacionada con Excel / VBA, pregunte en la sección de comentarios a continuación.
Cómo sumar una columna en un Excel haciendo coincidir el encabezado | Si desea obtener la suma de una columna con solo usar el nombre de la columna, puede hacerlo de 3 formas sencillas en Excel. La sintaxis del método SUMPRODUCT para sumar la columna coincidente es:
SUMIF con referencia 3D en Excel |El hecho divertido es que la referencia normal de Excel 3D no funciona con funciones condicionales, como la función SUMIF. En este artículo, aprenderemos cómo hacer que las referencias 3D funcionen con la función SUMIF.
Referencia relativa y absoluta en Excel | Hacer referencias en Excel es un tema importante para todos los principiantes. Incluso los usuarios experimentados de Excel cometen errores al hacer referencia.
Referencia de hoja de trabajo dinámica | Proporcione hojas de referencia dinámicamente utilizando la función INDIRECTA de excel. Esto es simple…
Ampliación de referencias en Excel | La referencia en expansión se expande cuando se copia hacia abajo o hacia la derecha. Usamos el signo $ antes de la columna y el número de fila para hacerlo. Aquí hay un ejemplo …
Todo sobre la referencia absoluta | El tipo de referencia predeterminado en Excel es relativo, pero si desea que la referencia de celdas y rangos sea absoluta, use el signo $. Aquí están todos los aspectos de la referencia absoluta en Excel.
Articulos populares:
50 accesos directos de Excel para aumentar su productividad | Acelera tu tarea. Estos 50 atajos harán que su trabajo sea aún más rápido en Excel.
La 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.
CONTAR.SI en Excel 2016 | Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar un valor específico. La función Countif 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 en condiciones específicas.