Asignar % Aleatorio que Sume 100%

"Necesito generar números aleatorios en 5 columnas y que la suma de cada fila resulte siempre el 100%".

Esta pregunta es bastante recurrente así que, aunque la solución no tiene mucho misterio, paso a resolverla. Empezamos planteando la tabla:

Nos situamos en la celda C13, seleccionamos el rango C13:G20 e introducimos la fórmula para generar los números aleatorios, esto es:

=ALEATORIO( ) acabamos pulsando Ctrl + Enter para generar de una sola vez la matriz:

Nos ponemos en la celda C3 y escribimos la fórmula:

=C13/SUMA($C13:$G13)

Copiamos esta fórmula hasta G3 y finalmente hasta G20 y... problema resuelto:

Asignación Aleatoria por Filas

Descargar Archivo

"Quiero realizar la asignación aleatoria de un número de anuncios determinado de distintas empresas en una parrilla dispuesta por bloques como aparece en la imagen:

De tal manera que la suma de los 5 bloques de cada anunciante debe resultar el número de anuncios contratados por cada empresa".

Pues nos ponemos manos a la obra. Empezamos creando la correspondiente tabla en nuestra hoja, a saber:

A continuación seleccionamos el rango E17:I23 y con dicho rango seleccionado escribimos la fórmula:

=ALEATORIO() y pulsamos Ctrl + Enter (así rellenamos todo el rango de una sola vez):

Nos situamos ahora en E5. Para que se entienda mejor vamos a realizar una primera fórmula que no es la definitiva. Escribimos (en E5) la fórmula:

=JERARQUIA(E17;$E17:$I17)

Copiamos dicha fórmula para toda la tabla (rango E5:I11). De esta manera conseguimos ordenar del 1 al 5 los resultados aleatoriamente obtenidos:

Ahora ya sólo nos queda aprovechar aquellos valores de orden que sean igual o inferiores al número de anuncios contratados por cada empresa. Por ejemplo, para el Balneario de Mondariz, que ha contratado 3, nos interesarán los valores 1, 2 y 3. A partir de aquí utilizaremos el formato condicional y la función SI para dar el formato final. Nos situamos nuevamente en la celda E5 y escribimos la fórmula:

=SI(JERARQUIA(E17;$E17:$I17)>$C5;"";1)

Nuevamente copiamos esta fórmula para el rango E5:I11. Al añadir el condicional si el número aleatorio es superior al número de anuncios, excel dejará la celda correspondiente al bloque en blanco. Pero si el número aleatorio obtenido es igual o inferior al número de anuncios contratados entonces escribirá un 1 en la celda del bloque correspondiente:

Ya sólo queda aplicar formato condicional para rematar la tarea. Seleccionamos el rango E5:I11. Dentro de la ficha Inicio vamos al módulo de Estilos y pulsamos el icono de Formato Condicional. De la lista que se abre seleccionamos nueva regla. En la ventana que se abre seleccionamos Utilice una fórmula que determine las celdas para aplicar formato (la última opción de la lista). Nos situamos en Dar formato a los valores donde esta fórmula sea verdadera y escribimos la siguiente fórmula: =1

Acabamos pulsando el Botón Formato. Ahora seleccionaremos el formato con el que queremos que excel resalte los bloques en los que sí hay anuncio. En nuestro ejemplo le pediremos relleno azul y color de fuente blanco y negrita. Aceptamos y...

Cada vez que se recalcule la hoja o que pulsemos la tecla F9 excel generará nuevos números aleatorios y, en consecuencia, un nuevo mapa como se puede ver a continuación en varios ejemplos:

Evidentemente también podemos modificar el número de anuncios contratados por cada empresa y el modelo seguirá funcionando correctamente (obviamente con un máximo, en este ejemplo, de 5 anuncios por empresa).

Resaltar Duplicados en Distintas Hojas

"Necesito resaltar texto duplicado entre varias hojas. Por ejemplo, tengo una lista de 80 nombres en la hoja 1 y en la hoja 2 otra lista de 80 nombres y quiero resaltar los nombres duplicados entre las dos hojas".

No problemo. Para solucionar este problema utilizaremos el Formato Condicional y la función CONTAR.SI. Partimos del siguiente ejemplo en donde nos encontramos una lista de 13 nombres en la hoja 1 y otra lista con 10 nombres en la hoja 2:

Los pasos que debemos seguir a continuación son los siguientes:

1. Seleccionamos en la hoja 1 el rango B3:B15 y dentro de la ficha Inicio vamos al módulo de Estilos y pulsamos el icono de Formato Condicional. De la lista que se abre seleccionamos nueva regla.

2. En la ventana que se abre seleccionamos Utilice una fórmula que determine las celdas para aplicar formato (la última opción de la lista):

3. Nos situamos en Dar formato a los valores donde esta fórmula sea verdadera y escribimos la siguiente fórmula:

=CONTAR.SI(Hoja2!$B$3:$B$12;B3)>0

4. Acabamos pulsando el Botón Formato. Ahora seleccionaremos el formato con el que queremos que resalte excel los nombres repetidos. En nuestro ejemplo le pediremos relleno violeta y color de fuente blanco y negrita. Aceptamos y problema resuelto:

Personalizando Fórmulas con Validación de Datos

