Atividade 14-B

Acompanhamento de vendas.

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

acompanhamento de vendas

Clientes

acompanhamento de vendas

Vendedores

acompanhamento de vendas

Produtos

acompanhamento de vendas

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.

  1. Na aba Fórmulas, clique em Gerenciador de Nomes.
  2. 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.

  1. Acrescente novas colunas á direita da tabela. Elas serão incorporadas à tabela. Nomeie como Produto, Cliente, Vendedor e Valor.
  2. 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.
  3. Repita o procedimento de forma similar para as colunas Cliente e Vendedor.
  4. 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:

  1. 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.
  2. Repita o procedimento de forma similar e crie IDclientes e IDvendedores.

acompanhamento de vendas

Agora você pode fazer a validação das células.

  1. Selecione uma célula da coluna ID produto.
  2. Na aba Dados, clique em Validação de dados e escolha validação por lista.
  3. Na Fonte digite =IDprodutos.
  4. 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.

Autor: Radamés

Engenheiro curitibano pela UFPR, professor e produtor de conteúdos e ferramentas educacionais para a Internet.

Sua opinião me interessa