Jump to content
  • Revista PROGRAMAR: Já está disponível a edição #60 da revista programar. Faz já o download aqui!

nokPT

[Resolvido] Excel - Criar uma fómula para gerar 2 resultados em 2 colunas

Recommended Posts

nokPT

Bom dia,

Pretendo fazer uma fórmula / função que devolva 2 resultados em 2 colunas diferentes:

Imagem a seguinte tabela

Coluna A | Coluna B | Coluna C | Coluna D | Coluna E

Dados 1  | Dados 2  | Dados 3  | Result.1  | Result. 2

...

A ideia é chamar a fórmula na Coluna D com os dados da Coluna A, B, C e a fórmula devolver resultado para a Coluna D e para a Coluna E.

Devolver só para uma coluna é fácil, o problema é devolver para a 2ª coluna.

Tentei passar a Coluna E como um Range e escrever lá, mas não funcionou ou não fiz bem.

Estarei a pedir demais?


Ricardo Timóteo

Share this post


Link to post
Share on other sites
pedrotuga

Pelo que sei só tens que criar duas fórmulas, uma na coluna D e uma na coluna E.

Share this post


Link to post
Share on other sites
nokPT

O problema é que o programa é complexo e faz uma série de cálculos de dimensionamento de equipamentos, no final de cada cálculo faz uma série de testes (6 ao todo) se falar num teste, tem que incrementar o valor de acordo com umas tabelas internas, calcular novamente e testar novamente até passar em todos os testes.

Na coluna em que chamo a fórmula coloco o resultado final dos cálculos, nas colunas anexas preciso de colocar o resultados dos testes (são sempre números reais).

Não me é possível calcular o resultado dos testes a partir do resultado final.

Será que posso chamar uma função a partir da minha função de cálculo para escrever na coluna ao lado? Se sim como se faz para passar a coluna a partir da qual estou a função a ser chamada?


Ricardo Timóteo

Share this post


Link to post
Share on other sites
pmg

Talvez uma formula de array te resolva o problema?

Neste momento, e durante o fim de semana, não tenho acesso ao Excel, mas 2a feira posso experimentar :)

A ideia básica é escreveres a função num módulo de código.

Essa função devolve um array (e não um valor).

O array devolvido é "espalhado" pelas células que contêm a fórmula.


What have you tried?

Não respondo a dúvidas por PM

A minha bola de cristal está para compor; deve ficar pronta para a semana.

Torna os teus tópicos mais atractivos e legíveis usando a tag CODE para colorir o código!

Share this post


Link to post
Share on other sites
jpaulino

Uma função devolve apenas um valor para uma célula, por isso existem duas hipoteses:

- Funções encadeadas, ou seja, várias funções em que uma actualiza o resultado de outra (como já foi referido pelo pedrotuga)

Ou

Deixar as funções e criar uma subrotina que calcula e escreve nos locais certos. Este método é mais aconselhável porque é mais rápido e centralizado.

Só precisas ver se existe alguma alteração no range pretendido e executar (de acordo com determinados critérios).

Exemplo:

Option Explicit

' Função auxiliar que valida se o campo é
' numérico e se não está em branco
Function ValidNumber(str As String) As Boolean
    
    If IsNumeric(str) And Len(str) > 0 Then
        ValidNumber = True
    End If
        
End Function


Private Sub Worksheet_Change(ByVal Target As Range)

    ' verifica se foi alterado algo no range A1:C20
    If Not Intersect([A1:C20], Target) Is Nothing Then
        
        Dim colA As Long, colB As Long, colC As Long
        Dim result As Long
         
        ' Verifica se algum valor na linha está em
        ' branco nas colunas A, B e C
        If ValidNumber(Cells(Target.Row, 1).Value) And _
           ValidNumber(Cells(Target.Row, 2).Value) And _
           ValidNumber(Cells(Target.Row, 3).Value) Then
            
            ' Verifica o valor das colunas
            colA = Cells(Target.Row, 1).Value
            colB = Cells(Target.Row, 2).Value
            colC = Cells(Target.Row, 3).Value
            
            ' Soma as colunas A, B e C
            result = colA + colB + colC
            
            ' Escreve os valores nas colunas D e E
            Cells(Target.Row, 4).Value = result
            Cells(Target.Row, 5).Value = result * 1.2 ' IVA
        Else
        
           ' Coloca as colunas D e E em branco
           Cells(Target.Row, 4).Value = ""
           Cells(Target.Row, 5).Value = ""
           
        End If
        
    End If

