Формула для Excel (вернуть наибольшее число для вычитания различных ячеек и какие ячейки были использованы для ответа)

Question

Я пытаюсь вернуть наибольшее число для разницы разных ячеек - т.е. вернуть наибольшее число после вычисления a1 - b1, a1 - c1, b1 - c1. Если предположить,

a1=3 
b1=2
c1=1 

тогда возвращаемое число равно 2 (поскольку a1=3 minus c1=1 gives 2). Я также хочу иметь возможность сказать, что ответ пришел именно от a1 - c1, или как бы там ни было. Фактическое количество ячеек намного выше, чем три, поэтому каждое уравнение в отдельной ячейке невозможно.

Answer 1

Следующая пользовательская функция (UDF) будет обрабатывать любое количество ячеек (не только три):

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

Это дает как максимальное значение абсолютной разности ячеек в диапазоне, так и адрес этих ячеек. Например:

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

=MaxDiff(A1:Z1)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!

Answer 2

Три формулы:

Поскольку максимальная разница всегда будет разницей между максимальным числом и минимальным числом, чтобы найти разницу:

=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))

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

Если вы хотите, чтобы они все были в одной ячейке, такой как @ Gary'sStudent, просто объедините их:

=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))