☰

每年新老客户

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
每年新老客户

我正在尝试确定每年新顾客和回头客的数量。

样本清单

根据上表，我列出了新客户和回头客，但这个过程是手动完成的，我确实有很多客户需要通过这种方式识别。

新/回头客名单

请问是否有任何公式可以让我确定每年新客户/回头客的数量。

答案1

如果您可以使用 VBA，只需单击按钮即可获得解决方案。建议的解决方案主要使用公式，很少支持 VBA，并且是两步解决方案。

由于您使用的是数据透视表，通过交换数据透视表中的行和列，可以使您的数据以更易于查看的格式显示，即将年份设为行，将客户设为列。

将您的工作簿另存为 xlsm（启用宏的 Excel 工作簿）在表格所在的工作表中按 ALT + F11 打开 VBA 编辑器。从“插入”菜单中插入一个模块并将以下代码粘贴到其中。保存代码和工作簿。

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
    For Each cell In cellrng
        If ignore_empty = False Then
            result = result & cell & delimiter
        Else
            If cell <> "" Then
                result = result & cell & delimiter
            End If
        End If
    Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function

这是一个 TEXTJOIN 函数。此功能在 Office 365 订阅中可用，但在其他版本中不可用。VBA 代码继承自以下文章。

来源 https://www.get-digital-help.com/2016/06/07/textjoin-function/

在此处输入图片描述

您需要创建一个类似的表，用 1 填充，将其转换为 OLD 和 NEW，如上图所示，此示例中从第 9 行开始。

单元格 B9 中的公式

=IF(ISBLANK(B5),"",IF(ISERROR(MATCH(B5,B4,0)),"NEW","OLD"))

单元格 B10 中的公式

=IF(ISBLANK(B6),"",IF(ISERROR(MATCH(B6,B4:B5,0)),"NEW","OLD"))

单元格 B11 中的公式

=IF(ISBLANK(B7),"",IF(ISERROR(MATCH(B7,B4:B6,0)),"NEW","OLD"))

现在将这三个公式拖到表格的长度上。您的 1 不会转换为相关的 OLD 和 NEW。这是步骤 1。

在其下方创建一个年份表，并创建两列“旧”和“新”

B15 中的公式

=CONCATENATE(COUNTIF(B9:I9,"OLD"),"(",TEXTJOIN(",",TRUE,IF(B9:I9="OLD",$B$3:$I$3,"")),")")

按 CTRL+SHIFT+ENTER 创建数组公式。现在您将看到用花括号括起来的公式，以及该单元格中所需的年份结果。将此公式向下拖动到所需的年份数。

同样，在 C15 中输入以下数组公式

=CONCATENATE(COUNTIF(B9:I9,"NEW"),"(",TEXTJOIN(",",TRUE,IF(B9:I9="NEW",$B$3:$I$3,"")),")")

并将其向下拖动。

这是一个两步过程。对于更好的版本，您可以选择完整的 VBA 代码，只需单击按钮即可实现此目的。

由于自定义创建了 TEXTJOIN 函数 VBA 代码，此解决方案仅适用于此工作簿。但是，如果您有 Office 365 订阅，那么它应该可以在 Excel 2016 中无缝运行。

答案2

也许条件格式可以在这里发挥作用。在下表中，新客户为绿色，回头客为蓝色。

在此处输入图片描述

要成为新客户，单元格必须 = 1，并且左侧的所有单元格必须为空白；要成为回头客，单元格必须 = 1，并且任何左侧单元格的总数必须为 1。

因此一个简单的 AND() 函数就可以区分这些。

要为新客户设置格式，请选择 C2:F10 并从格式菜单中选择“条件格式...”。添加新规则并选择“经典”作为样式。从下拉列表中选择“使用公式...”，选择格式样式，然后在框中输入此公式：

=AND(SUM($B2:B2)=0,C2=1)

对于回头客，添加另一条规则并使用以下公式：

=AND(SUM($B2:B2)>0,C2=1)

注意：根据您使用的 Excel 版本，执行此操作的步骤可能有所不同。关键是找到允许您使用公式设置格式的对话框。

如果您不想要条件格式，您可以在工作表的另一部分使用类似的公式：

=IF(AND(SUM($B2:B2)=0,C2=1),"New","")

=IF(AND(SUM($B2:B2)>0,C2=1),"Repeat","")

向右向下填写这些信息可以识别新客户和回头客。

希望这对您有所帮助，感谢您清楚地解释了您要做什么。我记得您之前的问题，添加小表格显示哪些客户是新客户，哪些是回头客，这样就更清楚了。

相关内容