У меня есть три ячейки, любая комбинация которых может быть пустой. Я хочу проверить, равны ли все непустые ячейки. Если ячейки 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
решение1
Вы уже ответили себе макросом, но вот решение не на VBA. Это формула массива, и ее нужно подтвердить с помощью ctrl+shift+enter:
=(SUM(IFERROR(1/COUNTIF(A1:A3,A1:A3),0))=1)
Эта формула подсчитывает количество уникальных значений в вашем диапазоне, игнорируя пустые ячейки. Если количество уникальных значений равно 1, то все значения одинаковы, и формула возвращает ИСТИНА. Единственное, что не было указано в вашем вопросе, это что делать, если все ячейки пустые. Сейчас формула возвращает ИСТИНА, но было бы легко добавить дополнительную логику, чтобы изменить это.
решение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
решение3
Попробуйте этот маленькийУДФ():
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
ПРИМЕЧАНИЕ:
Ячейки не обязательно должны быть смежными иУДФ()будет работать как для числовых, так и для текстовых ячеек.
решение4
Пытаться:
=COUNTA(A:A)=COUNTIF(A:A,A1)
В основном подсчитываем количество непустых ячеек. Подсчитываем количество ячеек, соответствующих первой ячейке. Если они одинаковые, то они все должны быть одинаковыми. Неважно, кого вы считаете для 2-го COUNTIF... так как он вряд ли будет равен COUNTA, если они не все одинаковы ;)
[править] если ваша первая ячейка может быть пустой .. попробуйте это вместо этого:
=COUNTA(A:A)=COUNTIF(A:A,VLOOKUP("*",A:A,1,FALSE))
он попытается найти первую непустую ячейку для проверки в СЧЕТЕСЛИ.