Tenemos una serie de provincias, y, años, donde tenemos los totales por provincia, y, año.
En este ejemplo, lo que queremos es seleccionar una provincia y un año, que nos devuelva el total.
En la celda I1, tengo una lista desplegable con las provincias.
En la celda K1, tengo otro desplegable con los años.
Pues, vamos a usar la función SUMAPRODUCTO, para obtener tanto la fila como la columna.
Empezamos por encontrar la fila en la celda H5.
Escribimos el signo igual, seguido de la función SUMAPRODUCTO, y, abrimos un paréntesis.
=SUMAPRODUCTO(
Como argumento matriz1, abrimos un paréntesis, y, ponemos la primera condición, que es que en el rango de provincias haya un valor igual a la provincia seleccionada, y, cerramos paréntesis.
=SUMAPRODUCTO((A2:A21=I1)
Vamos a seleccionar la condición.
Pulsamos F9.
Vemos que aparecen valores falsos donde no hay coincidencia, y, verdaderos, donde la hay, en este caso, solo tenemos una coincidencia, que es la provincia buscada.
Pulsamos CTRL más Z para deshacer.
Lo siguiente que vamos a añadir, es lo que nos debe de devolver, que es la fila que ocupa la provincia buscada, para ello, seguimos en la celda H5, ponemos el símbolo de asterisco, abrimos un paréntesis, donde vamos a usar la función FILA para que nos devuelva la fila del rango A2:A21.
Aceptamos, y, ya tenemos la fila.
= SUMAPRODUCTO((A2:A21=I1)*(FILA(A2:A21)))
Nos devuelve 4, ya tenemos tanto la fila como la columna.
Tenemos una función llamada DIRECCION, la cual, si le damos una fila y una columna, nos la convierte en una referencia.
Pues vamos a serlo en la celda J5, donde como argumento fila es la celda H5, y, como argumento columna, es la celda I5.
=DIRECCION(H5;I5)
Tenemos otra función, que a menudo se usa con la función DIRECCION, que es la función INDIRECTO, que nos va a devolver el valor de una referencia, y, esa referencia va a ser la devuelta por la función DIRECCION.
=INDIRECTO(DIRECCION(H5;I5))
Aceptamos, y, ya tenemos nuestro valor.
Lo siguiente que vamos a hacer es extraer el valor máximo de ese año que hemos seleccionado.
Para ello, en la celda J10, vamos a usar la función FILTRAR, para filtrar los datos numéricos por el año seleccionado, como argumento array, seleccionamos el rango B2:F21, como argumento include, seleccionamos el rango B1:F1 e igualamos al valor de la celda K1, que es el año seleccionado.
=FILTRAR(B2:F21;B1:F1=K1)
Y con la función KESIMOMAYOR, vamos a extraer el primer valor mayor, como argumento matriz, es la función FILTRAR, como argumento K, ponemos 1, para que nos devuelva el primer valor mayor.
=K.ESIMO.MAYOR(FILTRAR(B2:F21;B1:F1=K1);1)
Pues ya lo tenemos también.
Ejemplo de la función INDIRECTO y HORA
El siguiente ejemplo consiste en que somos una persona que trabajamos en una empresa donde desde las ocho de la mañana hasta las dos de la tarde, estamos atendiendo un teléfono.
Queremos en columnas apartes que se vayan introduciendo las horas en su lugar.
Por último, queremos saber en qué hora hubo más llamadas.
En el siguiente modelo es donde introducimos las horas.
Como sabemos que el horario es de ocho a dos, en columnas aparte voy a poner dichas horas, donde se irán almacenado las diferentes horas.
Hay que decir que trabajamos con un modelo donde ya hay horas introducidas.
Pues vamos a buscar la primera hora que son las ocho.
Tengo que preguntar en la celda G2, que, si la hora en el rango desde A2:A100 donde en A2 se encuentra la primera hora de llamada, por lo que vamos a usar la función HORA para extraer la hora del rango A2:A100, e, igualamos a la hora ocho, pues, que devuelva las columnas A y B.
=SI(HORA(A2:A100)=G1;A2:B100;"")
Aceptamos, y, tenemos una matriz desbordada de dos columnas con las horas que pertenecen a las ocho, lo ponemos en formato de hora.
Con la función ORDENAR, ordenamos las horas.
=ORDENAR(SI(HORA(A2:A100)=G1;A2:B100;""))
En la matriz obtenida anteriormente, en las dos primeras filas, tenemos las horas, el resto de las celdas son blancos.
Y, si queremos que no aparezcan los blancos, vamos a quitar la función ORDERNAR.
=SI(HORA(A2:A100)=G1;A2:B100;"")
Vemos que tenemos el mismo número de celdas en blanco en las dos columnas, además, de encontrarse en la misma posición.
Con la función FILTRAR, no podemos filtrar por más de una columna, pues, vamos a filtrar por una columna, ya que ambas columnas tienen el mismo número de blancos, y, en la misma posición, para ello, ponemos la función FILTRAR, como argumento array, es el condicional SI, como argumento include, vamos a tomar la primera columna, para ello, usamos la función TOMAR, como argumento matriz, vuelve a ser el condicional SI, omitimos el argumento filas, como argumento columnas, ponemos 1 y comparamos con distinto a blanco.
=FILTRAR(SI(HORA(A2:A100)=G1;A2:B100;"");TOMAR(SI(HORA(A2:A100)=G1;A2:B100;"");;1)<>"")
Vemos que solo tenemos las filas que contienen datos.
Fijamos las siguientes referencias.
=FILTRAR(SI(HORA($A$2:$A$100)=G1;$A$2:$B$100;"");TOMAR(SI(HORA($A$2:$A$100)=G1;$A$2:$B$100;"");;1)<>"")
Seleccionamos la función, y pulsamos CTRL más C para copiar.
Seleccionamos las celdas donde vamos a copiar con CTRL (J2, M2, P2, S2, V2).
Hacemos clic con botón alternativo de ratón, y, seleccionamos pegar formulas.
Ya tenemos nuestras horas cada una en su sitio.
Si queremos arrastrar hacia la derecha, podemos quitar las columnas que separan cada hora.
Ahora en vertical voy a poner las horas, para calcular la suma de los minutos para cada hora, para quedarnos con las horas únicas, en la celda D2, usamos la función HORA, como argumento ponemos la columna entrada de tabla1, uso una columna, porque en la columna salida, aparecen las mismas horas que para la columna entrada.
=HORA(Tabla1[Entrada])
Lo ponemos en formato general.
Nos quedamos con los valores únicos.
=UNICOS(HORA(Tabla1[Entrada]))
Ordenamos.
=ORDENAR(UNICOS(HORA(Tabla1[Entrada])))
Ya tenemos las horas, vemos que ninguna hora llega a las 14:00.
Vamos a preguntar que si la hora con la función HORA en el rango A2:A100 es igual a la primera hora, pues que nos reste ambas columnas, en caso contrario, que ponga un texto en blanco.
=SI(HORA(A2:A100)=D2;B2:B100-A2:A100;"")
Sumamos los resultados.
=SUMA(SI(HORA(A2:A100)=D2;B2:B100-A2:A100;""))
Ya tenemos el total de minutos para la primera hora.
Fijamos los rangos de búsquedas, y, arrastramos.
=SUMA(SI(HORA($A$2:$A$100)=D2;$B$2:$B$100-$A$2:$A$100;""))
Ya tenemos los totales de minutos para cada hora.
Lo siguiente que vamos a hacer es aplicar una regla de formato condicional para que quede resaltada la fila de más minutos, así sabremos de un simple vistazo cual ha sido la hora de más conversación.
Para ello, seleccionamos ambas columnas, desplegamos formato condicional y elegimos nueva regla, en la ventana dar formato a los valores, ponemos la siguiente expresión, donde comparamos cada valor del rango E2:E7 con el valor máximo de dicho rango, además, fijamos la columna de E2, para que, en caso de haber coincidencia, quede resaltada la fila.
Damos un formato.
Aceptamos, y, ya tenemos resaltada la mayor hora.
Todo esto está bien, pero hemos puesto como última fila la fila 100, pero ¿y si tenemos más de 100 llamadas?
Veamos cómo hacerlo en una celda.
Si escribo la siguiente expresión:
=CONTARA(A:A)
Escribo la letra A entre comillas dobles antes de la función CONTARA, y, concateno con la función CONTARA, obtendré una referencia en formato de texto.
="a" & CONTARA(A:A)
También podría usar CONTARA junto con DIRECCION donde como argumento fila, es la función CONTARA, y, como argumento columna, ponemos 1, de la siguiente manera:
=DIRECCION(CONTARA(A:A);1)
Obtengo la referencia en absoluta, pero, vamos a usar la primera expresión.
Entonces, vamos a modificar la primera expresión, la que corresponde con la hora de las ocho en la celda G2.
Nosotros hemos pues desde A2 a A100, es este 100 es el que tenemos que hacer dinámico.
Para ello, realizo los siguientes cambios, sustituimos A2:A100 por INDIRECTO("A2:a"&CONTARA($A:$A))), y, A2:B100, por INDIRECTO("A2:b"&CONTARA($A:$A)).
La expresion queda:
=LET(a;SI(HORA(INDIRECTO("A2:a"&CONTARA($A:$A)))=G1;INDIRECTO("A2:b"&CONTARA($A:$A));"");FILTRAR(a;TOMAR(a;;1)<>""))
Aceptamos y tenemos los mismos resultados, pero sabiendo cual es la última fila ocupada.
Volvemos a copiar y pegar como formula.
Lo siguiente que queremos es saber cuál fue la llamada de más duración, la hora de inicio y de fin.
Lo primero que voy a hacer en la celda D11 es restar ambas columnas.
=Tabla1[Salida]-Tabla1[Entrada])
Voy a sacar el valor máximo de esta resta.
=MAX(Tabla1[Salida]-Tabla1[Entrada])
Voy a comparar el valor máximo con cada resta con el condicional SI.
=SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada]
Si hay coincidencia le voy a decir que me devuelva ambas columnas, en caso contrario, que ponga un texto en blanco.
=SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada];Tabla1;"")
Ordenamos.
=ORDENAR(SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada];Tabla1;""))
Ya tenemos la hora de inicio y de fin de la llamada de más duración.
Pero, seguimos teniendo blancos donde no hay coincidencia.
Vamos a dejar la siguiente expresión:
=SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada];Tabla1;"")
Igual que antes, vamos a filtrar, por lo que usamos la función FILTRAR, como argumento array, es la expresión anterior.
=FILTRAR(SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada];Tabla1;"")
Como argumento include, vamos a tomar la primera columna de la expresión anterior, para ello, usamos la función TOMAR, como argumento matriz, sigue siendo la expresión anterior, omitimos el argumento filas, como argumento columnas, ponemos 1 y comparamos con distinto a blanco.
=FILTRAR(SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada];Tabla1;"");TOMAR(SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada];Tabla1;"");;1)<>"")
Aceptamos, y, ya tenemos una única fila.
Por último, vamos a aplicar una regla de formato condicional para resaltar dentro del modelo dicha llamada, seleccionamos el rango A2:B12, desplegamos formato condicional, seleccionamos nueva regla, en la ventana dar formato a los valores…usamos el operador lógico Y, como argumento valor logico1, ponemos la celda A2, y, fijamos la columna, igualamos a la celda D12, y, fijamos, como argumento valor logico2, ponemos la celda B2 y, fijamos la columna, e, igualamos a la celda E12, y, fijamos.
Aceptamos, y, ya lo tenemos.
Reporte de comparación de años y trimestres
Para el siguiente ejemplo, seguimos trabajando con el modelo que suelo usar habitualmente.
El modelo está en formato de tabla, y, se llama ventas.
El ejercicio va a consistir en crear un pronóstico, vamos a elegir un año y un trimestre, queremos ver las ventas del año y trimestre seleccionado, y, del año anterior, pero, si no seleccionamos ningún trimestre, debemos de ver las ventas del año seleccionado, y, año anterior.
Empecemos…
Primero, creamos una lista desplegable con los años únicos, para ello, en la celda O2, usamos UNICOS, como argumento usamos AÑO, y, como argumento ponemos la columna fecha, nos vamos a traer los años únicos de la columna fecha.
=UNICOS(AÑO(Ventas[Fecha]))
En la celda I2, creamos una lista desplegable para los años, para ello, vamos a la pestaña datos, dentro de herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, y, en la ventana origen, ponemos la celda O2, seguido del operador de rango derramado.
Aceptamos.
Seleccionamos un año.
Ahora, vamos a crear otra lista desplegable, en este caso, con los trimestres, volvemos a validación de datos, seleccionamos lista, y, en la ventana origen, escribimos los valores.
Aceptamos.
Seleccionamos un trimestre.
Quiere decir que debemos de calcular el total de ventas para el trimestre 2 del año 2018, y, compararlo con el trimestre 2 para el año 2017.
Con la siguiente expresión, en la celda I4, conseguimos un texto con lo que debemos de comparar.
=”Comparar el año “ & I2 & “ y “ & J2 & “ con el año “ & I2-1 & “ y “ & J2
Pero, que ocurre si no seleccionamos un trimestre, pues, que aparece la letra “y”, que, para este caso, está de más.
Para solventarlo, vamos a usar el condicional SI, donde vamos a preguntar que, si el valor de la celda J2 es diferente a blanco, que ponga la expresión anterior, en caso contrario, que ponga la misma expresión, pero sin la letra “y”, ni la palabra “trimestre”.
=SI(J2<>"";"Comparar el año " & I2 & " y " & J2 & " con el año " & I2-1 & " y " & J2;"Comparar el año " & I2 & " con el año " & I2-1 )
Si seleccionamos, o, no un trimestre, ahora el texto es correcto.
En la celda I5, vamos a poner otro texto descriptivo, pero, si solo seleccionamos un año, debe de aparecer “Nº de ventas para el año X”, pero, si seleccionamos un año, y, un trimestre, el texto debe de cambiar a “Nº de ventas para el año X y trimestre Y”, además, debe de quedar alineado a la izquierda, y, ser rellenado por puntos por la derecha hasta llegar al primer carácter del rotulo, para ello, vamos a usar le condicional SI, donde preguntamos que, si la celda J2 es igual a blanco, debe de poner "Nº de ventas para el año " & I2, es decir, concatenamos la frase junto con el año.
=SI(J2="";"Nº de ventas para el año " & I2
En caso contrario, debe de poner la siguiente expresión, donde ponemos el año y trimestre.
SI(J2="";"Nº de ventas para el año " & I2;"Nº de ventas para el año " & I2 & " y " & J2)
Copiamos la expresión excepto el signo igual, la pegamos en la celda I6, y, restamos 1 al año.
=SI(J2="";"Nº de ventas para el año "&I2-1;"Nº de ventas para el año "&I2-1&" y "&J2)
Aceptamos, y, ya tenemos el texto correcto, seleccionemos un trimestre, o no lo hagamos.
Antes de seguir, debemos de colocar los meses a que corresponde el trimestre seleccionado, por ejemplo, si seleccionamos el trimestre1, en una matriz desbordada en horizontal, deben de aparecer los números 1, 2, y 3.
Para ello, en la celda K1, vamos a usar le condicional SI, donde como argumento prueba lógica, preguntamos que, si el valor de J2 es igual a trimestre1, como argumento valor si verdadero, usamos APILARH, donde como argumento matriz1 es el número 1, como argumento matriz2, es el número 2, y, como argumento matriz3, es el número 3.
=SI(J2="Trimestre1";APILARH(1;2;3)
Punto y coma, como argumento valor si falso de este primer condicional, vamos a poner otro condicional SI, pero este caso, preguntamos que, si es igual a trimestre2, que ponga los números 4, 5, y, 6.
SI(J2="Trimestre1";APILARH(1;2;3);SI(J2="Trimestre2";APILARH(4;5;6)
Repetimos los pasos para trimestre3, y, trimestre4.
SI(J2="Trimestre1";APILARH(1;2;3);SI(J2="Trimestre2";APILARH(4;5;6);SI(J2="Trimestre3";APILARH(7;8;9);SI(J2="Trimestre4";APILARH(10;11;12)))))
Si seleccionamos un trimestre, veremos a los meses que corresponde.
Ahora, vamos a calcular el número de ventas (recuento) para cada año, o, para cada año y trimestre.
En la celda K5, vamos a abrir unos paréntesis, donde vamos a poner una condición, que es si el año de la columna fecha es igual al año seleccionado.
=(AÑO(Ventas[Fecha])=I2)
Obtenemos una matriz desbordada en vertical con FALSO donde no hay coincidencia, y, VERDADERO donde hay coincidencia.
Volvemos a la expresión, después del cierre de paréntesis, ponemos el símbolo de asterisco, abrimos otro paréntesis, para poner una segunda condición, esta condición es si el mes de la columna fecha es igual a la matriz desbordada con los meses.
=(AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)
Aceptamos, y, tenemos una matriz desbordada de tres columnas con 1 donde hay coincidencia, y, 0 donde no la hay, cada columna pertenece a un mes.
Sumamos los resultados.
=SUMA((AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#))
Aceptamos y tenemos un numero de 64 ventas para el año seleccionado y trimestre 1, que corresponde con el primer año seleccionado.
Copiamos la función, excepto el signo igual, y la pegamos en la celda K6, pero a la celda I2 le restamos 1, para obtener el año anterior.
=SUMA(ENCOL((AÑO(Ventas[Fecha])=I2-1)*(MES(Ventas[Fecha])=K2#)))
Ya tenemos el número de ventas para cada año.
Y si solo seleccionamos un año, pues, nos devuelve 0.
Para solventarlo, preguntamos con el condicional SI, que, si el valor de la celda J2 es diferente a blanco, quiere decir que hay un trimestre seleccionado, en ese caso, que ejecute la expresión anterior.
=SI(J2<>"";SUMA(ENCOL((AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)))
En caso contrario, tiene que sumar las fechas que correspondan al año seleccionado.
=SI(J2<>"";SUMA(ENCOL((AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)));SUMA((AÑO(Ventas[Fecha])=I2)*1))
¿Por qué ponemos AÑO(Ventas[Fecha])=I2 y lo multiplicamos por 1?
La siguiente expresión, AÑO(Ventas[Fecha])=I2, devuelve VERDADERO donde hay coincidencia, y, FALSO donde no la hay, valores que no podemos sumar, al multiplicar por 1, VERDADERO se convierte en 1, y, FALSO se convierte en 0, valores que ya si podemos sumar.
Aceptamos, y, tenemos el recuento en este caso solo para el año.
Copiamos la expresión, excepto el signo igual, la pegamos en K6, e, igual que antes restamos 1 a la celda I2.
=SI(J2<>"";SUMA(ENCOL((AÑO(Ventas[Fecha])=I2-1)*(MES(Ventas[Fecha])=K2#)));SUMA((AÑO(Ventas[Fecha])=I2-1)*1))
Y ya lo tenemos.
Vamos a alinear a la izquierda los títulos de las celdas I4, I5, e, I6.
Queremos rellenar con puntos, el espacio que queda desde la izquierda hasta el comienzo de la primera letra, es decir, la letra N, creo que quedara más profesional.
Para ello, debemos de trabajar con la longitud del encabezado más largo.
El problema es que, si seleccionamos solo un año, dicho encabezado cambiará, y, dicha longitud no es la que necesitamos., por lo que en una celda he escrito el texto de encabezado más largo, y, con LARGO he obtenido su longitud.
Ahora debo de sumar esta longitud con la longitud de la siguiente cadena.
Entonces, obtendré los puntos que debo de colocar.
La siguiente expresión la vamos a almacenar en una variable con LET.
SI(J2="";"Nº de ventas para el año " & I2;"Nº de ventas para el año " & I2 & " y " & J2)
=LET(a;SI(J2="";"Nº de ventas para el año "&I2;"Nº de ventas para el año "&I2&" y "&J2)
Punto y coma, como argumento cálculo de LET, usamos la función REPETIR, como argumento texto, entre comillas dobles, ponemos “.”, y, como argumento número de veces, es la suma de la longitud de la celda I4 más la longitud de la celda Q2 y concatenamos con la variable “a”.
=LET(a;SI(J2="";"Nº de ventas para el año "&I2;"Nº de ventas para el año "&I2&" y "&J2);REPETIR(".";LARGO(I4)+Q2)&a)
Cerramos paréntesis, y, aceptamos.
Y podemos ver como los puntos se colocan de forma automática, sea el texto que sea.
Ahora, vamos a calcular los totales en la celda I9.
En este caso, calculamos para el año 2018, y, trimestre 2.
Vamos a abrir un paréntesis, donde vamos a colocar una primera condición, la cual es si el año de la columna fecha es igual a la celda I2, que es el año seleccionado.
=(AÑO(Ventas[Fecha])=I2)
Aceptamos, y, tenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.
Ponemos el símbolo de asterisco, abrimos otro paréntesis, para una segunda condición, la cual, si el mes de la columna fecha es igual a la matriz desbordada K2#.
=(AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)
Obtenemos una matriz desbordada de tres columnas, cada columna pertenece a un mes, con 1 donde hay coincidencia, y, 0 donde no la hay.
Ponemos, de nuevo, el símbolo de asterisco, abrimos unos paréntesis, e, indicamos que nos devuelva la columna de total.
(AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)*(Ventas[Total])
Ahora, sumamos los resultados.
=SUMA((AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)*(Ventas[Total]))
Tenemos el total para el año 2018, y, trimestre 2.
Borremos el trimestre, y, vemos que nos devuelve un error, o, nos devuelve 0, porque, de momento no se contempla que haya un solo año.
Para solventarlo, vamos a usar LET, creamos una variable, y, almacenamos la expresión anterior.
=LET(a;SUMA((AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)*(Ventas[Total]));
Como argumento cálculo de LET, vamos a preguntar que, si la celda J2 es igual a blanco, que nos sume la columna de total, solo si el año es igual al seleccionado.
=LET(a;SUMA((AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)*(Ventas[Total]));SI(J2="";SUMA((AÑO(Ventas[Fecha])=I2)*(Ventas[Total]))
En caso contrario, que devuelva la variable A.
=LET(a;SUMA((AÑO(Ventas[Fecha])=I2)*(MES(Ventas[Fecha])=K2#)*(Ventas[Total]));SI(J2="";SUMA((AÑO(Ventas[Fecha])=I2)*(Ventas[Total]));a))
Cerramos paréntesis, y, aceptamos, y, ya tenemos el problema resuelto.
Para el año anterior, copiamos y pegamos la expresión, y, restamos uno a la celda I2.
=LET(a;SUMA((AÑO(Ventas[Fecha])=I2-1)*(MES(Ventas[Fecha])=K2#)*(Ventas[Total]));SI(J2="";SUMA((AÑO(Ventas[Fecha])=I2-1)*(Ventas[Total]));a))
Lo siguiente es el pronóstico, para ello, pero, vamos a hacerlo que se vea de forma clara, entonces, voy a usar la función BUSCARX en la celda I15, para buscar el valor máximo de los dos totales que tenemos, que es el argumento valor buscado.
=BUSCARX(MAX(I9;I12)
Punto y coma, como argumento matriz de búsqueda, usamos APILARV, para apilar en vertical ambas cantidades.
=BUSCARX(MAX(I9;I12);APILARV(I9;I12)
Punto y coma, como argumento matriz devuelta, apilamos de nuevo en vertical ambos rótulos.
Cerramos paréntesis.
=BUSCARX(MAX(I9;I12);APILARV(I9;I12);APILARV(I8;I11))
Aceptamos, y, podemos ver el rotulo de más ventas.
Concatenamos con el texto “mejores ventas”.
=BUSCARX(MAX(I9;I12);APILARV(I9;I12);APILARV(I8;I11))&" mejores ventas"
Concatenamos con la palabra “por”.
=BUSCARX(MAX(I9;I12);APILARV(I9;I12);APILARV(I8;I11))&" mejores ventas"&" por “
Concatenamos con la resta de I9 menos I12, pero ponemos la función ABS, por si alguna vez la cantidad es negativa, se quede con el valor absoluto.
=BUSCARX(MAX(I9;I12);APILARV(I9;I12);APILARV(I8;I11))&" mejores ventas"&" por "&ABS(I9-I12)
Aceptamos, y, lo tenemos, pero vemos que el numero ha perdido el formato, para poner formato de moneda, usamos TEXTO, donde como argumento valor es la resta, punto y coma, como argumento formato, entre comillas dobles, ponemos “0,00€”.
Cerramos paréntesis, aceptamos, y, ya lo tenemos.
=BUSCARX(MAX(I9;I12);APILARV(I9;I12);APILARV(I8;I11))&" mejores ventas"&" por "&TEXTO(ABS(I9-I12);"0.00 €")
Lo siguiente es saber la fecha de más ventas para el año seleccionado, es decir, para todo el año, y, todos los trimestres.
Primero, vamos a traernos del año seleccionada la máxima venta, para ello, filtramos la tabla ventas, para ello, en la celda I20, ponemos la función FILTRAR, como argumento array, ponemos Ventas.
=FILTRAR(Ventas
Punto y coma, como argumento include, usamos la función AÑO, como argumento seleccionamos la columna de fecha, e, igualamos al valor de I2.
=FILTRAR(Ventas;AÑO(Ventas[Fecha])=I2)
Obtenemos una matriz desbordada con los datos para el año seleccionado.
Ponemos LET después del signo igual, creamos una variable, y, almacenaos la expresión anterior.
Cerramos paréntesis.
=LET(a;FILTRAR(Ventas;AÑO(Ventas[Fecha])=I2)
Punto y coma, como argumento cálculo de LET, filtramos la variable A, siempre que la columna 7 (total) de la variable A sea igual al valor máximo de la variable A.
=LET(a;FILTRAR(Ventas;AÑO(Ventas[Fecha])=I2);FILTRAR(a;INDICE(a;;7)=MAX(INDICE(a;;7))))
Ya tenemos el registro de más venta para el año seleccionado.
Lo siguiente es realizar un reporte, con las ventas realizadas el mismo día y mes para el resto de los años, lo haremos en la celda I21.
Para ello, volvemos a filtrar la tabla ventas.
=FILTRAR(Ventas
Punto y coma, como argumento include, abrimos un paréntesis, y, ponemos la primera condición, que el día de la columna fecha debe de ser igual al día de la fecha obtenida.
=FILTRAR(Ventas;(DIA(Ventas[Fecha])=DIA(I20))
Ponemos el símbolo de asterisco, abrimos unos paréntesis, y, ponemos la segunda condición, que es mes de la columna fecha debe de ser igual al mes de la fecha obtenida.
=FILTRAR(Ventas;(DIA(Ventas[Fecha])=DIA(I20))*(MES(Ventas[Fecha])=MES(I20))
Ponemos otro asterisco, y, abrimos otros paréntesis, en este caso, la condición es que el año de la columna fecha, debe de ser diferente al año de la fecha obtenida.
=FILTRAR(Ventas;(DIA(Ventas[Fecha])=DIA(I20))*(MES(Ventas[Fecha])=MES(I20))*(AÑO(Ventas[Fecha])<>AÑO(I20)))
Aceptamos, y, vemos las ventas realizadas para el mismo día y año.
Miguel Angel Franco
Comentários