Para recuperar el último valor en una lista dinámica, usaremos la opción de Validación de datos junto con las funciones OFFSET y COUNTA en Microsoft Excel 2010/2013.
CONTAR: Devuelve el recuento del número de celdas que contienen valores.
Sintaxis de la función “CONTAR”: = CONTAR (valor1, valor2, valor3….)
Ejemplo: En el rango A1: A5, las celdas A2, A3 y A5 contienen los valores, y las celdas A1 y A4 están en blanco. Seleccione la celda A6 y escriba la fórmula-
= CONTAR (A1: A5) la función volverá 3
COMPENSAR: Devuelve una referencia a un rango que está desplazado un número de filas y columnas de otro rango o celda.
Sintaxis de la función OFFSET: = OFFSET (referencia, filas, columnas, alto, ancho)
Referencia:- Esta es la celda o rango desde el que desea compensar.
Filas y columnas para mover: - Cuántas filas desea mover el punto de partida y ambas pueden ser positivas, negativas o cero.
Alto y ancho: - Este es el tamaño del rango que desea devolver. Este campo es opcional.
Tomemos un ejemplo para comprender la función de compensación en Excel.
Tenemos datos en el rango A1: D10. La columna A contiene el código de producto, la columna B contiene la cantidad, la columna C contiene el costo por producto y la columna D contiene el costo total. Necesitamos devolver el valor de la celda C5 en la celda E2.
Siga los pasos que se mencionan a continuación.
- Seleccione la celda E2 y escriba la fórmula.
- = DESPLAZAMIENTO (A1,4,2,1,1)y presione Entrar en el teclado.
- La función devolverá el valor de la celda C5.
En este ejemplo, necesitamos obtener el valor de la celda C5 en E2. Nuestra celda de referencia es la primera celda en el rango que es A1 y C5 está 4 filas abajo y 2 columnas a la derecha de A1. Por lo tanto, la fórmula es = OFFSET (A1,4,2,1,1) o = OFFSET (A1,4,2) (ya que 1,1 es opcional).
Ahora, tomemos un ejemplo para recuperar el último valor en una lista dinámica.
Tenemos nombres de países en un rango. Ahora, si agregamos más países a esta lista, debería estar disponible en la lista desplegable automáticamente.
Para preparar una lista dinámica, necesitamos crear una fórmula que recuperará el último valor en la columna y se actualizará automáticamente cuando se agregue un nuevo número.
Siga los pasos que se indican a continuación:
- Seleccione la celda B2.
- Vaya a la pestaña Datos, seleccione Validación de datos en el grupo Herramientas de datos.
- Aparecerá el cuadro de diálogo "Validación de datos". En la pestaña "Configuración", seleccione "Personalizado" de la lista desplegable Permitir.
- Se activará el cuadro de fórmula.
- Escribe la fórmula en este cuadro.
- = DESPLAZAMIENTO (A: A, 1,0, CONTAR (A: A) -1,1).
- Haga clic en Aceptar.
- En esta etapa, la última celda actualizada es A11.
- Para comprobar si la validación de datos funciona correctamente, agregue un nombre de ciudad en la celda A12.
Tan pronto como agregue una entrada en A12, se agregará a la lista desplegable.
Esta es la forma en que puede crear una lista dinámica y completar nuevas entradas en ella automáticamente en Microsoft Excel 2010 y 2013.