martes, 27 de octubre de 2009

Cuadros de Texto con Datos Variables


" Necesitaría saber si es posible utilizar cuadros de texto vinculados a celdas. El motivo es que suelo escribir algunos textos explicativos de algunos resultados en celdas de la hoja utilizando la función CONCATENAR pero me gustaría que aparecieran en cuadros de texto al lado de dichos resultados".

Sí es posible. De hecho la forma es casi idéntica al anterior post "Datos dinámicos en los títulos de gráficos". Partimos del siguiente ejemplo:


Queremos que en la zona del rango F5:G9 aparezca un cuadro de texto indicando cuáles fueron las ventas totales de 2009 y la variación porcentual respecto al 2008.

1. Nos situamos en la celda, por ejemplo, I5 y escribimos la siguiente fórmula:

="Las ventas del 2009 ascendieron a "&TEXTO(SUMA(B5:E5);"#.###")&" lo que supuso una diferencia respecto al pasado año del "&TEXTO((SUMA(B5:E5)-SUMA(B4:E4))/SUMA(B4:E4);"0,00%")

Como puede comprobar estamos realizando cálculos de totales y de ratios de crecimiento que no tenemos en ninguna celda de la hoja. Evidentemente si tuviéramos estos cálculos la fórmula se simplificaría enormemente.

2. Seleccionamos el menú Ver/Barras de herramientas/Dibujo.
3. Dibujamos un cuadro de texto en el rango F5:G9
4. Seleccionamos el cuadro de texto y en la barra de fórmulas escribimos =
5. Tras el igual hacemos clic en la celda I5 y pulsamos Enter. El resultado será el siguiente:


Si modifica cualquier dato podrá comprobar que los resultados se actualizarán en el cuadro de texto. El único inconveniente es que sólo podremos realizar cambios de formato para todo el cuadro de texto en conjunto (no podemos entrar en él y seleccionar parte del contenido para darle formato específico).

miércoles, 21 de octubre de 2009

Datos Dinámicos en los Títulos de Gráficos


"Tengo un gráfico con las ventas de cada mes y me gustaría añadir como título del gráfico la cifra de ventas acumulada a final de año (pero no como texto fijo)".

Sin problema... Supongamos que partimos del siguiente ejemplo:


Y realizamos el siguiente gráfico de columnas (gráfico completamente básico al que simplemente le he cambiado los colores predeterminados y borrado el título y leyenda):


Lo que queremos conseguir es que por un lado aparezca un título fijo con el texto "Ventas Mensuales" y, por otro lado, que aparezca un título dinámico del tipo "Ventas Anuales Acumuladas" y a continuación la cifra en cuestión (lógicamente esta cifra debe ser dinámica, es decir, que si realizamos cualquier cambio en las ventas mensuales que también cambie dicho total). Para conseguir este objetivo debemos seguir los siguientes pasos:

1. Pulsamos el botón derecho del ratón encima del área de trazado del gráfico y seleccionamos Opciones de gráfico. En el apartado título del gráfico escribimos "Ventas Mensuales" y aceptamos.
2. Nos situamos en la celda B17 y escribimos la siguiente fórmula:

="Ventas Acumuladas: "&TEXTO(C15;"#.###")

3. Hacemos un clic encima del área de trazado de nuestro gráfico y a continuación escribimos un igual en nuestra barra de fórmulas y hacemos clic en B17. Pulsamos enter.

De esta manera aparecerá en el medio del gráfico el texto Ventas Acumuladas seguido de la suma total de nuestras ventas. Sólo nos resta colocar dicho cuadro en la parte del gráfico que deseemos:


Pruebe a modificar las cifra de ventas de cualquier mes y comprobará que la cifra total acumulada mostrada en el gráfico también cambiará.

miércoles, 7 de octubre de 2009

Cámara de Fotos en Excel



"Me gustaría saber si en excel existe la posibilidad de realizar fotos a celdas y/o rangos para manejar el resultado como un objeto (tipo imagen) pero que mantenga los vínculos correspondientes".

Efectivamente es posible. Lo que ocurre es que esta opción no se encuentra disponible en los menús y tenemos que proceder a personalizar alguna de nuestras barras para poder hacer uso de la Cámara de Fotos de excel.

1. Comenzamos yendo a Herramientas/Personalizar. En la pestaña Comandos seleccionamos la Categoría Herramientas. Dentro de esta categoría buscamos el Comando Cámara:


2. Hacemos clic encima de dicho comando y sin soltar el clic arrastramos hasta la posición deseada en la barra de herramientas. Soltamos el clic y entonces ya habremos incorporado dicho icono a nuestra barra:


Una vez incorporada la herramienta veamos cómo utilizarla. Supongamos que tenemos la siguiente entrada de datos en una hoja:


Y la siguiente salida de datos en otra hoja:


Cualquier cambio que realicemos en la entrada de datos es evidente que modificará el resultado. Si queremos ver en todo momento dicha cuenta sin necesidad de andar cambiando de hoja podemos utilizar la cámara para obtener una foto y colocarla al lado de la entrada de datos:

