当前位置: 首页 > news >正文

excel 一对多数据查询公式 经典用法

所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。

下面咱们就说说一对多查询的典型用法,先看数据源:

A~D列是一些员工信息,要根据F2单元格指定的学历,提取出所有“本科”的人员姓名。

G2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:

=INDEX(C:C,SMALL(IF(B$2:B$11=F$2,ROW($2:$11),4^8),ROW(A1)))&""

公式看起来好长啊,不用担心,咱们一点点的拆解。

先来看这部分:

IF(B$2:B$11=F$2,ROW($2:$11),4^8)

IF函数的意思是判断一个条件是不是成立,如果成立返回第二参数,否则返回第三参数。

本例中,要判断的条件是B$2:B$11=F$2,如果B列的学历等于F2单元格中指定的“本科”,就返回2~11行对应的行号ROW($2:$11),否则返回4^8的结果65536,最终得到一个内存数组的计算结果:

{2;65536;65536;65536;6;65536;65536;65536;10;65536}

把这个结果放到示意图中,会更直观:

有小伙伴会说:为啥用65536啊,先不要着急,咱们一会儿再说。

接下来,再用SMALL函数,在这个内存数组中提取内容。

SMALL函数的作用是返回一组数值中的第n个最小值,比如公式SMALL(A:A,3),就是返回A列中的第三个最小值了。

本例中,SMALL函数用IF函数的计算结果作为第一参数,要在这个内存数组中提取第n个最小值,这里的n由谁来指定呢?就是公式最后部分的ROW(A1)。

ROW(A1)的作用是返回A1单元格的行号,结果是1。当公式向下复制时,参数会依次变成ROW(A2)、ROW(A3)、……,也就是得到从1开始、依次递增的序号。最终的目的是给SMALL函数一个动态的参数,依次从内存数组中提取出第1至n个最小值。

咱们回头再看看上面的示意图,SAMLL函数先提取出内存数组中的第1个最小值,结果是2。

这个2有啥用呢?继续往下看:

下面该轮到INDEX函数出场了,这个函数的作用是根据指定的位置信息,从数据区域返回对应位置的内容。刚刚的2就是位置信息,INDEX函数从C列中返回第二个单元格的内容,结果就是第一个符合条件的姓名“刘一山”。

公式向下复制到G3单元格,ROW(A1)变成了ROW(A2),返回A2的行号2,SMALL函数再从内存数组中提取第2个最小值,结果是几呢?再看看上面的示意图,OK,是6。INDEX函数最终再返回C列中的第6个单元格中的内容“王希建”。

如果所有符合条件的行号都提取完了,公式还向下复制,这个时候SMALL函数的结果就是65536了,最终INDEX函数返回C列第65536个单元格中的内容。

通常情况下,咱们的工作表没有这么多数据,也就是65536是空白单元格,INDEX函数引用空白单元格时,会返回一个无意义的0,所以咱们在公式的最后部分加上一个&"",使无意义的0不再显示。

这里的65536可以是其他任意一个较大的数值,只不过人们使用习惯了,只要你高兴,换成63565、65356都没问题。

还没有看晕的小伙伴,接下来咱们再说说多对多查询的公式。

多对多查询通常分为两种情况:

一是要提取出同时符合多个条件的所有记录。

二是要提取出多个条件符合其一的所有记录。

如下图所示,要提取出性别为“女”,部门为“财务”的所有人员姓名:

这里变成性别和部门两个条件了,两个条件要同时符合,公式怎么用呢?

H2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:

=INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&""

这个公式看起来更长了,不用担心,咱们仔细看看,公式中的大部分内容和前面讲过的公式几乎是一样的,有所不同的地方就是这里:

($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2)

也就是把多个条件分别写到括号内,再用乘号把多个条件对应相乘。只有这几组条件同时符合了,对应相乘后的结果才是1,否则相乘结果是0。

在IF函数的第一参数中,0的作用相当于逻辑值FALSE,不等于0的数值则相当于逻辑值TRUE。也就是两个条件同时符合了,就返回对应的行号,否则返回65536。

公式的其他部分,和前面讲的第一个公式计算过程都是相同的。

多对多查询的第二种情况是多个条件符合其一。

比如下图中,要提取性别为“女”或是部门为“财务”的所有人员姓名。

 

可以在G2单元格输入以下公式:

=INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)+($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&""

这里告诉大家一个处理的小窍门:多个条件分别写到括号内,再用加号把多个条件对应相加就好。

这几组条件中只有一个符合了,对应相加后的结果就不是0,如果所有条件都不符合,相加结果才是0。

其他计算过程和第二个公式是完全一样的

来源:

https://mp.weixin.qq.com/s?__biz=MjM5NTcxODg0MA==&mid=2689814959&idx=1&sn=9a1a4c7f2193c9ea13959348d8831781&chksm=8353c278b4244b6e2a66df584ae99a13861d43c37c448f0ce02bcaed0bb830d4a14803aeb226&scene=21#wechat_redirect

相关文章:

excel 一对多数据查询公式 经典用法

所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。 下面咱们就说说一对多查询的典型用法,先看数据源: A~D列是一些员工信息,要根据F2单元格指定的学历,提取出所有“本科”的人员姓名…...

Zookeeper3.5.7版本——客户端命令行操作(节点删除与查看)

目录一、节点删除示例1.1、节点删除1.2、递归节点删除二、查看节点状态示例一、节点删除示例 1.1、节点删除 在客户端上创建 test 节点,并查看该节点 [zk: localhost:2181(CONNECTED) 5] create /test "123456"删除 test 节点,并查看该节点 […...

一句话设计模式6:享元模式

享元模式:局部单例模式。 文章目录 享元模式:局部单例模式。前言一、享元模式的作用二、如何实现享元模式总结前言 享元模式其实很简单,但是如果用好,确实可以达到减少内存,事半功倍的效果;适合 系统要创建大量相似对象,相同对象等; 一、享元模式的作用 1 享元模式可以解决对象…...

【C语言进阶】文本与二进制操作文件,优化通讯录。

前言:上篇文章,我们已经学习了有关本地磁盘文件的常用文件操作,已经能够对本地文件进行调用与读写。我们磁盘中还存在着一些内容用二进制存储的文件,这也就是我们今天将要讲解的内容。一、文本文件与二进制文件根据数据的组织形式…...

CleanMyMac X4.20最新Mac系统垃圾清理工具

CleanMyMac X是一款Mac系统垃圾清理工具,可以清除Mac系统多余的语言包、系统缓存、应用程序、PowerPc软件运行库等,是硬盘瘦身的好工具。在面对一款多功能型的软件时,复杂的操作面板是最容易让人头疼的,好在 CleanMyMac 一直以来都原生支持简体中文语言&…...

为什么做知识管理,就想选择Baklib呢?

随着科技的不断发展,知识管理已经成为现代企业不可或缺的一个重要组成部分。由于信息化快速发展,企业每天都会产生大量的数据和信息,如何高效地获取、整理和利用这些信息已经成为了企业成功的关键因素之一。为了更好地管理企业知识&#xff0…...

Spring Cloud融合gateway自带GatewayFilter使用 | Spring Cloud 15

一、Spring Cloud Gateway内置GatewayFilter 路由过滤器允许以某种方式修改传入的 HTTP 请求或传出的 HTTP 响应。路由过滤器的范围是特定路由。Spring Cloud Gateway 包括许多内置的 GatewayFilter 工厂。 官网地址:https://docs.spring.io/spring-cloud-gateway…...

SVN 版本控制软件

SVN 版本控制软件 属于C/S结构软件(客户端与服务端) 服务端软件:VisualSVN 网址:Downloads | VisualSVN 下载好:VisualSVN-Server-5.1.3-x64.msi 客户端软件:TortoiseSVN 网址:http://tor…...

全流程基于最新导则下的生态环境影响评价技术方法及图件制作与案例

目录 专题一、生态环境影响评价框架及流程 专题二、基于遥感解译的土地利用现状图的编制 专题三、生物多样性测定及R语言分析 专题四、植被类型及植被覆盖度图的编制 专题五、生物量与净初级生产力测定:实测及模型 专题六、生态系统类型及服务价值评估 专题七…...

(蓝桥真题)分果果(动态规划)

题目链接:P8746 [蓝桥杯 2021 省 A] 分果果 - 洛谷 | 计算机科学教育新生态 (luogu.com.cn) 样例1输入: 5 2 6 1 2 7 9 样例1输出: 0 样例2输入: 5 5 6 1 2 7 9 样例2输出: 2 分析:这道题的状态表…...

【CSS】CSS 背景设置 ① ( 背景颜色 | 背景图片 | 背景平铺 )

文章目录一、背景颜色1、语法说明2、代码示例二、背景图片1、语法说明2、代码示例三、背景平铺一、背景颜色 1、语法说明 CSS 的背景颜色样式语法 : 默认的背景颜色是 transparent 透明 ; background-color:颜色值;background-color 属性 可以 定义 文本颜色 , 其颜色值有三种…...

uniCloud基础使用

获取openID云函数use strict; exports.main async (event, context) > {//event为客户端上传的参数console.log(event : , event)// jscode2session 微信小程序登录接口,获取openidconst {code} event;// 云函数中如需要请求其他http服务,则使用uni…...

5、Elasticsearch优化

一、Elasticsearch集群配置 1、硬件选择 Elasticsearch的基础是 Lucene ,所有的索引和文档数据是存储在本地的磁盘中, 具体的路径可在 ES 的配置文件 ../config/elasticsearch.yml 中配置,如下:磁盘在现代服务器上通常都是瓶颈。…...

地质灾害防治单位资质

地质灾害危险性评估,是指在地质灾害易发区进行工程建设或者编制地质灾害易发区内的国土空间规划时,对建设工程或者规划区遭受山体崩塌、滑坡、泥石流、地面塌陷、地裂缝、地面沉降等地质灾害的可能性和建设工程引发地质灾害的可能性作出评估,…...

打怪升级之发送单个UDP包升级版

目标 1.message的输入由edit_control进行,需要捕获输入。 2.用户的主机地址和发送地址不一样,需要分别设置并绑定。 设计RC外观 必备组件:主机IP与端口,从机IP与端口,消息框,发送,连接按钮。…...

MyBatis开发

MyBatis开发入门搭建MyBatis框架开发环境在自己建的的项目建立个lib文件然后导入包3.两个jar包部署到项目中和为项目添加测试类库4.配置数据库mybatis-config.xml里面的配置&#xff1a;<?xml version"1.0" encoding"UTF-8"?> <!DOCTYPE config…...

excel 数据查询,几个模式化公式请收好

1、一对多查询 所谓一对多&#xff0c;就是符合某个指定条件的有多个结果&#xff0c;要把这些结果都提取出来。 如下图所示&#xff0c;希望根据F2单元格中指定的部门&#xff0c;提取出左侧列表中“生产部”的所有人员姓名。 Excel 2019及以下版本&#xff1a;在H2单元格输…...

Prometheus MySQL 性能监控

一、 介绍 Prometheus 是一种开源的监控系统和时序数据库&#xff0c;旨在收集和处理大量数据并提供可视化、监控警报等功能。它支持多种语言、多种部署方式&#xff0c;并且非常灵活&#xff0c;而且社区支持非常活跃&#xff0c;为用户提供了很多优秀的解决方案。 MySQL 是一…...

刷题记录:牛客NC24261[USACO 2019 Feb G]Cow Land

传送门:牛客 题目描述 Cow Land 总共有 NNN 个不同的景点&#xff08; 2≤N≤1052 \leq N \leq 10^52≤N≤105 &#xff09;。 一共有 n−1n-1n−1 条道路连接任意两个景点&#xff0c;这意味着任意两个景点间只有一条简单路径。 每个景点 iii 都有一个享受值 eie_iei​ &…...

MYSQL开发误区

一、表、列、索引设计误区 1、现象&#xff1a;在线业务系统出现了三张表以上的关联查询 建议&#xff1a;说明业务逻辑在表设计上的实现不合理&#xff0c;需要进行表结构调整&#xff0c;或进行列的冗余&#xff0c;或进行业务改造。 2、现象&#xff1a;大表拆成多张小表之…...

Linux 文件类型,目录与路径,文件与目录管理

文件类型 后面的字符表示文件类型标志 普通文件&#xff1a;-&#xff08;纯文本文件&#xff0c;二进制文件&#xff0c;数据格式文件&#xff09; 如文本文件、图片、程序文件等。 目录文件&#xff1a;d&#xff08;directory&#xff09; 用来存放其他文件或子目录。 设备…...

【JavaEE】-- HTTP

1. HTTP是什么&#xff1f; HTTP&#xff08;全称为"超文本传输协议"&#xff09;是一种应用非常广泛的应用层协议&#xff0c;HTTP是基于TCP协议的一种应用层协议。 应用层协议&#xff1a;是计算机网络协议栈中最高层的协议&#xff0c;它定义了运行在不同主机上…...

3.3.1_1 检错编码(奇偶校验码)

从这节课开始&#xff0c;我们会探讨数据链路层的差错控制功能&#xff0c;差错控制功能的主要目标是要发现并且解决一个帧内部的位错误&#xff0c;我们需要使用特殊的编码技术去发现帧内部的位错误&#xff0c;当我们发现位错误之后&#xff0c;通常来说有两种解决方案。第一…...

java 实现excel文件转pdf | 无水印 | 无限制

文章目录 目录 文章目录 前言 1.项目远程仓库配置 2.pom文件引入相关依赖 3.代码破解 二、Excel转PDF 1.代码实现 2.Aspose.License.xml 授权文件 总结 前言 java处理excel转pdf一直没找到什么好用的免费jar包工具,自己手写的难度,恐怕高级程序员花费一年的事件,也…...

解锁数据库简洁之道:FastAPI与SQLModel实战指南

在构建现代Web应用程序时&#xff0c;与数据库的交互无疑是核心环节。虽然传统的数据库操作方式&#xff08;如直接编写SQL语句与psycopg2交互&#xff09;赋予了我们精细的控制权&#xff0c;但在面对日益复杂的业务逻辑和快速迭代的需求时&#xff0c;这种方式的开发效率和可…...

三体问题详解

从物理学角度&#xff0c;三体问题之所以不稳定&#xff0c;是因为三个天体在万有引力作用下相互作用&#xff0c;形成一个非线性耦合系统。我们可以从牛顿经典力学出发&#xff0c;列出具体的运动方程&#xff0c;并说明为何这个系统本质上是混沌的&#xff0c;无法得到一般解…...

Axios请求超时重发机制

Axios 超时重新请求实现方案 在 Axios 中实现超时重新请求可以通过以下几种方式&#xff1a; 1. 使用拦截器实现自动重试 import axios from axios;// 创建axios实例 const instance axios.create();// 设置超时时间 instance.defaults.timeout 5000;// 最大重试次数 cons…...

智能仓储的未来:自动化、AI与数据分析如何重塑物流中心

当仓库学会“思考”&#xff0c;物流的终极形态正在诞生 想象这样的场景&#xff1a; 凌晨3点&#xff0c;某物流中心灯火通明却空无一人。AGV机器人集群根据实时订单动态规划路径&#xff1b;AI视觉系统在0.1秒内扫描包裹信息&#xff1b;数字孪生平台正模拟次日峰值流量压力…...

tree 树组件大数据卡顿问题优化

问题背景 项目中有用到树组件用来做文件目录&#xff0c;但是由于这个树组件的节点越来越多&#xff0c;导致页面在滚动这个树组件的时候浏览器就很容易卡死。这种问题基本上都是因为dom节点太多&#xff0c;导致的浏览器卡顿&#xff0c;这里很明显就需要用到虚拟列表的技术&…...

C++.OpenGL (20/64)混合(Blending)

混合(Blending) 透明效果核心原理 #mermaid-svg-SWG0UzVfJms7Sm3e {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-SWG0UzVfJms7Sm3e .error-icon{fill:#552222;}#mermaid-svg-SWG0UzVfJms7Sm3e .error-text{fill…...