Cómo calcular subtotales condicionales en una hoja de ingresos de Excel
El artículo "Cómo calcular subtotales condicionales en una hoja de ingresos de Excel" realiza un seguimiento de un balance diario en una hoja de ingresos simple. Este tipo de subtotales es común y se puede lograr con expresiones simples: restar esto, sumar aquello, y así sucesivamente. Esto funciona bien hasta que se desea algo más específico. En el primer artículo, utilizamos una función IF() simple para devolver un total de todas las transacciones realizadas en el mismo día; los resultados son el saldo diario. En este artículo, trabajaremos con expresiones para devolver un saldo mensual y anual.
Estoy utilizando Microsoft 365 en un sistema de 64 bits con Windows 10, pero puedes utilizar versiones anteriores. Para tu comodidad, puedes descargar los archivos de demostración en formato .xlsx y .xls. La edición del navegador admitirá las funciones y expresiones utilizadas en este artículo. Se asume que tienes habilidades básicas de Microsoft Excel.
Cómo obtener el total de transacciones y el saldo diario en Excel
Comenzaremos con la hoja de transacciones simple en Figura A. La primera expresión en la columna E,
E3: =C3-D3
resta los débitos de los créditos para la primera transacción, devolviendo un total de transacción. La segunda expresión, que se copia en los registros restantes,
Cómo utilizar AutoCompletar y una lista desplegable en ExcelE4: =C5-D5+E4
suma el total de la transacción anterior, creando un total acumulado o saldo.
La función en la columna F
=IF(B4=B3,"",E3)
devuelve el total de la transacción actual si la fecha de abajo no coincide con la fecha de la transacción actual. Esta función devuelve un total solo cuando no se cumple esa condición, es la acción FALSE. Por eso hay celdas vacías en la columna F; esas celdas vacías representan los días con múltiples transacciones. El resultado de esta función repite el total acumulado en la columna E, pero es más fácil distinguir el subtotal diario cuando se separa de los otros totales diarios. La hoja también contiene un formato condicional que resalta el subtotal diario.
Figura A
Cómo generar una lista de nombres de hojas en Excel usando VBA
En este momento, es importante tener en cuenta que las transacciones (registros) deben ordenarse por fecha (columna B). Lo más probable es que, dado la naturaleza de los datos, los registros ya estén ordenados por fecha; pero si no lo están, debes ordenarlos por la columna B. De lo contrario, las expresiones de transacción y diarias no devolverán los resultados correctos. Además, la fuente en color naranja es un formato condicional (en el archivo de demostración descargable) que resalta el subtotal diario. Tenemos nuestra función de saldo diario, pero pasemos a otro subtotal por fecha, un subtotal mensual.
Cómo agregar un saldo mensual en Excel
Como has visto, un saldo diario solo requiere una función IF() simple para que coincida con las fechas antes de devolver el saldo de cada día. Supongamos que también deseas un saldo mensual. Para hacerlo, debes coincidir los valores de mes para la fecha de cada transacción. Afortunadamente, podemos confiar en la misma lógica que ya se utiliza para devolver la función diaria
=IF(B4=B3,"",E3)
agregando la función MONTH() a la condición
=IF(MONTH(B4)=MONTH(B3),"",E3)
Cómo combinar números de capítulo y página en WordIngresa la función anterior en la celda G3 y cópiala en el resto de la hoja. (Si estás utilizando un objeto Tabla, no necesitarás copiar la función; el objeto Tabla lo hará por ti). La Figura B muestra los resultados; solo hay dos totales mensuales, abril y mayo.
Figura B
La lógica es la misma que para el subtotal diario, pero la función IF() devuelve el total de la transacción de la columna E solo cuando los meses (columna B) no coinciden. Por ejemplo, la función en G9 devuelve $1,248.96 porque el valor del mes en B9 no coincide con el valor del mes en B10. En otras palabras, la transacción en el siguiente registro pertenece a otro mes.
Ahora, pasemos a un saldo anual.
Cómo obtener un saldo anual en Excel
Nuestro simple conjunto de datos tiene transacciones solo para el año 2020, pero eso no importa. Esperamos solo un saldo para el conjunto de datos. La solución es la misma, pero esta vez utilizaremos la función YEAR() de la siguiente manera:
Cómo referenciar un número de página en Word=IF(YEAR(B4)=YEAR(B3),"",E3)
Como puedes ver en Figura C, esta función devuelve solo un saldo debido a que todas las fechas están en el año 2020.
Figura C
Veamos qué sucede si agregamos una fecha de 2021, como se muestra en Figura D. Ahora, la función devuelve dos saldos anuales. También expone un error: observa de cerca la columna de total mensual. ¿Puedes verlo?
Figura D
Cutefish: Un nuevo y hermoso entorno de escritorio para Linux
La función de saldo mensual solo evalúa por meses. En consecuencia, cuando se agrega la nueva transacción para mayo de 2021, considera que se cumple la condición de mes. Ambas fechas son de mayo. Es por eso que no hay un saldo mensual para mayo de 2020 en G12, lo cual sería correcto. Hice esto a propósito para mostrar lo que puede suceder. Estas funciones simples pueden funcionar para ti, pero ten en cuenta esta posibilidad. La solución es agregar una verificación condicional tanto para el mes como para el año. Esa solución es mucho más compleja.
Cómo usar formatos condicionales para subtotales en Excel
El artículo original incluye fórmulas de formato condicional que evalúan los totales de transacción en la columna E para que puedas eliminar las columnas de fecha condicionales adicionales, si lo prefieres. El archivo de demostración contiene nuevas reglas para los totales mensuales y anuales:
=MONTH($B4)<>MONTH($B3)
=YEAR($B4)<>YEAR($B3)
Ten en cuenta que las nuevas reglas tienen precedencia en el siguiente orden: diario, mensual, anual. Esto significa que si un total de transacción cumple más de una condición de fecha, prevalecerá la regla de fecha más grande. Es posible que no desees esto, así que debes tener en cuenta que deberás modificar las fórmulas para adaptarlas a tus necesidades.
Cómo descargar e instalar el archivo ISO de Windows 11Mantente atento para más consejos de Excel
Debido a que todas las expresiones, funciones y reglas de fórmulas son simples, no se adaptarán a todas las situaciones, como la función de saldo mensual. En un artículo futuro, te mostraré una función más compleja que maneja tanto el mes como el año para aquellos conjuntos de datos que requieren un control más preciso.
En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cómo calcular subtotales condicionales en una hoja de ingresos de Excel , tenemos lo ultimo en tecnología 2023.
Artículos Relacionados