如何更新Excel公式以适配右侧所有列而非仅当前2列

Replace the formula in History Matrix!B3 with the following, then drag it across and down to fill the matrix:

=IF(B$2=$A3, "x", SUMPRODUCT(--('Group History'!$N3:$ZZ3 = INDEX('Group History'!$N:$ZZ, MATCH(B$2, 'Group History'!$A:$A, 0), COLUMN('Group History'!$N:$ZZ)))))

Key Improvements Over Original Formula #

1. Dynamic Column Handling

   • The range $N:$ZZ automatically includes all columns from N onwards. Adding new date columns to Group History will be picked up without manual formula edits.
   • For fully automatic detection of the last date column, use this extended version:

     =IF(B$2=$A3, "x", SUMPRODUCT(--('Group History'!$N3:INDEX('Group History'!$3:$3, 1, MATCH(REPT("z",255), 'Group History'!$2:$2)) = INDEX('Group History'!$N:INDEX('Group History'!$1:$1000, 1, MATCH(REPT("z",255), 'Group History'!$2:$2)), MATCH(B$2, 'Group History'!$A:$A, 0), COLUMN('Group History'!$N:INDEX('Group History'!$3:$3, 1, MATCH(REPT("z",255), 'Group History'!$2:$2)))))))
     

2. Robust Column Reference (No INDIRECT)

   • Replaced INDIRECT with INDEX/MATCH which uses cell references instead of text strings. Deleting columns before N won't break the formula, as references adjust automatically when columns are inserted or removed.

3. Accurate Student Row Lookup

   • Uses MATCH(B$2, 'Group History'!$A:$A, 0) to find the correct row for the student in B$2, instead of the fragile COLUMN(B$2)+1 assumption. This works even if students are added, removed, or reordered in the Group History sheet.

How It Works #

• The IF condition still outputs "x" when a student is compared to themselves.
• SUMPRODUCT iterates over each column in the group range:
  • For each column, it checks if the group value for the row student ($A3) matches the group value for the column student (B$2).
  • The -- converts boolean matches (TRUE/FALSE) to numeric values (1/0), which are summed to get the total number of shared groups.

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