Cancelar botones de pegado y autorrelleno en Excel

Excel tiene varias funcionalidades para agilizar o hacer más eficiente el trabajo de usuario. Algunas son muy prácticas, otras pueden resultar molestas.
En esta última categoría encuadran, para mi gusto personal, los botones de autorrelleno y pegado. Sobre el molesto Autocompletar ya me explayado en otra oportunidad.
Estos botones aparecen, sin que nadie los haya llamado, cuando copiamos un rango




o arrastramos una serie de celdas para crear una serie



Para desactivar estos botones tenemos que abrir el menú Herramientas-Opciones y en la pestaña Modificar, quitar la marca de las opciones correspondientes

Technorati Tags: MS Excel

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

Usar BUSCARV (Excel) en listas con valores repetidos.

Usamos BUSCARV para encontrar en una tabla el valor correspondiente a otro determinado valor. Esta función, así como otras funciones de búsqueda en Excel, tiene una limitación. Si el valor de búsqueda tiene más de un valor correspondiente en la matriz de búsqueda, Excel dará como resultado el primer valor (en orden de aparición).

Supongamos esta lista de órdenes de compra.



Cada producto aparece tres veces, cada vez con distinto precio. Si usamos BUSCARV para averiguar el precio de los tornillos, el resultado será 7.65



Como explicamos antes, BUSCARV nos dará siempre el valor que aparece en primer lugar en la matriz. Si queremos encontrar el valor correspondiente al argumento de búsqueda que no sea el primero en la matriz, tenemos que usar una fórmula matricial como esta propuesta por Chip Pearson

={INDICE(compras,K.ESIMO.MENOR(SI(DESREF(compras,0,0,FILAS(compras),1)=B12,FILA(DESREF(compras,0,0,FILAS(compras),1))-FILA(DESREF(compras,0,0,1,1))+1,FILA(DESREF(compras,FILAS(compras)-1,0,1,1))+1),B13),3)}



Esta fórmula supone que sabemos cuantas instancias de Tornillos hay en la lista. Si buscamos una instancia inexistente, por ejemplo el cuarto valor de Tornillos, obtenemos un resultado #REF!

Podemos adaptar la fórmula para que dé el último valor de la lista

={INDICE(compras,K.ESIMO.MENOR(SI(DESREF(compras,0,0,FILAS(compras),1)=B12,FILA(DESREF(compras,0,0,FILAS(compras),1))-FILA( DESREF(compras,0,0,1,1) )+1,FILA( DESREF(compras,FILAS(compras)-1,0,1,1))+1),CONTAR.SI(DESREF(compras,0,0,FILAS(compras),1),B12)),3)}



Si queremos buscar el máximo valor de Tornillos, sin relación al orden de aparición en la lista, podemos usar esta fórmula matricial:

={MAX((A2:A10=B12)*(C2:C10))}



El archivo con las fórmulas se puede descargar aquí.

Actualización (Julio 2019): once años después de la publicación de esta nota Power Query, que Microsoft ha incorporado a Excel en los últimos años, nos permite realizar esta tarea mas eficientemente. Les invito a consultar esta nota.


Technorati Tags: MS Excel