Pivot Table无法求和求助：已尝试各类在线方案仍未解决

Hey Sandy, sorry to hear you've been hitting a wall with this pivot table problem—your data has company names and dollar amounts packed into the same cells, which is almost certainly why the pivot table won't sum correctly. Let's walk through concrete fixes that should resolve this:

Step 1: Split Your Combined Data into Separate Columns #

First, we need to split each cell into two distinct columns: one for the company name, one for the numeric amount. Since company names have spaces, splitting by the last space is the way to go. Here are two reliable methods:

Method A: Excel Formulas #

• Extract the Amount: In an empty column (say, B1), use this formula to grab the numeric value from the end of cell A1:

  =RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
  

  Drag this formula down to apply it to all rows.
• Extract the Company Name: In another empty column (C1), use this formula to get everything except the final amount:

  =LEFT(A1,LEN(A1)-LEN(B1)-1)
  

  Again, drag down to apply to all rows.

Method B: Power Query (More Reliable for Complex Names) #

This is better if you have long company names with multiple spaces:

1. Select your data range, go to the Data tab > click From Table/Range (make sure your data has headers, or check "My table has headers" if not).
2. In the Power Query editor, select the column with combined data.
3. Go to the Transform tab > click Split Column > By Delimiter.
4. Choose "Space" as the delimiter, then under Advanced options, select Split to the rightmost delimiter > click OK.
5. You'll now have two columns: one with the company name, one with the amount.
6. Select the amount column, go to Transform > Data Type > choose Decimal Number.
7. Click Close & Load to bring the cleaned data back to Excel.

Step 2: Ensure the Amount Column is Numeric #

Even after splitting, the amount column might be formatted as text. Fix this:

• Select the amount column.
• Go to the Home tab > in the Number group, choose Number from the format dropdown.
• If that doesn't work, use the VALUE() function to convert text to numbers: in a new column, enter =VALUE(B1) and drag down, then copy-paste values back to the original amount column.

Step 3: Rebuild Your Pivot Table #

Now that you have clean, separate columns:

1. Select your cleaned data range.
2. Go to the Insert tab > click PivotTable.
3. In the pivot table fields pane:
   • Drag the company name column to the Rows area.
   • Drag the amount column to the Values area.
   • If the value field shows "Count" instead of "Sum", click the dropdown on the value field > Value Field Settings > select Sum > click OK.

Quick Checks to Rule Out Other Issues #

• Make sure there are no hidden spaces in the amount column: use TRIM() on the amount values if needed (=TRIM(B1)).
• Verify all amount cells are numeric: use =ISNUMBER(B1) in a test column—if it returns FALSE, that cell has non-numeric characters you need to remove.

This should get your pivot table summing correctly. Let me know if you hit any snags with these steps!

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