2014-05-28

No es posible editar o actualizar los vínculos o el origen de datos

Cuando una hoja está protegida, al abrir Editar vínculos (en la ficha Datos, grupo Conexiones) las opciones Actualizar valores, Cambiar origen y Romper vínculos, aparecen desactivadas.


Hay que quitar la protección usando el comando Desproteger hoja (en la ficha Revisar, grupo Cambios) para poder editar, actualizar o romper los vínculos.


Ocurre exactamente lo mismo si tratamos de actualizar un origen de datos y la hoja está protegida. Al presionar sobre la tabla con el botón secundario del ratón, en el menú contextual aparecerá desactivada la opción Actualizar. Y, si en la ficha Datos, presionamos Actualizar todo, nos aparecerá el siguiente mensaje.


Tal y como nos advierte, si la hoja está protegida con contraseña, deberemos suministrarla al Desproteger hoja. Si no, no podremos editar o actualizar la conexión con el origen de datos. Sí que podremos quitar la conexión, aunque la hoja esté protegida y desconozcamos la contraseña.

2014-05-25

Seleccionar elementos en cuadros combinados o de lista

Title

Al abrir un formulario, es posible que los cuadros combinados o de lista no muestren ningún valor. Pero quizá queramos, que por defecto, nos muestren el primer o el último elemento de una lista de opciones. En esta entrada veremos cómo seleccionar estos valores predeterminados.

Vamos a emplear como ejemplo la base de datos Northwind, que puedes descargar aquí. Abrimos el formulario Cuadro de diálogo de informes de ventas. En el cuadro combinado Año aparece por defecto el año 2006. Lo que vamos a modificar es precisamente el valor predeterminado del mismo.

1. Abrimos el formulario en vista Diseño, y para ver la hoja propiedades presionamos F4 o sobre el icono homónimo. También clic en el cuadrado situado en la esquina superior izquierda del formulario y F4 (A). O desde la hoja de propiedades seleccionamos Formulario (B).

2. Tenemos dos opciones:

Al cargar el formulario

1. En la hoja de propiedades del formulario, en la pestaña Eventos, clic sobre Al cargar.

2. Se abre el editor de visual basic. Con la propiedad ItemData especificamos el número de la fila del cuadro combinado o cuadro de lista que deseamos mostrar. Las filas de los cuadros combinados y de lista se indizan empezando por cero. Con ListCount determinamos el número de filas del cuadro combinado o de lista.

Primera fila: Me.cbYear = Me.cbYear.ItemData(0)
Fila n: Me.cbYear = Me.cbYear.ItemData(n-1)
Última fila: Me.cbYear = Me.cbYear.ItemData(Me.cbYear.ListCount - 1)

Private Sub Form_Load()
    SetSalesPeriod ByYear
    InitFilterItems
    Me.cbYear = Me.cbYear.ItemData(0) ' Primera fila
End Sub

Propiedades del cuadro combinado

1. Clic sobre el cuadro Independiente (cbYear) a la derecha de Año y abrimos la hoja de propiedades.

2. En la pestaña Datos de la hoja de propiedades, borramos la fórmula (si nosotros lo creamos desde cero estaría en blanco) en la casilla Valor predeterminado.

3. Introducimos alguna de las siguientes líneas, dependiendo de la fila que queramos mostrar.

Primera fila: [cbYear].[ItemData](0)
Fila n: [cbYear].[ItemData](n-1)
Última fila: [cbYear].[ItemData]([cbYear].[ListCount]-1)

4. Presionamos guardar o Ctrl+G. Cerramos y abrimos el formulario.

Notas

En general, es recomendable la primera opción, al cargar formulario, pues nos permite mantener más fácilmente los valores predeterminados de todos los cuadros combinados o de lista del formulario. Así no tenemos que entrar en las propiedades, la pestaña datos y la casilla valor predeterminado para cada uno de los cuadros combinados o de lista. Además, si fijamos el valor en el formulario al cargar, la selección prevalece sobre la opción especificada en el valor predeterminado en las propiedades del mismo.

Entradas relacionadas

2014-05-23

Gráficos dentro de las celdas en Excel

Title En Excel tenemos la opción de crear gráficos dentro de celdas mediante fórmulas. Es una técnica que han mostrado hace mucho tiempo John Walkenbach o más recientemente John Peltier. Se basan en el uso de la fórmula REPETIR. A continuación muestro dos ejemplos que he creado y, más abajo, las técnicas explicadas.

