Ich habe herumgesucht und kann einfach nicht herausfinden, warum meine Formeln nicht funktionieren.
Bevor ich meine Formeln poste, muss ich erklären, was ich versuche zu tun, und es ist ein bisschen kompliziert (zumindest ist das die einzige Möglichkeit, die mir einfällt).
**Hintergrund**
Ich muss eine Kalkulationstabelle für das Budget meiner Abteilung erstellen (nach Kategorien sortiert). Wenn ich derzeit die Einnahmen/Ausgaben aus unserem Finanzsystem herunterlade, werden mir nur Datenzeilen (ROHDATEN) ausgegeben, sortiert nach Datum.
Ziel 1- Lassen Sie die exportierten Rohdaten automatisch in Kategorien sortieren, sodass in einer Tabelle klar ersichtlich ist, wie viel in welcher Kategorie ausgegeben wurdeERREICHT
Ziel 2- Lassen Sie die exportierten ROHDATEN in Kategorien sortieren und zeigen Sie NUR bestimmte Monate basierend auf der Auswahl des Benutzers anGESTECKT
Beispiel für Rohdatenexport:
TABELLE: Daten2015
Date | Description of Expense | Amount | Codes
Jan-15 | Workshop for employee | 100000 | WRKS
Jan-15 | Workshop for employee | 100000 | WRKS
Feb-15 | Sponsors for employee | 200000 | SPON
Feb-15 | Sponsors for employee | 100000 | SPON
Feb-15 | Workshop for employee | 300000 | WRKS
Mar-15 | Sponsors for employee | 100000 | SPON
Wenn wir die obige Tabelle als Beispiel für die Rohdaten (mit dem Namen „Data2015“) verwenden, die ich beim Export aus unseren Finanzsystemen erhalte (die ich in Excel in eine Tabelle konvertiert habe), habe
ich zur Erreichung von Ziel 1 eine neue Tabelle (mit dem Namen „Kategorien“) mit den Beschreibungen der Kategorien erstellt.
TABELLE: Kategorien
Categories | Codes
Workshop | WRKS
Sponsors | SPON
Anschließend wurde eine weitere Tabelle (mit dem Namen „Budget“) wie unten erstellt:
TABELLE: Haushalt
Categories | Codes | Amount
Workshop | WRKS | 500000
Sponsors | SPON | 400000
Die Formel, die ich für die Spalte „Betrag“ in der Tabelle „Budget“ verwendet habe, lautet
=SUMIF(Data2015[Codes],[@Codes],Data2015[Amount])
Diese Formel hilft mir also, Ziel 1 zu erreichen.
Bei meinem Versuch, Ziel 2 zu erreichen, habe ich Folgendes versucht.
Es wurde eine Tabelle erstellt, um die Monate zu erfassen, die der Benutzer sehen möchte:
TABELLE: Bedingungen
Month | X | Background
Jan-15 | x | 1/01/2015
Feb-15 | x | 1/02/2015
Mar-15 | x | 1/03/2015
Der Benutzer setzt ein „x“ in die Spalte X, wenn er die Ausgaben für den jeweiligen Monat sehen möchte.
Ich habe diese Formel in der Spalte Hintergrund verwendet:
=IF([@X]="x",TEXT([@Month], "d/mm/yyyy"),"")
Ich möchte die Monate dann wie zuvor weiter in Kategorien filtern.
Ähnlich wie die obige Kategorientabelle, aber für bestimmte Monate, je nachdem, was der Benutzer sehen möchte.
Ich habe es irgendwie zum Laufen gebracht, aber nur für eine Zeile, nicht für die ganze Tabelle.
Ich möchte, dass Sie meine Formel überprüfen und mir mitteilen, was ich möglicherweise falsch mache oder dass vielleicht sogar meine Logik zum Erreichen von Ziel 2 falsch ist. Ihre Einsicht wird sehr hilfreich sein und ich entschuldige mich für die sehr verwirrende Situation, mir fällt keine einfachere Erklärung ein.
Ich habe eine weitere Tabelle erstellt, die das Data2015-Blatt mit der folgenden Formel nachahmt
=IF(ISNUMBER(SEARCH(Month_View!$D$5, $A5)),Data2015[@Account], IF(ISNUMBER(SEARCH(Month_View!$D$6,$A5)),Data2015[@Account],""))
Month_View! ist woTABELLE: Bedingungenbefindet und $D$5 ist Jan-15, $D$6 ist Feb-15 und so weiter.
Diese Formel füllt die Tabelle im Wesentlichen aus, wenn sie erkennt, dass A5 (Spalte, in der das Datum steht) mit demTABELLE: Daten2015's Datumsspalte (in der gleichen Zeile, daher das @).
Bis hierhin ist alles gut, es funktioniert, solange ich in Zelle A5 „Jan-15“ oder „Feb-15“ habe. Um diese Zelle basierend auf den Benutzereinstellungen zu füllen, habe ich die folgende Formel verwendet (die nicht das gewünschte Ergebnis liefert):
=IF(Condition[Background]=TEXT(Data2015[@Date],"d/mm/yyyy",Data2015[@Date],"")
Für mich soll diese Formel also Folgendes bewirken:
WENN das Datum in derDaten2015Tabelle entspricht einem der Daten/Werte (im Format "t/mm/jjjj") inZustand's Spalte mit dem Namen "Hintergrund" (die nur angezeigt wird, wenn der Benutzer ein "x" in den Monat eingibt, den er sehen möchte) und füllen Sie diese Zelle dann mit dem Wert ausDaten2015's Datumsspalte.
Wenn die Zelle dann mit „Jan-15“ oder „1/01/2015“ gefüllt wird, wird der Rest der Tabelle gefüllt, was ich dann verwenden kann, um mit der Formel, die ich verwendet habe, um Ziel 1 zu erreichen, weiter nach Kategorie zu filtern.
Aber die Zelle wird nicht mit "Jan-15" oder "1/01/2015" gefüllt, sondern zeigt leer oder #VALUE!
Es scheint sich auf dieZustandTabelle auch mit @, was ich nicht will.
So verwirrend es auch ist ... Wenn Sie verstehen, was ich erreichen möchte, hilft Ihnen jede Einsicht oder Diskussion. Vielleicht ist mein Gehirn in diesem Stadium einfach überlastet.
Antwort1
Ich würde die in Excel integrierte Tabellenfunktion verwenden. Formeln sind nicht erforderlich. Wählen Sie Ihren Bereich aus und drücken Sie Strg+T, um zu beginnen.
Dadurch werden Filter in der obersten Zeile aktiviert – Sie/Ihre Benutzer können sie verwenden, um Daten nach Jahren und/oder Monaten zu filtern.
Für die Summierungsfunktion fügen Sie eine Summenzeile hinzu (mithilfe des Menübands Tabellentools/Entwurf). In dieser Zeile können Sie auswählen, welche Aggregation Sie für jede Spalte wünschen (Summe, Anzahl usw.). Dies wird automatisch entsprechend Ihrer Filterung angepasst.
Antwort2
Okay, ich habe es gelöst!
Hier ist die Formel, die ich verwendet habe:
=SUM(SUMIF(Data2015BG1[Category],[@Codes],Data2015GB1[Amount]))
*PS: Entschuldigen Sie, wenn die Referenzen von den in meiner Frage angegebenen Beispielen abweichen.
Das Data2015BG1-Blatt enthält Formeln, die erkennen, ob die Trigger-Spalte ein „x“ enthält oder nicht, und die erforderlichen Daten basierend auf dem ausgewählten Monat präsentieren. Die von mir verwendete Formel lautet:
=IF(ISNUMBER(MATCH(TEXT(Data2015[@Period],"d/mm/yyyy"),Condition2015A[Background],0))=TRUE,Data2015[@Period],"")
Ich hoffe, das ergibt Sinn. Ich hätte Bilder gepostet, um Ihnen genau zu zeigen, was ich visuell erreicht habe, denn im Wesentlichen habe ich die Verwendung für jedermann einfacher gemacht, aber ich habe noch nicht genug Ansehen, um Bilder zu posten! Vielleicht nächstes Mal.
Filter hätten funktioniert, aber ich erstelle dies für Leute, die nicht viel Erfahrung mit Excel haben, also ist es schon ein bisschen zu viel verlangt, sie zu bitten, zu filtern (... ich weiß, ich weiß ...)