"He leído el anterior artículo y me gustaría saber si hay forma de limitar la entrada de datos, por medio de la validación de datos, para conseguir que no permita introducir un código en la celda si no cumple la siguientes reglas: a)que el número total de caracteres sea de siete; b) que los dos primeros caracteres sean dos letras y estén escritos en mayúscula; c) que los siguientes caracteres sean 5 números."

Lo que queremos conseguir requiere del uso de la herramienta Validación de Datos por un lado y del uso de unas cuantas funciones para garantizar que se cumplen todas las restricciones indicadas por nuestro lector. En concreto, las restricciones que debe contemplar la fórmula que realicemos son:

1. Que el número de caracteres totales introducidos ha de ser 7

2. Que los dos primeros caracteres han de ser texto

3. Que los dos primeros caracteres han de ser mayúsculas

4. Que los caracteres del 3 al 7 han de ser números

Supongamos que tenemos una zona de introducción de datos como la mostrada en la siguiente imagen:

Lo que debemos hacer es seleccionar el rango B4:B12 que es nuestra zona de entrada de datos. Vamos a la ficha Datos/Validación/Validación de Datos y seleccionamos Permitir Personalizada. En Fórmula escribimos la siguiente (es un poco larga):

=Y(LARGO(B4)=7;IGUAL(B4;MAYUSC(B4));ESERROR(VALOR(EXTRAE(B4;1;1)));

ESERROR(VALOR(EXTRAE(B4;2;1)));

ESERROR(VALOR(EXTRAE(B4;3;5)))=FALSO)=VERDADERO

Voy a explicar las distintas funciones y partes de la fórmula. Empezamos con la función Y. Esta función nos sirve para comprobar si se cumplen una serie de pruebas que vamos a realizar. En caso de que se cumplan todas las pruebas que realicemos el valor que nos devolverá esta función será VERDADERO y en caso contrario FALSO (precisamente por este motivo podemos utilizarla dentro de validación de datos como fórmula personalizada).

La primera prueba que realizamos es la de el número total de dígitos. Para ello hacemos uso de la función LARGO, que nos devuelve el número de caracteres existentes en una celda, y comprobamos si es igual a 7.

La segunda comprobación que realizamos es si los dos primeros caracteres son mayúsculas. Para ello utilizamos la función IGUAL y la función MAYUSC. La función IGUAL comprueba si dos cadenas de texto son idénticas o no diferenciando entre mayúsculas y minúsculas.

Ahora comprobamos si los dos primeros caracteres son letras. Para ello debemos extraer dichos 2 caracteres para analizarlos. Hacemos uso de la función EXTRAE. Al utilizar esta función excel considera como texto los caracteres extraídos. En caso de que se trate de números será sencillo convertirlos nuevamente haciendo uso de la función VALOR. Finalmente utilizo la función ESERROR para comprobar que si tras convertir los dos primeros caracteres a número me devuelve un mensaje de error sólo puede ser debido a que se trate de texto. La misma operación pero al revés, es decir, comprobando que el valor devuelto es FALSO, resolverá la última parte de la fórmula donde verifico que los últimos 5 dígitos son números.

Una vez introducida esta fórmula en la validación de datos ya puede comprobar que el funcionamiento es el deseado y que sólo nos permitirá introducir valores correctos. Cada vez que cometamos un error nos aparecerá el mensaje que definamos dentro de la validación de datos, tal y como se muestra en los siguientes ejemplos:

Evitar Espacios Innecesarios al Introducir Textos

"Quisiera saber si hay alguna manera de evitar que por descuido se puedan introducir espacios al principio y al final de una introducción de texto en una celda de Excel y de igual forma impedir que se introduzca más de un espacio entre dos palabras".

Vamos a resolver este problema de una manera bien sencilla con dos funciones (ESPACIOS y LARGO) y una herramienta (Validación de datos). Partimos del siguiente ejemplo:

Lo que queremos conseguir es que si al introducir texto en las celdas de entrada (en este ejemplo rango B3:B7) introducimos espacios en blanco, ya sea al principio, al final o en el medio excel nos devuelva un mensaje de error y no nos permita continuar hasta que corrijamos dicho problema. Como se puede ver en la siguiente imagen, si introducimos espacios innecesarios en la entrada de texto, excel nos devuelve el mensaje de error correspondiente:

Para conseguir esto tenemos que seguir los siguientes pasos:

1. Seleccionamos el rango B3:B7

2. Vamos al menú Datos/Validación de datos

3. En Criterio de validación/Permitir seleccionamos la opción de Longitud de texto.

4. En Criterio de validación/Datos seleccionamos Igual a.

5. En Criterio de validación/Longitud escribimos la siguiente fórmula:

=LARGO(ESPACIOS(B3))

6. En la pestaña de Mensaje de error seleccionamos el Estilo Límite o Grave (según la versión) y personalizamos el mensaje que queremos que aparezca si cometemos un error.

Espacios(B3) quita todos los espacios del texto excepto los espacios entre palabras. La función Largo cuenta el número de caracteres que hay en una celda. Al anidar la función Espacios dentro de la función Largo LARGO(ESPACIOS(B3)) lo que estamos haciendo es calcular el número de caracteres que tiene una entrada de texto una vez hecha la limpieza de espacios en blanco innecesarios. Si dicho número coincide con la longitud del texto que introducimos en B3 entonces significará que no hay espacios en blanco y que, por lo tanto, la entrada es correcta. Si por el contrario el número de caracteres que tiene la entrada de texto difiere del calculado por la fórmula explicada entonces sólo puede ser debido a que existan espacios en blanco que deben ser corregidos.