根据条件在两个不同的列中列出项目 [英] List items based on criterias in two different columns

问题描述

我有以下Excel电子表格:

I have the following Excel spreadsheet:

         A           B            C        

1     Product      Sales        List      

2    Product A      500       Product A
3    Product B                Product C
4    Product C      400       Product D
5                             Product E  
6    =""                      Product F
7    Product D      600       Product H
8    Product E      550       
9    =""
10   Product F      200
11   Product G      =""
12   Product H      800 

在列A 和列B 中列出了不同的产品及其销售情况.如您所见，可能会在 A列或 B列中同时存在empty cells或带有=""的单元格.

In Column A and Column B different products with their sales are listed. As you can see it can either happen that there are empty cells or cells with ="" in both Column A or Column B.

在 C列中，我现在要实现的是，只有没有的产品在 A列中具有empty cells或带有=""的单元格或 B列包含在列表中.

In Column C I want to achieve now that only the products which do NOT have an empty cells or cells with ="" in Column A or Column B are inlcuded in the list.

我已经可以使用以下公式将其用于 A列:

I could already make it work for Column A with this formula:

={INDEX($A$2:$A$100,SMALL(IF(LEN($A$2:$A$100)=0,"",ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100))+1),ROW(A1)))}

在此公式中我需要更改什么，才能从 C列中的列表中排除在 B列中具有empty cell或单元格=""的产品强>?

What do I have to change in this formula to also exclude the products wich have an empty cell or a cell ="" in Column B from my list in Column C?

推荐答案

不必是数组公式.改为在C语言中使用此公式.

doesn't have to be an array formula. use this formula in C instead.

=IF(AND(A:2<>"",B:2<>""),A:2,"")

然后自动填充公式.然后对C列进行排序以获取所有产品列表.

Then autofill the formula. Then sort column C to get all product list.

或在行框中按列C旋转范围，以在A具有重复产品的情况下获得不同的产品列表.

or pivot the range by column C in row box to get the distinct product list in case A has duplicate products.

这篇关于根据条件在两个不同的列中列出项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！