Introducción de datos en Excel con un cuadro de diálogo

Veamos en este artículo una forma alternativa de introducir datos en Excel. A veces, al usuario le puede resultar un incordio o muy complicado introducir un valor en determinada celda de nuestro libro de Excel y necesita una forma de "canalizar" dicha introducción de datos. Las razones podrían ser:

1. La celda está en una hoja distinta a la que se encuentra el usuario cuando necesita introducir el dato. De esta forma, el usuario tendría que activar otra hoja, seleccionar la celda adecuada, introducir el dato, presionar "Intro" y activar de nuevo la hoja donde se encontraba.
2. La celda se encuentra en la misma hoja, pero es necesario realizar un scroll de la pantalla muy largo y tedioso hasta encontrar dicha celda y posteriormente volver a la situación inicial posiblemente con el mismo scroll.
3. La celda se encuentra oculta tras un objeto (forma, imagen, gráfico, etc.) o bien tiene un formato que hace imposible su distinción (color de fuente igual al color de fondo, por ejemplo).
4. El valor que deseamos que aparezca en la celda es distinto al que conoce el usuario. Por ejemplo,  el usuario podría conocer una base imponible y nosotros desear que el valor introducido finalmente en la celda objetivo sea baseimponible*IVA.
5. El valor debe cumplir con alguna condición. Por ejemplo, podríamos impedir al usuario que continúe utilizando la aplicación de Excel, obligándole a que introduzca un dato "sí o sí".
6. Incluso es posible que la pretensión no sea que el dato a introducir se visualice, sino que solo es necesario a nivel interno de Excel para algún proceso.

La solución a estas eventualidades es ofrecer al usuario que el dato lo introduzca cómodamente en un cuadro de dialogo que emerge en la pantalla y que Excel (mediante código VBA) se encargue de "llevar" ese dato a la celda correspondiente, allá donde esté.

Esto se consigue mediante la función de VBA: Inputbox, cuya sintaxis es:

InputBox(Mensaje, Título, Valor por defecto, posición x, posición y)

Mensaje = Mensaje que vemos justo encima del campo donde el usuario introduce el valor.

Título = Título del cuadro de diálogo que se muestra en la barra superior de este.

Valor por defecto = Valor que se muestra en el campo donde el usuario introduce el valor y que se considerará por defecto en el caso de que no se introduzca expresamente ningún valor.

Posición x = nº de twips que el cuadro se situará hacia la derecha, desde el borde izquierdo de la pantalla (15 twips = 1pixel).

Posición y = nº de twips que el cuadro se situará hacia abajo, desde el borde superior de la pantalla (15 twips = 1pixel). 

A continuación proponemos un ejemplo de código para cada caso de los que enumeramos al principio, donde el lector podrá personalizar el código que aparece en verde, según sus necesidades. Excepto en el ejemplo nº 5, el código se debe escribir en un Módulo ya existente o en uno nuevo (para insertar un módulo, ir al Editor de VBA (Alt+F11) y después hacer click en el menú Insertar / Módulo.) y se debe ejecutar, por ejemplo, asignando la macro resultante "EntradaDatos" a un botón o activando la ficha "Vistas" / Grupo "Macros" / "Ver Macros" / "Ejecutar".

Caso 1: Introducción de un texto (sin condiciones) desde un cuadro de diálogo en la hoja3, estando en la hoja1.

Sub EntradaDatos()
    Dim Entrada As String
    Entrada = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Introduzca su nombre", "Nombre", "Introduzca aquí su nombre", 1000, 1000)
    If Entrada = "Introduzca aquí su nombre" Then Entrada = ""
    Sheets("hoja3").Range("A1") = Entrada
End Sub

Caso 2: Introducción de un texto (sin condiciones) desde un cuadro de diálogo en la celda A1000 de la hoja1, estando en la celda A1 de la hoja1.

Sub EntradaDatos()    Dim Entrada As String
    Entrada = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) &  "Introduzca su nombre", "Nombre", "Introduzca aquí su nombre", 1000, 1000) 
    If Entrada = "Introduzca aquí su nombre" Then Entrada = ""
    Range ("A1000") = Entrada
    Range ("A1").Select
End Sub

Caso 3: Igual al anterior.

Caso 4: Introducción de un número que es una base imponible en un cuadro de diálogo, para que Excel convierta dicha base imponible en el TOTAL (Base Imponible + IVA). 

Sub EntradaDatos()
    Dim Entrada As Double
    Entrada = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Introduzca la base imponible", "Convertir Base imponible en TOTAL", 0, 1000, 1000)
    Range("A5") = Entrada * 1.21
