Gruppieren von Datenspalten nach gemeinsamen Werten

Question

Ich weiß nicht, wie ich genau beschreiben soll, was ich tun muss, deshalb gebe ich ein Beispiel. Ein Kollege hat einen Datensatz in Excel wie diesen:

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

Das Endergebnis sollte ungefähr so ​​aussehen:

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

Oder auch:

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

usw.

(falls es hilft: Die Spalten sind die Proteinextraktionsmethoden und die Buchstaben sind die Protein-IDs – wir müssen feststellen, welche Proteine ​​mit welchen Methoden extrahiert werden)

Mein Kollege macht das manuell, aber es liegen genügend Daten vor, sodass eine Automatisierung wirklich hilfreich wäre.

Gibt es in Excel eine Formel, um dies automatisch zu erledigen?

Answer 1

Dies ist keine schlüsselfertige Lösung, aber wenn Sie Tausende von Zeilen haben, kann Ihnen dies einiges an Aufwand ersparen. (Machen Sie dies in einer Arbeitskopie Ihrer Datei, nur für den Fall, dass etwas explodiert oder zusammenschmilzt, denn „Rückgängig“ funktioniert nicht immer.) Hinweis: Dieses Verfahren wurde für Excel 2007 entwickelt (ich habe es jedoch in Excel 2013 erneut überprüft).

Kopieren Sie zunächst alle Ihre Daten in eine temporäre Spalte. Nennen wir sie V. Beachten Sie, dass Sie die Überschrift aus Spalte A kopieren müssen. Andernfalls geben Sie einen Dummy-Wert in Zelle V1 ein.

Gehen Sie nun auf die Registerkarte „Daten“, in die Gruppe „Sortieren und Filtern“ und klicken Sie auf „Erweitert“:

                       

Dadurch wird das Dialogfeld „Erweiterter Filter“ geöffnet:

                 

Überprüfen Sie, ob „Listenbereich“ Ihre Daten in Spalte V anzeigt. Wählen Sie „An anderen Ort kopieren“ und „Nur eindeutige Datensätze“. Geben Sie „W1“ in das Feld „Kopieren nach“ ein – oder klicken Sie in das Feld und dann auf W1 (es gibt mehrere Techniken, die zum gleichen Ergebnis führen). Klicken Sie auf „OK“. Sie sollten ungefähr Folgendes erhalten:

                             

d. h. eine Liste Ihrer eindeutigen Datenwerte. Möglicherweise müssen Sie Spalte W sortieren.

Geben Sie nun =NOT(ISNA(VLOOKUP($W2,A$2:A$4,1,FALSE)))X2 ein (ersetzen Sie  4durch die Nummer der letzten Zeile, die Daten enthält) und ziehen bzw. füllen Sie es nach unten, bis es der Spalte W entspricht (d. h. eine Zeile für jeden eindeutigen Wert in Ihren Originaldaten) und nach rechts bis zur Spalte Z (d. h. der Anzahl der Spalten in Ihren Daten).

                              

Damit erhalten Sie eine Wahrheitstabelle, die der zweiten Form des gewünschten Ergebnisses in der Frage entspricht (aber mit „WAHR“ und „FALSCH“ statt „Ja“ und „Nein“). Zum Beispiel:

• X2 ist WAHR, da Spalte A „aaaaa“ enthält,
• X3 ist WAHR, da Spalte A „bbbbb“ enthält,
• Y2 ist WAHR, da Spalte B „aaaaa“ enthält,
• Y3 ist FALSCH, weil Spalte B nicht „bbbbb“ usw. enthält.

Löschen Sie Spalte V und korrigieren Sie die Überschriften (in Zeile 1) nach Belieben. Wenn Sie die Spalten AC nicht in der Tabelle behalten möchten, kopieren Sie die Spalten WZ und fügen Sie die Werte ein.

---

Einige Erläuterungen zur Formel: Die Formel, die ich oben vorgestellt habe, ist für die Verwendung in Spalte X vorgesehen. was Spalte A entspricht.  Da ich verwendet habe $W2, ist dies ein absoluter Verweis auf Spalte W und es wird auf die Zelle verweisen, wenn die Formel in die Zeile gezogen/gefüllt wird WnNeiner beliebigen Spalte. Im Gegensatz dazu A$2:A$4ist ein absoluter Verweis auf die Zeilen 2 bis 4, aber ein relativer Verweis auf die Spalte A. Wenn die Formel in die Spalte Y gezogen wird, ändert sich dieser Verweis automatisch in  B$2:B$4. Wenn die Formel in die Spalte Z gezogen wird, ändert sich dieser Verweis automatisch in  C$2:C$4.