☰

Excel: 조건부 서식을 데이터 위에 추가된 새 행으로 자동 확장

tag-icon • tag-icon microsoft-excel conditional-formatting
Excel: 조건부 서식을 데이터 위에 추가된 새 행으로 자동 확장

여러 행과 열에 걸쳐 많은 값이 있습니다. 각 행에 특정 월의 데이터가 표시되고 가장 최근 값이 위로 오도록 구성되어 있습니다. 따라서 새 데이터로 업데이트할 때 기존 데이터 위에 새 행을 삽입합니다.

이전 범위에 적용한 조건부 서식 규칙이 새 범위(행이 하나 더 있음)에 적용되도록 하려면 어떻게 해야 합니까? 테이블이나 명명된 범위를 사용해 보았지만 이전 데이터 위에 행을 추가했기 때문에 Excel에서는 명명된 범위/테이블을 자동으로 확장하지 않습니다.

편집 : 기존 데이터입니다

여기에 이미지 설명을 입력하세요

그리고 여기에 1월(빨간색으로 표시)을 추가하고 싶습니다. 모든 하위 범주 위에 빈 행(예: 9행)이 있으면 문제를 해결할 수 있지만 안타깝게도 그렇지 않습니다(5행 참조).

여기에 이미지 설명을 입력하세요

답변1

데이터 구조를 염두에 두고 VBA를 사용하면 원하는 것만 도달할 수 있다고 생각합니다. 나는 귀하의 데이터 구조와 유사한 것을 만들고 행이 추가될 때마다 실행되는 스크립트를 작성했습니다.

  1. 파일을 다음 이름으로 저장하세요.Excel 매크로 사용 통합 문서(.xlsm)* Excel 매크로 사용 통합 문서(*.xlsm)
  2. Ctrl+ 로 VBA 편집기 열기F11
  3. VBA 편집기의 왼쪽 창에서 시트를 두 번 클릭합니다.
  4. 다음 코드를 붙여넣으세요.
Private Sub Worksheet_Change(ByVal 대상 범위)
  iTargRowsCnt를 길게 어둡게 표시
  FormatCondition으로 희미한 OFCond
  희미한 sFCondRanges()를 문자열로
  내가 오랫동안 어둡게
  Dim blnFCondRangeModified As Boolean
  sNewAppliesTo를 문자열로 희미하게 표시

  Target.Address = Target.EntireRow.Address인 경우
    iTargRowsCnt = Target.Rows.Count
    Me.UsedRange.FormatConditions의 각 oFCond에 대해
      sFCondRanges() = 분할(oFCond.AppliesTo.Address, ",")
      i = LBound(sFCondRanges)의 경우 UBound(sFCondRanges)로
        Intersect(Me.Range(sFCondRanges(i))(1), Target(1).EntireRow.Offset(iTargRowsCnt))가 아무것도 아닌 경우
          sFCondRanges(i) = Range(Range(sFCondRanges(i)), Range(sFCondRanges(i)).Offset(-iTargRowsCnt)).Address
          blnFCondRangeModified = 참
        종료 조건
      다음 '나는
      blnFCondRangeModified인 경우
        i = LBound(sFCondRanges)의 경우 UBound(sFCondRanges)로
          sNewAppliesTo = sNewAppliesTo & sFCondRanges(i) & ","
        다음
        sNewAppliesTo = 왼쪽(sNewAppliesTo, Len(sNewAppliesTo) - 1)
        oFCond.ModifyAppliesToRange 범위(sNewAppliesTo)
        blnFCondRangeModified = 거짓
        sNewAppliesTo = ""
      종료 조건
    다음 'oFCond
  종료 조건

서브 끝

이 코드는 행을 삽입할 때마다 자동으로 실행됩니다. 하지만 VBA를 사용할 때는 주의하세요. 더 자주 저장하면 위험을 완화하려고 노력했지만 예상치 못한 일이 발생할 수 있습니다. 예를 들어 실행 취소 기능과 잘 작동하도록 코드를 디자인하는 것은 어렵습니다. 이 경우 행을 삽입한 다음 실행 취소를 사용하면 조건부 형식이 왜곡됩니다.

답변2

나는 단지 범위를 고수 할 것입니다. 조건부 서식을 적용할 때 맨 위에 추가 행을 포함시킨 다음 이를 숨기거나 시각적으로 원하는 작업을 수행합니다. (개인적으로 버퍼 행의 목적을 어딘가에 표시하여 실수로 제거되지 않도록 합니다.)

또 다른 방법은 맨 위 행을 복사한 다음 새 행에 서식으로 붙여넣는 것입니다 (현재 컴퓨터에서는 사용할 수 없으므로 조건부 서식에 작동하는지 확인해야 함) . 편집: O365 Business Plus: Excel 2016에서 조건부 서식 작업이 확인되었습니다.

답변3

범위 1:1000을 사용하면 전체 범위(A1:AZ1000 등)로 변경될 수 있지만 행이나 열을 더 추가하면 업데이트되므로 전체 범위를 유지하게 됩니다.

답변4

비슷한 문제로 어려움을 겪은 후 "적용 대상:" 항목에서 테이블 이름을 참조하기만 하면 줄 추가를 포함하여 테이블의 모든 변경 사항이 자동으로 조정된다는 사실을 발견했습니다. :)

관련 정보