如果所有Excel列值不一致，返回。

I have the following data in an Excel Spreadsheet:

我在Excel电子表格中有以下数据:

    A  B  C
 1  b  b  b

I would like to put a formula in the column after the data which compares each (text) value in the row, and returns TRUE if they're all the same ie A=B=C. I would like it to return FALSE if one or more of the values don't agree. ie

我想在数据后面放一个公式，比较行中的每个(文本)值，如果它们都是相同的，则返回TRUE。如果一个或多个值不一致，我希望返回FALSE。即

    A  B  C  D
 1  b  b  b  TRUE
 2  b  e  b  FALSE
 3  e  b  b  FALSE

I'm aware of logical functions like AND, hence could construct something like

我知道像这样的逻辑函数，因此可以构造像这样的东西

AND(A1=B1,A1=C1.. etc), however, this soon gets unwieldy as soon as the number of columns increases.

和(A1 = B1,A1 C1 = . .然而，一旦列的数量增加，这很快就变得难以处理。

Restructuring the data isn't do-able.

重组数据是不可行的。

Does anyone know an OOTB / VB solution?

有人知道OOTB / VB解决方案吗?

3 个解决方案

#1

If the countif finds the same number as the count then they are all the same.

如果countif找到与计数相同的数字，那么它们都是相同的。

=IF(COUNTIF(A1:C1,A1)=COUNTA(A1:C1),"true","false")

Hope this is what you are looking for, you just need to extend the ranges for however many columns you want to test.

希望这是您正在寻找的，您只需要为您想要测试的任何列扩展范围。

Update

更新

As pointed out in the comment this fails to return the right result if the dataset has blank cells.

如注释中所指出的，如果数据集有空白单元格，则无法返回正确的结果。

This will return false even if there is a blank cell in the range:

即使在范围内存在空白单元格，也会返回false:

=IF(AND(COUNTIF(A1:C1;A1)=COUNTA(A1:C1);COUNTBLANK(A1:C1)=0);"true";"false")

#2

Here is a VBA UDF: to make it case sensitive remove Option Compare Text

这里有一个VBA UDF:使它区分大小写，删除选项比较文本

Option Explicit
Option Compare Text
Public Function AllSame(theRange As Range) As Boolean
    Dim vR As Variant
    Dim vC As Variant

    AllSame = True
    vR = theRange
    If IsArray(vR) Then
        vC = vR(1, 1)
        For Each vC In vR
            If vC <> vR(1, 1) Then
                AllSame = False
                Exit For
            End If
        Next vC
    End If
End Function

#3

Although it will be a bit slower, you can also use an array formula (by pressing Ctrl + Shift + Enter after typing it in the cell):

虽然速度会稍慢一些，但您也可以使用数组公式(按Ctrl + Shift + Enter键在单元格中输入):

{=IF(SUM(COUNTIF(A1:C1,A1:C1))=POWER(COUNTA(A1:C1), 2),"true","false")}

This will work even if there are empty cells in the range.

即使范围中有空单元格，这也可以工作。

。

UPDATE:
This array formula also works, by making sure that there's only 1 unique value in a range. It also still returns an accurate value, even if there are blanks in the range:

更新:这个数组公式也可以工作，通过确保在一个范围中只有一个唯一值。它仍然返回一个准确的值，即使有空格在范围内:

{=SUM(IFERROR(1 / COUNTIF(A2:C2,A2:C2), 0)) = 1}

#1