Base de datos de imágenes en Excel

Excel nos permite manejar pequeñas bases de datos (y si hacemos esto es importante tener en cuenta las limitaciones de Excel en este tema). Sobre esto ya he escrito una serie de notas en el pasado.

Cada tanto veo consultas en los distintos foros sobre la posibilidad de manejar una base de datos con imágenes. La idea es, por ejemplo, tener un catálogo de partes o productos con sus respectivas imágenes en una hoja. En otra hoja tener la posibilidad de elegir un producto y que aparezca su imagen.
Si bien esto es posible y mostraremos en esta nota como hacerlo, me apresuro a aclarar que Excel no es la herramienta recomendable para este tipo de tareas.

Si a pesar de mis advertencias, han decidido crear un catálogo con imágenes en Excel, estos son los pasos a dar.

1 – Abrimos un cuaderno Excel en blanco. Cambiamos el nombre de la Hoja1 a "Base de datos" y el nombre de la Hoja2 a "Catalogo" (este paso no es indispensable).

2 – En la hoja "Base de datos" creamos nuestro catálogo de imágenes. En la columna A ponemos el número de catálogo, en la columna B la imagen del primer producto en nuestra lista y en la columna C la descripción del producto.






Como pueden ver he adaptado el tamaño de la imagen y el alto de la fila. Para este ejemplo he utilizado alguno de los productos de mi empresa (si alguno de ustedes se ocupa de irrigación, tuberías, aguas corrientes o gas, está invitado a visitar el sitio).

3 – Definimos un nombre que contenga la lista de números de catálogo, al que llamaremos "Lista". Para que este nombre sea dinámico usamos la siguiente fórmula:

=DESREF('Base de datos'!$A$2,0,0,CONTARA('Base de datos'!$A:$A)-1,1)

3 – Definimos otro nombre, "Imagen", con una fórmula que conecte el número de catálogo en la columna A con la imagen correspondiente en la columna B:

=DESREF('Base de datos'!$B$2,COINCIDIR(Catalogo!$A$3,Lista,0)-1,0,1,1)


4 – En la hoja "Catálogo" en la celda A3 creamos una lista desplegable con Validación de datos




5 – En la celda B3 copiamos la primer imagen de nuestro catalogo

Y ahora el truco importante: seleccionamos con un clic la imagen, activamos la barra de fórmulas con un solo clic y en ella escribimos "=Imagen" (sin las comillas).




6 - Para que aparezca la descripción correspondiente introducimos es fórmula en la celda C3

=DESREF('Base de datos'!A2,COINCIDIR(Catalogo!A3,Lista,0)-1,2)


Otra variante sería usar BUSCARV, para lo cual tendríamos primero que definir un nombre para el rango de datos, "Descripcion" que contiene esta fórmula

=DESREF('Base de datos'!$A$2,0,0,CONTARA('Base de datos'!$A:$A)-1,3)

y luego escribir la siguiente fórmula en la celda C3
=SI(ESBLANCO(A3),"",BUSCARV(A3,Descripcion,3,0))

Con esto terminamos nuestra tarea y disponemos de un catálogo dinámico de imágenes en Excel.
Como ven, hemos usado extensivamente la función DESREF en sus distintas variantes (referencia a celda y referencia a rango).

En esta nota hay un enlace para descargar una animación explicatoria de la técnica para construir el modelo


Categorías: Funciones&Formulas_, Manejo de Datos_, Varios_


Technorati Tags: Excel, Imagenes en Excel

Gráficos Excel con listas desplegables

Ya hemos visto que podemos usar controles en hojas de cálculo Excel.

Uno de los usos de estos controles es generar listas desplegables (combo boxes). Cuando de hojas de cálculo se trata, podemos usar la funcionalidad Validación de Datos para generar estas listas desplegables. Pero cómo hacemos para poner una lista desplegable en un gráfico de Excel?
La respuesta: con el control cuadro combinado de la barra de herramientas "Formularios"

Supongamos que tenemos esta lista de ventas por mes y por línea de productos



Nuestro objetivo es generar un gráfico como este:



Cada vez que elegimos un mes de la lista desplegable, los datos del gráfico se adaptan a nuestra elección.

Los pasos a seguir son los siguientes:

1 – Preparamos el rango A1:D2 para que contenga los datos del gráfico




