domingo, 29 de abril de 2012

Elementos distintos y repetidos en una lista

Descargar el fichero: aeropuertos.xlsx

Deseamos determinar cuantos elementos se encuentran repetidos en un listado, y deseamos saber quienes son los repetidos.

Hoja 1

Disponemos de una serie de ciudades y deseamos conocer cuantos elementos hay sin repetición. Para ello utilizamos la fórmula matricial que se encuentra en la celda D14 y cuya explicación se encuentra paso a paso en las columna C, y D.

La fórmula matricial de la celda D14 es la siguiente:

=SUMA(1/CONTAR.SI(B4:B11;B4:B11))



Hoja 2

En este caso vamos a entender que un elemento está o no repetido según dos diferentes criterios. El estudio se puede entender de dos formas. La primera considera el conjunto total de datos y establece quienes están repetidos una o más veces. El segundo criterio analiza los datos por orden y únicamente considera que el dato está repetido si ya apareció anteriormente.

Partimos de un listado de AENA con los aeropuertos españoles y sus códigos de tres caracteres. Por ejemplo, el código de Barcelona es BCN.

En la columna K establecemos una serie de números aleatorios entre 0 y 1, que nos servirán para luego elegir los aeropuertos que deseamos analizar en otra tabla.

La fórmula de la celda K4 es:

=1/42+K3



En la columna C generamos de forma aleatoria los códigos de los aeropuertos. Generamos 24 códigos a elegir entre los 42 disponibles. Estos códigos al ser aleatorios podrán salir repetidos. Esto se consigue con la fórmula siguiente:

=BUSCARV(ALEATORIO();valores;2)

Donde 'valores' es el rango K3:L44

Método 1

En la columna D determinamos si el valor de la columna C que estamos analizando se encuentra repetido o no respecto a todos los valores de la propia columna C. Esto se hace en la celda D12 con la siguiente expresión.

=SI(CONTAR.SI(C:C;C12)>1;"repe";"")

Método 2

En la columna E vamos a determinar si un valor se encuentra repetido únicamente respecto a los que han aparecido antes que él, dentro de la propia columna C.

Este es el motivo de que en E12 siempre ponga 'nuevo' por definición, ya que es el primero.

La fórmula de la celda E13 es la siguiente:

=SI(C13="";"";SI(ESERROR(BUSCARV(C13;C$12:C12;1;FALSO));"nuevo";"repetido"))

Es importante observar que el rango que se está analizando es el rango C$12:C12 que se corresponde con los valores previos, y se ha de marcar con un dolar la primera referencia para dejarla fija y sin dólares la segunda referencia para dejarla libre.


Cata de datos en Excel

Descargar el fichero: cata.xlsm

Una cata de vinos nos permite probar y elegir el vino que sea de nuestro agrado. Lo mismo sucede con una cata de quesos, o bien si realizamos una cata de un melón. En este caso nos hemos permitido utilizar el término 'cata' para aplicarla a unos datos que tenemos y elegir de entre ellos un número dado, que deseamos seleccionar de forma aleatoria.

Disponemos de una serie de 20 datos, en este caso códigos de identificación de productos, y de entre ellos deseamos elegir 5 de forma aleatoria, al azar.

Hoja 1

En la columna B generamos números aleatorios con la función =ALEATORIO().
En la columna C generamos códigos inventados con la función:

=ELEGIR(ALEATORIO.ENTRE(1;23);"A";"B";"C";"D";"E";"F";"G";"H";"J";"K";"L";"M";"N";"P";"Q";"R";"S";"T";"V";"W";"X";"Y";"Z")&ALEATORIO.ENTRE(1000;9999)

Dotamos los elementos de la columna C con un Formato Condicional que tiene la siguiente expresión para la celda C4:

=B4<=K.ESIMO.MENOR($B$4:$B$23;5)

La columna B se ha utilizado como columna auxiliar para dar carácter aleatorio a las selecciones que luego hacemos. Se utiliza la función K.ESIMO.MENOR para elegir los 5 que tienen un número aleatorio menor. También se podría haber utilizado la función K.ESIMO.MAYOR y funcionaría exactamente igual.




Hoja 2


En la Hoja2 tenemos una variante de la anterior. La columna C contiene códigos fijos que no varían y hemos preferido elegir los códigos y mostrarlos en la columna F. La columna E es necesaria para poner los números hasta el máximo que deseamos extraer, que en este caso son 5.

La fórmula de F4 es:

=INDICE($B$4:$C$23;COINCIDIR(K.ESIMO.MENOR($B$4:$B$23;E4);$B$4:$B$23;0);2)

El color que se da a los valores elegidos de la columna C en esta ocasión se realiza con Formato Condicional pero con otra expresión que se basa en determinar si ya existen en la columna F.

La fórmula del Formato Condicional de la celda C4 es el siguiente:

=CONTAR.SI($F$4:$F$8;C4)>0



viernes, 20 de abril de 2012

Incorpora datos

Descargar el fichero: incorpora.xlsm

Incorpore datos en Excel a una columna, indicando la fecha y hora de incorporación del dato. Esto se puede conseguir con una pequeña macro. La idea es disponer de un botón que esté asociado a una macro que cuando lo pulsemos incorpore una celda calculada y la fecha y hora del sistema a una tabla, de forma que se vaya guardando un historial.

Supongamos que en un proceso industrial usted debe incorporar el promedio de 5 pesadas (celda verde) y la fecha y hora actuales (celda amarilla) a una tabla (columnas E y F) donde desea guardar un historial. Cada vez que pulse el botón denonimado "Incorpora Dato" ambas celdas pasaran a formar parte del historial.



La macro que consigue esto es la siguiente.