Okay, zum Kontext: Dieser Link hat mir beim Einstieg geholfen, aber jetzt stecke ich fest, wenn ich versuche, ihn auf meinen Anwendungsfall anzuwenden: Excel - Summieren Sie Werte basierend auf einer Spalte, die mit einer anderen Spalte in einer anderen Tabelle übereinstimmen.
Ich habe ein Stundenprotokoll, in das ich meine Arbeit eintrage, einschließlich Datum, Projekt- und Aufgabeninformationen, für die sie in Rechnung gestellt wird, sowie die Anfangs- und Endzeit der geleisteten Arbeitsstunden. Dadurch wird berechnet, wie viele Stunden ich ab der eingegebenen Zeit gearbeitet habe (auf die nächsten 15 Minuten gerundet).
So sieht das Protokoll („timeLog“) mit Beispieldaten (ungefähr) aus:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Datum | Projekt und Aufgabe | Startzeit | Endzeit | Std |
| 2 | 1.1.2023 | Projekt 1:Aufgabe 1 | 8:00 UHR MORGENS | 10:00 UHR | 2 |
| 3 | 1.1.2023 | Projekt 2:Aufgabe 1 | 10:00 UHR | 16:00 Uhr | 6 |
| 4 | 06.01.2023 | Öffnungszeiten an Feiertagen | 8:00 UHR MORGENS | 16:00 Uhr | 8 |
| 5 | 07.01.2023 | Kranke Zeit | 8:00 UHR MORGENS | 12.00 | 4 |
| 6 | 06.02.2023 | Projekt 1:Aufgabe 1 | 8:00 UHR MORGENS | 16:00 Uhr | 8 |
[Transkription ausdieses Bild.]
Auf einem anderen Blatt habe ich eine Tabelle, die alle Projekte und zugehörigen Aufgaben auflistet (Spalte „Projekt- und Aufgaben-ID“). Jedem Projekt ist ein Typ zugeordnet (nämlich gearbeitete Stunden, Feiertage, Krankheit oder Urlaub).
So sieht die Projektinfotabelle („projectTable“) aus:
| A | B | |
|---|---|---|
| 1 | Projekt- und Aufgaben-ID | Typkennung |
| 2 | Projekt 1:Aufgabe 1 | Hat funktioniert |
| 3 | Projekt 2:Aufgabe 1 | Hat funktioniert |
| 4 | Öffnungszeiten an Feiertagen | Urlaub |
| 5 | Kranke Zeit | Krank |
[Transkription ausdieses Bild.]
Um zu berechnen, wie viel Krankheits-/Urlaubsanspruch ich habe, muss ich angeben, wie viele Stunden ich in einem bestimmten Abrechnungszeitraum gearbeitet habe. Allerdings muss ich auch die Gesamtstundenzahl angeben, die ich einreiche, einschließlich Krankheits-/Urlaubs-/Feiertage, damit sie im Stundenprotokoll bleiben, da ich diese für die Berechnung der gesamten Vollzeitäquivalente verwende.
Folgendes verwende ich derzeit, um alle Stunden in einem Abrechnungszeitraum (26.12.22-25.01.23) zu summieren, die einem Projekt mit der Typ-ID „gearbeitet“ zugeordnet sind:
=SUM(IFERROR(IF(COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")>0,timeLog[Hours],0),0))
Zuerst verwende ich FILTER, um alle Tabelleneinträge für den angegebenen Zeitraum abzurufen.
FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0)
Anschließend prüft COUNTIFS, ob es Projekte mit der TypeID „Bearbeitet“ gibt:
COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")
Dann gibt IF den Stundenwert für jede dieser Zeilen zurück, IFERROR „behebt“ einen Fehler, der immer wieder auftrat, und SUM berechnet die Gesamtzahl der „gearbeiteten“ Stunden für den Zeitraum.
Das scheint mir jedoch nicht die beste Vorgehensweise zu sein – es ist instabil, gibt unerwartete Fehler zurück und insgesamt habe ich das Gefühl, dass ich es falsch angehe. Gibt es eine bessere Vorgehensweise?
Antwort1
Ich habe Ihre Lösung nicht untersucht, aber auf den ersten Blick stimme ich zu, dass sie unnötig kompliziert erscheint. Ich schlage vor, dass Sie Ihrem Stundenprotokoll eine sechste Spalte hinzufügen, die enthält
=VLOOKUP(B2, projectTable!$A$2:$B$5, 2, FALSE)
Dadurch werden „Projekt und Aufgabe“ aus dem Stundenprotokoll wie folgt in die „Typ-ID“ aus der Projektinfotabelle übersetzt:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Datum | Projekt und Aufgabe | Startzeit | Endzeit | Std | Typkennung |
| 2 | 1.1.2023 | Projekt 1:Aufgabe 1 | 8:00 UHR MORGENS | 10:00 UHR | 2 | Hat funktioniert |
| 3 | 1.1.2023 | Projekt 2:Aufgabe 1 | 10:00 UHR | 16:00 Uhr | 6 | Hat funktioniert |
| 4 | 06.01.2023 | Öffnungszeiten an Feiertagen | 8:00 UHR MORGENS | 16:00 Uhr | 8 | Urlaub |
| 5 | 07.01.2023 | Kranke Zeit | 8:00 UHR MORGENS | 12.00 | 4 | Krank |
| 6 | 06.02.2023 | Projekt 1:Aufgabe 1 | 8:00 UHR MORGENS | 16:00 Uhr | 8 | Hat funktioniert |
Dann müssen Sie nur noch COUNTIFSund verwenden SUMIFS. Geben Sie beispielsweise irgendwo anders (z. B. A8:B10) die Typen in A8:A10 ein, geben Sie
=SUMIFS($E$2:$E$6, $A$2:$A$6,">"&DATE(2022,12,25), $A$2:$A$6,"<"&DATE(2023,1,23), $F$2:$F$6,A8)
in B8 und nach unten ziehen/füllen:
| A | B | ||
|---|---|---|---|
| 8 | Hat funktioniert | 8 | |
| 9 | Urlaub | 8 | |
| 10 | Krank | 4 |
Natürlich können die fest codierten Daten durch Zellbezüge ersetzt werden.
Beachten Sie, dass ich eine vage ähnliche, aber komplexere Antwort gegeben habe.Frage vor acht Jahren.