Wir wünschen allen Forenteilnehmern ein frohes Fest und einen guten Rutsch ins neue Jahr. x
Clever-Excel-Forum › Office-Anwendungen › Excel v
Tage summieren mit unterschiedlichen Zeiträumen

Baumstrukturmodus
Tage summieren mit unterschiedlichen Zeiträumen
LuckyJoe Offline
Excelanwender und VBA-Fan
Registriert seit: 26.07.2017
Version(en): 365
#1
07.01.2024, 14:07
Hallo zusammen,

ich verwalte eine Mitgliederliste, in der ich nachhalten möchte, welches Mitglied wieviel Tage dabei ist oder war. Dabei kann es vorgekommen, dass Mitglieder in der Vergangenheit mehrmals ein- bzw. ausgetreten sind. In einer Zelle ist notiert, zu welchem Datum die Berechnungen erfolgen sollen ("Stand").
Ich habe mittlerweile eine Formel zusammengebastelt, die das wohl auch ausrechnet, möchte aber die Formel-Profis fragen, ob es dafür nicht auch eine kürzere Formel gibt (die Formel aus L6 ist bis nach L11 heruntergezogen).

ABCDEFGHIJKL
1
2
3
4Stand:07.01.2024
5Nr.VornameNameEintritt 1Austritt 1Eintritt 2Austritt 2Eintritt 3Austritt 3Tage
61AntonMustermann14.01.197916.429
72BertaMusterfrau14.01.197931.12.19842.178
84CäsarTestmann01.01.198530.06.198601.01.198714.065
95DoraTestfrau27.01.197901.05.198216.07.198330.05.19841.509
106EmilProbename25.06.198231.12.199101.01.199830.06.200301.01.20206.949
117FriedrichNocheiner05.11.199730.06.200101.01.201531.12.201630.06.201931.12.20223.343

ZelleFormel
L6=WENN(G6="";TAGE($B$4;F6);TAGE(G6;F6))+WENN(H6="";0;WENN(I6="";TAGE($B$4;H6);TAGE(I6;H6)))+WENN(H6="";0;WENN(J6="";0;WENN(K6="";TAGE($B$4;J6);TAGE(K6;J6))))
Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 365
Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Danke für's Lesen.


Angehängte Dateien
.xlsx   Tage-Berechnung.xlsx (Größe: 16,39 KB / Downloads: 8)
Herzliche Grüße aus dem Rheinland
Jörg

[Windows 10, Microsoft 365]
Suchen
Antworten Top
Klaus-Dieter Offline
Excel und VBA
Registriert seit: 11.04.2014
Version(en): Office 365
#2
07.01.2024, 14:25
Hallo,

ich würde das so lösen:

Tabelle1

ABCDEFGHIJKLM
3
4Stand:07.01.2024
5Nr.VornameNameEintrittAustrittTageVornameNameTage
61AntonMustermann14.01.197907.01.202416429AntonMustermann16429
72BertaMusterfrau14.01.197931.12.19842178BertaMusterfrau2178
84CäsarTestmann01.01.198530.06.1986545CäsarTestmann14065
95CäsarTestmann01.01.198707.01.202413520DoraTestfrau1509
105DoraTestfrau27.01.197901.05.19821190EmilProbename6949
116DoraTestfrau16.07.198330.05.1984319FriedrichNocheiner3343
126EmilProbename25.06.198231.12.19913476
137EmilProbename01.01.199830.06.20032006
148EmilProbename01.01.202007.01.20241467
157FriedrichNocheiner05.11.199730.06.20011333
168FriedrichNocheiner01.01.201531.12.2016730
179FriedrichNocheiner30.06.201931.12.20221280
Formeln der Tabelle
ZelleFormel
G6=HEUTE()
H6=G6-F6
M6=SUMMENPRODUKT((D6:D17=K6)*(E6:E17=L6)*(H6:H17))
H7=G7-F7
M7=SUMMENPRODUKT((D7:D18=K7)*(E7:E18=L7)*(H7:H18))
H8=G8-F8
M8=SUMMENPRODUKT((D8:D19=K8)*(E8:E19=L8)*(H8:H19))
G9=HEUTE()
H9=G9-F9
M9=SUMMENPRODUKT((D9:D20=K9)*(E9:E20=L9)*(H9:H20))
H10=G10-F10
M10=SUMMENPRODUKT((D10:D21=K10)*(E10:E21=L10)*(H10:H21))
H11=G11-F11
M11=SUMMENPRODUKT((D11:D22=K11)*(E11:E22=L11)*(H11:H22))
H12=G12-F12
H13=G13-F13
G14=HEUTE()
H14=G14-F14
H15=G15-F15
H16=G16-F16
H17=G17-F17

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
[-] Folgende(r) 1 Nutzer sagt Danke an Klaus-Dieter für diesen Beitrag:
  • LuckyJoe
Homepage Suchen
Antworten Top
Peter Offline
Lernt immer noch dazu
Registriert seit: 12.04.2014
Version(en): Office 365
#3
07.01.2024, 14:42 (Dieser Beitrag wurde zuletzt bearbeitet: 07.01.2024, 14:43 von Peter.)
Hallo,

beim Tabellenaufbau von Klaus-Dieter (Spalte A-H), der m.E. deutlich mehr Sinn macht, könnte man auch einfach eine Pivottabelle verwenden um die Tage zu berechnen.
Gruß
Peter
[-] Folgende(r) 2 Nutzer sagen Danke an Peter für diesen Beitrag:
  • Klaus-Dieter, LuckyJoe
Suchen
Antworten Top
LCohen Offline
Benutzer
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
#4
07.01.2024, 20:10 (Dieser Beitrag wurde zuletzt bearbeitet: 07.01.2024, 20:13 von LCohen.)
bei Ausgangskonfiguration L6: =LET(x;SUMME((G6:K6-F6:J6)*ISTUNGERADE(SPALTE(G6:K6)));x+(x<0)*B$4) erlaubt ohne Formelverlängerung noch mehr Ein/Austr..
WIN/MSO schicken angeblich alle 5 Sekunden Deinen Screen heim zu Papa (recall-Klausel). 
[-] Folgende(r) 1 Nutzer sagt Danke an LCohen für diesen Beitrag:
  • LuckyJoe
Suchen
Antworten Top
Kuwer Offline
Excelanwender mit VBA
Registriert seit: 17.04.2014
Version(en): MS Office 365(32)
#5
08.01.2024, 00:31
Hallo,

ich hatte mich da auch mal versucht ohne LET.

=SUMME(F6:K6*(ISTGERADE(SPALTE(F6:K6))*-2+1))+(ISTUNGERADE(ANZAHL(F6:K6))*$B$4)

Mit LET dann so:

=LET(x;SUMME(F6:K6*(ISTGERADE(SPALTE(F6:K6))*-2+1));x+(x<0)*$B$4)

Gruß, Uwe
[-] Folgende(r) 2 Nutzer sagen Danke an Kuwer für diesen Beitrag:
  • LuckyJoe, LCohen
Suchen
Antworten Top
LuckyJoe Offline
Excelanwender und VBA-Fan
ThreadStarter

Registriert seit: 26.07.2017
Version(en): 365
#6
08.01.2024, 10:08
Hallo zusammen,

vielen Dank für eure Unterstützung.

@Klaus-Dieter, @Peter: Danke für die gute Lösung, allerdings geht mir damit etwas die Übersicht verloren.

@LCohen, @Kuwer: Ich hatte mir auch schon Gedanken um die LET-Funktion gemacht, bin dann aber mit den Formeln nicht weiter gekommen. Normalerweise würde ich das auch über VBA lösen, diesmal soll es aber eine makrofreie Mappe zum Weitergeben bleiben. Mit der LET-Funktion halte ich die Formel für übersichtlich und nachvollziehbar.

Danke nochmals an alle!
Herzliche Grüße aus dem Rheinland
Jörg

[Windows 10, Microsoft 365]
Suchen
Antworten Top
Klaus-Dieter Offline
Excel und VBA
Registriert seit: 11.04.2014
Version(en): Office 365
#7
08.01.2024, 10:39
Hallo Jörg,

Zitat: Danke für die gute Lösung, allerdings geht mir damit etwas die Übersicht verloren.

das kann man möglicherweise unterschiedlich bewerten, ich find die von mir vorgeschlagene Vorgehensweise übersichtlicher. Vor allem ist sie besser für alle Arten der Auswertung geeignet.
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
[-] Folgende(r) 1 Nutzer sagt Danke an Klaus-Dieter für diesen Beitrag:
  • LuckyJoe
Homepage Suchen
Antworten Top
LCohen Offline
Benutzer
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
#8
08.01.2024, 13:30
Rein normativ hat K-D recht.
WIN/MSO schicken angeblich alle 5 Sekunden Deinen Screen heim zu Papa (recall-Klausel). 
Suchen
Antworten Top
Luschi Offline
Benutzer
Registriert seit: 30.06.2023
Version(en): 2019
#9
08.01.2024, 16:10
Hallo LuckyJoe,

hier mal eine Power Query Lösung mit eingebauter M-Funktion.

Gruß von Luschi
aus klein-Paris


Angehängte Dateien
.xlsx   Tage-Berechnung.xlsx (Größe: 20,73 KB / Downloads: 3)
[-] Folgende(r) 1 Nutzer sagt Danke an Luschi für diesen Beitrag:
  • LuckyJoe
Suchen
Antworten Top
LuckyJoe Offline
Excelanwender und VBA-Fan
ThreadStarter

Registriert seit: 26.07.2017
Version(en): 365
#10
08.01.2024, 21:23 (Dieser Beitrag wurde zuletzt bearbeitet: 08.01.2024, 21:24 von LuckyJoe.)
@Klaus-Dieter: ja, grundsätzlich hast du natürlich Recht. In diesem Fall ging es aber um eine andere Darstellung der Tabelle, so dass ich mich für die andere Lösung entschieden habe.
Hallo Luschi,

Danke auch dir für deine interessante Lösung.
Herzliche Grüße aus dem Rheinland
Jörg

[Windows 10, Microsoft 365]
Suchen
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste