행아아빠의 내일은로또왕 / 내일은엑셀왕 구독, 좋아요 한번씩 부탁 드립니다.
행아아빠의 내일은로또왕 Youtube : https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA
▼행아아빠의 내일은로또왕 Youtube 후원 안내.
Youtube 채널 맴버십 후원 :https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join
안녕하세요. 행아아빠입니다.
이번 내용에서는 로또와 관련된 내용의 엑셀 서식으로 자신이 선택한 번호 조합 또는 자동 번호 조합이 과거 1등 당첨 번호 조합과 비교 했을때 중복되는지 여부를 체크할 수 있는 서식과 수식을 작성해 보고자 합니다.
로또를 구매하시는분들에게 활용될 수 있는 서식이 될 수 있기를 바라면서 엑셀 수식을 어떻게 활용하는지 전달 될 수 있기를 바라겠습니다.
1. 과거 로또 1등 당첨 내역 엑셀 작성과 편집.
과거 로또 1등 당첨 내역을 일일이 작성하기에는 시간이 오래 걸리고 오타의 가능성도 있기 때문에 직접 작성하지 마시고, 동행복권 홈페이지에서 과거 로또 1등 당첨 내역을 엑셀 원본으로 다운로드 해 주시면 됩니다.
동행복권 홈페이지로 이동 → 당첨결과 → 당첨내역 → 회차선택 (1회차~현재 진행된 회차까지) → 엑셀다운로드 순서로 과거 로또 1등 당첨 내역을 엑셀 파일로 다운로드 받으실 수 있습니다.
1-1. 과거 로또 1등 당첨 내역 엑셀 원본 파일 편집.
다운로드 받은 과거 로또 1등 당첨 내역중에서 필요 없는 부분의 행과 열은 삭제를 해 주고 통계에 사용할 부분만 남겨주도록 합니다.
남겨줘야 할 부분은 회차, 추첨일, 당첨결과만 남겨주도록 하고 나머지 부분은 삭제해 주도록 합니다.
날짜 형식은 0000.00.00으로 입력되어 있기 때문에 날짜 형식은 0000-00-00으로 변경해서 날짜로 인식할 수 있도록 표시형식을 변경해 주어야 합니다.
여기까지 다운로드 받은 과거 로또 1등 당첨 내역과 비교했을때 자신이 선택한 번호 조합 또는 자동 번호 조합이 과거 로또 1등 번호 조합과 중복되지를 체크할 수 있는 비교 지표 데이터를 일일이 작성하지 않고 간단하게 완성 시킬 수 있습니다.
다만 다운로드한 엑셀 원본 파일의 기본 저장 형식은 웹페이지 형식으로 다운로드 되기 때문에 통합문서로 다시 한번 저장해 주도록 합니다.
2. 선택한 번호 조합 또는 자동 번호 조합과 과거 1등 당첨 조합 중복 비교 수식 작성.
과거 1등 당첨 번호 조합 데이터를 작성했기 때문에 여러분들이 선택한 번호 조합 또는 자동 번호 조합이 과거 1등 당첨 번호 조합과 비교 했을때 1,2,3등 번호 조합과 중복되는 번호 조합인지 아닌지를 확인할 수 있는 수식을 작성해서 체크해 볼 수 있습니다.
중복 여부를 체크하기 위해서는 선택한 번호 조합이 1번 이상 이미 과거에 1,2,3등 번호 조합으로 나왔던 번호 조합이라면 중복되는 번호 조합으로 체크할 수 있습니다.
따라서 다중 조건의 개수를 카운트 하는 함수 countifs함수로 수식을 작성하면 선택한 번호 조합이 과거 1등 번호 조합과 비교했을때 몇번 나왔던 번호 조합인지 확인해 볼 수 있습니다.
여러분들이 선택해서 조합한 로또 6수 조합 번호를 K열부터 P열까지 입력했을때 Q열에서 과거 로또 1등 당첨 번호와 동일한 6수 조합이 몇개나 되는지 계산하는 수식을 작성해 주게 되면 중복 여부를 체크해 줄 수 있습니다.
2-1. 로또 1등 6수 조합 중복 체크 수식.
Q열에 수식은 다음과 같이 Q3셀에 작성하고 계산이 필요한 행까지 수식을 복사해 주시면 됩니다.
| =COUNTIFS($C$3:$C$1052,$K3, $D$3:$D$1052,$L3, $E$3:$E$1052,$M3, $F$3:$F$1052,$N3, $G$3:$G$1052,$O3, $H$3:$H$1052,$P3) |
수식은 countifs(조건 범위, 조건, 조건범위, 조건)으로 작성해면 되는데, 6수를 각 자리에서 출현한 번호가 있는지 개수를 카운트 하는것이기 때문에 첫째자리부터 여섯번째 자리까지 입력된 번호들을 과거 당첨 번호 내역의 첫번째 자리 당첨 번호 범위부터 여섯번째 자리 당첨 번호 범위까지로해서 여러분이 선택한 번호 조합이 과거 1등 당첨 번호와 중복되는 번호가 있는지 체크해 볼 수 있습니다.
2-2. 로또 2,3등 5수 조합 중복 체크 수식.
6개 번호로 5개 번호를 조합하기 위한 경우의 수는 6개입니다.
5수 번호 조합은 12345, 12346, 12356, 12456, 13456, 23456으로 6개 번호를 5수 조합으로 나눠볼 수 있습니다.
여러분이 선택한 6수 조합이 5수조합으로 자동으로 계산 될 수 있도록 각 자리에 맞는 번호가 입력 될 수 있도록 각 자리에 수식을 입력해 주도록 합니다. 다만, 서식이 지저분해지는것을 방지 하기 위해서 6수 조합 번호가 입력되지 않고 공백이라면 공백으로 표기 될 수 있도록 if함수로 각 자리에 맞는 셀 주소를 참조해 주도록 합니다.
6수 조합에 각 자리에 입력된 번호없이 공란이라면 공란으로 결과값을 반환하는 수식은 다음과 같이 S3셀을 기준으로 작성하고 각 자리를 참조하는 셀 주소를 변경해서 5수 조합 모두 수식을 입력해 주도록 합니다.
| =IF(K3="","",K3) |
여기까지 수식을 완성시켜 주게 되면 여러분이 6수 조합에 입력한 번호를 기준으로 6가지 경우의수에 해당하는 5수조합으로 나눠지게 되어 자동으로 표기 해주는 수식이 완성됩니다.
그럼 이제 5수 조합이 과거 로또 1등 당첨 내역과 비교했을때 중복되는 5수 조합 여부를 카운트 해주는 수식을 작성해 주면됩니다. 수식은 다음과 같이 X3셀을 기준으로 작성하고 5수 조합의 각 자리에 맞는 참조 범위맞 수정하여 출현 회수를 체크하는 셀에 수식을 복사하고 수식 계산이 필요한 행까지 복사 해주시면 됩니다.
| =COUNTIFS($C$3:$C$1052,$S3, $D$3:$D$1052,$T3, $E$3:$E$1052,$U3, $F$3:$F$1052,$V3, $G$3:$G$1052,$W3) |
여기까지 작성을 완료했다면 이제 여러분이 선택한 6수 조합을 K열부터 P열에 번호를 작은수부터 입력해 보게 되면 6수 조합이 과거 로또 1등 당첨 조합과 동일한 조합 여부를 확인하면서 3등 조합인 5수 조합까지 자동으로 확인할 수 있는 수식을 완성 시킬 수 있습니다.
이제 회차가 추가 되면서 출현하는 1등 번호 조합을 A열부터 I열까지 회차, 날짜 당첨번호를 직접 입력해 주고, 6수와 5수 조합이 과거 당첨 내역과 중복되는 조합인지 여부를 체크하는 범위를 수정해 주게 되면 됩니다.
이때 과거 로또 1등 당첨 번호 조합의 순서가 내림차순으로 정렬되어 있기 때문에 다음 회차를 맨 아래셀에 작성해 주게 되면 회차 순서별로 보기 힘들기 때문에 A열부터 I열을 오름차순으로 변경해 주도록 합니다.
3. 범위를 수정할 필요가 없는 동적 범위로 과거 로또 1등 당첨 번호 조합과 중복되는 수식 작성.
지금까지 작성한 수식은 countifs함수의 기본적인 수식작성 방법으로 범위를 직접 지정해주어 작성했기 때문에 비교 범위가 추가 된다면 범위를 일일이 모두 추가해 줘야 하기 때문에 매주 당첨 결과가 나오고 나면 범위를 모두 수정해 줘야 하는 번거로움이 발생하게 됩니다.
일단 A1부터 i1의 범위를 이번 회차와 회차를 입력할 수 있는 셀로 편집해 주고, f1셀에 이번 회차를 입력해 주도록 합니다.
이번 회차가 1051회차라면 참조할 범위의 행은 1050행을 참조하는 범위로 설정해야 하기 때문에 여러분이 이번 회차를 f1셀에 입력하게 되면 참조할 범위를 직접 수정하지 않고 자동으로 범위를 설정해 주는 수식을 작성할 수 있습니다.
따라서 범위가 추가된 행과 열까지 자동으로 변경되서 참조할 수 있는 수식을 작성하면 다음과 같이 각 기준셀에 수식을 작성하고 수식으로 계산되어야 할 행과 열에 각각의 수식을 복사해서 사용할 수 있습니다.
| 1. Q3셀 6수 조합 중복 개수 계산 수식. =COUNTIFS(OFFSET(INDEX($C$2:$H$2,MATCH($K$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$K3, OFFSET(INDEX($C$2:$H$2,MATCH($L$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$L3, OFFSET(INDEX($C$2:$H$2,MATCH($M$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$M3, OFFSET(INDEX($C$2:$H$2,MATCH($N$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$N3, OFFSET(INDEX($C$2:$H$2,MATCH($O$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$O3, OFFSET(INDEX($C$2:$H$2,MATCH($P$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$P3) 2. X3셀 5수 조합 중복 개수 계산 수식. =COUNTIFS(OFFSET(INDEX($C$2:$H$2,MATCH($S$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$S3, OFFSET(INDEX($C$2:$H$2,MATCH($T$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$T3, OFFSET(INDEX($C$2:$H$2,MATCH($U$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$U3, OFFSET(INDEX($C$2:$H$2,MATCH($V$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$V3, OFFSET(INDEX($C$2:$H$2,MATCH($W$2,$C$2:$H$2,0)),1,0,$F$1-1,1),$W3) ※ AE3셀, AL3셀, AS3셀, AZ3셀, BG3셀은 X3셀의 수식을 각 참조 범위 영역에서 찾을 조건의 셀 주소만 수정해주고 수식으로 계산이 필요한 셀까지 수식을 복사해서 사용할 수 있습니다. |
여러분들이 선택한 번호로 조합한 6수, 5수 조합으로 과거 로또 당첨 내역중 1,2,3등 번호 조합과 동일한 조합이 중복되는지를 확인할 수 있는 엑셀 서식을 작성해 보았습니다.
해당 엑셀 서식이 필요하신분은 아래 첨부파일을 다운로드하여 로또 당첨 번호를 조합하는데 활용해 보시기를 바라겠습니다.
'엑셀' 카테고리의 다른 글
| 엑셀 기초 배우기 1편 - 엑셀 문서 구조 이해와 시작하기. (0) | 2023.02.09 |
|---|---|
| [엑셀] 여러 시트의 조건 합계, 조건 개수, 시간대별 조건에 따라 결과값을 계산 하는 예제와 수식. (1) | 2023.02.04 |
| 엑셀 BOM 관리 가능한 생산관리 생산일보 서식과 자동 계산 수식 작성 방법. (0) | 2022.12.08 |
| 2023년 엑셀 급여 계산 서식과 수식 작성. (1) | 2022.12.06 |
| 엑셀 가계부 자동 계산 완성 서식 만드는 방법. (1) | 2022.11.29 |
댓글