Usando RStudio por primera vez

Una vez instalado R y RStudio, es hora de abrir RStudio:

La interfaz está dividida en 4 ventanas que explicamos muy simplificadamente en la imagen anterior. Se puede tanto minimizar las ventanas como modificar su tamaño. Si no apareciera la ventana de la esquina superior izquierda, creamos un nuevo R script: File, New y clic sobre R Script o Ctrl+Mayús+N

Tres atajos muy útiles para comenzar son:

Ctrl+1 - Mueve el cursor al editor del código
Ctrl+2 - Mueve el cursor a la consola
Ctrl+L - Limpia la consola

Una vez en el editor de código podemos escribir nuestro código:

# - Delante del texto, para introducir comentarios
Ctrl+Mayús+C - Convierte las líneas seleccionadas en comentarios.
Ctrl+Entrar - Para ejecutar el código seleccionado en la consola.

RStudio ofrece la opción de completar automáticamente el código presionando el tabulador. Tanto en el editor de código como en la consola. También muestra argumentos de una función y completa el nombre de un objeto que hayamos creado previamente.

En el ejemplo anterior escribimos read, presionamos tabulador y nos sugiere funciones que comienzan con esas letras. Una vez seleccionada una función y tras abrir paréntesis, presionamos tabulador y nos mostrará los argumentos de dicha función.

Aprendiendo más

Como en la mayoría de los programas hay muchas maneras de realizar la misma acción: menús desplegables, botones y atajos (indicados en las dos maneras anteriores). Por ejemplo, si abrimos el menú desplegable View ,vemos comandos adicionales a los mencionados previamente, para navegar en RStudio. Y a la derecha indica el método abreviado mediante teclas.

Finalmente, para familiarizarnos con el programa, es recomendable leer las preguntas más frecuentes,  la documentación, y explorar la barra menú con sus desplegables. También es buena idea situarnos sobre un botón para que el texto de ayuda nos aclare su utilidad.

Hay un exhaustivo listado de atajos disponible aquí. En sucesivas entradas exploraremos más opciones de RStudio para mejorar nuestra productividad.

Eliminar múltiples tablas de errores de importación en Access

Es frecuente que al importar ficheros, Access cree nuevas tablas con los errores de importación (tabla_ErroresDeImportación). Tres opciones para eliminarlas son:

1. Si es una importación puntual lo lógico es eliminar dicha tabla manualmente.

2. Si es un procedimiento que ejecutamos frecuentemente, sería recomendable incluir dentro del código de importación una instrucción que elimine dicha tabla, especificando el nombre de la misma:

DoCmd.RunSQL "DROP TABLE [tabla_ErroresDeImportación]"

3. Si en nuestro proceso de importación de ficheros se generaran varias tablas de errores, podríamos adaptar este código y copiarlo en un módulo. En este caso, nuestro código elimina aquellas tablas que contengan la palabra errores:

Sub EliminarTblsErroresImport()
Dim db As DAO.Database, t As DAO.TableDef, i As Integer
   Set db = CurrentDb()
      For i = db.TableDefs.Count - 1 To 0 Step -1
         Set t = db.TableDefs(i)
         If t.Name Like "*errores*" Then
            db.TableDefs.Delete t.Name
         End If
      Next i
   db.Close
End Sub

Podemos asociar el código anterior a un botón en un formulario que elimine todas esas tablas cuando deseemos. También utilizar el método de llamada para ejecutarlo desde nuestra subrutina de importación

Call EliminarTblsErroresImport

Las ventajas de esta tercera opción son:

• Flexibilidad: no es necesario escribir el nombre exacto de la tabla de errores generada. Solo especificar una palabra que siempre incluirán dichas tablas: errores.
• Más potente: elimina tantas tablas como errores generados.
• Extensibilidad: podemos llamar dicha subrutina desde cualquier módulo y emplearla en cualquier base de datos Access.

Es necesario tener en referencias disponibles para Ms Access 2007 y 2010 Microsoft Office 12.0 Access database engine Object Library. En Ms Access 2003 Microsoft DAO 3.6 Object Library.

Sistemas de ecuaciones lineales en Excel

Podemos usar Excel para resolver fácilmente sistemas de ecuaciones lineales. Tomemos el siguiente ejemplo:

2x + 3y + 3z = 20

