Office: Klassengrenzen finden mit Mindesthäufigkeit pro Klasse

Hallo,

ich habe eine Spalte mit vielen Werten. Von diesen Werten möchte ich die Häufigkeiten wissen, eingeteilt in Klassen (Intervalle).
Meine Intervalle sind links offen und rechts geschlossen, also so:

(-inf, a]
(a,b]
(b,c]
...
(x,y]
(y,z]
(z, inf)

Nun möchte ich, dass pro Klasse, also pro Intervall, mindestens 500 Werte drinnen sind. Dazu möchte ich nun die Klassengrenzen wissen. Da das nicht immer genau 500 sind, weil die Werte nicht fortlaufend und jeder Wert nicht genau einmal vorkommt, sind es oft mehr als 500 Werte pro Klasse.
Aber ich möchte den nächstmöglichen haben.
Das Ergebnis sind dann folglich die Klassengrenzen.

Wer hat dazu eine Idee?
LG und vielen Dank schon mal.

 

Hallo,

ich präsentiere im Nachfolgenden eine halbautomatische Lösung:

1) Dein Wertevorrat befinde sich (nur beispielhaft) im Bereich B3:B11.

2) Im Bereich G3:G12 befinden sich Deine Klassengrenzen (jeweils der höchste Wert der Klasse, da nach oben hin jeweils abgeschlossen): Die Zahl -6 in Zelle G3 bedeutet somit Intervall -∞ bis -6; die nächste Zahl -3 in Zelle G4 bedeutet Intervall -5,999 bis -3, usw.

3) Im Bereich E3:E11 befindet sich als Matrixformel die HÄUFIGKEIT-Funktion, die 2 Parameter besitzt: den Bereich mit den Werten und den Bereich mit Intervallklassen der Werte.
Die Zahl 0 in der Zelle E3 bedeutet, dass die Anzahl Werte in der 1. Klasse (also -∞ bis -6) gleich 0 ist. Die Zahl 5 in Zelle E4 bedeutet, dass die Anzahl Werte in der 2. Klasse (also -5,999 bis -3) gleich 5 ist, usw.
(Bei Deinem Echtwerten müsste dort dann eine Zahl stehen, die mindestens 500 ist).

4) Verschiebt man die obere Intervallgrenze der 2. Klasse (Zelle G4) ändern sich damit die Werteanzahlen in der Zelle E4; somit ist bei Deinen Echtdaten die Grenze in Zelle G4 so zu wählen, dass in Zelle E4 zwar mindestes 500 steht, die Differenz von E4 und 500 aber ein Minimum annimmt.

5) Dieses Minimum, das mindestens 500 betragen muss, kann man durch manuelles Ausprobieren der Klassenobergrenzen ermitteln oder aber mit dem Zielwertsuche-Tool in Excel (oder eventuell sogar mit dem Solver-Tool).

	A	B	C	D	E	F	G	H
2					Anzahl		Intervallklassen
3		-3			0	-∞ bis -6	-6
4		-4			5	-5,999 bis -3	-3
5		-4			4	-2,999 bis …
6		-1			#NV
7		-5			#NV
8		10			#NV
9		11			#NV
10		-3			#NV
11		10			#NV
12
13

Zelle	Formel
E3:E11	{=HÄUFIGKEIT(B3:B11;G3:G12)}
G3	=MIN(B3:B11)-1

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]

 

Hallo,

erstmal ganz vielen Dank.
Das Problem ist, dass ich es bisher genau so mache, wie du geschrieben hast. Es sind aber so viele Daten und ca. 600 Klassen erforderlich, dass es "halbautomatisch" nicht gut möglich ist.

Kannst du mir noch eine andere (vollautomatische) Lösung vorschlagen oder mir dieses Sovlertool oder die Zielwertsuche erklären, wie das hier anzuwenden wäre?

LG

 

Hallo,

ich habe mich entschlossen, ein VBA-Makro zu basteln, das die Intervallgrenzen-Berechnungen durchführt. Ich habe im Arbeitsblatt den nötigen Kommentar hinzugefügt.

Ich hoffe, dass das Makro für große Datenbestände nicht zu langsam ist. Ich konnte es nur mit meinem kleinen Beispieldaten testen.

 

Super, danke.
Nun habe ich noch eine Frage:
Ich habe nicht nur 14 Werte sondern 30000 und das werden ca. 150 Klassen.
Wenn ich die Werte einfach in die Spalte B unter Werte alle einfüge, dann klappt das nicht. Was kann ich tun?
Ich habe für verschieden Tabellenblätter dieses auszuführen.
Angenommen, das erste Tabellenblatt heißt TabelleA, das 2. TabelleB usw.
Wie müsste ich dann den Quellcode ändern?

LG und vielen Dank

 

Hallo,

Ich habe im Kommentar, den ich ins Arbeitsblatt Tabelle1 eingefügt habe, im Punkt 1) ausdrücklich darauf hingewiesen, dass 3 Namen (Werte, Klassengröße, Intervallklassen) unbedingt festzulegen sind, damit das Ganze funktioniert - und dieses korrekte Festlegen der 3 Bereichsnamen ist die einzige Vorbedingung, bevor das Makro ausgeführt wird.

