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所示对名片做一些颜色、边框等方面的美化，再添加操作描述即可。