Wie kopiere ich bedingte Formeln über Abschnitte eines Arbeitsblatts hinweg?

Question

Ich habe Daten in Minitabellen innerhalb eines einzelnen Arbeitsblatts angeordnet. Siehe Bild

Ich muss die gesamte Zeile (innerhalb der Minitabelle) basierend auf einem Zellenwert innerhalb der Zeile bedingt formatieren.

Beispielsweise würde ich für Tabelle U A4:E4 basierend auf dem Wert von B hervorheben.

Das Problem ist, dass, wenn ich die bedingte Formatierung in Tabelle T kopiere, diese immer noch auf Spalte B verweist, nicht auf Spalte G, wie ich es möchte. Ich muss das viele Male machen (50+), daher würde es unverhältnismäßig lange dauern, es manuell zu korrigieren (7 bedingte Formate pro Minitabelle, 50+ Tabellen). Irgendwelche Ideen?

Answer 1

Vorausgesetzt, dass alle Ihre Minitabellen die gleiche Breite haben und keine Lücken (oder nur Lücken mit fester Breite) zwischen ihnen vorhanden sind, können Sie eine einzelne Formel zur bedingten Formatierung wie diese verwenden und auf die Zellen anwenden $A$4:$E$4:

=OFFSET($A4, 0, 5 * QUOTIENT(COLUMN(A4)-1, 5) + 1) = "TWL W/G"

Anschließend sollten Sie das bedingte Format mit dem Formatierungstool in die restliche erste Tabelle und in die anderen Minitabellen kopieren können. Weitere Bearbeitungen sollten nicht erforderlich sein.

Dies funktioniert, indem berechnet wird, welche Minitabelle formatiert wird (aus der Spalte der ausgewerteten Zelle) und somit, mit welcher Zelle die Testzeichenfolge verglichen werden soll.

$A4ist der Startpunkt für den Offset. Es handelt sich um einen absoluten Verweis auf Spalte A, der jedoch einen relativen Zeilenverweis hat. Wenn Sie das Format also in andere Zeilen kopieren, werden diese separat formatiert.

Der QUOTIENT(COLUMN(A4)-1, 5)Teil berechnet, in welcher Tabelle sich die auszuwertende Zelle befindet, wobei davon ausgegangen wird, dass jede Tabelle 5 Spalten breit ist. A4ist eine völlig relative Referenz und wird daher für jede Zelle ersetzt, in die Sie das Format kopieren.

Der 5 * QUOTIENT() + 1Teil wählt die 2. Spalte der richtigen Tabelle aus (vorausgesetzt, sie sind alle 5 Spalten breit). Ändern Sie die 5er, wenn Ihre Tabellen eine andere Anzahl von Spalten verwenden oder wenn + 1Sie eine andere Spalte innerhalb der Tabelle testen müssen.

Answer 2

Folge diesen Schritten:

Erste Ansatz:

1. Wählen Sie den gewünschten Datenbereich in Tabelle U aus.
2. Klicken Sie auf „Bedingtes Format“, „Neue Regel“, „Formel verwenden“, um zu bestimmen, welche Zelle formatiert werden soll.
3. Geben Sie unter „Regelbeschreibung bearbeiten“ die folgende Formel ein.=B4 = B4.
4. Wenden Sie das gewünschte Format an und beenden Sie den Vorgang mit „OK“.
5. Wählen Sie den bedingt formatierten Bereich in Tabelle U aus und kopieren Sie ihn.
6. Wählen Sie den Zielbereich in Tabelle T aus und klicken Sie auf „Inhalte einfügen“.
7. Klicken Sie in der Kategorie „Einfügeoptionen“ auf „Formate“ und dann auf „OK“.

Zweiter Ansatz:

1. Kopieren Sie eine Zelle aus Tabelle U in eine leere Zelle.
2. Wählen Sie die kopierte Zelle aus, klicken Sie auf „Bedingtes Format“, dann auf die Option „Regeln verwalten“.
3. Wählen Sie unter „Formatierungsregeln anzeigen für“ die Option „Dieses Arbeitsblatt“ aus.
4. Ändern Sie für die Regel für Tabelle U die Option „Gilt für übereinstimmenden Datenbereich“.
5. Wählen Sie den Datenbereich in Tabelle T aus, um das Format anzuwenden.
6. OK klicken.

Hinweis:

Nachdem Sie das bedingte Format kopiert haben, aktivieren Sie „Regel verwalten“ für Tabelle T. Jetzt finden Sie die Formel =G4 = G4. Hier rechtfertigt Excel das „Kopierformat“ und passt auch den Zieldatenbereich an.

Bedenken Sie dabei jedoch, dass Excel auch die Zeilen formatiert, die nicht mit dem B4-Wert übereinstimmen.