Jak ustawić formatowanie warunkowe dla konkretnej komórki, która zależy od wartości innej komórki?

W moim przykładzie chciałbym warunkowo sformatować komórki B kolumny. Te oznaczone x powinny być sformatowane zgodnie z wartością w kolumnie A (w przykładzie jest to wartość 1):

A | B
1 | x
2 | 
3 | 
1 | x
1 | x
4 | 
8 |

// x can be any value and is here merely to mark the cell that should be formatted

WAŻNE 2014 UWAGA : Formatowanie warunkowe oparte na formule, która może zawierać inne komórki, jest teraz możliwe w Arkuszach Google i działa bardzo podobnie do arkuszy kalkulacyjnych Excel. Ta odpowiedź wyjaśnia jego użycie.

Złożone formatowanie warunkowe można uzyskać w arkuszach kalkulacyjnych Google za pomocą Google Apps Script. Na przykład możesz napisać funkcję, która zmienia kolor tła całego wiersza na podstawie wartości w jednej z jego komórek, co nie wydaje mi się możliwe z menu „Zmień kolor za pomocą reguł”. Prawdopodobnie chcesz ustawić wyzwalacze dla tej funkcji, takie jak „Po edycji”, „Po otwarciu” i „Po przesłaniu formularza”.

Dokumentacja funkcji setBackgroundRGB ()

AKTUALIZACJA: Oto przykład Google Apps Script zmiany koloru tła całego wiersza na podstawie wartości z kolumny A. Jeśli wartość jest dodatnia, użyj koloru zielonego. Jeśli pusty, biały. W przeciwnym razie czerwony. Zobacz wyniki w tym publicznym arkuszu kalkulacyjnym Google . (Musisz się zalogować, aby skrypt mógł działać, ale bez logowania nadal możesz zobaczyć wyniki).

function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var endRow = sheet.getLastRow();

  for (var r = startRow; r <= endRow; r++) {
    colorRow(r);
  }
}

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, 3);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[0] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[0] > 0){
    dataRange.setBackgroundRGB(192, 255, 192);
  }else{
    dataRange.setBackgroundRGB(255, 192, 192);
  }

  SpreadsheetApp.flush(); 
}

function onEdit(event)
{
  var r = event.source.getActiveRange().getRowIndex();
  if (r >= 2) {
    colorRow(r);
  }
}

function onOpen(){
  colorAll();
}

W pobliżu

Te nowe Arkusze Google pozwala to zrobić, jak to opisano tutaj . Musisz najpierw włączyć nowe arkusze kalkulacyjne w ustawieniach sterownika Google zgodnie z opisem w artykule. Następnie możesz wybrać opcję „Formuła niestandardowa” z opcji formatowania warunkowego i wprowadzić dowolną formułę (nie zapomnij dodać =prefiksu!). Odwołania do komórek bez $prefiksów są dostosowywane automatycznie po zastosowaniu do zakresów, tak jak można się tego spodziewać.

Wygląda na to, że brakuje wsparcia dla migracji ze starych do nowych - dotyczy to tylko nowych arkuszy i odkryłem, że tylko wartości można skopiować z jednego do drugiego. Kopiowanie całego arkusza może być opcją.

Napisałem tę aplikację internetową, Color Code + , wraz z Apps-Script, aby zaspokoić większość podstawowych potrzeb formatowania warunkowego. Rzucać w niektórych reguł i będzie to wypluć kod, który można następnie wkleić w arkuszu kalkulacyjnym, w obszarze Narzędzia → edytor skryptów ... . ( Wątek pomocy na forach Google.)

Reguły formatu warunkowego w Arkuszach Google wyglądają tak, jeśli chcesz formatować warunkowo na czerwonym tle, jeśli suma w dwóch komórkach jest inna niż w trzeciej komórce:

Formatowanie warunkowe - formuła niestandardowa

range 2:227

= if($i:$i = "Duplicate",True,False)

wybierz kolor, który chcesz podświetlić wiersz.

(Luty 2017) Jak wspomniano w innej odpowiedzi, Arkusze Google pozwalają teraz użytkownikom dodawać formatowanie warunkowe bezpośrednio z interfejsu użytkownika, niezależnie od tego, czy jest to komputer stacjonarny / laptop, urządzenia z Androidem czy iOS. Pozostała część tej odpowiedzi jest jednak przeznaczona głównie dla programistów, ponieważ aplikacje można pisać w formacie „do” formatowania warunkowego.

Dzięki Google Sheets API v4 (i nowszym) programiści mogą teraz pisać aplikacje, które stosują reguły formatowania warunkowego CRUD. Sprawdź przewodnik i przykładowe strony, aby uzyskać więcej informacji, a także dokumenty referencyjne (wyszukaj {add,update,delete}ConditionalFormatRule). Przewodnik zawiera ten fragment kodu w języku Python (zakładając identyfikator pliku SHEET_IDi SHEETSjako punkt końcowy usługi interfejsu API):

myRange = {
    'sheetId': 0,
    'startRowIndex': 1,
    'endRowIndex': 11,
    'startColumnIndex': 0,
    'endColumnIndex': 4,
}

reqs = [
    {'addConditionalFormatRule': {
        'index': 0,
        'rule': {
            'ranges': [ myRange ],
            'booleanRule': {
                'format': {'textFormat': {'foregroundColor': {'red': 0.8}}}
                'condition': {
                    'type': 'CUSTOM_FORMULA',
                    'values':
                        [{'userEnteredValue': '=GT($D2,median($D$2:$D$11))'}]
                },
            },
        },
    }},
    {'addConditionalFormatRule': {
        'index': 0,
        'rule': {
            'ranges': [ myRange ],
            'booleanRule': {
                'format': {
                    'backgroundColor': {'red': 1, 'green': 0.4, 'blue': 0.4}
                },
                'condition': {
                    'type': 'CUSTOM_FORMULA',
                    'values':
                        [{'userEnteredValue': '=LT($D2,median($D$2:$D$11))'}]
                },
            },
        },
    }},
]

SHEETS.spreadsheets().batchUpdate(spreadsheetId=SHEET_ID,
        body={'requests': reqs}).execute()

Oprócz języka Python interfejsy API Google obsługują wiele języków , więc masz do wyboru różne opcje. W każdym razie ta próbka kodu formatuje Arkusz (patrz zdjęcie poniżej), tak że osoby młodsze niż mediana wieku są podświetlone na jasno czerwono, podczas gdy osoby powyżej mediany mają swoje dane na czerwono.

UWAGA: moja odpowiedź tutaj jest identyczna z odpowiedzią na SO dla tego pytania , tyle że upuściłem PSA, ponieważ nie musi mieszkać w> 1 miejscu.

Aby zająć się szczegółami Q w „nowych” Arkuszach, sugeruję wyczyszczenie formatowania warunkowego z B: B, wybranie Kolumny B i zastosowanie formuły niestandardowej :

=A1=1

z wybranym formatowaniem i Done.

Podczas edycji formatowania warunkowego wybierz Formułę niestandardową i użyj następujących ...

=if(A1 = 1 , true)

Wybierz kolory i gotowe.

Edytuj w odpowiedzi na wyjaśnienie pytania:

W oknie Arkusza kalkulacyjnego Google znajduje się menu Format z opcją „Zmień kolor za pomocą reguł”. Jest to tak techniczne, jak formatowanie warunkowe w Arkuszu kalkulacyjnym Google. O ile wiem, nie ma możliwości pokolorowania jednej komórki na podstawie wartości innej - Google nie pozwala wprowadzać formuł dla innych komórek.

O ile nie wstawisz innych danych do kolumny B, zawsze możesz zrównać całą kolumnę B z kolumną A, a następnie użyć opcji „Zmień kolor z regułami”, aby pokolorować wszystkie komórki wartością 1 z podobnymi kolorami - tzn. czerwone tło i czerwony tekst, gdy wartość wynosi 1, białe tło i biały tekst, gdy nie jest, skutecznie ukrywając wartość w kolumnie B. To da ci pożądany wygląd.

W programie Excel możesz robić, co chcesz. Używając twoich danych jako przykładu, warunkowo sformatowałem B1, gdy ta formuła jest prawdziwa:

=IF(A1=1,true,false)

a następnie przeciągnął formatowanie w dół, co podświetliło tylko komórki w B, gdzie jest sąsiadem w kolumnie A równym 1.

Oryginalna odpowiedź

Nie jestem pewien, czy możesz to zrobić w 2 kolumnach, ale wiem, że możesz to zrobić w 3:

 A  |  B  |  C
----------------
 1  |  f  |  x

W kolumnie B możesz użyć IFformuły, aby sprawdzić, czy x został umieszczony w odpowiedniej kolumnie C:

=IF(C1="x",A1,"") 

IFWzorze 3 części - testu, wówczas wartość oraz inne wartości. W powyższym przykładzie formuła sprawdza, czy występuje xw kolumnie C. Jeśli tak, wpisuje wartość z A1, w przeciwnym razie wpisuje się puste. Nie ma znaczenia, czy xjest to duża czy mała litera.

Po wprowadzeniu tej formuły na górze listy możesz użyć funkcji przeciągania, aby przeciągnąć formułę w dół na resztę listy. Gdy komórka jest podświetlona na niebiesko, przesuń mysz nad kwadrat w prawym dolnym rogu, aż kursor zmieni się w celownik. Następnie po prostu kliknij i przytrzymaj podczas przeciągania w dół, aby skopiować formułę do komórek poniżej.