谷歌表格公式需求：实现A列值为1时按指定次数重复对应单元格

Got it, let's solve this problem where you need to repeat rows (with columns C and B) exactly D2 times whenever column A has a value of 1. The QUERY function you tried works for filtering but doesn't handle repetition, so we'll build a single formula using Google Sheets functions that does both.

The Full Formula #

=LET(
  filtered_data, FILTER(A1:C7, A1:A7=1),
  repeat_count, D2,
  num_filtered_rows, ROWS(filtered_data),
  repeat_indices, FLATTEN(SEQUENCE(num_filtered_rows, repeat_count, 1, 0)),
  INDEX(filtered_data, repeat_indices, {3,2})
)

Breakdown of How It Works #

Let's walk through each part to make sense of it:

• LET(): This lets us name intermediate values, making the formula cleaner and easier to adjust later.
• filtered_data: Uses FILTER() to grab all rows where column A equals 1 (this replaces the filtering part of your original QUERY).
• repeat_count: Pulls the number of repetitions directly from cell D2.
• num_filtered_rows: Counts how many rows we've filtered (so we know how many sets of repetitions to create).
• repeat_indices: Generates a list of row numbers that repeat repeat_count times for each filtered row. For example, if there are 2 filtered rows and D2=3, this becomes [1,1,1,2,2,2] (flattened to a 1D array).
• INDEX(): Uses the repeating indices to pull the corresponding values from columns 3 (C) and 2 (B) of the filtered data—matching the column order you had in your QUERY (select C,B).

Notes #

• This formula is built for Google Sheets. If you're using Excel, the syntax would change slightly (you'd use TEXTSPLIT + REPT instead of FLATTEN + SEQUENCE, but feel free to ask for that version if needed!).
• Make sure your range A1:C7 matches your actual data range—adjust it if your table is larger or smaller.

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