Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 13:44:05
Bezeichnung Tabellenblatt aus Zelle für Formel auslesen
24.04.2024 12:38:40
Formeländerung durch Wechsel von Excel 2021 auf 2019
24.04.2024 12:31:18
Excel - Sverweis mit Wenn-Abfrage kombinieren
24.04.2024 12:16:26
Name von aktuellem VBA-Modul ausgeben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1472to1476
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1472to1476
1472to1476
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Ausschlusskriterien bei Berechnungen

Ausschlusskriterien bei Berechnungen
08.02.2016 13:53:24
Sascha
Hallo zusammen,
ich habe ein Problem das im ersten Moment sehr trivilal erscheint und wahrscheinlich auch ist, jedoch finde ich die Lösung dazu nicht.
Als vereinfachtes Beispiel findet sich dazu exemplarisch eine Datei auf dem Server:
https://www.herber.de/bbs/user/103366.xlsx
Es geht dabei um folgendes:
In Spalte A sind Werte eingetragen.
In Spalte B sind zwei Kriterien eingetragen.
In Spalte C und D sind erneut werte eingetragen.
In Spalte E soll nun eine Berechung (Summe) der Werte aus den Spalten C und D erfolgen, sofern der entsprechende Wert in Spalte A nicht den Kriterien in B entspricht.
Ein Beispiel:
Spalte A: 4
Spalte B: 4, 2
Spalte C: 2
Spalte D: 2
Das Ergebnis soll in diesem Fall 0 lauten, da der Wert in Spalte A einem Kriterium in Spalte B entspricht.
Weiteres Beispiel:
Spalte A: 6
Spalte B: 4, 2
Spalte C: 3
Spalte D: 2
Das Ergebnis soll in diesem Fall 5 (C+D) lauten, da der Wert aus A nicht den Kriterien in B entspricht.
In meiner verlinkten Arbeitsmappe habe ich das versucht umzusetzen, jedoch erfolglos.
Über eine Hilfestellung hierzu würde ich mich sehr freuen.
Lieben Gruß
Sascha

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Ausschlusskriterien bei Berechnungen
08.02.2016 14:05:07
Michael
Hallo Sascha!
So?
Tabelle1

 ABCDE
1MesswerteKriterienWert 1Wert 2Ergebnis
24422 
362325
42 42 
56 527
67 628
79 729
82 82 
91 9211

Formeln der Tabelle
ZelleFormel
E2=WENN(UND(A2<>$B$2;A2<>$B$3); SUMME(C2:D2); "")
E3=WENN(UND(A3<>$B$2;A3<>$B$3); SUMME(C3:D3); "")
E4=WENN(UND(A4<>$B$2;A4<>$B$3); SUMME(C4:D4); "")
E5=WENN(UND(A5<>$B$2;A5<>$B$3); SUMME(C5:D5); "")
E6=WENN(UND(A6<>$B$2;A6<>$B$3); SUMME(C6:D6); "")
E7=WENN(UND(A7<>$B$2;A7<>$B$3); SUMME(C7:D7); "")
E8=WENN(UND(A8<>$B$2;A8<>$B$3); SUMME(C8:D8); "")
E9=WENN(UND(A9<>$B$2;A9<>$B$3); SUMME(C9:D9); "")

LG
Michael

Anzeige
AW: Ausschlusskriterien bei Berechnungen
08.02.2016 16:06:16
Sascha
Hallo Michael,
danke schon mal für die Hilfe und so wie Du es gelöst hast ist es auch meiner Darstellung nach korrekt.
Jedoch ist es so, dass der Bereich in dem das Ergebniss angezeigt wird, keine Spalte sondern eine Matrix ist, bei der bestimmte Kriterien erfüllt sein müssen.
Die Formel in der Ergebnisszelle sieht dementsprechend so aus:
=WENN(ODER($A$2:$A$9=$B$2;$A$2:$A$9=$B$2);0;SUMME(WENN($F$2:$F$9=$G9)*($H$2:$H$9=I$9); ($C$2:$C$9)+($D$2:$D$9)+WENN...))
Der Fett markierte Teil der Formel ist der, der bei mir nicht funktioniert. Gibt es dafür eine Lösung?
Gruß
Sascha

Anzeige
AW: Ausschlusskriterien bei Berechnungen
08.02.2016 17:04:29
Michael
Hallo!
Warum hast Du Deine Frage nicht gleich mit Deinen konkreten Verhältnissen in der Tabelle veranschaulicht? Auf Deine Nachfrage Bezug zu nehmen ist ohne Kontext schwierig.
Kannst Du einen erläuternden Tabellenausschnitt hier hochladen (Arbeitsmappe!, kein Bild!)? Dann schau ich nochmal drüber!
LG
Michael

AW: Ausschlusskriterien bei Berechnungen
09.02.2016 09:44:53
Sascha
Hallo,
ich wollte es nicht unnötig kompliziert machen...
Nun habe ich eine Beispielarbeitsmappe erstellt die alle für die Berechnung notwendigen Informationen enthält.

Die Datei https://www.herber.de/bbs/user/103386.xlsx wurde aus Datenschutzgründen gelöscht


In der Berechnungstabelle (Spalte M bis AF) soll die Tabelle (Spalte A bis J) nach bestimmten Kriterien ausgewertet werden.
Das Problem liegt darin, dass bei dem Auftreten bestimmter Werte in in Spalte H keine Berechnung durchgeführt werden soll. Die Werte die das betrifft, sind in der Berechnung mit eingefügt und beziehen sich auf die Liste in Tabelle 2.
Genauer: Wenn also in Spalte H entweder "Free standing test system", ""Roof top test system" oder "Tracked test system" steht soll die Berechnung nicht ausgeführt, sondern als 0 gewertet werden.
So wie ich mir das bisher vorgestellt hab (Siehe Formel in Berechnungstabelle) funktioniert es nicht, da immer der Wert 0 ausgegeben wird.
Ich hoffe es ist jetzt konkreter.
Vielen dank für die Hilfe.
Gruß
Sascha

Anzeige
AW: Ausschlusskriterien bei Berechnungen
09.02.2016 10:12:10
Michael
Hallo Sascha!
ich wollte es nicht unnötig kompliziert machen...
Ich vermute das hast Du bereits in Deiner Berechnung getan ;-) - da ist mir nämlich noch nicht klar, WAS Du überhaupt berechnen bzw. summieren willst unter welchen Bedingungen.
Ich habe eine Vermutung: Du kannst die von Dir benötigte Ansicht/Berechnung am einfachsten durch eine Pivot-Tabelle lösen. Schau mal bspw. hier (Tabelle4): https://www.herber.de/bbs/user/103388.xlsx
Die Summe hab ich jetzt mal auf Spalte D bezogen (bei mir Überschrift "Wert").
Ist das die Ansicht die Du haben willst und kommen da plausible Daten raus? Kommst Du damit zurecht?
Gib Bescheid, dann schauen wir weiter...
Lg
Michael

Anzeige
AW: Ausschlusskriterien bei Berechnungen
09.02.2016 10:48:09
Sascha
Hallo,
das mit der Pivot-Tabelle ist eine gute Idee. Jedoch sind die Werte leider nicht die, die ich suche.
Ich hab nochmal eine Beispielmappe angehängt, die verdeutlicht, was ich summieren möchte.

Die Datei https://www.herber.de/bbs/user/103390.xlsx wurde aus Datenschutzgründen gelöscht


Es gibt mehrere Bereiche mit (Failure 1; Power loss 1) (Failure 2; Power loss 2) usw. aus den Ergebnissen der Berechnungen der einzelnen Bereiche soll die Summe gebildet werden, wobei eben in den besagten Fällen (sofern ein bestimmtes Kriterium aus Spalte K zutrifft) keine Berechnung durchgeführt werden soll.
Danke für die Geduld :)
Gruß
Sascha

