스프레드시트 4강. 데이터 관리와 분석
정렬을 잘못하면 데이터가 섞입니다. 그리고 그 사실을 몇 달 뒤에 알게 됩니다.
풀고 시작
정렬이 데이터를 망가뜨리는 순간
정렬은 위험한 기능입니다. 한 열만 선택한 채로 정렬하면 그 열만 순서가 바뀌고 나머지 열은 제자리에 남습니다. 이름과 점수가 어긋나 버리죠. 게다가 실행 취소 없이 저장하고 나가면 되돌릴 방법이 없습니다.
엑셀은 이 사고를 막으려고 "선택 영역 확장" 여부를 묻습니다. 그 창을 습관적으로 넘기다 사고가 납니다. 표 안의 셀 하나만 클릭한 상태에서 정렬하면 엑셀이 표 전체를 알아서 잡으므로 이쪽이 안전합니다.
정렬 기준은 여러 개를 겹칠 수 있습니다. 부서로 먼저 묶고 그 안에서 점수 내림차순으로 세우는 식이죠. 이때 먼저 적은 기준이 우선입니다. 정렬 기준에는 값뿐 아니라 셀 색이나 글꼴 색도 쓸 수 있고, 원하는 순서가 따로 있다면 사용자 지정 목록을 만들어 씁니다. 대리, 과장, 차장, 부장 같은 직급 순서가 그런 경우입니다. 가나다순으로는 나올 수 없는 순서니까요.
정렬 방향도 기억해 두세요. 기본은 위아래(행 단위)지만 옵션에서 왼쪽에서 오른쪽으로 바꿀 수 있습니다.
필터는 숨기고, 고급 필터는 옮깁니다
자동 필터는 조건에 맞지 않는 행을 숨깁니다. 한 열 안의 여러 값을 고를 수 있고, 여러 열에 조건을 걸면 그 조건들은 모두 만족해야 합니다. 즉 AND로 묶입니다.
그러면 "부서가 영업이거나 점수가 90 이상"처럼 OR로 묶으려면 어떻게 할까요. 여기서 고급 필터가 필요합니다.
고급 필터는 조건을 시트 어딘가에 표로 적어 두고 그 범위를 가리킵니다. 규칙이 명확합니다.
같은 행에 나란히 적은 조건은 AND, 다른 행에 적은 조건은 OR입니다.
조건 표의 첫 줄에는 원본과 똑같은 필드 이름을 적어야 합니다. 한 글자라도 다르면 조건이 먹지 않습니다. 그리고 고급 필터는 결과를 제자리에서 걸러 보여 줄 수도 있고 다른 위치에 복사할 수도 있습니다. 원본을 건드리지 않고 결과만 따로 뽑을 때 쓰죠. 중복된 항목을 하나씩만 남기는 옵션도 여기 있습니다.
부분합 앞에는 반드시 정렬이 옵니다
부분합(subtotal) 은 그룹마다 소계를 넣어 주는 기능입니다. 부서별 인원수, 지점별 매출 합계 같은 것이죠.
여기 반드시 지켜야 할 순서가 있습니다. 부분합을 넣기 전에 기준이 될 열로 먼저 정렬해야 합니다. 부분합은 "값이 바뀌는 지점"마다 소계를 끼워 넣기 때문에, 정렬이 안 돼 있으면 같은 부서가 여기저기 흩어져 소계가 여러 번 생깁니다. 이 순서를 놓치는 것이 시험의 단골 함정입니다.
부분합을 두 번 겹쳐 쓸 수도 있습니다. 합계를 넣은 뒤 평균을 추가할 때는 "새로운 값으로 대치"를 꺼야 앞의 것이 남습니다. 부분합을 없애려면 대화상자의 "모두 제거"를 누릅니다.
피벗 테이블은 부분합을 훨씬 자유롭게 만든 도구입니다. 행과 열에 무엇을 놓을지 끌어다 바꾸기만 하면 집계가 다시 계산되죠. 다만 원본 데이터가 바뀌어도 자동으로 갱신되지 않습니다. 새로 고침을 눌러야 반영됩니다. 이 점도 자주 나옵니다.
목표값 찾기는 반대 방향의 계산입니다. 결과를 먼저 정해 놓고 어떤 입력이 그 결과를 만드는지 거꾸로 찾아 줍니다. 바꿀 셀은 반드시 수식이 아닌 값이 든 셀이어야 하고, 하나만 지정할 수 있습니다. 여러 개를 동시에 조절하려면 해 찾기를 씁니다.