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).
| A | B | C | D | E | F | G | H | I | J | K | L |
| 1 | | | | | | | | | | | | |
| 2 | | | | | | | | | | | | |
| 3 | | | | | | | | | | | | |
| 4 | Stand: | 07.01.2024 | | | | | | | | | | |
| 5 | | | Nr. | Vorname | Name | Eintritt 1 | Austritt 1 | Eintritt 2 | Austritt 2 | Eintritt 3 | Austritt 3 | Tage |
| 6 | | | 1 | Anton | Mustermann | 14.01.1979 | | | | | | 16.429 |
| 7 | | | 2 | Berta | Musterfrau | 14.01.1979 | 31.12.1984 | | | | | 2.178 |
| 8 | | | 4 | Cäsar | Testmann | 01.01.1985 | 30.06.1986 | 01.01.1987 | | | | 14.065 |
| 9 | | | 5 | Dora | Testfrau | 27.01.1979 | 01.05.1982 | 16.07.1983 | 30.05.1984 | | | 1.509 |
| 10 | | | 6 | Emil | Probename | 25.06.1982 | 31.12.1991 | 01.01.1998 | 30.06.2003 | 01.01.2020 | | 6.949 |
| 11 | | | 7 | Friedrich | Nocheiner | 05.11.1997 | 30.06.2001 | 01.01.2015 | 31.12.2016 | 30.06.2019 | 31.12.2022 | 3.343 |
| Zelle | Formel |
| 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.
Hallo,
ich würde das so lösen:
Tabelle1 | A | B | C | D | E | F | G | H | I | J | K | L | M |
| 3 | | | | | | | | | | | | | |
| 4 | Stand: | 07.01.2024 | | | | | | | | | | | |
| 5 | | | Nr. | Vorname | Name | Eintritt | Austritt | Tage | | | Vorname | Name | Tage |
| 6 | | | 1 | Anton | Mustermann | 14.01.1979 | 07.01.2024 | 16429 | | | Anton | Mustermann | 16429 |
| 7 | | | 2 | Berta | Musterfrau | 14.01.1979 | 31.12.1984 | 2178 | | | Berta | Musterfrau | 2178 |
| 8 | | | 4 | Cäsar | Testmann | 01.01.1985 | 30.06.1986 | 545 | | | Cäsar | Testmann | 14065 |
| 9 | | | 5 | Cäsar | Testmann | 01.01.1987 | 07.01.2024 | 13520 | | | Dora | Testfrau | 1509 |
| 10 | | | 5 | Dora | Testfrau | 27.01.1979 | 01.05.1982 | 1190 | | | Emil | Probename | 6949 |
| 11 | | | 6 | Dora | Testfrau | 16.07.1983 | 30.05.1984 | 319 | | | Friedrich | Nocheiner | 3343 |
| 12 | | | 6 | Emil | Probename | 25.06.1982 | 31.12.1991 | 3476 | | | | | |
| 13 | | | 7 | Emil | Probename | 01.01.1998 | 30.06.2003 | 2006 | | | | | |
| 14 | | | 8 | Emil | Probename | 01.01.2020 | 07.01.2024 | 1467 | | | | | |
| 15 | | | 7 | Friedrich | Nocheiner | 05.11.1997 | 30.06.2001 | 1333 | | | | | |
| 16 | | | 8 | Friedrich | Nocheiner | 01.01.2015 | 31.12.2016 | 730 | | | | | |
| 17 | | | 9 | Friedrich | Nocheiner | 30.06.2019 | 31.12.2022 | 1280 | | | | | |
| Formeln der Tabelle |
| Zelle | Formel | | 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
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.
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..
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
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!
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.
Rein normativ hat K-D recht.
Hallo LuckyJoe,
hier mal eine Power Query Lösung mit eingebauter M-Funktion.
Gruß von Luschi
aus klein-Paris
@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.