Excel 2007 – Criando funções de planilha personalizadas

Existe a possibilidade de criar dois tipos de procedures VBA no Excel 2007: Sub Procedures e Function Procedures.

Visão Geral sobre funções VBA

Você pode utilizar as funções nas seguintes situaçoes:

• Você pode chamar funções de uma procedure VBA diferente
• Você pode usar as funções em fórmulas que você cria em sua planilha

Veremos neste texto como criar funções para serem utilizadas em fórmulas

O Excel contém milhares de funções pré-definidas. Você pode até estar se perguntando se existe mesmo necessidade de criar mais funções. O objetivo principal é criar funções personalizadas que simplifiquem suas fórmulas tornando-as mais curtas, e desta maneira mais simples e fáceis de usar. Outra razão é que você pode criar funções para executar operações que seriam impossíveis de serem realizadas de outra maneira.

Primeiro exemplo

Para criar funções personalizadas é preciso ter algum conhecimento sobre a linguagem de programação reconhecida e executada pelo Excel, o Visual Basic for Application. As funções são escritas e salvas em Módulos, que estão acessíveis através do editor do Visual Basic.

Função personalizada

O exemplo de função apresentado abaixo, contém uma função nomeadad NumSign, que recebe apenas um parâmetro. A função retorna um texto de positivo caso o argumento informado seja maior que zero, e retorna negativo se o argumento for menor que zero, e retorna zero se o argumento for igual a zero. Veja abaixo a função usada:

Function NumSign(num)
Select Case num
Case Is < 0 NumSign = “Negativo” Case 0 NumSign = “Zero” Case Is > 0
NumSign = “Positivo”
End Select
End Function

A mesma funcionalidade obtida com função acima pode ser obtida aninhando duas funções SE, como no exemplo abaixo:

=SE(A1=0;”Zero”; SE(A1>0;”Positivo”;”Negativo”))

O uso das funções SE aninhadas pode ser de difícil compreenção e manutenção, além do limite de sete níveis de aninhamento. Ao aprender a desenvolver suas próprias funções personalizadas, você vai concordar que elas são mais fáceis de manipular.

Usando as funções personalizadas em suas planilhas

Quando você insere uma fórmula que faz uso da função NumSign, o Excel executa a função e obtem o resultado. Funcionando da mesma maneira que qualquer uma das funções nativas do Excel. Você pode utilizar a caixa de diálogo Inserir Função para inserir a sua função personalizada. Sua função estará disponível na categoria Definidas pelo Usuário. A caixa de diálogo disponibiliza a caixa de diálogo Argumentos da Função para especificar os argumentos. É possível ainda criar fórmulas contendo funções aninhadas, misturando as funções nativas do Excel com as funções personalizadas.

Analisando a função personalizada

Veremos abaixo uma descrição da função personalizada que criamos. Veja abaixo:

Function NumSign(num)
Select Case num
Case Is < 0 NumSign = “Negativo” Case 0 NumSign = “Zero” Case Is > 0
NumSign = “Positivo”
End Select
End Function

Veja que a função começa com a palavra-chave (comando) Function, seguida pelo nome da função “NumSign”. Esta função personalizada recebe um argumento, que aparece entre parenteses (num). A variável num pode conter uma referência de uma célula ou o nome de uma variável. Ao ser utilizada em uma planilha, o argumento será uma referência ou um valor literal (como por exemplo 35). Quando a função for utilizada em outra procedure, o argumento será um valor numérico, um número literal, ou um valor que é obtido de uma célula.

O construtor Select Case foi usado para decidir se o valor de NumSign será Negativo, Positivo, ou Zero. O valor retornado pela função será sempre armazenado no próprio nome que lhe foi dado. No nosso exemplo, NumSign irá receber o resultado da verificação feita no argumento num.

Mais sobre Procedures de Funções

A diferença principal entre procedures de função e procedures Sub, é que a primeira retorna um valor como resultado do código que foi executado, enquanto as Sub procedures executam o código mas não retornam nenhum valor.

Executando Procedures de Função

As procedures Sub podem ser executadas de muitas maneiras, mas existem apenas duas maneiras de executar procedures de Função:

• Chamando a função de outra procedure VBA
• Usando ela em uma fórmula de planilha

Chamando funções personalizadas de uma procedure

Você pode chamar funções personalizadas da mesma forma que você chama as funções nativas do VBA. Por exemplo, depois de criar a função CalcTax, poderia utilizá-la da seguinte maneira:

Tax = CalcTax(Valor, Juros)

O comando acima executa a função CalcTax e armazena o resultado na variável Tax.

Chamando funções personalizadas em uma fórmula de planilha

Você pode fazer uso das funções personalizadas em suas planilhas da mesma maneira que faz uso das funções nativas do Excel. A única questão a ser considerada é a disponibilidade da função para outras planilhas. Existem três soluções possíveis:

• Preceda o nome da função com o nome do arquivo (pasta de trabalho) que a contém. Por exemplo, para poder utilizar a função ContarNomes que foi definida em uma pasta de trabalho de nome MinhasFuncoes, você pode fazer a seguinte referência:
=MinhasFuncoes.xlsm!ContarNomes(A1:A1000)
ou
=MinhasFuncoes.xlsm!ContarNomes(A1:A1000)
• Configurando uma referência para uma pasta de trabalho. Caso a referência da pasta de trabalho que contém a função esteja definida, não será necessário inserir o nome do arquivo antes do nome das funções. Para fazer a referência a uma função localizada em outra pasta de trabalho clique em Ferramentas > Referências no editor do VB. Será exibida uma lista de referências de todas as pastas de trabalho abertas, e você poderá marcar o caixa de verificação da referência que quiser adicionar.
• Criando um add-in. Quando você cria um Add-in em um pasta de trabalho que contém procedures de função, não é necessário fazer referência ao arquivo de origem da função. Será necessário apenas instalar o Add-in.

Argumentos da Função

É preciso considerar as seguintes questões sobre parâmetros de funções:

• Algumas funções tem uma combinação de argumentos obrigatórios e opcionais
• Algumas funções tem um número obrigatório de argumentos
• Algumas funções não tem argumentos
• Os argumentos podem ser variávbeis, arrays, constantes, ou expressões

Veja abaixo diversos exemplos que demonstram como usar argumentos de maneira efetiva. Não será abordado o uso de argumentos opcionais.

Uma função sem argumentos

Funções não necessáriamente utilizam argumentos. O Excel por exemplo, tem algumas funções nativas que não utilizam nenhum argumento, são elas: RAND, HOJE, e AGORA.

Segue abaixo um exemplo de função que não usa nenhum argumento. Esta função recupera a propriedade UserName, que é o nome que aparece na seção Personalizar da caixa de diálogo Opções do Excel. Esta função é simples mas pode ser útil, uma vez que não existe nenhum outro recurso que insira este valor em uma planilha.

Function User()
User = Application.UserName
End Function

Para utilizar esta função e exibir o nome de usuário em uma planilha, entre com a fórmula abaixo em uma célula.

=User()

Da mesma maneira que acontece com as funções nativas do Excel, é necessário usar os parênteses.

Uma função com um argumento

A função abaixo recebe um argumento e utiliza o recurso gerador de fala text-to-speech do Excel.

Function Fale(txt)
Application.Speech.Speak (txt)
End Function

O som será exibido sempre que o Excel recalcular o valor. Entre com a seguinte fórmula em uma planilha.

Outra função com um argumento

Esta seção contém uma função mais complexa, desenvolvida para gerentes de vendas que precisa calcular a comissão de sua força de vendas. A taxa de comissão varia de acordo com a quantidade vendida da seguinte maneira, quem vende mais, recebe uma taxa de comissão maior. O cálculo do exemplo é baseado na seguinte tabela:

Vendas Mensais
Taxa de Comissão
0-9.999
8.0%
10.000-19.999
10.5%
20.000-39.999
12.0%
40.000+
14.0%

Você pode usar qualquer um dos diversos métodos para calcular comissões para diversas quantidades de vendas que são inseridas em uma fórmula em uma planilha. Você pode escrever uma fórmula como a seguinte:

=IF(AND(A1>=0;A1<=9999,99);A1*0,08;IF(AND(A1>=10000;A1<=19999,99);A1*105;IF(AND(A1>=20000; A1<=39999,99); A1*0,12;IF(A1>=40000;A1*0,14))))

(o exemlo acima está com os comandos em inglês porque o office em minha máquina é em inglês, mas basta alterar IF por SE e AND por E.)

Esta abordagem não é a mais indicada por várias razões. Primeiro porque a fórmula é muito complexa e de difícil compreenção. Segundo, os valores inseridos diretamente na fórmula, tornam a fórmula difícil de modificar caso a estrutura de comissões mude.

Uma solução mais indicada é utilizar a função PROCV para calcular a comissão. Por exemplo:

=PROCV(A1;Tabela1;2)*A1

Para usar a função PROVC será necessário ter uma tabela de comissões em outro local da planilha.

Outra possibilidade é criar uma função personalizada, como a seguir:

Function Comissao(vendas)
camada1 = 0.08
camada2 = 0.105
camada3 = 0.12
camada4 = 0.14
Select Case vendas
Case 0 To 9999.99
Comissao = vendas * camada1
Case 10000 To 19999.99
Comissao = vendas * camada2
Case 20000 To 39999.99
Comissao = vendas * camada3
Case Is >= 40000
Comissao = vendasd * camada4
End Select
End Function

