Zelleninhalt

Zellen mit Hyperlinks erkennen und hervorheben

Roman Formeln, VBA (Makros) , , , ,

Excel wird für alle erdenklichen Auswertungen und Datenabgleiche verwendet. In diesem Beitrag stelle ich Dir zwei Möglichkeiten vor, um einen Zelleninhalt auf Hyperlinks zu prüfen.

In vorherigen Veröffentlichungen haben wir mit Formeln wie =ZELLE() oder über Wege mit =ZELLE.ZUORDNEN() Inhalte von Zellen interpretiert. Etwas ähnliches wurde mit bedingten Formatierungen aufgezeigt (z.B. zur Einfärbung einer Zeile, basierend auf deren Inhalte).

Ein Hyperlink kann auf zwei Arten in einer Excel-Tabelle hinterlegt werden: Via der Formel =HYPERLINK() oder durch Eingabe einer Websiten-URL. Den ersten Fall könnte mit der Formel "=ISTFORMEL()" überprüft werden und würde "WAHR" retournieren. Das könnte je nach Datengrundlage für den Auswertungszweck bereits ausreichen. Falls es sich jedoch um einen einfachen Eintrag handelt oder die Datei andere Formeln und Angaben beinhaltet, reicht dieser Ansatz nicht aus. Zur Orientierung habe ich hier eine kleine Beispieltabelle mit unterschiedlichen Fällen:

ZellenMitHyperlinks

Die Spalte A beinhaltet diverse Varianten:

  • Reguläre Texte (Titel), Zellen A2, A5 und A8;
  • Links auf Websiten (ohne Formel "HYPERLINK"!), Zellen A3, A6 und A9;
  • Mittels Formel "=HYPERLINK()" hinterlegter Link, Zelle A4;
  • fälschlicherweise als Link formatierter Text, Zelle A7.

Auslesen von Hyperlinks via Makro (VBA)

Um die eingetragenen Hyperlinks zu identifizieren, benötigen wir Makrounterstützung. In einer For-Each-Schleife gehen wir durch jede Zelle durch; ohne die Tabelle dynamisch (d.h. bei mehr oder weniger Zeileninhalten direkt zu reagieren), setzen wir:

For i = 2 To 9
[Weiterer Code folgt sogleich...]
Next

Damit durchläuft unsere Prozedur die Zeilen 2 bis 9. Innerhalb der For-Each-Schleife prüfen wir, ob (und wie viele) Hyperlinks die aktuelle Zelle der Spalte A (Spalten-Nummer 1) beinhaltet. Dazu verwenden wir:

For i = 2 To 9

If Cells(i, 1).Hyperlinks.Count = 1 Then
Cells(i, 3) = "LINK!"
End If

Next

Sollte also ein Hyperlink in der entsprechenden Zelle eingetragen worden sein, trägt das Makro in Spalte C den Hinweis "LINK!" ein. Der "Count" müsste im Falle eines Links immer 1 ergeben, weshalb die If-Prüfung das gewünschte Resultat liefert. Ich kann mir keinen Fall vorstellen, bei dem mehrere Hyperlinks in der Zelle enthalten sein sollten; ansonsten wäre die Prüfung "[...].Hyperlinks.Count >= 1 Then" sicherer.

Ganze Zeile einfärben (bedingte Formatierung)

Roman Allgemein , , , , , , , ,

Bedingte Formatierungen bieten die Möglichkeit, Zellen basierend auf einem Wert zu formatieren (beispielsweise farblich hervorheben). Im Normalfall wird dies auf eine einzelne Zelle angewandt, um darzustellen, dass ein bestimmter Parameter unter- oder überschritten wurde. Ein konkretes Beispiel zeigt der Beitrag "Einen eingefärbten Kalender erstellen". Symbolsätze bieten die Möglichkeit, Trends und Entwicklungen (Pfeile) oder Ampeln und Indikatoren für den - oder gar anstelle des - entsprechenden Wert anzuzeigen. Dies kann in Übersichten und Berichten spannend sein, um auf einen Blick relevante Gegebenheiten erkennen zu können. Dies sind die Symbolsätze, die man unter dem Menüpunkt "Start" → "Bedingte Formatierung" findet:

bedingteFormatierungSymbols

In diesem Blog zeige ich Dir einen Weg, wie Du nicht nur eine einzelne Zelle hervorheben kannst, sondern sämtliche dazugehörigen Informationen in derselben Zeile werden ebenfalls formatiert. Somit werden erfasste Einträge besser ersichtlich und Du kannst Deine Daten beispielsweise nach Farbe filtern (siehe unten). Für unser Beispiel haben wir einen übersichtlichen Datensatz mit Kundeninformationen:

bedingteFormatierungRohdaten

