Autor: Oscar Cronquist Artículo actualizado en Febrero 13, 2019

Robert Jr. pregunta:

Oscar
Estoy utilizando el código VBA y la matriz FilterUniqueSort para generar listas únicas que impulsan el Cambio de Autofiltro de Selección en varias columnas. ¿Hay alguna forma de hacer que la lista solo devuelva los valores únicos que son visibles en los datos de origen filtrados?

Veo una respuesta similar arriba usando solo una fórmula matricial, pero mi fuente es demasiado larga para que sea práctica. Cualquier ayuda sería muy apreciada.

Robert jr

Fórmula de matriz

He modificado una fórmula de Laurent Longre encontrada aquí: Excel E-letter de los expertos del sitio web de John Walkenbach.

Fórmula de matriz en la celda B26:

=INDEX(Table2[First Name], MATCH(0, IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), COUNTIF($C$25:C25, Table2[First Name]),""), 0))

Para ingresar una fórmula matricial, escriba la fórmula en una celda y luego presione y mantenga presionado CTRL + MAYÚS simultáneamente, ahora presione Entrar una vez. Suelte todas las teclas.

La barra de fórmulas ahora muestra la fórmula con un corchete de inicio y final que le indica que ingresó la fórmula con éxito. No entres en los rizos tú mismo.

Explicando la fórmula matricial en la celda B26.

Paso 1 - Crear matriz

Este paso es necesario para poder utilizar el Función subtotal en el siguiente paso los Función de fila devuelve números de fila basados ​​en una referencia de celda.

La Función MATCH convierte la matriz de números de fila en una matriz que comienza con 1.

OFFSET (Table2 [Nombre], MATCH (ROW (Table2 [Nombre]), ROW (Table2 [Nombre])) - 1, 0, 1)

se convierte en

