Imagen: Captura de pantalla

El artículo Cómo calcular subtotales condicionales en una hoja de ingresos de Excel utiliza expresiones simples, una función y un formato condicional para mostrar un saldo diario en una hoja de ingresos. El resultado proporciona mucha información y no es necesario esforzarse mucho para obtenerla.

En este artículo, abordaremos un problema similar: un total acumulado condicional. Un total acumulado también es común en dicha hoja. Primero, diseñaremos una hoja simple y agregaremos un total acumulado. A continuación, complicaremos las cosas agregando condiciones. Si eso suena complicado, no se preocupe. Este no es el caso.

VER: Cómo agregar una lista desplegable a una celda de Excel (República Tecnológica)

Uso Microsoft 365 en un sistema Windows 10 de 64 bits, pero puede usar versiones anteriores. Para su comodidad, puede descargar el archivo de demostración .xlsx. La edición del navegador admitirá las funciones y expresiones utilizadas en este artículo. Este artículo asume que usted tiene conocimientos básicos de Microsoft Excel.

Índice
  • Cómo hacer una hoja de trabajo básica de crédito-débito
  • Cómo sumar un total acumulado
  • Cómo sumar un total acumulado condicional
  • Cómo agregar un total acumulado para créditos y débitos
  • Cómo agregar un formato condicional
  • No descuides el orden de las fechas.
  • Cómo hacer una hoja de trabajo básica de crédito-débito

    Comenzaremos con la hoja simple que se muestra en Figura A. Cada transacción (fila o registro) tiene un total en la columna E, resultado de la siguiente expresión:

    E3:=C3-D3

    Esta simple expresión resta el débito del crédito. En algunas situaciones, esta información sería útil, pero tal como es, para la mayoría de nosotros, no es particularmente relevante. Algunas de las transacciones muestran un déficit que en realidad no existe (aunque podría existir). Un déficit comercial no es lo mismo que los ingresos que caen por debajo de $0. Depende de cómo utilice los datos para generar información significativa. Lo primero que agregaremos es un total acumulado simple.

    Figura A

    Cómo sumar un total acumulado

    A Fácil el total acumulado no tiene condiciones y se puede manejar agregando una segunda expresión:

    =créditodébito+Balance anterior

    Figura B muestra el total acumulado en la columna F. F3 contiene la misma expresión simple que E3: reste el débito del crédito. F4 y las celdas restantes suman el total de la transacción al registro anterior:

    F4: =C4-D4+C3

    Figura B

    En pocas palabras, la expresión resta el débito del crédito y suma el total acumulado anterior. Por lo tanto, el último registro muestra el correr balance de $2.873,96. Nótese que no hay déficit, aunque puede haberlo, y en este caso sería más significativo que en la primera hoja si en algún momento realmente los ingresos fueran por debajo de 0$. Nuevamente, la forma en que usa los datos es importante, y ahí es donde entran las condiciones. Agreguemos un total acumulado que deja de calcularse al final de cada día.

    Cómo sumar un total acumulado condicional

    Si se pregunta en qué se diferencia un "total acumulado que deja de calcularse al final del día" de un saldo diario, probablemente esté en buena compañía. La diferencia es esta: un saldo diario es un resultado que devuelve todas las transacciones del día; un total acumulado se basa en cada transacción, devuelve un total diario y luego comienza de nuevo cuando cambia la fecha. Uno no es mejor que el otro; ellos son diferentes.

    Lo que buscamos ahora es un total acumulado que comience con la primera transacción de cada día, evalúe todas las transacciones de ese día y finalice con el total acumulado final de ese día. Eso suena como un montón de requisitos para cumplir, y una simple expresión no hará el trabajo.

    Primero, agreguemos una nueva columna para el total acumulado diario a la columna G. Como siempre, la primera fila necesita la primera expresión simple que usamos en los dos ejemplos anteriores:

    G3: =C3-D3

    A continuación, agregue la siguiente función a G4 y cópiela en las celdas restantes del conjunto de datos:

    G4: =SI(B3=B4,C4-D4+G3,C4-D4)

    Cuando la fecha actual y la fecha en el Próximo record, son iguales, la función evalúa la expresión del total acumulado. Cuando las fechas no son las mismas, la función evalúa la expresión simple que solo evalúa el registro actual. Como puedes ver en Figura C, el saldo corriente diario proporciona mucha información sobre todas las transacciones del día, pero puede ser engañoso; los déficits pertenecen a transacciones diarias, no a cuentas balance. Cada día comienza con un saldo de 0. Por ejemplo, el 29 de abril, el total acumulado condicional muestra un déficit de $135, pero el total acumulado simple muestra un saldo de cuenta de $1658,96.

    Figura C

    Decidir si necesita un total acumulado diario o un total acumulado simple depende de cómo use los datos, pero consideremos otra posibilidad. Suponga que desea conocer el total acumulado de créditos y débitos.

    Cómo agregar un total acumulado para créditos y débitos

    Las columnas C y D almacenan los valores de crédito y débito, respectivamente. Si está utilizando un objeto Tabla, puede activar la fila Totales y ver el débito y los créditos totales de toda la hoja. Pero supongamos que desea ver un total acumulado de los valores de crédito y débito. Agregaremos dos columnas adicionales, Crédito acumulado y Débito acumulado, respectivamente, a las columnas H e I. ¿Puedes adivinar las expresiones que usarás en H3 e I3? Estos son los más simples de todos en este punto:

    H3:=C3

    I3:=D3

    Comienza con los primeros valores de crédito y débito. El siguiente paso es agregar las expresiones que suman los valores de débito y crédito actuales a los valores de crédito y débito anteriores:

    H4:=C4+H3

    I4: =D4+I3

    Copie en las celdas restantes del conjunto de datos. Figura D muestra los valores actuales de crédito y débito, y la última línea muestra el total de cada uno. Si resta los débitos totales de los créditos, el resultado debe ser igual al saldo diario en F12.

    Figura D

    Puede detenerse en este punto, pero hay una cosa que quizás desee hacer. Tal como están las cosas, el total acumulado diario se pierde en todos estos datos.

    Cómo agregar un formato condicional

    Si desea que el saldo corriente diario se destaque como se muestra en Figura E, utilice formato condicional. Comience seleccionando esta columna (G3:G12). Luego, en la pestaña Inicio, haga clic en Formato condicional en el grupo Estilos y elija Nueva regla. En el cuadro de diálogo resultante, seleccione el último elemento en el panel superior, Usar fórmula..., luego agregue la fórmula a continuación:

    =$B3$B4

    Haga clic en Formato y luego en Rellenar. De la paleta, elija un color (elijo un verde medio) y haga clic en Aceptar. Figura E muestra la regla y el formato. Haga clic en Aceptar para volver a la hoja que se muestra en Figura F. Este último paso no es necesario, pero si la acumulación diaria es importante, es fácil destacarlo.

    Figura E

    Figura F

    No descuides el orden de las fechas.

    Las grabaciones en nuestro archivo de demostración están ordenadas por fecha, lo cual tiene sentido. Dado que la fecha es la condición aplicada, es importante que los registros estén ordenados por fecha. De lo contrario, las expresiones y funciones de este artículo devolverán valores incorrectos. Cuando aplica esto a su propio trabajo, es posible que sus registros no se clasifiquen o agrupen por condiciónpor lo que es posible que deba verificar esto antes de informar los resultados.