Excel-Minis 20171128

Excel-Minis sind an dieser Stelle immer Kurzbeiträge, natürlich zum Thema Excel.

Für heute habe ich drei kleine Themen vorbereitet, vielleicht hast du Spaß damit.

Weiterlesen „Excel-Minis 20171128“

Advertisements

Versuchs doch mal mit INDIREKT

1. Beschreibung der Funktion

Die Excel-Funktion INDIREKT habe ich schon in vielen Beiträgen eingesetzt, sie aber bisher nicht erläutert.

INDIREKT ist im Funktionskatalog von Excel unter der Kategorie „Nachschlagen und Verweisen“ zu finden.

Die Funktion kann nützlich sein, wenn in Dateien mit vielen Arbeitsblättern dynamisch auf bestimmte Zellen oder Zellbereiche in den Blättern zugegriffen werden muss.

Doch Achtung! INDIREKT ist eine volatile Funktion. Peter Haserodt von Online-Excel schrieb einmal dazu [1]:

Indirekt sollte nur sehr sparsam angewendet werden und nicht bei rechenintensiven Formeln. Diese Funktion hat nämlich den großen Nachteil, dass sie volatil ist, d.h. sie wird immer neu berechnet, nach jeder Eingabe. Selbst dann, wenn sie in einer anderen geöffneten Arbeitsmappe steht!

Wichtig zu wissen ist auch, dass mit INDIREKT nur auf geöffnete Dateien zugegriffen werden kann.

Was schreibt die Excel-Hilfe?

Hier heißt es, INDIREKT „Gibt den Bezug eines Textwertes zurück. Bezüge werden sofort ausgewertet, so dass die zu ihnen gehörenden Werte angezeigt werden.“

Die Syntax lautet:

=INDIREKT(Bezug;[A1])

Bezug ist eine erforderliches Argument, möglich ist der Bezug auf eine Zelle, die einen Bezug in der A1-Schreibweise, einen Bezug in der Z1S1-Schreibweise, einen definierten Namen als Bezug oder einen Zellbezug als Zeichenfolge enthält.

Das Argument A1 kann optional eingesetzt werden. Es ist ein Wahrheitswert, der angibt, welcher Art der Bezug in der Zelle ist.

Ist A1 gleich WAHR, liegt die A1-Schreibweise vor.

Ist A1 gleich FALSCH, liegt die Z1S1-Schreibweise vor.

2. Beispiele

Beispiel 1: Wert einer Zelle im gleichen Arbeitsblatt abfragen

Die nachfolgende Tabelle (Arbeitsblatt Tabelle2) zeigt 12 Monate und Umsatzgrößen.

Indirekt1

Mit INDIREKT willst du den Umsatz des Monats, der in Zelle F2 angegeben ist, in Zelle F4 zeigen. Du schreibst in Tabelle2 in F4 die Formel

=INDIREKT(„C“&$F$2+2)

Warum das „+2“? Weil die Monate erst in Zeile 3 beginnen. Der dritte Monat befindet sich in Zeile 5.

Änderst du den Eintrag in F2, ändert sich das Ergebnis in F4 entsprechend.

Beispiel 2: Wert einer Zelle in einem anderen Arbeitsblatt abfragen

Angenommen, die Tabelle steht im Arbeitsblatt „Tabelle2“, das Ergebnis soll im Blatt „Tabelle1“ gezeigt werden.

Dann schreibst du in F4 die Formel:

=INDIREKT(„Tabelle2!C“&Tabelle1!F2+2)

und erhältst den gesuchten Umsatz.

Beispiel 3: Summen von Werten abfragen

In diesem Beispiel suchst du die Summe über mehrere Monate des Jahres, z.B. von März (3) bis Mai (5). Die Tabelle befindet sich wieder in „Tabelle2“, das Ergebnis soll ebenfalls in „Tabelle1“ gezeigt werden.

Indirekt2

Verwende diese Formel in F5:

=SUMME(INDIREKT(„Tabelle2!C“&Tabelle1!F2+2):INDIREKT(„Tabelle2!C“&Tabelle1!F3+2))

Wenn dir die Zelladressen der jeweiligen Monate bekannt sind, kannst du auch so vorgehen:

Indirekt3

Schreibe dazu in F4 die Formel:

=SUMME(INDIREKT($F$2))

