孙凡丽

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

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

接下来在B2单元格中输入公式“=IF（CHAR（6 5+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单元格中输入公式“= I F（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 ， M A T C H（$E$2，客户信息表！D：D，0），MATCH （ D3 ，客户信息表！A$1： H$1， 0 ）） ， IF （ IF ERROR （ FIND （ " 1 "， $E$2 ） ， 0 ）= 1 ， INDE X （ 客户信息表！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 ，" "，CONCATEN ATE （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（COU NT I F（客户信息表！ $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所示对名片做一些颜色、边框等方面的美化，再添加操作描述即可。