Excel - Фильтр значений со специальными правилами для дубликатов

Question

У меня есть два местоположения (комнаты) и несколько идентификаторов, которые находятся во многих отношениях; каждый идентификатор может встречаться один или несколько раз в одной или обеих комнатах.  Вот небольшой пример:

ID:       Location
1         Room_1
2         Room_2
3         Room_1
3         Room_2
4         Room_1
4         Room_1

Я хотел бы разделить идентификаторы на две группы в зависимости от местоположения.  Если идентификатор появляется только в Room_1, я хочу, чтобы он вошел в группу 1.  Если идентификатор появляется только в Room_2, я хочу, чтобы он вошел в группу 2.  Если идентификатор появляется в более чем одном экземпляре (строке), как 3 и 4 выше, я хочу сгруппировать его в группу 2, если какой-либо из идентификаторов относится к комнате 2.  Если есть несколько экземпляров, но все они находятся в комнате 1, то идентификатор может остаться в группе 1.

Поэтому в этом примере я бы хотел, чтобы идентификаторы 1 и 4 были в первой группе, а идентификаторы 2 и 3 - во второй.

Идеальным решением было бы разделить их на отдельные листы, но любое разделение было бы хорошо.

---

Вот большая выборка данных:

ID:       Location
1         Room_1
2         Room_2
3         Room_1
3         Room_2
4         Room_1
4         Room_1
5         Room_2
6         Room_1
7         Room_2
7         Room_2

После группировки это должно выглядеть так:

Group 1:

ID:       Location
1         Room_1
4         Room_1
4         Room_1
6         Room_1

Group 2:

ID:       Location
2         Room_2
3         Room_1
3         Room_2
5         Room_2
7         Room_2
7         Room_2

Возможно, более простой способ выразить это:

• Каждый идентификатор, который появляется только в Room_1, входит в группу 1.

• Каждый идентификатор, который появляется в Room_2 (вообще), входит в Группу 2.

   

Кроме того, я хочу сохранить каждую отдельную строку из ввода, даже те, которые идентичны.

Answer 1

Я думаю, что для решения, которое вы хотели, нужно 4 шага:

ШАГ 1:

• Импортируйте свои данные в таблицу (вкладка "Таблица")
• Вставьте сводную таблицу (PT) в столбцы, а не только в текущие данные, на всем пути вниз; это будет сделано для динамического роста, который может быть "обновлен" до PT в меню параметров, когда выбран PT. Обратите внимание на настройку PT справа от изображения.

ШАГ 2:

• Просто вставьте = в каждое поле, чтобы скопировать каждую ячейку из "Таблицы вкладок".
• Во втором столбце вставьте формулу, как показано на вкладке "Копировать", начиная с B2:=IFNA(IF(AND(LOOKUP(A2,Table!$J$3:$AZ$3)=A2,LOOKUP(A2,Table!$J$4:$AZ$4)=A2),"Room_2",Table!B2),Table!B2)

ЭТАП 3:

• Вставьте новый лист "Room_1", PT с "Tab Tab".
• Обратите внимание на фильтр для Room_1/Location.
• Смотрите вкладку Room_1.

ШАГ 4:

• Вставьте новый лист "Room_2", PT с "Tab Tab".
• Обратите внимание на фильтр для Room_2/Location.
• Смотрите вкладку Room_2.

GIF изображение. Нажмите, чтобы увеличить и просмотреть каждый слой.

Answer 2

Я надеюсь, что вы действительно точно описали свою проблему, и вы не собираетесь говорить что-то вроде: «О, по моим реальным данным, у меня больше двух комнат».

Я предполагаю, что у вас есть «ID» в столбце A и «Location» в столбце B , а все остальное доступно для производных значений.  (Если это не так, просто выдвиньте это в первый неиспользуемый столбец.)  Предполагая, что у вас есть заголовки («ID» и «Location») в строке 1,

• В ячейку C2 вставьте =A2 & B2
• В ячейку D2 вставьте =ISERROR(MATCH(A2 & "Room_2", C$2:C$11, 0))
  Замените 11 номером последней строки, где у вас есть данные.
• Выберите ячейки C2 и D2 и перетащите / заполните до последней строки, где у вас есть данные.

& является оператором конкатенации строк в Excel.  Вы также можете сказать CONCATENATE(A2, B2) , но A2 & B2 , очевидно, гораздо более лаконичны.  Это дает такие значения, как 1Room_1 , 2Room_2 , 3Room_1 , 3Room_2 ,… и т.д. В столбце C Функция MATCH(A2 & "Room_2", C$2:C$11, 0) (в столбце D) ищет в столбце C идентификатор в этой строке, объединенный со строкой Room_2 .  Другими словами, есть ли строка, где этот идентификатор находится в комнате 2?  Если он есть, MATCH возвращает номер индекса, а ISERROR возвращает FALSE. В противном случае, MATCH возвращает ошибку, а ISERROR возвращает TRUE.

Таким образом, столбец D содержит TRUE для каждого идентификатора, который появляется только в комнате 1, и FALSE для любого идентификатора, который появляется (хотя бы один раз) в комнате 2:

            

Затем выполните сортировку по столбцу D в порядке убывания от наибольшего к наименьшему (по убыванию), чтобы ИСТИННЫЕ строки были перед строками FALSE: