lunes, 17 de noviembre de 2014

Especificar Tipo de Formato de una Celda

"Regularmente me envían un listado con diferentes entradas en una columna y necesito detectar, por medio de fórmulas, cuáles de dichas entradas son fechas".

Para solucionar este problema haremos uso de la función CELDA. Partimos del siguiente ejemplo:
La función CELDA devuelve información acerca del formato, la ubicación o el contenido de una celda. La sintaxis de esta función es CELDA( tipo_de_info;referencia), tal y como muestra la ayuda de excel, y tiene los siguientes argumentos:

tipo_de_info: Es un valor de texto que especifica el tipo de información de la celda que se desea obtener. La siguiente lista muestra los posibles valores del argumento de tipo_de_info y los correspondientes resultados:

tipo_de_infoDevuelve
"DIRECCION"la referencia, en forma de texto, de la primera celda del argumento ref.
"COLUMNA"El número de columna de la celda del argumento ref.
"COLOR"

Valor 1 si la celda tiene formato de color para los valores negativos; de lo contrario, devuelve 0 (cero).

"CONTENIDO"Valor de la celda superior izquierda de la referencia, no una fórmula.
"ARCHIVO"

Nombre del archivo (incluida la ruta de acceso completa) que contiene la referencia, en forma de texto. Devuelve texto vacío ("") si todavía no se ha guardado la hoja de cálculo que contiene la referencia.

"FORMATO"
Un valor de texto correspondiente al formato numérico de la celda. Los valores de texto para los distintos formatos se muestran en la siguiente tabla. Si la celda tiene formato de color para los números negativos, devuelve "-" al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve "()" al final del valor de texto.

"PARENTESIS"

Valor 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores; de lo contrario, devuelve 0 (cero).

"PREFIJO"
Un valor de texto que corresponde al "prefijo de rótulo" de la celda. Devuelve un apóstrofo (') si la celda contiene texto alineado a la izquierda, comillas (") si la celda contiene texto alineado a la derecha, un acento circunflejo (^) si el texto de la celda está centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío ("") si la celda contiene otro valor.

"PROTEGER"
Valor 0 (cero) si la celda no está bloqueada; de lo contrario, devuelve 1 si la celda está bloqueada.

"FILA"
El número de fila de la celda del argumento ref.

"TIPO"
Un valor de texto que corresponde al tipo de datos de la celda. Devolverá "b" (para blanco) si la celda está vacía, "r" (para rótulo) si la celda contiene una constante de texto y "v" (para valor) si la celda contiene otro valor.

"ANCHO"El ancho de columna de la celda redondeado a un entero. Cada unidad del ancho de columna es igual al ancho de un carácter en el tamaño de fuente predeterminado.

referencia: (argumento opcional) La celda sobre la que desea información. Si se omite, se devuelve la información especificada en el argumento tipo_de_info para la última celda cambiada. Si el argumento de referencia es un rango de celdas, la función CELDA devuelve la información sólo para la celda superior izquierda del rango.

He destacado en naranja "formato" porque es el tipo_de_info con el que vamos a trabajar. Para ello nos situamos por ejemplo en la celda E3 y escribimos la fórmula:

=CELDA("formato";B3)   y copiamos hasta E9:
Como se puede ver, en aquellas celdas que tenemos formato de fecha obtenemos la referencia D1. Haciendo uso de la ayuda de Excel, la siguiente lista describe los valores de texto que devuelve la función CELDA cuando el argumento tipo_de_info es "formato":

Si el formato de Excel es
La función CELDA devuelve
Estándar
"G"
0
"F0"
#.##0
".0"
0,00
"F2"
#.##0,00
".2"
$#,##0_);($#,##0)
"C0"
$#.##0;(rojo)-$#.##0
"-M0"
$#.##0,00_);($#.##0,00)
"C2"
$#.##0,00;(rojo)-$#.##0,00
"-M2"
0%
"P0"
0,00%
"P2"
0,00E+00
"C2"
# ?/? o # ??/??
"G"
d/m/aa o d/m/aa h:mm o dd/mm/aa
"D4"
d-mmm-aa o dd-mm-aa
"D1"
d-mmm
"D2"
mmm-aa
"D3"
mm/dd
"D5"
h:mm a.m./p.m.
"D7"
h:mm:ss a.m./p.m.
"D6"
h:mm
"D9"
h:mm:ss
"D8"

Como se puede comprobar, todos los formatos de fecha comienzan por la letra D. Por ello hacemos ahora la siguiente fórmula en la celda D3:
=SI(IZQUIERDA(E3;1)="D";"Sí";"No")   y copiamos hasta la celda D9:
Evidentemente, podríamos resolver el modelo con una única fórmula en D3 que nos evitaría la columna E, a saber:
=SI(IZQUIERDA(CELDA("formato";B3);1)="D";"Sí";"No")

IMPORTANTE: Si el argumento tipo_de_info de la función CELDA es "formato", como en nuestro caso, y procedemos a asignar un formato diferente al inicial a la celda a la que se hace referencia, es necesario volver a calcular la hoja de cálculo (o pulsar F9) para poder actualizar los resultados de dicha función.

Si queremos destacar en otro color aquellas entradas que son fechas entonces tenemos que hacer uso de la herramienta de Formato condicional. Para ello seleccionamos el rango B3:B9 y vamos a Formato condicional y formulamos como se detalla en la imagen a continuación:

jueves, 2 de octubre de 2014

Transponer con la Función DESREF

"Al importar información de la base de datos de mi empresa sobre clientes y sus direcciones, obtengo en una sola columna y en 4.000 filas los datos referentes a nombre, dirección, código postal y ciudad. Lo que necesito es reordenar esta información en cuatro columnas en la que la primera sea el nombre, la segunda la dirección, etcétera."

Para realizar esta tarea no podemos utilizar directamente la opción de Pegado especial transponer porque, evidentemente, nos transformaría la matriz actual de 1 columna y 4.000 filas en 4.000 columnas y 1 fila. Por ello vamos a hacer uso de la función DESREF para transponer la información pero como nos interesa. Para hacer más manejable el ejemplo partiremos de una lista con 20 filas, como se muestra a continuación:


 Lo que pretendemos conseguir es lo siguiente:


Empezamos contando el número de entradas que tenemos y que en nuestro ejemplo es de 20 filas (desde B3 hasta B22). Queremos generar una matriz (una tabla) con cuatro columnas. Así las cosas, si dividimos el número de filas de partida (20) por dicho número de columnas (4) obtendremos el número de filas resultantes, esto es, 5. Esto es importante porque lo primero que tenemos que hacer es generar una lista de números partiendo de 0 y creciendo de 4 en 4. El motivo es que la información original de cada campo dista entre si exactamente 4 filas. Es decir el cliente 1 está en B3 el 2 en B7, el 3 en B11, etcétera. Entre estas celdas hay 4 filas. Lo mismo nos pasará si tomamos los datos de la dirección, B4, B8, B12, etcétera.

Una vez escrito los rótulos en el rango E2:H2, nos situamos en D3 y escribimos 0. En D4 escribimos 4. Seleccionamos D3:D4 y copiamos hacia abajo rellenando la serie hasta 16 (nuestras 5 filas):

Nos situamos ahora en E3 y escribimos la fórmula:
=DESREF($B$3;$D3;0)
Esto significa que que partiendo de la celda B3 "me traiga" el valor que se encuentra el número de filas D3. Como en D3 tenemos el valor 0, nos devolverá el valor de la celda B3, ya que le estamos diciendo que se desplace 0 filas y 0 columnas (el cero de las columnas lo podríamos omitir ya que no nos vamos a desplazar de columna). Al copiar esta fórmula en E4 se transformará en =DESREF($B$3;$D4;0) lo que significa que partiendo de B3 debe desplazarse el número de filas que le indique D4, y que en nuestro ejemplo es el valor 4. Por lo tanto "nos traerá" el valor de la celda B7, que es la que se encuentra 4 filas por debajo de B3. Copiamos entonces la fórmula de E3 hasta la celda E7 y ya tendremos el primer campo resuelto:


Ahora podemos copiar la fórmula de E3 en F3. De esta manera quedará como:
=DESREF($B$3;$D3;0) y lo único que tenemos que hacer es cambiar la referencia de fila de $B$3 por $B$4 y copiar hacia abajo hasta F7. Haremos lo mismo en G3 cambiando la referencia también a $B$5 y, finalmente en H6 donde la referencia debe ser $B$6. En resumen:
En E3: =DESREF($B$3;$D3;0)
En F3: =DESREF($B$4;$D3;0)
En G3: =DESREF($B$5;$D3;0)
En H3: =DESREF($B$6;$D3;0)
Copiamos hacia abajo y obtenemos el resultado deseado:

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: