[스프레드시트 활용] 복잡한 정산도 쉽게 계산할 수 있는 여행 경비 정산 시트

여행 경비 정산용 시트는 이전 포스팅 구글 스프레드시트 사용법 (11) 여행 비용 정산 서식 에서 다룬 적이 있습니다. 이번 포스팅에서는 좀 더 복잡한 정산도 쉽게 계산할 수 있도록 업그레이드한 확장판 버전을 공유하겠습니다.

1. 한명이 대표로 여행 경비를 계산한 경우의 여행 정산 시트
2. 계산한 사람이 여러명인 경우의 여행 정산 시트 확장판 설명
3. 복잡한 정산도 쉽게 계산할 수 있는 여행 정산 시트 공유
4. 여행 정산 시트 사용 방법

한명이 대표로 여행 경비를 계산한 경우의 여행 정산 시트

예전 포스팅에서 공유했던 여행경비 정산 시트에 대해 간단하게 설명드리자면, A~D열에 여행 비용 내역을 입력하고, 각자 계산한 내역일 경우 ‘인원’ (E열)에 1을 입력하고, 정산이 필요한 공동 내역의 경우 여행 인원을 입력합니다. 예시는 2명이서 다녀온 제주도 여행이었기 때문에 정산이 필요한 내역에 2를 입력한 것입니다.

해당 시트는 여행 멤버 중 한명만 여행 경비를 계산하고 나머지 인원은 추후에 정산하는 경우에 적합합니다.

• 여행중: ⓐ 와 ⓑ 둘이서 여행 중 ⓐ가 대표로 여행 비용 결제💳
• 여행 후: ⓑ가 ⓐ에게 정산 비용을 송금💸

예시처럼 2명이 여행했고, 둘중 한명이 경비를 계산한 경우 이 시트에서 ‘인원’이 2로 입력되어 있는 ‘1인당 비용'(F열)을 합산한 금액을 정산하면 됩니다.

정산 금액 수식 =sumif(E:E, 2, F:F)

A~E열의 결제 내역을 정리 한 후, SUMIF 함수를 사용하여 ‘인원'(E열)이 2인 ‘1인당 비용'(F열)을 합산하였습니다. 예시에서는 여행 비용을 계산한 사람에게 214,195원을 정산하면 되는 겁니다.

그런데 말입니다. 만약 결제한 사람이 한사람이 아니라 여러명인 경우에는 이 시트 만으로는 정산하기 어렵습니다.

여행을 하다보면 여행 경비를 담당하는 사람이 아닌 다른 여행 멤버가 결제하게 되는 일도 발생하게 됩니다. 그러면 정산이 복잡해지는데요. 과거 여행 중에 실제로 그런 경우가 발생하여 복잡한 정산을 쉽게 하고자 새로운 여행 경비 정산 시트를 만들어보았습니다.

계산한 사람이 여러명인 경우의 여행 정산 시트 확장판 설명

정산 시트에 사용한 예시 데이터는 저의 실제 여행 비용 내역이며, 항목에 따라 결제한 사람과 정산 대상 인원이 달라 정산이 아주 복잡해진 케이스 입니다.

여행 멤버는 사과, 감귤, 포도, 레몬 으로 4명이고 주로 결제를 진행한 사람은 사과였습니다. 외국에 거주하는 친구인 레몬의 한국 여행을 반기는 1박 2일 여행 정산 내역이라 정산 대상이 3명 혹은 4명이 되었습니다.

정산 대상이 되는 경우를 식별하기 위해서 E열~H열 헤더에 각 멤버의 이름을 입력하고, 아래쪽에는 체크박스를 만들었습니다. 정산 대상인 경우 체크박스를 체크하고, ‘금액’ (D열)데이터가 있는 경우 체크되어 있는 체크박스 갯수로 나누면 ‘1인 비용'(J열)이 계산되도록 만들었습니다.

1인 비용(J열) 수식 =if(D2>0, D2/COUNTIF(E2:H2, true), )

그리고 ‘결제한 사람'(I열)에는 해당 비용을 결제한 사람의 이름을 입력했습니다. 이름을 입력하기 쉽게하기 위해서 E~H열 헤더에 입력한 멤버들의 이름을 드롭다운으로 선택할 수 있도록 데이터 확인 규칙을 설정했습니다.

정산 대상이 되는 왼쪽의 A~I열에 여행 비용 내역을 입력하면 각 내역당 1인당 정산 비용을 계산하여 ‘1인 비용'(J열)에 표시해주고, 오른쪽 정산표에 멤버별로 1인당 정산 비용 합계와 결제한 금액의 합계를 계산해서 표시합니다. 시트에도 적어두었지만, 하늘색 배경의 셀에는 계산을 위한 수식이 설정되어 있기 때문에 데이터를 입력하시면 안됩니다.

개인별 비용 합계 수식 =SUMIF({해당 이름의 체크박스 열}, true, $J:$J))
개인별 결제 합계 수식 =SUMIF($I:$I, {해당 이름},$D:$D)

SUMIF 함수를 사용하여 각 멤버에 해당하는 항목의 ‘1인 비용'(J열)과 결제한 ‘금액'(D열)의 합계를 계산했습니다.

마지막으로 정산 금액은 개인별 결제 합계에서 개인별 비용 합계를 뺐습니다.
정산 금액이 음수로 나왔다면 개인별 비용보다 계산(지불)한 금액이 크므로 입금 받을 금액이고, 양수로 나왔다면 그 반대로 송금해야하는 금액입니다.

예시에서는 사과의 정산 금액만 음수로 나왔기 때문에 160,237원을 다른 멤버에게 정산 받으면 되고, 나머지 멤버들은 송금해야하는 금액입니다. 주로 계산한 사과만 정산 받을 금액이 있으므로 나머지 멤버들은 사과에게 계산된 정산 금액을 송금해주면 됩니다.

보너스로 ‘구분'(B열) 데이터를 넣으시면 정산표 아래에 카테고리별 비용 합계를 계산해서 보여주는 피벗 테이블을 넣어두었습니다. 피봇 테이블로 계산된 데이터로 원형 그래프를 만들면 카테고리별 비용의 비율을 쉽게 확인할 수 있습니다.

부가적으로 ‘구분'(B열)도 입력하기 쉽게하기 위해서 자주 사용하는 카테고리를 드롭다운으로 선택할 수 있도록 데이터 확인 규칙을 설정해두었습니다.

복잡한 정산도 쉽게 계산할 수 있는 여행 정산 시트 공유

예시처럼 복잡한 정산을 쉽게 계산할 수 있는 여행 정산 시트를 공유해드리겠습니다.

⭐ 공유 스프레드시트: 여행 비용 정산 시트
https://docs.google.com/spreadsheets/d/15CyFlS92-1o0PKdROSc8E2o2nSdvHsSpjz50AQrhAjk/copy

• 여행 멤버는 5명까지 설정 가능.
• 구분은 데이터 확인 규칙을 이용하여 드롭다운에서 선택 가능
  • 구분 항목: 숙박, 식사, 교통, 간식, 관광, 기타
  • 구분 항목을 바꾸고 싶다면 데이터 확인 규칙에서 항목을 추가, 삭제, 수정하시면 됩니다.

여행 정산 시트 사용 방법

1. 공유되어있는 여행 비용 정산 시트의 사본을 만들어 내 드라이브에 저장한다.

2. E~I열의 헤더에 멤버들의 이름을 입력한다.

예를 들어, “이름1” 자리에 “홍길동”을 입력하면 계산(J열) 드롭다운과 정산표의 해당 자리에 이름이 자동으로 “홍길동”으로 표시됩니다.

3. A~J열에 여행중 발생한 비용의 내역을 입력합니다.

• A열 날짜: 결제한 날짜
• B열 구분: 해당 항목의 카테고리. (숙박, 식사, 교통, 간식, 관광, 기타)
• C열 내역: 결제한 내역의 상세
• D열 금액: 결제한 금액
• E~I열의 체크박스: 정산 대상이 되는 사람의 체크박스를 체크☑️
• J열 결제한 사람: 해당 항목을 결제한 사람 (E~I열의 헤더에 입력한 사람 이름)

위에서 공유한 예시 시트를 참고하시기 바랍니다.

＊데이터 입력시 주의사항

A~B열의 ‘날짜’, ‘구분’은 필수 데이터는 아니어서 입력하지 않아도 큰 문제는 없습니다. 하지만 C~I열의 ‘내역’, ‘금액’, 멤버별 체크박스, ‘결제한 사람’ 데이터는 필수로 입력해주셔야 합니다.

E~I열의 체크박스는 행별로 최소 한개는 체크해주셔야 ‘1인 비용'(K열)을 계산할 수 있습니다. 체크박스는 체크하지 않고 ‘금액'(D열)만 입력하면 ‘1인 비용'(K열)에 오류가 발생합니다.

‘금액'(D열)과 E~I열의 체크박스 중 최소 한개이상 체크해야 ‘1인 비용'(K열)이 정상적으로 계산됩니다.

4. 오른쪽에 계산된 정산표 결과를 여행 멤버들에게 공유하고 정산하시면 됩니다.

• 정산 금액이 음수: 개인별 비용 < 결제한 금액, 해당 멤버가 입금 받아야 하는 금액
• 정산 금액이 양수: 개인별 비용 > 결제한 금액, 해당 멤버가 송금해야 하는 금액