La cantidad de elementos disponibles para filtrar es limitada. Excel no puede filtrar columnas en las que el número de elementos supera los 999 (no el número de filas).
Para filtrar cuando hay más de 999 elementos, use el filtro avanzado.
Para crear un filtro avanzado, usaremos las funciones “OFFSET” y “COUNTA” en Microsoft Excel.
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: - La cantidad de filas que desea mover desde 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.
Para obtener el resultado deseado, debemos seguir 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 a 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 un filtro avanzado, 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 podemos agregar más entradas que 999 elementos en Microsoft Excel.
