Excel技术求助：按ID批量将实例均等拆分为5组（修改公式）

Solution to Auto-Split Each ID's Instances into 5 Even Groups #

Got it, let's fix your grouping formula so it works automatically for every ID in column A—no hardcoded ranges needed. The key is to first calculate each ID's total instance count, then assign group numbers as evenly as possible, even when the total isn't a perfect multiple of 5.

For Excel 365/2021 (Dynamic Array Support) #

Use this spill formula in cell B2 (it’ll auto-fill the entire column for you, no dragging required):

=LET(
    id_range, A2:A,
    first_occur_row, XMATCH(id_range, id_range),
    instance_index, SEQUENCE(ROWS(id_range)) - first_occur_row + 1,
    total_instances, COUNTIF(id_range, id_range),
    base_group_size, INT(total_instances / 5),
    extra_instances, total_instances MOD 5,
    group_num, IF(
        instance_index <= extra_instances * (base_group_size + 1),
        CEILING(instance_index / (base_group_size + 1), 1),
        CEILING((instance_index - extra_instances * (base_group_size + 1)) / base_group_size, 1) + extra_instances
    )
)

Quick breakdown of how this works:

• id_range: References all IDs in column A starting from row 2.
• first_occur_row: Finds the first row each ID appears, so we can count instances per ID.
• instance_index: Assigns a 1-based number to each instance of an ID (e.g., 1, 2, 3 for the third instance of ID=1).
• total_instances: Counts how many times each ID appears in the column.
• base_group_size: The minimum number of instances per group (for IDs where total instances aren’t divisible by 5).
• extra_instances: How many groups need an extra instance to split the total as evenly as possible.
• group_num: Assigns the final group number: the first extra_instances groups get base_group_size+1 instances, the rest get base_group_size.

For Older Excel Versions (No Dynamic Arrays) #

Use this formula in cell B2 and drag it down to the last row of your data (adjust $A$2:$A$1000 to match your actual data range):

=LET(
    current_id, A2,
    all_ids, $A$2:$A$1000,
    instance_idx, COUNTIF($A$2:A2, current_id),
    total, COUNTIF(all_ids, current_id),
    base_size, INT(total/5),
    extra, total MOD 5,
    IF(
        instance_idx <= extra*(base_size+1),
        CEILING(instance_idx/(base_size+1),1),
        CEILING((instance_idx - extra*(base_size+1))/base_size,1)+extra
    )
)

If your Excel version doesn’t support the LET function, use this simplified (but longer) version:

=IF(COUNTIF($A$2:A2,A2)<=(COUNTIF($A$2:$A$1000,A2)MOD5)*(INT(COUNTIF($A$2:$A$1000,A2)/5)+1),CEILING(COUNTIF($A$2:A2,A2)/(INT(COUNTIF($A$2:$A$1000,A2)/5)+1),1),CEILING((COUNTIF($A$2:A2,A2)-(COUNTIF($A$2:$A$1000,A2)MOD5)*(INT(COUNTIF($A$2:$A$1000,A2)/5)+1))/INT(COUNTIF($A$2:$A$1000,A2)/5),1)+(COUNTIF($A$2:$A$1000,A2)MOD5))

Example of how this works #

Suppose ID=1 has 7 instances:

• Groups 1 and 2 will have 2 instances each (since 7 MOD 5 = 2 extra instances)
• Groups 3, 4, 5 will have 1 instance each
  This ensures the split is as even as possible, no manual adjustments needed.

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