Блог

(* Это может быть дублированный вопрос, но было бы удивительно, если бы у нас еще не было этой функции. *)

У меня есть таблица Table5 в столбце C, высота которой может измениться. в столбце E у меня есть формула, например, для извлечения уникальных значений, таким образом, результирующий разлитый диапазон имеет динамическую высоту.

Я хотел бы выполнить условное форматирование для результирующего разлитого диапазона, например, выделить значение, которое больше 5.

Но я не нашел, как определить правило условного форматирования для разлитого диапазона (например, с помощью # ). Я не хочу применять правило условного форматирования ко всему столбцу.

Кто-нибудь знает, как этого добиться?

Единственный способ, о котором я мог подумать, был:

 =AND(ROW($E1)>=4,ROW($E1)<=COUNTA(UNIQUE(INDIRECT("Table5[Coefficient]"))) 4,$E1>5)
 

Используйте приведенную выше формулу в качестве правила условного форматирования для всего столбца E: E.

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

 =AND(ROW(E1)>=4, ROW(E1)<=4 COUNT(UNIQUE(INDIRECT("Table5[Coefficient]"))), [your condition here])
 

Примените правило к E:E , и оно будет вычисляться только True для строк в нужном вам диапазоне.

Обновить

Indirect Ссылка может использовать диапазон разлива, поэтому вы могли бы использовать:

 =AND(ROW(E1)>=4, ROW(E1)<=4 COUNT(INDIRECT("E4#")), [your condition here])
 

Обратите внимание, что Unique это больше не требуется. Кроме того, предполагается, что значения в вашей таблице являются только числами.

Альтернативным обходным путем является использование именованного диапазона, который будет принимать # назначение разлитого диапазона.

Назначьте именованный диапазон «CF_1» как:

 =OFFSET($E$4,0,0,COUNTA($E$4#))
 

Условное форматирование, которое будет применено к E: E, может быть:

 =AND(ROW(E1)=MEDIAN(ROW(E1),MIN(ROW(CF_1)),MAX(ROW(CF_1))),E1>5)
 

По сути, это не слишком отличается от ответов JvdV и профессора Пантлесса, поскольку это сводится к одному и тому же, но у него есть бонус, заключающийся в том, что он будет применяться только к строкам разлитого диапазона (ячейки E1-E3 еще не были приняты во вниманиедля других ответов на сегодняшний день).

Обновить

И на самом деле, вам вообще не нужно использовать именованный диапазон, поскольку этот код также будет принимать # (все еще не могу понять, как применить только к диапазону разлива)

 =AND(ROW(E1)=MEDIAN(ROW(E1),MIN(ROW(E$4#)),MAX(ROW(E$4#))),E1>5)
 

Я использовал несколько разных условных форматов, применяемых ко всему столбцу или группе столбцов. Таблица выглядит следующим образом:

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

Заголовок:

 =AND(OR(ISERR(OFFSET(A1,-1,0)),ISBLANK(OFFSET(A1,-1,0)))=TRUE,ISBLANK(A1)=FALSE)
 

Диапазон:

 =AND(CELL("row",A1)=EVEN(CELL("row",A1)),ISBLANK(A1)=FALSE)
 

Последняя строка (или общая строка):

 =AND(ISBLANK(A1)=FALSE,ISBLANK(A2)=TRUE)
 

Со следующим форматированием:

Если вы не хотите, чтобы это форматирование применялось к ячейкам без разлива, вы можете определить, была ли ячейка разлитой или непосредственно введена. Вы можете сделать это с помощью формулы или написать UDF.

Используя формулу, вам нужно подделать ее. Вы можете использовать ISFORMULA , чтобы найти, где была введена формула разлива, и NOT(ISBLANK()) идентифицировать разлитые строки. Тогда вам придется предположить, что формула, за которой следуют непустые ячейки, не содержащие формул, является разлитой формулой. Могут быть полезны вспомогательные столбцы.

Используя UDF, вы можете напрямую определить, является ли ячейка разлитой. Ниже приведен простой пример. При желании вы можете добавить дополнительные проверки, чтобы определить, действительно ли формула разлилась.

 Public Function isFormulaOrSpill(ByVal rRange As Range) As Boolean
    Dim this_bIsSpill As Boolean
    Dim this_bIsFormula As Boolean
    
    this_bIsSpill = rRange.HasSpill
    this_bIsFormula = rRange.HasFormula
    
    isFormulaOrSpill = (this_bIsSpill Or this_bIsFormula)
End Function
 

Недавно я создавал целые таблицы в виде разлитых диапазонов (включая строки заголовка и всего). Вот пример тех, кто хочет попробовать:

 =LET(
    Column_Key, Table_Status[System],
    Column_FtEstimated, Table_Status[Estimated],
    Column_FtModeled, IF(Table_Status[Modeled]>Table_Status[Estimated],Table_Status[Estimated],Table_Status[Modeled]),

    Categories, SORT(UNIQUE(Column_Key)),
    Array_BoolKey, (TRANSPOSE(Column_Key)=Categories) 0,
    Mask1, TRANSPOSE(ISNUMBER(XMATCH(Column_Filter1,List_Filter1))),
    Mask2, TRANSPOSE(ISNUMBER(XMATCH(Column_Filter2,List_Filter2))),

    Array_BoolMasked, Array_BoolKey,
    Masked_FtModeled, IFERROR(Array_BoolMasked*TRANSPOSE(Column_FtModeled),0),
    Masked_FtEstimated, IFERROR(Array_BoolMasked*TRANSPOSE(Column_FtEstimated),0),
    Array_Ones, SEQUENCE(COLUMNS(Array_BoolMasked),1,1,0),

    Body_Count_Lines, MMULT(Array_BoolKey, Array_Ones),
    Body_Sum_FtModeled, MMULT(Masked_FtModeled, Array_Ones),
    Body_Sum_FtEstimated, MMULT(Masked_FtEstimated, Array_Ones),
    Body_Percent_FtModeled, IFERROR(Body_Sum_FtModeled/Body_Sum_FtEstimated,"-"),

    Total_Count_Lines, IFERROR(SUM(Body_Count_Lines),"-"),
    Total_Sum_FtModeled, IFERROR(SUM(Body_Sum_FtModeled),"-"),
    Total_Sum_FtEstimated, IFERROR(SUM(Body_Sum_FtEstimated),"-"),
    Total_Percent_FtModeled, IFERROR(Total_Sum_FtModeled/Total_Sum_FtEstimated,"-"),

    Array_Seq, {1,2,3,4,5},
    Array_Header, CHOOSE( Array_Seq,  "System", "Lines",  "Modeled Feet", "Estimated Feet", "Percent Modeled"),
    Array_Body, CHOOSE( Array_Seq,  Categories,  Body_Count_Lines,  Body_Sum_FtModeled, Body_Sum_FtEstimated,  Body_Percent_FtModeled),
    Array_Total, CHOOSE( Array_Seq,  "Total",        Total_Count_Lines,  Total_Sum_FtModeled, Total_Sum_FtEstimated,  Total_Percent_FtModeled),

    Range1,Array_Header,
    Range2,Array_Body,
    Range3,Array_Total,
    Rows1,ROWS(Range1), Rows2,ROWS(Range2), Rows3,ROWS(Range3), Cols1,COLUMNS(Range1),
    RowIndex, SEQUENCE(Rows1   Rows2   Rows3), ColIndex,SEQUENCE(1, Cols1),
    RangeTable,IF(
        RowIndex<=Rows1,
        INDEX(Range1,RowIndex,ColIndex),
        IF(RowIndex<=Rows1 Rows2,
            INDEX(Range2,RowIndex-Rows1,ColIndex),
            INDEX(Range3,RowIndex-Rows1-Rows2,ColIndex)
    )),

    Return, RangeTable,
    Return
)