Aus Matrix nachfolgenden Wert vom Gesuchten finden

[flix92]

Hallo!

Erst einmal Danke, dass es so ein tolles Forum gibt. Vorweg: So richtig wüsste ich nicht einmal, nach was ich suchen soll. Wenn meine Frage also schon einmal beantwortet wurde, wäre ich über einen kurzen Hinweis sehr dankbar und darf dann auch gesteinigt werden!

Folgende Struktur:

   

Ich habe also fünf Schichten, die jeweils 4 Zeilen umfassen, um 1. den Namen 2. die Von- 3. die Bis-Zeiten und 4. die Netto-Stunden anzeigen. Jede Spalte stellt einen Tag da. 

Ziel:

Ich möchte nun für jede Person die Schichten untereinander ausgeben lassen.
   

Problem:
Die Suche muss zwar waagerecht den Monat abtasten, aber auch innerhalb eines Tages prüfen, wie oft ein Wert vorkommt. Außerdem soll dann ja nicht der gesuchte Wert (Name) sondern z.B. der darauffolgende (bspw. Schicht-Start, -Ende, ...) angezeigt werden. Theoretisch muss also im Zick-Zack geprüft werden.


Stand:
Mit folgender Formel, schaffe ich es immerhin, mir das Datum korrekt einzutragen. Kommt ein Wert mehrmals pro Tag vor, wird auch der Tag mehrmals aufgeführt. Kommt der Wert an einem Tag nicht vor, wird das Datum auch nicht angezeigt und sofort das nächste relevante Datum angezeigt. 

Code:

=INDEX(INDIREKT($C$4&"!$3:$3";WAHR);1;AGGREGAT(15;6;SPALTE(INDIREKT($C$4&"!$19:$38";WAHR))/(FINDEN($E$4;INDIREKT($C$4&"!$19:$38";WAHR);1)>0);ZEILE()-6))

Abgesehen davon, dass ich den Code nicht einmal 100% verstehe, komme ich nun nicht mehr weiter. Ich müsste ja nun in der nächsten Spalte den dazugehörigen "Von"-Wert finden. Wie sage ich Excel aber, in welcher Spalte und Zeile er diese zu finden hat?

Ich hoffe Ihr versteht, was ich meine, ansonsten gerne nachfragen.

Ich bedanke mich schon jetzt, für jeden hilfreichen Hinweis und Kritik!

[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]
[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

[neopa]

Hallo,

Deine dargestellten Bilddaten passen nicht zu der von Dir abgebildeten Forme, den gemäß Ziel.png steht nichts in C4 so dass Dein Datenblatt nicht gefunden wird. Unabhängig davon sind Bilddaten meist und so auch hier ungeeignet eine Problemdarstellung aufzuzeigen.

Stell doch Deine Datei (zumindest mit den von Dir hier dargestellten Daten) als xlsx-Datei ein. Dann kann Dein Problem sicherlich schnell gelöst werden.

[flix92]

Danke für deine Antwort!

Anbei habe ich meine Datei angehängt (mal mit mal ohne Makros). Ziel.png stimmt deshalb nicht überein, weil ich damit nur zeigen wollte, wie es aussehen SOLL. Mein aktuelles Datenblatt sieht leicht anders aus, aufgrund von gescheiterten Versuchen (allerdings ja jetzt anhängig dabei).

Dort kann das unter "Stundenkonto" und "Juli" gefunden werden.

[neopa]

Hallo,

hab zunächst die Formel in A7 stark gekürzt und was wichtiger deren Auswertungsgeschwindigkeit beschleunigt. Dazu hab ich die Auswertungsbereiche in der Formel auf das max. notwendige begrenzt und auch vor das WENNFEHLER() noch das für die Ermittlung überflüssige WENN() gesetzt.

Code:

=WENN(A6="";"";WENNFEHLER(INDEX(INDIREKT(C$4&"!3:3");AGGREGAT(15;6;SPALTE(C1:AG1)/(E$4=INDIREKT(C$4&"!C19:AG38"));ZEILE()-6));""))

Die Formel kopiere weit genug nach unten kopieren.

Nachfolgende Formel in C7 bedarf Deiner Berechnung in Spalte  B nicht und ermittelt mit kürzerer Formel als bisher das von Dir gewünschte und zudem schneller. Die Formel kann zudem nicht nur nach unten sondern auch  in Spalte D kopiert werden.

Code:

=WENN($A7="";"";INDEX(INDIREKT($C$4&"!1:38");AGGREGAT(15;6;ZEILE(A$19:A$38)
/($E$4=INDIREKT($C$4&"!C19:AG38"))/(INDIREKT($C$4&"!C3:AG3")=$A7);
ZÄHLENWENN($A$7:$A7;$A7))+SPALTE(A1);
AGGREGAT(15;6;SPALTE($C1:$AG1)/($E$4=INDIREKT($C$4&"!C19:AG38"));ZEILE(A1))))

[flix92]

Hallo Werner,

der absolute Hammer! Nicht das es dich oder wen anders hier überraschen dürfte: Es funktioniert - so wie ich mir das vorgestellt habe. Werde dann versuchen nachzuvollziehen, was genau die Formel macht. Hab herzlichen Dank!!

[neopa]

Hallo Constantin,

zu Deiner per PN an mich gestellten Zusatzfragen, antworte ich Dir, wie bereits per PN  geschrieben zur Wahrung des Gesamtzusammenhanges sowie der einfacheren Nachvollziehbarkeit hier in Deinem Ursprungsthread.

Wie Du selbst festgestellt hast, ist zur Ermittlung des jeweiligen Urlaubstages die Klammerung der Formel mit WENNFEHLER() keine Hilfe. Man  muss den Urlaub in Spalte B:C schon in einer Teilfomel separat ermitteln.

Die ehemalige Formel C7, die durch Deine Löschung der Spalte B jetzt in B7 steht erweitert sich somit auf folgende Formel wie folgt:

Code:

=WENN($A7="";"";WENN(SUMME(INDEX((INDIREKT($B$4&"!C41:AG41")=$E$4)
*(INDIREKT($B$4&"!C3:AG3")=$A7);))=1;"Urlaub";INDEX(INDIREKT($B$4&"!1:41");
AGGREGAT(15;6;ZEILE(A$19:A$41)/($E$4=INDIREKT($B$4&"!C19:AG41"))
/(INDIREKT($B$4&"!C3:AG3")=$A7);ZÄHLENWENN($A$7:$A7;$A7))+SPALTE(A1);
AGGREGAT(15;6;SPALTE($C1:$AH1)/($E$4=INDIREKT($B$4&"!C19:AG41"));ZEILE(A1)))))

Diese Formel kannst Du wieder nach unten und in Spalte C nach rechts kopieren.

Die Formel in D7 musst Du somit ändern zu: =WENN(ODER(C7="";B7="";B7>"");"";C7-B7)

Die Berücksichtigung Deiner Zuschlagswerte in der der momentan vorliegenden Form, dürfte sich als problematisch erweisen.   Deshalb würde ich vorschlagen, dass Du diese direkt als 3. oder 4. Zeilenwert je Schicht ermittelst. Damit kann die Datenwertübernahme mit einer Formel analog B7 erfolgen. 

Sollten Deinerseits Rückfragen auftreten, so wisse, dass ich frühestens am Freitag wieder nach den thread schauen kann.

[flix92]

Hallo Werner,

herzlichen Dank, dass du dir dafür noch Zeit genommen hast!
Es klappt. Nachdem ich jetzt noch in den Monatsblättern einen Bereich für Krankheiten drin habe, hab ichs sogar geschafft, die Formel selber so anzupassen und "Krankheit" anzeigen zu lassen und in der darunter liegenden Zeile die entsprechende Stundenzahl abzurufen (bisschen Stolz   ):

Code:

INDEX(INDIREKT($B$4&"!1:59");
AGGREGAT(15;6;ZEILE(A$19:A$59)/($E$4=INDIREKT($B$4&"!C19:AG59"))
/(INDIREKT($B$4&"!C3:AG3")=$A13);ZÄHLENWENN($A$7:$A13;$A13))+SPALTE(A7);
AGGREGAT(15;6;SPALTE($C7:$AH7)/($E$4=INDIREKT($B$4&"!C19:AG59"));ZEILE(A7))))))

Und in der Zeile für die Stunden:

Code:

=WENN($A7="";"";WENN(SUMME(INDEX((INDIREKT($B$4&"!C49:AG59")=$E$4)
*(INDIREKT($B$4&"!C3:AG3")=$A7);))=1;RUNDEN(SVERWEIS($E$4;Einstellungen!$F$2:$H$13;3)/30,44;2);WENN(SUMME(INDEX((INDIREKT($B$4&"!C41:AG46")=$E$4)*(INDIREKT($B$4&"!C3:AG3")=$A7);))=1;INDEX(INDIREKT($B$4&"!1:59");AGGREGAT(15;6;ZEILE(C$19:C$59)/(E$4=INDIREKT($B$4&"!C19:AG59"))/(INDIREKT($B$4&"!C3:AG3")=$A7);ZÄHLENWENN($A$7:$A7;$A7))+SPALTE(A1);AGGREGAT(15;6;SPALTE($C1:$AH1)/($E$4=INDIREKT($B$4&"!C19:AG59"));ZEILE(A1)));(INDEX(INDIREKT($B$4&"!1:59");
AGGREGAT(15;6;ZEILE(C$19:C$59)/($E$4=INDIREKT($B$4&"!C19:AG59"))
/(INDIREKT($B$4&"!C3:AG3")=$A7);ZÄHLENWENN($A$7:$A7;$A7))+SPALTE(C1);
AGGREGAT(15;6;SPALTE($C1:$AH1)/($E$4=INDIREKT($B$4&"!C19:AG59"));ZEILE(C1)))))))

Den Rat mit den anderen Werten direkt unter die Schichten zu schreiben, werde ich beherzigen. Damit es nicht zu unübersichtlich wird, kann ich sie ja via Makro ausblenden.

Nochmal Danke!!

[flix92]

Da ich meinen eigenen Beitrag nicht editieren kann. Für die die es interessiert und natürlich für Werner   anbei meine aktuelle Lösung als Datei ohne Makros.

[neopa]

Hallo Constantin,

freut mich für Dich, dass Du nunmehr eine Lösung hast, die Deiner Zielstellung entspricht.

Bei meiner oberflächliche Sichtung Deiner aktuellsten Datei ist mir nur folgendes noch aufgefallen:

• Für "Urlaub" hast Du in den Monatsdatenblättern mE viel zu viele Eingabezeilen vorgesehen, die dann ja auch unnötigerweise ausgewertet werden müssen, obwohl ja die meisten leer bleiben werden.
  
• Deine bedingten Formatierungen haben sich durch wahrscheinlich nachträgliches Kopieren von Zeilen unnötige Vervielfachungen von gleichen Bedingungen ergeben (ein Excelproblem seit der Version 2007) die Du wieder zusammenfassen könntest und andere Bedingungen (z.B. Kennzeichnung des Sonntags) die möglicherweise nicht mehr gebraucht werden.
  
• Die bedingte Formatierungsformel zur Kennzeichnung falscher Namenszuweisung in Zeile 19, 23 ... , würde ich z.b wie folgt zusammenfassen: =(ZÄHLENWENN(C$5:C$18;C19)=0)*(C19>0)
  
• In C5:AG18 wäre eine ähnliche bedingte Formatierung zu empfehlen die da mit $A$5:$A18 abgleicht bzw. Du verwendest dort Dropdownzellen mit Bezug auf die gelisteten Personen in A5:A18