2020.02.11

엑셀 '백분율 수식' 따라잡기··· 총액 백분율, 증감율, 그리고 세금 계산 등

JD Sartain | PCWorld
엑셀의 백분율 수식을 활용하면 판매세(그리고 봉사료) 확정부터 증감 계산에 이르기까지 매일 크고 작은 문제를 해결할 수 있다. 아래에서 몇 가지 예를 살펴볼 예정이다. 총액 중 판매세 따로 떼어 놓기, 백분율 증감, 완료 백분율, 백분율 순위(백분위 수) 등이다.
 
ⓒ Getty Images Bank


분수를 백분율로 전환

백분율은 100 중에서 차지하는 부분(분수)이다. 백분율을 구하는 방법은 분자(분수의 윗부분에 있는 숫자)를 분모(분수의 아랫부분에 있는 숫자)로 나눈 다음 100을 곱하는 것이다. 예를 들어 분수 6/12는 6을 12로 나누면 소수(0.5)가 되고 여기에 100을 곱하면 50%가 된다.

엑셀에서는 분수를 백분율로 바꿀 때 수식이 필요 없고 서식 변경만 하면 된다. 예를 들면 다음과 같다.

1. A열에서 A2부터 A11까지 10개의 분수를 입력한다). (참고: 엑셀에서는 분수가 자동으로 기약분수로 변한다(예: 6/10은 3/5로)).
엑셀 기본값은 소수이므로 범위를 강조 표시한 후 서식을 분수로 설정해야 한다. 그 방법은 다음과 같다. 
2. A열의 분수를 B열로 복사한다.
3. 해당 범위를 강조 표시한 후 [홈] 탭으로 이동한다. [표시 형식] 필드의 드롭다운 메뉴에서 [백분율]을 선택한다.

참고: [셀] 모음의 [서식] 버튼에서 [셀 서식]을 선택해도 된다.
 
ⓒ ITWorld


총액에서 판매세 따로 떼어 놓기

어떤 회사들은 판매세가 포함된 가격으로 제품을 판매한 후 세금을 따로 떼어 국세청(IRS)에 납부한다. 정가(또는 수령 총액)을 1.0 더하기 판매세율로 나누면 세액을 구할 수 있다. 예를 들어, 전등 판매가가 50달러이고 지방 판매세율이 9%라면 50달러를 1.09로 나눈다. 판매세전 실제 소매가는 45.87달러고 판매세는 4.13달러이다. 구한 답이 맞는지 확인하려면 두 숫자를 합해 보거나 45.87달러에 9%를 곱해 보면 된다.

항목이 하나뿐일 때는 계산기를 이용해도 무방하지만, 주간 또는 월간 제품 판매 내역에서 판매세를 구하려면, 수식을 한 번 입력한 후, 판매 및 재고 스프레드시트 전체에 복사하면 된다.

1. A열에서 A2부터 A14까지 13개 정도의 제품을 입력한다.
2. 그 다음으로는 B열에서 B2에서 B14까지 각 제품의 정가(세금 포함)를 입력한다.
3. C열에서 C2부터 C14까지 임의의 판매세 백분율을 여러 개 입력한다(그래야 다양한 숫자로 실험해 볼 수 있다). 반드시 4.75%와 같은 소수/분수 백분율을 입력해야 한다. 대부분의 판매세는 정수가 아니기 때문이다.
4. D2 셀에 다음과 같은 2단계 수식(=SUM(B2/(C2+1)))을 입력한다. 여기에서 목표는 세금 백분율을 정수 제수로 변환하고(예: 9%를 1.09로) 총 수령 가격($198.56)을 정수 제수(1.09)로 나눠서 정확한 소매가(세전)인 $182.17를 구하는 것이다.
5. 이 수식을 D2에서부터 D14까지 복사한다.
6. E2 셀에서는 B2에서 D2를 차감하여 실제 “따로 뗀” 판매세(국세청 납부용)를 구한다. (=SUM(B2-D2)). 이 수식을 E2에서부터 E14까지 복사한다.
7. 답을 재차 확인하려면, F2부터 F14까지 이 수식(=SUM(D2*C2))을 입력한다. E열과 F열이 일치하면 자료가 정확한 것이다.
 
