根据条件在两个不同的列中列出项目 [英] 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屋!