Hallo liebe Forengemeinde,
ich habe eine Tabelle, in der aus einer anderen Arbeitsmappe Schüler angezeigt werden, die Zeiten nacharbeiten müssen. Angezeigt werden also immer Namen und Fehlzeiten (Ist/Soll). Da die Namen aus einer Klassenliste angezeigt werden, sind dazwischen immer Leerzeilen und Sie stehen an der Stelle, in der Sie auch in der Klassenliste stehen würden.
Was ich jetzt brauche ist eine Formel, welche mir die Namen und Fehlzeiten kopiert und geordnet zu einer Liste ohne Leerzellen an einer anderen Stelle anzeigt.
Ich hoffe ihr versteht was ich meine und könntet mir weiterhelfen. Hab unten die Datei mal angehängt (Namen sind verändert und dadurch die Formel in den Zellen ersetzt).
Schönen Gruß
Christian
Moin,
die Liste strotzt vor #BEZUG! - Fehlern, da die Verknüpfung fehlt.
Kannst/darfst/willst du Power Query ->
http://www.excel-ist-sexy.de/power-query-das-add-in/ nutzen? Dann lässt sich das damit gut lösen.
Natürlich auch mit VBA. Und wahrscheinlich auch mit mehr oder weniger transparenten Formeln.
Hallo Günther,
oh das mit den Bezugsfehlern habe ich nicht vorausgesehen. Aber eigentlich ist es eben eine Liste von Namen, wo eben dazwischen Leerzellen sind. Diese sollen schön geordnet werden, so dass man schnell Namen auswendig machen kann und auch die Anzahl weiß.
Ich nutze Excel auf einem Mac und habe die Version 2016. Sollte Power Query hier möglich sein, bin ich offen dafür. VBA bin ich auch offen dafür, nur hätte ich eigentlich gerne eine Lösung, die ich nachvollziehen kann und bei VBA habe ich gemerkt, dass ich Excel nicht sooo häufig nutze, dass sich ein Lernen der Sprache lohnen würde.
Edit: Habe die Arbeitsmappe noch von den Formeln befreit, sodass man sie bearbeiten kann. Nur wird die Liste eben von einer Wenn Funktion immer dynamisch mit Daten gefüllt - falls das für die Weiterbearbeitung relevant sein sollte.
Schönen Gruß
Christian
Code:
Sub M_snb()
sn = Filter([transpose(if(B4:B34="",,A4:A34))], "0", 0)
If UBound(sn) > -1 Then Cells(3, 11).Resize(UBound(sn) + 1) = Application.Transpose(sn)
sn = Filter([transpose(if(E4:E34="",,D4:D34))], "0", 0)
If UBound(sn) > -1 Then Cells(3, 12).Resize(UBound(sn) + 1) = Application.Transpose(sn)
sn = Filter([transpose(if(H4:H34="",,G4:G34))], "0", 0)
If UBound(sn) > -1 Then Cells(3, 13).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub
Hi Christian,
mit Formeln und je einer Hilfsspalte könntest du das so lösen:
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P |
| 1 | Nacharbeiten | | | | | | | | | | | | | Liste mit Personen, die Nacharbeiten müssen | | |
| 2 | | | | | | | | | | | | | | 17a | 17b | 17c |
| 3 | 17a | Soll | Ist | HS | 17b | Soll | Ist | HS | 17c | Soll | Ist | HS | | Meyer | Löwe | Lange |
| 4 | | | | | | | | | | | | | | Schulze | Meyermüller | |
| 5 | | | | | | | | | | | | | | Müller | Kamir | |
| 6 | | | | | Löwe | | | 6 | | | | | | Leopold | | |
| 7 | | | | | | | | | | | | | | Kaiser | | |
| 8 | | | | | | | | | | | | | | | | |
| 9 | | | | | | | | | | | | | | | | |
| 10 | | | | | | | | | | | | | | | | |
| 11 | | | | | | | | | Lange | | | 11 | | | | |
| 12 | | | | | | | | | | | | | | | | |
| 13 | | | | | | | | | | | | | | | | |
| 14 | | | | | Meyermüller | | | 14 | | | | | | | | |
| 15 | | | | | | | | | | | | | | | | |
| 16 | | | | | | | | | | | | | | | | |
| 17 | | | | | | | | | | | | | | | | |
| Zelle | Formel |
| N3 | =WENNFEHLER(INDEX($A$1:$A$33;KKLEINSTE($D$4:$D$33;ZEILE()-2));"") |
| O3 | =WENNFEHLER(INDEX($E$1:$E$33;KKLEINSTE($H$4:$H$33;ZEILE()-2));"") |
| P3 | =WENNFEHLER(INDEX($I$1:$I$33;KKLEINSTE($L$4:$L$33;ZEILE()-2));"") |
| Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2013 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Die Hilfsspalten kannst du einfach ausblenden.
Nachtrag:
Habe gerade festgestellt, dass ich versehentlich die Formeln in den Hilfsspalten nicht mitangegeben habe. In D4 gehört diese Formel rein und entsprechend runterziehen. Auf die anderen Hilfsspalten musst du sie halt noch anpassen.
Code:
=WENN(A4="";"";ZEILE())
Ohne Hilsfspalten:
in K3: Array Formel:
PHP-Code:
=IFERROR(INDEX($A$4:$A$34;SMALL(IF($B$4:$B$34<>"";ROW($1:$30));ROW()-2));"")
In L3
PHP-Code:
=IFERROR(INDEX($D$4:$D$34;SMALL(IF($E$4:$E$34<>"";ROW($1:$30));ROW()-2));"")
in M3
PHP-Code:
=IFERROR(INDEX($G$4:$G$34;SMALL(IF($H$4:$H$34<>"";ROW($1:$30));ROW()-2));"")
Moin Christian,
nein, Mc.Xcel ist leider kastriert und da gibt es so einige "Nettigkeiten" der Win-Version nicht. So leider auch kein Power Query ... :@
Hallo zusammen,
habe gerade Günthers Lösung ausprobiert und es klappt suuuuper und ist für mich auch reproduzierbar - auch wenn leider nicht ganz nachvollziehbar, aber daran arbeite ich jetzt mal.
Ich danke ganz herzlich für diese tolle Lösung!!!!
Schönen sonnigen Abend
Christian
Zitat:Günthers Lösung
Wie, was, wo ?
Er meint =WECHSELN("Günther";"h";"")
:21: