[벨운동회] 오늘만 열리는 개표톨의 구글 시트 특강!! 놓치지 마세요!!

때는 9월 6일... 16강 개표 첫 개시 때 이런 댓글이 달렸었고 저는 답변을 했죠

https://www.dmitory.com/novel/91179477

https://img.dmitory.com/img/201909/6hv/sjr/6hvsjrxqiQcsEQYsKQok0M.jpg

ㅎㅎㅎㅎㅎㅎ 그래서!!!! 

뒤풀이 회식에 열리는 구글 스프레드시트 특강! 벨운동회 편!!!!!

사실 이런거 처음 만들어봐서... 많이 헤맸고... 오류도 많았는데....에드맘 서주맘 내가 많이 미안해...ㅠㅠㅠㅠㅠㅠ

그리고 64강에는 만들 생각을 못해서서.... 주최톨에게 진짜진짜진짜 미안해ㅠㅠㅠㅠ 

어찌어찌 바로잡아서 8강부턴 완벽하게 했던듯...? 

각 경기마다 쓰읜 함수, 무효표 처리 방법, 그리고 명예의 전당 함수도 정리했어~~~

구글시트로 이케저케 할 수 있었던 가장 중요한 이유는 복붙이 가능했단 점 때문이야!!

요거만 알아주고 그럼 시작~

개표

1. 공통

- A~H열 댓글 복붙

- 1차 가공: =A4, =A7, A10 이렇게 3씩 건너뛰면서 A부터 H까지 (댓글 복붙하면 투표 내용이 2, 5, 8... 마다 있어)

2. 32강

→ 복병1: 띄어쓰기 유무, 복병2: 특수문자 섞이면 숫자로 인식x

- 2차 가공: 띄어쓰기 빼기

=SUBSTITUTE(K3, " ", "")

⇒ 1차가공칸[K3]에서 띄어쓰기를[" "] 띄어쓰기 없게[""] 바꿔줘[=SUBSTITUTE]

- 나누기: 모든 숫자와 특수기호를 텍스트로 바꿔서 글자마자 끊기

=REGEXEXTRACT(TO_TEXT(L3), REPT("(.)", LEN(L3)))

⇒ 2차가공칸[L3]을 텍스트로 바꾸고[TO_TEXT()] 모든 글자를["(.)"] 2차가공칸 글자 수대로[LEN(L3)] 반복해서[REPT()] 나눠줘[=REGEXEXTRACT]

2. 16강

→ 복병1: 이름 들어가기 시작, 복병2: 숫자와 기권표 띄어쓰기 유무, 복병3: 띄어쓰기 포함되었는데 @@ 붙어있을 때

- 2차 가공: 띄어쓰기 없이 숫자와 기권표로만 투표했을 경우 (i.e.. 1111, 11@2, @@@2) 띄어쓰기 넣어주기

=IF(LEN(K3)=4,REGEXREPLACE(TO_TEXT(K3),"(.)", "$1 "),K3)

⇒ 만약 1차가공칸[K3]의 길이가 4 [LEN(K3)=4] 일 경우[=IF()]

⇒⇒1차가공칸[K3]을 텍스트로 바꾸고[TO_TEXT()] 모든 글자["(.)"]에다가 한 글자마다[$1] 띄어쓰기를 붙여줘서["$1 "] 다시 넣어줘[REGEXREPLACE()]

⇒⇒아니면 1차가공칸[K3]을 반복해줘 

- 나누기: 띄어쓰기로 나누기

=IFERROR(SPLIT(L3," "))

⇒ 띄어쓰기[" "]마다 2차가공칸[L3]을 나눠줘[SPLIT()]. 그리고 정신사납지 않게 에러떠도 무시해[=IFERROR()]

- 수동으로 복병3: 4번째 열이 비어있다면 @@ 기권표가 붙어있단 뜻이니 손으로 띄어쓰기 넣어주기

⇒보기 편하게: 서식 >> 조건부 서식 >> P열 비어있으면 빨갛게 표시

3. 8강

→ 16강이랑 똑같다... 드디어ㅠㅠㅠ..... 재활용 고고고

4. 준결승

→ 준결승부터 이름과 기권표만!! 그래도 복병: 기권표와 이름이 띄어쓰기 없이 붙어있음

- 1차가공칸 = 2차가공칸

- 나누기: 띄어쓰기로 나누기

=IFERROR(SPLIT(L3," "))

⇒ 띄어쓰기[" "]마다 2차가공칸[L3]을 나눠줘[SPLIT()]

- 16강이랑 똑같이 수동으로 복병: 2번째 열이 비어있으면 기권표와 이름이 붙어있단 뜻

⇒보기 편하게: 서식 >> 조건부 서식 >> N열 비어있으면 빨갛게 표시

5. 무효표 거르기

→ @ 말고 다른 특수문자 기권표 유효, 오타 무효

