Transformar números en forma de texto a valores numéricos en Excel

Esta nota ha sido corregida a partir de las observaciones de mis lectores

Podría empezar esta nota con el conocido adagio "basado en hechos reales". Sucede que no existe una convención internacional sobre separadores de decimales y de miles. En Argentina y España por ejemplo, se usa punto para separar los miles y la coma para separar los decimales. Pero en otros países, como México, Puerto Rico y Estados Unidos, se usa la coma para separar los miles y el punto para los decimales.
Cuando introducimos un valor en una celda, Excel interpreta si es número o texto de acuerdo a las definiciones regionales

Como se puede apreciar, en Argentina se usa el punto para separar los miles y en México la coma.

Si un usuario en la Argentina recibe un cuaderno Excel hecho en México, Excel "traduce" el número de un sistema al otro. Los problemas empiezan cuando los datos son enviados de México a la Argentina en formato .txt o, peor aún, .csv.

Por ejemplo, sin un usuario mexicano envía un archivo .csv donde aparece el valor "123,456.78", que para él es un número, al abrir el archivo con las definiciones regionales de la Argentina, Excel lo transformará en texto.

En esta nota veremos cómo solucionar el problema con funciones Excel y, por supuesto, con UDF (funciones definidas por el usuario, macros).

En esta tabla,

los valores en la columna A son texto; en la columna B san sido transformados a números con esta fórmula

=VALOR(SUSTITUIR(SUSTITUIR(A2;",";".");".";",";LARGO(SUSTITUIR(A2;",";"."))-LARGO(SUSTITUIR(SUSTITUIR(A2;",";".");".";""))+ESERROR(HALLAR(".";A2))))

Vamos a explicar cómo funciona esta fórmula pasa por paso.

El primer paso consiste en reemplazar todas las comas que pueda haber en el texto por puntos. Esto lo hacemos en la columna B (Auxiliar 1) con la fórmula

=SUSTITUIR(A2;",";".")

Luego creamos una cadena sin separadores, en la columna C, con

=SUSTITUIR(B2;".";"")

Comparando el largo de las cadenas en B y C podemos saber cuántos separadores (puntos y comas) hay en la cadena con esta fórmula, lo que hacemos en la columna D

=LARGO(B2)-LARGO(C2)+ESERROR(HALLAR(".";A2))

Usamos la expresión =ESERROR(HALLAR(".";A2)) para agregar un 1 en caso que el "texto" no contenga una parte decimal

Este dato nos será útil en la columna E, para reemplazar el último punto en la cadena por el separador de decimales, la coma

=VALOR(SUSTITUIR(B2;".";",";D2))

Como se puede apreciar, también combinamos la función VALOR, para convertir la cadena de texto resultante de usar SUSTITUIR en un valor numérico.

En la columna F reemplazamos todas las fórmulas intermedias por referencias a la celda que contiene la cadena de texto (la columna A), resultando nuestra "mega-fórmula"

=VALOR(SUSTITUIR(SUSTITUIR(A2;",";".");".";",";LARGO(SUSTITUIR(A2;",";"."))-LARGO(SUSTITUIR(SUSTITUIR(A2;",";".");".";""))+ESERROR(HALLAR(".";A2))))

En definitiva, podemos usar esta fórmula sin todos los pasos intermedios.

También podemos incluir la fórmula en un nombre, lo que la hará más legible

Function TextNum_to_Num(strNumText As String, sepDec)
   
    Dim numInt As String 'la parte entera del numero
    Dim numDec As String  'la parte decimal del numero
    Dim arrNumTemp() As String
    Dim sepMiles
   
    If sepDec = "." Then
        sepMiles = ","
    Else
        sepMiles = "."
    End If
   
   
    arrNumTemp = Split(strNumText, sepDec, -1)
    numInt = WorksheetFunction.Substitute(arrNumTemp(0), sepMiles, "")
   
    Select Case WorksheetFunction.CountA(arrNumTemp)
        Case Is = 1
            TextNum_to_Num = Val(numInt)
        Case Else
            numDec = arrNumTemp(1)
            TextNum_to_Num = Val(numInt & sepDec & numDec)
    End Select
   
End Function


Los argumentos de esta función son

• strNumtoText: la celda que contiene que la cadena de texto que queremos convertir en número
• sepDec: el separador de decimales usado en la cadena de texto (distinto del usado en las definiciones regionales).

