Группировка столбцов данных по общим значениям

Question

Я не знаю, как правильно описать, что мне нужно сделать, поэтому приведу пример. У коллеги есть набор данных в Excel, вот такой:

Col A    Col B    Col C
aaaaa    aaaaa    bbbbb
bbbbb    ccccc    ccccc
ccccc    ddddd    eeeee

Конечный результат должен быть примерно таким:

Col A    Col B    Col C
aaaaa    aaaaa
bbbbb             bbbbb
ccccc    ccccc    ccccc
         ddddd
                  eeeee

Или даже:

      Col A    Col B    Col C
aaaaa Yes      Yes      No
bbbbb Yes      No       Yes

и т. д.

(если это поможет, столбцы — это методы извлечения белков, а буквы — идентификаторы белков — нам нужно определить, какие белки какими методами извлекаются)

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

Есть ли формула в Excel, позволяющая сделать это автоматически?

Answer 1

Это не «готовое» решение, но если у вас тысячи строк, это может сэкономить вам некоторые усилия. (Сделайте это в черновой копии файла, просто на случай, если что-то пойдет не так или рухнет, потому что «Отменить» не всегда срабатывает.) Примечание: эта процедура была разработана для Excel 2007 (но я перепроверил ее в Excel 2013).

Сначала скопируйте все данные в рабочий столбец; назовем его V. Обратите внимание, что вам нужно скопировать заголовок из столбца A или поместить какое-либо фиктивное значение в ячейку V1.

Теперь перейдите на вкладку «Данные», группу «Сортировка и фильтр» и нажмите «Дополнительно»:

                       

Откроется диалоговое окно «Расширенный фильтр»:

                 

Убедитесь, что «Диапазон списка» отображает ваши данные в столбце V. Выберите «Копировать в другое место» и «Только уникальные записи». Введите «W1» в поле «Копировать в» — или щелкните в поле, а затем щелкните в W1 (есть несколько методов, которые дадут тот же результат). Нажмите «ОК». Вы должны получить что-то вроде этого:

                             

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

Теперь =NOT(ISNA(VLOOKUP($W2,A$2:A$4,1,FALSE)))введите X2 (замените  4на номер последней строки, содержащей данные) и перетащите/заполните вниз, чтобы соответствовать столбцу W (т. е. одна строка для каждого уникального значения в исходных данных), и вправо, чтобы соответствовать столбцу Z (т. е. количеству столбцов в ваших данных).

                              

Это дает вам таблицу истинности, соответствующую второй форме желаемого результата в вопросе (но с «ИСТИНА» и «ЛОЖЬ» вместо «Да» и «Нет»). Например,

• X2 — ИСТИНА, потому что столбец A содержит «aaaaa»,
• X3 — ИСТИНА, поскольку столбец A содержит «bbbbb»,
• Y2 — ИСТИНА, поскольку столбец B содержит «aaaaa»,
• Y3 — ЛОЖЬ, поскольку столбец B не содержит «bbbbb» и т. д.

Удалите столбец V и исправьте заголовки (в строке 1) на досуге. Если вы не хотите сохранять столбцы AC в таблице, скопируйте столбцы WZ и вставьте значения.

---

Некоторые пояснения к формуле: Формула, которую я представил выше, предназначена для использования в столбце X, что соответствует столбцу А.  Так как я использовал $W2, это абсолютная ссылка на столбец W, и она будет ссылаться на ячейку, когда формула перетаскивается/заполняется в строку. Wnнлюбого столбца. Напротив, A$2:A$4является абсолютной ссылкой на строки 2–4, но относительной ссылкой на столбец A. При перетаскивании формулы в столбец Y эта ссылка автоматически изменится на  B$2:B$4. При перетаскивании формулы в столбец Z эта ссылка автоматически изменится на  C$2:C$4.