Bedingte Formatierung mit formel arbeiten

Computer: Software & Programmierung Office Software
#1

Hallo zusammen,

ich habe aktuell ein Problem, welches ich nicht gelöst bekomme (Excel 2010)

Folgende Ausgangssituation:

Ich habe ein Tabellenblatt auf dem sind 2 Tabellen (Tabelle1 und Tabelle2) vorhanden.

Die Länge von Tabelle1 ist variabel, kann sich also ändern (theoretisch bis zu 60000 Zeilen).
Die Tabellen sind auf Grund der Größe standardmäßig gruppiert und minimiert, also nicht sichtbar

Ich möchte nun AUßERHALB der Tabellen eine Zelle formatieren, welche auf bestimmte Werte INNERHALB von z.B. Tabelle1 reagieren soll.
Die Zelle soll rot gefärbt werden, wenn in Tabelle1[Test] (Test ist in diesem Fall eine Spalte von Tabelle1) ein Wert 1 vorkommt.

In der Bedingten Formatierung hab ich folgende Einstellungen getroffen:

  • „Formel zur Ermittlung der zu formatierenden Zellen verwenden“

  • „Werte formatieren, für die diese Formel wahr ist“

  • in dem Feld für die Formel versuche ich dann folgende formel einzutragen

=ODER(MAX(Tabelle1[Test])>1;MIN(Tabelle1[Test])

Aus irgendeinem Grund akzeptiert EXCEL diese Formel aber nicht.
Weiß jemand, wie ich die Bedingte Formatierung mit dieser Formel hinbekomme? Wenn ich die Formel in einer Zelle eingebe funktioniert sie anstandslos und liefert „WAHR“ bzw „FALSCH“ zurück.
Wieso wird die Formel in der Bedingten Formatierung nicht akzeptiert?

Vielen dank im Voraus und Beste Grüße
Stefan

#2

Hallo Stefan,

Tabelle1[Test]

was ist das für ein Bezug? Hasst du einen Namen vergeben der so heißt?
Mindestens unter Excel2007 sind eckige Klammern als Bezug oder in einen Namen ungültig.

Gruß Holger

#3

Hallo Holger,

Tabelle1[Test]

was ist das für ein Bezug? Hasst du einen Namen vergeben der
so heißt?
Mindestens unter Excel2007 sind eckige Klammern als Bezug oder
in einen Namen ungültig.

Nein, das scheint ein von Excel (2010) automatisch vergebener Bezug zu sein. In dem Moment wo ich mit EXCEL 2010 eine Tabelle erstelle und auswähle „Diese Tabelle hat überschriften“ so wird für jede „Überschrift“ ein Bezug erstellt in der Form „Tabelle[Überschrift]“

ich kann somit Formeln verwenden die für eine komplette Spalte in der Tabelle gelten

innerhalb von Zelle funktioniert dieser bezug auch nur scheint die bedingte Formatierung damit nicht klar zu kommen

Grüße
Stefan

#4

=ODER(MAX(Tabelle1[Test])>1;MIN(Tabelle1[Test])1;MIN(Test)

#5

Hallo Sportsman!

Ich habe deine Formel geringfügig verändert. Sie schaut nun so aus:

=ODER(MAX(Test)>1;MIN(Test)

#6

Hallo Stefan,

Nein, das scheint ein von Excel (2010) automatisch vergebener
Bezug zu sein. In dem Moment wo ich mit EXCEL 2010 eine
Tabelle erstelle und auswähle „Diese Tabelle hat
überschriften“ so wird für jede „Überschrift“ ein Bezug
erstellt in der Form „Tabelle[Überschrift]“

Jetzt versteh ich das Problem: Dieser Name Test ist kein Name im eigentlichen Sinne. Du hast ihn über den Punkt als Tabelle formatieren vergeben. Darum kannst du ihn auch nur innerhalb eines Blattes verwenden. Echte Namen in Excel werden über Strg+F3 vergeben und funktionieren auch blattübergreifend. Hättest du so einen Namen definiert, hätte das auch die bedingte Formatierung geschluckt. Solltest du noch Fragen haben, kannst du dich gerne wieder melden.

Gruß Alex

#7

Hallo Niclaus,

den Bereich habe ich nicht selber definiert, sondern der wurde definiert in dem Moment, wo ich die Tabelle erstellt habe.
Ich kann dann mit Tabelle[XYZ] auf einzelne Spalten in der Tabelle zugreifen.
In den Zellen funktioniert das auch, aber in der bedingten Formatierung nicht

Falls Du die Spalte wirklich mit dem Namen „Test“ definiert
hast, lautet die Formel:

=ODER(MAX(Test)>1;MIN(Test)

#8

Hallo Alex,

Jetzt versteh ich das Problem: Dieser Name Test ist kein Name
im eigentlichen Sinne. Du hast ihn über den Punkt als Tabelle
formatieren vergeben. Darum kannst du ihn auch nur innerhalb
eines Blattes verwenden. Echte Namen in Excel werden über
Strg+F3 vergeben und funktionieren auch blattübergreifend.
Hättest du so einen Namen definiert, hätte das auch die
bedingte Formatierung geschluckt. Solltest du noch Fragen
haben, kannst du dich gerne wieder melden.

Und jetzt hab ich das Problem auch gelöst, allerdings finde ich die Lösung mehr als „bescheuert“
Ich habe jetzt einen eigenen Bereich festgelegt „Tabelle1_Test“ welcher sich auf Tabelle1[Test] bezieht.

Wenn ich jetzt in der bedingten Formatierung auf „Tabelle1_Test“ Bezug nehme funktioniert es, was für mich „schwachsinnig“ ist, denn „Tabelle1_Test“ bezieht sich widerrum auf Tabelle1[Test].
Die Antwort auf die Frage, wieso ich in der bedingten Formatierung nicht direkt auf Tabelle1[Test] zugreifen kann, wird wohl nur Microsoft wissen
Aber Vielen Dank für deinen hinweis mit dem Bereich

Gruß Alex

Beste Grüße
Stefan

#9

strukturierte Verweise für Excel-Tabellen
Hallo ihr drei,

mit ist/war das mit den eckigen Klammern in der Form auch unbekannt.
Ich habe XL 2007, kein XL 2010.

Das mit „Tabelle hat Überschriften“ kann ich anhaken in dem kleinen
Fensterchen was erscheint nach Einfügen—Tabelle.
In das Fenster kann ich dann auch den dazugehörigen zellbereich vermerken.

Das ergibt in Formeln—Namensmanager einen Eintrag, aber eher wie immer, also keine eckigen Klammern zu sehen.

Suche ich nach:
" Verwenden von strukturierten Verweisen für Excel-Tabellen"
in der XL-Hilfe kommt eine lange Hilfeseite mit durchaus Klammern in
den Namen, Klammern, Formeln, oder wie die sonst genau heißen.

Gruß
Reinhard

#10

Hallo Stefan

Und jetzt hab ich das Problem auch gelöst, allerdings finde
ich die Lösung mehr als „bescheuert“
Ich habe jetzt einen eigenen Bereich festgelegt
„Tabelle1_Test“ welcher sich auf Tabelle1[Test] bezieht.

So meinte ich das, aber dein Name hätte auch kürzer ausfallen können. Test allein hätte gereicht.

Gruß Alex

#11

Hallo Niclausk

Da eine Namensdefinition nur einmal pro Datei vorkommen kann, muss/darf „Tabelle1“ nicht erwähnt werden.

Zumindest für die älteren Excels stimmt das nicht. Wenn man beim Festlegen des Namens den Blattnamen voranstellt (Tabelle1!Test), gilt der Bereichsname nur in diesem Blatt. Ich verwende das immer, denn

ich brauchte das z.B. für den Europatarif:

  • in einer Mappe waren Blätter mit den Preisen für das jeweilige Land
  • die Blätter hiießen wie die Länderkennzeichen bei Adressen
  • die Preislisten hießen „Datenbank“
  • im Kalkulationsblatt gab man nur das Länderkennzeichen ein, dann suchte der SVerweis in der Datenbank auf diesem Blatt

Allgemein: so kann man Master-Blätter erstellen. Normalerweise bleiben mappenweit definierte Namen auch mappenweit sichtbar. Allerdings: hat man ein Blatt mit einer komplexeren Namensstruktur und kopiert das (wie im obigen Europatarif), verliert die Kopie alle Namen und man müßte sie nach dem Kopieren neu erstellen.
Blattweit definierte Namen sind auch nur blattweit sichtbar. Man braucht ein wenig mehr Gehirnschmalz, um damit zu arbeiten, aber es ist einfach präziser und vor allem bei vielen Blättern sehr viel weniger störanfällig.

HTH.

Markus

#12

Da eine Namensdefinition nur einmal pro Datei vorkommen kann, muss/darf „Tabelle1“ nicht erwähnt werden.

Zumindest für die älteren Excels stimmt das nicht. Wenn man
beim Festlegen des Namens den Blattnamen voranstellt (Tabelle1!Test), gilt der Bereichsname nur in diesem Blatt.

Hallo Markus
Tatsächlich - auch in Excel 2010 ist das möglich. Wer mir wohl diesen Floh ins Ohr gesetzt hat!
Vielen Dank für Deinen Hinweis.
Niclaus

#13

Da eine Namensdefinition nur einmal pro Datei vorkommen kann, muss/darf „Tabelle1“ nicht erwähnt werden.

Zumindest für die älteren Excels stimmt das nicht. Wenn man
beim Festlegen des Namens den Blattnamen voranstellt (Tabelle1!Test), gilt der Bereichsname nur in diesem Blatt.

Tatsächlich - auch in Excel 2010 ist das möglich. Wer mir wohl
diesen Floh ins Ohr gesetzt hat!

Hallo Niclaus,

das mit dem Namen in Excel ist so einfach nicht.

Mal was anderes, das geht im alten wie auch im neuen Excel.

Vergib mal in Tabelle1 den Namen Fest für
Tabelle1!A1:A6
und den Namen Flex für
!A1:A6

Dann fülle paar Zellen in dem Bereich mit Zahlen, dann
B1: =Summe(Fest)
B2: =Summe(Flex)

In Tabelle2 gibste in A1:A6 auch paar aber andere Zahlen ein.
In B1:B2 die gleichen Formeln.

Dann sieht du an den Ergebnisunterschieden in Blatt1 und Blatt2
daß Fest bedeutet, immer die Werte von tabelle1!A1:A6 aber Flex
bedeutet A1:A6 des jeweiligen Blattes.
Falls du das mal brauchen könntest.

In einem Punkt gibt es da Unterschiede zwischen altem und neuem Excel.
Im neuen Excel wird das automatisch aktualisiert wenn du in A1:A6 etwas änderst bei Flex.

Im alten Excel mußte anschubsen mit:
und den Namen Flex für
!A1:A6+Jetzt()*0

Gruß
Reinhard

#14

Grüezi zusammen

Eine Anmerkung zu dieser Vorgehensweise sei mir erlaubt:

Vergib mal in Tabelle1 den Namen Fest für
Tabelle1!A1:A6
und den Namen Flex für
!A1:A6

Dann fülle paar Zellen in dem Bereich mit Zahlen, dann
B1: =Summe(Fest)
B2: =Summe(Flex)

In Tabelle2 gibste in A1:A6 auch paar aber andere Zahlen ein.
In B1:B2 die gleichen Formeln.

Dann sieht du an den Ergebnisunterschieden in Blatt1 und
Blatt2
daß Fest bedeutet, immer die Werte von tabelle1!A1:A6 aber
Flex
bedeutet A1:A6 des jeweiligen Blattes.
Falls du das mal brauchen könntest.

Die Definition für das aktive Tabellenblatt mit !xxxx ist inkonsisitent - wenn ein solches Tabellenblatt kopiert wird kann/wird das zu einem Absturz von Excel führen.

Daher ist diese Art der Namensvergabe nicht sinnvoll und sollte ausser zu Testzwecken nicht verwendet werden… :smile:

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -