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.
2014-05-28
No es posible editar o actualizar los vínculos o el origen de datos
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.
2014-05-25
Seleccionar elementos en cuadros combinados o de lista
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
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
.
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
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
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
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:
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
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 SubEn 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 SubEn 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
Instalación
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
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 Subc. 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