Seguimos trabajando con el modelo que suelo trabajar habitualmente, pero tenemos las ventas de productos para el año 2021, y, queremos elegir una provincia, y, que nos muestre cual fue el mes, o, meses de más ventas.
Primero, vamos a crear una lista desplegable de las provincias, para ello, vamos a usar la función UNICOS, y, ORDENAR, para traernos las provincias únicas.
Lo hare en la celda M2.
=ORDENAR(UNICOS(Table1[Provincia]))
En la celda I2, voy a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, en la ventana de origen, seleccionamos M2, junto con el operador de rango derramado (#), con esto nos aseguramos de que esta lista desplegable, siempre estará actualizada.
Aceptamos.
Seleccionamos una provincia.
En la celda J2, escribo el signo igual, abro un paréntesis, selecciono la columna de provincia, y, pregunto si es igual a la provincia que hemos seleccionado, y, cerramos paréntesis.
=(Table14[Provincia]=I2)
Escribe el símbolo de asterisco, abro otro paréntesis, y, le digo que me devuelva la fila de la columna provincia, siempre que haya coincidencia.
=(Table14[Provincia]=I2)*(FILA(Table14[Provincia]))
Obtengo una matriz desbordada con el numero de fila donde hay coincidencia, y, cero donde no la hay.
Para obtener la matriz solo con los números mayores a cero, voy a usar la función FILTRAR, para filtrar la expresión anterior, siempre que dicha expresión, sea mayor a cero.
=FILTRAR((Table1[Provincia]=I2)*(FILA(Table1[Provincia]));(Table1[Provincia]=I2)*(FILA(Table1[Provincia]))>0)
Obtengo una matriz desbordada solo con los números que son mayores a cero.
Como uso dos veces la misma expresión, puedo usar la función LET, donde creo una variable, le asigno la expresión (Table1[Provincia]=I2)*(FILA(Table1[Provincia])), y, después lo sustituyo, como sigue:
=LET(a;(Table1[Provincia]=I2)*(FILA(Table1[Provincia]));FILTRAR(a;a>0))
Ahora, con la función INDICE, voy a traerme las fechas que corresponden con dichos números de filas.
=INDICE(A:A;LET(a;(Table1[Provincia]=I2)*(FILA(Table1[Provincia]));FILTRAR(a;a>0)))
Tenemos las fechas de ventas, de la provincia seleccionada.
En la celda K2, con la función MES, me voy a traer el número de mes, de las fechas obtenidas.
=MES(J2#)
Obtengo una matriz desbordada con los números de meses, vemos que cada numero de mes, aparece mas de una vez, porque hubo diferentes ventas en cada mes.
Lo siguiente que necesito saber, son las veces que aparece, o, se repite cada mes, para ello, voy a usar la función FRECUENCIA, esta función tiene dos argumentos, que son datos, y, grupos, pues, para ambos argumentos voy a usar MES(J2#).
=FRECUENCIA(MES(J2#);MES(J2#))
Obtengo una matriz desbordada con las veces que aparece o se repite cada mes.
Con la función MAX, voy a extraer el valor máximo del resultado de la función FRECUENCIA.
=MAX(FRECUENCIA(MES(J2#);MES(J2#)))
Me dice que el mes que mas aparece es de cinco veces.
Ahora, compara el valor máximo de FRECUENCIA, con la función FRECUENCIA.
=FRECUENCIA(MES(J2#);MES(J2#))=MAX(FRECUENCIA(MES(J2#);MES(J2#)))
Obtengo una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.
Como se repite la función FRECUENCIA, usare la función LET, para asignar a una variable, la función FRECUENCIA.
=LET(a;FRECUENCIA(MES(J2#);MES(J2#));a=MAX(a))
Ahora, con el condicional SI, pregunto que, si FRECUENCIA es igual al valor máximo de FRECUENCIA, que me devuelva el nombre del mes de J2, con la función TEXTO, en caso contrario, que ponga un texto en blanco.
=SI(LET(a;FRECUENCIA(MES(J2#);MES(J2#));a=MAX(a));TEXTO(J2#;”mmmm”);””)
Vemos que, para Sevilla, nos devuelve el mes de diciembre, pero es una matriz desbordada, donde si no hay coincidencia, devuelve blanco, y, quiero quedarme solo con el o los datos, para ello, voy a usar la función UNIRCADENAS, donde como delimitador, usare la coma, ignorare las celdas vacías, y, las celdas a unir es J2#.
=UNIRCADENAS(",";VERDADERO;SI(LET(a;FRECUENCIA(MES(J2#);MES(J2#));a=MAX(a));TEXTO(J2#;"mmmm");""))
Elijo la provincia de Jaén, y, vemos que aparecen dos meses, separados por coma.
Ahora, uso la función DIVIDIRTEXTO, donde como argumento texto, es la expresión anterior, salto el argumento delimitador de columna, y, como delimitador de fila, pongo la coma.
=DIVIDIRTEXTO(UNIRCADENAS(",";VERDADERO;SI(LET(a;FRECUENCIA(MES(J2#);MES(J2#));a=MAX(a));TEXTO(J2#;"mmmm");""));;",")
Obtengo una matriz desbordada en vertical, solo con los meses, sin celdas en blanco.
Pues ya tengo el ejercicio hecho.
Commentaires