El archivo con los ejemplos y el código de la función se puede descargar aquí

Fórmulas con resultados múltiples

Hay situaciones en las que una fórmula puede dar más de un resultado. El caso clásico es con las funciones de búsqueda como BUSCARV o INDICE.


La alternativa más práctica en estos casos es usar Autofiltro (o Filtro Avanzado). Pero hay situaciones, por ejemplo cuando construimos un tablero de comandos (dashboard), donde queremos que los resultados aparezcan en un rango determinado de la hoja o en otra hoja.

Supongamos que tenemos esta tabla (la misma que usamos en la nota sobre el uso de BUSCARV en listas con valores repetidos):

En otra hoja queremos poner el nombre de un producto y que se desplieguen todas las órdenes de compra del producto.

Si usamos BUSCARV (o INDICE con COINCIDIR) para obtener todas las órdenes de compra de tornillos, sólo podemos obtener un resultado

Sólo podemos obtener un resultado por celda. No hay ninguna forma de escribir resultados de una fórmula en otra celda que no contenga la fórmula, al igual que no podemos cambiar la estructura de la hoja con fórmulas.
Si queremos poner en una hoja de Excel todos los resultados posibles de una fórmula de búsqueda tendremos que usar otras técnicas.

En esta nota mostraré dos técnicas para obtener todos los resultados. Una estrategia posible es usar múltiples fórmulas.


Establecemos por adelantado cuál pueda ser el número máximo de resultados a obtener y en un rango de celdas contiguas introducimos esta fórmula matricial:

=INDICE(od_compra;K.ESIMO.MENOR(SI(DESREF(od_compra;0;0;FILAS(od_compra);1)=$B$2;FILA(DESREF(od_compra;0;0;FILAS(od_compra);1))-FILA(DESREF(od_compra;0;0;1;1))+1;FILA(DESREF(od_compra;FILAS(od_compra)-1;0;1;1))+1);FILA()-3);2)

donde el nombre "od_compras" define el rango A2:B10 de nuestro ejemplo

Por ejemplo, en una nueva hoja ("formulas") introducimos la fórmula en la celda A4 y la copiamos en todo el rango hasta la celda A9 (estamos suponiendo que el número máximo de órdenes posibles es seis)

Dado que sólo hay tres órdenes de compras para tornillos, el resultado en las celdas A18:A20 es #¡REF!

Para ocultar estos resultados podemos usar formato condicional

Esta técnica tiene varias desventajas: el uso de fórmulas matriciales, el uso de DESREF que es volátil, y además es complicada.

Una técnica alternativa es usar la cámara junto con Autofiltro.

Activamos el Autofiltro en la tabla de datos (en la hoja "datos"). Luego seleccionamos todo el rango de la tabla sin incluir los encabezamientos y activamos la cámara; creamos una nueva hoja ("cámara") y pegamos la imagen en el lugar deseado

En la hoja Datos filtramos la tabla de acuerdo al producto deseado

La imagen en la hoja "cámara" reflejará el cambio instantáneamente

El inconveniente con este modelo es que debemos ir a la hoja Datos para hacer el filtrado. Si queremos manejar los resultados desde la hoja "cámara", tendremos que utilizar una macro para manejar el filtrado por "control remoto".

Empezamos por agregar una lista desplegables, con validación de datos, en la celda B2 de la hoja "cámara" para poder controlar el filtrado

Luego ponemos este código en un módulo común del editor de Vb

Sub filtrado_ordenes()
    Dim strCrit As String
    Dim rngTablaDatos As Range
  
    strCrit = Sheets("camara").Range("B2")
  
    Set rngTablaDatos = Sheets("datos").Range("A1").CurrentRegion
  
    If strCrit <> "Todos" Then
        rngTablaDatos.AutoFilter Field:=1, Criteria1:=strCrit
    Else
        rngTablaDatos.AutoFilter
    End If
  
End Sub

Para activar el código cuando elegimos el producto programamos este evento en el módulo de la hoja "cámara"

Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Address = "$B$2" Then Call filtrado_ordenes

End Sub

Con esto hemos completado nuestro modelo. Podemos ocultar la hoja "datos" de manera que nuestro usuario sólo vea la hoja "cámara" desde la cual puede manejar las que órdenes serán exhibidas en la hoja.

El archivo con el ejemplo se puede descargar gratuitamente aquí.

Rangos dinámicos y funciones volátiles

En los comentarios de la nota anterior surgió el asunto de los rangos dinámicos y las funciones volátiles. A pesar de que estos temas han sido tratados de alguna manera en este blog, haremos una reseña en esta nota para aquellos lectores que no conocen el tema.

Empecemos por lo básico: ¿qué es un rango dinámico? El rango A1:A5 en la fórmula =SUMA(A1:A5) es estático.

Si agregamos una fila dentro del rango, Excel lo ajustará automáticamente convirtiendo la fórmula a =SUMA(A1:A6).

Pero si insertamos la nueva fila entre la fila 5 y 6 de nuestro ejemplo, Excel no ajustará el rango de la fórmula

Para superar este problema podemos convertir el rango A1:A5 de la fórmula en dinámico usando rangos nominados ("nombres"). Seleccionamos la última celda del rango (A5 en nuestro ejemplo) y usamos como referencia en un nombre que llamaremos "ultima_celda" (sin acentos y sin espacios en blanco que nos aceptables en nombres) la celda inmediata superior (A4 en nuestro ejemplo)

Hay que prestar atención a que la referencia es relativa (la dirección de la celda no incluye las "anclas" $)

Ahora podemos escribir nuestra fórmula de esta manera:

Otro tipo de situación se presenta cuando queremos que el rango de una lista se extienda o contraiga de acuerdo a la cantidad de elementos (celdas ocupadas) que la componen. Un ejemplo es cuando creamos una lista de valores para una lista desplegable con validación de datos.

La receta clásica es usar la función DESREF como en este ejemplo

DESREF crea un rango empezando en el "ancla" (la celda A1 en nuestro caso) y usando la función CONTARA para determinar cuántas filas están incluidas en él.

Para crear listas desplegables dependientes usamos la función INDIRECTO.

Aquí comienza la cuestión con las funciones volátiles. ¿Qué es una función volátil?

No todas las fórmulas de una hoja son recalculadas cada vez que introducimos un cambio. Excel determina qué celdas serán afectadas por el cambio y cuáles no y sólo recalcula las primeras.

Sin embargo hay algunas funciones que son recalculadas con cualquier cambio en la hoja. Estas son las funciones volátiles. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones.

Entre las funciones volátiles de Excel se cuentan, justamente, DESREF e INDIRECTO.

Podemos construir rangos dinámicos sin usar DESREF e INDIRECTO con estas técnicas:

1 – si usamos la versión 2003 de Excel o posteriores, la mejor alternativa en mi opinión es usar Listas (Excel 2003, Tablas en Excel 2007/2010). No nos complicamos la vida con fórmulas complejas que a veces pueden producir errores inadvertidos y le dejamos que todo el trabajo lo haga Excel por detrás de las bambalinas.

2 – Usar una combinación de INDICE y CONTARA:

=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A))

Finalicemos la nota diciendo que la influencia de las funciones volátiles en una hoja de Excel será significativa sólo cuando se haga uso intensivo de ellas, algo similar a lo que ocurre con las fórmulas matriciales. Por eso, y como en muchos otros aspectos de la vida, las cuestiones no son absolutas. Usar funciones volátiles no es ni buena ni mala práctica; todo es una cuestión de cantidad.

Promedios con SUBTOTALES sin incluir ceros.

Un lector me consulta si se puede usar la función SUBTOTALES para calcular un promedio sin tomar en cuenta los ceros.

Recordemos que la función SUBTOTALES tiene la propiedad de realizar cálculos sin tomar en cuenta valores ocultos al aplicar Autofiltro (o con Ocultar filas usando el argumento 109 en lugar de 9 para SUMA, 101 en lugar 1 para PROMEDIO, etc).

Supongamos esta tabla de dos columnas: Categorías y Valores

La fórmula en la celda B12 es

=SUBTOTALES(1,B2:B10)

donde el argumento “1” indica que queremos calcular el promedio.

Ahora filtramos la lista para dejar visible sólo los valores de la categoría C

Podemos ver que el resultado es 3.67. Si no tomamos en cuenta los ceros, el resultado tendría que ser 5.50 (aclaremos que debemos tener un buen motivo para no tomar en cuenta los ceros, a diferencia de celdas vacías que no son tomadas en cuenta por la función).

Una solución es crear una columna auxiliar con la fórmula =SI(B2<>0,B2,"") en la columna C

