Rastreamento e sincronização de várias planilhas com uma planilha mestre

Question

Costumo encontrar situações em que as informações em uma planilha precisam ser divididas e distribuídas em unidades organizacionais separadas, mas, ao mesmo tempo, preciso rastrear e relatar atualizações de forma abrangente dessas informações como uma única lista.

Existe uma maneira eficiente de automatizar as principais tarefas necessárias para isso?

1. Divida uma folha mestre em vários arquivos separados, com base em um determinado atributo.
2. Sincronize informações entre a planilha principal e todos os arquivos relacionados.

Cada planilha será idêntica, em termos de cabeçalhos de coluna e layout. Na maioria das vezes, os itens de linha não serão adicionados / removidos ao longo do acompanhamento dos dados. Geralmente, também posso atribuir um GUID a cada item de linha quando precisar fazer referência cruzada de dados. Embora seja ideal que a solução se adapte automaticamente a exceções, elas devem ser raras o suficiente para que eu possa manipulá-las manualmente, conforme necessário.

Eu estou bastante familiarizado com a escrita de fórmulas do Excel e geralmente termino usando VLOOKUPS complexos ou operações similares para coisas como essa (quando não sou apenas copiar / colar por atacado entre as folhas manualmente). Estou aberto a aprender coisas como o VBScript para isso, mas tenho praticamente zero experiência na linguagem.

Talvez essa seja uma tarefa mais adequada para um banco de dados, como o Access, e estou aberto a essas opções também. Mas, no final, minha saída precisa estar em uma planilha do Excel e preciso sincronizar facilmente o banco de dados com esses documentos (nos dois sentidos) quando houver atualizações.

Eu preferiria fazer isso sem qualquer software de terceiros, se razoavelmente possível.

Infelizmente, os processos envolvidos aqui geralmente são um pouco ad-hoc. Os relatórios geralmente são enviados de um lado para o outro por e-mail. Ocasionalmente, usamos compartilhamentos de arquivos e / ou Sharepoint. Eu preciso ser capaz de me adaptar o máximo possível, sem forçar todo mundo a um sistema específico o tempo todo. (Enquanto isso seria ideal, é um problema além da minha autoridade para resolver.)

Algo parecido com isso seria bom:

1. "Master Sheet" (ou banco de dados) reside em meu próprio cantinho do mundo (compartilhamento de arquivos, Sharepoint, desktop, onde).
2. "Master Sheet" exporta "Child Sheets" para um local definido, onde quer que eles precisem. Todas as crianças podem ou não ser armazenadas no mesmo local.
3. As "Folhas de criança" são atualizadas diretamente no local vinculado ou eu copio manualmente as revisões mais recentes dos caminhos vinculados antes da sincronização.
4. "Master Sheet" precisa ser capaz de enviar / receber atualizações para / de "Child Sheets" vinculados sob demanda.

Answer 1

O Excel não pode sincronizar de duas formas
Como expliquei em essa pergunta semelhante (mas não a mesma) , o Excel pode sincronize dados de duas maneiras. Ou você tem um mestre e vários escravos (ou filhos, como você os chama) e você pode editar o mestre, mas somente ler o escravo. Ou você faz o inverso: você pode editar os escravos, mas você só pode ler o mestre.

Outra coisa que o Excel não pode fazer (fora da caixa) é enviar dados. O Excel foi projetado para sincronizar dados "on demand" ou "just in time", sempre que você abrir um arquivo ou atualizar seu conteúdo. Você não pode enviar uma edição para outra planilha do Excel.

Se os requisitos acima forem "obrigatórios", o Excel não é a solução certa, compre que você deve procurar uma solução baseada em um banco de dados relacional.

Vou explicar brevemente como configurar os master-> slaves e o slave-> master setups, apenas para você começar.

Master- > escravos
Crie uma tabela na folha mestre e salve-a; Você também pode usar um banco de dados do Microsoft Access para isso. Em seguida, crie uma tabela dinâmica em cada escravo. Defina o filtro da tabela dinâmica nas configurações necessárias e salve a folha.

Escravos- > master
Crie uma tabela em cada escravo. Use o Power Pivot para agregar escravos diferentes e colocá-los no mestre. Essa solução também funcionaria para uma abordagem "distribuída", na qual não há um mestre, mas todos os poços se conectam uns aos outros. Isso exigirá duas planilhas em cada pasta de trabalho: uma com os dados (o subconjunto gerenciado por esse usuário / departamento) e outra com o relatório dinâmico baseado no Power Pivot, que contém uma agregação de todos os dados nos diferentes escravos. / p>

Talvez a última opção mencionada (a abordagem distribuída) seja a que mais se aproxima dos seus requisitos.

Observe que, se você precisar ocultar um subconjunto dos dados em uma planilha, poderá sempre colocar um filtro de tabela no lugar e bloquear a célula do filtro de relatórios.