使用Excel公式过滤列表 [英] Filter list using Excel formula

问题描述

我在Excel工作表的A列中有一家公司列表，在B列中我具有该公司的特征.在另一个Excel工作表中，我想获取第一张工作表中的公司列表，并按其中一个特征过滤(可能会发生变化，因此我需要一个Excel公式来执行此操作).我知道如果我使用以下公式:=IF(Sheet1!A1="Criteria",Sheet1!A1;"")，然后向下复制该公式，它可以工作，但是我会在两者之间得到空白单元格.有什么想法吗?

I have a list of companies in column A of an Excel sheet and in column B I have a characteristic of the company. In another Excel sheet I want to get the list of companies in the first sheet, filtered by one of the characteristics (that could change, therefore I need an Excel formula to do this). I know that if I use the following formula: =IF(Sheet1!A1="Criteria",Sheet1!A1;"") and then copy the formula downwards, it works, but I would get blank cells in between. Any ideas?

谢谢.

推荐答案

假设您的公司列表为Sheet1！A2:A6，特征列表为Sheet1！B2:B6.您的条件可以输入到Sheet2！A1单元格中，您可以在Sheet2！A2中键入以下公式

Assuming your company list is Sheet1!A2:A6 and characteristics list is Sheet1!B2:B6. Your criteria can be input in cell Sheet2!A1 and you can type the below formula in Sheet2!A2

{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=Sheet2!$A$1,ROW($A$2:$A$6)-ROW($A$1),""),ROW(A2)-1)),"")}

按CTRL + SHIFT + ENTER，然后将其复制到输出范围Sheet2！A2:A6.

press CTRL+SHIFT+ENTER, then copy it through the output range Sheet2!A2:A6.

这篇关于使用Excel公式过滤列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！