Numerar las celdas visibles con el autofiltro en Excel

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

• Contextures
• Subtotales

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

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

• Comparar dos vectores en R: elementos comunes y únicos
• Cómo comparar dos data frames en R
• Comparar el tiempo de ejecución de expresiones en R con microbenchmark

Referencias

• stackoveflow

Borrar registros de un data frame con sqldf en R

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

• Entradas que emplean sqldf

Referencias

• stackoveflow

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

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

• Etiquetar selectivamente los puntos de un diagrama de dispersión con ggplot2
• Etiquetar interactivamente los puntos de un diagrama de dispersión en R

Referencias

• stackoveflow

Conectar Excel a una base de datos en Access

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

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

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

• Aplicar una función a cada columna en R
• Aplicar una función a cada columna en R con dplyr

Referencias

• stackoverflow
• Apply Functions Over Array Margins
• Más información sobre funciones apply
• Median value

Comparar dos vectores en R: elementos comunes y únicos

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

• Cómo comparar dos data frames en R
• Eliminar duplicados de un data frame en R

Referencias

• stackoverflow
• Set operations
• Value matching

Función Nz como alternativa a coalesce en Ms Access

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

• Cálculo de expresiones con valores nulos en Ms Access
• Incompatibilidad de Excel con consulta de Access usando NZ

Referencias

• Función Nz