慕课网wordpress/seo报名在线咨询
所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。
下面咱们就说说一对多查询的典型用法,先看数据源:
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呢?
随着科技的不断发展,知识管理已经成为现代企业不可或缺的一个重要组成部分。由于信息化快速发展,企业每天都会产生大量的数据和信息,如何高效地获取、整理和利用这些信息已经成为了企业成功的关键因素之一。为了更好地管理企业知识࿰…...

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里面的配置:<?xml version"1.0" encoding"UTF-8"?> <!DOCTYPE config…...

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

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

刷题记录:牛客NC24261[USACO 2019 Feb G]Cow Land
传送门:牛客 题目描述 Cow Land 总共有 NNN 个不同的景点( 2≤N≤1052 \leq N \leq 10^52≤N≤105 )。 一共有 n−1n-1n−1 条道路连接任意两个景点,这意味着任意两个景点间只有一条简单路径。 每个景点 iii 都有一个享受值 eie_iei &…...

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

k8s学习之路 | k8s 工作负载 DaemonSet
文章目录1. DaemonSet 基础1.1 什么是 DS1.2 DS 的典型用法1.3 如何编写 DS 资源1.4 DS 示例文件1.5 DS Pod 是如何被调度的1.6 更新 DS1.7 DS 替代方案1.8 DS 工作负载字段描述2. DaemonSet 的使用2.1 每个节点运行一个2.2 DS 更新策略2.3 滚动更新2.4 OnDelete 更新2.6 更新回…...

Javaweb MVC模式和三层架构
MVC 模式和三层架构是一些理论的知识,将来我们使用了它们进行代码开发会让我们代码维护性和扩展性更好。 7.1 MVC模式 MVC 是一种分层开发的模式,其中: M:Model,业务模型,处理业务 V:View&am…...

综合考虑,在客户端程序中嵌入网页程序,首选CefSharp。
综合考虑,在客户端程序中嵌入网页程序,首选CefSharp。 CefSharp 是一种将全功能符合标准的 Web 浏览器嵌入 C# 或 VB.NET 应用程序的简单方法。 https://www.jianshu.com/p/3f50cc747606 WinForm嵌入Web网页的解决方案 Microsoft Edge WebView2诞生较晚…...

【Java基础 下】 030 -- 网络编程
目录 一、什么是网络编程 1、常见的软件架构(CS & BS) ①、BS架构的优缺点 ②、CS架构的优缺点 2、小结 二、网络编程三要素 1、IP ①、IPv4 ②、IPv6 ③、小结 ④、IPv4的一些细节 ⑤、InetAddress的使用 2、端口号 3、协议 ①、TCP & UDP 三、…...

2021牛客OI赛前集训营-提高组(第三场) T3打拳
2021牛客OI赛前集训营-提高组(第三场) 题目大意 有2n2^n2n个选手参加拳击比赛,每个人都有一个实力,所有选手的实力用一个111到2n2^n2n的排列表示。 淘汰赛的规则是:每次相邻的两个选手进行比赛,实力值大…...

C++面向对象编程之四:成员变量和成员函数分开存储、this指针、const修饰成员和对象
在C中,成员变量和成员函数是分开存储的,只有非静态成员变量才存储在类中或类的对象上。通过该类创建的所有对象都共享同一个函数#include <iostream> using namespace std;class Monster {public://成员函数不占对象空间,所有对象共享同…...

卷积神经网络(CNN)基础知识
文章目录CNN的组成层卷积层卷积运算卷积的变种分组卷积转置卷积空洞卷积可变形卷积卷积层的输出尺寸和参数量CNN的组成层 在卷积神经⽹络中,⼀般包含5种类型的⽹络层次结构:输入层、卷积层、激活层、池化层和输出层。 输入层(input layer&a…...

opencv+python 常见图像预处理
import os import cv2 import numpy as np import pandas as pd from PIL import Image import matplotlib.pylab as plt """图像预处理"""#缩放 #灰度化 #二值化-otsu,自定义,自适应 #均值滤波 #中值滤波 #自定义滤波 #高斯/双倍滤波…...

如何实现一个单例模式
目录 前言 1.饿汉式 2.懒汉式 3.双重检测 4.静态内部类 5.枚举 总结: 前言 单例模式是我们日常开发过程中,遇到的最多的一种设计模式。通过这篇文章主要分享是实现单例的几种实现方式。 1.饿汉式 饿汉式的实现方式比较简单。在类加载的时候&#…...

传输线的物理基础(四):传输线的驱动和返回路径
驱动一条传输线对于将信号发射到传输线的高速驱动器,传输线在传输时间内的输入阻抗将表现得像一个电阻,相当于线路的特性阻抗。鉴于此等效电路模型,我们可以构建驱动器和传输线的电路,并计算发射到传输线中的电压。等效电路如下图…...