Adaptando el Área de impresión según validación de celdas.

Hace unos días me reencontré con un viejo conocido y tras ponernos al día, vimos que teníamos en común nuestro gusto por Excel, y lógicamente intercambiamos conocimientos. Entre muchas de las potentes funcionalidades que me enseño, un par de ellas me llamaron especialmente la atención.
Y una de estas es la que expongo a continuación. Cómo cambiar el área de impresión de acuerdo a valores de unas celdas validadas.
Partamos de un ejemplo sencillo de un informe con cuatro secciones diferenciadas: Ingresos 2012 y 2013 y Gastos 2012 y 2013. La idea es simple, indicar qué queremos imprimir según las celdas validadas D1 y D2:

Además cada rango ha sido definido por un Nombre:
Gastos2012 =Hoja1!$A$4:$M$14
Gastos2013 =Hoja1!$O$4:$AA$14
Ingresos2012 =Hoja1!$A$17:$M$27
Ingresos2013 =Hoja1!$O$17:$AA$27

El paso siguiente es el importante, seleccionaremos o Estableceremos un área de impresión, por ejemplo el que corresponde a 'Gastos2012' rango Hoja1!$A$4:$M$14, y desde la ficha Diseño de página > grupo Configurar página > botón Área de impresión > Establecer área de impresión.
Si ahora entramos en el administrador de Nombres veremos un nombre generado como Área_de_impresión:

Último paso, editamos este nombre definido Área_de_impresión y en el campo 'Se refiere a:' introducimos la siguiente fórmula:
=INDIRECTO(Hoja1!$D$1&Hoja1!$D$2)
Estamos listos para probarlo.

Cambiaremos la vista de hoja a 'Salto de página' para visualizar cuál es nuestra área de impresión según seleccionemos las celdas D1 y D2:

Seguir leyendo...

Conoce Excel.

Excel es una de las mejores herramientas que tenemos a nuestra disposición, pero no olvidemos que es sólo eso, una herramienta, nuestra cabeza es quien dirije y controla. Por esto, cuanto mejor conozcamos las posibilidades de Excel, más y mejor rendimiento seremos capaces de extraer.
Si necesitas aprender con los mejores te interesa la edición de Cursos de Excel y Macros online con tutor personal de Noviembre de 2013.

Los cursos de Excel y Macros abiertos para este mes de noviembre son:

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Excel Financiero

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan hoy día 1 de novibre de 2013; y la matrícula estará abierta hasta el día 10.
Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

ALEATORIO y RND en intervalos discontinuos.

Pocos días a trás un lector me planteó esta cuestión relacionada con la manera de generar un número aleatorio que estuviera entre los valores de dos intervalos discontinuos:

...¿Se podran generar numeros aleatorios en una celda con diferente intervalos? Es decir, por ejemplo, generar un numero aleatorio entre el 1 y el 10 pero a la vez entre el 30 y 40...

Me interesó la cuestión por las consecuencias que arrastra emplear estras funciones 'aleatorias' en Excel, y es que no siempre lo generado es al 100% un valor aleatorio puro.

Para responder la pregunta planteada, lo primero que pensé fue en emplear la función ALEATORIO.ENTRE que nos devuelve un número entero aleatorio entre los números que especifique (recordemos que nuestro aleatorio cambia cada vez que se calcula la hoja de cálculo). Pero con esto sólo tenía cubierto el primer intervalo. Y el segundo?, como conseguiría de una 'manera aleatoria' llegar a valores del segundo intervalo?.
La idea surgió rapidamente, empleando la otra función ALEATORIO que nos ofrece un número real aleatorio mayor o igual a 0 y menor que 1, distribuido uniformemente.
Mi fórmula quedó entonces:
=SI(ALEATORIO()< 0,5;ALEATORIO.ENTRE(1;10);ALEATORIO.ENTRE(30;40))

La idea es clara, obtengo un aleatorio entre 0 y 1, si está por debajo de su mitad aplico un aleatorio del intervalo primero 1-10, en caso contrario del segundo intevalo 30-40.
O como panteó después el mismo lector:
=SI(Y(ALEATORIO.ENTRE(1;40)>11;ALEATORIO.ENTRE(1;40)<=19);ALEATORIO.ENTRE(1;10);ALEATORIO.ENTRE(30;40)) siguiendo la misma idea. En ambos casos tenía lo que quería, un valor cambiante entre 1 y 10 o 30 y 40.

La pregunta realmente es ¿es esto realmente un número al azar?, o ¿está condicionado a algo?. La duda se me planteó al recordar la función de VBA RND, puesto que ésta devuelve valores condicionados a los últimos valores. Podemos leer la documentación de Excel al respecto (que replico a continuación):
Comentarios:
La función Rnd devuelve un valor menor que 1 pero mayor o igual que cero.

