2015-10-30

Numerar las celdas visibles con el autofiltro en Excel

Title

Problema

Al filtrar un rango de datos con autofiltro, queremos que solamente se numeren las celdas visibles.

Solución

Utilizamos la función SUBTOTALES precedida de dos signos negativos. El primer argumento es 2 (CONTAR) para contar los números e ignorará en el encabezado como texto. En el segundo rango fijamos la fila como referencia absoluta A$1 o A$2. Arrastramos la fórmula hacia abajo.

=--SUBTOTALES(2;A$1:A2)

Notas

Utilizamos los dos signos negativos (operador unario) delante de subtotales porque si no, el autofiltro de Excel no reconocería la última fila del rango. Al haber una función SUBTOTAL en la última fila del rango, Excel interpreta que es una fila especial para calcular los totales, excluyéndola del filtro.

Si la columna que usamos para contar (columna A) contiene texto (columna B) en lugar de números, explearíamos como primer argumento de SUBTOTALES el 3 (CONTARA):

=--SUBTOTALES(3;B$2:B2)
En el caso de que nuestro rango incluya filas ocultas emplearemos como primer argumento 102 o 103 para pasarlas por alto.

=--SUBTOTALES(102;B$2:B2)

Referencias

2015-10-26

Una manera más eficiente de comparar números que con ifelse en R

Title

Problema

Tenemos el siguiente data frame.

       x  y
[1,]  -1 99
[2,]   5  4
[3,]  10 -2
[4,] 600  0
[5,] -16  1
[6,]   0 55

Y deseamos obtener un vector fruto de la siguiente comparación de x e y:

1. El menor valor en y si tanto x e y son positivos.
2. Cualquier valor positivo de y si x es negativo.
3. O dejar el valor de x si no se cumplen las dos condiciones anteriores.

Datos

dat <- structure(list(x = c(-1L, 5L, 10L, 600L, -16L, 0L), y = c(99L, 
4L, -2L, 0L, 1L, 55L)), .Names = c("x", "y"), class = "data.frame", row.names = c("[1,]", 
"[2,]", "[3,]", "[4,]", "[5,]", "[6,]"))

Soluciones

 # Extraemos los vectores
x <- dat[, 1]
y <- dat[, 2]
  • ifelse
  • El problema de esta opción es que no es muy eficiente computacionalmente.

    ifelse(y >= 0, ifelse(x < 0, y, ifelse(x > y, y, x)), x)
    
     [1] 99  4 10  0  1  0

  • Indexando
  • Esta opción es entre 5 y 6 veces más rápida.

    xz[(x < y & x >= 0)| y < 0] <- x[(x < y & x >= 0)| y < 0];z
    
     [1] 99  4 10  0  1  0
  • Sin indexar
  • Aún más rápida que la anterior.

    x * ((x < y & x >= 0) | y < 0) + y * ((x > y & y >= 0) | x < 0) 
    
     [1] 99  4 10  0  1  0

    Comparativa

    Empleamos el paquete microbenchmark para comparar el tiempo de ejecución de las 3 opciones.

    microbenchmark(
      if_else = ifelse(y >= 0, ifelse(x < 0, y, ifelse(x > y, y, x)), x),
      indexar= z[(x < y & x >= 0)| y < 0] <- x[(x < y & x >= 0)| y < 0],  
      sinindexar = x *((x < y & x >= 0)| y < 0)+ y * ((x > y & y >= 0)| x < 0),
        )
    
    Unit: microseconds
           expr    min      lq     mean median     uq     max neval cld
        if_else 43.023 47.1785 53.29921 53.289 53.290 107.067   100  b
        indexar  8.800  9.2900 10.71218  9.778 10.267  53.290   100  a 
     sinindexar  7.333  7.8230  8.89833  8.311  8.800  52.800   100  a 
    
    La opción ifelse es la menos eficiente. La opción indexar mejora sustancialmente la velocidad de ejecución. Finalmente, la opción sinindexar es algo más rápida aún que indexar. Existen opciones más rápidas incluso, que puedes ver en el enlace del apartado referencias. Sin embargo, quizá la ganancia en velocidad de ejecución no compensa la pérdida de legibilidad.

    Entradas relacionadas

    Referencias

    2015-10-23

    Borrar registros de un data frame con sqldf en R

    Title

    Problema

    Nuestro objetivo es borrar del siguiente data frame aquellos registros cuyo ID sea 8254700, 8641472 y 9022163.

    df <- data.frame(ID = c(8254700, 8641472, 9022163, 1111111, 2222222 ), 
                     field = c(1, 2, 3, 4, 5))
    
           ID field
    1 8254700     1
    2 8641472     2
    3 9022163     3
    4 1111111     4
    5 2222222     5
    

    Solución

    Para borrar esos tres registros necesitamos añadir a la sentencia iniciam "SELECT * FROM df" tras la sentencia de borrado o devolverá NULL , porque sqldf siempre devuelve el resultado de las sentencias de SQL dadas y DELETE no devuelve ningún resultado.

    library(sqldf)
    sqldf(c("DELETE FROM df WHERE ID IN (8254700, 8641472, 9022163)", "SELECT * FROM df"))
    

    Resultado

           ID field
    1 1111111     4
    2 2222222     5 
    

    Notas

    Si los IDs están en un vector separado, ids, entonces podemos obtener el mismo resultado con:

    ids <- c(8254700, 8641472, 9022163)
    fn$sqldf(c("DELETE FROM df WHERE ID IN (`toString(ids)`)", "SELECT * FROM df"))
    
    o para examinar los resultado intermedios:

    idString <- toString(ids)
    idString
    ## [1] "8254700, 8641472, 9022163"
    
    sql <- fn$identity(c("DELETE FROM df WHERE ID in ($idString)", "SELECT * FROM df"))
    sql
    ## [1] "DELETE FROM df WHERE ID in (8254700, 8641472, 9022163)"
    ## [2] "SELECT * FROM df"   
    
    sqldf(sql)
    ##            ID field1
    ## 1 1111111      4
    ## 2 2222222      5
    

    Entradas relacionadas

    Referencias

    2015-10-21

    Etiquetar un diagrama de dispersión en ggplot2 basándonos en otra columna

    Title

    Problema

    Deseamos etiquetar solamente aquellos puntos de un diagrama de dispersión que cumplan una condición basada en otra columna de un data frame. En nuestro ejemplo, solamente aquellos puntos de la columna b cuyo valor sea mayor de 0.5

    set.seed(1)
    x <- data.frame(a = 1:10, b = rnorm(10))
    x$lab <- letters[1:10]
    
        a          b lab
    1   1 -0.6264538   a
    2   2  0.1836433   b
    3   3 -0.8356286   c
    4   4  1.5952808   d
    5   5  0.3295078   e
    6   6 -0.8204684   f
    7   7  0.4874291   g
    8   8  0.7383247   h
    9   9  0.5757814   i
    10 10 -0.3053884   j
    

    Solución

  • Opción 1
  • Crear un subconjunto (subset) dentro de la función geom_text.

    ggplot(data = x, aes(a, b, label = lab)) + 
      geom_point() + 
      geom_text(data = subset(x, abs(b) > 0.2), vjust = 1.5)
    

  • Opción 2
  • Asignando NA a aquellos valores que no queremos etiquetar

    x$lab[!(abs(x$b) > 0.5)] <- NA
    ggplot(data = x, aes(a, b, label = lab)) + 
        geom_point() + 
        geom_text(vjust = 1.5) 
    
    Usando qplot:
    qplot(a, b, data = x, label = lab, geom = c('point','text'), vjust = 1.5)
    

    Resultado

    Entradas relacionadas

    Referencias

    2015-10-19

    Conectar Excel a una base de datos en Access

    Title Conectar Excel con Access nos permite trabajar directamente con información procedente de Access. Así ahorramos tiempo, evitando copiar repetidamente los datos y posibles errores. Lo primero que necesitamos hacer es crear una conexión con la tabla o consulta de la base de datos de Access. Vamos a usar como ejemplo la base de datos en Access Neptuno.

    Crear conexión de datos entre Excel y Access

    1. En la ficha Datos hacemos clic en Desde Access.
    2. En el cuadro de diálogo Seleccionar archivo de origen de datos, buscamos la ubicación del fichero de Access y hacemos clic en Abrir.
    3. En Seleccionar tabla, elegimos aquella tabla o consulta que deseamos importar y vincular.
    4. En Importar Datos, dejamos las opciones por defecto. Nos aseguramos de elegir la celda de destino correcta.
    5. La conexión se habrá realizado y la tabla aparecerá en Excel.

    Actualizar la conexión

    Ahora podremos analizar la información proveniente de Access. Para asegurarnos de que Excel refleja la última información disponible, actualizaremos la conexión.

  • Forzar la actualización
  • Bien desde la ficha Datos clic en Actualizar todo. O bien haciendo clic sobre una celda de la tabla y desde la ficha Herramientas de tabla.

  • Personalizar la actualización
  • Personalizaremos la actualización de la conexión con Access para automatizar la actualizando el archivo cada 15 minutos y automáticamente cada vez que lo abramos.

    1. En la ficha Datos, clic en Conexiones y después en el botón de Propiedades.
    2. En Propiedades de conexión activamos las casillas Actualizar cada e indicamos los minutos deseados, y la casilla Actualizar al abrir el archivo.

    Entradas relacionadas

  • Importar datos desde la web a Excel
  • Importar ficheros CSV en Excel mediante VBA
  • Actualizar origen de datos de Excel: atajo y con VBA
  • No es posible editar o actualizar los vínculos o el origen de datos
  • Consulta SQL en Excel mediante Microsoft ActiveX Data Objects (ADO)
  • Conectar una consulta de unión (union query) de Access desde Excel
  • Referencias

  • Conectarse con datos externos
  • Propiedades de conexión
  • 2015-10-17

    Substraer a cada valor de una fila la mediana de su respectiva fila en R

    Title

    Problema

    Queremos substraer a cada valor de una fila la mediana de la fila.

    Datos

    Creamos una matriz de 5x10.

    set.seed(24)
    m1 <- matrix(sample(0:9, 10*5, replace=TRUE), ncol=5)
    
          [,1] [,2] [,3] [,4] [,5]
     [1,]    2    6    0    3    4
     [2,]    2    3    5    2    3
     [3,]    7    6    7    3    2
     [4,]    5    6    1    1    2
     [5,]    6    3    2    9    0
     [6,]    9    9    6    2    0
     [7,]    2    1    0    7    3
     [8,]    7    0    5    6    3
     [9,]    8    5    6    6    2
    [10,]    2    1    0    9    3

    Solución

    m1 - apply(m1, 1, median)
    
          [,1] [,2] [,3] [,4] [,5]
     [1,]   -1    3   -3    0    1
     [2,]   -1    0    2   -1    0
     [3,]    1    0    1   -3   -4
     [4,]    3    4   -1   -1    0
     [5,]    3    0   -1    6   -3
     [6,]    3    3    0   -4   -6
     [7,]    0   -1   -2    5    1
     [8,]    2   -5    0    1   -2
     [9,]    2   -1    0    0   -4
    [10,]    0   -1   -2    7    1
    Si queremos conocer la mediana de cada fila.

    apply(m1, 1, median)
    
    [1] 3 3 6 2 3 6 2 5 6 2
    

    Notas

    Empleamos la función apply para aplicar una función a cada fila, en este caso la función median (mediana). En el segundo argumento de la función 1 indica que la función será aplicada sobre las filas (fila a fila). Las funciones apply son muy útiles en R pues evitan el uso de bucles (loops), pudiendo aplicar funciones a los márgenes, filas o columnas, de una matriz.

    Entradas relacionadas

    Referencias

    2015-10-08

    Comparar dos vectores en R: elementos comunes y únicos

    Title

    Problema

    Deseamos comparar dos vectores en R y obtener los elemenos comunes y únicos de ambos.

    Datos

    # Dos vectores
    [1] "a" "b" "c" "d" "e" "f" "g" "h" "i" "j"
    [1] "f" "g" "h" "i" "j" "k" "l" "m" "n" "o"
    
    a <- letters[seq(from = 1, to = 10)]
    b < -letters[seq(from = 6, to = 15)]
    

    Soluciones

  • Elementos comunes
  • # Múltiples opciones
    intersect(a, b)
    b[b %in% a]
    a[a %in% b]
    
    [1] "f" "g" "h" "i" "j"
  • Elementos únicos de a
  • setdiff(a, b)
    a[!a %in% b]
    a[b %in% a]
    
    [1] "a" "b" "c" "d" "e"
  • Elementos únicos de b
  • setdiff(b, a)
    b[!b %in% a]
    b[a %in% b]
    
    [1] "k" "l" "m" "n" "o"

    Notas

    Merece la pena señalar que intersect y setdiff descartarán los valores duplicados en los argumentos. Mientras que %in% conservará los duplicados. Por lo que si tuviéramos duplicados en loos vectores obtendríamos diferentes resultados. Por ejemplo, introduciendo un elemento duplicado 'a' en el vector a.

    a <- c(a, "a")
    [1] "a" "b" "c" "d" "e" "f" "g" "h" "i" "j" "a"
  • setdiff descarta los duplicados
  • setdiff(a, b)
    [1] "a" "b" "c" "d" "e"
  • El operador "[]" con %in% los conserva
  • a[!a %in% b]
    [1] "a" "b" "c" "d" "e" "a"

    Entradas relacionadas

    Referencias

    2015-10-04

    Función Nz como alternativa a coalesce en Ms Access

    Title

    Problema

    Partimos de la siguiente tabla. Deseamos fusionar las columnas num1 y num2 en una nueva, de manera que no hay ningún valor nulo.

    Solución

    En Ms Access no disponemos de la función coalesce que sirve precisamente para este propósito. Como alternativa empleamos la función nz. Creamos una consulta con un campo calculado —comb— con la siguiente expresión.

    comb: Nz([num1];[num2])*1
    

  • SQL
  • SELECT Tabla.id, Tabla.nombre, Tabla.num1, Tabla.num2, Nz([num1],[num2])*1 AS comb
    FROM Tabla;
    
    

    Resultado

    Notas

    La función Nz por defecto —si dejamos el segundo argumento en blanco— devuelve un cero cuando un campo es nulo. En este caso, le especificamos otro campo para devolver ese valor en su lugar. Multiplicamos por 1 la expresión para que el tipo de datos del nuevo campo sea número en lugar de texto. Sin multiplicar por 1 el resultado sería texto, con los números alineados a la izquierda.

    Más de dos campos

    Podemos seguir el mismo procedimiento para más de dos campos, anidando la función Nz. Por ejemplo con 3 campos:

    comb:Nz([num1];Nz([num2];[num3]))*1
    

    Entradas relacionadas

    Referencias

    Nube de datos