Условная конкатенация между строками и столбцами для конкатенации

Question

У меня есть лист Excel посещаемости, который выглядит следующим образом. Каждый студент - это строка, каждая дата в классе - это столбец. У меня всего 48 классов, всего 66 учеников (это необходимо, потому что сложность решения должна быть выполнимой).

Теперь для каждой строки, то есть для каждого ученика, я хочу знать даты пропущенных занятий. Для ученика 6 я хочу, чтобы «9,19,23» отображалось в последней ячейке в этой строке.

Для каждого ряда я хочу, чтобы число учеников, которые пропустили занятия в тот день, составило список. Для date = 24 я хочу, чтобы «7,8,9,10» отображалось в последней ячейке в этом столбце.

Как мне сделать эту условную конкатенацию без жестких значений кодирования для каждой ячейки? Есть ли какая-нибудь формула диапазона, которую я могу использовать?

Answer 1

Ладно, это немного грязно, но работает, пока вы готовы создавать и скрывать кучу дополнительных столбцов.

По сути, идея состоит в том, чтобы создать целую сетку операторов IF (по одному для каждой ячейки в основных данных посещаемости), а затем скрыть все эти столбцы и объединить их значения в один столбец.

Справа от основных данных в ячейку во второй строке (строка для первого учащегося) добавьте следующую формулу:

=IF(B2="a",B$1&",","")

Это говорит о том, что если студент в этом ряду отсутствовал в данный день, напишите этот день здесь, иначе оставьте это поле пустым. ($ In B $ 1 важен, потому что это будет держать вывод, смотрящий на первую строку, даже в копируемых / вставленных версиях формулы.)

Затем скопируйте / вставьте эту формулу в полный диапазон ячеек, равный количеству ячеек данных о посещаемости (поэтому, если данные о посещаемости составляют 48 на 66 ячеек, то операторы IF также должны заполнять 48 на 66 ячеек).

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

Затем в столбце справа от всего этого, во второй строке, добавьте формулу вроде этого:

=CONCATENATE(X2,Y2,Z2,...etc...)

Замените X2, Y2, Z2 и т.д. Ячейками операторов IF (вы можете сделать это быстро, щелкнув по каждой ячейке, а затем нажав клавишу запятой).

Когда вы нажимаете Enter, эта ячейка должна выглядеть примерно так:6,13,18,

Заполните эту формулу вниз листа, чтобы учесть каждую строку / студент.

Теперь, если конечная запятая слишком болезненна для просмотра, вы можете создать еще один столбец для просмотра столбца сцепления и вернуть все символы этого столбца, кроме последнего.

=IF(LEN(X2)>0,LEFT(X2,LEN(X2)-1),"")

Замените X2 на ячейку с конкатенированной строкой. (Включая IF (LEN (X2)> 0), чтобы вы не получили ошибку, если у ученика была отличная посещаемость.

Затем просто спрячьте все столбцы, кроме последнего, и все готово!

В качестве альтернативы, я думаю, что VBA имеет функцию concatenateif, которая поможет, но я не знаю, как ее использовать.