Собственная формула, чтобы проверить, равен ли диапазон непустых ячеек

Question

У меня есть три клетки, любая комбинация которых может быть пустой. Я хочу проверить, все ли непустые ячейки равны. Если ячейки 1 и 2 имеют значение, а ячейка 3 пуста, я хочу, чтобы формула возвращала значение ИСТИНА, если ячейки 1 и 2 равны.

Если для этого нет нативной формулы, я просто напишу макрос VBA.

Обновление: на самом деле быстрее было просто написать макрос VBA. Я являюсь разработчиком .NET/c # и давно забыл о своем VBA, поэтому я открыт для улучшений в моем коде здесь (особенно в том, что касается установки возвращаемого значения и выхода из функции).

Public Function NonblankValuesAreEqual(cells As Range) As Boolean

Dim lastval As String

lastval = cells(1).Value

For i = 2 To cells.Count
    If lastval <> "" Then
        If cells(i).Value <> "" Then
            If cells(i).Value <> lastval Then
                NonblankValuesAreEqual = False
                Exit Function
            End If
        End If
    End If
    lastval = cells(i).Value
Next

NonblankValuesAreEqual = True

End Function

Answer 1

Вы уже ответили сами с помощью макроса, но здесь есть решение, отличное от VBA. Это формула массива, которая должна быть подтверждена ctrl+shift+enter:

=(SUM(IFERROR(1/COUNTIF(A1:A3,A1:A3),0))=1)

Эта формула подсчитывает количество уникальных значений в вашем диапазоне, игнорируя пустые ячейки. Если число уникальных значений равно 1, то все значения одинаковы и формула возвращает TRUE. Единственное, что не было указано в вашем вопросе, это что делать, если каждая ячейка пуста. Прямо сейчас формула вернет TRUE, но было бы легко добавить дополнительную логику, чтобы изменить это.

Answer 2

Убедитесь, что каждая пара столбцов точно равна (с учетом регистра) или содержит пробел.

=OR(EXACT(A2,B2),ISBLANK(A2),ISBLANK(B2))
=OR(EXACT(A2,C2),ISBLANK(A2),ISBLANK(C2))
=OR(EXACT(B2,C2),ISBLANK(B2),ISBLANK(C2))

=AND(D2:F2)

Пример:

A   B   C   AB      AC      BC      AND
1   1   1   TRUE    TRUE    TRUE    TRUE
1   1       TRUE    TRUE    TRUE    TRUE
A           TRUE    TRUE    TRUE    TRUE
    A       TRUE    TRUE    TRUE    TRUE
        A   TRUE    TRUE    TRUE    TRUE
a   A   a   FALSE   TRUE    FALSE   FALSE
a   a       TRUE    TRUE    TRUE    TRUE
a       2   TRUE    FALSE   TRUE    FALSE
    A   A   TRUE    TRUE    TRUE    TRUE
A       A   TRUE    TRUE    TRUE    TRUE
    A   B   TRUE    TRUE    FALSE   FALSE
A   B   C   FALSE   FALSE   FALSE   FALSE

Примечание. Для больших наборов количество смежных столбцов значительно возрастет: n! / 2

Answer 3

Пытаться:

=COUNTA(A:A)=COUNTIF(A:A,A1)

В основном посчитайте количество непустых клеток. Подсчитайте количество ячеек, соответствующих первой ячейке. Если они одинаковы, то все они должны быть одинаковыми. Неважно, кого вы считаете за 2-й СЧЕТЧИК ... так как он, скорее всего, не будет равен СЧЕТУ, если они не будут одинаковыми;)

[править] если ваша первая ячейка может быть пустой ... попробуйте вместо этого:

=COUNTA(A:A)=COUNTIF(A:A,VLOOKUP("*",A:A,1,FALSE))

он попытается найти первую непустую ячейку для проверки в COUNTIF.

Answer 4

Попробуйте этот маленький UDF():

Public Function EqualTest(r1 As Range, r2 As Range, r3 As Range) As Variant
   Dim BlankCount As Long, v1 As Variant, v2 As Variant, v3 As Variant
   v1 = r1.Value
   v2 = r2.Value
   v3 = r3.Value
   BlankCount = 0
   If v1 = "" Then BlankCount = BlankCount + 1
   If v2 = "" Then BlankCount = BlankCount + 1
   If v3 = "" Then BlankCount = BlankCount + 1

   If BlankCount > 1 Then
      EqualTest = True
      Exit Function
   End If

   If BlankCount = 0 Then
      If v1 = v2 And v1 = v3 And v2 = v3 Then
         EqualTest = True
         Exit Function
      Else
         EqualTest = False
         Exit Function
      End If
   End If

   If v1 = v2 Or v1 = v3 Or v2 = v3 Then
      EqualTest = True
   Else
      EqualTest = False
   End If
End Function

НОТА:

Ячейки не должны быть смежными, и UDF() будет работать как для числовых, так и для текстовых ячеек.