End Sub

Share this post


Link to post
Share on other sites
pmg

Bom ... não tenho o Excel, mas tenho o OpenOffice Calc :)

No screenshot abaixo, as quatro células seleccionadas tem uma só fórmula de array (CTRL + SHIFT + ENTER).

Essa fórmula calcula a soma dos dois maiores números do array *E* a soma do segundo maior com o terceiro maior.

http://img517.imageshack.us/my.php?image=fxusedvv6.png

E o código usado (não deve ser difícil adaptar ao Excel), sem grandes validações, é o seguinte

REM  *****  BASIC  *****
option explicit

function fxarr(x as array) as array
  dim y(1, 1)
  dim minx, maxx, n as integer
  dim top3(2)

  maxx = ubound(x)
  top3(0) = x(1, 1)
  top3(1) = x(1, 1)
  top3(2) = x(1, 1)
  for n = 2 to maxx step 1
    if x(n, 1) > top3(0) then
      top3(2) = top3(1)
      top3(1) = top3(0)
      top3(0) = x(n, 1)
    else
      if x(n, 1) > top3(1) then
        top3(2) = top3(1)
        top3(1) = x(n, 1)
      else
        if x(n, 1) > top3(2) then
          top3(2) = x(n, 1)
        endif
      endif
    endif
  next n
  y(0, 0) = "top 2"
  y(0, 1) = top3(0) + top3(1)
  y(1, 0) = "bottom 2 of top 3"
  y(1, 1) = top3(1) + top3(2)

  fxarr = y
end function


What have you tried?

Não respondo a dúvidas por PM

A minha bola de cristal está para compor; deve ficar pronta para a semana.

Torna os teus tópicos mais atractivos e legíveis usando a tag CODE para colorir o código!

Share this post


Link to post
Share on other sites
nokPT

Bom dia,

Por motivos de doença estou temporariamente ausente, quando regressar vou testar e tentar converter todas as sugestões que me deram,

Atentamente


Ricardo Timóteo

Share this post


Link to post
Share on other sites
jtiagodias

Confesso que não percebi muito bem o objetivo desta macro.

ALguém me pode clarificar um bocadinho mais? Já saquei o ficheiro mas não consegui perceber muito bem o objectivo...

Share this post


Link to post
Share on other sites
jpaulino

Viste o meu exemplo ?

O pmg depois explica o exemplo dele (ou caso ele demoro eu explico).

Share this post


Link to post
Share on other sites
jpaulino

Bom dia,

Por motivos de doença estou temporariamente ausente, quando regressar vou testar e tentar converter todas as sugestões que me deram,

Atentamente

Em primeiro lugar está sempre a saúde.

Rápidas melhoras.

Share this post


Link to post
Share on other sites
jtiagodias

Viste o meu exemplo ?

O pmg depois explica o exemplo dele (ou caso ele demoro eu explico).

Viva Paulino.

Eu vi mas não entendo concretamente o objectivo. Pelo desculpa ao pessoal mas sou um bocado lento...

ehehehehe

No ficheiro aparecem 2 resultados que vêm de duas somas diferentes contudo, não percebo que parametro contribuem para cada uma dessas somas....

Share this post


Link to post
Share on other sites
pmg
O problema é que o programa é complexo e faz uma série de cálculos de dimensionamento de equipamentos, no final de cada cálculo faz uma série de testes (6 ao todo) se falar num teste, tem que incrementar o valor de acordo com umas tabelas internas, calcular novamente e testar novamente até passar em todos os testes.

Tentei pensar numa coisa parecida com isto e o que me passou pela cabeça foi calcular a soma de valores mais altos do array, SEM PASSAR PELO ARRAY MAIS DO QUE UMA VEZ.

Se fizesse duas fórmulas diferentes, cada fórmula tinha de percorrer o array por si.

Fazendo com fórmula de array (CTRL + SHIFT + ENTER) é possível fazê-lo só uma vez e apresentar os resultados uns ao lado dos outros.

A fórmula que eu fiz trata o array de input (as células especificadas) e devolve um array que vai ocupar 2x2 células na folha de cálculo. Só a fiz criar um resultado 2x2 para exemplificar.

Faz (CTRL + F11) ou (SHIFT + F11) no Excel para aceder aos módulos.

Selecciona 4 células vazias da folha de cálculo, escreve a fórmula e faz (CTRL + SHIFT + ENTER) para ela ser uma fórmula de array; senão é uma fórmula normal e não funciona.

O Excel automaticamente mete uns {} à volta da fórmula para indicar que ela é especial.

{=fxarr(A1:A10)}

A função fxarr() recebe, neste caso, um array com 10 elementos.

Agora já não tenho o Excel outra vez, por isso vai explicação de memória.

No código tenho uma primeira parte para identificar os três valores mais elevados do array: top(0), top(1) e top(2).

Depois foi só construir o resultado final: um array 2x2 em que a coluna da esquerda levou as strings constantes e a coluna da direita levou as somas "pretendidas".

Espero ter ajudado, se for preciso amanhã volto ao código Excel e encho-o de comentários.


What have you tried?

Não respondo a dúvidas por PM

A minha bola de cristal está para compor; deve ficar pronta para a semana.

Torna os teus tópicos mais atractivos e legíveis usando a tag CODE para colorir o código!

Share this post


Link to post
Share on other sites
jtiagodias

Tentei pensar numa coisa parecida com isto e o que me passou pela cabeça foi calcular a soma de valores mais altos do array, SEM PASSAR PELO ARRAY MAIS DO QUE UMA VEZ.

Se fizesse duas fórmulas diferentes, cada fórmula tinha de percorrer o array por si.

Fazendo com fórmula de array (CTRL + SHIFT + ENTER) é possível fazê-lo só uma vez e apresentar os resultados uns ao lado dos outros.

A fórmula que eu fiz trata o array de input (as células especificadas) e devolve um array que vai ocupar 2x2 células na folha de cálculo. Só a fiz criar um resultado 2x2 para exemplificar.

Faz (CTRL + F11) ou (SHIFT + F11) no Excel para aceder aos módulos.

Selecciona 4 células vazias da folha de cálculo, escreve a fórmula e faz (CTRL + SHIFT + ENTER) para ela ser uma fórmula de array; senão é uma fórmula normal e não funciona.

O Excel automaticamente mete uns {} à volta da fórmula para indicar que ela é especial.

{=fxarr(A1:A10)}

A função fxarr() recebe, neste caso, um array com 10 elementos.

Agora já não tenho o Excel outra vez, por isso vai explicação de memória.

No código tenho uma primeira parte para identificar os três valores mais elevados do array: top(0), top(1) e top(2).

Depois foi só construir o resultado final: um array 2x2 em que a coluna da esquerda levou as strings constantes e a coluna da direita levou as somas "pretendidas".

Espero ter ajudado, se for preciso amanhã volto ao código Excel e encho-o de comentários.

Bom...

Boa explicação.. Acho que já percebi melhor o objectivo e vou tentar alicar em algumas das minhas folhas de cálculo...

Vou apenas ter que ir perceber como consigo meter as fórmulas porque o meu está em português e o Ctrl+Shif´+Enter nao funciona...

ABraço e obrigado

Share this post


Link to post
Share on other sites
jpaulino

Isto é totalmente fora do tópico mas aqui vai ...

Um formula de array o que faz é percorrer um range e executar cada célula individualmente e no fim produz um resultado. Para entenderes como funciona faz o seguinte: Na coluna A de A1 a A15 colocar várias palavras (de 1 a 5 letras). Depois coloca esta formula em outra célula:

=SUM(IF(LEN(A1:A15)=3;1;0))

Quando colocares a formula carrega ao mesmo tempo em CTRL+SHIFT+ENTER e aparecem as chavetas (Não tem nada a ver com o Excel estar em Português)

Isto o que faz é ... verifica em cada linha se o número de caracteres é igual a 3 - len(célula) - caso seja 3 coloca um 1, caso contrário 0. Depois existe um somatório que contabiliza o total de 1's no range.

Espero que dê para entender.

Share this post


Link to post
Share on other sites
jpaulino

Por falta de informação do utilizador e por existirem respostas aceitáveis à pergunta efectuada, esta questão foi marcada como resolvida.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.