excel 查找满足条件的数据并引用

Question

请教在EXCEL中如何实现下面操作：
1、表一
A B C
日期 内部号 金额
2010-01-01 0101 25
2010-01-02 0102 30
2010-01-02 0101 45
2010-01-03 0101 98
2、表二，自动引用表一中内部号为0101的数据
内部号 日期 金额
3、表三，自动引用表一中内部号为0102的数据
4、表一数据更新时，表二和表三的数据自动更新
各位的公式我都按以下方法试了，但总显示公式中包含错误，弄不明白。
我首先建立了工作表，每个是都按日期、内部号和金额建的，全部是空表；然后就复制输入了你的公式，按CTRL+SHIFT+ENTER确定，显示结果是公式中包含错误，确认后，显示错误位置总是表一!$A$2:$A$31000="0101"
另外，各位高手能不能解释一下公式呢？谢谢！

Answer 1

　　筛选满足条件的多个记录，可以使用数组公式解法。如图为Sheet1原始记录表：

　　第1步    在Sheet2表A2单元格输入以下公式并按下CTRL+SHIFT+ENTER结束：

　　=IF(ROW(1:1)>COUNTIF(Sheet1!$B:$B,"0101"),"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B$2:$B$5="0101",ROW($2:$5)),ROW(1:1))))

　　第2步    将A2单元格公式向右向下复制多行多列

　　第3步    选Sheet1的A:C列，单击格式刷，再刷到Sheet2的A:C列，将两表单元格格式设置一致。

 

　　【公式含义】IF函数第1参数判断ROW行号是否超过Sheet1的B列中"0101"的个数，就返回空文本""，否则：用IF函数判断Sheet1!B2:B5是否等于"0101"如果是，就返回他们的行号ROW($2：$5)即数组{2;3;4;5}，不是则返回FASLE，得到数组{2;FALSE;4;5}。然后SMALL函数+row函数依次将其中的2、4、5取出，并与INDEX函数结合取出对应Sheet的A2、A4、A5等单元格。

　　

Answer 2

假设表二，表三第一行是标题 日期 内部号 金额

在表二的A2单元格输入公式

=INDEX(表一!A:A,SMALL(IF(表一!$B$2:$B$2000="0101",ROW(Sheet2!$A$2:$A$2000),4^8),ROW(A1)))

数组公式，按CTRL+SHIFT+回车三键确定,鼠标移到单元格右下角,当出现一个黑色小十字时，同时按住鼠标左键往下拖动

在表三的A2单元格输入公式

=INDEX(表一!A:A,SMALL(IF(表一!$B$2:$B$2000="0102",ROW(Sheet2!$A$2:$A$2000),4^8),ROW(A1)))

数组公式，按CTRL+SHIFT+回车三键确定,鼠标移到单元格右下角,当出现一个黑色小十字时，同时按住鼠标左键往下拖动

注意公式中的表格名字要是你实际的表格名字，我现在假设你是用的表格名字是表一，表二，表三
就会得到你要的效果，且表一数据更新时，表二和表三的数据自动更新

Answer 3

数组公式如下，CTRL+SHIFT+ENTER。公式下拉、右拉
***有容错；自动调整内部号和日期列号***

sheet2的A2
=IF(ROW(A1)>COUNTIF(Sheet1!$B:$B,"0101"),"",INDEX(Sheet1!$A:$C,SMALL(IF(Sheet1!$B$1:$B$1000="0101",ROW($A$1:$A$1000),65536),ROW(A1)),3-MOD(COLUMN(A1),3)))

SHEET3的A2
=IF(ROW(A1)>COUNTIF(Sheet1!$B:$B,"0102"),"",INDEX(Sheet1!$A:$C,SMALL(IF(Sheet1!$B$1:$B$1000="0102",ROW($A$1:$A$1000),65536),ROW(A1)),3-MOD(COLUMN(A1),3)))

将日期列格式设置为日期格式（设置一次即可）
------------------------
有可能是内部号格式原因：
sheet2的A2
=IF(ROW(A1)>COUNTIF(Sheet1!$B:$B,101),"",INDEX(Sheet1!$A:$C,SMALL(IF(Sheet1!$B$1:$B$1000=101,ROW($A$1:$A$1000),65536),ROW(A1)),3-MOD(COLUMN(A1),3)))

SHEET3的A2
=IF(ROW(A1)>COUNTIF(Sheet1!$B:$B,102),"",INDEX(Sheet1!$A:$C,SMALL(IF(Sheet1!$B$1:$B$1000=102,ROW($A$1:$A$1000),65536),ROW(A1)),3-MOD(COLUMN(A1),3)))本回答被提问者采纳

Answer 4

为了引用方便，首先将表一的"内部号"列剪切插入到日期前面。
在表二的a2输入：
=INDEX(表一!A:A,SMALL(IF(表一!$A$2:$A$31000="0101",ROW(表一!$A$2:$A$31000),65536),ROW(A1)))&""
在表三的a2输入：
=INDEX(表一!A:A,SMALL(IF(表一!$A$2:$A$31000="0102",ROW(表一!$A$2:$A$31000),65536),ROW(A1)))&""
数组公式，同时按下ctrl shift enter 三键，公式下拉右拉

Answer 5

可以用高级筛选功能。
先在E1、E2分别输入“内部号”、“0101”，点选菜单“数据”->“筛选”->“高级筛选”，选择“将筛选结果复制到指定位置”，“列表区域”选择A、B、C列，条件区域选择E1：E2，“复制到”选择表二A1。同样,先在F1、F2分别输入“内部号”、“0102”，点选菜单“数据”->“筛选”->“高级筛选”，选择“将筛选结果复制到指定位置”，“列表区域”选择A、B、C列，条件区域选择F1：F2，“复制到”选择表三A1即可。