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.