FUNÇÕES NO EXCEL


ANUNCIOS

DEPOIMENTOS

Ver Depoimentos Numa Nova Janela...


Índice

 

 

 

O que é uma Função

Funções são rotinas que têm como objetivo executar um conjunto de instruções e produzir um valor final.

São definidas por:

Recorde-se como exemplo a função SUM , esta recebe por parâmetro um conjunto de valores que se pretendem somar, sabe-se que o resultado da aplicação dessa função ao conjunto de valores será o respectivo somatório.

 

Funções do Excel

O Excel dispõe de um conjunto variado de funções (de estatística, de data e hora, de matemática e trigonometria, de finanças, de pesquisa e referência, de bases de dados, de texto, de lógica, e de informação) pré-programadas que se podem utilizar nas células da worksheet para executar os mais diversos cálculos.

Contudo, existem funções que o Excel não contempla e que por vezes os utilizadores gostariam de aplicar para a realização dos seus cálculos usuais. Neste contexto, está previsto o desenvolvimento de funções personalizadas recorrendo a uma codificação das mesmas numa linguagem que o computador possa entender e executar - Visual Basic for Applications (VBA).

 

Como Criar Funções

  1. Defina o objectivo da função, os parâmetros e resultado
  2. Escreva (em linguagem normal) os passos que essa função deve executar para, com base nos parâmetros produzir o valor final. Nesta etapa está a definir o algoritmo.
  3. Traduza o seu algoritmo em VBA. Para escrever uma função em VBA deve obedecer à seguinte sintaxe:

Function <Nome da Função> ( <parametro1>, <parametro2>,…)

<Nome da Função> = <Valor ou Expressão>

End Function

  • A definição começa com a palavra-chave Function e termina com as palavras End Function. Para definir o valor produzido pela uma função basta no seu interior, atribuir ao nome da função um determinado valor ou expressão.
  •  

    Aplicações Práticas

    Problema: Fazer uma função que calcule o valor com IVA.

    a)Multiplicar o Valor Ilíquido pela Taxa de IVA

    b)Somar ao Valor Ilíquido o valor calculado no passo anterior. Assim o valor do IVA deve ser atribuído a uma variável, no passo 2.1, para utilização posterior.

     

     

     

    Problema: Fazer uma função que gere valores aleatórios num intervalo real definido por dois limites

     

    a)Utilizar a função Rnd para produzir números aleatórios (entre 0 e 1)

    b)Somar o Limite Inferior com o resultado da alínea anterior multiplicado pela diferença entre o Limite Superior e o Limite Inferior

     

     

     

     

     

     

     

    Onde Definir a Função

    A função tem de ser definida (escrita) em linguagem VBA num sitio apropriado -Editor de Visual Basic for Applications. Para aceder ao editor escolha a seguinte sequência de opções do menu: Ferramentas / Macro / Editor de Visual Basic

     

     

     

    Project Explorer

    Se a janela não estiver visível ative-a através do Exibir / Explorer do Objeto ou utilizando o botão ou combinações de letras associadas a esta tarefa.

    Nesta janela poderá visualizar a hierarquia dos projetos de Visual Basic for Applications ativos nesse momento.

    Entre eles deverá encontrar um cujo nome corresponde ao nome do EstaPasta_de_trabalho do Excel com que está a trabalhar e.g. VBAProject (Pasta1). É dentro deste projeto que deverá trabalhar para que todas as funcionalidades que implemente estejam nele activas sempre que necessário.

    Assim se fizer o desdobramento do seu VBAProject, encontrará um folder cuja designação é Microsoft Excel Objects, e um outro designado Modulos (se este não aparecer significa que o seu projecto ainda não possui qualquer função implementada. Para criar este folder deverá fazer: Inserir / Modulo).

    Certifique-se, antes de começar a criar a função, que o está a fazer dentro dos modules, caso contrário elas não funcionarão como pretende.

     

     

     

     

    Trabalhar no Editor de VBA – Criar uma Função

     

    Aplicação Prática

    Problema: Criar uma função que calcule o IVA

    1. Parâmetros / Input: Valor Ilíquido e Taxa de IVA
    2. Passos a executar:

    a)Multiplicar o Valor Ilíquido pela Taxa de IVA

    b)Para criar a função no Editor de VBA:

     

    Uma vez seleccionado o módulo onde pretende construir a função poderá:

  • Nesta caixa de diálogo deverá indicar o tipo de rotina a criar – Function e o nome que pretende dar à função. Automaticamente o Visual Basic for Applications criará a estrutura da rotina, que neste caso ficaria:

    Function IVA( )

    End Function

  •  

    Depois bastará escrever a expressão que produzirá o valor final, entre estas duas linhas de código, como mostra a figura seguinte

     

     

     

     

    Execução de uma Função dentro de uma célula

    Uma vez que uma função produz um valor ela poderá ser executada dentro de uma célula numa WorkSheet, à semelhança de qualquer uma outra função do Excel

     

     

    Posicione-se na célula onde pretende inserir a função

    1. Inserir / Função
    2. Selecione a categoria Definida pelo Usuário– repare que aparece listada a função que acabou de criar IVA
    3. Clique em OK e de imediato uma janela de ajuda virá auxiliar a utilização da sua função

     

     

    1. Introduza os parâmetros e clique em OK

     

     

     

    Em suma:

    Qualquer função poderá ser utilizada numa WorkSheet do Excel, sendo a sua utilização semelhante a qualquer outra função: numa célula

    = <nome da função> (<Param_1> , <Param_2>)

     

     

     

     

     

    Utilização de variáveis

    As variáveis constituem repositórios temporários de valores, podendo ser utilizadas para diversos fins.Quando se pretende atribuir valores a variáveis dever-se-á indicar o nome da variável, o operador "=" e o valor que se pretende que a variável armazene.

  • <Nome_Variável> = <Valor>
  • O nome da variável representa o conteúdo da mesma, i.e., sempre que mencionar o nome da variável é o seu conteúdo que será considerado.

     

    Tipos de variáveis

    O tipo de variável está associado ao género de informação que esta tem hipótese de armazenar:

    Boolean – 2 bytes – Permite armazenar valores Boolenaos – True ou False

    Byte – 1 Byte – permite armazenar números sem sinal entre 0 e 255

    Currency - 8 bytes – permite armazenar moeda

    Date – 8 Bytes – permite armazenar datas

  • Double – 8 bytes – permite armazenar um real desde –
  • 1.79769313486232E308 até -4.94065645841247E-324 para valores negativos, e desde 1.79769313486232E308 até 4.94065645841247E-324 para valores positivos.
  • Single – 4 bytes – permite armazenar um real desde -3.402823E38 até

    • -1.4011298E-45, para valores negativos e desde 3.402823E38 até 1.4011298E-45, para valores positivos
  • Integer – 2 bytes - permite armazenar números inteiros entre -32.768 e 32767

    Long – 4 bytes – permite armazenar números inteiros entre -2 147 483 648 e

    2 147 483 648

    Object – 4 bytes – utilizado para fazer referência a um objecto do Excel

    String – 1 byte por caractere – permite armazenar conjuntos de caracteres

    Variant – 16 bytes - permite armazenar qualquer tipo de dados

    User-Defined – permite armazenar valores de tipos diferentes

     

    Declaração de Variáveis

    As variáveis que utilizar dentro de uma função deverão por norma ser declaradas i.e. definidas, sempre que uma variável for utilizada sem ser definida o VBA considera-a do tipo Variant. A definição é feita da seguinte forma:

  • Dim Numero As Integer
  • Onde:

    Dim – Palavra chave que indica uma declaração de variáveis (abreviatura de dimensão)

    Numero - nome da variável a utilizar

    As – palavra chave utilizada para separar o nome da variável do tipo de dados

    Integer – tipo de dados atribuído à variável

     

    Estruturas de Controlo

    O VBA disponibiliza algumas estruturas que pode utilizar para controlar o decurso da execução da rotina. Estas estruturas dão ao programador um poder enorme para construir funções bastante complexas e flexíveis.

     

    VBA Control  
    If -Then - Else Testa uma condição e executa um determinado conjunto de instruções consoante o resultado dessa avaliação
    For – Next Executa uma determinada tarefa um determinado número de vezes.
    While-Wend Executa uma determinada tarefa enquanto que uma determinada condição permaneça verdadeira, i.e., com o valor True.
    Do – Loop Executa uma determinada tarefa enquanto que a avaliação de uma condição permaneça True ou então até que seja True.
    Select - Case Selecciona um dos segmentos de código a processar mediante a avaliação consecutiva de condições.
    For – Each – Next Realiza uma determinada tarefa repetitiva em cada objecto de uma colecção ou em cada item de um array.

     

     

    If-Then-Else

    Recorrendo à função IF do Excel, recorde:

    =IF( <condição>, <se condição verdadeira>, <se condição falsa> )

    A função IF tinha o seguinte comportamento:

    1- Avalia a condição, que deverá ser uma expressão booleana colocada como primeiro parâmetro;

    2-Se a condição for verdadeira, então (then) realiza as operações colocadas no segundo parâmetro;

    3- Caso contrário (else), realiza as operações que formam o terceiro parâmetro

     

    A estrutura IF do VBA tem o mesmo tipo de funcionamento, o que difere é a sintaxe.

    Assim:

    If <Condição> Then

    <se condição verdadeira>

    [ Else

    <se condição falsa> ]

    End If

    A palavra Else é opcional sendo que no caso de ser omitida, a avaliação negativa da condição implica uma saída automática da Instrução If.

     

    Aplicação Prática

    Problema: Determinar se um número é par

    a)Nome: É_Par

    b)Parâmetros: número que vai ser avaliado

     

     

    a)Dividir o número por dois e apurar o resto da divisão

     

    1. Avaliar o resto da divisão:

    a)Se for igual a zero, então o número é par e a função assume o valor True

    b)Senão, o valor é impar e a função assume o valor False

     

     

    Interpretação

    Dim resto As Double Declaração da variável resto, onde é guardado o resto da divisão do número por dois
    Resto = Numero Mod 2 Mod é uma função que calcula o resto da divisão do Numero por 2 (neste caso). O valor calculado é atribuído à variável resto.
    If Palavra chave que indica o inicio de uma instrução If-Then-Else
    Resto = 0 A condição a testar. Serve para determinar a sequência a dar à execução da rotina. Da avaliação desta condição pode-se obter um de dois valores True ou False, sendo que depende deste resultado o caminho a prosseguir. Se for True executará as instruções que seguirem a palavra-chave Then até encontrar a palavra chave Else, não executando mais nada dentro do IF, caso contrário executará o código que se seguir à palavra chave Else até ao End If.
    Then Palavra chave que determina o fim da condição teste. Todas as instruções que têm inicio nesta palavra-chave até à palavra-chave Else serão executadas se a condição for verdadeira.
    É_Par = True Instrução a executar se a condição for verdadeira.
    Else Palavra-chave que determina o terminus de execução das instruções quando o resultado da avaliação for True, e que determina o inicio das instruções a executar se o resultado da condição for False.
    É_Par = False Instrução a executar se a condição for falsa.
    End If Palavra-chave que indica o fim do controlo de If-Then-Else e como tal onde se deve retomar as instruções para prosseguir a execução do procedimento.

     

     

    Aplicação Prática

    Problema: Crie a função Mod para usar nas células do Excel.

     

    a)Nome: Resto_Divisão

    b)Parâmetros: Dividendo e Divisor

     

    a)Calcular o quociente da divisão (Dividendo / Divisor) na forma inteira

    b)Subtrair ao dividendo a multiplicação do quociente pelo Divisor, obtendo assim o resto

     

    Idade Classe Etária
    Menos de 3 anos Bebé
    Dos 3 aos 12 Criança
    Dos 13 aos 19 Adolescente
    Dos 20 aos 25 Jovem
    Dos 26 aos 65 Adulto
    Mais de 65 Idoso

     

    a)Nome: Classe_Etária

    b)Parâmetros: Idade

    Avaliar a idade

    a)Se tiver menos de três, então é "Bebé"

    b)Senão, se tiver menos de treze, então é "Criança"

    c)Senão, se tiver menos de vinte, então é "Adolescente"

    d)Senão, se tiver menos de vinte e seis, então é "Jovem"

    e)Senão, se tiver menos de sessenta e seis, então é "Adulto"

    f)Senão é "Idoso"

     

     

     

    Este tipo de codificação pode tornar-se confuso e difícil de controlar, como tal o VBA proporciona uma estrutura mais simples para este tipo de situações: Select Case.

    Select Case

    Permite a escolha de um percurso mediante a avaliação de n condições. É de extrema utilidade para evitar os If’s encadeados, dando um maior grau de legibilidade e simplicidade ao código construído.

  • Select Case <Expressão a ser avaliada>

    [Case <Valor da Expressão> [Instruções a realizar]]

    ...[Case Else [Instruções a realizar na situação residual]]

  • End Select

     

     

    Aplicação Prática

    Problema: Alterar a estrutura da função Classe_Etária mediante a aplicação do Select Case.

     

    Construção da Estrutura

    Select Case Palavras-Chave que indicam o inicio de um controlo Select Case
    Idade Expressão sujeita a teste, i.e., variável cujo conteúdo está a ser avaliado. Esta variável vai ser comparada sucessivamente pelos valores alternativos apresentados nas instruções Case <Valor>, se encontrar o valor nalguma dessas opções Case executará as linhas de código que aí terão inicio até à opção de Case seguinte. Caso o valor da variável a ser comparada não corresponda a nenhum valor apresentado nas opções Case, existe uma opção Case especial - Case Select - para os restantes valores, neste caso serão executadas todas as instruções que se localizem entre o Case Else e o Case Select.
    Case Is<3 ou

    Case Is<13 ou

    Case Is<20 ou

    Case Is<26 ou

    Case Is<66

    Expressões Case. Se o valor da variável for igual a qualquer um dos valores apresentados em cada uma destas expressões, o fluxo de execução terá continuidade na linha abaixo da expressão case que faz o matching, até que uma nova expressão case seja encontrada. Sendo que nessa altura termina o controlo Select Case dando continuidade ao programa nas instruções que se seguirem ao End Select.
    Case Else Será a instrução Case residual, seleccionada somente se nenhuma das outras o tiver sido. Neste caso serão realizadas todas as instruções de código que se lhe seguirem até à expressão End Select. Findo o qual seguirá todas as instruções após o controlo Select case.
    End Select Palavra-Chave que indica o fim do controlo Select Case.

     

     

    For – Next

    Permite a execução repetida de uma tarefa durante um determinado número de vezes.

    For <Inicialização do Contador> To <Valor > [ Step <Valor a Incrementar>]

    <Instruções a realizar em cada iteração>

    Next

     

    Aplicação Prática

    Problema: Pretende-se criar uma função que recebendo a base e a potência calcule o valor respectivo.

     

    a)Nome: Calc_Potência

    b)Parâmetros: Base, Potência

    c)Output: Base elevada à potência

    Multiplicar sucessivas vezes (número indicado pela potência) a base pelo resultado acumulado das sucessivas operações.

    1. Em VBA:

     

     

    Função das Variáveis:

    Variável

    Função

    i

    Conta o número de vezes que a base já foi multiplicada, é uma variável que será automaticamente incrementada em cada looping do ciclo.

    acumulado

    Variável que armazena o resultado sucessivo por cada vez que se multiplica. Inicializado a 1 (elemento neutro da multiplicação) antes do ciclo.

     

     

    Construção do Ciclo:

    For Palavra-chave que indica o inicio do ciclo For-Next
    i = 1 To Potência Expressão que contém a inicialização do contador, indicando o valor de inicio e de fim. Assim, o número de vezes que o ciclo é executado será: Valor_Fim – Valor_Inicio +1.

    A palavra To é utilizada para separar o valor do contador no momento inicial do valor no momento final. (Tradução: O contador iniciar-se-á a um e atingirá o valor máximo traduzido pela variável Potência)

    Step 1 Palavra chave utilizada para especificar o valor a incrementar ou decrementar ao contador do ciclo por cada vez que o loop é concretizado. Normalmente o valor a incrementar é um, contudo qualquer outro valor pode ser utilizado, desde números decimais, a números negativos (Provocando assim a decrementação). A palavra-chave Step é opcional, sempre que for omitida é assumido 1 como o valor a incrementar por defeito.
    acumulado = acumulado * Base Instrução a realizar de cada vez que o ciclo for executado. Neste caso a instrução é única, contudo poder-se-ão adicionar outras instruções.
    Next Palavra Chave que indica o fim de um ciclo For-Next . Sempre que a execução do ciclo chega à instrução Next incrementa a variável contador e volta ao inicio do ciclo.

     

     

    Tradução Integral:

    For i = 1 To Potência Step 1

    acumulado =acumulado * Base

    Next

    Para o número de vezes, a iniciar em 1 até que atinja, o valor Potência, pela incrementação de 1 na execução de cada ciclo, deverá multiplicar sucessivamente o resultado acumulado, pela base.

     

    Funcionamento do Ciclo:

    A primeira execução do ciclo distingue-se das restantes por a ela estar associada a inicialização do contador, sendo o restante procedimento semelhante a qualquer outra execução.

    No inicio de cada execução do ciclo, a variável contador é comparada com o valor final de execução. Se o Step for um valor positivo (incrementar) e o valor do contador for superior ao valor final significa que o ciclo já foi realizado o número de vezes pretendido, e então o código acabará a execução da instrução For-Next e seguirá na linha de código que esteja situada imediatamente a seguir, caso contrário executa uma vez mais o ciclo e incrementa a variável contador. Por outro lado, se o Step contiver valor negativo (decrementar) e o valor do contador for inferior ao valor final significa que o ciclo já foi realizado o número de vezes pretendido, e então o código acabará a execução da instrução For-Next e seguirá na linha de código que esteja situada imediatamente a seguir, caso contrário executa uma vez mais o ciclo e decrementa a variável contador.

     

    Perigos associados à utilização do ciclo For-Next:

     

     

    Aplicação Prática

    Problema: Pretende-se criar uma função para calcular um factorial.

     

    a)Nome: Factorial

    b)Parâmetros: Número

    c)Output: Factorial do Número introduzido

    a)Verificar se o número é positivo

    Se for, multiplicar sucessivas vezes um número pelo resultado acumulado das sucessivas multiplicações dos números inteiros que o antecedem

    Senão indicar "ERRO"

     

     

    While-Wend

    A estrutura While-Wend tem um funcionamento similar ao For-Next. Realiza um looping um determinado número de vezes, enquanto que uma determinada condição permaneça verdadeira. A diferença entre este ciclo e o ciclo For-Next é basicamente o facto deste saber apriori o número de vezes que vai ser realizado, enquanto que um ciclo While-Wend poderá ou não sabê-lo pois terminará quando a condição for Falsa.

    While <Condição>

    <Instruções a realizar em cada iteração>

    Wend

     

    Aplicações Práticas

    Problema: Modificar a estrutura da função Factorial, criada com o ciclo For-Next

    (Neste caso o ciclo sabe apriori o número de vezes que irá ser realizado)

     

    a)Nome: Factor

    b)Parâmetros: Número

    c)Output: Factorial do Número introduzido

    a)Verificar se o número é positivo

    Se for, multiplicar sucessivas vezes um número pelo resultado acumulado das sucessivas multiplicações dos números inteiros que o antecedem

    Senão indicar "ERRO"

    Em VBA:

     

    Problema: Apostar um número entre 1 e 10 e saber ao fim de quantos lançamentos (gerações aleatórias) é que foi seleccionado.

    (Neste caso o ciclo não sabe o número de vezes que irá ser realizado)

     

    a)Nome: Aposta_1a10

    b)Parâmetros: Número

    c)Output: Número de lançamentos

    a)Verificar se o número está no intervalo definido

    Se estiver, gerar continuamente números aleatórios entre 1 e 10 e comparar com o Número da aposta, quando for igual pára e indica o número de lançamentos

    Senão estiver no intervalo indicar "ERRO DE INSERÇÃO"

    Em VBA:

     

     

    A instrução While-Wend tem como função gerar números aleatórios entre 1 e 10 por forma a encontrar o número da aposta, e saber qual o número de lançamentos necessários para que aquele valor fosse obtido.

     

    A Função das Variáveis

    Variável

    Função

    Lançamento Guarda o valor obtido em cada um dos lançamentos.
    i Número de lançamentos realizados até obter o resultado da aposta. Este valor tem de ser incrementado cada vez que é realizado um sorteio.

     

    Construção do Ciclo

    While Palavra-chave que indica o inicio do ciclo While-Wend
    Número<>Lançamento Condição teste utilizada para determinar o terminus da realização do ciclo. Se esta condição for Verdadeira executa as instruções que estão dentro do While-Wend, se for Falsa a execução do ciclo é terminada tendo o programa sequência nas instruções que seguem a palavra chave Wend.
    Lançamento = Int(1 + Rnd() * (10 - 1 + 1)) Instrução a realizar de cada vez que o ciclo é executado. Tem como função gerar números aleatórios inteiros entre 1 e 10. Repare que este valor é inicializado antes do inicio do ciclo por forma a garantir uma comparação correcta na condição.

    Em vez de realizar esta expressão poderia utilizar a função Gera_Entre.

    i = i + 1 Instrução a realizar de cada vez que o ciclo é executado. Tem como função fazer a contagem de quantos lançamentos foram realizados até se obter o valor da aposta. Repare que este valor quando entra para o ciclo já tem o valor 1 correspondente ao sorteio realizado inicialmente.
    Wend Palavra Chave que indica o fim de um ciclo While-Wend.

     

    Tradução Integral

    While Número <> Lançamento

    Lançamento = Int(1 + Rnd() * (10 - 1 + 1))

    i = i + 1

    Wend

    Enquanto o número do lançamento não for igual ao número da aposta, o sorteio continua, o que implica sortear um número e contabilizar o número de sorteios realizados.

     

    Se utilizasse a função Gera_Entre para a geração de números aleatórios, a função seria:

     

     

    Funcionamento do Ciclo

    Existe uma fase de inicialização das variáveis envolvidas na condição – teste para garantir o correcto funcionamento do ciclo.

    Avalia a condição teste e se for verdadeira executa todas as instruções até à palavra-chave Wend voltando de novo à avaliação da condição, se for falsa prossegue a execução da rotina nas instruções que se localizam depois da palavra-chave Wend.

     

    Perigos associados à utilização do ciclo While-Wend

     

    Do – Loop

    Esta estrutura é similar à estrutura do While-Wend. Contudo fornece duas possibilidades que estão limitadas àquela estrutura:

     

    Poderá ser:

    Do [{While | Until} <condição>]

    <Instruções a realizar por iteração>

    Loop

    Ou então:

    Do

    <Instruções a realizar por iteração>

    Loop[{While | Until} <condição>]

     

    Aplicações Práticas

     

    Problema: Apostar um número num intervalo definido e saber ao fim de quantos lançamentos (gerações aleatórias dentro do intervalo considerado) é que foi seleccionado.

     

    a)Nome: Aposta

    b)Parâmetros: Número, Limite Inferior e Limite Superior

    c)Output: Número de lançamentos necessário realizar para que o número apostado fosse seleccionado no intervalo de números definido pelo Limite Inferior e Superior

    a)Verificar se o número está no intervalo definido (Limite Inferior a Limite Superior) e se o intervalo está bem definido (Limite Inferior < Limite Superior)

    b)Se estiver, gerar continuamente números aleatórios nesse intervalo e comparar com o Número da aposta, quando for igual pára e indica o número de lançamentos

    Senão estiver no intervalo indicar "ERRO DE INSERÇÃO"

    Em VBA:

     

     

     

     

     

     

     

     

    FIQUE TRANQÜILO, ESTE SITE É AUDITADO POR:

    EM APENAS 08 ANOS DE PUBLICAÇÃO ESTA HOMEPAGE JÁ ALCANÇOU A MARCA DE MAIS DE 3.000.000 PAGEVIEW!

    TUDO SOBRE EXCEL - Com este site seu Excel nunca mais será o mesmo!

    Nº PageView Do Portal:

    Nº PageView Desta Página:

    © 2012 tudosobrexcel.com. Todos os direitos reservados.