Resumindo várias tabelas no Excel

Question

Espero que este seja o site certo para esta questão

Eu tenho um ano de recibos de entrega (mais de 60 tabelas, cada uma em uma folha separada), todas com os mesmos cabeçalhos e eu gostaria de adicioná-las todas em uma tabela de resumo de anos.

Tudo o que preciso é de Art-No. , Description e total Delivered Quantity , mas passar por todas as planilhas e modificá-las individualmente levará muito tempo. Nem todo pedido terá os mesmos produtos, ou na mesma ordem. Alguns produtos podem aparecer apenas uma vez durante o ano, enquanto outros estão em todos os pedidos. É um pouco de confusão.

O Art-No. é essencialmente um ID do produto e pode ser usado para identificar um produto (assim como Description ).

Exemplo de Tabela 1

ExemplodeTabela2

TabeladeExemplo3

ExemplodaTabeladeResultadosDesejados

EutenteiusaroPowerQueryparamesclar,masissonãoconsolidaosdados,apenaspareceseraplicado.

Eutentei tabelas dinâmicas , mas eles não querem levar o chamado dizendo" Referência da Fonte de Dados inválida "

Existe uma maneira mais fácil de fazer isso?

    

Answer 1

Acho que você estava no caminho certo com o Power Query. Geralmente, as etapas são:

1. Carregue cada tabela no Power Query (é uma dor, mas não sei de nenhuma opção de carregamento em massa). Você pode definir a opção padrão para carregar apenas a conexão, para que você não tenha folhas duplicadas com a versão Power Query de cada tabela.

2. Abra sua primeira tabela no Power Query Editor. Escolha Anexar consultas (não mesclar) na seção Combinar da faixa de opções.

3. Na caixa de diálogo Acrescentar, selecione sua segunda tabela e, em seguida, OK. Repita para cada tabela que você carregou no Power Query. Agora você está adicionando as linhas de cada uma das suas 60 tabelas em uma "super mesa".

4. Quando todas as suas tabelas carregadas forem adicionadas, carregue sua consulta de acréscimo no Excel.

5. Use a tabela Anexar consulta como uma fonte de dados para criar uma tabela dinâmica. Selecione Art-No. e Descrição como seus rótulos de linha (formato como Layout tabular, sem subtotais) e Quantidade como seus valores.

    

Answer 2

Se todos os dados estivessem em uma planilha, seria muito fácil aplicar uma tabela dinâmica a ela para gerar o resumo. Copiar e colar mais de 60 folhas em uma levaria alguns minutos para ser feito manualmente, mas poderia ser feito.

Como alternativa, você pode usar uma macro para copiar e colar. Supondo que cada planilha comece em A1 e todas as planilhas contenham tabelas de dados, essa macro deve funcionar:

Sub Macro1()

NumSheets = Application.Sheets.Count
Sheets.Add After:=Sheets(Sheets.Count)

i = 1
Do While i <= NumSheets
    Sheets(i).Select
    If (i = 1) Then
      Range("A1").Select
    Else
      Range("A2").Select
    End If
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(NumSheets + 1).Select
    If (i = 1) Then
       Range("A1").Select
    Else
       Range("A2").Select
       Selection.End(xlDown).Select
       ActiveCell.Offset(1, 0).Range("A1").Select
    End If
    ActiveSheet.Paste
    i = i + 1
    Loop


End Sub

    

Answer 3

Aqui está uma abordagem que não usa o Power Query.

Para qualquer tabela, você pode totalizar a quantidade entregue de um produto específico (Art-No.) usando SUMIF . Por exemplo, a seguinte fórmula fornecerá a quantidade total fornecida de Art-No. 59792 em uma tabela chamada Tabela1.

'=SUMIF(Table1[Art-No.],"=59792",Table1[Delivered Quantity])'

Você pode usar a função INDIRECT para extrair o nome da tabela de outra célula. Por exemplo, se a célula A2 contiver "Tabela1", a fórmula a seguir produzirá o mesmo resultado que o acima

'=SUMIF(INDIRECT(A2"[Art-No.]"),"=59792",INDIRECT(A2&"[Delivered Quantity]"))'

