조건부서식 내에서 INDIRECT 함수와 AND 함수를 함께 사용할 수 없나요?

함수/공식
작성자
나는나
작성일
2022-01-22 20:43
조회
373

안녕하세요.

"관련 링크"의 강의를 참고해서 조건부서식의 함수를 수정해봤습니다.

=AND(INDIRECT("AB"&ROW())>3, INDIRECT("D"&ROW())<>"")

위와 같은 함수를 셀에 넣어서 확인해보면 True와 False 값이 정확하게 출력됩니다.

그런데 조건부서식 내 수식에 입력하면 서식 적용이 반영되지 않습니다. (False값이 출력된 것과 같습니다.)

이상해서 AND 조건을 빼고 =INDIRECT("AB"&ROW())>3 이렇게 조건 하나만 넣으면 조건부 서식이 반영됩니다.

INDIRECT를 사용하지 않고 AND 함수를 넣었을 때는 정상적으로 작동되던 걸 이번 강의를 보고 INDIRECT로 수정해 본 겁니다.

조건부서식에 INDIRECT와 AND를 같이 사용하면 안 되나요?

회원등급 : 17레벨
포인트 : 1847 EP
총질문 : 13 개 (마감율 : 46%)
채택답변 : 32 개
전체 12

  • 2022-01-22 21:30

    저도 원인은 모르겠지만 조건부서식에는 INDIRECT + AND 조합이 안되네요

    엑셀 함수중에 INDIRECT, OFFSET, INDEX 등 함수들은 이름범위에 사용할 때 중복사용이 안되는 것 처럼, 조건부서식도 그런 것 같습니다

    아래 첨부파일처럼 혼합참조로 한번 사용해보심이..


    • 2022-01-22 21:34

      @엑셀은즐거워 님 파일 첨부가 빠졌네요

      Screenshot_1.png

      첨부파일 : 조건부서식.xlsx


      • 2022-01-23 14:49

        @엑셀은즐거워 님 먼저 답변과 첨부 파일 감사합니다.

        첨부 파일의 조건부서식에 사용한 수식은 이미 제가 사용했던 수식입니다. 오빠두 강의에서 언급했듯이, 테이블에 필터링 또는 정렬이 적용되는 경우 이 조건부서식이 틀어지게 됩니다. 필터링을 사용해도 조건부서식이 틀어지지 않도록 예방하는 방법이 바로 INDIRECT 함수로 셀 참조를 하는 거라고 설명해 주셨거든요.

        그래서 기존에 사용했던 수식(엑셀은즐거워 님과 비슷한)을 INDIRECT 함수로 변경했습니다. 이 수식을 셀에 입력해서 조건에 따라 TRUE/FALSE 값이 출력되는 것을 확인한 건 아래 캡쳐를 보면 아실 겁니다. 그런데 정작 조건부서식에 이 수식을 사용하면 전부 FALSE가 반환됩니다. AND(INDIRECT 조건1, INDIRECT 조건2) 이게 원래 불가능한 건지 모르겠네요.

        강의에서 INDIRECT를 조건부서식에 사용하라고 알려주신 오빠두 님이 좀 도와주시면 좋겠는데요.


        • 2022-01-26 00:17
          채택된 답변

          @나는나 님 위에도 적어드렸지만, 엑셀 몇몇 함수는 VOLITILE 함수라고하여 이름범위에 제한적으로만 사용할 수 있습니다

          https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation

          가장 단편적인 예로 OFFSET 함수에 OFFSET 동적범위를 다시 사용할 수 없는 것 또한 마찬가지고요. 자세한 원인은 모르지만 엑셀 기능 설정상 순환참조가 발생해서 사용 못하도록 막아놓은게 아닐까 생각됩니다.

          직접 테스트해봐서 아시겠지만 INDIRECT + AND 함수도 셀 안에서는 잘 동작하지만, 조건부서식으로는 잘 동작하지 않습니다. => VOLITILE 함수 한계

          따라서 두 조건을 모두 만족할 경우 TRUE를 출력하는 보조열을 하나 추가해서, 보조열을 기준으로 조건부서식을 적용하면 바로 해결되실겁니다.


          • 2022-01-26 13:40

            @엑셀은즐거워 님 추가설명 덕분에 제 질문과 직접적인 관련은 없겠지만 공부 차원에서 사용자정의 함수와 Application.Volitile의 기능도 찾아보게 되었습니다.

            말씀하신 대로 제가 원했던 수식이 셀 안에서는 잘 작동하는데 조건부서식의 수식으로는 적합하지 않은가 봅니다. 특히 조건이 하나가 아닌 두 개의 조건을 AND로 묶을때 말이죠.

            보조열을 추가해서 거기에 결과를 출력하기에는 엑셀 양식을 건드려야 해서 TRUE/FALSE를 가려낼 다른 논리를 찾아 적용했습니다. 이 게시글의 댓글 정렬을 "작성순"으로 보시면 맨 마지막 댓글로 제가 자문자답이라고 쓴 글이 있는데, AND 없이 셀 하나만 INDIRECT함수와 연산을 사용해 조건값을 구하는 방식으로 수정했습니다.


  • 2022-01-24 15:36

    결국 자문자답합니다.

    아무래도 조건부서식에서 INDIRECT+ROW()와 AND 함수는 함께 사용할 수 없는 것 같습니다. 그래서 TRUE와 FALSE를 가려낼 다른 논리를 찾았습니다.

    필요한 조건이 AB>3이고 D<>""(D의 값이 1 이상)이었습니다. AB열에 수식이 입력되어 겉으로 보기에는 비어 있지만 ""가 들어있는 셀들에 서식이 적용되는 것을 제외하기 위해 두 번째 조건인 D<>""가 필요했던 것입니다. AB열의 비어 있지만 ""가 들어있는 행의 D열에는 수식 없이 빈 셀이기 때문입니다.

    =INDIRECT("AB"&ROW())*1>3 이렇게 AB열 하나만 사용해서 해결했습니다. 수식이 들어있는 빈 셀("")*1은 TRUE가 나올 수 없기 때문에 자동으로 조건부서식에서 제외되고 결국 AB의 값이 3 이상인 셀들만 서식이 적용됩니다. 두 번째 조건이었던 D열은 필요 없어졌기 때문에 AND함수를 지웠고 이렇게 조건부서식이 해결되었습니다.


  • 2022-01-23 11:24

     

    근본적으로 식의 설정이 잘못되어 있지않나요?

    =INDIRECT("AB"&ROW())>3 에서 ROW()가 7 이라면, =INDIRECT(AB7)>3 로 치환될 것이고,

    [AB7] 셀의 값을 주소로 하는 곳이 3보다 큰지를 묻는 식으로 바꿔집니다. 그런데 AB7에는 아무 자료가 없었기 때문에 이것은 사용자가 지정한 범위의 이름으로 설정될 수 없지요. 만약 AB7 셀에 'THERE' 라는 영역 이름이 있었다 하더라도 'THERE' 영역의 값이 각각 {3, 6, 8, 2, 4} 이었다면 =INDIRECT(AB7)>3 은 {FALSE, TRUE, TRUE, FALSE, TRUE} 의 배열 값으로 얻어집니다.

    그러나 조건부 서식은 각각의 셀마다 TRUE 또는 FALSE의 하나의 논리값에 의하여 서식을 지정하는 기능합니다. 결론적으로 말하면 조건부 서식에 입력하는 식은 배열수식이 아니라는 것입니다. 질문자가 제시한 식

    =AND(INDIRECT("AB"&ROW())>3, INDIRECT("D"&ROW())<>"") 은 배열수식이므로 조건부 서식의 조건식으로 접합하지 않다는 것입니다.


    • 2022-01-23 15:18

      @김학동 님 답변 감사합니다.

      그럼 조건부서식에 INDIRECT 함수를 사용한 게 적합하지 않다는 말씀이신가요?

      =AND(INDIRECT 조건1, INDIRECT 조건2)에서 AND 함수를 빼고 조건1 하나만 넣으면

      =INDIRECT("AB"&ROW())>3 TRUE/FALSE를 제대로 반환하여 서식이 적용됩니다.

      INDIRECT("AB"&ROW())와 INDIRECT("D"&ROW())는 모두 '단일 셀'입니다. '배열값'이 아닙니다. 예를 들어 주신 것처럼 ROW()가 7이면, [AB7]과 [D7] 셀의 값이 각각 '3보다 크고', '빈셀이 아니면' TRUE가 반환되어 서식이 적용되어야 합니다. "AB7에는 아무 자료가 없었기 때문에 이것은 사용자가 지정한 범위의 이름으로 설정될 수 없지요."라는 설명이 이해가 잘 안 가는군요. AB 컬럼에는 값이 정상적으로 들어가 있습니다. 캡쳐 이미지 참조바랍니다. 왼쪽의 빨간색 박스가 AB 컬럼에 입력된 값이고 오른쪽의 박스가 AND(조건1, 조건2)의 결과값입니다.

      INDIRECT 함수를 사용한 건 질문글 본문에서도 밝혔듯이 오빠두 님 강의 "초보자를 위한, 엑셀 일정관리 서식(투두리스트) 만들기"에서 필터링해도 틀어지지 않게 하려면 INDIRECT를 사용하라고 하셔서입니다.

      제가 사용한 함수에 문제가 있다면 어떻게 수정하면 좋을지 의견을 부탁드립니다. =AND($AB1>3,$D1<>"") 이런 수식은 말고요. 이미 썼던 건데 표 안에서 행 추가, 삭제 등 작업시 조건부서식의 적용 영역이 틀어지게 됩니다. 동일한 규칙이 여러 개 생겨서 정확하게는 매우 지저분해집니다.


      • 2022-01-23 15:44

        @나는나 님 이 시트만 파일로 올려보시지요.  정리하여 답 드릴께요. 이렇게 계속 사진으로만 자료를 주면 답변자는 자료를 다시 타이핑 할 수도 없고... 난감하거든요. 위 사진에서 [AB7] 셀은 어디이고, [D7]셀은 어디인가요?

        한편 [AB7]의 값이 76이라할 때, =INDIRECT("AB7") 과 =INDIRECT(AB7) 을 구분하여 설명하면,
        앞의 것은 주소가 AB7인 곳의 값 76을 가져오라는 것이고,
        뒤의 것은 AB7에 기억된 값 76을 주소로 하는 영역의 값을 가져오라는 것인데, 셀주소 76은 어디일까요? 이 주소를 찾을수 없으므로 #REF 에러를 내보내겠지요...


        • 2022-01-23 22:44

          @김학동 님 이제 보니 김학동 님께서 제 질문 내용을 약간 오해하고 계신 듯 합니다.

          =INDIRECT("AB"&ROW())에서 현재 ROW가 7행이라고 가정하면 결국 =INDIRECT("AB7")과 동일하여 셀 [AB7]의 값을 반환합니다. 제 질문 어디에도  =INDIRECT(AB7)(따옴표가 없는)와 같은 내용이 없습니다.  =INDIRECT(AB7) 이렇게 하면 당연히 #REF 오류가 나지요. 김학동 님께서 예를 들면서 AB7을 이름정의나 변수처럼 생각하셔서 배열이나 뭐 이런 쪽으로 잘 못 흘러가고 계신 듯 한데, 전 처음부터 명확히 AB열, ROW행의 값을 참고하기 위해 INDIRECT 함수를 사용했습니다.

          제 질문의 핵심은 조건부서식의 수식에서 INDIRECT 또는 AND 함수를 각각 사용하면 잘 작동하는데, 두 함수를 동시에 사용하면 안 되는 것 같다는 거였습니다.

          제 엑셀 파일은 실제 업무에서 사용하는 자료라 첨부가 불가함을 양해바랍니다. AB열 대신 그냥 A열을, D열 대신 그냥 B열이라 생각하고 숫자 값을 아무거나(0~9) 입력을 해보세요. 제가 서식에서 조건을 걸었던 것처럼, A열이 3보다 크고, B열이 빈셀(또는 0)이 아니면 A열에 서식을 적용하는 수식을 INDIRECT와 AND로 만들어 보시면 바로 결과를 확인하실 수 있을 것 같습니다. 처음 답변해 주신 엑셀은즐거워 님께서도 이렇게 직접 조건부서식에 수식을 적용해 보고 안 되는 것 같다고 그냥 혼합참조를 써야 할 것 같다는 의견을 주셨습니다. 저도 그 방법은 전에 썼던 건데, 표에서 행 추가 등 작업이 쌓이면 나중에 조건부서식 "규칙관리"가 아주 난리가 나있게 됩니다.

          <내용 추가>

          그냥 말로 해도 될 간단한 내용이라 예제 파일이 필요하지 않을 것 같았는데, 얘기가 불필요하게 계속 길어질 것 같아 A, B, C열만 있는 간단한 파일을 만들어서 첨부하니 참고해 보시기 바랍니다.

           

          첨부파일 : 오빠두_조건부서식_예제.xlsx


          • 2022-01-24 09:55

            @나는나 님 조건부 서식에 사용한 식  "A" & ROW(), "B" & ROW() 의 결과값을 각각 [F], [G]열에 표시해 두고

            조건부 서식의 식에서는 [F1], [G1]을 참조하도록 하여 표시한 결과입니다. 정상적으로 작동이 됩니다. 비주얼베이직 스크립트에서 ("A1") 과 ("A" & 1)이 서로 다르기 때문입니다. "A1"은 순수한 문자열(영역의 이름)이지만 ("A" & 1)의 연산결과인 (A1)은 셀 [A1]에 기록된 값을 의미한다는 것이지요.

            결론적으로 조건부 서식에서 INDIRECT와 AND의 조합이 안되는 것이 아니고, 주소를 얻기 위하여 연산식을 사용했다는 것이 문제라는 것입니다. 이점 참고하여 첨부파일 검토하시면 좋겠습니다.

             

            조건부서식2.JPG

            첨부파일 : 오빠두_조건부서식_예제_해법제안.xlsx


            • 2022-01-24 13:59

              @김학동 님 계속 관심 갖고 신경써 주셔서 감사합니다. 그런데 죄송하지만 계속 질문의 핵심을 잘 이해하지 못 하고 계신 것 같습니다.

              =AND(INDIRECT(F1)>3,INDIRECT(G1)<>"")

              이렇게 수식을 입력할 거면 굳이 INDIRECT가 필요가 없지요. =AND(A1>3,B1<>"") 그냥 이렇게 바로 원래 셀의 값을 참조하면 되겠지요. 결국 위 수식은 INDIRECT만 썼을 뿐이지 과거에 제가 썼던 수식과 다를 바가 없습니다.

              기존 수식에서 INDIRECT 함수로 굳이 바꾼 이유는 ROW()를 사용하기 위해서입니다. 표 안에서 복사 붙여넣기, 삽입 등을 하면, 조건부서식 규칙관리자가 매우 지저분해집니다. 처음에 규칙 만들 당시에는 한 개였던 규칙이 '적용대상'의 영역이 잘게 쪼개지면서 동일 규칙이 수십, 수백 개가 생성되는 증상이 있습니다. 오빠두님 강의에서 INDIRECT를 설명하면서 언급한 내용이 바로 이 문제입니다. 이 문제를 회피하기 위해 INDIRECT+ROW()를 사용하라는 겁니다. 그래야만 행 삽입 등에 따라서 행 번호가 뒤죽박죽 되지 않고 규칙의 '적용대상'도 처음 적용 범위가 유지될 겁니다. 그런데 제 양식에서는 조건을 2개 충족해야 하기 때문에 AND가 추가되면서 이 질문을 시작하게 된 겁니다. =INDIRECT("AB"&ROW())>3 이렇게 AND 없이 INDIRECT와 ROW() 함수가 들어가거나 ROW() 없이 AND와 INDIRECT가 들어간건 아무 문제 없습니다.

              질문하나 하려다 오히려 제가 설명할 게 더 많이진 것 같네요. 피곤하실 테니 죄송하지만 이제 그만 댓글 달아주셔도 될 것 같습니다. 감사합니다.


전체 6,403
번호 카테고리 제목 작성자 작성일 추천 조회
알림
[🏆 진짜쓰는 실무엑셀 스터디] 1기 - 우수 졸업생 및 후기, 축하합니다! (8)
오빠두엑셀 | 2022.04.30 | 추천 9 | 조회 1126
오빠두엑셀 2022.04.30 9 1126
공지사항 함수/공식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (62)
오빠두엑셀 | 2021.10.28 | 추천 79 | 조회 4436
오빠두엑셀 2021.10.28 79 4436
34219 기능/도구
New 주말 날짜가 적힌 셀의 행 일부분이 자동으로 색 채우기가 되는 방법 있나요? 첨부파일
forest choi | 00:59 | 추천 0 | 조회 12
forest choi 00:59 0 12
34218 함수/공식
New VLOOKUP 함수 여러개 값 가져오기에서 빈셀은 빼고 데이터가 작성된 것만 가져오기 문제 관련 첨부파일
moonlights | 00:26 | 추천 0 | 조회 14
moonlights 00:26 0 14
34213 함수/공식
New [vlookup] 다른 엑셀파일 출력값의 ture 값 색변경하기 첨부파일
pk2616 | 2022.05.19 | 추천 0 | 조회 14
pk2616 2022.05.19 0 14
34205 VBA
New [택배송장만들기] 수량만큼 행추가하고 복사하기 첨부파일 (1)
수메리안 | 2022.05.19 | 추천 0 | 조회 24
수메리안 2022.05.19 0 24
34204 파워쿼리/피벗
New 쿼리 평수값에서 m2 제거하고 숫자만 남길 수 있는 방법 있을까요? (1)
shxhehskc | 2022.05.19 | 추천 0 | 조회 25
shxhehskc 2022.05.19 0 25
34202 파워쿼리/피벗
New 쿼리파일에서 한 칸만 삭제하기
shxhehskc | 2022.05.19 | 추천 -1 | 조회 28
shxhehskc 2022.05.19 -1 28
34199 함수/공식
New 시간대별 여러 시트 값 합치기 첨부파일 (3)
sjlee**** | 2022.05.19 | 추천 0 | 조회 35
sjlee**** 2022.05.19 0 35
34197 함수/공식
New 일수를 개월 일로 나타나는 방법을 알려주세요 (1)
nab | 2022.05.19 | 추천 0 | 조회 24
nab 2022.05.19 0 24
34196 함수/공식
New 합계 구하기 _ 도와주세요... ㅠㅠ 첨부파일 (2)
연어샐러드 | 2022.05.19 | 추천 0 | 조회 28
연어샐러드 2022.05.19 0 28
34191 파워쿼리/피벗
New 하나의 열의 내용 중 중복되는 내용을 각각 다른 열에 배치할 수 있을까요? (1)
shxhehskc | 2022.05.19 | 추천 0 | 조회 24
shxhehskc 2022.05.19 0 24
34190 파워쿼리/피벗
New 쿼리 실행 중 몇개의 행만 첫 행을 머릿글로 사용하기 (2)
shxhehskc | 2022.05.19 | 추천 0 | 조회 24
shxhehskc 2022.05.19 0 24
34186 문서서식
New 문서 서식 글자 바뀌는것 질문 첨부파일 (1)
동준 | 2022.05.19 | 추천 0 | 조회 16
동준 2022.05.19 0 16
34185 문서서식
New 카드내역 다운받아 사용금액 합계를 했는데.. 합계가 안되네요 (1)
akdlt**** | 2022.05.19 | 추천 0 | 조회 21
akdlt**** 2022.05.19 0 21
34180 구글시트
New REGEXEXTRACT 첨부파일 (2) 답변완료
마법의손 | 2022.05.19 | 추천 0 | 조회 32
마법의손 2022.05.19 0 32
34179 대시보드
New 엑셀 하단에 시트이름 나온곳 넓게 쓰고 싶은데 고정안될까요? 첨부파일 (1)
태봉 | 2022.05.19 | 추천 0 | 조회 27
태봉 2022.05.19 0 27
34175 문서서식
New 빈칸 셀위에 색상표시? 첨부파일 (1)
km**** | 2022.05.19 | 추천 0 | 조회 27
km**** 2022.05.19 0 27
34174 VBA
New VBA changelink 이용하여 원본파일 변경 시, 워크시트도 같이 변경하는 방법 (3)
쿠킹왕자 | 2022.05.19 | 추천 0 | 조회 30
쿠킹왕자 2022.05.19 0 30
34171 구글시트
New 다른 시트에서 원하는 값 불러오기 (11)
나도엑셀천재? | 2022.05.19 | 추천 1 | 조회 45
나도엑셀천재? 2022.05.19 1 45
34169 함수/공식
New 오빠두 강의중 텍스트나누기 공식 질문드립니다. 첨부파일
월마리아 | 2022.05.19 | 추천 0 | 조회 40
월마리아 2022.05.19 0 40
34166 차트/그래프
New 누적 가로막대 차트 비율이 안맞습니다 첨부파일 (2) 답변완료
어제부터VBA | 2022.05.18 | 추천 0 | 조회 35
어제부터VBA 2022.05.18 0 35
34164 파워쿼리/피벗
New DRM걸린 문서를 오피스365 엑셀의 파워쿼리에서 처리하면 에러가 발생을 합니다. (1)
new**** | 2022.05.18 | 추천 0 | 조회 24
new**** 2022.05.18 0 24
34162 함수/공식
New 다른 시트와 연동, vlookup 함수 연동 오류 해결 방법ㅜㅜ
나도엑셀천재? | 2022.05.18 | 추천 1 | 조회 22
나도엑셀천재? 2022.05.18 1 22
34152 VBA
New 변수 선언에 필요한 변수명을 동적으로 만들 수 있나요? (Dynamic Variable Name) (7) 답변완료
나는나 | 2022.05.18 | 추천 0 | 조회 49
나는나 2022.05.18 0 49
34148 문서서식
New 엑셀 기본표시형식 설정 해결방법 (1)
zoe | 2022.05.18 | 추천 0 | 조회 30
zoe 2022.05.18 0 30
34146 피벗테이블
New 피벗테이블 열 합계 시 값이 다르게 나옵니다. 또한 한 아이템이 시간에 따라 2개의 가격을 가질 때 어떻게 처리를 해야하나요? 첨부파일 (1)
엽이 | 2022.05.18 | 추천 2 | 조회 28
엽이 2022.05.18 2 28
34144 함수/공식
New 음수를 제외한 양수들만으로 오름차순으로 순위 내는법 첨부파일 (1)
리테일러 | 2022.05.18 | 추천 0 | 조회 33
리테일러 2022.05.18 0 33
34143 함수/공식
New 평일 출근부 만들기 첨부파일 (3) 답변완료
왜사는건지 | 2022.05.18 | 추천 0 | 조회 43
왜사는건지 2022.05.18 0 43
34136 함수/공식
New 백분율의 합계? 백분율의 평균 값?이 다른이유가 있을까요? (4)
한다운 | 2022.05.18 | 추천 0 | 조회 29
한다운 2022.05.18 0 29
34135 함수/공식
New 음수를 제외한 양수들의 합만으로 순위 내는법 첨부파일 (8) 답변완료
리테일러 | 2022.05.18 | 추천 2 | 조회 51
리테일러 2022.05.18 2 51
34134 VBA
New VBA 필터 지정후 이미지 저장 문의 첨부파일 (6) 답변완료
다다익선 | 2022.05.18 | 추천 0 | 조회 36
다다익선 2022.05.18 0 36