Tabellenkalkulation, das rechnet sich!
Die drei Funktionen gehören zur Funktionskategorie „Nachschlagen und Verweisen“.
ZEILE und SPALTE sind die ersten beiden Argumente der Funktion ADRESSE.
Aus ADRESSE wiederum lassen sich die Zeilen- und Spaltennummern herauslesen.
Weiterlesen „So verwendest du die Excel-Funktionen ZEILE, SPALTE, ADRESSE“
Geometrische Objekte sind in diesem Blog nichts Neues. Du erinnerst dich vielleicht an die Kreis- und Dreiecksberechnungen.
Dieser Beitrag zeigt Berechnungen an der Kugel, d.h. an der Vollkugel und an der Halbkugel. Wie auch schon bei den Berechnungen am Kreis, erfolgt die Berechnung mit dem Radius und die Berechnung mit dem Durchmesser.
Quelle: https://de.wikipedia.org/wiki/Kugel
Mit der bedingten Formatierung kannst du einige Effekte für deine Berichte oder Planungen erzielen, das hast du in vorangegangenen Beiträgen auf diesem Blog sehen können.
In diesem Beitrag geht es um eine weitere Möglichkeit. Es sollen nur obere oder untere Werte hervorgehoben werden.
Weiterlesen „Bedingte Formatierung: Nur obere oder untere Werte formatieren“
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.
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.
Daraus erstellst du zunächst eine sortierte Urliste, die alle gemessenen Werte entsprechend der Anzahl der Striche enthält.
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.
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.
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.
Die vorliegende Verteilung ist näherungsweise erkennbar, Normalverteilung mit Glockenkurve.
Verwende die Klassentabelle aus Abschnitt 2 mit den oberen Intervallgrenzen.
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.
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:
Klicke OK und fülle die Maske aus:
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:
Du hast jetzt über das Add-In die Klasseneinteilung mit den Häufigkeiten und ein Histogramm generiert.
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:
Der Beitrag in der letzten Woche beschäftigte sich mit dem Ersetzen von Werten in Excel.
Mit dem heutigen Beitrag zeige ich dir das Ersetzen von Werten mit einem Makro in VBA. Dabei verwende ich die gleichen Beispiele wie im vorangegangenen Beitrag.
Im ersten Beispiel sollte „Düssel“ im Wort „Düsseldorf“ durch „Deggen“ ersetzt werden. „Düsseldorf steht z.B. in Zelle B119.
Das Makro dazu ist dieses:
Sub Ersetzen1()
Cells(121,2).Formula=“=REPLACE(B119,1,6,““Deggen““)“
End Sub
In VBA wird für das Ersetzen die Methode REPLACE verwendet. Das Makro nimmt das Wort aus B119 (Düsseldorf), ersetzt die Zeichenfolge ab dem 1. Zeichen, insgesamt 6 Zeichen, durch das Wort „Deggen“ und trägt den neuen Begriff (Deggendorf) in B121 ein.
In einem zweiten Beispiel soll die Jahreszahl 2016 im abgebildeten Bereich durch 2017 ersetzt werden.
Die Umsetzung realisierst du mit diesem Makro:
Sub Ersetzen2()
Dim rngZelle As Range
For Each rngZelle in Range(„B58:B65“)
rngZelle.Value=REPLACE(rngZelle.Value,“2016″,“2017″)
Next rngZelle
End Sub
Das Makro deklariert die Variable rngZelle. Mit einer For-Next-Schleife werden die Zellinhalte des Bereiches durch „2017“ ersetzt.
Das dritte Beispiel sieht ähnlich aus. Du markierst den Bereich B58:B65, Zellinhalt ist die Jahreszahl 2016. Mit diesem Makro wandelst du die Zahl 2016 in 2017 um:
Sub Ersetzen3()
Dim rngZelle As Range
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,“2016″,“2017″)
Next rngZelle
End Sub
Was ist anders als im zweiten Beispiel? Richtig, du ersetzt nicht die Werte in einem definierten Bereich, sondern in einem selektierten Bereich.
Im vierten Beispiel gibst du die alte und die neue Jahreszahl in separaten Zellen vor.
Und dies ist das Makro dazu:
Sub Ersetzen4()
Dim rngZelle As Range
Dim strAlt As String
Dim strNeu As String
strAlt=Range(„E82“)
strNeu=Range(„E83“)
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,strAlt,strNeu)
Next rng>Zelle
End Sub
Beachte bitte, dass auch hier wieder auf den selektierten Bereich abgestellt wurde.
Im fünften Beispiel verwendest du eine Inputbox zum Abfragen der alten und der neuen Jahreszahl.
Sub Erstezen5()
Dim rngZelle As Range
Dim strAlt As String
Dim strNeu As String
strAlt=Inputbox(„altes Jahr“,,“2016″)
strNeu=Inputbox(„neues Jahr“,,“2017″)
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,strAlt,strNeu)
Next rngZelle
End Sub
In einem letzten Beispiel geht es darum, die Jahreszahl „2015“ durch „2016“ und die Jahreszahl „2016“ durch „N.N.“ zu ersetzen. Das folgende Bild zeigt links den Zustand vor dem Makro und rechts danach:
Mit diesem Makro setzt du dein Vorhaben um:
Sub Ersetzen6()
Dim rngZelle As Range
For Each rngZelle in Range(„B93:B100“)
If rngZelle.Value=“2016″ Then
rngZelle=REPLACE(rngZelle.Value,“2016″,“N.N.“)
End If
If rngZelle.Value=“2015″ Then
rngZelle=REPLACE(rngZelle.Value,“2015″,“2016″)
End If
Next rngZelle
End Sub
Warum ersetzt du zuerst die Zahl „2016“? Bedenke, wenn du zuerst „2015“ durch „2016“ ersetzt, steht in jeder Zelle des Bereiches „2016“. Diese Zahl ersetzt du dann mit „N.N.“ Das Ergebnis wäre, dass in allen Zellen „N.N.“ steht.
Also ersetzt du zuerst „2016“ durch „N.N.“ und dann „2015“ durch „2016“.
Bei ganzen Spalten oder ganzen Zeilen gehst du analog vor.
Was machst du mit der Funktion ERSETZEN bzw. REPLACE? Lasse es uns in einem Kommentar wissen.
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“)
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.
Thermometerdiagramme sind kein Standard in Excel. Wenn du ein solches in deinen Berichten oder Dashboards zeigen willst, musst du ein wenig tricksen.
Anhand eines Beispiels zeige ich dir, wie du Schritt für Schritt vorgehen kannst.
Weiterlesen „So erstellst du in Excel ein Thermometerdiagramm“
