Excel技术咨询：如何将单行拆分为多组配对内容

Hey there! I totally get it—when you’re dealing with loads of rows/columns and trying to split a single row into multiple paired entries, putting that problem into words can feel impossible. Let’s fix that so we can find the perfect solution for you.

First, let’s nail down the details with a few quick asks:

• Share a small, anonymized sample of your original data (3-5 rows is enough!). For example:

  | Item ID | Colors          | Sizes       | Categories       |
  |---------|-----------------|-------------|------------------|
  | 101     | Red,Blue,Green  | L,M,S       | Electronics,Home |
  | 102     | Black           | XL          | Apparel          |
  

• Show exactly what your desired output should look like. Using the sample above, maybe this is what you want:

  | Item ID | Color | Size | Category   |
  |---------|-------|------|------------|
  | 101     | Red   | L    | Electronics|
  | 101     | Red   | L    | Home       |
  | 101     | Red   | M    | Electronics|
  | 101     | Red   | M    | Home       |
  | ...     | ...   | ...  | ...        |
  | 102     | Black | XL   | Apparel    |
  

• Let us know any special rules:
  • Are only specific columns getting split (e.g., just Colors and Sizes)?
  • Do you need every possible combination (full Cartesian product) of the split values?
  • Are there columns that should stay linked to the original row without splitting?

To give you a head start: For most bulk splitting/pairing tasks, Power Query (built into Excel 2016+) is way easier than messy formulas. The general workflow would be:

1. Import your data into Power Query
2. For each column with multiple values, use Split Column > By Delimiter (e.g., comma) to turn it into a list column
3. Use Expand to New Rows for each list column (or use merge queries if you need cross-column pairing)
4. Load the cleaned data back to Excel

But without seeing your exact data and end goal, it’s hard to give step-by-step. Once you share those samples, we can refine this into a precise solution!

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