Furmulae de matriz multi-célula, aninhada

Question

Meus dados estão nas primeiras 2 tabelas ( A1:B6 e D1:F6 ):

MeuresultadofinalpretendidoestáemI1:I4paracadavalorcorrespondenteemH1:H4.

Porexemplo,vocêpegaovalorde"A", você encontra cada código correspondente de B1:B6 (ou seja, "code1", "code2" e "code3"), então você procura a data mais recente em E1:E5 (correspondendo aos códigos da etapa anterior) e forneça o resultado correspondente à data do valor em D1:D5 .

Estou preso na etapa de encontrar a data mais recente. Eu tentei estes:

{=IF($F$1:$F$5=IF($A$1:$A$6=$H$1,$B$1:$B$6),$E$1:$E$5)}
{=IF($F$1:$F$5={IF($A$1:$A$6=$H$1,$B$1:$B$6)},$E$1:$E$5)}

Alguma orientação por favor? Eu poderia adicionar colunas adicionais, mas o problema são os resultados de várias matrizes, que, até onde eu sei, não podem ser armazenados em uma célula.

    

Answer 1

Eu não sou especialista em Excel! No entanto eu percebo que a fórmula funciona bem envolto em MAX quando uma matriz absoluta é passada para ele, em vez de Array retornado de IF. Eu acho que isso é porque os comprimentos resultantes de duas matrizes não correspondem retornando # N / A para o excesso e que puxa a fórmula inteira para # N / A. Veja esta imagem abaixo.

IssomelevaacriarumaUDFmuitobásicanoVBAqueretornaapenasoArraynecessário.NãoexistemmuitasvalidaçõesnesteUDF.Certifique-sedequeapenasumareferênciadecolunasejapassadaparaelaeosvaloresderetornoesperadosexistamnacolunaadjacenteàdireita.porexemplo.sevocêpassarA1:A4,eleverificaráosvaloresemB1:B4.

PressioneALT+F11paraacessaroEditorVBA,Inserir->Móduloecoleoseguintecódigonele.

PublicFunctionRetArray(r1AsRange,aAsString)AsVariantDimii=0Dimmyarray()ForEachcellInr1Ifcell.Value=aTheni=i+1EndIfNextcellReDimmyarray(i)Dimjj=0ForEachcellInr1Ifcell.Value=aThenmyarray(j)=cell.Offset(0,1).Valuej=j+1EndIfNextcellRetArray=myarrayEndFunction

UsaremosesseUDFnasoluçãofinal.porexemplo.paraobterumamatrizdacolunaBondeAéH1useestafórmulacomo=RetArray(A1:A6,H1)

ApróximapartecomplicadaémapearessevalorretornadoparaolocalexatonacolunaEeobterovalordeD.

Primeiro,crieumacolunaauxiliaremC,queéaconcatenaçãodeD&E

AfórmulaemC1é=E1&F1eéarrastadaatéascélulaspretendidasabaixo.Certifique-sedequesuadataestejaemumformatodedataválidonoseuExcelenãocomoumtexto;casocontrário,estasoluçãonãofuncionará.

SuatabelaestáemH1:H4.

Agora,emI1,coloqueaseguintefórmula.

=INDEX($D$1:$D$9,MIN(IF($C$1:$C$9=MAX(IF($F$1:$F$9=RetArray($A$1:$A$8,H1),$E$1:$E$9,0))&RetArray($A$1:$A$8,H1),ROW($C$1:$C$9),99^99)))

PressioneCTRL+SHIFT+ENTERparacriareArrayFormulaearraste-oparabaixoaolongodocomprimentodatabela.

Estasoluçãonãoétotalmentetestada.ElesófuncionarábemsevocêiniciarseusdadosnaLinha1,senãoareferênciairádarerrado,amenosquevocêmanipuleosmesmosusandoareferênciadelinhaanterior.Experimenteevolteatrás.Tambémverifiquesehásoluçõesmaisinteligentesdeoutrosusuáriostambém.

Atualizar

EuperceboqueenquantoosArraysConstantesfuncionavameoCellReferencenão(oquemelevouacriarumUDFretornandoumArray)usandoafunçãoTRANSPOSE,naverdade,parececriarumtipodeArrayConstantedentro.EntãoapenassubstituaUDFporTRANSPOSEenvoltoemIFeasoluçãofuncionasemnenhumcódigoVBA&qualquercolunaauxiliar.

Tenteissoeverifiqueseissocombinacomvocê.

AfórmuladamatrizemI1eabaixoé

=INDEX($D$1:$D$9,MIN(IF($E$1:$E$9&$F$1:$F$9=MAX(IF($F$1:$F$9=TRANSPOSE(IF($A$1:$A$8=H1,$B$1:$B$8)),$E$1:$E$9,0))&TRANSPOSE(IF($A$1:$A$8=H1,$B$1:$B$8)),ROW($C$1:$C$9),99^99)))

    

Answer 2

É assim que o resultado parece, depois de um longo dia de reflexão:

4colunasauxiliaresforamcriadascomfórmulasdematrizdecélulaúnicacada.

CélulaC1parabaixo:

{=MAX(IF(B1=$K$1:$K$5,$J$1:$J$5))}

CélulaD1parabaixo:

{=IFERROR(INDEX($I$1:$I$5,MATCH(1,(B1=$K$1:$K$5)*(C1=$J$1:$J$5),0)),"")}

Célula E1 para baixo:

{=MAX(IF(A1=$A$1:$A$6,$C$1:$C$6))}

Célula F1 para baixo:

{=INDEX($B$1:$B$6,MATCH(1,(A1=$A$1:$A$6)*($C$1:$C$6=E1),0))}

E a resposta, em G1 abaixo:

{=INDEX($D$1:$D$6,MATCH(1,($B$1:$B$6=F2)*($C$1:$C$6=E2),0))}

Seria bom ter isso em uma coluna, mas tudo bem ...:)

    

Answer 3

Amigos agora é minha vez .

Basicamente, a Consulta é encontrar a Data Mais Recente (Recente) para o Código 1, Código 2 & Code3, se tiver um valor de correspondência correspondente é A na ColA, (verifique a postagem da consulta original) .

Isso significa que ambos precisam ser degustados. Desde Code3 apareceu duas vezes primeiro com A, em seguida, com B.

E, como escrevi, Últimas é recente, então, a função MAX não pode ser usada, MIN encontra as últimas.

Verifique a captura de tela,

afórmulaé

=SE(D423:D428="A", SE (E423: E428 = ({"Código1", "Código", "Código3"}), MIN (F423: F428)))

Até a fórmula Non-Array resolveu o problema.

NB: você pode projetar a fórmula usando seu intervalo de dados.

Eu postei a solução depois de ter sido testada por mim, caso seja diferente, por favor apenas comente.

    

Answer 4

Com pouca correção, trouxe a solução que encontra a Data recente somente dentro da área necessária. Os dados de filtros são usados com os critérios A e Code1, Code2 ou Code3.

{=MAX(SE(D423:D428="A", SE (E423: E428 = {"Código1", "Código", "Código3"}, F423: F428, "")))}