uso (bastante) avanzado de funciones matriciales

Bueno... me hicieron una pregunta que logró "castañearme" los dientes un buen rato. Aunque ya tengo una entrada sobre como detectar si existen números dentro de una cadena alfanumérica (por ejemplo: cualquiertexto123) y separarlos de dicha cadena, esta consulta tiene sus "bemoles" y, de paso, nos ayudará a repasar el poderoso e interesante (y poco utilizado) mundo de las funciones matriciales. 

Vamos por partes.

Planteo: mensualmente me pasan una lista de productos, con la siguiente información: código, nombre y precio, pero todos ellos dentro de una cadena de texto. Cada campo se encuentra separado por caracter especial, en este caso un guión medio ( - ). Esto sería, mas o menos, algo así:

codigo-nombre-precio, tal cual lo dicho anteriormente.

[+/-] Ver el resto / Ocultar

Mi amigo necesita "sacar" de esa cadena de texto el último campo (precio) e individualizarlo en una celda aparte.

Les voy avisando que esta no se viene fácil.... por las dudas. Algunos amigos me dicen "¿para qué escribís algunas cosas tan complicadas....?" Y la respuesta es sencilla: ".... por que mis lectores me las preguntan". Es una realidad y, por suerte, existen millones de dudas dando vueltas, que "aceitan" los engranajes cerebrales y a la vez permiten ayudar a alguien que anda "un poco complicado" con su labor diaria. Complicadas o no, cada pregunta realmente es un desafío, ya que los problemas planteados son únicos y con sus aditamentos exclusivos. Todo un lujo para mí, ya que logré aprender mucho gracias a ellos.

Aquí el principal problema es que no debemos utilizar macros. Insté, vía correo electrónico, a mi lector a que no le "escape" al código de VBA, pero lamentablemente ese no era su problema: en el trabajo (y por cuestiones obvias de seguridad) las macros de Excel se encuentran deshabilitadas, sin posibilidad de activarlas. Por lo tanto y como decimos en Argentina: "Fuiste". Chau, vamos a las funciones.

Deberemos llevar a cabo un "paso a paso" muy cuidadoso y detallado para entender el resultado final, ya que el objetivo es aprender y no "copiar y pegar" una función que en realidad no comprendemos.

Empecemos.

¿Por donde empiezo?

Mi punto de vista (y habrá muchos mejores) es: a cada campo de la cadena "codigo-producto-precio" lo separa, indefectiblemente, un guión... y justo tengo uno antes de que empiece el dato que deseo extraer: el precio. Si la cadena de texto en cuestión tuviese una longitud fija la tarea sería muy sencilla y se solucionaría con la función =EXTRAE(texto, posición inicial, cantidad de caracteres), dado que ese segundo guión estaría siempre en el mismo lugar. Miren este ejemplo:

b1-un producto   -100

c3-otro producto -200

d4-otro mas      -600

Todos los registros tienen el mismo largo (21 caracteres), siendo que el guión medio que me interesa está en la posición 18. La aplicación de la mencionada función sería:

como la longitud es fija no encontramos inconvenientes a la hora de separar el dato que necesitamos.

Pero bueno, este no es el caso. Y necesitamos recurrir a las funciones matriciales para que nos ayuden.

Ese último guión puede estar en cualquier posición, y vamos a encontrarlo. Demos comienzo a mi tan querido y mentado "paso a paso".

Seguiremos utilizando a EXTRAE(). Uds. ya vieron que el segundo argumento es la posición inicial desde donde deseo extraer cierta cadena de texto.... y lo haremos "variable" gracias a la función =FILA():

=EXTRAE(A1;FILA(1:$50);1)

Ahora en el segundo argumento tenemos una matriz creada con FILA(), de 50 posiciones. El resultado es el que se observa en la siguiente imagen:

Ahá, solamente la "b". ¿Por qué? Si copio y pego esa función hacia abajo comprenderemos mejor el asunto:

Si !!!!!! Al llevar la función "hacia abajo" el "contador" de =FILA() se fue incrementando de a uno. A propósito me situé sobre la fila 4: observen que el resultado es un 0 (cero), o sea, el cuarto caracter de la cadena de texto. Estirándome lo suficiente hacia el final, pude colocar automáticamente una letra en cada celda, gracias a =FILA(1:$50). Noten que al segundo valor (50) le puse un $ adelante, convirtiéndolo en referencia Absoluta, para que no se desplace, si no que solamente lo haga el primer valor.

Pero de todos los caracteres posibles, a mí solo me interesa el guión medio, por cuanto realizaré una pequeña modificación a la fórmula y así pedirle que me devuelva Verdadero o Falso si el caracter en cuestión es el que busco:

=(EXTRAE(A$1;FILA(5:$50);1)="-")

agregué al final un comparador, el signo igual, diciéndole a Excel algo así: ¿el caracter extraído es igual a un guión medio?... y mi querido programa me responde:

Bien, de primera. B5 y B15 contienen VERDADERO, por que justo en esas posiciones encontramos los dos guiones medios que posee la cadena. El desafío continúa al intentar ubicar solamente al segundo de ellos.... sigamos modificando un poco mas la fórmula:

=(EXTRAE(A$1;FILA(1:$50);1)="-")*FILA(1:$50)

Recordemos que VERDADERO es igual a uno y FALSO a cero. Si entonces le multiplico un número cualquiera (en este caso la fila), tengo a mi alcance este resultado:

Todo sigue bien: a la matriz VERDADERO/FALSO la convertí en una matriz numérica, en donde gracias a multiplicar el nro de fila ahora tengo la posición de cada guión medio (5 y 15 respectivamente).

Para saber "a ciencia cierta" donde está el último guión agregaré la función MAX() para identificar ese 15 dentro de la matriz, dado que es el valor mas alto de la misma. Ojo, por que ahora sí presiono "ctrol + shift + enter" y hago matricial todo este asunto:

{=MAX((EXTRAE(A$1;FILA(1:$50);1)="-")*FILA(1:$50))}

Con esta utilidad matricial evitamos el uso de filas o columnas auxiliares, ya que Excel "arma en el aire" la matriz y luego nos permite mostrar su máximo valor, todo ello en una celda.

Ese 15 que obtuvimos en B1 es un gran gran paso. Ya sabemos que allí está el último guión medio de la cadena de texto. Necesito ahora extraer todo lo que se encuentra a la derecha del mismo, para lo cual contamos con la función =DERECHA(texto, nro de caracteres): primer argumento: texto de donde necesito extraer los caracteres, segundo argumento: que cantidad de caracteres. Si en A1 tuviese la palabra "Blog" y hago =DERECHA(A1, 2), obtendría "og".

Pero claro, aquí seguimos con el problema de que todo es variable y en cada celda necesitaré analizar cuantos caracteres de la derecha debo extraer.

Esta es una solución viable:

Como segundo argumento (longitud de la cadena a extraer desde la derecha) resto el largo de A1 menos la posición en la que se encuentra el último guión (15). Pero como el objetivo final de esto es centrar el resultado en una sola celda, aún nos queda un pequeño trabajo: el segundo argumento de =DERECHA() es LARGO(A1)-B1, así que debemos reemplazar ese B1 por la función matricial que armamos anteriormente... y a no asustarse ahora, por que queda "medio raro" todo esto, pero ya lo vinimos desglosando de a poco:

{=DERECHA(A1;LARGO(A1)-MAX((EXTRAE(A$1;FILA(1:$49);1)="-")*FILA(1:$49)))}

en color mas oscuro tienen individualizado nuestro trabajo anterior. Resultado final:

jaja, terminamos. a pesar de las longitudes dispares siempre obtenemos el campo "precio".

¿Terminamos realmente? Si y No. Verdadero y Falso. Uno y Cero.... el Ying y Yang... ahhh, bueno loco, te pusiste filosófico. Jajajaja. Esos números de la columna B son para Excel texto, ya que las funciones como DERECHA(), IZQUIERDA(), EXTRAE() y otras tantas convierten a ese tipo de datos cualquier valor. Pero se soluciona muy sencillamente: al resultado lo multiplicamos por 1 (uno):

ahora sí son números y podemos efectuar sobre ellos las operaciones a las que estamos acostumbrados.

Otra opción, para el que no lo sabe, sería encerrar toda la función dentro de =VALOR(), fórmula esta que se encarga de convertir a número cualquier caracter de texto válido (entiéndase como válido a caracteres numéricos que Excel toma como texto). Si hago =VALOR("a")... sería inútil, ya que "a" es una letra, pero si en cambio realizamos =VALOR("1"), ese "1", que entre comillas es texto, sería convertido a un valor numérico.

Bien, llegamos al final. En este archivo adjunto podrán ver bien todo el proceso e ir desmenuzándolo para practicar convenientemente. Espero haber explicado este tema lo mejor posible.

Saludos y gracias por todos sus mensajes, sean en este blog o enviamos a mi correo.