Tecnologia da Informação

1
Tecnologia da Informação
Prof. Mário Henrique de Souza Pardo
Resumo – Aula 4
MS-Excel
Aplicando funções às suas fórmulas de Excel (continuação)
Serão vistas, nesta aula as funções de busca e referência PROCV e PROCH. Além
dessas, será visto como efetuar a vinculação de dados entre planilhas e o recurso de
formatação condicional.
Função PROCV – procura vertical e referência a células indicadas.
Sintaxe:
=PROCV ( condição ; matriz ; índice ; ordem_classificação )
Exemplo:
=PROCV (C5 ; F9:H29 ; 2 ; 1 ) - C5 é a célula que possui o conteúdo a
ser procurado, F9:H29 é o intervalo de células onde deverá ser realizada a busca, o valor 2 é o
índice da coluna a ser adotada para o retorno caso a função encontre um valor que combine
com o valor da condição e o valor 1 significa VERDADEIRO para a ordem de classificação
de A e Z para a coluna 2.
Função PROCH – procura horizontal e referência a células localizadas abaixo.
Sintaxe:
=PROCH ( condição ; matriz ; índice ; ordem_classificação )
Exemplo:
=PROCH(B9;Lista_De_ProdutosV.B1:K4;3;1) - B9 é a célula que
possui o conteúdo a ser procurado horizontalmente, Lista_De_ProdutosV.B1:K4 é o
intervalo de células onde deverá ser realizada a busca, o valor 3 é o índice da linha a ser
adotada para o retorno caso a função encontre um valor que combine com o valor da condição
e o valor 1 significa VERDADEIRO para a ordem de classificação de A e Z para a linha 3.
Vinculação de Dados entre Planilhas
Pode-se vincular dados entre planilhas. Isso é bastante interessante em ocasiões onde o
desenvolvedor de planilha precisa compartilhar dados que encontram-se em planilhas
distintas.
No
exemplo
da
fórmula
de
exemplo
supracitada:
=PROCH(B9;Lista_De_ProdutosV.B1:K4;3;1) o valor Lista_De_ProdutosV.B1:K4 é uma
referência ao intervalo de células B1:K4 da planilha Lista_De_Produtos, esse valor poderia
ser incluído em uma fórmula de qualquer outra planilha que se queira.
Formatação Condicional
Todos os usuários do Excel já utilizaram alguma vez as opções de formatação para
ajustar as características relacionadas ao aspecto de uma célula e dos dados que ela contém,
como fonte, cor ou estilo de alinhamento. Essas características são fixas: se usarmos o estilo
em negrito em um texto, esse texto continuará em negrito até voltarmos a entrar no menu para
estabelecer outro estilo.
2
Em alguns casos, porém, seria mais interessante que o aspecto de uma célula se
modificasse automaticamente de acordo com certas condições. Por exemplo, queremos que
uma quantia seja assinalada em vermelho se superar certo valor limite, ou que a identificação
de uma fatura se destaque se for mais velha do que uma data limite ou ainda que um fundo
colorido assinale claramente o máximo valor de uma lista. Em outras palavras, que o formato
de uma célula seja dinâmico para localizar rapidamente certos valores com uma simples
olhada.
O Excel pode realizar estas mudanças automáticas com a opção de formatação
condicional. A seguir, vamos ver o uso desta opção para alguns exemplos típicos e também
para resolver determinados problemas especiais, tais como a identificação de dados repetidos.
É muito fácil entender a opção de formatação condicional e, certamente, você conseguirá
adaptar os exemplos aos problemas concretos do seu dia-a-dia.
1. Condições simples
Vamos supor que você tenha uma lista de empregados, com os valores vendidos ao
longo do mês e quer destacar, por exemplo, em vermelho, as vendas superiores a 2.000 reais.
Isto é, a condição será: se o valor da célula for maior que 2000, aplicar um padrão vermelho.
Comece selecionando o grupo de células nas quais quer aplicar o formato. Em
seguida, vá ao menu Formatar/Formatação Condicional. Aparecerá um quadro com diferentes
listas de rolagem, onde você terá de indicar a condição que quer associar a esse determinado
formato. No primeiro campo, deixe a opção O Valor da Célula É. No segundo, abra as opções
e indique Maior Do Que. No terceiro, escreva o valor escolhido: 2000.
Assim, você terá indicado a condição associada ao formato. Agora, vai indicar o
formato propriamente dito. Para isso, clique no botão Formatar. Aparecerá um conjunto de
guias parecido com o conjunto Formatar/Células. Na guia Padrões, selecione a cor vermelha.
Clique em OK (para o formato) e de novo em OK ( para a condição associada). A planilha
mostrará, sobre um fundo vermelho, os valores maiores que 2.000 reias. Este formato é
dinâmico: se mudar algum valor para mais ou menos de 2.000, mudará, conseqüentemente,
seu formato de exibição. Com isso, você economiza bastante tempo.
3
2. Mais de uma condição
A opção de formatação condicional permite indicar até três condições com seus
respectivos formatos associados. Vamos imaginar que você tem uma lista de faturas com suas
correspondentes datas de vencimento. Neste caso, você quer destacar com dois formatos
diferentes as faturas que vencem hoje e as que já estão vencidas.
Comece selecionando o conjunto de células que contenham as datas de vencimento.
Como você fez anteriormente, vá até as opções Formatar/Formatação Condicional. Na
primeira lista de rolagem, selecione a opção O Valor da Célula É. Na segunda, indique Igual
A. Na terceira, escreva a função =HOJE(). Esta será a condição que identifica as faturas que
vencem hoje. A seguir, clique em Formatar e selecione o formato escolhido para estas faturas.
Por exemplo, texto em negrito. Clique em OK para voltar ao primeiro quadro.
Para indicar uma segunda condição e seu respectivo formato, clique em Adicionar. O
quadro será ampliado, mostrando um segundo jogo de opções em que você indicará,
respectivamente, O Valor da Célula É, Menor Do Que e =HOJE(). Efetivamente, as faturas
vencidas são aquelas cuja data de vencimento são anteriores à data de hoje. Clicando em
Formatar, você indicará o formato associado a esta segunda condição - por exemplo,
preenchimento vermelho, na ficha Padrões, como vimos anteriomente. Clicando duas vezes
em OK (uma para o formato e outra para as condições) está pronta a planilha de cálculo que
destacará as faturas como desejado.
3. O problema das repetições
O formato condicional pode ser usado para assinalar os valores repetidos em uma lista.
Suponhamos que o conjunto de células H2:H11 de uma planilha contenha uma lista de
códigos, números de RG, CPF ou qualquer outro valor que não deva ser repetido na lista e
você quer que os eventuais números repetidos sejam assinalados automaticamente com o
fundo vermelho.
Selecione o conjunto de células. É importante fazer a seleção de cima a baixo. Vá
depois às opções Formatar/Formatação Condicional. Na primeira lista de rolagem, indique a
opção A Fórmula É. Isto deixará somente uma segunda opção no quadro. Aí, escreva a função
=CONT.SE(H$2:H$11;H2)>1. Isto é, que o valor da fileira se encontre mais de uma vez em
toda a lista. Esta condição é satisfeita com os valores repetidos.
Para indicar o formato que destacará as entradas repetidas, clique em Formatar e, na
paleta de cores da guia Padrões, selecione a cor vermelha. Clique duas vezes em OK para
aceitar o formato e a condição. Se houver algum número repetido na lista, aparecerá sobre o
fundo vermelho.
4
4. Formatos para toda uma coluna
Suponhamos que na lista de vendedores e valores, você queira destacar o vendedor
que fez as maiores vendas. Não quer destacar somente o valor, mas também toda a linha com
o valor e o nome da pessoa. Selecionamos toda a lista, excluindo os títulos. Suponhamos que
esta categoria seja: A2:H11, lembrando que A2 e H11 correspondem às células usadas na
planilha. Vá novamente para as opções Formatar/ Formatação Condicional. Na primeira lista
de rolagem, selecione a opção A Fórmula É. Na segunda, escreva a fórmula =
$H2=MÁXIMO($H$2:$H$11). É preciso fixar as referências com símbolos $ para que as
comparações sejam feitas corretamente.
O primeiro símbolo $ (em $H2) faz com que a condição leve em conta o valor da
coluna H, embora a categoria selecionada inclua também a coluna A. Não se fixa a referência
à linha para que a condição se aplique a cada vendedor com seu respectivo valor de vendas.
Na função MÁXIMO fixam-se todas as referências para que todos os valores sejam
comparados com o mesmo valor máximo. Em resumo, a condição pede que o valor da coluna
H coincida com o máximo valor desta coluna.
Agora, indique o formato que destacará o máximo - por exemplo, preenchimento
vermelho. Clique em Formatar e, na paleta de cores da gua Padrões, clique em vermelho.
Como foi feito anteriormente, clique duas vezes em OK e volte à planilha. onde a linha
correspondente ao responsável pelas maiores vendas aparecerá destacado sobre fundo
vermelho. Com esta técnica, você poderá assinalar toda a linha para os valores que estejam de
acordo com certa condição, em qualquer um dos exemplos anteriores.
5. Eliminar um formato condicional
Os formatos condicionais podem ser eliminados da categoria onde foram aplicados.
Para isso, selecione a categoria onde quer eliminar o formato. Abra o menu
Formatar/Formatação Condicional e clique em Excluir.
Aparecerá um quadro com três opções correspondentes às três condições, no máximo,
que você pode associar a estes formatos. Marque o número da condição cujo formato queira
eliminar e clique duas vezes em OK.
5
EXERCÍCIOS PROPOSTOS
1.) Crie uma planilha que integre dados entre uma tabela de preços e uma tabela que
representará o pedido de venda de um cliente.
A tabela de preços de produtos do Supermercado São João segue abaixo:
E a tabela que representa o pedido de venda está na próxima figura. Para a construção
do exercício com sucesso, lembre-se de utilizar as funções PROCV e a vinculação de dados
entre planilhas.
6
Tente também elabora a tabela de preços no seguinte formato:
Tente então criar uma nova versão do pedido de venda utilizando-se dessa nova tabela
de preços como tabela base. Para tal, lembre-se de usar a função PROCH.
OBS: Em algumas situações será interessante combinar as funções PROCV e PROCH
com outras funções, tal como a função SE( ), para um melhor funcionamento das planilhas.