검은사막 리마스터

커뮤니티

팁 & 노하우
#기타
구글 시트를 활용한 길드 시트 운영법과 사용한 함수 간단 정리
2022.01.20 13:40
513 2
최근 수정 일시 : 2022.01.20 14:51

 

안녕하세요, Sin 길드 시트 관리자 격살이라고 합니다.

 

재작년 한 해 검은사막을 접었다가 어쩌다 보니 Sin으로 되돌아오게 되었는데, 온 지 반 년쯤 되었을 때 얼렁뚱땅 시트 관리자가 됐고 길드 규모가 커지면서 수기 운영에 한계를 느껴 조금씩 시트를 발전시키다 보니 여기까지 오게 되어 조금이라도 팁을 공유하고자 글 써봅니당. 이런 게 있으면 좋을텐데, 하던 것을 혼자 공부해 이뤄낸 거라 뿌듯하기도 하구요 ㅎㅎ

글은 다시 쓰기 귀찮아서 블로그에서 긁어온 거라 조금 성의없을 수 있지만 덧글 달아주시면 친절하게 알려드리겠습니다.

전 이런 통계?문서작업? 같은 걸 잘 몰라서 구글링만으로 모든 함수를 짰는데 혹시 제가 함수를 너무 꼬아서 어렵게 쓰고 있다면 조언해주시면 감사하겠습니다!

검은사막의 모든 시트관리자 분들께 도움 되었으면 하는 바람입니다!

다시 읽어보니 잘 모르시는 분들은 외계어처럼 느껴지실 수 있겠지만 ㅠㅠ 한 번 틀을 짜두면 정말 편하니까요... 꼭 한 번 공부해보시기를 추천드립니다... 물론 재미도 있구요!

 

그리고 질문은 감사히 받겠으나 죄송하지만 시트 원본 공유는 보안상 어렵다는 점 미리 말씀드립니다!

 

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

나는 길드 시트 관리자다. 시트 관리를 막 시작했을 당시엔 엑셀을 단 하나도 할 줄 몰랐음. sum이나 if 함수도 다룰 줄 모를 정도로 엑셀에 무지했다. 그런데 길드원이 늘어나고 하나하나 수기로 썼다 지웠다 하는 것에 한계를 느끼며 몸이 슬슬 고생하기 시작하자 생존본능처럼 엑셀을 독학하기 시작했는데 처음엔 브이룩업도 무진장 막막해서 한참을 헤맸는데 하면 할수록 함수 오류 없이 잘 굴러가서 원하던 데이터가 좌라락 출력되면 그 쾌감이 장난아님.

요약을 하자면 이렇지만 길드시트 제작이 다사다난하면서도 재밌었던 만큼 시트 제작 관련 팁 및 유용한 함수 요약해봄. 요약이 될지는 모르겠지만..

 

1. 시트의 목적과 용도 명확히 하기

시트를 제작하면서 타 길드의 시트도 많이 참고하고 실제 업무에 사용되는, 게임과는 전혀 무관한 인원 관리용 시트들을 수없이 보고 또 봤다. 사람이 드나드는 어떤 단체의 목록을 표로 관리한다는 건 생각보다 단순하지 않고 그 그룹의 정체성과 그룹의 목적에 따라 수없이 많은 방식으로 표현될 수 있다.

시트를 만들고 관리하는 사람은 그 누구보다도 그 그룹을 잘 파악하고 있어야

시트의 방향성과 목적을 잃지 않고 시트를 발전시킬 수 있다.

진짜 공부잘하는 새끼들 기본에 충실해라 교과서 위주로 공부해라는 말 제일 싫어하는데 내가 그 소리 하고있네... 그런데 진짜 각종 함수들로 실험적인 시트들을 수없이 만들었다 지웠다 하면서 느낀 점들을 요약하면 위의 한 줄로 표현된다. 내 길드가 초식인지 육식인지, 길드의 규칙은 어떻게 되는지, 시트를 보는 나를 포함한 다른 사람들이 어떤 정보를 원하는지, 그 다른 사람들에는 길드원 전체가 포함되는지 일부 간부진에 한정되는지 등등 각자 생각하기 나름. 정말 제대로 각잡고 만들려면, 특히 거점 공성 위주의 육식 길드는 고려해야 할 점이 더 많다.

