출처 : 여성시대 여행요정
성릠들 자이루
여름이라 해외랑 국내로 친구들이랑 많이 다니지???
그리고 덮어놓고 쓰다보면 거지꼴을 면치 못하기 마련 큼큼
나는 정산을 매번 엑셀로 하는데
이것도 하다보니 거기서 거기라 매번 정리하는 것도 귀찮구
돈을 쓰는 순간에 얼마나 쓰고 있는지를 확실히 했음 좋겠더라구
그래서 마스터 파일을 만들어왔음다 ^^77
엑셀 초보도 할 수 있게 설명해줄게
일단 첨부파일 다운 받고 팔로미
두번째 시트 먼저 보실게여
여행 이름~~~ 이건 뭐 여샤가 쓰고싶은 거 쓰구
여긴 기본적으로 쓴 내용을 계속 적는 공간이야
여행자 1~3 더 필요하다면 여행자 n까지 옆으로 늘려주면 돼
그래서 1이 냈으면 항목을 잘 기억할 수 있게 적고(비행기 숙소 등)
1의 지불칸에다 금액을 입력하면 돼
여기서 두 가지 중요한 건
1. 환율계산
지금 나는 미달을 쓰는 곳으로 여행을 갈 거라서
환율입력 아래(E2)에 1.159를 넣어놨어
내가(여행자 2) 미달로 버스를 결재했으면
미달 열에 230이라고 적는 거야
그러면 기본적으로 한화 칸에는 미달*환율 한 금액이 들어가게 되어있어
캡쳐 수식 보이지?
=F9*$E$2
해외결제 카드 긁은 건 수수료 포함해서 확정이 늦게나는 경우에
얼마나 쓰고 있는지 감을 잡으려고 만들어 둔 거야
그래서 꼭 달러가 아니어두
엔화나 위안 뭐 이런 식으로 국가별 환전이 여행지에 따라 다르자나?
그럼 환율 (E2) 칸을 적당한 금액으로 바꾸고
미달 부분이름을 엔화나 현지돈 이렇게 입력하고
그 열에 맞게 금액을 넣으면 됩니다
그래서 각 여행자 한화부분은
미달*($E$2) 상태인 것!
☆엑셀에서 $는 절대주소야 알아두자
---------
<심화학습>
만약 유럽여행을 해서
유로도 쓰고 프랑도 썼다 뭐 이런 식이면
환율입력
(유로금액)
(프랑금액)
이렇게 아래로 각자 다른 환율을 쓰고
여행자별로
유로/프랑/한화
열을 추가해주면 돼!
그리고 이 땐 한화 자동입력 값을
=유로셀주소*(유로환율=$E$2)+프랑셀주소*(프랑환율=$E$3)
해놓고 쭉 드래그!!
---------
쉽지??
카드사수수료로 현화*환율보다 실제 출금액이 많이 나왘서!
하면 걍 수식 무시하고 숫자 기입하시면 됩니다
주의사항 2번은
n빵을 정의하는 거야
기본적으로 유형을
개인 / 여행자 1&2 / 1&3 / 2&3 / 셋이
이렇게 뒀는데
필요한만큼 줄이거나 늘려서 사용하면 되겠지?
캡쳐도 세탁해왔어 ^^
데이터 > 데이터유효성검사에서 목록을 수정해서 쓰면 돼
그니까 여행자 123이 아니라
이름으로 a b c 이렇게 수정했으면
유효성검사 목록에서도 수정을 해줘야해
그리고 홈 > 셀서식 복사해서
n빵유형의 데이터유효성 영역을 같게 만들어주자
이게 왜 중요하냐면
피벗테이블을 이걸로 만들거라서!
항목 구분이 잘 되어있어야해!
드디어 첫번째 정산 시트로 돌아가면
파란색 실제 지출 부분은 피벗이야!
2번 지출 시트에서 가져온 내용을
☆☆n빵 유형 기준으로
☆☆한화 지출 값의 합계를 낸거야!!!
이거는 지출내역에서 내용을 추가한 다음엔
피벗테이블 업데이트를 누르면 자동으로 계산이 될거야
완전 쉽고 편하죠???
그리고 우리가 궁금한 건 그래서 내가 누구한테 돈을 주고
누구한테 돈을 받아야하나? 이것이기 때문에
옆에 두개를 더 만들어뒀어
녹색은 실제로 1/n을 했을 때야
아까 지출시트에서 셋이 쓴 금액은 유형을 셋이< 요걸 했을거고
그럼 셋이 유형에서 나간 모든 돈을 /3했을 때
실질적으로 얼마인지가 나오는 거야
빨간색은
그래서 실제 1/n이 얼마였는데
누가 얼마를 냈기 때문에
차액이 어떻다!를 보는 거야
까만 글씨는 누군가에게 그만큼을 줘야한단 거고
(빨간 글씨)는 받아야된단 소리지(마이너스)
맨 아래
실제로 그래서 얼마씩 여행에 쓰는 중인지를 나타냈어
피벗 사람 별 쓴 금액 sum 값에서 1/n한 금액까지 더한 값이야!
여행에서 걍 항목이랑 결재금액만 정리해도
피봇에서 바로 볼 수 잌ㅅ으니까 감이 좀 올 거야
차액은 뭐 기부한다 생각하궄 ㅋㅋㅋㅋㅋ
즐겁게 여행하면서도
다녀온 후에 거지꼴은 면하자 흑흑 ㅠㅠ
그럼 빠용가☆
였다가 간지 안나게 돌아옴 ▼ 추가된 부분
두번째 시트! 사용내역을 줄줄 적는 곳에서 우리 여행하면 돈 많이 쓰자나....
이게 200개 아래로 계속 내려가면 개인인지 여행자 2&3인지 일일이 확인하면서 보기 귀찮잖아..
직관적으로 N빵 유형이 어떤 앤지를 잘 보게 하려고
앞에서 설정한 데이터유효성 목록에 맞게 내가 자동으로 열 색깔 서식을 걸어놨어
무슨 소리냐면
엑셀에는 중복이면 빨간색으로 보여조! 라거나
40보다 나이 많은 타코야끼들은 이름을 구린색으로 표시해조! 같은
'조건' 에 따른 '값'을 찾아내는 기능이 있음다
= 조건부 서식
이걸 활용하는 거야
홈 > 조건부서식 > 맨 밑에 규칙관리를 눌러줘!
그러면 서식 규칙 표시가 현재 선택영역(디폴트)값으로 되어서
여기 어떤 서식들이 적용되어있는지가 나올거야!
우리는 셀 하나만 규칙을 수정할 게 아니라 여기 전체를 수정할거니까
지금 캡쳐에 하늘색 되어있는 칸 보이지?
서식 규칙 표시를 꼭 현재 워크시트로 캡쳐처럼 바꿔줍니다!
그러면 내가 공유한 파일에는 수식이 총 3개가 있어
셋이 / 여행자 1&2 / 여행자 1&3
이거는 내 기준에서 1이 돈을 팡팡써서 그랬던거야
자 여기서
앞에 N빵 유형 바꾸는 방법을 잘 익혀가지구 구분을
셋이 / A랑 B / A랑 C / B랑 C / 개인
이렇게 바꿨다면
요기 조건부 서식에서 여행자 1&2 를 A랑 B로 이런 식으로 바꿔주면 되는거야
규칙 수식 부분을 두 번 누르면 서식 규칙 편집으로 넘어가질거야!
보면
다음 수식이 참인 값의 서식 지정 아랭
=$E7="여행자1&2"라고 되어있지
굳이 설명하면 이래
=$E (엔빵유형 열 E를 확인할 것! 그리고 색칠되는 값은 행 기준이 될 것!) 7 (이거는 7 89 10 11 ~~~~ 쭉 내려가면서 모두 검토할 것!)
="큰 따옴표 안에 있는 것이 참이다"
결과적으로 A랑 B라고 지정을 하고 싶으면
=$E7="A랑B"
이렇게 수식을 바꾸시면 됩니다!
꼭 "안에 써야해"
바꾸고 미리보기 옆에 있는 서식을 누르면
표시형식 / 글꼴 / 테두리 / 채우기를 더 휘황찬란하게 개인의 취향에 따라 만들 수 있어
캡쳐 봐주라!
---------------
<심화학습>
만약에 B랑C라는 새로운 항목에도 색을 넣어주고 싶다!
그러면 조건부서식을 하나 더 추가해주면 되겠지?
아까 조건부 서식 규칙 관리자에서 새 규칙을 누르고
조건 =$E7="B랑C" 라는 걸 전체 워크시트에 걸어주면 되는거야!
홈에서 조건부서식 > 새규칙 > 규칙유형: 수식을 사용하여 서식을 지정할 셀 결정
이걸 통해서도 똑같이 들어가서 적용 시킬 수가 있습니다.
---------------
헉헉 월루가 굉장히 보람차네 ^0^
또 궁금한 거 있음 물어봐조!
빠이루!