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

JLD de vacaciones

A partir de hoy y hasta el lunes 14 de julio, estaré de vacaciones terminando mi tantas veces postergado curso de buceo.
Respuestas a los comentarios y mails se demorarán hasta entonces. Mientras tanto, gracias a todos y buen fin de semana.

Technorati Tags: MS Excel

Convertir valores numéricos a texto en Excel

Hay situaciones en las cuales queremos transformar valores numéricos a texto. Uno de los casos más corrientes es con las números de catálogo. Estos códigos pueden estar formados sólo por cifras o contener letras y cifras. Debido a esto son guardados en la base de datos original como texto.
Dependiendo del método que usemos para importar los datos, tendremos o no control sobre el formato de los datos en la hoja de Excel (recordemos que también podemos usar el MS Query).
Supongamos que hemos importado esta lista de números de catálogo. Aquellos que contienen alguna letra han sido convertidos a texto. Pero aquellos que contienen sólo cifras han sido transformados a números, a pesar de que en la case de datos original hayan sido guardados como texto



En la columna auxiliar "chequeo" uso la función ESNUMERO para verificar a qué tipo han sido transformados los datos.
Como podemos ver, los datos en las celdas A2 y A3 han sido transformados a valores numéricos.
En esta nota mostraremos las distintas posibilidades que nos ofrece Excel para transformarlos a texto.

1 –Usar la función TEXTO. Esta función no sólo convierte el valor numérico en texto, sino que también nos permite establecer el formato.



En este ejemplo hemos empleado el formato "General". En lugar de la palabra general podemos emplear el símbolo que lo representa: "@".
Como pueden ver la columna de chequeo (E) muestra ahora FALSO para todas las filas.
Si hace falta, podemos pegar los valores convertidos a texto en el rango original (la columna A en nuestro ejemplo), usando Pegado Especial-Valores.



Esto hará que el formato de texto se mantenga también en la columna A



2 – Usar el menú Datos-Texto en columnas. Seleccionamos el rango a convertir y abrimos el menú indicado



En el diálogo que se abre pasamos al paso 3 y marcamos Texto en la opción Formato de los datos en la columna



Apretamos Finalizar y veremos que los datos han sido convertidos a texto



Una confusión bastante común en los usuarios principiantes es intentar aplicar el formato de celda "Texto" a toda la columna. Esto no influye en los datos que ya se encuentran en las celdas de la columna. Si aplicamos el formato Texto a una celda que contiene un valor numérico, deberemos editarla para que la conversión tome efecto.
Por ejemplo, seleccionamos la celda A2 en nuestro ejemplo. Aplicamos el formato Texto con el menú Formato de celdas. Luego apretamos F2 (modo de edición) e inmediatamente Enter. El número se convertirá en Texto. Lo que hemos hecho equivale a haber tipeado el número en la celda después de haber puesto el formato de texto.

Technorati Tags: MS Excel

Totales en el tope de una tabla dinámica de Excel

Cuando creamos una tabla dinámica y no cancelamos los totales por columna (con el menú Opciones de tabla), Excel pone los totales al pie de los campos de datos de la tabla



Hay situaciones en las cuales, a diferencia del ejemplo en esta nota, tenemos muchas líneas en nuestra tabla. Por ejemplo, supongamos que en lugar de 5 departamentos de una tienda, tenemos 100 o 200 sucursales de una red.
En esos casos es conveniente mostrar el total de la tabla en la parte superior, en lugar de forzar al destinatario del informe a moverse hasta el final de la tabla.
Excel no tiene un método integrado para hacer esto, pero podemos lograrlo con un pequeño truco.
Como en la nota sobre el cálculo de porcentaje para subtotales, lo que haremos es agregar una columna (campo) auxiliar.
Siguiendo con nuestro ejemplo, insertamos una columna entre Mes y Ventas (Total)



Al insertar el nuevo campo entre dos existentes no tenemos que actualizar el rango de la tabla dinámica ya existente.

Apretamos el icono de Actualización de datos para que el nuevo campo aparezca en la lista de campos. Lo arrastramos al área de campos de fina asegurándonos que sea el primer campo del área (a la izquierda)



El próximo paso es abrir el menú de configuración del campo, apretamos el botón Diseño y marcamos las opciones Mostrar elementos en formulario esquemático y Mostrar subtotales en la parte superior del grupo



Apretamos Aceptar dos veces y obtenemos esta tabla



Todo lo que nos queda por hacer es deshacernos de la línea de Total General al pie de la tabla. Para esto abrimos el menú de Opciones de la tabla y quitamos la marca de la opción Totales generales de columnas



con lo cual logramos el resultado esperado

Technorati Tags: MS Excel

Una manera fácil de publicar hojas Excel en la WEB.

Hasta ahora no me ocupado en este blog de temas que no estuvieran directamente relacionados con Excel y análisis y presentación de datos. Pero en esta entrada haré un excepción para contarles sobre el sitio Widgenie.
La propuesta del sitio es sencilla: crear un "widget" a partir de archivos Excel, o .csv que nos permita publicarlo en nuestra hoja, ya sea un blog o cualquier otro tipo de sitio (Facebook, iGoogle, etc.).
Lo que más me ha resultado atractivo de este sitio es la facilidad de uso.
Por ejemplo, después de haber creado una hoja con los datos de las visitas al blog en los primeros seis meses del año,



quiero publicarla en el blog. Después de haber creado una cuenta en el sitio, me conecto y entro en mi cuenta



Elijo crear un nuevo archivo de datos (data file)



En este caso elegimos el formato de Excel y subimos el archivo al sitio usando la interfaz de importación



Hay que prestar atención a las instrucciones en el cuadro a la derecha de la página, ya que hay ciertas limitaciones

Al apretar el botón Upload, se abre un formulario donde en la pestaña "Information" podemos agregar una descripción de los datos. En la pestaña "Columns" podemos borrar columnas de la tabla que no queremos publicar y finalmente, en la pestaña "Linked Widgets" determinamos la forma de presentación de los datos



Las posibilidades son expuestas en forma gráfica e intuitiva



En mi caso, he elegido el gráfico de columnas animadas, lo que abre el diálogo de diseño. Aquí arrastramos los datos que queremos que aparezcan en sobre el eje de las X (categorías) y los que aparecerán sobre el eje de la Y. Después de arrastrar los datos, aparecerá una imagen previa del gráfico



En el próximo paso podemos agregar títulos al gráfico y a los ejes y también cambiar la orientación (de columnas a barras). Finalmente, pasamos a la pestaña "Publish" donde obtenemos el código para publicarlo (en mi caso, obviamente, "Blogger")



El resultado lopueden ver en la parte inferior de la columna izquierda del blog.

A pesar de la facilidad de uso, existen ciertos inconvenientes. Por ejemplo, al usar los nombres de los meses (en inglés), las columnas del gráfico no aparecen en el orden original de la tabla de los datos. Como pueden ver, en el gráfico que publico en el blog, he reemplazado los nombre de los meses por sus números de orden.
Otro inconveniente es que no parece ser posible el uso de caracteres castellanos como los acentos. De todas maneras, la facilidad de uso hace que recomiende este sitio con entusiasmo.

Technorati Tags: MS Excel

Area entre líneas en gráfico de Excel

Para los que se interesan en crear gráficos con Excel, acabo de publicar en mi blog sobre gráficos y presentación de datos una nota sobre como colorear el área comprendida entre dos líneas en un gráfico de Excel




Buen fin de semana y vamos España!

Technorati Tags: MS Excel

Columnas de ancho variable en Excel

Una posibilidad interesante es representar dos series de datos en un gráfico de columnas de manera que la altura de cada columna represente por ejemplo el volumen de ventas de una serie de productos y el ancho de cada columna represente la cantidad de unidades vendidas por cada producto.

La idea es crear este gráfico este gráfico



para representar estos datos



La exlicación de la técnica pueden verla en la nota sobre gráficos de columnas con ancho variable que acabo de publicar en mi blog sobre gráficos y presentación de datos.

Technorati Tags: MS Excel