Wir beabsichtigen nun, sämtliche Kunden farblich hervorzuheben, die über eine Kundenkarte verfügen (Spalte G). Um ein erstes Resultat zu erzielen, klicke in die Zelle "G2" und gehe via "Start" → "Bedingte Formatierung" auf "Regeln zum Hervorheben von Zellen" und dort auf "Textinhalt...":

bedingteFormatierungTextinhalt

Damit wird der bestehende Inhalt ("Ja") standardmässig rot hervorgehoben. Mit "OK" lässt sich die Auswahl bestätigen. Man kann jedoch die Formatierung beliebig anpassen, indem man in der Auswahl auf der rechten Seite "benutzerdefiniertem Format..." auswählt. Die Schritte da sind gleich, wie wenn man eine normale Zelle formatiert. Wenn Du die Auswahl bestätigt hast, gehe erneut auf "bedingte Formatierung" im Menüband und wähle dieses Mal zu unterst "Regeln verwalten...". Du siehst diese Option auch im Screenshot oben, wo wir "Regeln zum Hervorheben von Zellen" gefolgt von "Textinhalt..." angeklickt haben. Nun siehst Du die eine Regel, die Du erstellt hast. Gehe oberhalb des einzelnen Eintrags auf "Regel bearbeiten" und wähle anstelle von "Nur Zellen formatieren, die enthalten" die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus. Nun kannst Du weiter unten eine Formel/Kondition eingeben, um bedingte Formatierung auszulösen. Verwende nachfolgende Formel:

=$G2="Ja"

(Das Dollarzeichen ist notwendig, um die Formatierung auf andere Spalten zu übertragen, aber dass die Spalte G und der Wert darin nach wie vor die Bedingung vorgibt.)

bedingteFormatierungFormatierungsregel

Bestätige die Formel und folglich die neue Formatierungsregel mit "OK". In der Übersicht passe nun noch den Bereich der Regel an -  dies ist die Spalte mit der Überschrift "Wird angewendet auf". Wie erweitern da den Bereich auf den gesamten Datenbereich (A2 bis G5). Dies führt dazu, dass alle Werte darin eingefärbt werden, sofern der jeweilige Wert in Spalte G "Ja" entspricht. Weitere Spalten rechts davon (z.B. H und fortfolgende) sind davon nicht betroffen. Damit der Bereich erweitert wird, gib folgende Formel an (die Dollarzeichen sind auch hier zwingend notwendig!):

=$A$2:$G$5

bedingteFormatierungRegelManager

Du solltest die Änderungen bereits aktiv sehen, wenn Du rechts unten auf "Übernehmen" klickst. Du kannst aber auch direkt auf "OK" drücken, um den Regel-Manager zu schliessen. Nun hast Du zwei Einträge komplett eingefärbt, weil der jeweilige Kunde über eine Kundenkarte verfügt:

bedingteFormatierungResultat

Resultate filtern (Farbfilter)

Einleitend habe ich erwähnt, dass das Resultat nun aufgrund der bedingten Formatierung deutlich besser ersichtlich ist. Das Ergebnis ist auch ansprechender, als wenn nur die Zelle in der Spalte G eingefärbt wäre. In jeder beliebigen Spalte kann nun nach der Farbe gefiltert werden, um nur die eingefärbten Zeilen anzuzeigen:

bedingteFormatierungFarbfilter

Zählen eines bestimmten Zeichens innerhalb einer Zelle

Roman Formeln , , , , , , , , ,

Mit den Formeln "ANZAHL" und "ZÄHLENWENN" lässt sich die Anzahl bestimmter Werte über mehrere Zellen hinweg ermitteln. Möchte man jedoch ein bestimmtes Zeichen innerhalb einer einzelnen Zelle zählen, benötigt das eine etwas andere Kombination von Formeln. Angenommen in Zelle "A1" steht unser Text:

ZaehlenTextfolge1

Wir möchten nun zählen, wie oft das Zeichen "e" darin vorkommt. Im untenaufgeführten Bildausschnitt wird dazu eine Hilfszelle (D1) verwendet. Die effektive Formel wird in der Zelle "D2" angewendet und ergibt im Falle des Textes "Alles Gute im Jahr 2018!" zwei. Die Formel lautet wie folgt:

=LÄNGE(A1)-LÄNGE(WECHSELN(A1;D1;""))

ZaehlenTextfolge2

Die oben genannte Formel macht folgendes: Sie zählt einerseits die Länge (Anzahl Zeichen) im Text. Mit dem Teil "WECHSELN(...)" wird innerhalb des Textes jedes "e" durch nichts ("") ersetzt und erneut die Länge (Anzahl Zeichen ohne "e") ermittelt. Die Differenz daraus ergibt demnach die Anzahl "e" innerhalb der Textfolge.

Alternativ könnte anstatt einer Hilfszelle (D1) auch einfach ein Wert eingegeben werden. Möchte man beispielsweise die Leerzeichen zählen (=4), wendet man folgende Formel an:

=LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))

D1 wurde hier durch " " ersetzt.