ⓒ ITWorld


총액의 백분율

자영업자나 재택 근무자의 경우 국세청에서 공제액(임차료, 공공요금, 주택 유지보수 비용 등 중에서 사무실 부분)을 결정하는 한 가지 방법은 주택 전체 면적에서 사무실 면적을 차감하는 것이다. 사업자는 그 총액 중 일정한 백분율을 청구할 수 있다. 사무실 면적을 주택 총 면적으로 나눈 다음 해당 백분율을 기준으로 간접비를 계산하면 된다.

1. 최상단의 B2 셀에 주택 전체 면적을 입력한다.
2. C2에 사무실 전체 면적을 입력한다.
3. D2 셀에 이 수식(=SUM(C2/B2))을 입력하여 사무실 면적 백분율을 구한다(이 경우 25%).
4. A열에 가정 및 사무실 간접비 항목(임차료, 전기요금 등)을 입력한다.
5. B열에 각 항목의 월간 비용을 입력한다.
6. C5셀부터 C12셀까지 이 수식(=SUM(B5*12))을 입력한다. 그러면 연간 총액이 나온다.
7. D5셀부터 D12셀까지 이 수식(=SUM(C5*$D$2))을 입력한다. 셀 주소 D2는 절대적이어야 한다. 함수 키 F4를 이용해 달러 표시를 추가하면 수식이 절대적으로 변한다. 따라서, D열의 각 셀에 D2가 곱해진다.
8. B열, C열, D열의 합을 13행에 입력한다.
매달 그리고 매년 집 전체에 지출한 간접비와 사무실에 한해 지출한 간접비를 확인할 수 있다. D13셀에 표시된 것은 재택근무 사무실 공제 총액($5,088.60)이다.
9. 총 간접비의 항목별 백분율을 계산하려면 E5에서부터 E12까지 이 수식(=SUM(B5/$B$13))을 입력한다. 여기에서 구한 백분율로 월간/연간 간접비가 정상적인 수준인지 판단할 수 있다.
 

가격 증감 백분율

특히 소매분야의 사업체라면 대부분 소유주와 관리자가 매출에서 급여에 이르기까지 거의 모든 것에 대한 증감 백분율을 알고 싶어 한다. 다음 수식을 활용하면 회사 내 증감 백분율을 계산할 수 있다.

워크북을 만들어 ‘증감’이라는 스프레트시트 탭을 추가하고 ‘판매세’라는 또 다른 스프레드시트 탭에는 소매 판매가 정보를 기입했다고 해 보자.

1. [증감] 탭의 A열에 여섯 개 정도의 제품 항목을 입력한다(아니면 위 A 부분의 스프레드시트에서 사용한 항목을 복사한다).
2. B열과 D열에 각 항목의 판매 수량을 입력한다.
3. ‘1월 매출’ 열에서 (C2부터 C14까지) 이 수식(=SUM(판매세!D2*’증감’!B2))을 입력한다. 판매세 스프레드시트 D열의 소매 판매가에 현재 커서가 위치한 증감 스프레드시트 B열의 수량을 곱하는 수식이다.
4. ‘2월 매출’ 열에서 (E2부터 E14까지) 이 수식(=SUM(판매세!D2*’증감’!D2))을 입력한다.
5. 다음으로, F2에 이 수식(=SUM(E2-C2)/C2)을 입력한다.
양수는 1월과 2월 사이에 판매 증가 백분율을 나타내고 음수는 판매 감소 백분율을 나타낸다.

 

ⓒ ITWorld


작업 또는 프로젝트 완료 백분율

프로젝트 관리 소프트웨어 프로그램에 돈을 쓰는 대신, 다음 수식들을 활용하면 지정된 간격에 각 프로젝트의 계획과 흐름을 완료 백분율로 관리할 수 있다.

