Então, um pouco diferente da maioria das questões de matriz de correspondência de índice
Tenho uma tabela de movimentos de A a B. Posso ter movimentos Tipo 1, Tipo 2, Tipo 3 ou Tipo 4.
Cada um destes movimentos corresponde a um conjunto diferente de “Roll up steps”. Estes seriam Passo 1, Passo 2 ou Passo 3.
Esse relacionamento é capturado em uma tabela organizada, assim:
Step Type Relationship
Step 1 - Type 1 - 1
Step 2 - Type 1 - 0
Step 3 - Type 1 - 0
Step 1 - Type 2 - 1
Step 2 - Type 2 - 1
Step 3 - Type 2 - 0
Step 1 - Type 3 - 0
Step 2 - Type 3 - 0
Step 3 - Type 3 - 1
Step 1 - Type 4 - 1
Step 2 - Type 4 - 1
Step 3 - Type 4 - 1
Onde 1 é "Sim, eles estão relacionados" e 0 é "Não, eles não estão".
Agora há uma subetapa para a tabela de etapas, semelhante a esta:
SubStep --- Step
1 1
2 1
3 1
4 1
5 1
6 1
7 2
8 2
9 2
10 3
11 3
12 3
Agora, tenho uma tabela de subetapas e quantas subetapas preciso realizar. Eu tenho uma tabela de movimentos e quantas unidades de movimentos estão sendo movidas de A para B. Estou tentando descobrir quantas unidades precisam ser adicionadas a cada subetapa.
Por exemplo:
Se eu tiver o tipo de movimento 1 para 100 unidades, precisarei adicionar 100 unidades ao subpasso 1 - 6. Se eu tiver o tipo de movimento 2 para 200 unidades, precisarei adicionar 200 unidades adicionais ao subpasso 1-6 , para um total de 300 unidades movidas e, em seguida, 200 unidades adicionadas às subetapas 7 a 9.
Descobri que um somaproduto é provavelmente a melhor maneira de fazer isso. Atualmente a primeira parte está assim:
=SUMPRODUCT(UnitMovementWorksheetCurrent[Units],--(UnitMovementWorksheetCurrent[Month]=[@Month]),--(UnitMovementWorksheetCurrent[Year]=[@Year]),--(UnitMovementWorksheetCurrent[From]=[@[Lab Abbr]]),--(INDEX(MovementToStepLookup[Roll up Step],MATCH(UnitMovementWorksheetCurrent[Movement Type],MovementToStepLookup[Movement Number],0))=[@[Roll up Step]]))
No entanto, a parte que não está funcionando é (INDEX(MovementToStepLookup[Roll up Step],MATCH(UnitMovementWorksheetCurrent[Movement Type],MovementToStepLookup[Movement Number],0))=[@[Roll up Step]]))- eu esperaria obter uma matriz TRUE/FALSE, mas em vez disso recebo um erro #VALUE. O Index me fornece corretamente o array, o Match gera corretamente um array, ao mesclar os dois (ao percorrer) ele me dá apenas um número como resultado, em vez de um array de números, então está me dando (por exemplo) { 1} = 1, então um único VERDADEIRO.
Isso funciona quando estou percorrendo a fórmula, mas quando destaco tudo para calcular, só me dá #VALOR. Então, suponho que tenho duas perguntas (na verdade, a única questão é como obter meus dados, mas dividindo-os um pouco...):
1) Como faço para que minha correspondência de índice retorne um resultado de array?
2) O que estou fazendo de errado na minha fórmula?
Responder1
Então, depois de MUITO mais pesquisar no Google, a resposta é estranha e contra-intuitiva e, no momento em que escrevo, não sei realmente todas as razões pelas quais funciona, apenas que (parece estar!) Funcionando.
Cometi um erro antes ao retirar a etapa de rollup e depois compará-la. Isso estava incorreto, eu precisava retirar se fosse usado ou não. A partida mudou ligeiramente para refletir isso.
A maneira de forçar o Index a retornar vários resultados parece ser dupla: 1) Para ter uma instrução condicional, 2) Para ter a função N, assim:
INDEX(MovementToStepLookup[Usado?],N(IF(TRUE,MATCH(UnitMovementWorksheetCurrent[Tipo de movimento],MovementToStepLookup[Número do movimento]*(MovementToStepLookup[Roll up Step]=[@[Roll up Step]]),0))) )
Para minha condicional, eu apenas considerei TRUE. Por que se preocupar com algo mais complexo? Para o N, basta passar o array.
Não tenho ideia de por que isso funciona. O (artigo muito melhor) que explica como fazer isso diz que eles também não sabem o porquê. Mais leituras podem ser encontradas aqui:https://excelxor.com/2014/09/05/index-returning-an-array-of-values/. No entanto, funciona, então estou feliz