End Sub

Caso 5: Al abrir el archivo, el usuario debe introducir algo en el campo del cuadro de diálogo. Mientras no sea así, el cuadro no se puede cerrar. Si el valor introducido no coincide con una contraseña, Excel se cierra sin otra posibilidad. Si este código se introduce en el objeto "Thisworkbook" con el evento "open", hace un efecto similar al cifrado del archivo mediante contraseña.

Private Sub Workbook_Open()
Dim Entrada As Variant
Do
    Entrada = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Introduzca la contraseña", "Contraseña", "Introduzca aquí la contraseña", 1000, 1000)
Loop Until Entrada <> ""
If Entrada <> "temporaexcel" Then
    ThisWorkbook.Close True
End If

End Sub

Si queremos ver un archivo con los ejemplos 4 y 5, podemos descargarnos el libro Excel:

Si deseamos saber cómo proteger nuestro código escrito en VBA, visitar este artículo:

Protección del código VBA en Excel

  José Manuel Pomares Medrano

Cómo personalizar el menú del botón derecho de Excel con VBA

Todos conocemos (y además usamos con mayor o menor frecuencia) el menú contextual que obtenemos al hacer clic en una celda de Excel. En la versión 2016, tiene el siguiente aspecto:

En este post, vamos a plantearnos modificar este menú contextual cuando trabajamos con una hoja de cálculo determinada. Concretamente queremos que, dentro de esa hoja, al hacer clic con el botón derecho en las celdas A1 y C1, el menú contextual lo veamos personalizado con unos comandos que nosotros decidiremos y que, evidentemente, ejecutarán las acciones que nosotros queramos.

Para ello, no tenemos más remedio que escribir algo de código Visual Basic for Applications. Así pues, lo primero que debemos hacer es presionar la combinación de teclas ALT+F11 para que se ejecute el editor de VBA.

Escribimos el código que permite que, al hacer clic con el botón derecho del ratón, el menú contextual esté personalizado

Una vez dentro del editor de VBA, nos fijaremos en la parte superior izquierda del editor, en el panel "Explorador de proyectos". Ahí podremos ver los archivos (proyectos para VBA) de Excel que están abiertos y debemos identificar el nuestro y expandir el árbol de objetos si es que no lo está.

Como vemos arriba, dentro de "Microsoft Excel Objetos" vemos la Hoja1 que será nuestra hoja objetivo a la hora de personalizar el menú contextual del botón derecho. Debemos hacer doble clic en nuestra hoja objetivo y veremos como a la derecha tenemos una ventana en blanco. En ella debemos realizar dos selecciones en la parte superior:

 

Podemos borrar todo lo que vemos en la parte inferior de la imagen anterior:

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)

End Sub

... y para que nuestro código se ejecute al de hacer clic con el botón derecho del ratón, entre la línea que comienza por Private Sub WorkSheet_BeforeRightClick (ByVal Target.... y la línea End Sub escribimos el siguiente código:

Dim NuevoComando As CommandBarButton

'LIMPIAMOS TODOS LOS COMANDOS Y REESTABLECEMOS ORIGINALES DE EXCEL

For Each AntiguoComando In CommandBars("cell").Controls
AntiguoComando.Delete
Next

CommandBars("Cell").Reset

'AÑADIMOS NUESTRO PRIMER COMANDO

If Not Intersect(Target, Range("A1")) Is Nothing Then
     Set NuevoComando = CommandBars("cell").Controls.Add
     With NuevoComando
         .Caption = "EJECUTAR MIMACRO1"
         .OnAction = "MiMacro1"
         .BeginGroup = True
     End With
End If

'AÑADIMOS NUESTRO SEGUNDO COMANDO

If Not Intersect(Target, Range("C1")) Is Nothing Then
     Set NuevoComando = CommandBars("cell").Controls.Add
     With NuevoComando
         .Caption = "EJECUTAR MIMACRO2"
         .OnAction = "MiMacro2"
         .BeginGroup = True
     End With
End If

Lo que hemos hecho hasta ahora es:

1) Activar la ventana de código y seleccionar el evento "BeforeRightClick" para escribir dentro de los protocolos de entrada y de salida que aparecen automáticamente (Sub.... y End Sub).

2) En el código que escribimos:

• Escribir el código que declara una variable objeto (NuevoComando) que representará el nuevo comando que se añade posteriormente

• Escribir la parte de código que limpia todos los comandos que pudiera haber anteriormente y volver a poner los comandos que por defecto muestra Excel al hacer clic con el botón derecho

