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 “COUNTA”: = COUNTA (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- _ = COUNTA (A1: A5) _ la función devolverá 3

img1

DESPLAZAMIENTO: 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 DESPLAZAMIENTO: = DESPLAZAMIENTO (referencia, filas, columnas, altura, ancho) Referencia: – Esta es la celda o rango desde el cual desea compensar .

Rutas y columnas para mover: _ _- La cantidad de filas que desea mover desde el punto de partida y ambas pueden ser positivas, negativas o cero.

_Altura y anchura _ * : – Este es el tamaño del rango que desea devolver. Este es un campo 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.

img2

Para obtener el resultado deseado, debemos seguir los pasos que se mencionan a continuación.

  • Seleccione la celda E2 y escriba la fórmula.

OFFSET (A1,4,2,1,1) * y presione Enter en el teclado.

  • La función devolverá el valor de la celda C5 .

img3

img4

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.

img5

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 del grupo Herramientas de datos.

img6

  • Aparecerá el cuadro de diálogo «Validación de datos». En la pestaña «Configuración», seleccione «Personalizado» de la lista desplegable Permitir.

img7

  • Se activará el cuadro de fórmula.

  • Escriba la fórmula en este cuadro.

* _ = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1) ._

  • Haga clic en Aceptar.

img8

  • En esta etapa, la última celda actualizada es A11.

img9

  • Para verificar si la Validación de datos está funcionando correctamente, agregue un nombre de ciudad en la celda A12.

img10

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.