Consulta de correspondência de índice do Excel

Question

Eu tenho a tabela à direita, abaixo, com as datas e as informações marcadas com elas. Quero mostrar / listar todas as informações marcadas com as datas correspondentes, sem usar tabela dinâmica, já que estou planejando extrair as informações de outra planilha que seja uma lista mestre. Que tipo de fórmula devo usar?

    

Answer 1

Originalmente, interpretei mal sua pergunta. Você tem os dados à direita e deseja convertê-los para os dados à esquerda? Eu não sou profissional, mas o jeito que eu faço é como mostrado na foto aqui (eu não sei tem bastante representante para postar fotos em linha).

Essencialmente, eu criei um par de índices auxiliares à esquerda, o primeiro rastreia a posição atual da linha que incrementa se houver dados a serem obtidos, caso contrário, ele é zerado:

=IFERROR(IF(INDEX(data,A3+1,B3)<>"",A3+1,1),1)

O IFERROR é apenas porque eu tinha uma matriz (dados) nomeada que tem um limite e eu não queria verificar se esse limite tinha sido atingido. A3 é a referência à linha usada anteriormente que está logo acima da célula atual, enquanto B3 é uma referência à coluna usada anteriormente (acima e à direita).

A fórmula do índice da coluna apenas pega a linha atual e, se for 1, significa que iniciamos uma nova coluna, então pegue a coluna usada anteriormente e adicione 1, caso contrário, ficaremos na mesma coluna.

=IF(A4=1,B3+1,B3)

A coluna de data apenas pega o índice da coluna atual e o obtém usando "index"

=IFERROR(INDEX(dates,1,B4),"")

A coluna de valor apenas pega os índices de linha e coluna e exibe o valor da tabela:

=IFERROR(INDEX(data,A4,B4),"")

Os índices iniciais para linha / coluna são definidos como 1 para começar, o restante é gerado automaticamente. Espero que isso ajude!

    

Answer 2

Isso pode ser feito com o Power Query.

Selecione uma célula no intervalo e clique no ícone Data Ribbon > Da tabela (ou, para versões anteriores do Excel que 2016, instale o suplemento do Power Query e clique na faixa de opções Power Query > From Table). Então

• Marque a caixa "Minha tabela tem cabeçalhos" e OK.
• Quando os dados forem abertos no Power Query Editor, clique em Adicionar coluna > Índice.
• Selecione a coluna de índice recém-criada e clique em Transformar > Unpivot Outras Colunas

Os dados agora mostram normalizados e você pode adicionar comandos para classificar, renomear as colunas e, finalmente, remover a coluna de índice, que não é mais necessária. Em seguida, salve a consulta para carregar os dados em uma planilha. Quando seus dados originais são alterados, você pode atualizar a consulta para atualizar a lista normalizada.