Imagen: 200dgr/Shutterstock

Hay varias formas de resaltar valores duplicados. El formato condicional es quizás el método más común; puede elegir un formato incorporado o usar una regla para personalizar el formato. Sin embargo, encontrará que la mayoría de los métodos funcionan directamente con los datos de origen. Crear una lista de valores duplicados separados de los datos de origen es un poco más difícil. Afortunadamente, gracias a la función de matriz dinámica FILTER() de Excel, crear una lista de valores duplicados es más fácil que antes. En este artículo, le mostraré cómo usar FILTER() para crear una lista de valores duplicados en dos columnas.

VER: 83 trucos de Excel que todo usuario debe dominar (TechRepublic)

Estoy usando Microsoft 365 en un sistema Windows 10 de 64 bits. (Le recomiendo que no actualice a Windows 11 hasta que se hayan resuelto todos los problemas, a menos que tenga una razón específica para hacerlo). FILTER() solo está disponible en Microsoft 365. Para su comodidad, puede descargar el archivo de demostración .xlsx .

Índice
  • Acerca de FILTER() en Excel
  • FILTER() sobre dos columnas para valores repetidos en Excel
  • Acerca de FILTER() en Excel

    Mostrar un conjunto filtrado en otra ubicación facilita la generación de informes y funciona especialmente bien en una configuración de tablero. Hasta la introducción de FILTER(), llevar un conjunto filtrado a otra ubicación requería un poco de esfuerzo y habilidad.

    FILTER() es una de las nuevas funciones de matriz dinámica en Microsoft 365. Admite lo que se denomina un rango de desbordamiento, que es el resultado de una fórmula de matriz dinámica que devuelve varios valores: su salida se desborda de la celda de entrada. En resumen, un rango de derrame es un rango de resultados calculados a partir de una función o expresión. Cuando selecciona una celda en un rango de desbordamiento, Excel resalta todo el rango con un borde azul. Siempre encontrará la fórmula en la celda superior de ese rango.

    Esta función utiliza la sintaxis

    FILTER(array, include, [if_empty])

    donde array identifica los datos de origen, include identifica el valor o los valores que desea ver en el conjunto de datos filtrado y la opción if_empty especifica el valor que se mostrará cuando el resultado sea un conjunto vacío.

    Como verá, FILTER() es extremadamente flexible.

    FILTER() sobre dos columnas para valores repetidos en Excel

    El objeto Tabla simple en Figura A duplica algunos nombres en cuatro columnas. Nuestra tarea es crear una lista única de valores repetidos en dos columnas. Comenzaremos con los trimestres 1 y 2. Con un vistazo rápido, podemos ver fácilmente que junio está en ambas columnas. En una hoja mucho más ocupada, no querrá depender de la vista.

    Figura A

    Primero veamos la sintaxis de esta tarea:
    =FILTER(column2,COUNTIF(column1,column2)>0)donde 1 y 2 indican las posiciones de las columnas de izquierda a derecha. La referencia de la primera columna2 identifica los datos de origen para la función FILTER(). COUNTIF() devuelve VERDADERO si un valor en la columna 1 también aparece en la columna 2.

    Ahora apliquemos esto a los dos primeros trimestres ingresando la siguiente función en G3:
    =FILTER(Table1[Qtr 2],COUNTIF(Table1[Qtr 1],Table1[Qtr 2])>0)

    Como puedes ver en Figura Besta expresión devuelve junio.

    Figura B

    Esta expresión devuelve cualquier valor repetido en Qtr 1 y Qtr 2.

    Dado que las referencias son relacionales, puede copiar la expresión en G3 a H3:I3 para obtener listas similares, como se muestra en Figura C. Específicamente, la expresión en H3 devuelve duplicados en los trimestres 2 y 3 y la expresión en I3 devuelve duplicados en los trimestres 3 y 4.

    Figura C

    Copie la expresión para crear listas similares de valores repetidos.

    Observe cómo difieren las tres expresiones:
    G3: =FILTER(Table1[Qtr 2],COUNTIF(Table1[Qtr 1],Table1[Qtr 2])>0)
    H3: =FILTER(Table1[Qtr 3],COUNTIF(Table1[Qtr 2],Table1[Qtr 3])>0)
    I3: =FILTER(Table1[Qtr 4],COUNTIF(Table1[Qtr 3],Table1[Qtr 4])>0)

    Cada referencia de columna se actualiza en una columna; la expresión en H3 evalúa los trimestres 2 y 3, y la expresión en I3 evalúa los trimestres 3 y 4. Ahora analicemos las cosas para que pueda ver cómo funciona todo esto usando la expresión en G3:

    =FILTER(Table1[Qtr 2],COUNTIF(Table1[Qtr 1],Table1[Qtr 2])>0)
    =FILTER(Table1[Qtr 2],{0;0;0;1})>0)
    =FILTER(Table1[Qtr 2],{FALSE,FALSE,FALSE,TRUE})
    =FILTER({"Susan";"Lilly";"Kate";"June"},{FALSE,FALSE,FALSE,TRUE})
    {"June"}

    COUNTIF() primero devuelve una matriz de 0 y 1, donde 1 indica un valor repetido y su posición: el cuarto valor en Qtr 2. Al agregar el componente> 0, esta matriz devuelve FALSO y VERDADERO, donde VERDADERO identifica el valor repetido en los datos de origen. Técnicamente, >0 no es necesario, pero es una forma sencilla de documentar su intención, lo que facilita mucho el mantenimiento posterior.

    La referencia de la matriz FILTER() devuelve los cuatro valores en Qrt 2: Susan, Lilly, Kate y June. El único valor que coincide con un valor VERDADERO es junio, por lo que la expresión devuelve junio.

    La expresión en H3 se evalúa como =FILTRO({"Bill";"John";"Susan";"Lilly"},{FALSO;FALSO;VERDADERO;VERDADERO}), devolviendo Susan y Lilly.

    La expresión I3 se evalúa como =FILTRO({“Jacob”;”Logan”;”Jordan”;”Bill”},{FALSO;FALSO;FALSO;VERDADERO}) devolviendo solo Bill.

    H3 es la única expresión que devuelve más de un valor y esta lista no está ordenada. Puede resolver este problema rápidamente agregando SORT() a la expresión en G3 como

    =SORT(FILTER(Table1[Qtr 2],COUNTIF(Table1[Qtr 1],Table1[Qtr 2])>0))

    luego copiándolo a H3:I3. La figura D muestra la lista ordenada en la columna H.

    Figura D

    Agregue SORT() para ordenar las listas resultantes.

    Cuando actualice los datos de origen, esta expresión devolverá listas ordenadas siempre que haya más de un valor. Esto solo es posible porque los datos de origen son un objeto Table. Si está trabajando con un rango de datos normal, las expresiones no se actualizarán.