너무 쉬운 엑셀 강좌 유용한 기능 소개

베베슈슈의 엑셀 강좌

목차

오늘은 엑셀을 사용할 때 필요한 유용한 기능에 대해서 소개해보고자 한다.

VLOOKUP이나 COUNTIF처럼 유용하고 강력한 도구들도 있지만 그런 부분은 따로 정리해 보기로 하고

이번엔 그보다는 조금 더 근본적인? 조작법에 대해서 소개하려고 한다. 

문자열 이어 붙이기(문자열에 함수 결과 넣기)

엑셀에서 문자열을 더해야 하는 경우가 간혹 있다.

위의 예제에서는 이번달 판매수량 36 개를 표시하기 위해서 세 칸의 셀이 필요했는데

칸을 나눌 여유가 없거나 모양을 예쁘게 하기 위해서 문자열을 붙여서 써야 하는 경우들이 자주 있다.

1. 엑셀의 문자열은 & 문자를 이용해서 붙여서 쓸 수 있다.
2. 함수에서 리턴되는 값들은 &를 이용해서 문자열과 이어 붙일 수 있다.

이 두 가지 규칙을 잘 기억해 두면 문자열을 쉽게 붙여서 사용할 수 있다.

위의 예제(이번달 판매수량 36 개)를 예시로 들어보면

="이번달 판매수량" & SUM(데이터!C3:C6) & 개
이렇게 입력하면 
->이번달 판매수량36개
이렇게 표시가 된다.

조금 더 보기 좋게 하기 위해서
="이번달 판매수량 : " & SUM(데이터!C3:C6) & 개
이렇게 입력하면 
->이번달 판매수량 : 36개
이렇게 표시가 된다.

 

하나 더 예를 들어 A2셀에 있는 값과 B12에 있는 값을 이어 붙이고 싶으면

=A2 & B12 라고 써주면 되는데

이때 띄어쓰기를 한 칸 넣어주고 싶다면

=A2 & " " & B12

이렇게 입력해서 A2와 B12 사이에 " " 문자열을 하나 더 넣으면 된다.

참조하는 값 고정하기

엑셀에서 자주 사용되는 매우 유용하고 파워풀한 도구가 채워 넣기라고 생각한다. 

값이 있는 셀의 오른쪽 하단을 클릭해서 드래그를 하면 빈칸에 값이 채워지는데

셀을 참조한 상태에서 드래그하면 셀의 값이 자동으로 늘어나서 곤란한 경우들이 있다.

 

이자 계산기 예제

예를 들어서 연리 5%인 예금상품을 1개월 단위로 가입해서 복리를 누리면 얼마의 이자가 들어오는지를

계산하고 싶다고 해보면 이자율을 C1에 넣고 원금을 F1에 넣었을 때 1개월 째의 이자를 나타내기 위해

F2 셀에 =F1+F1*C1/12 라고 값을 입력해 보았다. 연리가 5%의 1개월치 이자를 원금에 더한 값이다.

 

첫 달 이자가 4,167원이 나오는 것을 확인하고 F2의 우측 하단을 누른 뒤 채워 넣기를 하면 

F2에서부터 F7까지 모두 같은 값이 되어버린다.

F3의 계산 식을 살펴보면 =F2+F2*C2/12 이렇게 되어있을 텐데 계산식에 이자가 C1으로 들어가 있어야 하지만

C2로 자동으로 늘어나면서 이자가 0%가 되어버린 것을 확인할 수 있다.

 

이럴 때 참조값을 고정시키기 위해서 $ 기호를 이용한다. 

참조값 고정기능을 이용한 채워넣기 예제

 

위와 같이 C1을 참조할 때 C$1라고 입력해 주면 채워 넣기를 할 때 1의 값은 변경이 되지 않아 F에 있는 모든 셀이 C1의 이자율을 참조한 것을 볼 수 있다.

$는 C의 앞에도 붙을 수 있는데

C값이 고정되어야 하는 경우에는 $C1 (주로 횡으로 채워 넣기를 하는 경우)

