Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel conditional formatting русский

Сборник формул для условного форматирования

В данной статье собран список формул, которые можно использовать в условном форматировании ячеек, заданным при помощи формулы:

  • Excel 2003 : Формат (Format)Условное форматирование (Conditional formatting)формула;
  • Excel 2007-2010 : вкладка Главная (Home)Условное форматирование (Conditional formatting)Создать правило (New rule)Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)

Подробнее об условном форматировании можно прочитать в статье: Основные понятия условного форматирования и как его создать

Все условия приведены для диапазона A1:A20 . Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20 (столбцов может быть больше), начиная с ячейки A1 , после чего назначить условие.
Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона — A4 .

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
= $A1 =МАКС( $A$1:$A$20 )
при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.

Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: = B1 A1 , то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше( B1, B3). При этом если ячейки столбца D меньше ячеек столбца C в той же строке — они тоже будут выделены( D1 , D5 ).

  • Выделение ячеек с числами:
    =ЕЧИСЛО( A1 )
  • Выделение ячеек с числами, но не учитывая нули:
    =И(ЕЧИСЛО( A1 ); A1 <>0)
  • Выделение строк со значением больше 0:
    = A1 >0
  • Выделение строк со значением в диапазоне от 3 до 10:
    =И( A1 >=3; A1
  • Выделение в диапазоне $A$1:$A$20 ячейки с максимальным значением:
    = A1 =МАКС( $A$1:$A$20 )
  • Выделение в диапазоне $A$1:$A$20 ячейки с минимальным значением:
    =И(ЕЧИСЛО( A1 ); A1 =МИН( $A$1:$A$20 ))
  • Выделение в диапазоне $A$1:$A$20 ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6:
    = A1 =НАИБОЛЬШИЙ( $A$1:$A$20 ;2)

  • Выделение ячеек с любым текстом:
    =ЕТЕКСТ( A1 )
  • Выделение ячеек с текстом Итог:
    = A1 =»Итог»
  • Выделение ячеек, содержащих текст Итог:
    =СЧЁТЕСЛИ( A1 ;»*итог*»)
    =НЕ(ЕОШ(ПОИСК(«итог»; A1 )))
  • Выделение ячеек, не содержащих текст Итог:
    =СЧЁТЕСЛИ( A1 ;»*итог*»)=0
    =ЕОШ(ПОИСК(«итог»; A1 ))
  • Выделение ячеек, текст которых начинается со слова Итог:
    =ЛЕВСИМВ( A1 ;4)=»Итог»
  • Выделение ячеек, текст которых заканчивается на слово Итог:
    =ПРАВСИМВ( A1 ;4)=»Итог»

  • Выделение текущей даты:
    = A1 =СЕГОДНЯ()
  • Выделение ячейки с датой, больше текущей:
    = A1 >СЕГОДНЯ()
  • Выделение ячейки с датой, которая наступит через неделю:
    = A1 =СЕГОДНЯ()+7
  • Выделение ячеек с датами текущего месяца(любого года):
    =МЕСЯЦ( A1 )=МЕСЯЦ(СЕГОДНЯ())
  • Выделение ячеек с датами текущего месяца текущего года:
    =И(МЕСЯЦ( A1 )=МЕСЯЦ(СЕГОДНЯ());ГОД( A1 )=ГОД(СЕГОДНЯ()))
    или
    =ТЕКСТ( A1 ;»ГГГГММ»)=ТЕКСТ(СЕГОДНЯ();»ГГГГММ»)
  • Выделение ячеек с выходными днями:
    =ДЕНЬНЕД( A1 ;2)>5
  • Выделение ячеек с будними днями:
    =ДЕНЬНЕД( A1 ;2)
  • Выделение ячеек, входящих в указанный период(промежуток) дат:
    =И( $A1 >ДАТА(2015;9;1); $A1

  • Выделение различий в ячейках по условию:
    = A1 <> $B1
  • Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше:
    = A1 > B1
  • Выделение строк цветом через одну:
    =ОСТАТ(СТРОКА();2)
  • Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000 :
    =СЧЁТЕСЛИ( $F$1:$H$5000 ; A1 )
  • Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000 :
    =СЧЁТЕСЛИ( $F$1:$H$5000 ; A1 )=0
  • Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету:
    =СЧЁТЕСЛИ( $A$1:$A1 ; A1 )=2
  • Выделение ячеек, содержащих ошибки (#ЗНАЧ!; #Н/Д; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
    =ЕОШИБКА( A )
  • Выделение непустых ячеек в столбце A :
    = $A1 <>«»
  • Статья помогла? Поделись ссылкой с друзьями!

    Conditional formatting training in Excel with examples

    Conditional formatting is a convenient tool for data analysis and visual representation of results. Knowing how to use this tool will save you a lot of time and effort. A fleet glance at the document will be enough to obtain the necessary information.

    How to apply conditional formatting in Excel

    The tool «HOME»-«Conditional formatting» can be found on the main tab in the «Styles» section. If you click on the little arrow on the right, it will open the menu.

    Let’s compare the numerical values in an Excel range with a numerical constant. The rules «Greater Than / Less Than / Equal To / Between…» are the most frequently used. That’s why they’re listed in the menu «Highlight Cells Rules».

    Let’s enter some numbers in the range А1:А11.

    Select the range of values. Open the menu «Conditional formatting». Select «Highlight Cells Rules». Set a condition, for example, «Greater Than».

    Let’s enter the number 15 in the left box. In the drop-down menu on the right select the way of highlighting the values that correspond to the established condition: «Format cells that are GREATER THAN:» 15. The result is plain to see immediately:

    Leave this menu by hitting the OK button.

    Conditional formatting by a cell’s value

    Let’s compare the values in the range А1:А11 with the number in the B2 cell. Enter the number 20 in it.

    Select the initial range and open the window of the «Conditional formatting» tool. In this example, let’s apply the condition «less» («Highlight Cells Rules» — «Less Than»).

    In the left box, enter the link to the B2 cell (click on this cell and its name will appear in the box automatically). The link’s absolute by default.

    The formatting result is plain to see in the Excel sheet immediately.

    The values in the А1:А11 range that are less than the value of the B2 cell are filled with the selected color.

    Let’s set the following formatting conditions: compare the values of cells in different ranges and highlight the same values. We will compare the column А1:А11 with the column В1:В11.

    Select the initial range (А1:А11). Click «Conditional formatting» — «Highlight Cells Rules» — «Equal To». In the left box, enter the link to the B1 cell. The link should be Mixed or Relative! And not absolute!

    The program has compared every value in the A column with the corresponding value in the B column. The coinciding values have been highlighted with a fill color.

    Important note! When you use relative references, you have to pay attention to which cell was active the moment you opened the «Conditional formatting» tool. Since it’s the active cell to which the reference in the condition is “tied.”

    In our example, the A1 cell was active at the moment we opened the tool. The link is $B1. Consequently, Excel compares the value in the A1 cell with the B1 value. If we selected the column from the bottom upwards rather than from top to bottom, the A11 cell would be the active one. And the program would compare B1 with A11.

    Читать еще:  Как найти внешние ссылки в excel

    Pay attention to this nuance in order to ensure the «Conditional formatting» tool performs the task properly.

    You can do the following to check the accuracy of the established condition:

    1. Select the first cell in the range to which conditional formatting is applied.
    2. Open the tool’s menu; click «Manage Rules».

    In the newly-opened window, you can see which rule is applied to which range.

    Conditional formatting – several conditions

    The initial range is А1:А11. We need the numbers that are greater than 6 to be highlighted with red. Green for numbers greater than 10. Yellow for values greater than 20.

    1. Method: Select the range А1:А11. Apply «Conditional formatting» to it. «Highlight Cells Rules» — «Greater Than». Enter the number 6 in the left box. In the right one, select «Light Red Fill Dark Red Text». Hit OK. Select the range А1:А11 once again. Set the formatting condition as «Format cells that are GREATER THAN:» 10, and choose «Green Fill Dark Green Text». In the same way, set the yellow fill color for numbers greater than 20.
    2. Method: Go to the menu of the «Conditional formatting» tool and select «New Rule». Fill in the formatting parameters for the first condition. Click OK. Likewise, set the second and third formatting conditions:

    Note: the values in some cells simultaneously corresponds to two or more conditions. The highlighting priorities depend on the order of the rules listed in «Rules Manager».

    That is, the number 24, which is simultaneously greater than 6, 10, and 20, is highlighted in accordance with the condition «=$А1>20» (the first one on the list).

    Conditional formatting of dates in Excel

    Select the range containing the dates.

    Apply «Highlight Cells Rules»-«A Date Occurring» to it.

    In the newly-opened window, you can seen the list of available conditions (rules):

    Select the suitable one (for instance, for the last 7 days) and click OK.

    The red fill color highlights the cells containing the dates within the past week (the date when this article was written is March 3, 2017).

    Conditional formatting in Excel using formulas

    If the standard rules are not sufficient for the task, the user can apply a formula. The capabilities of this instrument are limitless, so virtually any formula can be used. Let’s view a simple variant.

    We have a column containing numbers. We need the cells with even numbers to be highlighted with a color. We will use the formula: =MOD(A1,2)

    Select the range containing the numbers and open the «Highlight Cells Rules» menu. Select «New Rules». Click «Use a formula to determine which cells to format». Fill in the box as follows:

    Click Ok to close the window and view the result.

    Conditional formatting of the row by a cell’s value

    The task is to highlight the row containing a cell with a certain value.

    The exemplary table:

    We need to highlight in red the information on the projects in progress («Underway»). For the completed projects’ data («Completed»), the green fill in color should be applied.

    Select the range containing the table values A2:D12. Click «Highlight Cells Rules»-«New Rule». Choose a formula as the type of condition. We will use the function: =IF().

    The order of filling in the formatting conditions for «Completed projects».

    Note: links to a row are absolute; links to a cell are mixed (only the column is fixed).

    Likewise, set the formatting rules for the projects in progress.

    In «Rules Manager», the conditions appear as follows:

    The obtained result:

    When the formatting parameters are set for the entire range, the condition will be fulfilled as soon as the cells are filled in. For example, let’s complete Caroline project dated January 28 by replacing «Underway» with «Completed».

    The highlight has changed automatically. It would have taken you a while to achieve this result using the standard Excel tools.

    Conditional formatting training in Excel with examples

    Conditional formatting is a convenient tool for data analysis and visual representation of results. Knowing how to use this tool will save you a lot of time and effort. A fleet glance at the document will be enough to obtain the necessary information.

    How to apply conditional formatting in Excel

    The tool «HOME»-«Conditional formatting» can be found on the main tab in the «Styles» section. If you click on the little arrow on the right, it will open the menu.

    Let’s compare the numerical values in an Excel range with a numerical constant. The rules «Greater Than / Less Than / Equal To / Between…» are the most frequently used. That’s why they’re listed in the menu «Highlight Cells Rules».

    Let’s enter some numbers in the range А1:А11.

    Select the range of values. Open the menu «Conditional formatting». Select «Highlight Cells Rules». Set a condition, for example, «Greater Than».

    Let’s enter the number 15 in the left box. In the drop-down menu on the right select the way of highlighting the values that correspond to the established condition: «Format cells that are GREATER THAN:» 15. The result is plain to see immediately:

    Leave this menu by hitting the OK button.

    Conditional formatting by a cell’s value

    Let’s compare the values in the range А1:А11 with the number in the B2 cell. Enter the number 20 in it.

    Select the initial range and open the window of the «Conditional formatting» tool. In this example, let’s apply the condition «less» («Highlight Cells Rules» — «Less Than»).

    In the left box, enter the link to the B2 cell (click on this cell and its name will appear in the box automatically). The link’s absolute by default.

    The formatting result is plain to see in the Excel sheet immediately.

    The values in the А1:А11 range that are less than the value of the B2 cell are filled with the selected color.

    Let’s set the following formatting conditions: compare the values of cells in different ranges and highlight the same values. We will compare the column А1:А11 with the column В1:В11.

    Select the initial range (А1:А11). Click «Conditional formatting» — «Highlight Cells Rules» — «Equal To». In the left box, enter the link to the B1 cell. The link should be Mixed or Relative! And not absolute!

    The program has compared every value in the A column with the corresponding value in the B column. The coinciding values have been highlighted with a fill color.

    Important note! When you use relative references, you have to pay attention to which cell was active the moment you opened the «Conditional formatting» tool. Since it’s the active cell to which the reference in the condition is “tied.”

    Читать еще:  Vba excel найти ячейку со значением

    In our example, the A1 cell was active at the moment we opened the tool. The link is $B1. Consequently, Excel compares the value in the A1 cell with the B1 value. If we selected the column from the bottom upwards rather than from top to bottom, the A11 cell would be the active one. And the program would compare B1 with A11.

    Pay attention to this nuance in order to ensure the «Conditional formatting» tool performs the task properly.

    You can do the following to check the accuracy of the established condition:

    1. Select the first cell in the range to which conditional formatting is applied.
    2. Open the tool’s menu; click «Manage Rules».

    In the newly-opened window, you can see which rule is applied to which range.

    Conditional formatting – several conditions

    The initial range is А1:А11. We need the numbers that are greater than 6 to be highlighted with red. Green for numbers greater than 10. Yellow for values greater than 20.

    1. Method: Select the range А1:А11. Apply «Conditional formatting» to it. «Highlight Cells Rules» — «Greater Than». Enter the number 6 in the left box. In the right one, select «Light Red Fill Dark Red Text». Hit OK. Select the range А1:А11 once again. Set the formatting condition as «Format cells that are GREATER THAN:» 10, and choose «Green Fill Dark Green Text». In the same way, set the yellow fill color for numbers greater than 20.
    2. Method: Go to the menu of the «Conditional formatting» tool and select «New Rule». Fill in the formatting parameters for the first condition. Click OK. Likewise, set the second and third formatting conditions:

    Note: the values in some cells simultaneously corresponds to two or more conditions. The highlighting priorities depend on the order of the rules listed in «Rules Manager».

    That is, the number 24, which is simultaneously greater than 6, 10, and 20, is highlighted in accordance with the condition «=$А1>20» (the first one on the list).

    Conditional formatting of dates in Excel

    Select the range containing the dates.

    Apply «Highlight Cells Rules»-«A Date Occurring» to it.

    In the newly-opened window, you can seen the list of available conditions (rules):

    Select the suitable one (for instance, for the last 7 days) and click OK.

    The red fill color highlights the cells containing the dates within the past week (the date when this article was written is March 3, 2017).

    Conditional formatting in Excel using formulas

    If the standard rules are not sufficient for the task, the user can apply a formula. The capabilities of this instrument are limitless, so virtually any formula can be used. Let’s view a simple variant.

    We have a column containing numbers. We need the cells with even numbers to be highlighted with a color. We will use the formula: =MOD(A1,2)

    Select the range containing the numbers and open the «Highlight Cells Rules» menu. Select «New Rules». Click «Use a formula to determine which cells to format». Fill in the box as follows:

    Click Ok to close the window and view the result.

    Conditional formatting of the row by a cell’s value

    The task is to highlight the row containing a cell with a certain value.

    The exemplary table:

    We need to highlight in red the information on the projects in progress («Underway»). For the completed projects’ data («Completed»), the green fill in color should be applied.

    Select the range containing the table values A2:D12. Click «Highlight Cells Rules»-«New Rule». Choose a formula as the type of condition. We will use the function: =IF().

    The order of filling in the formatting conditions for «Completed projects».

    Note: links to a row are absolute; links to a cell are mixed (only the column is fixed).

    Likewise, set the formatting rules for the projects in progress.

    In «Rules Manager», the conditions appear as follows:

    The obtained result:

    When the formatting parameters are set for the entire range, the condition will be fulfilled as soon as the cells are filled in. For example, let’s complete Caroline project dated January 28 by replacing «Underway» with «Completed».

    The highlight has changed automatically. It would have taken you a while to achieve this result using the standard Excel tools.

    Working with Conditional Formatting

    Conditional formatting is a feature of Excel which allows you to apply a format to a cell or a range of cells based on certain criteria.

    For example the following rules are used to highlight cells in the conditional_format.py example:

    Which gives criteria like this:

    And output which looks like this:

    It is also possible to create color scales and data bars:

    The conditional_format() method

    The conditional_format() worksheet method is used to apply formatting based on user defined criteria to an XlsxWriter file.

    The conditional format can be applied to a single cell or a range of cells. As usual you can use A1 or Row/Column notation ( Working with Cell Notation ).

    With Row/Column notation you must specify all four cells in the range: (first_row, first_col, last_row, last_col) . If you need to refer to a single cell set the last_* values equal to the first_* values. With A1 notation you can refer to a single cell or a range of cells:

    The options parameter in conditional_format() must be a dictionary containing the parameters that describe the type and style of the conditional format. The main parameters are:

    Other, less commonly used parameters are:

    • min_type
    • mid_type
    • max_type
    • min_value
    • mid_value
    • max_value
    • min_color
    • mid_color
    • max_color
    • bar_color
    • bar_only
    • bar_solid
    • bar_negative_color
    • bar_border_color
    • bar_negative_border_color
    • bar_negative_color_same
    • bar_negative_border_color_same
    • bar_no_border
    • bar_direction
    • bar_axis_position
    • bar_axis_color
    • data_bar_2010
    • icon_style
    • icons
    • reverse_icons
    • icons_only
    • stop_if_true
    • multi_range

    Conditional Format Options

    The conditional format options that can be used with conditional_format() are explained in the following sections.

    The type option is a required parameter and it has no default value. Allowable type values and their associated parameters are:

    TypeParameters
    cellcriteria
    value
    minimum
    maximum
    format
    datecriteria
    value
    minimum
    maximum
    format
    time_periodcriteria
    format
    textcriteria
    value
    format
    averagecriteria
    format
    duplicateformat
    uniqueformat
    topcriteria
    value
    format
    bottomcriteria
    value
    format
    blanksformat
    no_blanksformat
    errorsformat
    no_errorsformat
    formulacriteria
    format
    2_color_scalemin_type
    max_type
    min_value
    max_value
    min_color
    max_color
    3_color_scalemin_type
    mid_type
    max_type
    min_value
    mid_value
    max_value
    min_color
    mid_color
    max_color
    data_barmin_type
    max_type
    min_value
    max_value
    bar_only
    bar_color
    bar_solid*
    bar_negative_color*
    bar_border_color*
    bar_negative_border_color*
    bar_negative_color_same*
    bar_negative_border_color_same*
    bar_no_border*
    bar_direction*
    bar_axis_position*
    bar_axis_color*
    data_bar_2010*
    icon_seticon_style
    reverse_icons
    icons
    icons_only

    Data bar parameters marked with (*) are only available in Excel 2010 and later. Files that use these properties can still be opened in Excel 2007 but the data bars will be displayed without them.

    type: cell

    This is the most common conditional formatting type. It is used when a format is applied to a cell based on a simple criterion.

    For example using a single cell and the greater than criteria:

    Or, using a range and the between criteria:

    Other types are shown below, after the other main options.

    criteria:

    The criteria parameter is used to set the criteria by which the cell data will be evaluated. It has no default value. The most common criteria as applied to <'type': 'cell'>are:

    between
    not between
    equal to==
    not equal to!=
    greater than>
    less than
    greater than or equal to>=
    less than or equal to

    You can either use Excel’s textual description strings, in the first column above, or the more common symbolic alternatives.

    Additional criteria which are specific to other conditional format types are shown in the relevant sections below.

    value:

    The value is generally used along with the criteria parameter to set the rule by which the cell data will be evaluated:

    If the type is cell and the value is a string then it should be double quoted, as required by Excel:

    The value property can also be an cell reference:

    In general any value property that refers to a cell reference should use an absolute reference , especially if the conditional formatting is applied to a range of values. Without an absolute cell reference the conditional format will not be applied correctly by Excel from the first cell in the formatted range.

    format:

    The format parameter is used to specify the format that will be applied to the cell when the conditional formatting criterion is met. The format is created using the add_format() method in the same way as cell formats:

    In Excel, a conditional format is superimposed over the existing cell format and not all cell format properties can be modified. Properties that cannot be modified in a conditional format are font name, font size, superscript and subscript, diagonal borders, all alignment properties and all protection properties.

    Excel specifies some default formats to be used with conditional formatting. These can be replicated using the following XlsxWriter formats:

    minimum:

    The minimum parameter is used to set the lower limiting value when the criteria is either ‘between’ or ‘not between’ :

    maximum:

    The maximum parameter is used to set the upper limiting value when the criteria is either ‘between’ or ‘not between’ . See the previous example.

    type: date

    The date type is similar the cell type and uses the same criteria and values. However, the value , minimum and maximum properties are specified as a datetime object as shown in Working with Dates and Time :

    type: time_period

    The time_period type is used to specify Excel’s “Dates Occurring” style conditional format:

    The period is set in the criteria and can have one of the following values:

    type: text

    The text type is used to specify Excel’s “Specific Text” style conditional format. It is used to do simple string matching using the criteria and value parameters:

    The criteria can have one of the following values:

    The value parameter should be a string or single character.

    type: average

    The average type is used to specify Excel’s “Average” style conditional format:

    The type of average for the conditional format range is specified by the criteria :

    type: duplicate

    The duplicate type is used to highlight duplicate cells in a range:

    type: unique

    The unique type is used to highlight unique cells in a range:

    type: top

    The top type is used to specify the top n values by number or percentage in a range:

    The criteria can be used to indicate that a percentage condition is required:

    type: bottom

    The bottom type is used to specify the bottom n values by number or percentage in a range.

    It takes the same parameters as top , see above.

    type: blanks

    The blanks type is used to highlight blank cells in a range:

    type: no_blanks

    The no_blanks type is used to highlight non blank cells in a range:

    type: errors

    The errors type is used to highlight error cells in a range:

    type: no_errors

    The no_errors type is used to highlight non error cells in a range:

    type: formula

    The formula type is used to specify a conditional format based on a user defined formula:

    The formula is specified in the criteria .

    Formulas must be written with the US style separator/range operator which is a comma (not semi-colon) and should follow the same rules as write_formula() . See Non US Excel functions and syntax for a full explanation:

    Also, any cell or range references in the formula should be absolute references if they are applied to the full range of the conditional format. See the note in the value section above.

    type: 2_color_scale

    The 2_color_scale type is used to specify Excel’s “2 Color Scale” style conditional format:

    This conditional type can be modified with min_type , max_type , min_value , max_value , min_color and max_color , see below.

    type: 3_color_scale

    The 3_color_scale type is used to specify Excel’s “3 Color Scale” style conditional format:

    This conditional type can be modified with min_type , mid_type , max_type , min_value , mid_value , max_value , min_color , mid_color and max_color , see below.

    type: data_bar

    The data_bar type is used to specify Excel’s “Data Bar” style conditional format:

    This conditional type can be modified with the following parameters, which are explained in the sections below. These properties were available in the original xlsx file specification used in Excel 2007:

    In Excel 2010 additional data bar properties were added such as solid (non-gradient) bars and control over how negative values are displayed. These properties can be set using the following parameters:

    Files that use these Excel 2010 properties can still be opened in Excel 2007 but the data bars will be displayed without them.

    type: icon_set

    The icon_set type is used to specify a conditional format with a set of icons such as traffic lights or arrows:

    The icon set style is specified by the icon_style parameter. Valid options are:

    The criteria, type and value of each icon can be specified using the icon array of dicts with optional criteria , type and value parameters:

    The icons criteria parameter should be either >= or . The default criteria is >= .

    The icons type parameter should be one of the following values:

    The default type is percent .

    The icons value parameter can be a value or formula:

    Note: The icons parameters should start with the highest value and with each subsequent one being lower. The default value is (n * 100) / number_of_icons . The lowest number icon in an icon set has properties defined by Excel. Therefore in a n icon set, there is no n-1 hash of parameters.

    The order of the icons can be reversed using the reverse_icons parameter:

    The icons can be displayed without the cell value using the icons_only parameter:

    min_type:

    The min_type and max_type properties are available when the conditional formatting type is 2_color_scale , 3_color_scale or data_bar . The mid_type is available for 3_color_scale . The properties are used as follows:

    Ссылка на основную публикацию
    Adblock
    detector