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

Es frecuente encontrarnos con el problema de tener un fichero de texto delimitado por comas cuando necesitamos que el delimitador sea el punto y coma. En una entrada anterior vimos como reemplazar un carácter en un fichero de texto con VBA en Access.

En Excel tenemos dos alternativas:

1. Usar el mismo Excel, Bloc de notas, u otro editor de texto como Notepad++, para reemplazar un carácter por otro. Cuando el número de ocurrencias es muy grande, millones en el caso de fichero de tamaño considerable, el proceso puede ser algo lento.

2. Utilizar código de VBA acelerando el proceso notablemente, evitando tener que abrir otros programas, reduciendo la probabilidad de errores y pudiendo llamarlo desde una macro u otro módulo.

Copiamos el siguiente código en un módulo de Ms Excel:

Public Sub ReemplazarCaracteresExcel()

Dim folderPath As String
folderPath = ThisWorkbook.Path & "\" 'Ruta del Excel

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(folderPath & "Inicial.csv") Then
    Set objStream = fso.OpenTextFile(folderPath & "Inicial.csv", 1, False, 0)
End If
    Set objCopy = fso.CreateTextFile(folderPath & "Final.csv")

For x = 1 To 5  'Se 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

Notas: El fichero Inicial.csv debe de estar en la misma ruta del Excel. El fichero Final.csv se creará automáticamente. Borrar 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

También se puede usar este código para ficheros *txt. Ejemplo: Inicial.txt y Final.txt

Entradas relacionadas

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

Distribución de frecuencias en R

En dos entradas anteriores vimos como crear histogramas y tablas de frecuencias en Excel y Access. En esta entrada haremos lo mismo en R usando los mismos datos.

Manipulación de los datos

datos <- read.csv("datos.txt", sep = ";")  # Fichero en el directorio
hist(datos$año)  # Intentamos crear histograma

## Error in hist.default(datos$año) : 'x' must be numeric

names(datos)  # Genera error por la ñ en Año

## [1] "Id" "Nombre" "Año"

colnames(datos) <- c("id", "nombre", "fecha")  # Renombramos las columnas
names(datos)

## [1] "id" "nombre" "fecha"

Tabla de frecuencias

intervalos <- seq(from = 1913, to = 2013, by = 10) 
frecuencias <- cut(datos$fecha, # Usamos la nueva columna: fechas
                 breaks = intervalos,   
                 dig.lab = 4,   # Nº de dígitos de los intervalos
                 right = FALSE) # Intervalos [a, b). Por defecto: (a, b]
tabla.frec  <- table(frecuencias)   # Crea la tabla de frecuencias
as.data.frame(tabla.frec) # O cbind(tabla.frec) mejoramos el formato

##    frecuencias Freq
## 1  [1913,1923)   16
## 2  [1923,1933)    8
## 3  [1933,1943)   18
## 4  [1943,1953)    3
## 5  [1953,1963)    7
## 6  [1963,1973)   11
## 7  [1973,1983)    6
## 8  [1983,1993)    9
## 9  [1993,2003)   14
## 10 [2003,2013)    8

Histograma

# Creamos histograma por defecto
hist(datos$fecha) 

Queremos el formato aproximado al gráfico que creamos en esta entrada.

#Formateamos el histograma
intervalos <- seq(from = 1913, to = 2013, by = 10) 
h <- hist(datos$fecha, 
          breaks = intervalos,  # Añadimos los intervalos
          freq = TRUE, # Representa frecuencias
          right = FALSE, # Intervalos [a, b). Por defecto: (a, b]
          col = rgb(79, 129, 189, max = 255), # Color de las columnas
          border = "white",     # Color del borde
          labels = TRUE,        # Etiquetas de las columnas
          main = "Frecuencias", # Título del gráfico
          xaxt = "n",           # Eliminamos eje x
          yaxt = "n",           # Eliminamos eje y
          xlab = "Años",        # Título del eje x
          ylab = NULL,          # Título del eje y
          ylim = c(0, 20))      # Escala de y (fijamos max en 20)
