• 최초 작성일: 2022-08-14
  • 최종 수정일: 2022-08-14
  • 조회수: 1,251 회
  • 작성자: 권현욱 (엑셀러)
  • 강의 제목: 조건을 충족하는 모든 값 한꺼번에 가져오는 법

엑셀러 권현욱

들어가기 전에 ㅡ 호의를 권리로 생각하는 사람

2019년 말 경 어느 분의 제보(?)가 있었습니다. 내용은 대략 이랬습니다.

"F사의 온라인 유료 강의를 수강하고 있는데 아이엑셀러 닷컴 강의와 유사한 내용이 많습니다. 강사는 엑셀러 님이 아닌데 혹시 제휴 사이트인가요?"

이전에도 비슷한 경우가 드물지 않았기에 처음에는 대수롭지 않게 생각했습니다. 하지만 F사 콘텐츠를 직접 보니 놀라지 않을 수 없었습니다. 아이엑셀러 닷컴의 [VBA 입문 강의], <VBA 코딩의 정석> 도서 중 상당 분량을 무단 복제/전재하고 있음을 확인했습니다.

F사에 콘텐츠를 제공한 사람은 아이엑셀러 닷컴에서 엑셀을 배웠던 사람으로 저를 '사부'라 칭하던 사람이었습니다. 이 일이 있기 얼마 전에도 그는 다른 사이트에서 제 콘텐츠 중 도입 부분과 작성자를 자기 이름으로 바꾸고는 마치 자기가 만든 것인 양 행세한 바 있었습니다.

심사숙고 끝에 이번에는 그냥 넘어갈 사안이 아니라고 생각되어 출판사와 함께 소송을 진행하기로 결정했습니다. 아직 재판이 진행 중이지만 피고측의 지식재산권 침해 사실에 대한 확인 작업은 거의 끝나가고 있습니다. 영화 '부당거래'에서 류승범이 했던 말이 불현듯 떠오릅니다.

로딩 중...

(이미지 출처: https://blog.naver.com/pospossposss/222420288499)

세상에는 선의를 선의로 받아들이지 않는 사람도 많다는 것을 새삼 느낍니다. 물론 그렇지 않은 사람들이 더 많다는 걸 알지만 마음이 무겁습니다.

이런 일이 있었다고 해서 달라질 건 없습니다. 비영리적 목적으로 아이엑셀러 닷컴 콘텐츠를 사용하시는 것에 대해 크게 문제삼고 싶지는 않습니다. 다만 가급적 원본 그대로 사용해 주시고 출처를 꼭 표기해 주시기 바랍니다.

참, 자신의 일인 양 안타까워하고 제보해 주신 분들께 감사드립니다.



조건을 충족하는 첫 번째나 마지막 데이터를 검색하는 방법에 대해서는 예전 강의에서 소개한 것으로 기억합니다. 지정한 조건을 만족하는 처음이나 마지막 데이터만 가져올 것이 아니라 조건에 부합하는 모든 데이터를 가져올 수는 없을까요?

필터를 사용하거나 VBA로 코딩을 해도 되겠습니다만 이번 시간에는 함수를 사용하여 해결해 보겠습니다(여러 가지 기능을 수행해야 하므로 수식이 좀 깁니다. 미리 심호흡 해 두시길!ㅎㅎ).

로딩 중...

완성 예

H2 셀의 드롭다운 버튼을 이용하여 제품을 선택하면 아래 쪽에 해당 제품과 관련된 거래내역들이 표시됩니다. 뿐만 아니라 왼쪽의 원본 테이블에도 해당 데이터에 자동으로 표시됩니다.

Step 1: 고유 값을 추출하고 유효성 검사 설정

1. '제품명'이 들어 있는 C3:C17 영역을 복사하여 L4 셀에 붙여 넣습니다.

2. [데이터] 탭 - [데이터 도구] 그룹 - [중복된 항목 제거] 명령을 선택합니다.

로딩 중...

3. [중복 값 제거] 대화상자에서 [확인] 버튼을 클릭하면 고유한 값만 추출됩니다.

로딩 중...

4. H2 셀을 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [데이터 유효성 검사] 명령을 클릭합니다.

5. [데이터 유효성] 대화상자에서 [제한 대상]과 [원본] 범위를 지정하고 [확인] 버튼을 클릭합니다.

로딩 중...

Step 2: 수식 작성

1. '거래일자'가 들어갈 G5 셀에 아래 수식을 작성합니다. 배열 수식이므로 수식을 작성한 다음, CTRL + SHIFT + ENTER 키를 함께 눌러야 합니다. 수식 앞 뒤의 중괄호 { }는 손으로 입력하는 것이 아니고 CTRL + SHIFT + ENTER 키를 누르면 자동으로 생깁니다.

G5: {=IFERROR(INDEX($B$3:$B$17, SMALL(IF($C$3:$C$17=$H$2, ROW($C$3:$C$17)-2, FALSE), ROW()-4)), "")}
로딩 중...

무시무시한 수식이 나왔군요. 길고 복잡한 수식은 맨 안쪽부터 하나씩 끊어서 해석하는 것이 이해하기 쉽습니다. 하나씩 해석해 보도록 하죠.

① IF($C$3:$C$17=$H$2,ROW($C$3:$C$17)-2,FALSE)

C3:C17 셀의 내용이 H2 셀의 내용과 같은지 판별합니다. 사용자가 데이터 유효성 목록상자를 통해 선택한 제품이 C열의 각 셀 값과 일치하는지 하나하나 비교합니다. 그래서 같으면 ROW 함수를 이용하여 그 행의 번호를 알아냅니다. 여기서 2를 빼주는 이유는 데이터가 3행부터 시작되기 때문입니다.

② SMALL(①,ROW()-4)

만약 찾을 데이터가 여러 개인 경우, 배열을 이용하여 해당 값을 담아둡니다. SMALL 함수를 이용하여 작은 배열에 담습니다. ROW()-4라고 한 것은 첫 번째 데이터를 나타낼 행이 5행이기 때문입니다.

③ INDEX($B$3:$B$17,②))

B3:B17 영역, 즉 거래일자 필드에서 유효한 값을 가져옵니다. 만약 H2 셀에서 '로열허니에멀전'을 선택했다면 이 수식의 결과값은 '=INDEX($B$3:$B$17,{8})'이 됩니다.

④ IFERROR(③,"")

수식이 완성되면 아래로 복사할 예정인데 해당 값이 없으면 오류(#NUM!)가 발생하므로 보기에 좋지 않습니다. IFERROR 함수를 사용하여 이 문제를 처리합니다.

2. '수량'과 '금액'을 구하기 위한 수식을 작성합니다. INDEX 함수의 참조 영역만 다를 뿐 나머지는 앞의 수식과 같습니다.

[H5] =IFERROR(INDEX($D$3:$D$17, SMALL(IF($C$3:$C$17=$H$2, ROW($C$3:$C$17)-2), ROW()-4)), "")
[I5] =IFERROR(INDEX($E$3:$E$17, SMALL(IF($C$3:$C$17=$H$2, ROW($C$3:$C$17)-2), ROW()-4)), "")

3. 수식을 아래로 복사합니다. 일단은 다섯 행 정도를 범위로 정했는데 경우에 따라 유동적으로 적용하시면 되겠습니다.

로딩 중...

Step 3: 원본 테이블에 조건부 서식 설정

1. B3:E17 영역을 범위로 지정하고 [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [새 규칙] 명령을 선택합니다.

2. [새 서식 규칙] 대화상자의 각 항목을 다음과 같이 지정하고 [서식] 버튼을 클릭합니다. 두 셀의 내용을 비교할 때, C2 셀은 열고정 혼합 주소, H2 셀은 절대 주소로 지정한 점에 유의하세요.

로딩 중...

3. [셀 서식] 대화상자의 [채우기] 탭에서 적당한 색을 선택하고 [확인] 버튼을 클릭합니다.

로딩 중...

이제 데이터 유효성 목록상자를 클릭하여 제품을 선택해 보세요. 해당 데이터가 모두 나타나고, 원본 테이블에도 표시가 됩니다.

로딩 중...

수식이 조금 까다롭지만 이것을 잘 이해하시면 여러분의 실력은 한 단계 상승합니다.

덥거나 습하거나 하나만 하면 좋으련만 무덥고 습한 날이 이어지고 있습니다. 지역에 따라 게릴리성 폭우도 있었죠. 무더운 여름 건강하게 날 수 있도록 잘 관리하시기 바랍니다.

오늘은 여기까지...