Zellen auf Formeln überprüfen 16

Artikelbild 141
Ein verborgenes Juwel ermöglicht die ISTFORMEL-Funktion auch in Excel-Versionen vor 2013.
 

In den Excel-Quickies im Dezember hatte ich unter anderem gezeigt, wie man mit Hilfe der bedingten Formatierung und der ISTFORMEL-Funktion Zellen hervorheben kann, die eine Formel enthalten.

Leider hatte diese Lösung einen Haken: Die ISTFORMEL-Funktion gibt es erst seit Excel 2013. Alle Anwender vorheriger Versionen mussten leider mit dem Ofenrohr ins Gebirge schauen.

Solltest auch du eine Excel-Version 2010 oder älter einsetzen, dann zeige ich dir im heutigen Tipp, wie man das auch ohne ISTFORMEL-Funktion elegant hinbekommt.


Dieser sehr clevere Trick stammt jedoch nicht von mir. Ich bin auf der Website von Frank Arendt-Theilen darüber gestolpert, Ruhm und Ehre gebührt also zu 100 % ihm.

Dabei wird auf eine Makro-Funktion aus längst vergangenen Excel-Tagen zurückgegriffen. Aber keine Angst, hier muss niemand Makros oder VBA-Code programmieren, um diesen Trick einzusetzen.

Die Funktion ZELLE.ZUORDNEN

Mit dieser Funktion konnte (und kann) man eine Unmenge an Eigenschaften einer Zelle abfragen.

=ZELLE.ZUORDNEN(Typ; Bezug)

Dabei legt „Typ“ fest, welche Eigenschaft gefragt ist und „Bezug“, welche Zelle einen dabei interessiert. Der Typ „48“ prüft z.B., ob in der angegebenen Zelle eine Formel steht.

Diese mächtige Funktion kann jedoch nicht direkt in eine Zelle eingegeben werden. Stattdessen definiert man über den Namensmanager einen neuen Namen und gibt statt eines Zellbezugs eben die Formel ein. Da wir im zweiten Schritt über die bedingte Formatierung jedoch keine feste Zelle, sondern die jeweils gerade aktive Zelle ansprechen wollen, bedienen wir uns eines kleinen Tricks:

=ZELLE.ZUORDNEN(48; INDIREKT("ZS";FALSCH))

Neuen Namen definieren

Neuen Namen definieren

Zur Erklärung:
In der INDIREKT-Funktion geben wir die Zelladresse in der Z1S1-Schreibweise an. In dieser Schreibweise werden nicht Koordinaten verwendet, sondern Zeilen- und Spaltennummern. So wird aus der Zelle A1 eben Z1S1 und aus Zelle C15 wird Z15S3. Und die oben verwendete Angabe „ZS“ verweist damit auf sich selbst.

Die bedingte Formatierung

Mit dem so erstellten Namen können wir nun eine neue Formatierungsregel anlegen und auf alle Zellen anwenden, die wir auf eine Formel hin untersuchen wollen:

Formatierungsregel anlegen

Formatierungsregel anlegen

Und schon werden alle Zellen farblich hervorgehoben, die eine Formel enthalten:

Die hervorgehobenen Zellen

Die hervorgehobenen Zellen

Einen kleinen Haken…

…gibt es jedoch. Wenn du deine Arbeitsmappe nämlich im aktuellen xlsx-Dateiformat speichern willst, wird dich Excel mit folgender Meldung konfrontieren:

Warnung vor dem falschen Dateiformat

Warnung vor dem falschen Dateiformat

Auch wenn wir keine richtigen Makros geschrieben haben, verwenden wir mit ZELLE.ZUORDNEN eben doch eine Makro-Funktion. Daher bleiben dir beim Speichern nur zwei Möglichkeiten:

  1. das neue xlsm-Format, welches auch Makro-Code enthalten darf
  2. das alte xls-Format mit den entsprechenden Einschränkungen

Eine Kröte musst du also schlucken aber ich hoffe, sie rutscht nicht allzu schwer hinunter.

Wer unabhängig von dem oben gezeigten Tipp mit der ZELLE.ZUORDNEN-Funktion experimentieren möchte, findet hier noch ein paar Tipps sowie eine Übersicht aller Eigenschafts-Typen, die mit der Funktion verwendet werden können (leider ist der Original-Link von Frank Arendt-Theilen nicht mehr verfügbar, mein Leser Jörg Böhmichen hat aber diese Alternative gefunden, danke dafür!)
 
Zum Schluss noch ein kleiner Hinweis in eigener Sache:
Soeben ist mein neues E-Book „SVERWEIS & Co. Verweisfunktionen in Excel“ erschienen.

E-Book SVERWEIS & Co.

Solltest du dich also für die Excel-Funktionen SVERWEIS, WVERWEIS, INDEX und dergleichen interessieren, möchte ich dir mein neues Werk wärmstens ans Herz legen. Hier erfährst du mehr dazu…

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Roswitha Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

16 Gedanken zu “Zellen auf Formeln überprüfen

  • Avatar-Foto
    Michael

    Die Kombination mit INDIREKT ist wirklich eine feine Sache!
    Beim Speichern gibt es noch eine dritte Möglichkeit:
    3) das Binärformat .xlsb mit der Einschränkung, dass die Kompatibilät zu alternativen Office-Produkten verloren geht.
    Das Binär-Format reduziert außerdem die Dateigröße, bei großen Mengen von Daten um bis zu 50%.
    Viele Grüße aus Berlin
    Michael

    • Avatar-Foto
      Martin Weiß

      Hallo Michael,

      danke für diesen guten Hinweis. Die Ersparnis bei der Dateigröße kann ich nur bestätigen!

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Jörg Böhmichen

    Hallo, lieber Martin Weiß,
    auf Deine Webseite http://www.tabellenexperte.de bin ich schon öfter gestoßen. Ich finde Deinen Schreibstil sehr freundlich und unaufgeregt – bestens geeignet, um anderen Lesern „die Angst vor Excel zu nehmen“ – sehr gut!
    In Deinem Artikel „zellen-auf-formeln-ueberpruefen“ verweist Du auf eine Seite von Frank Arendt-Theilen, wo er sich mit der Funktion „Zelle.Zuordnen“ beschäftigt und die verschiedenen Typen (Funktionen) erklärt, die mit dieser Funktion möglich sind. Leider ist diese Seite gesperrt.
    Ich habe aber eine andere Seite, eine Microsoft-Seite gefunden, die diesen Artikel enthält:
    https://answers.microsoft.com/de-de/msoffice/forum/msoffice_excel-mso_other-mso_2013_release/die-excel4-makrofunktion-zellezuordnen/6ee8af02-b52c-45b7-94ef-7f7bb7e45d88
    Vielleicht ändern Sie den Link in Ihrer Webseite auf diese Adresse?

    Freundliche Grüße aus Dresden
    Jörg Böhmichen

    • Avatar-Foto
      Martin Weiß

      Hallo Jörg,

      vielen Dank für das Lob und natürlich für den Hinweis mit dem defekten Link. Ich habe ihn jetzt oben im Artikel geändert.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Leser

    Vielen Dank für den hilfreichen Artikel! Mit diesem Trick sollte es dann ja sicherlich auch möglich sein – anstelle einer bedingten Formatierung – mit der ZÄHLENWENN-Funktion alle Zellen mit Verweisen zusammenzuzählen.

    Viele Grüße aus Dresden!

    P.S.: Kleiner Tipp: die Original-Quelle ist nach wie vor noch über die Wayback-Machine des Internet Archive verfügbar: https://web.archive.org/web/20180428110838/http://www.at-exceltraining.de/index.php/artikel/48-formeln-und-funktionen/203-die-funktion-zellezuordnen.html

    • Avatar-Foto
      Martin Weiß

      Hallo Leser,

      die gute alte Wayback-Machine, die habe ich schon ganz vergessen 🙂
      Danke für den Tipp.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Moritz

    Hallo,

    eine Frage: das geht aber auch mit der Formel „ISTFORMEL()“ oder liege ich da falsch?

    Also sowohl bei der bedingten Formatierung und anderen Anwendungen.
    Vielen Dank für die Website, sie hat mir an anderen Stellen schon mal weiter geholfen.

    Best Grüße
    Moritz

    • Avatar-Foto
      Martin Weiß

      Hallo Moritz,

      ja, das geht natürlich auch mit ISTFOMREL. Der Artikel sollte lediglich eine Lösung für Anwender älterer Excel-Versionen (Excel 2010, Excel 2007) aufzeigen, da die Funktion erst mit Excel 2013 eingeführt wurde.

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Martin Weiß

      Hallo Nils,

      was genau geht denn nicht und welche konkrete Formel nutzt du in der bedingten Formatierung, die nicht funktioniert?

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Nils Haunschild

        Moin,

        ich hatte gestern mit den Namensmanager und =ZELLE.ZUORDNEN(48; INDIREKT(„ZS“;FALSCH)) sowie anschliessendem bedingten Formatieren von Feldern eine Formelpruefung mit Formel experimentiert und dann als xlsm gespeichert.

        Heute morgen sind alle Felder so, wie forher formatiert sind und nicht so, wie es bei „formel=ja“ sein sollten.

        gruß KC

      • Avatar-Foto
        Nils Haunschild

        koennten Sie sich bitte noch einmal melden, ich war so froh, dass es alles funktioniert und am naechsten Tage nicht mehr, das muss doch irgendeinen kompensierbaren Grund haben ? 🙂