Soy nuevo en esta versión de StackExchange, así que si pertenece a otro lugar, muévalo; Aunque pensé que este sería el mejor lugar.
Estoy creando una hoja de cálculo de Excel que simplemente almacena datos financieros básicos en 5 columnas ( ,,, Check Numbery ) y filas indefinidas . Cada hoja de trabajo representa un mes y cada libro de trabajo representa un año. Cuando hago o recibo un pago, lo almaceno como una nueva fila, lo que, inherentemente, hace que el número de filas por mes sea indefinido. La celda de cada transacción es la suma de la celda de la fila superior a ella y la celda de su fila. Quiero que cada mes comience con una fila especial (la primera después de los encabezados de las columnas) que muestre un resumen de losDate of TransactionDescriptionProfit from TransactionBalance After TransactionBalanceBalanceProfitúltimotransacciones del mes. Por ejemplo, la Balance After Transactioncelda mostraría el saldo de la última fila y la Profit from Transactioncelda mostraría las ganancias generales del mes).
Sé que si supiera que cada mes tiene exactamente 100 gastos, podría lograrlo para marzo con las siguientes fórmulas de ganancia y saldo, respectivamente:
=February!E2 - February!E102
=February!E102
Sin embargo, lo hagoNOSé cuántas filas habrá en la tabla de cada mes y me gustaría automatizar esto tanto como sea posible (por ejemplo, si encuentro un gasto omitido o duplicado en enero, no quiero tener que actualizar todas las fórmulas). que apuntan al saldo final de enero).¿Cómo puedo hacer que Excel use automáticamente el último valor ingresado en una columna, en cualquier hoja de cálculo de Excel, en una fórmula?
Respuesta1
Mi primera sugerencia será convertir este rango en una tabla (Insertar -> Tabla), y luego puede agregar una fila de Totales, a la que puede hacer referencia desde una hoja/libro de trabajo diferente, por su nombre y luego no habrá necesidad de preocuparse. número de filas.
Si no es una opción, puedes hacer esto:
Para obtener el resumen de ganancias del mes anterior debe utilizar
=SUM(Month!$D:$D)
(esto supone que la columna de ganancias no tiene otros números además de las ganancias).
Y para el saldo del mes anterior, la fórmula @Jesse debería funcionar, pero solo si tu tabla comienza desde la primera fila, y si no es así, puedes probar esto:
=INDEX(Month!$E:$E,MATCH(TRUE,ISBLANK(Month!$E:$E),0)-1)
al ingresarlo, asegúrese de usar CTRL+SHIFT+ENTER. Esto se utilizará ISBLANKen toda la columna para encontrar la primera celda vacía y luego recuperar el valor justo encima de ella (que sería la última línea).
Para hacer esto más dinámico y preparado para el futuro, sugeriría usar rangos con nombre, de modo que, si es necesario, no tendrá que cambiar todas las referencias a las columnas.
Respuesta2
No utilices indirectos, con este nivel de cálculo será doloroso. Indirecto es volátil y hará que cada fórmula de su hoja se vuelva a calcular cada vez que cambie algo.
Puedes usar index en su lugar. Se vería así:
=INDEX(Month!A:E,COUNTA(Month!A:A),COLUMN())
Solo hace referencia a columnas completas y el desplazamiento de la columna se calcula automáticamente con la COLUMN()función.
Respuesta3
Otra forma sencilla sería insertar nombres en el libro. Enero, febrero, etc. Úselos para determinar su última fila contando la cantidad de entradas de fecha en su columna de fecha.
Por ejemplo, inserte un nombre "Enero" y RefersTo =COUNT(Enero!A:A)+2. Esto le dará el recuento de filas y podrá usarlo en su fórmula con indirecto para construir la referencia de rango.
La última celda de la fila será =INDIRECT("E"&Jan) y, si lo desea, también puede utilizar estas referencias en una hoja de resumen en otro lugar.
Esto supone números (fechas) en la columna A. Ignorará un encabezado si es texto, pero en la fila 2, asegúrese de no tener un valor numérico o sesgará el recuento de filas.