如何用Excel/Sheets公式提取唯一行并动态构建索引表？

Hey there! Let’s break down your two needs and craft a solution that works for both extracting unique rows and building that dynamic index table based on your blue-labeled worksheets. I noticed you already experimented with an INDIRECT + TEXTJOIN formula—great start! Let’s refine that to hit your goals.

First: Extract Unique Rows from Individual Sheets #

Before building the index, we need to pull only unique rows from each target sheet. For either Google Sheets or Excel, the UNIQUE function is your go-to here. For a single sheet named "Sales", you’d use:

=UNIQUE('Sales'!A2:B)

This strips out duplicate full rows and keeps only the unique entries in the A2:B range.

Second: Build the Dynamic Index Table (Blue-Tabbed Sheets Only) #

The catch here is that neither Google Sheets nor Excel can natively read worksheet tab colors with formulas, so we’ll assume you’ve already listed all your blue-tabbed sheet names in a range (like your A6:A32). Now let’s combine those sheet names with their unique rows, and format the result to match your yellow-highlighted expected output.

For Google Sheets #

Use this combined formula to pull unique rows and add the sheet name as the first column:

=QUERY(
  FLATTEN(
    ARRAYFORMULA(
      IF(
        A6:A32<>"",
        {
          REPT(A6:A32, COUNTA(UNIQUE(INDIRECT("'"&A6:A32&"'!A2:B")))),
          UNIQUE(INDIRECT("'"&A6:A32&"'!A2:B"))
        },
        ""
      )
    )
  ),
  "where Col2 is not null",
  0
)

How this works:

1. UNIQUE(INDIRECT("'"&A6:A32&"'!A2:B")) grabs unique rows from each blue-tabbed sheet’s A2:B range.
2. REPT(A6:A32, COUNTA(...)) repeats the sheet name enough times to match the number of unique rows from that sheet (so each row in the index gets its source sheet name).
3. ARRAYFORMULA + IF loops through every sheet name in your A6:A32 list and creates a combined array of sheet names + unique rows.
4. FLATTEN turns that messy multi-dimensional array into a clean 2D table.
5. QUERY filters out any empty rows to keep your index tidy.

For Excel (365/2021+) #

Excel uses a slightly different set of functions, but we can achieve the same result with LET and lambda functions:

=LET(
  sheetNames, A6:A32,
  uniqueRowsPerSheet, BYROW(sheetNames, LAMBDA(sheet, IF(sheet<>"", UNIQUE(INDIRECT("'"&sheet&"'!A2:B")), ""))),
  combinedData, REDUCE("", uniqueRowsPerSheet, LAMBDA(acc, curr, IF(curr<>"", VSTACK(acc, HSTACK(REPT(INDEX(sheetNames, MATCH(curr, uniqueRowsPerSheet, 0)), ROWS(curr)), curr), acc)))),
  FILTER(combinedData, INDEX(combinedData,,2)<>"")
)

Quick breakdown:

• LET lets us define variables for cleaner code.
• BYROW loops through each sheet name to pull its unique rows.
• REDUCE + VSTACK + HSTACK merges all the unique rows and adds the corresponding sheet name as the first column.
• FILTER removes any leftover empty rows.

A note on your original formula #

Your original formula =INDIRECT(CONCATENATE("{",TEXTJOIN(";",true,ARRAYFORMULA("'" &A6:A32 &"'!" & "A2:B")),"}")) does a great job pulling all rows from your target sheets, but it misses two key pieces: filtering out duplicates and adding the source sheet name. The formulas above build on your initial logic to fix both those gaps.

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