Office: (Office 2013) Kontoauswertung mit MS Excel

Hallo zusammen,

ich möchte mir ein kleines Programm in Excel schreiben, habe jedoch ein Problem. Und zwar geht es um die Auswertung meiner Kontoausgaben.
Ich will die Ausgaben kategorisieren und dann weiter auswerten: (grafisch s. Numbrs .... etc. ).
Im Anhang sind zwei tabellen die exemplarisch sein sollen.
In Tab1 werden die Ausgaben aus ner csv importiert und in der grünen Spalte soll jeder Ausgabe eine Kategorie zugeordnet werden:
Die Kategorien sollen in Tab2 abgelegt sein. Diese Tabelle sollte auch stetig erweiterbar sein.

Ich habe bereits eine Formel dafür geschrieben, jedoch funktioniert sie nicht mit Zellen in denen noch weitere Worte außer der Ladenbezeichnungen selbst enthalten sind. Wie ändere ich die Formel, damit das funktioniert ?

G1 = INDEX(Tabelle2!B:B;VERGLEICH(D1;Tabelle2!A:A;0))

Zudem soll, wenn keine Kategorie gefunden werden kann, "Sonstige" als Kategorie ausgegeben werden. Dafür habe ich mir die Formel so erweitert:

G1 = WENN(ZÄHLENWENN(Tabelle2!A:A;D1);INDEX(Tabelle2!B:B;VERGLEICH(D1;Tabelle2!A:A;0) );"Sonstige")

Ich danke euch im vorraus.

gruß Thomas

:)

 

Hallo,

ich glaube über Formeln wird es nicht ganz einfach, aber vielleicht irgendwie möglich.
Kommt für dich eine Formel über VBA in Betracht? Das könnte ich deutlich leichter umsetzen *Smilie

In jedem Fall solltest du deine Beispieldaten als Excel-Datei hochladen und nicht als Bild. Niemand hat Lust das Bild abzuschreiben.

VG
Santa

 

Ich baue keine Datei nach, die Zeit hat schon jemand investiert.
Ein Nachbau sieht bestimmt anders aus als das Original.
Ein Link zur Datei oder ein Tabellen Ausschnitt nicht als Bild wäre nicht schlecht.

Jul 2014

	C
29	Falsch
30	496

verwendete Formeln
Zelle	Formel	Bereich	N/A
C29	=C27=C28
C30	=SUMMEWENN([Bildschirm.xlsm]Statistik!$Q:$Q;"<" &HEUTE();[Bildschirm.xlsm]Statistik!$R)

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.07 einschl. 64 Bit

Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)
Der Dateiname sollte was mit dem Problem zu tun haben.
Ich habe mir z.B. einen Ordner angelegt in dem ich alle Dateien aus dem Internet speichere. Bei Dateinamen wie Test..., Mappe…, Beispiel… wird eine vorhandene überschrieben.
Schaue hier, Dateiname im Beitrag
das ist nun das Word -Forum, das gilt hier aber auch.
Ein Bild in Excel geöffnet sieht anders aus als das Bild.
Ich habe kein Tool um ein Bild in Excel zu importieren.

 

Kein Problem,
anbei die excel datei.

Ich wäre nicht verschlossen gegenüber vba, solange ich es noch nachvollziehen kann; sprich überschaubar ist.

 

Hallo,

gut, dann folgende Idee:
Öffne den VBA-Editor (Alt+F11) und füge ein neues Modul ein (Einfügen > Modul)
Dort hinterlegst du folgenden Code:
Code:

Jetzt kannst du in der Tabelle eine neue Funktion aufrufen:
.mcs{margin-top:5pt;}.mcs table{border-collapse:collapse;}.mcs table td{border:1px solid #999999;color:#000000;background-color:#FFFFFF;font:11pt Calibri, sans-serif;padding:1pt 2pt;text-align:left;vertical-align:middle;}.mcs .foot td{border:0;font-size:9pt;color:#888888;padding:0;}.mcs .foot{clear: both;}.mcs .foot td{background-color:transparent;}.mcs .sheet td:first-child{background-color:#ddddee;text-align:center;}.mcs .sheet tr:first-child td{background-color:#ddddee;text-align:center;}.mcs .ffarial{font-family:arial;}.mcs .bcA9D18D{background-color:#A9D18D;}.mcs .fs8{font-size:8pt;}.mcs .c{text-align:center;}.mcs .formulas tr:first-child td{background-color:#eeaaaa;}.formats, .names, .formulas, .cond, .fonts, .valid{float: none;margin: 10pt 10pt 0 0;}

	D	E	F	G
1	ALDI SUED SAGT DANKE F006736509054 00000	-25,69	EUR	Ernährung
2	ALDI SUED SAGT DANKE F006736509054 00001	-24,69	EUR	Ernährung
3	der Lidl SUED SAGT DANKE F006736509054 00002	-23,69	EUR	Ernährung
4	Bei Deichmann gut einkaufen F006736509054 00003	-22,69	EUR	schuhe
5	SUED SAGT DANKE F006736509054 00004	-21,69	EUR	Sonstige

Zelle	Formel
G1:G5	=Kategorie_McS(D1;Tabelle2!$A:$B)

Tabelle by Tab2Html ©Gerd alias Bamberg

CSS by Mc Santa

Hilft dir das weiter und funktioniert es in deiner Datei?

Fragen und Feedback gerne
VG
Santa

 

Du hast viel unterschiedlich Begriffe in Spalte D. Warum wurden davon nur einige in Tabelle 2 aufgeführt?
Warum Sued oder Sagt oder Danke aus D5 nicht? Gibt es da eine Liste.
in Tabelle 2 sehe ich keine Zahlen.

 

\@Santa

also soweit ich damit gerade rumgespielt habe :P funktioniert es einwandfrei.
Also vielen lieben dank dafür.
Da ich bestimmt nochmal in eine längere Grübelphase kommen werde, speziell wenn ich die importfunktion der kontoeinträge aus der CSV Datei implementiere, hoffe ich eventuell nochmal auf dich/euch zurückkommen zu können.

eine kleinigkeit eventuell noch:

=Kategorie_McS(D1;Tabelle2!$A$1:$B$6)
Wenn ich Tabellenblatt2 noch erweitere wird, muss ja auch b6, in der formel $B$6, ersetzt werden durch b10 oder b20 wo auch immer die Tabelle endet,
lässt sich das noch automatisieren ?
Also, dass der "gültige bereich" neu erfasst wird, wenn ich was dazuschreibe in Tab2 und direkt in Tab1 "b6" ändert.

 

\@Hajo

Das Problem was ich habe/hatte, ist ja, dass meine "Leihenformel" die Spalte D nur dann richtig auswertet, wenn nur "Lidl" "Aldi" "Shell" oder was auch immer enthalten ist. Santa hat mir soweit mit dem kleinen VBA script geholfen, dass trotz des "aldi dankt herzlich..." trotzdem das keyword "aldi" gefunden wird und somit mit den Kategorien aus Tabellenblatt2 abgeglichen werden kann.

 

Hallo,

am besten geht das über sogannte "Listen" (oder auch "Tabellen", aber der Name ist missverständlich).

Gib zunächst deiner Tabelle ein paar Überschriften, damit sie so aussieht:
.mcs{margin-top:5pt;}.mcs table{border-collapse:collapse;}.mcs table td{border:1px solid #999999;color:#000000;background-color:#FFFFFF;font:11pt Calibri, sans-serif;padding:1pt 2pt;text-align:left;vertical-align:middle;}.mcs .foot td{border:0;font-size:9pt;color:#888888;padding:0;}.mcs .foot{clear: both;}.mcs .foot td{background-color:transparent;}.mcs .sheet td:first-child{background-color:#ddddee;text-align:center;}.mcs .sheet tr:first-child td{background-color:#ddddee;text-align:center;}.mcs .r{text-align:right;}.mcs .formulas tr:first-child td{background-color:#eeaaaa;}.formats, .names, .formulas, .cond, .fonts, .valid{float: none;margin: 10pt 10pt 0 0;}

	A	B
1	Suchwort	Kategorie
2	Aldi	Ernährung
3	Lidl	Ernährung
4	Real	Ernährung
5	Deichmann	schuhe
6	Aral	Tanken
7	Shell	Tanken
8	etc

Tabelle by Tab2Html ©Gerd alias Bamberg

CSS by Mc Santa

Jetzt markiere die Zellen A1:B8 und klicke auf Einfügen > Tabelle (ganz links).
Überprüfe den Bereich, Haken bei "hat Überschriften" und OK.
Du hast nun eine "Liste" (oder "Tabelle") erstellt und das Menüband ändert sich. Hier gibt es viele Einstellungen, mit denen du mal rumspielen kannst. Interessant ist der Tabellenname oben links. Hier steht derzeit vermutlich Tabelle1, vergib hier einen sprechenden Namen, zb: Kategorien_Zuordnung.
Die Formel passt du jetzt so an:
.mcs{margin-top:5pt;}.mcs table{border-collapse:collapse;}.mcs table td{border:1px solid #999999;color:#000000;background-color:#FFFFFF;font:11pt Calibri, sans-serif;padding:1pt 2pt;text-align:left;vertical-align:middle;}.mcs .foot td{border:0;font-size:9pt;color:#888888;padding:0;}.mcs .foot{clear: both;}.mcs .foot td{background-color:transparent;}.mcs .sheet td:first-child{background-color:#ddddee;text-align:center;}.mcs .sheet tr:first-child td{background-color:#ddddee;text-align:center;}.mcs .ffarial{font-family:arial;}.mcs .bcA9D18D{background-color:#A9D18D;}.mcs .fs8{font-size:8pt;}.mcs .c{text-align:center;}.mcs .formulas tr:first-child td{background-color:#eeaaaa;}.formats, .names, .formulas, .cond, .fonts, .valid{float: none;margin: 10pt 10pt 0 0;}

Zelle	Formel
G1:G5	=Kategorie_McS(D1;Kategorien_Zuordnung)

Das gute ist nun: Wenn du die Liste ergänzt, wird der Listenbereich automatisch erweitert. Da sich die Formel nun auf die Liste bezieht und nicht mehr auf feste Zellen, ist der Bezug immer aktuell.

Konntest du alle Schritte in deiner Datei umsetzen?

Fragen sind immer gerne gesehen *Smilie
VG
Santa

 

Und nochmal danke *Smilie .
Sooo hab ich mir das vorgestellt.
Excel fängt an mir immer besser zu gefallen *biggrin.gif*
Muss zugeben, bis dato hatte ich wenig berühungspunkte mit Excel
(Maschinenbau-Student)

 

\@MC Santa
Da ich noch paar Kleinigkeiten offen habe habe ich dir/ihnen eine PN geschickt.