세 가지 참조 방식과 자동 채우기에 변화를 알아보았으니 이제 실제로 실무에서는 어떻게 사용하는지 한번 보겠습니다.
예제는 지금까지 만들어 오고 있는 "찰라면 판매 현황"입니다. 이 페이지 하단의 예제 파일을 다운로드하고 열어 주세요. 일단 부가세부터 만들어 보겠습니다.
좌측 그림처럼 F4셀을 선택하고 "부가세"라고 입력한 후 엔터로 완성합니다. 우측 그림처럼 자동으로 채우기 색이 들어옵니다.
엑셀은 지금처럼 표에서 연장된다고 판단할 때 자동으로 서식을 만들어 주는 경향이 있습니다. 보시는 바와 같이 테두리는 안 생겼습니다. 나중에 다른 서식들처럼 맞춰주면 됩니다.
부가세는 금액의 10%죠 금액에 0.1을 곱하면 됩니다. 간단하게 참조하고 곱하면 끝납니다.
- 부가세를 작성할 첫 셀(F5)을 클릭하여 선택합니다.
- 수식 입력줄에서 "="을 입력하고 수식 작성을 시작합니다.
- 참조할 금액 셀을 클릭합니다. => 수식 입력줄에 "E5"가 자동 작성됩니다.
- 부가세는 금액의 10%이니 0.1을 곱해하고 엔터로 수식을 완성합니다.
- 다시 자동 채우기 대상 셀(F5)을 클릭하여 선택한 후 우측 하단에서 클릭 드래그하여 자동 채우기를 진행합니다.
값이 정상 작성되었습니다. 하지만 테두리가 표처럼 보이지 않습니다. 다시 테두리를 그려도 되지만 지금 같은 경우에는 다시 테두리를 그리지 않습니다. 테두리를 그리기 위해 두께를 확인하고 색깔도 확인해야 하므로 다시 테두리를 그리는 것은 여간 귀찮은 일이 아닙니다.
지금 같은 경우는 간단하게 값은 빼고 서식만 복사해 오면 됩니다.
- 복사할 대상 셀을 모두 선택합니다. 지금의 경우는 우측 테두리가 없는 E4:E9 범위가 적당하겠습니다. C4:C9 또는 D4:D9 범위를 복사해도 되지만 그 경우에는 오른쪽 테두리가 있기 때문에 다시 지워줘야 하는 일이 생기게 되겠죠.
- 리본 메뉴에서 서식 복사 버튼을 클릭합니다. => 선택한 범위가 모두 초록색 점선으로 처리됩니다. 복사된 부분을 알려주는 것입니다.
- 범위를 복사했으니 붙여 넣기 할 범위의 시작 셀(F4)로 마우스를 이동합니다. 이때 마우스의 모양이 브러시 모양으로 바뀌는 것이 확인됩니다. 클릭합니다.
지금한 방법은 셀의 서식만 복사해 온 것입니다. 값에는 전혀 영향이 없습니다. 굉장히 많이 쓰이는 기능이니 꼭 익혀두시기를 바랍니다.
이번에는 성과금을 만들어 보겠습니다.
판매팀별로 열심히 "찰라면"을 팔았으니, 회사 입장에서 판매한 금액의 20%를 성과금으로 지급하려 합니다. 껌은 사 먹을 수 있겠네요.
물론 부가세처럼 바로 금액에 0.2를 곱해서 구할 수도 있겠지만 성과금 비율을 만들어 놓고 참조하는 방식으로 사용한다면 나중에 성과금 비율을 조정하여 순식간에 성과금 계산을 할 수 있게 됩니다.
표의 머리글로부터 위로 2행 추가하겠습니다. 행 머리글 4에서 마우스 우클릭하고 삽입을 눌러 한 행을 추가하고 다시 4에서 우클릭하고 한 행을 더 추가합니다.
행 추가 방식이 생각나지 않으신다면 아래 강좌를 확인한 후 돌아와 주세요.
- 4행의 아래 경계선을 클릭 드래그하여 4행의 높이를 넓혀줍니다.
- F4, G4에 "성과금 비율"과 수치를 작성합니다.
- 표에서도 부가세처럼 성과금 항목을 레이블에 만들어 줍니다.
일단 준비는 끝났습니다. 이제 G7셀에 "금액*성과금 비율"을 수식으로 작성하고 G11셀까지 자동 채우기를 진행하면 되겠습니다.
- 수식을 작성할 G7셀을 클릭하여 선택합니다. 이때 만약 더블 클릭하여 입력 단계(상태 표시줄 확인)로 들어갔다면 Esc키를 눌러 입력 단계에서 빠저나옵니다.
- 수식 입력줄에서 "="을 입력하고 수식을 시작합니다.
- 계산될 금액 셀(E7)을 클릭하여 참조합니다.
- "*"를 입력하고 성과금 비율 셀(G4)를 클릭하여 비율도 참조하고 엔터로 수식을 완성합니다.
- 다시 G7셀을 클릭하여 선택하고 우측 하단에서 아래로(G11) 자동 채우기합니다.
문제없이 잘한 것 같은데 에러가 발생했습니다. 왜 일까요?
혹시 느낌이 왔다면 대단하신 겁니다. 지금까지 진행한 상대 참조, 절대 참조, 혼합 참조를 이해하신 거네요.
수식 탭 > 수식 표시를 눌러 수식을 확인하겠습니다.
일단 G7셀은 매우 잘 작성되었습니다. 그런데
G8셀은 금액에 성과금 비율 G4를 곱해야 하는데 G5를 곱했네요
G9셀도 금액에 성과금 비율 G4를 곱해야 하지만 G6을,
G10은 G7을
G11은 G8을 곱했습니다.
바로 상대 참조를 자동 채우기했기 때문에 숫자가 증가한 것이죠. 그래서 각 셀은 좌측으로 2칸 옆의 값과 3칸 위 칸의 값을 곱한 결과가 돼버린 것입니다. 곱할 수 없는 공백이나 문자를 곱했으니 당연히 에러가 발생했습니다.
이유를 알았으니, 해결법도 알 수 있습니다. 참조의 방식을 달리해야 한다는 것입니다.
간단하게는 성과금 비율을 절대 참조로 고정하는 것입니다. 움직이지 못하게 하면 해결되겠죠. 위의 경우는 사실 절대 참조가 적당합니다. 하지만 혼합 참조로도 가능하겠죠. 그렇다면 알파벳을 고정해야 할까요? 숫자를 고정해야 할까요?
다시 열과 행을 분리하고 변할 것인지 고정할 것인지를 판단해야 합니다.
열로는 자동 채우기가 진행되지 않습니다. 모두 G열이니 고정하든 하지 않든 문제가 되지 않습니다.
다음 행으로는 모두 4행을 참조해야 합니다. 변하면 안 되는 거죠. "$"로 고정해야 합니다.
리본 메뉴에서 수식 표식을 다시 눌러 일단 돌아와주세요.
- G7셀을 다시 클릭합니다.
- 수식 입력줄에서 G4부분을 클릭 드래그하여 블럭 선택합니다.
(G 앞에 커서를 위치 시키셔도 되지만 잘 못 클릭하면 곱하기 앞으로 위치될 수 있으니, 눈으로 확인하면서 블럭 선택하는 편이 정확합니다) - 키보드 상단의 기능키 중 F4키를 반복해서 눌러 숫자만 고정(행 고정)해 주고 엔터로 수식을 다시 완성합니다.
- 적용된 수식은 G7뿐입니다. 나머지 셀도 동일하게 적용하기 위해 다시 G7셀을 클릭하고 클릭 드래그하여 G11까지 자동 채우기합니다.
에러 없이 셀 값은 정상 작성되었습니다.
이제 나머지 구분이 좋지 않은 셀들도 서식을 정리해 줍니다. 일단 성과금은 부가세처럼 서식 복사를 진행합니다.
- 복사할 대상 셀을 모두 선택합니다. 지금의 경우는 우측 테두리가 없는 F6:F11 범위가 적당하겠습니다.
- 리본 메뉴에서 서식 복사 버튼을 클릭합니다. => 선택한 범위가 모두 초록색 점선으로 처리됩니다.
- 범위를 복사했으니 붙여 넣기 할 범위의 시작 셀(G6)로 마우스를 이동합니다. 이때 마우스의 모양이 브러시 모양으로 바뀌는 것이 확인됩니다. 클릭합니다.
이 방법은 범위의 모든 서식을 복사하여 범위에 붙여 넣는 방법입니다.
반면 하나의 서식을 범위에 붙여 넣는 방법도 가능합니다.
- 복사할 대상인 E2셀을 클릭하여 선택합니다.
- 서식 복사 버튼을 클릭하여 서식만 복사합니다.
- E2셀처럼 만들고 싶은 셀은 F2와 G2입니다. F2클릭한채 G2까지 드래그합니다.
F2:G2가 E2셀의 서식으로 만들어졌습니다. 지금처럼 하나의 셀 서식을 여러 셀 서식에 적용할 때는 클릭 드래그하여 붙여 넣기 해주시면 됩니다.
성과금 비율은 그냥 만들겠습니다.
- F4:G4를 범위 선택
- 테두리 버튼을 클릭하고 모든 테두리를 선택합니다.
- 다시 F4셀만 클릭하여 선택
- 채우기 색을 회색으로 선택합니다.
모든 서식이 정리됐고 성과금 비율을 조정하여 성과금이 자동으로 적용되는 시스템이 만들어졌습니다.
의견글 입니다.
()
