S-Verweis mit Wenn-Und, Transponieren????
TheDukeOfOGHooD (Level 1) - Jetzt verbinden
05.01.2016 um 15:27 Uhr, 1064 Aufrufe, 6 Kommentare, 2 Danke
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
Antworten
- Mehr
Auf Facebook teilen
Auf Twitter teilen6 Antworten
- LÖSUNG 114757 schreibt am 05.01.2016 um 15:50:35 Uhr
- LÖSUNG TheDukeOfOGHooD schreibt am 05.01.2016 um 16:38:22 Uhr
- LÖSUNG colinardo schreibt am 05.01.2016 um 19:03:58 Uhr
- LÖSUNG Biber schreibt am 06.01.2016 um 19:59:32 Uhr
- LÖSUNG colinardo schreibt am 07.01.2016 um 10:34:51 Uhr
- LÖSUNG Biber schreibt am 07.01.2016 um 13:38:26 Uhr
- LÖSUNG colinardo schreibt am 07.01.2016 um 10:34:51 Uhr
- LÖSUNG Biber schreibt am 06.01.2016 um 19:59:32 Uhr
LÖSUNG 05.01.2016, aktualisiert um 15:51 Uhr
Guckst du z.B. hier:
https://www.administrator.de/forum/excel-zeilen-zeile-anzeigen-241036.ht ...
den Code dort ein bisschen angepasst so dass es nicht in eine Zelle zusammengefasst wird sondern stattdessen als neue Zellen
Gruß jodel32
https://www.administrator.de/forum/excel-zeilen-zeile-anzeigen-241036.ht ...
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
LÖSUNG 05.01.2016 um 16:38 Uhr
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
LÖSUNG 05.01.2016, aktualisiert um 19:31 Uhr
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.
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)));"")Grüße Uwe
LÖSUNG 06.01.2016 um 19:59 Uhr
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
LÖSUNG 07.01.2016, aktualisiert um 10:39 Uhr
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
LÖSUNG 07.01.2016, aktualisiert um 13:39 Uhr
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
