30. Valores únicos dentro de una matriz

VALORES UNICOS.xlsx

Descargar XLSX • 15KB

En el siguiente ejemplo, vamos a ver como podemos obtener los valores únicos dentro de una matriz, tenemos una matriz de datos, donde hay valores repetidos, y, algunos que no se repiten, queremos saber cuales son los nombres de personas de esos que no se repiten.

La matriz es la siguiente:

Vemos que hay dos nombres que no se repiten, pero imaginemos que, en vez de estos registros, tenemos 1000, lo cual sería difícil a simple vista localizarlos.

Una de las funciones que vamos a usar para este ejemplo, es CONTAR.SI, que ya sabemos todos a esta altura, para que vale, busca de una columna, el criterio que le especifiquemos, y, nos devuelve los valores que sea correctos.

En este caso, vamos a usar la función CONTAR.SI, para que me genere una matriz derramada.

En la celda C5, en la barra de fórmulas, voy a escribir lo siguiente, el signo igual, como primer argumento, que es rango, ponemos, desde A6 hasta A13, que es donde están los nombres.

Como segundo argumento, que es criterio, ponemos el mismo rango de datos, quedaría:

Aceptamos, y, tenemos nuestra matriz derramada.

Podemos observar, que Jaime se repite tres veces, Claudia se repite también tres veces, que Miguel se repite una vez, y, que Pedro también se repite una vez.

Lo que quiero conseguir, son los nombres de esas personas que solo aparecen una vez.

Si a la función CONTAR.SI, se añado al principio un condicional SI, de manera, que verifique la matriz, y, si algún valor es mayor de 1, ponga un texto en blanco, en caso contrario, que ponga el número 1.

La sintaxis seria:

=SI(CONTAR.SI(A6:A13;A6:A13)>1;"";1)

Aceptamos, y, como resultado:

Vemos, que ahora nos aparecen solo dos 1, que es exactamente los nombres que no se repiten.

Pero lo que quiero son los nombres, para ello, necesitaría la posición exacta de esos nombres que son únicos, entonces, modifico el condicional SI, y en el argumento valor_si_falso, ponemos lo siguiente:

=SI(CONTAR.SI(A6:A13;A6:A13)>1;"";FILA(A6:A13))

Es decir, que en vez que me devuelva 1, me devuelva la fila exacta que ocupa dentro de la hoja de cálculo.

Como resultado:

Es decir, que los nombres que no se repiten, se encuentran en la fila 12 y 13.

Lo siguiente es extraer dichos nombres, para ello, vamos a usar la función INDICE, si vuelvo a modificar la formula con el condicional SI, y, le añado al principio, la función INDICE, que lo que hace es ir a una matriz de datos, y le indicamos que fila y columna queremos extraer el valor.

La sintaxis quedaría:

=INDICE(A:A;SI(CONTAR.SI(A6:A13;A6:A13)>1;"";FILA(A6:A13)))

Le estamos diciendo que, en la columna A completa, traiga el valor de la fila 12 y fila 13.

El resultado:

Vemos que aparecen errores en aquellas filas donde no hay valores, para solventarlo, vamos a usar la función SI.ERROR, de la siguiente manera:

=SI.ERROR(INDICE(A:A;SI(CONTAR.SI(A6:A13;A6:A13)>1;"";FILA(A6:A13)));"")

Es decir, si se produce un error, me devuelves un texto en blanco, en caso contrario, ejecutas la formula, y, como resultado:

Ya no tenemos esos errores.

Lo siguiente que podemos hacer, es activar los filtros, para ello, nos situamos en la primera celda de la matriz derramada, y, pulsamos <CTRL> <SHIFT> >L>.

Ahora, desplegamos el filtro, y, desmarcamos en vacías.

El resultado:

Vemos que matriz original Nombres, también se contrae.

Bien, para que sea mas vistoso, podemos abrir una hoja de cálculo nueva, y, ahí es donde debe de aparecer los nombres que son únicos.

Para ello, volvemos a modificar la formula con el condicional SI, y, la dejamos como estaba, es decir, que nos muestre los números de filas que sean únicas.

=SI(CONTAR.SI(A6:A13;A6:A13)>1;"";FILA(A6:A13))

En una hoja nueva (hoja2), ponemos un encabezado, por ejemplo, nombres.

Usamos INDICE, para extraer los valores de la hoja1, hay que decir, que debemos de usar la función K.ESIMO.MENOR, para extraer los valores devueltos, de esa manera, la formula será dinámica, sino seria estática, ya que tendríamos que indicarle nosotros los números de filas, y, nunca cambiaran.

La función K.ESIMO.MENOR, que tiene dos argumentos, matriz, que es donde vamos a buscar, y, el argumento k, que es número menor para devolver, es decir, el primer valor menor, después el segundo, y así, sucesivamente.

El argumento K, será un número, si ponemos 1, quiere decir el primer numero menor, si ponemos 2, el segundo número menor, y, así sucesivamente, pero, ese numero debemos de introducirlo nosotros de manera manual, cosa que es algo engorrosa, por tanto, con la función SECUENCIA, podemos crear un contador, pero debemos de hacerlo solo con los valores que hay, es decir, tenemos dos filas, que son los nombres que son únicos, la fila 12 y la fila 13, quiere decir que debemos de hacer un contador solo con dos valores, ¿Cómo lo hacemos?, bien, si uso la función CONTAR, que solo cuenta los valores numéricos dentro de un rango de datos, uso la siguiente sintaxis:

=CONTAR(C:C)

Es decir, que me diga cuantos valores numéricos hay en la columna C, la columna C, es donde están los números de filas, en este caso, solo tenemos los números de filas de los valores únicos, es decir, dos, la fila 12 y 13.

El resultado será:

Si pongo de la función CONTAR, la función SECUENCIA, me generara un contador de dos números, de la siguiente manera:

=SECUENCIA(CONTAR(C:C))

Pues ya tenemos el argumento k de la función K.ESIMO.MENOR, solo nos queda usar la función INDICE en la hoja2, para extraer los valores.

La sintaxis para usar es:

El resultado:

Pero como he copiado la formula hacia abajo, y, solo teníamos dos valores, el resto de las celdas me devuelve error, para quitarlo, ponemos delante de la formula la función SI.ERROR, como sigue:

=SI.ERROR(INDICE(Hoja1!A:A;K.ESIMO.MENOR(Hoja1!C:C;Hoja1!D5));"")

Quiere decir que, si se produce un error, me ponga un texto en blanco, en caso contrario, que ejecuta la formula.

El resultado:

Pues ya tenemos conseguido nuestro objetivo.