x + 4y + 3z = 15

5x + 3y + 4z = 30

1.1. Representamos el sistema con matrices, A*x = b:

1.2. Multiplicamos cada lado de la ecuación por la matriz inversa, A^-1 *A * x = b*A^-1  -> x = b*A^-1 :

Empleamos la fórmula matricial MINVERSA. Selecciona el rango, inserta la fórmula y presiona Ctrl+Mayús+Entrar.

1.3. El paso final es multiplicar ambas matrices, x = b*A^-1:

Empleamos la fórmula matricial MMULT. Selecciona el rango, inserta la fórmula y presiona Ctrl+Mayús+Entrar.

2. En un único paso:



Anidamos las dos funciones anteriores.

-Notas:

Es necesario que la matriz sea cuadrada y que tenga inversa. Una matriz es invertible si su determinante es distinto de cero. Podemos comprobar previamente si una matriz es invertible usando la función MDETERM:

 

Entradas relacionadas:

Sistemas de ecuaciones lineales en Excel

Instalación de R y RStudio

En esta entrada vamos a mostrar como instalar tanto R como RStudio.

R es un lenguaje y entorno de programación para análisis estadístico y gráfico. Está disponible en las siguientes plataformas: Linux, Mac OS y Windows. Así como en versiones de 32 y 64 bits. Las principales ventajas respecto a otros programas son:

• Es gratuito, se puede instalar en cualquier ordenador y para cualquier plataforma, sin pagar ningún tipo de licencia.
• Es un programa de código abierto susceptible de ser mantenido y mejorado continuamente por una comunidad muy activa e implicada.
• Fruto en parte de las dos anteriores características, hay un ingente material disponible en Internet. Tanto de ayuda, como una extensa variedad de funciones, paquetes y métodos estadísticos desarrollados por miles de contribuidores.
• Sus inmensas capacidades gráficas.

Para una información más exhaustiva en español consulta Introducción a R.

RStudio es una interfaz gráfica para R. Nos facilitará notablemente el trabajo con R. Es necesario tener instalado previamente R. Es gratuita, de código abierto y también está disponible para las plataformas anteriormente mencionadas, así como en un servidor web. Desde RStudio también podremos controlar si usamos la versión 32 o 64 bits en el caso de que tengamos ambas instaladas.

Instalación de R

En cran.r-project.org elige la plataforma para tu sistema. Para Windows haz clic aquí y descarga el instalador.

Tras descargarlo lo ejecutamos y seguimos los pasos del proceso de instalación. En el paso de instalación Select components nos dejará instalar las versiones de 32 o 64 bits. Lo más recomendable, si quieres instalar la versión de 64 bits, es instalar también la de 32 bits. Por si algún paquete (como el RODBC) que necesites usar no es compatible con la versión de 64-bits. Desde RStudio podremos controlar si usamos la versión 32 o 64 bits de R en el caso de que tengamos ambas instaladas.

Instalación de RStudio

Tras instalar R, descargamos el instalador de RStudio haciendo clic en Download RStudio Desktop.

Elegimos nuestra plataforma y seguimos los pasos del instalador. En el caso de que no dispongas permisos de administrador se pueden descargar los archivos binarios de RStudio.

Tras instalarlo, clic sobre el icono de Rstudio para abrir el programa:

Números aleatorios de una lista o rango de opciones

En otras entradas hemos empleado los números aleatorios en repetidas ocasiones. En este caso, queremos generar números aleatorios de una lista o rango de opciones. Por ejemplo, elegir aleatoriamente un valor de la siguiente lista: 1, 3, 5, 7, 11.

Excel

Hay dos soluciones usadas frecuentemente en diversos foros:

1. Mediante una fórmula en la que introducimos la lista con los números a elegir.

=ELEGIR(ALEATORIO.ENTRE(1;5);1;3;5;7;11)

2. Usando un rango en lugar de teclear manualmente la lista dentro de la fórmula

=INDICE(A2:A6;ALEATORIO.ENTRE(1;5)) o 
=INDICE(A2:A6;ALEATORIO.ENTRE(1;CONTAR(A2:A6)))

F9 para generar otro nº aletorio

Podemos usar ambas fórmulas con una lista alfanumérica. Por ej.: Flaubert, Proust, Balzac, Montaigne, Racine.

1. Escribiendo la lista en el interior de la fórmula

=ELEGIR(ALEATORIO.ENTRE(1;5);"Flaubert";"Proust";"Balzac";"Montaigne";"Racine")

2. Usando un rango

=INDICE(E2:E6;ALEATORIO.ENTRE(1;5)) o 
=INDICE(E2:E6;ALEATORIO.ENTRE(1;CONTARA(E2:E6)))

F9 para generar otro nombre aleatorio

Access

En Access ya vimos cómo generar una muestra aleatoria. En este caso, creamos una tabla con los valores numéricos o de texto y extraemos una muestra de un sólo elemento: Con campo numérico en SQL:

SELECT TOP 1 TuTabla.*
FROM TuTabla
ORDER BY Rnd([campo numérico]);

Con campo de texto en SQL:

SELECT TOP 1 TuTabla.*
FROM TuTabla
ORDER BY Rnd(Len([campo de texto]));

Entradas relacionadas

• Generar campo autonumérico aleatoriamente en Ms Access
• Consulta para generar una muestra aleatoria en Ms Access.
• Generar números aleatorios entre dos valores en Ms Access
• Generar números aleatorios entre dos valores con decimales

Gráfico de barras animado emulando Google Public Data Explorer - Parte 3 de 3

Finalizamos con los dos últimos pasos para elaborar un gráfico de barras animado emulando Google Public Data Explorer. Es importante señalar, que estos dos últimos pasos son independientes. Podemos optar por solamente uno de ellos o ambos. En anteriores entradas vimos:

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

4. Control de número ActiveX para avanzar y retroceder en el tiempo.

El penúltimo paso es insertar un control de número.

Una vez insertado, modificamos en el cuadro de diálogo Propiedades:

- LinkedCell - Celda vinculada al valor del control de número.
- Max - Valor máximo permitido. Fila donde está el primer país del último mes.
- Min -  Valor mínimo permitido. En este caso 1.
- SmallChange - Variación cada vez que se hace clic en la flecha de control de número. En este caso 30 pues es el número de filas necesarias para empezar en el siguiente mes.

Desactivamos el modo diseño y el cuadro de control estará listo para ser utilizado

5. Animación con código VBA asociada a dos botones para iniciar y detener la animación
Insertamos 4 formas para crear dos botones: play y stop. Dos rectángulos redondeados y en el interior un triángulo y un cuadrado;

A continuación, copiamos el siguiente código en un módulo.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public StopMacro As Boolean
Sub Iniciar()
    Dim i As Integer
    StopMacro = False
    For i = 1 To 10711 Step 30: ' Min, Max y SmallChange
    DoEvents
    If StopMacro = True Then Exit Sub
        Range("G5").Value = i   ' Celda vinculada
        Application.Calculate
        Sleep (1)   ' Velocidad de la animación
    Next
End Sub
Sub Parar()
    StopMacro = True
End Sub

He modificado el código de S Anand para incluir incrementos de 30 en cada iteración (Step 30), y la opción de detener la subrutina pulsando el botón de stop. Creamos una variable booleana pública llamada Stopmacro. El loop continua mientras dicha variable sea false. Al apretar el botón stop, le asignaremos a Stopmacro el valor true, deteniéndolo.

Asignamos a cada botón su macro correspondiente: Iniciar y Parar.

Ya tendremos lista la animación:

Entradas para construir un gráfico animado emulando Google Public Data Explorer:
1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación.

Gráfico de barras animado emulando Google Public Data Explorer - Parte 2 de 3

Continuamos una entrada anterior con los pasos para elaborar un gráfico de barras animado emulando Google Public Data Explorer. En esta ocasión explicamos el tercer paso.

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

3. Series ordenadas con los 10 primeros valores en orden descendente.

Creamos la tabla dinámica y ordenamos el campo GEO (países) en orden descendente por la suma del número de Value (desempleados). Clic con el botón secundario sobre GEO, Ordenar, Más opciones de ordenación . Allí, clic sobre Descendente y elegimos Sum of value.

Una vez ordenada la tabla dinámica necesitamos crear una tabla, con los 10 primeros países por mes, que será el origen de datos de nuestro gráfico. También necesitamos una celda que será el argumento número de fila de la función índice para encontrar los 10 primeros países de cada mes. Esa celda estará vinculada al control de número con el que incrementar o disminuir su valor.

