Formatowanie warunkowe na podstawie wartości innej komórki
Używam Arkuszy Google do codziennego Pulpitu nawigacyjnego. Potrzebuję zmienić kolor tła komórki B5 na podstawie wartości innej komórki-C5. Jeśli C5 jest większy niż 80%, to kolor tła jest zielony, ale jeśli jest poniżej, będzie bursztynowy / czerwony.
Czy jest to dostępne z funkcją Arkuszy Google, czy muszę wstawić skrypt?
6 answers
Uwaga: Kiedy w wyjaśnieniu poniżej jest napisane "B5", oznacza to " B{current_row}", więc dla C5 jest to B5, dla C6 jest to B6 i tak dalej. Chyba że podasz $B$5 - wtedy odnosisz się do jednej konkretnej komórki.
[[4]} jest to obsługiwane w Arkuszach Google od 2015: https://support.google.com/drive/answer/78413#formulas
W Twoim przypadku musisz ustawić formatowanie warunkowe na B5.
- użyj opcji " Custom formula is " i ustaw ją na
=B5>0.8*C5
. - ustaw opcję "Range " na
B5
. - ustaw żądany kolor
Możesz powtórzyć ten proces, aby dodać więcej kolorów tła, tekstu lub skali kolorów.
Jeszcze lepiej, aby pojedyncza reguła miała zastosowanie do wszystkich wierszy, używając zakresów w " Range". Przykład zakładając, że pierwszy wiersz jest nagłówkiem:
- przy formatowaniu warunkowym B2 Ustaw "formuła Niestandardowa to " na
=B2>0.8*C2
. - ustaw opcję " Range " do
B2:B
. - ustaw żądany kolor
Będzie jak w poprzednim przykładzie, ale działa na wszystkich wierszach, a nie tylko na wierszu 5.
Zakresy mogą być również używane w "niestandardowej formule jest", dzięki czemu można pokolorować cały wiersz na podstawie ich wartości kolumn.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-13 18:41:24
Jeszcze jeden przykład:
Jeśli masz kolumnę od A do D i musisz podświetlić całą linię (np. od A do D) Jeśli B jest "kompletna", możesz to zrobić następująco:
"Custom formula is": =$B:$B="Completed"
Background Color: red
Range: A:D
Oczywiście, możesz zmienić zakres Na A: T, jeśli masz więcej kolumn.
Jeśli B zawiera "Complete" , użyj wyszukiwania w następujący sposób:
"Custom formula is": =search("Completed",$B:$B)
Background Color: red
Range: A:D
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-07-14 15:32:01
Użyłem ciekawego formatowania warunkowego w moim ostatnim pliku i pomyślałem, że będzie to przydatne dla innych. Więc ta odpowiedź jest przeznaczona dla kompletności do poprzednich.
Powinien pokazać, do czego ta niesamowita funkcja jest zdolna, a zwłaszcza jak działa $ thing.
Przykładowa tabela
Kolor od D do G zależy od wartości w kolumnach a, B i C. Ale formuła musi sprawdzić wartości, które są stałe poziomo (użytkownik, początek, koniec) i wartości ustawione pionowo (daty w wierszu 1). Tam przydaje się znak dolara.
Rozwiązanie
W tabeli jest 2 użytkowników, każdy z określonym kolorem, odpowiednio foo (niebieski) i bar (żółty).
Musimy użyć następujących reguł formatowania warunkowego i zastosować obie z nich w tym samym zakresie (D2:G3
):
=AND($A2="foo", D$1>=$B2, D$1<=$C2)
=AND($A2="bar", D$1>=$B2, D$1<=$C2)
W języku angielskim warunek oznacza:
użytkownik jest name
, a data bieżącej komórki jest po start
i przed end
Zauważ, że jedyną rzeczą, która zmienia się między formułami 2, jest nazwa użytkownika. Dzięki temu jest naprawdę łatwy do ponownego użycia z wieloma innymi użytkownikami!
Objaśnienia
Important : zmienne wiersze i kolumny są względem początku zakresu. Ale stałe wartości nie mają wpływu.
Łatwo jest pomylić się z pozycjami względnymi. W tym przykładzie, jeśli gdyby użyto zakresu D1:G3
zamiast D2:G3
, formatowanie kolorów byłoby przesunięte o jeden rząd w górę.
Aby tego uniknąć, należy pamiętać, że wartość dla zmiennych wierszy i kolumn powinna odpowiadać początkowi zakresu zawierającego.
W tym przykładzie zakres zawierający kolory to D2:G3
, więc początek to D2
.
User
, start
, i end
różnią się wierszami
- >Kolumny stałe A B C, wiersze zmienne zaczynające się od 2: $A2
, $B2
, $C2
Dates
różni się kolumnami
- >Zmienne kolumny zaczynające się od D, stały wiersz 1: D$1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-28 21:24:57
W zasadzie wszystko, co musisz zrobić, to dodać $ jako prefiks na literę kolumny i numer wiersza. Proszę zobaczyć obrazek poniżej
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-01-30 04:45:30
Zmień kolor tła komórki B5 na podstawie wartości innej komórki-C5. Jeśli C5 jest większy niż 80%, to kolor tła jest zielony, ale jeśli jest poniżej, będzie bursztynowy / czerwony.
Nie ma wzmianki o tym, że B5 zawiera jakąkolwiek wartość, więc zakładając, że 80%
jest .8
sformatowane jako procent bez dziesiętnych i pustych liczy się jako "poniżej":
Wybierz B5, kolor "bursztynowy / czerwony" ze standardowym wypełnieniem, a następnie Formatowanie warunkowe..., Niestandardowa formuła jest i:
=C5>0.8
Z Zielonym wypełnieniem i zrobione .
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-10-05 15:39:22
Jestem rozczarowany, ile czasu zajęło nam wypracowanie tego.
Chcę zobaczyć, które wartości w moim zakresie są poza odchyleniem standardowym.
- Dodaj standard deviation calc do komórki gdzieś
=STDEV(L3:L32)*2
- Wybierz zakres, który ma być podświetlony, kliknij prawym przyciskiem myszy, formatowanie warunkowe
- Wybierz sformatuj komórki, jeśli są większe niż
- w polu wartość lub wzór typ
=$L$32
(niezależnie od komórki, w której znajduje się twój stdev)
I couldn ' t work out how to umieść STDEv w linii. Próbowałem wielu rzeczy z nieoczekiwanymi rezultatami.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-11-05 01:36:51