Die statischen Excel-Funktionen QUANTIL.INKL und QUANTIL.EXKL

Im letzten Beitrag hast du etwas über die Funktion QUANTIL erfahren können. Mit der Excel-Version 2010 kamen zwei neue Funktionen dazu, QUANTIL.INKL und QUANTIL.EXKL.

Dieser Beitrag wird sich diesen Funktionen widmen und die Unterschiede aufzeigen.

Weiterlesen „Die statischen Excel-Funktionen QUANTIL.INKL und QUANTIL.EXKL“

Advertisements

Die statistische Excel-Funktion QUANTIL

Was sind Quantile?

Quantile sind Lageparameter einer Beobachtungsreihe. Sie bezeichnen Grenzwerte, wobei ein Teil der Reihe unterhalb und ein Teil oberhalb dieser Grenzwerte liegt.

Lageparameter? Damit bist du im Reich der deskriptiven Statistik angelangt. Dir ist bekannt, dass es in Excel eine Kategorie mit statistischen Funktionen gibt.

Bis einschließlich Excel 2007 gab es nur die Funktion QUANTIL. Ab Excel 2010 findest du zwei neue Ausgestaltungen, die Funktionen QUANTIL.INKL und QUANTIL.EXKL.

Mit der alten Funktion QUANTIL kann dennoch weiter gerechnet werden. Dieser Beitrag beschäftigt sich allein mit ihr.

Ein QUANTIL berechnen

Die Excel-Hilfe führt dazu aus, das die Funktion das Alpha-Quantil einer Gruppe von Daten zurück gibt. Mithilfe der Funktion kannst du einen Akzeptanzschwellenwert festlegen. Du kannst z.B. entscheiden, dass nur Bewerber eingeladen werden, deren Prüfungsergebnisse oberhalb des 85%-Quantils liegen.

Die Syntax lautet:

=QUANTIL(Matrix;k)

Matrix bezeichnet den Datenbereich, den du untersuchen willst. k ist der Alphawert aus einem geschlossenen Intervall zwischen 0 und 1. Beim 30%-Quantil z.B. schreibst du k als „30%“ oder als „0,3“.

Ein Beispiel soll dir die Funktionsweise näher bringen. Angenommen, dir liegen Angaben über den Wasserverbrauch eines Ortes über 15 Monate vor. Diese sind chronologisch, unsortiert, in einer Liste erfasst.

Quantil1

Du willst wissen, welche Werte die unterste und die oberste Toleranzgrenze bilden.

Die unterste Grenze soll durch das 25%-Quantil, die oberste durch das 75%-Quantil abgebildet werden. Zusätzlich errechnest du noch den Median mit der Funktion MEDIAN und als 50%-Quantil.

25%-Quantil =QUANTIL($C$4:$C$18;25%) =303.152,50

50%-Quantil =QUANTIL($C$4:$C$18;50%) =306.994,00

Median =MEDIAN($C$4:$C$18) =306.994,00

75%-Quantil =QUANTIL($C$4:$C$18;75%) =313.950,00

Der Mittelwert liegt übrigens bei =MITTELWERT($C$4:$C$18) =309.168,93 und ist damit größer als der Median.

Mit bedingter Formatierung willst du sichtbar machen, welche Werte zwischen dem 25%- und dem 75%-Quantil liegen. Dazu sortierst du die Liste aufsteigend nach Größe.

Quantil2

Markiere die Liste, gehe auf die Bedingte Formatierung und wähle die Regel „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.

Schreibe die Formel:

=UND(C4>=$F$4;C4<=$F$5)

und formatiere „Ausfüllen“ mit grün.

In der Liste sind nun die sieben Werte zwischen 303.242 und 312.874 farblich markiert. Diese Werte liegen zwischen dem 25%- und dem 75%-Quantil. Vier Werte liegen darunter, vier Werte darüber.

Diese Erkenntnisse kannst du z.B. für Planungszwecke verwenden. Das 25%-Quantil kann als Worst Case, das 75%-Quantil als Best Case dienen.