Ordenar listas en Excel por día se semana o por mes.

En los últimos días he recibido varias consultas sobre el tema de ordenar rangos o listas en Excel, como pueden juzgar por las notas publicadas recientemente.
Una de las consultas se refiere a cómo ordenar una lista siguiendo el orden de los días de la semana. El asunto no es trivial en Excel, ya que los nombres de los días son considerados texto y ordenados como tales.
Por ejemplo si tenemos esta lista de días de la semana sin ordenar



y lo ordenamos en forma corriente



veremos que el orden no cambia. Esto se debe a que, para Excel, la lista ya está ordenada alfabéticamente.
Excel tiene un método que nos permite definir listas personalizadas. Una vez definida una lista personalizada, podemos utilizarla para ordenar un rango de valores.
En nuestro ejemplo, necesitamos definir una lista personalizada que contenga los días de la semana en el orden deseado. Esto lo hacemos accediendo a la pestaña Listas Personalizadas en el menú Opciones y agregando la lista



Una vez agregada la lista abrimos el menú Ordenar, pulsamos el botón Opciones y elegimos la lista personalizada en la ventana Primer criterio de ordenación



Ahora obtenemos el resultado deseado

Technorati Tags: MS Excel

Ordenar rangos de valores mixtos (alfanuméricos) en Excel

Ordenar listas e valores es una tarea muy corriente en Excel. Para ordenar un rango de valores, sencillamente usamos el menú Datos-Ordenar que nos permite ordenar los valores del rango en forma ascendente o descendente usando hasta tres criterios

Los problemas empiezan cuando los valores a ordenar son valores mixtos, es decir que contienen número y letras, como en el caso de direcciones.

En este ejemplo tenemos una lista de direcciones en la columna A y la misma lista ordenada en forma ascendente usando el menú Ordenar de Excel



Como pueden ver, Excel considera cada valor de izquierda a derecha y pone en primer lugar los números, en nuestro caso de mayor a menor, y luego las letras.
Es por este motivo que las direcciones en la calle 25 de Mayo aparecen antes que las de la calle Av. Mitre.
Ahora se preguntarán por qué 25 de Mayo aparece antes que 9 de Julio, dado que 25 es mayor que 9? Cuando se trata de valores mixtos, alfanuméricos, Excel considera todos los componentes del valor como texto. Por lo tanto, al considerar el primer elemento del texto, el 2 es menor que el 9.
Hasta aquí esto no parece ser un grave problema. Pero si nos fijamos en el resto de la lista veremos que las direcciones de calle tampoco están ordenadas como hubiéramos querido. Por ejemplo, 25 de Mayo 541 aparece después de 25 de Mayo 3290!
Si estuviéramos organizando el recorrido de cartero, por ejemplo, estaríamos dificultando enormemente su tarea.

Excel no tiene un método incorporado para este tipo de ordenamiento, pero podemos hacerlo creando dos columnas auxiliares, en base a las cuales ordenaremos la lista.

La idea es separar el nombre de la calle del número de la casa (o edificio). Basándonos en la norma que en la dirección siempre aparece el nombre de la calle al principio y el número de la casa al final, usaremos la técnica que mostré en la nota sobre cómo extraer el ultimo elemento de un texto en una celda en Excel.

Empezamos por crear dos columnas auxiliares (aux1, aux2). En la primera aparecerá el nombre de la calle y en la segunda el número de casa



La fórmula en la columna B es

=IZQUIERDA(A2,LARGO(A2)-(LARGO(A2)-ENCONTRAR("*",SUSTITUIR(A2," ","*",LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))))))

La fórmula en la columna C:

=VALOR(DERECHA(A2,LARGO(A2)-ENCONTRAR("*",SUSTITUIR(A2," ","*",LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))))))

En esta última usamos la función VALOR, que queremos que Excel ordene los números como tales y no como texto.

La explicación sobre cómo funcionan estas fórmulas las encontrarán en la nota mencionada.

Ahora todo lo que nos queda por hacer es ordenar la tabla usando la columna Aux1 como primer criterio y la columna Aux2 como segundo

Technorati Tags: MS Excel