내 경우에 시트는

- 거점, 공성전 및 쟁을 하는 육식 길드

- 인원 입출, 스펙 조사, 인센티브 단계 관리 및 거점전 데이터 정리용

- 주요 간부진만 접근 가능

이 핵심 줄기였고 이에 따라 나누고 합칠 데이터를 구분

2. 시트의 목적과 용도에 따라 데이터 분리 및 참조 설계하기

처음에는 모든 데이터가 담긴 시트를 하나 두고 그 데이터를 또 분리하여 다른 시트들이 원본 시트를 참조하게도 만들어 봤었는데, 그 경우 참조하는 다른 시트에서 수기로 입력해야 할 변수가 생겼을 경우 수정이 어렵고 결과적으로는 함수를 모르는 다른 사람들이 쓰기 힘들게 만들어진다. 웬만하면 목적별로 데이터를 분리하여 시트 간 데이터 참조는 최소화하는 것이 좋다.

- 목적별로 데이터를 분리

1. 길드원의 기본 정보(가문명, 직업, 공방합, 가입일, 특이사항 등)

2. 인센티브 내역(고정인센, 추가인센,거점인센,거점풀참인센)

3. 인센에 반영되는 체크시트(거점출석부같은거)

4. 거점전 누적 데이터(체크시트의 데이터를 복붙하여 누적)

내 경우는 1번 데이터를 기준으로 삼아 1번을 수기로 입력하면 2 3 4번이 1번의 가문명, 캐릭터명을 참조, 2번이 3번의 일자별 출석 목록 및 추가인센자 목록 참조 정도로 시트 간 데이터 참조를 최소화했다.

3. 함수 짜기

이제 골 싸매가며 1, 2번을 했다면 더 골아프면서 재미있는 함수 설계만 하면 시트는 완성이다.

1) 길드원 기본 정보

모든 데이터의 기본이 되는 1번 데이터, 인원 관리 데이터이다. 개개인마다 고유번호 - 가입일 - 탈퇴일 - 가문명 - 특이사항을 수기입력하고 나머지 정보를 구글시트 스펙조사를 통해 수집한다. 이후 VLOOKUP 함수로 인원 관리 데이터의 가문명을 참조하여 나머지 정보를 끌어온다. 전이캐릭터는 부대 편성 시 필요하다고 요청하여 추가하였다. 추가적으로 VLOOKUP을 통해 설문참여 여부도 체크했다.

내 시트의 경우 탈퇴인원을 날리지 않고 탈퇴일자 입력 후 숨기기만 하기 때문에 COUNT 함수가 아닌 SUBTOTAL로 현재 전체 인원수를 체크하였다. 탈퇴한 인원은 재가입하면 새로운 고유번호를 다시 부여받는다.

열별로 데이터를 가져오는 VLOOKUP

=IFERROR(vlookup($가문명,'조사 시트'!$참조할 데이터 전체,참조할 데이터 중 해당되는 열의 숫자,), )

설문 참여 여부 체크하는 VLOOKUP

=IFERROR(IF($가문명=vlookup($가문명,'조사 시트'!$가문명이 있는 열,1,),"O",),)

현재 인원수를 체크하는 SUBTOTAL

=SUBTOTAL(102,고유번호 셀 전체)&"명"

 

사실상 가장 근본이 되는 스펙조사 구글 시트. 인원이 많으면 많을수록 더더욱 구글 시트를 잘 활용하여 시트 운영하시길 바람. 몸과 마음 모두가 편하다. 질문들은 길드마다 길원에게 원하는 정보가 서로 다르므로 참고만 하세요. 대략적으로 눈으로 인원을 파악해야 하는 데이터가 있을 경우 조건부 서식을 걸어두기도 했다. 이건 유동적으로 시트 관리자가 알아서.

고유번호를 부여하는 이유는 다른 필터로 데이터를 재정렬 시 서로 데이터가 섞이는 대참사를 방지하기 위해서+인센 목록이 인원 목록을 참조할 때 조금이라도 더 수고를 덜기 위해서이다. 내 경우에 고유번호는 가입일자순으로 부여하였고 오더나 길마, 부길마 등 간부진들은 수작업으로 고유번호를 변경하여 상위에 두었다.

