6
S-Verweis mit Wenn-Und, Transponieren????
Hallo zusammen,
ich habe folgendes Problem (und es ist schon kompliziert es überhaupt verständlich zu erklären):
Wie in der Abbildung zu sehen, habe ich mehrer Klassen in Spalte B (bis zu 15), jedoch variieren die Anzahl der Einträge von 10 bis 15.
Jetzt soll die Tabelle transponiert werden, so dass sie in etwa so aussieht:
fbnr | 11 | 12 | 13 | 14 | 15 | ...
8314405 | 3190 | 1578 | 258 | ... | ...
8314517 | 247 | 214 | 1258 | ... | ...
8314931 | 125 | 7890 | 6987 | ... | ...
Die Idee ist, jeweils in der Zeile, in der der fbnr-Wert zum ersten Mal auftaucht den jeweiligen Wert für 11, 12, 13, etc. zu schreiben und die restlichen Zeilen danach zu löschen.
Meine Versuche mit Wenn-Dann und S-Verweis gingen allesamt schief, da die Anzahl der Klassen in Spalte B variiert, so dass nicht alle Werte in der ersten, jeweiligen fbnr-Zeile stehen.
Hat jemand eine Idee wie ich mein Problem elegant lösen kann?
Vielen Dank
R
ich habe folgendes Problem (und es ist schon kompliziert es überhaupt verständlich zu erklären):
Wie in der Abbildung zu sehen, habe ich mehrer Klassen in Spalte B (bis zu 15), jedoch variieren die Anzahl der Einträge von 10 bis 15.
Jetzt soll die Tabelle transponiert werden, so dass sie in etwa so aussieht:
fbnr | 11 | 12 | 13 | 14 | 15 | ...
8314405 | 3190 | 1578 | 258 | ... | ...
8314517 | 247 | 214 | 1258 | ... | ...
8314931 | 125 | 7890 | 6987 | ... | ...
Die Idee ist, jeweils in der Zeile, in der der fbnr-Wert zum ersten Mal auftaucht den jeweiligen Wert für 11, 12, 13, etc. zu schreiben und die restlichen Zeilen danach zu löschen.
Meine Versuche mit Wenn-Dann und S-Verweis gingen allesamt schief, da die Anzahl der Klassen in Spalte B variiert, so dass nicht alle Werte in der ersten, jeweiligen fbnr-Zeile stehen.
Hat jemand eine Idee wie ich mein Problem elegant lösen kann?
Vielen Dank
R
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 292247
Url: https://administrator.de/contentid/292247
Printed on: April 26, 2024 at 23:04 o'clock
6 Comments
Latest comment
Guckst du z.B. hier:
Excel Zeilen in eine Zeile anzeigen
den Code dort ein bisschen angepasst so dass es nicht in eine Zelle zusammengefasst wird sondern stattdessen als neue Zellen
Gruß jodel32
Excel Zeilen in eine Zeile anzeigen
den Code dort ein bisschen angepasst so dass es nicht in eine Zelle zusammengefasst wird sondern stattdessen als neue Zellen
Sub MergeDuplicates()
Dim ws As Worksheet, rngStart As Range, rngEnd As Range, rngCurrent As Range
' Erstes Tabellenblatt referenzieren
Set ws = Worksheets(1)
'Startzelle der Daten festlegen
Set rngStart = ws.Range("A2")
' Zelllendbereich ermitteln
Set rngEnd = rngStart.End(xlDown).Offset(0, 1)
' Bereich zuerst nach Nummern sortieren
ws.Range(rngStart, rngEnd).Sort ws.Range("A1")
'So lange zusammenfassen bis auf eine Zelle keinen Inhalt hat
Set rngCurrent = rngStart
While rngCurrent.Value <> ""
If rngCurrent.Value = rngCurrent.Offset(1, 0).Value Then
rngCurrent.End(xlToRight).Offset(0, 1).Value = rngCurrent.Offset(1, 1).Value
rngCurrent.Offset(1, 0).EntireRow.Delete
Else
Set rngCurrent = rngCurrent.Offset(1, 0)
End If
Wend
End Sub
Hallo Jodel,
vielen Dank erstmal aber ich kann mit Codes und Makros gar nichts anfangen und würde das gerne mit einem S-Verweis lösen und dann Copy+Paste das Problem lösen. Meine Kenntnisse reichen nicht aus um den Code einzugeben und ggf. anzupassen.
Hat mir jemand einen Vorschlag wie ich das mit einem S-Verweis löse?
Gruß
R
vielen Dank erstmal aber ich kann mit Codes und Makros gar nichts anfangen und würde das gerne mit einem S-Verweis lösen und dann Copy+Paste das Problem lösen. Meine Kenntnisse reichen nicht aus um den Code einzugeben und ggf. anzupassen.
Hat mir jemand einen Vorschlag wie ich das mit einem S-Verweis löse?
Gruß
R
Hallo @TheDukeOfOGHooD,
matrix_transpose_292247.xlsx
Dieses sieht so aus:
Für die Spalten A und B habe ich im Sheet Namen definiert welche in den Formeln Verwendung finden, so dass sie leserlicher werden.
Für die Werte in Spalte A verwende ich den Namen Numbers und die zu erhaltenden Daten in Spalte B den Namen Values.
In Zelle F2 steht dann folgende Matrix-Formel welche im ersten Schritt alle eindeutigen Nummern ohne Duplikate auflistet, und welche man einfach nach unten kopieren kann:
In Zelle G3 steht folgende Formel, welche sich ebenfalls nach rechts und unten kopieren lässt.
BITTE BEACHTEN: Dies sind Matrix-Formeln. Man muss die Formel mit STRG-SHIFT-ENTER abschließen anstatt nur mit einem "einfachen" Enter. Eine Matrixformel erkennt man in Excel daran das in der Formelzeile die Formel mit geschweiften Klammern eingefasst wird.
Man sollte beachten das das nur für eine begrenzte Anzahl an Daten effizient läuft da Matrix-Formeln einiges an Rechenleistung benötigen.
Eine Makro-Lösung habe ich bei Bedarf aber auch auf Lager:
matrix_transpose_makro_292247.xlsm
Grüße Uwe
Hat mir jemand einen Vorschlag wie ich das mit einem S-Verweis löse?
mit einem reinen S-Verweis klappt das nicht,Hat jemand eine Idee wie ich mein Problem elegant lösen kann?
aber mit etwas Matrix-Formel-Würze lässt sich das auch elegant lösen. Siehe dazu folgendes Beispiel-Sheet, daran kann man es sich einfacher abschauen.matrix_transpose_292247.xlsx
Dieses sieht so aus:
Für die Spalten A und B habe ich im Sheet Namen definiert welche in den Formeln Verwendung finden, so dass sie leserlicher werden.
Für die Werte in Spalte A verwende ich den Namen Numbers und die zu erhaltenden Daten in Spalte B den Namen Values.
In Zelle F2 steht dann folgende Matrix-Formel welche im ersten Schritt alle eindeutigen Nummern ohne Duplikate auflistet, und welche man einfach nach unten kopieren kann:
=WENNFEHLER(INDEX(Numbers;VERGLEICH(0;ZÄHLENWENN($F$1:F1;Numbers);0);1);"")
=WENNFEHLER(INDEX(Values;KKLEINSTE(WENN($F2=Numbers;ZEILE(Numbers)-MIN(ZEILE(Numbers))+1;"");SPALTE(A$1)));"")
Man sollte beachten das das nur für eine begrenzte Anzahl an Daten effizient läuft da Matrix-Formeln einiges an Rechenleistung benötigen.
Eine Makro-Lösung habe ich bei Bedarf aber auch auf Lager:
matrix_transpose_makro_292247.xlsm
Grüße Uwe
1
Moin colinardo,
im Prinzip ja, aber es passt nicht ganz zur Aufgabenstellung.
Der TO hat 3 Spalten, die er pivotieren bzw. "transponieren" will.
Wenn im Beispiel mal 99 Zeilen vorhanden wären, also
- in $A$2:$A$99 das, was du "Numbers" genannt hast
- in $B$;B99 die LUs, bei dir "Values", die die neuen Spaltenüberschriften darstellen sollen in $G1:$J1
- dann stehen in $C$2:$C$99 die Werte (hab ich Values2 benannt). Die sollen in die neue Matrix einsortiert werden.
Nun denn, wenn die Ausgangssituation ist, dass die Rohdaten nach Spalte A + Spalte B sortiert sind, dann passt deine Formel in den Feldern $F$2:F$99. Lassen wir also so.
Die neuen "Spaltenköpfe" in Zelle $G1 bis irgendwas$1 berechnen wir analog deiner Formel:
$G1;
(Als Matrixformel eingeben und nach rechts kopieren sowet nötig)
Den Inhalt der Matrix dann beginnend mit $G$2:
(Als Matrixformel eingeben und nach rechts kopieren)
Dann sollte es noch besser passen.
Grüße
Biber
im Prinzip ja, aber es passt nicht ganz zur Aufgabenstellung.
Der TO hat 3 Spalten, die er pivotieren bzw. "transponieren" will.
Wenn im Beispiel mal 99 Zeilen vorhanden wären, also
- in $A$2:$A$99 das, was du "Numbers" genannt hast
- in $B$;B99 die LUs, bei dir "Values", die die neuen Spaltenüberschriften darstellen sollen in $G1:$J1
- dann stehen in $C$2:$C$99 die Werte (hab ich Values2 benannt). Die sollen in die neue Matrix einsortiert werden.
Nun denn, wenn die Ausgangssituation ist, dass die Rohdaten nach Spalte A + Spalte B sortiert sind, dann passt deine Formel in den Feldern $F$2:F$99. Lassen wir also so.
Die neuen "Spaltenköpfe" in Zelle $G1 bis irgendwas$1 berechnen wir analog deiner Formel:
$G1;
=Wennfehler(INDEX(Values;VERGLEICH(0;ZÄHLENWENN($G1:G1;Values);0);1);"") Den Inhalt der Matrix dann beginnend mit $G$2:
=Wennfehler(INDEX(Values2;VERGLEICH($F2&G$1;$A$2:$A$99&$B$2:$B$99;0);0);"") Dann sollte es noch besser passen.
Grüße
Biber
1
Hallo Biber
.
Die Formeln sind ja dann, wie du schon demonstriert hast leicht angepasst. Sie sollten nur zeigen wie man eindeutige Werte auflistet und mehrere Ergebnisse zu einer Suche nebeneinander auflistet - das Handwerkszeug eben für diese Aufgabenstellung.
, hatte halt den Lerneffekt für den TO vordergründig im Sinn.
Aber jetzt hat er ja alles was er braucht.
Grüße Uwe
im Prinzip ja, aber es passt nicht ganz zur Aufgabenstellung.
wollte damit ja nur den Grundstein für den TO legen .Die Formeln sind ja dann, wie du schon demonstriert hast leicht angepasst. Sie sollten nur zeigen wie man eindeutige Werte auflistet und mehrere Ergebnisse zu einer Suche nebeneinander auflistet - das Handwerkszeug eben für diese Aufgabenstellung.
Dann sollte es noch besser passen.
Jepp , hatte halt den Lerneffekt für den TO vordergründig im Sinn.Aber jetzt hat er ja alles was er braucht.
Grüße Uwe
Moin colinardoo,
Na gut, dann hat er jetzt zwei Grundsteine.
Ist doch zum Rumbasteln und Probieren vielleicht besser als einer.
Mal abwarten, was er daraus macht.
Grüße zurück
Biber
Na gut, dann hat er jetzt zwei Grundsteine.
Ist doch zum Rumbasteln und Probieren vielleicht besser als einer.
Mal abwarten, was er daraus macht.
Grüße zurück
Biber