Top-Themen

Aktuelle Themen (A bis Z)

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, 13028 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 ..
Ähnliche Inhalte
Microsoft Office
Werte in Matrix einfügen- Schleife
gelöst Frage von IkanerMicrosoft Office6 Kommentare

Hallo Leute, wie kann ich das bestehende Makro ergänzen, sodass es mir für jedes entstandene Arbeitsblatt (trägt den Namen ...

VB for Applications
Werte mit gleicher Auftragsnummer addieren
gelöst Frage von Semih55VB for Applications8 Kommentare

Hallo Zusammen, ich habe ein Excel-sheet mit Auftragsnummer, Kunde und Betrag. Jetzt möchte ich alle Beträge mit gleicher Auftragsnummer ...

PHP
Werte in einer sql Spalte addieren
gelöst Frage von helmuthelmut2000PHP13 Kommentare

Hallo, Ich habe folgendes Problem. Ich habe eine PHP Seite mit einer MSSQL Datenbank. Da gibt es eine Tabelle ...

Batch & Shell
PS .length Werte addieren
gelöst Frage von AventosBatch & Shell5 Kommentare

Servus ich habe mehrere Dateigrößen per auslegesen. Die Werte werden mir nun wenn ich beide Variablen auslese z. B. ...

Neue Wissensbeiträge
Internet

EU-DSGVO: WHOIS soll weniger Informationen liefern

Information von sabines vor 1 StundeInternet1 Kommentar

Wegen der europäische Datenschutzgrundverordnung stehen die Prozesse um die Registierunf von Domains auf dem Prüfstand. Sollte die Forderungen umgesetzt ...

Verschlüsselung & Zertifikate

19 Jahre alter Angriff auf TLS funktioniert immer noch

Information von BassFishFox vor 7 StundenVerschlüsselung & Zertifikate1 Kommentar

Interessant zu lesen. Der Bleichenbacher-Angriff gilt unter Kryptographen als Klassiker, trotzdem funktioniert er oft noch. Wie wir herausgefunden haben, ...

Windows 10

Windows 10 Fall Creators Update - Neue Funktion Hyper-V Standardswitch kann ggf. Fehler bei Proxy Configs verursachen

Erfahrungsbericht von rzlbrnft vor 18 StundenWindows 103 Kommentare

Hallo Kollegen, Da wir die Gefahr lieben, haben wir bei einigen Usern nun mittlerweile das Creators Update drauf. Einige ...

Sicherheit

TLS-Zertifikat und privater Schlüssel von Microsofts Dynamics 365 geleakt

Information von Penny.Cilin vor 20 StundenSicherheit

Microsoft hat versehentlich das TLS-Zertifikat inklusive dem privaten Schlüssel seiner Business-Anwendung Dynamics 365 geleakt. TLS-Zertifikat und privater Schlüssel von ...

Heiß diskutierte Inhalte
Netzwerkmanagement
Firefox Profieles im Roaming
gelöst Frage von Hendrik2586Netzwerkmanagement17 Kommentare

Hallo liebe Leute. :) Ich hab da ein kleines Problem, welches anscheinend nicht unbekannt ist. Wir nutzen hier in ...

Netzwerkmanagement
NAS über zwei weitere Ethernet Anschlüsse verbinden
gelöst Frage von Sibelius001Netzwerkmanagement16 Kommentare

Sorry - ich bin hier wahrscheinlich als kompetter IT Trottel unterwegs. Aber eventuell kann mir jemand ganz einfach helfen: ...

LAN, WAN, Wireless
Häufig Probleme beim Anmelden in WLAN
Frage von mabue88LAN, WAN, Wireless15 Kommentare

Hallo zusammen, in einem Netzwerk gibt es relativ häufig (1-2 mal pro Woche) Probleme mit der WLAN-Verbindung. Zunächst mal ...

Netzwerkgrundlagen
Hi eine blöde frage. xD
Frage von 132954Netzwerkgrundlagen13 Kommentare

Also: Habe 2012 r2 essentials neuinstalliert, allerdings installiert diese version ja gleich diesen gangen AD kram mit, den hab ...