Função PROCV

Função PROCV

Iniciando nossas aulas sobre as funções de pesquisas e referências, entenderemos como as mesmas procedem isoladamente ou aninhadas. Abordaremos a Função PROCV nesta aula com intuito de elucidá-los sobre a sua importância nas atividades no Excel, inerentes a estoque, listagem de produtos e muito mais.

Vejamos o conceito da Função PROCV () segundo o suporte do Office:

Você pode usar a função PROCV para pesquisar a primeira coluna de um intervalo de células e, em seguida, retornar um valor de qualquer célula na mesma linha do intervalo.

Sintaxe: =PROCV(valor_procurado; matriz_tabela; núm_índice_coluna; [procurar_intervalo])

A função PROCV lhe solicita em valor_procurado o lugar (célula qualquer) para se digitar algo procurado em uma matriz_tabela que será uma tabela contendo as informações que deseje buscar. Se quiseres buscar um valor específico na tabela, terás que escolher um núm_índice_coluna que corresponde ao número que indica a posição da coluna a partir da primeira coluna (1). Na opção [procurar_intervalo] apenas se for o caso, você seleciona o intervalo vertical da procura, sendo então, mais específica. Vejamos a planilha "BaseDados" contendo a Lista de Produtos a seguir:

Lista de Produtos - Inicio

Lista de Produtos - Fim

Temos uma tabela com uma  lista de produtos que vai do produto 1 até 3904. A ideia será ao se digitar este número em valor_procurado, o mesmo irá buscar a descrição do produto, seu preço unitário e sua quantidade estocado; tudo baseado na posição da coluna (núm_índice_coluna). Mas para isso, devemos criar uma tabela em outra planilha, formatando-a preferencialmente conforme a figura a seguir.

Tabela para Pesquisa do Produto

Note que há em ID um espaço a frente (C3) onde será digitado o valor a ser procurado, gerando o resultado em "produto", "valor" e "em estoque". Para cada resultado usaremos a função PROCV alterando apenas o núm_índice_coluna. Vejamos como realizar estes procedimentos inicialmente em produtos (Figura 1):

Figura 1

O valor procurado será digitado em C3 e o segundo argumento (matriz_tabela) é a nossa tabela toda na planilha BaseDados. Para tal, vejamos as Figuras 2, 3 e 4.

Figura 2

Vá para a tabela presente na planilha BaseDados e na Lista de Produtos, selecione a primeira linha (como mostra a figura 3) e segure as teclas uma após a outra, Ctrl+Shift e pressione seta para baixo (↓). Teremos toda a tabela da planilha totalmente selecionada e o intervalo apresentado no argumento matriz_tabela (Figura 4)

Figura 3

Como resultado teremos:

Figura 4

Agora vamos para o terceiro argumento que se trata do núm_índice_coluna, já que temos uma tabela com 4 colunas selecionadas, devemos escolher a coluna correspondente à informação que queiramos quando escolher o ID; em nosso caso, será a descrição do produto, logo será o número 2 (Figura 5):

Figura 5

Não precisamos o último argumento nesse caso, o mesmo é opcional e apenas é aplicado quando necessitamos que apenas um intervalo na tabela seja procurado. Notemos que foi digitado o número 1 em C3, logo ao confirmar a função em C5, automaticamente teremos o resultado (Figura 6):

Figura 6

Nos demais itens ("Valor" e "Em estoque") iremos inserir a mesma fórmula, com a mesma referência de valor_procurado, mesma matriz_tabela, e somente mudaremos o valor de núm_índice_coluna que corresponderá à coluna de cada item na matriz_tabela. Vejamos suas correspondências (Figura 7):

Figura 7

Uma vez que, tenha repetido as funções para cada item e apenas substituído o núm_índice_coluna em cada função PROCV, iremos ter o seguinte resultado (Figura 8):

Figura 8

Note que o valor que atribuíres ao ID fará referência à tabela Lista de Produtos em BaseDados. Logo, você poderá fazer muito mais, uma que possa trazer informações separadas para trabalha-las de forma isolada. 

Disponibilizarei o arquivo trabalhado nesta aula clicando AQUI.

Espero que tenham gostado, até a próxima meus caros.

Apresentação Condicional de Dados

GRÁFICOS

APRESENTAÇÃO CONDICIONAL DE DADOS

Esta aula necessita dos conhecimentos:
Função E
Função SE
Operadores

Este é o primeiro gráfico apresentado em nossas aulas e bem útil quando queremos apresentar dados corporativos com qualidade no Excel. O mesmo, é muito utilizado na área de projetos para projeções econômicas, claro que de modo avançado, mas já que o intuito é desenvolver no aluno a capacidade de criar e, ao mesmo tempo, entender o gráfico; iremos de forma simples criá-lo com uma apresentação condicional.

No Excel, os gráficos possuem o papel de organização visual das informações disposta pelo usuário. Uma vez que essas informações estejam bem organizadas, o gráfico retornará de forma organizada e simples os mesmos. É importante afirmar que, o segredo está na organização dos dados.

Vamos primeiro criar os nossos dados que serão usados pelo nosso gráfico. Vejamos a planilha a seguir (Figura 1), nela apresentamos a estrutura das informações.

Figura 1

Notem que temos em nossa tabela três colunas em branco (Baixas Vendas, Vendas Equilibradas e Altas Vendas) e em baixo da tabela, há uma pequena tabela que conterá as variáveis responsáveis pelas condições.
Para o desenvolvimento do gráfico, vamos criar nossas condições. Vejamos a primeira condição conforme a Figura 2 digitada na célula D4.

Figura 2

Observem as referências. O teste_lógico (primeiro argumento) da Função SE é a Função E com os seguintes argumentos:

B4 maior ou igual a 0

B4 menor e igual a $D$19

Caso seja verdadeira (segundo argumento da função SE), mostrará o valor de B4; caso contrário manterá a célula vazia (terceiro argumento da função SE). O "$" significa que a linha e coluna ficaram fixas, ou seja, será uma referência absoluta. Já que essa condição se refere a "Baixas Vendas" atribuiremos um valor que, quando construirmos o gráfico, nos mostrará uma cor correspondente à condição. Agora vejamos a Figura 3 que apresenta a segunda condição digitada na célula E4.

Figura 3

Vemos que da mesma forma que a primeira condição (Baixas Vendas), assim é a segunda condição (Vendas Equilibradas). A diferença está nos argumentos da Função E que é o teste_lógico da Função SE nesta condição, com os seguintes argumentos:

B4 maior que $D$19

B4 menor e igual a $E$19

Se os testes lógicos da Função E forem satisfeitos, o segundo argumento da Função SE entrará em vigor mostrando o valor de B4, caso contrário a valor será vazio. Por último, vejamos a próxima condição que se refere a "Altas Vendas" digitada na célula F4 (Figura 4):

Figura 4

O primeiro argumento da Função SE em "Altas Vendas" nos traz o teste_lógico:

B4 maior e igual a $F$19

Como Altas Vendas é a maximização das vendas, o valor atribuído a célula F19 será o que a empresa considera como o auge das vendas em cada mês. Se B4 não satisfazer o teste_lógico, logo não será apresentado o valor.
Para continuarmos, devemos atribuir os valores à tabela menor, atribua esses valores (Figura 5):

Figura 5

Agora para cada célula que colocamos as funções (D4, E4 e F4) iremos aplicar o procedimento na (Figura 6):

Figura 6

Ao apontar para o canto da célula selecionada, o ponteiro do mouse torna-se uma cruz; clique duas vezes e a coluna se auto-preencherá. Uma vez que realizamos todos os passos até aqui, a nossa tabela se apresentará dessa forma (Figura 7):

Figura 7

Notem que os valores na coluna Lucro Bruto estão espalhados nas colunas Baixas Vendas, Vendas Equilibradas e Altas Vendas; se sua tabela estiver assim como apresentada, ocorreu tudo corretamente nas fórmulas de condição. Percebam que os valores compreendidos em Baixas Vendas são menores que R$ 60.000,00 (B.V), assim como os valores de Vendas Equilibradas que em nossas condições devem estar entre os valores maiores e iguais a R$ 60.000,00 e menores ou iguais a R$ 100.000,00 (V.E.). Por fim, temos nossa última condição em Altas Vendas que compreendem os valores maiores que R$ 100.000,00 (V.E. < A.V.).

Agora possuímos a estrutura para criação do gráfico, para tal, clique na aba INSERIR e em opções de gráficos selecione o tipo de gráfico 2D, (colunas agrupadas) conforme Figura 8:

Figura 8

Aparecerá um retângulo branco com a base do gráfico e inseriremos nossos dados nele. Para tal, clique com o botão direito do mouse sobre a base do gráfico e selecione a opção "Selecionar dados..." conforme a Figura 9:

Figura 9

Veremos então esta janela (Figura 10) e escolheremos a opção "Adicionar":

Figura 10

Em nossa próxima janela (Figura 11) clicaremos onde está marcado:

Figura 11

Então selecionaremos para o Nome da Série o título da nossa coluna Baixas Vendas conforme a Figura 12:

Figura 12

Retornaremos para a janela anterior clicando na parte marcada conforme a Figura 13:

Figura 13

Agora vamos adicionar os dados referente ao título Baixas Vendas, seguindo os procedimentos da Figura 14 e 15:

Figura 14

Selecione conforme a figura abaixo:

Figura 15

Clique no ponto marcado para retornar à janela Editar Série e clique em OK (Figura 16):

Figura 16

Retornaremos para a janela que nos permite adicionar dados no gráfico (Figura 17):

Figura 17

Nosso gráfico ficará conforme a Figura 18:

Figura 18

Todos os passos para inserir os dados de Baixas Vendas foram realizados, repita o mesmo procedimento para Vendas Equilibradas e Altas Vendas. Então teremos o seguinte gráfico (Figura 19):

Figura 19

Observando-o, percebemos que já lhe foi atribuído cores que diferenciam as vendas, mas ainda não está organizado; pois falta as legendas, títulos nos eixos e o título principal. Em nosso exemplo utilizo o Office 2013, no mesmo, os procedimentos são simples, vejamos (Figura 20):

Figura 20

Com as opções devidamente marcadas (Figura 21):

Figura 21

Teremos o seguinte gráfico (Figura 22):

Figura 22

Iremos renomear os títulos, ficando conforme a próxima imagem (Figura 23):

Figura 23

Vamos mudar a cor das Vendas para ficar melhor apresentável, atribuindo a cor vermelha para Baixas Vendas, amarela para Vendas Equilibradas e verde para Altas Vendas. Para realizar a mudança das cores, vamos seguir estes procedimentos (Figura 24 e 25):

Clique sobre a barra correspondente à venda que mudarás a cor.

Figura 24

Selecione a cor de preenchimento:

Figura 25

Faça o mesmo para as demais Vendas.

Vejamos como está nosso gráfico (Figura 26):

Figura 26

Notemos que os meses aparecem de forma numérica, para resolver a questão e apresentá-los de forma abreviada; siga os passos seguintes (Figura 27, 28 e 29):

Clique com o botão direito sobre o gráfico e escolha a opção "Selecionar Dados...", na janela que se abrir clique em "Editar":

Figura 27

Logo após aparecerá a janela de Rótulos do Eixo, como já vimos anteriormente, selecione os meses na tabela de dados:

Figura 28

Confirme com OK nas janelas que aparecerem. Como resultado teremos:

Figura 29

Finalizando nosso gráfico, vamos inserir os dados do Custo Total, mas iremos inserir com outro formato dos já presentes; em nosso exemplo, usaremos o gráfico de linhas. Vamos seguir os passos seguintes (Figura 30, 31 e 32)

Clique com o botão direito do mouse sobre qualquer barra que represente o Custo Total (Figura 30) e escolha a opção "Alterar Tipo de Gráfico de Série...":

Figura 30

Logo após, selecione as opções marcadas na Figura 31:

Figura 31

Já com o Custo Total apresentado graficamente em Estilo Linha, altere sua cor para azul conforme (Figura 32):

Figura 32

Agora, realize um teste simples, altere os dados em Lucro Bruto e veja automaticamente o gráfico alterar as cores de forma que você tenha uma visualização rápida da situação de vendas da empresa. 

Você pode modificar o gráfico formatando da forma como bem entender. Pratique, treine e aperfeiçoe as técnicas.

Modifique o valor no mês de junho que está acima de R$ 100.000,00 e coloque um valor abaixo de R$ 60.000,00, ficando dessa forma na cor vermelha representada pelas Baixas Vendas.

Como resultado, o mês de junho ficou na cor esperada.

Gerentes usam estes tipos de gráficos para ter controle do andamento das vendas, investindo em publicidade (caso as vendas estejam baixas), mantendo o ritmo de liderança entre os vendedores (para manter o equilíbrio de Vendas) ou quem sabe já preparando as devidas comissões para os vendedores (Altas Vendas). Tão somente em vendas como para controle de produção, planejamento em projetos e muito mais é válida essa aplicação de condição de dados em gráficos.
Segue a tabela de vendas e despesas: BAIXE AQUI

Espero que tenham gostado, e qualquer coisa é só comentar aqui no blog.

Até a próxima meus caros!

Condicionando NOTAS de ALUNOS

Condicionando NOTAS de ALUNOS

Está aula necessita dos conhecimentos sobre:
Operadores de Comparação
Função ÉCÉL.VAZIA

Função SE

Função E

Função OU

Como vimos, é bem simples a aplicação da função SE, e usaremos as funções E e OU aninhadas a ela. Em teste_lógico você determina sua condição, que praticamente pode ser qualquer coisa; em sequência atribuímos os dados como resultado, caso seja verdadeiro ou falso.

Vejamos a planilha (Figura 1) que contém as notas de 4 alunos e as condições (testes lógicos).

Figura 1

Possuímos quatro condições para definir a situação de cada aluno, propositalmente colocaremos uma situação para cada condição. Mas antes disso devemos explicar os títulos Média e Situação, no qual a média é calculada com base nas notas dos alunos em cada bimestre (matemática básica) e a situação será as condições (Aprovado, Final, Reposição e Reprovado). Agora com as notas devidamente preenchidas (Figura 2) temos:

Figura 2

Notemos que o aluno Sniper não possui a nota do 2º Bimestre e pré-analisando as médias, já sabemos pelas condições quem está reprovado, na final, aprovado e em reposição. Mas para ficar melhor, queremos que automaticamente na coluna com título "Situação" seja apresentada os resultados, para isso usaremos uma fórmula extensa (Figura 3) e contendo as funções SE, E, OU e ÉCÉL.VAZIA. Esta fórmula digitaremos na célula H5.

Figura 3

 Não nos preocupemos em analisar a fórmula toda de uma vez só, iremos destrinchar parte por parte para melhor compreendê-la. Vejamos o início da fórmula que inicia-se  pela função SE, então vamos ver os argumentos da mesma (Figura 4).

Figura 4

 A função OU é o teste_lógico inicial da função SE, como mostra a Figura 4.

Percebam que a função OU contem a função ÉCÉL.VAZIA como argumento de seus testes lógicos 1, 2, 3 e 4 (Figura 5, 6, 7 e 8).

Figura 5

Figura 6

Figura 7

Figura 8

Notem que as referências (C5, D5, E5 e F5) da função ÉCÉL.VAZIA são justamente as notas de cada bimestre. A função ÉCÉL.VAZIA irá ver se existe ou não uma nota nas células, e a função OU necessita que apenas um dos testes_lógicos seja verdadeiro para que vigore o segundo argumento ("Reposição") da função SE (Figura 9), caso contrário (se todas as células estiverem devidamente preenchidas), o valor_se_falso entrará em vigor (Figura 10).

Figura 9

Figura 10

Analisando o terceiro argumento da função SE presente na Figura 10, percebemos que há muitas outras funções SE's aninhadas. A compreensão do porquê de estarem aninhadas, se dá ao fato das condições expostas em nossa planilha. Observe-as (Figura 11):

Figura 11

Logo, se todas as células da linha 5 que correspondem as notas do 1º, 2º, 3º e 4º bimestre estiverem preenchidas nos apresentará uma média, e posteriormente a fórmula irá testar cada condição apresentada acima. A primeira (na figura 11, porém é a segunda na fórmula) é, se a média for igual ou acima (maior) que 7, estará aprovado (Figura 12):

Figura 12

Na prática, a primeira condição testa a ausência de notas que nos trará a situação "Reposição", em seguida temos a segunda condição que verifica se a média (G5) é superior ou igual a 7, que nos trará a situação de "Aprovado" como valor_se_verdadeiro e como valor_se_falso, uma nova função SE contendo a condição que verifica se a média (G5) é inferior ou igual a 6 (sublinhado de vermelho). E a última condição presente na última função SE (Figura 13) verifica se o valor da média em G5 é maior ou igual a 6 e menor ou igual a 7, ou seja, se o valor está entre 6 e 7, incluindo 6 e 7.

Figura 13

Logo, se o valor satisfazer o teste_lógico acima, o aluno estará na final. Caso contrário, se nenhuma das condições forem satisfeitas, o resultado será indefinido; contudo em nosso exemplo, para cada aluno, uma situação vigorou. Mas antes devemos copiar para as células abaixo a fórmula em H5. Vejamos como na Figura 14:

Figura 14

Para tal, aponte para o canto verde da célula selecionada e o formato do ponteiro do mouse mudará para uma cruz, agora dê um duplo clique e as demais células serão preenchidas pela fórmula que automaticamente apresentará as situações dos demais alunos (Figura 15).

Figura 15

Percebam que Sniper não possui a nota do 2º bimestre, sendo verificada pelo teste_lógico da função SE que inicia a fórmula, logo foi apresentada a situação "Reposição".

Apenas para efeito da aplicação das funções citadas que elaborei esta aula, porém muito mais ainda pode se fazer como aplicar uma opção de formatação condicional, ou usar a função ÉCÉL.VAZIA aninhada à média para não exibir a média caso houvesse ausência de alguma nota. O importante é que em mente sua deve ter aberto um leque de ideias provenientes da fórmula abordada nesta aula.

Até a próxima meus caros e qualquer dúvida é só comentar!