Hi,各位同学好!我是吴明课堂的答疑老师之一陈婉。祝大家一切顺利,平安快乐!

今天我给大家分享的是一对多查询和多对多查询的公式应用场景。

工作场景描述:

有位在某销售公司工作的同学,想统计手下员工的客户咨询工作量,作为绩效的综合评估因素之一。

如果是计算客户咨询数量,他会用countif(单条件)和countifs(多条件)解决,但他想直观地看到每个员工接待了哪些客户。

总结一下:这位同学是想根据员工名称去查询到对应的多个客户名称。


已知信息收集:

现有一张Excel表格,记录了每天的客户接待信息,表结构如下图(根据学员提供的文件虚构而成,且只虚构了查询所需相关数据,如有雷同,纯属巧合):

准时下班系列!Excel合集之第8集—一对多和多对多查询的解决方案_公式


数据分析需求:

  1. 查找当月(当前工作表内的都是当月数据)每个员工分别接待了哪些客户​

  2. 查找当月每天每个员工具体接待了哪些客户


详细公式展示:

重要提醒:

  1. 需要用Excel2021版或Excel365版,因为其中的textjoin、unique和filter等函数只有Excel2021及以上版本支持;原Excel2019版也支持这些函数,但Office2021版出来后,渐渐移到了2021版;

  2. WPS官网下载的最新版WPS软件,也支持这些函数,但WPS表格不能完全正确解析所有的数组公式。

  • 查找当月(当前工作表内的都是当月数据)每个员工分别接待了哪些客户

  • 方案1:使用vlookup函数实现

准时下班系列!Excel合集之第8集—一对多和多对多查询的解决方案_多对多查询_02

  • 方案2:使用filter函数实现

准时下班系列!Excel合集之第8集—一对多和多对多查询的解决方案_一对多查询_03


  • 方案3:使用index+small+row函数实现1对多查询

准时下班系列!Excel合集之第8集—一对多和多对多查询的解决方案_一对多查询_04



    • 方案4: 使用if函数实现

    • 准时下班系列!Excel合集之第8集—一对多和多对多查询的解决方案_公式_05



  • 查找当月每天每个员工具体接待了哪些客户

    • 方案1:if函数实现

    • 准时下班系列!Excel合集之第8集—一对多和多对多查询的解决方案_一对多查询_06


    • 方案2:index+small+row函数实现

    • 准时下班系列!Excel合集之第8集—一对多和多对多查询的解决方案_公式_07


    • 方案3:filter函数实现

    • 准时下班系列!Excel合集之第8集—一对多和多对多查询的解决方案_函数实现_08


该案例中所使用的公式及公式分析方法,均可在吴明老师的《Excel综合进阶课程》第10章公式与函数篇中学到。

如需系统学习Excel使用,可查看课程链接:  《吴明老师Excel综合课程》​​

该课程可以使学员以最少的学习时间,搭建完善的Excel知识架构。