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

Más sobre las funciones de bases de datos personalizadas

Silvia, mi amiga auditora, me ha planteado otro reto. Me pregunta: "Oye, si en excel tengo una relación de personas, y una serie de datos asociados a cada uno de ellos, ¿cómo puedo obtener las columnas en las que se obtienen determinados datos?". Como así, a bote pronto, y sin un ejemplo claro, parece un problema un poco abstracto, procedimos a montar un supuesto para dejar claras las cosas. El ejemplo era el siguiente:


La pregunta era bien clara: "¿cómo puedo obtener las columnas en las que se obtienen determinados datos?".

Bien, supongamos que queremos saber las veces que se obtiene el valor 1, para todos esos usuarios. En el caso de MANOLITO, el dato 1, se obtiene en la columna D y F, como se puede ver en la tabla anterior. Pero en el caso de FULANITO, el valor 1 no está presente en ninguna de las columnas.

La cuestión era crear un macro o una fórmula que nos permitiera determinar las columnas en las que obtenemos el dato que busquemos. Al final decidí montar una función, a la que he llamado localizar, y que tiene el siguiente código:


Function Localizar(rango As Range, valor As Variant)
'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).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

Como veis en la primera línea de la función, aparecen dos argumentos: el rango, y el valor. El rango es precisamente, el área donde tenemos los datos, mientras que el valor es el dato que buscamos. La fórmula nos devolverá las letras correspondientes a las columnas donde aparezca ese valor.

Por ejemplo, si buscamos las columnas donde aparece el valor 1, para MANOLITO, deberemos seleccionar como rango, el área comprendida entre C4 y G4, por lo que la función la llamaremos así:
Como veis en la primera línea de la función, aparecen dos argumentos: el rango, y el valor. El rango es precisamente, el área donde tenemos los datos, mientras que el valor es el dato que buscamos. La fórmula nos devolverá las letras correspondientes a las columnas donde aparezca ese valor.

Por ejemplo, si buscamos las columnas donde aparece el valor 1, para MANOLITO, deberemos seleccionar como rango, el área comprendida entre C4 y G4, por lo que la función la llamaremos así:
Como veis en la primera línea de la función, aparecen dos argumentos: el rango, y el valor. El rango es precisamente, el área donde tenemos los datos, mientras que el valor es el dato que buscamos. La fórmula nos devolverá las letras correspondientes a las columnas donde aparezca ese valor.

Por ejemplo, si buscamos las columnas donde aparece el valor 1, para MANOLITO, deberemos seleccionar como rango, el área comprendida entre C4 y G4, por lo que la función la llamaremos así:

=localizar(C4:G4;1)

Si lo preferimos, también podemos utilizar como valor, las coordenadas de la celda donde tenemos el dato a buscar. Por ejemplo, si en la celda A1 tuviéramos un 1, podríamos haber escrito la función, tal y como la hemos escrito antes, o bien de esta otra forma:

=localizar(C4:G4;A1)

Como podéis ver en la imagen siguiente, aparece a la derecha la fórmula localizar, donde nos devuelve como resultado, las columnas donde aparece el valor buscado. Si no se encuentra el valor, simplemente nos imprimirá en pantalla el texto No existe, tal y como también podéis ver en la imagen:


Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento, y por supuesto, con la función y el código de este artículo.



7 comentarios:

Anónimo dijo...

BUENA LA FORMA, ELEGANTE, ESTOY APRENDIENDO ESTE TEMA, TIENES ALGUNOS EJEMPLOS PARA RECORRERLOS Y APRENDER?

CLAUDIO CARVALLO
claudio.carvallo@gmail.com

Javier Marco dijo...

Gracias Claudio. La verdad es que a día de hoy, en el blog solo hay tres entradas relativas a las funciones de bases de datos. Este artículo que estás leyendo, el de la función bdcontar, y este sobre funciones de bases de datos que te puede ahorrar mucho trabajo, pues sirve para sustituir a la función bdcontar.

Anónimo dijo...

Hola, excelente tu aporte con el manejo de estas tablas. Ahora, la pregunta, como hago extensivo a una rango más amplio de celdas. Por ejemplo que busque el 1 en todas las celdas del rango y me diga en que columnas se encuentran, independiente de lo que diga la primera columna?. La verdad es que busco hacer lo mismo que muestras pero los nombres de las columnas son los nombres de las personas que colocas en la proimera columna, y de ahí van los números hacia abajo. Quiero buscar un número en todo el rango y me diga de quién es o son.
Gracias

Anónimo dijo...

O, como hacer para que, en vez de decir la letra de columna, me diga un título de columna?.

Gracias

Jose dijo...

ratMuchas gracias por la macro insertar imagenes asociadas a un desplegable.
Quiesiera tu apoyo para lo siguiente: tengo datos en una hoja de excel tales como No. emp, Nombre Fecha Permiso, A veces el No. Se repite en un dfeterminado mes quiesiera que validar en otra hoja que con solo introducir el No. de empleado me traiga el empleado solicitado. gracias

Anónimo dijo...

Saludos, un favor estoy extrallendo informacion del un dbf "tabla de visual fox pro" a un archivo de excel ya realice el optener datos externos, la duda es quisiera que la macro que extrae la informacion en SQL pueda yo ingresar una variable para que pueda cambiar el dato al traer la informcion de la tabla dbf, dejo el SQL que me resulta de la macro
el dato que se va a modificar es la (variable2)
ejemplo:
variable2 = Range("C1")
"WHERE ( CAJA.VTA_PED = variable2" & Chr(13) & "" & Chr(10) & "ORDER BY CAJA.DOCUMENTO" _,
Agradeceria bastante tus comentarios para resolver este procedimiento

Gracias Nicolas

Javier Marco dijo...

Pues yo no soy experto en nada, y mucho menos en SQL, pero prueba así a ver si te funciona, si el dato es numérico:

"WHERE CAJA.VTA_PED =" & variable2 & " ORDER BY CAJA.DOCUMENTO"

Si la variable2 es un texto, prueba así:

"WHERE CAJA.VTA_PED ='" & variable2 & "' ORDER BY CAJA.DOCUMENTO"

Saludos