3. Seleccionamos el rango que nos interesa (en nuestro caso la salida de datos A3:B11). y pulsamos en el icono de la cámara (he seleccionado un rango pero podría ser una única celda).

4. Vamos a la hoja de entrada de datos y al lado del rango A2:B11 hacemos clic y arrastramos el ratón. Se comenzará a dibujar un cuadrado. Soltamos el clic y aparecerá la foto del resultado como imagen (y se activará la barra de herramientas de imagen). Aunque se trate de una imagen la ventaja de esta herramienta es que cualquier cambio que introduzcamos en la entrada de datos se actualizará y verá reflejado en dicha imagen.


martes, 6 de octubre de 2009

Distintos Colores en Valores de una Serie de Datos de un Gráfico


"Me interesaría dibujar un gráfico que muestre los valores de la serie y que dichos valores tomen distintos colores en función de que sean mayores o menores que cero".

La solución es bastante sencilla utilizando el formato personalizado de número. Lo que deseamos obtener es lo siguiente:


Como se puede observar en e gráfico, cuando los valores son positivos los muestra en color azul y en color rojo cuando éstos son negativos. Lo mismo ocurre con los valores del eje Y.

Para conseguir este resultado debemos seguir los siguientes pasos (comienzo mostrando los datos del ejemplo):


1. Seleccionamos el rango A2:B13 y pulsamos el icono de insertar gráfico. Seleccionamos el tipo que queramos utilizar (en nuestro ejemplo tipo lineas y subtipo lineas con marcador en cada valor) y pulsamos finalizar. El resultado será:


2. Hacemos doble clic encima de la serie de datos; seleccionamos la pestaña Rótulos de datos; activamos Valor y aceptamos.


3. Tras eliminar las lineas de división principales, cambiar el color de la linea y añadir título (cuestiones básicas que entiendo no necesitan ser explicadas) el gráfico quedaría así:


4. Hacemos doble clic encima de los valores de la serie.

5. Seleccionamos la pestaña Número, marcamos la opción Personalizada y dentro del cuadro Tipo escribimos el siguiente formato:

[Rojo][<0]-#.###;[azul][>0]#.###;Estándar


6. Tras aceptar obtendremos el resultado deseado (hemos realizado la misma operación en el formato de número del eje Y):


domingo, 4 de octubre de 2009

Suma entre Fechas



"Tengo una tabla de ingresos con sus fechas correspondientes y me gustaría poder realizar sumas entre dos períodos que yo establezca".

No problemo, amigo. Para resolver este problema vamos a utilizar las funciones SUMAR.SI y CONCATENAR (&). Además aplicaremos Formato Condicional para que resalte las fechas indicadas. Partimos del ejemplo que se muestra en la siguiente figura:


1. Seleccionamos el rango B6:C17 y vamos al menú Insertar/Nombres/Crear. En la ventana que se abre seleccionamos Nombres en la Fila Superior y aceptamos. De esta manera el rango B6:B17 se llamará Fecha y el rango C6:C17 pasará a llamarse Ingreso.

2. Nos situamos en la celda C1 y escribimos la siguiente fórmula:

=SUMAR.SI(Fecha;"<="&C4;Ingreso)-SUMAR.SI(Fecha;"<"&C3;Ingreso)

Hemos utilizado el operador <= y la función CONCATENAR (en su versión &) para indicarle que buscamos aquellas fechas menores o iguales que la detallada en la celda C4, que es la fecha final. En la segunda parte de la fórmula hacemos lo mismo pero con la fecha inicial C3 (y utilizando sólo el operador <>

De esta manera ya tendríamos solucionado el problema. Si además queremos que resalte las fechas consideradas en la operación tendremos que utilizar Formato condicional. A saber:

3. Seleccionamos el rango B6:C17 y vamos al menú Formato/Formato condicional. Lo formulamos de la siguiente manera:


El resultado final será el siguiente:



sábado, 3 de octubre de 2009

Autofiltros en Tablas Dinámicas


"Tengo una tabla dinámica sobre la que me gustaría aplicar filtros con los datos que obtengo. La opción Autofiltro se encuentra desactivada cuando me encuentro en la tabla ¿hay alguna manera de hacerlo?"

Efectivamente cuando generamos una tabla dinámica si nos encontramos dentro de ella la opción Datos/Filtro/Autofiltro se encuentra desactivada. Pero hay un pequeño truco para que esto funcione. Para activar los autrofiltros tendremos que colocarnos en cualquier celda colindante con la tabla. Si nos fijamos en el siguiente gráfico, la zona destacada en naranja son las celdas en las que podemos colocarnos:


Una vez nos situemos en cualquiera de estas celdas vamos a Datos/Filtro/Autofiltro (que ahora sí aparece disponible). El resultado será:


Como se puede observar, los distintos medios existentes en esta tabla (avión, carretera y tren) presentan el filtro correspondiente que podemos proceder a utilizar.