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.

 

Análise Combinatória no Excel 2010: Combinação, Arranjo ou Permutação

Análise Combinatória no Excel: Como diferencio quando é combinação, arranjo ou permutação, se é simples ou com repetição para calcular no Excel?

abcdDificilmente sem alguma prática conseguimos diferenciar os casos de repetição dos casos simples, dos casos de arranjo dos casos de combinação e assim por diante. É preciso um pouquinho de estudo e paciência para conseguir identificar cada caso! Vou tentar mostrar exemplos aqui e pra começar vamos tentar investigar essas diferenças. Análise Combinatória no Excel é o tema desta postagem e das últimas 4 postagens, por sinal. São elas (clique nos títulos para ver as postagens):

Combinação e Arranjo Com Repetição no Excel 2010

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

Excel – Arranjo Simples: Análise Combinatória

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

Tabela completa de fórmulas

Em primeiro lugar, é hora de revisar todas as fórmulas usadas nas últimas postagens. Aqui vai o resumo de tudo:

tabela fórmulas análise combinatória Excelpermutação de bolas de 4 coresCom a tabela acima resolvemos QUASE todos os casos de Análise Combinatória no Excel, faltando alguns casos em que o problema requer raciocínio mais do que fórmulas (por isso não abordaremos aqui)

Bom, vamos começar pelo caso mais simples: permutação. Permutação é quando troco tudo de lugar e como estou usando todos os elementos de um conjunto. Também posso imaginar a permutação como um arranjo em que n=p, assim fica menos uma fórmula para decorar… De qualquer forma, imagine as 4 primeiras letras do alfabeto: se eu trocar todas de lugar, quantas possibilidades eu tenho? Ora, apenas trocando todas de lugar, é um caso de permutação simples (ou arranjo simples onde n=p=4), como visto em outra postagem, obterei as seguintes 24 combinações mostradas ao lado.

Um caso mais complicado, seria uma permutação com repetição, (não mostrada na postagem anterior sobre repetições e nem na postagem sobre permutações), que é, na verdade, um arranjo com repetição em que n=p. Por exemplo, quando quero formar uma senha com as 4 primeiras letras do alfabeto; uma senha permite a repetição de letras e trocando elas de ordem tenho outra senha diferente. Usando a fórmula mostrada na tabela acima para arranjos com repetição, coloque no Excel em qualquer célula a fórmula =POTÊNCIA(4;4) e obteremos a resposta 256,  que é 4 elevado na quarta potência. Quer saber quais são todas as 256 senhas possíveis? Estão aqui: AAAA, AAAB, AAAC, AAAD, AABA, AABB, AABC, AABD, AACA, AACB, AACC, AACD, AADA, AADB, AADC, AADD, ABAA, ABAB, ABAC, ABAD, ABBA, ABBB, ABBC, ABBD, ABCA, ABCB, ABCC, ABCD, ABDA, ABDB, ABDC, ABDD, ACAA, ACAB, ACAC, ACAD, ACBA, ACBB, ACBC, ACBD, ACCA, ACCB, ACCC, ACCD, ACDA, ACDB, ACDC, ACDD, ADAA, ADAB, ADAC, ADAD, ADBA, ADBB, ADBC, ADBD, ADCA, ADCB, ADCC, ADCD, ADDA, ADDB, ADDC, ADDD, BAAA, BAAB, BAAC, BAAD, BABA, BABB, BABC, BABD, BACA, BACB, BACC, BACD, BADA, BADB, BADC, BADD, BBAA, BBAB, BBAC, BBAD, BBBA, BBBB, BBBC, BBBD, BBCA, BBCB, BBCC, BBCD, BBDA, BBDB, BBDC, BBDD, BCAA, BCAB, BCAC, BCAD, BCBA, BCBB, BCBC, BCBD, BCCA, BCCB, BCCC, BCCD, BCDA, BCDB, BCDC, BCDD, BDAA, BDAB, BDAC, BDAD, BDBA, BDBB, BDBC, BDBD, BDCA, BDCB, BDCC, BDCD, BDDA, BDDB, BDDC, BDDD, CAAA, CAAB, CAAC, CAAD, CABA, CABB, CABC, CABD, CACA, CACB, CACC, CACD, CADA, CADB, CADC, CADD, CBAA, CBAB, CBAC, CBAD, CBBA, CBBB, CBBC, CBBD, CBCA, CBCB, CBCC, CBCD, CBDA, CBDB, CBDC, CBDD, CCAA, CCAB, CCAC, CCAD, CCBA, CCBB, CCBC, CCBD, CCCA, CCCB, CCCC, CCCD, CCDA, CCDB, CCDC, CCDD, CDAA, CDAB, CDAC, CDAD, CDBA, CDBB, CDBC, CDBD, CDCA, CDCB, CDCC, CDCD, CDDA, CDDB, CDDC, CDDD, DAAA, DAAB, DAAC, DAAD, DABA, DABB, DABC, DABD, DACA, DACB, DACC, DACD, DADA, DADB, DADC, DADD, DBAA, DBAB, DBAC, DBAD, DBBA, DBBB, DBBC, DBBD, DBCA, DBCB, DBCC, DBCD, DBDA, DBDB, DBDC, DBDD, DCAA, DCAB, DCAC, DCAD, DCBA, DCBB, DCBC, DCBD, DCCA, DCCB, DCCC, DCCD, DCDA, DCDB, DCDC, DCDD, DDAA, DDAB, DDAC, DDAD, DDBA, DDBB, DDBC, DDBD, DDCA, DDCB, DDCC, DDCD, DDDA, DDDB, DDDC e DDDD