인원 관리 데이터에는 VLOOKUP 말고 다른 중요한 함수가 딱히 없음. 구글 시트로 원본 데이터만 잘 살아있으면 다른 건 다 몰라도 VLOOKUP 하나면 편하게 시트 운영 가능합니다.

2) 인센티브 내역

가장 참조하는 데이터가 많은 시트. 인센에 반영되는 항목이 많기 때문에 항목별로 그룹화를 했는데 얼마나 많냐면...

 

이 모든 것이 인센에 반영된다. 해당 목록 중 나침반, 추가인센(오우거/트롤, 순블, 벨운전)을 제외한 모든 항목은 수기다. 나머지도 참조 형식으로 바꿀지 말지 생각중이다. 상단의 두 행에 각 항목별 추가점수와 해당 점수를 받는 인원수를 기입해두었고 이 역시 COUNT가 아닌 SUBTOTAL로 체크된다. 거점전 참여 독려를 위해 풀참여 시 추가인센이 지급되는데, 이 경우도 풀참여 시 횟수와 실제 참여횟수를 기입하여 이와 같을 경우 점수가 지급되는 함수를 쓰고 있다.

기본적으로 인원 목록은 고유번호만 써도 VLOOKUP을 통해 해당 인원의 1번 데이터를 모두 참조한다. 물론 함수 복사하는거 잊지 마시고.

 

나침반, 추가인센(오우거/트롤, 순블, 벨운전), 중범선 항목은 인원의 입출 및 인센 적용 시 수시로 추가 및 삭제, 변경되기 때문에 따로 해당되는 인원 목록이 기재된 체크시트에서 참조해온다. 이 항목 중 추가만 되고 더 빠지거나 삭제되지 않는 나침반, 중범선 보유 인원은 설문 시트를 참조해온다.

데이터를 수기로 입력하는 항목의 인원 체크(추가인센-오우거/트롤,순블,벨운전)

=IFERROR(IFERROR(IF(MATCH($참조할 데이터(가문명), '체크시트'!$데이터가 있는 열, 0),"O", ),IF(MATCH($참조할 데이터(캐릭명),'체크시트'!데이터가 있는 열, 0),"O", )),)

설문 시트에서 데이터를 체크하여 해당되는 인원 목록을 자동으로 업데이트(고정인센-나침반,중범선)

=IFERROR(INDEX('설문 시트'!$가문명 열,SMALL(IF('설문 시트'!$필요한 데이터 열<>"(해당되지 않는 인원이 체크하는 문자 또는 숫자)",ROW('설문 시트'!가문명 열)),ROW()-2)), )

두 번째 함수로 데이터를 뽑았을 경우 다시 인센티브 시트로 돌아가서 첫 번째 함수로 두번째 함수를 참조하면 인원 체크가 가능하다. 오우거 트롤은 '보유 인원 / 보유한 개수' 두 데이터를 한번에 나열한 거라 함수가 많이 복잡하고 그리 쓸모있는 함수가 아니라서 패스.

INDEX/MATCH와 SMALL/ROW 때문에 함수가 엄청 정신없어 보이는데 이 네 개의 함수는 이후 시트 연구할 때 정말정말 오지게 우려먹을 수 있기 때문에 꼭 한 번 공부하는 것을 추천.

MATCH(참조값,참조하는 범위,0)

> 참조값이 참조하는 범위에서 몇 번째에 있는지 계산(결과값이 숫자)

INDEX(범위, 행번호, [열번호-안적어도됨])

> 범위 내의 행번호(MATCH 함수가 주로 들어감) 위치에 있는 값

SMALL(범위,순번)

> 범위 내에서 N번째로 작은 값(특정 데이터를 필터하여 나열할 때 주로 씀)

ROW(셀)

> 해당 셀의 행번호(결과값이 숫자)

점점 함수 길어지죠? 이제 슬슬 보기 지겹죠? 뭔 말인지 모르겠죠? 나만 진심이죠?

ㅋㅋ

​​

