Selecione a última entrada por data e digite em Excel

Question

Eu 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árias razões) e novos dados são adicionados à parte inferior da lista. Eu também tenho o preço de venda de um produto na segunda coluna e o tipo de produto no terceiro. Por exemplo:

Oscálculosdevembasear-senossetepreçosmaisrecentesdequalquertipo.Oterceiromenorpreçoeosegundomaiorpreçovãoparaatabela,começandocomascélulas“A”(F12)e“B”(G12),respectivamente.

Aterceirafórmuladeveeliminar(ignorar)omaioreomenordossetepreçosmaisrecentesecalcularamédiadoscincopreçosrestantes.Issovainaúltimacolunadatabela,começandocomCell"C" ( H12 ).

Então eu preciso de um gráfico de barras / linhas combinado com "tipo" no eixo horizontal e "preço" na vertical. Eu 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").

Eu não sou um assistente do Excel e já experimentei todos os tutoriais de auto-ajuda para resolver isso. Cheguei a pensar que VLOOKUP pode ser útil, mas agora estou com prejuízo.

    

Answer 1

OK, a restrição contra várias vendas do mesmo tipo em um determinado dia que Scott criou tornou isso possível. Mas ainda está bastante envolvido. Usar o VBA pode ser muito mais simples e talvez alguém aqui poste um procedimento.

Começarei usando uma tabela de "ajuda" 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 de ajuda. A tabela auxiliar é mostrada abaixo junto com os resultados.

Euuseipreçosedatasaleatórios,masrespeiteiarestriçãoacimasobreváriasvendasdeumdeterminadotipopordata.AgoravamosconstruirafórmulaquevememF2.

Primeiro,precisamosdeumamatrizdossetepreçosmaisrecentesparacadatipo.Assimqueestiverdisponível,éfácilcalcularastrêsmétricasnatabeladeresultadosdessamatriz.

Começamoscomestaexpressão:($C$2:$C$55=ROW()-1).IssofazpartedafórmulaemF2,portanto,ROW()-1éiguala1eessaexpressãoforneceumamatrizdevaloresTrue/False,comTrue,ondeTypeéiguala1eFalseemtodososdemais.Àmedidaqueépreenchido,ROW()éincrementado,portanto,napróximalinha,eleforneceamatrizcomTrue,ondeTypeéiguala2,etc.

Agoramultiplicamosessearraypelacolunadedatas:($A$2:$A$55)*($C$2:$C$55=ROW()-1).IssoforneceumamatrizcontendoadataemqueTypeéiguala1eFalseemqualqueroutrolugar.

Agora,queremosas7maisrecentesdessasdataseasobtemosusandoafunçãoLARGE().Devidoàrestriçãoacima(háapenasumavendadoTipo1emcadadata),issoindicaasdatasdas7vendasmaisrecentesdoTipo1:

LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})

AsériedenúmerosentrechavesinformaLARGE()pararetornarosvalores1a7maiores.

AgorausamosumIF()paraobterospreçoscorrespondentesaessasdatas: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)

AprimeirapartedotestelógicodeIF()retornaumamatrizondeadatanaColunaAéumadas7datasacima,eessamatrizémultiplicadapelamatrizondetype=1novamente,porqueoutrostiposforamvendidonessas7datas.Portanto,oIF()testaseadatanaColunaAéigualaumadassetedatasmaisrecenteseseavendaespecíficanessadatafoiparaoTipo=1.Seambasascondiçõesforemverdadeiras,oIF()retornaopreçodevendadacolunaBe,deoutraforma,False.

Agora,paralistarospreçosnatabelaauxiliardealtoabaixo,usamosLARGE():(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)

EstafórmulaestánaColunaF,entãoCOLUMN()-5éiguala1eafórmularetornao1ºmaiorvalordamatriznessacoluna.Àmedidaqueépreenchido,COLUMN()éincrementado,portanto,napróximacoluna,eleforneceosegundomaiorvalor,etc.

Porfim,afórmulaéagrupadaemIFERROR(),demodoqueelaretornaráumespaçoembrancoquandohouvermenosde7vendasdeumdeterminadotipo.Afórmulafinal:

=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. Como é uma fórmula de matriz, ela deve ser digitada com CTRL Deslocar Enter , em vez de apenas Enter . Se inserido corretamente, o Excel colocará a fórmula entre chaves {} na barra de fórmulas. Uma vez inscrito, selecione F2 e preencha a fórmula e, em seguida, para dar a tabela auxiliar acima.

Agora é fácil preencher a tabela de resultados. Essas 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, ela deve ser inserida como acima. O preenchimento dessas fórmulas fornece a tabela de resultados.

Para preencher a tabela de resultados sem usar uma tabela auxiliar, use essas 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ê pode produzir o gráfico desejado quando tiver esses dados.

Quaisquer comentários ou sugestões são bem-vindos.