Есть ли способ использовать то же поле, что и строки и столбцы в листах Google, для подсчета уникальных вхождений между столбцами?

Я создал новый лист («Помощь Эрика») в вашем образце электронной таблицы.

В В1:

=SORT(FILTER(Sheet1!B1:1,Sheet1!B1:1<>""))

Это просто заполняет верхнюю строку списком ваших имен, отсортированных по алфавиту.

В А2:

=TRANSPOSE(SORT(FILTER(Sheet1!B1:1,Sheet1!B1:1<>"")))

Это заполняет A2 тем же списком имен, что и выше, только вертикально.

В B2 основная формула для сетки (которая затем перетаскивается вверх и вниз):

=ArrayFormula(IF( ($A2 = "") + (B$1 = "") + ($A2=B$1),, SUM(MMULT(IF((FILTER(Sheet1!$B$2:$L,Sheet1!$A$2:$A<>"")=1) * (Sheet1!$B$1:$L$1=$A2),1,0), SEQUENCE(COLUMNS(Sheet1!$B$1:$L$1),1,1,0)) * MMULT(IF((FILTER(Sheet1!$B$2:$L,Sheet1!$A$2:$A<>"")=1) * (Sheet1!$B$1:$L$1=B$1),1,0), SEQUENCE(COLUMNS(Sheet1!$B$1:$L$1),1,1,0)))))

Первый ( ) + ( ) + ( ) проверяет три условия ИЛИ. Если что-то верно, ячейка останется пустой. Это то, что позволяет перетаскивать формулу полностью вправо и вниз, не выбрасывая ошибок и, по сути, «ожидая» новых данных из первых двух формул выше, которые она может обработать.

Остальная часть формулы слишком сложна, чтобы требовать полного объяснения (например, как работает MMULT в деталях), поскольку это сайт, управляемый добровольцами. (Написание формулы заняло больше времени, чем я обычно провожу за день на этом или других форумах.) Но вот суть.

Две сетки, каждая из которых сформирована с помощью MMULT (умножение матриц), SUMmed. Первый MMULT создаст сетку того же размера, что и сетка Sheet1, заполненную 1, только если выполняются два условия: что в этом слоте уже была 1 и что имя выше соответствует имени справа в «Справке Эрика». " сетка. В противном случае результат для этого слота равен нулю. Второй MMULT формирует сетку того же размера на основе тех же условий, только на этот раз он получает 1 только в том случае, если 1 уже есть, а имя выше совпадает с именем над ячейкой в ​​«Справке Эрика». Эти две сетки перемножаются, и если произведение равно 1, мы знаем, что в ОБОИХ именах есть 1. После SUMmed мы получаем количество общих проектов для этих двух имен.

При перетаскивании этой формулы ссылки на ячейки, не заблокированные знаком доллара, изменятся, так что два разных имени будут сравниваться двумя сетками MMULT.

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

Опять же, формула в настоящее время перетаскивается до столбца Z и вниз до строки 200. Однако она ссылается только на столбец L (что соответствует вашему текущему списку имен). Если ваше реальное приложение имеет больше имен и, таким образом, переносится за пределы столбца L, самый простой способ изменить все формулы сразу:

1. Перейдите на лист «Erik Help» (который вы, конечно, можете переименовать по своему усмотрению).

2. Нажмите Ctrl-H, чтобы открыть диалоговое окно «Найти/Заменить».

3. Введите $L в поле НАЙТИ и $? в поле ЗАМЕНА (где ? будет новым столбцом, на который вы хотите распространить результаты, например, $M или $P и т. д.)

4. Выберите «Этот лист» в раскрывающемся списке «Поиск».

5. Установите флажок «Также искать в формулах».

6. Нажмите кнопку «Заменить все».

Если набор данных снова сжимается или увеличивается, выполните те же действия, просто заменив старую ссылку на самый дальний столбец новой ссылкой на самый дальний столбец.

Вот очень простой способ сделать это, который просто меняет пару столбцов, выбранных в счетчиках, когда формула перемещается поперек и вниз по относительной адресации:

=countifs(index($B$2:$D,0,row(A1)),1,index($B$2:$D,0,column(A1)),1)

потянул вниз и поперек.

Попытка более общего решения.

Вопрос помечен сводной таблицей. Хотя подход со сводной таблицей кажется полезным, формат данных совершенно не подходит для этого. Задача состоит в том, чтобы преобразовать данные из единиц и нулей в номера столбцов, чтобы

1 1 0 => 1 2

1 0 1 => 1 3

1 1 1 => 1 2, 1 3 and 2 3.

Этого можно достичь, сгенерировав пары чисел следующим образом и выполнив поиск в исходных данных:

1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

Формулы для генерации этих последовательностей:

=ArrayFormula(quotient(mod(sequence(90,1,0),9),3)+1)

и

=ArrayFormula(mod(sequence(90,1,0),3)+1)

(9, потому что в строке данных 3X3 пары, 90, потому что в строке 10 данных).

Следующее генерирует поиск для каждой строки данных

=ArrayFormula(quotient(sequence(90,1,0),9)+1)

Объединив все это вместе и обернув в сводной запрос, вы получите

=ArrayFormula(query({vlookup(quotient(sequence(90,1,0),9)+2,{row(B2:D),B2:D},quotient(mod(sequence(90,1,0),9),3)+2,0)*(quotient(mod(sequence(90,1,0),9),3)+1),
vlookup(quotient(sequence(90,1,0),9)+2,{row(B2:D),B2:D},mod(sequence(90,1,0),3)+2,0)*(mod(sequence(90,1,0),3)+1)},
"select count(Col1) where Col1<>0 and Col2<>0 group by Col1 pivot Col2"))

Формулу можно обобщить на разное количество строк и столбцов.