Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema TextBox
BildScreenshot zu TextBox TextBox-Seite mit Beispielarbeitsmappe aufrufen

Durchschnitt Berechnen ohne Zahlenwerte | Herbers Excel-Forum


Betrifft: Durchschnitt Berechnen ohne Zahlenwerte von: esMicha
Geschrieben am: 27.11.2009 12:07:38

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

  

Betrifft: Durchschnittswert indirekt über Zuordnungstabelle von: Matthias5
Geschrieben am: 27.11.2009 12:27:12

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


  

Betrifft: Durchschnitt aus symbolischen Werten berechnen von: NoNet
Geschrieben am: 27.11.2009 12:39:23

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) 

Tabelle eingefügt mit Syntaxhighlighter 4.15

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


  

Betrifft: Hi NoNet, warum WECHSELN()? Gruß, Matthias von: Matthias5
Geschrieben am: 27.11.2009 12:49:31




  

Betrifft: Tja Matthias, Warum eigentlich ? von: NoNet
Geschrieben am: 27.11.2009 13:01:09

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 ;-))


  

Betrifft: OT :-)) mT von: Matthias5
Geschrieben am: 27.11.2009 13:07:04

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


  

Betrifft: AW: Durchschnitt Berechnen ohne Zahlenwerte von: ingoG
Geschrieben am: 27.11.2009 12:46:31

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...


  

Betrifft: OT@IngGi : 4,8666 = Excel Bug ? ;-) von: NoNet
Geschrieben am: 27.11.2009 12:53:06

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


  

Betrifft: Ich hätte auch noch eine außer Konkurrenz,... von: Luc:-?
Geschrieben am: 27.11.2009 18:45:24

...NoNet,
wenn nicht gerade 120 Zeilen verarbeitet wdn müssten... ;-)
Gruß Luc :-?


  

Betrifft: Mal als Anregung für VBA-Freaks... von: Luc:-?
Geschrieben am: 01.12.2009 01:44:17

 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 :-?


Beiträge aus den Excel-Beispielen zum Thema "Durchschnitt Berechnen ohne Zahlenwerte"