• Escribir la parte de código que agrega nuestro primer comando. Este código se inserta dentro de una estructura If... End If que condiciona la agregación del comando al hecho de hacer clic en el "Target" que hemos definido (A1)

• Escribir la parte de código que agrega nuestro segundo comando. Este código se inserta dentro de una estructura If... End If que condiciona la agregación del comando al hecho de hacer clic en el "Target" que hemos definido (C1)

A estas alturas, lo que ocurre es que, si dejáramos esto así, al salir de nuestra hoja o al salir de Excel, esta configuración del menú del botón derecho del ratón permanecería para siempre en nuestro Excel... y eso no es, muy probablemente, lo que queremos. Así pues, debemos volver a seleccionar en la parte superior lo siguiente (teniendo el cursor en la línea siguiente a la última del código que hay hasta el momento. Es decir, después de "End Sub"):

 

En medio de las instrucciones que aparecen automáticamente (y que podemos ver en la imagen anterior, debemos escribir:

CommandBars("Cell").Reset
Application.CommandBars("Cell").Enabled = True

Escribimos el código de las macros que se ejecutarán al seleccionar los comandos personalizados de nuestro menú contextual

De nuevo en el explorador de proyectos debemos asegurarnos de que hay un módulo disponible para poder escribir el código de nuestras macros a ejecutar.

En la imagen siguiente vemos que ya está el Módulo 1:

Pero si no tuviéramos ningún módulo, lo podríamos añadir desde el menú "Insertar" / Módulo:

Cuando ya lo tengamos, hacemos doble clic sobre él y a la derecha aparecerá una ventana para escribir código, donde tendremos que escribir lo siguiente:

Sub MiMacro1()
     MsgBox "Se ha ejecutado mi macro 1"
End Sub

Sub MiMacro2()
    MsgBox "Se ha ejecutado mi macro 2"
End Sub

(Veremos que aparece una línea divisoria automáticamente)

Este código corresponde a dos macros de prueba que muestran un cuadro de mensaje para comprobar que se han ejecutado. Evidentemente, el usuario sustiruirá estas macros por las que realmente necesite ejecutar.

Ahora, ya estamos en disposición de comprobar el correcto funcionamiento de todo lo realizado, desde Excel. Es decir, haciendo clic con el botón derecho en cualquiera de las celdas que definimos al principio (A1 y C1):

Otras opciones

VER SOLO NUESTROS COMANDOS, NO LOS DE EXCEL

En al caso de que no deseemos que aparezcan los comandos propios de Excel y tan solo queremos ver nuestro comando, tenemos que quitar del código escrito dentro del evento "BeforeRightClick" la línea:

CommandBars("Cell").Reset

¡¡¡Insistimos!!! hay que eliminar la línea que hemos escrito dentro del evento  "BeforeRightClick"  ... ¡¡NO eliminar esa misma línea que también escribimos dentro del evento "Worksheet_Deactivate"!!

El resultado de eliminar esa línea es el siguiente:

 

Donde vemos que en el menú contextual aparece solo el comando que activa nuestra macro.

CON UN CLIC EN CUALQUIER CELDA, VER TODOS NUESTROS COMANDOS A LA VEZ SIEMPRE PARA PODER ELEGIR LA ACCIÓN:

En este caso, sea la celda que sea, al hacer clic con el botón derecho, veremos todos los comandos a la vez para poder elegir el que necesitemos. Para conseguirlo, debemos eliminar en el código las estructuras condicionales que comienzan por If y terminan por End If: 
 

 

 Siendo el resultado el siguiente:

 

 
Para finalizar, no hay que olvidar que el archivo debe ser guardado como... "Libro de Excel habilitado para macros (*.xlsm).  En el archivo que es posible descargar a continuación, podemos ver (incluso copiar) todo el código necesario, además de comprobar su funcionamiento:

El espacio de Mac

En cuanto a Excel 2011 para Mac, no hay diferencias en el código a escribir. Tan solo podemos remarcar las diferencias que hay a la hora de activar el editor de VBA. Concretamente, debemos activar la ficha Programador (Menú Excel / Preferencias / Cinta... y activar la casilla "Programador".

Después de aceptar, veremos la nueva pestaña en la cinta y activándola podremos acceder al editor de VBA:

José Manuel Pomares Medrano

Eliminar filas vacías en una hoja de Excel con VBA

En el último curso de Excel que he impartido, durante la sesión que trataba de Visual Basic for Applications (VBA), un alumno me solicitaba un procedimiento (macro o subrutina) para automatizar la eliminación masiva de filas que estuvieran totalmente en blanco. Además, necesitaba indicar las filas primera y última de la zona donde era necesario el "rastreo" y eliminación.

Voy a aprovechar la pregunta de este alumno para publicar en este post, una solución a dicho "problema".

Comencemos:

PASO 1:

Presionar ALT+F11 para activar el editor de VBA y hacer click con el botón derecho en la carpeta "Microsoft Excel Objetos" del proyecto o libro que deseemos (si lo hacemos en la carpeta "Microsoft Excel Objetos" del libro de macros PERSONAL.XLSB ** que podremos ver en el explorador de proyectos, tendremos la macro disponible permanentemente en nuestro Excel... y no sólo disponible para un libro en concreto). En el menú contextual, seleccionamos "Insertar / Módulo":

PASO 2:

En la ventana de código del nuevo módulo, que se abre a la derecha, escribimos el siguiente código (o copiamos-pegamos del archivo que puede el lector descargarse al final de este artículo):

Sub EliminarFilasVacías()

'Variables y actualización de pantalla
    Dim FilaInicial As String
    Dim FilaFinal As Integer
    Dim Seguridad As Integer
    Application.ScreenUpdating = False

'Introducción de fila inicial de rastreo
    On Error GoTo 1
    FilaInicial = InputBox("Introduzca nº de fila inicial para rastreo", "Fila inicial de rastreo")

    ActiveSheet.Range("A" & FilaInicial).Select

'Introducción de fila final de rastreo
    FilaFinal = Val(InputBox("¿Hasta que nº de fila quiere rastrear?", "Última fila a rastrear"))
    If FilaFinal = 0 Then
        GoTo 1
    End If

'Pregunta de seguridad
    Seguridad = MsgBox("¿Está seguro de eliminar todas las filas vacías?", vbYesNo, "¡Atención!")
    If Seguridad = vbNo Then
        Exit Sub
    End If
'Rastreo y eliminación de filas vacías
    For I = ActiveCell.Row To FilaFinal
        If Application.WorksheetFunction.CountA(ActiveCell.EntireRow) <> 0 Then
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.EntireRow.Delete
        End If
    Next
Exit Sub

'Gestión de errores
1 MsgBox "Ha introducido datos erróneamente o ha cancelado un cuadro de diálogo"

End Sub

Una vez hecho esto, cerramos el editor de VBA.

PASO 3:

Asignaremos la macro a una combinación de teclas. Para ello, en la Ficha Programador, hacemos click en el botón "Macros". Seguidamente, buscamos el nombre de nuestra macro (EliminarFilasVacías) TENIENDO SELECCIONADO EL LIBRO PERSONAL XLSB, y la seleccionamos. Después, hacemos click en el botón "Opciones..." y por último, introducimos la letra que deseamos acompañe a CTRL.

Por supueso, también podemos asignar la macro a un botón (por ejemplo) o a una forma.

Si desea ver en funcionamiento la macro y además poder copiarla a su libro de macros personal (en vez de escribirla), puede descargar el siguiente archivo:

** Recordamos que el libro de macros PERSONAL.XLSB es un libro que se abre de forma automática (en modo oculto) siempre y cuando exista en nuestro equipo. Para ello, tenemos que haber grabado alguna macro en él alguna vez, puesto que Excel lo crea automáticamente.


Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Cómo hacer un Dashboard económico-financiero con Excel (4)

Seguimos en este artículo con la planificación de un dashboard o cuadro de mando económico-financiero. Vamos a ver la forma en la que se organizan los datos en las distintas hojas de cálculo. Es decir, los modelos de datos.

Como resumen y adelanto a este artículo y los siguientes, podemos decir que tenemos que aprender a llegar hasta aquí (presentación)...

...pasando por aquí (análisis)...

...y empezando desde aquí (datos)....

Lo que acabamos de ver son 3 ejemplos de las 3 capas o fases del flujo de la información:

datos > análisis> presentación

o bien...

datos > información > conocimiento

Cuando necesitamos desarrollar una aplicación cuya utilidad última va a ser el análisis de datos, no podemos pretender hacerlo de una vez y en una sóla hoja de Excel. Debemos trabajar en distintas hojas "especializadas" porque ello dará orden y escalabilidad (posibilidad de ampliación y sostenibilidad a lo largo del tiempo). Además, utilizar una hoja más, es gratis en Excel. Remitimos al lector al artículo "Buenas prácticas: Estructura y tipos de hojas en Excel" especialmente al gráfico de pirámide y al punto nº 7.

En este artículo vamos a desarrollar la primera de las fases anteriormente mencionadas:

DATOS

La primera fase o estado en el que se encuentra la información se llama "fase o capa de datos" y puede tomar varias formas. Comenzamos por descubrir los modelos que pueden adoptar los datos en esta primera fase:, pero antes recordemos el concepto "Dimensión" a la hora de hablar de tablas y modelos de datos:

"Dimensión" es un criterio por el que podemos ordenar, clasificar o agrupar unos datos numéricos. Por ejemplo, si tenemos una tabla con los campos "Fecha", "Tipo de gasto" y "Euros", las dimensiones serán los campos "Fecha" y "Tipo de gasto", puesto que podremos ordenar por fechas (ya sea por días, meses, años, etc.) o clasificar/ordenar por tipo de gasto (Suministros, Reparaciones, Impuestos, etc.). 

Ahora si....vamos allá con los modelos de datos...

1) TABLAS UNIDIMENSIONALES

Son las resultantes cuando introducimos nuestros datos en una colección de tablas de una dimensión, donde cada tabla representa normalmente a un período de tiempo (no siempre). En las siguientes tablas, la única dimensión es la "Cuenta" (Ingresos, Compras, Servicios, etc.). Esta primera modalidad que comentamos es UN GRAVE ERROR... y es el error más habitual en el usuario. 

... también es muy habitual poner cada tabla en una hoja distinta a la cual se pone un nombre descriptivo ...

Si no acertamos en la forma en la que disponemos nuestros datos en esta fase, el resto de desarrollo de nuestro dashboard será un proceso arduo, muy poco eficiente y tremendamente limitado en sus posibilidades. La mayoría de usuarios de Excel no superan esta fase de forma óptima porque no conocen otras posibilidades y aplican la única que saben y que cumple con estas 3 condiciones:

• La disposición de los datos se hace de forma intuitiva y responde a corto plazo a algunas necesidades de información.

• El usuario sólo necesita utilizar los operadores básicos (+-*/) y poco más.

• Como consecuencia de los anteriores puntos, es rápida.

El motivo de considerar este tipo de organización de datos como un error, estriba en su limitación a la hora de ofrecer más información (y de más valor) de la que se ve en cada tabla:

• Cada mes hay que copiar y pegar una nueva tabla (o copiar en una nueva hoja y nombrarla) y modificar como mínimo la fecha.

• Para sumar los datos de todos los meses se debe hacer una nueva tabla e introducir en cada celda correspondiente, una fórmula que vaya sumando cada dato en cada uno de todos los meses. Cuando transcurre un mes, se deben modificar todas las fórmulas para añadir el nuevo período. Todo ello es un consumo de recursos enorme y además, con una alta probabilidad de errores.

• Si quisiéramos sumar los datos de un número determinado de meses distinto al anteriormente realizado, deberíamos construir otra tabla más y de forma exclusiva para esos meses, debiendo modificar todas las fórmulas de la tabla cada vez que quisiéramos variar los meses consultados.

• Si quisiéramos realizar un gráfico para ver la evolución de alguna cuenta, nos encontraríamos con los mismos problemas de los anteriores puntos.

• Si transcurren, por ejemplo, 7 años desde que se crea la aplicación, tendríamos 84 hojas de calculo para manejar con 84 tablas, o bien 84 hojas ubicadas en una misma tabla... (según modelo elegido) y todo ello, necesitando manejarlas como hemos comentado en los anteriores puntos.

Es decir, para cualquier otra información que no sea la que se ve directamente en esas tablas, hay que volver a copiar y pegar la estructura, crear nuevas fórmulas y además modificarlas a lo largo del tiempo. Todo esto es insostenible y nada productivo.

En resumen, podemos decir que es un modelo de datos que:

NUNCA HAY QUE UTILIZARLO

Podemos ver un ejemplo si descargamos el siguiente archivo:

2) TABLAS BIDIMENSIONALES ("flat tables" o tablas planas): 

