Mantendo automaticamente duas tabelas de dados do Excel em sincronia (sem VBA)

Question

Estou montando uma pasta de trabalho para rastrear um portfólio de ações. A folha principal contém uma tabela com a lista das transações. A partir disso, gostaria de criar uma tabela de visão geral em outra folha com apenas uma linha por símbolo de ação exclusivo que inclui itens como base de custo, devoluções etc. O problema é que nada que tentei atualiza a tabela de visão geral corretamente quando são adicionadas linhas para a tabela de transação. O mais próximo que eu tenho é algo como o seguinte:

link

No entanto, isso requer aplicar essa fórmula a cada célula na coluna principal da planilha de visão geral. E, mesmo assim, o intervalo da tabela não é estendido para incluir novas linhas conforme elas se tornam válidas. Essencialmente, estou procurando uma maneira que adicione linhas automaticamente a uma tabela e copie a fórmula de linhas anteriores com base em uma tabela diferente, alterando sem usar o VBA.

Um exemplo concreto pode explicar melhor meu objetivo. Suponha que o GOOG acabou de ser adicionado e as outras transações já existiam.

Este é o closet que eu tenho para obter o comportamento de atualização automática.

Sheet1
Symbol     Type    Shares    Price
F          Buy     100       12
MSFT       Buy     100       25  
MSFT       Buy     50        28
F          Buy     100       16
GOOG       Buy     25        550

Sheet2
Symbol                              Quantity
F                                   200     
MSFT                                150
------------------------------------------------------------ // Table Boundary
GOOG                                25
{=UNIQUE_LIST(Sheet1[Symbol], A5)}  =SUMIF(Sheet1[Symbol], A5, Sheet1[Shares])
{=UNIQUE_LIST(Sheet1[Symbol], A6)}  =SUMIF(Sheet1[Symbol], A6, Sheet1[Shares])
... // Formulas start at row 2 and extend down tons of extra rows
    // Table boundary didn't extend to include GOOG

O ideal é que eu queira que Sheet2 fique mais parecido com isso, onde as fórmulas são copiadas somente quando necessário

Sheet2
Symbol                              Quantity
F                                   200     
MSFT                                150
GOOG                                25
------------------------------------------------------------ // Table Boundary
// Table extended and no extra formulas

Adicionando uma linha

    

Answer 1

Parece que alguém se inspirou na minha pergunta e escreveu um post inteiro sobre como fazer isso

link

    

Answer 2

Lembre-se de que geralmente há muitas maneiras de obter o mesmo resultado com o Excel. Talvez você possa usar as funções do banco de dados. No exemplo que você deu se referenciar o banco de dados como Folha1! A: D ou definir um nome que se refira a essa referência, você pode adicionar quantas linhas desejar. Infelizmente, as funções do banco de dados são feias e difíceis de usar devido à maneira como você deve especificar critérios.

Outra opção seria usar um array forum. algo como: {= sum (if (sheet1! a2: a999="MSFT", sheet1! b2: b999,0))}

    

Answer 3

Você escreve:

No entanto, isso requer aplicar essa fórmula a cada célula na coluna principal da planilha de visão geral. E, mesmo assim, o intervalo da tabela não é estendido para incluir novas linhas conforme elas se tornam válidas.

Você não pode escapar destes dois requisitos se pelo menos você não definir o comprimento máximo da lista em sua primeira planilha.

    

Answer 4

Neil

Se você estiver usando o XL 2003 ou posterior, tente colocar seus dados de origem em uma Lista (XL 2003) ou Tabela (mesma coisa, renomeada em XL 2007, 2010). Em seguida, use sua lista / tabela como a fonte de uma tabela dinâmica em uma planilha diferente. Isso atende às suas necessidades de dados e relatórios, como eu os entendo. A fonte dinâmica expandirá e contratará a lista / tabela.

A única ressalva é que as Tabelas Dinâmicas não são atualizadas automaticamente quando seus dados de origem são alterados. Você terá que apertar o botão Refresh. Se você puder usar o VBA, poderá gravar o código de evento que aciona uma atualização da tabela dinâmica sempre que uma célula dentro da Lista / Tabela for alterada.

    

Answer 5

Eu trabalho muito com tabelas dinâmicas. Quando você definir sua matriz, digite manualmente a célula final.

Por exemplo, tenho dados que começam com o intervalo A11: C2500.

Se eu previr que os dados vão crescer para a linha 10000, alterarei manualmente a matriz para ler A11: C15000 para garantir que eu tenha espaço para crescimento.

Se você fizer isso, verá sua tabela Dinâmica conforme novos itens são adicionados à lista de transações.