[구글시트] 여러 시트의 학생 명단을 하나로 합치기

새 학년이 시작될 때

학생용 구글워크스페이스 계정을 새롭게 만드는 작업을 해야 합니다.

전체 학년 계정을 빠르고 쉽게 만들기 위해서

학급에서 만든 명단을 하나로 합치는 구글 시트를 만들어 보려고 합니다. 

 

(지극히 개인적인 취향의 규칙이니 각 학교 상황에 맞춰 함수는 변경하실 수 있습니다.) 

 

  1. 명단 작성 시트 틀 제작 

1) 학년/반, 번호, 이름을 바탕으로 만들 예정이라 해당 내용을 A~C열에 배치합니다. (빨강)

2) 사용자 일괄 업데이트를 위해 필요한 csv 파일 템플릿 내용을 그 옆에 배치합니다. (파랑)

(각 반에서 확인하기 위해 시트별로 구성했습니다. 분홍색 음영은 계정 생성 필수 요소입니다.)

*한글이 깨지는 경우가 많이 아래 csv 파일을 구글 시트로 작업하는 것이 좋습니다.

 

users.csv

0.00MB

 

 

  2. 데이터 서식 설정 

1) A열에서 학년 반을 적을 때 "1-3"이렇게 적으면 날짜로 인식되는 경우가 많습니다.

   물론 103으로 적어도 되지만 A열을 선택하여 

    서식 - 숫자 - 일반 텍스트를 선택하여 텍스트로 설정을 바꾸겠습니다. 

 

2) B열의 출석번호는 두 자리수로 통일하여 적기 위해서 똑같이 텍스트로 설정해도 되지만 

     서식 - 숫자 - 맞춤 숫자 형식 에서 "00"을 입력하여 두 자리수를 맞출 수도 있습니다. 

 

  3. 함수 입력 

 

1) 24년도 1학년 3반 1번을  2410301@schoolname.es.kr

    와 같은 규칙으로 만들 예정이라 D열에 함수를 아래와 같이 넣었습니다. 

=IF(C2<>"","24"&LEFT(A2)&"0"&RIGHT(A2)&TEXT(B2,"00"),"")

• If (조건, 참인 경우 값, 거짓인 경우 값) : 이름이 있으면 계정 아이디가 나오고, 아니면 빈칸으로 나옵니다.
• C2<>"" : C2값이 있는 경우인지 확인하는 조건문입니다.
• & : 문자를 합칠 때 사용합니다. (&대신 CONCATENATE 를 사용하여 만들 수도 있습니다.)
• LEFT(   ) : 문자열의 가장 왼쪽 값만 가져오기 
• RIGHT(   ) : 문자열의 가장 오른쪽 값만 가져오기 
• TEXT(B2, "00") : 숫자를 텍스트로 바꿔 두 자리수로 나오게 하는 함수인데 이미 서식에서 설정하여 B2값만 가지고 와도 됩니다.

= IF(C2<>"", CONCATENATE("24",LEFT(A2),"0",RIGHT(A2),B2), "")

 

 

2) 나머지 E~G까지의 함수는 다음과 같습니다. 

E열	F열	G열
=if(C2<>"",C2,)	=if(C2<>"",(D2&"@schoolname.es.kr"),)	=if(C2<>"","password12@",)

 

3) I열에 그룹설정을 위해서는 미리 구글 관리 콘솔 사용자 관리에서 조직단위를 확인해야 합니다. 

* 가장 최상위의 학교 이름인 조직명은 제외한 하위조직부터 작성해주어야 합니다.

(I열)  =IF(C2<>"","/학생그룹/2024-"&left(A2)&"학년",)

 

 

  4. 학급별 시트로 복제하기

각 학급에서 작성할 수 있도록 복제하여 시트를 구분하도록 하겠습니다. 

 

  5. 전체 데이터 하나로 합치기 

학년/반, 번호, 이름은 제외하고 구글 일괄 업로드에 필요한 데이터만 가져오기 위해

QUERY 함수를 이용하려고 합니다. 제목을 여러번 반복할 필요는 없기 때문에

미리 첫 행에 제목을 적고 D2부터 데이터를 불러옵니다.

 

학생 이름이 있는 경우에만 D열에 데이터가 누적되도록 하였기에

D열에 값이 있는 경우에만 D, E, F, G, I열의 데이터를 불러옵니다. 

나머지는 필수 데이터는 아니기 때문에 따로 불러오지 않습니다. 

A2에 넣는 함수는 아래와 같습니다.  (시트 이름을 학년반으로 넣으면 반복 작성할 때 편해요.)

={QUERY('1-1'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('1-2'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('2-1'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('2-2'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('3-1'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('3-2'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('4-1'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('4-2'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('5-1'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('5-2'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('6-1'!D2:I,"select D,E,F,G,H,I where D<>'' ",);QUERY('6-2'!D2:I,"select D,E,F,G,H,I where D<>'' ",);

 

  6. CSV 파일로 다운 받기 

이제 완성된 파일을 CSV 파일로 다운 받습니다.

그리고 관리콘솔에서 사용자 일괄 업데이트를 누르고 CSV 파일을 첨부합니다.

 

  글을 마치며 

구글 스프레드 시트에서는 내부에서 데이터를 가져올 때는 QUERY 함수를

외부에서 데이터를 가져올 때는 IMPORT 함수를 주로 이용합니다.

특히 QUERY 함수는 주어진 범위에서 데이터를 검색하고 조작하는 데 사용되므로 

이를 활용하면 데이터를 정렬하고 필터링하여 요약한 자료를 만들 수도 있습니다.