Témpora Excel

Cuadro de mando económico con Excel 2007-2010

2012-02-24T10:53:00.000-08:00

El cuadro de mando económico para pequeñas y medianas empresas "CME Pymes v. 5.0" se actualiza a la versión 5.3.

Después de un año de funcionamiento y habiendo sido implantado micropymes de todos los sectores (imprentas, industrias de aluminio y cerrajerías, agencias de viajes, comercializadoras-distribuidoras, etc.), el cuadro de mando basado en Excel y desarrollado por José Manuel Pomares (Témpora Consultores) para el control de costes y optimización de resultados, incorpora 2 nuevos informes para un mayor control por parte de gerentes, directores financieros y profesionales que desean tener un seguimiento más cercano de la gestión y menor incertidumbre en la toma de decisiones:

El primero de ellos, es un dashboard que recopila los KPIs económicos fundamentales de una micropyme:

Como se puede ver en la imagen anterior, las micropymes podrán tener en un sólo informe, el control de los ingresos y la producción, costes desde la perspectiva de "Coste fijo-Coste variable" o "Coste directo-Coste indirecto" tan sólo haciendo click en un selector....

... gastos e ingresos extraordinarios, estructura de costes con gráficos de barras apiladas....

.... y gráficos avanzados de velocímetro que contienen en su interior un minigráfico con la evolución del parámetro que se muestra. Con ello se consigue proporcionar en pocos centímetros cuadrados multitud de información sobre un mismo aspecto de la empresa: promedio, objetivo, grado de cumplimiento del objetivo, evolución y estacionalidad, tendencia, etc.:

Otro informe es el de análisis y comentarios, pudiendo albergar información literal (no gráfica) y explicaciones sobre la toma de decisiones que a lo largo del año se deriva de los análisis mensuales de la información económica. A este informe se tiene acceso desde cualquier otro informe mediante un botón.

Además, se han actualizado algunos gráficos, sustituyéndolos por otros más avanzados y haciéndolos más intuitivos:

Por supuesto, se mantienen las herramientas de análisis dinámico para evaluar distintos escenarios: Análisis "What if":

Ver video de la versión anterior del CME Pymes

Al igual que la anterior versión, el nuevo CME pymes v.5.3. tiene una rápida y ágil implantación en medianas y pequeñas empresas (menos de 2 días). Más info en: info@temporaconsultores.com .

José Manuel Pomares Medrano

Análisis dinámico con barras de desplazamiento en Excel

2012-02-09T16:13:00.000-08:00

A veces, es necesario saber el resultado que obtendríamos en un gráfico o en cualquier celda calculada, dependiendo de un valor que nosotros necesitamos variar de forma interactiva, dinámica e intuitiva. Esta necesidad suele surgir en planes de viabilidad, gestión de presupuestos y aplicaciones similares en las que la administración de varios escenarios es su razón de ser.

La utilidad de las "barras de desplazamiento" estriba en dar como resultado en una celda que nosotros elegimos, un valor que podemos variar mediante una barra deslizante o mediante los botones que hay en los extremos (como si fuera una barra de scroll de una pantalla). Utilizando esta herramienta, tenemos la ventaja de estudiar infinitos escenarios y además, hacerlo interactuando con la hoja de cálculo, de tal forma que si la celda que elegimos como valor-resultado de la barra de desplazamiento es una celda de la cual depende un gráfico (por ejemplo), conseguimos saber que ocurre en el gráfico con cada uno de los resultados que nosotros asignemos con la barra.

Al ser una herramienta avanzada, para poder utilizarla debemos tener activa la Ficha “Programador” de la cinta de opciones o “Ribbon”. Recordemos que Excel, por defecto en su instalación, sólo incorpora las fichas “Archivo”, “Inicio”, “Insertar”, “Diseño de página”, “Fórmulas”, “Datos”, etc…

Así pues, debemos seguir los siguientes pasos para incluir una barra de desplazamiento en nuestra hoja de cáculo:

Asegurarse que tenemos la ficha "Programador" activa. Podemos ver el artículo: La ficha "Programador" en Excel para saber cómo activarla.
Cuando esté activa la Ficha “Programador” , hacer click en ella y después en el botón “Insertar” del grupo “Controles”.

3. Después, podemos encontrar la herramienta de “barra de desplazamiento” aquí:

4. Al presionar, el cursor quedará preparado para “extender” una barra de desplazamiento en cualquier sitio de nuestra hoja de cálculo. Se debe hacer en diagonal y puede quedar formada de forma horizontal o vertical, según se extienda el cursor:

5. Por último, haciendo click con el botón derecho del ratón encima de la barra de desplazamiento obtenida en el paso anterior, obtenemos un cuadro como el de la siguiente imagen y debemos dar formato al control, poniendo los valores mínimo y máximo que queremos (el valor actual no es necesario), la tasa de incremento (aumentar de uno en uno, de dos en dos, de diez en diez, etc.), la tasa de incremento de cambio de página (lo que aumenta cuando hacemos click entre la barra del centro y los botones laterales) y por último la referencia a la celda donde queremos enlazar el resultado-salida de nuestra barra de desplazamiento:

Hay que recordar que en “Vincular con la celda” se pone la referencia a la celda que queremos controlar con la barra de desplazamiento:

Con todo esto, ya sólo queda aplicarle imaginación y pensar ¿dónde puede proporcionarme la barra de desplazamiento mayor utilidad para administrar distintos escenarios?

Dejamos aqui un archivo para descargar como ejemplo de utilización de las barras de desplazamiento:

José Manuel Pomares Medrano

¿Qué tipo de gráfico utilizar en nuestros informes? (4/4)

2012-02-04T00:55:00.000-08:00

Y por fin el último post de la serie de artículos: ¿Qúe tipo de gráfico utilizar en nuestros informes?

3. Composición

Cuando se trata de expresar como está compuesta una magnitud total respecto de sus partes, tenemos varias opciones, que a su vez contienen varias posibilidades:

Gráficos circulares o de tarta: son de un gran impacto visual, aunque de una utilidad mucho más dudosa.:

Sólo son adecuados cuando tenemos que representar entre dos y cinco o seis partes. Aunque es justo decir que existe una solución para este inconveniente: los subgráficos circulares:

Cuando una o varias de las partes son inferiores a un 5% aproximadamente, es difícil distinguir visualmente los "trozos de tarta" pequeños y las etiquetas de datos que proporcionan la información de porcentaje o valor.

Tienen un carácter totalmente estático y por tanto, con estos gráficos no podemos expresar evolución a lo largo del tiempo.

No pueden comparar distintas series dentro de un mismo gráfico, sino que se debería recurrir a un gráfico paralelo, a diferencia de los gráficos de columnas (por ejemplo), en los que se puede simultanear varias series juntas:

Es difícil dsitinguir con precisión la diferencia entre partes cuando son similares. En estos casos, los gráficos de columnas o barras son más eficientes y significativos:

Gráficos de anillos: de igual impacto visual que los gráficos circulares, con los mismos condicionantes y similares soluciones:

Gráficos de áreas con dos o más series apiladas: al igual que los gráficos de tarta o circulares, los gráficos de áreas muestran las diferentes partes que integran un total, pero tienen una diferencia a su favor, que es su capacidad de mostrar evolución en el tiempo y las variables que se dan entre las partes y el total:

Para el caso en el que los datos de las distintas series los tengamos en porcentajes que forman un 100% total, están los gráficos de áreas apiladas al 100 %:

Por último, comentar que los gráficos de áreas de una sola serie (es evidente que no se utilizan para mostrar la composición de un total y no deberían tener lugar en este artículo) se utilizan para mostrar de forma muy intuitiva las magnitudes "stock" como son:

Evolución de saldos de tesorería
Evolución de deudas
Evolución de existencias en almacenes
Evolución de inmovilizado

José Manuel Pomares Medrano

Los artículos más leídos del Blog de Témpora Excel

2012-01-03T07:48:00.000-08:00

Como mandan los cánones, primeros de año, resumen del anterior. Vamos con una selección de los post más visitados por vosotros desde que iniciamos este Blog allá por Agosto:

1. Gráficos de velocímetro en Excel (2)

2. La ventana de Excel

3. Administrar escenarios en Excel

4. Modelización de datos en Excel (2)

5. Gráficos de velocímetro en Excel

También queremos mencionar la serie ¿Qué tipo de gráfico utilizar en nuestros informes? (1, 2 y 3) que ha tenido muy buena acogida, con un gran número de visitas tan sólo en el mes de diciembre de 2011. En los próximos días, estará disponible ¿Qué tipo de gráfico utilizar en nuestros informes? 4/4.

Agradecemos a todos los lectores la excelente acogida del Blog y prometemos nuevos contenidos y más interesantes. También aprovechamos desde aquí para adelantar la noticia del lanzamiento de varias cursos sobre Excel a lo largo de 2012. Se realizará en varias ciudades de España y en cada una de ellas se realizará un ciclo de 4 jornadas que dotarán a los alumnos de una importante base en el manejo de Excel (primera jornada), así como una importante especialización en materias como "gráficos", "macros y automatización de tareas" y "tablas dinámicas" (jornadas 2, 3 y 4).

Iremos informando al respecto y ofreciendo más detalles en cuanto a ciudades y fechas.

Aquí podemos ver la portada del ciclo de 4 jornadas:

José Manuel Pomares Medrano

Reducir al máximo el tamaño de un archivo Excel 2007-10

2011-12-24T03:39:00.000-08:00

En esta ocasión ofrecemos con este post un truco.

Se trata de conseguir que un archivo de Excel 2007 ó 2010 tenga el mínimo tamaño posible, pero que siga siendo un archivo Excel con el que podemos trabajar directamente (no dejándolo en formato .zip o .rar). Esto es útil porque en ocasiones, tenemos que "batallar" con archivos Excel que contienen miles de registros, formatos, imágenes, código VBA, etc. y todo ello sobredimensiona el tamaño del archivo y lo convierte en incómodo de manejar, enviar por e-mail, etc.

Previamente debemos conocer que los nuevos archivos de Excel de formato XML, son "armarios" o "cabinets" (en inglés) que contienen otros archivos a su vez. Si conseguimos abrir esos "armarios" mediante un programa compresor/descompresor como winzip , winrar o The Extractor o mediante el propio Windows 7 (lo tiene integrado), podremos realizar una nueva compresión de los archivos que contiene en el interior. Veamos paso a paso como hacerlo, es muy fácil:

1) Cambiamos la extensión del archivo Excel ...

... a una extensión como .zip

Antes de cambiar el nombre, Excel avisa de que al cambiar la extensión del archivo podríamos dejarlo inutilizado, pero no debemos hacer caso y tenemos que "Aceptar". Por otro lado, tenemos que decir que, en el caso de que no veamos las extensiones de los archivos en nuestro Explorador, tenemos que desmarcar la siguiente casilla de las opciones de carpeta del Explorador de Archivos de Windows.:

2) Descomprimimos el archivo mediante nuestro descompresor y obtenemos los siguientes archivos:

... este es el contenido de un archivo con extensión .xlsx.

3) Seleccionamos los 4 archivos anteriores y los comprimimos nuevamente con nuestro compresor, volviendo a obtener:

... aunque vemos que esta vez tiene 1.717 Kb, es decir, 343 Kb menos que el original, lo que equivale a un 16,65 % de reducción del tamaño.

4) Sólo resta volver a cambiar el nombre al archivo como el original:

y comprobar que funciona perfectamente.

Por último, comentar que en el ejemplo el archivo era de extensión .xlsm (habilitado para contener macros), pero esta técnica, por supuesto, también funciona con la extensión .xlsx que es la "normal".

Otra opción

Tenemos que recordar (ver el artículo "Significado de las extensiones de archivos Excel") que existe un tipo de archivo Excel llamado "binario" cuya extensión es .xlsb, que tiene una estructura que optimiza el tamaño del archivo al máximo (hace de forma automática e interna lo que hemos explicado por pasos anteriormente en este artículo).

Es decir, si convertimos cualquier archivo de Excel a extensión .xlsb (mediante "Guardar como"), automáticamente obtendremos una optimización máxima del rendimiento y tamaño del archivo, pudiendo funcionar exactamente igual con él. Es una opción conveniente para archivos muy grandes que trabajan con miles de registros, muchas imágenes, etc.

José Manuel Pomares Medrano

¿Qué tipo de gráfico utilizar en nuestros informes? (3/4)

2011-12-17T10:33:00.000-08:00

Abordamos en este post el tercero de los temas propuestos para la serie de artículos: ¿Qúe tipo de gráfico utilizar en nuestros informes?

3. Relación entre datos

Si nos interesa estudiar como evoluciona la relación entre dos datos cuando los dos varían, podemos utilizar dos tipos de gráficos:

Dispersión
Burbujas.

Gráficos de dispersión:

Imaginemos que queremos ver la relación que existe entre el número de visitas que realiza un comercial en un varios períodos estudiados y las ventas conseguidas. Partiendo de una tabla como la siguiente...

... podemos conseguir un significativo gráfico de dispersión como el siguiente:

Puede ser una gran ayuda para la toma de decisiones, pues es posible apreciar como, al aumentar las visitas a clientes por encima de ocho, las ventas no consiguen aumentar en la misma proporción (línea azul de tendencia añadida). También se puede observar que menos de 5 visitas no logran obtener los mejores resultados, al estar por debajo de la línea. Podría decirse, en definitiva, que el número de visitas óptimo para el comercial son ocho.

Gráficos de burbujas:

A veces, son tres los tipos de datos que necesitamos relacionar. Puede ser el caso de necesitar conocer el comportamiento de las desviaciones en costes de una empresa, en función de:

El tipo de coste.
La importancia de la desviación respecto de la producción.
La cantidad de desviación en % respecto del año anterior.

Los datos se pueden obtener de una tabla similar a esta:

y el gráfico resultante sería:

Podemos observar que lo más preocupante para la empresa debe ser la desviación sufrida por los costes del personal productivo. No sólo es la desviación más importante (podemos ver en la tabla que es del orden del 407 %), sino que esta partida tiene un peso específico importante en la estructura de costes de la empresa en 2011 (31 % respecto de la producción).

Tenemos de esta forma, un ejemplo de cómo convertir los datos y la información en conocimiento a través de una original e intuitiva forma de gráficos orientados a la toma de decisiones de la gerencia o dirección de la empresa.

José Manuel Pomares Medrano

¿Qué tipo de gráfico utilizar en nuestros informes? (2/4)

2011-11-30T10:09:00.001-08:00

En este 2º artículo de la serie ¿Qué tipo de gráfico utilizar en nuestros informes? vamos a abordar los gráficos que representan cuántas veces se repite algo que tenemos categorizado o segmentado. Es decir, gráficos de distribución de frecuencias o "Histogramas".

Por ejemplo, un director comercial puede necesitar un gráfico que exprese cómo se distribuye la cantidad de camisas vendida en un período determinado, según la talla (categoría). Otro ejemplo puede ser la necesidad de tener un gráfico que represente la distribución de las unidades de coches vendidos, en función del modelo.

Una vez llegados aquí, nos damos cuenta de que los gráficos de distribución de frecuencias se utilizan para mostrar como se distribuyen entre varias categorías, las unidades vendidas, pero NO el valor monetario de esas unidades.

2. Distribución de datos

En Excel, un "Histograma" es un gráfico de columnas para el cual hemos cambiado "filas" por "columnas", desde la ficha "Diseño" de las Herramientas de gráficos:

El resultado es que las columnas quedan sin separación, además de que Excel asigna un color distinto a cada categoría:

... y en la leyenda del histograma aparecen los nombres y colores de cada categoría.

Este tipo de gráficos es una opción recomendable cuando las categorías son pocas. En caso contrario, el tipo de gráfico más adecuado es uno de línea suavizada:

... aunque también podríamos combinarlo si no son demasiadas categorías:

Para suavizar la línea, debemos activar la casilla:

