Excel-엑셀 활용-01

엑셀실생활예제.zip

잘 쓰면 참 편한 엑셀

실생활에 유용한 쓰임새를 소개해드립니다. 

일간, 월간, 년간 판매 혹은 소비를 집계하는데 유용하다고 생각됩니다.

이번 포스트에서는 아래의 개념들이 사용됩니다.

이름정의, 이름정의 삭제, 드롭다운, 유효성검사, indirect, iferror, 대분류, 소분류

자 그럼 시작해보겠습니다.

01. 데이터 목록 작성하기

일단 본인이 음식점 사장님이라고 생각해보죠. 온갖 종류의 음식을 다 팔고 있습니다. 어떤 음식이 얼마나 팔리고, 어떤 연령층이 주로 사먹는지 등을 집계하고 싶습니다. 잘팔리는 것은 재료를 더 많이 사둬야되니까요. 

중식, 한식, 일식에 치킨등등 많기도 하군요.

집계에 필요한 정보들을 다 적어둔 다음에 각 부분에 이름을 달아줍니다.

가장 크게 분류가 되는 식사종류를 선택하고 왼쪽 위에 '식사메뉴'라고 적어줍니다.

요 이름은 아무렇게나 지어도 상관없습니다.

다음은 각각의 식사종류에 들어있는 메뉴들에 이름을 달아줍니다.

여기서는 메뉴들의 이름을 '식사메뉴'로 지정된 식사종류들의 이름과 똑같아야합니다. 이름정의를 사용할 때는 띄어쓰기가 불가능하므로 띄어쓰기가 포함되어 있을 경우 '_'다운 하이푼을 사용해주면 좋습니다.

메뉴들 이름정의가 끝났으면 메뉴별 가격에 이름정의를 해줍니다.

네, 하나하나 다 해야됩니다. 대신 한번만 해놓으면 쭉 쓸 수 있으니까요 :)

이렇게 데이터 목록 작성이 끝났습니다.

추가로 신메뉴를 넣고 싶다거나 메뉴 가격이 바뀌면 지정해준 이름정의 삭제하고 신메뉴를 포함한 부분을 다시 같은 이름으로 지정해주면 됩니다.

Ctrl+F3을 눌러서 이름관리자를 실행시킨뒤 수정하려는 이름을 삭제하고 수정후에 다시 등록하면 됩니다.

02. 유효성검사를 통한 드롭다운 목록 생성

이제 열심히 만든 이름정의를 사용해 볼 시간입니다.

먼저 대분류를 사용할 셀을 선택해줍니다. 지금 대분류는 한식, 중식, 일식 같은 식사종류겠죠.

그리고 유효성 검사에 들어가서 목록을 선택해줍니다.

목록으로 사용된 리스트를 지정해줍니다. 위에서 '식사메뉴'라는 이름으로 각종 식사종류를 지정해뒀습니다. 

짜잔! 셀 옆에 화살표가 생기고 셀에 입력될 수 있는 목록이 나옵니다.

이제 한칸 옆으로 가서 메뉴에도 드롭다운 목록을 생성해야합니다. 똑같이 유효성 검사로 들어가서 목록을 선택하는데... 넣어야할 목록이 너무 많습니다.

중식, 한식, 일식, 치킨... 등의 목록을 다 넣어야하는데...

이때 사용되는 함수가 indirect입니다. 이름 붙인 셀영역을 바로 이어주는 함수죠. 

이해가 잘 안 돼도 괜찮습니다. 백문이 불여일견!

원본에 =indirect(C3)이라고 입력해줍니다.

저렇게 쓴 것은 "C3에 나온 텍스트와 같은 이름정의와 연결해라"라는 명령입니다.

확인을 누르면 지금 C3는 빈칸이기때문에 오류상태라고 뜰겁니다. 다시 확인을 눌러줍니다.

앞에 나오는 식사종류에 따라 메뉴 드롭창의 목록이 바뀌게 됩니다.

식사종류를 했던 것과 같은 방법으로 나이와 성별도 목록을 지정해줍니다.

03. iferror 함수 활용

이름정의했던 것 중에 가격이 남았군요. 

판매가 밑에 셀에 "=indirect(D3)*E3"라고 입력해줍니다.

이 수식은 "D3에 나온 텍스트와 같은 이름정의를 연결한 후 E3에 있는 수량과 곱셈해라"라는 명령이죠.

그런데!

참조값이 없다는 에러 표시가 똭!

당연히 앞칸이 비었으니 참조값이 없긴한데... 왠지 에러 표시는 사람 마음을 심쿵하게 하죠. 거슬립니다!

매우 거슬림.

이때 쓸 수 있는 함수가 iferror입니다.

수식이 에러일 경우 출력값을 조절할 수 있는 함수죠.

=iferror(수식,오류시 출력값)

이렇게 사용할 수 있습니다.

잘 모르시겠으면 일단 해보는거죠!

위에서 넣은 "=indirect(D3)*E3"수식 위로 "=iferror(indirect(D3)*E3,"")을 추가해줍니다.

"indirect(D3)*E3"가 오류일 경우에는 " "를 출력한다. 는 명령입니다. 큰따옴표 사이에 아무것도 넣지 않았으니 빈칸이 출력되겠죠. 

이런식으로도 가능합니다. 숫자도 오케이.

여기까지 만들었다면 메뉴 선택후 수량만 입력하면 자동으로 가격이 출력되게 됩니다.

다음 포스팅에서는 집계된 자료로 분석하는 법을 다뤄보겠습니다.