[엑셀] 평균 볼링 점수 5의 배수 반올림과 특정 숫자 변환하기

 

 

엑셀 함수 5의 배수 반올림하기

 

 

해당 포스팅은 볼링 에버리지를 반올림과 내림, 그리고 올림하여 계산합니다.

추가적으로 5의 배수 발올림하는 방법도 포함합니다.

 

 

볼링에서의 에버리지 계산하기

 

2022년 하반기 결산전이 모두 마무리가 되었다. 사설 동호회 분들이 보면 아주 작고 귀여운 에버리지이다. 그래도 회사 돈으로 열심히 동호회 활동을 하고 있다. 한 번 하반기 결산전을 해보니까 숫자가 예쁘고 깔끔하게 떨어지지 않아서 계산하기 조금 불편하기도 하다.

 

그래서 이번에 에버리지 옆에 깔끔하여 반올림을 해서 5나 0 단위로 떨어지게 만들려고 한다. 그러면 플레이하는 사람도 계산하는 사람도 편할 것이라는 판단이다. 엑셀에 반올림과 내림, 올림을 통해 편한 것을 가져다 써보자.

 

작고 귀여운 볼링 에버리지

 

 

숫자 반올림 / 올림 / 내림 함수

 

숫자 반올림과 올림 내림 함수는 'ROUND' 함수를 기본으로 사용한다. 반올림은 1~4인 경우에는 일의 자리 숫자를 버리고 5~9의 경우에는 십의 자리를 올린다. 올림은 일의 자리 숫자와 상관없이 0으로 통일하고 십의 자리 숫자를 올린다. 내림은 반대로 일의 자리 숫자를 그냥 버린다. 그 근처의 숫자로 나타낸다는 의미로 round를 사용했을지 모른다. 올림은 'roundup', 내림은 'rounddown' 함수를 사용한다.

 

• 반올림 함수: =round(값, 소수점 이하 자리수)

반올림 함수는 round를 사용한다. 값은 내가 올림을 하고 싶은 숫자의 값을 의미하는 것이다. 소수점 이하 자리수는 원하는 자리수를 이야기 한다. 만약 볼링처럼 숫자가 정수로 끝나는 경우에는 소수점 이하 자리수를 '-1'로 설정하면 된다. 십의 자리를 기준으로 반올림을 하고 싶다면 '-2'로 설정하면 깔끔하게 끝이 난다.

 

round 함수

 

이렇게 되면 모든 점수가 반올림된 점수로 치환이 된다. 끝 자리는 모두 0으로 변경이 되며 계산하기 매우 쉽게 된다. 하지만 한 가지 문제가 있다. 예를들어 점수가 134인 사람과 135인 사람은 단순히 에버리지 1 차이일 뿐인데 반올림을 하게 되면 134 사람은 130이 되고 135인 사람을 140이 되어버린다. 비슷한 실력자인데도 불구하고 반올림의 차이 때문에 10이나 벌어지게 된다. 이렇게 해서는 안된다.

 

 

반올림, 올림, 내림 적용

 

그래서 round 함수를 사용해서 5와 0으로 반올림 할 수 도 있다. 밑에 힘든 버전이 있기는 하나 5나 0으로 반올림 하지 않을 경우에만 쓰는 방법이고 5이나 0으로 반올림 할 경우에는 round 함수와 약간의 수학적 지식을 더한 꼼수라고 할 수 있다.

 

round(에버리지/5,0)을 하여 소수점 첫 번째 자리에서 반올림을 합니다. 그 후 5를 곱하면 정확하게 5의 배수에서 반올림이 되는 것을 확인할 수 있습니다. 아래 공식을 대입하면 간단하게 할 수 있죠.

 

=round(에버리지/5,0) * 5

 

 

간단한 버전

 

 

 

특정 숫자 5와 0으로 끝자리 맞추기 (힘든 버전)

 

