按站点统计超容设备数量及数据透视表过滤问题求助

Hey there! Let's solve this problem—since your "是否超载" column uses text values ("Yes"/"No"), the pivot table's numeric filters don't play nice with it. But we've got several straightforward workarounds to get you the count of overloaded devices per site:

1. Preprocess Your Data: Convert Text to Numeric Values #

This is the simplest fix, as it lets the pivot table treat your overload status as a numeric metric right away:

• Add a new column next to your "是否超载" column (name it something like "超载标记").
• Use this formula in the first cell of the new column, then drag to fill down:
  =IF(B2="Yes",1,0) (replace B2 with your actual "是否超载" cell reference)
• Now create your pivot table with:
  • Rows: Site names
  • Values: Sum of "超载标记" (this will give you the total number of overloaded devices per site)
• You can now use numeric filters (e.g., "Greater than 0") to target sites with overloaded devices.

2. Add a Calculated Field Directly in the Pivot Table #

No need to modify your original data—create a calculated field inside the pivot table to count "Yes" values:

• Click any cell in your existing pivot table.
• Go to the Analyze tab (Excel) or Options tab (older versions) → select Fields, Items & Sets → Calculated Field.
• Name the field "超载数量", then enter this formula:
  =IF('是否超载'="Yes",1,0)
• Click Add, then OK.
• Drag the new "超载数量" field into the Values area, set the summary type to Sum. You’ll now have a numeric count of overloaded devices per site, which works with numeric filters.

3. Use Power Query for Bulk Data Cleaning (Great for Large Datasets) #

If you’re working with a big dataset, Power Query lets you transform the data efficiently without manual formulas:

• Select your entire data range → go to the Data tab → click From Table/Range to open the Power Query Editor.
• Go to Add Column → Custom Column.
• Name the column "超载计数", then enter this formula:
  =if [是否超载] = "Yes" then 1 else 0
• Click OK, then go to Home → Close & Load to bring the cleaned data back to Excel.
• Create your pivot table from this new cleaned table—numeric filters will work perfectly here.

4. Alternative: Use COUNTIFS Instead of a Pivot Table #

If you don’t need the flexibility of a pivot table, a simple formula can give you the counts directly:

• In a blank cell (next to a list of unique site names), use:
  =COUNTIFS($A$2:$A$100, C2, $B$2:$B$100,"Yes")
  • Replace $A$2:$A$100 with your site name range, C2 with the cell containing the target site, and $B$2:$B$100 with your "是否超载" range.
• Drag the formula down to get counts for all sites.

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