Consolidar dados de várias tabelas é uma das operações mais comuns que realizamos no Microsoft Excel. Para o fazer temos sempre várias opções desde logo a utilização de funções como PROCV [VLOOKUP] e PROCX [XLOOKUP]. Se as origens de dados são de origens externas podemos também sempre usar o Power Query como ferramenta base para juntar os dados das várias tabelas.

Mas, se os vários intervalos já se encontram na folha de cálculo e se pretendemos trabalhar o intervalo no seu todo e não apenas 1 ou 2 colunas, podemos usar funções de Python para trabalhar os dados.

É o que vamos ver no artigo com 3 métodos diferentes para juntar vários intervalos num único.

Vamos utilizar a biblioteca Pandas que é ideal para cenários onde temos um intervalo (DataFrame) para trabalhar.

Método 1 | pd.DataFrame.concat

O método pandas.concat() serve para combinar dois ou mais objetos de Pandas, mais especificamente DataFrames ou Series (uma série é uma coluna) ao longo de um eixo específico, ou seja, empilhá-los verticalmente ou juntá-los lado a lado.

Podes pensar nele como uma forma realizar um copy – paste de intervalos, um em cima do outro (empilhados) ou lado a lado.

No cenário temos 3 intervalos que vamos começar por juntá-los na vertical.

Passo 1 – Criar a variável para os intervalos

Podemos considerar cada intervalo como uma base de dados, e no caso vamos guardar cada um dos intervalos como DataFrame aplicando o nome a cada uma das bases.

Após iniciar o código Python numa célula, que podes começar por escrever =PY ou iniciar no botão Inserir Python do separador Fórmulas.

df_1 = xl(“B5:E14”, headers=True)

df_2 = xl(“B17:E28”, headers=True)

df_3 = xl(“B31:E41”, headers=True)

Depois de definido o código, que basicamente armazena cada um dos intervalos numa variável, submetes o código com CTRL + ENTER.

O resultado é uma DataFrame de Python, que na verdade é a df_3 que representa o último intervalo, mas o objetivo é ter uma célula com as 3 bases para podermos chamá-las quando necessário.

Passo 2 – Concatenar todos os intervalos

Para juntar todos os intervalos com o método pd.concat() basta indicarmos sobre a forma de uma lista -> [], todos os intervalos: [df_1, df_2, df_3]. A este resultado dei o nome “consolidado” caso pretenda utilizar esta DataFrame no futuro.

consolidado = pd.concat([df_1, df_2, df_3])

Se pretendermos podemos ver o resultado como Array Dinâmico no Excel.

No exemplo podemos reparar que é criado um índice para cada uma das DataFrames. Se pretenderes ignorar este índice pode usar o parâmetro ignore_index = True.

Aplicar o parâmetro ignore_index = True

consolidado = pd.concat([df_1, df_2, df_3], ignore_index = True)

Nota: Podes usar uma solução semelhante com as funções de Excel, neste caso com a função JUNTARV [VSTACK].

Reconhecer os intervalos de origem com pd.concat()

Imagina que ao consolidar os dados pretendes identificar numa coluna o nome do intervalo de origem, para saber de onde vem o conjunto de dados.

Podes implementar esta lógica com outro parâmetro útil da função pd.concat com o parâmetro keys onde mais uma vez, indicas sobre a forma de uma lista o intervalo de valores para as origens.

No exemplo vamos utilizar 3 células do Excel com os nomes dos intervalos.

consolidado = pd.concat(

    [df_1, df_2, df_3],

    keys=[xl(“B4”),xl(“B16”),xl(“B30”)]

)

O resultado é a DataFrame com o índice substituído pelo nome de cada Origem.

Concatenar na Horizontal

Embora seja possível concatenar os intervalos na horizontal, como demonstrado no código e na imagem em baixo. Vamos ver uma alternativa melhor com a função pd.DataFrame.merge.

Para juntar na horizontal com a função concat() devemos indicar o índice de cada uma das origens com o parâmetro set_index -> (neste exemplo vamos usar a coluna do ID) e indicar que vamos concatenar os intervalos pelo eixo horizontal -> axis = 1.

Para completar a função adicionei o método .fillna para preencher as células sem dados com um valor em branco.

juntar_h = pd.concat(

    [df_1.set_index(“ID Produto”),df_2.set_index(“ID Produto”),df_3.set_index(“ID Produto”)],

    axis=1).fillna(“”)

O resultado é uma DataFrame com os intervalos empilhados na horizontal! Como existem IDs que não coincidem em todas as bases obtemos valores em branco, neste caso com o método fillna().

Utilizar a função pd.DataFrame.merge()

A função merge é mais usada e semelhante para juntar intervalos na horizontal. Assemelha-se mais com o conceito JOIN em SQL ou mesmo o Merge Queries em Power Query.

No próximo exemplo temos 2 tabelas (Departamento e Colaborador), e como habitual, vamos começar por definir as variáveis para cada uma das DataFrames, para podermos reutilizar nas próximas funções.

Juntar a Tabela Colaborador à Tabela Departamento

Para realizarmos a operação Merge, devemos sempre considerar a ordem das Tabelas, sendo que existe sempre uma tabela “à esquerda” e outra “à direita”. Esta questão é importante para sabermos qual é a tabela principal à qual serão adicionadas as restantes.

Por outro lado, esta questão é também fundamental para definir o método de junção, que é possível definir, também como em SQL ou Power Query. Refiro-me aos métodos left, right, inner e outer por exemplo, que definem como as linhas de ambas as tabelas se cruzam. Esta opção é definida com o parâmetro how

Para realizarmos a função merge devemos ainda indicar qual a coluna comum a ambas as tabelas. Esta opção é definida pelo parâmetro on

De resto basta indicar cada uma das tabelas.

juntar = pd.merge(departamento, colaborador, on=[“ID”], how=”left”)

O resultado é uma DataFrame com a junção onde mostra todos os registos da tabela da esquerda (Departamento), com os registos da tabela da direita (Colaborador) mostrando os registos que não têm correspondência do lado direito.

NOTA: se não aplicarmos o parâmetro how, por defeito a função merge aplica o método “inner” resultando apenas nas linhas existentes em simultâneo em ambas as tabelas.

Podemos optar por novamente juntar o método .fillna() para substituir os valores nulos por outro valor qualquer.

juntar = pd.merge(departamento, colaborador, on=[“ID”], how=”left”).fillna(“-“)

Outros tipos de união | Right

Outros tipos de união | Inner

Outros tipos de união | Outer

Verificar o tipo de junção

Existe um parâmetro adicional indicator que permite verificar numa nova coluna da DataFrame o tipo de junção aplicado.

Com esta opção não podemos anexar o método fillna()

Aplicar o merge diretamente na DataFrame

O Objeto DataFrame, tem o método merge, que permite realizar a mesma operação. No exemplo estou a definir uma nova DataFrame, aplicando o “método” .merge à DataFrame -> Departamento. Por defeito é aplicado o join com a opção “inner”.

novos_dados = departamento.merge(colaborador, on=[“ID”])

Utilizar o método pd.DataFrame.join()

O último é exemplo é o método join() disponível na DataFrame. A principal diferença em relação à função merge é, por um lado, o resultado do exemplo anterior, onde verificámos que a função merge é autónoma, mas que tem o outro lado da moeda com o método .merge. O método join() é focado quase exclusivamente em combinar dados através dos índices das DataFrames.

Uma vez que este método é focado nos índices, devemos em primeiro lugar considerar um novo índice para as DataFrames. Esta opção é importante porque quando é selecionado o intervalo, o Python cria um índice automático. Neste caso especifico pretendo usar a coluna ID de cada uma das tabelas / intervalos como índice.

Assim vamos usar o parâmetro set_index quando definirmos as DataFrames.

departamento = xl(“B5:E12”, headers=True).set_index(“ID”)

colaborador = xl(“B15:D22”, headers=True).set_index(“ID”)

Agora para aplicar o método .join() o processo é muito semelhante ao método .merge().

Por defeito a junção é feita pela opção “inner” mas podemos indicar o parâmetro how.

O método é aplicado diretamente na DataFrame!

juntar_join = colaborador.join(departamento, how=”left”).fillna(“-“)

Aqui vai um resumo das 3 alternativas que vimos no artigo:

Método

pd.DataFrame.concat(): Empilhar os intervalos com as mesmas colunas ou apenas “colar” lateralmente os dados.

pd.DataFrame.merge(): Para realizar operações de junção mais complexas (semelhante a SQL ou Power Query), com a possibilidade de indicar a coluna de junção entre as tabelas.pd.DataFrame.join(): Unir tabelas de forma rápida quando os dados já estão definidos pelos índices respetivos índices.

Artigos por Categoria

Artigos por Categoria

• Microsoft Excel (43)
• Power Apps (16)
• Power Automate (3)
• Power BI (11)
• Power Query (14)
• Python (3)
• VBA (7)

Últimos Artigos

• Soma Dinâmica em Power Query: Guia Completo
• Funções Essenciais do Power Query: FillDown e ReplaceValue
• Como Mudar a Direção do Filtro em Power BI
• Consolidar Dados no Excel com Python: 3 Métodos Eficazes
• Comparar Intervalos em Excel com NumPy