Então, para criar uma nova tabela com o total de cada tipo de artigo em cada tabela, você pode fazer o seguinte:

1. Coloque todos os números de artigo na primeira linha, começando em B1.
2. Coloque todos os nomes das tabelas na primeira coluna, começando em A2.
3. Copie o seguinte em B2

= SUMIF (INDIRECTO ($ A2 & "[Art-No.]"), B $ 1, INDIRECT ($ A2 & "[Delivered Quantity]"))

4. Copie B2 e cole no restante da sua mesa.

Answer 4

Aqui está uma opção que não usa Power Query ou tabelas dinâmicas:

Como você declarou nos comentários, você pode criar a tabela de resultados desejada com tudo, exceto os campos Ordered qty. e Delivered qty. . Isso significa que tudo o que precisamos fazer é totalizar os valores para cada planilha usando Art-no. como o ID exclusivo.

Etapa 1: Crie uma lista com todos os nomes de planilha contidos nela. Isso pode ser feito com um pequeno VBA, pois há tantas folhas no seu arquivo.

Sub SheetNames()
    Dim ws As Worksheet
    Dim r As Integer
    Set ws = Worksheets.Add
    For r = 1 To Worksheets.Count
        ws.Cells(r, 1).Value = Worksheets(r).Name
    Next
End Sub

Etapa 2: Crie um intervalo nomeado que se refira à lista de nomes de planilhas. Eu transformei a lista em uma tabela e baseie o intervalo de nomes nisso, mas você não precisa.

Etapa3:CriesuatabeladeresultadosbasesemosvaloresOrderedqty.eDeliveredqty.preenchidos.Sevocênãotiverissodisponívelemalgumlugar,talvezsejamaisfácilusaralgocomoo Sir Adelaide postou para combinar todas as folhas, apague os campos que você não precisa, apague todo o qty. valores e remova duplicados. Você pode se perguntar por que você precisaria disso se você simplesmente seguir esse método, mas o truque na próxima etapa é muito legal para não compartilhar.

Etapa4:Escrevasuasfórmulasparatotalizarosdadosdetodasasplanilhasdeumasóvez.Primeiro,aquiestáafórmulaquevocêpodeusarparaOrderedqty.:

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"B2:B10000"),[@[Art-no.]],INDIRECT("'"&sheets&"'!"&"D2:D10000")))

É uma SUMIF especial envolvida em SUMPRODUCT . A chave para fazer com que o SUMIF funcione com uma referência 3D é a função INDIRECT e a lista de nomes de planilhas que criamos. Aqui estão as partes da fórmula:

• INDIRECT("'"&sheets&"'!"&"B2:B10000") É uma função INDIRECT padrão, exceto que estamos fazendo referência ao intervalo nomeado que fizemos na Etapa 2, que aponta para uma lista de nomes de planilha. É isso que faz dela uma referência 3D. Eu escolhi B2:B10000 , mas você pode ajustar / expandir conforme necessário. É fácil tornar a referência maior do que a necessária para garantir que nada seja esquecido, desde que os intervalos nas duas funções INDIRECT sejam do mesmo tamanho. Ele precisa apontar para a coluna com Art-no. nas outras planilhas.
• [@[Art-no.]] Desde que criei minha tabela de resultados em uma tabela real no Excel, isso apenas aponta para o valor Art-no. na linha atual com a fórmula.
• INDIRECT("'"&sheets&"'!"&"D2:D10000") Este é o mesmo que o outro INDIRECT , mas aponta para a coluna Ordered qty. .
• SUMIF(~) Isso agora se torna SUMIF(Art-no. from every sheet, Art-no. in this row, Ordered qty. from every sheet)
• SUMPRODUCT(~) O SUMIF retornará uma matriz em vez de um único valor, portanto, usamos isso para apenas somar todos os valores nessa matriz.

A fórmula para Delivered qty. é a mesma, exceto que você deve alterar D2:D10000 para G2:G10000 ou o que for necessário para apontar para a coluna com Delivered qty. nas outras planilhas.

_{Crédito onde o crédito é devido, eu encontrei este método em CreditJet.net e pensei que era incrível.}