Fórmula para excel (retorna o maior número para subtração de células diferentes e quais células foram usadas para resposta)

Question

Estou tentando retornar o maior número para a diferença de células diferentes - ou seja, retornar o maior número após o cálculo a1 - b1 , a1 - c1 , b1 - c1 . Assumindo

a1=3 
b1=2
c1=1 

o número retornado é 2 (como a1=3 minus c1=1 gives 2 ). Eu também quero ser capaz de dizer que a resposta veio especificamente de a1 - c1 , ou qualquer que tenha sido o caso. A quantidade real de células é muito maior que as três, portanto, fazer cada equação em uma célula separada não é viável.

    

Answer 1

A seguinte função definida pelo usuário (UDF) irá lidar com qualquer número de células (não apenas três) :

Public Function MaxDiff(rIN As Range) As String
    Dim r1 As Range, r2 As Range, WhichOnes As String
    Dim v As Double, diff As Double
    Dim difftemp As Double

    diff = 0
    For Each r1 In rIN
        v = r1.Value
        For Each r2 In rIN
            difftemp = Abs(v - r2.Value)
            If difftemp > diff Then
                diff = difftemp
                WhichOnes = r1.Address(0, 0) & "-" & r2.Address(0, 0)
            End If
        Next r2
    Next r1
    MaxDiff = CStr(diff) & " -> " & WhichOnes
End Function

Fornece o valor máximo da diferença absoluta de células no intervalo, bem como o endereço dessas células. Por exemplo:

FunçõesDefinidaspeloUsuário(UDFs)sãomuitofáceisdeinstalareusar:

1. ALT-F11exibeajaneladoVBE
2. ALT-IALT-Mabreumnovomódulo
3. coleomaterialefecheajaneladoVBE

Sevocêsalvarapastadetrabalho,aUDFserásalvacomela.SevocêestiverusandoumaversãodoExcelposteriora2003,deverásalvaroarquivocomo.xlsmemvezde.xlsx

PararemoveroUDF:

1. abrirajaneladoVBEcomoacima
2. limpeocódigo
3. fecheajaneladoVBE

ParausaroUDFdoExcel:

=MaxDiff(A1:Z1)

Parasabermaissobremacrosemgeral,consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!

    

Answer 2

Três fórmulas:

Como a diferença máxima sempre será a diferença entre o número máximo e o número mínimo para encontrar a diferença:

=MAX($A$1:$A$10)-MIN($A$1:$A$10)

Para encontrar os endereços:

MAX:

=ADDRESS(AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=MAX($A$1:$A$10)),1),AGGREGATE(15,6,COLUMN($A$1:$A$10)/($A$1:$A$10=MAX($A$1:$A$10)),1))

MIN:

=ADDRESS(AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=MIN($A$1:$A$10)),1),AGGREGATE(15,6,COLUMN($A$1:$A$10)/($A$1:$A$10=MIN($A$1:$A$10)),1))

Se você quiser todos eles em um celular como @ Gary'sStudent, simplesmente concatene-os:

=MAX($A$1:$A$10)-MIN($A$1:$A$10) & "->" & ADDRESS(AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=MAX($A$1:$A$10)),1),AGGREGATE(15,6,COLUMN($A$1:$A$10)/($A$1:$A$10=MAX($A$1:$A$10)),1)) & "-" & ADDRESS(AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=MIN($A$1:$A$10)),1),AGGREGATE(15,6,COLUMN($A$1:$A$10)/($A$1:$A$10=MIN($A$1:$A$10)),1))