Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 07:54:11
nur den ersten Eintrag in spalte zählen
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1696to1700
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1696to1700
1696to1700
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Abwesendheitsdaten zu Zeitleiste umbauen

Abwesendheitsdaten zu Zeitleiste umbauen
06.06.2019 09:26:02
Georg
Hallo Excel Welt,
eigentlich eine einfache Themenstellung, hatte ich zumindest gedacht. Ich breche mir aber gerade die Finger daran und finde auch nichts:
Ich möchte eine Tabelle mit Abwesenheiten in einen Zeitstrahl übersetzen. Dazu habe ich eine Tabelle mit 4 Spalten: Name, Anfangstag, Endtag, Grund.
Das soll jetzt übersetzt werden in einen Zeitstrahl. Also pro Kollege eine Zeile mit je einer Spalte pro Tag in der (im entsprechenden Fall) der Grund der Abwesenheit (Urlaub, Schulung, etc.) steht. Siehe angehängte Datei, dort exemplarisch auf nur einen Monat reduziert.
Ich hab es mit Matrix, Pivot, etc probiert, bin aber gescheitert. Das kann doch nicht so schwer sein, oder? Aber ich hab ein Brett vor dem Kopf.
Habt ihr eine Idee?
Danke und Gruß Georg
https://www.herber.de/bbs/user/130239.xlsx

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Abwesendheitsdaten zu Zeitleiste umbauen
06.06.2019 09:29:55
SF
Hola,
=WENNFEHLER(INDEX($F$8:$F$13;AGGREGAT(15;6;ZEILE($A$8:$A$13)-7/(($C$8:$C$13=$H8)*($D$8:$D$13<=I$7) *($E$8:$E$13>=I$7));1));"")
Gruß,
steve1da
genial - und das in der Zeit
06.06.2019 09:36:28
Georg
Jetzt bin ich ja fertig mit der Welt... - das hätte ich NIE hinbekommen... danke!
Frage: wieso teile ich eine leere Spalte (A) in der Aggregatsfunktion durch die Multiplikation der Parameter?
AW: genial - und das in der Zeit
06.06.2019 09:39:24
SF
Hola,
durch ZEILE($A$8:$A$13)-7 wird der Formel mitgeteilt, wie groß der Bereich vom Index() ist. Von F8 bis F13 sind es 6 Zeilen, also schreibt man ZEILE($A$8:$A$13)-7. Alternativ geht auch: ZEILE($A$1:$A$6).
Gruß,
steve1da
Anzeige
AW: genial - und das in der Zeit
06.06.2019 10:00:07
Georg
wirklich cool - geht dann ja auch mit beliebig großen Tabellen, oder? Und doch nochmal die Frage: warum teilt man durch die Parameter?
AW: genial - und das in der Zeit
06.06.2019 10:07:37
ASC_254
naja, solche Formeln sind rechenintensiv, dh bei sehr großen Tabellen können längere Berechnungzeiten die Folge sein.
das Prinzip ist hier folgendes: du brauchst eine zutreffende Zeilennummer.
wenn die Bedingungen zutreffen, teilt man den Wert durch 1, dadurch bleibt er erhalten.
treffen die Bedingungen nicht zu, teilt man durch 0 : es entsteht der DIV/0-Fehler und dieser wird vom Aggregat ignoriert.
hierzu muss man wissen, dass in Berechnungen die Wahrheitswerte WAHR und FALSCH wie 1 und 0 gewertet werden.
Gruß Daniel
Anzeige
:-)
06.06.2019 10:36:52
Georg
Ah - der Groschen ist gerutscht - Danke Daniel!
AW: genial - und das in der Zeit
06.06.2019 10:02:35
ASC_254
Hi
du brauchst nur die Zeilennummer, also eine Zahl zwischen 8 und 13.
in welcher Spalte du die Zeilennummer ausliest, ist völlig egal.
du solltest aber wenn möglich einen Zellbereich verwenden, den du schon an anderer Stelle in der Formel verwendest, z.B. C7:C13.
Der Hintergrund ist, dass dann bei nachträglichen Änderungen wie dem Einfügen oder Löschen von Zeilen die Zellbereiche korrekt angepasst werden.
wenn du bspw in mit dieser Formel den Zellbereich C1:F6 löschst, um die Eingabetabelle nach oben zu verschieben, würde der Zellbezug A7:A13 nicht automatisch mit verschoben werden und du müsstest die Formeln manuell nochmal anpassen.
Gruß Daniel
Anzeige
AW: Abwesendheitsdaten zu Zeitleiste umbauen
06.06.2019 09:35:47
ASC_254
Hi
versuche mal diese Formel in I8:
=WENNFEHLER(LINKS(INDEX($F:$F;AGGREGAT(14;6;ZEILE($C$8:$C$23)/($H8=$C$8:$C$23)/(I$7>=$D$8:$D$23) /(J$7
Gruß Daniel
wow - Vielen Dank!!
06.06.2019 09:40:43
Georg
Klasse Daniel! Ganz ähnlich wie von Steve, aber auch hier muss ich erstmal noch auf der Logik der Funktion rumkauen.. :-))
AW: wow - Vielen Dank!!
06.06.2019 09:43:03
SF
Hola,
die Formel bringt aber falsche Ergebnisse. Danach hätte Kollege 1 am 4.Februar keine Schulung. Der Fehler liegt hier: (J$7<=$E$8:$E$23)
Gruß,
steve1da
AW: wow - Vielen Dank!!
06.06.2019 10:00:55
Georg
OK, vielen Dank!
AW: hierzu noch angemerkt ...
06.06.2019 14:24:01
neopa
Hallo Georg,
... mal abgesehen von Daniels - sehr wahrscheinlich nur versehentlichen - Schreibfehler in seinem Formelvorschlag, ist diese Formelkonstruktion gegenüber der von SF eingestellten vorzuziehen.
Ein entscheidenden Grund dafür hat Daniel auch schon in seinem Beitrag von 9:36 angeführt ("... bei nachträglichen Änderungen wie dem Einfügen oder Löschen von Zeilen...")
Darauf hatte ich in mehreren Beiträgen mit gleichen/ähnlichen Formeln schon direkt oder auch indirekt versucht SF aufmerksam zu machen. Für jemanden jedoch dem diese spez. Formelkonstruktion offensichtlich noch neu ist wie Dir, kann ich nur raten, sich gleich mit der günstigeren Variante vertraut zu machen.
Gruß Werner
.. , - ...
Anzeige
AW: hierzu noch angemerkt ...
06.06.2019 14:56:59
SF
Hola,
sorry Werner, ich scheine das immer zu überlesen haben! War keine Absicht und macht natürlich Sinn!
Gruß,
steve1da
AW: hierzu noch angemerkt ...
06.06.2019 15:48:43
Georg
Hallo Werner,
nur, dass ich es richtig verstehe:
wenn ich die ganze Spalte nutze ("$F:$F") und auf den bereits genutzten Bereich referenziere,
- spare ich mit die "-7" und
- kann Zellen oben drüber löschen, ohne Fehlerbug
Ist das so richtig?
Und wie war das mit dem Schreibfehler? Da meinst Du die 2x "/" statt der "*", oder?
Warum funzt es eigentlich mit 15 (KKLEINSTE) genauso, wie mit 14 (KGRöSSTE )?
Ich nähere mich LANGSAM und mit heißen Ohren dem Thema... Vielen Dank Euch allen für die Nachhilfe!!
Gruß Georg
Anzeige
AW: hierzu noch angemerkt ...
06.06.2019 16:21:43
Daniel
Hi
ja. für INDEX brauchst du als Zeilen- und Spaltenparameter nicht die absolute Zeilen- und Spaltennummern aus der Tabelle, sondern die Postitonsnummer innerhalb des angegebenen Zellbereichs im ersten Parameter.
nur wenn die linke obere Zelle des Zellbereichs in Zeile 1 oder Spalte A liegt, sind die Postitonsnummern gleich den Zeilen- und Spaltennummern in der Exceltabelle.
beginnt der Zellbereich für Index in einer anderen Zeile/Spalte als 1/A, musst du, wenn dir deine Funktion die Zeilen/Spaltennummer der Exceltabelle zurückgibt, entsprechend umrechnen.
zu deiner zweiten Frage:
es gibt ja zu jedem vorhandenen Eintrag immer nur ein eindeutiges Ergebnis, dh eine Zahl und ansonsten nur Fehlerwerte (die ignoriert werden).
Wenn du nur eine Zahl in der Auswertung hast, ist es egal ob KKleinste oder KGrößte.
die Wahl der Auswertefunktion wird erst dann relevant, wenn die Matrixberechnung mehr als einen Zahlenwert als Ergebnis haben kann, dann musst du dich entscheiden, ob du den kleinsten oder den größten Wert als Treffer haben willst.
Gruß Daniel
Anzeige
:- )=)
06.06.2019 16:27:20
Georg
klasse, danke Daniel!
AW: es geht nicht ums "sparen" ...
06.06.2019 16:30:14
neopa
Hallo Georg,
... sondern in der Formel ist -7 ein fixer Wert. Wenn Zeilen von Zeile 8 eingefügt oder gelöscht werden müsste die -7 entsprechend abgeändert werden, um auch dann noch korrekte Ergebnisse zu erhalten.
Den Schreibfehler in Daniels Formel hatte SF bereits bezeichnet. Es sollte nicht "J" sondern "I" lauten.
Die Bedingungsprüfungen mit jeweils einer Division zu koppeln halte ich für konsequenter/einheitlicher und erübrigt ein Klammerpaar, welches auch leicht vergessen oder unvollständig eingegeben werden könnte.
Zur letzten Frage. In Deinem Beiepiel mag das keine Rolle spielen, aber wie die "Namen der Unterfunktionen" von AGGREGAT() schon aussagen ist das bei vielen anderen Themen schon von entscheidender Bedeutung, ohne jetzt näher darauf einzugehen.
Gruß Werner
.. , - ...
Anzeige
AW: es geht nicht ums "sparen" ...
06.06.2019 16:36:19
Georg
… und wieder ein Stückchen mehr verstanden! Klasse und danke Werner!

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige