funciones de base de datos en excel
DESCRIPTION
funciones de base datos ExcelTRANSCRIPT
Funciones de base de datos en ExcelLas funciones de base de datos en Excel facilitan nuestro trabajo con información que está organizada como una bases de datos, es decir, que se encuentra en un rango de celdas donde la primera fila tiene los títulos de cada columna.
Estas funciones nos permiten contar, sumar, multiplicar los valores de una columna que cumplen con los criterios especificados e inclusive podremos extraer un registro que cumpla con ciertas condiciones.
Excel dispone de un conjunto de funciones que comienzan con las iniciales BD y que sirven para trabajar con bases de datos.
Se usan de un modo similar a los criterios de selección en las Consultas de Access. A los lectores que conozcan Access les resultará familiar su manejo.
Éstas son las funciones BD:
FUNCIÓN INGLÉS DESCRIPCIÓN
BDCONTAR DCOUNT Cuenta las celdas que contienen números en el campo (columna) de registros de la base de datos que cumplen las condiciones especificadas.
BDCONTARA DCOUNTA Cuenta el número de celdas que no están en blanco en el campo (columna) de los registros de la base de datos que cumplen las condiciones especificadas.
BDDESVEST DSTDEV Calcula la desviación estándar basándose en una muestra de las entradas seleccionadas de una base de datos.
BDDESVESTP DSTDEVP Calcula la desviación estándar basándose en la población total de las entradas seleccionadas de una base de datos.
BDEXTRAER DGET Extrae de una base de datos un único registro que coincide con las condiciones especificadas.
BDMAX DMAX Devuelve el número máximo en el campo (columna) de registros de la base de datos que coinciden con las condiciones especificadas.
BDMIN DMIN Devuelve el número menor del campo (columna) de registros de la base de datos que coincide con las condiciones especificadas.
BDPRODUCTO DPRODUCT Multiplica los valores del campo (columna) de registros en la base de datos que coinciden con las condiciones especificadas.
BDPROMEDIO DAVERAGE Obtiene el promedio de los valores de una columna, lista o base de datos que cumplen las condiciones especificadas.
BDSUMA DSUM Suma los números en el campo (columna) de los registros que coinciden con las condiciones especificadas.
BDVAR DVAR Calcula la varianza basándose en una muestra de las entradas seleccionadas de una base de datos.
BDVARP DVARP Calcula la varianza basándose en la población total de las entradas seleccionadas de una base de datos.
Instructora: America Sabalu S 1
Funciones debase de datos – excel 2013
Todas tienen la misma sintaxis:
Sintaxis: FUNCIÓN_BD(base_de_datos;nombre_de_campo;criterios)
base_de_datos: Es la tabla o base de datos.
nombre_de_campo: Es el nombre de la columna de la tabla sobre la que se va a realizar el cálculo.
criterios: Es el rango de celdas que contiene las condiciones que se van a utilizar en el cálculo.
El rango de criterios puede colocarse en cualquier lugar pero, para permitir la adición de nuevos datos, se desaconseja situarlo debajo de la tabla.
Vamos a emplear la función BDCONTAR para ilustrar cómo se usan las funciones BD.
Utilizaremos una base de datos ficticia que colocaremos en el rango B9:F41, dejando las filas 1 a 7 para situar los criterios. La fórmula la pondremos en H10.
Primer ejemplo
Contar las veces que la Empresa Ascensores J & C ha hecho Aportaciones comprendidas entre 150 y 300, teniendo, al mismo tiempo, algún valor en la columna Devolución.
Instructora: America Sabalu S 2
Funciones debase de datos – excel 2013
Copiamos los encabezamientos de la base de datos en B2:F2. Como el criterio que vamos a emplear requiere hacer dos comprobaciones en el campo Aportación, necesitamos dos celdas con este título. Por tanto, escribimos Aportación en G2.
En la celda B3, escribimos:="=Ascensores J & C" [Excel mostrará: =Ascensores J & C]
En C3:>150
En G3:<300
Los tres criterios están en la misma fila. Esto significa que están vinculados mediante el operador Y. Dicho de otra forma: (El campo Empresa contiene Ascensores J & C) Y (Aportación es mayor que 150) Y(Aportación es menor que 300).
Como queremos contar el número de celdas no vacías de la columna Devolución que cumplen los tres criterios, la fórmula que pondremos en H10 será:
En H10:=BDCONTAR(B9:F41;D9;B2:G3) [Resultado: 2]
El primer argumento es el rango que ocupa la base de datos; el segundo, es el campo sobre el que vamos a
Instructora: America Sabalu S 3
Funciones debase de datos – excel 2013
aplicar la función (contar registros); el tercero, el el rango que ocupa los criterios. El segundo argumento, D9, podemos sustituirlo por el nombre del campo. Si lo hacemos así, la fórmula sería:=BDCONTAR(B9:F41;"Devolución";B2:G3)
Segundo ejemplo
Contar las veces que Ascensores J & C ha hecho Aportaciones mayores que 300 o menores que 200 y haya algún dato en la columna Devolución.
Los criterios deberán ser:
Cuando se usa el operador O los criterios van en filas distintas.
En este caso, la fórmula será:
En H10:=BDCONTAR(B9:F41;D9;B2:C4) [Resultado: 2]
Tercer ejemplo
Contar las veces que cualquier empresa distinta de Ascensores J & C haya hecho Aportaciones mayores que 200 y haya algún dato en la columna Devolución.
En H10:=BDCONTAR(B9:F41;D9;B2:C3) [Resultado: 8]
Cuarto ejemplo
Contar las veces que cualquier empresa, excluidas Ascensores J & C y Decoraciones Eder, haya hecho Aportaciones mayores que 200 y haya algún dato en la columna Devolución.
Instructora: America Sabalu S 4
Funciones debase de datos – excel 2013
En H10:=BDCONTAR(B9:F41;D9;B2:H3) [Resultado: 8]
Quinto ejemplo
Contar celdas no vacías de Devolución que cumplan: Ascensores J & C tenga Aportación entre 150 y 300, O Decoraciones Eder tenga Rendimiento=6, O Decoraciones Eder tenga Beneficios >3500, O Pascual Reina tenga Aportaciones >84
––
En H10:=BDCONTAR(B9:F41;"Devolución";B2:G6) [Resultado: 6]
Sexto ejemplo
Contar celdas no vacías de Devolución que cumplan: La Empresa no debe ser Ascensores J & C La Empresa no debe ser Metalkarma, S.L. Beneficio menor que la media de beneficios de todas las empresas
–Estamos ante un caso complejo ya que no conocemos el promedio de la columna Beneficio para poner el criterio. Podemos calcularlo en una celda vacía o poner la fórmula correspondiente en la zona de criterios. El primer método es poco recomendable ya que requiere cambiar la fórmula si se modifica algún valor de la columna Beneficio. Veamos cómo se haría.
En H14 (o cualquier otra celda vacía):=PROMEDIO(F10:F41) [Resultado: 2.565,67]
Conocido el promedio, ponemos los criterios:
Instructora: America Sabalu S 5
Funciones debase de datos – excel 2013
La fórmula en H10 sería:=BDCONTAR(B9:F41;"Devolución";B2:G3) [Resultado: 14]
Es mejor utilizar el segundo método: poner una fórmula en la zona de criterios. Sin embargo, antes hay que conocer una serie de condiciones de obligado cumplimiento (extraídas de la ayuda de Excel): La fórmula se debe evaluar como VERDADERO o FALSO. Puesto que está utilizando una fórmula, escriba la fórmula como lo haría normalmente, pero no la escriba de la forma siguiente: =''=entrada'' No utilice rótulos de columnas para los rótulos de los criterios; deje los rótulos de criterios en blanco o utilice uno que no sea un rótulo de columna incluido en el rango. Si en la fórmula utiliza un rótulo de columna en lugar de una referencia relativa a celda o un nombre de rango, Excel presenta un valor de error, como por ejemplo #¿NOMBRE? o #¡VALOR!, en la celda que contiene el criterio. Puede pasar por alto este error, ya que no afecta a la manera en que se filtra el rango. La fórmula que utilice con el fin de generar los criterios debe utilizar una referencia relativa para hacer referencia a la celda correspondiente de la primera fila. Todas las demás referencias usadas en la fórmula deben ser referencias absolutas. A la nueva columna de la zona de criterios le llamaremos Auxiliar y la fórmula será:
En H3: =F10<PROMEDIO($F$10:$F$41) [Resultado: FALSO]
F10 es la primera celda de la columna con la que vamos a hacer el cálculo (en nuestro caso la media aritmética). Debe ser una referencia relativa (no lleva signo $). Todas las demás referencias deben ser absolutas (llevan signo $).
En H10: =BDCONTAR(B9:F41;"Devolución";B2:H3) [Resultado: 14]
Séptimo ejemplo
Contar celdas no vacías de la columna Devolución de las empresas que sean sociedades anónimas (S.A.) o sociedades limitadas (S.L.)
En este caso tendremos que usar caracteres comodín: asterisco (*) e interrogación (?). El asterisco sustituye a un número indeterminado de caracteres; la interrogación, solamente a uno.
Instructora: America Sabalu S 6
Funciones debase de datos – excel 2013
Si entre los elementos buscados hay una interrogación o un asterisco, para incluirlo en la búsqueda debe ir precedido de la tilde (~).
En H10: =BDCONTAR(B9:F41;"Devolución";B2:B3) [Resultado: 6]
Instructora: America Sabalu S 7
Funciones debase de datos – excel 2013
Listas aleatorias (1 de 2)El problema de hoy consiste en ordenar aleatoriamente una lista de nombres.
Excel dispone de dos funciones muy parecidas, ALEATORIO y ALEATORIO.ENTRE, que generan números al azar. En el primer caso, los números generados están comprendido entre 0 y 1. En el segundo, entre los dos valores que fijemos. En este ejercicio, emplearemos la función ALEATORIO.
Dada una lista de nombres (C1:C9), queremos que Excel los ordene al azar cada vez que pulsemos la tecla F9.
En la columna B, con la función ALEATORIO, creamos una serie de números aleatorios comprendidos entre 0 y 1. ¿Por qué en la columna B? Pronto comprenderemos la razón.
En B2:
=ALEATORIO() [Extendemos la fórmula hasta la fila 9]
ALEATORIO es una función volátil, de modo que cada vez que pulsemos F9 o hagamos algún cambio en la hoja, se recalculará y los valores de la columna B cambiarán.
La lista aleatoria la crearemos en tres pasos y la pondremos en la columna D. Usaremos las columnas F y G como columnas auxiliares, que eliminaremos una vez creada la fórmula definitiva.
Instructora: America Sabalu S 8
Funciones debase de datos – excel 2013
Primero, ordenamos los números aleatorios de la columna B de menor a mayor. Esto se hace con la función K.ESIMO.MENOR. Cuando pongamos la fórmula, los valores de la columna B se recalcularán y aparecerán otros diferentes.
En F2:
=K.ESIMO.MENOR($B$2:$B$9;FILA(A1)) [Extendemos la fórmula hasta la fila 9]
Ahora, usando la función BUSCARV, extraemos de la matriz B2:C9 los nombres de la columna C cuyo valor asociado (columna A) coincide con el de la columna F. La función BUSCARV realiza la búsqueda en la primera columna de la matriz. Ésta es la razón por la que hemos puesto los números aleatorios en esa posición, a la izquierda de los nombres, en la columna B.
En G2:
=BUSCARV(F2;$B$2:$C$9;2;FALSO) [Extendemos la fórmula hasta la fila 9]
Excel ha hecho un nuevo recalculo. La lista de la columna G es una lista aleatoria. Si queremos otra, pulsamos F9 o modificamos alguna celda.
Finalmente, creamos la fórmula compuesta en D2 y borramos las columnas F y G.
Instructora: America Sabalu S 9
Funciones debase de datos – excel 2013
En D2:
=BUSCARV(K.ESIMO.MENOR($B$2:$B$9;FILA(A1));$B$2:$C$9;2;FALSO) [Extendemos la fórmula hasta la fila 9]
¿Qué ocurre si no es posible poner la columna de números aleatorios a la izquierda de la columna de nombres? La respuesta es bien sencilla: no se podrá utilizar la función BUSCARV. Habrá que idear algún truco, como crear los números aleatorios en una zona vacía de la hoja y copiar la columna de nombres a su derecha.
Otra solución es utilizar la función INDICE. Veamos cómo.
Nuestra lista de nombres está en la columna B y, por el motivo que sea, no podemos escribir nada en la columna A.
La lista de números aleatorios la crearemos en la columna E.
En E2:
=ALEATORIO() [Extendemos la fórmula hasta la fila 9]
Instructora: America Sabalu S 10
Funciones debase de datos – excel 2013
Clasificamos de menor a mayor los valores de la columna E usando la función JERARQUIA.EQV (en Excel 2003 hay que usar JERARQUIA).
En G2:
=JERARQUIA.EQV(E2;$E$2:$E$9;1) [Extendemos la fórmula hasta la fila 9]
Con INDICE ponemos en la columna H el nombre de la columna B que ocupa la posición indicada en la columna G.
En H2:
=INDICE($B$2:$B$9;G2) [Extendemos la fórmula hasta la fila 9]
Instructora: America Sabalu S 11
Funciones debase de datos – excel 2013
Agrupamos todas las fórmulas en la columna C y borramos las columnas G y H.
En C2:
=INDICE($B$2:$B$9;JERARQUIA.EQV(E2;$E$2:$E$9;1)) [Extendemos la fórmula hasta la fila 9]]
Son posibles otras fórmulas; por ejemplo:
En C2:
=INDICE($B$2:$B$9;COINCIDIR(K.ESIMO.MENOR($E$2:$E$9;FILA(A1));$E$2:$E$9;0))
Esta fórmula devuelve una lista diferente de la anterior, pero también es totalmente aleatoria.
Sin embargo, las fórmulas usadas aquí tienen un fallo. Aunque es poco problable en listas pequeñas como la del ejercicio, podría darse el caso de que dos números aleatorios fueran exactamente iguales. En ese caso, uno de los
Instructora: America Sabalu S 12
Funciones debase de datos – excel 2013
nombres se repetiría. Por ejemplo, consideremos que ALEATORIO ha devuelto una lista de números en los que coinciden los correspondientes a las celdas E4 y E7. El resultado será:
La columna G tendrá dos valores iguales, lo que hará que la fórmula devuelva dos veces el mismo nombre (Carlos). Otro (Iker) habrá desaparecido de la lista aleatoria (columnas C y H).
Instructora: America Sabalu S 13
Funciones debase de datos – excel 2013
Listas aleatorias (2 de 2)Continuando con el artículo anterior, vamos a ordenar aleatoriamente una lista de nombres utilizando una macro. Nos servirá la macro empleada en la entrada Números aleatorios no repetidos, aunque habrá que hacer algunas adaptaciones.
Para comenzar, preparamos la lista de nombres en la "Hoja3":
Las columnas E, F y G serán columnas auxiliares. La lista aleatoria la pondremos en la columna C.
Necesitaremos pasarle a la macro la cantidad de nombres que tiene la lista. Por ello, la primera operación consistirá en realizar este cálculo y almacenarlo en E2.
En E2:
=CONTARA(B:B)-1 [Resultado: 8]
Accedemos al editor de VBA pulsando Alt + F11, seleccionamos Insertar + Módulo y escribimos el siguiente procedimiento:
Instructora: America Sabalu S 14
Funciones debase de datos – excel 2013
Salimos del editor pulsando Alt + Q.
Para ejecutar esta macro lo más cómodo es poner un botón en la hoja. Se puede usar un Botón de Formulario(Programador + Insertar + Botón (control formulario)), un Botón ActiveX (Programador + Insertar + Botón de comando (control ActiveX)) o una forma cualquiera; por ejemplo, un Rectángulo redondeado.
Si elegimos la última opción, tendremos que acceder a Insertar + Formas + Rectángulo redondeado y dibujar un rectángulo en algún sitio cómodo de la hoja, como la celda C1. Redondeamos los laterales y, enHerramientas + Formato + Estilos de forma, escogemos el estilo que más nos guste. En el menú contextual, elegimos Modificar texto y escribimos: Nueva lista
Volvemos a abril el menú contextual, seleccionamos Asignar macro y, en el cuadro de diálogo correspondiente, seleccionamos Números_no_repetidos. Terminamos pulsando el botón Aceptar.
Instructora: America Sabalu S 15
Funciones debase de datos – excel 2013
El botón ya es plenamente operativo. Hacemos clic sobre él y en la columna F aparecerá una lista de números no repetidos comprendidos entre 1 y 100000.
Ahora, asignamos a cada número un orden jerárquico. Lo haremos en la columna G.
En G2:
=SI.ERROR(JERARQUIA.EQV(F2;$F:$F;1);"") [Extendemos la fórmula hasta la fila 30]
Si prevemos que la lista va a ser muy grande, habrá que extender la fórmula mucho más abajo.
En C2:
=SI.ERROR(INDICE($B$2:$B$200;G2);"") [Extendemos la fórmula hasta la fila 30]
Cada vez que pulsemos el botón, Excel devolverá una nueva lista. Si añadimos nuevos nombres, serán incorporados a la lista aleatoria en la próxima pulsación. Naturalmente, también podemos eliminar nombres.
En apariencia, la columna G es superflua. Podríamos haber diseñado la macro de manera que generara números aleatorios no repetidos comprendidos entre 1 y el valor de la celda E2. De ese modo, conseguiríamos directamente en la columna F una lista aleatoria similar a la que hemos obtenido en la columna G.
La macro sería ésta:
Instructora: America Sabalu S 16
Funciones debase de datos – excel 2013
También habría que modificar ligeramente la fórmula de C2:
En C2:
=SI(F2<>"";INDICE($B$2:$B$200;F2);"")
La hoja quedaría de este así:
Instructora: America Sabalu S 17
Funciones debase de datos – excel 2013
Si esta macro resuelve el problema con una columna auxiliar menos, ¿no sería lógico utilizarla y descartar la anterior?
La verdad es que la última macro puede dar problemas cuando la lista tiene muchos nombres. Supongamos que la lista tiene 1.000 nombres. El bucle Do While... Loop debe generar 1.000 números al azar comprendidos entre 1 y 1.000, todos diferentes y guardarlos en la matriz número(). Se comprende que muchas veces el número generado ya exista y, en consecuencia, tendrá que descartarlo y volver a generar otro. Sin embargo, si le damos la opción de generar 1.000 números elegidos entre un abanico de números muchísimo mayor (por ejemplo, entre 1 y 100.000), la posibilidad de que un número generado no haya salido anteriormente será mucho mayor y los descartes serán, en consecuencia, mucho menores. El tiempo de ejecución de la macro disminuirá en la misma proporción. Por si esto no fuera suficiente, la segunda macro se cuelga cuando se trabaja con listas grandes.
La conclusión es clara: si no quieres problemas, usa la primera macro.
¿Qué ocurre si en la lista hay nombres repetidos? Nada raro. Cualquiera de las dos macros genera números diferentes, por lo que la función INDICE apuntará siempre a filas distintas. A esta función no le importa lo que haya escrito en una celda; simplemente, lo lee y lo copia en la columna C.
Instructora: America Sabalu S 18
Funciones debase de datos – excel 2013
Pantalla de Excel – Macros
Instructora: America Sabalu S 19
Funciones debase de datos – excel 2013
1. Configuración de Macros
2. Ejecución de la Macro
Private Sub cmdPc_Click()
imgPc.Left = 132
imgLaptop.Visible = False
imgPc.Visible = True
lblprecio = "1200"
End Sub
Private Sub cmdCalcular_Click()
lblImporte.Caption = Val(txtCantidad.Text) * Val(lblprecio)
End Sub
Private Sub cmdCierre_Click()
Unload Me
End Sub
Private Sub cmdSeguir_Click()
txtCantidad.Text = Empty
txtCliente.Text = Empty
lblImporte.Caption = Empty
lblprecio.Caption = Empty
txtCliente.SetFocus
End Sub
Private Sub cmdLaptop_Click()
imgLaptop.Top = 6
imgLaptop.Left = 132
imgPc.Visible = False
imgLaptop.Visible = True
lblprecio = "2500"
End Sub
Private Sub cmdEnunciado_Click()
Instructora: America Sabalu S 20
Funciones debase de datos – excel 2013
msje1 = "Se debe seleccionar la Pc a Vender haciendo clic en el botón Respectivo" & vbCrLf
msje2 = "Escribir el nombre del cliente y la cantidad de Pcs a Vender" & vbCrLf
msje3 = "Al hacer clic en Calcular se muestra el importe de las Pc vendidas" & vbCrLf
msje4 = "Al hacer clic en Cierre cierra el formulario" & vbCrLf
msje5 = "[Emplear las propiedades: Visible, Height, Width, Left y Top]"
msje = msje1 & msje2 & msje3 & msje4 & msje5
MsgBox msje, vbInformation, "Instrucciones"
End Sub
Private Sub Form_Load()
frmPcs.Height = 3360
frmPcs.Width = 3980
Instructora: America Sabalu S 21
Funciones debase de datos – excel 2013