MS Excel 根据条件从范围中提取所有行

发布于 2025-01-16 01:09:47 字数 1411 浏览 4 评论 0原文

我正在尝试从满足我的条件的范围中提取所有行，这是我的表

组	名字和名称。姓氏	发票编号	发票日期	不含。增值税		含 TAX	O.B	剩余	付款日期
A	某人 01	10001	2022/01/01	1000	50	1050	已付款	0	2022/01/15
B	某人 02	10002	2022/01/01	1000	50	1050	部分付款	550	2022/01/14
A	某人 01	10003	2022/01/10	1500	75	1575	部分付费	75	2022/01/15
C	有人 03	10004	2022/01/10	1500	75	1575	付费	0	2022/01/15

我在互联网上找到了一种提取 Rows 的方法N°发票基于一个条件名字和名字姓氏 单击 CTRL + SHIFT 后，然后向下交换公式

=INDEX($C$2:$C$04, SMALL(IF($B$10=$B$2:$B$04, ROW($ B$2:$B$04)-ROW($C$2)+1), ROW(1:1)))

B10 是包含我要查找的名称的单元格。

我想再添加一个条件，以便它可以通过某个名字和名字提取出来。姓氏和其他是 不等于 0

我尝试 =INDEX($C$2:$C$04, SMALL(IF(AND($B$10=$B$2:$B$04,0>>$H$2:$N$145), ROW($B$2 :$B$04)-ROW($C$2)+1), ROW(1:1)))

但它只提取索引中的第一行

原文

i'm trying to extract all row from a range that meet my conditions, here is my table

Group	first name & last name	N°invoice	invoice Date	Excl. Tax	VAT	Incl. TAX	O.B	Rest	date of payment
A	Someone 01	10001	2022/01/01	1000	50	1050	paid	0	2022/01/15
B	Someone 02	10002	2022/01/01	1000	50	1050	Partial Paid	550	2022/01/14
A	Someone 01	10003	2022/01/10	1500	75	1575	Partial Paid	75	2022/01/15
C	Someone 03	10004	2022/01/10	1500	75	1575	paid	0	2022/01/15

I find in the internet a way to extract Rows of N°invoice based on one condition first name & last name After clicking CTRL + SHIFT and then swap down the formula

=INDEX($C$2:$C$04, SMALL(IF($B$10=$B$2:$B$04, ROW($B$2:$B$04)-ROW($C$2)+1), ROW(1:1)))

B10 is the cell that contain the name that im looking for.

i want to add one more condition so that it well extract by a certain first name & last name and Rest is
NOT equal to 0

i try
=INDEX($C$2:$C$04, SMALL(IF(AND($B$10=$B$2:$B$04,0<>$H$2:$N$145), ROW($B$2:$B$04)-ROW($C$2)+1), ROW(1:1)))

but it only extract the first row in the index

分享到QQ

分享到微博