wake-up-neo.net

Excel-Formel zur Referenz 'ZELLE NACH LINKS'

Ich versuche, die bedingte Formatierung so vorzunehmen, dass sich die Zellenfarbe ändert, wenn sich der Wert von dem Wert in der Zelle links davon unterscheidet (jede Spalte ist ein Monat, in jeder Zeile sind die Ausgaben für ein bestimmtes Objekt.) Ich möchte überwachen leichte Preisänderungen über Monate.)

Ich kann es pro Zelle machen und per Format ziehen, aber ich möchte, dass eine allgemeine Formel auf das gesamte Arbeitsblatt angewendet wird.

Vielen Dank!

63
mik
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
71
Jason Young

Die kürzeste kompatible Version ist:

=INDIRECT("RC[-1]",0)

"RC [-1]" bedeutet eine Spalte links. "R [1] C [-1]" ist unten links.

Der zweite Parameter 0 bedeutet, dass der erste Parameter mit der R1C1-Notation interpretiert wird.

Die anderen Optionen:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

Zu lange meiner Meinung nach. Aber nützlich, wenn der relative Wert dynamisch ist/von einer anderen Zelle abgeleitet wird. z.B.: 

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, A1)

Die einfachste Option:

= RC[-1]

hat den Nachteil, dass Sie die R1C1-Notation mit Optionen aktivieren müssen. Dies ist ein No-Go, wenn andere Personen Excel verwenden müssen.

66
seb

Legen Sie beim Erstellen der bedingten Formatierung den gewünschten Bereich (das gesamte Blatt) fest, und geben Sie eine relative - Formel ein (entfernen Sie die $-Zeichen), als ob Sie nur die obere linke Ecke formatieren würden.

Excel wird die Formatierung ordnungsgemäß auf die restlichen Zellen anwenden.

In diesem Beispiel wäre die Zelle left ausgehend von B1 A1. Verwenden Sie einfach das - keine erweiterte Formel erforderlich.


Wenn Sie nach etwas Fortgeschrittenem suchen, können Sie mit column(), row() und indirect(...) herumspielen.

9
Michael Haren

Wenn Sie die Zellreferenz so ändern, dass die R1C1-Notation verwendet wird (Registerkarte Extras | Optionen, Registerkarte Allgemein), können Sie eine einfache Notation verwenden und in eine beliebige Zelle einfügen.

Nun lautet Ihre Formel einfach:

=RC[-1]
6
edoloughlin

Beim Erstellen einer benutzerdefinierten Funktion habe ich herausgefunden, dass die anderen Antworten mit den Funktionen OFFSET und INDIRECT nicht angewendet werden können.

Stattdessen müssen Sie Application.Caller verwenden, um auf die Zelle zu verweisen, in der die benutzerdefinierte Funktion (UDF) verwendet wurde. In einem zweiten Schritt konvertieren Sie den Index der Spalte in den Namen der entsprechenden Spalte.
Schließlich können Sie die linke Zelle mit der Funktion Range des aktiven Arbeitsblatts referenzieren.

Function my_user_defined_function(argument1, argument2)
    ' Way to convert a column number to its name copied from StackOverflow
    ' http://stackoverflow.com/a/10107264
    ' Answer by Siddarth Rout (http://stackoverflow.com/users/1140579/siddharth-rout)
    ' License (if applicable due to the small amount of code): CC BY-SA 3.0
    colName = Split(Cells(, (Application.Caller(1).Column - 1)).Address, "$")(1)

    rowNumber = Application.Caller(1).Row

    left_cell_value = ActiveSheet.Range(colName & rowNumber).Value

    ' Now do something with left_cell_value
1
ComFreek

Fülle die A1-Zelle mit der folgenden Formel: 

 =IF(COLUMN(A1)=1;"";OFFSET(A20;0;-1))&"1"

Dann autoextend nach rechts, bekommst du 

 1|  A  |  B  |  C  |  ect ect
 2|    1|   11|  111|  ect ect

Wenn der Offset außerhalb des Bereichs der verfügbaren Zelle liegt, erhalten Sie das # REF! Error.

Ich hoffe dir gefällt es.

1
MUY Belgium

Noch einfacher:

=indirect(address(row(), column() - 1))

OFFSET gibt eine Referenz relativ zur aktuellen Referenz zurück. Wenn also indirekt die korrekte Referenz zurückgegeben wird, benötigen Sie sie nicht.

1
Gerard ONeill

Anstatt sehr lange zu schreiben: 

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

Sie können einfach schreiben: 

=OFFSET(*Name of your Cell*,0,-1)

So können Sie zum Beispiel in Cell B2 schreiben:

=OFFSET(B2,0,-1)

bezug auf Zelle B1

Trotzdem danke Jason Young !! Ohne Ihre Antwort wäre ich niemals auf diese Lösung gekommen!

1
Sam Fed

Erstellen Sie eine benannte Formel "LeftCell"

