Para el siguiente ejemplo, tenemos una serie de provincias, con unos encabezados que hacen referencia a centros comerciales, y, debajo tenemos una serie de productos vendidos en diferentes provincias.
Tenemos los productos sin repetir, y, ordenados, el ejemplo va a consistir en que en la celda de al lado de cada producto, debe de aparecer el centro o centros donde ha sido vendido dicho producto.
Si nos fijamos en el modelo podemos ver que el producto impresora HP, se ha vendido en el centro comercial Nervión, y, Metromar, pues es lo que queremos conseguir, que al lado del producto impresora HP, aparezca C.C. Nervión, y, Metromar.
Voy a usar el condicional SI para preguntar que si el valor en el rango B2:B16 es igual al producto que hemos seleccionado, que me devuelve el encabezado, en caso contrario, que devuelva un texto en blanco.
=SI(B2:E16=G2;B1:E1;"")
Obtenemos una matriz desbordada con los centros donde hay coincidencia, y, blanco donde no la hay.
Si seleccionamos la función, excepto el signo igual, y, pulsamos F9, podemos ver el centro o centros donde hay coincidencia, y, texto en blanco donde no la hay.
Pero, queremos que al lado del producto nos aparezcan los centros donde se ha vendido dicho producto.
Podemos usar la función CONCAT que ignora los espacios en blanco.
=CONCAT(SI(B2:E16=K2;B1:E1))
Lo tendríamos por el momento.
Fijamos los rangos de búsquedas, y, arrastramos.
=CONCAT(SI($B$2:$E$16=L1;$B$1:$E$1))
Pero, vemos que donde el producto se ha vendido más de una vez aparecen juntos.
Podría poner punto y coma entre comillas como ultimo argumento de CONCAT.
=CONCAT(SI($B$2:$E$16=G2;$B$1:$E$1;"");",")
Vemos que aunque haya vendido en un solo centro, nos aparece el punto y coma al final, cosa que no queremos.
Si somos suscriptor de office 365, tenemos una función llamada UNIRCADENAS, donde solo tenemos que especificar una vez el delimitador, y, lo pondrá en las cadenas que haya para unir, además, le diremos que ignore las celdas que estén vacías.
Además, esta función admite funciones.
Escribimos el signo igual seguido de la función UNIRCADENAS, y, abrimos un paréntesis.
=UNIRCADENAS(
Como delimitador ponemos, por ejemplo, la coma.
=UNIRCADENAS(",";
Ignoramos celdas vacías.
=UNIRCADENAS(",";VERDADERO;
Y ahora ponemos el SI que hemos usado anteriormente.
=UNIRCADENAS(",";VERDADERO;SI($B$2:$E$16=G2;$B$1:$E$1;""))
Aceptamos.
Arrastramos.
Podemos ver los centros separados por coma, y, donde hay un único centro, no aparece la coma.
Pero ¿Qué ocurre si un producto se repite para el mismo centro comercial?
Pues, que aparece dos veces dicho centro, y, debe de aparecer una sola vez.
Supongamos que no disponemos de la función UNICOS, que lo haría de forma rápida, poniendo esta función antes del condicional SI.
=UNIRCADENAS(",";VERDADERO;UNICOS(SI($B$2:$E$16=G2;$B$1:$E$1;"")))
Puedo usar la función ENCONTRAR de forma matricial, donde como primer argumento selecciono los encabezados, y, como segundo argumento es la primera celda donde hemos obtenidos el primer resultado.
Esta función nos va a devolver donde hay la primera coincidencia, por ejemplo, si busca el centro comercial Metromar, nos devolverá 1, porque compara Metromar con Metromar, y, la primera coincidencia es la letra M.
=ENCONTRAR(B1:E1;H2)
Fijo en rango de encabezados y arrastramos.
=ENCONTRAR($B$1:$E$1;H2)
Vemos para el primer producto, aspiradora, que se ha vendido en el centro comercial Nervión, aparece 1, que es la posición del primer carácter, y, después tenemos errores, porque la función ENCONTRAR solo hallo coincidencia con el primer encabezado.
Si nos fijamos donde impresora HP se ha vendido en el mismo centro comercial, además, de en Metromar, vemos la posición 1, que es donde comienza la C de C.C. Nervión, y,27 que es el carácter donde empieza la M de Metromar.
Si seleccionamos la función, excepto, el signo igual, y, pulsamos F9, veremos los resultados que vemos.
Debemos de quedarnos con los números, porque los errores no nos interesan.
Pues, tenemos una función llamada ESERROR, que solo tiene un argumento, que es la celda o celdas para examinar, y, nos va a devolver verdadero si la expresión devuelve un error, y, falso si no lo hace.
Pues englobemos la expresión anterior en esta.
=ESERROR(ENCONTRAR(B1:E1;I2))
Vemos que nos devuelve para el primer encabezado falso, porque no devuelve ningún error, y, para el resto verdadero, porque si lo devuelve.
Lo siguiente es preguntar que si la función ESERROR devuelve verdadero, o sea, es un error, que ponga un texto en blanco, en caso contrario, que me devuelve el encabezado.
=SI(ESERROR(ENCONTRAR(B1:E1;I2));"";B1:E1)
Tenemos una matriz desbordada en horizontal, en este caso, con un centro comercial.
Vamos a fijar los rangos de búsquedas, y, vamos a arrastrar.
=SI(ESERROR(ENCONTRAR($B$1:$E$1;I2));"";$B$1:$E$1)
Tenemos una matriz desbordada, con los centros donde se han vendido productos, pero nos aparece en la posición donde se encuentra el producto.
Pues, volvemos a usar la función UNIRCADENAS, usando como delimitador la coma, e, ignorando las celdas en blanco.
=UNIRCADENAS(",";VERDADERO;SI(ESERROR(ENCONTRAR($B$1:$E$1;I2));"";$B$1:$E$1))
Arrastramos, y, vemos los centros comerciales únicos, aunque un producto se haya vendido más de una vez en el mismo centro.
Pero ¿Qué ocurre?
La función ENCONTRAR está buscando el valor a comparar con la celda H2, y, queremos una sola función.
Pues vamos a seleccionar y copiar la expresión anterior.
Vamos a la expresión donde estamos trabajando, y, dentro de la función ENCONTRAR, borramos H2, y, pegamos la función.
=UNIRCADENAS(",";VERDADERO;SI(ESERROR(ENCONTRAR($B$1:$E$1;UNIRCADENAS(",";VERDADERO;SI($B$2:$E$16=G2;$B$1:$E$1;""))));"";$B$1:$E$1))
Aceptamos.
Arrastramos, y, tenemos los mismos resultados.
En vez de usar la función ESERROR, también podemos usar la función ESNUMERO.
Para ello sustituimos ESERROR por ESNUMERO, y dentro del condicional SI debemos de cambiar el valor verdadero por el valor falso y al contrario.
=UNIRCADENAS(",";VERDADERO;SI(ESNUMERO(ENCONTRAR($B$1:$E$1;UNIRCADENAS(",";VERDADERO;UNICOS(SI($B$2:$E$16=G2;$B$1:$E$1;"")))));$B$1:$E$1;""))
Aceptamos, y, seguimos teniendo los mismos resultados.
Comments