Depois de criar a função Comissao no módulo VBA, você pode utilizá-la em uma fórmula de sua planilha. Inserindo a seguinte fórmula em uma célula irá produzir o resultado 4200.

=Comissao(35000)

Caso o valor esteja armazenado na célula A1, o argumento da função poderá ser uma referência de célula:

=Comissao(A1)

Funções com dois argumentos

Este exemplo é construído em cima do exemplo anterior. Imagine que a empresa passe a adotar uma política de acrescentar 1% por ano em que o representante faz parte da empresa. A função Comissao foi modificada para receber dois parâmetros obrigatórios. Vamos chamar esta função de Comissao2.

Function Comissao2(vendas, anos)
camada1 = 0.08
camada2 = 0.105
camada3 = 0.12
camada4 = 0.14
Select Case vendas
Case 0 To 9999.99
Comissao2 = vendas * camada1
Case 10000 To 19999.99
Comissao2 = vendas * camada2
Case 20000 To 39999.99
Comissao2 = vendas * camada3
Case Is >= 40000
Comissao2 = vendasd * camada4
End Select
Comissao2 = Comissao2 + (Comissao2 * anos / 100)
End Function

Foi uma modificação batante simples. O segundo argumento, o “ano”, foi adicionado ao comando da função e uma codificação foi incluída para ajustar a comissão antes de sair da função.

Segue abaixo um exemplo de como fazer uma chamada a nova função de cálculo de comissão. O exemplo assume que a célula A1 contém o valor de vendas e a célula B1 contém o número de anos que o representante faz parte da equipe de vendas da empresa.

=Comissao2(A1; B1)

Função com um argumento de matriz

O exemplo apresentado abaixo demonstra como usar um intrevalo de uma planilha como argumento para uma função. Na verdade, não existe nenhum truque, o Excel lida com toda complexidade de maneira transparente.

Imagine que você quer clacular a média dos cinco maiores valores em um intervalo nomeado Dados. O Excel tem uma função que pode fazer este cálculo. Veja abaixo:

=(MAIOR(Dados;1)+MAIOR(Dados;2)+MAIOR(Data;3)+MAIOR(Dados; 4)+MAIOR(Dados;5))/5

Esta fórmula utiliza a função MAIOR, que retorna o n maior valor do intervalo. A fórmula anterior acrescenta os 5 maiores valores de um intervalo e então divide-os por 5. A fórmula funciona bem, mas não é a melhor opção. Mas, e se precisarmos mudar esta regra de pagamento de comissões para os seis maiores valores? A quantidade de alterações necessárias para a implementação da nova regra será muito custosa.

Será muito mais simples se a função TopAvg, por exemplo, puder ser implementada da seguinte maneira:

=TopAvg(Dados, Num)

O código para a função acima é apresentado abaixo:

Function TopAvg(Dados, Num)
Sum = 0
For i = 1 To Num
Sum = Sum + WorksheetFunction.Large(Dados, i)
Next i
TopAvg = Sum / Num
End Function

A função acima recebe dois argumentos, Dados e Num. O argumento Dados representa um intervalo da planilha, enquanto o argumento Num representa a quantidade de maiores números que serão calculados através da média de seu maior valor. A variável Sum é inicializada com o valor zero, e em seguida é realizado um loop com os comandos For/Next, por todas as médias de cada um dos níveis começando de um até o número especificado no argumento Num. A variável Sum recebe o valor resultante de todas as somas das médias obtidas. E finalmente a divisão de Sum por Num obtém a média final.

A palavra chave WorksheetFuncion, permite utilizarmos qualquer função de planilha em nossas funções personalizadas, sendo necessário apenas preceder o nome da função por esta palavra chave, separada do nome da função por um ponto (.). Como no exemplo abaixo:

Sum = Sum + WorksheetFunction.Large(Dados, i)

Não podemos criar funções personalizadas com o mesmo nome de funções nativas do Excel.

Debugando Funções Personalizadas

Debugar uma função pode ser mais difícil que debugar uma sub procedure. Caso você desenvolva uma função para ser usada em uma fórmula de planilha, e esta função contenha erros. A planilha exibirá normalmente, apenas a mensagem de erro padrão #Value!. Esta mensagem de erro não é de nenhuma ajuda para a verificação e correção do problema.

Quando estiver debugando funções, tenha certeza de fazer chamadas de teste para a função, de um único local. Seguem abaixo, três técnicas para fazer o tratamento de erros:

• Utilize o comando MsgBox para exibir a caixa de diálogo do windows para alertas, contendo a informação do valor da variável que você deseja conhecer. O caminho indicado é inserir os comandos MsgBox dentro das funções que se deseja avaliar.
• Teste a função chamando-a de dentro de uma sub procedure. Desta maneira os erros do sistema serão exibidos normalmente permitindo sua que você corrija o problema ou entre diretamente no modo de debugger.
• Configure um breakpoint no sistema para que o processamento seja pausado nos pontos configurados. Pressione F9 para criar um breakpoint. Quando o processamento pausar nos breakpoints criados, você pode utilizar a tecla F8 para seguir com o processamento passo a passo.

A caixa de diálogo Inserir Função é uma ferramenta útil para selecionar suas funções personalizadas. Depois de selecionar uma função, será exibida a caixa de diálogo Argumentos de Função que disponibliza caixas para a entrada dos respectivos argumentos.

NOTA: Funções precedidas pela palavra-chave Private, não serão exibidas na lista de funções. Você pode utilizar esta palavra-chave sempre que a função que estiver criando seja utilizada somente entre procedures e não nas planilhas.

Você também pode exibir uma descrição da sua função personalizada na caixa de diálogo Inserir Função. Faça o seguinte:

1- Crie uma função em um módulo, utilizando o editor do VB
2- Ative o Excel
3- Clique na guia Desenvolvedor > Código > Macros
4- Na caixa de diálogo Macros, entre com o nome da função na caixa Nome da Macro.
5- Clique no botão Opções
6- Entre com uma descrição para a função e clique em OK

Funções personalizadas são listadas na categoria Definidas pelo Usuário.

Quando a ciaxa de diálogo Argumentos da Função é exibida, você pode visualizar uma descrição sobre cada argumento, mas esta opção não está disponível para as funções personalizadas.

About these ads

31 comentários sobre “Excel 2007 – Criando funções de planilha personalizadas

  1. Eryx,

    Gostei muito de seu material, conheço pouquissimo o VBA e gostaria se você pudesse me ajudar a criar o cálculo abaixo.

    Desde já agradeço muito pelo seu blog.

    Obrigado,

    Anilto Martins

    Function LarguraChapa(H6)

    Select Case H6

    Case Caixa_Maleta_N
    LarguraChapa = J9 + N9 + B9

    B9 = “E” + 4
    B9 = “B” + 7
    B9 = “C” + 11
    B9 = “A” + 16
    B9 = “EC” + 20
    B9 = “EB” + 16
    B9 = “BC” + 24
    B9 = “AC” + 32

    Case Meia_Cxa_Maleta_N
    LarguraChapa = J9 / 2 + N9 + B9

    B9 = “E” + 2
    B9 = “B” + 4
    B9 = “C” + 6
    B9 = “A” + 8
    B9 = “EC” + 10
    B9 = “EB” + 8
    B9 = “BC” + 11
    B9 = “AC” + 16

    Case Cxa_Maleta_Aba_Total
    LarguraChapa = J9 * 2 + N9 + B9

    B9 = “E” + 5
    B9 = “B” + 10
    B9 = “C” + 14
    B9 = “A” + 16
    B9 = “EC” + 18
    B9 = “EB” + 16
    B9 = “BC” + 25
    B9 = “AC” + 28

    Case Chapa_Normal
    LarguraChapa = J9

    Case Chapa_Corte_Vinco_Rotativa
    LarguraChapa = J9 + 20

    Case Chapa_Corte_Vinco_Plana
    LarguraChapa = J9 + 30

    End Select
    End Function

    • Prezado Anilto, tenho estado muito atarefado e por esta razão demorei em responder. Veja, pelo código que você enviou não ficou claro o que você quer fazer exatamente. Reparei que você parece estar tentando usar a Function para alterar valores nas células, mas isto não é possível porque o papel da Function é simplesmente retornar um valor. A function não faz alterações nas células das planilhas. Para fazer alterações nas planilhas utilize uma Sub Procedure. Para ajudar mais, por favor me forneça mais detalhes sobre o objetivo do seu código. Não está claro para mim porque você atribui um valor para a função e a seguir executa uma série de atribuições a célula B9 por exemplo.

      • Olá!
        Depois de muito procurar na net uma resposta ao meu problema resolvi deixar aqui o meu problema…
        Preciso converter em folha de excel tempos (minutos, segundos e centêsimos de segundos ) num números décimal..

        Qualquer coisa deste género:
        01:13,45 = 73,45

        Já tentei a função segundo, tempo, etc. mas nenhuma resolveu…

        Existe alguma solução????

        Muito obrigado

      • Prezado João Ribeiro, acredito que não entendi bem a questão ou o problema. Realizei as seguintes etapas: Inseri o valor 01:13,45 em uma célula, abri a caixa Formatar Células na guia Número, e selecionei o formato Geral, e obtive o resultado 0,00085. Para o tempo 01:13,5 obtive o valor 0,042516782 e para o tempo 32:01:13,22 obtive o valor 1,33418344907407. Na verdade, é desta forma que o Excel enxerga valores de data e hora, o formato de data como nós seres humanos enxergamos datas e horas, é na verdade uma formatação aplicada sobre o respectivo número serial. Caso não tenha entendido corretamente, favor detalhar mais a sua questão.

      • João Ribeiro, se entendi bem sua questão, você pretende converter tudo para segundos. Pensando assim, fiz o seguinte:

        1. Digitei 01:13,45 na célula A1, formatada como TEXTO. Tem que formatar ANTES de inserir os dados, ou o Excel converterá para seu sistema de data/hora;
        2. Na célula A2, digitei: =MINUTO(A1)*60+SEGUNDO(A1)+DIREITA(A1;2)/100

        – Célula A1 formatada ANTES como texto, e os dados devem ser inseridos sempre no mesmo formato, ou seja, com duas casas decimais, mesmo que seja 00, por conta da função DIREITA;
        – Multipliquei os minutos por 60 para converter em segundos, claro;
        – O valor retornado por DIREITA(A1;2) é 45, por isso dividi por 100 (convertendo para centésimos);
        – Observe que, mesmo que o valor esteja formatado como texto, quando usamos as funções MINUTO e SEGUNDO o Excel consegue perceber onde estão os dados procurados;
        – Da mesma forma, quando aplicamos qualquer operação matemática com um número formatado como texto, o Excel o interpreta como número. A função DIREITA sempre retorna texto, mas quando fiz operações com o resultado dela (dividi e somei), o “45” (texto) foi convertido para 0,45 (número).

  2. Eu conheço puco sobre o VBA, o seu texto é muito útil e detalhado e nos ajuda a tentar compreender o VBA. Se for possivel gostaria de saber como faço para passar texto de células mescladas (por exemplo Plan 1, célula a5:a10 mescladas) para outra planilha (por exemplo: Plan 2, células a5:a10). Tem alguma formula para células mescladas?eu tenho que usar macros? Como faço? Se possível entre em contato também pelo meu email. Grato

    • Prezado Josimar, se o seu objetivo é somente obter o conteúdo de células mescladas, basta inserir uma referência a célula (mesclada) que você quer, mas quero antes lembrar-lhe que no momento em que você mescla qualquer intervalo de células, elas assumem o nome da primeira célula selecionada, como por exemplo, o intervalo A5:A10 (que você citou), será referênciado apenas por A5. Dito isto, para exibir o valor inserido nesta célula A5 (A5:A10 mescladas), selecione uma célula da planilha onde você quer exibir o conteúdo da célula referenciada e então entre com a referência exemplificada abaixo:

      =NomeDeSuaPlanilha!A5

      De qualquer forma, caso queria utilizar uma função para isto, a solução é bem simples também. Abra o editor do VBA, e então clique com o botão direito no título do projeto: VBAProject(nomedoseuarquivo.xlsm), e selecione a opção Insert/Module. No módulo criado entre com o método abaixo:

      Function CopiarValor(ref As Range)
      CopiarValor = ref
      End Function

      Depois de salvar o arquivo, volte para o Excel, selecione uma célula qualquer, e entre com a chamada a função, conforme exemplificado abaixo:

      =CopiarValor(NomeDaPlanilha!B3)

      Qualquer dúvida é só falar!

  3. Sei que faz tempo deste a data do conteudo, que por sinal ficou excelente. Sou programador java, e faço bastante uso do excel, sei bem trabalhar com macros, sub procedures, functions ate outras tantas ferramentas do excel, tais como tabela dinamicas, mas minha duvida vai alem disso tudo ja revirei a net atras dessa informação e ainda não consegui nada, como todo programador sou muito organizado e gosto de comentar os codigos a modo facilitar ao usuario final, o que preciso é como faço para criar ajuda nas functions ao usuario, por exemplo, quando clico em Inserir Função, e escolho as definitas pelo usuario a Ajuda apararece não disponivel, gostaria de inseri-las teria como???
    Deste ja agradeço…
    André

  4. Boa noite,
    Eryx, sou aluno estou estudando Aplicativo do Office 2007 e VBA, porem tenho algumas duvidas em realizar umas provas, posso envia-las para seu e-mail.
    o meu é ti_jonathan@hotmail.com pois tenho duvida de como proceder em alguns casos, como por exemplo um textbox queria saber se posso usar tanto integer como string no seus valores para buscar comparaçao com os valores de cadastro.
    Abraços
    Jonathan Bruno

    • Prezado Jonathan, perdão pela demora em responder. Infelizmente estou com o tempo muito ocupado e não poderei avaliar as suas provas. Mas quanto a sua dúvida sobre a possibilidade de entrar com valores numéricos e textuais em um campo TextBox, certamente é possível. Se quiser reformular ou detalhar melhor o que está tentando fazer, terei prazer em ajudar.

  5. Tenho uma dúvida, quando eu crio a formula não aparece a descrição de cada argumento, tem como personalizar isso?

    • Prezado Luis Gustavo, pela sua dúvida, suponho que você sabe como atribuir uma descrição de help para uma função que você criou, clicando com o botão direito dentro da janela Object Explorer no editor VBA em cima da sua função personalizada, e escolhendo a opção Propriedades. Isto faz com que você atribua uma descrição as suas funções personalizadas, que serão exibas na caixa de diálogo Inserir Função no Excel. No entanto, até o Excel 2007, ainda não havia a possibilidade de atribuir descrições explicativas para os atributos de suas funções. No Excel 2010, a Microsoft disponibilizou esta funcionalidade através de uma macro VBA. Encontrei um exemplo no link http://spreadsheetpage.com/index.php/tip/user-defined_function_argument_descriptions_in_excel_2010/. Estou meio apertado ultimamente mas em breve também postarei um exemplo sobre o assunto. Espero ter ajudado.

  6. Muito bom o texto! Mas fiquei com uma dúvida. Estou fazendo uma planilha de orçamentos e geralmente recebo valores em US Dólar e queria criar uma função pra converter este valor pra real. Eu consegui uma cotação sempre atualizada usando “Conexões Existentes” do próprio Excel, porém, eu não consigo criar uma função que calcule um valor que eu entre em outra planilha. Eu poderia copiar o valor usando tipo “=dolar!c9″ na planilha atual e fazer uma multiplicação simples, mas eu queria fazer isso sem usar este recurso, usando apenas o vba. Existe essa possibilidade?

    Obrigado!

    • Prezado, até amanhã responderei sua questão.

    • A situação descrita representa perfeitamente uma situação onde você pode tirar proveito dos benefícios obtidos com o uso de Suplementos no Excel: a simplificação do acesso a execução das suas funções personalizadas, assim como, a simplificação da interação do usuário que não precisará lidar com dois arquivos (o arquivo onde deseja executar a função e o arquivo que contém a mesma). Como resultado, você poderá chamar ou executar sua função personalizada, exatamente da mesma forma que executa uma função local do seu Excel (ex: =MAX(A2:A10)). Criei um post explicando sobre a criação de Suplementos especialmente para responder a sua dúvida. Peço desculpas pela demora em responder mas tenho estado muito atarefado. Estou a disposição para quaisquer dúvidas. O link do post é http://eryxjose.wordpress.com/2012/10/24/expandindo-as-funcionalidades-do-excel-2007-utilizando-suplementos/

  7. Bom dia a todos.
    preciso da ajuda de vocês!

    Estou desenvolvendo uma planilha de controle de horas (entrada;saída;saldo positivo e negativo), porém, preciso acrescentar uma condição. Ocorre que foi concedido aos funcionários um horário especial.

    Por exemplo: em determinados dias da semana alguns colaboradores chegam para sua rotina de trabalho, uma hora a mais = (SEGUNDA: 08:00/11:00-13:00/18:00), entretanto, (TERÇA: 09:00/11:00-13:00/18:00).

    Conforme previsto, a planilha irá contabilizar um saldo negativo para o colaborador, de uma hora a menos.

    A minha dificuldade está em contabilizar a hora que está faltando, pelo de que foi concedido este horário especial aos funcionários. Não estou conseguindo elaborar uma formula que atenue a hora faltante, ou seja, não conte como negativo.

    Dese já, conto com a ajuda de vocês e muito obrigado!

    • Prezado Guilherme, antes de responder gostaria de esclarecer que sou apenas eu, Eryx Guimarães que escrevo todos os artigos e respondo a todas as questões, não há ninguém mais. Faço isto por diversas razões, primeiramente gosto de compartilhar meus conhecimentos e ter contato com as situações reais de uso das ferramentas de software que as pessoas utilizam. Quanto a sua questão, eu recomendo que você crie uma planilha com as colunas: Funcionário, Data, Hora de Entrada Manhã, Hora de Saída Manhã, Hora Entrada Tarde, Hora Saída Tarde, Total Manhã, Total Tarde, Total Dia, Horas Previstas Funcionário Dia, Saldo Horas Extras, Saldo Horas Faltas. A seguir explicarei cada uma das colunas.

      Funcionário – contém apenas o nome de cada funcionário. Você poderá inclusive tratar cada funcionário em uma planilha separada mas prefiro, sempre que possivel, incluir todas as informações na mesma planilha e utilizar os recursos do próprio Excel para obter as informações que procuro. Neste caso, depois de construir a planilha eu transformei ela em uma tabela (Menu Inserir > Tabela), de maneira que quando eu quiser visualizar informações sobre um funcionário específico, eu apenas utilizo o recurso de filtrar (seta exibida no rótulo da coluna Funcionários) para selecionar o funcionário que desejo avaliar as horas.

      Data – contém a data do dia, e você pode utilizar o filtro (referido no item acima) para selecionar uma data específica para avaliação.

      Hora de Entrada Manhã – contém a hora que o funcionário entrou de manhã.

      Hora de Saída Manhã – contém a hora que o funcionário saiu para o almoço.

      Hora de Entrada Tarde – contém a hora que o funcionário chegou para o turno da tarde.

      Hora de Saída Tarde – contém a hora que o funcionário saiu no fim do dia de trabalho.

      Total Manhã – contém uma fórmula subtrai a coluna da Hora de Entrada Manhã da Hora de Saída para o almoço.

      Total Tarde – contém uma fórmula que subtrai a coluna Hora de Saída Tarde pela Hora Entrada Tarde, obtendo a quantidade de horas trabalhadas no período.

      Horas Total Dia – contém uma fórmula que soma a coluna Horas Total Manhã e a coluna Horas Total Tarde, obtendo a quantidade total de horas trabalhadas.

      Horas Previstas Funcionário Dia – contém a quantidade de horas previstas por funcionário por dia. Normalmente oito horas diárias, mas no caso, alguns funcionários trabalham apenas sete horas por dia.

      Saldo Horas Extras – contém uma fórmula que subtrai a coluna Horas Total Dia da coluna Horas Previstas Funcionário Dia, mas é importante empregar a função SE para verificar se esta subtração retorna um valor positivo, caso contrário o Excel exibirá caracteres # demonstrando um cálculo inválido. No meu caso, utilizei a seguinte fórmula =IF(I6″”; IF(I6-J6>0;I6-J6;”-“);””). Meu Excel é em ingles, você precisa apenas substituir o IF por SE, caso seu Excel esteja em português.

      Saldo Horas Faltas – basicamente contém a mesma fórmula, modificando apenas a ordem das colunas subtraídas, ou seja, no exemplo acima, o I6 – J6 ficará J6 – i6.

      O último passo é utilizar os recursos da Tabela (guia Ferramentas de Tabela) para exibir a linha de totais, e nas colunas Saldo Horas Extras e Saldo Horas Totais, da linha de totalizações exibida no final da tabela, selecionar a função de Soma nas respectivas células. Sempre que você quiser visualizar a situação de saldo de um funcionário, utilize o filtro da coluna Funcionários, conforme descrevi no respectivo item.

      Qualquer dúvida me avise.

  8. Me ajudem por favor, como fazer uma formula no excel 2007?

    Eu gostaria de saber como fazer uma formula no excel 2007.
    Eu estou montando uma planilha de despesas, essas que usamos para ver os gastos de nossas casas…
    mas queria saber como fazer uma formula para colocar o quanto eu ganho e conforme eu colocar os gastos vai automaticamente fazendo o desconto do salário! Para no final de colocar todas as contas e gastos eu ver se fiquei devendo ou se vai sobrar algum dinheiro a mim.

    1 Exemplo: eu recebe 1000.00 reais
    Mas preciso pagar uma conta de 300.00 reais o que ainda sobra 700.00 reais!

    Se alguém conseguir me ajudar, eu agradeço de verdade!!
    valeu a todos desde já.

  9. Bom dia. Estou começando a trabalhar mais com o excel agora e não estou tendo sucesso com uma formula, então gostaria de uma ajuda.
    Eu tenho varios valores em diversas linhas, e algumas delas tem celula em branco, e terminam antes ou depois das outras. Tem alguma formula que mostre para mim na coluna A o último valor digitado nessas linhas?

  10. Boa tarde, estou querendo criar uma fórmula que resultasse no mesmo número existente na célula. Este valor é uma combinação de duas outras células onde o que se enxerga são códigos (ex:a1=v1 e c1=s2). Criei outra tabela na Planilha 2 com os valores originais para poder remeter o resultado. Estou usando a função SE,. Minha dúvida é: primeiro se a função que escolhí é a mais indicada e segundo como juntar duas funções desse tipo, pois minha intenção é colocar todas as combinações possíveis (v e s) na mesma fórmula para utilizar apenas 1 coluna. Obrigado

  11. Boa tarde,

    Em minha planilha tenho células com a informação “-8:00″ para horas extras não compatibilizadas. Em outra célula tenho o somatório de horas extras.
    Gostaria que quando aparecesse “-8:00″ se transformasse em uma célula sem informação alguma para não influenciar no somatório de horas extras que está negativo, é claro.
    Já tentei várias possibilidades de “se” e Formatação Condicional e nada.
    Trabalho na versão 2010.

    Desde já, obrigado.

    • Prezado Anderson, não entendi bem a sua dúvida. Se você tentar subtrair 32 horas de 40 horas irá obter 8 horas como esperado, no entanto se você tentar subtrair 40 horas de 32 horas, o Excel mostrar os símbolos #### indicando um erro na fórmula. Por esta razão vou ter que pedir que detalhe melhor como está fazendo para produzir o resultado -8:00. De qualquer forma, você pode pensar em realizar o cálculo das horas somente quando a coluna que contém o número de horas esperadas for maior que o valor da coluna que contém o número de horas trabalhadas, e mostrar uma mensagem caso contrário.

      • Obrigado pelo pronto atendimento. Vou tentar explicar melhor.

        Em minha planilha de trabalho de prestação de serviço tenho células específicas para horário de entrada, horário de saída, total de horas diárias, total de horas extras diárias, total de horas extras quinzenais, horas da jornada de trabalho, total de horas extras do mês, dentre outras. Lembrando que o valor da célula das horas da jornada de trabalho é “8:00″.

        Dito isto, quando minhas células estão sem informação alguma de horários de entrada e saída, outras células ficam com horários negativos. Por exemplo:
        1- nas horas extras diárias aparecem “-8:00″ já que são horas diárias menos jornada de trabalho, ou seja, 0:00-8:00=-8:00.
        2- nas horas extras quinzenais aparecem negativas já que são a soma das horas extras diárias, todas “-8:00″.
        3- no total de horas extras do mês aparece “-176:00:00″ já que é a soma das horas extras quinzenais, todas negativas.

        Resumindo, finalmente, gostaria que quando contabilizasse “-8:00″ nas horas extras diárias aparecesse “0:00″ ou nada, como se tivesse selecionado a célula e deletado o conteúdo dela. Isso resolveria, eu acho, automaticamente as horas extras quinzenais e as horas extras do mês.

        Caso não tenha entendido, posso lhe enviar o arquivo que estou trabalhando em anexo?

        Muito obrigado.

      • Prezado Anderson, escrevi um artigo mostrando a construção de uma planilha de controle de horas, que acredito responderem as suas dúvidas e ainda apresento algumas dicas que considero relevantes. Acesse a página inicial do blog http://eryxjose.wordpress.com e confira. Mas caso ainda tenha dúvidas, por favor me avise.

      • Bom dia, Eryx.
        Quase deu certo. Utilizei a fórmula =SE((H5””); SE(H5-I5>0;H5-I5;”-”);””) e adaptei à minha planilha.

        A única coisa que não deu certo foi que se eu trabalhar menos de oito horas em um dia não contabiliza a hora negativa. Ex: se eu trabalhar de 9:30 às 14:20 deveria aparecer -3:10 nas horas extras, ou seja, estaria devendo 3:10h. Tem como acrescentar esta condição à esta fórmula?

        Só lembrando que por esta fórmula resolveu o problema de valores de horas extras iguais a 8:00, iguais a -8:00 e maiores que 8:00. Só falta resolver para demais valores negativos.

        Nunca vi uma fórmula deste tipo. Poderia explicar melhor o funcionamento dela para não ficar só no copiar e colar? E realmente saber o que estou fazendo?

        Obrigado.

      • Anderson, quanto a questão do horário de trabalho de 9:30 as 14:20, num primeiro momento, acredito que simplesmente modificando a planilha para não trabalhar com os turnos manhã e tarde, ou seja, trabalhar um turno único, já resolveria a questão. Quanto a fórmula usada, na verdade é bem simples, é uma função aninhada e no artigo http://eryxjose.wordpress.com/2010/12/26/excel-2007-criando-funcoes-de-planilha-personalizadas/ você vai encontrar um exemplo no trecho: A mesma funcionalidade obtida com função acima pode ser obtida aninhando duas funções SE, como no exemplo abaixo:

        =SE(A1=0;”Zero”; SE(A1>0;”Positivo”;”Negativo”))
        … leia com atenção este exemplo, que acredito que será suficiente para compreender melhor, mas também aconselho praticar um pouco. No momento estou um pouco apertado de tempo para detalhar melhor, mas caso ainda tenha dúvidas, me avise que assim que possível darei mais detalhes. Agradeço a sua participação e interesse em aprender mais sobre o Excel. Um abraço!

  12. Olá Eryx,

    Preciso de uma função que realize a soma de taxas de juros, da seguinte maneira: (1 + taxa1) * (1 + taxa2) * (1 + taxa3) …
    A fórmula soma a cada taxa o valor 1 e depois multiplica todos os valores encontrados. Gostaria de criar um função própria para isso. Obrigado.

    • Imagino que uma maneira de fazer isto seja nomear uma tabela composta das fórmulas de cálculo de cada taxa individualmente, por exemplo, na célula b2 =1+taxa1, na célula b3 =1+taxa2, e assim sucessivamente. Você atribui um nome para esta tabela contendo todas as fórmulas e depois pode fazer uma fórmula de soma ou simplesmente ativar a visualização da linha de totalização nas ferramentas de tabela. Me avisa se tiver dúvida.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s