야야곰 경험

SUMIF와 IF로 내가 원하는 사람의 값을 더해 오는걸 해 보자

디디대장 2008. 11. 25. 15:52
반응형

 엑셀에서 SUMIF으로 같은 이름의 값들을 더하는걸 해 보겠다. 또 IF문을 써서 표시하는걸 설명하겠다.


 엑셀 책을 두번 쳐다 보기는 했는데 보통 볼때 뿐 전혀 감이 없는 저로서는 남들은 자격증까지 따서 써 먹는데 왜 이리 어럽게 느껴지는건지 모르겠다는 생각을 안할수 없다.

그러던중 10년전 쯤 업무상 엑셀을 쓸일이 있어서 초급책 보고 수식 넣기 더하기 빼기 나누기 곱하기 등등 수식 넣는걸 해보았다. 이게 내 엑셀 실력의 전부다.

  수식 넣기는 키보드에서 숫자옆에 보면 "=" 를 쓰기만 하면 된다. "=" 표시가 나타나면서 수식을 넣을수 있게 된다.  



 그런데 하다 보면 엑셀의 수식를 활용할 일이 또 생겼다. 

 예를 들면 어떤 상품의 매달 출고 숫자의 합같은거다. 

 그래서 인터넷에서 찾아보려면 매우 힘들었다. 결국 저도 인터넷을 쳐다 보면서 공부한 결과  원하는 데이터를 얻어 내는데 성공했다.

  처음에는 어떤 수식을 써야 하는지 몰라서 엄청 고생했다. 그런데 유연히 발견한 간단한 수식으로 이 문제를 해결하고 기쁨 마음에 이렇게 글을 올린다.


 고급 사용자들이 보면 웃길거다. 이 설명 자체가 하지만 초보분은 유용하게 쓰실수 있지 않을까 생각한다.

그럼 한사람의 매달 회비를 더하거나  한 제품의 판매량이나 입금액을  더한 결과를 내고 싶을때 쓰는 수식은 무얼까 자로 SUMIF 문이다. 

=<SUMIF>(비교 범위조건실제 더할 범위)

비교범위 - 조건과 비교할 셀 범위이다.

조건 -  숫자, 수식 또는 텍스트 형태로 된 찾을 조건이다.

 예를 들어 조건은 32, "32", ">32", "사과", "<>사과" 등으로 표시할 수 있다.

실제 더할 범위 - 합을 구할 실제 셀입니다. 생략시 비교 범위를 더함>

범위는 절대번지, 상대번지, 혼합번지로 구성됩니다. 변경키는 F4

빨강은 필수로 입력해야 한다.

※ 예문은  제가 가상으로 만든 데이터이다. 사냥터교회 헌금으로 설명한다. 이름별 헌금액수를 자동으로 더하는 시트(sheet)를 만드는 거다.

물론 IF문을 중복하면 여러가지 다 표시할수 있겠지만 그럼 너무 복잡하니 오늘은 여기까지 설명한다.


※ 아래는 파일을 올려 놓았다. 선택하면 다운 받을수 있다. 아래 파일을 열어 놓고 설명을 들으면 더 편할것이다.  

SUMIF연습1.xls



※ 동영상으로 이해가 부족할것 같아서 아래 설명을 다시 써 놓았다.

 동영상 설명중 잘못 말한부분도 있다.  이해해 주세요.

 아래는 헌금이나 기부금을 받는걸로 설명한거다.  

1. 먼저 데이타를 만든다.  첫번째 시트에 품목과 금액을 쓴다.

  

 이렇게 한  sheet1을 작성한다. 이건 누구나 쉽게 할수 있을 거다.  그대로 쓰기만 하면 되니까 수식은 전혀 없다. 아래한글의 테이타도 엑셀도 가져와서 이렇게 만들수 있다.

  2.  sheet2는 같은 이름의 사람들의 헌금액을 더하는걸 먼저 해보겠다. SUMIF 를 쓴다.


 앞에 sheet1의 테이터와 비슷하지만 잘 보시면 이름을 한번만 써 있다. 그리고 작정액이 추가 되어 있다. 상품으로 치면 판매목표정도 된다.   작정액 옆에 헌금(기부금) 합계 보일거다.  앞에 SHEET1에서 데이터를 가져온거다.  


 합계의 수식은 이렇다. 김길동 헌금 합계 첫번째 부분에 E3에  =SUMIF(sheet1!$B$3:$D$36,$B3,sheet1!$D$3:$D$36) 이라고 수식을 넣는다. 여기서 포기하실분 있을거다. 난 안된다 하고 말이다. 하지만 아래 설명을 계속 보면 될거다 

  좀더 편하게 하는 방법은 수식을 넣을때는 아래 그림을 보면 삼짜 꺼꾸로 해 놓은것 같은 모양있다. 그걸 선택한다.


또는 메뉴에서 삽입 > 함수를 하면 아래 모양의 창이 열린다. 여기서 범주선택에서 모두를 선택하고 SUMIF를 찾아서 선택한다. 

 첫번째: Range는 사용할 테이타가 어디냐고 물어 보는거다. 여기서는 sheet1의 이름과 헌금액인데 직분을 안가져 올수 없으므로 그냥 이름부터 직분의 테이터로 선택한다. 직접 범위를 써 넣어도 되지만 초보에게는 너무 어려운 일이다. 그래서 그냥 마우스로 범위를 선택하도록 하겠다. 위의 그림을 보면 빨간색 보일거다. 3개중 첫번째것을  선택하면 창이 작아지면서 어디든지 이동할수 있게 된다.  함수인수 창이 작게 뛸겁니다.  이벤 sheet1로 이동한다. 

 범위를 선택한 후 다시 위에 빨간 버턴을 누르면 처음 함수 인수 넣은 넒은 창이 열린다. 범위를 넣을때 이름부터 하셔야 한다. 날짜를 넣으니 수식이 안 작동한다. 아마도 앞에 날짜 표시들 때문인것 같다.  sheet1!b3:b33 으로 한다.


두번째: Criteria는 지금 작성하는 같은 이름의 값을 더할 이름을 선택하는거다. 여기서는 B3다. 같은 방법으로 빨간 버턴을 누르고 B3를 선택한다. 그리고 다시 빨란버턴은 눌러서 넒은 창으로 돌아온다. 


3번째 : sum-rage 는 더하고자 하는 테이타를 말한다.  sheet1의 헌금액수가 기록된 부부을 선택했다.  이제 원하는 사람의 그러니까 지금은 B3 의 사람의 헌금 합계가 보일겁니다. 그런데 문제는 여기서 부터다. 그럼 한칸 마다 다 이 수식을 넣고 작업을 하면 너무 힘들거다.  그래서 수식을 복사한다. 하나만 제대로 작성하고 나머지는 수식을 복사해서 넣으면 된다.  

Tip 수식을 복사 방법 : 수식은 마우스로 원하는 칸 마우스 오른쪽 버턴 누리고 복사해서 복사한걸 붙일 부분을 마우스로 선택하고 붙여 넣기 해도 된다. 또는 원하는 칸에 오른쪽 모서리 부분에 마우스를 가져가면 "+" 모양이 나타난다. 그때 마우스 왼쪽 버턴 누르고 아래로 쭉 눌리면 된다.

   그런데 여기서는 복사하기 전에 하나 더 작업이 필요하다. 위의 수식은 복사하면 마구 숫자가 변한다. 가져올 데이터의 범위가 변하면 안되기 때문에 그 범위를 고정시키는 것이 바로 $ 표시다.  

 복사하기 전에 만든 수식이 있는 곳에 마우스로 선택하면 위의 그림처럼 수식이 나온다.

 =SUMIF(sheet1!B3:D36,B3,sheet1!D3:D36) 이 수식을 이렇게 변경해주면된다.  
=SUMIF(sheet1!$B$3:$D$36,$B3,sheet1!$D$3:$D$36)  

 다 $표시 했는데 가운데 $B3만  숫자 앞에 $표시가 없다 이유는  그건 복사를 할때 그 다음 칸 사람의 이름을 찾아서 헌금액을 더하라는 수식을 넣기 위해서다. 다른것들은 변하지 않아야 하고 이 값은 복사했을 때 위치에 따라서 같이 변해주어야하기 때문이다.  


 이렇게 해서 SUMIF를 이용해서 원하는 영역안의 같은 이름의 테이타를 값을 가져와 합산하는걸 해 보았다.

 다음은 if문을 써서 약간의 정보를 재가공하는것인데 초보에게는 여기까지도 힘들고 어려울것이다. 아래는 나중에 기회가 될때 보시면 된다.



3. 진행%를 넣는걸 설명하겠다. 작정한 액수로 현재까지 헌금합계를 나누어 주면된다. 수식이 간단하다. 나누기이므로 " = " 키보드 누릅니다. 그리고 넣고자 하는 부분을 마우스로 선택하면 셀의 자리수가 표시됩니다. 나누기 키보드의 버턴 "/" 하구요 다시 마우스로 나눌 값 작정액을 선택한다. 


이제 이수식을 복사한다.  마우스로 첫번째 방금 넣은 값 모서리 부분에 마우스를 가져가면 + 표시가 나타난다.  그때  마우스 왼쪽 버턴을 누린상태로 쭉 늘리면서 원하는 부분까지 가서 버턴을 놓으시면 된다.

그런데 소숫점 짜리 까지 나오는 경우가 있다.  성공을 기대했는데 실망스러울 것이다. 

 간단하게 또 고쳐 보자. 맨위 F를 선택합니다 마우스 오른쪽 버턴 누르면 셀 서식 선택하면 아래 화면이 나온다.


백분율 선택하고 확인 누른다.



+ 여기서 혹시 수식에 에러가 나서 #div/0! 같은 기호가 나오는 분이 있을지 모르겠다.그럴때는 IF문을 조금 수정하면 된다. 

=IF(iserror(e25/d25)," ",e25/d25) 하시면 에러문자가 안보이게 될거다.  

4. 이젠 결과 표시 하는 if 문 설명하겠다.  설명하는 저도 지치는데 보는분은 얼마나 답답할까



수식에 대해서 설명하겠다. =if(f3>99%,"완납', "")
f3가 99% 이상이면 완납이라고 쓰고 아니면 아무것도 쓰지 말라는 수식이다. 

 그럼 100%이상일때 완납이라고 표시 될것이다. 왜 99%이상이라고 했냐면 100% 넘은 헌금을 갑자기 하는 분이 있을것다. 초과 달성자죠. 모두 완납 이라고 표시하기 위해서 99%로 한것이다. 물론 IF문을 중복하면 여러가지 다 표시할수 있겠지만 그럼 너무 복잡하니 오늘은 여기서 강의를 마친다.


좋은하루아래는 파일을 올려 놓았다. 선택하면 다운 받을수 있다.

SUMIF연습1.xls

반응형
- 구독과 공감(♥)은 눌러주세요 -