Son las que tienen una dimensión con sus items extendidos a lo largo de los encabezados de columna y otra dimensión con sus items extendidos a lo largo de las filas.

No tiene porqué ser así siempre, pero suelen presentar en las columnas los períodos de tiempo (la dimensión "fecha", que está extendida a lo largo de las columnas, pero es sólo una dimensión. ¡Cada mes NO es una dimensión!) y en las filas los items de otra dimensión a estudiar, por ejemplo, las cuentas:

Se utilizan para modelos de datos que están limitados en el tiempo y cuyo análisis se circunscribe a un período definido de antemano (sería insostenible una tabla durante muchos años con decenas de columnas). Ejemplos de ello, podrían ser un plan de viabilidad económico-financiero a X años, previsiones de tesorería o una gestión presupuestaria.

Este es un modelo de organización de datos que tiene las siguientes VENTAJAS:

• Es ciertamente intuitivo y fácil de desarrollar en un principio (los datos con este formato, suelen ser introducidos por el usuario, no importados de otros programas).

• En el paso de la fase de DATOS a INFORMACIÓN hay posibilidad y flexibilidad para introducir ratios y fórmulas complejas y otras combinaciones de datos. Por ejemplo, las fórmulas del punto de equilibrio o las de los períodos medios de cobro y pago.

• Es un modelo de datos que aporta muchas posibilidades para la última fase de PRESENTACIÓN en el propio dashboard: es fácil extraer rangos de celdas para construir gráficos y es fácil extraer valores para formar KPI's comparativos y de alto valor (gráficos de velocímetro, de termómetro, etc.).

Pero también presenta algunas DESVENTAJAS:

• La posibilidad de agrupar la información por distintos períodos de tiempo no es ágil y se hace compleja.

• Debido al anterior punto y a otros similares, el usuario se ve obligado a utilizar de forma intensiva fórmulas y funciones como SI, Y, O, BUSCARV, BUSCARH, INDICE, COINCIDIR, DESREF, etc. En definitiva, el paso de la fase DATOS  a INFORMACIÓN es más difícil y ardua.

• 2 dimensiones es un origen de datos muy escaso. Estamos privados de conocer otros puntos de vista (otras dimensiones) de forma ágil, aunque sí es posible con ciertas rigideces y apelando a algunos "trucos".

En resumen y como factores más destacados, podemos decir que es un modelo de datos que:

• No tiene limitaciones para ofrecer KPI's y gráficos de alto valor
• Requiere más conocimientos del usuario y utilización intensiva de funciones
• El manejo de los períodos de tiempo

Las tablas bidimensionales suponen un avance espectacular respecto de las tablas unidimensionales, como se puede ver en el archivo que a continuación puede descargar el lector:

TABLAS DE DATOS MULTIDIMENSIONALES (Cubos [3D] o hipercubos [+3D] OLAP)

Son tablas en las que hay una o varias columnas con un "Valor en euros" y el resto de columnas son dimensiones o criterios por las que se podría consultar la información. Entre las dimensiones, en el ámbito económico-financiero casi siempre está el campo o columna "Fecha". Vemos a continuación un ejemplo de "Tabla Multidimensional":

Es la forma habitual que nos encontramos en las tablas que importamos desde otras aplicaciones (por ejemplo, el diario de una contabilidad o el listado de facturas emitidas), pero también es posible que sea una tabla que el usuario administre introduciendo datos registro a registro (por ejemplo una aplicación de gestión de costes por proyectos o de gestión de nóminas). Podemos ver en la tabla anterior las dimensiones "Fecha", "Tipo de movimiento", "Código Cuenta" y "Descripción Cuenta". Es decir, un hipercubo OLAP.

Con este modelo de datos, se tienen las siguientes VENTAJAS: 

• Desaparecen los problemas de sostenibilidad en el tiempo, puesto que  la tabla puede aumentar hasta 1.048.576 registros.

• Se pueden añadir, mediante columnas, tantas dimensiones como sea necesario.

• Una organización de datos así, podrá servir como origen de datos de Tablas Dinámicas y ahí... se abre un mundo de posibilidades que podemos descubrir (si el lector no lo ha hecho ya) en la serie de artículos dedicado a las Tablas Dinámicas que comienza por este: "Tablas Dinámicas (I) ¿Qué son y para qué sirven?". La principal ventaja que se obtiene de esto es la velocidad de proceso y la agilidad. Es decir:
• Es una forma eficiente de ordenación de datos y se procesan las consultas y filtros a una velocidad inigualable y muy superior a las bases de datos relacionales.
• Podemos cambiar la presentación de la información por un criterio u otro de forma ágil y en tiempo real según necesidades.

Estas serían las DESVENTAJAS:

• La estructura multidimensional u OLAP, es poco intuitiva y genera rechazo a usuarios poco experimentados.

