| |
- 엑셀 버전(95,97,2000,xp,2003,2007):2010
* 아래줄에 질문을 작성하세요 >>
홍길일(성명1에 입력되어 있음)을 치면
성명1,성명2에 홍길일,김갑일이 나타나는데
김갑일(성명2에 입력되어 있음)을 치면
성명1,성명2에 안나타나는데
홍길일을 치든지 김갑일을 치든지 성명1,성명2에 나타나게 하는 수식을
배우고 싶습니다
엑셀이 안되면 매크로라도 알려주세요
==============[차알돌이님 글에 대한 답변입니다]==============
일단, 질문하신 문제에 대한 해답은 첨부파일에 있습니다.
원하는 결과를 얻을 수 있는 방법은 다양할 수 있습니다.
다만, 효율성을 염두에 두고 답을 구하였음을 말씀드립니다.
제가 드린 답의 경우는 B3 셀에 잘 설계해서 입력한 수식을 복사하여
B3:H12 영역의 전체 셀에 동일한 모양의 수식으로 붙여넣은 것입니다.
이 효율성이 제대로 동작하도록 하기 위해서는
시트 설계를 조금 수정해야 합니다.
즉, [개인별검색] 시트의 표 제목(필드명)과
원본 참조표인 [입출금] 시트의 표 제목(필드명)이 서로 일치해야 한다는 점입니다.
따라서 [개인별검색] 시트에 굵은 글씨 부분을 수정하였습니다.
===============================================================================
=IFERROR(INDEX(입출금!$B$2:$H$1205,1/LARGE(INDEX((입출금!$D$2:$D$1205=$D$1)/ROW(입출금!$D$2:$D$1205),),ROW($C1))-1,3),"")
의 분석
처음에 질문 올라온 수식을 보고 많이 의아했습니다.
배열수식 같아 보이는데 배열수식이 아니면서도 오류 없이 결과가 나온다?
문법 오류도 있는 것 같은데 이상하다?
그런데 제가 이 수식에 매력을 느낀 이유는
중복키가 존재하는 경우(즉, 찾는 이름을 포함하는 레코드가 둘 이상 존재할 경우)
지금까지 제가 몇차례 답변했던 기법인 색인키를 새로 만들지 않고서도
중복 키 값들을 모두 찾아올 수 있다는 점이었습니다.
* 이전 답변 참고 자료
http://www.officetutor.co.kr/board/Dtype/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=123066&page=
http://www.officetutor.co.kr//board/DType/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=123702&page=&fchk=&fval
답은 INDEX 함수의 두번째 기능이었습니다.
-------------------------------------------------------------------------------
* INDEX 함수: =INDEX(<배열범위>, [행번호], [열번호])
1) 기본 기능: 배열범위로 지정한 배열에서 지정한 행번호와 열번호 위치에 있는
요소를 찾아옵니다.
배열이 1행 또는 1열로 이루어진 1차원배열일 경우는
행번호나 열번호만 지정합니다.
행번호와 열번호는 첫번째 위치가 1부터 시작됩니다.
(비교: OFFSET 함수는 기준위치를 0으로 계산함)
예) INDEX(A1:D10,2,3): A1:D10 영역에서 제2행 제3열 위치
즉, C2 셀에 있는 값을 가져옵니다.
INDEX(B5:B20,7): B5:B20 영역은 한 줄입니다.(1차원)
거기서 7번째 즉, B11 셀의 값을 가져옴
INDEX(B5:Z5,4): B5:Z5 역시 한 줄입니다.
거기서 4번째 즉, E5 셀의 값을 가져옴
2) 추가 기능: 그런데 말입니다....
INDEX 함수의 위치 지정 인수를 0으로 지정하거나
생략하고 코머만 입력한 경우에는
INDEX 함수는 배열 자체(배열의 일부를 추출)를 반환하게 됩니다.
예) INDEX(A1:D10,2,0): A1:D10 영역은 2차원 배열인데 행번호만 지정한 경우
두 번째 줄 전체 즉, A2:D2 영역을 반환합니다.
INDEX(A1:D10,2,)로 쓸 수도 있습니다.
INDEX(A1:D10,0,3): 행번호를 생략하고 열번호만 지정한 경우
3번째 열 전체 즉, C1:C10 영역을 반환합니다.
INDEX(A1:D10,,3)로 쓸 수도 있습니다.
-----------------------------------------------------------------------------
이제 질문하신 파일에서 가장 안쪽에 있는 수식을 살펴보죠.
INDEX((입출금!$D$2:$D$1205=$D$1)/ROW(입출금!$D$2:$D$1205),)
---------
전체적으로 살펴보면, 배열범위 인수만 지정하고 위치 지정 값을 0으로 지정한 경우로
위의 추가기능에 해당합니다. 즉, 이 함수의 결과는 배열 자체를 반환하게 됩니다.
다시 말하면, 원래의 배열 입출금!$D$2:$D$1205을 어떤 목적에 의해 적당히 변형한
값으로 바꾼 배열을 반환하는 함수입니다.
그게 뭔고 하면
(입출금!$D$2:$D$1205=$D$1)이 배열수식으로 입출금!$D$2:$D$1205 영역의 값이
$D$1 셀에 저장된 값과 일치하는지 여부에 따라 True 혹은 False로 변환됩니다.
그 결과를 다시 ROW(입출금!$D$2:$D$1205)으로 나누었습니다.
Boolean 값에 대하여 사칙연산을 수행하면 True는 1로, False는 0으로 변환되어
계산됩니다.
즉, 입출금 시트의 D열에서, 개인별검색 시트의 D1 셀에 입력한 이름을 검색하여
그 이름이 존재하는 곳은 1, 아닌 곳은 0의 순서로 치환되는데
이것을 다시 그 행번호로 나누었습니다.
따라서, 찾는 이름이 있는 위치에는 그 행번호의 역수로, 찾는 이름이 아닌 곳에는 0으로
치환이 됩니다.
행번호의 역수 값을 갖기 때문에,
그 값을 행번호가 클 수록 즉, 아래 쪽에 있는 데이터일수록 작은 값이 되고
찾는 이름이 아닌 위치에는 무조건 0으로 치환된
새로운 배열이 생성되는 것입니다.
여기까지의 과정을 계산 단계별로 풀어 써보면 첨부파일의 [해설] 시트
A~D열과 같습니다.
결국 위 함수는 [해설] 시트의 D열과 같은 배열을 반환하는 것입니다.
찾으려는 이름과 일치하는 항목만 먼저 나온 것부터 양수 값이 조금씩 작아지는 모양새이고
일치하지 않는 항목은 모두 0으로 치환되는 것입니다.
---------------------------------------------------------------------------
이제 한 단계 바깥쪽의 함수로 시야를 넓혀보면
LARGE(<INDEX 함수의 결과>,ROW($C1))
-------------------
앞에 INDEX 함수가 만들어준 배열에서 ROW($C1)번째로 큰 값을 가져옵니다.
여기서 ROW($C1) 수식은 이웃 셀에 복사하면 열번호 C는 변하지 않고
행번호는 아래로 복사할 경우 상대적으로 변합니다.
개인별검색 시트의 3행부터 이 수식이 입력되어 있는데
이 수식을 아래쪽으로 복사하면
4행에서는 ROW($C2)
5행에서는 ROW($C3)
와 같이 바뀝니다.
즉, 3행에서는 1, 4행에서는 2, 5행에서는 3, ... 이런 값으로 바뀌지요.
따라서, 3행에서는 배열 중 가강 큰 값
4행에서는 2번째, 5행에서는 3번째로 큰 값을 가져옵니다.
다시 말하면, 내림차순으로 정렬된다고 보시면 됩니다.
앞에서 INDEX 함수의 결과 생성/변형된 배열 값들이
찾는 이름이 있는 위치에서 크기가 점점 작아지는 양수 값들을 가지므로
이렇게 검색하면 찾는 이름이 있는 배열요소들을
먼저 만나는 것부터 차근차근 모을 수 있게 된 것입니다.
첨부파일 [해설] 시트의 E, F열에 그 중간과정을 보였습니다.
------------------------------------------------------------------------------
그 다음 바깥 단계
1/LARGE(...)
--------------------
이렇게 모아진 값들은 행번호의 역수라고 설명했지요?
그것의 역수를 다시 취하면 원래의 값 즉, 행번호가 구해집니다.
첨부파일 [해설] 시트의 G열과 같습니다.
실제로 찾는 이름과 일치하는 데이터가 있는 행번호가
신기하게 구해진 것을 볼 수 있습니다
그리고, 그 외의 값들은 당연히 #DIV/0! 오류가 발생하겠지요.
이것은 나중에 IFERROR로 걸러지게 됩니다.
------------------------------------------------------------------------------
그 다음 바깥 단계
INDEX(입출금!$B$2:$H$1205,<행번호>-1,3)
-----------------------------------------
이제 INDEX 함수의 기본 기능에 해당됩니다.
입출금!$B$2:$H$1205 영역에 있는 참조표에서 <행번호>-1 번째 행, 3번째 열에 있는
데이터를 찾아오는 것입니다.
당연히 찾는 이름이 있는 행에서 성명1 항목이 불려지겠지요.
여기서 <행번호>에서 왜 1을 빼 주었을까요?
원본 참조표가 1행이 아닌 2행부터 시작하는 주소를 지정했기 때문입니다.
물론 INDEX(입출금!$B$1:$H$1205,<행번호>,3) 로 써도 되겠지요.
사실은 실제 참조표는 입출금!$B$4:$H$1205 이므로
INDEX(입출금!$B$4:$H$1205,<행번호>-3,3)
으로 써야 정확한 표현일 것 같은데...
왜 저렇게 썼는지는 저도 모르겠습니다. ^^
첨부파일 [해설] 시트의 H열과 같습니다.
------------------------------------------------------------------------------
맨 바깥 단계
IFERROR(INDEX(...),"")
-----------------------------------------
찾는 이름이 아닌 경우에는 0에서 #DIV/0! 오류로 바뀌었었지요.
이 오류는 INDEX 함수에서도 그대로 유전이 됩니다.
그렇다고 최종 결과에도 오류 값이 그대로 나타나면 곤란하겠지요.
이건 더 이상 일치하는 게 없는 경우이니까 그냥 공백으로 표시해 주길 원합니다.
그 함수가 바로 IFERROR 함수이지요.
어떤 함수가 오류가 아닌 경우에는 그 함수의 결과값을 그대로 반환하고
만일 오류가 발생한 경우에는 IFERROR 함수의 두 번째 인수로 지정한 값으로
바꾸어 반환해 줍니다.
첨부파일 [해설] 시트의 I열과 같습니다.
=============================================================================
효율화를 위한 첫번째 제안
-----------------------------------------------------------------------------
찾은 이름에 대해 그 행의 년도, 일시, 성명2, 수입, 지출, 잔액 등의 자료들을
불러오기 위해서는 어떻게 해야 할까요?
위 INDEX 함수의 마지막 인수인 열번호를 각각 1, 2, 3, 4,... 등으로
바꾸어주면 될 겁니다.
그런데 이렇게 일일이 바꾸어주는 것은 좀 비효율적입니다.
그 대신 마지막 열번호를
MATCH(D$2,입출금!$B$3:$H$3,0)
로 바꾸어주면 어떨까요?
원본 참조표의 표제목에서, 개인별검색시트의 각 열 제목과 일치하는 것을 찾아
그것을 열번호로 지정하는 것입니다.
단, 이렇게 하려면 서두에도 언급했듯이
입출금 시트의 원본 참조표와 개인별검색시트의 표에 각 필드명(표 제목)이
각각 일치하도록 해야 합니다.
이렇게 함으로써, D3 셀에 똑똑한 수식 하나만 잘 입력한 후에
이것을 B3:H12 셀에 동일한 수식으로 복사해주면 되는 것입니다.
각 열의 수식을 일일이 고쳐주는 것보다 훨씬 편리하겠지요?
물론 MATCH 함수 대신 COLUMN 함수도 가능할 수 있습니다. 즉
COLUMN()-1
이 경우에는 입출금 시트의 원본 참조표와 개인별검색시트의 표에 각 필드(표 제목)의
순서가 일치해야 합니다.
여기에서 COLUMN() 함수의 결과 값에서 1을 빼 주는 이유 역시
행번호에서 1을 빼 주었던 이유와 동일합니다.
==============================================================================
이제 이 원리를 그대로 응용해서
첫번째 질문이었던 성명2로 검색하기를 구현해 보겠습니다.
------------------------------------------------------------------------------
힌트는, 성명1에서 찾는 이름이 아닌 경우는
1/LARGE(...) 수식의 결과가 #DIV/0! 오류가 된다는 점입니다.
따라서 만일 이 수식이 오류이면(IFERROR 함수)
E열에서 한 번 더 찾아보라고 수식을 써 주면 될 것입니다.
------------------------------------------------------------------------------
IFERROR(1/LARGE(INDEX((입출금!$D$4:$D$1205=$D$1)/ROW(입출금!$D$4:$D$1205),)
,ROW($B1))
,1/LARGE(INDEX((입출금!$E$4:$E$1205=$D$1)/ROW(입출금!$E$4:$E$1205),)
,ROW($B1))
------------------------------------------------------------------------------
이해하기 쉽게 줄을 맞추어 놓았습니다.
첫번째 인수로 지정한 수식이 오류라면 즉, D열에서 이름을 못 찾았다면
똑같은 원리를 응용하여 E열에서 그 이름을 찾아서 행번호를 반환하라는 수식이지요.
==============================================================================
이렇게 해서 완성한 수식이 바로 다음 수식입니다.
D3 셀:
=IFERROR(INDEX(입출금!$B$4:$H$1205,IFERROR(1/LARGE(INDEX((입출금!$D$4:$D$1205=$D$1)/ROW(입출금!$D$4:$D$1205),),ROW($B1)),1/LARGE(INDEX((입출금!$E$4:$E$1205=$D$1)/ROW(입출금!$E$4:$E$1205),),ROW($B1)))-3,MATCH(D$2,입출금!$B$3:$H$3,0)),"")
그리고 이 수식을 B3:H12 영역에 동일하게 복사해서 쓰면 됩니다.
==============================================================================
그런데 말입니다....?
==============================================================================
효율화를 위한 두번째 제안
------------------------------------------------------------------------------
제가 이 질문을 처음 보고 구미가 땅겼던 이유 중 하나가
'보조수식'을 사용하지 않고도 중복키를 모두 찾아올 수 있다
는 것이었지만...
막상 분석과 적용, 응용까지 해 놓고 보니
왠지 석연치 않은 점이 발견됩니다.
바로... 상당히 난해하면서 복잡한 수식
IFERROR(1/LARGE(INDEX((입출금!$D$4:$D$1205=$D$2)/ROW(입출금!$D$4:$D$1205),),ROW($B1)),1/LARGE(INDEX((입출금!$E$4:$E$1205=$D$2)/ROW(입출금!$E$4:$E$1205),),ROW($B1)))-3
이게 동일한(상대적 주소까지도 똑같은) 수식이 여러 군데에서(즉, 같은 행에서는)
반복이 되고 있다는 점입니다.
물론 =MATCH(B$3,입출금!$B$3:$H$3,0) 수식 역시
같은 열에서는 결국 같은 결과 값을 가져오는 복잡한 수식을 반복해서
계산하고 있다는 것입니다.
이것은 결국 워크시트의 실행 속도를 떨어뜨리는 원인이 되고
처리할 데이터가 많아질수록 이 현상은 심각해질 수 있습니다.
제가 질문들에 답변할 때마다 자주 반복하는 표현
-----------------------------------------------------------------
중간 계산값을 별도의 셀에 저장해 두고 활용하면
1) 실행 속도가 빨라집니다.
2) 수식을 간결하게 작성할 수 있으므로, 문제가 쉬워집니다.
------------------------------------------------------------------
이 격언을 여기서도 또 적용하고 싶어집니다.
결국 실행 속도 개선을 위해서 보조 수식을 다시 활용한 해법이
[효율화] 시트에 별도로 작성되어 있습니다.
당연히 시트 구조는 조금 바뀌었습니다.
이상으로 해설을 마칩니다.
제가 가진 글재주껏 쉽게 설명한다고 했는데...
이해가 되실지 모르겠습니다.
|
|
엑셀에 대한 질문과
답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
