Determinar se o VBA é necessário no Excel ou se um gráfico / tabela dinâmica pode ser suficiente

Question

Eu gostaria de ter um título descritivo mais preciso para essa pergunta, mas não tenho muito conhecimento de planilhas do Excel ou VBA (sou principalmente um desenvolvedor de Java e não jogo muito no mundo da Microsoft).

Estou tentando usar uma planilha do Excel para realizar algo. Eu preciso determinar se eu posso chegar lá com uma tabela dinâmica, gráfico dinâmico ou alguma outra funcionalidade interna ... ou se eu preciso percorrer o caminho de escrever código VBA personalizado (ou talvez usando outra plataforma completamente).

O objetivo da planilha é ajudar a criar agendamentos para uma organização (um clube Toastmasters). Essa organização tem uma lista de membros, se reúne semanalmente e vários membros são designados para várias funções em uma determinada reunião.

Minha planilha é assim:

Primeira guia

Uma coluna ... uma lista de nomes que representam a lista de membros.

Segunda guia

Cada linha representa uma data de reunião anterior. Existem colunas para cada função e as células devem ser preenchidas com quem desempenhou essa função nessa data. Eu uso Validação de Dados para ter a lista da primeira guia disponível dentro de cada célula como um pulldown.

Terceira guia (talvez várias guias?)

Aqui está o ponto da coisa toda. Para cada uma das possíveis funções de reunião, gostaria de ver quais membros são os mais "atrasados" a serem designados para essa função. Basicamente, eu quero uma lista de todos os membros do clube, classificados em ordem de quanto tempo se passou desde a última vez que eles cumpriram esse papel. As pessoas que nunca serviram nessa função seriam classificadas no topo da lista.

Esses dados da terceira guia são algo que pode ser feito com uma Tabela Dinâmica, etc ... ou eu estou entendendo mal as finalidades e limitações dessas ferramentas?

    

Answer 1

Corrija-me se eu entender errado. Seus dados são assim:

Primeira guia (planilha):

Name
----
Alice
Bruce
Chuck
Derek
Emily

Segunda aba:

Date        Role1    Role2    Role3
--------    -----    -----    -----
13-01-11    Alice    Bruce    Chuck (Each selected from dropdown)
14-01-11    Derek    Emily    Bruce
15-01-11    Alice    Chuck    Derek
16-01-11    Emily    Bruce    Alice
17-01-11    Chuck    Derek    Bruce
18-01-11    Chuck    Bruce    Alice
19-01-11    Alice    Chuck    Bruce

Se a Tabela Dinâmica não for aplicável, a menos que você a converta para os formatos que o Mike escreveu.

Em vez disso, (EDITAR: depois de separar cada coluna de função na mesma guia e classificá-las em ordem alfabética), ou seja, criar uma segunda aba como essa (cut-paste-sort):

Date        Role1
--------    -----
13-01-11    Alice
15-01-11    Alice
19-01-11    Alice
17-01-11    Chuck
18-01-11    Chuck
14-01-11    Derek
16-01-11    Emily

Date        Role2
--------    -----
13-01-11    Bruce
16-01-11    Bruce
18-01-11    Bruce
15-01-11    Chuck
19-01-11    Chuck
17-01-11    Derek
14-01-11    Emily etc

Em seguida, você pode criar guias para cada função (ou separá-las na mesma guia) e usar a função LOOKUP para ver a última vez que um membro fez essa função. Por exemplo, para o Role1, o Tab 3 deve ser assim:

Name     Last Done (Date)
----     -------- 
Alice    19-01-11 [=LOOKUP(A2,Sheet2!$B$2:$B$8,Sheet2!$A$2:$A$8)]
Bruce    N/A
Chuck    18-01-11
Derek    14-01-11
Emily    16-01-11

Se você classificar do mais novo para o mais antigo, poderá ver quem nunca fez o papel no topo e quem fez a última vez na parte inferior.

Você também pode adicionar colunas para quantas vezes alguém fez uma função específica usando COUNT e quantos dias / semanas passaram desde a última vez usando TODAY e obtendo a diferença.

Copie e cole as mesmas funções para outras funções (assumindo que não são muitas) em guias diferentes.

    

Answer 2

Eu pessoalmente iria com o VBA, mas geralmente me inclino dessa maneira. Se você não é um cara do VBA, pode ser mais fácil de manter se você fizer isso com fórmulas e tabelas dinâmicas. Você terá que fazer alguns sacrifícios, no entanto. Então vale a pena investigar a maneira não-VBA de ver se os sacrifícios são aceitáveis para você. Aqui está um jeito.

Na folha de listagem, adicione colunas para todas as funções. No meu exemplo, há duas funções: Presidente e Secretário, então o Col A é o nome, o Col B é intitulado Presidente e o Col C é intitulado Secretário. Em B2, coloque esta fórmula

{=MAX((OFFSET(Meetings!$A$1,1,MATCH(B$1,Meetings!$B$1:$C$1,FALSE),20,1)=$A2)*(OFFSET(Meetings!$A$2,0,0,20,1)))}

Existem algumas suposições nesta fórmula que eu definitivamente tornaria mais robusta, mas é uma boa prova de conceito. Você insere isso com Control + Shift + Enter porque é uma fórmula de matriz. Ele retornará a data máxima que encontrar para esse nome na coluna Presidente. Preencha essa fórmula para baixo e para a direita para adequar seus dados.

Agora, com os dados na planilha de lista de tarefas, crie uma tabela dinâmica para cada coluna "role". Coloque o nome na área da linha e a função (por exemplo, Presidente) na área de dados. Provavelmente, o padrão será Contagem de Presidente. Clique com o botão direito do mouse nesse campo e escolha Configurações do campo. Mude Summarize By para Max e defina o formato numérico para uma data.

Em seguida, clique com o botão direito do mouse em um dos nomes e escolha as configurações do campo. Clique no botão Avançado. Altere as Opções de AutoClassificação para Ascendente em Máximo de Presidente.

O resultado deve ser uma lista de nomes classificados. Os nomes no topo foram os mais longos sem ser presidente.

Nada melhor que um exemplo. Faça o download de um link Ele contém uma pasta de trabalho do Excel 2003 que mostra o que descrevi acima.

    

Answer 3

Sim, uma tabela dinâmica deve ser suficiente, desde que você defina seus dados adequadamente na segunda guia:

Meeting       Member      Role      Weeks since
1/12/2010     Member 1    Role 1    8 [ =(TODAY()-A2)/7 ]
1/12/2010     Member 2    Role 2    8
1/12/2010     Member 3    Role 3    8
1/12/2010     Member 4    Role 4    8
1/12/2010     Member 5    Role 5    8
8/12/2010     Member 1    Role 1    7
8/12/2010     Member 3    Role 2    7
8/12/2010     Member 7    Role 3    7
8/12/2010     Member 6    Role 4    7
8/12/2010     Member 4    Role 5    7
15/12/2010    Member 1    Role 1    6
15/12/2010    Member 3    Role 2    6
15/12/2010    Member 4    Role 3    6
15/12/2010    Member 2    Role 4    6
15/12/2010    Member 6    Role 5    6
22/12/2010    Member 1    Role 1    5
22/12/2010    Member 2    Role 2    5
22/12/2010    Member 5    Role 3    5
22/12/2010    Member 3    Role 4    5
22/12/2010    Member 4    Role 5    5
29/12/2010    Member 2    Role 1    4
29/12/2010    Member 3    Role 2    4
29/12/2010    Member 4    Role 3    4
29/12/2010    Member 5    Role 4    4
29/12/2010    Member 5    Role 5    4
...

Em seguida, você pode criar sua tabela dinâmica da seguinte forma:

Filtro de relatório: nenhum

Rótulos de coluna: nenhum

Rótulos de linha: função, membro

Valores: MIN de semanas desde

Então:

• Remover campos totais desnecessários
• Edite as configurações do campo Membro e selecione Layout e Imprimir / Mostrar itens sem dados. Isso exibirá membros que nunca serviram uma função.
• Altere a ordem de classificação em Mínimo de semanas desde a menor para a maior.

Sua tabela de pivor deve ser semelhante à seguinte:

Role        Member          Min of Weeks since
Role 1      Member 4        
            Member 7        
            Member 3        
            Member 5        
            Member 6        
            Member 2        4
            Member 1        5
Role 2      Member 4        
            Member 7        
            Member 6        
            Member 1        
            Member 5        
            Member 3        4
            Member 2        5
Role 3      Member 6        
            Member 1        
            Member 2        
            Member 4        4
            Member 5        5
            Member 7        7
            Member 3        8
Role 4      Member 7        
            Member 1        
            Member 5        4
            Member 3        5
            Member 2        6
            Member 6        7
            Member 4        8
Role 5      Member 7        
            Member 1        
            Member 3        
            Member 2        
            Member 5        4
            Member 4        5
            Member 6        6

Testado no Excel 2010.

    

Answer 4

Uma abordagem ligeiramente diferente aqui, em três partes:

• Tabela de entrada de dados - Cada sessão, alguém entraria nos atores e nas funções. A tabela é ordenada por data (decrescente).
• Tabela de referência cruzada - cada nome com referência cruzada à data mais recente em que eles foram veiculados em cada função
• Próxima lista

É uma manutenção muito baixa. Depois de configurado, o único trabalho é inserir os três nomes por dia e estender o intervalo de datas conforme necessário.

Tabela de entrada de dados

Aqui está a tabela (A1: D10). Nota: Nenhuma fórmula aqui. A cada semana, insira três nomes com base na lista a seguir.

Date    Role1   Role2   Role3  
21-Jan            
20-Jan            
19-Jan  Alice   Chuck   Bruce  
18-Jan  Chuck   Bruce   Derek  
17-Jan  Chuck   Derek   Alice  
16-Jan  Emily   Bruce   Chuck  
15-Jan  Alice   Chuck   Emily  
14-Jan  Derek   Emily   Alice  
13-Jan  Alice   Bruce   Emily  

Tabela de referência cruzada

A tabela de referência cruzada (A14: D19) abaixo exibirá o dia mais recente em que cada nome executou cada função.

=IF(ISNA(MATCH($A15,B$2:B$10,0)),"",INDEX($A$2:$A$10,MATCH($A15,B$2:B$10,0)))

Name    Role1   Role2   Role3   Role4  
Alice   19-Jan          17-Jan  18-Jan  
Bruce           18-Jan  19-Jan  14-Jan  
Chuck   18-Jan  19-Jan  16-Jan  13-Jan  
Derek   14-Jan  17-Jan  18-Jan  19-Jan  
Emily   16-Jan  14-Jan  15-Jan  17-Jan  

Se todos os nomes tiverem desempenhado o papel pelo menos uma vez, ele fornecerá a data mais recente correspondente. Célula em branco se o nome nunca tiver desempenhado o papel

Lista seguinte acima

A seção Next Up (A12: D12) procura a data mais antiga na coluna de cada função e fornece o nome correspondente. Ele funciona localizando o número mínimo na coluna e retornando a linha. Células vazias (nunca executadas) são priorizadas.

=IF(ISNA(MATCH("",B$15:B$19,0)),INDEX($A$15:$A$19,MATCH(MIN(B$15:B$19),B$15:B$19,0)),INDEX($A$15:$A$19,MATCH("",B$15:B$19,0)))

Next-Up Role1   Role2   Role3  
Name    Bruce   Alice   Emily  

^{Todas as fórmulas podem ser arrastadas para baixo e para a direita. Se você deseja estender o período, insira linhas adicionais abaixo da linha 2 e as fórmulas continuarão a funcionar. Por exemplo, para estender até 31 de janeiro, selecione linhas 3:12 e insira, depois as células A13: A14 e arraste a alça até a célula A2.}