Spalten zusammenführen und sortieren

Zahnerer · Beitrag von **Zahnerer** » Mi, 21.02.2007 01:10

Hallo OO-Gemeinde, halle OO-Cracks!

Ich bin neu hier und habe hier schon einige super Lösungsansätze gefunden.

Wahrscheinlich könnt Ihr mir auch bei meinem Problem helfen.

Ich habe auf einem Tabellenblatt 3 Spalten mit Kundennamen von 3 Mitarbeitern. Die Kundennamen beinhalten außerdem eine Unterscheidung nach der Art (Kunde - Lieferant).

D.h. die Spalten sollen (ohne erste Zeile) zusammengeführt werden und in einer Spalte ausgegeben werden. Zusammen mit der dazugehörigen Spaltenüberschrift als Zelleninhalt rechts von der neuen Spalte. Zudem soll das Ergebnis nach Art sortiert und dann alphabetisch sortiert sein. (Zur Vereinfachung habe ich hier Kunde und Lieferant eingegeben. Eigentlich sind es zwei verschiedene Kundenarten.)

Versucht habe ich es mit folgender Excel-Lösung aus einem Excel-Forum:
(Hier der Link: http://www.excelformeln.de/formeln.html?welcher=315)

Zusammenführung mit Leerzeilen:
=WENN(INDEX($J$1:$L$333;REST(ZEILE()-1;333)+1;(ZEILE()-1)/333+1)="";"";INDEX($J$1:$L$333;REST(ZEILE()-1;333)+1;(ZEILE()-1)/333+1))

Danach die Leerzeilenvernichtung:
=WENN(ZEILE()>ANZAHL2($J$1:$L$333);"";INDEX($S$1:$S$999;KKLEINSTE(WENN($S$1:$S$999<>"";ZEILE($S$1:$S$999));ZEILE())))

Der erste Teil funktioniert. Bei der Leerzeilenvernichtung würde er in diesem Fall nun aber leider ab Zeile 6 "#Wert" ausgeben.

Außerdem bin ich in dieser Lösung die Sortierung nach Art und dann alphabetisch noch nicht angegangen. Das wäre dann der nächste Schritt.

Es muss doch hier jemanden geben, der so etwas schon einmal gemacht hat, bzw. der echt viel Ahnung von der Materie hat. Wo steckt hier das Problem der Excel2OO-Konvertierung? Was mache ich falsch und wie bekomme ich die Sortierung hin?

Ich freue mich über jede Antwort! Vielen Dank im Voraus!

Zahnerer

Beitrag von **Gert Seler** » Mi, 21.02.2007 08:52

Hallo Zahnerer,
die 2te. Formel ist eine "MatrixFormel". Diese werden mit der
Tastenkombination "Strg + Umschalt + Enter" abgeschlossen
und zeigen dann an "Anfang" und "Ende" die Klammern "{ und }".
Nur so wird die Formel berechnet.

mfg
Gert

FranzX · Beitrag von **FranzX** » Mi, 21.02.2007 09:26

Hallo Zahnerer,

wäre noch hinzuzufügen, dass diese Formel über Bearbeiten|Inhalte einfügen - Formeln in die übrigen Zellen der Spalte kopiert werden muss.
Diese Formel kann nicht über das Aufziehkästchen nach unten gezogen werden!

Gruß
FranzX

Zahnerer · Beitrag von **Zahnerer** » Do, 22.02.2007 10:24

Hallo Gert Seler, hallo FranzX,

vielen Dank für die schnelle Antwort!

Die Zusammenführung der Daten und das Herauslöschen der Leerzeilen funktioniert nun wunderbar. Nur bekomme ich nun statt der Leerzeilen am Ende der Liste "#Wert" angezeigt. Das kommt wahrscheinlich daher, dass ich die Listen, aus denen die Formeln ihre Daten holen mit einem Bezug herstelle. Will heißen, Tabellenblatt A gebe ich ein mit Kopfzeile und sonstigen Informationen, auch Kundeninformationen (Adresse usw.); Tabellenblatt B führe ich die relevanten Daten in einer Tabelle mithilfe einer Formel zusammen (die Kopfzeile Mitarbeiter habe ich hier weggelassen, nun habe ich die Kunden jedes Mitarbeiters in einer Spalte). Ich denke. der "#Wert" kommt von der in jeder Zelle (auch in Leerzellen) des geprüften Bereichs vorkommenden Formel ("=$TabelleA.$A$1"). Wie kann ich verhindern, das diese Formel erkannt wird oder wie schaffe ich es, dass, wenn die Ursprungsformel kein Ergebnis zeigt, dann meine Sortierformel auch kein Ergebnis zeigt? Das Problem besteht auch nur bei der Matrixformel.

Danke im Voraus für die Antworten,

mit freundlichen Grüßen,

Zahnerer

Zahnerer · Beitrag von **Zahnerer** » Sa, 24.02.2007 00:50

Hallo Hilfesuchende,

falls es jemanden interessiert, wie ich hier nach viel Probieren meine Formeln zum Daten nach Art und dann alphabetisch Sortieren und zum lückenlosen Zusammenführen
verschiedener Spalten gelöste habe, gebe ich Euch hier nun eine kurze Beschreibung der Vorgehensweise und die entsprechenden Formeln dazu.

(Ich weiß aus eigener
Erfahrung, wie lange man suchen muß, um bei komplizierteren Fragestellungen den richtigen Formelansatz hinzubekommen)

Die Problematik habe ich oben bereits beschrieben. Einzig dazu anzumerken ist, dass die Kundenart innerhalb des Namens unterschieden wird ("Labor XY" oder
"Dr. Z") Die Lösung ist folgende:

=WENN(ODER(INDEX($A$1:$J$100;REST(ZEILE()-1;100)+1;(ZEILE()-1)/100+1)="";LINKS(INDEX($A$1:$J$100;REST(ZEILE()-1;100)+1;(ZEILE()-1)/100+1);2)="Dr")=1;"";
INDEX($A$1:$J$100;REST(ZEILE()-1;100)+1;(ZEILE()-1)/100+1))

Hier werden zunächst alle Einträge der Tabelle in eine Spalte geschrieben (inklusive Leerzeilen innerhalb des Tabbellenbereiches) und daraus wird noch die Kundenart herausgefiltert.

--> LINKS(Zelle;die ersten zwei Buchstaben)="Dr". Selbiges habe ich für alle Kundenarten gemacht.

{=WENN(ZEILE()>ANZAHL2($A$1:$J$100);"";INDEX($K$1:$K$1000;KKLEINSTE(WENN($K$1:$K$1000<>"";ZEILE($O$1:$O$1000));ZEILE())))}

Dann kam die Matrixformel, mithilfe derer die Leerzeilen innerhalb der neuen Spalten gelöscht wurden.

=WENN(ISTFEHLER(O1)=1;"";WERT(VERKETTEN(CODE(KLEIN(TEIL(O1;7;1)));",";CODE(KLEIN(TEIL(O1;8;1)))))+ZEILE()*0,0000001)

Dann habe ich für die alphabetische Sortierung die ersten beiden Namensbuchstaben mit der Funktion CODE(KLEIN(TEIL(Zelle;ab Buchstabe X;1 Buchstabe)))in Zahlen
umgewandelt und mit WERT(VERKETTEN(Code 1;","Code 2)) erst mit einem Komma in der Mitte verkettet und dann in einen zählbaren Wert zurückverwandelt. Wichtig ist hier
die Addierung mit einer extrem kleinen von Zeile zu Zeile minimal unterschiedlichen Zahl hier +ZEILE()*0,0000001, da sonst Probleme auftreten, wenn 2 Namen identisch
sind! Dadurch könnte man die Rangfolge der Sortierung nicht eindeutig festlegen!

=WENN(ISTFEHLER(O1)=1;"";RANG(M1;M$1:M$1000;1))

Dann werden diese Zahlen, bei mir beispielsweise 122,2280007 anhand der Größe in eine Rangfolge gebracht. Die Rangfolge sollte in der Spalte links von der Matrixformel
stehen, damit der SVERWEIS gleich auch funktioniert.

=WENN(ISTFEHLER(R1)=1;"";SVERWEIS(ZEILE();Q$1:R$1000;2;0))

Nun haben wir die einzelnen Kundenarten alphabetisch sortiert in einer Spalte vorliegen.

=WENN(INDEX($S$1:$T$1000;REST(ZEILE()-1;1000)+1;(ZEILE()-1)/1000+1)="";"";INDEX($S$1:$T$1000;REST(ZEILE()-1;1000)+1;(ZEILE()-1)/1000+1))

Jetzt werden die Kundenarten zusammengesetzt (wieder mit Leerzeichen dazwischen)

{=WENN(ZEILE()>ANZAHL2($S$1:$T$1000);"";INDEX($U$1:$U$2000;KKLEINSTE(WENN($U$1:$U$2000<>"";ZEILE($V$1:$V$2000));ZEILE())))}

und mit derselben Matrixformel Leerzeichen-frei geschrieben.

=WENN(ISTFEHLER(V1)=1;"";V1)

Nun noch den "#Wert!"-Fehler beheben und das Ganze sauber in einer Ergebnisspalte ausgeben.

=WENN(W1<>"";WENN(ISTNV(SVERWEIS(W1;Y$1:Z$100;2;0))=0;SVERWEIS(W1;Y$1:Z$100;2;0);WENN(ISTNV(SVERWEIS(W1;AA$1:AB$100;2;0))=0;SVERWEIS(W1;AA$1:AB$100;2;0);
WENN(ISTNV(SVERWEIS(W1;AC$1:AD$100;2;0))=0;SVERWEIS(W1;AC$1:AD$100;2;0);WENN(ISTNV(SVERWEIS(W1;AE$1:AF$100;2;0))=0;SVERWEIS(W1;AE$1:AF$100;2;0);
WENN(ISTNV(SVERWEIS(W1;AG$1:AH$100;2;0))=0;SVERWEIS(W1;AG$1:AH$100;2;0);WENN(ISTNV(SVERWEIS(W1;AI$1:AJ$100;2;0))=0;SVERWEIS(W1;AI$1:AJ$100;2;0);
WENN(ISTNV(SVERWEIS(W1;AK$1:AL$100;2;0))=0;SVERWEIS(W1;AK$1:AL$100;2;0);WENN(ISTNV(SVERWEIS(W1;AM$1:AN$100;2;0))=0;SVERWEIS(W1;AM$1:AN$100;2;0);
WENN(ISTNV(SVERWEIS(W1;AO$1:AP$100;2;0))=0;SVERWEIS(W1;AO$1:AP$100;2;0);WENN(ISTNV(SVERWEIS(W1;AQ$1:AR$100;2;0))=0;SVERWEIS(W1;AQ$1:AR$100;2;0)))))))))));"")

Meine Mitarbeiterdaten habe ich dann mit einer einfachen aber ziemlich langen SVERWEIS-Formel dazugestellt.

Also ich finde, dass ich mich als Anfänger in Sachen Formeln-schreiben ganz gut geschlagen habe. Man braucht ein gutes Forum (!) -- ich kann da übrigens eines empfehlen#

-- und ein wenig Phantasie!

Vielen Dank nochmals an alle Forennutzer und all die fleißigen und geduldigen "Besserwisser", die es uns Anfängern doch sehr viel leichter machen, uns zurechtzufinden!

Herzliche Grüße aus dem Frankenlande, Zahnerer

OpenOffice.info

Spalten zusammenführen und sortieren

Spalten zusammenführen und sortieren

Fertiggestellt!