Formuła Excela odnosząca się do „KOMÓRKI PO LEWO”


86

Próbuję wykonać formatowanie warunkowe, aby kolor komórki zmienił się, jeśli wartość różni się od wartości w lewej komórce (każda kolumna to miesiąc, w każdym wierszu są wydatki na określony obiekt. Chcę monitorować łatwo zmienia się ceny w ciągu miesięcy.)

Mogę to zrobić dla każdej komórki i przeciągnąć ją formatem, ale chciałbym, aby ogólna formuła była stosowana do całego arkusza.

Dzięki!


3
... i na wypadek, gdyby ktoś tego szukał ... aby dostać kolumnę po prawej:=INDIRECT("RC[1]",0)
prograhammer

To bardzo dobre pytanie. Niestety, nie jestem pewien, czy dobrze to rozumiem, oryginalny plakat chciał zmienić swoje komórki bez konieczności formatowania warunkowego, a następnie wklejania malarza formatu do każdej komórki. Powiedzmy, że mamy miesiące w rzędach, a banany, jabłka i pomarańcze w kolumnach. W macierzy są ceny. W jaki sposób bylibyśmy w stanie zrobić coś, co automatycznie zabarwiłoby komórkę na czerwono, gdyby nastąpiła zmiana ceny z poprzedniego miesiąca na bieżący? Jeśli istnieje również automatyczny sposób na pokolorowanie komórek na zielono, jeśli cena spadnie, i na czerwono, jeśli
Bryan

Odpowiedzi:


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

14
Używasz PIĘCIU rzadko używanych funkcji wymagających sprawdzenia użycia zamiast prostego odniesienia względnego A1 lub RC [-1]? To jak podróż z Brooklynu do Bronxu z postojem w Walla Walla WA!
John Machin,

5
Dlaczego nie zamiast tego = INDIRECT (ADDRESS (ROW (), COLUMN () - 1))
Prometheus

1
„Pośredni” ma tak wiele problemów, w tym (ale nie tylko) bycie (1) jednowątkowym i (2) niestabilność (prawdopodobnie kaskadowo zmuszająca cały skoroszyt do ponownego obliczania przy każdej zmianie. Nie używaj go, jeśli liczy się wydajność) ,
John Joseph,

W formatowaniu warunkowym ustawiam regułę jako, ="OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)=""xyz"""ale komórka nie jest sformatowana. Co jest nie tak?
zamek błyskawiczny

81

Najkrótsza i najbardziej kompatybilna wersja to:

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

„RC [-1]” oznacza jedną kolumnę po lewej stronie. „R [1] C [-1]” jest na dole po lewej stronie.

Drugi parametr 0 oznacza, że ​​pierwszy parametr jest interpretowany przy użyciu notacji R1C1.

Pozostałe opcje:

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

Moim zdaniem za długo. Ale przydatne, jeśli wartość względna jest dynamiczna / pochodzi z innej komórki. na przykład:

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

Najprostsza opcja:

= RC[-1]

ma tę wadę, że trzeba włączyć notację R1C1 za pomocą opcji, co jest niedopuszczalne, gdy inne osoby muszą korzystać z programu Excel.


11

Tworząc formatowanie warunkowe, ustaw zakres, do którego ma ono dotyczyć tego, czego chcesz (całego arkusza), a następnie wprowadź formułę względną (usuń$ znaki), tak jakbyś formatował tylko lewy górny róg.

Program Excel odpowiednio zastosuje formatowanie do pozostałych komórek.

W tym przykładzie, zaczynając od B1, lewa komórka będzie A1. Po prostu użyj tego - nie jest wymagana żadna zaawansowana formuła.


Jeśli szukasz czegoś bardziej zaawansowanego, możesz pobawić się column(),row() i indirect(...).


7

Jeśli zmienisz odwołanie do komórki, aby używać notacji W1K1 (Narzędzia | Opcje, karta Ogólne), możesz użyć prostej notacji i wkleić ją do dowolnej komórki.

Teraz twoja formuła to po prostu:

=RC[-1]

5

Zamiast pisać bardzo długie:

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

Możesz po prostu napisać:

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

Na przykład możesz napisać do komórki B2 :

=OFFSET(B2,0,-1)

w odniesieniu do komórki B1

Wciąż dzięki Jasonowi Youngowi !! Nigdy nie wymyśliłbym tego rozwiązania bez Twojej odpowiedzi!


2
To nie jest przydatne. Jeśli wiesz już, że twoja komórka to B2, możesz po prostu napisać B1 dla komórki po lewej stronie i w ogóle nie używać przesunięć. Chodzi o to, aby zrobić coś zupełnie ogólnego.
Matthew Przeczytaj

1
Jest to całkiem wygodne, jeśli wiesz, od której komórki zaczynasz, a tylko przesunięcie, które chcesz zrobić, to na przykład dynamiczne, powiedzmy = OFFSET (B2,0, A2). W jakim kontekście go użyłem.
Sam Fed

To jest odpowiedź, której szukałem! Chciałem, aby ktoś mógł wybrać pojedynczą komórkę, która reprezentuje cały wiersz i aby wszystko automatycznie zmieniało się, aby użyć parametrów w tym wierszu. Dzięki!
BT

1

wypełnij komórkę A1 następującym wzorem:

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

Następnie automatycznie rozciągnij w prawo

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

Jeśli przesunięcie jest poza zakresem dostępnej komórki, otrzymasz #REF! błąd.

Mam nadzieję, że ci się podoba.


1

Jeszcze prostsze:

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

PRZESUNIĘCIE zwraca odniesienie względem bieżącego odniesienia, więc jeśli pośredni zwraca prawidłowe odniesienie, nie jest ono potrzebne.


