Comercialização e Desenvolvimento de Sistemas em Planilhas Excel
DICAS DE PROGRAMAÇÃO
Algumas funções são muitos utilizados na programação ou na automatização das planilhas Excel.
O conhecimento do funcionamento destas funções facilita muito a escolha de sua utilização, por ocasião de uma programação, no desenvolvimento de qualquer planilha Excel.
Abaixo, descrevemos o funcionamento de algumas das principais funções disponíveis nas planilhas Excel.
Função DESLOC(),
Clique no botão acima e veja um vídeo sobre uma das utilidades da função DESLOC()
A função Desloc() é muito útil quando você necessita criar nomes para áreas dinâmicas, ou seja, estão sujeitas a aumentos ou diminuições.
Na figura acima, temos um exemplo real, onde existe uma área nomeada como "Motivo Demissão", sendo este nome utilizado em diversos pontos da planilha de Banco de Horas.
Se você não utiliza-se nome, teria que atualizar. manualmente, todas as formulas que utilizassem esta área, sempre que fosse incluído ou excluído uma linha desta área.
A função DESLOC(), automatiza a área, aumentanto ou diminuindo a área de acordo com as linhas existentes na área.
Como funciona esta função? (Clique na imagem ao lado e veja a tela expandida, com a formula descrita abaixo:
A formula básica é, baseada no exemplo ao lado :=desloc($S$2;0;0;cont.valores($S:$S)-1;1
Explicando :
-
$S$2=é o endereço da primeira linha da coluna onde se encontram nossos dados, no nosso caso celula S2
-
0;0=o deslocamento será na mesma linha e na mesma coluna
-
Cont.Valores($S:$S)-1;1. é uma outra função do excel, que trabalha em conjunto com o desloc. Cont.valores, como o próprio nome mesmo diz, conta as celulas preenchidas / diferentes de branco, da coluna "S" menos uma linha, pois a linha 1 está preenchida com o nome da área (Motivo Demissão)
-
;1 significa que o deslocamento só atinge 1 unica coluna
-
Com a utilização da função DESLOC(), você não poderá mais utilizar a coluna S, para escrever qualquer coisa, mesmo que esteja muitas linhas abaixo, pois a função CONT.VALORES, rastreia todas as linhas da coluna, contando todas as que são diferentes de vazio. Neste caso, haver[a a necessidade de se alterar o (-1) da formula pelo numero de celulas não vazias, na coluna fora da área de pesquisa.
-
É importante também que os endereços sejam travados, absolutos, tanto nas colunas como nas linhas, com $. Em caso dos endereços serem relativos, a formula muda em função do posicionamento do cursor, e acaba dando erro.
-
A Função SE(E()) ou SE(OU())
A função SE() pode ser aninhada com as
funçôes E() ou OU(), o que facilitam as pesquisas
de lógica, quando se precisa comparar 2 ou
mais condiçoes, para obter um resultado.
Todas os 2 conjuntos de funçôes acima,
trabalham com a seguinte formatação:
=SE(OU(CONDIÇÃO-01="RESULTADO CONDIÇÃO-01";CONDIÇÃO-02=RESULTADO CONDIÇÃO-02");VALOR SE VERDADEIRO;VALOR SE FALSO)
-
Como sempre, qualquer texto, deve ser indicado entre aspas duplas ("")
-
Comparação de datas, devem ser informadas no formato ("dd/mm/aa")
-
Comparação com simbolos de maior e menor, devem ser colocados entre aspas duplas "<" ou ">" ou "<>" ou "<=" ou ">=" e depois o valor a ser pesquisado.
Nos exemplos, hipotéticos, ao lado, temos um exemplo da utilização da função SE(), encadeada com a função E().
A descrição analítica da função, seria:
Se, e, a média de notas dos 4 bimestres for menor que 6 e a nota máxima necessária. na prova final for maior que 10, o aluno está reprovado, (condição verdadeira do primeiro conjunto SE.
A condição falsa é um segundo SE(), que é: Se a média for maior ou igual a 6 o aluno está APROVADO (condição verdadeira do segundo SE(), e se esta condição for falsa, o aluno está REPROVADO, pois como a média dele foi menor que 6 e a nota final que ele precisaria seria maior que 10, impossível de tirar, ele está reprovado.
A formula da linha 5 é:
=SE(E(F5<6;G5<=10);"Recuperação";SE(F5>=6;"APROVADO";"REPROVADO"))
Clique nas fotos, e veja-as expandidas, com as formulas acima explicadas.
A variação de cores das palavras REPROVADO, APROVADO e RECUPERAÇÃO, são determinadas pelas formatações condicionais, conforme as formulas constantes na primeira tela.
Função PROCV()
A função PROCV(), é uma função de procura
Sua configuração básica é:
=procv(valor procurado, local da procura, quantidade de colunas, verdadeiro ou falso)
A ultima condição (Verdadeiro ou falso) é uma condição lógica. Usa-se VERDADEIRO se a procura for por um valor aproximado, e FALSO se a procura for por um valor exato.,
Na imagem ao lado, dando continuidade ao exemplo acima, você tem os nomes dos alunos e necessita achar as médias e a situação final de cada um. Clque na imagem e veja-a expandida com a formula comentada abaixo
São formulas colocadas nas colunas "B" e "C", e a formula digitada na linha 11 é:
=PROCV($A11;$A$2:$H$8;6;FALSO) para a coluna "B" e =PROCV($A11;$A$2:$H$8;8;FALSO) na coluna "C".
$A11 é o valor procurado, no nosso caso o ANTONIO
$A$2:$H$8 é o local da procura, no nosso caso, a tabela acima, da célula A2 à celula H8
6 na primeira formula e 8 na segunda formula são as colunas onde estão os dados procurados, contados da coluna inicial do local da procura, "A" no nosso caso
FALSO, procuramos os valores exatos, no caso a média 8,3 e APROVADO.
Repare que na linha do JOÃO, aparece nas duas colunas #N/D. Repare que no local da procura, o nome JOAO está grafado sem o til, e isso é suficiente para o excel não reconhecer as duas celulas como iguais, retornando #N/D (Não disponível). Lembre que o valor procurado e o nome constante do local da procura TEM DE SEREM EXATAMENTE IGUAIS.