구글 스프레드시트] 효율적인 데이터 작업을 위한 3가지 기능 | 틀 고정, 중복 데이터 삭제, 조건부 서식
효율적인 데이터 작업을 위해서는 데이터 관리와 분석을 돕는 다양한 도구들을 효과적으로 활용할 수 있어야 합니다. 특히 방대한 데이터의 화면을 스크롤 하더라도 항상 제목행이 표시되도록 하는 틀 고정과 중복된 항목을 제거하는 데이터 정리 기능, 그리고 특정 데이터를 강조하기 위해 시각화하는 조건부 서식을 알아두면 작업의 효율을 높일 수 있습니다.
실습 파일 다운로드
틀 고정
방대한 데이터를 아래로 스크롤 하면 제목행이 화면에 보이지 않아 불편한데, 이럴 때 제목행을 고정할 수 있습니다.
1. 제목행 고정
현재 데이터에서 1행에서 4행까지 화면에 항상 보이도록 설정하려면 4행에 있는 임의의 셀을 선택하고 [보기] - [고정] - [4행까지]를 선택합니다.
화면을 아래로 스크롤 해보면 4행까지 고정된 것을 알 수 있습니다.
2. 틀 고정 해제
고정을 해제하려면 [보기] - [고정] - [행 고정 없음]을 선택하면 됩니다.
3. 열 고정
현재 데이터는 열을 고정하지 않아도 한 화면에 모든 필드가 다 보이지만 필드 개수가 많아 화면을 오른쪽으로 스크롤 해야 하는 경우 왼쪽 열몇 개를 고정해서 항상 화면에 보이도록 설정할 수 있습니다.
[보기] - [고정] 메뉴를 선택하면 아래에 열을 고정하는 메뉴가 보입니다.
지점번호가 항상 표시되도록 하려면 [열 1개] 또는 [A열까지]를 선택하면 됩니다.
하지만 오류 메시지가 뜹니다. 1행에 있는 제목 셀이 병합되어 있기 때문입니다.
제목은 [병합 해제]하고 [왼쪽 정렬]합니다.
그리고 다시 [A열까지] 고정을 선택합니다.
필드 개수가 많은 경우로 설정해놓고 오른쪽으로 화면을 스크롤해 보면 A 열이 고정됩니다.
엑셀은 선택한 셀을 기준으로 위쪽 행과 왼쪽 열을 한 번에 고정할 수 있지만 스프레드시트는 행과 열을 각각 고정해 줘야 합니다.
이 상태에서 [4행까지] 다시 고정하면 행과 열이 고정되는 것을 알 수 있습니다.
중복된 항목 삭제
현재 데이터는 특정 기간 동안 개설된 프로그램 정보를 정리해 놓은 표입니다.
어떤 프로그램이 개설되었는지 이 기간 동안 몇 번 개설되었는지 집계표를 만들 때 [중복 항목 삭제]를 사용하면 편리합니다.
1. 중복 항목 삭제
[M] 열에는 미리 프로그램명을 복사해 붙여 놓았는데, 이 범위를 선택한 후 [데이터] - [데이터 정리] - [중복 항목 삭제]를 선택합니다.
'데이터에 머리글 행이 있습니다.'에 체크 표시를 합니다.
범위를 선택할 때 머리글을 포함해서 선택했기 때문에 체크 표시를 하는 겁니다.
[중복 항목 삭제] 버튼을 누릅니다.
2. COUNTIF 함수를 사용해 개설 횟수 구하기
이제 조건에 맞는 개수를 구하는 COUNTIF 함수를 사용해 개설된 프로그램 횟수를 구해보겠습니다.
=COUNTIF 함수를 입력합니다.
세부 항목 표시를 누르면 함수 도움말을 볼 수 있습니다.
첫 번째 범위 인수에는 데이터베이스에서 프로그램명 범위를 지정하고 절대 참조합니다.
그리고 쉼표를 입력해 인수 사이를 구분하고 기준 인수에는 조건에 해당하는 셀(고윳값을 추출해 놓은 첫 번째 프로그램 셀)을 지정합니다.
괄호를 닫고 <Enter>를 누른 후 자동 완성에서 체크 표시를 하면 프로그램 종류 별 개설 횟수가 구해집니다.
중복 항목 삭제 기능은 고윳값을 구할 때도 활용할 수 있지만 집계표를 작성할 때도 활용할 수 있습니다.
조건부 서식
이번에는 조건에 만족하는 값들에 서식을 적용해 정보를 빠르게 파악할 수 있도록 도와주는 조건부 서식 사용법을 소개하겠습니다.
10월 신청자 명단에서 교육비 납입 여부와 중복 신청 수강생을 조건부 서식을 적용해 빠르게 파악해 보겠습니다.
먼저 교육비를 미납한 신청자의 행에 채우기 색을 적용해 보겠습니다. 그럼 납입 열의 동그라미 기호가 없는 행에 색이 적용되어야 합니다.
1. 조건부 서식 규칙 작성
서식이 적용될 범위를 선택하고 [서식] - [조건부 서식]을 선택하면 화면 오른쪽에 [조건부 서식 규칙] 창이 실행됩니다.
규칙을 수식으로 작성해야 하므로 다음의 경우 셀 서식 지정을 '맞춤 수식'으로 선택합니다.
[E5] 셀이 비어 있으면 미납입니다. 그래서 수식 입력창에 =E5=""을 입력합니다.
그러면 E5 셀이 비어있으면이라는 수식이 됩니다.
그리고 다른 신청자의 납입 셀도 비교해 색상을 적용해야 하니까 E 열은 절대 참조해 고정해 줘야 합니다.
E 열은 고정되고 행을 상대 참조해 다음 행, 다음 행을 비교합니다.
※ 스프레드시트 조건부 서식 규칙을 수식으로 작성할 때 엑셀처럼 셀을 클릭해서 참조할 수 없기 때문에 직접 셀 주소를 입력해야 합니다.
그리고 채우기 색상을 선택하고 [완료]를 누릅니다.
어떤가요? 한눈에 미납 신청자를 확인할 수 있습니다.
[E11] 셀에 O를 입력하면 납입이 되었으므로 색이 지워지는 것을 알 수 있습니다.
2. 조건부 서식 수정/삭제
적용한 서식을 수정 또는 삭제하려면 [조건부 서식 규칙] 창에서 규칙을 더블클릭해 수정하면 되고 [규칙 삭제]를 누르면 제거됩니다.
3. 조건부 서식으로 중복 입력된 데이터 강조하기
이번에는 규칙의 난이도를 조금 높여볼까요!
11월 신청자 명단에서 중복 수강한 신청자를 색상을 적용해 빠르게 파악해 보겠습니다.
서식을 적용할 대상이 되는 [E4:E16] 셀 범위를 선택하고 [조건부 서식 규칙] 창에서 [다른 규칙 추가]를 누릅니다.
'다음의 경우 셀 서식 지정'에서 맞춤 수식을 선택합니다.
그리고 스프레드시트 조건부 서식에서 규칙을 함수식으로 작성할 때 직접 인수를 입력해야 하는 번거로움이 있어 셀에 수식을 작성한 후 복사/붙여넣기 하겠습니다.
=COUNTIF 함수를 입력합니다. 범위 인수로 성명 범위를 지정하고 <F4>를 눌러 절대 참조합니다.
그리고 쉼표를 입력해 인수를 구분하고 기준 인수에는 첫 번째 성명 셀을 지정한 후 <F4>를 세 번 눌러 열을 고정합니다. 그러면 성명 범위에서 ‘이금은’의 개수를 구합니다.
이렇게 구한 개수가 1을 초과하거나 2 이상이면 중복 데이터가 되는 거죠! 괄호를 닫고 >1을 입력하고 <Enter>를 누릅니다.
결과가 FALSE입니다. 이금은 중복 데이터가 아니기 때문이죠!
수식이 있는 셀을 더블클릭해 블록을 지정한 후 복사해 수식 입력창에 붙여 넣고 [완료]를 누릅니다.
김수근과 김영희가 중복 데이터입니다.
복습을 해보겠습니다.
먼저 빈 셀에 =COUNTIF 함수를 입력하고 첫 번째 인수로 성명 범위를 지정한 후 절대 참조합니다.
그리고 두 번째 인수에는 천영희가 입력된 셀을 지정하고 <F4>를 세 번 눌러 열을 고정합니다.
괄호를 닫고 >1을 입력합니다.
그리고 수식을 복사해놓습니다.
서식을 적용할 표 범위를 선택하고 [조건부 서식 규칙] 창에서 [다른 규칙 추가]를 누릅니다. 다음의 경우 셀 서식 지정에서 ‘맞춤 수식’을 선택한 후 수식을 붙여 넣습니다.
이번에는 색상도 변경해 보겠습니다.
그리고 [완료]를 누르면 천영희가 중복 데이터입니다.
조건부 서식을 사용하면 많은 양의 데이터에서 특정 조건에 맞는 데이터를 쉽게 확인할 수 있겠죠!
영상 강의도 공유합니다.
https://youtu.be/NqvUx2WvmJQ?si=CeTGO_I4Z_ZP_EWx