Excel跨工作表IF语句实现库存分类及分表需求咨询

Hey there! Let's walk through exactly how to get your inventory tables sorted out—since you already have your stocks, fruits, and vegetables base tables set up, we just need to tie them together and split the data properly.

Step 1: Add Category Labels to the stocks Table (Column C) #

First, we'll use an IF function paired with COUNTIF to flag each item as "fruit" or "vegetable" based on whether its code exists in the fruits table.

1. Go to cell C2 in your stocks table (the first row of data under the header).
2. Paste this formula:

   =IF(COUNTIF(fruits!A:A, A2) > 0, "fruit", "vegetable")
   

3. Drag the fill handle (the small square at the bottom-right of C2) down to apply this formula to all rows in column C.

How this works: #

• COUNTIF(fruits!A:A, A2) counts how many times the code in cell A2 appears in column A of the fruits table.
• If the count is greater than 0 (meaning the code exists in fruits), the formula returns "fruit"—otherwise, it defaults to "vegetable" (since you've already split out the two categories).

Pro tip: If your fruits table has a fixed range (e.g., A2:A500 instead of the entire column), use that specific range (like fruits!A2:A500) instead of fruits!A:A—it'll make the formula run faster, especially with large datasets.

Step 2: Split into Fruit & Vegetable Exclusive Inventory Tables #

Once you have the category labels, you can split the data into dedicated tables using one of these methods:

Method 1: Manual Filter & Copy (Quick for one-time use) #

1. Select the header row of your stocks table (cells A1:C1).
2. Go to the Data tab and click Filter—dropdown arrows will appear on each header.
3. Click the dropdown arrow in column C (your category column), uncheck "Select All", then check only "fruit" and click OK.
4. Select all the filtered rows (including headers), copy them, and paste into a new worksheet (name it something like Fruit_Inventory).
5. Repeat the process for "vegetable" to create your Vegetable_Inventory table.

Method 2: Advanced Filter (For easy updates later) #

If you need to refresh the split tables regularly as inventory changes, Advanced Filter is better—it lets you set up reusable rules:

1. In a blank area of your workbook (e.g., cell E1), type "Category" (match the header in column C of stocks). In cell E2, type "fruit". This is your criteria range.
2. Go to the Data tab, click Advanced.
3. In the popup:
   • Select Copy to another location.
   • For List range, select the entire stocks table data (e.g., stocks!A1:C100).
   • For Criteria range, select your criteria (E1:E2).
   • For Copy to, select the starting cell of your new Fruit_Inventory worksheet (e.g., Fruit_Inventory!A1).
   • Click OK.
4. Repeat the process with "vegetable" in the criteria range to create the vegetable table. Next time your stocks data updates, just re-run the Advanced Filter to sync the split tables.

Method 3: Power Query (Fully Automated, Best for Frequent Updates) #

If you're using Excel 2016 or later, Power Query lets you set up a dynamic workflow that refreshes with one click:

1. Select your stocks table data, go to the Data tab, and click From Table/Range (make sure your table has headers).
2. In the Power Query Editor, go to Add Column > Custom Column.
3. Paste this formula into the custom column editor (replace code with your actual header name if different):

   = if List.Contains(Excel.CurrentWorkbook(){[Name="fruits"]}[Content][code], [code]) then "fruit" else "vegetable"
   

4. Click OK to add the category column.
5. To split into fruit inventory:
   • Click the dropdown arrow on your new category column, filter for "fruit".
   • Go to Home > Close & Load To and choose Only Create Connection, then check "Enable load" and select a new worksheet to load the data.
6. Repeat step 5 for "vegetable" to create the vegetable table.
7. Whenever your stocks or fruits data changes, just right-click the split tables and select Refresh to update everything automatically.

Quick Notes to Avoid Issues #

• Make sure the code values in stocks and fruits are formatted the same way (e.g., all text, no leading/trailing spaces)—mismatched formats will break the matching.
• If you're using Google Sheets instead of Excel, the COUNTIF formula works the same way, and you can use the Filter tool or QUERY function for splitting data.

内容的提问来源于stack exchange，提问作者fetus-necrophagy