Zum Festlegen von Namen gibt es in Excel2007 den Namensmanager, erreichbar über den Tabulator Formeln der Multifunktionsleiste in der Gruppe Definierte Namen durch Klick auf den Button Namensmanager.
Führt man den Klick aus, erscheint ein Dialogfenster, das eine Liste von 4 Namen enthält (sie wurden von mir angelegt), wobei der Name Häufigkeiten überflüssig ist (darf gelöscht werden). Jede Namensdefinition enthält den Eintrag Tabelle1 in der Spalte Bereich, d.h. diese Namen sind nur im Arbeitsblatt Tabelle1 gültig (sogenannte lokale Namen). Man kann fürs Arbeitsblatt Tabelle2 wieder genau die gleichen Namen verwenden, nur müssen sie neu eingegeben werden und zwar mit dem Bereich Tabelle2.
Daneben gibt es auch globale Namen (d.h. diese sind in der ganze Arbeitsmappe gültig); ein solcher Name enthält in der Spalte Bereich den Eintrag Arbeitsmappe. Angenommen ich will, dass der Name Klassengröße zu einem globalen Namen wird (der zwar in einem Zellbereich eines bestimmten Arbeitsblattes enthalten ist, der aber innerhalb der ganzen Arbeitsmappe gültig und somit erreichbar ist; er braucht dann insgesamt nur 1x festgelegt werden für die ganze Arbeitsmappe), dann ist so vorzugehen:
a) Markieren des bisherigen Namenseintrages Klassengröße
b) Löschen des Namens durch Klick auf Button Löschen
c) Neuerliches Einfügen durch Klick auf Button Neu...
d) Im Ausfüllfenster Neuer Name ausfüllen: Feld Name: Klassengröße eingeben, Combobox Bereich Arbeitsmappe auswählen, Feld Bezieht sich auf =Tabelle1!$I$6 eingeben (oder interaktiv durch Markieren der Zelle)
e) Button Ok anklicken.

Wenn Du ferner im Blatt Tabelle1 statt 14 Werte 30000 Werte hast, musst Du natürlich auch den Bereichsnamen Werte neu festlegen. Angenommen Du willst ihn wie bisher in Zelle B4 beginnen lassen, dann würde er bis Zelle B30003 reichen. Um diesen Namen anzupassen,
a) den Listeneintrag Werte auswählen
b) unten im Dialogfenster wird das Feld Bezieht sich auf angezeigt; dieses Feld durch Eingabe von =Tabelle1$B$4:$B$30003 (wird wahrscheinlich in diesem Fall das Bequemere sein anstelle des interaktiven Zellbereichmarkierens). Unbedingt darauf zu achten ist, dass alle 4 $-Zeichen miteingegeben werden (denn sonst handelte es sich um einen relativen Namen - die Reaktion wäre in diesem Fall abenteuerlich).
Was den Namen Intervallklassen anbelangt, ist nur die 1. Zelle ganz zu oberst für den Ausgabebereich des Makros festzulegen. Dieser Name wäre somit ok, außer Du willst die Zelle an einem anderen Platz im Arbeitsblatt.

Diese Vorgangsweise muss auch für Werte in weiteren Arbeitsblättern eingehalten werden.
Wenn wir davon ausgehen, dass der Name Klassengröße global ist (wie vorhin festgelegt), braucht dieser nicht ein weiteres Mal festgelegt werden.
Dagegen müssen die Bereichsnamen Werte und Intervallklassen fürs Arbeitsblatt Tabelle2, Tabelle3, usw. festgelegt werden, aber jedesmal mit exakt diesen Namen (aber mit jeweils neuem Bereich: Tabelle2, Tabelle3, usw. und jeweils neuem Bezieht sich auf).

Das Makro wird durch Drücken der Tastenkombination Alt+F8 mit anschließender Eingabe-Taste ausgeführt. Es greift dabei stets auf die Bereichsnamen, die im aktiven Arbeitsblatt erreichbar sind, zu. Ist also zB. das Arbeitsblatt Tabelle2 das aktive Arbeitsblatt, so greift das Makro auf die Bereichsnamen Werte und Intervallklassen zu, die fürs Arbeitsblatt Tabelle2 festgelegt wurden, wogegen der Bereichsname Klassengröße (weil global) aus dem Arbeitsblatt Tabelle1 aus Zelle G2 bezogen wird.
Angenommen es gibt 1 Arbeitsblatt, für welches eine andere als die globale Klassengröße benötigt wird. Dann kann man für dieses eine Arbeitsblatt einen lokalen Namen Klassengröße eingeben. Diese lokale Namensdefinition übersteuert nur in diesem einen Arbeitsblatt die globale Definition - in allen anderen Arbeitsblättern ist mangels eines lokalen Namens der globale Name gültig.

Anmerkung:
1) Änderst Du nachträglich die Arbeitsblattnamen, wird diese Änderung vollautomatisch auch an den im Namensmanager eingegebenen Arbeitsblattnamen (Spalte Bereich und Spalte Bezieht sich auf) durchgeführt.

2) Zellbereichs-Namen kann man auch im Schnellverfahren festlegen:
a) Globale Namen: Man markiert den Zellbereich, der benamst werden soll. Dann gibt man den Namen in den weißen Bereich links von der Formeleditierzeile ein. Durch abschließendes Drücken der Eingabe-Taste ist dieser Bereichsname für die ganze Arbeitsmappe gültig.
b) Lokale Namen: Im Prinzip genau so wie bei a), nur muss man vor dem eigentlichen Namen noch den Namen des Arbeitsblattes gefolgt von einem !-Zeichen eingeben.
c) Durch Klick auf dieses weiße Feld (das eine Combobox ist) links der Formeleditierzeile wird die Liste der im aktiven Arbeitsblatt gültigen Namen (globale und lokale) angezeigt. Wählt man einen Listeintrag aus und klickt darauf, springt die Zellmarkierung zum definierten Namensbereich (der im Feld Bezieht sich auf im Namensmanager hinterlegt ist).

 

Hallo,

ganz vielen Dank für die viele Mühe.
Hat mir wirklich richtig geholfen und ich habe viel dabei gelernt!!!

SUPER - DANKE