Beispiel 4: Wert aus einem namentlichen Bereich abfragen

Für dieses Beispiel benennst du den Bereich B3:B14 mit „Monat“ und den Bereich C3:C14 mit „Umsatz“.

Du willst in Tabelle2 in F5 z.B. den Umsatz des Monats März sehen.

Schreibe:

=INDEX(Umsatz;INDIREKT(„F2“);1)

Die Formel macht allerdings nicht viel Sinn, zeigt aber die Funktionsweise von INDIREKT.

Einfacher wäre es so:

=INDEX(Umsatz;F2;1)

Beispiel 5: Summe von Werten eines namentlichen Bereiches abfragen

Willst du nicht nur den Umsatz eines Monats sehen, sondern z.B. die Summe von Mai bis August, kannst du diese Formel verwenden (in F2 steht eine 5, in F3 steht eine 8).

=SUMME(INDEX(Umsatz;INDIREKT(„F2“);1):INDEX(Umsatz;INDIREKT(„F3“);1))

Beispiel 6: Mit INDIREKT auf einen durch Verkettung erzeugten Bereichsnamen zugreifen

Dir liegen die monatlichen Umsätze aus drei Jahren vor. Du vergibst Namen für die Bereiche:

  • C5:C16 Umsatz2014
  • F5:F16 Umsatz2015
  • I5:I16 Umsatz2016

Indirekt4

Für die Abfrage hast du diese Felder vorbereitet:

Indirekt5

In C19 schreibst du das Jahr, in C21 den Monat deiner Wahl.

C23 enthält die Formel

=“Umsatz“&C19

In C25 schreibst du schließlich die Formel

=INDEX(INDIREKT(C23);C21;1)

In C25 kannst du jetzt den Umsatz für den 6. Monat des Jahres 2014 ablesen.

INDIREKT hast du dazu verwendet, den Namen der Matrix, der in C23 durch Verkettung erzeugt wurde, in die INDEX-Formel einzugeben.

Änderst du den Eintrag in C19 auf 2015, erhältst du den Umsatz für Juni 2015. Das Spiel kann beliebig weiter geführt werden.

Ein ausführliches Beispiel findest du u.a. in meinem Beitrag „Arbeiten mit der rS1-Methode – Namenskonventionen und Beispiel„.

Beispiel 7: Die verschiedenen Schreibweisen

In der A1-Schreibweise suchst du z.B. den Umsatz, der in Zelle C10 steht.

Schreibe in Zelle F2 somit „C10“ hinein.

Indirekt6

Der Umsatz soll in Zelle F4 angezeigt werden. Schreibe dort diese Formel:

=INDIREKT(F2;WAHR)

Das Argument WAHR deutet auf die A1-Schreibweise hin, als „C10“.

Mit der Formel wird nun indirekt auf den Inhalt der Zelle C10 zugegriffen. In C10 wird der Wert 15.464 gefunden.

Mit der Z1S1-Schreibweise werden relative Bezüge hergestellt. Schreibe in F4 diese Formel:

=INDIREKT(„Z10S3“;FALSCH)

FALSCH weist auf die Z1S1-Schreibweise hin. Mit der Formel wird nun auf den Inhalt der Zelle in Zeile (Z) und Spalte (S3) zugegriffen. Dort steht, natürlich, der Wert .

Ebenso könntest du die Zelle F2 statt mit „C.“ jetzt mit „ZS3“ füllen.

Indirekt7

Schreibe in F4 diese Formel:

=INDIREKT(F2;FALSCH)

und du erhältst wieder das Ergebnis 15.464.

[1] http://www.online-excel.de/excel/singsel.php?f=24

Summe unter Bedingungen bei nichtnumerischen Werten

 

Einleitung

Angeregt durch die Anfrage eines Lesers, die ich zunächst nicht beantworten konnte, ist dieser Beitrag entstanden.

In der Anfrage ging es darum, Flugzeiten zu addieren, wenn zwei Bedingungen in einer anderen Spalte eingehalten werden. Das Problem war, dass die Spalte mit den Flugzeiten auch nichtnumerische Werte enthalten sollten, z.B. in Klammern gesetzt oder mit Buchstaben versehen.

(4:00)

4:00 xz

Der Leser wollte das Problem mit der Funktion SUMMENPRODUKT lösen, hatte aber keinen Erfolg damit.

