Tenho dados que preciso desesperadamente analisar e não sei como proceder. Os dados estão em três colunas; o primeiro é a data. As datas não estão em ordem (por vários motivos) e novos dados são adicionados ao final da lista. Também tenho o preço de venda de um produto na segunda coluna e o tipo de produto na terceira. Por exemplo:
Os cálculos devem basear-se nos sete preços mais recentes de qualquer tipo. O 3º preço mais baixo e o 2º preço mais alto vão para a tabela começando pelas células “A” ( F12) e “B” ( G12), respectivamente.
A terceira fórmula deve eliminar (ignorar) o maior e o menor dos sete preços mais recentes e calcular a média dos cinco preços restantes. Isso vai para a última coluna da tabela, começando pela célula “C” ( H12).
Então preciso de um gráfico combinado de barras/linhas com "tipo" no eixo horizontal e "preço" na vertical. Preciso de um gráfico de barras empilhadas mostrando o intervalo inferior (Célula “A”) e o intervalo superior (Célula “B”), e um gráfico de linhas deve ser sobreposto com as médias (Célula “C”).
Não sou um assistente do Excel e tentei todos os tutoriais de autoajuda que pude para resolver isso. Cheguei ao ponto de pensar que isso VLOOKUPpode ser útil, mas agora estou perdido.
Responder1
OK, a restrição contra vendas múltiplas do mesmo tipo em um determinado dia mencionada por Scott tornou isso possível. Mas ainda está bastante envolvido. Usar VBA pode ser muito mais simples, e talvez alguém aqui poste um procedimento.
Vou começar usando uma tabela “auxiliar” porque é útil ver os dados intermediários e verificar se as coisas estão calculadas corretamente, mas também mostrarei fórmulas que não usam a tabela auxiliar. A tabela auxiliar é mostrada abaixo junto com os resultados.
Usei preços e datas aleatórios, mas honrei a restrição acima sobre vendas múltiplas de um determinado tipo por data. Agora vamos construir a fórmula que entra em F2.
Primeiro, precisamos de uma matriz dos sete preços mais recentes para cada tipo. Quando isso estiver disponível, será fácil calcular as três métricas na tabela de resultados dessa matriz.
Começamos com esta expressão: ($C$2:$C$55=ROW()-1). Isso faz parte da fórmula em F2, portanto ROW()-1é igual a 1, e esta expressão fornece uma matriz de valores Verdadeiro/Falso, com Verdadeiro sempre que Tipo for igual a 1 e Falso em todos os outros lugares. À medida que é preenchido, ROW()aumenta, na próxima linha, ele fornece o array com True onde Type é igual a 2, etc.
Agora multiplicamos esse array pela coluna de datas: ($A$2:$A$55)*($C$2:$C$55=ROW()-1). Isso fornece uma matriz contendo a data em que Type é igual a 1 e False em todos os outros lugares.
Agora queremos as 7 datas mais recentes e as obtemos usando a LARGE()função. Devido à restrição acima (há apenas uma venda do Tipo 1 em cada data), isso fornece as datas das 7 vendas mais recentes do Tipo 1:
LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})
A série de números entre colchetes indica LARGE()para retornar do 1º ao 7º maior valor.
Agora usamos an IF()para obter os preços correspondentes a essas datas:
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)
A primeira parte do IF()teste lógico retorna um array onde a data na coluna A é uma das 7 datas acima, e esse array é multiplicado pelo array onde type = 1 novamente, porque outros tipos foram vendidos nessas 7 datas. Portanto, IF()testa se a data na coluna A é igual a uma da matriz de 7 datas mais recentesese a venda específica naquela data foi para Tipo = 1. Se ambas as condições forem verdadeiras, IF()retorna o preço de venda da Coluna B e, caso contrário, Falso.
Agora, para listar os preços na tabela auxiliar de cima para baixo, 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á na coluna F, portanto COLUMN()-5é igual a 1, e a fórmula retorna o primeiro maior valor da matriz nessa coluna. À medida que é preenchido, COLUMN()aumenta, na próxima coluna fornece o segundo maior valor, etc.
Por fim, a fórmula é agrupada IFERROR()para retornar um espaço em branco onde houver menos de 7 vendas de um determinado tipo. A 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),"")
é inserido em F2. Por ser uma fórmula de matriz, ela deve ser inserida com CTRLShiftEnter, em vez de apenas Enter. Se inserido corretamente, o Excel colocará a fórmula entre colchetes {} na barra de fórmulas. Uma vez inserido, selecione F2 e preencha a fórmula para baixo e depois para obter a tabela auxiliar acima.
Agora é fácil preencher a tabela de resultados. Estas duas fórmulas
=SMALL(F2:L2,3) and =LARGE(F2:L2,2)
calcule o terceiro menor e o segundo maior valor na primeira linha da tabela auxiliar. E esta fórmula
=AVERAGE(IFERROR(LARGE(F2:L2,{2,3,4,5,6}),""))
calcula a média dos 5 valores intermediários. Também é uma fórmula de matriz, portanto deve ser inserida conforme acima. O preenchimento dessas fórmulas fornece a tabela de resultados.
Para preencher a tabela de resultados sem usar uma tabela auxiliar, use estas três fórmulas de matriz em F, G e H12 e preencha:
=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 isso ajude você e tenho certeza de que você poderá produzir o gráfico desejado assim que tiver esses dados.
Quaisquer comentários ou sugestões são bem-vindos.