![每年新老客户](https://linux22.com/image/1540468/%E6%AF%8F%E5%B9%B4%E6%96%B0%E8%80%81%E5%AE%A2%E6%88%B7.png)
我正在尝试确定每年新顾客和回头客的数量。
根据上表,我列出了新客户和回头客,但这个过程是手动完成的,我确实有很多客户需要通过这种方式识别。
请问是否有任何公式可以让我确定每年新客户/回头客的数量。
答案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","")
向右向下填写这些信息可以识别新客户和回头客。
希望这对您有所帮助,感谢您清楚地解释了您要做什么。我记得您之前的问题,添加小表格显示哪些客户是新客户,哪些是回头客,这样就更清楚了。