• Si la tabla la administramos nosotros introduciendo la información de forma cotidiana, en cada registro hay que introducir de forma repetitiva los datos de cada dimensión (ver en la tabla anterior los nombres de los "Agentes", por ejemplo, repetidos muchas veces en la tabla. Esta desventaja desaparece, evidentemente, si la tabla es importada.

• Las tablas multidimensionales están destinadas casi irremediablemente a ser procesadas mediante Tablas Dinámicas para convertir los "Datos" en "Información". Esto proporciona las ventajas propias de dichas tablas dinámicas, pero también sus servidumbres: las tablas dinámicas ofrecen más rigideces y dificultad para ser incluidas en Dashboards o para combinar datos que generen KPI's de muy alto valor. Es decir, son muy buenas (únicas y las mejores) para pasar de "Datos" a "Información", pero no tanto para pasar de "Información" a "Conocimiento".

En resumen y como factores más destacados, podemos decir que es un modelo de datos que:

• Es fácilmente sostenible en el tiempo y escalable en tamaño.
• Es extremadamente rápido, fácil de utilizar y eficaz para el entorno operativo y táctico en la empresa (a nivel de mandos intermedios).
• En muchas ocasiones, ofrece rigidez y dificultades para construir KPI's para la toma de decisiones estratégica.

Los siguientes archivos muestran ejemplos del modelo de datos multidimensional y de posibles formas de convertir los datos en información mediante Tablas Dinámicas y algunos gráficos:

Tabla Multidimensional importada de otra aplicación:

Tabla Multidimensional administrada por el usuario:

Puede  ver otros artículos de esta serie:

Cómo hacer un Dashboard económico-financiero (1)
Cómo hacer un Dashboard económico-financiero (2)
Cómo hacer un Dashboard económico-financiero (3)
Cómo hacer un Dashboard económico-financiero (5)
Cómo hacer un Dashboard económico-financiero (6)

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Extraer una parte del texto de una celda, con VBA

Hace unos días recibí un e-mail de una lectora del Blog, preguntando cómo podría conseguir mediante código VBA (una macro) extraer una parte del texto de un rango de celdas en una columna. En su mensaje, me proponía un código que ya estaba utilizando, pero quería perfeccionarlo para tener una mayor utilidad.

El código aportado por mi lectora era:

Sub Extraer()

Final = Range("A65536").End(xlUp).Row

For x = 2 To Final

Cells(x, 2) = Mid(Cells(x, 1), 5, 4)

Next

Lo que hacía cada una de las líneas era:

1. Determinar a cuantas celdas se le debe aplicar la "Extracción"  (2ª línea del código)
2. Iniciar un bucle con un número de repeticiones igual al número de celdas hallado en el punto anterior, asignando en la primera vuelta del bucle, el valor x = 2
3. Insertar en la celda B2 un texto que se extraerá del texto de la celda A2 (Cells (x,2), empezando por el 5º caracter (contando desde la izquierda)  y tomando para la extracción los siguientes 4 caracteres.
4. Comienza otra vuelta de bucle en el que inserta en la celda B3 un texto que se extraerá del texto de la celda A3, empezando por el 5º caracter (contando desde la izquierda)  y tomando para la extracción los siguientes 4 caracteres.
5. ...y así sucesivamente...

En definitiva, que del texto  FRANCISCO-01   extraería la cadena de texto   CISC

Pero mi lectora quería algo diferente. Lo que pretendía era tener la posibilidad de "extraer" un cierto número de caracteres contando desde la derecha. Ese "cierto número de caracteres" debería ser elegido previamente por el usuario, mediante un cuadro de diálogo que se obtendría en pantalla al presionar un botón (por ejemplo). 

Pues bien, le ofrecí la siguiente solución:

Sub RecortarDerecha()
NCaracteres = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Introduce el número de caracteres a recortar", "Nº caracteres a recortar", "Nº caracteres a recortar", 1000, 1000)
Final = Range("A65536").End(xlUp).Row
For x = 2 To Final
Cells(x, 2) = Right(Cells(x, 1), NCaracteres)
Next
End Sub

Respetando parte del código que teníamos en la propuesta inicial, una de las soluciones posibles se trata de:

1. Introducir la variable NCaracteres, que representaría a lo introducido por el usuario en un "Inputbox" (2ª línea del código). Podemos recordar cómo se introduce un cuadro de introducción de datos o "Inputbox" en el siguiente artículo: "Introducción de datos en Excel con un cuadro de diálogo"
2. Cambiar la función MID por RIGHT  (recordemos que ahora se pretende extraer desde el primer caracter de la derecha siempre).
3. Cambiar los dos últimos argumentos que tenía la función MID, por un sólo argumento: el número de caracteres que queremos recortar desde el primer caracter de la derecha (NCaracteres).

Podemos comprobar el funcionamiento de todo ello, descargando el siguiente archivo Excel:

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Importar datos desde Access a Excel (3 de 5)

Es el turno ahora, de importar datos desde Access.  Como mucho usuarios sabrán (y si no es así, los remitimos al artículo ¿Qué debo utilizar... Access o Excel? , Microsoft Access es una aplicación del paquete Office, cuya utilidad estriba en almacenar datos de una forma ordenada (relacionando datos de un tipo con datos de otro tipo en distintas tablas) en un entorno que nos facilita la introducción de nuevos datos (mediante formularios fáciles de diseñar y cómodos de utilizar) y la recuperación de los ya introducidos (mediante unos filtros llamados "consultas").

Las aplicaciones que se pueden diseñar basadas en Access son múltiples, en especial, todas aquellas que sirven para la gestión de la producción, la gestión de RRHH o la gestión comercial.... entre otras.

Por lo tanto, ocurre a veces que necesitamos importar y enlazar determinados datos de estas aplicaciones diseñadas en Access, para tenerlos disponibles en Excel y poder actuar de forma más eficiente en otros menesteres, por ejemplo, el análisis y representación gráfica de datos en forma de reporting.

Vamos a aprender a realizar el proceso de importación y enlace de los datos de una tabla de Access, hasta una hoja de Excel.

Aplicación práctica de la herramienta SOLVER de Excel

Vamos a abordar en este post una herramienta de Excel bastante conocida en algunos ámbitos (matemáticas, ingeniería, economía, química, etc.) en los que el análisis de datos y la programación lineal es parte importante.

SOLVER (definida de forma coloquial y simple), es una herramienta que sirve para saber en qué cantidad se tienen que combinar varias cosas (variables) para formar un todo perfecto u óptimo (objetivo) ateniéndonos a ciertas condiciones dadas y a algunas restricciones también dadas.

Para complementar la definición anterior, pongamos un ejemplo típico susceptible de solucionar con la herramienta SOLVER de Excel:

Como en todos los problemas, una de las cosas más importantes, es esquematizarlo de forma gráfica para entenderlo y estar en disposición de resolverlo más fácilmente. A continuación hacemos esto, y además asignamos unos valores de ejemplo.

También, para trabajar posteriormente en SOLVER es muy conveniente que la plantilla que hemos configurado para nuestro planteamiento, la copiemos, la peguemos y la dejemos sin datos más abajo para que ahí puedan introducirse las formulas que definirán las relaciones que habrá entre las restricciones y los datos que nos tiene que dar SOLVER:

Una vez llegados aquí, ya hemos hecho lo más laborioso y difícil. Es la hora de SOLVER y para ello, debemos asegurarnos de que Excel lo tiene entre sus complementos. Para ello, hacemos click en el botón "Complementos" del grupo "Complementos" de La ficha "Programador" y activamos la casilla correspondiente:  

Después, para ejecutar el complemento y empezar a utilizarlo, hacemos click en el botón "Solver" que estará en el grupo "Análisis" de la ficha "Datos":


Aparecerá la ventana de SOLVER, en al que debemos seleccionar en primer lugar:

• La celda objetivo. Es decir, la celda donde aparecerán los beneficios (en €) que se podrán obtener con la combinación óptima (H43).
• El tipo de optimización que hará SOLVER. En este caso, queremos Maximizar los beneficios.
• El rango de celdas donde aparecerán los resultados que aportará SOLVER, en este caso H39:J40.

Una vez hecho esto, debemos decirle por último a SOLVER cuáles son las restricciones. Para ello, comenzamos con las restricciones de crédito a los clientes, haciendo click en el botón "Agregar" y seleccionando el rango donde pusimos las fórmulas de las restricciones (Referencia de celda) y seleccionando la propia Restricción... de esta forma:

Hacemos click en "Agregar" y seleccionamos los rangos de las siguientes restricciones (las de fabricación), de tal forma que al final, debe quedar así:

Una de las restricciones que podemos ver en la imagen anterior y cuyo objetivo es que los resultados sean exclusivamente enteros se agrega así:

 
Pues ya lo tenemos todo. Sólo nos queda hacer click en "Resolver" y obtendremos un cuadro similar a este:

... que al "Aceptar", vemos cómo aparecen los resultados en Excel:

Con las condiciones de rentabilidad o beneficio por cada producto y cliente y las restricciones de fabricación y crédito a los mismos, los máximos beneficios que se pueden obtener son 49.000 € si la política de ventas de la empresa incentiva a sus vendedores a cumplir con las cantidades y proporciones que SOLVER ha proporcionado en el cuadro anterior.

Podemos apreciar a continuación la utilidad de esta aplicación si hacemos un análisis de los resultados:

Pueden descargar, si lo desean, el archivo de Excel que ha servido para las explicaciones en este artículo:

  José Manuel Pomares Medrano