그래서 숫자를 조금 쪼개보기로 했다. 예를 들어 0은 0으로 설정, 1~5는 5로, 6~9는 반올림하기. 사실 말로 하는 것을 코딩이나 엑셀로 표현하는게 어렵지는 않다. 다만 내가 잘 모르는 분야고 모르는 함수를 배워가며 해야하는게 어려울 뿐이다. 그냥 내가 이야기 한 것을 풀어만 쓰면 된다.

 

모로 가면 서울로만 가면 되기에 취미로 코딩을 하거나 엑셀을 다루는 것을 좋아한다. 분명 필자보다 더 깔끔하게 하는 분들이 존재할 것이기 때문에 좋은 자료 있으면 댓글에 공유 부탁드립니다 :)

 

내가 생각하는 방식은 아래와 같다.

 

• 일의 자리를 판단하는 것은 쉽다. 예를 들어 132의 일의 자리만 보고 싶다면 사람은 쉽지만 기계한테는 설명하기 애매하다. 그래서 '버림 함수'를 써서 일의 자리를 구할 생각이다. 원래 숫자 - rounddown(원래숫자, -1)로 하면 일의 자리만 남게 될 것이다.

• IF 함수를 써서 세 개로 나눈다. 1~4까지는 5로 변환, 6~9까지는 10으로 변환, 0하고 5는 그대로 현상 유지하면 끝난다.
• 그 이후 남은 일의자리 숫자와 기존내 버림 함수를 썼던 값과 더하면 된다. 어렵지 않다.

 

편하게 볼 수 있도록 가능성 있는 모든 경우의 수를 다 넣어두었다. 0,5인경우 그리고 1~4 / 6~9의 경우까지 모두 넣었다. 원래 값에서 내림함수를 빼면 일의 자리만 남게 된다.

 

노가다 시작

 

그리고 IF문을 넣어서 말을 맞추면 된다. 일단 함수식을 적어두고 하나씩 설명해보도록 하자. 머리에서 막 거쳐서 내보내다싶이 하니 이게 될리가 만무하다.

* D37은 에버리지 원래 값을 뜻합니다. 변환하시면 됩니다.

 

=rounddown(D37,-1)+if(D37-rounddown(D37,-1)=0,0,if(D37-rounddown(D37,-1)<5,5,if(D37-rounddown(D37,-1)>5,10,D37-rounddown(D37,-1))))

 

rounddown(D37,-1)

• 에버리지의 원래 값을 내림 함수를 사용해서 1의 자리를 버린다.

 

if(D37-rounddown(D37,-1)=0,0, (참이 아닐경우))

• D37-rounddown(D37,-1)는 에버리지의 1의 자리수만 구하기 위해 원래 수에서 내림 함수를 사용한 수를 뺀 것입니다. 그러면 어떤 수여도 1의 자리만 남습니다.
• 1의 자리가 0과 같은 경우 값을 0으로 돌려줍니다.

if(D37-rounddown(D37,-1)<5,5, (참이 아닐경우))

• 1의 자리가 5보다 작은 경우는 5로 변환합니다. 0의 경우에도 참이 될 수 있으나 위에서 벌써 선행으로 걸러줬기 때문에 1~4의 경우에는 5로 바꿉니다.

if(D37-rounddown(D37,-1)>5,10, (참이 아닐경우))

• 1의 자리가 5보다 큰 경우에는 10으로 숫자를 변환합니다. 6~9의 경우 10으로 변경이 됩니다.
• 참이 아닐 경우는 원래 값을 돌려줍니다. 5와 0인 경우이니까 그냥 더해줘도 무방합니다.

최종적으로 에버리지에서 내린 값과 일의 자리 0 혹은 5를 더하면 원하는 값을 가질 수 있습니다. 0과 5가 아니더라도 변환하는 값에 맞춰서 사용할 수 있습니다. 웬만하면 하지 마세요. 고통스럽습니다.

 

최종으로 완료

 

 

 

추천 게시글

 

▶ 볼링 점수 중 가장 높은 숫자(최고점, 올타임하이) 색칠하기 - 조건부 서식 MAX

▶ [엑셀] 볼링 정기전 점수 계산표, 에버리지 / 핸디 자동 계산 및 순위 1등 이름 출력