Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

ISTZAHL in SUMMENPRODUKT-Formel funktioniert nicht
#1
Liebe Excel-Experten
 
Ich komme mit meinem Excel-Problem einfach nicht weiter.
Ich möchte einen Altersdurchschnitt aus einer Liste in einem Tabellenblatt errechnen.
Die Liste besteht aus zwei Spalten:
A: Alter in JJ (Spaltentitel); Zelleneinträge von 0 bis 67 in den Zellen 3 bis 70.
B: Anzahl Personen gleichen Alters (Spaltentitel); Zellenwerte werden durch die Formel =WENN(ZÄHLENWENN('Team -Daten'!$AC$5:$AC$80,A43)<1,#NV,ZÄHLENWENN('Team -Daten'!$AC$5:$AC$80,A3)) aus dem Tabellenblatt „Team-Daten“ generiert. Den Eintrag #NV für 0 Personen brauche ich für die nachfolgende Grafik. Die Linie soll nicht ständig auf 0 absacken.
In C71 berechne ich die Anzahl Personen: =SUMMEWENNS(B3:B70,B3:B70,"<>#NV").
In C72 berechne ich das Durchschnittsalter mit der Formel: =SUMMENPRODUKT((A3:A70)*(ISTZAHL(B3:B70)))/C71.
Und hier beginnen auch die Probleme: die Formel in C72 nimmt für „ISTZAHL“ jeweils immer eine 1, auch wenn an entsprechenden Stellen höhere Zahlen stehen. Das ergibt dann natürlich ein falsches Durchschnittsalter.
Das Problem könnte ich durch eine zusätzliche Spalte C lösen, in der die Zeilenprodukte von A und B stehen und die dann am Schluss summiert werden. Doch ich denke, es muss eine elegantere Lösung geben, indem man die bestehende Formel C72 entsprechend anpasst.
22
 
Für Euren Effort bedanke ich mich jetzt schon ganz herzlich.
 
Peter, der Frager
Antwortento top
#2
Hallöchen,

im Prinzip so:

Arbeitsblatt mit dem Namen 'Tabelle1'
ABC
3125
4231,6
53#NV

ZelleFormel
C3=SUMMEWENNS(B3:B70;B3:B70;"<>#NV")
C4{=SUMMENPRODUKT((A3:A70)*(WENN(ISTZAHL(B3:B70);B3:B70;0)))/C3}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
[-] Folgende(r) 1 Benutzer sagt Danke an schauan für diesen Beitrag:
  • Frager
Antwortento top
#3
abschließenHallo Peter,

Deine Auswertung bedarf weder SUMMEWENNS() noch SUMMENPRODUKT() .
Den prinzipiell richtigen Ansatz mit WENN(), hat ja Dir André schon aufgezeigt. Er hat dabei jedoch Deine beide oben Funktionen übernommen, die hier aber nicht wirklich notwendig sind.

Außerdem kannst Du beide Formelteile in einer Zelle verbinden und zwar so:

Code:
{=SUMME(WENN(ISTZAHL(B3:B70);A3:A70*B3:B70))/SUMMEWENN(B3:B70;"<>#NV";B3:B70)}
Die geschweiften Klammern {} nicht eingegeben sondern Formeleingabe mit [Strg]+[Shift]+[Enter] abschließen.


Ergänzend hierzu:
Aus Deinem Schreiben entnehme ich, dass Du Deine Datentabelle in A3:B70 wahrscheinlich zu einer "intelligenten" Tabelle formatierst hast.  Eine erste derartig formatierte Tabelle in einer Arbeitsmappe benennt Excel automatisch zu "Tabelle1" (bitte nicht verwechseln mit einem Tabellenblattnamen gleichen Namens), eine evtl. zweite in "Tabelle2" ...
Empfehlenswert ist es jedoch, den/die von Excel automatisch erzeugten Tabellennamen im Namensmanager in einen "sprechenden" zu ändern; hier z.B. in "Altersgruppendaten" oder einfacher und kürzer z.B. in "Daten"). In nachfolgender analoger Formel hab ich den Namen "Tabelle1" jedoch so belassen, wie automatisch von Excel als erste "intelligenten" Tabelle in meiner Beispielmappe erzeugt.

Dafür würde obige Formel wie folgt lauten:

Code:
{=SUMME(WENN(ISTZAHL(Tabelle1[Anzahl Personen gleichen Alters]);Tabelle1[Alter in JJ]*
Tabelle1[Anzahl Personen gleichen Alters]))/SUMMEWENN(Tabelle1[Anzahl Personen gleichen Alters];"<>#NV";
Tabelle1[Anzahl Personen gleichen Alters])}

Hieran erkennst Du aber auch, dass lange Überschriftnamen sich nicht unbedingt nur vorteilhaft auf die Lesbarkeit einer Formel auswirken können. Aber unabhängig davon, bleibt der entscheidende Vorteil einer "intelligenten" Tabelle, dass Du in einer solchen weitere Daten ergänzen kannst, ohne dass Du die Formel entsprechend anpassen musst. Denn diese passt sich automatisch an einen verkürzten oder erweiterten auszuwertenden Datenbestand an.
Gruß Werner
.. , - ...
[-] Folgende(r) 1 Benutzer sagt Danke an neopa für diesen Beitrag:
  • Frager
Antwortento top
#4
Hallo Schauan und Werner

Vielen Dank für Euren Einsatz.
Bei Schauans Version kam eine 0.0 raus. Dort scheint ein ähnliches Problem zu stecken, wie in meiner Version (Zellformatierungen?). Aber eine Super-Darstellung! Danke.
Werners Version führte direkt zum richtigen Ergebnis. Ich übernahm den ersten Code. Den zweiten mit der intelligenten Tabelle liess ich bewusst beiseite in Besorgnis darum, dass diese meinen IQ konkurrenzieren könnte und das Zepter in der Anwendung übernimmt 16 .

Gäbe es solche Foren mit diesen hilfsbereiten Fachkräften nicht, ich weiss nicht, mit wieviel monetärem Kapital, Ärger und Zeitverlust dies die Wirtschaft belasten würde!
Nochmals vielen herzlichen Dank an Euch!

18 

Peter, der Frager
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste