Início / computer / Perguntas / 1671787
Accepted
Darren Bartrup-Cook
Asked: 2021-08-28 04:24:00 +0800 CST

Fórmula para criar um produto cartesiano a partir de dois conjuntos de dados em um intervalo derramado

  • 772

Estou atrás de uma fórmula que criará um produto cartesiano a partir de dois conjuntos de dados em que qualquer um dos conjuntos de dados possa crescer ou diminuir.

A resposta dada por @ScottCraner neste post quase me deu a resposta, exceto que não está derramando como esperado.
Excel - Cada combinação da coluna a e coluna b na coluna d e e

Não estou procurando uma solução PowerQuery ou VBA - PQ envolve confiar na equipe para pressionar Refresh, VBA envolve confiar na equipe para notar a barra amarela que diz "Ativar macros" e entender o que isso significa.

Dados de teste: insira a descrição da imagem aqui

Na imagem acima estou tentando obter os valores nas colunas M:Nusando os dados nas colunas A:F.
No momento estou recebendo os valores em J:K.

  • Minha versão reduzida dos dados brutos está na coluna Dem uma tabela imaginativamente chamada Table1 .
  • F1contém a fórmula =SEQUENCE(EndDate-StartDate+1,,StartDate)para me dar uma lista de todas as datas entre o início e o fim.
  • G1contém a fórmula=UNIQUE(Table1[Process])
  • I1contém a fórmula =SEQUENCE(COUNTA($F$1#)*COUNTA(G1#))que será derramada para me dar o número de linhas na minha tabela final (ou seja, quanto eu preciso derramar).
  • J1contém a fórmula =IF(I1#<>"",INDEX($F$1#,INT(ROW()-1)/COUNTA($G$1#)+1)).
    A Indexparte por conta própria será arrastada para baixo e me fornecerá os valores em column M, ou eu poderia selecionar o intervalo e usar uma fórmula de matriz para também me fornecer os valores.
    Se eu avaliar a fórmula na célula J4, ela deve fazer referência à célula F2.... e faz, mas retorna o valor da célula F1.
    insira a descrição da imagem aqui

Tenho a sensação de que estou perdendo algo fundamental sobre ranges derramados aqui. Alguma ideia?

microsoft-excel worksheet-function

1 respostas

  1. Best Answer

    Com Let and CHOOSE, podemos combinar todas as suas etapas em uma fórmula para gerar as duas colunas:

    =LET(rng,Table1[Process],
    strt,A2,
    end,B2,
    unq,UNIQUE(rng),
    dt,SEQUENCE(end-strt+1,,strt),
    cntP,COUNTA(unq),
    cntD,COUNT(dt),
    lstP,INDEX(unq,INT(SEQUENCE(cntP*cntD,,1,1/cntD))),
    lstD,INDEX(dt,MOD(SEQUENCE(cntP*cntD,,0),cntD)+1),
    CHOOSE({1,2},lstP,lstD))

    Basicamente segue seus passos.

    1. Obtemos o único da tabela.
    2. Obtemos o intervalo de datas usando a sequência.
    3. Contamos ambas as listas e as armazenamos.
    4. Usando INT() obtemos um array de números que são a contagem de datas repetidas o número de vezes que são únicos para que os números sejam ordenados. Isso passa para o índice
    5. Então o mesmo com datas, mas os números são de série.
    6. Então, com CHOOSE, nós os colocamos lado a lado para que eles se espalhem.

    insira a descrição da imagem aqui

    • 5

relate perguntas