EXCEL-FUNKTIONEN: KGROESSTE Beispiel Mandatsberechnung Deutscher Bundestag, KKLEINSTE

Mit der Funktion KGRÖSSTE wird EXCEL damit "beauftragt", einen bestimmten Bereich - im Beispiel ist es G2 bis G7 - nach den größten Zahlen zu durchsuchen. Genaue Erklärung des Vorgangs und der Formeln unterhalb der Tabelle.

Beispiel 1:
Die Berechnung der Sitzverteilung im Bundestag wird mit einer von den Herren Hare (GB, Verfassungsjurist) und Niemeyer (D, Mathematiker) entwickelten Formel durchgeführt:
Die zu vergebenden Sitze im Bundestag werden mit der Zahl der Zweitstimmen der einzelnen Parteien multipliziert und durch die Gesamtzahl der Zweitstimmen aller an der Verteilung teilnehmenden Parteien dividiert. C2 bis C7
Dabei erhält jede Partei so viele Sitze, wie ganze Zahlen auf sie entfallen. D2 bis D7
Die dann noch verbleibenden Sitze werden in der Reihenfolge der höchsten Reste, die sich bei der Berechnung ergeben, zugeteilt. F2 bis F7
In einem weiteren Zählgang werden die direkt in ihren Wahlkreisen gewählten Abgeordneten Spalte H von der für jedes Land ermittelten Gesamtzahl abgezogen.

	A	B	C	D	E	F	G	H	I
1	PARTEI	ZWEIT-STIMMEN	BERECHNUNG	GANZE MANDATE	REST	REST-MANDATE	ZWISCHEN-SUMME	ÜBERHANG-MANDATE	GESAMT
2	SPD	16.194.665	213,170	213	0,170	0	213	9	222
3	CDU	13.136.740	172,919	172	0,919	1	173	7	180
4	CSU	3.494.309	45,996	45	0,996	1	46	0	46
5	GRÜNE	3.838.326	50,524	50	0,524	1	51	0	51
6	FDP	4.648.144	61,184	61	0,184	0	61	0	61
7	DIE LINKE	4.118.14	54,208	54	0,208	0	54	0	54
8		45.430.378	598	595	3,000	3	598	16	614

C2: =B2*598/$B$8
Die für die Landeslisten der Parteien insgesamt abgegeben Stimmen - in B2 stehen jene für die SPD - werden mit 598 - die Anzahl der Sitze im Bundestag, die mittels Zweitstimmen vergeben werden - multipliziert und durch die Gesamtzahl aller Zweitstimmen - B8 - dividiert. FORMEL BIS C7 KOPIEREN

C8: =RUNDEN(SUMME(C2:C7);0)
Die Werte in Spalte C werden addiert -und auf 0 Kommastellen gerundet.

D2: =ABRUNDEN(C2;0)
Egal, wie groß die Nachkommastellen sind, es muss auf ganze Zahlen abgerundet werden. FORMEL BIS D7 KOPIEREN

E2: = C2-D2
Mit einer einfachen Subtraktion werden die Nachkommastellen ermittelt. FORMEL BIS E7 KOPIEREN

F2: =WENN(E2>=KGRÖSSTE($E$2:$E$7;$C$8-$D$8);1;0)
KGRÖSSTE sucht im Bereich von E2 bis E7 nach den größten Zahlen. Und zwar nach den DREI größten Zahlen, denn C8-D8 ergibt die Zahl der Restmandate, bei der Wahl 2005 waren es DREI. WENN die Zahl in E2 zu dieser Gruppe gehört (>=), dann soll der Wert 1 ausgegeben werden, die anderen kriegen nix 0. FORMEL BIS F7 KOPIEREN

G2: =D2+F2
Ganzzahlige Mandate (D2) + Restmandate

I2: =G2+H2
Zu den bisher ermittelten Mandaten werden nun noch die Überhangmandate (gibt es, wenn die in den Wahlkreisen der Länder errungene Anzahl der Direktmandate größer ist, als die mittels Zweitstimmen gewonnenen Mandate) FORMEL BIS I7 KOPIEREN

Beispiel 2:
Die Berechnung der Sitzverteilung im Burgenländischen Landtag erfolgt in zwei Stufen. Zahlen: Wahl 2010

Zuerst werden Mandate auf Bezirksebene (es gibt 7 Wahlbezirke) vergeben. Jede Partei, die mehr als 4 % der gültigen Stimmen erreicht hat, nimmt an der Mandatsverteilung teil.
Die Wahlzahl ergibt sich aus der Rechnung:
Gesamtzahl der gültigen Stimmen im Bundesland / Zu vergebende Mandate im Landtag (=36)
Dann wird für jede Partei die Anzahl ihrer Mandate auf Bezirksebene errechnet:
Für die Partei abgegebene Stimmen / Wahlzahl
Die Reststimmen werden nach dem d`Hondtschen Verfahren verteilt. Dabei werden die Reststimmen der Parteien durch 2, dann durch 3, durch 4, usw. geteilt. Die auf Bezirksebene nicht vergebenen Mandate (bei der Landtagswahl 2010 waren es 13) kommen nun so zur Verteilung, dass es für die dreizehn größten Zahlen je ein Mandat gibt.

B11 =D9/36
F9 =SUMME(F2:F8)
G2 =GANZZAHL(F2/$B$11) kopieren bis G8
G9 =SUMME(G2:G8)
H2 =GANZZAHL(F2-(G2*$B$11)) kopieren bis H8
nur SPÖ (14) und ÖVP (9) gewannen auf Bezirksebene Mandate:

13 Mandate sind noch zu vergeben. Daher werden mit der Funktion KGRÖSSTE die 13 größten Zahlen gesucht:
(Glück für die LBL, sie hat mit 7.559 gerade noch die 4 %-Hürde geschafft)
A5 =188960*4%
H2 =KGRÖSSTE(B2:F19;13) im Bereich B2 bis F19 wird die 13-größte Zahl gesucht 4.425
I2 =WENN($H$2<=B2;"Mandat";"") nach unten kopieren
J2, K2, L2 und M2 =WENN($H$2<=B2;"Mandat";"") nach unten kopieren

KKLEINSTE durchsucht einen vom Anwender eingegrenzten Bereich nach dem niedrigsten Wert. Im folgenden Beispiel nach den sechs niedrigsten Werten, deren "größter" (3,79) angezeigt wird.

Im Rahmen eines Wirtschaftlichkeitstests werden drei PKW-Modelle von 20 verschiedenen Fahrern an drei Tagen auf vorgegebenen Distanzen gefahren und ihr Treibstoffverbrauch ermittelt.
Es werden sechs "Economy-Zertifikate" vergeben.
Welches Modell erreicht die meisten Zertifikate?

	A	B	C	D	E	F	G	H	I
1	FAHRER	Modell A	Modell B	Modell C		ECONOMY	Modell A	Modell B	Modell C
2	Fahrer 1	4,02	3,99	4,01		3,79	-	-	-
3	Fahrer 2	3,98	4,02	4,12			-	-	-
4	Fahrer 3	3,89	3,91	3,80			-	-	-
5	Fahrer 4	4,04	3,81	3,79			-	-	Economy
6	Fahrer 5	3,96	4,00	4,01			-	-	-
7	Fahrer 6	3,99	3,88	4,01			-	-	-
8	Fahrer 7	3,88	3,79	3,79			-	-	-
9	Fahrer 8	3,90	3,92	3,82			-	-	-
10	Fahrer 9	4,00	3,99	3,99			-	-	-
11	Fahrer 10	3,92	4,01	3,97			-	-	-
12	Fahrer 11	3,97	3,86	3,81			-	-	-
13	Fahrer 12	4,02	4,02	3,78			-	-	Economy
14	Fahrer 13	4,10	3,78	3,98			-	Economy	-
15	Fahrer 14	3,81	3,93	3,96			-	-	-
16	Fahrer 15	3,86	4,03	4,03			-	-	-
17	Fahrer 16	4,07	3,76	3,99			-	Economy	-
18	Fahrer 17	3,89	3,79	3,77			-	-	Economy
19	Fahrer 18	4,01	3,88	3,98			-	-	-
20	Fahrer 19	4,00	3,78	4,01			-	Economy	-
21	Fahrer 20	3,80	4,04	4,04			-	-	-

F2: =KKLEINSTE(B2:D21;6)    die sechs 6 niedrigsten KKLEINSTE Verbrauchswerte aller Fahrten B2:D21 werden gesucht.
G2: =WENN(B2<=$F$2;"Economy";"-") Wenn WENN der Verbrauch von Modell A in B2 B2 zu den sechs niedrigsten <=$F$2 gehört, wird in G2 Economy "Economy" ausgegeben, andernfalls ein Strich "-"     FORMEL BIS G21 KOPIEREN

H2: =WENN(C2<=$F$2;"Economy";"-")   FORMEL BIS H21 KOPIEREN
I2: =WENN(D2<=$F$2;"Economy";"-")    FORMEL BIS I21 KOPIEREN