⇒ 보기 편하게:  서식 >> 조건부 서식 >> 새로운 규칙 추가 >> 열에 해당되는 유효표(i.e. @, 숫자, 정확한 선수 이름)거나 빈칸이 아니면 빨갛게 표시. 

예: 16강 A조 1경기 [=NOT((M3="@")+(M3=1)+(M3=2)+(M3="문강")+(M3="강이준")+(M3=""))]

이렇게 하면 쉽게 무효표인지 오류인지 확인 가능 해

6. 통계

→ 나눠진 열에서 이름 또는 번호, 또는 그 합을 세기. 기권표 세고, 총 투표수로 합하기

- 이름 + 숫자 

=COUNTIF(M$3:M,R3)+COUNTIF(M$3:M,1)

⇒ 경기에 해당되는 열[M$3:M]에서 선수의 이름[R3]을 세고[=COUNTIF()] 또 그 열에서 선수의 번호[1]를 세서 더하기. 숫자만, 이름만, 또는 둘다 일 때에 따라 수식 바꿔주라!

- 기권

=COUNTIF(M$3:M,"@")

⇒  경기에 해당되는 열[M$3:M]에서 기권표["@"]를 세기[=COUNTIF()] 

- 총 투표수

=SUM(S3:S5)

⇒ 선수1, 2 그리고 기권표[S3:S5]의 합[=SUM()]

결승은 우리 보스 주최톨이 공정성 공평성 형평성을 위해 개표!!!

물론 나도 개표할 땐 댓글 창 다 띄어놓아서 한번에 새로고침 했어!!

흠흠 이상 개표수식 파헤치기는 끝~~

명예의 전당

1. 전체

→ 복붙 복붙 계속 복붙

2. 등록순 추천 20, 10, 3

→ 전체에서 20, 10, 3 이상인 글들만 모아서 정렬

=QUERY('전체'!A2:F2000, "SELECT * WHERE F >= 20 ORDER BY A")

⇒ 전체 시트에서 모든 내용중['전체'!A2:F2000], 추천수열[F]이 20이거나 그 이상이면[WHERE F >= 20] 전체 오를 선택해서[SELECT *] 번호열[A] 순서대로[ORDER BY A] 정렬해줘[=QUERY()]

10, 3 추천수 반복

2. 추천 높은 순

→ 추천 3 이상이면 추천 높은 순대로 글 정렬

=QUERY('전체'!A2:F2000, "SELECT * WHERE F > 2 ORDER BY F desc, A")

⇒ 전체 시트에서 모든 내용중['전체'!A2:F2000], 추천수열[F]이 2 이상이면[WHERE F > 2] 전체 오를 선택해서[SELECT *] 추천 내림차순, 그 다음 번호 오른차순으로[ORDER BY F desc, A] 정렬해줘[=QUERY()]

3. 검색

→ 노정 첫 검색은 최근 5000글까지만 검색 가능해서 (직접 세어봄) 그 이후에 연어질 할 땐 계속 검색하거나 딤토 전체 검색 돌려야 해!! 근데 이래도 안나올 경우가 있어서 구글 검색도 해봐야 해

- 노정 검색

=HYPERLINK("https://www.dmitory.com/?_filter=search&act=&vid=&mid=novel&category=&search_keyword=" & ENCODEURL(LEFT($C2,20)) & "&search_target=title", "노정")

⇒ 노정 검색 URL 형식을 보니 앞부분, 뒷부분으로 나눠져 있어. 글 제목[$C2]을 찾기 쉽게 첫 20글자로 자르고[LEFT($C2,20)] URL형식으로 바꾼 다음 [ENCODEURL()] & 을 이용해서 붙여넣었어. 그리고 링크 글자로 노정["노정"] 표시

- 딤토 검색

=HYPERLINK("https://www.dmitory.com/search?q=" & ENCODEURL(LEFT($C2,20)), "딤토")

⇒ 딤토 검색은 앞부분만 맞춰주면 돼. 노정검색이랑 똑같이 제목을 가공해주고 붙였어. 링크 글자로 딤토 표시

- 구글 검색 

=HYPERLINK("www.google.com/search?q=" & LEFT($C2, 40), "구글")

⇒ 구글 검색도 딤토 검색이랑 비슷해. 대신 제목이 길면 더 정확해서 40글자로 잘랐어. 링크 글자로 구글.

요렇게 하면 명예의 전당 완료~~~ 개표할 떄보다 수식가 적네

후하후하!!! 이제 정말 끝!!!!

모아보니까 별것 아닌것 같기도 하고.... 또 헤맨거 생각하니까 눈앞이 아찔해지고.....

주최톨 불판 깐다는 얘기에 급하게 적었는데 왜 벌써 2시간이 지났지...

불판 다 식었으면 어떡하지...?

만약 아직도 타오른다면 주전부리 챙겨서 신분세탁 짜란~ 하고 놀거야!!!

긴 글 읽어줘서 고마워~~~

뿅!