.... que está dentro del cuadro "Formato de serie de datos" (al que podemos acceder seleccionando la opción "Dar formato a la serie de datos..." del menú contextual que aparece al pinchar con el botón derecho encima de la línea.

¿Qué tipo de gráfico utilizar en nuestros informes? (1/4)

2011-11-24T18:17:00.001-08:00

Elegir un tipo de gráfico u otro a la hora de confeccionar un informe en Excel, puede marcar la diferencia entre transmitir lo que queremos comunicar, o no.

A lo largo de los siguientes artículos, daremos un repaso a los principales (no todos) tipos de gráficos que hay, los clasificaremos por el tipo de utilidad que ofrecen y pondremos algún ejemplo de utilización de cada uno de ellos.

Vamos a clasificar las utilidades que ofrecen los gráficos. Podemos decir que un gráfico puede servir para:

Ver una comparación de datos.
Ver la distribución entre varias categorías
Ver la relación y afinidad que tienen los datos entre ellos
Ver la composición que forman los datos respecto de un total.

Comenzamos en este post por la primera de las cuatro opciones enumeradas.

Comparar datos:

Entre categorías distintas: por ejemplo, si queremos comparar las ventas conseguidas en distintas líneas de negocio, zonas geográficas, sucursales, etc. (categorías). Como las categorías no "crecen"a corto plazo en la empresa, se pueden utilizar gráficos de barras. Este tipo de gráficos resulta también muy intuitivo cuando queremos comparar la duración de eventos o proyectos. En el aspecto práctico, son convenientes cuando los nombres de las categorías son muy largos, pues Excel permite también ponerlos dentro de cada barra.

En el transcurso del tiempo, de forma indefinida:

Cuando cada dato que queremos comparar va sucediendo en el tiempo de forma indefinida y, por ejemplo, debemos detectar la estacionalidad, el tipo a utilizar sería un gráfico de columnas o de líneas.

Se pueden construir gráficos de líneas o columnas con varias categorías (no muchas: no deben ser más de 2, 3 ó 4), apareciendo las líneas o las columnas de forma paralela y resultando una doble utilidad o posible comparación:

Comparar un determinado dato con otros datos que hay a lo largo del tiempo
Comparar ese dato con el ocurrido en el mismo período, pero en otra categoría

Vemos que en el gráfico de la derecha aparece una "tercera" posibilidad de

comparación. Es decir, con este gráfico no sólo se expresa la evolución a lo largo

del tiempo de cada categoría y la comparación entre categorías en un determinado

mes, sino que también se compara la suma o acumulación de las categorías en un

mismo mes. Este gráfico sería útil para expresar la evolución mensual de los costes

directos (categoría roja), indirectos (categoría azul) y totales (categorías roja y azul

acumuladas).

Existe una posibilidad similar al gráfico de arriba a la derecha, pero para el caso en

el que los datos los tenemos en porcentajes que se complementan hasta el 100%.

Regularidad, excesos y defectos, cuando los datos no son comparables entre sí y lo importante es saber si se mantiene una regularidad o si alguna categoría destaca por exceso o por defecto respecto de una escala de valores, se pueden utilizar los gráficos radiales. Un ejemplo de utilización sería representar las notas o valoraciones que se hacen de una persona sobre aspectos distintos (productividad, trabajo en equipo, disciplina, puntualidad, etc.).

Comparación entre un objetivo o presupuesto y lo que realmente se ha cumplido: es el caso donde es idóneo utilizar gráficos velocímetros o de termómetro. Ejemplo de los primeros lo tenemos en el CME Pymes:

En el siguiente post, dedicaremos nuestros esfuerzos en entender y aprender los distintos tipos de gráficos que podemos utilizar para representar la distribución de datos entre distintas categorías.

José Manuel Pomares Medrano

Código VBA (condiciones con IF...THEN...ELSE))

2011-10-17T10:24:00.000-07:00

Presentamos una selección de ejemplos de código en VBA listo para ejecutar mediante cualquier macro o por medio de un evento de algún objeto (Excel, un libro, una hoja o un rango).

En este artículo, ofrecemos líneas de código ejecutable para realizar acciones con una hoja de Excel, siempre que se cumpla una condición. Concretamente veremos el grupo de instrucciones IF ... THEN...ELSE (Si.... entonces..... si no).

El texto en verde que a veces se puede ver entre el código, es el que debe personalizar el usuario, si procede.

IF....THEN

1) Comprobar que un número escrito en la celda activa es el requerido. Si se cumple, muestra un mensaje diciendo "El código es correcto". Si no se cumple, la macro termina y no hace nada.

Sub ComprobarCódigo()

If ActiveCell = "BF1234" Then
MsgBox ("El código es correcto")
End If

End Sub

IF....THEN...ELSE

2) Comprobar que un número escrito en una celda determinada es el requerido. Si se cumple, muestra un mensaje diciendo "El código es correcto". Si no se cumple, muestra un mensaje diciendo "El código no es correcto".

Sub ComprobarCódigo()

If Range("A1") = "BF1234" Then
MsgBox("El código es correcto")

Else

MsgBox("El código no es correcto")
End If

End Sub

José Manuel Pomares Medrano

Código VBA (acciones con rangos de Excel)

2011-10-17T09:41:00.000-07:00

Presentamos una selección de ejemplos de código en VBA listo para ejecutar mediante cualquier macro o por medio de un evento de algún objeto (Excel, un libro, una hoja o un rango).

En este artículo, ofrecemos líneas de código ejecutable para realizar acciones con rangos en Excel. Concretamente veremos formas de SELECCIONAR, BORRAR y ESCRIBIR EN CELDAS con VBA.

El texto en verde que a veces se puede ver entre el código, es el que debe personalizar el usuario, si procede.

SELECCIONAR RANGOS EN EXCEL CON VBA

1) Seleccionar una celda.

Sub SeleccionarCeldaA1()

Range("A1").Select

End Sub

2) Selecionar una celda que está 2 filas más arriba y una columna más a la derecha que la celda actualmente seleccionada.

Sub CambiarSeleccionCelda()

ActiveCell.Offset(-2, 1).Select

End Sub

3) Selecionar toda la columna A.

Sub SeleccionarColumnaA()

Range("A:A").Select

End Sub

4) Selecionar toda la fila 1.

Sub SeleccionarFila1()

Range("1:1").Select

End Sub

5) Selecionar rangos distintos.

Sub SeleccionarRangosDistintos()
Range("A1:A4,B5,C4:D4").Select

End Sub

EDICIÓN DE RANGOS EN EXCEL CON VBA

6) Borrar contenido de un rango.

Sub BorrarContenidoRango()
Selection.ClearContents

End Sub

7) Borrar fila seleccionada.

Sub BorrarFila()
Selection.EntireRow.Delete

End Sub

8) Borrar columna seleccionada.

Sub BorrarFila()
Selection.EntireColumn.Delete

End Sub

9) Introducir el valor 1000 en la celda A1 de la hoja activa (si lo que se introduce es texto, debe ir entre comillas y si lo que se introduce es un número seguido de un texto, deben ir separados por el símbolo &).

Sub IntroducirValor()
Range("A1") = 1000

End Sub

10) Introducir el valor 1000 en la celda A1 de la hoja2 (si lo que se introduce es texto, debe ir entre comillas y si lo que se introduce es un número seguido de un texto, deben ir separados por el símbolo &).

Sub IntroducirValor()
Sheets("Hoja2").Range("A1") = 1000

End Sub

José Manuel Pomares Medrano

Código VBA (acciones con una hoja de Excel)

2011-10-16T15:29:00.000-07:00

Presentamos una selección de ejemplos de código en VBA listo para ejecutar mediante cualquier macro o por medio de un evento de algún objeto (Excel, un libro, una hoja o un rango).

En este artículo, ofrecemos líneas de código ejecutable para realizar acciones con una hoja de Excel. Concretamente veremos formas de OCULTAR, MOSTRAR, PROTEGER, DESPROTEGER, SELECCIONAR, IMPRIMIR y OTRAS ACCIONES con VBA.

El texto en verde que a veces se puede ver entre el código, es el que debe personalizar el usuario, si procede.

OCULTAR / MOSTRAR HOJAS DE EXCEL CON VBA

1) Ocultar una hoja de Excel (para mostrarla, hay que cambiar "False" por "True".

Sub OcultarHoja1()

Sheets("Hoja1").Visible=False

End Sub

2) Ocultar totalmente una hoja de Excel (no se podrá mostrar con el menú contextual que emerge de las pestañas de hojas).

Sub OcultarHoja1Totalmente()

Sheets("Hoja1").Visible=xlVeryHidden

End Sub

PROTECCIÓN DE HOJAS DE EXCEL CON VBA

3) Proteger la hoja activa de Excel (no se podrán seleccionar las celdas bloqueadas).

Sub ProtegerHoja()

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End Sub

4) Desproteger una hoja de Excel.

Sub DesprotegerHoja()

ActiveSheet.Unprotect

End Sub

VARIAS ACCIONES CON HOJAS DE EXCEL CON VBA

5) Seleccionar una hoja de Excel (el nombre debe ser el que aparece en la pestaña de la hoja).

Sub SeleccionarHoja1()

Sheets("Hoja1").Select

End Sub

6) Cambiar el zoom de una hoja de Excel.

Sub Zoom120()

ActiveWindow.Zoom = 120

End Sub

7) Imprimir las hojas seleccionadas con la impresora y configuraciones por defecto.

Sub ImprimirHojaActiva()

ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True

End Sub

8) Ocultar la cuadrícula de la hoja activa (para mostrarlos, cambiar "False" por "True").

Sub OcultarCuadricula()

ActiveWindow.DisplayGridlines = False

End Sub

9) Ocultar los títulos de encabezamiento de filas y columnas (para mostrarlos, cambiar "False" por "True").

Sub OcultarTitulos()

ActiveWindow.DisplayHeadings = False

End Sub

José Manuel Pomares Medrano

Código VBA (acciones con un libro de Excel)

2011-10-16T09:26:00.000-07:00

Presentamos una selección de ejemplos de código en VBA listo para ejecutar mediante cualquier macro o por medio de un evento de algún objeto (Excel, un libro, una hoja o un rango).

En este artículo, ofrecemos líneas de código ejecutable para realizar acciones con un libro o archivo de Excel. Concretamente veremos distintas formas de CERRAR, ABRIR Y GUARDAR con VBA.

El texto en verde que a veces se puede ver entre el código, es el que debe personalizar el usuario, si procede.

CERRAR UN LIBRO DE EXCEL CON VBA

1) Cierra un libro de Excel llamado "Libro1.xlsx" (comprueba primero que está abierto). Si ha habido cambios en dicho libro, pregunta si se desea guardarlos.

Sub CerrarLibro1()

Dim TestWorkbook As Workbook

Set TestWorkbook = Nothing

On Error Resume Next

Set TestWorkbook = Workbooks("Libro1.xlsx")

On Error GoTo 0

If TestWorkbook Is Nothing Then

MsgBox "El archivo no estaba abierto"

Else

Workbooks("Libro1.xlsx").Close

End If

End Sub

2) Cierra el libro de Excel que esta activo en este momento (pregunta si guarda los cambios).

Sub CerrarLibroActivo()

ActiveWorkbook.Close
End sub

3) Cierra el libro de Excel que esta activo en este momento y guarda los cambios.

Sub CerrarYGuardar()

ActiveWorkbook.Close savechanges:=True

End Sub

ABRIR UN LIBRO DE EXCEL CON VBA

4) Abrir un libro de Excel llamado "Libro1.xlsx" que está en la misma carpeta o ruta que el libro en el que estamos trabajando.

Sub AbrirLibro1MismaRuta()

Dim TestWorkbook As Workbook

Set TestWorkbook = Nothing

On Error Resume Next

Set TestWorkbook = Workbooks("Libro1.xlsm")

On Error GoTo 0

If TestWorkbook Is Nothing Then

ruta = ActiveWorkbook.Path
Workbooks.Open Filename:=ruta & "\Libro1.xlsx"

Else

MsgBox "El archivo ya estaba abierto"

End If

End sub

5) Abre un libro de Excel llamado "Libro1.xlsx" que está en la ruta :
C:\Carpeta1\Carpeta2\Carpeta3

Sub AbrirLibro1RutaConcreta()

Dim TestWorkbook As Workbook

Set TestWorkbook = Nothing

On Error Resume Next

Set TestWorkbook = Workbooks("Libro1.xlsm")

On Error GoTo 0

If TestWorkbook Is Nothing Then

ruta = "C:\Carpeta1\Carpeta2\Carpeta3"
Workbooks.Open Filename:=ruta & "\Libro1.xlsx"

Else

MsgBox "El archivo ya estaba abierto"

End If

End sub

6) Abrir cualquier libro de Excel (se abre el cuadro de diálogo "Abrir archivo" para elegirlo).

Sub AbrirArchivo()
Application.Dialogs(xlDialogOpen).Show
End Sub

GUARDAR UN LIBRO DE EXCEL CON VBA

7) Guarda los cambios en el libro de Excel activo.

Sub GuardarArchivo()
ActiveWorkbook.Save
End sub

8) Guarda los cambios en el libro de Excel activo y abre el diálogo "Guardar como" para hacer una copia de seguridad en una ubicación a elegir.

Sub GuardarComo()
ActiveWorkbook.Save
Application.Dialogs(xlDialogSaveAs).Show
End sub

José Manuel Pomares Medrano

Código VBA (acciones con la aplicación Excel)

2011-10-15T18:47:00.000-07:00

Presentamos una selección de ejemplos de código en VBA listo para ejecutar mediante cualquier macro o por medio de un evento de algún objeto (Excel, un libro, una hoja o un rango).

En este artículo, ofrecemos líneas de código ejecutable para realizar acciones con la aplicación. Es decir, con el propio Excel. Concretamente veremos como modificar la APARIENCIA DE EXCEL y como SALIR DE EXCEL con VBA.

El texto en verde que a veces se puede ver entre el código, es el que debe personalizar el usuario, si procede.

MODIFICAR LA APARIENCIA DE EXCEL CON VBA

1) Evitar que la pantalla parpadee o muestre actividad miestras se ejecuta el código de un procedimiento o macro (camiar "False" por "True" para mostrar parpadeo).

Sub EvitarParpadeoPantalla()

Application.ScreenUpdating = False

End sub

2) Ocultar la barra de estado para ganar espacio útil de pantalla (camiar "False" por "True" para mostrarla).

Sub OcultarBarraEstado()

Application.DisplayStatusBar = False

End sub

3) Ocultar la barra de fórmulas para ganar espacio útil de pantalla (camiar "False" por "True" para mostrarla).

Sub OcultarBarraFormulas()

Application.DisplayFormulaBar = False

End sub

4) Ver en modo pantalla completa (camiar "True" por "False" para salir del modo Pantalla completa).

Sub PantallaCompleta()

Application.DisplayFullScreen = True

End sub

5) Maximizar Excel.

Sub Maximizar()

Application.WindowState = xlMaximized

End sub

6) Ocultar las pestañas de las hojas (camiar "False" por "True" para mostrarlas).

Sub Maximizar()

ActiveWindow.DisplayWorkbookTabs = False

End sub

7) Quitar el modo "Copiar-Pegar" (quitar el recuadro de puntos intermitentes que se queda en una celda cuando la copiamos al portapapeles y la tenemos dispuesta para pegar).

Sub QuitarModoCopiarPegar()

Application.CutCopyMode = False

End sub

SALIR DE EXCEL CON VBA

8) Salir de Excel (si ha habido cambios, pregunta si los guarda).

Sub SalirDeExcel()

Application.Quit

End sub

9) Salir de Excel guardando los cambios.

Sub SalirDeExcel()

ActiveWorkbook.Save
Application.Quit

End sub

José Manuel Pomares Medrano

Cálculo de la TIR con Excel

2011-10-15T14:15:00.000-07:00

¿Qué es la TIR?

TIR es el acrónimo de Tasa Interna de Retorno (en inglés IRR: Internal Rate o Return) y se refiere a la tasa de rentabilidad que obtenemos de una inversión, al recibir a cambio posteriormente una serie de importes a lo largo de determinados períodos.

El cálculo de la TIR se utiliza para saber si una inversión es aceptable o no (es aceptable si el interés que obtenemos como resultado, es mayor que el interés que obtendríamos si invirtiéramos en otro proyecto).

Puesto que la fórmula de cálculo del TIR puede llegar a ser muy complicada (ver fórmula del TIR), en la práctica real se hace imprescindible la utilización de aplicaciones informáticas que realicen el cálculo. En este caso, Excel tiene una función que calcula de forma sencilla la TIR.

La TIR en Excel

En Excel, la TIR se calcula con la función TIR

=TIR (valores;[estimar])

Valores: es el rango de celdas donde están los valores de los cuales se quiere calcular la TIR. Los valores deben estar en el orden correcto y debe haber al menos un valor positivo (si no es así, la función devuelve un error). Las inversiones y los flujos de rentabilidad negativa se ponen con signo negativo.
[Estimar]: es opcional y se refiere al valor que estimamos aproximado a la tasa que obtendremos. Excel utiliza una técnica iterativa para la que necesita un valor estimado para iniciarla. Si no se le proporciona, Excel supone un 10 % (0,1).

Uno de los casos más frecuentes de cálculo de la TIR es el representado por la siguiente imagen, en la que podemos ver que la inversión es el primero de los flujos (en negativo, pues es una salida de dinero) y el resto de celdas del rango son los flujos de rentabilidad generados a lo largo de 4 períodos más (se omite el argumento "estimar"):

En la imagen podemos ver como, con una inversión de 100.000 € y consiguiendo 4 flujos de rentabilidad (en 2.013, negativa), obtenemos una TIR del 2%. La interpretación de todo esto es que en 4 años, se ha obtenido una tasa de rentabilidad neta del 2%. Para decidir si es conveniente llevar a cabo el proyecto o la inversión, debemos asegurarnos de que el coste de oportunidad (tipo de interés que podemos conseguir de forma segura en una entidad financiera, por ejemplo) es inferior a dicho 2%. En caso de que la TIR sea inferior a lo que una entidad financiera nos puede remunerar, deberíamos invertir nuestro dinero en dicha entidad y no hacer la inversión.

José Manuel Pomares Medrano

Cálculo del VAN con Excel

2011-10-15T03:48:00.000-07:00

¿Qué es el VAN?

VAN es el acrónimo de Valor Actual Neto (en inglés NPV: Net Present Value) y se refiere a la rentabilidad neta que obtenemos de una inversión que nos genera unos rendimientos de forma períodica. En el cálculo del VAN se tiene en cuenta (restando de la rentabilidad) un tipo de interés considerado como el coste de oportunidad que supone no haber realizado la inversión de otra forma.

El cálculo del VAN se utiliza para saber si una inversión es conveniente o no (es conveniente si el resultado es mayor que 0).

Puesto que la fórmula de cálculo del VAN puede llegar a ser muy complicada (ver fórmula del VAN) dependiendo del nº de períodos en los que recibimos flujos de rentabilidad, en la práctica real se hace imprescindible la utilización de aplicaciones informáticas que realicen el cálculo. En este caso, Excel tiene una función que calcula de forma sencilla el VAN.

El VAN en Excel

En Excel, el VAN se calcula con la función VNA y restando posteriormente la inversión:

=VNA (tasa;valor1;[valor2]...) +(- Inversión)

Tasa: es el tipo de interés que consideramos como referencia o coste de oportunidad. Es decir, si nos planteamos invertir 100.000 € en una empresa y nuestro banco nos ofrece de forma segura un 4% de interés, nuestro coste de oportunidad es el 4%.
Valor1: es el rendimiento que obtenemos de nuestra inversión en el primero de los períodos (meses, años, etc.)
[Valor2]: es opcional y se refiere al rendimiento que obtenemos de nuestra inversión en el segundo de los períodos.
Inversión: es la cantidad que invertimos al principio.

Si nos fijamos, en la ayuda que tiene Excel sobre la función VNA no incluye la ultima resta de la inversión. Es decir, la función original es: =VNA (tasa;valor;[valor2]...) . El motivo es que VNA ofrece el resultado de actualizar los flujos futuros a una determinada tasa, pero NO considera a la inversión como uno de los flujos que haya que actualizar. Por lo tanto, el cálculo del VAN, en Excel se hace sumando la inversión (que estará con signo negativo) al resultado de la función VNA.

En la imagen podemos ver como, con una inversión de 100.000 € y consiguiendo 4 flujos de rentabilidad anuales de 35.000 €, obtenemos un VAN de 10.945,29 €. La interpretación de todo esto es que en 4 años, la inversión se ha amortizado y además se ha obtenido una rentabilidad neta de 10.945,29 €, incluso teniendo en cuenta el coste de oportunidad de tener el dinero en una cuenta bancaria al 10% de rentabilidad.

José Manuel Pomares Medrano

Referencias relativas, absolutas y mixtas en Excel

2011-10-15T00:47:00.000-07:00

Referencias relativas

Una referencia relativa a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", se ajusta automáticamente para hacer referencia a otras celdas.

Son las referencias más utilizadas y las que, en la mayoría de las veces, se obtienen por defecto en Excel al hacer click en una celda para hacer referencia a ella. Son del tipo A1.

Por ejemplo, si tenemos la expresión = A1*2 en la celda B1 y utilizamos "Autorrellenar" hacia abajo, las fórmulas que se obtienen van a justando el número de forma que en la celda B2 tendremos la fórmula =A2*2, en la celda B3 tendremos la fórmula =A3*2 y así sucesivamente.

Podemos ver en la siguiente imagen como se ajustan las fórmulas a medida que "arrastramos" con "Autorrellenar":

El resultado sería:

Referencias absolutas

Una referencia absoluta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", NO se ajusta y queda bloqueada haciendo referencia siempre a la misma celda.

Para hacer una referencia absoluta a una celda, se deben introducir los símbolos del dólar $ antes de la letra y antes del número de una referencia normal del tipo A1. Es decir, una referencia absoluta a la celda A1, sería: $A$1

Con ello, conseguimos que al copiar y pegar o bien al utilizar "Autorrellenar" siempre mantenemos la referncia a la misma celda:

El resultado es:

Referencias mixtas

Una vez aprendidos los conceptos de "referencia relativa" y "referencia absoluta", es fácil entender el concepto de referencia mixta. Una referencia mixta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", ajusta sólo la letra o sólo el número de la referencia, quedando bloqueado sólo el número o sólo la letra respectivamente.

Por lo tanto, si queremos bloquear la letra, debemos colocar el símbolo del dólar "$" antes de la letra y si queremos bloquear el número, debemos colocar el símbolo del dólar "$" antes del número.

Las referencias mixtas son útiles en multitud de ocasiones, especialmente cuando tenemos que utilizar "Autorrellenar" en una matriz de celdas (varias celdas x varias celdas), puesto que nos permite "arrastrar" una fórmula introducida en una esquina, hasta la celda opuesta en la matriz, para operar con los encabezados de columnas y filas rápidamente y de una sóla vez. Podemos ver en la siguiente imagen una referencia mixta preparada para "arrastrar":

Teniendo el siguiente efecto, una vez "arrastrada" la fórmula de la celda C3 hasta la celda E5:

Truco: cambio rápido del tipo de referencia con la tecla F4

Estando el cursor dentro de la fórmula, podemos convertir rápidamente el tipo de referencia mediante la tecla F4. Por ejemplo, la primera vez que presionamos dicha tecla de función, la referencia A1 se convierte en una referencia absoluta (por ejemplo $A$1), la segunda vez se convierte en una referencia mixta que bloquea sólo el número de fila (por ejemplo A$1), la tercera vez se convierte en una referencia mixta que bloquea sólo la letra de la columna (por ejemplo $A1), la cuarta vez se convierte de nuevo en referencia relativa.

José Manuel Pomares Medrano

La función SUMAR.SI.CONJUNTO en Excel

2011-10-14T21:30:00.000-07:00

La función SUMAR.SI.CONJUNTO se utiliza para sumar los valores numéricos de todas las celdas de un rango que cumplan con una o más condiciones. Tiene como mínimo 3 argumentos separados por punto y coma y encerrados entre paréntesis:

= SUMAR.SI.CONJUNTO (rango_suma;rango_criterio1;criterio1;[rango_criterio2];[criterio2])

Rango_suma: es el rango de celdas donde están los valores que queremos sumar.
Rango_criterio1: es el rango de celdas donde están los valores que queremos utilizar como primera condición.
Criterio1: es la primera condición que queremos poner para que una celda de rango_suma sea incluida en la suma.
[Rango_criterio2]: Es opcional y se refiere al rango de celdas donde están los valores que queremos utilizar como segunda condición.
[Criterio2]: Es opcional y se refiere a la segunda condición que queremos poner para que una celda de rango_suma sea incluida en la suma.

Es importante saber que:

Rango_suma y Rango_criteriox deben tener la misma cantidad de filas y columnas.

SÍ se incluirán en la suma los valores lógicos VERDADERO / FALSO (como uno o cero).
En los argumentos criteriox se pueden utilizar caracteres comodín ? y * para celdas que contengan información con formato de texto.

Ejemplos de utilización del caracter ?

"=Villa*" selecciona para sumar, todos los registros que en rango_criteriox , empiecen por Villa
"=*ez" selecciona para sumar, todos los registros que en rango_criteriox , haya apellidos terminados en ez
"=AXT899-?" selecciona para sumar, todos los registros que en rango_criteriox , haya códigos que empiezan por AXT899- , independientemente de cuál sea el último caracter.
">66" selecciona para sumar, todos los registros que en rango_criteriox , haya cantidades superiores a 66.

Hay que resaltar la necesidad (como se ha podido comprobar) de poner entre comillas las expresiones de los criterios, aunque estén referidas a números.

José Manuel Pomares Medrano

La función PROMEDIOA en Excel

2011-10-14T09:28:00.000-07:00

La función PROMEDIOA se utiliza para obtener la media aritmética de un rango de valores. Es muy sencilla y sólo tiene 2 argumentos separados por punto y coma y encerrados entre paréntesis:

= PROMEDIOA (rango;[rango2])

Rango: es el rango de celdas del que queremos obtener el promedio.
[Rango2]: Es opcional y se refiere a otro rango adicional que podemos incluir en el promedio, o bien valores que directamente escribimos y que queremos que sean incluidos en el promedio.

Es importante saber que SÍ se incluirán en el promedio las celdas vacías (como ceros), los textos (como ceros), valores lógicos VERDADERO / FALSO (como uno o cero) y las celdas con texto vacío ="" (como ceros).

Los errores incluidos en el rango de PROMEDIOA, provocarán un error como resultado de la función.

Las celdas vacías NO se incluyen en el rango de PROMEDIOA.

En el siguiente ejemplo (en el cual se ha omitido el 2º argumento), podemos ver como la celda que contiene la función PROMEDIOA, hace referencia a un rango en el que hay valores de texto, celdas vacías y valores numéricos, sin embargo, sólo cuenta los numéricos (incluyendo el cero) y los textos (tomándolos como ceros), pero no las celdas vacías:

José Manuel Pomares Medrano

La función PORMEDIO en Excel

2011-10-14T09:06:00.000-07:00

La función PROMEDIO se utiliza para obtener la media aritmética de un rango de valores. Es muy sencilla y sólo tiene 2 argumentos separados por punto y coma y encerrados entre paréntesis:

= PROMEDIO (rango;[rango2])

Rango: es el rango de celdas del que queremos obtener el promedio.
[Rango2]: Es opcional y se refiere a otro rango adicional que podemos incluir en el promedio, o bien valores que directamente escribimos y que queremos que sean incluidos en el promedio.

Es importante saber que NO se incluirán en el promedio las celdas vacías, los textos, valores lógicos (VERDADERO / FALSO), ni las celdas vacías o con texto vacío (="").

Los errores incluidos en el rango de PROMEDIO, provocarán un error como resultado de la función.

Las fechas SI se incluyen en el rango de PROMEDIO tomando el valor de su número de serie (todas las fechas tienen un número de serie asignado, empezando por el nº 1 que corresponde al 1 de Enero de 1900).

En el siguiente ejemplo (en el cual se ha omitido el 2º argumento), podemos ver como la celda que contiene la función PROMEDIO, hace referencia a un rango en el que hay valores de texto (el título), celdas vacías y valores numéricos, sin embargo, sólo cuenta los numéricos (incluyendo el cero), pero no las celdas vacías:

José Manuel Pomares Medrano

La función EXTRAE en Excel

2011-10-14T08:42:00.000-07:00

La función EXTRAE se utiliza para obtener un "trozo" o cadena de caracteres, de una palabra o texto. La función tiene 3 argumentos y la siguiente sintaxis:

= EXTRAE (texto;posición_inicial;núm_de_caracteres)

Texto: es un texto o la referencia a una celda que contiene el texto, del cual queremos extraer los caracteres.
Posición inicial: es el nº de orden del caracter (empezando a contar desde la izquiera), a partir del cual, vamos a empezar a extraer la cadena de texto.
Núm_de_caracteres: es el número de caracteres que queremos obtener, teniendo en consideración que se empieza a contar desde el caracter definido en el anterior argumento.

Veamos un ejemplo:

Observamos como la fórmula =EXTRAE(E3;6;4) nos devuelve un texto de la celda E3, empezando a contar desde el 6º caracter y con una longitud de 4 caracteres.

José Manuel Pomares Medrano

Las funciones IZQUIERDA y DERECHA en Excel

2011-10-14T08:21:00.000-07:00

La función IZQUIERDA se utiliza para obtener los caracteres de la parte izquierda de un texto. Se pueden obtener el número de caracteres que deseemos, empezando a contar desde la izquierda. Es muy sencilla y sólo tiene 2 argumentos separados por punto y coma y encerrados entre paréntesis:

= IZQUIERDA (texto;núm_de_caraacteres)

Texto: es un texto o la referencia a una celda que contiene el texto, del cual queremos extraer los caracteres.
Núm_de_caracteres: es el número de caracteres que queremos obtener, teniendo en consideración que se empieza a contar desde la izquierda.

El siguiente sería un ejemplo de aplicación:

La función DERECHA tiene exactamente el mismo funcionamiento y objetivo, pero todo está referido al lado derecho.

José Manuel Pomares Medrano

La función DECIMAL en Excel

2011-10-14T05:28:00.000-07:00

Supongamos que es necesario presentar un importe en euros acompañado de un texto en la misma celda de forma encadenada. Es el caso del siguiente ejemplo, en el que hay una celda (abajo a la derecha) que expresa el margen de seguridad de las ventas de una empresa (diferencia entre el promedio mensual real de ventas y el punto de equilibrio o el óptimo de ventas):

Si introdujéramos la fórmula:

="Margen de seguridad "&R6-AC7

obtendríamos el resultado:

Margen de seguridad -46758,7653495918

que, como podemos ver, adopta un formato incómodo de ver y con una cantidad de decimales excesiva.

La razón es que Excel presenta el número en su "estado puro" sin formatos y con todos los decimales. Sin embargo, a esta celda no podemos dar un formato de "Contabilidad" o "Moneda", pues contiene también texto.

Una de las soluciones es utilizar la función DECIMAL, cuyos 3 argumentos son:

= DECIMAL(número;decimales;[no_separar_millares])

Número: es un número o expresión que se refiera a un número que queramos redondear.

Decimales: es un número que representa la cantidad de decimales que queremos fijar.

No_separar_millares: es opcional y se refiere a los valores lógicos VERDADERO O FALSO. Si se omite es FALSO y quiere decir que SI se incluyen separadores de miles en la presentación del número.

Así pues, con una expresión como:

="Margen de seguridad "&DECIMAL(R6-AC7;2)&" €"

Conseguimos un resultado mucho más práctico y fácil de interpretar como:

Margen de seguridad -43.758,77 €

José Manuel Pomares Medrano

La función CONTAR en Excel

2011-10-14T03:43:00.000-07:00

La función CONTAR se utiliza para obtener la cantidad de celdas que, dentro de un determinado rango, tengan algún valor numérico o de fecha. Es muy sencilla y sólo tiene 2 argumentos separados por punto y coma y encerrados entre paréntesis:

= CONTAR (rango;[valor])

Rango: es el rango de celdas donde queremos contar el numero de entradas que contienen valores numéricos o de fecha.
[Valor]: Es opcional y se refiere a otro rango adicional que podemos incluir en la cuenta, o bien valores que directamente escribimos y que queremos que sean incluidos en la cuenta.

Es importante saber que NO se incluirán en la cuenta las celdas vacías, los textos, valores lógicos (VERDADERO / FALSO), ni los valores de error de Excel

En el siguiente ejemplo (en el cual se ha omitido el 2º argumento), podemos ver como la celda que contiene la función CONTAR hace referencia a un rango en el que hay valores de texto (el título) y valores numéricos, sin embargo, sólo cuenta los numéricos:

José Manuel Pomares Medrano

La función CONTARA en Excel

2011-10-14T03:42:00.000-07:00

La función CONTARA se utiliza para obtener la cantidad de celdas que, dentro de un determinado rango, tengan cualquier valor, incluidos los textos vacíos que se escriben como ="" y los valores de error de Excel. Es muy sencilla y sólo tiene 2 argumentos separados por punto y coma y encerrados entre paréntesis:

= CONTAR (valor1;[valor2])

Valor1: es el rango de celdas donde queremos contar el numero de entradas que contienen algún valor algún texto vacio del tipo ="" o algún valor error de Excel.
[Valor2]: Es opcional y se refiere a otro rango adicional que podemos incluir en la cuenta, o bien valores que directamente escribimos y que queremos que sean incluidos en la cuenta.

En el siguiente ejemplo (en el cual se ha omitido el 2º argumento), podemos ver como la celda que contiene la función CONTARA hace referencia a un rango que es toda la columna (L:L) en el que hay valores vacíos, de texto, fechas, números y errores y todos son incluidos en la cuenta, excepto las celdas vacías:

La función CONTARA es muy útil para anidar dentro de una función DESREF cuando esta última pretende definir un rango de una tabla cuya altura puede variar al introducir nuevas filas. Se puede ver un ejemplo de ello en el artículo "Relacionar datos de distintas tablas en Excel".

José Manuel Pomares Medrano

Las funciones BUSCARV y BUSCARH en Excel

2011-10-13T18:19:00.000-07:00

BUSCARV

La función BUSCARV se utiliza en Excel para obtener como resultado, el valor de una celda que está en la misma fila de una tabla, pero una o varias columnas más a la derecha que otro valor que conocemos y buscamos.

Para escribir la función BUSCARV, necesitamos 4 argumentos separados por punto y coma y encerrados entre paréntesis:

=BUSCARV (valor buscado ; rango de toda la tabla ; nº columna valor devuelto ; [Ordenado])

Valor buscado: debe ser una referencia a la celda donde esté el valor que queremos buscar en la otra tabla.
Rango de toda la tabla: debe ser un rango que abarque todos los datos de la tabla donde hay que buscar, debiendo ser la columna donde se encuentra el valor buscado, la primera a la izquierda.
Nº columna valor devuelto: es un número (entero positivo o negativo) que es el número de columna (contando hacia la derecha desde la columna de la izquierda) donde está al dato que queremos obtener. Si el número es negativo, se cuenta hacia la izquierda.
[Ordenado]: es un valor opcional. Sólo admite una de las opciones: VERDADERO o FALSO. Si se omite, se considera VERDADERO, lo que obliga a que la columna de la izquierda (donde se encuentra el valor buscado) esté ordenada de forma ascendente. Si por el contrario ponemos FALSO (es la opción que frecuentemente se utiliza), no es necesario que la columna esté ordenada.

Para una mayor comprensión, veamos el siguiente video-tutorial:

BUSCARH

La V (vertical) de BUSCARV significa que el valor buscado se debe encontrar a lo largo de una columna). Esta función tiene como homóloga BUSCARH, la cual tiene el mismo funcionamiento que BUSCARV, excepto que el valor buscado se intenta encontrar a lo largo de una fila (en horizontal) y que el valor que se solicita está a x filas hacia abajo (si se pone un nº positivo), contando desde la primera fila de la tabla.

José Manuel Pomares Medrano

Relacionar datos de distintas tablas en Excel

2011-10-13T15:34:00.000-07:00

Si el lector ha revisado los artículos de este blog referidos a Modelización de datos en Excel, y los 4 artículos relacionados siguientes, encontrará la necesidad de saber como se pueden relacionar los datos de dos tablas distintas.

En los artículos Modelización de datos en Excel (2) y Modelización de datos en Excel (3), veíamos como era necesario crear en cada tabla unos campos de enlace llamados "campos clave" y que estos podían ser claves primarias (primary keys) o claves externas (foreing keys).

Pues bien, en primer lugar definamos qué es eso de "relacionar" los datos de dos tablas.

Relacionar tablas

Dicho de forma coloquial, relacionar dos tablas, es conseguir que, estando en una tabla nº 2, se pueda elegir un dato de un registro de la tabla nº 1 y automáticamente aparezcan en la tabla nº 2 los datos asociados a ese registro.

Por ejemplo: estando en una tabla que registra los movimientos de artículos que hay en un almacén, cuando se selecciona un artículo, aparezcan al lado todos los datos restantes del artículo seleccionado.

¿Cómo se hace?

En primer lugar, hemos de decir que es imprescindible dominar las funciones DESREF, BUSCARV, BUSCARH, CONTARA y PROMEDIOA:

Ver La función DESREF en Excel

Ver Las funciones BUSCARV y BUSCARH en Excel

Ver La función CONTARA en Excel
Ver La función PROMEDIOA en Excel

También facilitaría la comprensión el seguimiento de los siguientes pasos teniendo abierto el siguiente archivo Excel:

Para que en una tabla aparezcan automáticamente los campos de un registro que está en otra tabla, debemos hacerlo en 2 pasos:

1) Crear en una columna, un campo clave (que será la clave externa o foreign key), donde cada registro tenga una lista desplegable con la utilidad "validación de datos que contenga una lista cuyo origen de datos sea la columna de datos deseada de la otra tabla (clave primaria o primary key). En la utilidad "Validación de datos", se utiliza la función DESREF y la función CONTARA anidada como 4º argumento de la primera.

Veamos un video-tutorial para que quede más claro:

2) Crear en una o varias columnas, el campo o campos donde queremos que aparezcan automáticamente los datos que hay en otra tabla. Se debe utilizar la función BUSCARV , la función DESREF anidada como 2º argumento y la función CONTARA anidada como 4º argumento de la anterior.

Veamos otro video-tutorial para una mejor comprensión:

José Manuel Pomares Medrano

Pronósticos y tendencias en Excel

2011-10-10T15:12:00.000-07:00

En la vida (en general) y actualmente en el mundo de la empresa (en particular), es difícil poder predecir lo que va a ocurrir en el futuro y que además se de la circunstancia de que acertamos. Lo que sí podemos hacer es intentarlo basándonos en la experiencia de lo vivido y ayudándonos de todas las herramientas de las que disponemos.

Centrándonos en el mundo de la empresa, predecir resultados es complicado por varios motivos, entre los cuales se encuentran:

La estacionalidad
El grado de volatilidad en el comportamiento del mercado en cuestión o de la economía del país
Las propias actuaciones de la empresa, ya sea por el lado de las ventas (marketing) o el de los costes (control de gestión).

Así las cosas, las empresas pueden aplicar técnicas para pronosticar el comportamiento de algunos parámetros económico - financieros y lo harán con más éxito en la medida en que la estacionalidad sea menor, el mercado esté más aislado de factores externos influyentes y la empresa tenga comportamientos más o menos estables y alejados de importantes o radicales cambios en la gestión (ya sea comercial, productiva, etc.).

En Excel, existen algunas funciones y utilidades que tienen como finalidad hacer pronósticos sobre determinados parámetros, basándose en datos históricos:

La función PRONOSTICO

Se utiliza para pronosticar un valor en una posición determinada a lo largo de una escala de tiempo. Por ejemplo, podríamos necesitar saber cuáles son las ventas más probables en el mes de Septiembre, sabiendo las ventas de la empresa en los primeros 6 meses. Abajo podemos ver a la izquierda el resultado de la función y a la derecha la gráfica que representa a dicha solución.

Para saber como utilizar la función PRONOSTICO, podemos ver el siguiente vídeo-tutorial:

La función TENDENCIA

Esta función, en vez de pronosticar lo que va a ocurrir en un sólo período, "extiende" el pronóstico a un rango de celdas que abarcan varios períodos (ya sean días, meses, años, etc.) que previamente debemos seleccionar, logrando una continuidad en el tiempo. Abajo podemos ver a la izquierda el resultado de la función y a la derecha la gráfica que representa a dicha solución.

Para saber como utilizar la función TENDENCIA, podemos ver el siguiente vídeo-tutorial:

TENDENCIA con la utiliadad "AUTORRELLENAR"

En el video-tutorial anterior, el cálculo de la tendencia se hacía considerando que las ventas tienen una relación muy directa con la cantidad de visitas comerciales, pero en ocasiones, no hay una causa predominante que condicione los valores que queremos pronosticar. En estos casos, asociamos el pronóstico simplemente al paso del tiempo y esperamos que quede explicado o justificado sólo con los datos históricos que tenemos (en este caso, las ventas desde Enero hasta Junio).

Cuando tenemos esta circunstancia (la mayoría de las veces), podemos calcular una tendencia de forma muy sencilla con la utilidad Autorrellenar, pero esta vez ejecutándola con el botón derecho del ratón. Es un truco poco conocido, pero muy práctico. Veámoslo en otro vídeo - tutorial:

Línea de TENDENCIA en un gráfico de línea

Por último, veamos un video-tutorial que nos enseñará a agregar una línea de tendencia a un gráfico de línea. Además conoceremos el concepto de "coeficiente de determinación" o R cuadrado, el cual es importante a la hora de juzgar si el pronóstico que realiza la línea de tendencia es más o menos válido:

José Manuel Pomares Medrano

La utilidad "Buscar objetivo" en Excel

2011-10-08T04:46:00.000-07:00

La utilidad "Buscar objetivo" calcula qué valor debe tener una variable para que una fórmula proporcione el resultado que nosotros queremos.

Poniendo el ejemplo más simple posible, podríamos decir que "Buscar objetivo" es capaz de calcular la variable "b" para que el resultado "y" de la siguiente operación sea igual a 15.

10 + b = y

A veces, despejar una variable para hacer un cálculo no es fácil. Es entonces cuando "Buscar objetivo" tiene mayor utilidad. Es el caso del cálculo de la Tasa Interna de Retorno (TIR):

(aunque Excel tienen una Función específica para el cálculo de la TIR)

Un ejemplo de utilización práctica sería la aplicación de "Buscar objetivo" al cálculo de la cifra óptima de producción en una empresa. Observemos el siguiente cuadro de datos, donde la última fila es el cálculo de la producción óptima que debe hacer una empresa, teniendo como condición los datos del resto de filas: costes fijos mensuales, importe de las inversiones (capital de préstamos por ejemplo) que mensualmente paga la empresa, % de coste variable y el beneficio que el empresario exige:

Este planteamiento le resuelve el empresario la duda de... ¿qué producción tengo que conseguir para poder abordar todos los costes de mi empresa, el pago de préstamos y obtener un cierto beneficio?. Pero cuando la coyuntura económica de la empresa no es buena, el empresario se ve obligado a plantearse otras preguntas y a "Buscar otros objetivos" (nunca mejor dicho).

El empresario podría plantearse:

Dado que estoy en crisis y mi cifra de producción media es tan sólo de 12.000 €, ¿Qué cantidad de costes fijos puedo permitirme, como máximo?

Veamos como puede el empresario solucionar su problema. Para ello, debemos descargar el siguiente archivo Excel para realizar el ejercicio que a continuación explicamos:

1) Seleccionamos previamente la celda D6 que contiene la fórmula principal que relaciona el resto de celdas. Hacemos click en la "Ficha Datos" y después en el botón "Análisis Y si" que hay dentro del grupo "Herramientas de análisis". Después sólo queda seleccionar la opción "Buscar Objetivo...". Aparecerá el cuadro de dialogo siguiente:

2) En el campo "Definir la celda" debe aparecer D6. En general, se puede decir que en este campo hay que hacer referencia a una celda que contenga una fórmula y debe ser la variable dependiente o endógena, es decir, la fórmula principal que pone en relación al resto de variables. En nuestro ejemplo, es la celda que contiene el importe de la Cifra de producción.

3) En el campo "Con el valor" debemos poner el valor que queremos que arroje la celda anterior (D6) que contiene la fórmula principal. En nuestro ejemplo, debemos escribir 12.000, puesto que el empresario quiere forzar que la combinación del resto de variables le permitan tener una producción media de ese valor, pues piensa que no es realista, dada la coyuntura, esperar mayores producciones.

4) En el campo "Para cambiar la celda", debemos hacer referencia a la variable que queremos hallar, puesto que ahí nos aparecerá el importe que hace posible las exigencias del anterior punto. En nuestro ejemplo, debemos hacer referencia a la celda D2

Una vez terminados los pasos 2, 3 y 4, debe quedar así:

5) Aceptamos y observamos como, en la celda D2, aparece una nueva cantidad que hace cumplir las exigencias del empresario. Es decir, el empresario debe reducir sus costes fijos hasta 7.300 € para poder mantener su empresa con una producción media de 12.000 € y no tener pérdidas

Análisis de datos en Excel

2011-10-07T22:40:00.000-07:00

Excel dispone de 3 herramientas de análisis dinámico de datos (análisis "Y si" o también llamado a veces análisis What If en otros contextos) , las cuales tienen finalidades distintas aunque también una característica en común: obtienen resultados (operando con varios parámetros) basados en determinadas condiciones que imponemos y que podemos variar con comodidad para contrastar como afectan a dichos resultados.

(además de las 3 herramientas de análisis dinámico "Y si" aludidas y que conoceremos en este artículo, Excel tiene otra utilidad llamada "Solver" muy similar a "Buscar objetivo", pero además puede trabajar con restricciones, calcular óptimos y manejar más de 1 variable)

Un ejemplo ejemplo de análisis "Y si", sería obtener como resultado la cuota de préstamo que se pagaría si la tasa de interés fuera de un 5%.... Y si fuera de un 6% .... Y si fuera de un 7% ....

Otro ejemplo de análisis "Y si", sería conocer qué escenario de rentabilidad habría en una empresa, si simultáneamente aumentan las ventas un 3%, disminuyen los costes de personal un 7% y obtenemos de nuestros proveedores un descuento del 2% en todos los aprovisionamientos.... Y si simultáneamente disminuyen las ventas un 10%, disminuyen los costes de energía un 6% y aumentan los costes de Seguridad Social un 9% ..... Y si...

Como decíamos, en Excel existen 3 modalidades de análisis "Y si" (algunas muy útiles y difícilmente sustituibles y otras menos útiles y con posibilidad de obtener resultados similares o mejores con sitemas alternativos):

Tablas de datos

Excel llama "Tablas de datos" a una utilidad mediante la cual podemos obtener los resultados que arrojaría una operación en caso de que una o dos de las variables implicadas en dicha operación tomaran distintos valores.

Una de las ventajas (quizá la única) que tiene utilizar las tablas de datos (frente a otras formas de análisis "Y si" ) estriba en que todos los resultados posibles para cada combinación de variables son desplegados en una tabla, con lo que se pueden ver de forma simultánea. En cuanto a las desventajas, una de ellas es la limitación en el número de variables que podemos manejar (1 ó 2).

Si desea aprender más sobre esta forma de análisis "Y si", haga click en:

Tablas de Datos en Excel (2 variables)

Administrador de escenarios

Cuando las variables que queremos manejar son más de 2, tenemos que recurrir al "Administrador de escenarios". Esta herramienta nos permite diseñar (y dejar guardados) conjuntos de combinaciones de variables (escenarios) para aplicar uno u otro de forma rápida y comparar los resultados que se dan en una hoja de cálculo determinada. Excel puede manejar simultáneamente en cada escenario, hasta 32 variables.

