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

Mitglied: lupolo

lupolo (Level 1) - Jetzt verbinden

03.03.2012, aktualisiert 14:27 Uhr, 13452 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
Windows 10

USB Maus und Tastatur versagen Dienst unter Windows 10

Erfahrungsbericht von hardykopff vor 13 StundenWindows 103 Kommentare

Da steht man ziemlich dumm da, wenn der PC sich wegen fehlender USB Tastatur und Maus nicht bedienen lässt. ...

Administrator.de Feedback
Update der Seite: Alles zentriert
Information von Frank vor 16 StundenAdministrator.de Feedback11 Kommentare

Hallo User, die größte Änderung von Release 5.8 ist das Zentrieren der Webseite (auf großen Bildschirmen) und ein "Welcome"-Teaser ...

Humor (lol)

WhatsApp-Nachrichten endlich auch per Bluetooth versendbar

Information von BassFishFox vor 1 TagHumor (lol)4 Kommentare

Genau darauf habe ich gewartet! ;-) Der beliebte Messaging-Dienst WhatsApp erhält eine praktische neue Funktion: Ab dem nächsten Update ...

Google Android

Googles "Android Enterprise Recommended" für Unternehmen

Information von kgborn vor 1 TagGoogle Android3 Kommentare

Hier eine Information, die für Administratoren und Verantwortliche in Unternehmen, die für die Beschaffung und das Rollout von Android-Geräten ...

Heiß diskutierte Inhalte
Server-Hardware
Welche Rolle spielt Design bei Enterprise IT Hardware?
Frage von ApolloXServer-Hardware17 Kommentare

Ich arbeite für einen internationalen Elektronikhersteller in der Forschung und meine Aufgabe ist es, Feedback von Nutzern in Hinsicht ...

Windows Netzwerk
WSUS4 und Windows 10 Updates automatisch installieren
Frage von sammy65Windows Netzwerk15 Kommentare

Hallo miteinander, ich habe mit einen neuen WSUS Server aufgesetzt Server 2016 darauf einen aktuellen WSUS. Grund, wir stellen ...

Speicherkarten
Vergessliche USB-Sticks?
Frage von hanheikSpeicherkarten14 Kommentare

Ich habe in den letzten Tagen 500 USB-Sticks mit Bilddateien bespielt. Obwohl ich die Dateien mit größter Sorgfalt kopiert ...

Windows Netzwerk
Backup über WAN
Frage von petereWindows Netzwerk11 Kommentare

Hallo, ich muss aus einem entfernten WAN (synchrone 1Gbit) Daten sichern. Dabei handelt es sich sowohl um wenige große ...