Usar un cuadro combinado para buscar al teclear en Access

Queremos poder filtrar los registros de un formulario continuo mediante un cuadro combinado. Según tecleemos el formulario se filtrará y permitirá coincidencias parciales en la búsqueda. Utilizaremos como ejemplo la base de datos Neptuno.

Solución

1. Creamos un formulario continuo basado en la tabla Clientes.
1. En el panel de navegación, clic en la tabla Clientes.
2. En la pestaña Crear, clic en Más formularios > Varios elementos.


2. Insertamos un cuadro combinado independiente y le damos el nombre de Cuadro_combinado

3. En la Hoja de propiedades del cuadro combinado, en la pestaña Datos modificamos estas tres propiedades


En Origen de fila, introducimos la siguiente consulta o la creamos con el generador de consultas (clic en los 3 puntos suspensivos).

SELECT Clientes.NombreCompañía FROM Clientes; 

4. En la Hoja de propiedades del cuadro combinado, en la pestaña Eventos, clic en Al cambiar y copiamos el siguiente código:

   Private Sub Cuadro_combinado_Change2()
   ' Elimina el filtro del formulario,
   ' si el cuadro combinado está vacío
   If Nz(Me.Cuadro_combinado.Text) = "" Then
       Me.Form.Filter = ""
       Me.FilterOn = False
   ' Si se filtra un ítem del cuadro combinado,
   ' realiza una búsqueda exacta en el formulario
   ElseIf Me.Cuadro_combinado.ListIndex <> -1 Then
      Me.Form.Filter = "[NombreCompañía] = '" & _
         Replace(Me.Cuadro_combinado.Text, "'", "''") & "'"
      Me.FilterOn = True
   ' Filtra en formulario con una coincidencia parcial
   Else
       Me.Form.Filter = "[NombreCompañía] Like '*" & _
          Replace(Me.Cuadro_combinado.Text, "'", "''") & "*'"
       Me.FilterOn = True
   End If
   ' Mueve el cursor al final del cuadro combinado
   Me.Cuadro_combinado.SetFocus
   Me.Cuadro_combinado.SelStart = Len(Me.Cuadro_combinado.Text)
   End Sub
   

Empleamos la función Replace para reemplazar el apóstrofe simple ' por el doble ''. Pues si no, al buscar compañías con apóstrofe generaría un error.

Resultado

Ahora podemos o bien seleccionar un elemento de la lista del cuadro combinado, o teclear la cadena de texto que deseemos. Como se puede ver en la imagen, realiza una búsqueda parcial de las cadenas de texto: super, comi or chin.

Referencias

• Cuadros combinados en cascada en Ms Access
• Seleccionar elementos en cuadros combinados o de lista

Entradas relacionadas

• Using a Combo Box to search as you type

Calcular el valor mínimo excluyendo los ceros en Excel

Problema

Deseamos calcular el valor mínimo de un rango de celdas excluyendo los ceros. En nuestro ejemplo tenemos una serie de productos y sus precios en tres periodos de tiempo. En las tres celdas resaltadas en naranja el precio mínimo es cero cuando deseamos que sean 10, 5,95 y 1,95 respectivamente.

Solución

Utilizamos la función MIN en la que incluimos la condición para evaluar que las celdas del rango sean mayores que cero. Al ser una fórmula matricial. Al ser una fórmula matricial, presionamos Ctrl+Mayús+Entrar antes de salir de F2, y arrastramos el controlador de relleno hacia abajo:

{=MIN(SI(B2:D2>0;B2:D2))}

Alternativa

Hay otras alternativas más eficientes a la hora de realizar el cálculo que la anterior fórmula matricial (suelen requerir más cálculos). Por ejemplo la siguiente opción, aunque menos breve y, quizá, más difícil de recordar.

=K.ESIMO.MENOR(B2:D2;CONTAR.SI(B2:D2;0)+1)

Entradas relacionadas

Descartar variables de un data frame por su nombre

Problema

Deseamos excluir variables de un data frame de acuerdo a su nombre. En nuestro ejemplo usamos el data frame iris, y queremos descartar las variables Sepal.Length y Petal.Width.

head(iris)

  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa
6          5.4         3.9          1.7         0.4  setosa

Soluciones

Hay múltiples opciones.

Paquete base

# Opción 1
iris[, -which(names(iris) %in% c("Sepal.Length", "Petal.Width"))]
# Opción 2
iris[ , !names(iris) %in% c("Sepal.Length","Petal.Width")]
# Opción 3
subset(iris, select = -c(Sepal.Length, Petal.Width)) 

dplyr

library(dplyr)
iris %>% select(-c(Sepal.Length, Petal.Width))

data.table

library(data.table)
DT = as.data.table(iris)
DT[ , !names(DT) %in% c("Sepal.Length", "Petal.Width"), with = FALSE]
# Otra opción
subset(DT, select=-c(Sepal.Length, Petal.Width)) 

Resultado

Las tres variables restantes. Solamente mostramos las 6 primeras filas

  Sepal.Width Petal.Length Species
1         3.5          1.4  setosa
2         3.0          1.4  setosa
3         3.2          1.3  setosa
4         3.1          1.5  setosa
5         3.6          1.4  setosa
6         3.9          1.7  setosa

Notas

Hay diferencias sutiles entre la primera opción con el paquete base -which y la segunda con la función !. Si con -which especificamos nombres que no encuentra en el data frame devolverá un data frame vacío con cero columnas. Mientras que si sucede lo mismo con !, devolverá el data frame original sin modificar.

La sintaxis con dplyr es bastante sencilla. Con data.table es muy similar a las utilizadas con el paquete base. No obstante, es necesario especificar el argumento with = FALSE o devolverá un vector lógico. También podemos emplear con data.table la función subset.

Entradas relacionadas

Referencias

• stackoveflow

Cómo cambiar números positivos a negativos en Excel

Problema

Queremos cambia el signo de un rango de datos de positivos a negativos.

Solución

1. Escribimos en una celda cualquiera un -1 y lo copiamos, Ctrl + C.

2. Seleccionamos el rango de destino que queremos cambiar, clic con el botón secundario y seleccionamos Pegado especial (Ctrl + Mayús + V).

3. En el cuadro de diálogo de Pegado especial seleccionamos Todo y Multiplicar y clic en Aceptar.

4. Resultado

En el caso de que el rango que queramos cambiar contenga fórmulas, se añadirá al final un -1. Por ejemplo: =(A1)*-1

Entradas relacionadas

• Crear atajo con VBA para pegar valores en Excel

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