HERBERS Excel-Forum - das Archiv
Bewerten Sie hier bitte das Excel-Portal
Durchschnitt Berechnen ohne Zahlenwerte
esMicha

Hallo.
Ich habe eine Frage und ich hoffe jemand kann mir helfen:
Ich habe eine Tabelle mit Werten (Zahlen und Buchstaben gemischt), denen je eine Zahl zugeordnet werden kann.
r = 1
+ = 2
1 = 3
2 = 4
a = 5
b = 6
m = 7
3 = 8
4 = 9
5 = 10
Wie kann ich, wenn nun in einer Zeile bunt gemischt alle diese Werte stehen einen durchschnittswert ermitteln?
Beispiel:
+ 2 b b 3 1 hier wäre der Durchschnitt 4,66
Es wäre gut, wenn man das mit einer Formel machen könnte, ich muss das Ganze nämlich für ca. 150 Zeilen errechen.
Danke schonmal im Voraus.
Grüße,
esMicha

Durchschnittswert indirekt über Zuordnungstabelle
Matthias5

Hallo,
ich würde das so über eine Zuordnungstabelle machen, wobei nach meiner Rechnung der Durchschnitt bei deinem Beispiel 4,83 wäre (wenn ich die Zuordnungen richtig verstanden habe):
Tabelle1
 ABCDE
1+4,83  r1
22    +2
3b    13
4b    24
53    a5
61    b6
7      m7
8      38
9      49
10      510

verwendete Formeln
Zelle Formel
B1 {=SUMME(SUMMEWENN(D:D;A1:A10;E:E))/ANZAHL2(A1:A10)}
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren Version 4.8

Gruß,
Matthias
Durchschnitt aus symbolischen Werten berechnen
NoNet

Hallo Micha,
trage die Symbole/Werte in die Tabelle ein, dann kannst Du per SVERWEIS() von den symbolischen Zeichen auf deren Wert schließen.
Hier ein Beispiel : Spalten A:B enthalten die Zuordnung, der gelbe Bereich die zufälligen Symbole, der grüne Bereich die Umrechnung in "echte" Werte, der blaue Bereich den daraus errechneten Durchschnittswert :
ABCDEFGHIJKLMNOP
1
2
3
4
5
6
7
8
9
10

Funktionen im Tabellenblatt :

Zelle Formel 
J2   =WENN(D2="";"";WECHSELN(D2;D2;SVERWEIS(D2;$A$1:$B$10;2;0))*1) 
P2   =MITTELWERT(J2:O2) 

Du musst nur die Funktionen in J2 eingeben und dann bis O9 runterkopieren und die Funktion in P2 eingeben und bis P9 runterkopieren !
Gruß, NoNet
Hi NoNet, warum WECHSELN()? Gruß, Matthias
Matthias5

Tja Matthias, Warum eigentlich ?
NoNet

Hey Matthias,
Deine Frage ist berechtigt - ich wollte ja zunächst noch den Zusatz +N(BEREICH.VERSCHIEBEN(A1;ZEILE(2009:2010)*LN(EXP(PI())^0);ABS(HEUTE()-JETZT());1;1)) mit in die Funktion einbauen, aber das konnte ich dann geschickterweise doch noch heraus"kürzen".
Den letzten Optimierungsschritt habe ich wohl großzügigerweise "übersehen" - aber dafür gibt es ja andere Aufpasser hier im Forum ;-)
Gruß, NoNet
PS: Ich möchte jetzt auch nichts bzgl. "Senilität", "hohes Alter" oder gar WECHSEL-Jahre lesen ;-))
OT :-)) mT
Matthias5

Hi NoNet,
gut, dass du das noch kürzen konntest, BEREICH.VERSCHIEBEN() ist ja so inperformant.
Schade, dass du mir meinen Kommentar mit den Wechsel-Jahren nicht gönnst, den hätte ich doch mit Freuden reingeworfen!
Gruß,
Matthias
AW: Durchschnitt Berechnen ohne Zahlenwerte
ingoG

Hallo Micha,
es ist ein bischen problematisch, da sverweis und index sich weigern ;-)
so sollte es gehen:
in a1:B10 steht deine Referenztabelle (ohne die gleichzeichen)
in a13:a18 stehen deine Zeichen (+,2,b,b,3,1)
dann lautet die Formel:
=SUMMENPRODUKT(N(BEREICH.VERSCHIEBEN(B1;VERGLEICH(A13:A18;$A$1:$A$10;0)-1;0)))/ANZAHL2(A13:A18)
das Ergebnis ist allerdings (2+4+6+6+8+3)/6=29/6=4,866666
Gruß Ingo
PS eine Rückmeldung wäre nett...
OT@IngGi : 4,8666 = Excel Bug ? ;-)
NoNet

Hallo Ingolf,
Du hast Dich vermutlich verschrieben : 29/6 = 4,83333 und nicht 4,86666
Oder hast Du einen "Käfer" (BUG) in Deinem Excel ;-)
Interessant, welch unterschiedliche Lösungsansätze zu dieser Frage gepostet werden...
Gruß, NoNet
Ich hätte auch noch eine außer Konkurrenz,...
Luc:-?

...NoNet,
wenn nicht gerade 120 Zeilen verarbeitet wdn müssten... ;-)
Gruß Luc :-?
Mal als Anregung für VBA-Freaks...
Luc:-?

 GHIJKLM
75Daten1t5m3c
76Formeln11+t+5+m+3+c ƒ(c=2, m=6, t=7)=249*
77Formeln21¢t¢5¢m¢3¢c ƒ(c=2, m=6, t=7)=66*
78Formeln31¤t¤5¤m¤3¤c ƒ(c=2, m=6, t=7)=41,5*
79Formeln4  ƒ(c=2, m=6, t=7)=4   * ohne c,m,t-Ersetzung
80ZellformelnH76:=ListOn(H$75:M$75;"+")J76:=T2Form(H76;;2;6;7)L76:=ListOp(H76;"sum")
81 H77:=ListOn(H$75:M$75;"¢")J77:=T2Form(H77;;2;6;7)L77:=ListOp(H77;"cnt")
82 H78:=ListOn(H$75:M$75;"¤")J78:=T2Form(H78;;2;6;7)L78:=ListOp(H78;"ave")
83   J79:=T2Form(ListOn(H$75:M$75;"¤");;2;6;7)

Gruß Luc :-?