Excel巧妙制作客户的动态名片表

笔者的Excel文档中以便于输入的横排方式保存了很多客户的联系信息,在查找某个客户的信息时直接使用Excel的筛选、查找功能,得到的关联信息为横排显示,看起来不太直观。于是笔者就利用公式建立了一个动态名片表(图1),在E2单元格中任意输入姓名、固话、手机号码或者通过下拉箭头选择后,可以自动辨识信息类别,并自动显示名片的其他栏目。这样查询到的关联信息会以名片的形式显示,而且还能查到所有同名、同固话的客户。下面笔者以Excel 2019为例介绍具体的操作过程。

一、 限定查询信息和名片内容

打开“客户信息表”(图2),接着新建一个“辅助表”,在A1单元格中输入“名片栏目”、B1单元格中输入“限定查询信息”,然后选中A2:A8数据区域,在公式栏中输入“=TRANSPOSE(客户信息表!B1:H1)”并按下“Ctrl+Shift+Enter”组合键,将名片栏目转换为按列显示。

接下来在B2单元格中输入公式“=IF(CHAR(65+ROUNDUP(ROW(A1)/COUNT(客户信息表!A:A),0))>"D","/",INDIRECT("客户信息表!"&CHAR(65+ROUNDUP(ROW(A1)/COUNT(客户信息表!A:A),0))&MOD(ROW(A1)-1,COUNT(客户信息表!A:A))+2))”,下拉填充到显示“/”为止,可以将姓名、手机号、固话这三类信息全部合并到B列中(图3)。

公式解释:利用行号和“客户信息表”中序号的关系,按其比值算出循环次数,用CHAR函数将循环次数转换为列号字母B、C和D,字母E及以后超出查询范围,故显示“/”。再按其除余算出引用行号,实现将多列信息合并到一列中。

二、 建立动态名片表

1、设置输入查询信息单元格的数据有效性

新建一个“动态名片表”并定位到E2单元格,依次点击“数据→数据验证→设置”,在弹出的对话框中将“允许”设置为“序列”、“来源”选择“=辅助表!B2:B31”(可以将该区域设置得更大一些,如B2:B100,这样更方便后续添加新数据),同时勾选“忽略空值”和“提供下拉箭头”(图4)。

2、设置自动辨识信息类别

在D2单元格中输入公式“=IF(IFERROR(FIND("-",$E$2),0)>1,辅助表!A4,IF(IFERROR(FIND("1",$E$2),0)=1,辅助表!A3,辅助表!A2))”。

3、设置顺序显示除D2单元格外的其他名片栏目

在D3单元格中输入公式“=IF(IF(D$2=辅助表!$A2,辅助表!$A3,辅助表!$A2)=D2,辅助表!$A3,IF(D$2=辅助表!$A2,辅助表!$A3,辅助表!$A2))”,下拉填充到D8单元格。

4、引出名片内容

在E3单元格中输入公式“=IF(IFERROR(FIND("-",$E$2),0)>1,INDEX(客户信息表!A:H,MATCH($E$2,客户信息表!D:D,0),MATCH(D3,客户信息表!A$1:H$1,0)),IF(IFERROR(FIND("1",$E$2),0)=1,INDEX(客户信息表!A:H,MATCH($E$2,客户信息表!C:C,0),MATCH(D3,客户信息表!A$1:H$1,0)),INDEX(客户信息表!A:H,MATCH($E$2,客户信息表!B:B,0),MATCH(D3,客户信息表!A$1:H$1,0))))”,下拉填充到E8单元格。

三、 查重名、重固话情况

切换到“辅助表”,在C1单元格中输入“查重名”、D1单元格中输入“查重固话”,接着在C2单元格中输入公式“=IF(客户信息表!B2="","",COUNTIF(客户信息表!B$2:B2,动态名片表!$E$2))”、D2单元格中输入公式“=IF(客户信息表!D2="","",COUNTIF(客户信息表!D$2:D2,动态名片表!$E$2))”,分别下拉填充公式到显示空白为止(图5)。

然后切换到“动态名片表”,在F1单元格中输入公式“=IF(IFERROR(FIND("-",$E$2),0)>1,IF(COUNTIF(客户信息表!$D:$D,$E$2)<2,"",CONCATENATE(COUNTIF(客户信息表!$D:$D,$E$2),"人同用")),IF(COUNTIF(客户信息表!$B:$B,$E$2)<2,"",CONCATENATE(COUNTIF(客户信息表!$B:$B,$E$2),"人同名")))”,设置遇到重名、重固话时的提示信息。在F2单元格中输入公式“=IF(IFERROR(FIND("-",$E$2),0)>1,IF(COUNTIF(客户信息表!$D:$D,$E$2)>COLUMN(A1),$E$2,""),IF(COUNTIF(客户信息表!$B:$B,$E$2)>COLUMN(A1),$E$2,""))”,向右填充到H2单元格,公式的作用是显示重复的姓名和固话。

为了能突出显示“动态名片表”中出现重名和重固话的情况,可以为F1单元格设置条件格式,依次点击“开始→条件格式→新建规则”,在打开的窗口中,在“为符合此公式的值设置格式”处输入“=$F$3<>""”、“格式”选择填充亮黄色(图6)。G1单元格、H1单元格操作类似。

接下来在F3单元格中输入公式“=IF(IFERROR(FIND("-",$E$2),0)>1,IF(COUNTIF(客户信息表!$D:$D,$E$2)>COLUMN(A1),INDEX(客户信息表!$A:$H,MATCH(COLUMN(B1),辅助表!$D:$D,0),MATCH($D3,客户信息表!$A$1:$H$1,0)),""),IF(COUNTIF(客户信息表!$B:$B,$E$2)>COLUMN(A1),INDEX(客户信息表!$A:$H,MATCH(COLUMN(B1),辅助表!$C:$C,0),MATCH($D3,客户信息表!$A$1:$H$1,0)),""))”,向下填充公式到F8单元格,设置动态显示重名、重固话的名片内容。然后选中F3:F8数据区域并向右填充到H3:H8数据区域,可按重名、重固话的最多数量调整填充的列标范围,可以设置得更大一些,如K3:K8。

最后,参照图1所示对名片做一些颜色、边框等方面的美化,再添加操作描述即可。