☰

尝试让 Excel 创建具有多个条件的新列表

tag-icon • tag-icon microsoft-excel
尝试让 Excel 创建具有多个条件的新列表

我可能对 Excel 要求太高了，或者我可能就是搞不懂。我想根据两个唯一的条件创建一个列表，它是另一个列表的子集。

输入列表位于 Sheet1!B9:B20 范围内的一张表中。列表元素是文本，格式为：NNNN - 文本描述（例如 1320 - 所有者费用）。前四个字符很重要。这是一个 GL 代码。

输出列表是输入列表的子集。我需要排除以下元素：a) GL 代码以“4”开头，b) GL 代码位于排除的 4 位代码列表中（例如 1320），在 Sheet3!A2:A20 中列为文本。例如：

输入列表：输出列表
1000 - 描述 1 ---> 1000 - 描述 1  
1320 - 说明 2 5110 - 说明 4  
4000 - 描述 3  
5110 - 描述 4

...其中 4000 以“4”开头且 1320 在排除列表中。

以下是成功排除以“4”开头的项目的代码：


Note the {} brackets; it's an array formula.

{=IFERROR(
INDEX(Sheet1!B$9:B$20,
SMALL(
IF(
LEFT(Sheet1!$B$9:$B$20,1)<>"4",    // I can enter only a single condition here
ROW(Sheet1!B$9:B$20)-ROW(Sheet1!B$9)+1
),
ROWS(Sheet1!B$9:'SS Version'!B9)
)
),"")}
我尝试了以下条件，但没有成功： 

AND(LEFT(Sheet1!$B$9:$B$20,1)<>"4", LEFT(Sheet1!$B$9:$B$20,4)<>Sheet3!$A$2:$A$20),

AND(LEFT(Sheet1!$B$9:$B$20,1)<>"4", LEFT(Sheet1!$B$9:$B$20,4)<>{"1320","1330", excluded code list},

我并不反对将所有 4XXX 代码添加到我的排除列表中并仅使用该列表，但当我尝试时这也不起作用。我尝试的条件是： 


LEFT(Sheet1!$B$9:$B$20,4)<>Sheet3!$A$2:$A$20),

有建议吗？

答案1

您可以使用高级筛选在另一个位置创建新列表。

请参阅设置的屏幕截图。 是包含排除列表Excluded的单元格范围。Sheet 2

公式

A2:   =LEFT(A7)<>"4"
B2:   =ISERROR(LOOKUP(2,1/ISNUMBER(FIND(Excluded,A7))))

B2 中的公式有点复杂，因为数组公式不允许作为高级筛选中的条件。

在此处输入图片描述

答案2

我还没有找到完整的解决方案，但这非常接近我认为您正在寻找的解决方案：

=IF(ISNA(MATCH(NUMBERVALUE(LEFT(INDEX(Sheet1!$B$9:$B$20,SMALL(
IF(
LEFT(Sheet1!$B$9:$B$20,1)<>"4",
ROW(Sheet1!B$9:B$20)-ROW(Sheet1!B$9)+1
),
ROW()-8
)),4)),Sheet3!A:A,0)),INDEX(Sheet1!$B$9:$B$20,SMALL(
IF(
LEFT(Sheet1!$B$9:$B$20,1)<>"4",
ROW(Sheet1!B$9:B$20)-ROW(Sheet1!B$9)+1
),
ROW()-8
)))

这是返回结果，但是中间有 FALSE 行，我无法将其删除。

我在 Sheet1 中的输入列表在 A9:B20 中如下：

1   1000 - Description 1    
2   1320 - Description 2 on exlusion list   
3   4000 - Description 3    
4   5110 - Description 4    
5   4100 - Description 5    
6   2230 - Description 6 on exclusion list  
7   3330 - Description 7 on exclusion list  
8   6000 - Description 8    
9   7000 - Description 9    
10  5330 - Description 10 on exclusion list 
11  blank
12  blank

Sheet3 中的排除项如下（A 列）：

1320
2230
3330
5330

输出如下所示：

1000 - Description 1
FALSE
5110 - Description 4
FALSE
FALSE
6000 - Description 8
7000 - Description 9
FALSE
0
0
FALSE
FALSE

我知道这不是一个完整的答案，但把它放在这里，以便有人可以在我仍在尝试破解它时在我的工作基础上继续努力。我没有足够的凭证来添加评论。

相关内容