Si mostramos las fórmulas de la hoja (Alt + º):

Empleamos la fórmula INDICE para obtener los países y el nº de desempleados:

 En el argumento Matriz seleccionamos la columna B de la tabla dinámica con los nombres de países. En el argumento Núm_fila la celda vinculada al cuadro de número. Le añadimos FILA() - 7 para incrementar una fila más al arrastrar la fórmula hacia abajo. El argumento Núm_columna lo dejamos a 0, pues queremos que devuelva la columna B. Hacemos exactamente lo mismo para el nº de desempleados, cambiando la columna B por la C.

Entradas para construir un gráfico animado emulando Google Public Data Explorer:
1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

Proteger y desproteger hojas mediante VBA

En Excel no es posible proteger más de una hoja a la vez. Es necesario ir una a una. En el grupo Cambios de la ficha Revisar, clic en Proteger hoja.

No obstante, podemos emplear VBA para proteger varias hojas de un libro, en este caso todas:

Sub ProtegerHojas()
Dim i As Integer
    For i = 1 To Sheets.Count
        Sheets(i).Protect Password:="Contraseña"
    Next i
End Sub

Sub DesprotegerHojas()
Dim i As Integer
    For i = 1 To Sheets.Count
        Sheets(i).Unprotect Password:="Contraseña"
    Next i
End Sub

Otra opción, para evitar la contraseña escrita en el código, es usar un cuadro de entrada donde escribiremos la contraseña deseada. En caso de dejarse en blanco, protegerá las hojas sin contraseña.

Sub ProtegerHojas2()
Dim i As Integer, contr As String
contr = InputBox("Contraseña para proteger las hojas")
    For i = 1 To Sheets.Count
        Sheets(i).Protect Password:=contr
    Next i
End Sub

Sub DesprotegerHojas2()
Dim i As Integer, contr As String
contr = InputBox("Contraseña para desproteger las hojas")
    For i = 1 To Sheets.Count
        Sheets(i).Unprotect Password:=contr
    Next i
End Sub

Mostrar u ocultar formas en Excel mediante VBA

En una entrada anterior vimos como mediante el panel de selección podemos cambiar el nombre, ordenar, mostrar, ocultar y seleccionar varios objetos. ↓ Cómo acceder al panel de selección

Una alternativa a mostrar u ocultar objetos manualmente mediante el panel de selección, es usar VBA. Para ilustrarlo con sencillo ejemplo, hemos creado un semáforo cuyo color cambia en función de la selección en un cuadro combinado (verde, ámbar o rojo).

1. Creamos tres círculos del mismo tamaño. Para dibujar un círculo, hacemos clic en elipse, luego en el lugar donde deseemos iniciar el círculo. Para que la forma sea un círculo, mantenemos presionada la tecla MAYÚS mientras dibujamos la forma. A continuación les aplicamos el color de relleno correspondiente y alineamos vertical y horizontalmente.

Es esencial que asignemos a las formas los nombres que usaremos en el código del paso 3 (Shapes: verde, rojo y ambar). Mediante el panel de selección, doble clic sobre el nombre del objeto y tecleamos el nombre escogido. También seleccionando el objeto y en el cuadro de nombres escribimos el nuevo nombre.

2. Insertamos un cuadro combinado (control ActiveX). En propiedades, en ListFillRange le indicamos el rango utilizado para rellenar la lista (Verde, Rojo y Ámbar). Los nombres del rango deben de coincidir con los valores asignados a Case en el código siguiente.

3. Asignamos código al cuadro combinado, haciendo clic en Ver código.

Private Sub ComboBox1_Change()
ActiveSheet.Shapes.Range(Array("verde", "rojo", "ambar")).Visible = False
Select Case ComboBox1.Value
    Case "Verde"
        ActiveSheet.Shapes("verde").Visible = True
    Case "Rojo"
        ActiveSheet.Shapes("rojo").Visible = True
    Case Else
        ActiveSheet.Shapes("ambar").Visible = True
    End Select    
End Sub

El código oculta las tres formas y muestra la forma correspondiente en función del valor del cuadro combinado (Verde, Rojo y Ámbar).

4. Desactivamos el modo diseño y el semáforo estará listo para cambiar según el color seleccionado en el cuadro combinado.

