Transcripción del vídeo
En esta hoja de Excel tenemos información financiera de un pequeño negocio. Las celdas de color azul muestran el presupuesto que se estimó para los meses del primer semestre del año y de color amarillo el monto que realmente gastamos.
Esta información se repite para diferentes tipos de gasto dentro de la empresa y en las filas 15 y 16 queremos obtener el total del presupuesto para cada mes, así como el total de lo que gastamos.
Ya que los datos que queremos sumar se encuentran en filas alternas, es necesario crear una fórmula que nos permita hacer este cálculo de una manera sencilla sin la necesidad de sumar celda por celda.
En esta ocasión te mostraré dos métodos que nos ayudarán a sumar filas alternas y el primero de ellos requiere de una columna Auxiliar, así que en la celda I1 ingresaré el título de la columna Auxiliar.
En esta columna vamos a generar un número que nos ayudará a saber si una fila es par o impar.
En la celda I2 ingresaré el símbolo igual y la función RESIDUO y el primer argumento será el número que vamos a evaluar y por lo tanto ingresaré la función FILA que me devolverá el número de fila de la celda actual.
El segundo argumento de la función RESIDUO es el número divisor e ingresaré el número dos de manera que la fórmula nos devuelva el residuo cero para las filas pares y el residuo uno para las filas impares.
Cierro el paréntesis de la función RESIDUO y pulsaré la tecla Enter y obtendremos el valor cero porque estamos en una fila par.
Copiaré la fórmula hacia abajo y podrás notar que efectivamente obtenemos el número cero para las filas pares y el número uno para las filas impares. Los números cero y uno se van alternando y utilizaremos esta columna para sumar las filas alternas.
En la celda C15 ingresaré el símbolo igual y la función SUMAR.SI. Esta función nos permitirá evaluar la condición en una columna y sumar los valores que se encuentren en otra columna y en esta ocasión vamos a aprovechar esta característica.
El primer argumento es el rango de celdas que será evaluado para saber si se cumple con el criterio, así que ingresaré la referencia a la columna Auxiliar que es I2:I13 y en seguida pulsaré la tecla F4 para convertir la referencia en absoluta ya que quiero que se mantenga fija al momento de copiar la fórmula a otra celda.
El segundo argumento de la función será el número cero, ya que con esta fórmula quiero obtener el total del presupuesto lo cual implica sumar las celdas de color azul que son filas pares y que por lo tanto tienen el número cero en la columna Auxiliar.
El tercer argumento de la función será el rango de celdas que contiene los valores que vamos a sumar y ya que estoy creando la fórmula para la columna Enero, ingresaré la referencia al rango C2:C13 y cerraré el paréntesis de la función.
Antes de pulsar Enter, explicaré de nuevo esta fórmula. Se evaluarán los valores de la columna Auxiliar y solo se considerarán aquellas filas que sean iguales a cero. Si se cumple el criterio, se agregará a la suma el valor que se encuentre en la columna enero.
Pulsaré la tecla Enter y obtendremos el valor 44085 y para comprobar que esta es la suma de las filas pares de la columna enero, voy a seleccionar cada una de las celdas manteniendo pulsada la tecla Control.
Me aseguro de seleccionar cada un de las celdas de color azul para esta columna y una vez que termino con la selección podrás ver en la barra de estado que tenemos la suma con el mismo total de 44085 de nuestra fórmula.
La función SUMAR.SI ha utilizado los valores de la columna Auxiliar para incluir en la suma solo las filas pares.
Voy a activar la celda C15 para copiar el texto de nuestra fórmula y en seguida activaré la celda C16 para pegarla.
La única modificación que tenemos que hacer a la fórmula, para que sume las filas de color amarillo, será cambiar el número cero por el número uno.
Con este cambio le estamos pidiendo a la función SUMAR.SI que considere solo las filas que tienen el valor uno en la columna Auxiliar y que son efectivamente las filas de color amarillo.
Pulsaré la tecla Enter y obtendremos el total de la suma y de nuevo puedo seleccionar cada una de las celdas de color amarillo de la columna enero para comprobar que nuestra fórmula ha devuelto el resultado correcto.
De nuevo puedes observar en la barra de estado que el resultado de la suma es 34690 que es el mismo valor de la celda C16 y por lo tanto nuestra fórmula ha devuelto el valor correcto.
En seguida voy a seleccionar las dos celdas, con las fórmulas que acabamos de crear, y voy a copiarlas a las columnas de la derecha y finalmente hago clic en el botón de Opciones de auto relleno para elegir la opción Rellenar sin formato de manera que solo se copie la fórmula.
De esta manera hemos utilizado la columna Auxiliar junto con la función SUMAR.SI para hacer la suma de los valores en las filas alternas.
Al principio mencioné que te mostraría dos métodos para sumar filas alternas, así que activaré la Hoja2 del libro para mostrarte esa segunda alternativa.
Pero antes de comenzar a crear la fórmula, debo decirte que este método utiliza una fórmula matricial, que es un tema avanzado de Excel, y aunque trataré de dar una explicación detallada del funcionamiento de la fórmula, será importante que utilices este método solo si sabes trabajar con fórmulas matriciales.
La ventaja de esta fórmula es que no necesitarás de una columna Auxiliar, sino que dentro de la fórmula se hará la selección de las filas adecuadas.
Lo primero que debemos obtener es una matriz de ceros y unos que indicarán las filas que serán incluidas en el cálculo.
Para ejemplificar la matriz que vamos a utilizar voy a seleccionar el rango J2:J13 e ingresaré la siguiente fórmula.
En primer lugar, la función RESIDUO y como primer argumento la función FILA que evaluará el rango C2:C13 y el segundo argumento de la función RESIDUO será el número dos y finalmente cierro el paréntesis.
Con esta fórmula obtendremos un residuo de cero para las filas pares y un residuo de uno para las filas impares.
Para terminar nuestra fórmula matricial, debemos pulsar la combinación de teclas Ctrl+Shift+Enter y puedes notar que son las filas impares las que tienen el número uno.
Esta matriz será de ayuda para sumar las filas de color amarillo, pero si queremos sumar las filas de color azul necesitaremos una matriz que invierta estos valores para tener el número uno en las filas pares.
Para generar una matriz de ese tipo debemos hacer un pequeño cambio en la fórmula que acabamos de crear, así que primero voy a copiar el texto de la fórmula de la celda J2 y en seguida seleccionaré el rango K2:K13 y pegaré el texto de la fórmula.
El cambio que debemos hacer es sumar el valor uno al resultado de la función FILA y con eso invertiremos los números cero y uno de la matriz devuelta.
Pulsaré la combinación Ctrl+Shift+Enter y tendremos una matriz donde las filas pares tendrán el número uno y las filas impares el número cero.
Las fórmulas que he creado en las columnas J y K solo son para ilustrar la manera en que funcionará nuestra fórmula matricial pero no son necesarias.
Ahora vamos a crear nuestra primera fórmula en la celda C15 donde queremos sumar las filas de color azul de la columna enero.
Para que nuestra fórmula incluya solo las celdas de color azul, debemos utilizar la matriz que tiene el número uno en las filas pares y que es la fórmula de la columna K así que voy a copiar el texto de la fórmula de la celda K2 excluyendo el signo igual.
Enseguida ingresaré el símbolo igual en la celda C15 y la función SUMAPRODUCTO que nos pide las matrices que serán consideradas en el cálculo y la primera matriz será la fórmula que acabamos de copiar así que pegaré el texto en este argumento.
La segunda matriz de nuestro cálculo será el rango C2:C13 que son los datos de enero y cerramos el paréntesis de la función SUMAPRODUCTO.
La manera en que trabaja la función SUMAPRODUCTO es la siguiente: En primer lugar, multiplicará los valores de las matrices, que en este ejemplo son los valores de la columna enero y los valores que están ejemplificados en la columna K.
Aquellos valores que son multiplicados por cero se convierten en cero y, por el contrario, aquellos valores que se multiplican por uno, permanecen igual y eso quiere decir que con esta fórmula nos quedaremos solo con los valores de las celdas de color azul.
Una vez que se han hecho las multiplicaciones, se sumarán todos los resultados y la función devolverá el total y de esa manera obtendremos la suma de las filas alternas.
Para ingresar la fórmula matricial pulsaré la combinación de teclas Ctrl+Shift+Enter y obtendremos el número 44085.
Activaré por un instante la Hoja1 del libro para que observes que es el mismo resultado que obtuvimos anteriormente y eso quiere decir que nuestra fórmula matricial funciona correctamente.
Ahora vamos a crear la fórmula del Total Real que sumará las celdas de color amarillo y el primer paso será copiar la fórmula de la celda J2 sin considerar el signo igual y en la celda C16 ingresaré la función SUMAPRODUCTO.
El primer argumento de la función será la matriz devuelta por la fórmula que acabamos de copiar y el segundo argumento será el rango C2:C13 y terminaré la fórmula con el paréntesis y pulsando la combinación Ctrl+Shift+Enter.
En esta ocasión, los valores de la columna enero son multiplicados por la matriz que está ejemplificada en la columna J y por lo tanto solo permanecerán los valores de las filas impares que son multiplicados por el número uno.
Activaré por un instante la Hoja1 para comprobar que hemos obtenido el mismo resultado para el Total Real.
Para finalizar, regresaré a la Hoja2 y copiaré las fórmulas recién creadas y para eso seleccionaré las celdas C15 y C16 y las copiaré a la derecha hasta llegar a la última columna y en el botón de Opciones de auto relleno seleccionaré la opción Rellenar sin formato.
Con esto hemos copiado nuestra fórmula matricial para obtener las sumas correspondientes de todos los meses.
Recuerda que las fórmulas de las columnas J y K solo las coloqué para ilustrar la manera en que trabaja la función SUMAPRODUCTO y por lo tanto puedo eliminarlas sin afectar nuestros cálculos.
Seleccionaré ambas columnas y haré clic derecho en el encabezado de columna y elegiré la opción Eliminar.
Los dos métodos que revisamos en esta ocasión nos devuelven el mismo resultado. Si apenas comienzas a familiarizarte con las fórmulas de Excel será mejor que utilices la opción que mostré en la Hoja1 que se apoya en los valores de la columna Auxiliar.
Por el contrario, si ya eres un usuario avanzado de Excel, considera el uso de la fórmula matricial ya que nos permite realizar el cálculo sin la necesidad de insertar una columna adicional a nuestros datos.