Função Definida pelo Usuário (UDF) no Excel 2010

Em nossa última postagem, onde falarmos sobre Análise Combinatória (Análise Combinatória no Excel 2010: Combinação, Arranjo ou Permutação) mencionamos sobre criação de Função Definida pelo Usuário. Conforme prometemos, mostraremos como criar uma função personalizada para calcular a combinação com repetição no final desta postagem.

Existem muitas funções prontas no EXCEL, mas nem todas as fórmulas do mundo estão implementadas na forma de função. Entretanto, é possível criar nossas próprias funções, uma função definida pelo usuário (UDF – User Defined Functions ou Custom Excel Functions). Este tópico é descrito pela Microsoft como

Escrevendo um procedimento Function

Se houver um cálculo complexo a ser reutilizado com freqüência no Microsoft Excel, você pode criar sua própria função de planilha para executar o cálculo.

Para criar suas próprias funções personalizadas, utilize o Microsoft Visual Basic® for Applications (VBA). O VBA é uma linguagem de programação interna do Excel que está acessível pelo atalho ALT+F11.

Vamos começar com uma fórmula simples de ser implementada como função…

Função Definida pelo Usuário

Problema: um termômetro americano marca 68 graus Fahrenheit. Será que está quente ou frio?

A fim de manter o exemplo simples, escolhemos implementar uma função calcula o número de graus Celsius a partir dos graus Fahrenheit. Quando a função for chamada a partir de uma célula do EXCEL, o resultado do cálculo é retornado e mostrado na célula.

Antes de começar, crie a seguinte planilha colocando os valores numéricos nas células A2, A3 e A4: tabela fahrenheit-celsius excel

O procedimento a seguir ensina, em apenas 5 passos, como criar e usar uma função definida pelo usuário:

I) O atalho ALT+F11 abre a janela do VBA (Microsoft Visual Basic For Applications)

II) Procure pela guia Inserir e selecione a opção Módulovba - inserir módulo no excel 2010

III) Na janela que abriu, digite o código de sua função. Por exemplo, uma função para calcular graus Celsius a partir dos graus Fahrenheit:

Function Celsius(grausF)   

        Celsius = (grausF - 32) * 5 / 9

End Function

Digite a função acima na janela Módulo 1 aberta:

digitando uma UDF no VBA Excel

IV) No menu Arquivo do VBA, clique em Fechar e voltar para Microsoft Excel (ou use o atalho Alt+Q) e Volte para a planilha Excel .

V) Teste sua função. Digite na planilha a função que você acabou de implementar (não esqueça de começar com o sinal de igualdade) colocando entre parênteses o argumento desejado (neste caso um número ou uma célula que contém um número). Neste exemplo você deve digitar =CELSIUS(A2) na célula B2 (ou outra célula qualquer) do Excel.

Observe que o Excel reconhece a existência da função CELSIUS à medida que está sendo digitada:

digitando a UDF no Excel

Na planilha, se os valores numéricos estiverem nas células A2, A3 e A4, use sua função para fórmulas do mesmo modo que usaria qualquer função de planilha. Por exemplo, a função Celsius pode ser usada na planilha assim:

UDF digitada no Excel 2010

 Importante: Uma função criada dessa maneira somente se encontra disponível para uso na pasta de trabalho onde tiver sido criada e, se quiser ser usada posteriormente, precisará ser salva como Pasta de Trabalho Habilitada Para Macro Do Excel.

Dica: se quiser usar essa função em outro local, você pode copiar o código da função para módulos do VBA situados em outras pastas de trabalho.

Detalhes da função definida pelo usuário Celsius

Eis, novamente, a listagem de nossa função, vamos examiná-la em detalhes:

Function Celsius(grausF) 


     Celsius = (grausF - 32) * 5 / 9

End Function

O nome da função que será usada pelo usuário, Celsius no exemplo acima, deve receber dentro do corpo da função o resultado do cálculo realizado com o argumento grausF, que, neste caso foi subtraído de 32 e depois multiplicado por cinco nonos (5/9). As quatro operações básicas (soma, subtração, multiplicação e divisão)  mais a potência utilizam os mesmos operadores que usamos dentro de uma planilha Excel: +*/ e ^.

Outros operadores bem como algumas funções estão disponíveis, mas usam uma forma diferente dos operadores e funções que usamos dentro de uma planilha. Outros operadores disponíveis dentro do VBA: \ (divisão inteira), Mod (para obter o resto da divisão inteira), & (para concatenar texto). Existem ainda várias funções , mas isso é fácil de achar na Internet e foge do escopo da nossa postagem.

O exemplo de FUNÇÃO DEFINIDA PELO USUÁRIO (UDF) usado nesta postagem foi bastante simplificado, apenas para demonstrar o funcionamento básico de uma função definida pelo usuário. Para criar funções complexas no VBA, é preciso conhecer um pouco o modelo de objetos do VBA para Excel, a estrutura da linguagem do Visual Basic (VB) e o ambiente VBA.

Exemplo de outra Função Definida pelo Usuário

1) Como calcular através de uma UDF o processo inverso, transformação de Celsius para Fahrenheit?

Neste caso, siga os mesmos passos anteriores, mas a função digitada em módulo 1 ficará assim (pode ser digitada abaixo da função anterior, se não quiser apagar a outra função já digitada):

Function Fahrenheit(grausC) ' de graus C para graus F

     Fahrenheit = grausC * 9 / 5 + 32

End Function

Para usar a função acima dentro de uma planilha, escreva =FAHRENHEIT(100) dentro de uma célula no Excel para saber, por exemplo, a temperatura em que a água ferve (100°C) em um termômetro com a escala Fahrenheit. Deverá surgir o número 212.

2) Eis aqui um outro exemplo de função definida pelo usuário para calcular centímetros a partir de valores em polegadas:

Function cm(pol)  ' transformar polegadas em centímetros

     
     cm = pol * 2.54


End Function

A função acima, quando digitada em Módulo 1 (conforme fizemos com a função anterior), permite calcular na planilha a conversão de polegadas para centímetros, bastando para isso digitar =CM(A2) na planilha Excel, caso o número em polegadas esteja na célula A2, por exemplo.

3) A função que faz o contrário, transforma centímetros em polegadas está aqui:

Function pol(cm)  ' transformar centímetros em polegadas 

     pol = cm / 2.54

End Function

Para usar, basta digitar, por exemplo, =POL(12) na planilha Excel e saberemos quanto mede, em polegadas, a medida 12 centímetros. Deve dar 4,724409 polegadas.

Como criar uma função para calcular a combinação com repetição

Seguindo os passos acima, mostrados aqui de forma resumida:

I) Tecle o atalho ALT+F11 para abrir a janela do VBA

II) Vá na guia Inserir e selecione a opção Módulo

III) Digite o seguinte código:

Function CombinRep(ByVal n As Long, ByVal p As Long) As Double

    If (n > 0 And p > 0) Then

       CombinRep = factorial(n + p - 1) / (factorial(p) * factorial(n - 1))

    Else

        CombinRep = Error()

    End If

End Function

Function factorial(ByVal n As Long) As Double

    factorial = 1

    Dim a As Double

    For a = 1 To n

        factorial = factorial * a

    Next a

End Function

IV) Tecle o atalho ALT+Q para sair do VBA e voltar à planilha

V) Teste a função entrando com algum valor, como por exemplo, digitando na planilha Excel, =CombiRep(4,2)  em alguma célula qualquer. Se tudo estiver funcionando como deveria, deve aparecer programagicamente o número 10, conforme previsto em nossa última postagem (http://xek.me/?p=1099).

Acabamos de criar, conforme prometido, uma função para cálculo de Combinação com Repetição.

Em nossa próxima postagem veremos como fazer funções que permitam usar intervalos como argumentos ou entrar com número variável de argumentos, exatamente como faz a função SOMA do Excel e assim poderemos implementar a PERMUTAÇÃO com ELEMENTOS REPETIDOS.

 

Permutação no Excel 2010 – Análise Combinatória

Permutação no Excel 2010 – permutação simples, permutação circular e permutação com repetição de elementos 

Nas postagens anteriores mostrei como lidar com ARRANJOS simples (http://xek.me/?p=1057) e COMBINAÇÕES simples (http://xek.me/?p=1049) sem repetição no EXCEL 2010. Chegou a vez das permutações, mas vamos aproveitar e mostrar aqui não só as PERMUTAÇÕES simples, mas também como calcular as permutações circulares e as permutações com repetição de elementos. Eis as fórmulas da permutação:

fórmulas da permutação no Excel

Acima, as fórmulas que usaremos para os diversos cálculos realizados nesta postagem tanto na matemática como sua equivalente no Excel 2010. Lembre que a função fatorial pode ser FATO( ) ou FATORIAL( ), dependendo da versão de Excel que você está usando.

permutação de bolas de 4 coresPERMUTAÇÕES SIMPLES no EXCEL

Exemplo: “tenho 4 bolas com cores diferentes, de quantas maneiras posso dispor estas 4 bolas uma do lado da outra?”

Este é um caso claro de permutação, pois uso todos os elementos n do meu conjunto, onde n=4. Veja na imagem ao lado todas as 24 permutações possíveis.

Bom, se eu tiver de desenhar bolas para calcular isso o tempo todo, vai ficar bem chato!

Ao saber que a permutação é um caso particular de arranjo em que todos os elementos estão sendo usados, podemos usar a função PERMUT(núm ; núm_escolhido) do Excel que, apesar do nome, serve mesmo é para o cálculo de arranjos. Um arranjo de núm elementos em que eu pego um subconjunto de núm_escolhido de elementos igual a núm (todos) pode ser calculado com a função PERMUT repetindo-se o número n nas duas posições como argumentos da função. A vantagem é que fica sendo independente da versão do Office que você tem na sua máquina.

Se eu colocar na célula A2 o número n e em B2 o número p, ambas as células serão preenchidas com o número 4, portanto. Na célula C2 posso usar a função =PERMUT(A2;B2). Veja abaixo a fórmula utilizada no Excel:

permutação no Excel 2010

Outra possibilidade é que, “trocando as bolas” ([trocadilho infame detectado!]), as permutações simples podem ser calculadas usando a função fatorial do Excel, mas dependendo da versão do Excel (2003, 2007 ou 2010), a função fatorial pode ser escrita como FATO(núm) ou FATORIAL(núm). Você vai ter de testar uma delas e descobrir qual. Veja abaixo como eu fiz para a minha versão, usando ainda A2 como meu número n e em C2 a função =FATO(A2):

fatorial no Excel

Não faz diferença, o resultado dará os mesmos 24 da função PERMUT, como era esperado.

Permutação Circular

Problema: “André, Bianca, Carlos e Diana, de mãos dadas formando uma roda, podem ser dispostos de quantas maneiras diferentes?”

roda de amigos

Como dá para adivinhar, é um caso de permutação circular. Na permutação simples, a resposta seria 24, mas André, Bianca, Carlos e Diana, se estiverem ocupando as posições 1, 2, 3 e 4 de um círculo, não estarão formando uma roda diferente se ocuparem as posições 2, 3, 4 e 1 ou 3, 4, 1 e 2 ou 4, 1, 2 e 3. Eles apenas giraram a roda e permaneceram na mesma posição relativa, como pode ser observado na imagem:

rodaamigos2Todos permanecendo na mesma disposição relativa não irá caracterizar um círculo diferente, vão ficar girando e girando até ficarem tontos, mas a disposição será a mesma. Portanto, o número de permutações em que se considera disposição em círculo é menor do que o número de permutações simples.

Abaixo, a resposta no Excel, usei a célula A2 com o número 4 e em B2 coloquei a fórmula =FATO(A2-1), lembrando que em outras versões do Excel, a fórmula pode ser =FATORIAL(A2-1).

permutação circular no Excel

Permutação com Elementos Repetidos

Se eu quiser saber quantos anagramas de 4 letras diferentes formo com a palavra ANEL, vou obter a resposta 24, exatamente como no problema das bolas, só que ao invés de bolas de 4 cores, terei as letras A, N, E e L no lugar de cada bola. Os 24 anagramas serão:

ANEL, NELA, ELAN, LANE, ANLE, NLEA, LEAN, EANL, NAEL, AELN, ELNA, LNAE, AENL, ENLA, NLAE, LAEN, LNEA, NEAL, EALN, ALNE, NALE, ALEN, LENA e ENAL

Mas se a palavra fosse, ao invés de ANEL, a palavra MALA? A letra A está repetida, portanto quando troco o primeiro A da palavra MALA pelo segundo A não formo um anagrama diferente. Portanto é um caso em que devo usar a formula da PERMUTAÇÃO com ELEMENTOS REPETIDOS. Para o caso de MALA, a fórmula do Excel ficaria sendo =FATO(4)/(FATO(2)) o que daria 12. Veja a fórmula para n em B3, n1 em B4 e o cálculo sendo feito em B5:

permutação com elementos repetidos no excel

Os 12 anagramas obtidos serão: MALA, ALAM, LAMA, AMAL, MAAL, AALM, ALMA, LMAA, AMLA, MLAA, LAAM e AAML.

Esse foi um caso simples, mas se eu tivesse várias letras repetidas como em ARARA em que a letra R repete duas vezes e a letra A repete 3? Quantos anagramas de 5 letras são possíveis?

Olhe os anagramas formados: RRAAA, RARAA, RAARA, RAAAR, ARRAA, ARARA, ARAAR, AARRA, AARAR e AAARR.

Nesse caso, minha fórmula teria o n=5 (5 letras) o n1 seria 2 (temos 2 letras R) e n2 seria 3 (são 3 letras A) e para os valores 5, 2 e 3 respectivamente nas células B3, B4 e B5 a fórmula no Excel ficaria:

cálculo de permutação circular no Excel

Como este problema precisa de uma fórmula montada caso a caso, o ideal seria criar uma função definida pelo usuário (usando VBA) com entradas através de um intervalo, por exemplo, como é na função SOMA do Excel. Para ver como fazer isso, o que foge um pouco do nível básico da nossa postagem, deixarei para as próximas postagens…

Na próxima postagem mostrarei os casos de Arranjos e Combinações com repetição: http://xek.me/?p=1097

Análise Combinatória no Excel – Combinação Simples

Como eu resolvo exercícios de análise combinatória no Excel – COMBINAÇÃO usando o Excel 2010?

bolinhasEm um concurso que eu fiz recentemente, em uma das questões de RACIOCÍNIO LÓGICO, perguntaram: “um conjunto A tem 8 elementos distintos, quantos subconjuntos de A com 5 elementos diferentes posso criar?”

Em primeiro lugar, eu sei que é uma questão de COMBINAÇÃO e não de ARRANJO porque a ordem dos elementos nos subconjuntos não importa, trocar eles de ordem não caracterizará um subconjunto diferente. Se eu escolhesse os elementos A, B, C, D e E em outra ordem, o subconjunto ainda seria o mesmo.

Bom, esta é uma questão de análise combinatória e posso resolver no EXCEL 2010 usando a função COMBIN, que usa a seguinte fórmula matemática:

fórmula combinação no excel

Se na célula A2 eu colocar o número 8 e em B2 o número 5, na célula C2 posso usar a fórmula =COMBIN(A2;B2) (como pode ser visto na imagem):

função COMBIN do Excel

Em C2 obtenho a resposta correta: o número 56.

Que outros problemas podem ser usados por esta fórmula no Excel?

Todos os problemas de análise combinatória que envolvam combinação simples (isto é: sem repetição). Exemplos:

1) Calcular quantas combinações de números são possíveis de serem sorteados na Megasena (n=60 e p=6, basta colocar estes números em A2 e B2);

2) Quantas equipes diferentes de 11 jogadores de futebol posso colocar em campo numa convocação de 23 jogadores da seleção – supor que não existam posições certas, isto é, posso montar um time sem nenhum goleiro ou, pelo contrário, com todos os goleiros que convoquei (n=23 e p=11);

3) De quantas maneiras podemos combinar sabores de uma casquinha de sorvete com 2 bolas de sorvete se temos 12 sabores diferentes à disposição, mas não queremos duas bolas do mesmo sabor e não faz diferença que sabor vai em cima ou embaixo (n=12 e p=2);

4) Quantas combinações de livros posso escolher para levar para uma viagem, se tenho espaço para 3 livros na mala e possuo 10 livros para escolher (n=10 e p=3);

5) Uma caixa tem 6 bolas de cores diferentes, quantas combinações diferentes de 3 bolas posso tirar de lá (n=6 e p=3).

Na próxima postagem mostro como resolver problemas de arranjos no Excel 2010: http://xek.me/?p=1057

Habilitando Macros no Excel 2010

Para habilitar macros na versão EXCEL 2010, faça isso:

1)Na aba Arquivo, procure por opções, irá abrir uma janela como esta:

excel

2)Selecione a opção Central de Confiabilidade (à esquerda) e clique no botão Configurações da Central de Confiabilidade, abrirá outra janela:

opções macros

3)Selecione a opção Configurações de Macro (à esquerda) e marque a opção Habilitar todas as macros

Agora os programas com macros já podem ser abertos pelo EXCEL!

LINKS para jogos de macros em VBA para o EXCEL:

http://www.tudosobrexcel.com/jogos_avancados/

http://pcworld.uol.com.br/downloads/2008/07/23/dez-games-classicos-todos-gratuitos-para-baixar-e-jogar-no-excel/

http://www.joguinhosantigos.com/2008/07/joguinhos-para-excel.html

 

Uso de parênteses em fórmulas no Excel

Uso de parênteses no Excel

exemplo excel

Após analisar os exemplos acima, descreva passo a passo como faço para resolver no EXCEL as seguintes fórmulas matemáticas:

fórmulas matemáticas no Excel

Explicação como proceder:

Exercicio A proceder com =RAIZ(a2 + 2)
Exercicio B proceder com =b2/(c2+d2)
Exercicio C proceder com =(1+(1/C2))^2

Link de pagina da web com explicação detalhada: http://alunos.naotemnome.com/Quest%e3o%203/Pasta1.htm

Link de power point com explicação detalhada: http://alunos.naotemnome.com.br/1ºpasso.pptx

Definir nome para um intervalo no Excel 2010

Definir nome para um intervalo

Tenho uma planilha e desejo dar um nome para um intervalo, explique (a) para que serve (qual é a utilidade) e (b) como faço isso passo a passo.

Resposta:

a) Para que serve dar nomes a intervalos? Essa opção serve para diminuirmos a quantidade de células de um certo intervalo em uma futura função e também para facilitar no uso de varios intervalos.

b) Como fazemos isso?
1) Devemos selecionar o intervalo que queremos nomear, após
2) clicamos com o botão direito do mouse e
3) vamos até a opção: DEFINIR NOME, irá abrir uma janela onde
4) colocaremos o nome desejado para o intervalo selecionado. (Observação: podemos também ir na aba FÓRMULAS na opção DEFINIR NOME.)
Link de pagina da web com explicação detalhada:

http://alunos.naotemnome.com.br/Para definir nome no intervalo do excel 2010 basta seguir estes passos.htm

Link de vídeo com explicação detalhada: http://vimeo.com/41741119