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

nokPT

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

16 mensagens neste tópico

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?

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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?

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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...

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Viste o meu exemplo ?

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

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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....

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites
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.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

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

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Crie uma conta ou ligue-se para comentar

Só membros podem comentar

Criar nova conta

Registe para ter uma conta na nossa comunidade. É fácil!


Registar nova conta

Entra

Já tem conta? Inicie sessão aqui.


Entrar Agora