Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

EXCEL Addieren von Werten in Matrix - Suche nach Inhalten und diese dann addieren

Frage Entwicklung VB for Applications

Mitglied: lupolo

lupolo (Level 1) - Jetzt verbinden

03.03.2012, aktualisiert 14:27 Uhr, 11774 Aufrufe, 9 Kommentare

Hallo Leute

ich habe folgendes vor. In einer Tabelle stehen meine Ein- und Ausgaben, die ich das Jahr über habe. Siehe Bild:
Die untere Tabelle zeigt an, wie viel, ich in welchem Monat ausgegeben habe. Um nicht das immer händisch Makieren zu müssen würde ich das gern über eine Funktion oder einen Scribt lösen....

d050cc38d9a28a3fd2086c30db936aba - Klicke auf das Bild, um es zu vergrößern

1. Ich würde gerne die ganzen Einnahmen/ Monat in die Tabelle darunter eintragen. Dies soll für Januar, Februar ... Dezember gelten. Ich denke man muss hier mit einer Suchfunktion arbeiten und dann die Einnahmen bzw. Ausgaben/€ addieren. Habe es über den SVERWEIS probiert aber das war glaube nicht das Richtige.

2. Ich würde gerne, und ich glaube das ist dann nur im Punkt 1.die Funktion abgeändert, die Tabelle nach dem Inhalt "Tanken" überprüfen und dann die Beträge für den Monat addieren.


Hoffe ich habe es verständlich geschildert. Ich habe ein wenig Erfahrung in VBA aber ich habe nicht wirklich eine Ahnung, wie ich das Problem lösen könnte.

Grüße und schonmal Danke,
Jens
Mitglied: bastla
03.03.2012 um 16:27 Uhr
Hallo jens-3456!

Das könnte so gehen: Zunächst in A15 bis A26 die Monatszahlen 1 bis 12 eintragen, die Summen aus Zeile 10 in Zeile 11 verschieben und dann folgende "Array"-Formeln verwenden:
B15: =SUMME(WENN(MONAT($B$2:$B$10)=$A15;$C$2:$C$10;0))
C15: =SUMME(WENN(MONAT($E$2:$E$10)=$A15;$F$2:$F$10;0))
E15: =SUMME(WENN(MONAT($E$2:$E$10)=$A15;$F$2:$F$10;0)*($G$2:$G$10="Tanken"))

Die Eingabe dieser Formeln (auch nach jeder Bearbeitung) muss mit der Tastenkombination Strg + Umsch + Enter erfolgen - in der Bearbeitungszeile wird die Formel dann zwischen geschwungenen Klammern angezeigt.

Die Formeln lassen sich dann nach unten kopieren. Um weitere Zeilen in die Einnahmen-Ausgaben-Übersicht aufnehmen zu können, wird auch die Zeile 10 in die Berechnung mit einbezogen - so können neue Werte hinzugefügt werden, indem vor der Zeile 10 eine neue Zeile eingefügt wird.

Grüße
bastla
Bitte warten ..
Mitglied: Biber
04.03.2012 um 15:46 Uhr
Moin Jens-3456,

ergänzend zu bastlas funktionierender Lösung noch die Anmerkungen
  • matrixorientiert, aber ohne Eingabe mit Strg + Umsch + Enter ginge es mit SUMMENPRODUKT in allen Excelversionen
  • und in den neueren Versionen ab Excel 2007 (laut M$ auch schon mit 2003?) elegant mit SUMMEWENNS()

Die Formeln für SUMMENPRODUKT (die wie üblich etwas von der Syntax der Excel-Hilfe abweichen) mal als Beispiel:
B15	 =SUMMENPRODUKT((TEXT($B$2:$B$10;"MMM")=$A15)*$C$2:$C$10) 
C15	 =SUMMENPRODUKT((TEXT($E$2:$E$10;"MMM")=$A15)*$F$2:$F$10) 
E15	 =SUMMENPRODUKT((TEXT($E$2:$E$10;"MMM")=$A15)*$F$2:$F$10*($G$2:$G$10="Tanken"))
Anders als bastla habe ich die Texte "JAN", "FEB", "MRZ"..."DEZ" als nu' ma' leider gegeben hingenommen.
Damit ich einen Text "JAN" vergleichen kann mit einem Datumswert "02. Jan 12" --> verwende ich die Funktion TEXT(datumswert; "MMM"), dann passt dat.

Die obigen Formeln liefern übrigens, wenn die Matrizen mit Semikola aneinandergereiht werden wie laut Excel-Hilfe SUMMENPRODUKT( Matrix1; Matrix2;..MatrixN) immer 0.
Deshalb statt Verkettung die Multiplikation mit "*"

Alternative wäre die Verkettung mit ";" plus zusätzlichem Voranstellen des ebenfalls undokumentierten "--".
E15	 =SUMMENPRODUKT(--(TEXT($E$2:$E$10;"MMM")=$A15);$F$2:$F$10;--($G$2:$G$10="Tanken"))
Falls nur du allein diese Excel-Tabelle bearbeitest und nicht durch die Gegend mailst und du ein Excel 2007 oder höher hast--> Nimm SUMMEWENNS()
Die funktioniert wie im obigen Link beschrieben.

