jueves, 21 de agosto de 2014

Lista Desplegable "Autocompletable"

"Tengo listado de provincias y ciudades de España y me gustaría crear una lista desplegable que al teclear la primera letra me enseñara las provincias que comienzan por dicha letra".

No hay problema. Vamos a ver distintas opciones que nos ofrece excel para que al ingresar la primera letra (o más) de una lista desplegable nos autocomplete la búsqueda. Para ello partimos del siguiente ejemplo:

A continuación vamos a crear la lista desplegable. Para ello abrimos la ficha de programador y  hacemos clic en Insertar:
Dentro de las opciones de Controles ActiveX hacemos clic en la segunda, que es Cuadro Combinado (control de ActiveX):
Una vez seleccionado ya podemos dibujar en la hoja la lista desplegable:

Dentro de la ficha Programador hacemos clic en Modo diseño:

Ahora hacemos un clic encima del cuadro combinado que tenemos en la hoja:


Dentro de la ficha Programador, hacemos clic en Propiedades:

Se abrirá la ventana de propiedades. En la opción ListFillRange escribimos el rango en el que se encuentran nuestro listado de provincias, esto es, B3:B54.

En la opción LinkedCell escribimos la celda en la que queremos que aparezca el valor seleccionado de la lista. En nuestro caso utilizaremos D4:

Sólo nos queda manipular la opción MatchEntry. Tres son las opciones. Por defecto aparecerá seleccionado fmMatchEntryNone, lo que significa que no acepta entrada alguna. Otra opción es fmMatchEntryFirstLetter que, evidentemente, nos permitirá introducir la primera letra de nuestra búsqueda y que nos muestre la primera opción del listado disponible. Finalmente, podemos utilizar fmMatchEntryComplete que nos permitirá ir escribiendo el nombre de la provincia en cuestión y que excel lo autocomplete. Una vez seleccionada la opción deseada, cerramos el cuadro de propiedades, VOLVEMOS A PULSAR Modo diseño para desactivarlo y problema resuelto:


jueves, 3 de julio de 2014

Transformar una Matriz a Sistema de Numeración Binario

"Necesito transformar una matriz numérica en una matriz binaria (con valores 0 ó 1), es decir, que los valores que superen un cierto número se conviertan en uno y el resto en cero".

El pasado lunes 30 de junio les prometí a mis alumnos del Master in Management del IE Business School que les dedicaría el próximo post que publicase. Vaya pues por delante la dedicatoria y mi agradecimiento a una clase maravillosa!

Vamos a resolver el problema planteado con una sencilla fórmula utilizando la función lógica SI y acabando con un Ctrl + Enter. Partimos del siguiente ejemplo:

Se trata de una matriz de 8x10 (8 columnas y 10 filas) y lo que buscamos es transformar los números que aparecen en 1 y 0. Para ello necesitamos un criterio, es decir, un valor, por ejemplo, a partir del cuál los valores inferiores se conviertan en uno y, por contra, los valores superiores se conviertan en cero. Dicho criterio lo tenemos en la celda C2. Lo que hacemos a continuación es seleccionar una matriz de la misma dimensión, es decir, seleccionar un rango de 8 columnas por 10 filas. Lo hacemos en B19:I28

Con dicho rango seleccionado escribimos la fórmula:  =SI(B5<$C$2;1;0) y finalizamos pulsando Ctrl + Enter. De esta manera rellenamos de una sola vez toda la matriz resultante:

Si además queremos que, por ejemplo, los 1 se destaquen en negrita y cambie el color de fondo, podemos aplicar Formato condicional. Para ello dentro de la ficha Inicio seleccionamos Formato condicional. En el menú que se abre seleccionamos Resaltar reglas de celdas y, en el nuevo menú, Es igual a...  Aparecerá la siguiente ventana:

Escribimos un 1, dejamos el formato que aparece (si queremos aplicar cualquier otro abrimos la lista y marcamos Personalizado) y pulsamos Aceptar. El resultado será el deseado:

viernes, 16 de mayo de 2014

Parejas Aleatorias sin Repetición

"Tengo dos grupos de 10 personas y quiero hacer 10 parejas aletorias pero sin que se repita ninguna persona (Ejemplo: pareja 1: el 1 con el 12; pareja 2: el 7 con el 19, etcétera. No valdría el 1 con el 5; el 1 con el 7; etcétera)".

Vamos allá. Lo solucionaremos con dos funciones, a saber: ALEATORIO y JERARQUIA. Empezamos generando una tabla de 20 valores aleatorios en dos columnas de 10 cada una:
Seleccionamos el rango H3:I12 y, con el rango seleccionado, escribimos la fórmula =ALEATORIO()  y terminamos pulsando Ctrl + Enter. De esta manera rellenamos todo el rango de una sola vez:
Seguidamente, preparamos la tabla de las distintas parejas como, por ejemplo, se muestra a continuación:
 Nos situamos en la celda C3 y escribimos la fórmula:
=JERARQUIA(H3;$H$3:$H$12)  y copiamos hasta la celda C12. De esta manera hemos obtenido un número de manera aleatoria y sin repetición entre el 1 y 10.
En la celda D3 escribimos la fórmula:
=JERARQUIA(I3;$I$3:$I$12)+10  y copiamos hasta la celda D12. Hemos hecho lo mismo que en el caso anterior pero al sumarle 10 en la fórmula estamos obteniendo ahora un número de manera aleatoria entre el 11 y el 20.
Y problema resuelto. Cada vez que pulsemos F9 estaremos generando una nueva combinación. Como sugerencia se podría utilizar la función CONCATENAR (&) para presentar el resultado unido y con texto. La fórmula en F3 sería:
=JERARQUIA(H3;$H$3:$H$12)&" con "&JERARQUIA(I3;$I$3:$I$12)+10

miércoles, 7 de mayo de 2014

Cálculo de Combinaciones

" En mi trabajo tengo que calcular con bastante frecuencia el número de combinaciones posibles de un determinado número de elementos (15 normalmente) en grupos de distinto tamaño. ¿Hay alguna función de excel que lo calcule directamente? ".

Sí (y gracias que esta vez me lo habéis puesto facilito...). La función es COMBINAT, que calcula, precisamente, el número total de grupos posibles para un número determinado de elementos. Para evitar malentendidos es necesario aclarar que cuando hablamos de combinaciones el orden no importa. Si tengo, por ejemplo, que preparar un sandwich y dispongo de 4 ingredientes, queso, jamón, lechuga y huevo, y quiero saber cuántas combinaciones son posibles de 3 ingredientes entonces obtendré 4 combinaciones, a saber: [queso, jamón, lechuga]  [queso, jamón, huevo]  [queso, lechuga, huevo]  [jamón, lechuga, huevo]. 

Para calcular todas las combinaciones de 15 elementos en grupos de diversos tamaños preparamos la siguiente tabla:

Nos situamos en la celda C5 y escribimos la siguiente fórmula:
=COMBINAT($C$2;B5)   y copiamos esta fórmula hasta la celda C19. El primer argumento de esta función hace referencia al número total de elementos y el segundo es el tamaño, esto es, el número de elementos de cada combinación (15 elementos en grupos de 1; 15 elementos en grupos de 2, etcétera). El resultado obtenido es el siguiente:

miércoles, 23 de abril de 2014

Resaltar Duplicados Concatenados

"Necesito una fórmula que localice códigos duplicados en la columna B y, si los encuentra, que los coloree sólo si en la columna C los nombres coinciden también, pero no puedo añadir columnas adicionales en la hoja."

Necesitamos concatenar la columna B y la C para comprobar si hay entradas duplicadas y, en tal caso, resaltar dichas celdas pero sin utilizar columnas adicionales en la hoja. Para ello formularemos directamente en la herramienta de Formato Condicional. Lo solucionaremos con la ayuda de la función SUMAPRODUCTO. Empezaremos formulando en la hoja para que se entienda mejor y luego pasaremos dicha formulación a la herramienta. Partimos del ejemplo de la primera imagen y queremos conseguir el resultado de la segunda imagen:



Para ello nos situamos en la celda E3 y escribimos la siguiente fórmula que copiaremos hasta la celda E10: 




SUMAPRODUCTO es una función que suma el producto de dos rangos (rangos que deben tener la misma dimensión). Si la fórmula fuese =SUMAPRODUCTO(B3:B10;C3:C10) excel ejecutaría (B3*C3)+(B4*C4)+(B5*C5)... Al introducir un criterio en la función (en nuestro caso el criterio es que el primer rango sea =$B3 y que el segundo sea =$C3) , excel genera una matriz de resultados tipo VERDADERO/FALSO que al multiplicarlo por 1 se convierte en una matriz del tipo 1/0. De esta manera estamos consiguiendo valores 1 para el rango B3:B10 en aquellos casos en los que un código esté repetido y valores cero para los que no lo estén.Y lo mismo en el rango C3:C10. Al combinar ambos resultados obtendremos valores mayores de 1 para aquellas combinaciones repetidas. Para aplicar esta formulación directamente en la herramienta de Formato Condicional debemos especificar una condición que, nuevamente, genere un resultado tipo VERDADERO/FALSO. Es por ello que introducimos el >1 del final de la fórmula. El resultado es el siguiente:

Como no podemos utilizar columnas adicionales en la hoja, seleccionamos ahora el rango B3:C10 y vamos a Formato condicional. Elegimos la opción de introducir una fórmula y escribimos (o copiamos y pegamos directamente) nuestra fórmula. En el botón Formato... damos la apariencia de relleno de color que deseemos y aceptamos:


Evidentemente, procedemos a borrar la formulación realizada en la columna E. Para finalizar correctamente el modelo debemos considerar que si dejamos celdas en blanco excel las rellenará con el formato que le hayamos asignado:

  Para evitar ésto, ampliamos la fórmula de la siguiente manera:

viernes, 14 de marzo de 2014

Destacar Datos Repetidos más de n-veces

"En una lista de datos, los cuales se repiten varias veces, necesito resaltar aquellos datos que se repitan mas de dos veces, pero que las dos primeras veces que aparezcan no se marquen". 

Partimos del siguiente ejemplo:
 Y queremos conseguir lo siguiente:
Para ello nos seleccionamos el rango B5:B24. Vamos a Formato Condicional y elegimos "Utilice una fórmula que determine las celdas para aplicar formato". En "Editar una descripción de regla" escribimos la siguiente fórmula:
=CONTAR.SI($B$5:B5;B5)>$C$2
Pulsamos el botón Formato... y seleccionamos el aspecto que queremos que tomen las celdas a destacar. Acabamos pulsando Aceptar y trabajo terminado.

jueves, 20 de febrero de 2014

Rellenar Hacia Arriba Celdas en Blanco

"Necesito rellenar celdas en blanco con el contenido de los rótulos que aparecen al final de cada grupo, es decir, en vez de hacia abajo tiene que ser hacia arriba."

Partimos del siguiente ejemplo:
Queremos que en el rango B2:B4 aparezca el rótulo Zona Norte; en el rango B5:B10 Zona Sur, etcétera.
Para ello seleccionamos primeramente el rango B2:B17. Vamos a la ficha Inicio al grupo Modificar y seleccionamos el icono de los prismáticos. Dentro de éste seleccionamos Ir a Especial... y marcamos Celdas en Blanco:
Obtenemos el siguiente resultado:
Tenemos las celdas en blanco seleccionadas y B2 como celda activa. Ahora CON LA TECLA Ctrl PULSADA, hacemos clic en la primera celda superior a nuestro primer rótulo. En nuestro caso el primer rótulo lo tenemos en B4 y, por lo tanto, hacemos clic en B3 (insisto en que debemos hacer dicho clic CON LA TECLA Ctrl PULSADA). Obtenemos lo siguiente:
Ahora la celda activa es B3. Directamente, sin tocar nada más ni hacer clic en ningún sitio, escribimos la fórmula: =B4   porque es el primer rótulo que tenemos, y acabamos pulsando Ctrl + Enter