Histogramm mit automatischer Erzeugung der Klassen
bernd wegener
ich habe folgendes Problem, das ich mit MS-Excel lösen möchte:
Ich habe zwei Spalten mit Daten, die erste Spalte enthält ein Merkmal
(numerisch), die zweite die Anzahl der Elemente der Stichprobe, die
dieses Merkmal besitzen (also eigentlich eine Häufigkeitsverteilung).
Ich möchte nun lediglich eine Klassenbreite vorgeben und Excel aus
diesen Daten das zugehörige Histogramm berechnen lassen, also eine
Folge von Summen der Werte der zweiten Spalte, in Abhängigkeit von der
Größe des Wertes (Klassen- oder Intervallzugehörigkeit) der ersten
Spalte.
Ich denke, ein Beispiel erläutert das besser:
Merkmal - Häufigkeit
5 10
12 7
17 2
19 33
22 35
25 6
Wenn ich als Klassenbreite 10 nehme, komme ich zu folgender Zuordnung
meiner Werte zu den einzelnen Klassen und den entsprechenden
summierten Häufigkeiten, die ich dann direkt als Histogramm (nicht
mittels Histogramm-Funktion !) gegen die Klassen auftragen könnte:
Merkmal - Häufigkeit Klasse Gesamthäufigkeit in der Klasse
5 10 1 - 10 10
12 7 11 - 20
17 2 11 - 20 7 + 2 + 33 = 42
19 33 11 - 20
22 35 21 - 30 35 + 6 = 41
25 6 21 - 30
Ich besitze also schon die Häufigkeit jedes einzelnen Merkmals, nicht
aber die Häufigkeiten nach Klassen. Ergebnis sollten also die
Summenwerte der vierten Spalte sein.
Formeln wie SUMMEWENN, TEILSUMME, TEILERGEBNIS scheinen nicht zu
greifen, die Histogramm-Funktion leistet offensichtlich auch nicht das
gewünschte. Irre ich da oder muß ich wirklich einen anderen Weg gehen,
der aber mittels der eingebauten Excel-Funktionen gangbar ist ?
Vielen Dank für Eure Hilfe.
Gruß
Bernd
Thomas Ramel
bernd wegener schrieb:
>
> Merkmal - Häufigkeit Klasse Gesamthäufigkeit in der Klasse
> 5 10 1 - 10 10
> 12 7 11 - 20
> 17 2 11 - 20 7 + 2 + 33 = 42
> 19 33 11 - 20
> 22 35 21 - 30 35 + 6 = 41
> 25 6 21 - 30
>
> Ich besitze also schon die Häufigkeit jedes einzelnen Merkmals, nicht
> aber die Häufigkeiten nach Klassen. Ergebnis sollten also die
> Summenwerte der vierten Spalte sein.
Angenommen, die obigen Werte stehen in A1:B7
Erstelle zwei Spalten mit den jeweiligen Werten von Klassenbeginn/ende
(hier angenommen in D1:E4):
von bis Summe
1 10
11 20
21 30
in F2 dann die folgende Formel eingeben und nach unten kopieren:
=SUMMENPRODUKT((A2:A7>=D2)*(A2:A7<=E2)*(B2:B7))
Wenn du nun die Werte in D2:E4 veränderst oder die Formeln entsprechen nach
unten erweiterst, kannst Du die Summierung über die Klassen beliebig
erweitern und gestalten.
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für Microsoft-Excel -
Jean Ruch
Hallo Bernd,
> die Histogramm-Funktion leistet offensichtlich auch nicht das gewünschte.
Ist es Dir nicht möglich die Daten so in eine Tabelle zu bringen, - bzw. aus
einer solchen zu holen -, wie die Funktion Histogramm es verlangt ?
Hast Du nähmlich in einer Spalte
10 mal die5 ,
12 mal die7,
2 mal die17,
33 mal die19,
35 mal die22 und
6 mal die25 untereinander (müssen aber nicht geordnet sein stehen),
so gibt Dir Excel / Histogramm bei Klassenvorgabe 10, 20 & 30 die
Klassenverteilung wie gewünscht :
10 für die Klasse 0...10,
42 für die Klasse 11...20
41 für die Klasse 21...30 und
0 für größer 30
Wenn diese Ur-Zahlen also in irgendeiner Spalte stehen sollten, kannst Du
Dir viel Mühe sparen
.
Oder trägt jemand diese Eigenschaften sonstwie zusammen und übergibt Dir
effektiv nur diese Teilsummen ? Dann ist eine Lösung, wie Thomas
vorgeschlagen, schon eine Erleichterung.
Mit freundlichem Gruß,
Jean
"bernd wegener" <bew...@gmx.net> schrieb im Newsbeitrag
news:18b5338b.03051...@posting.google.com...
bernd wegener
Hallo Thomas,
Deine Lösung funktioniert, mir ist aber noch nicht alles klar:
Vielleicht könntest Du mir die Funktionsweise noch näher erläutern.
Ein Verständnisproblem habe ich mit den Vergleichsausdrücken: generell
gesprochen, der Vergleich zwischen einem Bereich und einem Zellinhalt
liefert (erwartungsgemäß) einen Wahrheitswert, aber wie kommt dieser
zustande, d.h., unter welchen Bedingungen liefert ein solcher Ausdruck
wahr bzw. falsch ?
Ein anderes Problem das ich hatte mit der Anwendung Deiner Methode auf
meine Daten (wobei ich exakt die gleiche Positionierung der Daten im
Arbeitsblatt benutzt habe wie in Deiner Anleitung) sind die
Ergebniszellen in Spalte F:
In Zelle F4 finde ich nach dem Kopieren z.B. folgende Formel:
=SUMMENPRODUKT((A4:A9>=D4)*(A4:A9<=E4)*(B4:B9))
wobei die drei angegebenen Bereiche ja nicht mehr alle Daten erfassen,
sondern nur noch die jeweils untersten 4 Zellen. Ist die Formel dann
so im allgemeinen Falle noch korrekt? Ich denke, mit mehr
Informationen über die Funktionsweise sehe ich da auch klarer.
Wäre nett, wenn Du mir ein bischen dazu schreiben könntest.
Gruß
Bernd
Thomas Ramel
bernd wegener schrieb:
>
> Ein Verständnisproblem habe ich mit den Vergleichsausdrücken: generell
> gesprochen, der Vergleich zwischen einem Bereich und einem Zellinhalt
> liefert (erwartungsgemäß) einen Wahrheitswert, aber wie kommt dieser
> zustande, d.h., unter welchen Bedingungen liefert ein solcher Ausdruck
> wahr bzw. falsch ?
Summenprodukt ist eine Matrixformel, welche die angegebenen Bereiche
durchläuft und entsprechende WAHR/FALSCH-Matrizen erzeugt, die dann
miteinander 'verrechnet' werden.
In der genannten Formel (hier bereits die korrigierte Version):
=SUMMENPRODUKT(($A$2:$A$7>=D2)*($A$2:$A$7<=E2)*($B$2:$B$7))
wird im ersten Teil der Bereich A2:A7 mit dem Inhalt der Zelle D2
verglichen; es wird geprüft, ob der jeweilige Zellinhalt grösser oder
gleich dem Wert in D2 ist.
Die Matrix sieht in diesem Falle so aus:
{WAHR;WAHR;WAHR;WAHR;WAHR;WAHR}
(Zur Kontrolle markiere ($A$2:$A$7>=D2) in der Bearbeitungszeile und drücke F9)
Im zweiten Teil geschieht dasselbe mit dem Bereich A2:A7 und Zelle E2; hier
wird geprüft, ob der jeweilige Wert kleiner oder gleich dem Wert in E2 ist.
Die Matrix sieht in diesem Falle so aus:
{WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}
(Kontrolle durch markieren von ($A$2:$A$7<=E2) und F9)
Diese beiden Matrizen werden nun multipliziert:
{WAHR;WAHR;WAHR;WAHR;WAHR;WAHR}*{WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}
und ergeben:
{1;0;0;0;0;0}
(Kontrolle durch markieren von ($A$2:$A$7>=D2)*($A$2:$A$7<=E2) und F9)
Diese Matrix wird nun noch mit der dritten multipliziert:
{1;0;0;0;0;0}*{10;7;2;33;35;6}
das ergibt dann:
{10;0;0;0;0;0}
und diese Werte werden summiert.
In dieser Weise geschieht dies mit jeder Zeile in der die Formel steht.
> Ein anderes Problem das ich hatte mit der Anwendung Deiner Methode auf
> meine Daten (wobei ich exakt die gleiche Positionierung der Daten im
> Arbeitsblatt benutzt habe wie in Deiner Anleitung) sind die
> Ergebniszellen in Spalte F:
>
> In Zelle F4 finde ich nach dem Kopieren z.B. folgende Formel:
>
> =SUMMENPRODUKT((A4:A9>=D4)*(A4:A9<=E4)*(B4:B9))
>
> wobei die drei angegebenen Bereiche ja nicht mehr alle Daten erfassen,
> sondern nur noch die jeweils untersten 4 Zellen. Ist die Formel dann
> so im allgemeinen Falle noch korrekt?
In der Tat fehlten hier die absoluten Bezüge; das Resultat hat also nur per
Zufall gestimmt - Danke fürs Aufmerksamsein ;-)
Die korrigierte Formel findest Du oben.
> Wäre nett, wenn Du mir ein bischen dazu schreiben könntest.
Es ist nun etwas mehr als 'ein bisschen' geworden....
bernd wegener
Hallo Thomas,
vielen Dank für Deine sehr ausführliche Erläuterung. Jetzt habe auch
ich es geblickt. Eine pfiffige Lösung.
Vielleicht noch eine Frage / Anmerkung: Wenn ich mich recht erinnere,
gibt es so etwas wie die Multiplikation von Wahrheitswerten nicht. Ist
es korrekt, daß man die Multiplikation der ersten beiden Matrizen in
der SUMMENPRODUKT-Formel eher als logische AND-Verknüpfung
entsprechender Wahrheitswerte in den Matrizen auffassen muß ? (Gut,
man könnte vielleicht auch sagen, daß Excel die Wahrheitswerte hier
als 0 (Null) und 1 interpretiert, so daß eine Multiplikation wieder
Sinn macht).
Vielen Dank für Deine Hilfe, ich denke, ich kann jetzt meine Daten mit
Hilfe der Formel recht elegant verarbeiten.
Gruß
Bernd
Thomas Ramel
bernd wegener schrieb:
>
> vielen Dank für Deine sehr ausführliche Erläuterung. Jetzt habe auch
> ich es geblickt. Eine pfiffige Lösung.
Bitte schön, ist gern geschehen.
> Vielleicht noch eine Frage / Anmerkung: Wenn ich mich recht erinnere,
> gibt es so etwas wie die Multiplikation von Wahrheitswerten nicht. Ist
> es korrekt, daß man die Multiplikation der ersten beiden Matrizen in
> der SUMMENPRODUKT-Formel eher als logische AND-Verknüpfung
> entsprechender Wahrheitswerte in den Matrizen auffassen muß ? (Gut,
> man könnte vielleicht auch sagen, daß Excel die Wahrheitswerte hier
> als 0 (Null) und 1 interpretiert, so daß eine Multiplikation wieder
> Sinn macht).
Das ist genau so; ob Du nun (in einem Tabellenblatt) 1 oder WAHR schreibst
ist dasselebe, ebenso bei 0 oder FALSCH.
> Vielen Dank für Deine Hilfe, ich denke, ich kann jetzt meine Daten mit
> Hilfe der Formel recht elegant verarbeiten.
Fein, das freut mich - und Danke für die Rückmeldung.