Voltar para: Excel avançado
Acompanhamento de vendas
Funcionalidades usadas nesta atividade:
- Tabelas.
- Intervalos nomeados.
- Função PROCV.
- Validação de dados.
- Intervalos dinâmicos.
- Função ÍNDICE.
- Função CONT.VALORES.
Vamos criar uma pasta de trabalho para acompanhamento das vendas de uma representação comercial de filtros d’água. Para melhor organização vamos dividir os dados em várias planilhas: vendas, clientes, produtos e vendedores. Essa separação previne erros de digitação, duplicação de dados e agiliza o preenchimento. Os dados iniciais são estes:
Vendas
Clientes
Vendedores
Produtos
Conversão para tabela
Nossa primeira ação será converter os intervalos de dados em tabelas do Excel. Um intervalo convertido em tabelas oferece algumas vantagens como:
- Formatação automática.
- Expansão rápida. Basta selecionar a última célula à direita e teclar TAB para criar uma linha nova.
- Cópia automática de funções para todas as células da mesma coluna.
- Expansão automática de intervalos em funções quando acrescentamos linhas à tabela.
- Expansão automática de séries quando acrescentamos linhas à tabela.
Para converter um intervalo em tabela basta clicar em alguma célula do intervalo e na aba Inserir escolher Tabela.
Nomeando as tabelas
Para facilitar o entendimento da lógica da pasta, vamos dar nomes às tabelas criadas.
- Na aba Fórmulas, clique em Gerenciador de Nomes.
- Observe que as tabelas criadas estão listadas com nomes genéricos. Edite cada uma delas e dê os seguintes nomes: TBLclientes, TBLprodutos e TBLvendedores.
Dar nomes para intervalos de dados simplifica o trabalho com planilhas, pois em vez de usarmos referências genéricas usamos nomes mais naturais.
Colunas calculadas
Começamos com as informações digitadas e agora vamos para as informações calculadas na planilha Vendas.
- Acrescente novas colunas á direita da tabela. Elas serão incorporadas à tabela. Nomeie como Produto, Cliente, Vendedor e Valor.
- Na coluna Produto insira uma função PROCV. O valor procurado é o ID do produto na linha correspondente. A matriz é a TBLprodutos e o valor exibido virá da coluna 2. A correspondência deve ser exata e por isso, digitamos FALSO no último argumento.
- Repita o procedimento de forma similar para as colunas Cliente e Vendedor.
- Na coluna do valor temos que inserir uma função PROCV para trazer o valor unitário da TBLprodutos. Além disso, temos que multiplicar o valor unitário pela quantidade vendida.
Esse método tem suas vantagens. Se adicionarmos novos produtos na TBLprodutos não será preciso ajustar a função PROCV correspondente, pois usamos um intervalo nomeado (TBLprodutos) que é dinâmico.
Validação de dados
As colunas ID produto, ID cliente e ID vendedor devem ser preenchidas apenas com valores cadastrados nas planilhas Produtos, Clientes e Vendedores respectivamente. Assim evitamos uma série de erros que costumam acontecer quando deixamos a digitação livre.
Para controlar o que é digitado faremos validação das células. O método básico para isso é selecionar a célula que será validada, clicar em Dados/Validação de dados e optar pela validação por lista. Em seguida, informamos qual é o intervalo de células que deve ser considerado para montar a lista. O problema desse método é que o intervalo selecionado é estático. Se acrescentarmos novos itens à lista, isso não repercute na validação. Temos que criar intervalos dinâmicos.
Vamos começar criando três intervalos nomeados:
- No Gerenciados de nomes crie um intervalo nomeado como IDprodutos. No campo refere-se a selecione inicialmente as células com os ID da TBLprodutos.
- Repita o procedimento de forma similar e crie IDclientes e IDvendedores.
Agora você pode fazer a validação das células.
- Selecione uma célula da coluna ID produto.
- Na aba Dados, clique em Validação de dados e escolha validação por lista.
- Na Fonte digite =IDprodutos.
- Repita o procedimento de forma similar para validar as colunas ID cliente e ID vendedor.
Pronto. A validação já está acontecendo, mas ainda não está dinâmica.
Para deixar a lista de itens dinâmica vamos retornar aos intervalos nomeados e alterar o campo refere-se a.
No intervalo IDprodutos digite:
=Produtos!$A$2:ÍNDICE(Produtos!$A:$A;CONT.VALORES(Produtos!$A:$A);1)
No intervalo IDclientes digite:
=Clientes!$A$2:ÍNDICE(Clientes!$A:$A;CONT.VALORES(Clientes!$A:A);1)
No intervalo IDvendedores digite:
=Vendedores!$A$2:ÍNDICE(Vendedores!$A:$A;CONT.VALORES(Vendedores!$A:A);1)
Pronto. Essa ação deixará as listas de validação dinâmicas. Você pode testar se deu certo acrescentando linhas às tabelas.
Explicando a criação do intervalo dinâmico
Inicialmente tínhamos um intervalo estático:
=Produtos!$A$2:$A$7
Esse intervalo pode mudar de tamanho, mas sempre começará na célula $A$2. O que varia é a célula $A$7.
Então, vamos substituir $A$7 por uma função ÍNDICE.
=Vendedores!$A$2:ÍNDICE(Vendedores!$A:$A;7;1)
A função ÍNDICE é uma função intermediária que retorna um endereço de célula. Você pode ler a função acima assim: Procure no intervalo $A:$A a célula que está na 7a. linha, primeira coluna.
Até agora, trocamos seis por meia dúzia, pois a função ÍNDICE retorna o endereço $A$7 que já tínhamos antes da substituição.
A última etapa é dar dinamismo ao endereço devolvido pela função ÍNDICE. Vamos inserir uma função CONT.VALORES no argumento que define o número da linha.
=Produtos!$A$2:ÍNDICE(Produtos!$A:$A;CONT.VALORES(Produtos!$A:$A);1)
A função CONT.VALORES nos devolve quantas células estão preenchidas na coluna A inteira. Assim se a lista aumenta a contagem acompanha e o intervalo de dados se expande.