Grüße
Biber
Bitte warten ..
Mitglied: bastla
04.03.2012 um 16:39 Uhr
[OT] @Biber

Ich hätte ja schon fast selbst SUMMENPRODUKT() erwähnt, war mir aber ziemlich sicher, dass Du noch vorbeischauen würdest ...

Wie sieht übrigens Deine Lösung unter Verwendung von SUMMEWENNS() aus? Ich hatte da nicht wirklich den richtigen Zugang gefunden und deshalb die Array-Formel verwendet ...

Grüße
bastla
[/OT]
Bitte warten ..
Mitglied: Biber
04.03.2012 um 20:10 Uhr
Moin bastla,

eine Lösung ein Ansatz mit der SUMMEWENNS() könnte beispielsweise so aussehen:
B15	 =SUMMEWENNS($C$2:$C$10;$B$2:$B$10;">=01. "& $A15; $B$2:$B$10;"<01. "& $A16) 
C15	 =SUMMEWENNS($F$2:$F$10;$E$2:$E$10;">=01. "& $A15; $E$2:$E$10;"<01. "& $A16) 
E15	 =SUMMEWENNS($F$2:$F$10;$E$2:$E$10;">=01. "& $A15; $E$2:$E$10;"<01. "& $A16;$G$2:$G$10;"Tanken")
.... was zugegebenermaßen auch nicht viel weniger kryptisch anmutet als die Formeln oben.

Deshalb würde ich ohnehin -egal ob mit Arrayformeln oder SUMMENPRODUKT() oder mit SUMMEWENNS() mit "benamsten Bereichen" arbeiten.

Dann sieht das etwas lesbarer aus.
--  Bereiche benennen -hart oder dynamisch mit BEREICH.VERSCHIEBEN() 
-- Tabellenblatt heisst "AbXls2007", weil wegen... 
DatumRaus	=AbXls2007!$E$2:$E$10 
DatumRein	=AbXls2007!$B$2:$B$10 
KohleRaus	=AbXls2007!$F$2:$F$10 
KohleRein	=AbXls2007!$C$2:$C$10 
WegenWattDenn	=AbXls2007!$G$2:$G$10 
	 
B15	 =SUMMEWENNS(KohleRein;DatumRein;">=01. "& $A15; DatumRein;"<01. "& $A16) 
C15	 =SUMMEWENNS(KohleRaus;DatumRaus;">=01. "& $A15; DatumRaus;"<01. "& $A16) 
E15	 =SUMMEWENNS(KohleRaus;DatumRaus;">=01. "& $A15; DatumRaus;"<01. "& $A16;WegenWattDenn;"Tanken")
Der (relative) Charme von SUMMEWENNS() ist bei der Erweiterung der Formel in Zelle C15 hin nach E15 zu erkennen.
  • Der zu summierende Bereich "KohleRaus" muss nur einmal als erstes Argument angegeben werden, egal ob 1 oder 3 oder 7 Kriterien folgen.
  • das Hinzufügen eines weiteren Kriteriums wie von C15 nach E15 macht kein Problem -> es bleibt (den Umständen entsprechend) lesbar und wartbar

Grüße
Biber

P.S. Ist keine Fertiglösung, da die runterkopierten Formeln zumindest im Monat Dezember angepasst werden müssen, da hier kein Folgemonat in der Liste $A15:$A26 auftaucht.
Hier kann dann aber einfach das jeweils zweite Kriteriumspärchen aus der Formel gelöscht werden.
Bitte warten ..
Mitglied: bastla
04.03.2012 um 20:49 Uhr
Hallo Biber!

Ah ja, jetzt weiß ich wieder, warum ich schon SUMMEWENN() nie so wirklich mochte ...

Um die Formel unverändert auch für den Dezember verwenden zu können, sollte das etwa so gehen:
=SUMMEWENNS($C$2:$C$10;$B$2:$B$10;">=01. "&$A15;$B$2:$B$10;"<="&DATUM(JAHR(HEUTE());$A15+1;0))
- es wird hier die Ermittlung des letzten Tages eines Monats als 0. Tag des Folgemonats vorgenommen ...

Grüße
bastla
Bitte warten ..
Mitglied: Biber
04.03.2012 um 21:08 Uhr
[OT]
Moin bastla,

Zitat von bastla:
- es wird hier die Ermittlung des letzten Tages eines Monats als 0. Tag des Folgemonats vorgenommen ...
Genau, treffender hätte ich auch nicht beschreiben können, wie man/frau mit dieser Redmonder PraktikantInnenlogik umspringen muss, um irgendwas zum Fliegen zu bringen..
Einfach den 31.12.2012 umdefinieren als den 0.1.2013 -> schon klappt alles.

Bin ich froh, das dieses Jahr nur noch bis zum 23.12. geht... *fg

Grüße zurück
Biber
[/OT]
Bitte warten ..
Mitglied: lupolo
05.03.2012 um 18:57 Uhr
Vielen lieben Dank =) hat mir echt weiter geholfen. Jetzt geht es nur noch darum, diese Funktion (SUMMENPRODUKT() und Dergleichen) zu verstehen.
Bitte warten ..
Mitglied: Biber
05.03.2012 um 19:19 Uhr
Moin jens-3456,

das Verstehen-Wollen von abgedrehten Spezialfunktionen ist ja auch durchaus lobenswert.

Aber Und ergänzend dazu ist es manchmal nützlich, zwischendurch mal zwei Schritt zurückzutreten und das Konzept/den Aufbau nochmals aus etwas selbstkritischer Distanz anzuschauen.

Oben in deinem bisherigen Tabellenblatt ist der Aufbau momentan:

--> Zwei Tabellen untereinander
--> davon die erste Einnahmen/Ausgabentabelle variabel lang und mit abendfüllenden Details
--> die zweite Extrakt/Ergebnistabelle immer von konstanter Länge (Überschrift + genau 12 Monatszeilen + ggf Summenzeilen) und mit den interessanteren Infos
--> und die zweite interessante Tabelle wird dir ca. im April aus dem Gesichtsfeld wandern, es sei denn, du kaufst dir einen 72x192-Zoll-Monitor (sind im Moment noch ziemlich teuer!).

Ordne das Layout umgekehrt an, dann hast du auch viel weniger Stress mit den Formenl & dem Nach-unten-Kopieren und irgendwo eine Zeile einfügen etc.

Grüße
Biber
[Edit & P.S]
@jens-3456
Ergänzend zu den vortrefflichen Erklärungen bastlas nochmals der Hinweis auf die "benannten Bereiche".
Dann wird (s.o.) aus
=SUMMENPRODUKT((TEXT($E$2:$E$10;"MMM")=$A15)*$F$2:$F$10*($G$2:$G$10="Tanken"))
... das etwas verständlichere ...
=SUMMENPRODUKT((TEXT(DatumRaus;"MMM")=$A15)*KohleRaus*(WegenWattDenn="Tanken"))

Grüße und Dank an bastla
[Edit & P.S]
Bitte warten ..
Mitglied: bastla
05.03.2012 um 19:23 Uhr
Hallo jens-3456!

Na, so eine Hexerei ist das auch wieder nicht - am Beispiel der längsten Variante
=SUMMENPRODUKT((TEXT($E$2:$E$10;"MMM")=$A15)*$F$2:$F$10*($G$2:$G$10="Tanken"))
Immer schön von "innen" nach "außen":
(TEXT($E$2:$E$10;"MMM")=$A15)
lässt sich übersetzen in "Vergleiche die auf das Format 'Monat abgekürzt' formatierten Inhalte der Zellen E2:E10 jeweils mit dem Wert von A15 (also 'JAN')" - wenn es eine Übereinstimmung gibt, ist das Ergebnis 1, wenn nicht (weil das Datum aus einem anderen Monat stammt), dann 0

Analog dazu
($G$2:$G$10="Tanken")
Auch hier ergibt sich 1, wenn in der jeweils betrachteten Zelle (aus dem Bereich G2 bis G10) "Tanken" steht, und 0, wenn eben nicht.

Diese beiden Teilergebnisse liefern miteinander multipliziert nur dann 1, wenn in beiden Fällen eine Übereinstimmung gefunden wurde (also "Datum aus dem mit 'JAN' abgekürzten Monat" und "Zweck ist 'Tanken')", und nur dann ergibt die abschließende Multiplikation mit
*$F$2:$F$10
also der entsprechenden Zelle aus F2 bis F10 das Ergebnis "Wert der Zelle in Spalte F" - in allen anderen Fällen liefert die Multiplikation den Wert 0.

=SUMMENPRODUKT()
(das besser PRODUKTSUMME() heißen sollte - aber das ist ein anderes Thema) schließlich dient nur dazu, die genannten Berechnungen für alle Zellen der angegebenen Bereiche auszuführen ...

... daher nochmals:
Es wird geprüft, ob E2 als umformatiertes Datum 'JAN' liefert und ob G2 'Tanken' enthält, und nur wenn beides zutrifft, wird F2 in die Summenberechnung aufgenommen.
Für E3, G3 und F3 sowie alle weiteren Zeilen bis 10 läuft genau das Gleiche ab, und am Ende sind nur die passenden Werte im Ergebnis (= der Summe) enthalten.

Grüße
bastla
Bitte warten ..
Neuester Wissensbeitrag
CPU, RAM, Mainboards

Angetestet: PC Engines APU 3a2 im Rack-Gehäuse

Erfahrungsbericht von ashnod zum Thema CPU, RAM, Mainboards ...

Ähnliche Inhalte
Heiß diskutierte Inhalte
Windows Server
DHCP Server switchen (25)

Frage von M.Marz zum Thema Windows Server ...

Grafikkarten & Monitore
Win 10 Grafikkarte Crash von Software? (13)

Frage von Marabunta zum Thema Grafikkarten & Monitore ...

Windows Server
Mailserver auf Windows Server 2012 (8)

Frage von StefanT81 zum Thema Windows Server ...

Backup
Clients als Server missbrauchen? (8)

Frage von 1410640014 zum Thema Backup ...