Wenn Sie nach einer nichtflüchtigen Antwort suchen, können Sie dies mithilfe der INDEX-Funktion in einer benannten Formel erreichen.

  1. Wählen Sie Zelle A2 aus
  2. Öffne Name Manager (Strg + F3)
  3. Klicken Sie auf New
  4. Nennen Sie es "LeftCell" (oder was auch immer Sie bevorzugen)
  5. Wählen Sie für Scope:Workbook
  6. Geben Sie in Refers to: Die Formel ein:

    =INDEX(!A1:!A2, 1)

  7. Klicken Sie auf OK und schließen Sie Name Manager

Dadurch wird Excel angewiesen, immer den Wert unmittelbar links von der aktuellen Zelle zu betrachten. Dieser Wert ändert sich dynamisch, wenn verschiedene Zellen ausgewählt werden. Wenn der Name alleine verwendet wird, gibt er den Wert der Zelle an, in einem Bereich wird jedoch die Referenz verwendet. Gutschrift an diese Antwort über Zellreferenzen für die Idee.

0
JCKE

Warum nicht einfach verwenden:

=ADDRESS(ROW(),COLUMN()-1)
0
Andrew Algava

Ich stolperte über diesen Thread, weil ich immer auf die "Zelle nach links" verweisen wollte, aber auf eine nicht flüchtige Weise (keine OFFSET, INDIRECT und ähnliche Katastrophen). Im Web nach oben und unten blickend, keine Antworten. (Dieser Thread bietet auch keine Antwort.) Nach einigem Basteln bin ich auf die erstaunlichste Methode gestoßen, die ich mit dieser Community teilen möchte:

Nehmen Sie einen Startwert von 100 in E6 an. Angenommen, ich gebe in F5 ein Delta zu diesem Wert ein, sagen wir 5. Wir würden dann den Fortführungswert (105) in F6 = E6 + F5 berechnen. Wenn Sie einen weiteren Schritt hinzufügen möchten, können Sie dies ganz einfach tun: Kopieren Sie einfach Spalte F in Spalte G und geben Sie ein neues Delta in G5 ein. 

Das machen wir regelmäßig. Jede Spalte hat ein Datum, und diese Daten MÜSSEN in chronologischer Reihenfolge sein (um bei MATCH usw. zu helfen). Es kommt immer wieder vor, dass wir vergessen, einen Schritt einzugeben. Angenommen, Sie möchten eine Spalte zwischen F und G einfügen (um Ihre Auslassung nachzuholen) und F in das neue G kopieren (um die Fortführungsformel erneut zu füllen). Dies ist NICHTS KURZE einer totalen Katastrophe. Probieren Sie es aus - H6 sagt jetzt = F6 + H5 und NICHT (wie wir es unbedingt brauchen) = G6 + H5. (Der neue G6 wird korrekt sein.)

Damit dies funktioniert, können wir diese banale Berechnung auf erstaunliche Weise verschleiern. F6 = Index ($ E6: F6; 1; Spalten ($ E1: F1) -1) + F5. Nach rechts kopieren und Sie erhalten G6 = Index ($ E6: G6; 1; Spalten ($ E1: G1) -1) + G5.

Das sollte nie funktionieren, richtig? Zirkelbezug, klar! Probieren Sie es aus und staunen Sie. Excel scheint zu erkennen, dass, obwohl der INDEX-Bereich die Zelle umfasst, die wir neu berechnen, diese Zelle selbst nicht vom INDEX adressiert wird und daher KEINEN Zirkelverweis erstellt.

Nun bin ich zu Hause und trocken. Fügen Sie eine Spalte zwischen F und G ein, und wir bekommen genau das, was wir brauchen: Der Fortsetzungswert im alten H verweist auf den Fortsetzungswert, den wir im neuen G eingefügt haben.

0
Ollie2893

Bitte wählen Sie das gesamte Arbeitsblatt und HOME> Formatvorlagen - Bedingte Formatierung, Neue Regel ..., Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen und Werte formatieren, bei denen diese Formel zutrifft::

=A1<>XFD1

Format..., wählen Sie die Formatierung, OKOK.

0
pnuts

Ich denke, das ist die einfachste Antwort.

Verwenden Sie einen "Namen", um den Versatz zu referenzieren.

Angenommen, Sie möchten eine Spalte (Spalte A) bis zu der Zelle summieren, in der sich die Summe befindet, ohne sie jedoch einzuschließen (z. B. Zelle A100). mach das:

(Ich gehe davon aus, dass Sie beim Erstellen des Namens die A1-Referenzierung verwenden. Anschließend kann auf R1C1 umgeschaltet werden.)

  1. Klicken Sie auf eine beliebige Stelle im Blatt, nicht auf die oberste Zeile - sagen Sie "Zelle D9"
  2. Definieren Sie einen benannten Bereich mit dem Namen "OneCellAbove", aber überschreiben Sie das Feld "RefersTo" mit "= D8" (keine Anführungszeichen).
  3. Jetzt können Sie in Zelle A100 die Formel Verwenden.
    = SUMME (A1: OneCellAbove)
0
Gnq

Sie können ein VBA-Skript verwenden, das die bedingte Formatierung einer Auswahl ändert (möglicherweise müssen Sie die Bedingung und die Formatierung entsprechend anpassen):

For Each i In Selection
i.FormatConditions.Delete
i.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=" & i.Offset(0, -1).Address
With i.FormatConditions(1).Font
    .Bold = True
End With
Next i
0
sdfx