¿En qué columnas tenemos los datos que buscamos?

En esta ocasión vamos a explicar, a través de una función personalizada, es decir, a través de una función no implementada en el propio Excel, sino que nosotros mismos vamos a programar, cómo conseguir descubrir en qué columnas se encuentra un valor que buscamos dentro de un rango de celdas.

Quizás leyendo el párrafo anterior, no os quede muy claro que es lo que queremos obtener, pero como siempre, vamos a hacerlo lo más ameno posible, explicándolo de una forma sencilla, para que sea más inteligible.

Concretamente, vamos a hacer algo como esto: Imaginemos que tenemos una tabla de Excel, donde hay diferentes valores, ya sean numéricos, alfanuméricos, o simplemente caracteres de texto. Lo que pretendemos, es buscar un valor determinado en esa tabla, para saber en qué columnas se encuentra. Veámoslo con una imagen:

Como veis en esa imagen, hay una serie de vendedores, con sus ventas mensuales, un total anual, y una desviación con respecto a las ventas medias por vendedor. Vamos a complicarnos la vida, y lo que vamos a buscar no son los meses en los que encontremos determinado valor, sino la columna de Excel donde estos se encuentran.

Veámoslo con un ejemplo: Supongamos que queremos buscar en qué columnas tenemos el valor 6.000,00, para el vendedor llamado Pedro. Si os fijáis en la tabla anterior, podemos localizar ese valor en el mes de septiembre, que corresponde a la columna K. Esto último es lo que pretendemos localizar.

Para hacerlo más complicado, y como es probable que el valor buscado no coincida con ninguno de la tabla (por ejemplo, el vendedor Rafael no tiene ventas por importe de 6.000,00), vamos a buscar también aquellas columnas que tengan el valor buscado o uno superior (que se cumpla cualquiera de esas dos condiciones), y también aquellas columnas que tengan un valor inferior. Si queréis, podéis adaptar vosotros este ejercicio, para buscar las columnas cuyos valores que sean iguales o inferiores (que se cumpla cualquiera de esas dos condiciones), o bien, buscar las columnas cuyos valores sean superiores al valor buscado, pues es muy sencillo hacerlo, con los pasos que os indico a lo largo de este artículo.

Vamos al lío… Vamos a construir nuestra función personalizada, para lo cual, en un módulo VBA, copiaremos esto, que nos servirá para localizar las columnas que contengan el valor exacto al valor que busquemos:

Function Localizar(rango As Range, valor As Variant) 'fichamos la celda donde están los datos 'inicial y final (el rango) 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).Column - Range(inicio).Column      'Comprobamos el valor introducido      If Range(celda) = valor Then          columna = columna & " " & Range(celda).Address          'eliminamos el signo de $ de referencia absoluta          columna = Replace(columna, "$", "")      End If      'pasamos a la siguiente columna      celda = Range(celda).Offset(0, 1).Address 'seguimos con el bucle Next 'Si no existe el dato en ninguna columna If columna = 0 Then columna = " No existe" 'eliminamos los números de la fila numeros = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9) For j = 0 To UBound(numeros)      columna = Replace(columna, numeros(j), "") Next 'asignamos a la función, el dato del contador 'eliminando previamente el primer espacio vacío Localizar = Mid(columna, 2) End Function

Vale, esto está muy bien, pero ¿cómo se utiliza esta función?. Pues así:

=localizar(rango_de_celdas_donde_buscaremos;valor_a_buscar)

Veámoslo con otro ejemplo:

En la tabla anterior, podemos ver que estamos buscando el valor 6.000,00 en toda la tabla que va, del rango de celdas C7, hasta la celda N12, pero lo hemos separado por vendedores, por lo que en el caso por ejemplo del vendedor Rafael, lo que haremos será buscar el valor 6.000,00 en el rango que va de C7 a N7. Para ello, la fórmula que hemos puesto en la celda C17 es esta:

=localizar(C7:N7;$D$3)

En este caso, como vemos, no hay ventas por valor de 6.000,00, aunque sí las hay por valores superiores e inferiores. Si lo que queremos es obtener las columnas donde haya valores superiores o iguales a 6.000,00, entonces crearemos otra función personalizada a la que llamaremos "localizarMasOIgual", donde copiaremos el código que os puse anteriormente, cambiando estas líneas que os añado a continuación.

Cambiaremos esta línea del código (ojo, que tenemos que llamar a esta nueva función con otro nombre, y yo he elegido el de "localizarMasOIgual"):

If Range(celda) = valor Then

Por esta otra:

If Range(celda) >= valor Then

Y también cambiaremos esta línea:

localizar = Mid(columna, 2)

Por esta otra:

localizarMasOIgual = Mid(columna, 2)

De esta forma, ya tendremos nuestra nueva función creada, donde buscaremos las columnas donde tengamos un valor igual o superior al buscado. Este será el resultado, después de aplicar la nueva fórmula, al rango de datos donde buscaremos las ventas superiores o iguales a 6.000,00:

En el caso de buscar las columnas con valores inferiores al valor buscado (en nuestro ejemplo, ventas inferiores a 6.000,00), crearemos una nueva función a la que llamaremos "localizarMenos". En esta nueva función, cambiaremos el código inicial que os puse de dos líneas, concretamente cambiaremos esta línea:

If Range(celda) = valor Then

Por esta otra:

If Range(celda) < valor Then

Y también cambiaremos esta línea:

localizar = Mid(columna, 2)

Por esta otra:

localizarMenos = Mid(columna, 2)

Y el resultado después de aplicar la nueva función, será este, si buscamos las columnas donde tenemos ventas de cada vendedor, inferiores a 6.000,00:

Como veis, es muy sencillo adaptar la función inicial, a vuestras necesidades. Si queréis, podéis probar a obtener las columnas donde se encuentran valores superiores al valor buscado. En nuestro ejemplo hemos aplicado la función "localizarMasOIgual", pero nos busca los valores superiores o iguales al valor buscado, y no solo los valores superiores, como os sugiero que hagáis. También podéis crear una función más, para localizar las columnas donde hay valores menores o iguales al valor buscado. En nuestro ejemplo hemos aplicado la función "localizarMenos", pero nos busca solo los valores inferiores al valor buscado.

Desde aquí, podéis descargar el libro de Excel, con el ejemplo que hemos visto en este artículo, para que lo analicéis, lo probéis, y veáis si esta utilidad tiene alguna funcionalidad en vuestra vida diaria, o simplemente os parece otro interesante ejemplo más de cómo crear funciones personalizadas en Excel.