El objetivo no es en modo alguno dificultar lo que sencillamente podemos lograr utilizando un conjunto de iconos aplicando el formato condicional. Es un ejemplo muy simple pero puede servir como punto de partida para empezar a manipular formas y objetos mediante VBA.

Cómo acceder al panel de selección En la ficha Inicio, en el grupo Modificar, haga clic en Buscar y seleccionar y clic en Panel de selección. O bien clic sobre la forma u objeto, Herramientas de dibujo, y en el grupo Organizar clic en Panel de selección.

Limpiar celdas aparentemente en blanco en Excel

En algunos hojas de Excel nos encontramos con celdas que están aparentemente vacías, pero que realmente no lo están. De acuerdo con la documentación:

Este comportamiento puede producirse cuando la celda contiene una cadena de longitud cero. Una cadena de longitud cero puede ser consecuencia de las condiciones siguientes:

• Una fórmula.
• Una operación de copiar y pegar.
• Una celda que contiene una cadena de longitud cero se importa desde una base de datos que admite cadenas de longitud cero y que contiene cadenas de longitud cero.

Para identificar estas celdas, empleamos la fórmula ESBLANCO. Y devolverá FALSO cuando la celda no esté vacía. Como se puede observar, a pesar de estar en blanco, la fórmula devuelve FALSO.

Una vez identificadas, para borrar su contenido disponemos de varias alternativas:

1. En Buscar y reemplazar, dejamos Buscar: vacío y en Reemplazar con: 0. Presionamos Reemplazar todos.

El resultado sería:

2. Mediante código VBA. Es necesario seleccionar el rango que deseamos limpiar antes de ejecutar el código. En lugar de ceros, como con el método anterior, dejaría las celdas en blanco.

-La solución proporcionada por galileogali es:

Option Explicit
Sub limpiar() 
Dim rng As Range, celRng As Range 
Set rng = Selection
For Each celRng In rng 
    If celRng = Empty Then 
        celRng.ClearContents 
    End If 
Next celRng 
End Sub

-Otra solución propuesta por glh es:

Option Explicit 
Sub find_newlines()
Dim c As Object
Dim firstAddress As String
With Selection
        Set c = .Find("", LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = ""
                Set c = .FindNext(c)
                If c Is Nothing Then Exit Do
            Loop While c.Address <> firstAddress
        End If
    End With
End Sub

Gráfico de barras animado emulando Google Public Data Explorer - Parte 1 de 3

Google Public Data Explorer permite explorar series de datos públicas de organismos e instituciones como el Banco Mundial, Eurostat, la OCDE o de la Oficina del Censo de los Estados Unidos. Estos datos se pueden visualizar de diferentes modos (líneas, barras, mapas, etc.) y animar en el tiempo. Ver ejemplo a continuación:



Siguiendo el ejemplo anterior, he tratado de emular en la medida de lo posible, el mismo gráfico animado en Excel. Lo primero es descargar los datos de Eurostat, importarlos en Excel y hacer algún pequeño ajuste para emplearlos como origen de una tabla dinámica. Solamente incluyo los diez primeros países de cada año para aumentar la claridad.

Describiremos por encima el proceso, deteniéndonos sólo en las técnicas más generales y versátiles que se pueden aplicar en otras ocasiones:

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

El resultado final sería:

1. Título del gráfico dinámico. 

Insertamos un cuadro de texto vinculado a la celda que cambiará. En la ficha Insertar, grupo Texto, clic sobre Cuadro de texto. Después arrastramos para dibujar el cuadro del tamaño deseado. Mientras está el cursor dentro del cuadro de texto, en lugar de escribir, situamos el cursor en la barra de fórmulas y tecleamos = y clic sobre la celda a vincular. Cambiamos el formato del cuadro, a p.ej, borde sin línea.

2. Formato personalizado del eje vertical.

Para acceder al cuadro de diálogo Dar formato a eje: botón secundario sobre el eje vertical y clic sobre Dar formato a eje o CTRL+1. O bien clic sobre el gráfico y en la ficha Formato en el grupo Selección actual, en el cuadro Elementos de gráfico seleccionamos el EjeVertical (Valor) y clic sobre Aplicar formato a la selección. En Código de formato escribimos: #." M"

Entradas para construir un gráfico animado emulando Google Public Data Explorer:

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

Cuadros combinados en cascada en Ms Access

Los cuadros combinados en cascada en un formulario son cuadros combinados sincronizados. La elección de un elemento de un cuadro combinado limita los elementos disponibles en los cuadros combinados subsiguientes. Pueden ser muy útiles para guiar, agilizar y reducir la probabilidad de errores en la búsqueda o la introducción de datos en un formulario. Por ejemplo, si en nuestro ejemplo seleccionamos Castilla-La Mancha en CC.AA., en provincia no podremos elegir Granada o en municipio Salamanca.

Para construir cuadros combinados dependientes necesitamos:

1. Orígenes de filas con consultas cuyos criterios se basen en los cuadros combinados precedentes.
2. Eventos que recalculen los valores de los cuadros combinados dependientes.

Tenemos tres tablas relacionadas Tbl_CCAA, Tbl_Provincias y Tbl_Municipios. Creamos tres cuadros combinados con origen en sus respectivos campos.

1. Cuadro combinado CC.AA.

Accedemos a las propiedades del cuadro combinado haciendo clic sobre icono Hoja de propiedades o seleccionando el cuadro y presionando Alt+Entrar o F4. En la pestaña Datos, en la propiedad Origen de la fila seleccionamos las comunidades autónomas de la tabla homónima:

En la pestaña Eventos, seleccionamos Después de actualizar, clic en el cuadro con tres puntos suspensivos y, a continuación, clic en Generador de código.

En el editor de visual escribimos el siguiente código:

Private Sub CCAA_AfterUpdate()
Provincia.Value = Null
Provincia.Requery
Municipio.Value = Null
Municipio.Requery
End Sub

Cada vez que cambia la selección del cuadro combinado CCAA, asigna un valor nulo a los cuadros Provincia y Municipio, y recalcula ambos. Sin recalcular, el cambio en el cuadro combinado precedente no tendría efecto en las consultas de origen de fila basadas en los mismos. Pues los cuadros combinados se actualizan al abrir el formulario.

2. Cuadro combinado Provincia.

En la Hoja de propiedades, en las pestaña Datos, Origen de la fila, hacemos clic sobre los tres puntos para diseñar una consulta que relacione las tablas Tbl_CCAA y Tbl_Provincias. Introducimos un criterio basado en la selección del cuadro de combinado precedente: CCAA.

En la pestaña Eventos, seleccionamos Después de actualizar, clic en el cuadro con tres puntos suspensivos y, a continuación, clic en Generador de código:

Private Sub Provincia_AfterUpdate()
Municipio.Value = Null
Municipio.Requery
End Sub

Cada vez que cambia la selección del cuadro combinado Provincia, asigna un valor nulo a Municipio, y lo recalcula.

2. Cuadro combinado Municipio.

Repetimos el paso anterior, esta vez con el cuadro municipio. En la Hoja de propiedades, en las pestaña Datos, Origen de la fila, hacemos clic sobre los tres puntos para diseñar una consulta que relacione ambas tablas, Tbl_Provincias y Tbl_Municipios. En criterios acotamos la consulta en función de la elección del cuadro provincias.

Esta vez no es necesario crear el evento Después de actualizar.

Tras estos pasos, ya tendríamos los tres cuadros combinados sincronizados en cascada.

Entradas relacionadas

• Usar un cuadro combinado para buscar al teclear en Access
• Seleccionar elementos en cuadros combinados o de lista

Vínculos externos rotos al mover el libro de destino en Excel

Cuando Excel no puede actualizar los vínculos externos, nos muestra el siguiente mensaje alertando de la imposibilidad de actualizarlos. 

Podemos hacer clic en Modificar vínculos para editar las rutas de los libros de origen y en principio el problema estaría resuelto. No obstante, cuando los libros de destino y origen están ubicados en la misma carpeta, si copiamos el libro de destino a otra carpeta, se romperán los vínculos. En tal caso tendremos que editar los vínculos de nuevo.

¿Cómo es posible? Están correctamente vinculados y en ubicación indicaba la ruta completa del libro de origen. Si no cambia la ubicación del libro de origen de lugar, ¿por qué se rompen los vínculos?

La explicación se encuentra en cómo Excel almacena las rutas de acceso al vínculo. Excel básicamente sigue una serie de reglas para almacenar la ruta de acceso a un libro vinculado, guardando los vínculos de forma relativa siempre que sea posible. Es decir, no guarda la ruta completa al libro de origen: "si el archivo vinculado y el archivo de datos de origen están en la misma carpeta, se almacena sólo el nombre de archivo."

Al mover el libro de destino, se rompen los vínculos con los libros de origen que estaban en la misma carpeta del libro de destino. Excel busca esos ficheros en la nueva ruta del libro de destino y, al no encontrarlos, no puede actualizarlos. Para solucionarlo, tenemos dos opciones:

1. Copiamos esos libros de origen en la nueva ubicación del libro de destino para que continúen juntos.
2. Con el libro de destino abierto, guardar como en la nueva ubicación y se actualizarán los enlaces adecuadamente.

Por ello es recomendable separar los libros de origen y de destino. Así, si necesitáramos mover el libro de destino a otra ubicación, no tendremos problemas al actualizar los vínculos. Por ejemplo:

Por un lado, los ficheros de origen en la carpeta de su semana correspondiente. Y por otro, fuera de la carpeta, los ficheros de destino. Cuando los ficheros de destino no necesiten actualizarse más, podremos ubicarlos en su carpeta semanal.

Quitar o borrar esquema en Excel con VBA

Recientemente me he encontrado con un libro en el que varias hojas contenían un esquema de filas. Podemos quitar el esquema yendo a la ficha Datos grupo Esquema, haciendo clic sobre Borrar esquema.

Para evitar repetir manualmente el proceso para cada hoja, he creado el siguiente código que desagrupa y borra el esquema de filas y columnas de todas las hojas de un libro de Excel, incluidas las ocultas:

Sub Desagrupar()

Application.ScreenUpdating = False
Dim ws As Worksheet
    For Each ws In Sheets
    ws.Activate
    Cells.ClearOutline
    On Error Resume Next 
    Next
Application.ScreenUpdating = True

End Sub 

La línea On Error Resume Next evita que la ejecución del código se detenga en el caso de que, por ejemplo, la hoja esté protegida.

Buscar y reemplazar cadenas de texto con VBA

Para reemplazar puntualmente texto y números en una hoja de Excel, en la pestaña Inicio, en el grupo Edición, hacemos clic en Buscar y seleccionar. O bien CTRL+L. Si tenemos que reemplazar así varias cadenas de texto el procedimiento es tedioso y lento.

Una manera de acelerar y automatizar el mismo es mediante VBA, creando una función definida por el usuario. Veamos un ejemplo:

En la tabla anterior, columna A, tenemos un campo fecha descargado de un sistema que las procesa en inglés. Cuando tratamos de operar con las fechas nos devuelve un error porque Excel, con el idioma local en español, no reconoce las iniciales de los meses de enero (JAN), abril (APR), agosto(AUG), y diciembre (DEC). Necesitamos sustituir esas cuatro cadenas de texto. Para reemplazarlas creamos en un módulo de VBA, la siguiente función de usuario.

Public Function BuscarCadena(Reemplazarcadena As String) As String

Dim Originales As Variant, Sustituciones As Variant
Dim i As Long
Originales = Array("JAN", "APR", "AUG", "DEC")
Sustituciones = Array("ENE", "ABR", "AGO", "DIC")
BuscarCadena = Reemplazarcadena
    For i = 0 To 3 'Los subíndices de matriz empiezan en 0,
                   'por tanto Originales(0) = "JAN"[...] Originales(3)="DEC"
    BuscarCadena = Replace(BuscarCadena, Originales(i), Sustituciones(i), _
    compare:=vbTextCompare)
    Next

End Function

El resultado final, con los cambios resaltados en naranja, es:

En la celda C2 introducimos la función definida en VBA =BuscarCadena(A2) y arrastramos la fórmula hasta C13. Si queremos en lugar de 01-ENE-2013 de la columna C el formato de fecha corta (dd/mm/aaaa) de la columna D hay tres opciones:

1. Aplicar el formato en VBA

BuscarCadena = Format(Replace(BuscarCadena, Originales(i), Sustituciones(i), _
compare:=vbTextCompare), "dd/mm/yyyy")

2.Multiplicar por 1 la columna C. Al operar le cambia el formato a fecha corta.

3. Usar la función en D2 =FECHANUMERO(C1) y arrastrar hacia abajo el controlador de relleno. Nos devuelve el número de serie secuencial que representa una fecha determinada, 41275 para C1. A continuación, para darles el formato deseado, seleccionamos las celdas D2:D13. Y en la ficha Inicio, en el grupo Número, clic en la flecha situada en la esquina inferior derecha. En el cuadro de diálogo Formato de celdas, seleccionamos en la categoría Fecha el primer tipo.

También podemos acceder al cuadro de diálogo Formato de celdas presionando CTRL+1.

Insertar imágenes en una hoja de cálculo de Google Drive

En una entrada anterior vimos como insertar una imagen GIF en Excel. Insertar una imagen GIF en una hoja de cálculo en Google Drive es mucho más sencillo. Existen dos opciones, insertar una imagen en la hoja o en una celda.

Insertar una imagen en una celda

En la celda introducimos la fórmula =image("URL", 3). El parámetro 3 indica que se respete el tamaño original de la imagen. Adaptamos la altura y anchura de la celda hasta que aparezca íntegramente.

Abajo la celda con la imagen insertada:

Insertar una imagen en la hoja

Hacemos clic en el menú desplegable Insertar y seleccionamos insertar imagen. En el menú tenemos diferentes opciones: Subir, Tomar una instantánea, URL, Albumes, Buscar. En nuestro caso vamos a elegir URL, pegamos la ruta de la imagen elegida y presionamos seleccionar.

El tamaño máximo de cada imagen que podemos insertar es de 2MB a día de hoy. El resultado sería:

Entradas relacionadas:

Insertar una imagen GIF en Ms Excel

Incompatibilidad de Excel con consulta de Access usando NZ

En la entrada anterior tratamos el cálculo de expresiones con valores nulos en Access. Una consecuencia imprevista al usar la función NZ() en una consulta de Access es que Excel no permite usar como origen de datos consultas que incluyan dicha fórmula. Si tratamos de importarla, y vamos a la ficha Datos, grupo Obtener datos externos y clic en Desde Access, la consulta no aparecerá listada en el cuadro de diálogo Seleccionar tabla.

Si tratamos de importarla De otras fuentes, Desde Microsoft Query listará la consulta pero al tratar de incorporar sus campos no dará un error.

Se me ocurren varias soluciones al problema:

1.Transformar la consulta de selección y crear una tabla con los resultados. El inconveniente es que crea una tabla innecesaria que duplica la información contenida en la tabla original.

2. Utilizar otra fórmula alternativa a NZ. Usamos la fórmula SiInm y EsNulo para comprobar si el campo es nulo. Si es verdad que devuelva un cero y, si es falso, el campo.

CVble: nz([Coste_variable])  'Expresión con NZ
CVble: SiInm(EsNulo([Coste_variable]);0;[Coste_variable]) 'Sin función NZ

SQL:

IIf(IsNull([Coste_variable]),0,[Coste_variable]) AS CVble

3. Diseñar una tabla donde sea obligatoria la entrada de datos en el campo. Evitaremos nulos pues nos forzará a introducir un valor, p. ej., ceros en lugar de nulos. Podemos modificar una tabla existente haciendo clic con el botón derecho sobre la tabla y clic sobre Vista Diseño. Elegimos el campo deseado y en la propiedad Requerido seleccionamos Sí:

4. Crear una consulta de actualización para sustituir los valores nulos por ceros, y a continuación cambiar el diseño de la tabla para que ese campo sea requerido (ver solución anterior).

En SQL:

UPDATE Costes SET Costes.Coste_variable = 0
WHERE (((Costes.Coste_variable) Is Null));

Dejando de lado la primera opción por ineficiente, la número dos es la más rápida y recomendable si disponemos de poco tiempo. Las soluciones tres y cuatro son complementarias, nos permiten solucionar definitivamente el problema en la base de datos con nulos.

En general, y salvo que sea absolutamente necesario, en el diseño de la tabla debemos prevenir la existencia de valores nulos siguiendo los pasos de la solución tres. De esta manera, con un buen diseño inicial de nuestra base de dato, podemos evitar futuros comportamientos indeseados y ahorrarnos mucho tiempo.

Entradas relacionadas:

Cálculo de expresiones con valores nulos en Ms Access