Anzeige
AW: Ausschlusskriterien bei Berechnungen
09.02.2016 11:37:40
Michael
Hallo Sascha!
Ich glaube immer noch, dass wir mit einer PivotTabelle hinkommen. Allerdings konnte ich Deiner neuen Bsp-Mappe keine Hinweise entnehmen, die für mich hilfreich sind. Bedenke: Ich habe von Deiner Branche bzw. den entsprechenden Berechnungen keine Ahnung - d.h. ich kann Dir bei der Excel-Umsetzung nur helfen, wenn ich genau verstehe welche Werte wie, womit und unter welchen Bedingungen berechnet werden sollen.
Versuchen wir's mal so: Kannst Du mir mal für Zelle P2 aufzeigen
a) welcher Wert (Ergebnis) da stehen soll und
b) wie Du auf dieses Ergebnis kommst und zwar in dieser Form: In P2 steht die Summe aller Werte aus Spalte D multipliziert/dividiert... mit Werten aus Spalte J, wenn in der jeweiligen Zeile in Spalte B xyz steht, aber in der jeweiligen Zeile in Spalte K NICHT abc steht usw... Also alle Bedingungen und Berechnungen verbal und bezogen auf Deine Tabellenbereiche!
LG
Michael

Anzeige
AW: Ausschlusskriterien bei Berechnungen
09.02.2016 12:01:50
Sascha
Hallo,
zu a) In Zelle P2 soll die Summe aller Degradationsraten stehen, wenn der Fehler im ersten Jahr (P1), dem spezifischen Fehler (O2) und der Klimazone (O1) entspricht.
zu b) Die Summe der sogenannten Degradationsraten berechnet sich folgendermaßen:
In P2 steht die Summe aller Ergebnisse der Quotienten aus (Spalte D multipliziert mit Spalte M) dividiert durch (Spalte J multipliziert mit Spalte L).
Kurz: Summe (Spalte D*Spalte M)/(Spalte J*Spalte L)
Und wie gesagt, wenn Spalte K der Wert entsprechend "Tabelle2!A5", "Tabelle2!A6" oder"Tabelle2!A9" steht, soll die Berechnung nicht durchgeführt werden.
Die Berechnung der sogenannten Degradationsraten klappt auch super, nur wenn ich eben das Kriterium definiere, dass bei bestimmten Werten in Tabelle K keine Berechnung durchgeführt wird, wird plötzlich alles 0 in der Berechnungstabelle.
Gruß
Sascha

Anzeige
AW: Ausschlusskriterien bei Berechnungen
09.02.2016 13:33:10
Michael
Hallo!
Okay, nochmals Versuch mit Pivot (Tabelle3): https://www.herber.de/bbs/user/103395.xlsx
Versuch mal die PivotFelder entsprechend zu verändern.
Kommt das hin?
LG
Michael

AW: Ausschlusskriterien bei Berechnungen
09.02.2016 14:35:17
Sascha
Hallo,
das ist schon mal super.
Nur zwei Kleinigkeiten noch. Zum einen erschließt sich mir die Berechnung der Werte in der Pivot Tabelle noch nicht. Ich sehe nirgendwo eine hinterlegte Formel, aus der sich das Ergebnis in Zelle B6 ergibt. Dazu muss ich sagen, dass ich mich mit Pivot-Tabellen nicht besonders auskenne.
Zum anderen ist in die Berechnung ja bisher nur in dem Feld "Zeilen" die Spalte Failure 1 aufgeführt. Wenn ich nun die Spalte Failure (Tabelle1 Spalte E) hinzufüge, addiert die Tabelle alle Werte unabhängig welcher Failure typ. Es sollen jedoch natürlich nur gleiche Typen addiert werden.
Nach mehrfachem probieren und Änderung der Bereichsfilter komme ich dabei nicht auf das richtige Ergebnis.
Gruß
Sascha