Dos ejemplos de escenarios serían los siguientes conjuntos de combinaciónes de variables:

Escenario pesimista
Crecimiento interanual de los ingresos: 0 %
Crecimiento interanual de los gastos: 10 %
IPC interanual: 5%

Escenario optimista
Crecimiento interanual de los ingresos: 10 %
Crecimiento interanual de los gastos: 8 %
IPC interanual: 4%

Con la "Administración de escenarios" podemos aplicar casi a golpe de click todo un escenario a la vez a una cuenta de resultados o a un presupuesto (por ejemplo) y saber qué efectos tiene en los resultados de una empresa.

Si desea aprender más sobre esta forma de análisis "Y si", haga click en:

Administracion de escenarios en Excel

Buscar objetivo

Imaginemos una micro-empresa que desea introducir una nueva línea de negocio contratando a un comercial. El gerente se informa de los costes que supondría dicha contratación (sueldo fijo, Seguridad Social y dietas) y conoce el precio medio de coste del producto que se venderá en la nueva línea. Con todo ello, ha calculado el punto de equilibrio o umbral de rentabilidad de ventas que se debe alcanzar y estima que la cantidad de ventas que realmente se alcanzará, supererá dicho umbral y no habrá problemas.

A la hora de negociar las condiciones económicas con un prestigioso comercial, ¿hasta qué porcentaje podría ofrecer como comisión de ventas al candidato sin caer en pérdidas?

Este es tan sólo un ejemplo de la finalidad de "Buscar objetivo", que es la tercera de las formas de análisis "Y si" que presentábamos al principio. Es una utilidad fácil de entender y usar, pero tiene la limitación de trabajar sólo con una variable.

Si desea aprender más sobre esta forma de análisis "Y si", haga click en:

La herramienta "Buscar objetivo" en Excel

José Manuel Pomares Medrano

Administrar escenarios en Excel

2011-10-07T22:35:00.000-07:00

El "Administrador de escenarios" de Excel es una buena herramienta de análisis de datos con multiples aplicaciones en el ámbito de la Dirección Financiera o Comercial. Es aplicable a cuentas de resultados, presupuestos y en general, cualquier contexto en el que haya que operar con varios parámetros.

Para conocer su funcionamiento, vamos a descargar una plantilla sobre la que practicaremos:

Si vamos a la hoja "Escenarios", podemos ver una tabla similar a la de la imagen de abajo, con la cuenta de resultados previsional a 5 años (simplificada para el ejercicio) de una empresa. También podemos ver más arriba un cuadro con 3 parámetros supuestos referidos al crecimiento que se espera experimente la empresa y a la evolución del Índice de Precios al Consumo:

Las celdas del año 2 contienen fórmulas que calculan el crecimiento respecto de los valores de las celdas del año 1. Las celdas del año 3 contienen fórmulas que hacen lo propio respecto de los valores de las celdas del año 2..... y así sucesivamente. Por lo tanto las únicas celdas de la tabla de abajo que contienen valores (no fórmulas) son las del año 1.

Es una imagen muy frecuente en el departamento financiero de cualquier pyme o bien en el plan de viabilidad económica de cualquier emprendedor, pero imaginemos que el director financiero o el gerente de la empresa necesita valorar distintas situaciones que se pudieran dar debido a la coyuntura económica y/o a la propia empresa, variando los valores de algunos parámetros... ¿cómo se podría hacer esta simulación?.

Supongamos que las tablas que presentamos anteriormente conforman un "escenario moderado" y el gerente necesita estudiar otros posibles escenarios (uno optimista y otro pesimista) para poder prever una toma de decisiones en todos los casos:

1) Hagamos click en la "Ficha Datos", después en el botón "Análisis Y si" (que hay dentro del grupo"Herramientas de datos") y después en "Administrador de escenarios...". Obtendremos el cuadro de diálogo:

... donde tendremos que agregar uno a uno, los "escenarios" que queremos estudiar, empezando por el moderado (que es el que aparece de partida en las tablas) y terminando con el pesimista y optimista.

2) Hacemos click en "Agregar" e introducimos en el siguiente cuadro de diálogo que emerge, los siguientes datos y referencias:

En el "Nombre de escenario" podemos poner libremente un nombre con el que queremos identificar el escenario.

En "Celdas cambiantes", una vez borramos la referencia que aparece por defecto, presionamos CTRL y con el ratón seleccionamos el rango de celdas H3:H5 y, sin haber dejado de hacer presión sobre CTRL, selesccionamos el rango de celdas D9;D10. El resultado que obtenemos son 2 rangos separados por punto y coma (Excel pone automáticamente el signo punto y coma y los signos de $).

3) Aceptamos y obtenemos el siguiente cuadro:

4) Vemos que lo que aparece son los datos que había en las celdas seleccionadas y que deseamos queden memorizadas como "Escenario moderado". Así pues, no tenemos que cambiar los datos y damos a "Agregar", obteniendo de nuevo el cuadro de diálogo donde se definen los escenarios (el nombre y las celdas cambiantes).

5) Repetimos exactamente el paso 2), pero en el "Nombre de escenario" , ponemos "Escenario pesimista" y aceptamos (las celdas cambiantes las pone Excel ya por defecto esta vez. Obteniendo de nuevo el pequeño cuadro de diálogo "Valores de escenario".

6) Esta vez, cambiamos los valores que nos presenta por defecto Excel, por los valores del "Escenario pesimista" de nuestra tabla de supuestos. Es decir:

... y damos a "Agregar".

7) Repetimos los pasos 5) y 6), pero esta vez poniendo como nombre de escenario "Escenario optimista" y en el cuadro de diálogo "Valores del escenario" ponemos los valores del escenario optimista de nuestros supuestos. Es decir:

...y esta vez, puesto que no tenemos que agregar más escenarios,hacemos click en "Aceptar" y obtenemos:

Ahora ya tenemos todos los escenarios configurados, sólo nos queda seleccionar el deseado en el panel "Escenarios" y hacer click en el botón "Mostrar". Podremos ver cómo se introducen automáticamente en las 5 celdas los datos de nuestro supuesto y todo queda recalculado según el escenario elegido.

Debemos apreciar que cuando hacemos clik en "Mostrar" el cuadro de diálogo no desaparece. Esto es para que podamos cambiar entre escenarios más rápidamente y poder apreciar los cambios con más facilidad.

En el cuadro de diálogo anterior ("Administrador de escenarios"), vemos que existe un botón llamado "Resumen..." que al hacer click sobre él obtenemos el siguiente cuadro, donde debemos seleccionar en el campo "Celdas de resultado:", el rango de celdas que consideramos como resultados importantes para nosotros y cuyo resultado para cada escenario, nos gustaría saber (vemos abajo que se han seleccionado arbitrariamente como rango de celdas importantes, las que reflejan el beneficio en porcentaje respecto de las ventas):

Si hacemos click en "Aceptar", obtenemos un cuadro - resumen que es totalmente modificable (y no se actualiza al volver a cambiar los datos en la tabla sobre la que hemos aplicado los escenarios). Una vez cambiamos las celdas en las que obtenemos resultados irrelevantes para nosotros, podemos obtener un interesante resumen como este:

Por último, comentar que en el cuadro de diálogo ("Resumen del escenario"), también disponemos de la opción de obtener una tabla dinámica de escenario (en vez de un resumen "estático" como hemos obtenido en nuestro ejemplo).

Análisis de datos en Excel
Tablas de datos en Excel (2 variables)
La utilidad "Buscar objetivo" en Excel

José Manuel Pomares Medrano

Tablas de datos en Excel (2 variables)

2011-10-06T18:07:00.000-07:00

Como decíamos en el post "Análisis de datos en Excel", llamaremos "Tablas de datos" a una utilidad mediante la cual podemos obtener los resultados que arrojaría una operación en caso de que una o dos de las variables implicadas en dicha operación tomaran distintos valores. En este artículo se estudiarán las Tablas de datos con 2 variables.

Conozcamos pues, cómo utilizar las Tablas de datos en Excel:

A continuación, vemos una tabla de datos completada:

Vemos una tabla en la que para distintos valores de ventas (encabezados de columnas) que pudieran conseguir los comerciales de una empresa, se pagarían distintos importes (en rojo), según se aplique un porcentaje de comisión u otro (encabezados de filas). Excel rellena automáticamente todos los importes en rojo gracias a la aplicación de esta forma de análisis dinámico sobre:

Los encabezados de columna, en los que ponemos a nuestra elección los valores que podrían tomar las ventas de los comerciales
Los encabezados de fila, en los que ponemos a nuestra elección los porcentajes de comisión que la empresa podría pagar
2 celdas (sobreadas en verde), cuyos valores no son relevantes para el cálculo de la "Tabla de datos", de hecho, podrían estar vacías o tener cualquier valor. Lo que si es importante es su situación, pues estas celdas deben estar localizadas y fuera del ámbito de la Tabla de datos que queremos conseguir. El cometido de estas celdas es servir únicamente de referencia a Excel.
1 celda (sombreada para la ocasión en naranja), que debe tener una formula mediante la cual, Excel pueda saber qué relación tienen las celdas verdes.

¿Cómo se hace?

La forma de conseguir que Excel rellene automáticamente todos los importes que habría que pagar a los comerciales según porcentaje de comisión y según nivel de ventas, es la siguiente:

1) Introducimos en la celda naranja (DEBE ESTAR EN LA POSICIÓN QUE VEMOS EN LA IMAGEN, ES DECIR, EN LA ESQUINA SUPERIOR IZQUIERDA DE LA TABLA QUE DESEAMOS FORMAR) una fórmula que relacione de la forma que queramos a las celdas de color verde (en la imagen de abajo, podemos ver que la relación es promedio de ventas x % de comisión). Como ya se ha dicho antes, es indistinto qué valores contienen las celdas verdes, puesto que lo importante es tenerlas identificadas, fuera del rango de la Tabla de datos y que la celda naranja las relacione.

2) Seleccionamos todo el rango de la tabla que se formará:

3) Seleccionamos la "Ficha Datos", el botón "Análisis Y si" que hay dentro del grupo "Herramientas de datos", y después seleccionamos la opción "Tabla de datos...".

4) Una vez hecho, aparecerá un cuadro de diálogo en el que debemos hacer la siguiente referencia a las filas:

5) Después, hacemos la referencia a las columnas:

6) A aceptamos.... con el consiguiente resultado que veíamos al principio del artículo:

¿Otras opciones similares?

El mismo resultado que obtenemos con el análisis "Y si" de Excel, lo podemos conseguir introduciendo una fórmula en una de las celdas y haciendo uso de "Autorrellenar" y con la precaución de utilizar las "Referencias relativas" necesarias en las celdas. Por ejemplo, en la celda que une la columna 50.000 € y la fila 1 %, podríamos poner: =$B9*C$8 y arrastrar hacia abajo y hacia la derecha para conseguir un "autorrellenado", teniendo al final el mismo resultado:

José Manuel Pomares Medrano

Gráficos de "Velocímetro" en Excel (3)

2011-10-06T05:54:00.000-07:00

En el anterior artículo Gráficos de "Velocímetro" en Excel (2) proporcionábamos un archivo de Excel que contenía 5 gráficos de velocímetro listos para usar.

En este post, vamos a ofrecer varias posibilidades de personalización del diseño y lo vamos a hacer mediante videotutoriales:

Aspecto de la aguja:

Longitud:
Para cambiar la longitud de la aguja, basta con cabiar el valor de la celda que a tal efecto hay en el cuadro del velocímetro:

Ancho y forma:

Seleccionando la aguja y desde el comando "Contorno de forma" de la "Ficha Formato", podemos alegir el aspecto que tendrá la aguja de nuestro velocímetro:

Aspecto de la banda de graduación:

Podemos hacer más ancha o estrecha esta banda y dar un aspecto 3D, además de adecuar los colores a nuestro gusto:

Etiquetas de la banda de graduación:

Si deseamos acompañar la banda de graduación con etiquetas, es tan sencillo como introducir los porcentajes deseados en determinadas celdas y después colocar el velocímetro en el lugar adecuado:

Mostrar el porcentaje cumplido:

Si queremos presentar en pantalla el porcentaje cumplido respecto del objetivo, tan sólo debemos hacer referencia a la celda que a tal efecto hay en el cuadro de datos del velocímetro.

Lo vemos en un video-tutorial:

Gráficos de "Velocímetro" en Excel

2011-10-06T02:31:00.000-07:00

En el mundo de la empresa son fundamentales los indicadores que comparan una información con otra. Los gráficos son una inestimable ayuda para ello, pues facilitan al usuario la transición entre el "conocimiento" y la "toma de decisiones".

Hay que recordar al lector, que el sistema de información (SI) de cualquier empresa debe tener la facultad de convertir los datos (representación simbólica sin sentido propio) de los sistemas transaccionales (contabilidad, facturación, etc.) en información (conjunto de datos que una vez procesados tienen relevancia, contexto y finalidad) que sea capaz de generar conocimiento (experiencia y saber hacer) para poder tomar decisiones (elegir la mejor de las opciones) que den paso a acciones que optimicen la empresa (mejora de procesos).

Como decíamos, la utilidad de los gráficos es facilitar al usuario la transición entre las fases "conocimiento" y "toma de decisiones" y aunque esto a alguien le pudiera parecer baladí, pensemos en Eward Tufte, especialista en representación de datos que demostró con sus teorías que el accidente del Challenger (28-01-1.986), pudo haberse evitado si los ingenieros hubieran demostrado con claridad en sus presentaciones gráficas a los responsables del lanzamiento, que el motivo de la erosión en unas piezas llamadas O-Rings, eran las bajas temperaturas.

Un ejemplo de tipo de gráfico que ayuda a interpretar correctamente y de un vistazo el conocimiento que se infiere al unir dos informaciones, es el gráfico de tipo velocímetro. Se trata de un gráfico que compara dos magnitudes, una de las cuales representa un objetivo a alcanzar y la otra el valor realmente conseguido hasta el momento.

Las magnitudes pueden ser promedios, acumulados, porcentajes, etc. y la comparación de la cifra conseguida hasta el momento puede hacerse respecto de:

Una cifra elegida como referencia, por ejemplo, un estandar del sector en el que opera la empresa
Un cifra-presupuesto.
Una cifra umbral o mínima (es el caso, por ejemplo, del punto de equilibrio o umbral de rentabilidad del volumen de ventas)
Una cifra óptima (similar al punto de equilibrio, pero el umbral ya proporciona una determinada rentabilidad exigida y no un beneficio cero).
Una cifra máxima (es el caso utilizado en el control de costes)

En el siguiente artículo Gráficos de "Velocímetro" en Excel (2) proporcionaremos un archivo Excel en el cual hay una hoja con 5 velocímetros preparados para que cualquier usuario los incorpore a su libro de Excel de forma muy sencilla.

Artículos relacionados:

Gráficos de "Velocímetro" en Excel (2)
Gráficos de "Velocímetro" en Excel (3)
Gráficos de "Velocímetro" en Excel (4) .... próximamente

José Manuel Pomares Medrano

Gráficos de "Velocímetro" en Excel (2)

2011-10-06T00:36:00.001-07:00

Vamos a proporcionar en este post, un archivo Excel para versiones 2007 o 2010, que contiene una hoja con 5 velocímetros ya "pre-fabricados", para que cualquier usuario pueda incorporarla al libro con el que está trabajando.

El archivo ofrecido contiene una sóla hoja, en la cual hay 5 velocímetros. A grandes rasgos, podemos resumir el proceso en que el usuario debe copiar esta hoja en el libro de Excel suyo, eliminar los velocímetros que no vaya a utilizar, enlazar las celdas que alimentan al velocímetro con las celdas que ofrecen las magnitudes relevantes: lo cumplido y el objetivo y colocar los que si vaya a utilizar en el lugar deseado de la hoja.

Más detalladamente, el proceso sería:

1) Mover el archivo descargado "velocímetros.xlsx" a la misma carpeta donde tenemos el libro en el que queremos colocar el/los velocímetros (supongamos que se llama "milibro.xlsx")

