Extraer el ultimo elemento de un texto en una celda en Excel

Ayer un compañero de trabajo me trajo el siguiente problema. Había recibido una lista productos de nuestra empresa como esta




Mi compañero quería poner los números de catálogo en una columna y las descripciones de los productos en otra.
Como pueden ver el problema consiste en que el número de catálogo es la última palabra en la celda y el número de palabras por celda es variable. Esto hace que no podamos emplear Datos—Texto en Columnas.

La solución consiste en hacer algunas manipulaciones con funciones de Texto. Las funciones que usaremos en este caso son: DERECHA, ENCONTRAR, SUSTITUIR y LARGO.

Nuestro objetivo es construir una fórmula DERECHA(A1, número de caracteres). El problema es establecer el número de caracteres para cada celda.

El primer paso consiste en identificar el separador de las palabras; en nuestro caso es el espacio (que en fórmulas de Excel representamos " "). El número de caracteres será el número total de caracteres en la celda menos el número de caracteres hasta la última palabra, es decir hasta el último espacio.

Tomemos el texto de la celda A1



Usamos la formula SUSTITUIR(A1," ","") que da como resultado

DARKBLUENIPPLEWITHOUTTRAY022052801

Sencillamente hemos eliminado los espacios reemplazando " " por "".


Este resultado lo usamos como argumento en la función LARGO

LARGO(SUSTITUIR(A1," ","")) que da como resultado 34

Ahora usamos la fórmula

SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ","")))

Que da como resultado

DARK BLUE NIPPLE WITHOUT TRAY*022052801

Es decir, hemos puesto un asterisco * entre la anteúltima y la última palabra.
El truco consiste en que LARGO(A1) calcula el total de caracteres en la celda incluyendo los espacios en blanco (39); la fórmula LARGO(SUSTITUIR(A1," ","")) hace el cálculo sin los espacios en blanco (34). La diferencia (5) es la posición del último espacio.

Ahora podemos encontrar la posición de * usando
ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ",""))))

Que da como resultado 30

Ahora sabemos que el largo del texto es 39, y que nuestro separador de la última palabra se encuentra en la posición 30. Por lo tanto el largo de la última palabra es 9.

Así que usamos la fórmula

DERECHA(A1,LARGO(A1)-ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ","")))))

Que nos da el resultado esperado.

Esta solución fue propuesta por Ken Wright y mencionada por David McRitchie

Para separar la descripción usamos la función IZQUIERDA de esta manera

=IZQUIERDA(A1,LARGO(A1)-LARGO(B1))

cuando en B1 hemos puesto la fórmula que nos da el número de catálogo.


Technorati Tags: MS Excel

Corrigiendo la posición del signo menos en números importados

Una tarea bastante corriente en Excel es importar datos de fuentes externas, como sistemas ERP (SAP, Oracle,etc) o bases de datos. Suele suceder que al importar los datos a Excel los números negativos aparezcan con el signo menos a la derecha del número; por ejemplo 550-, en lugar de -550.
El problema en estos casos no es tanto la estética como el hecho que Excel no interpreta estos datos como números, sino como texto.
Mostraremos como corregir esta situación con una fórmula.

Supongamos que hemos importado esta lista de un sistema externo



Si agregamos en la columna B la fórmula =ESNUMERO() vemos que aquellos con el signo menos a la derecha son considerados por Excel como texto.



En la celda B2 ponemos esta fórmula

=VALOR(SUSTITUIR(DERECHA(ESPACIOS(A2);1)&A2;"-";"";2))

el resultado es el valor corregido



Pasemos a explicar como funciona esta fórmula, lo que hacemos de "adentro" hacia "afuera".

ESPACIOS(A2) todo lo que hace es asegurarnos que no hay espacios en blanco después del signo menos.

DERECHA(ESPACIOS(A2);1)&A2 extrae el signo menos a la derecha del número y "lo pone" por delante de él. El resultado de esta fórmula es que el signo menos aparece por delante y por detrás del número



SUSTITUIR(DERECHA(ESPACIOS(A2);1)&A2;"-";"";2) sustituye el segundo signo menos en -5119- por un blanco, con lo que obtenemos el resultado deseado.

Finalmente usamos la función VALOR para que el resultado de SUSTITUIR sea numérico.

Esta fórmula tiene un serio problema. Si la copiamos a lo largo del rango de números a corregir, los números positivos serán deformados



Esto se soluciona agregando una función SI a nuestra fórmula

=SI(DERECHA(ESPACIOS(A3);1)="-";VALOR(SUSTITUIR(DERECHA(ESPACIOS(A3);1)&A3;"-";"";2));A3)

Lo que hace la fórmula es evaluar si el valor en la celda a corregir tiene un signo "-" a la derecha; si lo tiene usa la fórmula; si no, copia la celda como tal.



Si no queremos usar columnas auxiliares, podemos usar esta macro

Sub sustituir_menos()
Dim Celda As Range

For Each Celda In Selection
If Trim(Right(Celda, 1)) = "-" Then
Celda.Value = Val("-" & Left(Celda, Len(Celda) - 1))
End If
Next

End Sub

Para que esta macro esté siempre disponible hay que copiarla a un módulo del Personal.xls usando el editor Vba.

Technorati Tags: MS Excel

Funciones Base de Datos en Excel

Escribiendo las notas sobre Filtro Avanzado me acordé de un tema que parece haber caído en desuso en Excel: las funciones Base de Datos (Database Functions, DBase Functions).

Estas funciones permiten analizar y realizar cálculos en base a listas. En Excel "lista" es una tabla de datos que en su primera fila tiene encabezamientos.

Tal vez uno de los motivos de la pérdida de popularidad de estas funciones sea la alternativa de usar tablas dinámicas (pivot tables) y fórmulas matriciales (array formulas).

Sin embargo en la mayoría de los casos, las funciones base de datos son preferibles a las fórmulas matriciales.
Las funciones matriciales son muy poderosas pero tienen un gran inconveniente: producen problemas de recálculo. El uso intensivo de fórmulas matriciales hace que los archivos tarden en recalcularse. En este terreno, las funciones base de datos son la mejor alternativa.

Las funciones base de datos comienzan todas con BD y tienen una sintaxis en común:
Función BD(base_de_datos, campo ,criterios).

El argumento base_de_datos es la tabla/lista que contiene los datos; campo es la columna sobre cuyos datos queremos realizar el cálculo y criterios es el rango que contiene los criterios para filtrar la base de datos.

Existen 13 funciones base de datos (XL2003), entre ellas BSUMA, BCONTAR y BPROMEDIO.

Consideremos esta lista





El rango A5:E20 está asociado al nombre "alumnos"

Si queremos calcular la cantidad de alumnos que cumplen dos condiciones simultáneamente: han recibido por lo menos 80 puntos en matemática y por lo menos 75 puntos en historia, hacemos lo siguiente



Nótese que en la primer fila de la hoja hemos replicado los encabezamientos de las columnas de la tabla de datos.

En la celda E22 hemos puesto la fórmula =BDCONTARA(alumnos;1;B1:D2)

donde:

alumnos =Hoja1!$A$5:$E$20 es el rango de la tabla de datos

1 indica que queremos contar las miembros de la primer columna de la tabla

B1:D2 es el rango de criterios con los cuales queremos "filtrar" la tabla

En lugar de usar el número de columna, podemos usar el encabezamiento, con lo cual la fórmula se vuelve totalmente legible

=BDCONTARA(alumnos;"nombre";B1:D2)

Al poner las definiciones de los criterios en la misma fila, estamos indicando el uso del operador Y, es decir, todas las condiciones deben cumplirse simultáneamente.

Para usar el operador O, ponemos las condiciones en filas distintas. Por ejemplo, si queremos contar cuantos alumnos hay en la lista que recibieron por lo menos 80 puntos en matemática o 75 en historia, arreglamos la hoja de la siguiente manera



Por supuesto, también hemos modificado el rango de los criterios en la fórmula para que incluya todas las filas

=BDCONTARA(alumnos;"nombre";B1:C3)

También podemos combinar los operadores Y y O en el rango de criterios. Por ejemplo, si queremos saber cuantos alumnos hay que recibieron por lo menos 80 puntos en matemática Y 75 en historia O más de 85 de promedio, usamos el modelo



También aquí hemos modificado la fórmula para que incluya todos los criterios

=BDCONTARA(alumnos;"nombre";B1:E3)

Las funciones base de datos son fáciles de usar, una vez que hemos entendido la sintaxis básica. Son muy flexibles y permiten hacer cálculos que con más facilidad y velocidad que las funciones matriciales

La lista completa de funciones base de datos es:

BDPROMEDIO Devuelve el promedio de las entradas seleccionadas de la base de datos

BDCONTAR Cuenta las celdas que contienen números en una base de datos

BDCONTARA Cuenta las celdas que no están en blanco en una base de datos

BDEXTRAER Extrae de la base de datos un único registro que coincida con los criterios especificados

BDMAX Devuelve el valor máximo de las entradas seleccionadas de la base de datos

BDMIN Devuelve el valor mínimo de las entradas seleccionadas de la base de datos

BDPRODUCTO Multiplica los valores de un campo determinado de registros de la base de datos que coinciden con los criterios especificados

BDDESVEST Calcula la desviación estándar basándose en una muestra de entradas seleccionadas de la base de datos

BDDESVESTP Calcula la desviación estándar basándose en la población total de las entradas seleccionadas de una base de datos.

BDSUMA Suma los números de la columna del campo de los registros de la base de datos que coincidan con los criterios especificados

BDVAR Calcula la varianza basándose en una muestra de las entradas seleccionadas de una base de datos

BDVARP Calcula la varianza basándose en la población total de las entradas seleccionadas de una base de datos

IMPORTARDATOSDINAMICOS Devuelve los datos almacenados en una tabla dinámica

Technorati Tags: MS Excel