Anzeige
AW: Ausschlusskriterien bei Berechnungen
09.02.2016 14:49:38
Michael
Hallo!
Aha - wir nähern uns dem Ziel ;-). Für die Berechnung der Werte habe ich ein "Berechnetes Feld" innerhalb der PivoTabelle erstellt. Das kannst Du Dir wie folgt anschauen: Stell Dich in ein Feld der PivotTabelle, dann im Menüband PivotTable-Tools -- Optionen -- Gruppe Berechnungen -- Felder, Elemente und Gruppen -- Berechnetes Feld. Im folgenden Menü steht dann "Feld1" -- wenn Du den Dropdown-Pfeil daneben anklickst siehst Du "Summe" -- wähle diesen Punkt aus und Du siehst die von mir hinterlegte Formel.
Bzgl. Spalte "Failure 1" und "Failure": Wo sollen die Werte aus Spalte "Failure" einfließen? In die Formel (Berechnetes Feld) oder in die Anzeige? In meiner Variante war die Spalte Failure noch gar nicht in die PivotFelder miteinbezogen.
LG
Michael

Anzeige
AW: Ausschlusskriterien bei Berechnungen
09.02.2016 15:21:00
Sascha
Hallo,
die Werte aus Spalte "Failure 1" und "Failure" sind im Prinzip auch Filter. Da nur wenn ein bestimmter "Failure" in einem bestimmten "Climate" in einem bestimmten "Year" auftritt, die Berechnung erfolgen soll.
Im Grunde zeigt die von mir Anfangs eingebrachte Berechungstabelle ja einigermaßen, wie ich mir das vorgestellt hatte. Somit auch die Listung von "Failure" in den Zeilen und von "Year" in Spalten.
Das Problem in der Pivot Tabelle ist jetzt eben der Umgang mit den Daten, wenn der gleiche "Failure" in unterschiedlichen Zeilen aber im selben "Year" auftritt.
Tritt ein "Failure" mehrfach im gleichen "Year" auf, dann sollen diese addiert werden, auch wenn sie in unterschiedlichen Zeilen ("PV Survey(#)") auftreten.
Gruß
Sascha

Ich versteh's nicht - evtl. jemand anderer?
09.02.2016 17:14:50
Michael
Hallo!
Sorry, aber ich kann Dir einfach nicht folgen, auch nicht nachdem ich mir Deine Ursprungsformel (die so keinesfalls funktionieren kann) jetzt nochmal zu Gemüte geführt habe.
Ich geb hier auf, vielleicht versteh ich Dich einfach nicht richtig!
LG
Michael

AW: Ich versteh's nicht - evtl. jemand anderer?
09.02.2016 17:22:18
Sascha
Ok, trotzdem vielen dank für deine Zeit und Hilfe.
Ich bekomme das schon noch irgendwie raus. Hat ja bis jetzt noch immer geklappt.
Wenn ich es geschafft hab werde ich mal eine Beispielmappe posten.
Bis dann.
Gruß
Sascha

Problem gelöst
10.02.2016 10:35:39
Sascha
Hallo,
ich habe nun eine Lösung gefunden und um das ganze nicht unvollendet hier zu hinterlassen, möchte ich sie kurz präsentieren.
Die Beispielmappe dazu findet sich unter folgendem Link:
https://www.herber.de/bbs/user/103421.xlsx
Es ging allgemein ja darum, einen Wert nur dann zu ermitteln, wenn bestimmte Kriterien erfüllt sind.
Im speziellen sollte die Ermittlung des Wertes nicht erfolgen, wenn in Spalte K bestimmte Werte vorliegen.
Gelöst hab ich es jetzt wahrscheinlich nicht mit der elegantesten aber einer denkbar einfachen Methode.
Ich habe eine neue Spalte L eingefügt, die mir einen Faktor aus dem Nenner des Quotienten in der Berechnungsformel gleich 0 setzt, wenn in Spalte K das entsprechende Ausschlusskriterium zutrifft.
In der Berechnung wird dann mit der Spalte L und nicht wie bisher mit der Spalte N gerechnet.
Manchmal muss man einfach nur eine Nacht darüber schlafen :-)
Besten dank nochmal für die tatkräftige Unterstützung.
Gruß
Sascha

Na super! Viel Erfolg noch! owT
10.02.2016 10:39:38
Michael

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige