Hojas de cálculo en Excel - página principal

Funciones de bases de datos personalizadas

No sabía si llamar a este artículo "a mi manera" en memoria de la célebre canción de Frank Sinatra, o bien llamarlo como lo he hecho. Posiblemente un título como el que finalmente he desestimado, atrajese más curiosos, frikis, y gente que se pierde por la red, pero menos público interesado realmente por las posibilidades de excel, y más concretamente por la funciones de bases de datos, como la que hoy nos ocupa nuevamente, la función BDcontar, origen de lo que hoy os presento.

Todo ha nacido gracias a mi buena amiga Silvia, una chica de culo inquieto, que de vez en cuando delega en mí el trabajo sucio. Como buena auditora de cuentas, dedica su tiempo al análisis, y no a las tareas de control repetitivas. Eso implica que cuando tiene que controlar y analizar un conjunto extenso de datos, recurra a su buen amigo Javier, el mismo que escribe estas líneas, para que le solucione, si sabe -cosa que rara vez ocurre-, alguna de sus dudas para tratar tan compleja y abultada información.

Silvia me dijo: “oye, mira a ver si puedes solucionarme este problema que tengo, que quiero acostarme pronto: tengo una base de datos en excel, con una serie de usuarios que se repiten unas cuantas veces. A su derecha tenemos una columna, con el permiso que se le otorga a cada usuario, pero que puede ser variado, entre Administrador y Usuario, pudiéndose repetir cualquiera de esos permisos, en las diferentes filas de la base de datos donde aparece ese usuario. ¿Por qué no me creas una función que determine las veces se le otorga uno y otro permiso a ese usuario?”.

Bueno, creo que me tocó leer así como tres o cuatro veces la pregunta, para hacerme una idea de lo que quería mi buena amiga Silvia. Finalmente, y tras recibir su ejemplo, todo me quedó mucho más claro. Concretamente quería saber cuántas veces obteníamos el permiso Administrador y Usuario, para cada uno de los usuarios.

La tabla originaria era de unas 2.000 filas, pero para nuestro ejemplo nos dará igual 1 que 50, porque nos bastará utilizar una fórmula y arrastrarla en tantas celdas como nos interese.

A lo que iba…, la tabla originaria era esta:


Una de las posibilidades era crear un encabezado de datos, donde incluiríamos el nombre del usuario y el permiso a evaluar. Algo así como esto:


Si os fijáis, en la celda C22 estamos evaluando las veces que se obtiene el permiso de Administrador, para el usuario Andrea, pues esos son los datos del encabezado, de B2 a C3, es decir, los criterios o las condiciones de búsqueda. Esta función incorpora la novedad de que no se trata exactamente de la función BDcontar, sino de la función BDcontara, que se diferencia de aquella, en que esta permite el uso de datos de texto, mientras que la función BDcontar, solo evalúa datos numéricos.

Esto tiene sus limitaciones, puesto que si tenemos 2.000 filas y deseamos evaluar tanto las veces que tenemos permisos de Administrador, como de Usuario, deberemos cambiar las celdas B3 y C3 muchísimas veces, tantas que se nos acabará la paciencia, y encontraremos la función BDcontara realmente inútil.

¿Qué podemos hacer para simplificar esta tarea?. Pues crearnos una función personalizada (o como la llama excel, una función definida por el usuario). Después de dedicar un rato a analizar la tarea, y tener bien claro que es lo que debíamos incluir en la función, acabé obteniendo una función en la que intervenían cuatro variables:

1.- El rango de datos: en nuestro ejemplo, de B7 a C20, pues son en esas celdas donde tenemos los datos.

2.- La columna donde figura el criterio que queremos contar: en nuestro ejemplo queremos contar tanto las veces que aparece la palabra Administrador como Usuario que aparecen en la segunda columna, por lo que deberemos informar que la columna a evaluar es la 2.

3.- El primer criterio de búsqueda: en nuestro caso, el nombre del usuario.

4.- El segundo criterio de búsqueda: en nuestro caso, Administrador y/o Usuario.

Y la función que obtuve, la llamé Coincidencias, y fue esta:


Function Coincidencias(rango As Range, columna As Integer, _
usuario As String, permiso As String)
'fichamos la celda donde están los datos
'inicial y final (el rango, vamos)

mi_rango = rango.Address
'separamos los datos inicial y final
coordenadas = Split(mi_rango, ":")
inicio = coordenadas(0)
fin = coordenadas(1)
'pasamos a una variable la dirección de la celda inicial
celda = Range(inicio).Address
'para todo el rango de datos...
For i = 0 To Range(fin).Row - Range(inicio).Row
'si el usuario coincide con el que
'hemos introducido en la fórmula

If LCase(Range(celda)) = LCase(usuario) Then
'comprobamos si el permiso es el que
'hemos introducido en la fórmula

If LCase(Range(celda).Offset(0, columna - 1)) = LCase(permiso) Then
'y en ese caso, sumamos 1
contador = contador + 1
End If
End If
'pasamos a la siguiente fila
celda = Range(celda).Offset(1, 0).Address
'seguimos con el bucle
Next
'asignamos a la función, el dato del contador
Coincidencias = contador
End Function

Si os fijáis, en C27 tenemos esta función:

=Coincidencias($B$7:$C$20;2;$B27;C$25)


Fijaos en la siguiente imagen:


Como veis, B7:C20 es el rango (está puesto en términos absolutos, de ahí el símbolo del $ que incorpora), la columna 2, el primer criterio que es B27 (Andrea), y el segundo criterio que es C25 (Administrador). Como podéis comprobar, obtenemos el mismo dato que utilizando la función BDcontara, pero de una forma más sencilla ahora que ya tenemos definida la función. Por eso nos bastará crear una tabla como la que he incluido yo desde B25 a D29, o en el caso de tener muchos datos (como así era en el ejemplo, pues teníamos 2.000 filas), también podemos poner la función en una columna anexa a la tabla de datos inicial, por ejemplo en la columna D. Si lo hacemos de esta última forma, podemos poner la función con los criterios referidos al valor de una celda, o bien podemos ponerlos directamente en la fórmula, de tal forma que esto que vamos a presentar a continuación será exactamente lo mismo que lo que hemos visto anteriormente:

=Coincidencias($B$7:$C$20;2; "Andrea";"Administrador")

Y no hay más cera que la que arde. Espero que esta función os haya sido tan útil como le ha sido a mi amiga Silvia.

Desde aquí podéis descargar el fichero de excel, con todo el código que os presento en este artículo.



18 comentarios:

VALENTIN dijo...

Cuando intento descargar algún archivo adjunto, me aparece una pantalla de error.

Javier Marco dijo...

Pues a mí me funciona perfectmante la descarga.

En lugar de abrir el fichero "al vuelo", guárdalo antes en tu disco duro, y ábrelo después desde allí. Si lo abres online directamente, puede que no funcionen correctamente los macros.

Salu2

Gloria dijo...

Hola, yo también tengo problemas para descargar el archivo.
El enlace me lleva a una página erótica. ¿Alguien me puede pasar el archivo excel, por favor?

entropia_1957@yahoo.es

Gracias de antemano y saludos cordiales.

Javier Marco dijo...

Pues no debería enviarte a ninguna página erótica, sino a megaupload. Revisa tu PC, por si tuvieras spyware.

Te paso por email el fichero.

Ayax dijo...

Javier de verdad te agradezco esta ayuda, estaba buscando un función que no fuera solo, buscarv y esas cosas.
Gonzalo.

Javier Marco dijo...

Muchas gracias por tu comentario. Espero que te haya sido útil :-)

Anónimo dijo...

Javier me parece que esto mismo se puede hacer con la funcion SUMAPRODUCTO, o me equivoco?

Javier Marco dijo...

Si los datos fueran numéricos, es probable, pero se trata de cadenas de caracteres (texos) con los que no se puede operar, ni multiplicar ni sumar :-)

Anónimo dijo...

Javier, si aplicas la siguiente formula a la celda c26 del archivo que colgaste para darnos el ejemplo hace la misma funcion de lo que nos explicas aqui

=SUMAPRODUCTO(--($B$7:$B$20=B26);--($C$7:$C$20=$C$25))

lo podrias probar y decirme si hay alguna diferencia, porque tengo que hacer un trabajo que implica la formula y nose cual de las dos formas seria mejor aplicar, muchas gracias

Javier Marco dijo...

Sinceramente, nunca se me hubiera ocurrido aplicar esa fórmula para tratar coincidencias de cadenas de texto.

Por supuesto, ya que es una función nativa de excel, esta es la opción que planteas, es la más recomendable, pues encima te ahorras tener que aplicar macros al libro de excel.

Anónimo dijo...

hola te felicito por la pagina!!
tengo un problema que nose como resolver aparte nose usar mucho el excel. queria saber si podrias ayudarme. tengo que pasar de una lista donde figuran datos del personal como nombre apellido, dias trabajados, sueldo, dni etc. de esa lista yo tengo que hacer recivos con esos datos. como hago para para hacer un click y hacer esto facil y rapido. espero que me alla expresado bien te agradeceria si me pudieras dar una mano un abrazo gracias por todo y mucho exito. vasco_ld@hotmail.com

Warloofer dijo...
Este comentario ha sido eliminado por el autor.
Warloofer dijo...

Hola, con una tabla dinamica (nombres en filas y permisos en columnas) se obtiene el mismo resultado. Eso si, tu opcion es mucho mas elegante y te evitas tener que refrescar la tabla. ;)

Anónimo dijo...

Hola que tal, por lo visto eres todo un mega master en EXCEL. mira tengo un problemita, parecido, es una lista de programas de telivison que han sido transimitidos en un mes, cada progrmaa tiene su clave unica, y necesito saber cuantas veces paso cada progrmaa ne el mes, estan todos desordenados, el orden que lelvan es la fecha y la hora, son aproximadamente 25000 registros. Como puedo hacer para q en base a su calve unica me diga cuantas veces se repitieron.

kingmasterlord@hotmail.com

travieso dijo...

Hola, super interesante el blog y buscaba algo similar a este solo que saber si ustedes conocen una funcion o codigo que me permita mostrar todos los registros de una tabla algo similar a Buscarv solo que el buscarv solo muestra el primer registro y pues yo necesito todos los existentes sin usar filtros, de antemano gracias
mi correo es levso@hotmail.com

Anónimo dijo...

hola javier un saludo nuevamente tengo una gran inquietud. tengo una aplicacion de excel que he estado haciendo bueno la idea lleno una hoja de excel desde un userform que contiene entre otros clientes, asesores, productos, codigos de los dos primeros fechas, descripciones.. quiero llenar una hoja or asesor para poder hacer reportes de cada uno y que me muestre el nombre del asesor y sus clientes con cada uno de los movimientos que ha realizado sacand estos datos de la hoja que he llenado previamente .. como puedo hacerlo .. muchas gracias por tu ayuda

Luis A.

Anónimo dijo...

javier necesito que me ayudes con este problema si puedes darme una manito
tengo un userform en excel y encontre un codigo para implementar un boton de buscar con el sigueinte codigo y me lo realiza muy bien pero como hago si hay mas de una coincidencia en la busqueda el codigo que tengo es

strBuscado = Trim(TextBox1.Text)
If strBuscado <> "" Then

Set Encontrado = Sheets("Hoja2").Range("J:J").Find(strBuscado)
If Not Encontrado Is Nothing Then

Set Encontrado = Sheets("Hoja2").Range("J:J").FindNext(Encontrado)
TextBox13.Text = Encontrado.Offset(0, -6).Value '
TextBox12.Text = Encontrado.Offset(0, -7).Value '
TextBox11.Text = Encontrado.Offset(0, -8).Value '
TextBox16.Text = Encontrado.Offset(0, -3).Value '
TextBox15.Text = Encontrado.Offset(0, -4).Value '
TextBox14.Text = Encontrado.Offset(0, -5).Value '
TextBox4.Text = Encontrado.Offset(0, -1).Value 'asesor
TextBox10.Text = Encontrado.Offset(0, -2).Value 'cliente
TextBox5.Text = Encontrado.Offset(0, 3).Value 'tipo parte
TextBox6.Text = Encontrado.Offset(0, 4).Value 'descr
TextBox7.Text = Encontrado.Offset(0, 1).Value 'division
TextBox8.Text = Encontrado.Offset(0, 2).Value 'area
TextBox9.Text = Encontrado.Offset(0, 5).Value 'status
TextBox17.Text = Encontrado.Offset(0, 6).Value 'valor
TextBox18.Text = Encontrado.Offset(0, 7).Value 'fed
TextBox19.Text = Encontrado.Offset(0, 8).Value 'fem
TextBox20.Text = Encontrado.Offset(0, 9).Value 'fea
TextBox21.Text = Encontrado.Offset(0,10).Value 'facturado

te agradezco una solucion a este problemailla una vez mas muchas gracias de antemano

Luis Alberto M.

Anónimo dijo...

Hola Javier.

Muy interesante tus tutoriales para neofitos como yo que necesitan programar en excel y nos perdemos en detalles que desconocemos como resolver. Gracias a tipos como tu convivir con excel es menos amargo.

Quisiera saber si puedes resolverme un problema. He visto que tienes un tutorial sobre como localizar datos en una hoja con las funciones INCIDE y COINCIDIR.

Yo estoy intentando programar una factura en la que me rellene todos los datos del cliente a partir de introducir el nombre del cliente en la factura. Pues bien consigo localizar, por ejemplo el CIF en la hoja de "clientes", y me da el número de fila y columna correcto en el generador de formulas pero en la casilla de la factura me aparece #VALOR. ¿Cuál es la razón por la que sale el error?
Te agradezco de antemano tu tiempo si puedes darme una respuesta.
Gracias.