Tabellenkalkulation, das rechnet sich!
In einem vorangegangenen 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.
„Die statischen Excel-Funktionen QUANTIL.INKL und QUANTIL.EXKL“ weiterlesenQuantile 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.
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.
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.
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.
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.
„3 Wege, ein Histogramm zu erstellen“ weiterlesenEinzelne 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“)
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“)
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“)
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“)
Angenommen, eine Tabelle enthält in einer Spalte mehrfach die Jahreszahl „2016“, die durch „2017“ ersetzt werden soll.
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“.
Es folgt diese Mitteilung:
Im Bereich B58:B65 steht nun „2017“.
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.
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:
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.
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
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:
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.
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:
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] www.bulibox.de
