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!
Odpowiedzi:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
="OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)=""xyz"""ale komórka nie jest sformatowana. Co jest nie tak?
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.
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(...).
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]
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!
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.
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.
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
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
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.
Dlaczego nie po prostu użyć:
=ADDRESS(ROW(),COLUMN()-1)
Dla tych, którzy szukają nieulotnej odpowiedzi, możesz to zrobić, używając funkcji INDEKS w nazwanej formule.
Wybierz komórkę A2
Otwórz Name Manager(Ctrl + F3)
Kliknij New
Nazwij to „LeftCell” (lub jak wolisz)
W przypadku Scope:wybierzWorkbook
W Refers to:wprowadź formułę:
=INDEX(!A1:!A2, 1)
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.
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)
= SUMA (A1: OneCellAbove)
=INDIRECT("RC[1]",0)