Cuando necesite ver el último valor de una tabla de Excel o rango de datos, estas fórmulas le ayudarán a realizar el trabajo. Este recorrido detalla las fórmulas necesarias y algunos códigos VBA que realizan la misma tarea.
Recuperar el último valor de una columna es una tarea fácil en una base de datos, pero Excel no es una base de datos (aunque mucha gente la usa como tal. Llevar a cabo esta tarea es un poco más difícil en Excel, pero sólo un poco. En este artículo, le mostraré una fórmula que funciona con un objeto Tabla y un rango de datos regular. También le mostraré los equivalentes de VBA.
Más información sobre Office
Estoy usando Excel 2019 en un sistema Windows 10 de 64 bits. Para su comodidad, puede descargar los archivos de demostración.xlsx y CLS. La fórmula del conjunto de datos no funcionará en las versiones anteriores del menú, y las macros no funcionan en la edición del navegador. La edición del navegador no reconoce el objeto Tabla, pero la fórmula sigue funcionando: Excel es lo suficientemente inteligente como para utilizar las referencias estructurales del objeto Tabla si abre el archivo en el navegador.
Usar una tabla
Gracias al objeto Tabla de Excel, este tipo de tarea es más simple que antes. La fórmula utiliza referencias estructurales, por lo que es larga, pero la fórmula hace referencia sólo a la Tabla. Esto significa que puede posicionar la fórmula casi en cualquier lugar y puede introducir datos por encima y por debajo de la columna en cuestión. Además, las referencias estructuradas son más fáciles de leer que las referencias de celdas; son similares a los nombres de rango en este sentido.
La muestra una simple Tabla de datos de factura y una fórmula (E2) que devuelve la última fecha en la columna C, Fecha de la factura. Como puede ver, la fórmula y la Tabla acomodan la Fila Total de la Tabla y los datos adyacentes.
Esta fórmula devuelve la última fecha de la columna C.
La fórmula utiliza las referencias estructuradas a las columnas Tabla y Fecha de la factura:
=INDEX(Facturas[Fecha de la factura],COUNTA(Facturas[Fecha de la factura])))
Para entender cómo funciona esta fórmula, necesita saber cómo funcionan las funciones INDEX() y COUNTA():
- INDEX() devuelve un valor o referencia de la celda en la intersección de una fila y columna específica dentro de un rango dado usando la sintaxis INDEX(array, fila,[columna].
- COUNTA() cuenta el número de celdas que no están vacías dentro de un rango específico usando la sintaxis COUNTA(valor1,[valor2].
Para aprender más sobre las funciones de conteo de Excel, lea La primicia sobre las funciones de conteo de Excel.
La función COUNTA() devuelve 30 porque hay 30 líneas en la columna Fecha de la factura, excluyendo las líneas de cabecera y de totales. (Puede incluir estas filas en un recuento incluyendo el identificador[#All].) Dado que la matriz sólo tiene una columna, el argumento de la columna no es necesario. Debido a que COUNTA() devuelve 30, la función INDEX() devuelve el valor en Datos de factura en la línea 30, 8/7/2019. La fórmula sigue funcionando si mueve la Tabla, si agrega una fila por encima de la Tabla y si agrega datos por encima o por debajo de los Datos de Factura.
Aunque flexible, la fórmula tiene un defecto: no puede manejar una celda en blanco. COUNTA() evalúa todos los valores, incluyendo el texto e incluso una cadena vacía, «»». Sin embargo, no cuenta las celdas en blanco. Por consiguiente, el número devuelto será el número total de filas menos el número de celdas en blanco. Si un registro no tiene valor, introduzca 0 o NA.
Puede usar VBA para lograr lo mismo usando el código de la Lista A. La propiedad End(xlDown) se refiere a la última celda de la columna Fecha de Factura. Como antes, las referencias de la Tabla saben dónde está la última fila, así que el texto debajo de la Tabla no es un problema. Sin embargo, esta referencia incluye la fila de Totales, que es la que acomoda la propiedad Offset.
Nota: No intente copiar el código directamente desde esta página web. En su lugar, descargue los archivosCLS o introduzca el código manualmente.
Listado A
Función ReturnLastValueTable() 'Devolver el último valor en la columna especificada.
ReturnLastValueTable = Rango («Facturas[Fecha de la factura]». Fin(xlDown.Offset(-1, 0.Value Debug.Print ReturnLastValueTableEnd Function
Utilizar un rango de datos
La misma fórmula general funciona sin la Tabla, pero es menos flexible. En la , se puede ver que la fórmula tiene la misma forma pero usa referencias de celdas:
=INDICE(C:C,COUNTA(C:C:C))
Esta vez, la función COUNTA() devuelve 31 porque cuenta la celda de encabezado en la fila 1. Por lo tanto, la fórmula devuelve el valor de la fila 31 en la columna C, 8/7/2019. Si tiene una línea de totales, también debe tenerla en cuenta.
La misma fórmula en un rango de datos regular funciona pero es menos flexible.
La fórmula funciona con un rango de datos normal, pero es menos flexible. No puede mover el conjunto de datos porque las referencias de las columnas no se actualizan. Insertar filas sobre el conjunto de datos puede desviar el cálculo ya que COUNTA() no evaluará las filas en blanco sobre el conjunto de datos -COUNTA() devuelve el número de celdas que contienen datos. Sin embargo, si las celdas por encima del conjunto de datos de la columna C contienen datos, la fórmula sigue funcionando. No recomiendo ese tipo de arreglo porque debes recordar este detalle. No puede ingresar datos debajo del conjunto de datos porque la referencia de la columna en COUNTA() evalúa toda la columna; ingresar datos debajo del conjunto de datos desviará la cuenta.
Puede hacer lo mismo usando el código de la Lista B. La propiedad End(xlDown) se detiene en la primera celda en blanco. Esto significa que puede introducir datos debajo del conjunto de datos en la columna C si hay al menos una celda en blanco entre la última fila y los nuevos datos.
Listado B
Función ReturnLastValueDataSet() 'Devolver el último valor en la columna especificada. ReturnLastValueDataSet = Rango("C1".End(xlDown.Value Debug.Print ReturnLastValueDataSetEnd FunctionÚltima llamada
Este estudio rápido no es exhaustivo, pero debería ser adecuado para la mayoría de las situaciones. La fórmula de la Tabla es más flexible, pero ninguna de las fórmulas contiene una celda en blanco. Si no hay ningún valor válido para un registro, introduzca 0.
Boletín semanal de Microsoft
Conviértase en un experto en Microsoft de su empresa con la ayuda de estos tutoriales de Windows y Office y de los análisis de nuestros expertos sobre los productos empresariales de Microsoft. Entregado Lunes y Miércoles
Envíeme su pregunta sobre Office
Respondo a las preguntas de los lectores cuando puedo, pero no hay garantía. No envíe archivos a menos que se le solicite; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos se eliminarán sin leer. Puede enviar capturas de pantalla de sus datos para ayudar a aclarar su pregunta. Cuando se ponga en contacto conmigo, sea lo más específico posible. Por ejemplo, «Please troubleshoot my workbook and fix what’s wrong» probablemente no obtendrá respuesta, pero «Can you tell me why this formula is’ t returning the expected results? Por favor, menciona la aplicación y la versión que estás usando. ConsejoTecnologico.com no me reembolsa por mi tiempo o experiencia en ayudar a los lectores, ni tampoco pido una cuota a los lectores a los que ayudo. Puede ponerse en contacto conmigo en susansalesharkins@gmail.com.
Lea también….