Gráfico 1

Gráfico 2

Técnicas

1. Crear el efecto de barra apilada

2. Mostrar una marca o símbolo

Notas

Encima de cada gráfico especifico la fuente y su tamaño. Debajo la fórmula empleada. A1 es la celda con el valor representado. Dependiendo de la fuente, su tamaño y el valor de la celda (tamaño del número, porcentajes, etc.) puede que el gráfico o marca no se vea u ocupe una celda demasiado grande. Para corregirlo, multiplicamos o dividimos A1 para cambiar la escala.

En el caso de marcas o símbolos, repetimos espacio (" "), n-1 veces antes de mostrar la marca. En nuestro ejemplo para Wingdings 11, -0,01 ya que el (5/8)-1 generaría un número negativo y error. En el gráfico 2, alineamos los valores negativos a la derecha y los positivos a la izquierda. El nivel de zoom también puede alterar el aspecto del gráfico.

Adicionalmente, el uso del formato condicional y aumentando la complejidad de las fórmulas podemos incrementar la sofisticación de estos gráficos. Sin embargo, habría que considerar, especialmente si tenemos Excel 2010, el uso de otras técnicas alternativas como son los minigráficos y las barras de datos.

2014-05-20

Pegar texto sin formato en Google docs

Cuando pegamos un texto en Google Docs, usando el botón secundario del ratón, tenemos las siguientes opciones:
La única alternativa para pegar es Ctrl+V. Pero si copiamos el texto de una página web y queremos pegarlo sin formato, recurrimos a un atajo no documentado en la ayuda: Ctrl+Mayús+V

.

El atajo tradicional Ctrl+V (con formato) frente a Ctrl+Mayús+V (sin formato).

También podemos usar las combinaciones anteriores en la ventana de redacción de Gmail.

2014-05-18

Ayuda de Office sin conectarse a internet

Para evitar que los programas de Microsoft Office se traten de conectar a internet cada vez que abrimos la ayuda hacemos lo siguiente:

1. Abrimos la ayuda presionando F1 o haciendo clic en el icono de ayuda en la esquina superior derecha de la barra de título.

2. Clic en el menú Estado de conexión, en la esquina inferior derecha de la ventana, que mostrará: Conectado a Office Online o Sin conexión. Y hacemos clic en Mostrar contenido únicamente desde este equipo.

Para volver a permitir la búsqueda de contenido con conexión, seguimos el paso 1 y en el 2 paso, clic sobre Mostrar contenido de Office Online. El cambio se aplicará a todos los programas de Office instalados en nuestro equipo, no solamente al programa en el que lo hayamos realizado. La ventaja de mostrar el contenido Online es que permite buscar ayuda de todos los programas de Microsoft Office, no solamente ayuda específica del programa en el que estamos.

Búsqueda temporalmente en contenido sin conexión o con conexión

Por ejemplo, si estamos en Ms Access y tenemos activada la opción sin conexión seleccionamos en la opción de la lista buscar Todo Access para que busque puntualmente dentro del contenido online. O en Ayuda de Access debajo de Contenido de este equipo, si a pesar de estar conectado a Office Online, deseamos que busque exclusivamente en el contenido sin conexión.

Cuando volvamos a abrir la ayuda, mostrará la ayuda en función de la opción activada en el menú de estado de conexión, sin tener en cuenta la selección temporal en la opción de la lista buscar.

2014-05-15

Introducción al paquete igraph para R

Title Uno de los paquetes que nos permite realizar análisis de redes en R es igraph. Proporciona rutinas y funciones para crear y manipular grafos con facilidad.

Como ejemplo, vamos a crear un gráfico no dirigido ponderado para luego calcular el camino más corto entre dos vértices. Para calcular esta ruta, igraph emplea en este caso el algoritmo de Dijkstra. Reproducimos el siguiente ejemplo, que ilustra el algoritmo de Dijkstra, en R.

Para obtener un grafo no dirigido ponderado necesitamos crear un listado de aristas (edgelist) con los pesos de cada arista (o distancias entre ellas). Creamos tres columnas, las dos primeras representan la conexión (las aristas) entre los vértices y la tercera los pesos de cada arista. Al incorporar weight en el título de la columna, igraph asignará como atributo el peso de las aristas a las parejas de vértices.