Nigdy tego nie rób. POŚREDNI łamie drzewo zależności - to katastrofa.
Ollie2893

Właśnie odkryłem tę funkcję, która moim zdaniem jest przydatna do formatowania warunkowego w zależności od pierwszego wiersza kolumny. Nie jestem pewien, czy w tym przypadku działa notacja RC []
narrowtux

1

Podczas tworzenia funkcji zdefiniowanej przez użytkownika dowiedziałem się, że inne odpowiedzi dotyczą funkcji OFFSETi INDIRECTnie można ich zastosować.

Zamiast tego należy Application.Callerodwołać się do komórki, w której została użyta funkcja zdefiniowana przez użytkownika (UDF). W drugim kroku należy przekonwertować indeks kolumny na nazwę odpowiedniej kolumny.
Na koniec możesz odwołać się do lewej komórki za pomocą funkcji Range aktywnego arkusza .

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

0

Możesz użyć skryptu VBA, który zmienia formatowanie warunkowe zaznaczenia (może być konieczne odpowiednie dostosowanie warunku i formatowania):

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

Natknąłem się na ten wątek, ponieważ zawsze chciałem odnosić się do „komórki po lewej stronie”, ale NAJWAŻNIEJNIE w sposób nieulotny (bez OFFSETU, POŚREDNIEGO i podobnych katastrof). Przeglądanie sieci w górę iw dół, bez odpowiedzi. (Ten wątek również nie dostarcza odpowiedzi.) Po kilku majstrach natknąłem się na najbardziej zdumiewającą metodę, którą lubię się podzielić z tą społecznością:

Załóżmy, że wartość początkowa 100 w E6. Załóżmy, że wprowadzę deltę do tej wartości w F5, powiedzmy 5. Następnie obliczylibyśmy wartość kontynuacji (105) w F6 = E6 + F5. Jeśli chcesz dodać kolejny krok, łatwo: po prostu skopiuj kolumnę F do kolumny G i wprowadź nową deltę w G5.

To jest to, co robimy okresowo. Każda kolumna ma datę i te daty MUSZĄ BYĆ w porządku chronologicznym (aby pomóc w dopasowaniu itp.). Często zdarza się, że zapominamy o wejściu na stopień. Teraz załóżmy, że chcesz wstawić kolumnę między F i G (aby nadrobić zaległości) i skopiować F do nowego G (aby ponownie wypełnić formułę kontynuacji). To NIC nie ma w całkowitej katastrofie. Spróbuj - H6 powie teraz = F6 + H5 i NOT (tak jak jest to absolutnie potrzebne) = G6 + H5. (Nowy G6 będzie poprawny.)

Aby to zadziałało, możemy zaciemnić to banalne obliczenie w najbardziej zdumiewający sposób F6 = indeks ($ E6: F6; 1; kolumny ($ E1: F1) -1) + F5. Skopiuj w prawo, a otrzymasz G6 = indeks ($ E6: G6; 1; kolumny ($ E1: G1) -1) + G5.

To nigdy nie powinno działać, prawda? Oczywiście okólnik! Wypróbuj i daj się zaskoczyć. Wydaje się, że program Excel zdaje sobie sprawę, że chociaż zakres INDEKSU obejmuje komórkę, którą ponownie obliczamy, ta komórka nie jest adresowana przez INDEKS, a zatem NIE tworzy odwołania cyklicznego.

Więc teraz jestem w domu i jestem suchy. Wstaw kolumnę między F i G, a otrzymamy dokładnie to, czego potrzebujemy: Wartość kontynuacji w starym H będzie odnosić się do wartości kontynuacji, którą wstawiliśmy w nowym G.



0

Utwórz nazwaną formułę „LeftCell”

Dla tych, którzy szukają nieulotnej odpowiedzi, możesz to zrobić, używając funkcji INDEKS w nazwanej formule.

  1. Wybierz komórkę A2

  2. Otwórz Name Manager(Ctrl + F3)

  3. Kliknij New

  4. Nazwij to „LeftCell” (lub jak wolisz)

  5. W przypadku Scope:wybierzWorkbook

  6. W Refers to:wprowadź formułę:

    =INDEX(!A1:!A2, 1)

  7. Kliknij OKi zamknijName Manager

To mówi programowi Excel, aby zawsze sprawdzał wartość bezpośrednio po lewej stronie bieżącej komórki i będzie się zmieniać dynamicznie po zaznaczeniu różnych komórek. Jeśli nazwa jest używana samodzielnie, podaje wartość komórki, ale w zakresie używa odwołania. Podziękowania dla tej odpowiedzi na temat odwołań do komórek dla tego pomysłu.


0

Myślę, że to najłatwiejsza odpowiedź.

Użyj "Nazwy", aby odnieść się do przesunięcia.

Załóżmy, że chcesz zsumować kolumnę (kolumnę A) aż do komórki, w której znajduje się podsumowanie, ale nie wliczając jej (powiedzmy Komórka A100); Zrób to:

(Zakładam, że podczas tworzenia nazwy używasz odwołań A1; R1C1 można następnie przełączyć na)

  1. Kliknij w dowolnym miejscu arkusza, a nie w górnym wierszu - powiedz Cell D9
  2. Zdefiniuj nazwany zakres o nazwie, powiedz „OneCellAbove”, ale zastąp pole „RefersTo” wartością „= D8” (bez cudzysłowów)
  3. Teraz w Cell A100 możesz użyć formuły
    = SUMA (A1: OneCellAbove)

-1

Wybierz cały arkusz i STRONA GŁÓWNA> Style - formatowanie warunkowe, nowa zasada ..., użyj formuły, aby określić, które komórki sformatować i sformatuj wartości, jeśli ta formuła jest prawdziwa ::

=A1<>XFD1

Format...Wybierz wybór formatowania OK, OK.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.