en el rango B2:D2 escribimos esta fórmula

=INDICE($B$7:$D$18,$A$3,COINCIDIR(B$1,$B$6:$D$6,0))

y en la celda A2 escribimos esta: =INDICE(A7:A18,A3)

Por ahora las fórmulas dan un resultado #¡VALOR!, cosa que será corregida escribiendo en la celda A3 el número 1 (o cualquier número entre 1 y 12).

2 – Creamos un gráfico de columnas con el rango A1:D2

borramos la leyenda y le damos un color distinto a cada serie (línea de productos).

3 – Activamos la barra de herramientas "Formularios", y cliqueamos el control "cuadro combinado". Lo ubicamos dentro del gráfico y abrimos el menú de formato del control



4 – En la ventanilla "rango de entrada" señalamos el rango que contiene los meses en la tabla de datos (A7:A18); en la ventanilla "vincular con la celda" señalamos A3; en "líneas de unión verticales" escribimos 12. También podemos marcas "sombreado 3D" para obtener este efecto en el control.

Al apretar "aceptar" las fórmulas darán #¡VALOR!, ya que el valor de la celda vinculada (A3) pasa a ser 0. Todo lo que hay que hacer es elegir un mes en la lista desplegable y veremos los resultados en el gráfico.

Otro truco es cambiar el color de fuente de la celda A3 a blanco, para "hacerla desparecer". Otra variante, más elegante tal vez, es ubicar este argumento en una celda que se encuentra "debajo" del gráfico, por ejemplo H10.




Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel

Gráficos en Excel usando la función REPETIR

En la nota sobre diagramas Gantt con Excel, vimos que podíamos crear diagramas sin usar el menú Gráficos de Excel. En ese caso creamos el diagrama usando el menú Formato Condicional.

También podemos crear gráficos con fórmulas usando la función REPETIR.

La primera vez que vi esta técnica fue en el libro de John Walkenbach "Microsoft Excel 2000 Formulas". Buscando en la red encontré esta nota en JuiceAnalytics. Así que estas son las dos fuentes para esta entrada.

Supongamos esta tabla que presenta los gastos planeados y realizados en un año determinado

Podemos crear un diagrama de barras horizontales que representen el déficit o superávit de cada mes. Los pasos son los siguientes:
1 – creamos una tabla de tres columnas a la derecha de la tabla de datos




2 – En la columna F (déficit) introducimos la fórmula
=SI(D2<0,repetir("n",-redondear(d2*100,0)),"")>0,REPETIR("n",REDONDEAR(D2*100,0)),"")

Obtenemos este diagrama




3 – Para obtener los pequeños cuadrados usamos la fuente Windings. La columa F está alineada a la derecha y la columna G a la izquierda.

Podemos experimentar otras posibilidades en lugar de la letra "n" con la fuente Windings. Por ejemplo podemos usar la línea vertical (caracter 124), con fuente Arial en tamaño 10




Si queremos que las barras horizontales sean "sólidas" podemos cambiar el tamaño de la fuente a 8, abrir el menú de formato de celdas y poner la alineación vertical a "centrar"



Otra mejora sería usar formato condicional para colorear las barras de acuerdo al tamaño de la desviación. Por ejemplo, toda diferencia de más del 15% que aparezca en rojo si es del lado del déficit o en verde si es superávit.
Para eso establecemos dos condiciones en formato condicional




Hay que prestar atención a la referencia semi-relativa en la fórmula ($D2, columna fija, fila variable).




Si los números a representar son todos positivos (o negativos), la fórmula a usar se simplifica a =REPETIR(CARACTER(124),B2) en esta tabla de temperaturas promedio



En lugar de CARACTER(124) se puede tipear directamente el caracter (por lo general a la derecha del teclado, al lado de Enter).

Otra variante es usar espacios en blanco con un carácter al final. Con esta fórmula
=REPETIR(" ",B2)&"X" se obtiene este diagrama




También podemos agregar el valor representado al lado de la barra, con esta fórmula
=REPETIR(CARACTER(124),B2)&" "&B2




Hay que señalar que REPETIR siempre da como resultado, lógicamente, un número entero de repeticiones.



Categorías: Funciones&Formulas_, Graficos_

Technorati Tags: Excel, Diagramas en Excel