基于多个条件的值计数

Question

首先，我想感谢你们所有人阅读这篇文章，并帮助我走出困境。我有一个我无法解决的问题，阅读Excel的提示和技巧也没有帮助。

我有一个表(下面只有更多的行)，我必须获得一个唯一的计数，一个人在家里工作了多少次，他/她去了办公室多少次。我想要的结果是：

​

​

我已经用尽了我的智慧和希望你的帮助。

part1 of the table part2 of the table

表：

PERSON NUMBER   PERSON NAME         DATE            REPORTED HOU    LOCATION
7272            John Doe            2021-03-01      3               WFH
7272            John Doe            2021-03-01      1               WFH
7272            John Doe            2021-03-01      2               WFH
7272            John Doe            2021-03-01      2               WFH
7272            John Doe            2021-03-02      2               WFH
7272            John Doe            2021-03-02      2               WFH
7272            John Doe            2021-03-02      2               WFH
7272            John Doe            2021-03-03      2               WFH
7272            John Doe            2021-03-03      1               WFH
7272            John Doe            2021-03-03      1               WFH
7272            John Doe            2021-03-03      2               WFH
7272            John Doe            2021-03-03      2               WFH
7272            John Doe            2021-03-04      2               WFH
7272            John Doe            2021-03-04      1               WFH
7272            John Doe            2021-03-04      3               WFH
7272            John Doe            2021-03-04      1               WFH
7272            John Doe            2021-03-04      1               WFH
7272            John Doe            2021-03-05      2               WFH
7272            John Doe            2021-03-05      1               WFH
7272            John Doe            2021-03-05      1               WFH
7272            John Doe            2021-03-05      2               WFH
7272            John Doe            2021-03-05      2               WFH
7272            John Doe            2021-03-08      3               WFH
7272            John Doe            2021-03-08      2               WFH
7272            John Doe            2021-03-08      1               WFH
7272            John Doe            2021-03-08      2               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-09      3               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-10      1               WFH
7272            John Doe            2021-03-10      1               WFH
7272            John Doe            2021-03-10      1               WFH
7272            John Doe            2021-03-10      5               WFH
7272            John Doe            2021-03-02      2               WFH
7273            Jane Doe            2021-03-01      3               WFH
7273            Jane Doe            2021-03-01      1               WFH
7273            Jane Doe            2021-03-01      2               WFH
7273            Jane Doe            2021-03-01      2               WFH
7273            Jane Doe            2021-03-02      2               Office
7273            Jane Doe            2021-03-02      2               Office
7273            Jane Doe            2021-03-02      2               Office
7273            Jane Doe            2021-03-03      2               WFH
7273            Jane Doe            2021-03-03      1               WFH
7273            Jane Doe            2021-03-03      1               WFH
7273            Jane Doe            2021-03-03      2               WFH
7273            Jane Doe            2021-03-03      2               WFH
7273            Jane Doe            2021-03-04      2               Office
7273            Jane Doe            2021-03-04      1               Office
7273            Jane Doe            2021-03-04      3               Office
7273            Jane Doe            2021-03-04      1               Office
7273            Jane Doe            2021-03-04      1               Office
7273            Jane Doe            2021-03-05      2               WFH
7273            Jane Doe            2021-03-05      1               WFH
7273            Jane Doe            2021-03-05      1               WFH
7273            Jane Doe            2021-03-05      2               WFH
7273            Jane Doe            2021-03-05      2               WFH
7273            Jane Doe            2021-03-08      3               Office
7273            Jane Doe            2021-03-08      2               Office
7273            Jane Doe            2021-03-08      1               Office
7273            Jane Doe            2021-03-08      2               Office
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-09      3               WFH
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-10      1               Office
7273            Jane Doe            2021-03-10      1               Office
7273            Jane Doe            2021-03-10      1               Office
7273            Jane Doe            2021-03-10      5               Office
7273            Jane Doe            2021-03-10      2               Office

Answer 1

您可以使用SUMPRODUCT和COUNTIF对具有多个条件的唯一记录进行计数：

https://www.ablebits.com/office-addins-blog/2016/04/07/how-to-count-distinct-and-unique-values-in-excel/

我得到了这个：

​

​

我在H5中的公式是：

=SUMPRODUCT(IFERROR(1/COUNTIFS($B$2:$B$75;$B$2:$B$75;$C$2:$C$75;$C$2:$C$75;$E$2:$E$75;$E$2:$E$75;$B$2:$B$75;$G5;$E$2:$E$75;H$4);0))

如您所见，它返回预期的输出(请注意，我的头文件是WFH和Office)

Answer 2

如果您对VBA解决方案感兴趣--

从VBA编辑器添加一个引用(Tools -> References... )到Microsoft Data Objects (最新版本；通常是6.1)。

然后，您可以编写如下代码：

Const filepath As String = "C:\path\to\excel\file.xlsx"
Const sheetname As String = "Sheet1"

Dim connectionString As String
connectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & filepath & """;" & _
    "Extended Properties=""Excel 12.0;HDR=Yes"""

    ' If your data is in a macro-enabled file (.xlsm), the previous line should
    ' look like this:
    ' "Extended Properties=""Excel 12.0 Macro;HDR=No"""

Dim sql As String
sql = _
    "SELECT [PERSON NAME], COUNT(*) " & _
    "FROM [" & sheetname & "$] " & _
    "GROUP BY [PERSON NAME]"

' If your data is only part of the worksheet, you can specify the range as follows:
' sql = _
'    "SELECT [PERSON NAME], COUNT(*) " & _
'    "FROM [" & sheetname & "A1:E10000$] " & _
'    "GROUP BY [PERSON NAME]"

Dim rs As New ADODB.Recordset
rs.Open sql, connectionString

Worksheets.Add.Range("A1").CopyFromRecordset(rs)

此代码将向活动工作簿添加一个新工作表，其中包含分组的数据。

参考：

Microsoft Access SQL (在查询Excel文件时使用)：

• Access SQL: basic concepts, vocabulary, and syntax
• Reference

ADODB：

• object - method

Excel：

• collection - method
• object - property
• object - method

Answer 3

这与Foxfire和Burns and Burns的答案是一致的：

可以使用unique()自动生成唯一的名称列表，如下所示：

​

​

但是，可以使用名称副本和“删除重复项”按钮手动构建它。