Excel - 조건부 형식 지정 - 행 삽입

Excel - 조건부 형식 지정 - 행 삽입

'적용 대상'에서 오프셋 또는 간접을 사용하는 것이 작동하지 않는 것 같습니다.행을 삽입한 후 조건부 포맷이 깨지지 않도록 하는 다른 방법이 있습니까?

예를 들어 $O$19 범위의 조건부 형식이 있다.$O$105. 조건은 '셀 값이 10보다 크면' 빨간색으로 포맷됩니다.

문제는 excel에 행을 삽입하면 이 포맷 범위가 분할되어 2개의 포맷 규칙이 생깁니다.예를 들어 범위가 $O$19,$O$21인 두 가지 규칙의 경우:20번째 줄에 새 행을 삽입하면 각각 $105와 $20입니다.

일반적으로 위와 같은 조건에서는 규칙이 여러 범위로 분할되어 있으면 문제가 되지 않을 수 있습니다.그러나 'Top 10'과 같은 조건에서는 바람직하지 않은 결과를 초래합니다.

나는 다음을 시도했지만 별로 운이 없었다.

1. 간접 사용 시도 - 그러나 Excel은 공식을 해결하고 형식 규칙을 저장하므로 예상대로 삽입과 함께 작동하지 않습니다.
2. 오프셋을 사용해 보았습니다.여기에서도 Excel을 사용하면 위와 같은 범위가 해결됩니다.

행 삽입으로 끊기지 않는 조건 포맷을 쓸 줄 아는 사람?

[편집] 삽입 행이 조건부 포맷 규칙을 분할하는 원인이 아님을 깨달았습니다.행을 복사하여 삽입된 행에 붙여넣습니다.특수 페이스트를 선택하고 수식만 선택하면 문제 없습니다.

이것이 오래된 내용인 것은 알지만, 여기 매우 단순하고 효과가 좋은 다른 솔루션이 있습니다.

필요에 따라 새 행 또는 열을 삽입하기만 하면 됩니다.그런 다음 올바른 조건부 형식을 가진 행/열을 선택하여 복사합니다.방금 작성한 새 행/열에 "Past Special"을 입력하고 "All merge conditional formats" 항목을 선택합니다.이제 조건부 형식 규칙이 자동으로 업데이트됩니다.

해피 엑셀링 =)

이것은 조건부 형식의 일반적인 문제입니다.행을 삽입하거나 항목을 이동할 경우 Excel은 조건부 형식을 셀과 함께 전환하고 원래 형식을 사용하여 셀을 삽입하거나 전혀 삽입하지 않는 것으로 가정합니다.

따라서 변경에 따라 포맷된 범위를 최대한 분할하려고 합니다.불행하게도 "최고"는 별로 좋지 않다.운이 좋은 경우 형식 지정 규칙이 사용자 모르게 복제되고, 운이 나쁜 경우 적용된 범위의 일부 또는 전체에 대해 규칙이 깨집니다.

이것은 특히 ListObjects("Excel 테이블")를 사용하는 경우 문제가 됩니다.몇 개의 행을 삽입하고, 그 순서를 변경하고, 몇 개의 값을 드래그 하면, 다음에 조건 포맷 리스트를 참조할 때는, 수십에서 수백 개의 중복 규칙이 있습니다.(예: http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/)

제 경험상 혼란을 해결하는 가장 빠른 방법은 모든 규칙을 삭제하고 다시 만드는 것입니다.

일부 측면:

• apply-to 범위는 항상 절대입니다.그건 어쩔 수 없어요.
• 설상가상으로 조건부 포맷은 휘발성 수식처럼 취급됩니다.즉, 많은 경우(다른 파일 열기, 스크롤링 등) 재계산됩니다.분할이 인식되지 않으면 잠시 후 애플리케이션 전체의 속도가 크게 느려질 수 있습니다.
• VBA를 사용하는 경우, 적어도 공식에서 다른 워크시트를 참조하는 경우(이름에 유의하십시오) Worksheet_Calculate 이벤트를 사용할 수 있습니다.

보고서를 작성할 때 이 문제가 있었습니다.일단 보고서를 작성하면 변경할 필요가 없습니다만, 작성하는 동안, 새로운 행을 계속 추가해, 새로운 행마다 조건 포맷이 필요하게 됩니다.

이것은 결코 좋은 솔루션은 아니지만, VBA에 의존하지 않고 찾을 수 있는 최선의 솔루션이었습니다.그것은 다음과 같습니다.

a) 조건부 포맷 규칙을 한 번에 전체 컬럼에 적용하도록 한다.

예를 들어 C2와 C17에 조건부 포맷을 설정하는 대신 추가 컬럼을 삽입하고 2행과 17행에 "이것"이라고 쓴 다음 C열의 전체 포맷을 "다른 컬럼이 "이것"이라고 하면 이 포맷을 적용합니다."로 설정합니다.

b) 적용 대상을 $C$1:$C$2로 변경합니다.

c) 변경 및 행 삽입 등

d) 다음으로 돌아가서 적용 대상을 $C로 변경합니다.C달러

이렇게 하면 변경이나 추가를 할 때 조건 포맷은 필요 없고 나중에 모든 것을 원래대로 되돌릴 수 있습니다.

나중에 행을 추가해야 할 경우 먼저 $C:$C에서 $C$1로 변경합니다.$C$2를 변경한 후 $C:$C로 되돌립니다.이렇게 하면 포맷 규칙을 완전히 처음부터 다시 작성할 필요가 없습니다.이전에 제가 했던 것처럼 포맷 규칙을 모두 삭제하고 욕하고 다시 시작할 수 있습니다.

1열 또는 2열의 맨 위에 행을 삽입할 계획이라면 이 방법은 사용할 수 없지만, 항상 변경하지 않는 다른 행으로 설정할 수 있습니다.

이것은 꽤 오래된 주제이지만, 새로운 행을 삽입할 때 내 Excel 시트도 조건 포맷이 중복되는 문제가 있었습니다.

나는 그것을 회피할 수 있었다.다른 사람들과 공유하게 해주세요, 도움이 될 수도 있어요.

제 경우, 모든 조건부 포맷 규칙이 테이블 전체에 적용되었습니다.새 행을 삽입할 때 특정 규칙만 중복된다는 것을 깨달았습니다.이러한 규칙은 다른 행의 값을 비교하는 공식 기반입니다.

내 경우 인접한 두 행의 값이 다를 때 수평 테두리를 렌더링하려고 했습니다. 예를 들어 다음과 같습니다.

=$A2 <> $A1

OFFSET을 사용하여 이전 행을 참조하면 모든 것이 올바르고 중복된 조건부 형식 규칙은 없습니다.

=$A2<>OFFSET($A2; -1; 0)

이 조건 포맷 수식을 숨겨진 열에 넣었지만 결과는 같아야 합니다.

삽입 행이 조건부 포맷 규칙을 분할하는 원인이 아님을 깨달았습니다.행을 복사하여 삽입된 행에 붙여넣습니다.특수 페이스트를 선택하고 수식만 선택하면 문제 없습니다.

다만, 조건 포맷의 「적용처」필드에 「INDIRECT」나 「OFFSET」를 사용할 필요는 없을까.만약 그렇다면, 문제가 될 것입니다.

오래된 투고인 것은 알고 있습니다만, 같은 문제에 부딪쳐, 그 후 분할 조건부 포맷의 룰을 취득하지 않는 방법을 알게 되었습니다.

Excel 2010 스프레드시트에서 B열에 날짜를 입력합니다.날짜 엔트리에 손가락을 댔을 때가 있기 때문에 조건부로 포맷하고 싶었습니다.처음에는 범위를 선택하고 있었습니다(B2:B1960)의 경우 조건부 포맷 규칙에서 내 공식은 "=B2:B1960>TODAY().

기존 행 사이에 새 행을 삽입하기 전까지는 잘 작동했습니다.규칙은 OP에서 설명한 대로 분할됩니다.우연히 몇 개의 다른 웹사이트를 보고 답을 알려주는 마이크로소프트 오피스 사이트를 발견했습니다.포맷할 범위를 강조 표시하되 수식을 "=B2>로 변경하도록 언급했습니다.투데이().

수식을 변경한 후 기존 행 사이에 새 행을 삽입할 수 있으며 이전과 같이 분할 규칙을 가져올 수 없습니다.여기 그 웹 페이지 링크가 있습니다.http://office.microsoft.com/en-us/excel-help/use-a-formula-to-apply-conditional-formatting-HA102809768.aspx

행을 삽입할 때 형식을 다른 행에서 복사하지 않는 것이 좋습니다.특수 복사만 수식을 붙여넣습니다.그 후 조건부 포맷은 분할되지 않습니다.

1) 새 행을 삽입 2) 복제할 행을 복사 3) 특수 "Merge Conditional Formating" 붙여넣기

작성한 워크북을 다른 사람에게 공유시키고 싶은 경우 직감적이지 않고 사용자 트레이닝이 산더미처럼 쌓기

이 문제에 대한 나의 해결책은 원본 복사본에서 포맷을 지우는 것이었습니다.순서:

1. 소스를 클립보드에 복사
2. 새 Excel 문서 열기
3. 특수 붙여넣기, 공식 선택(값 및 공식 복사 및 형식 생략)
4. 클립보드에 복사
5. 이제 사용하기 전에 소스에 다시 붙여넣을 수도 있고, 조건 포맷을 사용하여 빈 행을 시트에 삽입할 수도 있습니다(행만 삽입해도 조건 포맷 규칙의 범위가 변경되지 않음). 클립보드 내용을 새 행에 붙여넣을 수도 있습니다.

이것은 MS Excel 2016을 사용한 나에게 효과가 있었다.

이전에 투고된 내용에 동의합니다.값 복사 및 붙여넣기(또는 수식 붙여넣기)는 조건부 포맷을 분할하지 않기 위해 완전히 작동합니다.

그러기엔 좀 게을러요.그리고 저는 제 스프레드시트를 사용하는 사람들이 그렇게 하지 않기를 바랍니다.값을 복사하여 붙여넣는 것도 잊지 않을 것입니다.:(

이 솔루션이 고객의 요구에 부합할지는 모르겠지만 워크북을 열 때마다 모든 조건부 포맷을 삭제하고 올바른 조건부 포맷을 다시 적용했습니다.

이 매크로는 워크북을 열 때마다 실행되므로 복사 및 붙여넣기 방법을 변경할 필요가 없습니다.그들은 매크로가 거기에 있다는 것을 알 필요가 없다.매크로를 수동으로 실행할 필요가 없습니다.매크로는 자동으로 실행됩니다.이것이 더 나은 사용자 경험을 만들어 낸다고 생각합니다.

이 코드는 일반 모듈이 아닌 "이 워크북" 모듈에 복사하여 붙여넣어야 합니다.

Private Sub Workbook_Open()
'This will delete all conditional formatting and reapply the conditional formatting properly.
'After copying and pasting the conditional formatting get split into two or more conditional formattings. After a few
'weeks there are so many conditional formattings that Excel crashes and has to recover.

Dim ws As Worksheet, starting_ws As Worksheet


Set starting_ws = ActiveSheet   'remember which worksheet is active in the beginning
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "InvErr" Then
        ws.Activate
        Cells.FormatConditions.Delete
        ''Every Other Row Tan
        Range("A4:M203").FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(ROW(A4))"
        Range("A4:M203").FormatConditions(Range("A4:M203").FormatConditions.Count).SetFirstPriority
        Range("A4:M203").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        Range("A4:M203").FormatConditions(1).Interior.ThemeColor = xlThemeColorDark2
        Range("A4:M203").FormatConditions(1).Interior.TintAndShade = 0
        Range("A4:M203").FormatConditions(1).StopIfTrue = False

        ''Highlight Duplicates Red
        Columns("B").FormatConditions.AddUniqueValues
        Columns("B").FormatConditions(Columns("B").FormatConditions.Count).SetFirstPriority
        Columns("B").FormatConditions(1).DupeUnique = xlDuplicate
        Columns("B").FormatConditions(1).Font.Color = -16383844
        Columns("B").FormatConditions(1).Font.TintAndShade = 0
        Columns("B").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        Columns("B").FormatConditions(1).Interior.Color = 13551615
        Columns("B").FormatConditions(1).Interior.TintAndShade = 0
        Columns("B").FormatConditions(1).StopIfTrue = False
    End If
Next

starting_ws.Activate   'activate the worksheet that was originally active
Application.ScreenUpdating = True

End Sub

이 정도면 충분히 효과가 있었어

Sub ConditionalFormattingRefresh()
'
' ConditionalFormattingRefresh Macro
'

'Generales
Dim sh As Worksheet
Dim tbl As ListObject
Dim selectedCell As Range
Set sh = ActiveSheet
Set tbl = Range("Plan").ListObject
Set selectedCell = ActiveCell

'Rango a copiar
Dim copyRow As Range
Set copyRow = tbl.ListRows(1).Range

'Rango a restaurar
Dim startCell As Range
Dim finalCell As Range
Dim refreshRange As Range
Set startCell = tbl.DataBodyRange.Cells(2, 1)
Set finalCell = tbl.DataBodyRange.Cells(tbl.ListRows.Count, tbl.ListColumns.Count)
Set refreshRange = Range(startCell.Address, finalCell)

'Ocultar procesamiento
Application.ScreenUpdating = False
Application.EnableEvents = False

'Borrar formato corrupto
refreshRange.FormatConditions.Delete

'Copiar
copyRow.Copy
'Pegar formato
tbl.DataBodyRange.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Retornar a la normalidad
selectedCell.Select
    Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

1. 예를 들어 전체 열에 조건부 형식을 적용합니다."$A:$A""$A2: $A10"과 같은 서로 다른 셀 참조를 제거합니다.

2. "insert row"는 조건부 서식이 깨지므로 사용하지 마십시오.대신 Excel 테이블의 마지막 행 뒤에 새 데이터 엔트리를 새 행으로 추가하십시오.vba를 사용하는 경우 vba를 사용하여 마지막 행을 식별합니다.

3. "All merge conditional formats" 옵션을 사용하여 마지막 행에서 새 행으로 열의 현재 수식 및 형식을 "Paste Special"합니다.

   last_row = 셀(Rows).카운트, 1)종료(xlUp).배를 젓다

   행(last_row)알았다.

   행(last_row + 1)특수 xlPasteAllMergingConditionalFormats 붙여넣기

4. 필요에 따라 vba를 사용하여 테이블을 다시 정렬합니다.

   예: 범위("A:AU")AutoFilter 필드:=46, Criteria1:="참"

다음은 여러분이 올바른 방향으로 나아갈 수 있는 비슷한 맥락입니다.

조건 포맷에 대해 워크시트_변경 이벤트를 사용하는 방법

코멘트에서 언급한 VBA 접근법과 함께 삽입물(테스트되지 않음)의 영향을 받지 않을 수 있는 R1C1 스타일 포맷에 대한 회피책의 개요를 설명합니다.

새로운 행 또는 열을 삽입하기 위해 일관되게 기능하는 것처럼 보이는 간단한 프로세스를 발견했으며, (최소한 Office 2010에서는) 다음과 같이 조건부 포맷 규칙의 연속성을 유지합니다.

1. 보존할 조건부 서식이 포함된 행 또는 열의 위, 아래 또는 왼쪽 또는 오른쪽에서 원하는 수의 새 행 또는 열을 간단히 "삽입"합니다.

주 a) 삽입된 행 또는 열에 조건 포맷이 자동으로 적용되므로 더 이상 작업을 수행할 필요가 없습니다.포맷은 인접 행 또는 열에서 상속되어야 합니다.b) 테두리 포맷도 새로 삽입된 셀에 복사되어야 합니다.