En la celda C12 ponemos la fórmula con la función SUBTOTALES, que al filtrar dará el resultado esperado

Si no queremos usar una columna auxiliar, podemos crear esta fórmula, adaptada de una propuesta por Bob Phillips

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B2,FILA($B$2:$B$10)-2,,1))*($B$2:$B$10<>0))/
SUMAPRODUCTO(SUBTOTALES(3,DESREF(B2,FILA($B$2:$B$10)-2,,1))*($B$2:$B$10<>0))

Technorati Tags: MS Excel

Elementos únicos y repetidos en dos listas

En la nota de ayer vimos cómo extraer elementos únicos o repetidos en una lista usando fórmulas matriciales. En el ejemplo de ayer la condición era que los elementos estaban ubicados en una única lista, es decir, en una sola columna.

Usando fórmulas similares podemos también extraer elementos repetidos o únicos de dos listas.

En este caso supondremos dos listas de 9 nombres

Hemos creado dos nombres para contener los rango de las listas

lista1 = A2:A10
lista2 = B2:B10

Para extraer los nombres que repetidos, aquellos que aparecen en ambas listas usamos esta fórmula matricial “multicelular”

={SI(CONTAR.SI(lista1,lista2)>0,lista2,"")}

Recordemos que para usar esta fórmulas seleccionamos previamente el rango (en nuestro caso C2:C10) y luego introducimos la fórmula apretando simultáneamente Ctrl+Mayúsculas+Enter.

Por supuesto queremos que la lista de nombres repetidos aparezca ordenado, para lo cual usamos la función INDICE en forma matricial de la siguiente manera

=INDICE(lista1,K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,""),FILA()-1))

Al rango D2:D10 aplicamos formato condicional para ocultar los resultados #NUM! que aparecen.
La fórmula funciona de la siguiente manera:

+ la expresión SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,"") genera un vector de números o blancos. Cuando un nombre de la lista 1 aparece en la lista 2, la fórmula produce un número equivalente al número de fila menos 1; en caso contrario produce un valor en blanco.

+ Este vector los ordenamos con la función K.ESIMO.MAYOR

K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,""),FILA()-1)

+ Este vector ordenado nos sirve como argumento en la función INDICE para extraer los elementos de las filas correspondientes en la lista 1.

Para extraer elementos únicos, debemos definir previamente el orden de comparación. Es decir, nombres de la lista 1 que no aparecen en la lista 2 o nombres de la lista 2 que no aparecen en la lista 1.

Para extraer los nombres de la lista 1 que no aparecen en la lista 2 usamos la fórmula matricial

=INDICE(lista1,K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)=0,FILA()-1,""),FILA()-1))

En las fórmulas que hemos mostrado podemos usar también K.ESIMO.MENOR para cambiar el orden de aparición de los nombres:

=INDICE(lista1,K.ESIMO.MENOR(SI(CONTAR.SI(lista2,lista1)=0,FILA()-1,""),FILA()-1))

Technorati Tags: MS Excel

Extraer elementos únicos y repetidos con fórmulas.

Si tenemos una lista de valores ordenados en una única columna podemos obtener una lista de los valores repetidos o de los valores únicos usando fórmulas.

Supongamos esta lista de nombres. Los nombres repetidos los hemos marcado con un fondo de color usando Formato condicional





Si queremos crear una lista de valores únicos en el rango B2:B19, usamos esta fórmula matricial:

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))




El archivo con el ejemplo se puede descargar aquí
Dos observaciones importantes en relación a esta fórmula:

1 – ésta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter

2 – ésta es una fórmula matricial “multicelular”, es decir, la misma fórmula da un resultado distinto en cada celda. Por esto primero debemos seleccionar el rango que va a ocupar la fórmula y luego introducirla.

Si queremos obtener una lista de los elementos repetidos, modificamos levemente la fórmula

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)<>1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))


De la misma manera, si queremos extraer los elementos que se repiten 3 veces (no los hay en el ejemplo), usaríamos

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=3)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))

Es decir, si queremos extraer los elementos que se repitan n veces, usamos
=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=n)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))
Si queremos que los resultados #¡VALOR! no aparezcan podemos usar Formato condicional





Una breve explicación de las fórmulas:
La fórmula

=(CONTAR.SI(Lista,Lista)=1)

genera un vector de valores VERDADERO o FALSO que multiplicamos por el número de fila para generar una serie ordenada
=(CONTAR.SI(Lista,Lista)=1)*FILA()-1


