根据组内匹配条件复制值的Excel数组公式

是一种在Excel中用于根据特定条件从一个数据范围中复制值的函数。它可以帮助用户根据指定的条件在数据表中查找并提取所需的数据。

这个公式的一般形式是： {=INDEX(返回范围,MATCH(条件1&条件2&...&条件n,条件范围1&条件范围2&...&条件范围n,0))}

其中，INDEX函数用于返回指定范围中的值，MATCH函数用于查找满足条件的行或列的位置。

这个公式的应用场景包括但不限于：

数据筛选和提取：根据特定条件从大量数据中筛选出所需的数据。
数据分析和报告：根据不同的条件生成不同的报表和分析结果。
数据更新和同步：根据条件将数据从一个表格复制到另一个表格，实现数据的同步更新。

腾讯云提供了一系列与Excel相关的产品和服务，例如腾讯文档、腾讯会议等，这些产品可以帮助用户进行在线协作和数据处理。您可以通过访问腾讯云官方网站（https://cloud.tencent.com/）了解更多相关信息。

请注意，以上答案仅供参考，具体的解决方案和产品选择应根据实际需求和情况进行评估和决策。

相关·内容

Excel公式练习：根据条件获取唯一的第n个值

引言：本文的练习整理自chandoo.org。多一些练习，想想自己会怎么解决这个问题，看看别人又是怎样解决的，这样能够快速提高Excel公式编写水平。...本次的练习是：编写一个公式，用于显示数据（Data）列中与当前选定查找项目匹配的项目（Item）列的第n个最大的唯一值。示例数据如下图1所示。...单元格D2、E2中的数据可以输入，公式根据其数据返回相应的结果。根据不同的输入数据，公式的结果应该如下图2所示。图2 规则： 1.公式中不能使用整列引用。 2.不能使用中间公式。...4.无论数据放置在工作表中的任何地方，公式都能正常运行。 5.除了规定的名称“i”“d”“n”“l”外，不能有其它硬编码引用。请写下你的公式。解决方案公式1：数组公式。...4：数组公式。

2.2K3 0

精通Excel数组公式023：使用数组公式的条件格式

4.评估为TRUE或FALSE的逻辑公式可以用于创建条件格式。 5.条件格式可以使用非数组公式和数组公式。 6.条件格式是易失性的：经常重新计算，减慢整个工作表的计算时间。...（按Alt，O，D键，或者单击功能区“开始”选项卡“样式”组中的“条件格式——管理规则”）（3）打开“新建格式规则”对话框。...（按Alt+N键，或者单击“新建规则”按钮）（4）从“选择规则类型”列表中选取“使用公式确定要设置格式的单元格”。（5）单击“为符合此公式的值设置格式”框。（6）输入公式。...（10）单击“确定”关闭“条件格式规则管理器”对话框。如下图1所示，高亮显示单元格A11中指定的城市名的最小时间所在的行。注意，混合引用$B3允许每个单元格将指定城市的最小值与列B中时间值比较。...单元格D18中的数组公式计算得到指定的时间并作为条件格式的辅助单元格。 ? 图2 欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

2.8K3 0

Excel公式练习：根据条件获取唯一的第n个值（续）

引言：本文的练习整理自chandoo.org。多一些练习，想想自己会怎么解决这个问题，看看别人又是怎样解决的，这样能够快速提高Excel公式编写水平。...本次的练习是：在《Excel公式练习：根据条件获取唯一的第n个值》中，编写了一个公式用于显示数据（Data）列中与当前选定查找项目匹配的项目（Item）列的第n个最大的唯一值。...然而，如果n是6，而我们只有3个唯一值，那么编写的公式应该返回0。这里，你的任务是修改这些公式，以便在上面所说的情况下，返回最小的非零唯一值。示例数据如下图1所示。...单元格D2、E2中的数据可以输入，公式根据其数据返回相应的结果。根据不同的输入数据，公式的结果应该如下图2所示。图2 规则： 1.公式中不能使用整列引用。 2.不能使用中间公式。...4.无论数据放置在工作表中的任何地方，公式都能正常运行。 5.除了规定的名称“i”“d”“n”“l”外，不能有其它硬编码引用。请写下你的公式。解决方案公式1：数组公式。

1.8K1 0

精通Excel数组公式009：提供多个值的数组公式

这个数组生成5个值，并分别在5个单元格中输入这些值。这类数组公式有下列特点： 1. 不能对数组公式所在的区域进行部分修改。...如果需要编辑数组公式，则可以在该数组公式区域中编辑任一单元格中的公式，然后按Ctrl+Shift+Enter键。 5. 选择数组公式区域的任意单元格，在公式栏中都会看到相同的公式。 6....数组函数（TRANSPOSE函数除外）都可以放置在其他接受一组值的函数（例如COUNT、MIN、MAX等）中，并且该公式不需要按Ctrl+Shift+Enter键。...上例中的数组公式可以归纳为一个求序号的公式构造： ROW(单元格区域)-ROW(单元格区域中的第一个单元格)+1 这个公式构造可以作为更高级的数组公式中的元素。...欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。完美Excel社群2020.9.2动态 #VBA# 增强的CELL函数和INFO函数

5.2K5 0

Excel公式技巧105：带条件的部分匹配计数

图2 解决方案1：使用数组公式： =SUM(N(ISNUMBER(SEARCH(C5,IF('Product Data'!B3:B10000=B5,'ProductData'!...A3:A 注意，这是一个数组公式，因此按下Ctrl+Shift+Enter键才算公式输入完成。...公式中，IF函数先筛选出State名为B5中值的Product Data；接着，SEARCH函数在筛选出的ProductData中查找C5中的值，如果找到则返回一个数字；传递给ISNUMBER函数，得到一组由...TRUE/FALSE值组成的数组；N函数将其转换成1/0组成的数组，其中的1就是满足条件的条目，将它们求和得到满足条件的所有条目数。...解决方案2：使用公式： =COUNTIFS('Product Data'!B2:B9995,B5,'ProductData'!A2:A 很简单的一个公式，更容易理解。

5.4K6 0

Excel公式技巧68：查找并获取所有匹配的值

学习Excel技术，关注微信公众号： excelperfect 在《Excel公式技巧67：按条件将数据分组标识》中，我们根据指定的条件采用数字标识将数据进行了分组。...利用这列分组数据，我们能方便地查找并获取所有匹配的值。如下图1所示的工作表，我们想查找商品名称是“笔记本”且在区域A的所有数据。 ?...图1 我们利用《Excel公式技巧67：按条件将数据分组标识》中的公式技巧，在单元格E3中输入公式： =SUM(E2,AND(B3:B20=H3,C3:C20=I3)) 向下拉至单元格E20，从而构建了一个辅助列...可以看到，工作表中以商品名称是“笔记本”且在区域A的数据行为分界点连续编号。在单元格G3中输入公式： =MAX(E3:E20) 得到共有多少个满足条件的查找值。...公式很简单，其关键在于： MATCH(G6,E3:E 查找到第n个（由列G中的单元格指定）匹配的值所在的位置。而COLUMNS($H6:H6)则返回要获取的值所在列的位置。

10.3K1 0

Excel公式练习59：获取与满足多个查找条件的所有值

导语：本文所讲的案例在第一季公式练习中有相似的例子，这里再巩固一下。只要知道要在公式中使用的函数，没有Excel解决不了的问题！...本次的练习是：如下图1所示，单元格区域A1:E25中存放着数据，列D中是要查找的值需满足的条件，列I和列J中显示查找到的结果，示例中显示的是1月份南区超市销售的蔬菜及其数量。 ?...图1 要求在I2中输入公式，向右向下拖拉以获取全部满足条件的数据。先不看答案，自已动手试一试。...公式在单元格I2中输入数组公式： =IF(COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2),"",INDEX(D:D,SMALL(IF(...公式解析公式中的： COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2) 用来计算符合条件的结果数（本例中为5），并与已放置值的单元格数（已返回的值

2.8K2 0

Excel公式技巧65：获取第n个匹配的值（使用VLOOKUP函数）

学习Excel技术，关注微信公众号： excelperfect 在查找相匹配的值时，如果存在重复值，而我们想要获取指定的匹配值，那该如何实现呢？...图1 我们知道VLOOKUP函数通常会返回找到的第一个匹配值，或者最后一个匹配值，详见《Excel公式技巧62：查找第一个和最后一个匹配的数据》。...然而，我们可以构造一个与商品相关的具有唯一值的辅助列（详见《Excel公式技巧64：为重复值构造包含唯一值的辅助列》），从而可以使用VLOOKUP函数来实现查找匹配值。...首先，添加一个具有唯一值的辅助列，如下图2所示。 ? 图2 在单元格B3中输入公式： =D3 & "-" &COUNTIF( 下拉至单元格B14。...在单元格H6中输入公式： =VLOOKUP(H2 & "-" &G6,B3:E 即可得到指定的匹配值，如下图3所示。 ? 图3 可以修改单元格H2或G6中的数值，从而获取相应匹配的数据。

7.6K1 0

Excel公式技巧66：获取第n个匹配的值（使用INDEX函数）

学习Excel技术，关注微信公众号： excelperfect 在《Excel公式技巧65：获取第n个匹配的值（使用VLOOKUP函数）》中，我们构造了一个没有重复值的辅助列，从而可以使用VLOOKUP...图1 在单元格G6中输入数组公式： =INDEX(D3:D14,SMALL(IF(C3:C14=G2,ROW(C3:C14)-ROW(C3)+1),F6)) 结果如下图2所示。 ?...图2 公式中： C3:C14=G2 将单元格区域C3:C14中的值与单元格G2中的值相比较，得到由布尔值组成的数组： {TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE...;FALSE;FALSE;TRUE;TRUE} 公式中： ROW(C3:C14)-ROW(C3)+1 生成一系列由1至单元格区域的最大个数的连续整数组成的数组： {1;2;3;4;5;6;7;8;9;10...代入INDEX函数中，得到： =INDEX(D3:D14,6) 结果为单元格D8中的值10。如果使用定义的名称，那么公式将更灵活，如下图3所示。 ?

6.3K1 0

Excel公式练习45：从矩阵数组中返回满足条件的所有组合数

本次的练习是：如下图1所示，在一个4行4列的单元格区域A1:D4中，每个单元格内都是一个一位整数，并且目标值单元格（此处为F2）也为整数，要求在单元格G2中编写一个公式返回单元格A1:D4中四个不同值的组合的数量...这四个值的总和等于F2中的值 2. 这四个值中彼此位于不同的行和列 ? 图1 下图2是图1示例中满足条件的6种组合。 ? 图2 先不看答案，自已动手试一试。...不幸的是，在Excel中生成这种排列的数组绝非易事。在Excel中生成大型数组唯一现实的方法是通过使用ROW函数的公式构造。...这样，公式构造中的： MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3) 将转换成的数组是什么呢？实际上，我们在这里所做的就是将一系列以10为底的值转换为以3为底的值。...2.不仅要理解Excel函数原理，而且要打好数学基础，这是灵活应用公式的一切。

3.3K1 0

Excel公式技巧06： COUNTIFS函数如何处理以数组方式提供的条件

这个数组是怎么来的？这里的关键是之前提到的元素“配对”。当两个（或多个）数组具有相同的“向量类型”（即要么都是单列数组，要么都是单行数组）时，Excel将对每个数组中相对应条件进行配对。...并且，第三个数组中的第三个元素“Roleplaying”在第一个数组中并没有相配对的元素。然而，Excel会继续构建适当大小的数组以容纳预期的返回值，即上面看到的2行3列的数组。...它是使用零填充这些新创建的多余空间，然后根据需要对结果数组进行操作。下面，可以给出一个与上面中间结果{0,1,0;1,0,0}的等效表达式，其分解起来如下图4所示。 ?...这三组数据和第一个数组{"Male","Female"}交叉运算的结果如下图5所示。 ?...有许多的Exceller，其工作清楚地表明了他们对标准公式技术的理解：使用FREQUENCY非常容易；编写出长而复杂的数组公式；会记住经过实践检验的成熟的解决方案；善于在各种情况下使用绝大多数Excel

5.3K4 2

精通Excel数组公式005：比较数组运算及使用一个或多个条件的聚合计算

下面是Excel的比较运算符： = 等于不等于 > 大于 >= 大于等于 < 小于 <= 小于等于在诸如基于条件查找最小值或最大值、计算标准偏差等情形时，Excel没有提供相应的内置函数，必须编写数组公式...图1 使用数组公式 Excel中没有一个MINIF函数来根据条件求相应的最小值，可以使用MIN/IF函数组合来实现。...在公式中： A3:A8=D3 将单元格区域A3:A8中的城市名与单元格D3中的城市名相比较，生成数组： {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE} 接着，IF函数根据比较的结果...使用数据库函数在Excel中，有一组基于判断条件执行计算的数据库函数，共12个，也称之为D-函数，例如DMIN、DMAX和DSUM函数。...图7 想要编写一个公式能够直接向下复制，且当源数据更新时结果能自动更新。有了上文的基础后，我们知道可以使用MAX函数配合两个嵌套的IF函数来实现。

8.3K4 0

Excel公式技巧17：使用VLOOKUP函数在多个工作表中查找相匹配的值（2）

我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例，但是将匹配多个条件，并提供两个解决方案：一个是使用辅助列，另一个不使用辅助列。下面是3个示例工作表： ?...这样，获取值的数组公式（单元格C7）如下： =VLOOKUP(A7&”|”&B7,INDIRECT("'"&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets...A1:D10"),4,0) 其中，Sheets是定义的名称：名称：Sheets 引用位置：={"Sheet1","Sheet2","Sheet3"} 这个公式的运行原理与上文相同，可参见《Excel公式技巧...16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）》。...C:C"),$B11)>0,0) 名称：Arry2 引用位置：=ROW(INDIRECT("1:10"))-1 在单元格C11中的数组公式如下： =INDEX(INDIRECT("'"&INDEX(Sheets

13.9K1 0

Excel公式技巧16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）

在某个工作表单元格区域中查找值时，我们通常都会使用VLOOKUP函数。但是，如果在多个工作表中查找值并返回第一个相匹配的值时，可以使用VLOOKUP函数吗？本文将讲解这个技术。...图4：主工作表Master 数组公式如下： =VLOOKUP($A3,INDIRECT("'"&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"...公式中的： COUNTIF(INDIRECT("'"&Sheets&"'!...B:B"}),$A3) INDIRECT函数指令Excel将这个文本字符串数组中的元素转换为单元格引用，然后传递给COUNTIF函数，同时单元格A3中的值作为其条件参数，这样上述公式转换成： {0,1,3...因为我们想得到第一个匹配的结果，所以将该数组传递给MATCH函数： MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!

24.2K2 1

Excel公式技巧14：在主工作表中汇总多个工作表中满足条件的值

我们可能熟悉使用INDEX、SMALL等在给定单列或单行数组的情况下，返回满足一个或多个条件的值的列表。这是一项标准的公式技术。...可以很容易地验证，在该公式中的单个条件可以扩展到多个条件，因此，我们现在有了从一维数组和二维数组中生成单列列表的方法。那么，可以更进一步吗？...： =SUMPRODUCT({3,2,1}) 其中数组的值由3、2、1组成，与工作表Sheet1、Sheet2、Sheet3的列D中包含“Y”的数量一致。...实际上，该技术的核心为：通过生成动态汇总小计数量的数组，该小计数量由来自每个工作表中符合条件（即在列D中的值为“Y”）的行数组成，然后将公式所在单元格相对行数与该数组相比较，以便有效地确定公式所在行中要指定的工作表...k的值，即在工作表Sheet1中匹配第1、第2和第3小的行，在工作表Sheet2中匹配第1和第2小的行，在工作表Sheet3中匹配第1小的行。

9K2 1

遇到复杂业务查询，怎么办？

这个业务问题，使用多维度拆解分析方法，可以把这个业务问题分为两部分：第一部份创造下拉列表，第二部份根据选好的条件查询匹配出相应的费用。...1.制作下拉列表第1步：A列中的机构名称有很多是重复的，把这一列的值复制到表格空白处，然后删除重复值后，发现这一列的机构名称只有A、B、C共3家。...image.png 2.查询费用机构、利率档、期限的下拉列表创造好后，接下来要根据这3个条件进行费用的查询匹配。这时用到Excel里的两个查询函数：index，match。...) 上面公式表示在A2：E17这个区域内，第1行第3列的值是976元。...简单来说数组就是一组结构相同的数据按照一定的规律进行排列，组成数组的数据我们统称为元素。什么情况下会用到数组呢？如果计算结果返回的是一个集合就要用到数组。

1.6K1 0

精通Excel数组公式026：你弄清楚大型数组公式是怎么工作的吗？

2.当公式在单个单元格中时，运行“公式求值”命令（按Alt,M,V键，或者选择功能区“公式”选项卡“公式审核”组中的“公式求值”）。“公式求值”功能对于看到公式计算时Excel所遍历的步骤是非常好的。...image.png 图2 查找列，在该列中匹配条件并提取数据如下图3所示，首先查找一列（“第3天”），然后在该列中匹配条件（Job 4），获取对应的员工名，并垂直显示。...如下图6所示，计算每个系的学生成绩排名。 image.png 图6 计算连续两天运行时间之和的最大值如下图7所示，计算7天内连续两天运行时间之和的最大值。...image.png 图7 根据可变长度的系列折扣计算等效净成本如下图8所示，对于单元格D3来说，公式必须执行计算：0.8*0.95*0.9*0.8，而复制到单元格D4中，则执行计算：0.9*0.9。...image.png 图9 最后的提示数组公式不能够计算整列在Excel中，虽然可以创建非常大的数组公式，但不能创建使用整列的数组，因为重新计算使用整列单元格的数组公式是非常耗时的，Excel不允许创建这类数组

2.3K2 0

Excel公式练习47：根据单元格区域中出现的频率和大小返回唯一值列表

通过在第二个参数指定的值后添加一个空字符串，Excel将空单元格解析为空字符串而不是0，因此公式： COUNTIF(Range1,Range1&"") 解析为： COUNTIF(Range1,{"1",...COUNTIF(Range1,Range1)+1/(Range1*10^6) 将为单元格区域内的每个值生成一个计数数组，这很重要，因为问题的症结在于根据值在该区域内的频率返回值。...使用额外的子句的原因是为我们提供一种方法，使我们可以区分在区域内两个或多个值出现频率相同的情况。更重要的是，此子句的目的是在这种情况下首先返回较小的值。...可以看到，这种情形下使用FREQUENCY函数，从而将数组简化为每个值在该数组中出现次数的数组。公式中之所以在区域后添加0，是为了将空单元格转换为0。...这里，确保我采用了必要的技术来强制INDEX对一组值进行操作（更多信息，请参见《Excel公式技巧03：INDEX函数，给公式提供数组》），因此： INDEX(FREQUENCY(0+(Range1&0

1.7K2 0

精通数组公式17：基于条件提取数据（续）

excelperfect 导语：本文为《精通Excel数组公式16：基于条件提取数据》的后半部分。使用数组公式来提取数据创建数据提取数组公式的技巧是在公式内部创建一个“匹配记录”相对位置的数组。...如下图8所示，可以看到与条件相匹配的记录的相对位置是7和10，它们将作为INDEX的row_num参数的值。 ?...图9：使用数组公式提取满足条件的记录对于Excel2010及以后的版本来说，还可以使用AGGREGATE函数的公式： =IF(ROWS(F12:F12)>7,"",INDEX(A20,AGGREGATE...图10：使用AGGREGATE函数的公式提取满足条件的记录示例：从一个查找值返回多个值在Excel中，诸如VLOOKUP、MATCH、INDEX等标准的查找函数不能够从一个查找值中返回多个值，除非使用数组公式...4.有两种有用的方法来考虑数据提取公式：提取匹配一组条件的记录或数据；从单个查找值返回多个数据值。注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。

3.3K1 0

精通数组公式16：基于条件提取数据

2.当单元格中的条件改变时，公式能够即时更新。 3.使用辅助列的非数组公式解决方案比数组公式计算速度更快。 4.数组公式可能使用许多单元格引用、包含许多计算，因此可能要更长的计算时间。...在Excel中，标准的查找函数例如INDEX、MATCH、VLOOKUP等都非常好，但当存在重复值时就比较困难了。如下图1所示，提取满足3个条件的数据记录，可以看出有2条记录满足条件。...辅助列包含提供顺序号的公式，只要公式找到了满足条件的记录。这些顺序号解决了重复值问题，因为对于每条匹配的记录都有唯一的标识号。辅助列作为查找列，供查找函数查找并提取数据。 2.基于全数据集的数组公式。...这些公式是独立的，不需要额外的列辅助。对于这些公式，必须在公式内为与条件相匹配的记录创建一个相对位置数组。 ? 图1：需要提取两条记录，标准的查找函数对于重复值有些困难。...图7：AND和OR条件，双向查找从日期和商品数列中获取数据未完待续>>> 注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。

4.3K2 0

点击加载更多

扫码

添加站长进交流群

领取专属 10元无门槛券

手把手带您无忧上云