2) Abrir "milibro.xlsx" y también abrir el libro descargado anteriormente "velocímetros.xlsx".

3) Hacer click con el botón derecho del ratón en la hoja "Velocímetros" del libro "velocímetros.xlsx"

y seleccionar la opción "Mover o copiar...", con lo que obtenemos:

... y una vez ahí, hacemos click en la lista desplegable y vemos que aparece "milibro.xlsx" :

... y lo seleccionamos, viendo que entonces aparecen las hojas que contiene:

... seleccionamos el lugar que queremos que ocupe la hoja "velocímetros.xlsx" dentro de "milibro.xlsx", por ejemplo "(mover al final)", y aceptamos.

El resultado es que se nos queda abierto sólo "milibro.xlsx" y con la hoja "velocímetros" ya incluida.

4) Si por ejemplo queremos construir un dashboard con 2 velocímetros, debemos eliminar 3 de los que hay en la hoja "velocímetros". Es tan sencillo como seleccionar todas las filas desde el velocímetro 3 hasta el 5 y seleccionar "Eliminar filas de hoja" despues de hacer click en el btón "Eliminar" del grupo "Celdas" de la "Ficha Inicio" de la cinta de opciones de Excel.

Al eliminar, obtendremos un cuadro de diálogo comunicando: "Una hoja de esta hoja de cálculo contiene una o más referencias no válidas........" pero debemos obviarlo y hacer click en aceptar.

5) Los velocímetros se suministran con unos datos de ejemplo, pero ahora debemos enlazar las celdas de "Cantidad cumplida" y "Objetivo a cumplir" con el origen de datos que nosotros necesitamos. Es decir, si en "milibro.xlsx" tenemos el promedio de ventas realizadas en la celda B1 de "mihoja1" y por otro lado tenemos el promedio de ventas presupuestado en la celda B2 de "mihoja1" también, pues debemos poner: = mihoja1!B1 y = mihoja1!B2 respectivamente, con lo que obtendremos algo similar a la siguiente imagen (dependiendo, evidentemente de las cifras):

6) Ya tenemos nuestro velocímetro funcionando. Sólo queda colocarlo en el lugar que deseemos seleccionandolo y moviéndolo o bien hacer una copia seleccionandolo y Copiar + Pegar y colocar esta copia donde queramos. Finalmente, podemos esconder el cuadro que constituye el "corazón" del velocímetro:

Insertamos varias filas en la parte superior del título "VELOCÍMETRO 1" y de esta forma "esconderemos" el cuadro.
Seleccionar todo el cuadro y asignar el color blanco para los datos, eliminar los sombreados y los bordes. (esta opción es peligrosa si la hoja no se protege, pues podríamos borrar accidentalmente el contenido de cualquier celda)

Nota: No se debe cambiar el contenido de las demás celdas del cuadro (Graduación, Porcentaje cumplido, etc...)

En el siguiente artículo "Gráficos de "Velocímetro" en Excel (3)", ofrecemos algunas opciones para personalizar el aspecto los velocímetros:

Aguja (longitud, ancho y forma de la flecha)
Aspecto de la banda de graduación de colores (ancho, colores, etc.)
Etiquetas de graduación

Artículos relacionados:

Gráficos de "Velocímetro" en Excel
Gráficos de "Velocímetro" en Excel (3)
Gráficos de "Velocímetro" en Excel (4) ... próximamente

José Manuel Pomares Medrano

Modelización de datos en Excel (5)

2011-09-23T17:43:00.000-07:00

Finalizamos esta serie con este post dedicado a presentar el archivo de Excel de la aplicación de ejemplo para gestionar las nóminas de una pyme, que ha servido de guía al lector a lo largo de estos 5 artículos:

A continuación haremos unas puntualizaciones de apoyo para que el lector (con el archivo abierto de forma paralela) pueda comprobarlas:

El esquema final adoptado es un diagrama de copo de nieve con 4 tablas:

Áreas (Tabla de dimensión)
Trabajadores (Tabla de dimensión)
Cuentas de Tesorería (Tabla de dimensión)
Nóminas (Tabla de hechos)

Cada tabla se ha alojado en una Hoja de Excel distinta, especialmente es conveniente esto para que los registros de la tabla de hechos pueda crecer de forma prácticamente ilimitada.
Excepto el campo ID Nombre Trabajador, que se ha aprovechado para que sea una clave primaria, el resto de claves primarias son cadenas alfanuméricas de 3 caracteres en mayúsculas a elección del usuario, pero que es conveniente sean lo más descriptivas posibles. Por ejemplo, para el Área de Administración de la empresa, asignamos el código ADM. Debemos estar seguros de asignar un nombre definitivo, pues a posteriori no deberá cambiarse.
La tabla de hechos Nóminas tiene las claves externas de ID Nombre trabajador e ID Tesorería, pero también se ha incluido la clave externa de ID Área para crear una relación con la tabla de dimensión Trabajadores. El motivo se explica en el siguiente punto.
En la tabla de hechos Nóminas han de estar representados todos los campos importantes sobre los cuales queremos solicitar informes (como mínimo, los campos propios de la tabla de Nóminas, más todas las claves externas). La razón no es otra que casi todos los filtros e informes de tablas y/o gráficos dinámicos va a tener su origen de datos en esta tabla o en parte de ella.
No toda, pero la mayoría de información que queramos obtener de la aplicación, la podremos conseguir mediante los Filtros que podemos activar desde el botón que a tal efecto hay en el grupo "Ordenar y filtrar" de la Ficha "Datos".
Si frecuentemente queremos un informe concreto, podemos desarrollar una tabla dinámica (con su gráfico correspondiente si es necesario) a la cual podremos acceder de una forma inmediata. Unas de las ventajas que podemos conseguir con estas tablas es la posibilidad de agrupar las fechas por meses, trimestres, años, etc., además de poder trabajar por más de una dimensión a la vez (es decir, podemos obtener información de las comisiones que se pagan por área y, dentro de cada área, saber a que trabajadores se les paga y qué cantidad. En el archivo de Excel, se han desarrollado, a modo de ejemplo, 3 tablas dinámicas, una de ellas con gráfico incluido.
El archivo tiene un acabado "en bruto", puesto que su finalidad es servir como ejemplo. Para una utilización cómoda (además de agregar los campos que una empresa en concreto necesitara), debería

Tener un panel con enlaces o botones para navegar por las distintas tablas e informes con más comodidad.
Tener botones para "Nuevo registro" y "Eliminar Registro" en todas las tablas, de forma que el código de las macros de dichos botones tuviese instrucciones para que las columnas de claves quedaran bloqueadas al usuario.

El usuario puede ver la forma de relacionar las tablas y las funciones empleadas (Listas desplegables con "Validación de datos" con la función DESREF como protagonista para obtener un rango variable atendiendo a la agregación de registros)

Nota: El archivo tiene formato .xlsm para que en un futuro pudiera albergar macros. (si tiene dudas sobre tipos de archivos, haga click en "Guardar archivos en Excel".

José Manuel Pomares Medrano

Modelización de datos en Excel (4)

2011-09-23T14:39:00.000-07:00

Abordamos en este artículo el segundo y último de los esquemas de modelización de datos que habíamos empezado a estudiar: el Esquema o diagrama en Copo de Nieve.

Esquema o diagrama en Copo de Nieve

Una vez estudiado el Esquema o diagrama de Estrella, nos damos cuenta de que, a veces, las tablas de dimensión necesitan otras tablas de dimensión auxiliares.

El motivo es que, una de las características o dimensiones que puede tener un trabajador, es el área o departamento al que pertenece dentro de la empresa. Si para nosotros fuese importante este detalle, necesitamos construir una tabla de dimensión Áreas con su correspondiente campo de clave primaria y luego agregar en la tabla de dimensión Trabajadores un campo de clave externa para poder relacionar ambas tablas.

Es decir, cuando estamos en la tabla de dimensión Trabajadores, necesitamos cumplimentar un campo que exprese a qué área funcional de la empresa pertenece el trabajador de ese registro. Como en otros casos, esto se soluciona con una lista desplegable en el campo de la clave externa, cuyo rango origen será el campo de clave primaria en la tabla de dimensión Áreas.

Vemos a continuación como, mediante tablas de dimensión auxiliares de otras tablas de dimensión, se forma una estructura en forma de un copo de nieve, replicando la estructura de dentro hacia afuera de forma piramidal.

Todo lo dicho en el artículo Modelización de datos en Excel (3) sobre claves primarias y externas y sobre la forma en la que se relacionan las tablas, es válido para la relaciones entre las tablas de dimensión .

Finalmente, el esquema de tablas de nuestro ejemplo de una aplicación en Excel para gestionar las nóminas, es la siguiente:

En el próximo post, veremos como ha quedado el archivo Excel ya prácticamente terminado y funcionando, con todas las relaciones realizadas (listas deplegables y demás fórmulas y funciones necesarias) y algunos informes de ejemplo realizados con tablas y gráficos dinámicos, resaltando las ventajas de haber construido una aplicación con una estructura "normalizada" que, entre otras ventajas...

Evita redundancias
Posibilita un funcionamiento ilimitado en el tiempo tan solo agregando registros y sin necesidad de copiar y pegar constantemente en nuevas hojas o libros.
Tiene la flexibilidad adecuada para no ser necesario diseñar y confeccionar un informe cada vez que los administradores de la empresa quieran obtener alguna información para la toma de decisiones.

José Manuel Pomares Medrano

Modelización de datos en Excel (3)

2011-09-23T12:41:00.000-07:00

Continuando con el artículo Modelización de datos en Excel (2) e intentando acercar las técnicas del Business Intelligence al diseño de aplicaciones en Excel, vamos a ver ahora las formas de relacionar las tablas que hemos aprendido a conformar. Es evidente que las tablas creadas deben tener relación entre ellas, de hecho, lo hacen mediante campos clave.

Claves externas (foreign keys)

Para que cada registro de una tabla de hechos quede indentificado por una dimensión, es necesario agregar a tal efecto un campo en dicha tabla (la de hechos) que adopte uno de los valores de la clave primaria que tiene la tabla de dimensión, de forma que exista una especie de "cordón umbilical" entre ambas tablas. Las claves que se agregan en la tabla de hechos se llaman claves externas o foreign keys y sí pueden tener duplicados, evidentemente:

A este tipo de relación de la llama "relación de uno a varios"

Por lo tanto, cada tabla de dimensión necesita tener relacionado su campo de clave primaria con un campo de clave externa que debe haber a tal efecto en la tabla de hechos. Es una especie de cordón umbilical que sirve para que queden relacionadas.

Centrándonos en el ejemplo que iniciamos en artículos anteriores y con perspectiva práctica, podemos adivinar que cuando estamos en la tabla de hechos, el campo de ID Tesorería lo cumplimentaremos mediante una lista desplegable cuyo origen de datos es la columna ID Tesoreria de la tabla de dimensión.

Para recordar como hacer una lista desplegable adecuada para este caso, ver los artículos:

Listas desplegables en Excel (3)

La función DESREF en Excel

A estas alturas, es necesario explicar que existe otro motivo por el cual es necesaria la creación de campos de clave primaria con valores únicos. De hecho, algunos lectores se preguntarán:

¿Por qué es necesario el campo ID Tesorería si se podría haber aprovechado el campo Tesorería para convertirlo en campo de clave primaria asegurándonos de no repetir un mismo nombre?

La respuesta es que, si lo hiciesemos así, en el futuro (por ejemplo) el banco podría cambiar de nombre y entonces las claves externas en la tablas de hechos, se verían sin sus "hermanas gemelas":

Es fácil adivinar los problemas que crearíamos si en un futuro cambiáramos por otra distinta la clave primaria "AAA": en la tabla de hechos habria varios registros sin la dimensión "hermana". Es decir romperíamos la llamada "integridad referencial". Con claves primarias que sean independientes de las descripciones, se salvaguarda la posibilidad de actualizar las segundas, dejando siempre intactas las primeras.

En resumen: las claves (primarias y externas), no pueden cambiarse una vez diseñada la aplicación y puesta en marcha. Esto es solucionable en Excel mediante una macro que bloquee las celdas de claves después de haber añadido un registro.

Pero demos un paso adelante y veamos como, mediante la relación de unas tablas con otras, podemos formar esquemas o diagramas que, según su forma tienen unas u otras características:

Esquema o diagrama de "Estrella"

Hasta ahora, podemos concluir que las 3 tablas que hemos construido tienen la siguiente relación:

La tabla de dimensión de Trabajadores y la tabla de hechos quedan relacionadas por el campo clave "ID Nombre trabajador". La tabla de dimensión de Cuentas de Tesorería y la Tabla de Hechos quedan relacionadas por el campo clave "IDTesorería" (como hemos visto anteriormente en este artículo).

El caso que nos ocupa es sencillo y apenas necesita 2 tablas de dimensión, pero en caso de que el ejemplo fuera más complicado debería ampliarse con otras tablas de dimensión:

Esta filosofía de relacionar las tablas (modelizar los datos) se llama "Esquema o diagrama de Estrella", debido a la forma adoptada por las tablas de dimensión (alrededor de la tabla de hechos en forma de estrella).

Nuestro siguiente artículo será estudiar el "Esquema o diagrama de Copo de Nieve".

Artículos relacionados:

Modelización de datos en Excel
Modelización de datos en Excel (2)
Modelización de datos en Excel (4)
Modelización de datos en Excel (5)

José Manuel Pomares Medrano

Modelización de datos en Excel (2)

2011-09-22T16:17:00.000-07:00

Durante este tutorial vamos a trabajar sobre un ejemplo supuesto muy sencillo:

Un Director Financiero quiere construir una aplicación en Excel para tener un control de las nóminas que se van generarndo mes a mes de los empleados (producción, comerciales, administrativos y gerencia) y sus remuneraciones (fijas, horas extra y comisiones), así como de los pagos realizados a los trabajadores.

¿Cómo se modelizan los datos en Excel?

Como se decía en el artículo precedente a este (Modelización de datos en Excel), para modelizar y montar una estructura de datos adecuada, es necesario organizar los datos agrupándolos, según unos criterios determinados, mediante tablas de dos tipos:

1) Tablas de hechos (fact tables)

En estas tablas deben ir los datos que representen los hechos principales a medir y a vigilar mediante indicadores. Suelen ser los datos que se multiplican en mayor cantidad a medida que pasa el tiempo, luego también suele ser imprescindible que en una tabla de hechos exista como mínimo un campo de fecha y otro que represente el hecho en sí, valorado en euros. En otras palabras (y simplificando para entenderlo mejor), los datos de las tablas de hechos suelen expresar: ¿cúando ha ocurrido un hecho? y ¿qué valor tiene el hecho?.

En nuestro ejemplo de aplicación para el control de nóminas, el hecho principal es que cada mes se generan una serie de nóminas en la empresa. Las nóminas podrán ser de un trabajador u otro, pagarse mediante una cuenta de tesorería u otra, pero cada mes se generan nóminas... y así... indefinidamente. Es una tabla que multiplicará sus registros muy rápidamente y cada registro (cada hecho) es una nómina que está asociada a una fecha de generación (el último día de cada mes). En definitiva, la Tabla de Hechos será la tabla de nóminas generadas con los siguientes campos, por ejemplo:

Fecha Nómina: La fecha de devengo de la nómina. Normalmente el último día del mes.

Devengos: Suma de las cantidades principales que componen la nómina (salario base, antigüedad, etc.)
Horas: Horas extras a incluir en la nómina.
Incentivos: Comisiones y similares por productividad o ventas.
S.Social: Deducción de la Seguridad Social que va a cargo del trabajador
IRPF: Deducción del IRPF que corresponde al trabajador por su nómina.

Podemos observar que, los datos que de momento se han incluido en la tabla de hechos son los que cumplen las siguientes condiciones:

Todos los meses varían. Es decir, la fecha, el importe devengando, el IRPF, etc... siempre varían en cada nómina. Podría hacerse una lista muy larga de fechas e importes, sólo limitada por la duración de la vida de la empresa.
Las cantidades (devengos, horas, comisiones e IRPF) y la fecha de cada nómina son lo que queremos cuantificar y son los datos protagonistas.

2) Tablas de dimensión (dimensión tables)