2. 새로 삽입된 행과 인접한 행, 열 또는 범위(클릭하여)를 선택합니다.이 행에는 복사할 조건부 형식(및 수식 및 데이터(해당하는 경우)이 포함됩니다.

3. + 기호가 나타날 때까지 선택한 범위의 왼쪽 아래 또는 오른쪽 아래 모서리에 마우스를 놓습니다(줄 크기 변경 가젯과 혼동하지 마십시오).

4. +를 좌클릭한 채로 원하는 행, 열 또는 범위를 드래그하여 포맷한 후 놓습니다.

메모: "Format values where this formula is true" (이 수식이 true인 값 포맷) 필드에 조건 포맷 규칙을 하나밖에 참조하지 않고 만듭니다.=AND($B8=sublic$C8=sublic$D8=sublic$K8<>")입니다. 여기서 이 규칙은 범위를 말하기 위해 적용됩니다.=$B$8:$D$121,$J$8:$M$121.

2013년에 포맷 규칙이 분할/복제된 것을 발견하면 각 포맷에 대해 새로운 명명 범위를 정의합니다.그런 다음 적용을 =[이름 지정 범위]로 설정합니다.Excel은 지정된 범위를 실제 범위로 바꿉니다.그런 다음 중복 형식을 삭제합니다.

Excel 스킬 레벨이 혼재된 다른 사람이 사용할 수 있도록 솔루션을 구축하고 있기 때문에, 카피와 페이스트의 특정 방법을 기억하는 것보다 쉽고 일관성 있는 솔루션이 필요했습니다.

Excel 2016에서는 선택한 범위의 테이블을 삽입하여 구조화된 참조를 사용할 수 있습니다(예: tblTOP[Type], tblTOP라는 이름의 테이블의 유형 열에 있는 참조 데이터).

그 후 마이크로소프트 사이트에서 CF의 공식 부분에서 표를 참조하는 효과적인 방법을 보여주는 답변을 찾았습니다: 조건부 포맷 구조 참조

---

자, 그럼 이제...

제가 하고 있는 일은 다음과 같습니다.

tbl Top 열

A에서E 사이의 Type 값을 변경하면 해당 행이 대응하는 색으로 변경되도록 조건부 포맷을 설정했습니다(예: B는 행을 녹색으로 바꿉니다).

이는 =CJB("tblTOP[Type]")=" 공식을 사용하여 달성되었습니다.B"

행을 추가해도 두 번째 행에 동일한 포맷이 적용되었습니다. : (

두 줄 사이에 CF가 끊어졌습니다.

CF 공식은 효과가 있었다.

한마디로 다음 공식은 CF 규칙을 특정 행에 적용하고 추가 또는 삭제되는 행에는 영향을 주지 않기 위해 생각해 낸 것입니다.

=CJB("tblTOP[@Type]")="B"

구조화된 참조 앞에 '@'를 추가하면 해당 행에 대해서만 문제가 계속 발생합니다.좋네요.

이제 탭을 통과하거나 상황에 맞는 메뉴를 사용하여 새 행을 추가할 수 있습니다. 그러면 해당 행의 색상만 결정하기 위해 유형 선택을 기다립니다.

새 행이 새로 추가되었습니다.

예상대로 작동하는 새 행

이 표의 목적은 최종 사용자가 데이터를 입력하고 필요에 따라 행을 추가/삭제하는 것이기 때문에 셀 붙여넣기에서는 테스트하지 않았습니다.

테이블에서 조건 포맷을 하는 데 도움이 되었으면 합니다.

Excel Mac 2011에서 작동하도록 하기 위해 다음과 같은 절차를 밟았습니다.

• 새 행 삽입
• 위의 것을 복사(조건부 포맷이 이미 적용되어 있음)
• 를 강조 표시합니다.

CF 규칙은 플릿되지 않고 추가 행을 포함하도록 업데이트되었습니다.

언급URL : https://stackoverflow.com/questions/12480934/excel-conditional-formatting-insert-row