Reto 03: Separar los valores de las unidades de medida
A raiz de una plantilla de Excel que estoy desarrollando para un cliente, en la que se deben separar unos valores de sus respectivas unidades (casi todas diferentes), se me ha ocurrido plantear el Reto 03.
Ayúdame a obtener otras soluciones diferentes a la que le he dado yo en la plantilla:
Observa la imagen:
Como ves, en la misma celda se pueden dar datos que mezclan valores con unidades. Cuando esto sucede, Excel trata todo el contenido de la celda como si fuera texto y es totalmente imposible hacer operaciones matemáticas con esos números. Por eso quiero separar los números de las letras.
Para separar un número de la unidad, primero se debe identificar la posición donde se encuentra el último número. Sumando 1 a esta posición, se obtiene la posición donde se encuentra el primer carácter de la unidad.
En el ejemplo de la imagen la fórmula sería:
=MAX(ESNUMERO(VALOR(EXTRAE(A4;{1;2;3;4;5;6;7;8;9};1)))*{1;2;3;4;5;6;7;8;9})+1Utilizo la función EXTRAE para extraer los primeros 9 caracteres de la celda A1, de uno en uno. El resultado que se obtiene es este:
{"8";"5";"0";"k";"g";".";"";"";""}Ahora es el turno de VALOR, que convierte esta matriz en texto con formato de número, obteniendo este resultado:
{8;5;0;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!}La función ESNUMERO utiliza este resultado para determinar si cada valor de la matriz es un número o no:
{VERDADERO;VERDADERO;VERDADERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}Ahora la matriz resultante se multiplica por outra matriz com otros nueve valores y se obtiene este resultado:
{1;2;3;0;0;0;0;0;0}Para finalizar, la función MAX se encarga de determinar el valor más alto de la matriz. Este valor indica la última posición en la que se halla un número. Como quiero saber en qué posición comienzan las unidades, le sumo 1 a este resultado.
Esta fórmula sirve solamente para saber en qué posición se encuentra el primer carácter no numérico. Ahora toca separar cada parte (valores y medidas) con otras dos fórmulas más sencillas:
- =VALOR(IZQUIERDA(A5;B5-1)) para la parte numérica. Como el resultado de IZQUIERDA es un texto, le aplico la función VALOR para convertirlo en número.
- =DERECHA(A4;LARGO(A4)-B4+1) para la parte de las unidades de medida.
Esta es una de las muchas formas de separar el contenido de una celda en valores y unidades de medida. El único inconveniente que tiene es que sólo funciona con celdas que contienen hasta 9 caracteres. En el caso de la plantilla que estaba desarrollando, no había ningún inconveniente porque en ningún caso se superaba este largo.
Descarga el ejemplo para practicar:
Ahora te toca a ti
Demuestra a los seguidores de Ayuda Excel cómo lo harías tú. Escribe tu comentario con tu fórmula para separar valores de unidades de medida.
¡Hola Sergio!
Para la Cantidad:
=-BUSCAR(1;-IZQUIERDA(A4;FILA(INDIRECTO(“1:”&LARGO(A4)))))
Para la Unidad:
=EXTRAE(A4;1+LARGO(E4);8^5)
¡Bendiciones!
Gracias por tu respuesta. Esta solución funciona para cualquier cantidad de dígitos.
me guta.. 😉
Hola Sergio
Tengo esto, para los números:
“=EXTRAE(C3,1,COINCIDIR(0,INDICE(–(ESERROR(–EXTRAE(C3,FILA(INDIRECTO(“1:”&LARGO(C3))),1))),),1))”
para la unidad
“=SUSTITUIR(C3,E3,””)”
🙂
Saludos,
Silvia
Muchas gracias, Silvia!
Pues..
con ASAP
Seleccionas el rango
ASAP/TEXTO/
elegir opción 13
dejas desmarcado números
(se hace seleccionando 1,2,3)
y también, desmarcas la coma , que está en la misma columnas de los números.
Aceptar y Cerrar
para las unidades, Sustituir, como lo mencioné antes
=SUSTITUIR(C3,F3,””)
**Ahhh pues, recién veo que estaba el archivo de descarga 😉 **
pero si se entiende, no? ^^
Silvia
Tengo que utilizar más ASAP. Es una herramienta increible a la que no saco todo el rendimiento que debería…
ya somos dos.. 😉
Valor
=IZQUIERDA(A4;LARGO(A4)-SUMAPRODUCTO((ESNUMERO(ENCONTRAR(EXTRAE(A4;FILA($1:$8);1);”ABCDFGHJKLMNPQRSTVWXYZ.”)))*1)+SI(ESERROR(HALLAR(“,”;A4));0;1))*1
Unidades
=SUSTITUIR(A4;H4;””)
siendo H4 la celda donde está la función anterior
Otra opción con una UDF
Function SoloNum(celda)
For pos = 1 To Len(celda)
num = Mid(celda, pos, 1)
Select Case num
Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, “,”
NoText = NoText & num
End Select
Next
SoloNum = NoText * 1
End Function
ioyama, muchas gracias!!
Hola de nuevo
En la función anterior tengo un error, a partir de cierto número de caracteres falla.
Otra opción (matricial)
=EXTRAE(A4;COINCIDIR(VERDADERO;ESNUMERO(VALOR(EXTRAE(A4;FILA(INDIRECTO(“1:”&LARGO(A4)));1)));0);SUMA((ESNUMERO(VALOR(EXTRAE(A4;FILA(INDIRECTO(“1:”&LARGO(A4)));1))))*1)+SI(ESERROR(HALLAR(“,”;A4));0;1))*1
Recordad Control+Mayúscvulas+Intro
Un saludo desde Vitoria
hola a todos
aquí una mas, para los números
=IZQUIERDA(A4,COINCIDIR(0,–ESERR(HALLAR({1,2,3,4,5,6,7,8,9,0},B4,1)),)-1)
Silvia.
Una mas
Para la unidad
=EXTRAE(A4,BUSCAR(2,1/(EXTRAE(A4,FILA(INDIRECTO(1&”:”&LARGO(A4))),1)+1),FILA(INDIRECTO(1&”:”&LARGO(A4))))+1,5)
Para los numeros
=IZQUIERDA(A4,HALLAR(C4,A4)-1)
Saludos a todos
Muchas gracias, Gerson!
¡Que formulas mas complejas! ¿Cómo se aprende a utilizar combinaciones de funciones para lograr estos resultados increibles? Excelente explicación, Sergio, muchísimas gracias por compartir tus conocimientos
Juan, te resultarán complejas hasta que compruebes que estas fómulas largas son el resultado de otras más pequeñas. Debes comenzar a leerlas desde dentro hacia afuera. Ya verás qué pronto dejas de verlas complicadas.
La solución planteada originariamente es realmente interesante, y la forma de combinar estas funciones me será de gran utilidad en algún proyecto. Pero puede dar un problema si en las propias unidades aparece algún número, como, por ejemplo, si se utiliza “m2” para medir superficies o “m3” para volúmenes. Y, aparte de la limitación a 9 caracteres que ya se mencionó, tampoco funcionaría correctamente si las unidades se antepusiesen a la cifra, como puede ocurrir en ocasiones al referirse a cantidades de divisas, donde es frecuente indicar primero la moneda y luego la cifra (por ejemplo, 56 dólares podrían escribirse como “56 $” o como “USD 56”). Por otro lado, si las unidades de medida incluyesen algún espacio o varios espacio, estos espacios se trasladarían al resultado (por ejemplo, si hubiese un dato como “850 kg”, la cantidad sería 850, pero la unidad de medida sería “ kg”).
Por otro lado, en mi trabajo diario he comprobado la gran tendencia que tienen las bases de datos a corromperse por una mala sintaxis de la información que se incluye en ellas. Por ejemplo, es frecuente encontrarse con tablas en la que aparecen “José Antonio”, “José Ant.”, “José A.”, “J. Antonio”, “J. A.”, y otras posibilidades con acento y sin él, con más de un espacio, con errores de tecleo (por ejemplo, “Josñe Antonio”), con espacios al comienzo y al final (“ José Antonio” o “José Antonio ”), etc. En una tabla con unidades de medida podrían encontrarse “metro”, “metros”, “mt”, “m”, “m.”, “met.”, “ m.”, “m. ”, y cualquier variante imaginable, con puntos, sin ellos, con espacios, sin ellos, con abreviatura, sin ella, en singular, en plural… Esto suele ocasionar problemas a la hora de buscar información o de filtrar listados, o de agrupar información con funciones de suma o de recuento condicionales. En mis herramientas no sólo intento que los cálculos tengan en consideración la posible existencia de estos “ruidos”, sino que procuro que la propia herramienta impida que se cometan estas imperfecciones o que, al menos, avise cuando se detecte.
He ideado una solución alternativa que, aunque también tendrá otros inconvenientes, tiene algunas ventajas relacionadas con estas cuestiones. La describo para que cada usuario pueda tomar lo que le resulte más útil de unas y otras propuestas.
En primer lugar, se crea una lista de unidades de medidas válidas. Por ejemplo, en E23:E28 (por decir algo), se ponen los números 1, 2…, 6, y en F23:F28 se pone “kg.”, “m2”, “hp.”, “EUR”, “USD”, “ft”, o lo que se necesite. Estas serían las formas que requieren las unidades, de modo que los kilos siempre se tengan que escribir como “kg.” y no valga “kg” (sin punto), ni “k”, ni “kilo”, ni “kgs”, ni nada distinto de “kg.”. Si en algún dato apareciese una sintaxis diferente, la hoja de cálculo lo detectaría y advertiría de ello, para que se revise la escritura o se incluya esa sintaxis en la lista de unidades válidas. Al rango E23:E28 le asignamos el nombre “serie” y al rango F23:F28 el nombre “unidades_válidas” (esto es conveniente, por ejemplo, por si quisiesen usarse estos rangos en reglas de validación de otras hojas).
Una vez creada la lista de unidades de medida “bien escritas”, para cada dato original debemos separar la cifra y la unidad de medida. Lo primero que se debe hacer es calcular en qué fila de la lista de unidades de medida válidas está la unidad de medida del dato original. Esto se conseguiría para la primera fila de la tabla con la siguiente fórmula matricial:
=SUMA(SI(ESNUMERO(ENCONTRAR(Unidades_válidas;A4))=VERDADERO;serie;0))
Este resultado se utilizará luego como argumento en una función SUSTITUIR para aislar las cifras del dato original. Suponiendo que la fórmula anterior se hubiese escrito en F4, en H4 se escribiría:
=SI(F4=0;”-“;SUSTITUIR(A4;I4;””)*1)
Y en I4 se escribiría:
=SI(F4=0;”-“;INDICE(Unidades_válidas;F4))
Para obtener información adicional en aquellos casos en los que las unidades del dato original no estuviesen en la lista, se podría escribir en K4 algo parecido a esto:
=SI(F4=0;”Revisar sintaxis de las unidades o incluir en la lista”;”-“)
Se puede comprobar que si la lista de unidades válidas incluye “kg.”, “m2”, “hp.”, “EUR”, “USD” y “ft” (con esta exacta forma), las fórmulas descritas aislarían correctamente las cantidades y las unidades en los siguientes casos:
“850kg.”, “850 kg.” (con un espacio entre las cifras y las letras), “850 kg. ” (con varios espacios al final), 3520 hp.” (con varios espacios intermedios), “8,5ft”, “EUR 120” (aunque las unidades de medida estén antepuestas a la cifra y haya un espacio en el medio), “USD 35” (con varios espacios), “10m2” (aunque haya cifras en la sintaxis de la unidad de medida), “ 10 m2” (aunque haya cifras en la unidad de medida, y haya espacios al comienzo de la cadena y en medio).
En todos estos casos, la fórmula funcione adecuadamente y, además, al separar cifras y espacios se toma la unidad de medida de la lista de unidades de medida válidas, de modo que da igual si en el dato original iba delante o detrás de la cifra, si llevaba un espacio o no lo llevaba, si llevaba uno o veintiuno, etc.
En cambio, las fórmulas anteriores no separarían cifra y medida en los siguientes casos:
En “850 kg”, porque la sintaxis que se ha incluido en la lista de unidades de medida válidas lleva un punto (“kg.”); en “3520hp” (por la misma razón); o en “250 kw” porque el “kw” no está incluido en la lista. En todos estos casos, la fórmula de la columna K nos invitaría a cambiar el dato introducido en la columna A o a incluir una nueva unidad de medida (o una nueva sintaxis) en la lista de unidades admitidas.
La solución propuesta tampoco se ve afectada por la limitación de 9 caracteres, de modo que permite abarcar un abanico de posibilidades bastante amplio.
Seguro que esta solución también tiene otros inconvenientes, así que no la ofrezco como una alternativa “mejor” que ninguna de las demás que se han propuesto, sino como una solución “distinta”, de la que quizás alguien pueda sacar algún provecho, en este concreto problema o en otros proyectos.
Rafael, ¡gran respuesta y justificación! En la mayoría de las ocasiones, cuando encontramos una solución que nos es suficientemente válida, dejamos de buscar otros métodos. Veo que tú has ido más alla y eso me gusta.
En cuanto a mi “método”, comentas que si las unidades contienen un número (m2), la fórmula no funcionaría porque contaría ese dos como parte del valor. He comprobado que no es así. La fórmula sólo lee los caracteres hasta que encuentra una letra. De ahí hacia el final, lo computa como unidad de medida.
He sustituido en el fichero original los “850kg.” de A4 por “850m2” y entonces en “posición” (B4) aparece 6, lo que origina un error “#¡VALOR!” en C4 y D4 queda en blanco.
Si en A5 sustituyo “3520hp” por “3520m2” ocurre lo mismo, e igualmente en las demás celdas de la columna A (copiando las fórmulas, claro).
La fórmula de la columna B, si no la interpreto mal, calcula la última posición de la cadena donde se encuentra un número (dentro de los 9 primeros caracteres), que es una técnica distinta de buscar la primera posición donde haya una letra. La anidación de EXTRAE, VALOR, ESNUMERO y MAX me parece formidable, pero calcula la máxima posición de un carácter numérico. Cuando hay algún número detrás de una letra y esto ocurre en las 9 primeras posiciones (por ejemplo, en “850m2”), la función MAX(…) arroja un 6 como resultado, pues el 2 de “m2” está en la quinta posición y se le suma 1 suponiendo que será en la posición siguiente (la sexta) donde empiecen las unidades. Pero, en este caso, las unidades ya habían empezado en la posición 4.
Como consecuencia del cálculo anterior, la función IZQUIERDA de C4 tomaría como valor numérico “todo hasta el 2”, asumiendo que está ese “2” se acaba la cantidad, pero entonces se encuentra con una cantidad igual a “850m2”, que no es realmente la cantidad. Y las unidades quedan en blanco porque a partir de la posición que toma como comienzo ya no hay nada.