巧妙制作客戶的動(dòng)態(tài)名片表

孫凡麗

一、 限定查詢信息和名片內(nèi)容

打開“客戶信息表”（圖2），接著新建一個(gè)“輔助表”，在A1單元格中輸入“名片欄目”、B1單元格中輸入“限定查詢信息”，然后選中A2：A8數(shù)據(jù)區(qū)域，在公式欄中輸入“=TR ANSPOSE（客戶信息表！B1：H1）”并按下“Ctrl+Shift+Enter”組合鍵，將名片欄目轉(zhuǎn)換為按列顯示。

接下來在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））”，下拉填充到顯示“/”為止，可以將姓名、手機(jī)號(hào)、固話這三類信息全部合并到B列中（圖3）。

公式解釋：利用行號(hào)和“客戶信息表”中序號(hào)的關(guān)系，按其比值算出循環(huán)次數(shù)，用CHAR函數(shù)將循環(huán)次數(shù)轉(zhuǎn)換為列號(hào)字母B、C和D，字母E及以后超出查詢范圍，故顯示“/”。再按其除余算出引用行號(hào)，實(shí)現(xiàn)將多列信息合并到一列中。

二、建立動(dòng)態(tài)名片表

1設(shè)置輸入查詢信息單元格的數(shù)據(jù)有效性

新建一個(gè)“動(dòng)態(tài)名片表”并定位到E2單元格，依次點(diǎn)擊“數(shù)據(jù)→數(shù)據(jù)驗(yàn)證→設(shè)置”，在彈出的對(duì)話框中將“允許”設(shè)置為“序列”、“來源”選擇“=輔助表！B2：B31”（可以將該區(qū)域設(shè)置得更大一些，如B2：B100，這樣更方便后續(xù)添加新數(shù)據(jù)），同時(shí)勾選“忽略空值”和“提供下拉箭頭”（圖4）。

2設(shè)置自動(dòng)辨識(shí)信息類別

在D2單元格中輸入公式“= I F（IFERROR（FIND（"-"，$E$2），0）>1，輔助表！A4，IF（IFERROR（FIND（"1"，$E$2），0）=1，輔助表！A3，輔助表！ A2））”。

3設(shè)置順序顯示除D2單元格外的其他名片欄目

在D3單元格中輸入公式“= IF （ IF （ D$2=輔助表！ $A2 ， 輔助表！ $A3 ， 輔助表！ $A2 ） =D2 ， 輔助表！$A3 ， IF （ D$2= 輔助表！$A2 ，輔助表！ $A3 ，輔助表！$A2））”，下拉填充到D8單元格。

4引出名片內(nèi)容

在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，動(dòng)態(tài)名片表！$E$2））”、D2單元格中輸入公式“=IF（客戶信息表！D2=""，""，COUNTIF（客戶信息表！D$2：D2，動(dòng)態(tài)名片表！$E$2））”，分別下拉填充公式到顯示空白為止（圖5）。

然后切換到“動(dòng)態(tài)名片表”，在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），"人同名"）））”，設(shè)置遇到重名、重固話時(shí)的提示信息。在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單元格，公式的作用是顯示重復(fù)的姓名和固話。

為了能突出顯示“動(dòng)態(tài)名片表”中出現(xiàn)重名和重固話的情況，可以為F1單元格設(shè)置條件格式，依次點(diǎn)擊“開始→條件格式→新建規(guī)則”，在打開的窗口中，在“為符合此公式的值設(shè)置格式”處輸入“=$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單元格，設(shè)置動(dòng)態(tài)顯示重名、重固話的名片內(nèi)容。然后選中F3：F8數(shù)據(jù)區(qū)域并向右填充到H3：H8數(shù)據(jù)區(qū)域，可按重名、重固話的最多數(shù)量調(diào)整填充的列標(biāo)范圍，可以設(shè)置得更大一些，如K3：K8。

最后，參照?qǐng)D1所示對(duì)名片做一些顏色、邊框等方面的美化，再添加操作描述即可。