Restamos 1 para dado que la primer fila en el rango es 2.

Los nombres repetidos dan como resultado -1; los valores únicos dan el número de fila menos 1.
Ahora tenemos que ordenar este vector, para lo que usamos K.ESIMO.MAYOR
=K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista))))


Este vector nos sirve de argumento en la función INDICE para encontrar el elemento indicado.

¿Cómo haríamos para obtener los mismos resultados si los nombres estuvieran divididos en dos listas?




Technorati Tags: MS Excel

Listas desplegables en Excel con ajuste automático.

En Excel es my fácil crear listas desplegables. El método más práctico es usando Validación de Datos – Listas. En este blog hemos tratado el tema de diversas oportunidades y hemos mostrado también como crear listas desplegables dependientes.

En relación a este tema recibo a menudo consultas sobre cómo ir eliminando de la lista los valores que ya han sido seleccionados.

Supongamos que estamos organizando en que mesa se sentarán los invitados a una cena o recepción. Para el caso supongamos una lista de 16 invitados que habrá que sentar en cuatro mesas, cuatro en cada una.

Empezamos por poner la lista de invitados en una hoja y una tabla que representa las mesas y sillas en otra








El objetivo es crear una lista desplegable que pondremos en cada celda del rango C3:F6 (rango que hemos puesto en el nombre “mesas”) de manera que cada vez que ubiquemos un comensal en la tabla éste desaparezca de la lista.

Mostraremos dos posibilidades: con columnas auxiliares y con fórmulas matriciales.

Con columnas auxiliares.

En la hoja que contiene la lista de invitados creamos una columna auxiliar con el rótulo “Por ubicar”




El objetivo de esta columna es controlar que invitado ya ha sido ubicado en alguna mesa. Para eso usamos la fórmula

=SI(CONTAR.SI(mesas,C2)=1,"",C2)

Por ejemplo, si ubicamos a Carla y a Federico en la matriz de las mesas, obtenemos







Ahora agregamos una nueva columna auxiliar (Aux1)



La fórmula

=CONTAR.SI($D$2:$D$17,"<="&D2)

da un número de orden a los valores del rango C2:C17 (como la función JERARQUIA a una serie de números).
En caso que el valor de celda sea vacío (la celda no está vacía ya que contiene una fórmula) el resultado es 0 (cero).
Nuestro próximo paso es crear una celda para controlar cuantas invitados nos quedan por ubicar, es decir, cuantos valores distintos de cero hay en el rango Aux1. Con este objetivo ponemos esta fórmula en la celda J1
=CONTAR.SI(E2:E17,">0")




Ahora creamos la columna auxiliar Aux2 en el rango F2:F17 con esta fórmula

=K.ESIMO.MAYOR($E$2:$E$17,$J$1-FILA()+2)




Esta columna nos sirve como argumento para la columna final, en el rango G2:G17, donde obtenemos los valores de la lista desplegable



Para evitar que en la lista desplegable aparezcan los valores #NUM de las últimas dos celda, usamos el valor de la celda “control” para crear un rango dinámico que las excluya con la fórmula

=DESREF(invitados!$G$2,0,0,invitados!$J$1,1)



Aplicamos esta validación de datos al rango “mesas”.



A medida que vamos ubicando a los invitados, la lista se va ajustando y mostrando sólo aquellos que quedan por ubicar



Con fórmulas matriciales.

Al igual que en el modelo anterior creamos la lista de los invitados a ubicar en el rango D2:D17.

La lista de invitados a ubicar la creamos en el rango E2:E17 con esta fórmula matricial

={INDICE($D$2:$D$17,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI($D$2:$D$17,"<="&D2:D17),FILA()-1),CONTAR.SI($D$2:$D$17,"<="&D2:D17),0))}





Como puede verse, no obtenemos errores #NUM, pero las celdas al final de la lista no están vacías. De manera que necesitamos una celda de control, como en la solución con columnas auxiliares, para que la lista desplegable muestre sólo los nombres disponibles.
En la celda H1 ponemos esta fórmula matricial

={SUMA((CONTAR.SI($D$2:$D$17,"<="&D2:D17)<>0)*1)}





La lista desplegable la creamos con el fórmula

=DESREF('invitados matricial'!$E$2,0,0,'invitados matricial'!$H$1,1)

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Technorati Tags: MS Excel