Extraer direccion de celda en matriz

Supongamos que tenemos esta matriz



¿Cómo haríamos para encontrar la dirección de la celda que contiene el valor 455? Excel cuenta con la función DIRECCION a la cual le podemos pasar el número de fila y el número de columna y nos dará la dirección en forma de texto. Luego usando la función INDIRECTO podemos usar este texto como referencia real a la celda.
Si nuestra tuviera sólo una fila o una columna podríamos resolver el problema con facilidad con esta fórmula

=DIRECCION(1,COINCIDIR(A7,A1:E1))



El problema es que el valor buscado puede estar en cualquiera de las filas y columnas de la matriz y COINCIDIR no funciona cuando la matriz de búsqueda no es unidimensional.

No he profundizado en una solución basada en funciones nativas de Excel pero podemos encontrar una solución bastante sencilla creando una función definida por el usuario (UDF):

Public Function busc_mat(matriz As Range, valor_busc)
    Dim Celda As Range
    
    For Each Celda In matriz
        If Celda.Value = valor_busc Then _
                busc_mat = Celda.Address
    Next
        
End Function


Los argumentos de la función son

Matriz: la tabla de búsqueda
Valor_busc: el valor de referencia

Copiamos el código de la función a un módulo del editor de Vba (preferentemente en el cuaderno Personal.xls) y aplicamos la función a nuestro ejemplo




Esta función tiene un inconveniente: si el valor aparece más de una vez en la matriz, siempre dará como resultado el valor que se encuentre más abajo y a la derecha en la tabla. Por ejemplo, si agregamos algunos valores a nuestra tabla




Vemos que el resultado es $A$4. El código busca hacia la derecha y de arriba hacia abajo y retiene el último valor encontrado.
Si queremos obtener un resultado que no sea la dirección del último valor en la tabla, tendremos que modificar nuestra función.
Lo que hacemos es agregar un argumento opcional, el número de orden (nro_orden). Lo definimos como opcional ya que suponemos que no sabemos de antemano cuantas instancias del valor buscado hay en la tabla. Si dejamos el argumento en blanco, la función deberá dar el último valor; si ponemos un número de orden inexistente, la función dará un resultado de error (#VALOR!).
El código modificado de la función es:

Public Function busc_mat2(matriz As Range, valor_busc, _
                                Optional nro_orden As Integer)
    
    Dim Celda As Range, tmpRng(), tmpSize As Long, Counter As Integer
    
    If nro_orden = 0 Then
    
        For Each Celda In matriz
            If Celda.Value = valor_busc Then _
                    busc_mat2 = Celda.Address
        Next
    
    Else
    
        tmpSize = WorksheetFunction.CountIf(matriz, valor_busc)
    
        ReDim tmpRng(tmpSize)
        
        Counter = 0
        For Each Celda In matriz
            If Celda.Value = valor_busc Then
            Counter = Counter + 1
                tmpRng(Counter) = Celda.Address
            End If
        Next
        
    busc_mat2 = tmpRng(nro_orden)
    
    End If
    
End Function


Si nro_orden está en blanco, usamos el código de la primer función. Si ponemos un número de orden, creamos primero un vector (array) con las direcciones de las celdas cuya valor coincide con el valor de búsqueda y luego usamos el argumento nro_orden para extraer el elemento correspondiente del vector.

Un uso posible sería en un caso como este:



Podemos saber en qué mes se dio la instancia buscada, buscando el valor en la matriz y calculando la columna del mes correspondiente. La fórmula para calcular el mes es

=INDICE(A1:E1,COLUMNA(INDIRECTO(busc_mat2(A2:E6,B8,B9))))

Usamos INDIRECTO para interpretar la dirección de la celda como tal y no como texto.

Technorati Tags: MS Excel