Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.05.2024 23:36:08
IFNA Funktion in WENNNV übertragen
16.05.2024 19:31:55
Zellenfarbe beim.Filtern ändern
16.05.2024 19:29:08
Mit INDIREKT() auf andere Tabellenblätter referenzieren
16.05.2024 19:06:22
Monatssumme / Zahlweise / Kalenderjahr
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1960to1964
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
1960to1964
1960to1964
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

DB-Mittelwert, 2 Datensätze, viele Spalten, nur >0

DB-Mittelwert, 2 Datensätze, viele Spalten, nur >0
15.01.2024 16:03:27
Henki
Hallo zusammen,

eine hübsche DB-Mittelwertsberechnung aus der Botanik raubt mir die Nerven. :)

Es sind zwei Datensätze (DS).

DS 1: Kennwerte bzw. Zeigerwerte je Pflanzenart
DS 2: Deckungsgrad je Pflanzenart

Ziel: Durchschnittlicher Kenn- bzw. Zeigerwert berechnen ohne Wichtung

Bei unbekannten Zeigerwerten oder Fehlen der Art steht eine 0 in der Zelle, daher nur Werte >0


Für Hilfe wäre ich sehr dankbar, mit trial and error komme ich nicht weiter oder bin bis 2030 beschäftigt. :)
Grüße, Henki

https://www.herber.de/bbs/user/166098.xlsx

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
=SUMME(Bereich)/ZÄHLENWENN(Bereich;">0")
15.01.2024 16:40:12
Rolf
Hallo
evtl. so?

Gruß Rolf
AW: DB-Mittelwert, 2 Datensätze, viele Spalten, nur >0
16.01.2024 11:17:45
Henki
Vermutlich habe ich bei der Anpassung an die Originaltabelle Fehler drin. Das prüfe ich nochmal Rolf.
AW: Dann viel Erfolg bei der Fehlersuche!
16.01.2024 11:43:48
Rolf
wenns nix wird, nenn mir doch mal die zu prüfenden Bereiche und zeig mir die von dir angepasste Formel,
oder lösche alle sensiblen Daten und poste die gesamte Tabelle.
Gruß Rolf
angepasste Formel
16.01.2024 11:49:16
Henki
=SUMMENPRODUKT(($K$2:$FK$2)*($K$2:$FK$2>0)*($K6:$FK6>0))/ZÄHLENWENNS($K6:$FK6;">0";$K$2:$FK$2;">0")
AW: Tabelle
16.01.2024 12:35:40
Rolf
...deine Bezüge sind falsch.

Du hast doch für deine Bereiche Namen vergeben, dann für F einfach:

=SUMMENPRODUKT((F)*(F>0)*($E4:$FE4>0))/ZÄHLENWENNS($E4:$FE4;">0";F;">0")
und nach unten ziehen. F bleibt damit immer "$E$1:$EF$1" und die 4 ändert sich beim runterziehen.
Die anderen Formeln bekommst du dann alleine hin.
Gruß Rolf
Anzeige
AW: Tabelle
16.01.2024 13:05:49
Henki
Ich verstehe es nicht. Das habe ich gemacht.
F bleibt bei in Zeile 1 und der zweite Teil der Formel rutscht nach unten. Oder sehe ich da irgendwas nicht?

Mit deiner letzten Formel hier, bleibt es auch gleich.
=SUMMENPRODUKT((F)*(F>0)*($E4:$FE4>0))/ZÄHLENWENNS($E4:$FE4;">0";F;">0")


Ich schaus mir nochmal in aller Ruhe an... auch meine Gegenrechnung. Vielen Dank dir erstmal soweit.
AW: Tabelle
16.01.2024 13:16:59
Rolf
..jetzt versteh ich bald auch nix mehr;-)
welce Mittelwerte willst du denn haben?
Die der Kennwerte, oder die der der jeweiligen Deckungsgrade?
Bisher dachte ich, du willst die der Kennwerte.
Wenn du die Deckungsgrademittelwerte brauchst, musst du natürlich die Formel vorne umstellen:
=SUMMENPRODUKT(($E4:$FE4)*(F>0)*($E4:$FE4>0))/ZÄHLENWENNS($E4:$FE4;">0";F;">0")
Gruß Rolf
Anzeige
Gelöst
16.01.2024 13:38:30
Henki
Kennwerte ist Prima. :) Das passt. Wenn du dir aber sicher bist, dass deine Formel stimmt, kann der Fehler ja nur in meiner Probe bzw. Gegenrechnung liegen.

Jetzt! Ich habs! Klar! Oh Mann! :) Also, ich kann ja nicht alle Arten für die Gegenrechnung nehmen, sondern nur diejenigen, die auch einen Kennwert größer 0 haben!
Sorry Rolf. :) Herzlichen Dank für deine große Hilfe!
AW: Gelöst
16.01.2024 14:14:22
Rolf
Hallo,

ich weiß jetzt zwar immer noch nicht, welche Mittelwerte du suchst,
aber schön, dass zu jetzt zufrieden bist.
Welche meiner Formeln war denn jetzt die richtige?
die für die Kennwerte, oder die für die Deckungsgrade?
Gruß Rolf
Anzeige
AW: Gelöst
16.01.2024 20:22:36
Henki
Guten Abend Rolf,

die einzelnen Mittelwerte für die Kennzahlen pro Plot brauche ich.
Die erste Formel von dir passt. Kann sie nachvollziehen / verstehe ihren Aufbau.
Habe also auch was gelernt. :)

Gruß, Henki
AW: =SUMME(Bereich)/ZÄHLENWENN(Bereich;">0")
15.01.2024 17:19:34
Henki
Hallo Rolf,

danke dir. Knapp vorbei wie es aussieht.

Ist von mit glaube ich nicht gut genug erklärt.

Der gesuchte Mittelwert resultiert aus Datensatz 1.
Dieser soll aber nur berücksichtigt werden, wenn die Werte in DS 2 größer 0 sind.
Der Mittelwert aus dem DS 1 soll aber in der Berechnung die 0-Werte ausschließen.

Mein Betreuer meinte, es wäre die Formel mit DBMITTELWERT(Bereich;">0") und beide DS kombiniert.
Anzeige
AW: =SUMME(Bereich)/ZÄHLENWENN(Bereich;">0")
15.01.2024 17:21:09
Henki
Aber letztlich egal wie, hauptsache richtig. :)
AW: dann so
15.01.2024 18:45:41
Rolf
Hallo

dann probiers mal so (wenn ich dich richtig verstanden habe):
=SUMMENPRODUKT((E2:I4)*(E2:I4>0)*(E5:I7>0))/ZÄHLENWENNS(E5:I7;">0";E2:I4;">0")

das Grundprinzip bleibt, du musst halt alle Bedingungen mit aufnehmen/verknüpfen,
sowohl bei der Summenermittlung, als auch bei der Anzahl der Divisionswerte.

P.s. Es ist immer hilfreich, wenn ein Sollergebnis mit angegeben wird.

Gruß Rolf
Geänderte Tabelle
15.01.2024 20:13:18
Henki
Leider nein. Ich habe die Tabelle angepasst und hoffe es ist nun besser zu verstehen? Sorry.

Hier noch ein paar Infos zum Hintergrund, damit es greifbarer wird und nicht nur abstrakte Zahlen im Raum stehen:

Grundlage ist z. B. 1m² Wiese mit ihren Pflanzen. Ziel ist es, die Bodeneigenschaft anhand der Pflanzen und ihren spezifischen Zeigerwerten wie Feuchte oder Stickstoffgehalt zu beschreiben (die Brennnessel ist z. B. ein klassicher Stickstoffzeiger). Manchmal schwanken Zeigerwerte je nach Art oder sind unbekannt. Dann werden sie mit 0 definiert.
Man schaut sich also an, welche Pflanze vorkommt und welchen Zeigerwert sie hat. Mit der Berechnung des Mittelwertes erreicht man somit Erkenntnisse über die Bodeneigenschaften wie Feuchtigkeit oder Stickstoffgehalt.

https://www.herber.de/bbs/user/166102.xlsx
Anzeige
AW: Geänderte Tabelle
15.01.2024 23:30:39
Rolf
Hallo

wenn du einzelne Mittelwerte suchst (F, R und N), musst du das auch dazu sagen, ich bin immer von einem Mittelwert über alles ausgegangen.

Wenn du nur eine Zeile betrachten willst, musst du sie halt auch nur auf eine einzelne Zeile beziehen:
für F: =SUMMENPRODUKT(($E2:$I2)*($E2:$I2>0)*($E$5:$I$5>0))/ZÄHLENWENNS($E$5:$I$5;">0";$E2:$I2;">0")

für R und N einfach nach unten ziehen.
Beachte, wie sich die absuluten und relativen Bezüge verhalten.

Gruß Rolf
Ergebnisse sind etwas zu hoch
16.01.2024 10:21:49
Henki
Moin Rolf,

die Ergebnisse sind leider zu hoch. Ich habe zwei Zeilen gegengerechnet. Auch die Formel mit den absoluten und realtiven Bezügen unterschiedlich angepasst. Teilweise waren dann zu viele und zu starke Ausreißer dabei.
Im Gegensatz zur eingestellten Beispieltabelle hat die Originaltabelle 157 Spalten, aber das müsste ja egal sein.

Hier habe ich gestern einen Beitrag entdeckt, von 2004, der ein ähnliches oder identisches Problem hat. Aufgrund der fehlenden Tabelle ist es für mich nur schwer die Formel versuchshalber auf meine Tabelle anzupassen.

Aber vielleicht hilft sie dir weiter?

https://www.herber.de/forum/archiv/480to484/480018_DBMITTELWERT_bei_mehreren_Spalten.html#2


PS Mein Betreuer meinte bereits, dass es eine komplexe Formel wird. :-/ Zum Kuckuck! :)
Anzeige
AW: Ergebnisse sind etwas zu hoch
16.01.2024 11:03:22
Rolf
Guten Morgen,

...aber meine Formel bringt doch exakt die Ergebnisse, wie in deiner Beispieldatei!?
Du brauchst für die R- und N-Mittelwerte auch nichst anzupassen, nur die Formel nach unten ziehen, die Formel passt sich
(wegen der z.T. relativen Bezüge) selbst an.
Stell doch nochmal deine Beispieldatei mit meinen Formeln hier ein und erläutere, wo sie falsch rechnet.

Gruß Rolf
AW: Ergebnisse sind etwas zu hoch
16.01.2024 11:09:53
Henki
Das ist es ja! Ich weiß auch noch nicht wo der vermeintliche Fehler liegt. Ich glaube, dass in der Originaltabelle mit 157 Spalten irgendwelche Abhängigkeiten vorliegen, die die Tabelle hier mit 5 Spaltet nicht berücksichtig. Anders kann ichs mir nicht erklären. Oder ich habe irgendeinen Fehler reingehaun. Muss ich mir nochmal in Ruhe ansehen.

Beispiel, Zeile eins. Summe der Zeigerwerte ist 45, Anzahl der Arten ist 16 - Ergebnis = 2,8
Die Formel von dir, eingefügt, ich hoffe ich habe sie auch richtig angepasst, denke schon, kommt auf den Wert 3,46
Anzeige

Links zu Excel-Dialogen

Frame

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige