2016-01-31

Obtener el nombre y la ruta de las conexiones de un libro de Excel VBA

Title

Problema

Queremos obtener los nombres y rutas de las conexiones de un libro de Excel mediante VBA, sin necesidad de ir a la pestaña Datos, y ñuego a Conexiones.

Solución

Mediante código VBA podemos extraer el nombre y la ruta de las conexiones existentes en un libro. En el prime bucle obtenemos los nombres y en el segundo la ruta. Este segundo bucle, que incluye la propiedad TextConnection sólo funciona con Excel 2013 y versiones posteriores.

Sub ObtenerNombreRutaConexion()
    Dim conn As WorkbookConnection
    For Each conn In ActiveWorkbook.Connections
      Debug.Print conn.Name
    Next conn
    For Each conn In ActiveWorkbook.Connections
      Debug.Print conn.TextConnection.Connection
    Next conn
End Sub

Resultado

En la ventana Inmediato del editor (VBE) obtendremos el nombre y la ruta de las conexiones.

Entradas relacionadas

2016-01-28

Ordenar un data frame por la frecuencia de una columna en R

Title

Problema

Tenemos el siguiente data frame. Y, sin agrupar, queremos ordenarlo de mayor a menor por la frecuencia de la columna Salary. Es decir, 1002 se repite tres veces en nuestro data frame, 1001 y 3001 dos veces y resto una vez.

   Region  ID Salary
1       1  A1    100
2       1  A2   1001
3       1  A3   2000
4       1  A4   2431
5       1  A5   1001
6       2  A6   1002
7       2  A7   1002
8       2  A8   1002
9       3  A9   3001
10      3 A10   3001
11      3 A11   4001
df <- structure(list(Region = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 
3L, 3L), ID = c("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", 
"A9", "A10", "A11"), Salary = c(100L, 1001L, 2000L, 2431L, 1001L, 
1002L, 1002L, 1002L, 3001L, 3001L, 4001L)), .Names = c("Region", 
"ID", "Salary"), class = "data.frame", row.names = c(NA, -11L
))

Solución

  • Paquete base
  • Con transform creamos la columna frecuencia (freq) y después con order, ordenamos por ella. Por defecto es en orden ascendente al preceder la columna del signo menos, indicamos que sea en orden descendente.

    df <- transform(df, freq = ave(seq(nrow(df)), Salary, FUN = length))
    df[order(-df$freq), ]
    
       Region  ID Salary freq
    6       2  A6   1002    3
    7       2  A7   1002    3
    8       2  A8   1002    3
    2       1  A2   1001    2
    5       1  A5   1001    2
    9       3  A9   3001    2
    10      3 A10   3001    2
    1       1  A1    100    1
    3       1  A3   2000    1
    4       1  A4   2431    1
    11      3 A11   4001    1
    
  • dplyr
  • library(dplyr)
    df %>%
      add_count(Salary) %>% 
      arrange(-n)
    
       Region  ID Salary n
    1       2  A6   1002 3
    2       2  A7   1002 3
    3       2  A8   1002 3
    4       1  A2   1001 2
    5       1  A5   1001 2
    6       3  A9   3001 2
    7       3 A10   3001 2
    8       1  A1    100 1
    9       1  A3   2000 1
    10      1  A4   2431 1
    11      3 A11   4001 1
    

Referencias

2016-01-25

Pegar una lista filtrada con Excel en las celdas visibles

Title

Problema

Deseamos copiar el rango de celdas de una lista filtrada y pegar el resultado en aquellas celdas visibles.

Filtramos las lista dejando los números pares, copiamos el rango y lo pegamos en una columna a la derecha E.

  • Resultado incorrecto
  • Excel copia el resultado también en las celdas no visibles, las celdas E4 y E6 (números 3 y 5 de la columna C).

    Solución

  • Opción 1
    1. Copiamos el rango filtrado
    2. Seleccionamos el rango de destino.
    3. Presionamos Ctrl+D. O bien en la pestaña Inicio, en el grupo Edición, clic en Rellenar y, después, clic en Hacia la derecha.
  • Opción 2
    1. En el rango de destino, hacemos referencia a la primera celda del rango de origen: =C3 y arrastramos el controlador de relleno hacia abajo.

    Resultado

    Ahora obtenemos el resultado correcto. Si quitamos el autofiltro, observamos como el resto de celdas han quedado vacías.

    Entradas relacionadas

    2016-01-21

    Dividir un vector en intervalos separados por ceros

    Title

    Problema

    Tenemos el siguiente vector.

    111 00 111 -1-1-1 000 11
    
    Y deseamos dividirlo en intervalos separados por los ceros.

    111 
    111 -1-1-1 
    11
    

    Solución

    x <- c("111 00 111 -1-1-1 000 11")
    x <- gsub(" ", "", x , fixed = TRUE)
    x <- unlist(strsplit(x, split="0+"))
    x
    
    [1] "111"       "111-1-1-1" "11"  
    
    O bien.

    split(x , 1:3)
    $`1`
    [1] "111"
    
    $`2`
    [1] "111-1-1-1"
    
    $`3`
    [1] "11"
    

    Referencias

    2016-01-17

    Project Euler - Problema 7 en R

    Title Continuamos con los problemas planteados en Project Euler.

    Problema

    By listing the first six prime numbers: 2, 3, 5, 7, 11, and 13, we can see that the 6th prime is 13.
    What is the 10001st prime number?

    Si listamos los primersos seis números primos: 2, 3, 5, 7, 11, y 13, observamos que el sexto número primos es 13.
    ¿Cuál es el número primo en la posición 10.001?

    Solución

    La función Primes del paquete numbers nos permite generar una lista de números primos. Después extraemos el elemento 10.001 de la lista.

    require(numbers)
    Primes(120000)[10001]
    
    [1] 104743
    

    Referencias

    2016-01-09

    Cómo añadir etiquetas a los extremos de una leyenda continua en ggplot2

    Title

    Problema

    Queremos añadir etiquetas a los extremos de una legenda continua en ggplot2. Así podremos saber el mínimo y el máximo.

    library(ggplot2)
    d <- subset(diamonds, price >= 257 & price <= 8888)
    ggplot(d, aes(depth, carat, colour = price)) +
      geom_point() +
      scale_colour_gradient(limits = c(257, 8888))
    

    Se observa en la leyenda como las etiquetas van del 2.000 al 8.000 pero no sabemos ni el mínimo ni el máximo.

    Solución

    Dentro de scale_colour_gradient, especificamos los argumentos breaks y labels.

    ggplot(d, aes(depth, carat, colour = price)) +
      geom_point() +
      scale_colour_gradient(limits = c(257, 8888), 
                            breaks = c(257, 2000, 4000, 6000, 8000, 8888),
                            labels = c(257, 2000, 4000, 6000, 8000, 8888))
    

    Ahora hemos añadido a la leyenda el mínimo 257 y el máximo 8.888.

    Extra

    Si queremos que en lugar de una escala de azules, parta de un color diferente, por ejemplo el rojo, añadimos el argumento low. Si queremos modificar el color superior introduciríamos el argumento high.

    ggplot(d, aes(depth, carat, colour = price)) +
      geom_point() +
      scale_colour_gradient(limits = c(257, 8888), 
                            breaks=c(257, 2000, 4000, 6000, 8000, 8888),
                            labels=c(257, 2000, 4000, 6000, 8000, 8888), low = "red")
    

    Referencias

    Entradas relacionadas

    2016-01-06

    Cómo buscar y reemplazar en varias hojas en Excel

    Title

    Problema

    Queremos realizar las búsqueda una cadena texto en varias hojas de Excel.

    Solución

  • Opción 1
    1. Cuadro de diálogo Buscar o reemplazar: Ctrl + B o Ctrl +L
    2. Clic en Opciones, Dentro de: Libro, escribimos la cadena de búsqueda en el cuadro buscar y clic en Buscar todos.
    3. Mismo procedimiento para Reemplazar
  • Opción 2
    1. Seleccionamos las hojas en las que deseamos buscar.
    2. - Para hojas no contiguas Ctrl presionado y clic en las hojas deseadas.
      - Para hojas contiguas mantenemos presionado Mayús.
      - Para seleccionar todas las hojas de un libro, Mayús y clic en la primera y la última del libro. O bien botón secundario sobre cualquier hoja y clic en Seleccionar todas las hojas.

    3. En la ficha Inicio, en el grupo Edición, clic en Buscar y seleccionar. Luego en Buscar o Reemplazar. O mediante el atajo: Ctrl + B o Ctrl + L
    4. En el cuadro correspondiente, escribimos el texto deseado. Y luego en el botón correspondiente, si deseamos buscar o reemplazar el siguiente elemento o todos.

    Resultado

    En el ejemplo anterior, hemos buscado en todas las hojas todos los resultados de la cadena de texto que empieza por text. Para ordenar ascendentemente los resultados por hoja, hacemos clic sobre la columna hoja en la lista con las celdas encontradas.

    Entradas relacionadas

    Referencias

    2016-01-02

    Cómo contar los elementos únicos de un campo en una consulta de Access

    Title

    Problema

    Deseamos contar el número de elementos únicos de un campo en Access. Por ejemplo el número de colores únicos de la siguiente tabla.

    Solución

    En otras aplicaciones de bases de datos como Oracle, MySQL o SQL Server, podríamos emplear DISTINCT dentro de la función de agregado COUNT.

    SELECT COUNT(DISTINCT Color) AS N 
    FROM tabla1
    

    Desafortunadamente, Access no permite utilizar COUNT(DISTINCT [Campo]). Por tanto emplearemos la siguiente consulta. Primero creamos una subconsulta donde seleccionamos los elemento únicos y después contamos los elementos de la misma.

    SELECT COUNT(*) AS N
    FROM
    (SELECT DISTINCT Color
    FROM Tabla1)
    

    Otra alternativa, sería la siguiente. Sin embargo, no evitamos crear una subconsulta, es menos concisa y empleamos una agrupación sin ninguna función de agregado.<`>

    SELECT COUNT(*) AS N
    FROM
    (SELECT Color
    FROM Tabla1
    GROUP BY Color)
    

    Entradas relacionadas

    Referencias

    Nube de datos