내 기준 내 인센티브 시트의 꽃이라고 할 수 있는 우측의 출석부 시트 함수. 모든 시트 관리자들이 골치아파하고 귀찮아하는 부분 중 대부분의 것을 차지하는 것일 건데, 나도 원래는 하나하나 이름을 찾아서 표시했지만 이 함수 하나라면 해당 일자의 목록만 거점 후에 캡쳐해 체크시트에 쭉 적어내려가주면 바로 인원 체크가 가능하다. 다만 약간의 단점이라면 1. 오타가 없어야 하고 2. 중복인원이 없어야 정확한 인원 체크가 가능하다는 거? 하지만 이것들도 조건부 서식으로 해결할 수 있긴 하다.

해당 날짜에 참여한 인원 체크하기(3행의 날짜도 체크시트에서 참조해오면 편함)

=IFERROR(IFERROR(IF(MATCH($가문명, '체크시트'!하나의 열, 0),"O", ),IF(MATCH($캐릭명,'체크시트'!하나의 열, 0),"O", )),)


위의 함수가 참조하는 체크시트는 다음과 같다. 1행은 가끔 있는 버콜쟁시 버콜/거점 여부 체크하려고 만들어둔 행. 버콜 이라고 쓰면 빨개지는 조건부 서식을 걸어두었다. 별 쓸모는 없고 그냥 기분이 좀 급박하라고

4행부터 아래로 당일 참여인원을 쭉 써내려가면 위의 함수가 참조하는 열('체크시트'!하나의 열)의 데이터와 함수가 있는 행의 가문명/캐릭명 정보를 대조하여 데이터가 있으면 O, 아니면 공란으로 비워둔다.

다른 건 몰라도 시트 관리자들이 이 함수는 꼭 써봤으면 좋겠다. 정말 편함.

3) 체크시트

체크시트는 수기항목이 대부분이고 나머지들도 다 위에서 설명했으니 패쓰.

4) 거점 데이터

 


사실 만든 지 며칠 안된 따끈한 시트. 인센지급이 끝나면 주기적으로 삭제되는 체크시트의 데이터가 뭔가 아깝고 누적시키면 뭔가 나올 것 같은데 능력이 안 따라줘서 굉장히 오래 고민한 시트이다. 체크시트의 데이터만 수작업으로 옆으로 계속 복붙하면 자동으로 누적되고 시작날짜와 종료날짜, 가문명만 설정해주면 자동으로 그 기간 내 해당 인원의 참석률과 참석 횟수가 검색되는 시트이다. 설정된 날짜는 왼쪽의 참석률 저조자 목록에도 적용되어 수시로 바뀐다. 참석률 저조자 목록 함수를 작성할 때 엄청 골을 썩였는데, 이 목록 한 줄을 뽑기 위해 데이터가 무려 세 줄이 필요했기 때문에...

체크시트에는 탈퇴인원까지 모두 체크되기 때문에 1.(일정 참석률 이하의 인원)을 필터링하면 탈퇴 인원까지 같이 잡힌다. 그래서 2.(탈퇴 인원 목록)을 또 만들어서 리스트업하고, (1) - (2)를 한 3번 목록을 만든다. 3번 목록에서 FALSE값(탈퇴한 인원)을 SMALL함수로 제외한 목록을 결과적으로 저조자 목록에 반영시키는 방식인데, 3번에서 최종목록으로 바로 넘어갈 수 있을 듯 말 듯 한데 더 하다간 때려칠 것 같아서 그냥 데이터 한 줄 더 만드는 걸로 타협봤다^^...

사실 기간을 설정하는 동적 함수도 꽤 오래 고민했는데, 특정 날짜가 위치한 열번호를 어떻게 뽑아서 어떻게 범위를 먹일지에 대한 고민이 꽤 컸다. 어렵기도 어려웠고. 결과적으로 난 해냈지만ㅎ

함수의 순서는 이렇다

<특정 인원의 특정 기간 내 참석률/참석횟수 조회>

1. HLOOKUP으로 날짜행에서 해당 날짜가 위치한 열을 찾는다.

2. MATCH로 해당 열의 열번호(숫자)를 반환한다.

=MATCH(HLOOKUP($날짜,$데이터시작:$데이터끝,1,True),$데이터시작:$데이터끝,1)

