IF函数筛选不重复项,神准!:客户名出现多遍,你只需提取一次

article_cover

询盘表,是Excel做得最多的表格之一,


销售外贸人手必备。


通常做法是,一个询盘视为一个单号;


再对应排列填写序号、询价日期、客户名字、询问产品、回复情况等……


但这里,你能每次控制不一样的是询盘单号,


而其他项呢?


是会变动的啊!是会重复的啊!


比如前几天,一位粉丝宝宝遇上棘手问题,发来邮件问:


  • 筛选时,怎样让客户名字不重复出现?




在这里,张先生发了询盘两次,李先生也是,所以他们在粉红色表格里必须分别被填两次名字。


可是,如果直接对  客户名  ,按平常那样用  数据验证  做筛选。


他俩的名字,在右边的  下拉序列  里就会重样了,如上图示里的红框标记。


所以,粉丝宝宝想得到解决:


  • 筛选时,能自动检验客户名是否重复,并且去掉重复的。


最后让每一个客户在  下拉序列  里,名字只被填一遍。


如下图,张先生和李先生的名字,刚刚是两个,现在都只剩下一个啦。


◎ 效果展示


怎么做的呀?


经过解题宝宝多次拆解,终于发现有两组神奇的IF函数能帮上忙。


操作不难,好用到炸裂呐!


来听解题宝宝跟你讲解叭。




1 标记不重复项


首先,我们要算出  D列  里,有多少个客户名字是重复哒。



 Countif  函数能帮我们实现,它的作用是对特定条件计算。


那么,这个特定条件,我们可以用  If  逻辑判定函数来设定。


规则如下:


  • 从张先生起,往下对所有名字从1开始报数。


  • 如果下一个名字是新的,报数就递增,报2、3、4……


  • 如果下一个名字是出现过的,就重报上一个数。

 

这个规则,我们使用  辅助列  来操作。


因为它可以被添加到  D列  旁边,对  D列  计算,还可以被隐藏,隐藏后计算照样生效。


这样,你就不需要改变原本表格模样了。



Step 1


  • 在  D列  左侧新添加一列。该列第一行内容(即单元格  D1  )记得为空。


◎ 黄色框为新添加列



Step 2


  • 在单元格  D2  输入公式:
     =IF(COUNTIF($E$2:$E2,E2)=1,D1+1,D1)  


◎ 操作展示


这条公式的意思是:


  • COUNTIF($E$2:$E2,E2)=1  


将单元格  E2  的值(在这里是  张先生  ),对单元格  E2  匹配,看值是否唯一(张先生是否只出现了一次?)。


听起来很绕口,但下拉填充公式后,

比如到单元格  E3  时公式会变成:

 COUNTIF($E$2:$E3,E3)=1  


这样类推后,

就能对单元格  E3   E4  、 E5  ……匹配呐。

 

  • D1+1  - 是  IF  函数中的项。


如果上面条件成立(张先生只出现了一次),那么就报数+1。


现在  D1  为空,即零。


所以当  D2  遇到了张先生第一次,0+1,会从1开始报起。

 

  • D1  - 也是  IF  函数中的项。
 
如果上面条件不成立 (张先生出现了第二次),那么就报上一个数到的数。



Step 3


  • 输入完公式后按  Enter  确认,再鼠标下拉填充公式。


◎ 操作演示


这时你看,如红框所示,当分别第二次遇到  张先生  和  李先生  ,


我可爱的公式,就自动自觉地重复报2。


直到遇到了  谭小姐  ,才开始报3,继续往下递增。


◎ 效果展示


拖到最下面,发现报数到12停止。


这就代表,在这个表格里,


虽然客户名被填进去是15个(灰色最左列所示数字16-1,因为第一行是标题)。


但实际上,真正有效的客户只有12个,其中有3个客户是重名的。


◎ 效果展示


接下来,我们要将这12个有效客户,提取出来。


也就是,提取不重复项。




2 提取不重复项


  • 跟刚刚一样,也是用生成  辅助列  来实现。


Step 1


  • 在  D列  右侧新添加一列。该列第一行内容(即单元格  F1  )记得也是为空。


◎ 效果展示



Step 2


  • 点击单元格  F2  ,输入公式:
     =IF(ISNA(VLOOKUP(ROW(1:1),$D:$E,2,0)),"",VLOOKUP(ROW(1:1),$D:$E,2,0))  


◎ 效果展示


是,解题宝宝知道这公式很长!但别慌!


我们只取重点记下。


  •  ISNA  函数,表示判断一个单元格的值,是否为#N/A 错误值。


  •  Vlookup  函数,在特定区域查找你想要的值。


当  ISNA  函数+两组  Vlookup  函数用在一起表示:


  • 如果第一个  Vlookup  函数查找不到你想要的值,就报错。


  • 反之,如果找得到,就报第二个  Vlookup  函数的结果。

 

在这里,该公式表示:


我们用第一个  Vlookup  函数,


在D列和E列  $D:$E  这个区域中,如黄色框所标示,


查找第一列第一行  ROW(1:1)  的值。


  • 如果找不到,就填  ""  ,就是啥也不填。

  • 如果找得到,就生效第二个  Vlookup  函数,填第二列同一行的值,即单元格  E2  ,值是  张先生  。


而在本表格里,第一列第一行  ROW(1:1)  =单元格  D1  ,如红色箭头指向,  D1  值是空的。


那么,相当于拿  D1  跟  F2  来匹配,看看是否一模一样。



所以你会得到以下结果:


  • 如果  F2  不是空的,就啥也不填。

  • 如果  F2  是空的,就填  张先生  


刚刚在输入公式之前,  F2  就是空的。所以你一填完公式,现在它马上就出现了  张先生  。提取成功!


◎ 效果展示



Step 3


  • 鼠标下拉填充公式,提取剩下的不重复名字。


◎ 操作演示


看,拖到最后,是不是比左边一列少了三个名字?


这时,代表你的  F2  已经自动去除重复的名字,不填进来啦。



Step 4


  • 点击菜单栏  数据  -   数据验证  ,在  允许  处选择  序列  ,在  来源  处选择  F  列,确认完成。

◎ 操作演示


这时,你的下拉列表里,客户名字已经完全没有重复的呐。


  • 但是,还差一步,对着两个辅助列  D列  和  F列  按  Ctrl+0  ,把它们隐藏。

最终效果长这样,如红色框标示,上方已经显示它们被折叠起来啦。


◎ 效果展示



各位粉丝宝宝,

有什么不明白的Excel问题,

欢迎在下方评论区留言,

我会24小时内回复你哒。


如果觉得这篇文章帮到你,

请动动手指“分享”到朋友圈

转发给更多需要的好友呐。


最后,祝你早日成为Excel高手,

解题宝宝相信,你一定可以


6027人看过

评论 (11)

0/500
  • 只恐夜深

    感觉在职场如果不会用Excel,几乎就跟在丛林里裸奔一样难

    01月16日
  • 心藏深海

    这个功能十分强大

    01月15日
  • 不要在黎明前被冻死了

    关于那个筛选里面,没太看懂

    01月14日
  • 限量版的等待

    收藏了!

    01月13日
  • 回忆星空下的繁华

    看上去枯燥无味的工具,还有这么多花招可玩,厉害

    01月13日
  • 不想认识你

    再也不怕数据错误的事情发生了

    01月09日
  • 别离情话

    对着用了一遍,很简单,很好操作..

    01月09日
  • 感情的债温柔依赖

    销售福音!实操可行

    01月06日
  • 厌盛

    这个考试的时候真的是我过不去的坑,要是早点看到就好了😭

    01月04日
  • 你的眼似海却不为我蓝

    刚好用上,收藏

    01月03日
  • 手机

    2019年09月10日
没有更多评论啦~
Skill成长学院
办公生产力研究所