Excel单个单元格含多值时，能否用数据透视表按州筛选预算？

Hey there! Let's break down your questions about handling multi-value cells in pivot tables—totally doable, just need a small workaround since pivot tables treat each cell's content as a single item by default.

Question 1: Can you filter pivot tables by multiple values in a single cell? #

Short answer: Yes, but not directly with native pivot table filters. The pivot table will see a cell like "Virginia, Maryland" as one unique value, not two separate states. You'll need to either split the multi-value cells into separate rows/entries first, or use a helper column to flag which rows match your target value.

Question 2: How to calculate total budget for projects tied to a specific state (e.g., Maryland) in your table? #

Using your sample data, here are two practical methods that work perfectly:

Method 1: Split Multi-Value Cells into Separate Rows (Most Structured Approach) #

This converts your data into a "one state per row" format, which plays nicely with pivot tables:

• Select the entire "所属州" (State) column (including the header).
• Go to the Data tab → click Text to Columns. Choose "Delimited" → Next, check "Comma" as the delimiter → Finish. Now your multi-state cells are split into separate columns.
• To turn these columns into rows (so each state gets its own project entry):
  1. Select all your data (Name, Budget, and the new split state columns).
  2. Press Alt+D+P to open the PivotTable and PivotChart Wizard. Select "Multiple consolidation ranges" → Next, choose "I will create the page fields" → Next.
  3. Click Add, select your data range, then click Finish to generate a new pivot table.
  4. In the pivot table field list:
     • Drag the "Value" field from the Rows area to Row Labels
     • Drag the "Value" field from the Columns area to Column Labels
     • Set the Values field to "Sum of 预算" (Budget)
• Now you'll see a clean breakdown of total budget per state—cross-state projects will be counted in each relevant state's total.

Method 2: Use a Helper Column (No Data Structure Changes) #

If you want to keep your original data intact, add a helper column to flag rows that include your target state:

• Add a new column to the right of your table, name it something like "Includes Maryland".
• In the first row of this helper column, enter the formula:

  =IF(ISNUMBER(SEARCH("Maryland", [@所属州])), 1, 0)
  

  (Adjust [@所属州] to match your actual column name if needed.) This formula checks if the cell contains "Maryland" and returns 1 if yes, 0 if no.
• Drag the formula down to apply it to all rows.
• Create your pivot table:
  1. Drag "预算" (Budget) to the Values area, set it to Sum.
  2. Drag the helper column to the Filters area, then filter for "1".
• This will show you the total budget for all projects that have at least partial operations in Maryland. To check other states, just update the state name in the helper column formula!

Both methods work great: splitting rows is ideal if you need to analyze multiple states regularly, while the helper column is perfect for quick, one-off stats without modifying your original data.

内容的提问来源于stack exchange，提问作者Orion