Observe que no caso anterior, se levássemos em consideração que numa senha as letras podem ser maiúsculas ou minúsculas, teríamos arranjo com repetição usando as letras a, b, c, d, A, B, C ou D em que n=8 e p=4 e  a fórmula ficaria =POTÊNCIA(8;4), dando um total de 4096 senhas diferentes. É por isso que é muito importante usarmos combinações de maiúsculas e minúsculas ao criar uma senha, fica muito mais difícil de alguém descobrir! Não vou listar aqui as 4096 senhas, porque iria ocupar muito espaço… :-)

Para diferenciar entre ARRANJO COM REPETIÇÃO de ARRANJO SIMPLES de COMBINAÇÃO COM REPETIÇÃO de COMBINAÇÃO SIMPLES montei o seguinte esquema: quero obter, ainda com as 4 primeiras letras do alfabeto, todas as senhas possíveis de 2 letras. Como já vimos na postagem sobre Arranjos com Repetição (http://xek.me/?p=1097), a fórmula é =POTÊNCIA(4;2) no Excel…

Eis todas as 16 possíveis senhas:

arranjo com repetição

tabela 1

O que fizemos foi, praticamente, um “produto cartesiano” de dois conjuntos com as mesmas 4 letras em cada um. Agora imagine que a partir do arranjo com repetições acima gostaríamos de ter um arranjo sem repetições, o que mudaria? Teríamos de retirar  da tabela acima as letras que aparecem repetidas, AA, BB, CC e DD, que são as linhas (I), (VI), (XI) e (XVI) e ficaríamos com:

arranjo simples 2 a 2

tabela 2

Note que o cálculo de =PERMUT(4;2) no Excel vai dar 12, que é o que esperávamos para a tabela acima de arranjos sem repetição, conforme vimos na postagem sobre arranjos simples (http://xek.me/?p=1057).

Agora pegue novamente a tabela 1 com seus 16 arranjos com repetição e suponha que queremos as COMBINAÇÕES COM REPETIÇÃO. O que teria de sair da tabela 1? Sabemos que, neste caso, uma combinação “AB” será igual a “BA”, por exemplo. Teremos de tirar todos os caso da tabela em que uma dupla aparece novamente, só que invertida. Ou seja teríamos de tirar (V), (IX), (X), (XIII), (XIV) e  o (XV) que correspondem aos arranjos BA, CA, CB, DA, DB e DC e ficaríamos com:

arranjo simples

tabela 3

A COMBINAÇÃO COM REPETIÇÃO não tem uma função própria no Excel, precisamos montar uma fórmula. Para isto, devemos usar =FATORIAL(4+2-1) / (FATORIAL(2) * FATORIAL(4-1)) no Excel ou, dependendo da versão, =FATO(4+2-1) / (FATO(2) * FATO(4-1))). Vimos este cálculo na postagem anterior quando mostramos vários problemas de Arranjos e Combinações com Repetição (http://xek.me/?p=1097).

Em uma próxima postagem, onde falaremos sobre criação de funções definidas pelo usuário, mostraremos como criar uma função par calcular a combinação com repetição.

Esta tabela 3, acima, da COMBINAÇÃO COM REPETIÇÃO ainda pode ser alvo de uma nova modificação. Pegue esta tabela 3 e retire todas as repetições, ou seja, retire AA, BB, CC e DD, que são as linhas (I), (VI), (XI) e (XVI) e ficaremos com a COMBINAÇÃO SIMPLES:

combinação simples

tabela 4

Como vimos na postagem sobre COMBINAÇÃO SIMPLES, o cálculo no Excel é feito com =COMBIN(4;2) em alguma célula da planilha e resultará no número 6. Para detalhes adicionais, dê uma revisada na nossa primeira postagem sobre Análise Combinatória no Excel – Combinação Simples em http://xek.me/?p=1049.

Espero que a explanação acima sobre Análise Combinatória no Excel tenha ajudado!

Um último problema

Imagine o seguinte cenário: você precisa saber quantos jogos são possíveis de serem realizados entre 4 times, é Combinação, Arranjo, Simples ou com Repetição? Há duas respostas possíveis. Em primeiro lugar sabemos que não há repetição, já que um time não joga contra ele mesmo. Em segundo lugar, precisamos considerar se um jogo AxB é o mesmo jogo BxA. Se considerarmos que A versus B é diferente de B versus A porque o time citado em primeiro tem o mando de campo, teremos um arranjo pois, se a ordem não importasse seria combinação, mas como a ordem fará diferença, é um arranjo simples. No Excel isso seria o resultado da tabela 2, que já calculamos.

Na próxima postagem, onde falaremos sobre criação de funções definidas pelo usuário criaremos uma função par calcular a combinação com repetição.

Combinação e Arranjo Com Repetição no Excel 2010

Como Calcular Combinação e Arranjo Com Repetição no Excel 2010?

1 2 3 4 5 6 7 8 9Combinação Simples (http://xek.me/?p=1049) e Arranjo Simples (http://xek.me/?p=1057) já foram vistos nas postagens anteriores, além das permutações simples, permutações circulares e permutações com repetição de elementos (http://xek.me/?p=1077). O que faltam inventar para complicar nossa cabeça? A combinação e arranjo com repetição, claro…

Em primeiro lugar vamos revisar as fórmulas necessárias para resolver a parada no nosso querido Excel, lembrando que o Excel apresenta notações diversas para fatorial, às vezes usando FATO( ) e às vezes usando FATORIAL( ) dependendo da versão utilizada:

fórmulas de combinação e arranjo no Excel

A próxima postagem fará um resumo sobre o que vimos até agora, mostrando todas as fórmulas utilizadas em todas as postagens sobre análise combinatória usando Excel – Análise Combinatória no Excel 2010: Combinação, Arranjo ou Permutação – Para vê-la, clique aqui: http://xek.me/?p=1099.

Combinação Simples

Um exemplo típico do cálculo de combinação sem repetição (simples) é tendo 9 bolas numeradas de 1 a 9 dentro de uma urna, quero saber quantos subconjuntos diferentes de 3 bolas posso formar. Este, aliás é o princípio do sorteio com bolinhas (tipo Mega Sena, bingo, etc), pois uma vez que só temos uma bolinha com cada número, a mesma não poderá ser retirada duas vezes. Já vimos como calcular isso no Excel usando a fórmula da combinação simples onde n=9 e p=3, basta colocar em qualquer célula do Excel a função =COMBIN(9,3) para obter a resposta 84.

Combinação com Repetição

Mas quando um problema assim passa ser um problema com repetições? No caso de termos várias bolas numeradas com os números 1 a 9 ou no caso em que a bolinha sorteado pudesse ser colocada de volta numa urna (por exemplo) o mesmo número poderia participar mais de uma vez do subgrupo, isto é, eu poderia ter subconjuntos como (1, 1, 1) ou (1, 1, 2), etc. Em relação ao problema mostrado na seção anterior, combinação simples, ainda temos n= 9 e p=3, mas temos de usar a fórmula da combinação com repetição.  Neste caso, o Excel não tem uma função pronta para o cálculo e teríamos de usar a fórmula =FATO(9+3-1) / (FATO(3) * FATO(9-1)) em alguma célula qualquer do Excel para obter 165. Lembre-se que em algumas versões do Excel, não usamos FATO, mas FATORIAL.

São outros problemas que envolvem combinação com repetição:

1) Uma cantina serve, numa promoção, pratos com 3 porções de alimentos que podem ser: arroz, feijão, purê, couve ou omelete. De quantas formas distintas posso montar um prato, sabendo que o cliente pode escolher qualquer alimento disponível inclusive mais de uma porção do alimento, desde que tenha exatamente 3 porções em cada prato? Resposta: n=5, p=3, Combinação com repetição (um prato com arroz, feijão e purê é o mesmo que purê, feijão e arroz, logo é combinação, mas podemos ter 2 ou 3 porções do mesmo alimento como quem gosta de feijão, feijão e feijão, logo é com repetição), portanto ficaria no Excel =FATO(5+3-1) / (FATO(3)*FATO(5-1)) ou =FATORIAL(5+3-1) / (FATORIAL(3)*FATORIAL(5-1)) que deve dar 35;

2) Uma empresa, na páscoa, pinta ovos de uma única cor, quantas alternativas há para pintar 10 ovos iguais sendo que só estão disponíveis apenas 4 cores? Resposta: mudando os ovos de lugar não muda nada e as cores podem ser repetidas, logo é um caso de Combinação com repetição com n=10 e p=4;

3) Um conjunto C={1, 2, 3, 4, 5, 6} pode formar quantos subconjuntos diferentes contendo 2 elementos? Como um subconjunto {1, 2} é o mesmo que um subconjunto {2, 1} não é um caso de arranjo, mas os conjuntos {1, 1} ou {2, 2}, por exemplo, podem ser criados, logo há repetição. É um caso de combinação com repetição com n=6 e p=2.

Arranjos Simples

Arranjo simples ou sem repetição, como já vimos em outra postagem, é um caso facilmente resolvido no Excel com a função PERMUT. Um exemplo de arranjo simples é quando quero montar tabela de jogos em que a ordem dos times tenha importância (o primeiro é o mandante do jogo). Outro caso é quando quero números a partir de um conjunto de algarismos em que não posso repetir os algarismos. O número 123 é diferente do número 321, por exemplo. Para rever como se faz, dê uma olhada na postagem sobre Arranjo Simples (http://xek.me/?p=1057).

Arranjos Com Repetição

Quando os números que quero montar a partir de algarismos podem conter algarismos repetidos tenho um caso de Arranjo com repetição.

Exemplo: com os 9 algarismos significativos (isto é, sem o zero), quero saber quantos números diferentes posso ter usando apenas 4 algarismos.

Sabemos que teremos os números de 1111 até 9999 retirando todos os números que tem algum algarismo zero… Mas como chegamos lá? Usamos a fórmula do arranjo com repetição, com n=9 e p =4 e colocaremos =POTÊNCIA(9;4) em alguma célula do Excel o que resultará em 6561.

Outros exemplos de problemas de arranjos com repetição:

1) Uma moeda é jogada 4 vezes, uma depois da outra. Quantas sequências de resultado são possíveis? n=4 p=2, use  =POTÊNCIA(4;2) no Excel;

2) Quantas palavras com 3 letras podemos formar com as 26 letras de nosso alfabeto? n=26 p=3, use  =POTÊNCIA(26;3) no Excel;

3) Quantas senhas de 4 dígitos diferentes posso formar usando apenas os algarismos? n=10 e p=4, use  =POTÊNCIA(10;4) no Excel.

Nossa próxima postagem será a última sobre análise combinatória através do Excel – Análise Combinatória no Excel 2010: Combinação, Arranjo ou Permutação – o link é: http://xek.me/?p=1099.

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

Excel – Arranjo Simples: Análise Combinatória

Como eu resolvo questões de análise combinatória na versão 2010 do Excel – ARRANJO simples sem repetições?

Na última postagem (http://xek.me/?p=1049) eu mostrei como calcular combinações simples no Excel com a função COMBIN. Entretanto, se eu quiser calcular um arranjo, como faço? O Excel não tem uma função chamada ARRANJO!

8 primeiras letras do alfabetoPor exemplo: “Criei uma senha de 5 letras maiúsculas a partir das primeiras 8 letras do alfabeto. Entretanto, esqueci minha senha! Quero, por tentativa e erro, descobrir a senha que eu criei e esqueci. Quantas possíveis senhas diferentes existem para serem tentadas?”

Este é um problema de Excel – ARRANJO e não de COMBINAÇÃO, porque além de pegar um subconjunto de 5 letras de um conjunto de 8 (que como visto na postagem anterior seriam apenas 56 combinações), cada subconjunto formado pode sofrer uma permutação e será uma nova senha! ABCDE será diferente de BCDEA que será diferente de ABCED, etc. Serão 120 permutações para cada subconjunto!

Bom, o problema é que na hora de traduzir, ao invés de usar o nome ARRANJO, usaram PERMUT, que lembra permutação. A permutação é um caso de arranjo em que estamos usando todos os elementos de um conjunto (e que, igualmente, portanto, a ordem importa). A fórmula do arranjo, na matemática e a função que a calcula no Excel podem ser vistas abaixo:

Excel - Arranjo

Excel – Arranjo Simples

Como usar no EXCEL?

Simples: coloque na célula A2 o valor de n e na célula B2 o valor de p, no nosso exemplo, 8 e 5. Na célula C coloque a fórmula =PERMUT(A2;B2) conforme a imagem:

arranjo no excel

Função permut calcula arranjos

Assim que for teclado Enter, deve aparecer o resultado. Não fique chocado ao saber que mesmo usando letras maiúsculas numa senha de 5 letras que utiliza apenas as 8 primeiras letras do alfabeto, o número de senhas possíveis é enorme! O resultado também poderia ser obtido a partir do número de combinações (que era 56) multiplicado pelas permutações possíveis de cada grupo de 5 letras, ou seja, a permutação de 5 elementos dá o o fatorial de 5 (que é 120). 56*120 é o número de senhas possíveis para este problema, vai levar um bom tempo para testar todas as senhas… Mas nem são muitas, se conseguir testar uma senha a cada 10 segundos consegue achar a senha em menos de 10 horas… :-)

Que outros problemas resolvo com a fórmula do ARRANJO?

Exemplos de problemas:

1) Quantas ordenações de 4 cartas são possíveis fazer com um baralho de 52 cartas em que a ordem das cartas tiradas tenha importância (n=52 e p=4);

2) A partir de um nome de 4 letras que não possua letras repetidas (Jaci, Igor, Gina, etc.) quantas “palavras” diferentes de 2 letras posso formar – supor que cada 2 letras forma uma palavra, mesmo que não tenha sentido (n=4 e p=2);

3) Considerando todas as vogais de nosso alfabeto, quantos são os encontros vocálicos (2 vogais) de vogais diferentes que são possíveis na nossa língua (n=5 p=2);

4) 10 seleções participam de uma fase eliminatória para a Copa do Mundo de Futebol, quantos possíveis jogos inaugurais posso ter, sabendo que cada jogo tem 2 seleções e que a primeira seleção escolhida tem o mando de campo (n=10 e p=2);

5) Considerando uma corrida de Fórmula 1 com 20 participantes, quais o número total de possibilidades diferentes de 3 pilotos ocuparem o pódio? Lembre que o pódio tem 3 lugares diferentes (n=20 e p=3);

6) Uma ilha tem 7 pontes, qual é o número total de maneiras distintas de chegar na ilha e sair por uma ponte diferente (n=7 e p=2).

Existem ainda casos de Arranjos com repetição (eu poderia usar letras repetidas na minha senha, por exemplo) que veremos em uma próxima oportunidade (ver postagem http://xek.me/?p=1097).

Na próxima postagem abordaremos o caso mais simples de ser resolvido no Excel – permutação. Já adiantando o que veremos na próxima postagem, num arranjo, quando n e p são iguais, temos um caso de permutação: http://xek.me/?p=1077

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