Cada una de las tablas de dimensión debe contener los valores que definen a los hechos diciendo quién los ha realizado y qué características tienen.

Por ejemplo, el dato: Comisión = 105,56 € en 31/01/2011 no tiene utilidad "per se", sino que necesita de otros datos complementarios (dimensiones) que expliquen quién ha recibido la comisión y por qué entidad financiera se pagará. Las dimensiones son aquellos conceptos por los que después se consultará en los informes: es frecuente que un Director Financiero quiera consultar las comisiones por cada comercial, las nóminas por cada área o departamento, etc.

En otras palabras (y simplificando para entenderlo mejor), las tablas de dimensión suelen decir ¿qué tipo de hecho ha ocurrido? ¿quién hay detrás del hecho?

Claves primarias (primary keys)

Las tablas de dimensión necesitan que uno de los campos sea una clave primaria o primary key para que cada registro quede identificado inequívocamente. Esto significa que en ese campo NO PUEDE HABER DUPLICADOS. En el mundo del Business Intelligence, las claves primarias suelen ser números enteros que se generan mediante autonumeración (por norma y aunque haya campos en cuyos registros no haya duplicados), pero en Microsoft Excel y dado que no es una aplicación diseñada como base de datos relacional, debemos flexibilizar algunas normas y esta es una de ellas: si en una tabla de dimensión hay un campo que contiene datos para los que no va a haber duplicados, lo aprovecharemos como clave primaria, pero si no lo hay, agregaremos uno "ad hoc" cuyo contenido sea preferentemente significativo, no demasiado largo y por supuesto, sin duplicados. Veremos esto a continuación y sobre la práctica de ejemplo.

Para nuestro ejemplo y de momento, construiremos 2 tablas de dimensión:

1) La tabla de trabajadores será una de las dimensiones que defina quién está detrás de cada nómina y los datos íntimamente relacionados con cada trabajador (fecha de nacimiento, situación laboral, etc.)

ID Nombre trabajador: Apellidos y Nombre del trabajador, que será la clave primaria (ID), puesto que es imposible que nos veamos obligados a poner un mismo nombre a 2 trabajadores de una misma empresa. Es decir, no habrá duplicados.
Fecha nacimiento: Fecha de nacimiento del trabajador.
Situación: Alta, Baja o Enfermedad.
Ultima fecha: Última fecha de cambio de situación.

2) La tabla de cuentas de tesoreria.

ID Tesorería: Código de la cuenta de tesorería. Este campo se ha creado "ad hoc" porque es ciertamente posible que una empresa pueda tener dos cuentas de tesorería en una misma entidad financiera, o bien que maneje varias cuentas de efectivo de caja (como es el caso del ejemplo). Por lo tanto, el nombre de la tesorería (que es el siguiente que explicamos) no nos sirve como clave primaria.
Nombre tesorería: Nombre de la entidad financiera o de la cuenta de efectivo.
Cuenta: Nº de cuenta de tesorería.

En el siguiente post, nos adentramos algo más en otras teorías del Business Intelligence para conocer las principales formas (esquemas o diagramas) que hay de relacionar las tablas que hemos creado. Concretamente veremos el Esquema en Estrella y el Esquema en Copo de Nieve. Previamente se estudiarán las claves que debe contener una tabla de hechos (claves externas).

Artículos relacionados:

Modelización de datos en Excel
Modelización de datos en Excel (3)
Modelización de datos en Excel (4)
Modelización de datos en Excel (5)

José Manuel Pomares Medrano

Modelización de datos en Excel

2011-09-22T11:50:00.000-07:00

Iniciamos con este post, una serie de 5 artículos (quizás los más importantes de este blog y los que primero deberían ser leídos porque componen la base que todo usuario de Excel debería, al menos, conocer mínimamente), dedicados a conocer la forma ideal de organizar y estructurar los datos (modelizar) en un libro de excel con el fin de evitar los errores que más frecuentemente se comenten a la hora de diseñar una aplicación de Excel en el ámbito de la Dirección Financiera, Comercial o en cualquier otro contexto.

¿Cuales son los problemas más habituales al diseñar nuestras hojas de cálculo?

Cuando detectamos la necesidad de diseñar una aplicación en Excel para gsetionar determinados procesos en cualquier área de una empresa, nos abalanzamos sobre la Hoja1 en blanco que genera automáticamente Excel al abrirlo y empezamos a "pintar"aquello que nosotros queremos ver en un momento dado. Una vez hecho, hacemos doble click en la pestaña de la hoja y le asignamos un nombre, por ejemplo: "Factura Enero" o bien "Nóminas Enero". La imagen de lo descrito, bien podría ser similar a este sencillo ejemplo:

Tenemos la sensacion de que está todo controlado hasta que llega Diciembre y necesitamos sumar todos los totales mensuales para obtener el anual. Para ello estamos obligados a crear una nueva hoja consolidando todo. Aun así, nos llegarán más problemas con esta estructura de datos en la que cada mes corresponde a una hoja:

¿Y si el gerente nos pide los totales entre Julio de un año y Junio del siguiente año... tendremos que construir otra tabla para consolidar todos los meses?
¿Y si queremos saber los totales por trabajador... también tendremos que construir otra tabla a tal efecto?
¿Y si el Director de Producción nos pide un resumen de los sueldos de todos los trabajadores de su departamento, de los últimos 5 meses... tendremos que diseñar otra tabla para poder satisfacer la necesidad del responsable del área productiva?
¿Y si queremos saber el IRPF trimestral... tendremos que construir otra tabla para ello?
¿Y cuando llevemos 3 años acumulados... tendremos que manejar 36 pestañas (una por cada mes)?

De esta forma, nos pasaremos los días continuamente construyendo informes y más informes con hojas y más hojas en un libro... siempre copiando y pegando en el mejor de los casos. (por no hablar de algunos usuarios que agravan hasta el límite todo lo comentado, alojando en cada libro o archivo un año completo... o peor aun en cada libro o archivo un mes sólamente... o mucho peor..¡¡¡¡Horror!!!! en cada archivo, una factura)

La mayoría de las veces, se da la circunstancia de que hay que escribir una y otra vez un mismo nombre de cliente, trabajador, etc. o actualizar una fecha en cada hoja o en cada tabla, con la consecuente pérdida de tiempo y de fiabilidad, puesto que todo esto se presta a multitud de errores y olvidos.

¿Por qué ocurre esto?

Todos estos errores ocurren porque a la hora de diseñar nuestro trabajo en Excel, lo hacemos pensando gráficamente en lo que necesitamos al final (una tabla con unos determinados campos) y simple y directamente lo plasmamos construyendo una tabla a imagen y semejanza de nuestro primer pensamiento. Es decir, empezamos construyendo al principio lo que queremos como objetivo final. Como dice el refrán: pan para hoy (rapidez) y hambre para mañana (lentitud, problemas y errores): la arquitectura de los datos no es la correcta para que pueda ser consultada de forma dinámica y desde varios puntos de vista o dimensiones.

¿Qué es modelizar los datos?

Modelizar significa organizar todos los datos que tenemos a nuestra disposición, agrupándolos según unos criterios determinados, mediante tablas. Cada grupo de datos (tabla) contiene uno o varios campos que tienen alguna relación con algún/os campo/s de otras tablas.

La dificultad y el "arte" de modelizar estriba en decidir qué datos pueden y deben estar agrupados en una misma tabla y, posteriormente, decidir como se van a relacionar estas tablas. Para ello, entre otros requisitos, es fundamental conocer a fondo la empresa y el contexto de necesidades que mueve a diseñar una aplicación en Excel.

¿Como se modelizan los datos?: Este es el principal contenido de nuestro siguiente artículo...

José Manuel Pomares Medrano

Cómo automatizar una acción al abrir Excel

2011-09-17T07:33:00.001-07:00

Es muy frecuente que cada vez que entramos a una aplicación de Excel , tengamos que realizar una o varias acciones siempre de la misma forma. Por ejemplo, seleccionar una hoja, poner la fecha del día en un determinado campo, mostrar un cuadro de diálogo, etc.

En este artículo, vamos a ofrecer unos primeros pasos muy sencillos en programación Visual Basic for Appliations (VBA) en Excel, para conseguir que al abrir Excel, se ejecute la acción deseada.

Pongamos un sencillo ejemplo, en el que deseamos que al abrir Excel se realicen las siguientes acciones:

Seleccionar la Hoja3.
Introducir la fecha del día en la celda A1 de la Hoja3.
Activar el modo Pantalla completa de Excel.

(Además, deseamos que el usuario no perciba en pantalla que se realiza ninguna de las dos primeras acciones)

Es posible conseguir esto de dos formas. En este artículo lo vamos a hacer creando un procedimiento para el evento "Open" de un libro (también se puede hacer creando una macro y asignándole el nombre de "Auto_open", aunque esta opción puede tener alguna incompatibilidad en algunas circunstancias: Limitaciones de una macro Auto_open.)

En resumen, el proceso es decirle a Excel mediante código VBA, que al abrir el libro (Workbook), ejecute las instrucciones que nosotros queramos. Esto se hace introduciendo dicho código con las instrucciones, en la colección de objetos "ThisWorkbook" (que también está considerado como un objeto) e indicando que ocurran cuando se abra (Open). Veamoslo paso a paso:

1) Creamos un nuevo libro de Excel e inmediatamente guardamos el archivo dándole el nombre que deseemos (en la imagen de abajo se llama "Libro1") y seleccionando el tipo de archivo: "Libro de Excel habilitado para macros". Hay que recordar que si deseamos hacer este ejercicio en un archivo que ya teníamos, este debe estar guardado con el formato anteriormente mencionado (habilitado para macros).

2) Hacemos click en la Ficha "Programador" (si no la tenemos activada y no sabemos como hacerlo, ver: La ficha "Programador") y después en Visual Basic (a la izquierda). Con esto, se abre el editor de Visual Basic y deberíamos ver el panel "Explorador de proyectos" a la izquierda. (si no es así, podemos abrirlo con Ctrl+R o bien desde el comando "Explorador de proyectos" del menú "Ver").

3) Dependiendo de los complementos y archivos de Excel que tengamos habilitados y abiertos, veremos más o menos "Proyectos" en el panel abierto en el punto 1), pero entre ellos, debe estar el del archivo de Excel al que queremos asignar el código. Por ejemplo:

4) Como vemos en la imagen anterior, aparece la colección de objetos "ThisWorkbook", como un objeto más dentro de "Microsoft Excel Objetos". Pues debemos hacer doble click en "ThisWorkbook" para abrir a la derecha la ventana de código y seleccionar en el desplegable de la izquierda (Objetos) "ThisWorkbook" y en el desplegable de la derecha (Procedimientos) "Open", obteniendo lo siguiente:

5) Vemos que automáticamente se escriben las instrucciones Sub y End Sub (además de otras palabras clave y parámetros) como inicio y final del procedimiento. Nuestro código debemos situarlo entre las dos líneas que se han generado automáticamente y por lo tanto, debe quedar todo así:

Private Sub Workbook_Open()

Application.ScreenUpdating = False

Sheets("Hoja3").Select

Range("A1") = Now()

Application.DisplayFullScreen = True

End Sub

6) Cerramos el editor de Visual Basic, cerramos y guardamos el libro al salir y volvemos a abrir el libro. Podemos comprobar que, automáticamente y sólo con la apertura del archivo, Excel selecciona la Hoja3 (Sheets("Hoja3").Select), escribe en la celda A1 la fecha y hora actual (Range("A1") = Now()) y además lo visualizamos a pantalla completa (Application.DisplayFullScreen = True). También hemos podido comprobar que, de todo el proceso, sólo vemos el resultado final gracias que Excel no va actualizando en pantalla lo que va procesando interiormente (Application.ScreenUpdating = False)

(Sólo tenemos que presionar ESC para salir del modo pantalla completa)

Esto es tan sólo un ejemplo sencillísimo, pero basta con escribir un código diferente diseñado con alguna finalidad y situarlo en el lugar explicado (entre las instrucciones Sub y End Sub), para obtener la automatización de una tarea.

En siguientes artículos pondremos más ejemplos de código. También en xltoday.net hay muchos ejemplos de código VBA para copiar y pegar en nuestro "Thisworkbook", así como en Cyta.com y en Ron's Excel Tips .

José Manuel Pomares Medrano

La función DESREF en Excel

2011-09-17T07:31:00.001-07:00

Cuando queremos hacer referencia a una celda o rango de celdas, conocemos varias posibilidades de hacerlo pero, ¿qué ocurre si dicha celda o rango puede variar de posición o de tamaño?

En el siguiente enlace, se puede descargar un archivo de Excel donde podemos encontrar el ejercicio que seguiremos en este tutorial (Ejercicio 1) y otro ejercicio muy útil en la práctica (Ejercicio 2) donde se hace una lista desplegable, cuyo origen de datos es un rango variable generado por la función DESREF.

Por ejemplo, si un rango está compuesto por una lista de artículos de almacén, es normal que varíe su tamaño, pues los artículos del almacén de una empresa aumentan o disminuyen.

Por otro lado, un rango que varía de posición y tamaño, pudiera ser uno que se refiera a un período de fechas que varía a elección del usuario (por ejemplo, mediante unas Listas desplegables). Por ejemplo, seleccionar un período entre Enero y Marzo....

Rango 1

o entre Febrero y Junio:

Rango 2

En estos casos, si la celda o el rango puede tener posiciones y tamaños distintos (como los aludidos anteriormente con los ejemplos), hay una forma de hacer referencia a ellos mediante la función DESREF. Esta función no es del todo fácil de entender, precisamente por que alude a referencias que varían en el espacio y es necesario entender cómo se manejan "las coordenadas".

La función DESREF devuelve una selección de celda o rango, cuya situación se define con las coordenadas que se escriben en parámetros separados por el signo punto y coma. La sintaxis de la función es:

Por ejemplo, si quisieramos sumar los importes del Rango 1 de la imagen anterior, deberíamos escribir:

=SUMA ( DESREF (A1;1;1;1;5) )

Donde... se suma un rango cuyo extremo superior izquierdo empieza a definirse 1 fila más hacia abajo y 1 columna más a la derecha comenzando a contar desde la celda A1 (celda de referencia). Por último, los últimos parámetros significan que el rango tiene 1 fila de alto y 5 columnas de ancho.