Datos: listado de aristas

La primera opción es importar el CSV que contiene los datos.

data <- read.csv("datos.csv", sep = ";") 
data
  var1 var2 weight
1    1    2      7
2    1    3      9
3    2    3     10
4    2    4     15
5    3    4     11
6    3    6      2
7    4    5      6
8    5    6      9
9    6    1     14
La segunda opción consiste en escribir directamente los datos en R. Creamos el data frame data que editamos con la función edit.

data <- edit(data.frame())
En el Data Editor, donde navegamos como en una hoja de cálculo, rellenamos los datos. Cambiamos el nombre de la tercera columna haciendo clic sobre el título y escribimos weight. El resultado final será:

Si en el data frame no tuviéramos como título de la columna weight, podemos asignar el atributo peso posteriormente. Asumiendo que la columna con los pesos sea var3, el código sería:

E(g)$weight = data$var3 

igraph

Con el siguiente código creamos un igraph a partir del data frame anterior y lo representamos.

install.packages("igraph")                   # Descarga e instala igraph
library("igraph")                            # Carga igraph

# Empezamos a usar igraph ---------------
g <- graph.data.frame(data, directed = FALSE)  # Crea igraph 
class(g)                                     # Clase del objeto
V(g)$name                                    # Nombres de los vértices
E(g)$weight                                  # Peso de las aristas
tkplot(g)                                    # Gráfico dinámico
plot(g, edge.label = paste(E(g)$weight, sep = "")) # Gráfico de abajo

Camino más corto

Calculamos el camino más corto en función de la distancia total recorrida (suma de pesos) y la secuencia de vértices de la misma.

# Camino más corto entre 1 y 5
sp <- shortest.paths(g, v = "1", to = "5")     
sp[]                                           # Distancia 
gsp <- get.shortest.paths(g, from = "1", to = "5")
V(g)[gsp$vpath[[1]]]                           # Secuencia de vértices 
> sp[] # Distancia 
##    5
## 1 20

> V(g)[gsp$vpath[[1]]] # Secuencia de vértices
## Vertex sequence:
## [1] "1" "3" "6" "5"

Matriz de adyacencia

A continuación calculamos la matriz de adyacencia (distancia entre nodos adyacentes).

# Matriz de adyacencia con ceros
adj <- get.adjacency(g, attr='weight', sparse = FALSE)
adj
   1  2  3  4 5  6
1  0  7  9  0 0 14
2  7  0 10 15 0  0
3  9 10  0 11 0  2
4  0 15 11  0 6  0
5  0  0  0  6 0  9
6 14  0  2  0 9  0

Matriz de distancias

La matriz de distancias con el camino más corto entre todos los nodos. Y entre dos nodos concretos: 1 y 5.

# Matriz de distancias
distMatrix <- shortest.paths(g, weights = E(g)$weight)
distMatrix[1, 5] 
   1  2  3  4  5  6
1  0  7  9 20 20 11
2  7  0 10 15 21 12
3  9 10  0 11 11  2
4 20 15 11  0  6 13
5 20 21 11  6  0  9
6 11 12  2 13  9  0

## [1] 20

Todos los caminos más cortos

Para obtener todos los caminos más cortos desde un nodo.

# Caminos más cortos desde 1
allsp <- get.all.shortest.paths(g, from = "1")
str(allsp)
List of 2
 $ res  :List of 6
  ..$ : num 1
  ..$ : num [1:2] 1 2
  ..$ : num [1:2] 1 3
  ..$ : num [1:3] 1 3 4
  ..$ : num [1:4] 1 3 6 5
  ..$ : num [1:3] 1 3 6
 $ nrgeo: num [1:6] 1 1 1 1 1 1
Entradas relacionadas:
Calcular el trayecto recomendado entre dos estaciones con igraph
Calcular el número de transbordos en una ruta con igraph

2014-05-12

Cuota a pagar en un préstamo hipotecario o personal en Excel

Tratando de construir una tabla con la amortización de un préstamo encontré una plantilla en la página de Microsoft Office. Sin embargo, los cálculos de la plantilla no son correctos. La suma de los pagos totales debe de ser igual a la suma del importe del préstamo más los intereses. La columna capital debe ser igual al total del importe del préstamo. Y las cuotas del préstamo (pago total), al seguir el sistema francés, deben de ser iguales.

Aunque el empleo de nombres debería facilitar la compresión de los cálculos, este no es el caso. Emplea 27 nombres para calcular realmente dos columnas: pago principal y de los intereses. Antes de rastrear los nombres y fórmulas con el error, he decidido crear los cálculos desde cero conservando un formato parecido.

Tabla de amortización y cuota mensual

En Entrada de datos, especificamos los argumentos: capital, años, nº de pagos al año, TIN (tipo de interés nominal) y fecha inicial del préstamo o hipoteca. Automáticamente genera la tabla de amortización con las cuotas (pago total), desglosado en capital (principal) e intereses y el capital pendiente de amortizar. En la imagen muestro solamente 4 columnas. Ahora el importe del último pago y de los totales son correctos. La columna pago total es la suma del capital más los intereses.

Cálculos

Hemos utilizado 5 funciones financieras:

PAGO (tasa;nper;va;vf;tipo)

Con esta función calculamos la mensualidad (o cuota) constante a pagar. Ponemos un signo negativo delante del capital para que devuelva el importe con signo positivo.

PAGOPRIN (tasa;período;nper;va;vf;tipo)

Para calcular el pago del capital (principal) en cada periodo. Signo negativo delante de capital para obtener un importe positivo. En la imagen para el periodo 1.

PAGOINT (tasa;período;nper;va;vf;tipo)

Emplea los mismos argumentos que la función PAGOPRIN, pero devuelve el pago de intereses en cada periodo. Signo negativo delante de capital para obtener un importe positivo. En la imagen para el periodo 1.

PAGO.PRINC.ENTRE (tasa;nper;vp;per_inicial;per_final;tipo)

Función similar a PAGOPRIN pero calcula el pago acumulado entre dos periodos. Como genera un error si ponemos un signo negativo delante del capital, escribimos un signo negativo delante de la fórmula. En la imagen entre el periodo 1 y 12.

PAGO.INT.ENTRE (tasa;nper;va;período_inicial;período_final;tipo)

Emplea los mismos argumentos que la función PAGO.PRINC.ENTRE y es similar a PAGOINT pero calcula el pago acumulado de intereses entre dos periodos. Como genera un error si ponemos un signo negativo delante del capital, escribimos un signo negativo delante de la fórmula. En la imagen entre el periodo 1 y 12.

Gráficos

Para el mismo capital durante un periodo de cuatro años:

Referencias:
Simulador hipotecario del banco de España

2014-05-09

Importar ficheros CSV en Excel mediante VBA

Title En Excel, una de las opciones para importar un fichero de texto es conectarse a él (obtener datos externos) mediante el asistente para importar texto: Alt+D+F+X. El conectarnos a datos externos en lugar de abrirlos, nos permitirá poder actualizarlos en el futuro.

Si queremos automatizar este proceso y evitar el uso repetido del asistente, podemos emplear un código similar al que he creado:

Importar CSV seleccionándolo con un cuadro de diálogo

Sub ImportarCSV()
    Dim t As Single
    t = Timer
    Sheets("DATOS").Cells.ClearContents
    strFile = Application.GetOpenFilename("CSV, *.csv")
        If strFile = Empty Then
           Response = MsgBox("Ningún fichero seleccionado", _
           vbOKOnly, "Error")
        Exit Sub
        Else
        End If

    With Sheets("DATOS").QueryTables.Add(Connection:= _
        "TEXT;" & strFile _
        , Destination:=Sheets("DATOS").Range("$A$1"))
        .Name = "fichero"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True 'CSV: punto y coma
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) '5 columnas
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    MsgBox Timer - t
End Sub
En la primera parte iniciamos el cronómetro. Limpia los contenidos de la hoja DATOS en la que importaremos el fichero de texto CSV desde la celda A1. Abre un cuadro de diálogo que nos permite seleccionar el fichero a importar y nos alerta si no seleccionamos ninguno. Finalmente, importa el fichero de texto delimitado por punto y coma de 5 columnas, y nos indica el tiempo empleado en la importación.

Deliberadamente he dejado todas las propiedades que se detallan al grabar una macro usando el asistente de importación. Sin entrar a explicar todas las propiedades del objeto QueryTable, que casi se explican por sí solas, vamos a detenernos en tres:

