Fortgeschrittene (WENN) Funktion in EXCEL

[WarezBay]

Ich versuche meine Problem einfach und überschaubar zu schildern.

-----------------------------------------------------------------------------------------

In EXCEL habe ich 2 Tabellenblätter ("Tag" & "Monat")

In TAG werden alle finanziellen Ein- und Ausgänge eingetragen. Also Format dient folgendes:

Datum (dd.mm.yyyy) / Kategorie (z. B. KFZ, Lebensmittel, etc.) / Betrag

Beispiel:

01.01.2013 / KFZ / 45 €
15.01.2013 / Lebensmittel / 15 €

In MONAT werden die Eintragungen von TAG addiert und übersichtlich dargestellt.

Jänner / Februar / März / usw.
KFZ
Lebensmittel
usw.

Nun bräuchte ich in KFZ für Jänner alle Ausgaben von 01.01.2013 bis 31.01.2013 der Kategorie KFZ summiert.

Man bedenke: In Tabelle TAG sind alle Ein- und Ausgaben chronologisch geordnet (von 01.01.2013-31.12.2013) und somit auch die Kategorien vermischt.
Wenn bei einer Formell Datum & Kategorie in einer Zeile zutrifft darf diese NUR den Betrag dieser Zeile (bzw. Zelle) mit den nächsten richtigen Eintrag addieren.

Da ich nur WENN Funktionen mächtig bin weiß ich nicht welche Funktion hier am effektivsten wäre.

Danke für eure Antworten

 

[kugelfisch01]

Hier wäre eine =SUMMEWENN Funktion evtl. eine Lösung.

 

[Snooty]

Kannst du mit dem Summenprodukt machen:

=SUMMENPRODUKT((B:B="KFZ")*(A:A>=DATUM(2013;1;1)*(A:A<=DATUM(2013;1;31)*C:C)

 

[Shagrath]

Oder eine Pivottabelle!

 

[WarezBay]

@kugelfisch
Denke nicht.

Die Funktion darf zum einem nicht fragen welche Felder addiert werden sollen, da nur diese summiert werden dürfen welche auf das bzw. die Suchkriterien zutreffen UND die Funktion muss min. zwei Kriterien akzeptieren.

----


Alle weiteren werde ich mich am Abend ansehen, Danke

 

[Juro]

Ich würde das auch mit einer Pivottabelle lösen.
Ist denke ich das einfachste.

 

[WarezBay]

@Snooty
Ohne konkretes Beispiel fällt es mir schwer die Formel anzuwenden.
Habe hier als Beispiel eine EXCEL Vorlage hochgeladen. Wenn du so nett wärst und mir die Formel für eine Kategorie deiner Wahl anwenden und hier einfügen würdest, wäre ich dir sehr dankbar
(Angenommen Spalte A wäre für Datum)

Bzgl. Pivottabelle:
Kenne ich, aber ich möchte die Tabelle für meine Ergebnisse nicht jedesmal neu konfigurieren, sondern mehrere Tabellen pflegen, welche die Angaben immer aus einer Quelle (Tabelle "TAG") beziehen und in verschiedenen Grafiken und Auswertungen übersichtlich und schnell erkennbar darstellen.

 

[Snooty]

Bitte mal eine Tabelle - kein Bild - hochladen (als ZIP).

 

[WarezBay]

Hier bitte.

 

[Snooty]

Siehe hier: http://sdrv.ms/1iIf9hL

Fehler in meiner Formel oben waren die Spaltenangaben A:A/B:B/C:C; im Summenprodukt muss man definierte Bereiche angeben (A2:A.../B2:B...).

 

[WarezBay]

Genial

Vielen Dank nochmal. Bin überrascht wie schnell das ging

 

[WarezBay]

Hier hätte ich noch ein paar Fragen. Würde mich über Antworten sehr freuen.

Kann man die Färbung einer Zelle fixieren sodass durch runterkopieren der darüber liegenden Zellen nichts mehr verändert?

Spalten C bis F weisen von der Original EXCEL Vorlage bereits Formatierungen auf. So werden z. B., wenn A o. B gefiltert werden (wurden nachträglich eingefügt) diese nicht miteinbezogen. Woran liegt das, bzw. mit welcher Funktion entfernt man alle Formatierungen einer Zelle?

Die SUMMEPRODUKT Formel welche oben gepostet wurde will bei mir tabellenübergreifend nicht funktionieren. Ein Ansatz befindet sich in Tabellenblatt 2. Will es jemand probieren?


Danke für eure Antworten

 

[Snooty]

ann man die Färbung einer Zelle fixieren sodass durch runterkopieren der darüber liegenden Zellen nichts mehr verändert?

Versuch das Runterziehen mal per Rechtsklick. Beim Loslassen kannst du dann wählen "Ohne Formatierung ausfüllen".

Spalten C bis F weisen von der Original EXCEL Vorlage bereits Formatierungen auf. So werden z. B., wenn A o. B gefiltert werden (wurden nachträglich eingefügt) diese nicht miteinbezogen. Woran liegt das, bzw. mit welcher Funktion entfernt man alle Formatierungen einer Zelle?

Ich weiß nicht genau, was du meinst - ggf. musst du in denen Bedingten Formatierungen die Zellbezüge anpassen!? Bedingte Formatierungen löscht du auch da, wo du sie erstellst; normale Formatierungen kannst du unter Bearbeiten auf der Registerkarte Start.

​

Die SUMMEPRODUKT Formel welche oben gepostet wurde will bei mir tabellenübergreifend nicht funktionieren. Ein Ansatz befindet sich in Tabellenblatt 2. Will es jemand probieren?

Das Summenprodukt funktioniert hier nicht richtig, weil du in der Spaltenüberschrift einen Text hast - die Formel sucht aber nach einem Datum [MONAT(C$5)=MONAT('Täglicher Cashflow'!C7:C14)]

Entweder schreibst du hier einen festen Wert in die Formel (1 für Januar, 2 für Februar usw.; [1=MONAT('Täglicher Cashflow'!C7:C14)) oder du musst aus der Tabelle die Spaltenüberschrift löschen und dann ein echtes Datum reinschreiben (bspw. 1.1.2014 für Januar, 1.2.2014 für Februar) und mit Zellenformat MMM versehen, damit nur Jan, Feb, ... angezeigt wird; dann kannst du weiterhin in deiner Formel MONAT(...) schreiben.

 

[WarezBay]

Soweit alles nachvollziehbar und funktioniert auch.

Zu Frage zwei:
Im letzten Tabellenblatt ist es nicht möglich alle Zellen auf einmal mit der Filterfunktion zu belegen. Ich kann nur C bis F welche ursprünglich in der Excel-Vorlage enthalten waren auf einmal mit der Funktion belegen. A & B (welche ich beim bearbeiten hinzugefügt habe) müssen separat bearbeitet werden. Haben alle Zellen die Filterfunktion, markiere diese und filtere z. B. C, D oder E werden A und B nicht miteinbezogen (selbigen umgekehrt). Oder wenn ich z. B. in A absteigend sortiere wird Spalte B, aber nicht die folgenden Spalten miteinbezogen.

Aber in Tabellenblatt zwei scheint ein Problem auf. Ich kann zwar das Datum in Zahlenformat eintragen und in MMM formatieren, aber es wird nicht in diesen Format angezeigt sondern behält das nummerische Format bei.


Hier sind in beiden Tabellenblätter Formatierungen enthalten die ich nicht selbst festgelegt habe (Vorlage) und nun auch nicht weiß wie ich jene entfernen kann.

Hast du einen Rat?

 

[Snooty]

Wegen dem Datum: du musst wohl oder übel die Überschriften deaktivieren - sonst scheint das mit dem Format nicht zu funktionieren (ggf. geht es irgendwie, ich weiß es aber nicht):

​

Da aber einige Formeln auf die Überschriften zugreifen, müsstest du diese anschließen noch korrigieren.

Im letzten Tabellenblatt ist es nicht möglich alle Zellen auf einmal mit der Filterfunktion zu belegen. Ich kann nur C bis F welche ursprünglich in der Excel-Vorlage enthalten waren auf einmal mit der Funktion belegen.

Das Problem ist, dass die Spalten A-C keine eigenständige Tabelle sind, die Spalte rechts davon aber schon.

​

Entweder musst du alle Spalten in eine Tabelle packen oder die Tabellenformatierung entfernen.

​