3 Wege, ein Histogramm zu erstellen

Der Begriff „Häufigkeit“ bezeichnet die Anzahl von Ereignissen, die mit einem Zählvorgangs ermittelt werden.

Mit Hilfe von Excel lassen sich verschiedene Wege gehen, um Häufigkeiten zu ermitteln und grafisch dazustellen.

Zunächst benötigst du die Ergebnisse des Zählvorgangs, die Strichliste und die Urliste.

 

1. Die Urliste

Dem Beitrag liegt eine Strichliste mit 160 Dickenmessungen eines Materials in mm, die im Rahmen der Qualitätsüberwachung erstellt wurde, zugrunde.

Freundlicherweise wurde mir vom Ingenieurbüro Uwe Herbst [1]gestattet, diese Daten zu verwenden.

Häufigkeit1

Daraus erstellst du zunächst eine sortierte Urliste, die alle gemessenen Werte entsprechend der Anzahl der Striche enthält.

Häufigkeit2

 

2. Bestimmung der Klassen

Die Bestimmung von Klassen ist erforderlich, um im Histogramm, der grafischen Darstellung der Häufigkeitsverteilung, keine 160 Säulen darstellen zu müssen. Die Messwerte werden in Gruppen, den Klassen, eingeteilt.

Angenommen, die Messwerte sollen in 10 Klassen eingeordnet werden.

Dazu wird die Spannweite der Messwerte (Maximum – Minimum) durch die Anzahl der Klassen dividiert.

Maximum = 1,90

Minimum = 1,59

Spannweite = 0,31

0,31 / 10 Klassen = 0,031, rund 0,03

Die Intervallgrenzen für die Klassen erhöhen sich um jeweils 0,03.

Häufigkeit3

Die absoluten Häufigkeiten ermittelst du mit dieser Formel, schreibe in E64:

=SUMMENPRODUKT((B$5:B$36>=C64)*(B$5:B$36<=D64)*(D$5:D$36))

Ziehe die Formel mit dem Ausfüllkästchen bis E73 herunter.

 

3. Erstellen des Histogramms

Ein Histogramm ist die grafische Darstellung der Häufigkeitsverteilung.

Bis zur Excel-Version 2013 bringt ein Säulendiagramm hilfsweise das Ergebnis. Markiere E64:E73 und rufe über Einfügen / Diagramme / Säule die gruppierte 2D-Säule auf.

Klicke die Säulen an und rufe Datenreihen formatieren auf. Gehe in die Reihenoptionen und stelle die Abstandsbreite auf 0.

Lege um die Säulen und um den Diagrammbereich noch einen sichtbaren Rahmen.

Formatiere die horizontale Achse, wähle die Daten aus der Klassentabelle aus.

Häufigkeit4

Die vorliegende Verteilung ist näherungsweise erkennbar, Normalverteilung mit Glockenkurve.

 

4. Die Excel-Funktion HÄUFIGKEIT

Verwende die Klassentabelle aus Abschnitt 2 mit den oberen Intervallgrenzen.

Häufigkeit5

Markiere den Bereich C79:C88, gib diese Formel ein und beende die Eingabe mit Strg + Shift + Enter (Matrixformel). Schreibe die geschweiften Klammern nicht selbst.

Auch hieraus lässt sich das Histogramm wie oben erstellen.

 

5. Die Analyse-Funktion „Histogramm“

Excel bietet im Menü Daten unter Datenanalyse die Analyse-Funktion Histogramm an.

Sollte dein Menüband diese noch nicht anzeigen, kannst du die Funktionen aktivieren, indem du über das Menü Datei / Optionen / Add-Ins die Analyse-Funktionen auswählst.

Rufe „Histogramm“ so auf:

Häufigkeit6

Klicke OK und fülle die Maske aus:

Häufigkeit7

Als Eingabebereich wählst du die sortierte Urliste aus Abschnitt 1, den Klassenbereich findest du in Abschnitt 3 und 4.

Wähle als Ausgabebereich einen Bereich deines Arbeitsblattes und setze abschließend noch den Haken bei „Diagrammerstellung“.

Dies ist das Ergebnis:

Häufigkeit8Häufigkeit9

Du hast jetzt über das Add-In die Klasseneinteilung mit den Häufigkeiten und ein Histogramm generiert.

 

6. Das Histogramm in Excel 2016

Ordne die Werte aus der sortierten Urliste in einer Spalte.

Gehe ins Menü Einfügen / Diagramme / Statistikdiagramme und rufe das Histogramm auf. In der Menüleiste befindet sich der Button Entwurf. Rufe dort „Daten auswählen auf“ und markiere deine neue Urliste.

Klicke auf die horizontale Achsenbeschriftung. Setze die „Anzahl Container“ z.B. auf 10. Die Containerbreite ergibt sich daraus automatisch mit 0,031. Das bedeutet beim ersten Container, dass er die Häufigkeit der Werte von 1,59 bis 1,62 abbildet.

Gestalte jetzt vielleicht noch die Zeichnungsfläche andersfarbig und bezeichne die Überschrift.

Das Histogramm sieht fertig so aus:

Häufigkeit10

[1] http://www.qmberatung.eu/

Wie du Werte in Excel ersetzen kannst

1. Ersatz von Einzelwerten

Einzelne Werte in Excel-Arbeitsmappen können mit der Textfunktion ERSETZEN ausgetauscht werden. Die Funktion ersetzt auf der Grundlage der Anzahl von Zeichen, die du angibst, einen Teil einer Textzeichenfolge durch eine andere Textzeichenfolge.

Die Syntax lautet:

=ERSETZEN(Alter_Text;Erstes_Zeichen;Anzahl_Zeichen;Neuet_Text)

Beispiel 1: In B4 steht die Textzeichenfolge „Düsseldorf“. Der Teil „Düssel“ soll durch „Deggen“ ersetzt werden.

=ERSETZEN(„Düsseldorf“;1;6;“Deggen“)

oder

=ERSETZEN(B4;1;6;“Deggen“)

Excel_Ersetzen1

Beispiel 2: In B19 steht „60708090“. Die 3. und 4. Ziffer soll durch „AB“ ersetzt werden.

 =ERSETZEN(„60708090″;3;2;“AB“)

oder

=ERSETZEN(B19;3;2;“AB“)

Excel_Ersetzen2

Beispiel 3: In B34 steht der Begriff „Autobahnmeisterei“. Der gesamte Begriff soll durch den Begriff „Tiefbauamt“ ersetzt werden.

=ERSETZEN(„Autobahnmeisterei“;1;99;“Tiefbauamt“)

oder

=ERSETZEN(B34;1;99;“Tiefbauamt“)

Excel_Ersetzen3

Als Anzahl_Zeichen wird hier ohne zu zählen, 99 angenommen.

Wenn gezählt werden soll, wird zusätzlich die Funktion LÄNGE genutzt.

=ERSETZEN(B34;1;LÄNGE(B34);“Tiefbauamt“)

Excel_Ersetzen4

2. Ersetzen von Werten in einem Bereich

Angenommen, eine Tabelle enthält in einer Spalte mehrfach die Jahreszahl „2016“, die durch „2017“ ersetzt werden soll.

Excel_Ersetzen5

Markiere den Bereich B58:B65. Gehe im Menü Start / Bearbeiten auf „Suchen und Auswählen“, gebe als „Suchen nach“ den Text „2016“, als „Ersetzen durch“ den Text „2017“ ein und klicke auf „Alle ersetzen“.

Excel_Ersetzen6

Es folgt diese Mitteilung:

Excel_Ersetzen7

Im Bereich B58:B65 steht nun „2017“.

Excel_Ersetzen8

3. Ersetzen von Werten in einer Spalte oder Zeile

Angenommen, die Spalte M enthält unregelmäßig die Jahreszahl „2016“, die durch „2017“ ersetzt werden soll.

Markiere dazu die Spalte am oberen Rand (M).

Gehe im Menü Start / Bearbeiten auf „Suchen und Auswählen“. Gebe unter „Suchen nach“ das Jahr „2016“ und unter „Ersetzen durch“ das Jahr „2017“ ein. Klicke auf „Alle ersetzen“.

Alle Werte in Spalte M, die vorher die Jahreszahl 2016 enthielten, enthalten jetzt die Jahreszahl 2017.

Willst du Werte in einer gesamten Zeile ersetzen, klicke am linken Rand auf die Zeilenzahl und verfahre ebenso.

Mehrspaltige und mehrzeilige Bereiche bearbeitest du analog.

Rangfolgebestimmung bei doppelten Werten

Ein allgemeiner Fall

Wenn in Berichten eine Bewertung von mehreren Ergebnissen untereinander vorgenommen werden soll, wird oftmals mit der Excel-Funktion RANG (bis Excel 2007) bzw. RANG.GLEICH (ab Excel 2010) gearbeitet.

So kann es aber vorkommen, dass mehrere Ergebnisse gleichlautend sind. Die RANG-Funktion gibt dann den höheren Rang aus. Haben z.B. zwei Ergebnisse den Rang 3, wird der 4. Rang übersprungen.

Ein kleines Beispiel:

Rangfolge1

Der Rang wurde mit dieser Formel berechnet:

=RANG.GLEICH(C7;$C$6:$C$12;0)

Du siehst, Rang 4 ist doppelt, Rang 5 gar nicht vorhanden.

Üblicherweise wird in der Praxis mit einer Hilfsspalte dem Umsatz ein sehr kleiner Wert hinzugefügt. Den kannst du mit

=ZEILE()/1000000

berechnen.

Rangfolge2

Den Rang 2 errechnest du dann mit:

=RANG.GLEICH(E6;$E$6:$E$12;0)

Nun sind alle Ränge von 1 bis 7 vorhanden, es gibt keine Dopplungen mehr.

Versehe jetzt den Umsatz noch mit dem Zahlenformat #.##0

Wenn ein zweites Kriterium vorhanden ist

Für die Fußballvereine, egal welcher Liga, und für die Fußballfans ist die Abschlusstabelle schon fast ein Heiligtum.

In einem früheren Beitrag habe ich eine Excel-Variante zum Führen einer solchen Tabelle für die 1. Bundesliga schon einmal vorgestellt. Der letzte Schritt bei allen Berechnungen war die Bestimmung des Ranges eines jeden Vereins.

In diesem Beitrag soll es weniger um Fußball, von dort kommt nur dieses geeignete Beispiel, sondern mehr um die Excel-Funktion RANG.GLEICH gehen.

Ganz wichtig dabei ist, wie der Rang bestimmt werden kann, wenn zwei Mannschaften zunächst gleichrangig sind.

Als Beispiel nutze ich die „Abschlusstabelle der 1. Bundesliga – Saison 2016 / 2017“, die ich bei BuLi-Box [1] gefunden habe.

Ausgehend von der anfänglichen Mannschaftsaufstellung für die Saison habe ich die Punkte entnommen und hier zusammengestellt:

Rangfolge3

Zu sehen ist, dass es zwei 5., zwei 8. , zwei 13. und zwei 15. Plätze gibt. So ist das nicht gewollt, ich will nur eindeutige Werte von 1 bis 18 haben.

Was kann getan werden?

Die Regeln der Liga besagen, dass zur Rangbestimmung bei Punktgleichheit zunächst noch die Tordifferenz ( geschossene abzgl. empfangene Tore) zu berücksichtigen ist.

Ich entnehme deshalb aus der Abschlusstabelle die Tordifferenzen und füge sie in meine Tabelle ein.

Dann bilde ich jeweils eine Summe aus Punkte und Tordifferenz, für die ich dann den Rang bestimmen will.

Rangfolge4

Die Summe errechne ich mit der Formel:

=C3+(E3/100)

wobei ich den Tordifferenzen durch die Division durch 100 eine geringere Gewichtung gebe, denn zuerst entscheiden die Punkte, dann die Differenzen.

Schließlich füge ich die Spalte Rang 2 hinzu und rechne mit der Formel

=RANG.GLEICH(F3;$F$3:$F$20;0)

die ich bis F20 herunter ziehe, die Ränge.

Zum Schluss erstelle ich noch die Abschlusstabelle, die mit den Angaben zu Rang, Mannschaft, Punktezahl und Tordifferenz, die endlich so aussieht:

Rangfolge5

Die Spalte Rang erstelle ich zuerst manuell und hole die zugehörigen Inhalte mit der Funktion BEREICH.VERSCHIEBEN aus der vorangegangenen Tabelle.

Jetzt entsprechen die Rangzahlen der „amtlichen“ Abschlusstabelle von BuLi-Box.

Quellen:

[1] http://www.bulibox.de

Prüfung, ob vertikale und horizontale Summen identisch sind

Enthält eine Tabelle mehrere Spalten und mehrere Zeilen, Zeilensummen und Spaltensummen sowie eine Gesamtsumme, vertraue ich oftmals darauf, alle Formeln zur Summierung richtig eingetragen zu haben.

Wie darüber die Kontrolle behalten werden kann, ist Gegenstand dieses Beitrages.

Die folgende Tabelle zeigt beispielsweise die produzierten Mengen aus sechs Werken für die ersten sechs Monate eines Jahres.

Spaltensumme1

Die Zellen B11:G11 sollten richtigerweise die Mengen der Werke 1 bis 6 addieren.

Die Zellen H5:H10 sollten ebenso richtig die Mengen der Monate Januar bis Juni addieren.

Und schließlich soll in Zelle H11 entweder die Summe von B11:G11 oder die Summe der Werte aus H5:H10 gebildet werden.

Die Summe aus B11:G11 und die Summe aus H5:H10 müssen identisch sein. In H11 kann aber nur eine Formel eingetragen werden. So kann es durchaus passiert sein, das irgendeine Summe einen Wert aus der Spalte oder aus der Zeile „vergessen“ hat. Ein solcher Fehler ist aber nicht sofort sichtbar.

Wie lässt sich im Arbeitsblatt eine Kontrolle darüber einflechten?

Eine Möglichkeit ist, irgendwo auf dem Arbeitsblatt, z.B. in I11, eine der folgenden Formeln einzutragen:

  • =WENN(SUMME(H5:H10)=SUMME(B11:G11);“OK“;“Fehler“)
  • =IDENTISCH(SUMME(H5:H10);SUMME(B11:G11))
  • =SUMME(H5:H10)=SUMME(B11:G11)

Die zweite und dritte Formel geben als Ergebnis ein „WAHR“ oder ein „FALSCH“ heraus.

Darüber hinaus kann durch eine bedingte Formatierung die Zelle H11 manipuliert werden, so dass sofort ersichtlich ist, ob die Summenformeln richtig sind.

bedingte Formatierung in H11

neue Regel / Formel: =SUMME(H5:H10)<>SUMME(B11:G11)

formatieren / ausfüllen: rot

und als zweite Formel:

bedingte Formatierung in H11

neue Regel / Formel: =SUMME(H5:H10)=SUMME(B11:G11)

formatieren / ausfüllen: grün

Ergänzend kann unterhalb von H11, z.B. in H13, noch eine kurze Erläuterung des Fehlers konstruiert werden. Dazu kannst du diese Formel verwenden:

=WENN(SUMME(H5:H10)<SUMME(B11:G11);“Zeilensumme ist kleiner“;WENN(SUMME(B11:G11)<SUMME(H5:H10);“Spaltensumme ist kleiner“;“OK“))