(Es necesario saber que los parametros de "filas" y "columnas" pueden ser positivos o negativos (cuando son negativos, el nº de filas se cuenta hacia arriba y nº de columnas se cuenta hacia la izquierda).

Una vez entendido esto, nos damos cuenta de que, hasta ahora sólo hemos conseguido definir un rango según unas "coordenadas" fijas. Pero lo interesante es que algunas de las coordenadas varíe en función de alguna variable que podamos definir como usuario (en definitiva, que el rango sea dinámico). Por ejemplo, podemos cambiar en la fórmula anterior:

=SUMA ( DESREF (A1;1;B4-1;1;B5) )

... con lo que conseguimos que el rango tenga su inicio en una celda que depende del valor de otra celda en la que nosotros podemos introducir un valor a nuestra elección:

Ejercicio 1

Vemos en el Ejercicio 1 que, poniendo el nº de mes en B4 y la cantidad de meses que deseamos en B5, obtenemos la suma del rango en B6. Este es un ejemplo muy simple y con fines didácticos, pero no demasiado aplicable en la práctica.

José Manuel Pomares Medrano

Listas desplegables en Excel (4)

2011-09-17T03:13:00.001-07:00

Después de conocer el resto de formas de generar una lista desplegable en Excel en los anteriores artículos de esta serie, nos adentramos en la última de las posibilidades, que es conseguir una lista desplegable (en este caso, Excel lo llama "Cuadro combinado") desde el comando "Insertar" que hay en el grupo "Controles" de la Ficha "Programador".

(si en su aplicación Excel no está visible la Ficha "Programador", vea el artículo: La Ficha "Programador" en Excel)

Una vez hacemos click en el control de formulario (segundo por la izquierda arriba), podemos "dibujar" el control donde queramos en nuestra hoja de cálculo, con tan solo arrastrar en diagonal de arriba a abajo y de izquierda a derecha.

Podemos ver ejemplos de cuadros combinados en esta imagen del CME Pymes:

Dos son las características que tenemos que resaltar de estas listas desplegables realizadas con cuadros combinados en Excel:

La lista desplegable ofrece al usuario una serie de items a elegir y, una vez realizada la selección, el valor no queda introducido en ninguna celda, tan sólo es presentado en pantalla por el cuadro combinado. Lo que sí hace la lista desplegable es generar un número que asocia al item elegido (asocia un número de orden a cada uno de los items que presenta la lista desplegable) y lo introduce en una celda que nosotros elegimos. Una vez ocurrido esto, podemos condicionar cualquier objeto, acción, cálculo, etc. al número asociado al item que se ha elegido. En pocos casos esto constituye una ventaja frente a las listas desplegables generadas con "Validación de datos".

Los cuadros combinados pueden colocarse en cualquier posición de una hoja de cálculo, inlcuso encima de gráficos, imágenes o formas.

Se puede elegir si se imprimirá o tan sólo lo veremos en pantalla.

Ofrecen la posibilidad de que si se insertan filas o columnas, el cuadro combinado se desplace (quedando ligado a una celda concreta) o que mantenga su posición respecto sin desplazarse.

Es posible elegir la cantidad de items que se mostrarán sin necesidad de utilizar una barra de desplazamiento (en las listas desplegables realizadas con "Validación de datos" se muestran hasta 8 items sin necesidad de recurrir a la barra de desplezammiento. Si hay más items, es necesaria dicha barra))

Todo lo anterior es posible configurarlo haciendo click con el botón derecho en el cuadro combinado, eligiendo "Formato de control..." y seleccionando/cumplimentando las opciones adecuadas en el cuadro de diálogo que aparece:

Después de todo lo anterior, queda claro que utilizar un cuadro combinado para generar una lista desplegable debe estar motivado principalmente por cuestiones "gráficas" y de posición.

Ver Listas desplegables en Excel

Ver Listas desplegables en Excel (2)

Ver Listas desplegables en Excel (3)

José Manuel Pomares Medrano

Nombres de celdas o de rangos en Excel

2011-09-16T03:10:00.000-07:00

Cuando tenemos que referirnos al contenido que hay en una determinada celda o en un grupo de celdas, lo podemos hacer escribiendo su referencia. Por ejemplo, =Hoja!B1 (en el caso de la celda B1 que está en la Hoja1) o por ejemplo =B1:B5 (en el caso de un rango de celdas que está en la hoja actual).

A veces, hacer estas referencias de la forma que todos conocemos (haciendo click en la celda o seleccionando el rango deseado mientras escribimos una fórmula en una celda) es rápido y eficiente, pero en otras ocasiones, es posible que aparezcan algunos inconvenientes:

Hacer referencia a una celda o rango seleccionándola directamente allí donde se encuentre, pudiera ser más molesto o lento de lo deseado.
Si tenemos que revisar una fórmula que tiene referencias a una celda o a un rango, probablemente no vamos a reconocer a primera vista que tipo de datos contiene esa referencia. Por ejemplo, la fórmula =B4 + C4 no nos dice que contienen las celdas B4 y C4.

Para solucionar esto, existe la posibilidad de asignar un nombre descriptivo a las celdas o rangos.

Siguiendo el ejemplo del último punto anterior y suponiendo que B4 contiene una base imponible y que C4 contiene el IVA, podemos asignar nombres a B4 y a C4, de forma que podamos relacionar esas celdas con sus conceptos cuando tengamos que revisar la fórmula. De esta forma y una vez asignados los nombres "Base" e "IVA", para sumarlos en una fórmula sólo tenemos que poner: =Base+IVA.

De la misma forma, si tenemos un rango donde aparecen las ventas realizadas por una serie de vendedores, por ejemplo, F5:F24, podemos asignarle el nombre Ventas y en adelante cuando tengamos que operar con el rango sólo tenemos que poner directamente (por ejemplo) =SUMA(Ventas) o PROMEDIO(Ventas), etc.

José Manuel Pomares Medrano

Listas desplegables en Excel (3)

2011-09-15T13:02:00.001-07:00

Además de la opción de generar listas desplegables vista en el post "Listas desplegables en Excel (2)" , tenemos la posibilidad de generarlas mediante el cuadro de diálogo "Validación de datos" que está en la ficha "Datos" de la cinta de opciones y seleccionando en el campo "Permitir:" la opción "Lista":

Con esta opción, tenemos las siguientes ventajas:

Los items de la lista desplegable pueden ser:

Elegidos arbitrariamente por nosotros e introducidos a mano, separados por el signo punto y coma, en el campo "Origen:" del cuadro de dialogo de "Validación de datos".

Elegidos de un rango de celdas de la hoja activa o de otra hoja (en Excel 2003 sólo de la hoja activa).

Elegidos de un rango variable (con más o menos items) mediante la función DESREF. Es decir, podemos tener una lista desplegable con los nombres de nuestros clientes, pero si añadimos un cliente, automáticamente aparecerá después en nuestra lista desplegable.

Elegidos de un rango al que le hemos asignado un nombre (Ver: Nombres de celdas o de rangos en Excel). Si utilizamos esta opción para el origen de los items, podemos permitir que se introduzcan valores que no están incluidos en la lista siempre y cuando:

Se active la casilla "Omitir blancos".
En el rango exista una celda en blanco al menos.

Las listas desplegables realizadas con "Validación de datos" se crean en una celda y el valor elegido queda introducido en ella directamente.
Podemos utilizar mensajes informativos para que el usuario tenga información, por ejemplo, de que información debe introducir en la celda. Esto se consigue en la pestaña "Mensaje de entrada" del cuadro de diálogo que ya conocemos de "Validación de datos":

Con el resultado de...

Podemos utilizar mensajes de error para el caso de que el usuario no seleccione correctamente un item o bien intente introducir un valor no adecuado. Esto se consigue de forma muy similar al mensaje de entrada, pero en la pestaña "Mensaje de error" del cuadro de diálogo que ya conocemos de "Validación de datos".
Si deseamos, por ejemplo, que una tabla contenga listas desplegables en todos las celdas de una columna, podemos replicar las listas desplegables tan sólo con utilizar "Autorrellenar".

Ver Listas desplegables en Excel
Ver Listas desplegables en Excel (2)

José Manuel Pomares Medrano

Listas desplegables en Excel (2)

2011-09-14T13:41:00.001-07:00

Desarrollamos con más detalle y otra metodología (video-tutorial) la primera de las clases de listas desplegables que podemos utilizar en Excel.

Nos referimos a las listas desplegables generadas mediante el menú contextual que obtenemos con el botón derecho del ratón:

Cuando tenemos una serie de items introducidos en un rango de celdas a lo largo de una columna (de forma similar a la imagen anterior), Excel reconoce la posibilidad de que el usuario pueda necesitar introducir algunas veces más alguno de los items ya utilizados. De esta forma, cuando hacemos click con el botón derecho en la celda inmediatamente inferior a la última de la lista, obtenemos un menú contextual como el siguiente:

Podemos ver que la antepenúltima opción es "Elegir de la lista desplegable...". Una vez que la seleccionamos, obtenemos una lista de los items que hay en las celdas inmediatamente superiores a la celda donde hemos obtenido el menú desplegable.

Tenemos que tener en cuenta que:

Si la lista que ya tenemos en Excel tiene algún item repetido, sólo lo muestra una vez en la lista desplegable.
La lista desplegable que generamos, sólo contiene items que ya hayan sido introducidos en la lista que tenemos ya en Excel.
Si la lista que ya tenemos en Excel está fraccionada (no tiene todos los items seguidos), sólo aparecerán en la lista desplegable los items contiguos del último tramo de más abajo.
Si hacemos click en otra celda que no sea la inmediatamente inferior a la lista que ya tenemos en Excel, no se generará ninguna lista desplegable.
Si la lista que ya tenemos en Excel contiene items de fechas o números, estos no se incluirán en la lista desplegable. Sólo se inlcuyen en las listas desplegables generadas mediante el menú contextual, los items que son cadenas de texto (o texto mezclado con números).

Veamos un video-tutorial con todo esto:

Ver Listas desplegables en Excel
Ver Listas desplegables en Excel (3)

José Manuel Pomares Medrano

La ficha "Programador" en Excel

2011-09-14T11:50:00.001-07:00

Muchas de las opciones avanzadas que tenemos a nuestra disposición en Microsoft Excel, están en la ficha "Programador". Entre las principales, se encuentran aquellas que están referidas a la automatización de tareas (macros), programación en Visual Basic for Applications (VBA) y utilización de controles de formulario (botones, cuadros combinados, listas desplegables, casillas de verificación, etc.).

Si su aplicación Excel 2007 o 2010 no tiene la ficha "Programador" se debe a que no queda habilitada durante la instalación, pero puede habilitarla para que quede visible:

En Excel 2007, debemos hacer click en el Botón de Office / Opciones de Excel / Más frecuentes / Mostrar ficha de programador en la cinta de opciones.

En Excel 2010, debemos hacer click en Archivo / Opciones / Personalizar cinta de opciones . Una vez obtenemos el cuadro de diálogo, seleccionamos "Fichas principales" de la lista desplegable que hay arriba a la derecha y después activamos la casilla "Programador" que hay en el panel de Fichas Principales:

En el caso de Excel 2003 no existe ficha "Programador" puesto que no hay cinta de opciones, pero a la mayoría de comandos podemos acceder desde el menú Herramientas / Macro... y también haciendo click en el menú Ver / Barras de herramientas / Cuadro de controles

José Manuel Pomares Medrano

Listas desplegables en Excel

2011-09-14T10:48:00.001-07:00

Una lista desplegable es una herramienta que proporciona Excel, mediante la cual, se nos permite elegir entre una serie de items (lista de palabras, frases, fechas o números).

Cuando creamos una lista desplegable, los items que deseamos poder elegir más adelante, pueden tener distintos orígenes: introducirse en manualmente separados por el signo de punto y coma, tener origen en un rango de celdas de la hoja de calculo donde se encuentra la lista desplegable (en la versión 2003) o de cualquier otra hoja (versiones 2007 y 2010), o bien tener origen en unos valores ya introducidos en alguna lista que tengamos hecha.

Hay 3 tipos de listas desplegables en Excel. Los 2 primeros tipos que vamos a ver tienen como finalidad la introducción del dato elegido, en una celda. El último tipo de lista desplegable que veremos, introduce en una celda un número asociado al dato elegido, quedando este presentado gráficamente en un control.

Listas desplegables que se generan automáticamente desde el menú contextual que obtenemos al hacer click con el botón derecho del ratón y seleccionando "Elegir de la lista desplegable...". Tienen una utilidad limitada, aunque se generan de forma rápida y espontánea partiendo de una lista ya introducida en celdas.

Si desea saber más detalles de cómo utilizar este tipo de listas desplegables, vea el artículo:

Listas desplegables en Excel (2)

Listas desplegables que se generan de forma personalizada desde el comando "Validación de datos" que está en la ficha "Datos" de la cinta de opciones de Excel. Es la forma más versátil de generar listas desplegables, pues se generan desde una celda, dejando el dato seleccionado como introducido en ella y pudiendo ser replicadas mediante autorrellenar o mediante copiar y pegar (por ejemplo). Además, el origen de los datos que obtenemos en la lista, desde la versión 2007 en adelante, puede estar en otras hojas de cálculo y no sólo en la hoja en la que se presenta la lista desplegable. Como desventaja, tenemos que no se puede colocar en cualquier lugar (por ejemplo encima de un gráfico), puesto que como hemos dicho, la propia lista desplegable ES una celda y nace de una celda.

Si desea saber más detalles de cómo utilizar este tipo de listas desplegables, vea el artículo:

Listas desplegables en Excel (3)

Listas desplegables generadas a partir de un control de formulario (llamadas "Cuadros combinados"), desde el comando "Insertar" que hay en el grupo "Controles" de la Ficha "Programador". (Si su aplicación de Excel no tiene la ficha de "Programador", para habilitarla puede ver: La ficha "Programador" en Excel . Una de las características principales de estas listas creadas mediante el control de formulario "Lista Desplegable", es que el item que se elige no queda almacenado tal cual en una celda, sino que lo que se almacena es el número de orden del item en la celda que nosotros indicamos al insertar el control. Por lo tanto, es necesario indicar el rango de origen de los items y también la celda en la cual queremos que se almacene el número de orden asociado del item elegido. En cuanto a las ventajas (frente a las listas realizadas mediante "Validación de datos") está el hecho de que, al ser un control de formulario, se puede mover y situar libremente por toda la hoja de cálculo, incluso encima de imágenes, gráficos, etc.

Si desea saber más detalles de cómo utilizar este tipo de listas desplegables o cuadros combinados, vea el artículo:

Listas desplegables en Excel (4)

José Manuel Pomares Medrano

Autorrellenar en Excel (2)

2011-09-04T10:19:00.000-07:00

Supongamos que constantemente es necesario introducir una lista de nombres o números que no responden a ninguna de las series reconocidas por Excel (como pudimos ver en Autorrellenar en Excel ).

Un ejemplo de ello podría ser la siguiente lista:

Si necesitamos introducir una y otra vez esta lista, podemos crear una lista personalizada para que, tan sólo con introducir uno de los items (preferentemente el primero que es "Aragón"), podemos proceder a "Autorrellenar" (de la forma que se mostró en el artículo precedente a este Autorrellenar en Excel ) arrastrando de un modo u otro (filas o columnas) para que se complete automáticamente la lista.

Puesto que el preoceso de "Autorrellenar" ya se vio, a continuación mostramos el proceso para que Excel reconozca nuestra lista personalizada.

Hacemos click en Archivo / Opciones / Avanzadas / General / Modificar listas personalizadas...

y obtenemos el siguiente cuadro:

A partir de aqui, podemos introducir una lista personalizada escribiendo directamente en "Entradas de lista", separando con comas cada uno de los items que componen nuestra lista.

Si la lista es muy extensa y su introducción resultará tediosa y/o con riesgo de errores, tenemos la opción de importarla de alguna hoja de cálculo donde la tengamos escrita. Todo ello se puede conseguir:

Haciendo click en el botón de la derecha del campo que hay junto al botón "Importar"
Seleccionado posteriormente el rango de celdas donde se encuentra y presionando "Enter.
Haciendo click en "Importar"

Una vez realizado esto, podemos ver que nuestra lista se incorpora a las ya existentes en el cuadro de la imagen anterior, quedando aproximadamente como sigue:

Una vez llegados aquí, cada vez que se introduzca "Aragón" y hagamos uso de "Autorrellenar", extenderemos el resto de items a lo largo del rango que deseemos. También podemos introducir cualquiera de los items para emepezar a "Autorrellenar" desde el centro de la lista. Veamos un video-tutorial con todo ello:

José Manuel Pomares Medrano

Autorrellenar en Excel

2011-09-04T09:37:00.000-07:00

Cuando necesitamos introducir una serie de datos (números, textos, palabras, etc.) que tienen una relación entre si (en Excel se llaman "listas") podemos hacer más rápido el proceso utilizando la herramienta "Autorrellenar" de Excel.

Veamos una demostración con un viedo-tutorial:

Por defecto y con la propia instalación del programa ya hay configuradas unas cuantas listas, preparadas para cuando utilicemos "Autorrellenar". Concretamente, son las cuatro que hemos visto en el video-tutorial:

Días de la semana con formato corto (lun)
Días de la semana con formato largo (lunes)
Meses del año con formato corto (ene)
Meses del año con formato largo (enero)

Es posible añadir y modificar listas personalizadas para optimizar tiempo en la introducción repetitiva de listas de datos. Para ello, remitimos al artículo que trata de ello:

Ver Autorrellenar en Excel (2)

José Manuel Pomares Medrano