>source

둘 이상의 범위에 조건부 서식을 적용하려고합니다. 서식은 항상 동일하지만 조건을 포함하는 셀은 동일하지 않습니다.

예를 들면 :

`=OR($B$11="金",$B$11="木")` //this custom formula applies to range C5:Q14  
'=OR($B$22="金",$B$22="木")  //this custom formula applies to range C16:Q25

첫 번째 수식은 B11 셀에서 조건을 확인합니다.
두 번째 수식은 B22 셀에서 조건을 확인합니다.

의도 한대로 작동합니다. 그러나 이러한 범위를 많이 설정해야하며 복사하거나 붙여 넣으면 서식 지정 범위가 이미 수식에있는 항목에 추가되고 모두 동일한 셀에서 조건을 확인합니다. 모든 범위에 대한 조건을 수동으로 설정하면 필요한 것을 얻을 수 있지만 수식이 실행 가능하지 않은 경우 시트 수식이나 스크립트를 통해 더 나은 방법이 있는지 알고 싶습니다.

예는 시트를 참조하십시오

https://docs.google.com/spreadsheets/d/1G0eUibjNKlZ1fDm9id5SPFNlF392cuEPzNDMB8E5jyU/edit?usp=sharing


  • 답변 # 1

    Apps Script 솔루션

    AFAIK 가장 좋은 방법은 Apps Script를 사용하는 것입니다.

    다음과 같은 스크립트로 :

    function createRule() {
      // Get Ranges
      var sheet = SpreadsheetApp.getActiveSheet();
      var cellToWatch = sheet.getActiveCell()
      var rangeForRule = cellToWatch.offset(-6, 1, 10, 15)
      
      // Create absolute Cell reference
      var cellNotation = cellToWatch.getA1Notation()
      var patt = /([a-zA-Z]+)([\d]+)/
      var result = patt.exec(cellNotation)
      var absoluteRef = "$" + result[1] + "$" + result[2]
      // Create Conditional Formatting rule
      var rule = SpreadsheetApp.newConditionalFormatRule()
        .whenFormulaSatisfied('=OR('+ absoluteRef +'="金",'+ absoluteRef +'="木")')
        .setBackground("#00FF00")
        .setRanges([rangeForRule, cellToWatch])
        .build();
      var rules = sheet.getConditionalFormatRules();
      rules.push(rule);
      sheet.setConditionalFormatRules(rules);
    }
    
    

    이 스크립트

    시트가 항상 정확히 동일한 형식을 갖는다 고 가정합니다.

    다음과 같은 기준으로 셀을 선택해야합니다.

    지금은 스크립트 편집기에서 실행해야합니다.

    선택한 셀의 위치에 따라 서식 지정 규칙을 적용 할 범위를 선택합니다. var rangeForRule = cellToWatch.offset(-6, 1, 10, 15)

    선택한 셀의 A1 표기법을 가져오고 RegEx를 사용하여 A1 표기법의 절대 버전을 만듭니다. A1 => $A$1

    방금 만든 참조를 사용하여 조건부 서식 규칙을 만듭니다.

    필요에 맞게 색상의 HEX 값을 수정해야합니다. "#00FF00"

    이 규칙을 몇 번의 마우스 클릭만으로 만들 수 있도록 스프레드 시트에서 사용자 지정 사이드 바를 만들 수 있습니다.

    참고 문헌

    Apps Script 메인 페이지

    RegEx

    시트 범위 개체

    조건부 형식 규칙 작성기

    조건부 형식 규칙 클래스

  • 답변 # 2

    나는 당신의 문제의 3 가지 가능한 추상화를 봅니다.

    1) 알려진 길이의 행 그룹에 의한 조건부 서식. 2) 복사-붙여 넣기 친화적 인 방식으로 섹션 상단의 행 인덱스를 식별하는 방법도우미 열이 허용되는 경우. 3) 특정 행 위의 열에서 비어 있지 않은 맨 위의 셀을 감지합니다.

    물론 3)이 가장 일반적입니다. 그러나 1) 또는 2) 만 필요한 경우 더 간단한 솔루션을 사용하는 것이 좋습니다. 그래서 각각에 대해 설명하겠습니다.

    <시간 /> 알려진 길이의 행 그룹에 의한 조건부 서식

    조합 사용 indexmatch . (요구 사항이 더 복잡해지면 indirect .)

    예를 들어, 헤드 행을 참조하기 위해 11 개 그룹의 행이 필요한 경우 다음을 수행 할 수 있습니다.

    =match(index($A:$A,floor(row(B1)/11)*11+1,1),{"金";"木"},0)
    
    

    B1 : C11에서;11 행마다 A : A에 평일 문자가있는 경우.

    조건부 서식에서는 (고정) 범위와 상대 범위를 참조하는 수식을 지정합니다. 그런 다음 Google Sheet는 (고정) 범위에 대해 셀 인덱스를 반복합니다. 즉, 가장 왼쪽 상단 셀부터 시작하여 1 행 아래로 이동하면 수식의 (상대) 범위에 모두 행 인덱스가 +1 추가됩니다. ;한 열 오른쪽으로 이동하면 수식의 (상대적) 범위에 모두 열 색인에 +1이 추가됩니다. $기호는 정상적으로 작동합니다.

    수식에서 반복 할 수있는 유일한 (상대적) 범위는 다음과 같습니다. B1 . 따라서 여기서 일어나는 일은 다음과 같습니다. (고정 된) 범위를 따라 이동할 때 B1:C11 , 예를 들어 C10 , 공식의 (상대적) 범위는 C10 (동시에) C10 범위 내 가장 왼쪽 상단에서 9 행 아래에 1 열 오른쪽에 있습니다. B1:C11 .

    테스트:

    입력

    결과

    섹션 상단의 행 인덱스 식별/지정

    도우미 열을 사용할 수있는 경우 섹션 위치의 함수로 행 인덱스를 쉽게 지정할 수있는 방법이 있습니다.

    예를 들어 섹션이 B1:D10 . 이 섹션을 복사하여 붙여 넣기를 원합니다. B21:D21 그리고 다른 모든 것을 유지하기를 원합니다.

    섹션 상단에 레이블을 붙이기 위해 A : A를 사용하여 허용 할 수 있다면 간단합니다.

    섹션 당 행 수를 알 필요가 없습니다.미리.

    A1 , 입력 =row(A1) . 에 A2 , 입력 =A1 . 이제 섹션에서 수식을 드래그합니다. ...에 A10 .

    이제 간단하게 C1 : D10에 조건부 서식을 넣을 수 있습니다.

    =match(index($B:$B,A1,1),{"金";"木"},0)
    
    

    물론 문자열 비교를 위해 더 간단한 공식을 사용할 수 있습니다.

    특정 행 위의 열에서 비어 있지 않은 맨 위에있는 셀 감지

    알려진 수의 행이있는 섹션에 대한 고정 템플릿이없고 시트를 도우미 열로 정리해야하는 경우 유일한 방법은 열의 지정된 행 위에 비어 있지 않은 마지막 셀을 감지하는 것입니다. 평일 캐릭터가 있습니다.

    여기에서 다양한 변형이 가능합니다. 비어 있지 않은 셀을 감지 할 수 있습니다. 또는 문자열 목록에서 문자열의 존재를 감지 할 수 있습니다. 셀의 내용을 검색하거나 행 인덱스를 가져올 수 있습니다. 등등. 우리는 여기서 가장 간단한 것을 할 것입니다.

    당신의 평일 캐릭터가 A:A B : C의 조건부 서식이 필요합니다. 그런 다음 조건부 서식 탭에서 범위를 B : C로, 수식을 다음과 같이 입력합니다.

    =match(+SORT($A$1:$A1,$A$1:$A1<>"",,ROW($A$1:$A1),),{"金";"木"},0)
    
    

    여기서 일어나는 일은 +SORT($A$1:$A1,$A$1:$A1<>"",,ROW($A$1:$A1),) 문제의 행을 기준으로 A 열에서 비어 있지 않은 마지막 셀의 내용을 선택합니다.

    방법은 다음과 같습니다. SORT 우리를 위해 결과를 얻습니다.

    두 번째 입력 SORT TRUE는 비어 있지 않음을 의미하는 부울 열로 평가됩니다. $A$1:$A1 상대적 B1:C 세포를 골라내는 것을 의미합니다. A1:A 위와 문제의 셀을 포함합니다. 세 번째 입력을 비워 두는 것은 내림차순을 의미하며, 이는 다시 TRUE가 FALSE보다 먼저 오는 것을 의미합니다. + Google Sheet에 배열 출력의 첫 번째 요소를 출력하도록 지시합니다. 여기까지, +SORT($A$1:$A1,$A$1:$A1<>"",) 비어 있지 않은 최상위 셀을 출력합니다. 비어 있지 않은 모든 셀 내에서 마지막 셀을 원합니다. 따라서 행 인덱스에 대한 네 번째 입력과 내림차순에 대한 다섯 번째 입력입니다. 가장 높은 행 인덱스가있는 비어 있지 않은 셀 A1:A 당신이 원하는 세포입니다.

    <시간 />

    당면한 작업에 대한 정확한 요구 사항을 식별하는 것은 질문자에게 달려 있습니다.

    나는 질문자가 요구 사항을 요약하고 질문에 그것들을 서술하는 것이 중요하다고 말하고 싶습니다. 답에서 당면한 과제를 평가하는 방법을 찾는 것과는 반대로 말입니다.

    이것이 바로 다른 사람들이 배우고 적응할 수있는 프로그래밍 질문과 아웃소싱 쿼리를 구별하는 것입니다.

  • 이전 docker exec의 속도를 높이는 방법이 있습니까?
  • 다음 Javascript를 사용하는 Mirth getMessageCount가 작동하지 않습니다