Folgende Tabelle enthält in der Summe des Monats April (gelb markiert) einen Fehler. Und der wird angezeigt:

Spaltensumme2

Wenn ein Fehler vorhanden ist und dies auch angezeigt wird, müssen die Zeilen-, Spalten- und Gesamtsummen nur überprüft und korrigiert werden. Dann ist H11 wieder grün.

Runden oder nicht runden?

Wer kennt das Problem nicht? In einem Bericht werden die Zahlenwerte mit zwei Nachkommastellen dargestellt. Im Bericht selbst werden aber Prozentwerte berechnet oder Konstanten wie die Kreiszahl Pi oder die Eulersche Zahl e bei Rechenoperationen verwendet. Die Ergebnisse haben dann in der Regel mehr Nachkommastellen als zwei.

Die gezeigten Endergebnisse sind ohne Zweifel richtig errechnet. Aber wenn ein Kollege oder ein Vorgesetzter horizontal und vertikal die sichtbaren Ergebnisse mit dem Taschenrechner nachrechnen, passt irgendetwas meistens nicht.

Ein Beispiel soll das verdeutlichen:

runden1

Formeln:

D6   =WENN(UND(C6>180000;C6<200000);0,03; WENN(UND(C6>200000;C6<220000);0,05; WENN(C6>220000;0,07;““)))

E6   =WENN(D6<>““;C6*D6;““)

E13   =SUMME(E6:E12)

Dabei gelten diese Regeln:

Umsatz >220000   Provision 7%
Umsatz >200000   Provision 5%
Umsatz >180000   Provision 3%

Je nachdem, welche der Regeln auf einen Verkäufer zutrifft, wird der erreichte Umsatz mit dem Prozentsatz multipliziert und die Provision mit zwei Nachkommastellen ausgewiesen, eben so, wie dann die Auszahlung erfolgen wird.

Nun verändere das Zahlenformat für die Provisionen derart, dass vier Nachkommastellen gezeigt werden.

runden3

Zu sehen ist, dass jeweils eine dritte Nachkommastelle errechnet wurde.

Addierst du die Werte so, wie sie zu sehen sind, also 16.895,515+5.957,364 usw., erhältst du das Ergebnis 65.689,971, so wie es jetzt in der Tabelle zu sehen ist.

Addierst du aber die Werte so, wie du sie in der ersten Tabelle siehst, also 16.895,52+5.957,36 usw., lautet die Summe 65.689,98. In E13 steht aber 65.689,97.

Fest steht, dass Werte gerundet werden müssen, aber welche?

Es könnten die einzelnen Positionen oder die Summe der Provisionen gerundet werden.

Zuerst rundest du die einzelnen Provisionen:

runden4

Die Formel in E6 hast du geändert in:

=WENN(D6<>““;RUNDEN(C6*D6;2);““)

Das Zahlenformat kann jetzt wieder auf zwei Nachkommastellen reduziert werden und das Ergebnis 65.689,98 entspricht nun auch dem Additionsergebnis aus den sichtbaren Werten in der ersten Tabelle.

Du willst noch die zweite Variante beim Runden ausführen und nur die Summe runden.

runden5

Die Formeln lauten:

In E89   =WENN(D6<>“;C6*D6;““)

In E13   =RUNDEN(SUMME(E6:E12);2)

Zu sehen ist, dass in diesem Beispiel eben der Wert erscheint, der schon ursprünglich zu sehen war. Er ist nicht exakt.

Fazit:

Wird mit Prozenten oder Konstanten wie MWSt-Satz, Pi oder e gerechnet, sollten sinnvollerweise die unmittelbaren Einzelergebnisse auf die Anzahl Nachkommastellen gerundet werden, die sichtbar sein soll.

Das Argument „Anzahl_Stellen“ in der Rundungsformel muss der Anzahl der Nachkommastellen gemäß Zahlenformat entsprechen. Die Regel ist, dort zu runden, wo die Abweichungen entstehen.

Dies ist nun die korrekte Tabelle:

runden6