仅用Microsoft Office且权限受限，如何编程处理关系型数据集？

Great question! Since you're stuck with only Microsoft Office but have programming experience, you've got several powerful, built-in tools to handle large relational datasets—no Python or Bash needed. Let's break down the best options, with practical examples you can start using right away.

VBA is Office's native programming language, and it works seamlessly with Excel, Access, and other Office apps. It's perfect for custom scripts to automate repetitive tasks, join relational datasets, clean large datasets, and more.

Excel VBA for Relational Data #

• Open the VBA editor with Alt + F11 in Excel.
• You can write macros to connect multiple worksheets (your "tables"), perform joins, filter records, and export results. For example, here's a snippet to match customer data from one sheet to order data in another:

  Sub MatchCustomerOrders()
      Dim customerWS As Worksheet, orderWS As Worksheet, resultWS As Worksheet
      Dim lastCustRow As Long, lastOrderRow As Long, i As Long, j As Long
      
      Set customerWS = ThisWorkbook.Sheets("Customers")
      Set orderWS = ThisWorkbook.Sheets("Orders")
      Set resultWS = ThisWorkbook.Sheets("CustomerOrderSummary")
      
      lastCustRow = customerWS.Cells(customerWS.Rows.Count, "A").End(xlUp).Row
      lastOrderRow = orderWS.Cells(orderWS.Rows.Count, "A").End(xlUp).Row
      
      ' Clear existing results
      resultWS.Range("A2:Z" & resultWS.Rows.Count).Clear
      
      ' Loop through customers and match orders
      For i = 2 To lastCustRow
          For j = 2 To lastOrderRow
              If customerWS.Cells(i, "A").Value = orderWS.Cells(j, "B").Value Then
                  ' Copy matching data to result sheet
                  resultWS.Cells(resultWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Value = customerWS.Cells(i, "A").Value
                  resultWS.Cells(resultWS.Rows.Count, "B").End(xlUp).Value = customerWS.Cells(i, "B").Value
                  resultWS.Cells(resultWS.Rows.Count, "C").End(xlUp).Value = orderWS.Cells(j, "C").Value
                  resultWS.Cells(resultWS.Rows.Count, "D").End(xlUp).Value = orderWS.Cells(j, "D").Value
              End If
          Next j
      Next i
      
      MsgBox "Customer-order matching complete!"
  End Sub
  

Access VBA (Even Better for Relational Data) #

Since Access is a dedicated relational database, VBA here shines for working with multiple linked tables. You can write modules to run SQL queries, update records in bulk, and automate database maintenance. For example, a script to run a join query and export results to Excel:

Sub ExportCustomerSales()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim excelApp As Object
    Dim ws As Object
    
    Set db = CurrentDb()
    ' Create a temporary query to join Customers and Sales tables
    Set qdf = db.CreateQueryDef("", "SELECT Customers.Name, SUM(Sales.Amount) AS TotalSales " & _
                                "FROM Customers INNER JOIN Sales ON Customers.ID = Sales.CustomerID " & _
                                "GROUP BY Customers.Name")
    
    Set rs = qdf.OpenRecordset()
    
    ' Export to Excel
    Set excelApp = CreateObject("Excel.Application")
    excelApp.Visible = True
    Set ws = excelApp.Workbooks.Add.Sheets(1)
    
    ' Copy headers
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' Copy data
    ws.Range("A2").CopyFromRecordset rs
    
    rs.Close
    qdf.Close
    db.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Sub

Power Query is built into Excel (and available in Access) for ETL (Extract, Transform, Load) tasks. While it has a visual interface, you can use its M language—a functional programming language—to write custom logic for handling relational datasets. This is great for large datasets because Power Query is optimized to process data efficiently without loading everything into Excel's grid.

How to Use M Language #

1. Go to the Data tab in Excel, click Get Data to connect to your tables (Excel sheets, Access databases, etc.).
2. After doing some basic transformations, click Advanced Editor to view and edit the M code.
3. For example, here's M code to join two tables (Customers and Orders) and filter for 2023 orders:

let
    Source = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    OrdersSource = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"ID"}, OrdersSource, {"CustomerID"}, "Orders", JoinKind.Inner),
    #"Expanded Orders" = Table.ExpandTableColumn(#"Merged Queries", "Orders", {"OrderDate", "Amount"}, {"OrderDate", "Amount"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Orders", each Date.Year([OrderDate]) = 2023)
in
    #"Filtered Rows"

You can write custom functions in M to reuse logic across datasets, like a function to clean phone numbers or normalize text fields.

Power Pivot is Excel's in-memory data modeling tool, designed for large relational datasets. It lets you create relationships between tables, build data models, and use DAX (Data Analysis Expressions)—a formula language similar to Excel formulas but far more powerful—to calculate aggregations, metrics, and custom values.

Key Uses for Relational Data #

• Create Relationships: Link tables using common keys (like CustomerID) just like a relational database.
• Write DAX Measures: For example, calculate total sales per customer with:

  Total Customer Sales = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Customers, Customers[ID]))
  

• Build PivotTables/PivotCharts: Use your data model to quickly analyze large datasets without slowing down Excel. Power Pivot can handle millions of rows efficiently because it stores data in a compressed in-memory format.

If you're comfortable with SQL, Access is a perfect fit. You can write standard SQL queries to join tables, filter data, aggregate results, and create views—all without leaving Office.

For example, a SQL query to get top 10 customers by total sales:

SELECT TOP 10 Customers.Name, SUM(Sales.Amount) AS TotalSales
FROM Customers
INNER JOIN Sales ON Customers.ID = Sales.CustomerID
GROUP BY Customers.Name
ORDER BY TotalSales DESC;

You can save these queries, run them on demand, or even automate them with VBA (as shown earlier).

For the best results, combine these tools:

• Use Power Query to extract and clean raw data from multiple sources.
• Load the cleaned data into Power Pivot to build a relational data model.
• Use DAX to create custom metrics for analysis.
• Automate the entire workflow with VBA so you can rerun it with a single click.

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