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

Quitar acentos (tildes) de un texto en Excel.

Un lector me consulta cómo quitar los acentos (tildes) de un texto en una celda de Excel. Por ejemplo, si en la celda A1 tenemos el texto "María Angélica Esdrújula", nuestra tarea es transformar el texto en "Maria Angelica Esdrujula".

Coincidamos que ésta no es una tarea corriente en Excel. Pero la solución nos permitirá mostrar algunas técnicas útiles para construir funciones definidas por el usuario (UDF).

Empecemos por el caso más sencillo. Supongamos que en la celda A1 tenemos el texto "María". Queremos quitar el acento y transformar el texto en "Maria". En este caso sencillo podemos usar la función SUSTITUIR de la siguiente manera

=SUSTITUIR(A1,"í","i")



El problema con esta solución surge si tenemos varias palabras con acentos en distintas vocales.
En ese caso tendremos que usar una función definida por el usuario (UDF). Es decir, tendremos que escribir por nuestra cuenta el código de la función.

Empecemos por definir el problema. En castellano ponemos acentos sólo sobre vocales. Esto significa que tendremos cinco casos: á, é, í, ó y ú.
Lo primero que tenemos que saber es cuál es el código ASCII de estás vocales acentuadas, y cuáles son los códigos de las vocales sin acento. Para esto podemos usar la función CODIGO



Nuestra función tendrá que descomponer le texto en cuestión y reemplazar las vocales acentuadas por vocales sin acentos.
Empecemos por el final mostrando el código de la función:

Function txtNoAcc(texto) As String
    Dim largoTexto As Long, iX As Long
    Dim Lett As Long
  
    txtNoAcc = ""
  
    largoTexto = Len(texto)
  
    For iX = 1 To largoTexto
        Lett = Asc(Mid(texto, iX, 1))
        Select Case Lett
            Case Is = 225
                txtNoAcc = txtNoAcc & Chr(97)
            Case Is = 233
                txtNoAcc = txtNoAcc & Chr(101)
            Case Is = 237
                txtNoAcc = txtNoAcc & Chr(105)
            Case Is = 243
                txtNoAcc = txtNoAcc & Chr(111)
            Case Is = 250
                txtNoAcc = txtNoAcc & Chr(117)
            Case Else
                txtNoAcc = txtNoAcc & Mid(texto, iX, 1)
        End Select
    Next iX
End Function

Nuestra función tiene un solo argumento: "texto", que es el texto contenido en la celda cuyo contenido queremos transformar. Hemos definido tres variables, cuya función será evidente más adelante.

La sentencia
"largoTexto = Len(texto)"
define cuantas letras y espacios hay en la celda.

Luego usamos la construcción For Next para descomponer el texto en sus componentes, los cuales analizamos para ver si hay alguna vocal con acento.
Para esto hemos definido la variable

Lett = Asc(Mid(texto, iX, 1))

que nos da el número de código de cada una de las letras del texto. Una vez que tenemos el número de código ASCII de cada letra, lo sometemos al examen de la construcción Select Case. En el caso que se trate de alguno de los cinco casos de letras con acento, ésta será reemplazada por la letra sin acento. En caso contrario, dejaremos la letra original.
Todo esto se va concatenando en la variable " txtNoAcc", el nombre de la función.

Para usar la función copiamos el código en un módulo del editor de VBa (preferentemente en el cuaderno Personal.xls, de manera que la función esté a nuestra disposición cualquiera sea el cuaderno abierto).
Luego, abrimos el asistente de funciones en la categoría "Definidas por el usuario"




y elegimos la función que acabamos de definir



El resultado será el esperado

Technorati Tags: MS Excel

Numeros con ceros precedentes en Excel.

El tema de hoy es cómo agregar ceros precedentes a un valor en Excel. Esta es una tarea bastante común para todos aquellos que trabajan con sistemas como SAP o Oracle Applications e importan datos del sistema a Excel.
Uno de los problemas que suelen surgir al importar archivos .csv es que Excel convierte todo lo que parece un número a valor numérico. Si tenemos un campo con números de catálogo cuya primer cifra es cero, por ejemplo 0123456, Excel lo transformará a 123456. Podemos superar este problema usando las técnicas explicadas en la nota sobre cómo importar datos a Excel, pero hay situaciones en las cuales recibimos un archivo ya sin los ceros precedentes.

Veamos este ejemplo, donde en la columna A tenemos los valores originales (texto) y en la columna B tal como aparecen en Excel después de importarlos



Veamos las distintas alternativas a nuestra disposición.
Si queremos transformar el número a texto agregando un cero precedente y todos los valores tienen el mismo número de cifras, podemos optar por la opción de concatenar el valor en la columna B con un cero



Podemos también usar la función CONCATENAR en lugar del operador "&". Excel dará automáticamente formato de texto al resultado.

Si queremos agregar un cero precedente, pero que se mantenga el formato de número, tendremos que aplicar un formato personalizado como este: "0"#



Estas técnicas no se pueden aplicar al valor en la celda B3, ya que allí necesitamos dos ceros precedentes. Podemos aplicar la fórmula ="00"&B3 o aplicar el formato personalizado "00"#. Pero estas técnicas nos obligan a escribir una fórmula distinta para cada caso.

Si todos los valores tienen la misma cantidad de dígitos, por ejemplo 7 como los valores en el rango A2:A4, podemos aplicar el formato personalizado "0000000"



Los valores mostrarán ahora el número de ceros precedentes requerido pero seguirán siendo números. Si copiamos y pegamos los valores en el rango C2:C4 con Pegado Especial-Valores, veremos en la barra de las fórmulas que Excel no "ve" los ceros precedentes



Si queremos convertir los valores a texto, podemos usar la función TEXTO, de esta manera

=TEXTO(B2,"0000000")

Technorati Tags: MS Excel