Editor Top
Hay varias formas de resaltar valores duplicados. El formato condicional podría ser el método más común; puede elegir un formato incorporado o usar una regla para personalizar el formato. Sin embargo, lo que encontrará es que la mayoría de los métodos funcionan directamente con los datos de origen. Crear una lista de valores duplicados que esté separada 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 consejos de Excel que todo usuario debe dominar (TechRepublic)
Uso Microsoft 365 en un sistema Windows 10 de 64 bits. (Le recomiendo que no actualice a Windows 11 hasta que se hayan solucionado todos los problemas, a menos que tenga un motivo específico para hacerlo). FILTER() está disponible solo en Microsoft 365. Para su comodidad, puede descargar la demostración .xlsx Archivo.
Acerca de FILTER() en Excel
Mostrar un conjunto filtrado en otra ubicación facilita la creación de informes y funciona especialmente bien en una configuración de tablero. Hasta que se introdujo FILTER(), obtener un conjunto filtrado en otra ubicación requería un poco de esfuerzo y habilidad.
FILTER() es una de las nuevas funciones de matriz dinámica de Microsoft 365. Admite lo que se conoce como rango de dispersión, que es el resultado de una fórmula de matriz dinámica que devuelve varios valores: su salida se extiende más allá de la celda de entrada. En resumen, un rango de derrame es un rango de resultados calculados de una función o expresión. Cuando selecciona cualquier celda en un rango de dispersión, 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 los valores que desea ver en el conjunto de datos filtrados y if_empty opcional especifica el valor que se mostrará cuando el resultado sea un conjunto vacío.
Como verá, FILTER() es extremadamente flexible.
FILTER () en dos columnas para valores repetidos en Excel
El objeto Table simple en Figura A duplica algunos nombres en cuatro columnas. Nuestra tarea es crear una sola lista de valores repetidos en dos columnas cualesquiera. 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, revisemos la sintaxis para esta tarea:=FILTER(column2,COUNTIF(column1,column2)>0)where 1 and 2 denote the column positions from left to right. The first column2 reference identifies the source data for the FILTER() function. COUNTIF() returns TRUE if any value in column1 also occurs in column2.
Now, let’s apply this to the first two quarters by entering the following function in 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 que se repita en Qtr 1 y Qtr 2.
Debido a 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, no se requiere >0, pero es una manera fácil de documentar su intención, lo que facilita mucho el mantenimiento posterior.
La referencia de matriz de FILTER() devuelve los cuatro valores en Qrt 2: Susan, Lilly, Kate y June. El único valor que corresponde a 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 esa lista no está ordenada. Puede solucionar esto rápidamente agregando SORT() a la expresión en G3 en la forma
=SORT(FILTER(Table1[Qtr 2],COUNTIF(Table1[Qtr 1],Table1[Qtr 2])>0))
y 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.
A medida que actualice los datos de origen, esta expresión devolverá listas ordenadas cada vez que haya más de un valor. Esto es posible solo porque los datos de origen son un objeto Table. Si está trabajando con un rango de datos normal, las expresiones no se actualizarán.
