Reemplazar texto entre dos caracteres en Excel

Vamos a ver dos modos de reemplazar texto entre dos caracteres en Excel. En nuestro ejemplo entre dos paréntesis.

Sin VBA

Usando el cuadro de diálogo de Buscar y reemplazar:

1. En la pestaña Inicio, en el grupo Edición, clic en Buscar y seleccionamos reemplazar. O Ctrl+F.
2. En Buscar: escribimos (*) y en Reemplazar con: el nuevo texto o lo dejamos vacío para eliminar en lugar de sustituir.

Para reemplazar solamente en rangos no contiguos, las seleccionamos manteniendo la tecla control presionada y seguimos el procedimiento anterior.

Con VBA

Eliminamos el texto entre paréntesis. Mediante el cuadro de diálogo podemos seleccionar rangos no contiguos manteniendo presionada la tecla control o tecleando ; después de cada rango. Para reemplazar el texto, escribimos el nuevo texto en Replacement (equivalente al Reemplazar con del cuadro de diálogo de Buscar y reemplazar).

' Todas las celdas de la hoha
Sub Reemplazar()
    Cells.Replace What:="(*)", Replacement:="", LookAt:=xlPart
End Sub

' Rango seleccionado
Sub Reemplazar2()
    Selection.Replace What:="(*)", Replacement:="", LookAt:=xlPart
End Sub

' Mediante cuadro de diálogo
Sub Reemplazar3()
    On Error Resume Next
    Dim myRange As Range
    Set myRange = Application.InputBox(Prompt:="Texto reemplazado: (*)", _
    Type:=8)
    myRange.Replace What:="(*)", Replacement:="", LookAt:=xlPart
    On Error GoTo 0
End Sub

Gráfico Q-Q en R

El gráfico Q-Q ayuda a comparar gráficamente dos distribuciones. La Q viene de quantile en inglés, cuantil en español, pues comparamos los cuantiles de dos distribuciones. En esta entrada, compararemos nuestros datos con los valores teóricos de una distribución normal estándar. Si la distribución de nuestros datos es normal, el gráfico será aproximadamente una línea recta.

Funciones en R

La función qqnorm genera un gráfico Q-Q que compara los cuantiles de nuestros datos (sample quantiles) con los cuantiles teóricos (theoretical quantiles) de la distribución normal estándar, N(0, 1).

La función qqline superpone una línea que nos ayuda a evaluar la relación lineal de las dos distribuciones. Esta línea, que por defecto cruza los puntos del primer (0,25) y el tercer cuartil (0,75), es una aproximación robusta de los valores esperados de nuestros datos si siguieran una distribución normal estándar. Si los datos se alejan de esta línea, especialmente cerca del centro, nos sugeriría que nuestros datos no se distribuyen normalmente.

Muestra aleatoria normal

set.seed(2001)
serie <- rnorm(100)
qqnorm(serie)
qqline(serie)

En el Q-Q gráfico anterior, los datos aleatorios generados siguiendo una distribución normal estándar (rnorm) están en el eje vertical y los cuantiles teóricos de la distribución normal en el eje horizontal. La linealidad de los puntos sugiere que los datos se distribuyen normalmente.

Veamos dos ejemplos más con datos incluidos dentro del paquete datasets, cargado por defecto en R.

Rivers

qqnorm(rivers) 
qqline(rivers)

En el gráfico anterior se observa como los puntos se alejan de la recta, especialmente a la derecha del gráfico. La distribución presenta una asimetría positiva, la cola a la derecha de la media es más larga que la izquierda.

Trees volume

qqnorm(trees$Volume)
qqline(trees$Volume)

.

En el gráfico anterior también se observa como los puntos se alejan de la recta, especialmente a la derecha del gráfico. La distribución presenta una asimetría positiva, la cola a la derecha de la media es más larga que la izquierda. Y también se observa un segundo pico pero más pequeño que el principal.

Referencias

Gráfico Q-Q
Quantile-Quantile Plots

Duplicar y cerrar libro de Excel mediante VBA

En otra entrada vimos como duplicar el fichero de Excel y cómo capturar la respuesta del usuario con mensajes emergentes (msgbox). En esta ocasión combinamos ambas para tras duplicar el fichero preguntar al usuario si quiere cerrar el fichero actual (sin guardar los cambios) y abrir el nuevo fichero duplicado.

Option Explicit
Sub DuplicarLibroCerrar()
    Dim wb As Workbook, sRuta As String
    sRuta = ThisWorkbook.Path & "\" & _
    Format(Now, "yyyymmdd") & Chr(32) & ThisWorkbook.Name
    ThisWorkbook.SaveCopyAs Filename:=sRuta
    
    Dim resp As String
    resp = MsgBox("¿Quieres abrir el duplicado y cerrar éste?", vbYesNo)
        If resp = vbYes Then
        Set wb = Workbooks.Open(sRuta)
        ThisWorkbook.Close
        Else
        End If
End Sub

Guardará una copia del libro activo en la misma ruta del libro, con el nombre precedido de la fecha y un espacio. P. ej.: Libro1.xlsm se guardará como 20140519 Libro1.xlsm.Si también queremos la hora reemplazamos por "yyyymmdd" por "yyyymmdd-hh.mm.ss" u otro formato deseado.

Aparecerá esta ventana emergente al finalizar:

En el caso de que el fichero ya cuente con una fecha y espacio delante del nombre del fichero, empleamos este macro:

Option Explicit
Sub DuplicarLibroCerrar2()
    Dim sNomb As String, sNombSinFecha As String ' Nombre con y sin fecha
    sNomb = ThisWorkbook.Name ' Nombre entero
    sNombSinFecha = Right(sNomb, Len(sNomb) - InStr(1, sNomb, " ")) 
    
    Dim wb As Workbook, sRuta As String
    sRuta = ThisWorkbook.Path & "\" & _
    Format(Now, "yyyymmdd") & Chr(32) & sNombSinFecha
    ThisWorkbook.SaveCopyAs Filename:=sRuta
    
    Dim resp As String
    resp = MsgBox("¿Quieres abrir el duplicado y cerrar éste?", vbYesNo)
        If resp = vbYes Then
        Set wb = Workbooks.Open(sRuta)
        ThisWorkbook.Close
        Else
        End If
End Sub

Extrae el nombre del fichero sin fecha y le añade la fecha actualizada.

Entradas relacionadas:
Duplicar libro de Excel mediante VBA

Operaciones básicas con la distribución normal en R

En esta entrada emplearemos diferentes funciones en R para el cálculo de valores asociados con la distribución normal. Los valores por defecto son los de la distribución normal estándar, una media de 0 y una desviación típica de 1: N(0, 1).

Los prefijos antepuestos a las funciones de distribución de probabilidad, en nuestra entrada de la normal (norm), son:

d - density, densidad
p - probability, probabilidad
q - quantile, cuantil
r - random, aleatorio

dnorm

Devuelve la altura de la función de densidad en un punto. La marcamos con una línea vertical punteada

# Altura en el punto x = 0
v <- 0
curve(dnorm(x), xlim = c(-3, 3), las = 1, 
      main = bquote("dnorm:" ~ .(round(dnorm(v), 4))))
segments(x0 = v, y0 = 0, x1 = v, y = dnorm(v), lty = 3)

pnorm

Dado un número o una lista, devuelve la probabilidad acumulada a la izquierda de los valores suministrados. Veamos varios ejemplos.

P(X<=-0.24)

pnorm(-0.24)

[1] 0.4051651

Al ser simétrica podemos calcular la probabilidad de Pr(X>0.24)

# Probabilidad acumulada partiendo de la derecha
pnorm(0.24, lower.tail = FALSE)
# Restamos a la probabilidad total la acumulada a la izquierda 
1 - pnorm(0.24)

P(X>=-1)

# P(X>=-1)
1 - pnorm(-1)
pnorm(-1, lower.tail = FALSE)
pnorm(1)

[1] 0.8413447

pnorm(2.89) - pnorm(0.3)
1-pnorm(0.3) - pnorm(-2.89)
pnorm(0.3, lower.tail = FALSE) - pnorm(2.89, lower.tail = FALSE)

[1] 0.913085

P(-1< X <1)

1-pnorm(-1)-pnorm(-1)
1-pnorm(-1) - pnorm(1, lower.tail = FALSE)
pnorm(-1, lower.tail = FALSE) - pnorm(1, lower.tail = FALSE)

[1] 0.6826895

qnorm

Es la función inversa de pnorm. Dada una probabilidad devuelve el número que deja a la izquierda la probabilidad acumulada suministrada. Calculamos la inversa de los ejemplos anteriores.

Ejemplo 1: valor central

qnorm(0.5)

[1] 0.5

Ejemplo 2: cola de la izquierda

qnorm(0.4051651)

[1] -0.2400001

Ejemplo 3: cola de la derecha

qnorm(0.8413447, lower.tail = FALSE)

[1] -1

Ejemplo 4: intervalo

qnorm((1-0.6826895)/2) # Izquierda
qnorm((1 - 0.6826895)/2, lower.tail = FALSE) # Derecha

[1] -1
[1] 1

rnorm

Genera números aleatorios de la distribución normal. Lo empleamos al comienzo de la entrada para dibujar la distribución normal.

A continuación creamos un histograma al que añadimos la curva de la distribución normal. Empleamos la función set.seed para que generar números pseudoaleatorios y que los resultados sean los mismos si le asignamos el mismo número.

set.seed(123) 
hist(rnorm(10000), prob = TRUE, xlab = "x", main = "Histograma", 
     xlim = c(-3, 3), ylim = c(0, 0.4), las = 1)
curve(dnorm(x), xlim = c(-3, 3), add = TRUE, las = 1, 
      col = "darkblue", lwd = 2)

Creamos la tabla de frecuencias del histograma anterior:

set.seed(123)
rangos <- seq(-3, 3, 0.5)
intervalos <- cut(rnorm(10000), breaks = rangos) 
tabla.frec  <- table(intervalos)   # Crea la tabla de frecuencias
as.data.frame(tabla.frec) 

   intervalos Freq
1   (-3,-2.5]   46
2   (-2.5,-2]  173
3   (-2,-1.5]  438
4   (-1.5,-1]  942
5   (-1,-0.5] 1451
6    (-0.5,0] 1978
7     (0,0.5] 1899
8     (0.5,1] 1484
9     (1,1.5]  924
10    (1.5,2]  431
11    (2,2.5]  161
12    (2.5,3]   45

Entradas relacionadas

• Distribución normal
• Generar una distribución normal aleatoria en R
• Calculadora y gráfica de la distribución normal estándar en R
• Test de normalidad Shapiro–Wilk en R
• Superponer gráficos en R: distribución normal

Reemplazar carácter en un fichero de texto con VBA en Access cuadro dialogo

Anteriormente vimos cómo reemplazar carácter en un fichero de texto con VBA en Access. En esta ocasión, en lugar de especificar en nuestro código la ruta y nombre del fichero, lo seleccionamos mediante un cuadro de diálogo. El nuevo fichero con el sufijo _final se creará en la misma ruta del fichero seleccionado.

Sub ReemplazarCaracteresAccess()

Set FileName = Application.FileDialog(msoFileDialogFilePicker) 
FileName.Show 'Diferente de Excel
ruta = FileName.SelectedItems(1)
RutaSinExtension = Left(ruta, InStrRev(ruta, ".") - 1) 

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(ruta) Then
    Set objStream = fso.OpenTextFile(ruta, 1, False, 0)
End If
    Set ObjCopy = fso.CreateTextFile(RutaSinExtension & "_final." & Right(ruta, 3))

For x = 1 To 5  'Salta el nº de líneas indicadas: 5
    objStream.readline
Next x
 
Do While Not objStream.AtEndOfStream
    strOldLine = objStream.readline
    i = 1
    NewArray = Split(strOldLine, ",") 'Carácter reemplazado: ,
       strNewLine = NewArray(0)
       
    Do Until i = UBound(NewArray) + 1
         strNewLine = strNewLine & ";" & NewArray(i) 'Carácter nuevo: ;
         i = i + 1
    Loop
    ObjCopy.WriteLine strNewLine
Loop
End Sub