1의 값이 고정되어야 하는 경우에는 C$1 (주로 종으로 채워 넣기를 하는 경우)

복잡한 건 모르겠고 무조건 고정값이면 좋겠다! 할 때는 $C$1으로 쓸 수 있다. (VLOOKUP을 하거나 하나밖에 없는 값을 참조하는 경우

값 고정 예제.xlsx

0.01MB

다른 시트의 값 가져오기

엑셀은 여러개의 시트를 사용할 수 있는데 초보자들은 주로 하나의 시트에서 결판을 내는 것을 좋아한다.

하나의 시트에서 계산이 끝난 파일을 복사해서 다음 시트로 붙여 넣은 다음 다시 새로운 전투를 시작한다.

이런 과정이 나쁘다고 할 것은 없지만 세번째 시트를 계산하던 중 첫 번째 시트에서 잘 못 된 값을 발견하면

다시 처음부터 진행해야 한다는 단점이 있다. 

엑셀파일 하단의 시트 탭

엑셀파일 하단의 Sheet1옆의 더하기 칸을 눌러서 시트를 하나 더 생성한 다음

Sheet1은 서식, Sheet2는 데이터라고 이름 지어보자

(예전버전은 새 문서를 만들면 시트가 세 개 만들어져서 이것이 시트라는 것입니다!! 하고 있었는데 

이제는 다들 익숙하다고 생각한 건지 시트는 하나만 생성된다.)

서식 시트의 서식 모양

이번 달 판매수량이 36개로 표시되는데 SUM 함수를 이용해서 데이터시트의 판매수량 값을 더해서 표시했다.

데이터시트의 값을 참조했기 때문에 SUM 함수 안에 들어가는 값은 C3:C6이 아니라 데이터!C3:C6으로 표시되었다.

이번달 판매금액은 =SUM(데이터!D3:D6)으로 입력해서 데이터시트의 판매금액을 표시했다.

데이터 시트의 데이터 A:1 에서 D:6 까지 입력 되어있다.

 

이렇게 왼쪽 시트에서는 판매수량과 판매금액만을 표시하고 실제 데이터는 데이터 시트에만 넣어서

데이터 시트의 모양과 상관없이 서식 시트를 꾸밀 수 있다.

시트참조 예제.xlsx

0.01MB

 

복합 예제

이것을 어디에 이용하면 좋을까?

예를 들어 상장을 만들어서 출력을 해야 하는 경우가 있는데 인원이 100명일 때 시트 참조를 사용하면

데이터시트에는 수상할 사람들의 정보를 넣고 출력해야 할 서식 쪽에서는 몇 번째 사람을 참조할 건지만 

지정해 주면 서식을 최소한으로 조작하기에 손쉽게 출력이 가능하다.

 

또 학교에서 학생들을 상담한다고 생각해보자.

반 아이들의 성적을 적어놓은 엑셀 파일을 보면서 상담을 하다보면 실수로 학생 점수가 노출 될 수도 있다.

학생 성적 시트

잘 하는 아이의 점수가 노출되면 상관 없겠지만 성적이 나쁜 아이라면

성적이 노출 된 것에 대해서 마음이 상할 수 도 있지 않을까?

그래서 상담 시트와 성적 시트를 나눠서 상담 시에는 상담시트에 적힌 번호의 아이만 보여주면 좋을 것 같다.

 

위의 예제들에 INDIRECT 함수를 섞으면 만들 수 있다.

성적표 시트에서 INDIRECT를 사용한 예

위의 서식에서 번호 칸만 수정하면 성적 탭의 해당 학생 정보만 하단에 표시가 된다.

번호를 1로 선택하면 홍길동 학생의 성적이, 2로 선택하면 김길동 학생의 성적이 나타단다.

INDIRECT 함수는 괄호 안에 들어간 문자열을 주소로 하여 해당 주소의 값을 리턴해주기 때문에 

문자열 붙이기, 다른 시트의 값 가지고 오기, 참조 주소 고정하기를 이용해서 실용적인 서식을 만들 수가 있다.

성적표 상담 예시.xlsx

0.01MB