Wie kann ich die Überschriften in einer Excel Tabelle nach ihren untergeordneten Daten sortieren?
Situation ist wie folgt: Die erste Spalte der Tabelle enthält Titel von verschiedenen Büchern. Die nächsten Spalten sind jeweils mit einem Schlagwort betitelt.
Zu jedem Buch ist nun die Häufigkeit des Schlagworts, welches in der Überschriften-Zeile steht in der jeweiligen Spalte eingetragen. Dabei sind nur die obersten ~30 Schlagwörter pro Buch eingetragen, es bleiben weit über 100 Zellen pro Buch leer.
Wenn wir zum Beispiel Hänsel und Gretel in der ersten Spalte stehen haben, steht unter dem Schlagwort „Hexe“ eine 18, wenn dieses Wort so häufig in dem Text vorkommt.
Jetzt möchte ich jedes Buch die häufigsten 3 Schlagwörter in eine neue Spalte setzen. Diese sind dann nicht mehr mit dem Schlagwort betitelt, sondern mit: Am häufigsten, zweithäufigsten, dritthäufigsten...
Mit einer Kombination aus KGRÖSSTE und WVERWEIS bin ich dem ganzen schon sehr nahe gekommen, aber es ergibt sich ein Problem: mit meiner Formel suche ich nach dem k-größten Wert und lasse mir mit WVERWEIS die jeweilige Überschrift zurück geben. Kommen zwei Schlagwörter in einem Text nun aber gleich oft vor, wird nur eines von beiden zurück gegeben, das andere wird ignoriert.
Beispiel: Hexe und Brot kommen beide 18 mal vor. Es wird jedoch nur Brot zurück gegeben und Hexe wird ignoriert. Stattdessen steht in der nächsten Zelle „Mutter“, welches nur 17 mal vorkommt.
Ich weiß die Frage ist kompliziert aber ich komme einfach nicht drauf. Ich würde mich freuen wenn jemand eine Lösung darauf hätte. Vielen Dank!
5 Antworten
Ich habe Dich im Prinzip so verstanden:
Formel in B2 ist:
=INDEX($A$1:$L$1;SUMMENPRODUKT(((($E2:$L2)-SPALTE($E2:$L2)/1000)=KGRÖSSTE((($E2:$L2)-SPALTE($E2:$L2)/1000);SPALTE(A1)))*SPALTE($E2:$L2)))
Die kannst Du zweimal nach rechts kopieren und denn die drei Zellen zusammen nach unten kopieren.
Ich musste allerdings die Leerzellen durch Nullen ersetzen.
Ich kann Dir aber zeigen, wie Du mit drei Klicks alle Leerzellen in Deiner Tabelle durch Nullen ersetzen kannst und auch, wie Du anschließend die Zellen so formatieren kannst, dass statt der Nullen (die drin bleiben) wieder Leerzellen angezeigt werden.
Frag dann nochmal nach, wenn Du diese Hilfe brauchst.
Habe ich Dich so im Prinzip richtig verstanden?
Übrigens wird bei mehreren gleichwertigen Anzahlen immer zuerst die mit der kleinstern Spaltennummer, also quasi die linke, gezeigt.
Falls Du lieber die rechte priorisieren willst, muss Du die beiden - durch + ersetzen.
Hilft Dir das?
Ich denke, das bekommst du nur mit einer Datenbank hin.
So spontan würde ich an eine Pivottabelle denken, in der Anzahl in Kombination mit absteigender Sortierung abgefragt wird.
Mach eine Hilfsspalte D2=Wenn(A1="";D1;A1) In dieser Spalte sind alle Buchtitel ausgefüllt. Jetzt kannst Du eine weitere Hilfsspalte E mit E1=D1&" "&B1. (Formeln natürlich runterziehen). Dritte Hilfsspalte F mit = C1+Zeile(C1)/10000000
Setze die Spalten D, E und F zu WERT um
Jetzt kannst du zB Spalte A und D löschen (oder auch nicht) und nach Herzenslust sortieren, verweisen, KKleinste/Grösste verwenden etc.
Für KGrösste evtl noch die Zusatz-Hilfsspalte G mit =E1&" "&F1
Liste ist dann nach Titeln geordnet, dann Häufigkeit, dann Zeile (unterste der Häufigkeit zuoberst)
Mit einer Matrixformel wärs einfacher, aber so ist es übersichtlicher zu erstellen.
Hoffe, hab bei der "Trockenübung" keinen Fehler reingebracht, muss dringend weg.
Dritte Hilfsspalte F mit = C1&Text(Zeile(C1)/10000000;"0000000")
also
- Texte werden mit & verbunden und
- Zahlentext 7-stellig formatieren, damit auch Wechsel von 9=>10, 99=>100, ... richtig einsortiert werden (und bei theor. Möglichkeit von 1,1 Mio Zeilen reicht 7 Stellen)
auch das war falsch!
Text(Zeile(C1)/10000000;"0000000"): Hilfsspalte D2=Wenn(A1="";D1;A1) muss heissen: D2=Wenn(A2="";D1;A2)
in Spalte F müssen beide Komponenten mit höherer Stellenzahl vertextet und aneinandergehängt werden:
F1 = Text(C1;"000")&Text(Zeile(C1)/10000000;"0000000"),
(sonst würden Wörter mit Häufigkeit 10 geringer eingestuft als solche mit Häufigkeit 2...9) Würde bis 999 gleiche erfassen. Wenn mehr erforderlich (zB zum Buchstaben Zählen) kann auch das noch beliebig erhöht werden.
Danke erstmal für die Antwort!
Die Idee ist gut, aber leider glaube ich, dass es nicht ganz auf meine Tabelle anzuwenden ist. Bei Gelegenheit versuche ich einen Screenshot zu posten, weil ich glaube dass der Aufbau der Tabelle aus meiner Frage nicht genau genug klar gemacht wurde.
da fehlte noch was (auf die Schnelle)
Mit Zählenwenns kannst Du die Häufigkeit ermitteln und mit "Rang" den Wert des Ergebnisses
... möglicherweise gibt es auch einen einfacheren Weg.
Je nach Anzahl der Bücher reicht dies auch :o)
Danke für die Antwort! Leider sieht meine Tabelle etwas anders aus, tut mir leid dass das aus meiner Frage nicht ganz klar geworden ist.
Ich würde das in 2 Schritten - mit 2 Hilfsspalten machen.
z.B. ... und am Ende die Spalte A kopieren und "Werte in Spalte A" einfügen.
Danach kannst Do - wenn ich das richtig verstanden habe - nach Wunsch sortieren.
Für die Optik kannst Du danach die Spalten a und b ausblenden.
.
kann es leider nicht ändern. Spalte B reicht aus.
Angaben für Spalte A sind verzichtbar - zu löschen.
Vielen Dank! Genauso sieht meine Tabelle im Grunde aus. Ich probiere die Formel später einmal aus, aber wenn sie so bei dir funktioniert, sehe ich keinen Grund warum sie nicht auch bei mir klappen sollte. Ich durchblicke zwar noch nicht ganz wie genau sie funktioniert, aber wenn ich sie einmal angewendet habe, sollte sich das ergeben.