Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1492to1496
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
1492to1496
1492to1496
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

freie zeiten raussuchen umfangreiche formel

freie zeiten raussuchen umfangreiche formel
27.05.2016 20:45:49
Thomas
Hallo Excelfreunde,
ich versuche mich seid tagen an einem Formelkonstrukt. Aber ich schaffe es nicht.
Ich versuche die freien zeiten aus einem Bereich zu ermitteln und Sie so anzuordnen wie ich es im Beispiel dargestellt habe.
Es ist sehr schwer zu erklären deshalb habe ich versucht es in einem Beispiel darzustellen. In den Zeilen 1 bis 7 ist das ausgangsmaterial.
und in den zeilen 12 bis 24 steht das wunschergebnis. bin schon am verzweifeln und drauf und dran mein excelstatus runterzu stufen.
die anordnung vom ausgangsmaterial kann ich leider nicht verändern. Wäre nur mit einem extrblatt möglich. die lösung sollte zwar eine formel sein aber ich könnte auch ein VBA vorschlag händeln.
nun ja wenn es nicht richtig erklärt ist arbeite ich natürlich nach.
https://www.herber.de/bbs/user/105832.xlsx
ich bedanke mich schon mal im voraus für euer Interesse.
liebe grüsse thomas

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: freie zeiten raussuchen umfangreiche formel
27.05.2016 21:38:45
Michael
Hi Thomas,
ich frage mich, wo die Formelprofis bleiben...
Ich hab's mal mit VBA gemacht, das geht mir leichter von der Hand: https://www.herber.de/bbs/user/105834.xlsm
Was ich vermißt habe, war eine Angabe, ab wann die Zeiten beackert werden sollen. Ich verwende jetzt die Angaben in B9 und (neu) B8.
Viel Spaß,
Michael

AW: freie zeiten raussuchen umfangreiche formel
27.05.2016 22:31:13
Thomas
Hallo Michael,
ich bin total begeistert. Hab recht vielen dank.
Hab es leider vergessen zu schreiben von wo aus es gehen soll.
Die spaltennummer von b9 wäre der Anfang und das ende wäre dann 28 spalten rückwärts.
Als Beispiel steht in Zelle b9 eine 56 dann wäre der zeitraum zum abklappern von der 56 spalte bis zur 28 Spalte ( inclusive)
bin total begeistert.
Kannst du das noch einarbeiten?
und noch ein zwei kommentare reinschreiben? Das wäre total lieb von dir.
liebe grüsse thomas

Anzeige
sorry das hast du ja schon drinn
27.05.2016 22:38:47
Thomas
Hallo Michael,
ich bin ein dummie. Das hast du es ja schon erledigt.
Ich muss nur richtig lesen und die alten daten löschen man man sorry meine schuld.
Aber kannst du noch kurz dahinterschreiben was das macro macht? Nur kleine bemerkungen würden reichen.
Hab vielen dank schon mal und noch mal sorry das ich das nicht gleich erkannt habe.
liebe grüsse thomas

ok, kein Problem
28.05.2016 16:53:12
Michael
Hi Thomas,
mit dem guten Aggregat muß ich mich auch mal beschäftigen - habe erst seit Kurzem ein neueres Excel.
Ich habe das Makro mal kurz kommentiert: Option Explicit Sub machen() Dim von&, bis&, i&, k&, p& Dim a As Variant, aus() As Variant von = Range("B8") bis = Range("B9") 'lädt den Bereich ab Zeile2/von-Spalte in das Array a, 'und zwar 5 Zeilen und die gewünschte Anzahl von Spalten a = Cells(2, von).Resize(5, bis - von + 1) 'das beginnt wiederum bei a(1,1), so daß die Breite des 'Arrays noch einmal ermittelt wird - schreibt sich einfacher bis = UBound(a, 2) 'Das Array wird spaltenweise von links nach rechts durchlaufen, 'und WENN die 5. Zeile nicht leer ist, wird der Wert wieder 'ins Array "zurückgeschrieben", und zwar an die Position (Spalte) p p = 0 For i = 1 To bis If a(4, i) "" Then p = p + 1 For k = 1 To 5: a(k, p) = a(k, i): Next End If Next 'so daß die "leeren" Spalten heraussortiert sind bzw. durch die 'mit Werten ersetzt wurden. 'Interessant sind also in a nur noch die Spalten 1 bis p, der 'Rest wird nicht weiter beachtet 'Für die Ausgabe wird das Array aus angelegt, mit p Zeilen und '6 Spalten (weil Deine Beispielausgabe 6 Spalten hat!) ReDim aus(1 To p, 1 To 6) 'Mit dieser Schleife wird das aus-Array mit Werten gefüllt, For i = 1 To p - 1 ' und zwar vom gleichen Tag aus(i, 1) = a(1, i) aus(i, 2) = a(5, i) ' bzw. nächsten Tag aus(i, 4) = a(1, i + 1) aus(i, 6) = a(4, i + 1) Next 'und schließlich in die Tabelle an Zelle "L14" geschrieben Range("L14").Resize(p, 6) = aus 'und anschließend sortiert, weil ich das noch nicht in der Schleife 'erledigt hatte. Range("L14").Resize(p, 6).Sort key1:=Range("L14"), order1:=xlDescending End Sub Schöne Grüße & happy exceling,
Michael

Anzeige
AW: so umfangreich sind/werden die aber nicht ...
28.05.2016 09:23:45
...
Hallo Thomas,
... (m)eine Lösungsformeln generieren sich aus jeweils einer Matrixfunktion(alität)sformel auf Basis von AGGREGAT() und einer einfachen WVERWEIS()-Formel. Anstelle Deines Spaltenbezugs hab ich zu dem (freien oberen/ max) Datum in B11 ein weiteres (freies unteres/min) Grenzdatum eingeführt.
Nachfolgende Formeln einfach weit genug nach unten ziehend kopieren:
 ABCDEFG
10max Datum:18.05.2016     
11min Datum:01.05.2016     
12  von Datum / Zeit bis Datum / Zeit
13       
14  17.05.201618:20 18.05.201604:40
15  14.05.201618:55 17.05.201605:40
16  13.05.201619:40 14.05.201608:50
17  12.05.201621:50 13.05.201612:15
18  11.05.201619:20 12.05.201613:40
19  10.05.201617:20 11.05.201605:40
20  09.05.201617:20 10.05.201605:55
21  06.05.201617:55 09.05.201605:55
22  04.05.201613:00 06.05.201607:40
23  03.05.201613:30 04.05.201606:00
24  01.05.201622:20 03.05.201604:35
25       

Formeln der Tabelle
ZelleFormel
C14=WENNFEHLER(AGGREGAT(14;6;$2:$2/($2:$2<B$10)/($5:$5>0)/($2:$2>=B$11); ZEILE(A1)); "")
D14=WENN(C14="";"";WVERWEIS(C14;$2:$6;5;))
F14=WENNFEHLER(AGGREGAT(14;6;$2:$2/($2:$2<=B$10)/($5:$5>0)/($2:$2>B$11); ZEILE(A1)); "")
G14=WENN(F14="";"";WVERWEIS(F14;$2:$5;4;))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: so umfangreich sind/werden die aber nicht ...
28.05.2016 09:41:33
Thomas
Hallo Werner,
hab auch du vielen vielen dank für die formellösung.
es funktioniert auch super.
Da bin ich wirklich richtig begeistert nun habe ich gleich zwei lösungen cool.
Ich freu mich so riesig weil ich es eine Woche lang versucht habe.
habt nochmal recht vielen vielen dank
liebe grüsse thomas

kleine Nachfrage
28.05.2016 10:14:55
Thomas
Hallo Werner,
noch eine kleine Nachfrage,
in der Aggregatformel =WENNFEHLER(AGGREGAT(14;6;$2:$2/($2:$2<B$10)/($5:$5>0)/($2:$2>=B$11); ZEILE(A1)); "")bestimmt man ja mit 2:2, 5:5, zeile(a1) die betroffenen zeilen.
Wie müsste man dies anpassen wenn man die daten nicht zeilenweise angeordnet hat sondern Spaltenweise. Ich habe da noch so ein ähnlichen fall den ich zu kompliziert aufgebaut habe den würde ich mit deiner variante gern verbessern wollen.
https://www.herber.de/bbs/user/105835.xlsx
Aus den beiden WVerweisen werden dann ja sverweise soviel bekomme ich hin aber die Aggregatformel
ist unwahrscheinlich interessant.
hab vielen dank schon mal für deine hilfe.
liebe grüsse thomas

Anzeige
konnte es selbst rausfinden
28.05.2016 12:14:35
Thomas
Hallo Werner,
ich konnte es selbst herausfinden.
Die Formel ist richtig gut. Damit werde ich mich noch ne weile beschäftigen.
besten dank dafür.
liebe grüsse thomas

AW: freut mich; ist ja auch fast "rum wie num" owT
28.05.2016 15:23:39
...
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige