Wie du Werte mit VBA ersetzen kannst

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.

Excel_Ersetzen5          Excel_Ersetzen8

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.

VBA_Ersetzen1

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:

VBA_Ersetzen2

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.

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.

So erstellst du in Excel ein Thermometerdiagramm

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“

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

Wie du in Pivottabellen den Beschriftungsfilter einsetzt

Eine Pivottabelle erstellen

Um eine Pivottabelle erstellen zu können, ist zunächst eine Datensammlung in einer Datenbank erforderlich.

Wie es dann weiter geht, habe ich in meinem Beitrag zu Absatzmengenplanung gezeigt.

In diesem Beitrag will ich dir die Funktionsweise des Beschriftungsfilters erklären.

Zunächst erstellst du die Datenbank für deine Pivottabelle. Darin enthalten sind Informationen zur Region, zum Filialort, zum Produkt sowie zugehörige Mengenangaben.

PivotSchrift1

Daraus erstellst du eine Pivottabelle. Du erinnerst dich?

Menü Einfügen / Tabellen / PivotTabel / PivotTable

Der Berichtsfilter soll die Region enthalten. In die Zeilenbeschriftungen schiebst du die Orte, in die Spaltenbeschriftungen die Produkte. Der Wertebereich schließlich soll die Mengen enthalten.

PivotSchrift2

Auf eine Spalte Gesamtergebnis kannst du in der Zeile verzichten, denn es macht keinen Sinn, Betten und Stühle zu addieren. Klicke dazu mit der rechten Maustaste in die Pivottabelle und entferne in den PivotTable-Optionen den Haken unter Summen & Filter.

Wie gewohnt, kannst du mit den Filtern der Pivottabelle nun Regionen, Orte und Produkte auswählen.

Einen Beschriftungsfilter setzen

Der Beschriftungsfilter ermöglicht es dir zusätzlich, z.B. nur alle Orte anzuzeigen, die ein „o“ enthalten. Klicke dazu auf den Pfeil neben Zeilenbeschriftungen.

PivotSchrift3

Wähle dort „Enthält“ und gib ein „o“ ein.

PivotSchrift4

Bestätige mit OK und die Pivottabelle zeigt nur noch diese Daten:

PivotSchrift5

Alle weiteren möglichen Varianten siehst du im Screenshot oben.

 

Sparklines, die kleinen Diagramme in Excel

Was sind Sparklines?

Der Google-Übersetzer übersetzt das Wort „sparkline“ vom Englischen ins Deutsche mit „Sparkline“, „spark line“ aber mit „Funkenlinie“.

Das hilft noch nicht allzu viel, um zu erklären, was Sparklines sind. Aber es gibt die Excel-Hilfe. In diesem Fall gebe ich gern zu, dass ich hier gute Erläuterungen gefunden habe.

Sparklines sind danach sehr kleine Diagramme im Zellenhintergrund eines Arbeitsblattes. Dadurch ist es möglich, zusätzlich zur Sparkline auch noch einen Text in der Zelle unterzubringen. Sie sind kleine Objekte, wie die richtigen Diagramme.

Mit Sparklines lassen sich z.B. saisonale Auf- und Abschwünge von Datenreihen darstellen. Es lassen sich erste oder letzte Werte, der höchste oder niedrigste Wert hervorheben u.v.a.m.

Sparklines haben den Vorteil, dass sie klein sind und in einer Zelle untergebracht werden können. Werden sie unmittelbar neben der Wertereihe angeordnet, ist der Zusammenhang mit der Wertereihe unmittelbar erkennbar. Trends der Wertereihen werden so klar dargestellt.

Wie werden Sparklines erstellt?

Folgende Tabelle zeigt die mengenmäßigen Quartalsverkäufe von drei Filialen eines Unternehmens für ein Produkt.

Sparklines1

Sparklines können sowohl für die Zeilen als auch für die Spalten erstellt werde. Du willst dich auf die Zeilen beschränken und die Sparklines unmittelbar in den Zellen rechts der Tabelle unterbringen.  In R4 schreibst du als Überschrift „Entwicklung“, damit wird die Spalte Bestandteil der Tabelle, denn diese ist intelligent.

Gehe nun auf R5 und wähle im Menü Einfügen unter Sparklines z.B. die Linie aus.

Sparklines2

Fülle den entsprechende Datenbereich wie gezeigt aus. Der Positionsbereich enthält bereits einen Eintrag, da du dich in R5 befindest. Nach dem OK befindet sich in R5 ein Sparkline.

Verfahre in R6 und R7 ebenso, die Tabelle sieht nun so aus:

Sparklines3

Auf den ersten Blick siehst du, wie sich die Quartalswerte entwickelt haben.

Wie lassen sich die Sparklines gestalten?

Ein Sparkline ist als Linie erstellt. Berechtigterweise fragst du dich: Ist das alles? Oder was kann ich noch damit machen?

Klicke auf die Zelle mit dem Sparkline und dann in der Menüzeile unter den Sparklinetools auf Entwurf. In der Gruppe „Typ“ findest du die drei Möglichkeiten, ein Sparkline darzustellen:

Sparklines4

Die Gruppe „Formatvorlagen“ zeigt dir mögliche Vorlagen, die sich aber nur farblich unterscheiden.

Da die Dateireihe für die Beispiel-Sparkline auch eine negativen Wert enthält, ist es sinnvoll, eine Achse einzufügen. Ruf dazu in der Gruppe „Gruppieren“ das Menü „Achse“ auf und klicke „Achse anzeigen“ an.

Die Sparklines sehen dann so aus:

Sparklines5

Interessant ist die Gruppe „Anzeigen“. Darin kannst du bestimmte Punkte durch Hakensetzung markieren, so den Höchstpunkt, den Tiefpunkt, negative Werte, den ersten oder letzten Punkt und mit „Markieren“ alle Punkte in der Liniendarstellung.

Sparklines6

In der Gruppe „Formatvorlagen“ findest du noch Menüs zur Auswahl der Sparkline- bzw. der Datenpunktfarbe.

In der Gruppe „Sparkline“ gibt es ein Menü „Daten bearbeiten“. Darin kannst du den Quelldatenbereich für ein Sparkline neu definieren.

Schließlich gibt es in der Gruppe „Gruppieren“ das Menü „Löschen“, mit dem du ausgewählte Sparklines oder Sparklinegruppen löschen kannst.

Was noch fehlt?

Du kannst die Zelle, in der sich ein Sparkline befindet, wie jede andere Zelle formatieren. Zumindest kann ein Rahmen gesetzt und eine Ausfüllfarbe ausgesucht werden.

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.