如何在Excel中生成4列不同行数数据的所有组合

Got it, let's figure out how to generate every possible combination of your four columns—even with different row counts. This is called a Cartesian product, and it's exactly what you need to get that 13,440-row table. Below are step-by-step solutions using tools you’re likely working with:

Excel Solution (Using Power Query) #

Excel's Power Query makes this straightforward without messy formulas:

1. Organize each column into its own named table (e.g., Table_Segment for Column A, Table_BAA for Column B, Table_Terminal for Column C, Table_Hour for Column D).
2. Go to the Data tab, click Get Data > From Other Sources > Blank Query to open the Power Query Editor.
3. Load your first table (e.g., Table_Segment) into the editor.
4. Click Home > Merge Queries > Merge Queries as New.
5. In the merge dialog:
   • Select your second table (Table_BAA) as the right table.
   • For join kind, choose Cross join (this is the key for generating all combinations).
   • Click OK.
6. Expand the merged Table_BAA column to show the BAA Sector values.
7. Repeat steps 4-6 to merge the resulting table with Table_Terminal, then with Table_Hour.
8. Once all merges are done, click Close & Load to export the full combination table to Excel.

Python Solution (Using Pandas) #

If you're comfortable with Python, pandas has a simple cross merge option to build the Cartesian product:

import pandas as pd

# Replace these with your actual data (or load from CSV/Excel files)
segment_df = pd.DataFrame({"Segment": [f"Seg_{i}" for i in range(1, 11)]})
baa_df = pd.DataFrame({"BAA Sector": [f"Sector_{i}" for i in range(1, 15)]})
terminal_df = pd.DataFrame({"Terminal": [f"Term_{i}" for i in range(1, 5)]})
hour_df = pd.DataFrame({"Hour": list(range(0, 24))})

# Build the full combination set step-by-step
full_combo = pd.merge(segment_df, baa_df, how="cross")
full_combo = pd.merge(full_combo, terminal_df, how="cross")
full_combo = pd.merge(full_combo, hour_df, how="cross")

# Verify the row count (should return 13440)
print(f"Total rows generated: {len(full_combo)}")

# Save to Excel if needed
full_combo.to_excel("full_combinations.xlsx", index=False)

SQL Solution #

If your data is stored in a database, use CROSS JOIN to generate all possible combinations in one query:

SELECT
    seg.Segment,
    baa.`BAA Sector`,
    term.Terminal,
    hr.Hour
FROM segment seg
CROSS JOIN baa_sector baa
CROSS JOIN terminal term
CROSS JOIN hour hr;

Running this query will return exactly 10144*24 = 13,440 rows of every possible combination across the four columns.

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