axis(side = 1, at = intervalos)  # Etiquetas del eje x 

Entradas relacionadas

• Distribución de frecuencias en Ms Access
• Distribución de frecuencias en Ms Excel

Cambiar origen de datos de múltiples tablas dinámicas con VBA

Una tarea bastante frecuente trabajando con tablas dinámicas es cambiar el origen de datos. La acción es bastante rápida para una sola tabla dinámica. En el caso de múltiples tablas dinámicas, el procedimiento se vuelve muy pesado. No es posible cambiar manualmente el origen de datos para varias tablas dinámicas simultáneamente. Sin embargo, podemos sortear esta limitación programáticamente:

1. Definimos un nombre (NuevoOrigen) para el rango de celdas que será el nuevo origen de datos.
2. Copiamos el siguiente código en un módulo. Nos aseguramos de que SourceData es igual al nombre definido.

Option Explicit
Sub Cambiar_Origen_Tbls_Dinamicas()
Dim n As Integer
Dim i As Integer
Dim pt As PivotTable
n = ActiveWorkbook.Worksheets.Count
    For i = 1 To n
        For Each pt In ActiveWorkbook.Worksheets(i).PivotTables
            pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:="NuevoOrigen")
        Next pt
    Next i
End Sub

En lugar de un nombre, podemos indicar un rango de celdas, p. ej.: SourceData:="Hoja1!$A$1:$M$1000"

 3. Ejecutamos el código anterior. El origen de datos habrá cambiado para todas las tablas dinámicas del libro.

Crear una lista personalizada mediante VBA

Anteriormente, vimos como crear listas personalizadas para crear una lista alfabética. Esta vez vamos a utilizar VBA para crear una sencilla subrutina que crea una lista personalizada con el rango seleccionado. Será particularmente útil si tenemos que crear varias listas, sin seguir la enrevesada ruta para hacerlo manualmente.

1. Escribimos el siguiente código en un módulo.

Sub Crear_lista_personalizada()
Application.AddCustomList Selection
End Sub

2. Seleccionamos el rango que compondrá los elementos de la lista.
3. Presionamos Alt+F8.
4. Seleccionamos Crear_lista_personalizada y clic sobre ejecutar.
5. La lista estará creada. Para usarla, escribimos en una celda un elemento de la misma y arrastramos el controlador de relleno.

Es importante señalar que el rango seleccionado debe de contener valores y no fórmulas. En caso contrario nos mostrará el error:

 

Entradas relacionadas:
Crear una lista alfabética automáticamente

Editando y ejecutando código en RStudio

Introducción

Al abrir RStudio en la consola aparece el símbolo > que nos indica que R está preparado para ejecutar código. Podemos escribirlo directamente en la consola y ejecutarlo al presionar Entrar. No obstante, en RStudio es recomendable usar el editor de código (source editor) para escribir y la consola para ejecutarlo.

Abrimos un R Script nuevo, File> New> R Script, para escribir nuestro código. Para ejecutar una línea situamos el cursor en la misma (o seleccionamos el bloque de texto que deseamos ejecutar) y presionamos Ctrl+Entrar o el botón Run.

En el editor de código disponemos de una pestaña para cada fichero abierto. También encontramos dos iconos: Find and Replace (Ctrl+F) y Code Tools. Con el primero buscamos y reemplazamos texto, con Code Tools accedemos a varias herramientas para editar nuestro código: completar automáticamente el código (Tabulador), obtener ayuda (F1), ver la definición de una función (F2) o convertir código en comentarios y viceversa (Ctrl+Mayús+C) por mencionar algunos.

Otros comandos en RStudio:

Ctrl+O - Abre un documento
Ctrl+Mayús+N - Crea un documento nuevo: R Script
Ctrl+S - Guarda el documento activo
Ctrl+2 - Situa el cursor en la consola.

Escribiendo código por primera vez

R admite operadores aritméticos como: + - = / * ^. Los dos puntos : son el operador para generar secuencias.

1 + 1  # Usamos R como una calculadora

## [1] 2

1:10  # Secuencia del 1 al 10 

##  [1]  1  2  3  4  5  6  7  8  9 10

letters[1:10]  # Secuencia de la 'a' a la 'j'

##  [1] "a" "b" "c" "d" "e" "f" "g" "h" "i" "j"

En la consola:

▲▼ Con las flechas de dirección navegamos el historial del código ejecutado.
Tabulador - Completa el código.
Esc - Borra el código escrito.
Ctrl+L - Limpia la consola.

Asignación

En R el operador para asignar es <- (menor que y signo menos). Se puede obtener presionando Alt+-. El símbolo < indica hacia el objeto al que asignamos el valor de una expresión. Si el objeto ya existe se sobreescribe. Los objetos creados permanecen la memoria de R y RStudio los muestra en (Environment, anteriormente Workspace)

resultado <- 1 + 1  # Creamos el objeto: resultado
resultado  # Escribimos el nombre del objeto para mostrarlo

## [1] 2

edit(resultado)  # Invoca el editor de texto. No permite cambiar el objeto
fix(resultado)  # Sí permite cambiar el objeto

Función c

La función c (combine) combina varios elementos en un vector.

x <- c(1, 9, 7, 7)  # Creamos el vector x
x  # Mostramos x

## [1] 1 9 7 7

x[2]  # Obtenemos el segundo elemento del vector x

## [1] 9

R tiene algunas palabras reservadas y algunas letras, que aunque no reservadas, no conviene utilizar: c, q, s, t, C, D, F, I, y T. Las cadenas de texto se delimitan con comillas simples o dobles. R distingue entre mayúsculas y minúsculas.

y <- c("n", "u", "b", "e")  # Creamos el vector x
y  # Mostramos x

## [1] "n" "u" "b" "e"

Y  # Y mayúscula genera un error pues no existe

## Error: object 'Y' not found

Panel superior derecho de RStudio

Muestra los objetos creados en la pestaña Environment y el historial del código ejecutado en la consola en History. Desde aquí también se pueden eliminar objetos y borrar líneas. Desde History podemos seleccionar líneas de código y copiarlas en la consola (To Console) o en el R Script activo (To Source)

Funciones básicas para comenzar

ls()  # Lista los objetos disponibles en el entorno de trabajo.
list.files()  # Lista los ficheros dentro del directorio de trabajo.
rm(x)  # Elimina el objeto x de la memoria.
rm(list = ls())  # Limpia todos los objetos del entorno de trabajo (Environment).
q()  # Finaliza la sesión actual de R.

Medir el tiempo de ejecución de subrutinas en VBA

En VBA tanto en Access como en Excel, podemos medir el tiempo transcurrido desde que se inició la subrutina hasta un punto determinado. Para mostrar el tiempo usamos la función MsgBox.

Option Explicit
Sub NuestraSubrutina()
  Dim t As Single 'Inicia el cronómetro
  t = Timer
  'NUESTRO CÓDIGO
  MsgBox Timer - t, vbInformation, "Segundos" ' Muestra el tiempo transcurrido
End Sub

El mensaje será:

Entradas relacionadas:
Mensaje emergente al abrir un fichero Ms Excel.

Distribución de frecuencias en Ms Excel

Problema

Al igual que en la entrada anterior, queremos calcular  la distribución de frecuencias para una serie de datos. En nuestro ejemplo, tenemos una tabla con las fechas de nacimiento de 100 personas. Queremos mostrar el número de personas nacidas en cada intervalo: <1923, 1923-1932, 1933-1942, etc.

Solución

1. Determinamos la amplitud del intervalo.

Calculamos la diferencia entre la fecha de nacimiento mínima y máxima, y la dividimos entre el número de intervalos deseados, 10 en nuestro caso. El rango de cada intervalo es de aproximadamente 10 años.






2. Creamos una tabla con 3 columnas.

Grupos: los intervalos dentro de los cuales deseamos agrupar los valores.
Intervalos: con los nombres de los grupos.
Frecuencia: seleccionamos el rango F2:F11, presionamos F2, introducimos la fórmula matricial FRECUENCIA y presionamos Ctrl+Mayús+Entrar.





Hemos creado a propósito un grupo menos (9) que el número de intervalos devueltos (10). Esto es así porque como indica la documentación:

El número de elementos de la matriz devuelta supera en una unidad el número de elementos de grupos. El elemento adicional de la matriz devuelta devuelve la suma de todos los valores superiores al mayor intervalo.


3. Gráfico.

Entradas relacionadas

• Distribución de frecuencias en Ms Access
  
• Distribución de frecuencias en R

Distribución de frecuencias en Ms Access

En estadística, la distribución de frecuencias muestra una agrupación de datos en categorías mutuamente excluyentes y el número de observaciones en cada categoría. En nuestro ejemplo, tenemos una tabla con las fechas de nacimiento de 100 personas. Queremos mostrar el número de personas nacidas en cada intervalo: 1913-1922, 1923-1932, 1933-1942, etc.

Para determinar la amplitud del intervalo, calculamos la diferencia entre la fecha de nacimiento mínima y máxima, y la dividimos entre el número de intervalos deseados, 10 en nuestro caso. El rango de cada intervalo es de aproximadamente 10 años.

Consulta para calcular la amplitud del intervalo

1. Creamos una tabla definiendo los intervalos.

Escribimos los nombres del campo Intervalo y los rangos de los intervalos en Min y Max:

Es importante señalar que el máximo de un intervalo no debe ser idéntico al mínimo del siguiente. Hay un día de diferencia. En caso contrario, correríamos el riesgo de que un dato se encuentre en ambos intervalos.

 2. Consulta de selección para calcular la distribución de frecuencias.

Añadimos las tablas Intervalos y Datos. No establecemos una combinación explícita entre ambas (combinación cruzada):

   - De Intervalos añadimos el campo Intervalos con los nombres de los mismos. 

   - De Datos añadimos el campo Id para contar el número de observaciones y Fecha para especificar el criterio que relaciona ambas tablas: Entre [Min] Y [Max]. 

3. Gráfico.

Entradas relacionadas:
Distribución de frecuencias en Ms Excel
Distribución de frecuencias en R

Listas numeradas en Google Docs

En Word usando listas numeradas, si insertamos un párrafo y apretamos el botón Numeración, la lista numerada continua con el siguiente elemento de la misma. En cambio, a día de hoy, Google Docs comienza la numeración en 1 tras cada párrafo.

Para corregir la secuencia de numeración en Google Docs:

1. Clic con el botón secundario del ratón y elegimos Volver a empezar la numeración.

2. En la ventana emergente, escribimos el número correspondiente y clic en Aceptar.

Sistemas de ecuaciones lineales en Excel: ESTIMACION.LINEAL

Una alternativa para resolver sistemas de ecuaciones lineales es usar la función ESTIMACION.LINEAL. Por ejemplo:

2x + 3y + 3z = 20

  x + 4y + 3z = 15

5x + 3y + 4z = 30

En Conocido_y, seleccionamos el rango b.
En Conocido_x, seleccionamos el rango A.

Al ser una fórmula matricial, selecciona el rango (tres celdas contiguas horizontalmente, p.ej.: I21:K21), inserta la fórmula y presiona Ctrl+Mayús+Entrar.

Si quisiéramos los coeficientes (x, y, z) en el orden correcto introducimos:

=INDICE(ESTIMACION.LINEAL($E$21:$E$23;$A$21:$C$23;0);1;COLUMNAS(A21:$C21)) 

Y arrastramos la fórmula dos celdas a la derecha.

Entradas relacionadas:
Sistemas de ecuaciones lineales en Excel