El valor de número determina cómo la función Rnd genera un número aleatorio:

Para cualquier valor de semilla inicial se genera la misma secuencia de números. Esto es debido a que cada llamada sucesiva a la función Rnd usará el número anterior como valor de semilla para el siguiente número de la secuencia.

Antes de llamar a la función Rnd, utilice la instrucción Randomize sin argumento para inicializar el generador de números aleatorios con un valor de semilla basado en el reloj del sistema.

Nota: Para repetir secuencias de números aleatorios, llame a la función Rnd con un argumento negativo antes de utilizar la función Randomize con un argumento numérico. Al utilizar la instrucción Randomize con el mismo valor de número no se repite la secuencia anterior.

A saber, la clave está en esa definición de valor semilla:= Valor inicial usado para generar números pseudoaleatorios.
Pero también, al menos en VBA, en la instrucción Randomize que limpia la memoria y genera un número semilla nuevo que usa la función Rnd para crear secuencias de números pseudoaleatorios únicas.

¿Querrá decir esto entonces que en nuestra hoja de cálculo corremos el riesgo de obtener valores 'aleatorios' supeditados a algún valor semilla??, ¿existe en la hoja de cálculo alguna manera de liberar ese valor semilla, como hace Randomize en VBA??...
Hablemos entonces de valores pseudo-aleatorios, ya que por lo que parece, yo no he sido capaz de encontrar una respuesta a mis preguntas, la función ALEATORIO y ALEATORIO.ENTRE siguen algún tipo de algoritmo determinista para la producción de una serie de números, eso sí, que tienen muchas de las mismas propiedades de una serie de números aleatorios puros. Las secuencias pueden reflejar verdaderos números aleatorios en la distribución de frecuencias y la ausencia de patrones fácilmente discernibles; sin embargo, a diferencia de una secuencia pura, una secuencia pseudo-aleatoria es 100% predecible cuando se conocen su algoritmo y de las semillas (claro...).

Probablemente todo esto nos de igual, si lo que queremso es jugar un poco al azar, ya que mientras no concozcamos el algoritmo de Excel para estas funciones, nos valdrá el valor devuelto.. por que, para todos algo desconocido lo tomamos por suerte...

Si realmente queremos un número aleatorio lo más puro posible (pseudo-aleatorio en el mejor de los casos), al menos no condicionado a una semilla anterior, nos conformaremos con la función VBA RND junto a Randomize.

Por supuesto, cualquier informción al respecto de esta entrada, que aporte claridad al asunto, será muy bien recibida.

Seguir leyendo...

Propiedades de los datos externos: Ajustar el ancho de la columna.

Hoy veremos una propiedad muy concreta de las Tablas originadas al importar datos externos, se trata en concreto de la propiedad Ajustar el ancho de la columna.
Probablemente te haya pasado alguna vez (o muchas) que has construido una Tabla importando datos de un origen externo, y cada vez que actualizabas estos datos el trabajo de ajustar cada columna al ancho que deseabas, se veía desbaratado, autoajustándose al ancho determinado por los datos importados.
Podemos ver el efecto comentado en el siguiente video:

Es importante mencionar que dicha propiedad sólo está habilitada cuando se trata de un Tabla con un origen externo...
La manera de configurar esto es simplemente desde la barra de herramientas de Tabla > pestaña Diseño > grupo Datos externos de tabla > botón Propiedades, lo que abriría la siguiente ventana, desde donde marcaremos/desmarcaremos la opción indicada:

También es posible acceder a este opción desde la ficha Datos > grupo Conexiones > botón Propiedades.

El efecto tras desmarcar Ajustar el ancho de la columna es el esperado, tras la actualización de datos, el ancho de las columnas, permanecerá tal y como lo hubieramos definido.
Lo vemos en el video:

Seguir leyendo...

Una curiosidad: Calcular la edad media.

Un lector preguntaba por la manera de calcular la edad media para un listado de personas, lo que inicialmente podía parecer sencillo (y lo es) me hizo dar un par de vueltas a la forma de calcular esa media.
Al tratarse de fechas y cálculo de edades tiré de archivo y de la manera en cómo calcular la edad de un individuo (ver).

Veamos el planteamiento inicial:

Lo primero que pensé fue calcular la diferencia entre HOY() y cada una de las fechas, para luego aplicarle un PROMEDIO... pero el resultado sería la edad media en días (no viéndolo en años, meses y días...). Se me ocurrió entonces que si sabía los días transcurridos de media, podía calcular por diferencias desde hoy a qué día, mes y año atrás en el tiempo correspondía esa resta.

Desde aquí, con la fecha media de antigüedad era fácil saber/convertir cuántos días, meses y años habían transcurrido hasta el día de HOY(), bastaba aplicar la fórmula (ver):
=SIFECHA(D12;$D$2;"y")&" años, "&SIFECHA(D12;$D$2;"ym")&" meses y "&SIFECHA(D12;$D$2;"md")&" días"

Seguir leyendo...

Gráfico de Excel con los últimos datos incorporados.

A través de un comentario un lector preguntaba por la manera de generar un gráfico con los últimos valores introducidos en una tabla:

...ya encontré realizar un gráfico dinámico que se actualiza automáticamente; ahora necesito sólo graficar los últimos 3 (tres) datos de una tabla que incorpora una fila diariamente...

El trabajo consisitirá en recrear, mediante funciones, un rango variable que reconozca los últimos datos insertados en una Tabla. Las funciones empleadas son ya conocidas: INDIRECTO y DIRECCION. Esto incorporado dentro de un Nombre definido conseguirá el efecto deseado.

En otras ocasiones hemos podido añadir nuestros Nombres definidos como parte de los Valores de una Serie (ver), pero esta vez por la particularidad en la creación de nuestra fórmula no es posible, por lo que deberemos trabajar sobre un rango auxiliar de datos, sobre el cual construiremos nuestro gráfico.

Veamos en la siguiente imagen el detalle del ejercicio:

haz clic en la imagen

Lo que vemos en la imagen es en el rango de celdas A1:B6 una Tabla llamada 'Tabla1', y que además hemos generado dos Nombres definidos con fórmulas:
importes=INDIRECTO(DIRECCION(CONTARA(Tabla1[año])+1-2;2)&":"&DIRECCION(CONTARA(Tabla1[año])+1;2))
fechas=INDIRECTO(DIRECCION(CONTARA(Tabla1[año])+1-2;1)&":"&DIRECCION(CONTARA(Tabla1[año])+1;1))
Estos nombres construirán, de manera variable, el rango correspondiente a los últimos tres datos de la 'Tabla1'.
Si desglosamos ambas fórmulas, veríamos que con:
DIRECCION(CONTARA(Tabla1[año])+1-2;2)&":"&DIRECCION(CONTARA(Tabla1[año])+1;2)
en la situación actual obtendríamos: $B$4:$B$6; y es que jugamos con la función DIRECCION para conseguir una referencia de inicio y otra de fin de rango.
Lo bueno de emplear esta técnica es que hemos asociado el número de elementos en el campo 'año' de nuestra 'Tabla1' a la celda inicio y final del rango a graficar. Si nos detenemos un segundo en la función:
DIRECCION(CONTARA(Tabla1[año])+1-2;2)
vemos como con CONATARA obtengo la fila donde deso comience nuestro rango, fijando el número de columna a un 2, es decir, a la columna B.

El rango para los 'Años' se contruye exactamente igual, pero refiriéndonos a la columna 1 (esto es, columna A).

Una vez construidos los rangos 'fechas' y 'importes' los ejecutamos matricialmente en las celdas A18:A20 y B18:B20 respectivamente; esto lo haremos seleccionando previamente A18:A20 (o B18:B20), y escribiendo en la celda activa =fechas (ó =importes) y validándolo pulsando simultaneamente Ctrl+Mayusc+Enter.

El resto es simple, construimos un gráfico, del tipo que queramos, sobre ese rango A18:B20, que siempre será el mismo, puesto que sólo deseamos graficar los tres últimos datos de la Tabla.

En el video siguiente podemos ver cómo se comporta nuestro gráfico al añadir nuevos datos a la Tabla1:

Seguir leyendo...

Un gráfico en Excel de las masas patrimoniales contables.

En la entrada de hoy aprenderemos a representar en un gráfico las diferentes Masas patrimoniales que componen un balance de una empresa. Este es el típico gráfico que nos enseñaban en la universidad... pero sin embargo creo que puede ser un claro ejemplo de cómo tratar la información y construir un tipo de gráfico a partir de una serie de datos.
En algunos otros ejemplos de gráficos hemos aplicado esta misma técnica de dividir en diferentes series un original, la idea es siempre la misma, representar en un mismo gráfico diferentes condiciones.

En el ejemplo de hoy partiremos de un balance de una empresa resumido (claro está), reducido a sus componenetes esenciales:
ANC = Activos No Corrientes ó Activos fijos
AC = Activos Corrientes ó Activos Circulantes
PN = Patrimonio Neto o Recursos Propios
PNC = Pasivos No Corrientes ó Pasivos a largo plazo
PC = Pasivos Corrientes o Pasivos a corto
Dos conceptos adicionales son el de Capitales permanentes, suma del PN y PNC, y otro fundamental: El fondo de maniobra, calculado como:
FM = AC - PC
ó
FM = KP - ANC = (PN + PNC) - ANC

Vemos el balance de una empresa ficticia y el gráfico esperado final:

Comencemos dividiendo nuestra serie de datos única en tres:

Observamos como la primera serie (columna C) corresponde a los valores del Activo, la segunda (columna D) calculada por diferencias al Fondo de maniobra y la tercera y última (columna E) a los valores del Pasivo.
Merece especial atención el valor asignado a la celda D3, que será la 'base oculta' del Fondo de maniobra... es decir, será el punto de la segunda serie que suba el punto 'Fondo de maniobra' hasta su altura correcta.

A continuación seleccionaremos los datos a representar (celdas en amarillo):

Desde la ficha Insertar > grupo Gráficos > desplegable Columna > Columna apilada, construimos y vemos el siguiente gráfico....

Seguir leyendo...

VBA: el método .GetOpenFilename para seleccionar un fichero.

Hace ya algunos años aprendimos la manera de seleccionar una carpeta o directorio empleando en nuestras macros el Explorador de Windows (ver).
Hoy iremos un paso más lejos y no nos quedaremos en la carpeta, si no que seleccionaremos un fichero concreto, con su ruta completa (nombre incluido). Para ello emplearemos el método .GetOpenFilename, lo que no mostrará el cuadro de diálogo estándar Abrir, obteniendo el nombre de archivo que seleccionemos, pero lo más importante, sin que en realidad se abra ningún archivo!!!... lo que será cómodo para conseguir rutas completas.

En el ejemplo que propongo, conseguir dicha ruta mediante la selección directa en la ventana Abrir, y servirá precisamente para esto. En el código siguiente he modificado el Título de la ventana, además de aplicar un filtro de posibles tipos de ficheros a mostrar, dejando sólo visibles los ficheros .xlsx.

Insertamos un módulo en nuestro Proyecto de VB en el editor de VB, y en él la macro siguiente:

Sub SeleccionFichero()
'identificamos el fichero y su ruta para importarlo
Dim RutaArchivo As String

'controlamos algún posible error.
On Error Resume Next
'damos valor a la variable Filename
'además hemos cambiado el Título que aparecerá en la ventana diálogo
'y forzamos para que sólo abra ficheros de Excel (extensión .xlsx)
RutaArchivo = Application.GetOpenFilename(Title:="Prueba selección ficheros Excelforo", _
                            filefilter:="Excel files (*.xlsx), *.xlsx")

'si hemos seleccionado algún archivo muestra un cuadro mensaje
If Not RutaArchivo = "False" Then
    MsgBox RutaArchivo
End If

'con el método .Open abrimos el fichero seleccionado
'o cualquier otra acción que requiera la ruta de un fichero
Workbooks.Open FileName:=RutaArchivo
End Sub

Al ejecutar nuestra macro veremos la siguiente ventana:

Tras seleccionar un fichero cualquiera, se mostrará un Cuadro de mensaje de texto con la ruta y nombre completo del archivo seleccionado, y en mi caso, posteriormente se abrirá dicho archivo (ya que lo he forzado con Workbooks.Open).

Seguir leyendo...

Opción para reducir hasta ajustar.

Hoy veremos una opción muy especial. Se trata de la opción de Excel: Reducir hasta ajustar. Ésta nos permite autoajustar el contenido de una celda (texto o valor) al ancho de la columna dado, sea cual sea, modificando visualmente el tamaño de la fuente (sólo visualmente).
Veamos en la siguiente imagen un conjunto de celdas con diferentes valores (numéricos o de texto) que en algunos casos ocupan más espacio del determiando por el ancho de columna:

Ahora viene la magía de Excel, desde el Formato de celdas y su pestaña Alineación, buscaremos en la sección Control del texto, la opcíon Reducir hasta ajustar:

haz clic en la imagen

Tras seleccionar dicha opción, vemos el resultado:

Seguir leyendo...

Curso Tablas dinámicas online con tutor personal... y más.

Presento la edición de Cursos de Excel y Macros online con tutor personal de Octubre de 2013.

Los cursos de Excel y Macros abiertos para este mes de octubre a los mejores precios son:

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Excel Financiero

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan hoy día 1 de Octubre de 2013; y la matrícula estará abierta hasta el 10 de octubre..
Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...