Como já foi mencionado, o que você quer seria natural para uma solução de banco de dados. Aqui está uma abordagem que aproveita a capacidade do Excel para se conectar a fontes de dados externas. Eu não trabalhei com a abordagem extensivamente, então tem que ser considerado experimental, pelo menos para mim, e pode haver pegadinhas escondidas que eu ainda não descobri.
A idéia é fazer uma conexão de dados da pasta de trabalho consigo mesma, usar a Ferramenta de Consulta da Microsoft para definir uma consulta União em tabelas de dados nas planilhas de assunto da pasta de trabalho e retornar o resultado da consulta como uma tabela consolidada no resumo Folha. Os dados nessa tabela podem ser classificados e filtrados conforme necessário.
A configuração das folhas de assunto segue as linhas que você delineia (elaboradas de alguma forma). Para que os dados nas folhas sejam reconhecidos como "tabelas" pela Ferramenta de consulta, cada lista de livros deve ser nomeada. Por exemplo, o intervalo A1:E11 na folha Subj1 também foi nomeado Subj1 , com nomes semelhantes para as outras duas folhas de dados. [1]
Paraconfiguraraconsulta,comeceselecionandoFromOtherSources/FromMicrosoftQuerynaguiaDatanafaixadeopções,escolhendoArquivosdoExcelcomoafontededadoseespecificandoaprópriapastadetrabalhocomooarquivodeorigem.
A Ferramenta de consulta será aberta com o Assistente de consulta visível. Cada um dos nomes de intervalo que você definiu será listado como tabelas, com seus cabeçalhos de coluna como campos. Vá em frente e selecione os campos da primeira tabela, Subj1, para serem incluídos na consulta. Em seguida, clique em Próximo duas vezes para ir para o diálogo Concluir do Assistente de Consulta. Lá, selecione o botão de rádio View data or edit query in Microsoft Query e clique em Concluir .
AprópriaFerramentadeconsultaseráaberta,comumavisualizaçãográficadaconsultaespecificadaatéomomentoeumalistagemdosresultadosdaconsulta.CliquenobotãoSQLnabarrademenuprincipal,abrindooeditordeSQL.
Modifique a consulta para que ela combine todos os intervalos de dados (três no meu exemplo):
SELECT subj1.subject, subj1.title, subj1.status
FROM subj1
UNION ALL
SELECT subj2.subject, subj2.title, subj2.status
FROM subj2
UNION ALL
SELECT subj3.subject, subj3.title, subj3.status
FROM subj3
Depois de clicar em OK , salve a consulta. Agora você pode incorporar os resultados da consulta selecionando File / Return Data to Microsoft Excel , seguindo os prompts para importar os resultados para a planilha Summary como uma tabela classificável e filtrável.
Como o Excel acha que os dados da tabela são de uma fonte de dados externa, você precisará atualizar a tabela depois de editar as listas de livros. Refresh pode ser acessado clicando com o botão direito do mouse na tabela para abrir o menu de contexto. [2]
Se você deseja modificar a consulta posteriormente (por exemplo, para adicionar uma categoria), pode acessá-la via Data / Connections na faixa de opções. Clique em Propriedades na caixa de diálogo Workbook Connections e, em seguida, selecione a guia Definition . Edite a consulta diretamente na caixa Command text ; O Excel irá reclamar se você pressionar Editar consulta .
1. As áreas sombreadas em azul na coluna E e na linha 11 estão lá para conveniência futura quando livros adicionais (ou categorias) são adicionados. Eles podem ser arrastados para baixo ou para a direita para liberar espaço para mais dados, preservando o nome do intervalo. (Incluir linhas extras no nome do intervalo resultará em linhas em branco aparecendo na tabela de resumo; os intervalos dinâmicos definidos não são reconhecidos como tabelas pela Ferramenta de consulta.)
2. Escolher Table / External Data Properties no menu de contexto trará algumas propriedades de formatação de tabela úteis, incluindo desativar o redimensionamento automático das colunas da tabela em uma atualização.