Tengo datos que necesito analizar desesperadamente y no sé cómo proceder. Los datos están en tres columnas; el primero es la fecha. Las fechas no están en orden (por varias razones) y los datos nuevos simplemente se agregan al final de la lista. También tengo el precio de venta de un producto en la segunda columna y el tipo de producto en la tercera. Por ejemplo:
Los cálculos deben basarse en los siete precios más recientes de cualquier tipo. El tercer precio más bajo y el segundo precio más alto van en la tabla que comienza con las celdas “A” ( F12) y “B” ( G12), respectivamente.
La tercera fórmula debería eliminar (ignorar) el máximo y el mínimo de los siete precios más recientes y calcular el promedio de los cinco precios restantes. Esto va en la última columna de la tabla, comenzando con la celda “C” ( H12).
Entonces necesito un gráfico combinado de barras/líneas con "tipo" en el eje horizontal y "precio" en el vertical. Necesito un gráfico de barras apiladas que muestre el rango inferior (Celda "A") y el rango superior (Celda "B"), y se superpondrá un gráfico de líneas con los promedios (Celda "C").
No soy un mago de Excel y he probado todos los tutoriales de autoayuda que he podido para solucionar este problema. Llegué a pensar que VLOOKUPpodría ser útil, pero ahora estoy perdido.
Respuesta1
Bien, la restricción contra múltiples ventas del mismo tipo en un día determinado que mencionó Scott ha hecho posible esto. Pero todavía es bastante complicado. Usar VBA podría ser mucho más sencillo y tal vez alguien aquí publique un procedimiento.
Voy a comenzar usando una tabla "auxiliar" porque es útil para ver los datos intermedios y verificar que todo se calcule correctamente, pero también mostraré fórmulas que no usan la tabla auxiliar. La tabla auxiliar se muestra a continuación junto con los resultados.
He utilizado precios y fechas aleatorios, pero respeté la restricción anterior sobre ventas múltiples de un tipo determinado por fecha. Ahora desarrollemos la fórmula que va en F2.
Primero, necesitamos una serie de los siete precios más recientes para cada tipo. Una vez que esté disponible, es fácil calcular las tres métricas en la tabla de resultados a partir de esa matriz.
Empezamos con esta expresión: ($C$2:$C$55=ROW()-1). Esto es parte de la fórmula en F2, por lo que ROW()-1es igual a 1, y esta expresión proporciona una matriz de valores Verdadero/Falso, con Verdadero donde Tipo es igual a 1 y Falso en el resto. A medida que se completa, ROW()aumenta, por lo que en la siguiente fila, proporciona la matriz con Verdadero donde Tipo es igual a 2, etc.
Ahora multiplicamos este array por la columna de fechas: ($A$2:$A$55)*($C$2:$C$55=ROW()-1). Esto proporciona una matriz que contiene la fecha donde Tipo es igual a 1 y Falso en el resto.
Ahora queremos las 7 fechas más recientes y las obtenemos usando la LARGE()función. Debido a la restricción anterior (solo hay una venta de Tipo 1 en cada fecha), esto proporciona las fechas de las 7 ventas más recientes de Tipo 1:
LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})
La serie de números entre llaves indica LARGE()que se devuelvan los valores más grandes del 1.º al 7.º.
Ahora usamos an IF()para obtener los precios correspondientes a esas fechas:
IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55)
La primera parte de la IF()prueba lógica devuelve una matriz donde la fecha en la columna A es una de las 7 fechas anteriores, y esta matriz se multiplica por la matriz donde tipo = 1 nuevamente, porque se vendieron otros tipos en esas 7 fechas. Entonces, IF()prueba si la fecha en la columna A es igual a una de las 7 fechas más recientesysi la venta particular en esa fecha fue para Tipo = 1. Si ambas condiciones son verdaderas, IF()devuelve el precio de venta de la Columna B, y Falso en caso contrario.
Ahora, para enumerar los precios en la tabla auxiliar de mayor a menor, usamos LARGE():
(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5)
Esta fórmula está en la columna F, por lo que COLUMN()-5es igual a 1 y la fórmula devuelve el primer valor más grande de la matriz en esa columna. A medida que se completa, COLUMN()aumenta, por lo que en la siguiente columna se obtiene el segundo valor más grande, etc.
Finalmente, la fórmula está envuelta IFERROR()de modo que devolverá un espacio en blanco donde haya menos de 7 ventas de un tipo determinado. La fórmula final:
=IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5),"")
se ingresa en F2. Debido a que es una fórmula matricial, se debe ingresar con CTRLShiftEnter, en lugar de solo Enter. Si se ingresa correctamente, Excel rodeará la fórmula con llaves {} en la barra de fórmulas. Una vez ingresado, seleccione F2 y complete la fórmula hacia abajo y luego hacia arriba para obtener la tabla auxiliar de arriba.
Ahora es fácil llenar la tabla de resultados. Estas dos fórmulas
=SMALL(F2:L2,3) and =LARGE(F2:L2,2)
calcule el tercer valor más pequeño y el segundo más grande en la primera fila de la tabla auxiliar. Y esta fórmula
=AVERAGE(IFERROR(LARGE(F2:L2,{2,3,4,5,6}),""))
calcula el promedio de los 5 valores centrales. También es una fórmula matricial, por lo que debe ingresarse como se indica arriba. Al completar estas fórmulas se obtiene la tabla de resultados.
Para completar la tabla de resultados sin usar una tabla auxiliar, use estas tres fórmulas de matriz en F, G y H12 y complete:
=SMALL(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),3)
=LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),2)
=AVERAGE(IFERROR(LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),{2,3,4,5,6}),""))
Espero que esto te ayude y estoy seguro de que podrás producir el gráfico que deseas una vez que tengas estos datos.
Cualquier comentario o sugerencia es bienvenido.