Die Power von Excel nutzen und anwenden

Klick den Tipp!


Bilderauswahl

Video - Matrixformeln

excelmx

Video - Dynamische Diagramme

 

excel2010dyd

 

Microsoft Award 2011

Microsoft Community Contributor

Die Funktion ZELLE.ZUORDNEN() Drucken
Artikel - Formeln und Funktionen
Geschrieben von: Frank Arendt-Theilen   

 

Die Excel4-Makrofunktion ZELLE.ZUORDNEN()

Immer wieder treten auch in Excel 2010 Situationen auf, bei denen die offensichtlichen, bordeigenen Funktionen nicht weiter helfen. Hilfe naht durch weniger bekannte, aber dennoch mächtige Funktionen aus einer vergangenen Excel-Welt: Seit der Version Excel4 enthalten alle Versionen von Excel noch die alten Excel4-Makrofunktionen.

Dieser Beitrag beschreibt den Einsatz der Excel4-Makrofunktion ZELLE.ZUORDNEN(). Diese Funktion kann nicht direkt im Tabellenblatt genutzt werden, sondern wird über die Erstellung einer benannten Formel im Namens-Manager (Excel 2003: Dialogfeld Namen definieren) zur Verfügung gestellt.

Hierbei sind keinerlei VBA-Kenntnisse erforderlich!

Zunächst zeige ich Ihnen den grundsätzlichen Einsatz dieser Funktion anhand eines Beispiels und liste anschließend alle Argumente, mit denen die Funktion gesuchte Informationen zurück gibt.


Der Einsatz der Funktion ZELLE.ZUORDNEN() anhand eines Beispiels

Die allgemeine Syntax der Funktion lautet:

ZELLE.ZUORDNEN(Typ;Bezug)


Das Argument Typ ist eine Zahl, die angibt, welche Information die Funktion zurückliefern soll.
Das Argument Bezug ist eine Zelle oder ein Zellenbereich, zu dem Sie Informationen zurück geliefert haben möchten.

  • Wird das Argument nicht angegeben, wird die aktive Zelle angenommen. 
  • Besteht Bezug aus mehreren Zellen, wird die Zelle angenommen, die die linke obere Ecke des ersten von Bezug angegebenen Bereichs bildet.

In dem folgenden Beispiel sollen alle Zellen, die eine Formel enthalten, über die bedingte Formatierung mit einer hellgrünen Hintergrundfarbe versehen werden. Dabei gehen Sie wie folgt in zwei Schritten vor.

1.) Definition einer benannten Formel

  • Öffnen Sie auf der Registerkarte Formeln, in der Gruppe Definierte Namen, den Namens-Manager.
  • Klicken Sie auf die Schaltfläche Neu.
  • Geben Sie einen möglichst sprechenden Namen im Bearbeitungfeld Name ein, z.B. HatFormel.
  • Geben Sie die folgende Formel im Referenzfeld Bezieht sich auf ein:

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

  • Schließen Sie den Namens-Manager mit.

Diese benannte Formel steht für alle Tabellenblätter der Arbeitsmappe zur Verfügung.


2.) Anwendung der benannten Formel im Tabellenblatt

  • Markieren Sie einen Bereich im Tabellenblatt.
  • Klicken Sie in der Registerkarte Start, in der Gruppe Formatvorlagen, auf die Schaltfläche Bedingte Formatierung.
  • Klicken Sie auf Neue Regel.
  • Wählen Sie als Regeltyp Formel zur Ermittlung der zu formatierenden Zellen verwenden.
  • Geben sie als Formel ein:

    =HatFormel

  • Wählen Sie nach einem Klick auf die Schaltfläche Formatieren eine hellgrüne Hintergrundfarbe auf der Registerkarte Ausfüllen aus.
  • Schließen Sie alle Dialogfelder mit OK.

Es werden alle Zellen, die im vorher markierten Bereich eine Formel enthalten, mit hellgrüner Hintergrundfarbe formatiert.


Erläuterung der benannten Formel =ZELLE.ZUORDNEN(48;INDIREKT("ZS";FALSCH))

ZELLE.ZUORDNEN ist der Name einer alten Excel4-Makrofunktion. Die Funktion können Sie nur über eine benannte Formel einsetzen.

Das erste Argument (Typ) ist die Zahl 48. Sie gibt WAHR zurück, wenn die Zelle eine Formel enthält, ansonsten FALSCH.

Das zweite Argument (Bezug) benutzt die Funktion INDIREKT(). Mit ihr wird ein Zellbezug auf die Zelle selbst erstellt. ZS ist die absolute Adresse der Zelle auf sich selbst in der Z1S1-Schreibweise. Es ist die einzige Adressangabe bei der eine Zelle auf sich selbst adressiert werden kann. Das Argument FALSCH in der Funktion INDIREKT() gibt an, das es sich ausdrücklich um die Z1S1-Schreibweise handelt.

Dieser Funktion wurde der Name HatFormel zugeordnet. Wird dieser Name in der bedingten Formatierung angewendet, ermittelt sie ob die betreffende Zelle eine Formel enthält. Bei WAHR wird die bedingte Formatierung umgesetzt und in unserem Beispiel die hellgrüne Hintergrundfarbe gezeigt.

Hinweis:
In einer englischen Excel-Version lautet die benannte Formel für obiges Beispiel:

=GET.CELL(48,INDIRECT("RC",FALSE))


Erweiterung der benannten Formel

Soll in der Funktion INDIREKT() nicht die Adressierung der Zelle auf sich selbst angewandt werden (ZS), geben Sie einen Versatz an, entweder in der Z1S1-Schreibweise oder mit Hilfe der Funktion BEREICH.VERSCHIEBEN().


Beispiele:

  1. Angabe eines Versatzes in der Z1S1-Schreibweise

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

    In diesem Fall verweist die Adresse ZS(-1) auf die Zelle links von der Zelle auf der diese Funktion angewandt wird.

  2. Angabe eines Versatzes mit Hilfe der Funktion BEREICH.VERSCHIEBEN()

    =ZELLE.ZUORDNEN(48;BEREICH.VERSCHIEBEN(INDIREKT("ZS";FALSCH);0;-1))

    Die Funktion BEREICH.VERSCHIEBEN() nimmt als Ausgangspunkt die Zelle, auf die diese Funktion angewandt wird (hier INDIREKT("ZS";FALSCH)) und versetzt diesen Bezug um eine Anzahl Zeilen (hier 0) und eine Anzahl Spalten (hier -1).

    Anwendungsmöglichkeit:
    Zelle B1 soll bedingt formatiert werden, wenn Zelle A1 eine Formel enthält.

    Hinweis:
    In der englischen Excel-Version ist diese Art der Adressierung nicht möglich. Hier müssen Sie die Funktion OFFSET() einsetzen.

    =GET.CELL(48;OFFSET(INDIRECT("RC",FALSE),0,-1))

 

Liste des Arguments Typ

