Frage von KaliKalo, 78

Wie lautet die Excel funktion die ich suche (siehe Details?

HalloIch suche eine Excelfunktion mit der ich Werte aus einer Tabelle anhand von Bedingungen an bestimmte Stellen kopieren kann.

Es geht sich hierbei um sehr lange Dienstpläne die als eine Art Matrix vorhanden sind, aber in eine lesbare Tabelle umgewandelt werden sollen.

Bei den Bustaben der ersten Spalte handelt es sich um NamenDie weiteren Spalten der Matrix benannt mit 1-5 sind ZeiträumeDie Buchstaben K, G, LF etc sind zu besetzende Posten.Hellgraue (leere) Zellen sind Pausenzeiten. Diese werden nicht mit eingetragen

Die Farben dienen nur der besseren Unterscheidung der Posten.

Am Ende soll einfach zu erkennen seinz. Bsp.: A hat zu Zeitpunkt 1 an Stelle K zu sein

Genaueres wird in der anghängten Bilddatei erklärt

Das Beispiel ist sehr kurz, doch die sonst zu verarbeitenden Tabellen sind ewig lang, daher ist eine Funktion die all das vereinfacht wirklich notwendig

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Ninombre, Community-Experte für Excel, 17

ich dachte schon, dass ist eine Testfrage, um zu schauen, wer die Herausforderung annimmt, denn das ist zum einen ja eine Kreuztabelle, die aber transponiert werden muss weil Zeilen / Spalten sich vertauschen, zum anderen mehrere Treffer möglich sind. Mehrere Treffer in eine Zeile bekomme ich nicht hin, ohne mir die Finger zu brechen

Es sind also zwei verschiedene Formeln, um den ersten Treffer in der oberen Zeile einer Zeiteinheit zu ermitteln und in der darunterliegenden dann einen möglichen zweiten Treffer. Über optische Mittel kannst Du dafür sorgen, dass die beiden Zeilen als zusammengehörig erkannt werden. Vielleicht ist es für weitere Auswertungen auch sinnvoll die Werte nicht in eine Zeile zu vermischen (aus der Not eine Tugend machen ;-)

Ich frage in den Formeln nicht direkt auf die Zeiteinheiten 1,2,3 oder ähnliches ab, sondern habe es mit bereich.verschieben umgesetzt, damit man die Formeln in die anderen Zeilen kopieren kann und dadurch automatisch die Spalte, in der zu suchen ist, geändert wird. Das funktioniert aber nur solange immer zwei Zeilen zu einer Zeit gehören und keine Lücken enthalten sind: weder bei den Spalten in der Ursprungstabelle, noch in der Zieltabelle.
Das "aufrunden(zeile...." bewirkt, das jeweils nach zwei Zeilen die Suchspalte um 1 nach rechts verschoben wird. Daher ist egal, ob die Zeiteinheiten in den beiden Tabellen gleich oder unterschiedlich beschriftet sind.

In der Formel für die möglichen zweiten Treffer muss der Bereich zusätzlich "nach unten" verschoben werden, damit der erste Treffer nicht mehr im Bereich enthalten ist. Das müsste theoretisch auch in einer Zeile gehen, also die beiden Formeln einfach verknüpfen, das habe ich aber nicht hinbekommen.

Excel zeigt mir teilweise an, dass die Formel fehlerhaft ist - ich kann aber nicht erkennen, was noch schief sein sollte, in meinen Tests funktioniert es zumindest.

Formel für die jeweils erste Zeile:

=WENNFEHLER(INDEX($A$2:$A$7;VERGLEICH(I$6;BEREICH.VERSCHIEBEN($C$2:$C$7;0;AUFRUNDEN(ZEILE(E1)/2;0)-1);0);1);"")

für die jeweils zweite Zeile:

=WENNFEHLER(INDEX(BEREICH.VERSCHIEBEN($A$2:$A$7;VERGLEICH(I7;$A$2:$A$7;0);0);VERGLEICH(I$6;BEREICH.VERSCHIEBEN($C$2:$C$7;VERGLEICH(I7;$A$2:$A$7;0);AUFRUNDEN(ZEILE(E2)/2;0)-1);0);1);"")
Kommentar von KaliKalo ,

Bei den Zellen, die am Ende nur einen Namen enthalten, funktioniert es nun nach ein wenig rumgeschiebe. Allerdings bei Überschneidungen bekomme ich Fehler. Aber, dass ist schon 1000x besser als die manuelle Tortur.

Danke für die schnelle und präzise Hilfe

Kommentar von Ninombre ,

Für Deinen Fall würde ich eigentlich zu einem Makrolösung tendieren, wenn die Tabelle v.a. tatsächlich noch viel umfangreicher ist. Ich denke damit geht es einfacher...

Lösung könnte ungefähr so aussehen - unter der Annahme, dass die Ergebnisse allerdings als Tabelle ohne Leerzeilen dazwischen erstellt wird, das wird sonst noch etwas komplizierter, v.a. wenn Du noch was änderst:

Die Ergebnisse werden mit den jetzigen Einstellungen von H7:M11 eingetragen, das kannst Du mit den Variablen s und z noch verschieben. Ich habe die Zeilen / Spalten der Ergebnistabelle relativ zu den Zeilen/Spalten der Ursprungstabelle verwendet, d.h. für z und s gibst Du die Differenz an, wieviele Spalten bzw. Zeilen zwischen den beiden Tabellen liegen sollen. Wenn das nicht klappt, kann man eigene Variablen noch einführen. Erstmal wäre wichtig, dass das Prinzip als solches funktioniert.

Sub uebernahme()
Dim i, j, k, s, z As Long
s = 7 '1. Spalte mit Ergebnissen für die Tabelle 2 als Differenz zum Beginn der Tabelle 1
z = 5 '1. Zeile mit den Ergebnissen für die Tabelle 2 als Differenz zum Beginn der Tabelle 1
For i = 2 To 6 'Spalte 2 bis 6 der ersten Tabelle = B:F
For j = 2 To 7 'Zeilen 2 bis 7 der ersten Tabelle
Select Case Cells(j, i).Value 'reihenfolge der Spalten für Tabelle2 definieren
Case "K": k = 1
Case "G": k = 2
Case "H": k = 3
Case "S": k = 4
Case "LF": k = 5
Case "T": k = 6
End Select
If Cells(j, i) <> "" Then
If Cells(i + z, s + k).Value = "" Then
Cells(i + z, s + k).Value = Cells(j, 1).Value
Else: Cells(i + z, s + k).Value = Cells(i + z, s + k).Value & "," & Cells(j, 1).Value
End If
End If
Next j
Next i
End Sub
Kommentar von Iamiam ,

ein dickes DH!

Das Verknüpfen kann entweder passieren, indem Du einfach inkauf nimmst, dass der gleiche Name 2x erscheint oder mit bei dieser Lösung äusserst aufwändigen Wiederholungen:

=FormelName1&Wenn(Formelame1=FormelName2;"";", "&FormelName2)

Expertenantwort
von Iamiam, Community-Experte für Excel, 41

Nach längerem Nachdenken ahne ich, was Du darstellen willst. Die Schwierigkeit ist der zweifache Einsatz. Kann es auch noch höhere Kumulierungen geben (dreifach oder sogar noch höher und wenn ja, wie hoch maximal?)

Soll in der 2. Tabelle K in B1 und die 1 in A2 stehen?. Schreib mal die wichtigsten Grenzadressen auf, im derart Allgemeinen kann man nur schwafeln!

Und nochmal zum Verständnis: K ist eine Person oder ein Gerät, 1:5 waagrecht ist ein Zeitraum, zB jeweils ein Wochentag und entspricht 1:5 senkrecht aber mit Zwischenzeile, A:F sind Einsatzorte

Muss das ohnehin erst verinnerlichen und werds jetzt erst mal überschlafen, hoffe morgen Deine AW vorzufinden!

Kommentar von KaliKalo ,

K, LF, G, oder T sind Orte.

A-F sind Personen

1-5 sind Zeiträume.

In der Matrix ist einer Person und einem Zeitpunkt je ein Ort zugeordnet.

In der Tabelle soll am Ende einem Zeitpunkt und einem Ort eine Person zugeprdnet werden.

Das Resultat soll letztendlich die lesbarkeit erhöhen.

Mein Problem ist, dass ich nur diese Matrizen bekomme und diese als Plan nicht so gut geeignet sind wie eine Tabelle in der Form wie sie rechts zu finden ist.

Kommentar von Iamiam ,

ich bin nicht unfroh, dass Ninombre mir die Lösung abgenommen hat, denn ich war zwar knapp dran, aber es ergab sich dann doch immer wieder ein Fehler. Auch musste ich für meine Lösung den 2-Zeilen-Abstand aufgeben und wollte Dir doppelt hohe Zeilen mit Ausrichtung oben vorschlagen, ggf mit Zellkommentaren. Hat sich ja jetzt erledigt.

Keine passende Antwort gefunden?

Fragen Sie die Community