Frequently asked questions - Calc

    From The Document Foundation Wiki
    < Faq‎ | Calc
    This page is a translated version of the page Faq/Calc/115 and the translation is 17% complete.
    Outdated translations are marked like this.
    Other languages:


    Как задать более трёх условий в условном форматировании

    Условное форматирование (Формат ▸ Условное форматирование) позволяет автоматически применить преднастроенные стили к ячейке, при выполнении определенных условий. Начиная с версии 3.5, количество условий не лимитировано.

    Для более ранних версий существуют различные методы обхода проблемы, однако мы вам советуем обновить LibreOffice до версии 5.2 или более свежей.

    Here is a way to set more than 3 conditions.

    Using four styles

    The conditional format can set up to four different styles using this trick :

    1. Apply one of the four styles to the whole area : this will be the default style.
    2. Use the conditional formatting tool to define the conditions for applying the other three styles.

    Using more than four styles

    We will use the possibility of defining the format as a function of the result of a formula.

    1. Create as many styles as you require for the formats. In our example, we will create four cell styles, in which the only difference is the color of the background, according to the name of the style : green, yellow, orange and red. We want to conserve the Default style for the cells that do not have a whole value between 1 and 4. The result expected :
      copie d'écran plage contenant de 0 à 4 formatée
      Résultat du formatage conditionnel
  • Select the area to be formatted. After selection of a range, the first cell selected remains the current cell.
    copie d'écran sélection plage
    Sélection de A1:A5
  • Go to Format ▸ Conditional ▸ Condition
  • Check Condition 1 and select Formula is.
    1. In the adjacent area, type the formula :
    STYLE(SI(A1=1;"green";SI(A1=2;"yellow";SI(A1=3;"orange";SI(A1=4;"red";"Default")))))
  • Note :

    • We have selected A1:A5, A1 is the current cell (see above) ; the formula refers only to A1 but the program extends the formula to the other cells selected. Relative references are used (so the second line refers to A2, etc.).
    • The STYLE function is not sensitive to the case in style names. Thus, if the styles red and Red exist, the functions STYLE("Red"), STYLE("RED") or STYLE("red") all apply the same style, depending on the order in which they were created.
    • You must not begin the formula with an equals (=) sign