如何将表格中同一行的多组商品列转换为多行结构并保留客户信息

Method 1: Using Array Formulas (No Scripts) #

This approach combines INDEX, SEQUENCE, FLATTEN, and QUERY to split each product into its own row while retaining shared order/customer details.

Step-by-Step Formula #

Assume your data has:

• Fixed columns (repeat for every product): A (Order ID), B (Customer Name), C (Email Address), D (Total), E (Shipping Address)
• Product groups: Each product uses 3 columns (Brand, Description, Price), with 10 total groups (columns F-H = Product 1, I-K = Product 2, ..., F+27 = Product 10's Price)

Use this formula in a blank sheet starting at cell A1:

=ARRAYFORMULA(
  QUERY(
    {
      // Repeat fixed columns 10 times (once per product group)
      INDEX(A2:A, CEILING(SEQUENCE(ROWS(A2:A)*10, 1)/10)),
      INDEX(B2:B, CEILING(SEQUENCE(ROWS(A2:A)*10, 1)/10)),
      INDEX(C2:C, CEILING(SEQUENCE(ROWS(A2:A)*10, 1)/10)),
      INDEX(D2:D, CEILING(SEQUENCE(ROWS(A2:A)*10, 1)/10)),
      INDEX(E2:E, CEILING(SEQUENCE(ROWS(A2:A)*10, 1)/10)),
      // Flatten all Product Brand columns into a single column
      FLATTEN(F2:F, I2:I, L2:L, O2:O, R2:R, U2:U, X2:X, AA2:AA, AD2:AD, AG2:AG),
      // Flatten all Product Description columns
      FLATTEN(G2:G, J2:J, M2:M, P2:P, S2:S, V2:V, Y2:Y, AB2:AB, AE2:AE, AH2:AH),
      // Flatten all Product Price columns
      FLATTEN(H2:H, K2:K, N2:N, Q2:Q, T2:T, W2:W, Z2:Z, AC2:AC, AF2:AF, AI2:AI)
    },
    // Filter out rows where Product Brand is null
    "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8 WHERE Col6 IS NOT NULL",
    0
  )
)

Adjustments for Your Sheet #

• Fixed columns: Add/remove INDEX lines for each column you need to retain (e.g., Discount, Order Date).
• Product fields: If each product has more than 3 fields, add corresponding FLATTEN lines for each attribute (e.g., Product Category).
• Number of products: If you have fewer than 10 product groups, remove the extra column references from the FLATTEN sections.

Method 2: Using Google Apps Script (For Complex Cases) #

If your sheet has many product fields or you prefer a more flexible solution, use this script to automate the unpivoting:

Step-by-Step Script Setup #

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Delete any existing code and paste the following:

function unpivotProducts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const output = [];
  
  // Define fixed columns (adjust indices to match your sheet)
  const fixedCols = [0, 1, 2, 3, 4]; // Order ID, Customer Name, Email, Total, Shipping Address
  
  // Define product attribute suffixes and number of product groups
  const productAttrs = ["Brand", "Description", "Price"];
  const numProducts = 10;
  
  // Add headers to output
  const outputHeaders = fixedCols.map(i => headers[i])
    .concat(productAttrs);
  output.push(outputHeaders);
  
  // Process each row
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const fixedVals = fixedCols.map(idx => row[idx]);
    
    // Check each product group
    for (let p = 1; p <= numProducts; p++) {
      // Get column indices for current product's attributes
      const brandCol = headers.indexOf(`Product ${p} - Brand`);
      const descCol = headers.indexOf(`Product ${p} - Description`);
      const priceCol = headers.indexOf(`Product ${p} - Price`);
      
      // Skip if product brand is null/empty
      if (!row[brandCol]) continue;
      
      // Create new row with fixed values + product details
      const newRow = [...fixedVals, row[brandCol], row[descCol], row[priceCol]];
      output.push(newRow);
    }
  }
  
  // Write output to a new sheet
  const newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Unpivoted Data");
  newSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

Customize the Script #

• Fixed columns: Update the fixedCols array with the indices of columns you want to retain (0 = first column, 1 = second, etc.).
• Product attributes: Modify productAttrs if your products have additional fields (e.g., ["Brand", "Description", "Price", "Category"]).
• Product groups: Adjust numProducts to match the maximum number of products per row.

Run the Script #

• Save the script, then click the run button (▶️). Authorize the script when prompted.
• A new sheet named "Unpivoted Data" will be created with the split rows.

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