OFFSET (Table2 [First Name], MATCH ({2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; XNX; 17; 18; }, {19; 20; 21; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; XNX; XNXX )

se convierte en

OFFSET (Table2 [Nombre], {1; 2; 3; 4; 5; 6; 7; 8: Recompensa de la competencia; 9; NUMX: Recolección, 10; 11; 12; 13, 14, 15)

se convierte en

OFFSET (Table2 [Nombre], {0; 1; 2; 3; 4; 5; 6; 7; NUMX: Recolección principal; 8, 9)

se convierte en

OFFSET ({"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara" "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}, {0; 1; 2; 3; 4; 5; 6; 7; ; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, 0, 1)

La Función de desplazamiento crea una matriz de matrices que la función SUBTOTAL puede procesar.

OFFSET ({"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara" "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}, {0; 1; 2; 3; 4; 5; 6; 7; ; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, 0, 1)

y devoluciones

{{"Kaya"}; {"Fraser"}; {"Jui"}; {"Cyril"}; {"Spencer"}; {"Horace"}; {"Emmit"}; {"Lynna"}; {"Charley"}; {"Fraser"}; {"Mckinley"}; {"Tiara"}; {"Damien"}; {"Linnie"}; {"Jui"}; {"Fraser"}; {"Siena"}; {"Lynna"}; {"Kelton"}; {"Kaya"}}

Paso 2 - ¿Qué valores están ocultos?

La Función subtotal en este paso contará cada matriz en la matriz como una sola y devolverá 1 si el valor no está vacío y no es visible. El primer argumento es 3 que representa COUNTA función. Cuenta el número de celdas que no están vacías.

SUBTOTAL (3, OFFSET (Table2 [First Name], MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1))

se convierte en

SUBTOTAL (3, {{"Kaya"}; {"Fraser"}; {"Jui"}; {"Cyril"}; {"Spencer"}; {"Horace"}; {"Emmit"}; {"Lynna "}; {" Charley "}; {" Fraser "}; {" Mckinley "}; {" Tiara "}; {" Damien "}; {" Linnie "}; {" Jui "}; {" Fraser "} ; {"Siena"}; {"Lynna"}; {"Kelton"}; {"Kaya"}})

y devoluciones

{1; 1; 1; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 1; 1; 0; 0; 1; 1}.

Esta matriz nos dice que los tres primeros valores en Table2 [Nombre] son ​​visibles, sin embargo, el cuarto valor está oculto, etc. 1 - visible, 0 (cero) - oculto.

Paso 3 - Convertir 1 a 0 (cero)

La Función IF tiene tres argumentos, el primero debe ser una expresión lógica. Si la expresión se evalúa como VERDADERA, sucede una cosa (argumento 2) y si FALSA sucede otra cosa (argumento 3).

IF (SUBTOTAL (3, OFFSET (Table2 [First Name], MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1)), COUNTIF ($ B $ 25: B25, Table2 [Nombre]), "")

La función COUNTIF en este paso se asegurará de que solo se devuelvan valores distintos únicos, contiene una referencia de celda en expansión que realiza un seguimiento de los valores mostrados anteriormente.

IF (SUBTOTAL (3, OFFSET (Table2 [First Name], MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1)), COUNTIF ($ B $ 25: B25, Table2 [Nombre]), "")

se convierte en

IF ({1; 1; 1; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 1; 1; 0; 0; ; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, "")

y devoluciones

{0; 0; 0; ""; ""; 0; ""; ""; ""; 0; ""; ""; ""; ""; 0; 0; ""; ""; 0; 0}

Paso 4 - Encontrar posición

La Función MATCH devuelve un número que representa la posición del primer 0 (cero) en la matriz.

MATCH (0, IF (SUBTOTAL (3, OFFSET (Table2 [First Name]), MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1)), COUNTIF ($ C $ 25: C25, Table2 [Nombre]), ""), 0)

se convierte en

COINCIDIR (0, {0; 0; 0; ""; ""; 0; ""; ""; ""; 0; ""; ""; ""; ""; 0; 0; ""; " "; 0; 0}, 0)

y devuelve 1.

Paso 5 - Valor de retorno

La Función ÍNDICE devuelve un valor basado en la referencia de celda y un número de fila y columna.

ÍNDICE (Table2 [Nombre], MATCH (0, IF (SUBTOTAL (3, OFFSET (Table2 [Nombre]), MATCH (ROW (Table2 [Nombre]), ROW (Table2 [Nombre])) - 1, 0 , 1)), COUNTIF ($ C $ 25: C25, Table2 [Nombre]), ""), 0))

se convierte en

ÍNDICE (Table2 [Nombre], 1)

y devuelve "Kaya" en la celda B26.

Función definida por el usuario

Función definida por el usuario en el rango de celdas A26: A31:

=FilterUniqueSortTable(Table2[First Name])

Esta fórmula también se ingresa como una fórmula matricial.

Sintaxis de funciones definidas por el usuario

FilterUniqueSortTable (rng)

Argumentos

Parámetro Texto
RNG Necesario. Una referencia de celda al rango en la Tabla definida por Excel de la que desea extraer valores distintos únicos.

Código VBA

'Name custom function and parameters
Function FilterUniqueSortTable(rng As Range)

'Declare variables and data types
Dim ucoll As New Collection, Value As Variant, temp() As Variant
Dim iRows As Single, i As Single

'Redimension array variable temp in order to be able to expand the variable later on
ReDim temp(0)

'Enable error handling
On Error Resume Next

'Iterate thorugh each value in cell range
For Each Value In rng

    'Check if cell is not empty and visible
    If Len(Value) > 0 And Value.EntireRow.Hidden = False Then
        
        'Add value to collection, this line will return an error if the value already exists in the collection
        ucoll.Add Value, CStr(Value)
    End If

'Continue with next value
Next Value

'Disable error handling
On Error GoTo 0

'Iterate through each value in collection
For Each Value In ucoll

    'Save value to array variable temp
    temp(UBound(temp)) = Value

    'Add another container to array variable temp
    ReDim Preserve temp(UBound(temp) + 1)

'Continue with next value
Next Value

'Remove last item from array variable temp
ReDim Preserve temp(UBound(temp) - 1)

'Count the number of rows the UDF is entered in by the user and save to variable iRows
iRows = Range(Application.Caller.Address).Rows.Count

'Use UDF SelectionSort to sort values from A to Z
SelectionSort temp

'Add items so the array variable temp has the same number of rows as the entered UDF and save blank values to items that contain nothing
For i = UBound(temp) To iRows
  ReDim Preserve temp(UBound(temp) + 1)
  temp(UBound(temp)) = ""
Next i

'Rearrange values in array variable temp and return them to worksheet
FilterUniqueSortTable = Application.Transpose(temp)
End Function

¿Dónde copiar el código?

Presione Alt + F11 para abrir VB Editor.

  1. Haga doble clic en su libro de trabajo en el Explorador de proyectos.
  2. Haga clic en "Insertar" en el menú y luego haga clic en "Módulo".
  3. Pegue el código anterior al módulo de código.
  4. Salga de VB Editor y vuelva a Excel.
Tenga en cuenta que asegúrese de guardar su archivo con la extensión * .xlsm (Libro habilitado para macros) para guardar la macro junto con el libro.