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

 

 

 

 

 

 

 

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

 

 

---

 

 

 

 

회사 동호회를 만들었다. 워낙 볼링을 좋아하기 때문에 만들자 만들자 했었는데, 퇴사가 겹쳐서 못 만들었다가 도저히 퇴사 결재를 해주지 않을 것 같아서 남기로 하면서 다시 동호회를 만들었다.

 

볼링 동호회에서 가장 중요한 것은 점수를 관리하는 것이라고 생각한다. 왜냐하면 정기전의 점수를 토대로 결산전 할 때 에버리지를 적용해줘야하기 때문이다. 정기전때야 점수가 어떻든 상관 없지만 결산전은 동일한 선상에서 게임을 즐기길 바라기 때문이다. 상품이 걸려있기 때문에.

 

가상으로 양식을 만들어 두었다. 해당 엑셀에는 전 경기 에버리지와 핸디, 그리고 각 정기전의 개별 점수와 에버리지가 존재한다. 정기전때는 3판을 기본으로 칠 것이기 때문에 총 3개의 게임을 입력할 수 있도록 작성해두었다. 그리고 매번 정기전에 순위를 정해서 아래에 표시하기로 했다.

 

 

 

정기전 점수 및 에버리지 (SUM)

 

아직 정기전을 하지는 않았지만 개별 점수를 입력하면 에버리지를 출력하는 함수를 사용하려 한다. 워낙 간단해서 뭐 크게 어려운 것은 없을 것 같다. sum 함수를 사용해서 개별 점수 셀을 더한 후 3으로 나누면 에버리지가 나온다. 셀을 고정으로 하지 않았기 때문에 드래그를 하면 모든 정기전 출전한 사람들의 평균치가 나온다.

 

=sum(개별점수 셀)/3

 

 

 

정기전 점수 랭킹 표시

 

랭킹 표시는 좀 쉬울줄 알았는데 생각보다 조금 복잡하다. 함수가 총 3개가 필요하다. INDEX, MATCH, LARGE이다.

 

 

• LARGE: 정해진 셀 안에서 몇 번째로 큰 숫자인지 출력해주는 함수 (에버리지가 제일 높은 숫자 찾기)
• MATCH: LARGE 함수에서 출력한 숫자가 어떤 위치에 있는지 알려주는 함수
• INDEX: MATCH에서 출력된 위치에 동일 선상에 있는 다른 행의 정보를 출력해주는 함수

 

제일 먼저 LARGE를 활용하여 AVERAGE의 숫자 중 제일 큰 숫자를 찾는다. LARGE 함수 뒤에 오는 숫자는 몇 번째로 높은 숫자를 찾는것인가 알려준다. 1이라고 하면 제일 높은 숫자, 2라고 하면 2등인 숫자라고 이해하면 된다.

 

=LARGE(행범위,1)

 

LARGE 함수로 제일 높은 숫자를 찾았다면 MATCH로 해당 행의 위치를 반환해야 한다. 그래야 동일 선상의 이름을 가져올 수 있기 때문이다. MATCH 제일 마지막에 들어가는 숫자는 0을 기본으로 사용한다. 보통 0으로 설정해야 우리가 아는 그 값이 출력될 것이다.

 

=MATCH(LARGE(점수범위,1), 점수범위, 0)

 

제일 높은 숫자의 위치를 파악했다면 INDEX 함수로 다른 것을 반환해야한다. 우리가 아는 이름을 반환해야 할 것이다. 함수를 풀어서 쓰면 에버리지 숫자 중 제일 큰 것을 찾아 (LARGE) 행 위치를 기억하고 (MATCH) 그 위치에 동일한 선상에 있는 이름을 출력(INDEX)해주세요. 라고 풀어쓴 것이다.

=INDEX(이름범위, MATCH(LARGE(점수범위,1), 점수범위, 0))
=INDEX($A$3:$A$17,MATCH(LARGE($M$3:$M$17,1),$M$3:$M$17,0))

 

추가적으로 LARGE의 숫자 1을 2,3으로 변경하면 AVERAGE의 순위를 포현할 수 있다.

 

 

정기전 점수 총 에버리지 및 핸디

 

보통 6개월 단위로 정기전에 대한 결산을 하기 때문에 총 6월까지 존재할 것이다. 한 번 만들어놓고 추가하지 않고 채워넣을 생각이기 때문에 빈 칸이 드문드문 보인다. 예를 들어 2022년 12월의 정기전은 아직 하지 않아서 빈 칸이라는 의미다. 

 

자고로 평균이라고 하면 전체를 더해서 더한 개수를 나누는 것이 평균인데 매 월 정기전을 치룰때마나 그 나누는 값을 수정해야하는 것이 여간 귀찮은 것이 아니다. 그래서 COUNTA 함수를 사용하기로 했다.

 

 

 

=SUM(E3,G3,I3,K3,M3)/COUNTA(E3,G3,I3,K3,M3)

 

 

 

COUNTA 함수는 말 그대로 셀 내부에 값이 포함되는 것들을 셈해준다. 결국 위의 그림처럼 12월과 11월 그리고 10월에는 에버리지가 존재하지 않기 때문에 당연히 셈을 하지 않는다. 결국 전체 에버리지에서 2만큼 나눠준 값을 얻을 수 있다. 10월에 에버리지를 입력하면 3으로 나눠줄 것이다.

 

 

=SUM($B$3:$B$17)/COUNTA($B$3:$B$17)-B3

 

핸디는 당연히 점수가 낮은 사람에게는 플러스인 점수이며, 에버리지가 높은 사람들에게는 마이너스를 해야한다. 그렇기 때문에 전체 점수의 평균에서 자신의 점수를 뺀 값을 책정한다. 이 값은 추후 정기전을 할 때 사람들간의 실력 격자를 최소한으로 줄이기 위한 방법이다.

 

 

 

아직 더 많이 수정을 봐야겠지만 그래도 첫 시작으로는 괜찮은 출발인 듯 하다. 8월부터 첫 정기전이 시작하는데 사무실 내부 인테리어 공사로 아마 재택을 할 듯 하다. 일정이 픽스가 되어야 뭐 변경 공지도 할텐데 언제 알려주련지 모르겠다. 빨리 정기전 하고 싶다.