Как я могу обновить эту формулу, чтобы она работала для всех столбцов справа, а не только для текущих 2, для которых она настроена?

Предполагая, что обе ваши таблицы начинаются с ячейки A1, в 'Group History'!B2 введите:

 =IF($A2<>B$1,SUMPRODUCT((XLOOKUP($A2,'Group History'!$A$3:$A$28,'Group History'!$B$3:$S$28, 0,0)=XLOOKUP(B$1,'Group History'!$A$3:$A$28,'Group History'!$B$3:$S$28,0,0))*(XLOOKUP($A2,'Group History'!$A$3:$A$28,'Group History'!$B$3:$S$28, 0,0)<>"")),"x")

и скопируйте формулу вниз и вправо.

Один из способов добиться этого с помощью формулы массива, которая продолжает автоматически заполняться вниз и вправо, — это сначала преобразовать данные в формат, ориентированный на строки, а затем перебирать учащихся, подсчитывая, сколько раз они оказались в одной группе с каждым разом. другой студент отдельно.

Чтобы отменить поворот данных, выберите «Вставка» > «Лист», назовите новый лист Unpivot и поместите эту формулу в ячейку A1:

=let( 
  unpivot_, lambda(data, numFixedCols, numColsPerGroup, let(k,n(numFixedCols),d,if (k,data,hstack(sequence(rows(data)),data)),f,if (k,k,1),g,numColsPerGroup,s,lambda(r,c,h,w,chooserows(choosecols(d,sequence(1,w,c)),sequence(h,1,r))),h,hstack(s(1,1,1,f),"Date","GroupID"),i,sequence(1,(columns(d)-f)/g,f+1,g),a,reduce(h,sequence(rows(d)-1,1,2),lambda(a,r,let(x,s(r,1,1,f),b,reduce(tocol(æ,2),i,lambda(y,c,let(z,s(r,c,1,g),if (""=+z,y,vstack(y,hstack(x,s(1,c,1,1),z)))))),vstack(a,b)))),if (k,a,choosecols(a,sequence(1,columns(a)-1,2))))), 

  unpivot_('Group History'!A2:ZZ, 1, 1) 
)

Это будет работать до столбца ZZ, но при необходимости вы можете пройти до ZZZ.

Чтобы получить матрицу, вставьте еще один лист и поместите туда эту формулу:

=let( 
  students, tocol('Group History'!A3:A, 1), 
  data, filter(Unpivot!A2:C, len(Unpivot!A2:A)), 
  names, choosecols(data, 1), 
  dates, choosecols(data, 2), 
  groupIds, choosecols(data, 3), 
  matrix, map(students, lambda(s, let( 
    d, filter(dates, names = s), 
    i, filter(groupIds, names = s), 
    inSameGroup, filter(names, match(dates & groupIds, d & i, 0)), 
    counts, query(inSameGroup, "select Col1, count(Col1) group by Col1", 0), 
    row, arrayformula(ifna(vlookup( 
      torow(students), counts, 2, false 
    ))), 
    arrayformula(if (torow(students) = s, "x", row)) 
  ))), 
  vstack( 
    hstack( 
      "in the same group on the same date", 
      torow(students) 
    ), 
    hstack(students, matrix) 
  ) 
)

Видеть let(), лямбда(), карта(), vstack(), hstack(), selectcols(), фильтр(), vlookup(), match(), arrayformula(), запрос(), torow() и tocol().

С Microsoft 365 вы можете попробовать следующее:

=LET(
    students, 'Group History'!A3:A28,
    grp_assigned, 'Group History'!B3:AD28,
    num_students, ROWS(students),
    st_indices, SEQUENCE(num_students),
    num_shared, LAMBDA(x, y,
        IF(
            x = y,
            "x",
            SUM(
                --BYCOL(
                    CHOOSEROWS(grp_assigned, x, y),
                    LAMBDA(c, IF(SUM(--(LEN(TRIM(c)) = 0)), 0, ROWS(UNIQUE(c)) = 1))
                )
            )
        )
    ),
    history_matrix, MAKEARRAY(num_students, num_students, num_shared),
    HSTACK(VSTACK("", students), VSTACK(TOROW(students), history_matrix))
)

Результат:

Итерация двух строк (студентов) за раз, а затем по каждому столбцу (дата):

• Установите количество столбцов = 0, если есть пробелы SUM(--(LEN(TRIM(c)) = 0))
• Если поделиться, уникальное количество будет равно 1 ROWS(UNIQUE(c)) = 1

Пожалуйста, скорректируйте диапазон B3:AD28 в соответствии с возможными будущими записями.

Google Таблицы

Кажется, работает с некоторыми корректировками:

• в BYCOL, с --(ROWS(UNIQUE(c)) = 1)
• чтобы проверить пустые строки sum(--byrow(c, lambda(a, len(trim(a)) = 0)))

=LET(
    students, 'Group History'!A3:A28,
    grp_assigned, 'Group History'!B3:AD28,
    num_students, ROWS(students),
    st_indices, SEQUENCE(num_students),
    num_shared, LAMBDA(x, y,
        IF(
            x = y,
            "x",
            SUM(
                BYCOL(
                    CHOOSEROWS(grp_assigned, x, y),
                    LAMBDA(c, IF(sum(--byrow(c, lambda(a, len(trim(a)) = 0))), 0, --(ROWS(UNIQUE(c)) = 1)))
                )
            )
        )
    ),
    history_matrix, MAKEARRAY(num_students, num_students, num_shared),
    result, HSTACK(VSTACK("", students), VSTACK(TOROW(students), history_matrix)),
result
)