Typ Liefert...
1 den absoluten Bezug der oberen linken Zelle von Bezug; der Bezug liegt als Zeichenfolge und in der für den aktuellen Arbeitsbereich definierten Schreibweise vor.
2 die Zeilennummer der obersten Zelle von Bezug.
3 die Spaltennummer der äußerst linken Zelle von Bezug.
4 dasselbe wie TYP(Bezug).
5 den Inhalt von Bezug.
6 die in Bezug stehende Formel als Zeichenfolge entweder in der A1- oder in der Z1S1-Schreibweise (hängt von der Einstellung des Arbeitsbereichs ab).
7 das Zahlenformat der Zelle als Zeichenfolge (zum Beispiel TT/MM/JJ oder Standard).
8 eine Zahl, die die horizontale Ausrichtung der Zelle angibt:
1 = Standard
2 = Linksbündig
3 = Zentriert
4 = Rechtsbündig
5 = Ausfüllen
6 = Bündig anordnen
7 = Zentriert über Markierung
9 eine Zahl, die die Linienart angibt, die dem linken Rand der Zelle zugeordnet ist:
0 = Kein Rahmen
1 = Dünne Linie
2 = Mittlere Linie
3 = Unterbrochene Linie
4 = Gepunktete Linie
5 = Dicke Linie
6 = Doppelte Linie
7 = Haarlinie
10 eine Zahl, die die Linienart angibt, die dem rechten Rand der Zelle zugeordnet ist; Beschreibungen zu den gelieferten Zahlen finden Sie bei Typ 9.
11 eine Zahl, die die Linienart angibt, die dem oberen Rand der Zelle zugeordnet ist; Beschreibungen zu den gelieferten Zahlen finden Sie bei Typ 9.
12 eine Zahl, die die Linienart angibt, die dem unteren Rand der Zelle zugeordnet ist; Beschreibungen zu den gelieferten Zahlen finden Sie bei Typ 9.
13 eine Zahl von 0 bis 18, die das Muster der markierten Zelle angibt, wie es im Register Muster des Dialogfeldes Zellen formatieren angezeigt wird; dieses Dialogfeld wird eingeblendet, wenn Sie aus dem Menü Format den Befehl Zellen wählen; ist kein Muster ausgewählt, wird 0 zurückgegeben.
14 WAHR, wenn die Zelle gesperrt ist, sonst FALSCH.
15 WAHR, wenn die in der Zelle stehende Formel ausgeblendet ist, sonst FALSCH.
16 eine einzeilige, aus zwei Elementen bestehende Matrix, die die Breite der aktiven Zelle und einen Wahrheitswert enthält; ist der Wahrheitswert gleich WAHR, ist die Zellenbreite so eingestellt, daß sie zusammen mit Änderungen der Standardbreite geändert wird; ist der Wahrheitswert gleich FALSCH, entspricht die Breite der Zelle einer benutzerdefinierten Breite.
17 die Zellenhöhe der Zelle in Punkten.
18 den Namen der Schriftart als Zeichenfolge.
19 die Größe der Schriftart in Punkten.
20 WAHR, wenn entweder für alle Zeichen oder nur für das erste Zeichen in einer Zelle die Auszeichnung Fett angegeben ist, sonst FALSCH.
21 WAHR, wenn entweder für alle Zeichen oder nur für das erste Zeichen in einer Zelle die Auszeichnung Kursiv angegeben ist, sonst FALSCH.
22 WAHR, wenn entweder für alle Zeichen oder nur das erste Zeichen in einer Zelle die Auszeichnung Unterstrichen angegeben ist, sonst FALSCH.
23 WAHR, wenn entweder für alle Zeichen oder nur für das erste Zeichen in einer Zelle die Auszeichnung Durchgestrichen angegeben ist, sonst FALSCH.
24 die Farbe der Schrift als eine Zahl von 1 bis 56; 0, wenn die Farbe der Schrift auf Automatisch eingestellt ist.
25 WAHR, wenn entweder für alle Zeichen oder nur für das erste Zeichen in einer Zelle die Auszeichnung Kontur angegeben ist, sonst FALSCH; das Schriftformat Kontur wird von Microsoft Excel für Windows nicht unterstützt.
26 WAHR, wenn entweder für alle Zeichen oder nur für das erste Zeichen in einer Zelle die Auszeichnung Schattiert angegeben ist, sonst FALSCH; das Schriftformat Schattiert wird von Microsoft Excel für Windows nicht unterstützt.
27 eine Zahl, die angibt, ob bei der Zelle ein manueller Seitenumbruch auftritt:
0 = Kein Umbruch
1 = Zeile
2 = Spalte
3 = Zeile und Spalte
28 Zeilenebene (Gliederung).
29 Spaltenebene (Gliederung).
30 WAHR, wenn die Zeile, zu der die aktive Zelle gehört, eine Zeilenzusammenfassung ist, sonst FALSCH.
31 WAHR, wenn die Spalte, zu der die aktive ZeIle gehört, eine Spaltenzusammenfassung ist, sonst FALSCH.
32 Name der Arbeitsmappe und des Blattes, zu der die Zelle gehört. Enthält die Arbeitsmappe nur ein einzelnes Blatt und trägt es den selben Namen wie die Arbeitsmappe ohne Präfix, wird nur der Name der Arbeitsmappe in der Form MAPPE1.XLS zurückgegeben.
33 WAHR, wenn die Zelle so formatiert ist, daß Text umbrochen wird, sonst FALSCH.
34 die Farbe des linken Randes als Zahl von 1 bis 56; 0, wenn die Farbe auf Automatisch eingestellt ist.
35 die Farbe des rechten Randes als Zahl von 1 bis 56; 0, wenn die Farbe auf Automatisch eingestellt ist.
36 die Farbe des oberen Randes als Zahl von 1 bis 56; 0, wenn die Farbe auf Automatisch eingestellt ist.
37 die Farbe des unteren Randes als Zahl von 1 bis 56; 0, wenn die Farbe auf Automatisch eingestellt ist.
38 die Farbe der Vordergrundschattierung als Zahl von 1 bis 56; 0, wenn die Farbe auf Automatisch eingestellt ist.
39 die Farbe der Hintergrundschattierung als Zahl von 1 bis 56; 0, wenn die Farbe auf Automatisch eingestellt ist.
40 die Formatvorlage der Zelle als Zeichenfolge.
41 die in der aktiven Zelle stehende Formel, ohne diese zu übersetzen (hilfreich für internationale Makrovorlagen).
42 den in Punkten gemessenen horizontalen Abstand zwischen der linken Kante des aktiven Fensters und der linken Kante der Zelle; kann eine negative Zahl sein, wenn sich die Zelle außerhalb des Fensters befindet.
43 den in Punkten gemessenen vertikalen Abstand zwischen der oberen Kante des aktiven Fensters und der oberen Kante der Zelle; kann eine negative Zahl sein, wenn sich die Zelle außerhalb des Fensters befindet.
44 den in Punkten gemessenen horizontalen Abstand zwischen der linken Kante des aktiven Fensters und der rechten Kante der Zelle; kann eine negative Zahl sein, wenn sich die Zelle außerhalb des Fensters befindet.
45 den in Punkten gemessenen vertikalen Abstand zwischen der oberen Kante des aktiven Fensters und der unteren Kante der Zelle; kann eine negative Zahl sein, wenn sich die Zelle außerhalb des Fensters befindet.
46 WAHR, wenn die Zelle eine Textnotiz enthält, sonst FALSCH.
47 WAHR, wenn die Zelle eine Tonnotiz enthält, sonst FALSCH.
48 WAHR, wenn die Zelle eine Formel enthält, FALSCH, wenn sie eine Konstante enthält.
49 WAHR, wenn die Zelle zu einer Matrix gehört, sonst FALSCH.
50 eine Zahl, die die vertikale Ausrichtung für die Zelle angibt:
1 = Oben
2 = Zentriert
3 = Unten
4 = Bündig anordnen
51 eine Zahl, die die vertikale Richtung für die Zelle angibt:
0 = Horizontal
1 = Vertikal
2 = Nach oben
3 = Nach unten
52 das Präfixzeichen (oder das Zeichen für die Textausrichtung) der Zelle oder eine leere Zeichenfolge (""), wenn die Zelle kein solches Zeichen enthält.
53 den Inhalt der Zelle als Zeichenfolge in der Form, wie er momentan angezeigt wird, einschließlich aller weiteren Zahlen oder Symbole, die sich aus der Formatierung der Zelle ergeben.
54 den Namen der Pivot-Tabelle, die die aktive Zelle enthält.
55 die Position einer Zelle innerhalb der Pivot-Tabelle.
0 = Zeilenfeld
1 = Spaltenfeld
2 = Seitenfeld
3 = Datenfeld
4 = Zeilenbeschriftung
5 = Spaltenbeschriftung
6 = Seitenbeschriftung
7 = Datenbeschriftung
8 = Tabellengerüst
56 den Namen des Feldes, das den Bezug der aktiven Zelle enthält, wenn sich diese innerhalb einer Pivot-Tabelle befindet.
57 WAHR, wenn entweder alle Zeichen oder nur das erste Zeichen in einer Zelle im Format Hochgestellt vorliegt, sonst FALSCH.
58 den Schriftstil für alle Zeichen oder nur das erste Zeichen in einer Zelle, wie er im Register Schriftart des Dialogfeldes Zellen formatieren angezeigt wird: zum Beispiel Fett Kursiv.
59 eine Zahl, die den Typ der Unterstreichung angibt:
1 = Keine
2 = Einzelstrich
3 = Doppelstrich
4 = Einfach (Buchhaltung)
5 = Doppelt (Buchhaltung)
60 WAHR, wenn entweder alle Zeichen oder nur das erste Zeichen in einer Zelle im Format Tiefgestellt vorliegt, sonst FALSCH.
61 den Namen des Pivot-Tabellen-Elements der aktiven Zelle als Zeichenfolge.
62 den Namen der aktuellen Arbeitsmappe und des Blattes in der Form [Mappe1]Blatt1.
63 die Farbe für Füllung (Hintergrund) einer Zelle.
64 die Farbe für Muster (Vordergrund) einer Zelle.
65 WAHR, wenn die Option "Einrücken" aktiviert wurde (nur Fernost-Version von Microsoft Excel), sonst FALSCH.
66 den Namen der Arbeitsmappe, die die aktive Zelle enthält, in der Form MAPPE1.XLS.

 

Zuletzt aktualisiert am Sonntag, den 03. Januar 2016 um 17:16 Uhr
 

Kommentare 

 
# 2012-10-29 01:03
Hallo,
ein dickes lob meinerseits, der von excel noch keine ahnung hat; aber das ist hier sehr gut beschrieben und sehr gut layoutet!!
Eine frage hab ich dennoch:
kann ich farben zählen?
Mir würd reichen, wenn in B1 eine 1 erscheint, weil in A1 - dort formatiere ich mit unterschiedlich en farben- eine farbe auftaucht, bei der B1 eine 1 zurückgibt.

schönen gruss
mcmatus
Antworten
 
 
# 2012-11-01 17:23
Hallo Hendrik Maaß,
vielen Dank für dein Interesse an meiner Webseite.

>Kann ich Farben zählen?

Ja!
1. Vergib folgende benannte Formel im Namens-Manager von Excel 2012:
Name: Farbe
Bezieht sich auf: =ZELLE.ZUORDNEN(38;INDIREKT("ZS(-1)";FALSCH))

2. Zelle A1 wurde mit einer Füllfarbe eingefärbt.
3. Zelle B1 enthält folgende Formel: =WENN(Farbe>0;1;0)
4. Die Formel gibt eine Eins zurück falls die linke Zelle eingefärbt wurde, ansonsten Null.

Anschließend kannst du die Einsen (1) mit der Funktion SUMME() zählen.


Mit freundlichem Gruß aus der Rattenfängersta dt Hameln

Frank Arendt-Theilen
Antworten
 
 
# 2013-01-17 09:11
Unglaublich!
So eine Funktion hab ich schon so oft umgangen. Ja, ich hab nicht mal richtig gesucht. Hab immer komplizierte Workarounds verwendet.
Dass es das "von der Stange" gibt ...
Einfach nur gut.
Und was auch hervorzuheben ist:
Ich hab's hier gelesen und konnte es direkt umsetzen. Toll beschrieben.

Frank ... vielen Dank!

:-)
Dirk B.
Antworten
 
 
# 2013-04-18 15:37
Hallo!
Danke für die super beschreibung! Kann mich meinen vorrednern nur anschließen;)
... mach weiter so und nochmal danke für den Beitrag;)
Lg Lisa
Antworten
 
 
# 2013-06-14 12:32
Einfach prima!
Genügend kompakt, genügend ausführlich mit Erklärungen und Varianten (insbesondere EN-Version).
Merci, lo
Antworten
 
 
# 2013-11-25 16:23
Hallo,

habe da mal die Frage zum Argument/Zahl 48.
Ich brauche es genau andersherum. Also Formatierung wenn keine Formel vorliegt.
Antworten
 
 
# 2013-11-25 17:05
Hallo Andreas,
umgib die Formel einfach mit der Funktion NICHT(), also:

=NICHT(ZELLE.ZUORDNEN(48;IND IREKT("ZS";FALSCH)))


Mit freundlichem Gruß aus der Rattenfängersta dt Hameln

Frank Arendt-Theilen
Antworten
 

Heute

Freitag, 27. Mai 2016

Video - Management-Reports

 

excel2013report

 

Video - Bedingte Formatierung


excel2010v2bbf


Buch - Bedingte Formatierung

ExcelBedingteFormatierung

Office2010-Blog


Office2010-Blog Logo

Hinweis

Fast alle Hardware- und Software- bezeichnungen, die auf dieser Web- site erwähnt werden, sind gleichzeitig eingetragene Warenzeichen und sollten als solche behandelt werden.

Video2Brain-Autor

v2bautor