Borramos estas 3 líneas en el caso de que en el fichero de texto no haya que saltarse líneas en blanco y que provocarían un error:

For x = 1 To 5  'Se salta el nº de líneas indicadas 
    objStream.readline
Next x

Entradas relacionadas

• Reemplazar carácter en un fichero de texto con VBA en Access
• Reemplazar carácter en un fichero de texto con VBA en Excel

Generar una distribución normal aleatoria en R

En R, podemos generar un histograma de una distribución normal mediante la función rnorm.

Muestras aleatorias de una distribución normal

Para que los resultados sean reproducibles generamos números pseudoaleatorios con al función set.seed

set.seed(123)
normal <- rnorm(10000)
head(data.frame(normal))

       normal
1 -0.56047565
2 -0.23017749
3  1.55870831
4  0.07050839
5  0.12928774
6  1.71506499

Comprobamos que los resultados son muy parecidos a los que esperaríamos de una distribución normal estándar N(0, 1). Recordar que la notación de la distribución normal N(media, varianza), es distinta de la empleada por R: N(media, desviación típica).

mean(normal) # Calculamos la media

[1] -0.002371702

# Calculamos la varianza ≃ 1
sd(normal)^2 
var(normal)

[1] 0.9986366

Si deseamos generar muestras aleatorias distintas de la distribución estándar, especificamos la media y la desviación típica.

N(-2, 0.5)

set.seed(123)
normal <- rnorm(10000, -2, sqrt(.5))
head(data.frame(normal))

mean(normal)

[1] -2.001677

# Calculamos la varianza ≃ 0.5
sd(normal)^2 
var(normal)

[1] 0.4986376

Histograma de la distribución

set.seed(123)
hist(rnorm(10000), prob = TRUE, xlab = "x", 
     main = "Histograma", xlim = c(-3, 3), ylim = c(0, 0.4))

Añadimos la curva de la función de densidad de probabilidad sobre el histograma:

curve(dnorm(x), xlim = c(-3, 3), add = TRUE, las = 1, 
      col = "darkblue", lwd = 2) 

Tabla de frecuencias

Creamos la tabla de frecuencias de la muestra aleatoria generada.

set.seed(123)
rangos <- seq(-3, 3, 0.5)
intervalos <- cut(rnorm(10000), breaks = rangos)
tabla.frec  <- table(intervalos) 
as.data.frame(tabla.frec)

   intervalos Freq
1   (-3,-2.5]   46
2   (-2.5,-2]  173
3   (-2,-1.5]  438
4   (-1.5,-1]  942
5   (-1,-0.5] 1451
6    (-0.5,0] 1978
7     (0,0.5] 1899
8     (0.5,1] 1484
9     (1,1.5]  924
10    (1.5,2]  431
11    (2,2.5]  161
12    (2.5,3]   45

Algunos valores de los 10.000 generados están fuera de los intervalos fijados de -3 y 3: 28

sum(tabla.frec)

 [1] 9972

Referencias:

The Normal Distribution
Gráfico de la función de densidad de la distribución normal en R
Gráfico de la función de distribución acumulada de la distribución normal en R
Distribución de frecuencias en R

Mostrar números como fracciones no simplificadas en Excel

Cuando introducimos una fracción en una celda, Excel automáticamente muestra el número como una fecha. 1/4 devolverá 01-abr. Para evitarlo, precedemos la fracción del símbolo =. Excel aplicará el formato general con un número de decimales variable en función del tamaño de la celda. En nuestro ejemplo, =1/4 mostrará 0,25.

Para conservar el formato de fracción:

1. Abrimos el cuadro de diálogo Formato de celdas presionando Ctrl+1 o

2. En la Categoría Fracción elegimos Hasta un dígito (1/4) en nuestro ejemplo, o el que corresponda.

Fracciones no simplificadas

Por defecto, Excel simplificará la fracción. Para evitarlo, o cuando el denominador excede los 3 dígitos, especificamos el denominador en el formato de celdas personalizada: ?/denominador

Por ejemplo, si introducimos en una celda =36/180, aunque especifiquemos el formato fracción hasta los tres dígitos, Excel mostrará 1/5. Para conservar la fracción no simplificada, en formato de celdas Personalizada, en Tipo, escribimos ?/180

Referencias:
Mostrar números como fracciones

Distribución normal estándar en Excel

En esta entrada construiremos una distribución normal estándar en Excel, con una media de 0 y varianza de 1: N(0,1)

Tabla de datos

El primer paso es generar la tabla con los datos de origen para una distribución normal estándar. Representamos el intervalo [μ -3σ, μ + 3σ], 3 desviaciones típicas alrededor de la media, pues es donde se sitúa la práctica totalidad de la distribución (99,7%). Solamente 3 de cada 1.000 puntos quedarán fuera de este área.

En la primera columna los datos del eje x. En la segunda columna el valor de la función de densidad de probabilidad. En la tercera columna el valor de distribución acumulada.

Columna A - En A2 introducimos -3, desviaciones típicas de la media. En A3 sumamos a A2 un número arbitrario y arrastramos el controlador de relleno hasta llegar a 3 desviaciones típicas de la media. Cuanto más pequeño sea ese número más puntos obtendremos entre los dos extremos (-3 y 3) y más suave será la curva.

Ejemplo con solamente 6 puntos entre -3 y 3:

Columna B - Calculamos la función de densidad para los valores de la columna A. En los argumentos, Media y Desv_estándar, especificamos los parámetros de la distribución normal N(0, 1), media 0 y desviación típica de 1. En el argumento Acum, especificamos FALSO para que calcule la probabilidad no acumulada en ese punto.

Columna C - Calculamos la función de distribución acumulada para los valores de la columna A. En los argumentos, Media y Desv_estándar, especificamos los parámetros de la distribución normal N(0, 1), media 0 y desviación típica de 1. En el argumento Acum, introducimos VERDADERO para que calcule la probabilidad acumulada hasta ese punto.

Función de densidad

Creamos un gráfico de dispersión con líneas rectas y lo formateamos apropiadamente. Como origen de los datos seleccionamos las columnas A y B.

Función de distribución

Creamos un gráfico de dispersión con líneas rectas y lo formateamos apropiadamente. Como origen de los datos seleccionamos las columnas A y C.

Entradas relacionadas:

Distribución normal

Ley de Benford en R - Gráfico usando ggplot2

Partimos del gráfico de barras creado en la entrada anterior, usando el paquete estándar graphics.

A continuación representaremos un gráfico similar usando el paquete ggplot2. Cargamos los paquetes necesarios. Añadimos scales para el formato usado en scale_y_continuous.

library(ggplot2)
library(scales)

Creamos la distribución de Benford. Como usaremos ggplot, necesitamos crear un data frame a partir del vector.

benford <- log10(1+1/(1:9))
benford <- data.frame(d = 1:9, p = benford)
benford

  d          p
1 1 0.30103000
2 2 0.17609126
3 3 0.12493874
4 4 0.09691001
5 5 0.07918125
6 6 0.06694679
7 7 0.05799195
8 8 0.05115252
9 9 0.04575749

Versión 1

benford$d <- factor(benford$d, levels = c(9:1)) 
ggplot(data = benford, aes(x = d, y = p)) +
        geom_bar(stat = 'identity', fill = "steelblue") + 
        xlab("Dígitos") + 
        ylab("Ley de Benford") +
        coord_flip() + # Horizontal
        scale_y_continuous(labels = percent) 

Prácticamente hemos conseguido el formato del primer gráfico. Sin embargo, debemos modificar el color de fondo, las líneas de división verticales y las marcas del eje.

Versión 2

ggplot(data = benford, aes(x = d, y = p)) +
  geom_bar(stat = 'identity', fill = "steelblue") + # 1
  xlab("Dígitos") + 
  ylab("Ley de Benford") +
  coord_flip() + # Horizontal
  scale_y_continuous(labels = percent_format(), 
                     breaks =seq(0, 0.35, 0.05), 
                     minor_breaks = NULL) + 
  theme(panel.grid.minor.y = element_blank(), 
        panel.grid.major.x = element_line(colour = "grey", linetype = 3),
        panel.background=element_blank()) 

Referencias

• ggplot2 ayuda
• stackoverflow