1. A열에 여섯 개가량의 (진행 중인) 프로젝트를 입력한다.
2. B열과 C열에 각 프로젝트의 시작일과 종료일을 각각 입력한다.
3. (현재까지) 프로젝트 완료 상황을 파악하려면 완료일에서 시작일을 뺀다. D2에서부터 D7 까지 이 수식(=SUM(C2-B2))을 입력한다.
4. E열에 지금까지 완료된 일수를 입력한다. 이 열의 자료만 앞으로 변경하게 된다. 예를 들면, 매일(또는 매주) 한 번 이 스프레드시트에 접근하여 이 열의 자료를 수정하면 F열과 G열(남은 일수와 완료 백분율)에서 정확한 결론을 얻을 수 있다.
5. 각 프로젝트에 남은 일수를 구하려면 F2에서부터 F7까지 이 수식(=SUM(D2-E2))을 입력한다. 이 숫자들은 E열의 숫자 자료(완료 일수)에 따라 계속 변하게 된다.
6. 마지막으로, 이 수식(=SUM(E2/D2))을 입력하면 지금까지 완료된 작업/프로젝트 백분율을 구할 수 있다.  
 
ⓒ ITWorld


백분율 순위

국립공원과 산림관리 일자리에 지원자가 수십 명 있다고 생각해 보자. 이력서와 최초 면접을 보면 지원자들은 모두 동등하게 자격을 갖춘 듯하다. 그러나, 이력서에는 대개 명시되지 않은 최소한의 자격 요건을 충족해야 한다. 예를 들면, 개의 얼굴과 목에 박힌 호저 가시를 안전하게 뽑는 방법 같은 것들이다. 

해결 방법은 지원자들에게 기술 시험을 치르게 한 뒤 엑셀의 PERCENTRANK.EXC 함수를 이용해 각 지원자의 순위 백분위수를 구하는 것이다.

A2셀부터 A11셀까지 지원자 열명의 이름을 입력한다. B2셀부터 B11셀까지는 각 지원자의 기술 점수(10점에서 100점 사이)를 입력한 후 범위의 이름을 정한다.
B2셀부터 B11셀까지 선택한 후 강조 표시한다. [수식]>[이름 정의]>[이름 정의]를 선택한 후 [이름] 필드 상자에 점수라는 단어를 입력한다. [범위] 필드 상자 옆의 화살표를 클릭하고 목록에서 해당 시트의 이름을 선택하여 범위 위치를 확인한다. [참조 대상] 필드 상자에 강조표시/선택된 범위의 셀 주소가 포함된 것을 확인한다. 범위가 맞지 않을 경우, 오른쪽의 붉은 화살표를 클릭한 후 맞는 범위 주소를 입력하거나 맞는 셀을 다시 선택한 후 [확인]을 클릭한다.

참고: 수식이 작동하려면 반드시 ‘배열’의 범위를 정의하고 이름을 지정해야 한다.

다음으로 C2셀에 이 수식(=PERCENTRANK.EXC(점수,B2,2))을 입력한다. 여기에서 ‘점수’는 범위 이름과 같고 B2는 범위의 첫 번째 값이며 2는 소수점 2자리를 표시하라는 뜻이다.

수식을 복사하여 C11셀까지 붙여 넣는다. 목록 아래로 커서를 움직일 때 함수 중에서 셀 주소(B2, B3, B4 등)만 달라지는 것을 확인한다.

보기에 편하도록 소수의 서식을 백분율로 지정할 수 있다. 범위를 강조 표시한 후 [셀 서식] 대화상자에서 [백분율]을 선택하면 된다. 아니면 C열을 복사한 후 D열에서 [붙여넣기] > [특수] > [값]을 선택하여 문자를 복제한다. 단, B열의 값을 어느 하나라도 변경할 경우, 복사-붙여넣기-특수-값 단계를 반복해야 한다.

값들이 항상 업데이트되어 최신 상태가 되게 하려면, D2에 다음 수식(=VALUE(C2))을 입력한 후 복사하여 D11까지 붙여 넣는다. editor@itworld.co.kr



2020.02.11

엑셀 '백분율 수식' 따라잡기··· 총액 백분율, 증감율, 그리고 세금 계산 등

JD Sartain | PCWorld
엑셀의 백분율 수식을 활용하면 판매세(그리고 봉사료) 확정부터 증감 계산에 이르기까지 매일 크고 작은 문제를 해결할 수 있다. 아래에서 몇 가지 예를 살펴볼 예정이다. 총액 중 판매세 따로 떼어 놓기, 백분율 증감, 완료 백분율, 백분율 순위(백분위 수) 등이다.
 
ⓒ Getty Images Bank


분수를 백분율로 전환

백분율은 100 중에서 차지하는 부분(분수)이다. 백분율을 구하는 방법은 분자(분수의 윗부분에 있는 숫자)를 분모(분수의 아랫부분에 있는 숫자)로 나눈 다음 100을 곱하는 것이다. 예를 들어 분수 6/12는 6을 12로 나누면 소수(0.5)가 되고 여기에 100을 곱하면 50%가 된다.

엑셀에서는 분수를 백분율로 바꿀 때 수식이 필요 없고 서식 변경만 하면 된다. 예를 들면 다음과 같다.

1. A열에서 A2부터 A11까지 10개의 분수를 입력한다). (참고: 엑셀에서는 분수가 자동으로 기약분수로 변한다(예: 6/10은 3/5로)).
엑셀 기본값은 소수이므로 범위를 강조 표시한 후 서식을 분수로 설정해야 한다. 그 방법은 다음과 같다. 
2. A열의 분수를 B열로 복사한다.
3. 해당 범위를 강조 표시한 후 [홈] 탭으로 이동한다. [표시 형식] 필드의 드롭다운 메뉴에서 [백분율]을 선택한다.

참고: [셀] 모음의 [서식] 버튼에서 [셀 서식]을 선택해도 된다.
 
ⓒ ITWorld


총액에서 판매세 따로 떼어 놓기

어떤 회사들은 판매세가 포함된 가격으로 제품을 판매한 후 세금을 따로 떼어 국세청(IRS)에 납부한다. 정가(또는 수령 총액)을 1.0 더하기 판매세율로 나누면 세액을 구할 수 있다. 예를 들어, 전등 판매가가 50달러이고 지방 판매세율이 9%라면 50달러를 1.09로 나눈다. 판매세전 실제 소매가는 45.87달러고 판매세는 4.13달러이다. 구한 답이 맞는지 확인하려면 두 숫자를 합해 보거나 45.87달러에 9%를 곱해 보면 된다.

항목이 하나뿐일 때는 계산기를 이용해도 무방하지만, 주간 또는 월간 제품 판매 내역에서 판매세를 구하려면, 수식을 한 번 입력한 후, 판매 및 재고 스프레드시트 전체에 복사하면 된다.

1. A열에서 A2부터 A14까지 13개 정도의 제품을 입력한다.
2. 그 다음으로는 B열에서 B2에서 B14까지 각 제품의 정가(세금 포함)를 입력한다.
3. C열에서 C2부터 C14까지 임의의 판매세 백분율을 여러 개 입력한다(그래야 다양한 숫자로 실험해 볼 수 있다). 반드시 4.75%와 같은 소수/분수 백분율을 입력해야 한다. 대부분의 판매세는 정수가 아니기 때문이다.
4. D2 셀에 다음과 같은 2단계 수식(=SUM(B2/(C2+1)))을 입력한다. 여기에서 목표는 세금 백분율을 정수 제수로 변환하고(예: 9%를 1.09로) 총 수령 가격($198.56)을 정수 제수(1.09)로 나눠서 정확한 소매가(세전)인 $182.17를 구하는 것이다.
5. 이 수식을 D2에서부터 D14까지 복사한다.
6. E2 셀에서는 B2에서 D2를 차감하여 실제 “따로 뗀” 판매세(국세청 납부용)를 구한다. (=SUM(B2-D2)). 이 수식을 E2에서부터 E14까지 복사한다.
7. 답을 재차 확인하려면, F2부터 F14까지 이 수식(=SUM(D2*C2))을 입력한다. E열과 F열이 일치하면 자료가 정확한 것이다.
 
