나눔터  
  HOME > 나눔터 > 묻고답하기 > 엑셀
엑셀
엑셀에 대한 질문과 답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
 "000 님, 도와주세요", "부탁 드립니다.", "급합니다!" 등과 같이 막연한 제목을 달지 말아주세요.
[필독] 빠르고 정확한 답변을 얻는 16가지 Tip !
[필독] 저작권법 개정에 따른 이용안내
작성자:  

 조삿갓 (choga21)

추천:  2
파일:     예제(1135)[2].xls (87KB) 조회:  2585
제목:   [RE]성명이 이중일때...
     
  - 엑셀 버전(95,97,2000,xp,2003,2007): 2007

 * 아래줄에 질문을 작성하세요 >>

1.<홍길동>이 신촌,홍대 두곳에서 근무 → 근무지별로 계산
2. 현근무지<홍대>로 집계 → CG열 "Y" 표시
 일때, 명세서를 출력하려고 하는데 출력이 안됩니다. 
성명이 이중일때는 어떻게 식을 변경해야 하나요?

 ==============[몽실님 글에 대한 답변입니다]==============

앗, 제가 파일 첨부를 깜박 했네요.
아예 두 가지 해법을 제시하는 걸로 고쳤으니 참고 바랍니다.

인덱스 키에 중복이 존재할 경우 해결하는 방법은
COUNTIF 함수와 결합하거나(http://www.officetutor.co.kr/board/Dtype/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=123066&page=)
또는 문자열 결합을 이용하여 인덱스 키가 유일하도록 해 주어야 합니다.

즉, 홍길동1, 홍길동2, ... 등으로 구분하거나
 신촌홍길동, 홍대홍길동, ... 등으로 구분하는 것이지요.

그런데 질문하신 사례의 경우는
 신촌홍길동과 홍대홍길동이 동명이인이 아니고
 인사이동이 되었다는 것이지요.
그리고 그 구분을 현임 근무지에 "Y"를 표시하겠다...
그렇다면 홍길동Y, 홍길동N으로 구분해서
 홍길동Y만 찾아주면 되겠지요.

급여대장에 참조열 하나를 더 추가하여
 문자열 결합을 하고, 그 열에서 참조키를 찾으면 됩니다.
파란색 음영으로 표시하였으니 참고 바랍니다.

그리고, #N/A 오류를 IFERROR 함수로 해결해 주어야
 지급촘액과 공제총액이 제대로 계산됩니다.

또 한가지
 테이블 참조하여 값을 빼오는 방법은
VLOOKUP, INDEX, INDIRECT, OFFSET 등 방법이 있는데
 사실 VLOOKUP 함수가 가장 간결하고 사용하기 쉽습니다.
바로 아래 게시글 답변에 [강좌]로 정리해 두었으니 참고하시기 바랍니다

첨부파일에서 [영수증_P2] 시트가 VLOOKUP 함수를 활용한 예제입니다.
보다시피 함수 사용 자체가 훨씬 간결해집니다.
이 때 참조대상 표가 조건을 갖추어야 하므로
[급여대장2]로 변형하여 만들었습니다.

1. 첫째 전제: 참조키가 반드시 테이블의 맨 왼쪽에 있어야 합니다.

2. 둘째로, 열번호를 MATCH 함수와 결합하려면
   참조 테이블에서 필드명을 일정한 행에 반드시 기록해 주어야 합니다.
   원본의 경우처럼 2행에 썼다가 3행에 썼다가 일관성이 없으면
   수식을 노가다해야 합니다.
   [급여대장2] 시트에서는 3행으로 모두 옮겼습니다.

   그리고, 이 필드명은 찾아오려는 시트 즉, [영수증_P2] 시트에서도
   동일한 명칭을 사용해야 합니다.
   예를들어 [영수증_P] 시트에서는 '근무기간'인데(A4 셀)
   참조대상인 [급여대장] 시트에서는 '비고'라고 하면
   또 노가다를 해야 합니다.

   따라서 [급여대장2] 시트에는 '비고'를 '근무기간'으로 고치고
   3행에 기록했습니다.

   '노가다'의 의미는 이렇습니다.

   이와 같이 일관성을 유지해 주면
   [영수증_P2] 시트에서
   D3 셀에 똑똑한 수식 하나만 잘 입력한 다음에
   요걸 갖다가
   B4,
   B6:B14,
   D6:D13
   에다가 그냥 복사만 하면 끝입니다.
   물론 서식이 조금씩 다르기 때문에
   선택하여 붙여넣기에서 수식으로 붙여넣기를 하면 됩니다.

   안 그러면
   [영수증_P] 시트에서처럼
   일일이 참조대상 범위를 다시 지정해야 하는
   그야말로 노가다를 해야 합니다.

3. 또 한가지 위반이 있는데
   급여대장2 시트에서 필드명 역시
   중복이 발생하면 곤란한 문제가 생깁니다.
   MATCH 함수로 열 위치를 읽어오기 때문에
   필드명이 중복되어 있으면   
   최초에 만난 위치만 읽어버립니다.

   즉, 현재로서는
   [급여대장2] 시트의 X3 셀과 BK3 셀의 필드명이
   '기본급'으로 똑같아서
   홍길동 사원의 신촌 근무 급여가 합산이 안되게 되어 있습니다.

   근무지별기본급, 기본급 등으로
   필드명을 다르게 구분해 주어야 합니다.

   안 그러면 또 노가다를 해야겠지요.

   
 
[불량 게시물 신고]  
몽실조삿갓님 감사합니다.
한데, 수정하고 보니 성명 홍길동이 중복되어 나오는게..
위에서 COUNTIF 함수와 결합해서 하라고 했는데 잘 안됩니다.
01-26 (04:36)
삭제 ■신고
조삿갓위에 괄호 안에 있는 링크를 눌러서 공부해 보세요.

COUNTIF 함수와 결합하는 경우는 두 명의 홍길동(동명이인?)을
각각 검색하고 싶을 경우 또는
몇 번째 특정위치의 레코드를 뽑아오고 싶은 경우에 유용하고

몽실님의 케이스처럼 근무지별로 구분하여 검색하려면
근무지와 이름을 문자열 결합(& 연산자)하여
검색키를 만들어주면 됩니다.

즉, A4셀의 수식을
=C4&D4
로 하고 아래로 쭈욱 복사해서
'신촌홍길동', '홍대홍길동'으로 검색하면
지점별로 구분하여 각각 뽑아 올 수 있고

=D4&COUNTIF(D$4:D4,D4)
로 하고 아래로 쭈욱 복사해서
'홍길동1', '홍길동2' 등으로 검색하면
동명이인인 홍길동을 각각 뽑아 올 수 있다는 뜻이지요
01-26 (10:51)
삭제 ■신고
몽실네 감사합니다.
함수로 바로 목록으로 지정할 수 있나 했는데, 조건에 맞게 참조셀을 만들고 함수로 목록을 지정해야 하네요. 신입사원이 들어오면 참조셀을 추가 복사해야 해서.. vba를 사용해 목록을 만들어야 할듯 하네요. 
01-26 (16:16)
삭제 ■신고
조삿갓에궁... VLOOKUP의 용법을 제대로 이해하지 못한 상태라면
VBA로 구현하기는 더 어려울텐데요.

신입사원이 6만5천명 정도까지는 영수증_P2 셀의 수식을 고칠 필요가 없습니다.
VLOOKUP 함수의 두번째 인수를 급여대장2!$A:$CH로 열 전체를
대상으로 지정하지 않았나요?
신입사원 명단은 급여대장2에만 추가 입력해 주면 끝입니다.
다만, 누누이 설명했듯이, 양쪽 시트의 필드명도 중복되지 않도록 조정하셔야 하고
사원을 추가할 생각이라면 급여대장2 시트의 12행에 있는 합계 수식을 2행으로 옮기시기 바랍니다.
01-27 (19:52)
삭제 ■신고
        
  

작성일 : 2018-01-26(01:52)
최종수정일 : 2018-01-26(01:57)
 


 ◎ 관련글

  제 목   작성자   날짜
성명이 이중일때... 몽실 2018-01-25
[RE]성명이 이중일때... 조삿갓 2018-01-26