조건을 충족하는 첫 번째나 마지막 데이터를 검색하는 방법에 대해서는 예전 강의에서 소개한 것으로 기억합니다. 지정한 조건을 만족하는 처음이나 마지막 데이터만 가져올 것이 아니라 조건에 부합하는 모든 데이터를 가져올 수는 없을까요?
필터를 사용하거나 VBA로 코딩을 해도 되겠습니다만 이번 시간에는 함수를 사용하여 해결해 보겠습니다(여러 가지 기능을 수행해야 하므로 수식이 좀 깁니다. 미리 심호흡 해 두시길!ㅎㅎ).
완성 예
H2 셀의 드롭다운 버튼을 이용하여 제품을 선택하면 아래 쪽에 해당 제품과 관련된 거래내역들이 표시됩니다. 뿐만 아니라 왼쪽의 원본 테이블에도 해당 데이터에 자동으로 표시됩니다.
Step 1: 고유 값을 추출하고 유효성 검사 설정
1. '제품명'이 들어 있는 C3:C17 영역을 복사하여 L4 셀에 붙여 넣습니다.
2. [데이터] 탭 - [데이터 도구] 그룹 - [중복된 항목 제거] 명령을 선택합니다.
3. [중복 값 제거] 대화상자에서 [확인] 버튼을 클릭하면 고유한 값만 추출됩니다.
4. H2 셀을 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [데이터 유효성 검사] 명령을 클릭합니다.
5. [데이터 유효성] 대화상자에서 [제한 대상]과 [원본] 범위를 지정하고 [확인] 버튼을 클릭합니다.
Step 2: 수식 작성
1. '거래일자'가 들어갈 G5 셀에 아래 수식을 작성합니다. 배열 수식이므로 수식을 작성한 다음, CTRL + SHIFT + ENTER 키를 함께 눌러야 합니다. 수식 앞 뒤의 중괄호 { }는 손으로 입력하는 것이 아니고 CTRL + SHIFT + ENTER 키를 누르면 자동으로 생깁니다.
무시무시한 수식이 나왔군요. 길고 복잡한 수식은 맨 안쪽부터 하나씩 끊어서 해석하는 것이 이해하기 쉽습니다. 하나씩 해석해 보도록 하죠.
① 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 함수의 참조 영역만 다를 뿐 나머지는 앞의 수식과 같습니다.
3. 수식을 아래로 복사합니다. 일단은 다섯 행 정도를 범위로 정했는데 경우에 따라 유동적으로 적용하시면 되겠습니다.
Step 3: 원본 테이블에 조건부 서식 설정
1. B3:E17 영역을 범위로 지정하고 [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [새 규칙] 명령을 선택합니다.
2. [새 서식 규칙] 대화상자의 각 항목을 다음과 같이 지정하고 [서식] 버튼을 클릭합니다. 두 셀의 내용을 비교할 때, C2 셀은 열고정 혼합 주소, H2 셀은 절대 주소로 지정한 점에 유의하세요.
3. [셀 서식] 대화상자의 [채우기] 탭에서 적당한 색을 선택하고 [확인] 버튼을 클릭합니다.
이제 데이터 유효성 목록상자를 클릭하여 제품을 선택해 보세요. 해당 데이터가 모두 나타나고, 원본 테이블에도 표시가 됩니다.
수식이 조금 까다롭지만 이것을 잘 이해하시면 여러분의 실력은 한 단계 상승합니다.
덥거나 습하거나 하나만 하면 좋으련만 무덥고 습한 날이 이어지고 있습니다. 지역에 따라 게릴리성 폭우도 있었죠. 무더운 여름 건강하게 날 수 있도록 잘 관리하시기 바랍니다.
오늘은 여기까지...