.RefreshStyle Establece cómo se agregan o eliminan filas de la hoja de cálculo especificada. Si se sobreescriben las filas o no.
.TextFileStartRow Nos permite especificar la fila a partir de la que comienza la importación. Es 1 por defecto.
.TextFileColumnDataTypes Para especificar los tipos de datos de las columnas importadas mediante constantes. El 1 (xlGeneralFormat) las importa con formato general y el 9 (xlSkipColumn) para saltar esa columna. Ej.: TextFileColumnDataTypes = Array(1, 9, 1, 1, 1) saltaría la segunda columna. Si especifica más elementos para la matriz que columnas disponibles, se omiten esos valores.

Anexar nuevos CSV

Además, si necesitamos anexar nuevos ficheros CSV (asumimos la misma estructura que el anterior) podemos usar el siguiente código:

Sub AnexarCSV()
    Dim t As Single
    t = Timer
    Sheets("DATOS").Select
    Dim LastRow As Long
    LastRow = Range("A1").End(xlDown).Row + 1

    With Sheets("DATOS").QueryTables.Add(Connection:= _
        "TEXT;" & ThisWorkbook.Path & "\fichero.csv" _
        , Destination:=Sheets("DATOS").Range("A" & LastRow))
        .Name = "fichero"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertEntireRows ' Inserta filas
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 2 ' Salta 1ª línea con encabezado
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True 'CSV: punto y coma
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    MsgBox Timer - t
End Sub
En la primera parte iniciamos el cronómetro. Identificamos la última fila escrita e importamos el CSV que de copiará a partir de la fila previamente identificada. En este caso, en lugar de elegir el fichero con un cuadro de diálogo, se trata del fichero.txt ubicado en la misma ruta que nuestro Excel. Finalmente, nos indica el tiempo empleado en la importación.

En cualquier caso, si deseamos indicar manualmente la ruta del fichero y que se copie en la hoja activa, basta con sustituir las tres líneas desde With y a .Name por estas:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\TU_RUTA_CORRESPONDIENTE\fichero.csv", _ 
Destination:=Range("$A$1"))

2014-05-06

Aprender R interactivamente con swirl

Title El paquete swirl nos permite aprender R interactivamente en inglés desde la consola de R o RStudio. Las lecciones son cortas y nos introducen gradualmente a los conceptos básicos del lenguaje de programación R. Recomiendo a todos los principiantes en R que lo instalen y practiquen con él.

Instalación

1. Instalamos R y RStudio

2. Instalamos el paquete swirl. En la consola o en un R Script escribimos:

install.packages("swirl")  # Instala swirl
3. Cargamos y ejecutamos swirl:

library(swirl)             # Carga swirl
swirl()                    # Ejecuta swirl
4. Ya estamos listos, tras unos textos introductorios, para iniciar nuestros cursos:

1: Basic Building Blocks
2: Sequences of Numbers
3: Vectors
4: Missing Values
5: Subsetting Vectors
6: Matrices and Data Frames

Consola de RStudio ejecutando swirl

2014-05-03

Abrir ficheros y aplicaciones con un cuadro de diálogo en Access

Title En Access, dos alternativas para abrir otros ficheros o aplicaciones son el cuadro de diálogo y el hipervínculo.

Cuadro de diálogo

a. Nos aseguramos de que en referencias disponibles Microsoft Office 11.0 Object Library, o una versión posterior, está seleccionada. En el menú Herramientas, clic en Referencias, la seleccionamos y Aceptar.

b. Copiamos el siguiente código en un módulo.
Sub AbrirCuadroDialogo()
Dim cd As FileDialog
Set cd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
     With cd
        .AllowMultiSelect = True
        If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            Application.FollowHyperlink vrtSelectedItem
        Next
        End If
    End With
    Set cd = Nothing
End Sub
   c. Asignamos la macro a la autoforma: clic sobre el botón secundario y elegir Asignar macro en el menú contextual.

   d. Al hacer clic en la autoforma abrirá el cuadro de diálogo y podremos seleccionar uno o múltiples ficheros o aplicaciones.

Hipervínculo

También podemos insertar un hipervínculo en un formulario. Con el formulario en Vista diseño,  en la ficha Diseño dentro del grupo Controles, clic sobre Insertar hipervínculo o Ctrl+K.

Entradas relacionadas:
Abrir ficheros y aplicaciones con cuadro de diálogo e hipervínculo en Excel

Nube de datos