单行列交替结构下实现类数据透视表（Pivot Table）功能的替代方案问询

Absolutely, this requirement is totally feasible! You can achieve that alternating "item, amount, item, amount..." single-row structure with automatic sum aggregation using dynamic array formulas—no static pivot table or single dependent table required. Here's how to implement it in both Google Sheets and Excel:

Google Sheets Implementation #

Assume your raw ingredient data is in columns A (item names) and B (amounts), starting from row 2. Use this formula to generate the flattened, aggregated single row:

=FLATTEN(HSTACK(UNIQUE(FILTER(A2:A, A2:A<>"")), MAP(UNIQUE(FILTER(A2:A, A2:A<>"")), LAMBDA(item, SUMIF(A2:A, item, B2:B)))))

Let’s break down what each part does:

• UNIQUE(FILTER(A2:A, A2:A<>"")): Gets all distinct item names, excluding any empty cells.
• MAP(..., LAMBDA(item, SUMIF(...))): Calculates the total amount for each unique item using SUMIF.
• HSTACK: Pairs each unique item with its corresponding total amount (items in column 1, sums in column 2).
• FLATTEN: Converts this two-column range into a single row, resulting in the exact "item, amount, item, amount..." order you want.

Handling Multiple Data Sources #

If your client data is spread across multiple sheets/ranges, just combine them using curly braces:

=FLATTEN(HSTACK(UNIQUE(FILTER({Sheet1!A2:A; Sheet2!A2:A}, {Sheet1!A2:A; Sheet2!A2:A}<>"")), MAP(UNIQUE(FILTER({Sheet1!A2:A; Sheet2!A2:A}, {Sheet1!A2:A; Sheet2!A2:A}<>"")), LAMBDA(item, SUMIF({Sheet1!A2:A; Sheet2!A2:A}, item, {Sheet1!B2:B; Sheet2!B2:B})))))

Case Insensitivity #

If you want to aggregate items like "apple" and "Apple" together, adjust the formula to normalize case:

=FLATTEN(HSTACK(PROPER(UNIQUE(FILTER(LOWER(A2:A), A2:A<>""))), MAP(UNIQUE(FILTER(LOWER(A2:A), A2:A<>"")), LAMBDA(item, SUMIF(LOWER(A2:A), item, B2:B)))))

Excel Implementation (Dynamic Arrays) #

For Excel 365/2021 with dynamic array support, use this equivalent formula:

=TOROW(HSTACK(UNIQUE(FILTER(A2:A, A2:A<>"")), BYROW(UNIQUE(FILTER(A2:A, A2:A<>"")), LAMBDA(x, SUMIF(A2:A, x, B2:B)))))

• TOROW replaces Google Sheets' FLATTEN to convert the two-column HSTACK result into a single row.
• BYROW performs the sum calculation for each unique item, similar to Google Sheets' MAP.

Key Benefits #

• Automatic Updates: The formula will refresh automatically when you add new client ingredient data, just like a pivot table.
• No Dependent Tables: Everything is contained in a single formula—no need to maintain intermediate helper tables.
• Scalable: Works with as many items and data sources as you need, making it perfect for growing client lists.

This setup will give you exactly the dynamic, aggregated shopping list structure you’re looking for.

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