Aplicando uma fórmula única a um intervalo de células

Question

Eu tenho uma grande variedade de dados contendo valores numéricos. mas devido ao mais recente requisito para o valor máximo e mínimo dos dados numéricos, eu tenho que modificar um monte de células que definem o valor de cada célula para o seu valor atual, exceto se estiver fora do intervalo, nesse caso eu deveria ter apenas o mínimo valor ou o máximo e armazená-lo na mesma célula.

Note que não quero armazenar a fórmula no arquivo excel, apenas aplique-a a um intervalo de valores once e armazene na célula o valor do resultado e não a fórmula.

Vou tentar deixar isso mais claro. Suponha que eu tenha na linha 1 os valores nas colunas A, B e C:

20 42 53

Eu quero selecionar o intervalo A1: C1 e aplicar a cada um a fórmula:

= if($cell > 50; 50; if($cell < 25;25; $cell))

Eu usei $ cell para denotar a célula atual onde o cálculo ocorre, isso não funciona, é apenas para ilustrar o que estou tentando fazer.

Como resultado, espero:

25 42 50

Mas mais uma vez, apenas os valores e não a fórmula. Observe também que não posso escrever a fórmula acima na célula A1 para essa célula porque é uma referência circular, o que gera um erro.

Uma abordagem possível seria ter duas planilhas, uma com os valores atuais e a segunda com a fórmula com os valores na faixa correta, mas isso não é possível porque o arquivo é processado por um programa que não espera fórmulas que espera várias folhas.

Espero ter deixado claro o meu ponto de vista.

    

Answer 1

Na verdade, a solução é bem simples e acabou de me acertar alguns minutos atrás. Eu poderia alcançar meu objetivo escrevendo uma simples macro vba que apenas aplica os intervalos desejados sobre as células selecionadas. O código:

Function applyMakeInRange(minVal As Double, maxVal As Double, cells As Range)

Dim cellVal As Double

For Each cell In cells.cells
    cellVal = cell.Value

    If cellVal > maxVal Then
        cellVal = maxVal
    Else
        If cellVal < minVal Then
            cellVal = minVal
        End If
    End If

    cell.Value = cellVal

Next

End Function

Sub makeInRange()

Dim maxVal As Double
Dim minVal As Double
Dim cells As Range

Set cells = Selection

maxVal = InputBox("Max Value")
minVal = InputBox("Min Value")

res = applyMakeInRange(minVal, maxVal, cells)

End Sub

Simplesmente salvando o arquivo como sem macros (uma vez executado) deve fazer o truque

    

Answer 2

As células no Excel contêm fórmulas ou valores. Você pode usar a célula atual em uma fórmula, mas ela é ignorada para computação.

Como você mencionou, você pode ter os valores em uma página e as fórmulas em outra. Não tenho certeza de como esse outro programa está processando o arquivo do Excel, mas se você salvá-lo em algo como um CSV, apenas os valores serão exportados (e você especificará qual folha será exportada).

A única outra opção que consigo pensar seria criar uma nova linha / coluna usando uma fórmula para modificar os valores. Em seguida, use Copiar / Colar especial para colar apenas os valores modificados nas células originais.

    

Answer 3

Suponho que você queira sobrepor uma fórmula a um intervalo de células que já contém dados. Se for esse o caso, acho que a única maneira de conseguir isso é com o VBA que seleciona o (s) intervalo (s) em questão e aplica uma fórmula quando esses intervalos são preenchidos. Você também pode ocultar linhas se quiser usar fórmulas diretas, mas isso provavelmente não é o ideal.