Doch mal meine Versuche im Detail. Als Zahlenformat ist [hh]:mm zu verwenden.

Summen ohne nichtnumerische Werte und zwei Nebenbedingungen

Die Tabelle könnte etwa so aussehen:

Nichtnumerisch1

Nehme ich SUMMENPRODUKT

=SUMMENPRODUKT((B24:B42={„QW“.“LK“})*(C24:C42))

erhalte ich als Ergebnis 52:30

Die entsprechenden Zellen sind blau hervorgehoben.

Nehme ich SUMMEWENN

=SUMMEWENN(B24:B42;{„QW“.“LK“};C24:C42)

erhalte ich als Ergebnis 27:00

Das ist nur die Summe der Zeiten, denen die Bedingung „QW“ zugeordnet ist.

Selbst wenn die Formel mit Strg+Shift+Enter als Matrixformel eingegeben wird, bleibt das Ergebnis. SUMMEWENN ist daher nicht geeignet.

Nehme ich SUMMEWENNS

=SUMMEWENNS(C24:C42;B24:B42;“QW“;B24:B42;“LK“)

erhalte ich das Ergebnis 0.

Das ist logisch, denn die Abfrage verlangt, dass sowohl „QW“ als auch „LK“ als Bedingung erfüllt wird und das ist nicht der Fall.

Versuche ich, das Kriterium_1 mit der ODER-Funktion zu definieren, geht auch das nicht.

=SUMMEWENNS(C24:C42;B24:B42;ODER(„QW“;“LK“))

=0

Nehme ich SUMME in Verbindung mit WENN

={SUMME(WENN(B24:B42={„QW“.“LK“};C24:C42))}

erhalte ich mit 52:30 wieder das richtige Ergebnis. Die Formel ist jedoch mit Strg+Shift+Enter als Matrixformel abzuschließen.

Zusammengefasst ist festzuhalten, dass die Funktionen SUMMENPRODUKT und SUMME in Verbindung mit WENN geeignet sind, Flugzeiten bei zwei Nebenbedingungen zu addieren.

Summen mit nichtnumerischen Werten und zwei Nebenbedingungen

Nichtnumerisch2

Die Flugzeiten wurden in drei Zellen der Tabelle nichtnumerisch gestaltet.

Nehme ich SUMMENPRODUKT

=SUMMENPRODUKT((B85:B103={„QW“.“LK“})*(C85:C103))

erhalte ich als Ergebnis #WERT!

Warum?

Vergleiche ich die Werte in B85:B103 mit den Bedingungen „QW“ bzw. „LK“, ergibt diese Abfrage entweder WAHR oder FALSCH.

Multipliziere ich WAHR oder FALSCH nun mit den nichtnumerischen Werten, wie es SUMMENPRODUKT tut, erhalte ich #WERT!.

Die Summe all dieser Produkte ergibt dann auch #WERT!

In nachstehender Tabelle habe ich das zusammengefasst.

Nichtnumerisch3

Nehme ich SUMMEWENN

=SUMMEWENN(B85:B103;{„QW“.“LK“};C85:C103)

erhalte ich als Ergebnis 14:45

Das ist die Summe aller numerischen Zeiten, für die „QW“ zutrifft, „LK“ wird ignoriert.

Nehme ich SUMMEWENNS

=SUMMEWENNS(C85:C103;B85:B103;“QW“;B85:B103;@LK“)

erhalte ich als Ergebnis 0

Das ist wiederum logisch, denn die Abfrage verlangt, dass sowohl „QW“ als auch „LK“ als Bedingung erfüllt wird, das ist nicht der Fall.

Nehme ich SUMME in Verbindung mit WENN

={SUMME(WENN(B85:B103=“QW“.“LK“};C85:C103))}

=38:00

erhalte ich das richtige Ergebnis.

Die Formel ist wieder mit Strg+Shift+Enter als Matrixformel abzuschließen. Die entsprechenden Zellen sind blau hervorgehoben.

Damit kann die Leserfrage beantwortet werden. Die Lösung kann nicht mit der Funktion SUMMENPRODUKT herbeigeführt werden.

Zu verwenden ist eine Kombination aus den Funktionen SUMME und WENN, wie zum Schluss gezeigt.

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“

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.