3. OFFSET으로 시작날짜의 열에서 시작해 (끝날-시작날+1)칸만큼 옆으로, 100칸만큼 밑으로 범위를 설정하고

4. COUNTIF로 해당 범위에 입력한 가문명의 개수를 센다.

=COUNTIF(OFFSET($첫데이터한줄왼쪽첫칸,,INDIRECT("시작날짜열번호가있는셀"),100,INDIRECT("끝날짜열번호가있는셀")-INDIRECT("시작날짜열번호가있는셀")+1),$가문명검색셀)

5. 횟수를 퍼센테이지로 바꾸고 싶다면 뒤에 이거만 붙여주면 됨. 붙여주고 소수점, 이상한 숫자가 나온다면 서식-숫자-퍼센트로 바꾸기.

/($끝날짜열번호있는셀-$시작날짜열번호있는셀+1)

6. 이제 시작 날짜 / 끝 날짜 / 가문명만 설정해주면 누구든지 참석률 조회 간응삽간응

<특정 기간 내 참석횟수 저조 인원 조회>

1. 위의 3,4번 함수가 일정 퍼센테이지 아래(내 경우 30%이하)인 사람을 길드원 기본 정보 목록에서 조회한다.

2. UNIQUE 함수로 30%초과(IF 구문에서 FALSE가 뜨는 인원)인 사람들을 걸러준다.

=UNIQUE(IFERROR(IF(COUNTIF(OFFSET($첫데이터한줄왼쪽첫칸,,INDIRECT("시작날짜열번호가있는셀"),100,INDIRECT("끝날짜열번호가있는셀")-INDIRECT("시작날짜열번호가있는셀")+1),'길드원 기본 정보'!가문명열)/($끝날자열번호있는셀-$시작날짜열번호있는셀+1)<=0.3,'길드원 기본 정보'!가문명열)))

3. 탈퇴일자가 공백이 아닌(현재 탈퇴한) 인원의 가문명을 필터한다.

4. 역시 UNIQUE 함수로 FALSE 인원을 걸러준다.

=UNIQUE(IFERROR(IF('길드원 기본 정보'!$탈퇴일자열<>"",INDEX('길드원 기본 정보'!$가문명열,INDIRECT('길드원 기본 정보'!$탈퇴일자열),1)),'길드원 기본 정보'!$가문명열),"false")

5. 2번과 4번 함수를 통해 나온 결과값을 대조하여 4번함수에 있지 않은 인원을 걸러준다.

6. 4번 함수에 포함된 인원들을 FALSE로 표시한다.

=IF(ISERROR(MATCH($2번함수열,$4번함수열,0)),$2번함수열,"false")

7. 6번함수열에서 FALSE를 찾아 열번호를 매기고 해당 열번호가 아닌 함수들을 작은 숫자들부터 나열한다.

(그냥 6번함수에서 FALSE만 거르고 다시 나열했다는 말임)

=INDEX(6번함수열, SMALL(IF(NOT(ISNUMBER(SEARCH("false",$6번함수열))), ROW(A:A)), ROW()))

4. 후기

엑셀 짤 땐 하나도 안 힘들고 너무너무 재밌었는데 글 쓰고 나니까 너무 힘들어...내가 뭔짓했나 싶고 현타가 찾아온당...... 내가 쓰고도 이해를 못함

어차피 아무도 안볼 거 같지만 뭔가 여태 한 뻘짓의 여정을 좀 정리할 필요가 있다고 느꼈음

사실 이 시트 그래프랑 많이 쓰인 함수들도 좀 자세히 말할랬는데 귀찮아 안할랭...

그리고 함수 그대로 따라해도 안 되면 컨+슆+엔터(배열수식에 한해서만이긴 한데 설명하기 귀찮고 찾아보세요, 배열수식 작동시키는 키임) 눌러보시고 그래도 안되면 님이 잘못 쓴거임 노오력하셈

넘모 힘들고 긴 여정이었다 이러고 또 얼마 안가서 재밌게 생긴 함수 보면 또 침 질질 흘리면서 시트짜고있을 거 같지만 ㅎ

엑셀 넘모 잼미따 다들 엑셀의 세계로~