Excel根据不同列进行OR条件过滤

Multiple criteria, multiple columns, any criteria true

Boolean logic:     (Type = "Produce" OR Salesperson = "Buchanan")

1. Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

 

Selecting an example from Help

1. Press CTRL+C.
2. In the worksheet, select cell A1, and press CTRL+V.
3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

---

 

1

2

3

4

5

6

7

8

9

10

A	B	C
Type	Salesperson	Sales
Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

1. To find rows that meet multiple criteria in multiple columns, where any criteria can be true, type the criteria in the different columns and rows of the criteria range. In the example, you would enter:

	A	B	C
1	Type	Salesperson	Sales
2	="=Produce"
3		="=Buchanan"

1. Click a cell in the list range. In the example, you would click any cell in the list range, A6:C10.
2. On the Data tab, in the Sort & Filter group, click Advanced.

 

1. Do one of the following:

• To filter the list range by hiding rows that don't match your criteria, click Filter the list, in-place.
• To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

 Tip   When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

1. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$B$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

1. In the example, the filtered result for the list range would be:

	A	B	C
6	Type	Salesperson	Sales
9	produce	Buchanan	$6,328
10	Produce	Davolio	$6,544