针对货运数据电子表格的三类数据子集实现查找、求和与去重的技术方案问询

Hey there! Let's work through your freight data summarization problem together— I’ve got a few practical solutions tailored to your needs, whether you prefer no-code tools, formulas, or a bit of VBA. All of these will keep your original spreadsheet structure intact and give you that single record per Receiver(CUST) + Month-Year you’re after.

1. Simplified Pivot Table (Fixes the "Cluttered" Issue) #

You said pivot tables felt too messy before, and that’s likely because you were including unnecessary fields. Let’s strip it down to only what you need:

• Click any cell in your data range, go to the Insert tab, and select PivotTable. Choose where you want the table to go (a new sheet or blank space in your current sheet).
• In the PivotTable Fields pane:
  • Drag Month-Year and Receiver(CUST) to the Rows area (put Month-Year first to group receivers under each month).
  • Drag Tons to the Values area— it should default to "Sum of Tons", but if not, right-click the value field > Value Field Settings > select Sum.
• That’s it! You’ll get a clean summary with one record per receiver per month, no extra clutter. If you ever want to see VDH vs VPK breakdowns later, you can add those fields to the Columns area, but this basic setup hits your core requirement.

2. Dynamic Array Formulas (Excel 365/2021+) #

If you have a newer Excel version with dynamic arrays, this method auto-updates when your source data changes:

1. Add headers in a blank section of your sheet (e.g., G1: Month-Year, H1: Receiver(CUST), I1: Total Tons).
2. In cell G2, paste this formula to get unique month-receiver pairs:

   =UNIQUE(CHOOSECOLS(A2:E, 1, 4))
   

   (Adjust the column numbers: 1 is your Month-Year column, 4 is your Receiver column— tweak these to match your sheet.)
3. In cell I2, paste this formula to calculate total tons for each pair:

   =SUMIFS(E:E, A:A, G2#, D:D, H2#)
   

   (E is your Tons column, A is Month-Year, D is Receiver.)
   The formulas will automatically spill down to fill all rows, and update instantly if you add or edit data.

3. VBA Script (Automated Summarization) #

If you’re using an older Excel version or want full automation, this VBA script will generate your summary directly in your sheet:

• Press Alt + F11 to open the VBA Editor. Right-click your workbook in the left pane > Insert > Module.
• Paste this code (adjust column letters to match your sheet):

  Sub SummarizeCustomerTons()
      Dim ws As Worksheet
      Dim lastRow As Long, summaryRow As Long
      Dim custData As Object
      Dim uniqueKey As Variant, monthYr As String, cust As String, tonnage As Double
      
      ' Set the worksheet to your data sheet
      Set ws = ActiveSheet
      ' Use a dictionary to track unique month-receiver pairs
      Set custData = CreateObject("Scripting.Dictionary")
      
      ' Find the last row of your source data
      lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      
      ' Loop through each row of data (skip header row)
      For i = 2 To lastRow
          monthYr = ws.Cells(i, "A").Value
          cust = ws.Cells(i, "D").Value
          tonnage = ws.Cells(i, "E").Value
          
          ' Create a unique key for month + receiver
          uniqueKey = monthYr & "|" & cust
          
          ' Add or update the tonnage in the dictionary
          If custData.Exists(uniqueKey) Then
              custData(uniqueKey) = custData(uniqueKey) + tonnage
          Else
              custData(uniqueKey) = tonnage
          End If
      Next i
      
      ' Set up summary headers (starts at column G)
      summaryRow = 1
      ws.Cells(summaryRow, "G").Value = "Month-Year"
      ws.Cells(summaryRow, "H").Value = "Receiver(CUST)"
      ws.Cells(summaryRow, "I").Value = "Total Tons"
      summaryRow = summaryRow + 1
      
      ' Write the summarized data to the sheet
      For Each uniqueKey In custData.Keys
          Dim keyParts As Variant
          keyParts = Split(uniqueKey, "|")
          ws.Cells(summaryRow, "G").Value = keyParts(0)
          ws.Cells(summaryRow, "H").Value = keyParts(1)
          ws.Cells(summaryRow, "I").Value = custData(uniqueKey)
          summaryRow = summaryRow + 1
      Next uniqueKey
      
      ' Auto-fit columns for readability
      ws.Range("G:I").AutoFit
      MsgBox "Summarization complete!", vbInformation
  End Sub
  

• Go back to Excel, press Alt + F8, select SummarizeCustomerTons, and click Run. Your summary will appear starting at column G.

4. Power Query (Great for Frequent Data Updates) #

If you need to refresh your summary often as data changes, Power Query is a robust tool:

• Select your data range, go to the Data tab, and click From Table/Range (check "My table has headers" if prompted).
• In the Power Query Editor:
  • Go to the Transform tab > Group By.
  • Select Advanced, then add two grouping columns:
    1. Column: Month-Year, Operation: Group By
    2. Column: Receiver(CUST), Operation: Group By
  • Add an aggregation column: New column name Total Tons, Operation: Sum, Column: Tons
  • Click OK, then go to Home > Close & Load To > choose to load the summary to a blank space in your current sheet.
• When your source data updates, right-click the summary table > Refresh to get the latest totals.

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