ⓒ ITWorld


총액의 백분율

자영업자나 재택 근무자의 경우 국세청에서 공제액(임차료, 공공요금, 주택 유지보수 비용 등 중에서 사무실 부분)을 결정하는 한 가지 방법은 주택 전체 면적에서 사무실 면적을 차감하는 것이다. 사업자는 그 총액 중 일정한 백분율을 청구할 수 있다. 사무실 면적을 주택 총 면적으로 나눈 다음 해당 백분율을 기준으로 간접비를 계산하면 된다.

1. 최상단의 B2 셀에 주택 전체 면적을 입력한다.
2. C2에 사무실 전체 면적을 입력한다.
3. D2 셀에 이 수식(=SUM(C2/B2))을 입력하여 사무실 면적 백분율을 구한다(이 경우 25%).
4. A열에 가정 및 사무실 간접비 항목(임차료, 전기요금 등)을 입력한다.
5. B열에 각 항목의 월간 비용을 입력한다.
6. C5셀부터 C12셀까지 이 수식(=SUM(B5*12))을 입력한다. 그러면 연간 총액이 나온다.
7. D5셀부터 D12셀까지 이 수식(=SUM(C5*$D$2))을 입력한다. 셀 주소 D2는 절대적이어야 한다. 함수 키 F4를 이용해 달러 표시를 추가하면 수식이 절대적으로 변한다. 따라서, D열의 각 셀에 D2가 곱해진다.
8. B열, C열, D열의 합을 13행에 입력한다.
매달 그리고 매년 집 전체에 지출한 간접비와 사무실에 한해 지출한 간접비를 확인할 수 있다. D13셀에 표시된 것은 재택근무 사무실 공제 총액($5,088.60)이다.
9. 총 간접비의 항목별 백분율을 계산하려면 E5에서부터 E12까지 이 수식(=SUM(B5/$B$13))을 입력한다. 여기에서 구한 백분율로 월간/연간 간접비가 정상적인 수준인지 판단할 수 있다.
 

가격 증감 백분율

특히 소매분야의 사업체라면 대부분 소유주와 관리자가 매출에서 급여에 이르기까지 거의 모든 것에 대한 증감 백분율을 알고 싶어 한다. 다음 수식을 활용하면 회사 내 증감 백분율을 계산할 수 있다.

워크북을 만들어 ‘증감’이라는 스프레트시트 탭을 추가하고 ‘판매세’라는 또 다른 스프레드시트 탭에는 소매 판매가 정보를 기입했다고 해 보자.

1. [증감] 탭의 A열에 여섯 개 정도의 제품 항목을 입력한다(아니면 위 A 부분의 스프레드시트에서 사용한 항목을 복사한다).
2. B열과 D열에 각 항목의 판매 수량을 입력한다.
3. ‘1월 매출’ 열에서 (C2부터 C14까지) 이 수식(=SUM(판매세!D2*’증감’!B2))을 입력한다. 판매세 스프레드시트 D열의 소매 판매가에 현재 커서가 위치한 증감 스프레드시트 B열의 수량을 곱하는 수식이다.
4. ‘2월 매출’ 열에서 (E2부터 E14까지) 이 수식(=SUM(판매세!D2*’증감’!D2))을 입력한다.
5. 다음으로, F2에 이 수식(=SUM(E2-C2)/C2)을 입력한다.
양수는 1월과 2월 사이에 판매 증가 백분율을 나타내고 음수는 판매 감소 백분율을 나타낸다.

 

ⓒ ITWorld


작업 또는 프로젝트 완료 백분율

프로젝트 관리 소프트웨어 프로그램에 돈을 쓰는 대신, 다음 수식들을 활용하면 지정된 간격에 각 프로젝트의 계획과 흐름을 완료 백분율로 관리할 수 있다.

1. A열에 여섯 개가량의 (진행 중인) 프로젝트를 입력한다.
2. B열과 C열에 각 프로젝트의 시작일과 종료일을 각각 입력한다.
3. (현재까지) 프로젝트 완료 상황을 파악하려면 완료일에서 시작일을 뺀다. D2에서부터 D7 까지 이 수식(=SUM(C2-B2))을 입력한다.
4. E열에 지금까지 완료된 일수를 입력한다. 이 열의 자료만 앞으로 변경하게 된다. 예를 들면, 매일(또는 매주) 한 번 이 스프레드시트에 접근하여 이 열의 자료를 수정하면 F열과 G열(남은 일수와 완료 백분율)에서 정확한 결론을 얻을 수 있다.
5. 각 프로젝트에 남은 일수를 구하려면 F2에서부터 F7까지 이 수식(=SUM(D2-E2))을 입력한다. 이 숫자들은 E열의 숫자 자료(완료 일수)에 따라 계속 변하게 된다.
6. 마지막으로, 이 수식(=SUM(E2/D2))을 입력하면 지금까지 완료된 작업/프로젝트 백분율을 구할 수 있다.  
 
ⓒ ITWorld


백분율 순위

국립공원과 산림관리 일자리에 지원자가 수십 명 있다고 생각해 보자. 이력서와 최초 면접을 보면 지원자들은 모두 동등하게 자격을 갖춘 듯하다. 그러나, 이력서에는 대개 명시되지 않은 최소한의 자격 요건을 충족해야 한다. 예를 들면, 개의 얼굴과 목에 박힌 호저 가시를 안전하게 뽑는 방법 같은 것들이다. 

해결 방법은 지원자들에게 기술 시험을 치르게 한 뒤 엑셀의 PERCENTRANK.EXC 함수를 이용해 각 지원자의 순위 백분위수를 구하는 것이다.

A2셀부터 A11셀까지 지원자 열명의 이름을 입력한다. B2셀부터 B11셀까지는 각 지원자의 기술 점수(10점에서 100점 사이)를 입력한 후 범위의 이름을 정한다.
B2셀부터 B11셀까지 선택한 후 강조 표시한다. [수식]>[이름 정의]>[이름 정의]를 선택한 후 [이름] 필드 상자에 점수라는 단어를 입력한다. [범위] 필드 상자 옆의 화살표를 클릭하고 목록에서 해당 시트의 이름을 선택하여 범위 위치를 확인한다. [참조 대상] 필드 상자에 강조표시/선택된 범위의 셀 주소가 포함된 것을 확인한다. 범위가 맞지 않을 경우, 오른쪽의 붉은 화살표를 클릭한 후 맞는 범위 주소를 입력하거나 맞는 셀을 다시 선택한 후 [확인]을 클릭한다.

참고: 수식이 작동하려면 반드시 ‘배열’의 범위를 정의하고 이름을 지정해야 한다.

다음으로 C2셀에 이 수식(=PERCENTRANK.EXC(점수,B2,2))을 입력한다. 여기에서 ‘점수’는 범위 이름과 같고 B2는 범위의 첫 번째 값이며 2는 소수점 2자리를 표시하라는 뜻이다.

수식을 복사하여 C11셀까지 붙여 넣는다. 목록 아래로 커서를 움직일 때 함수 중에서 셀 주소(B2, B3, B4 등)만 달라지는 것을 확인한다.

보기에 편하도록 소수의 서식을 백분율로 지정할 수 있다. 범위를 강조 표시한 후 [셀 서식] 대화상자에서 [백분율]을 선택하면 된다. 아니면 C열을 복사한 후 D열에서 [붙여넣기] > [특수] > [값]을 선택하여 문자를 복제한다. 단, B열의 값을 어느 하나라도 변경할 경우, 복사-붙여넣기-특수-값 단계를 반복해야 한다.

값들이 항상 업데이트되어 최신 상태가 되게 하려면, D2에 다음 수식(=VALUE(C2))을 입력한 후 복사하여 D11까지 붙여 넣는다. editor@itworld.co.kr

X