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