남이 만든 엉망인 표를
쓸 수 있게 만들기

Ctrl+Shift+방향키 로 범위를 잡는 것과, F4 로 $ 를 붙이는 것. 이 둘은 여기서 계속 씁니다. 1장 02·06장을 먼저 보세요.

보기 좋은 표와 계산되는 표는 다릅니다.

병합된 칸, 비워 둔 칸, 한 칸에 여러 줄 — 사람 눈에는 깔끔하지만 엑셀에게는 구멍입니다.

그래서 실무에서는 두 벌을 씁니다. 계산용 원본은 규칙대로 쌓아 두고, 보고용은 거기서 만들어 냅니다. 이 장은 그 방법입니다.

1. 파일을 엽니다

   「천만리자전거」 시트 하나입니다. 표는 B5:J15 인데, 그 위 4행에 커미션 비율이 D4:J4 로 한 줄 얹혀 있습니다.

2. 머리글이 몇 줄인지 봅니다

   5행에 지사·영업점·1분기·2분기·합계, 6행에 1월~6월. 머리글이 두 줄이고 그 위에 커미션까지 있습니다.

3. 정렬을 걸어 봅니다

   B5 를 누르고 데이터 → 정렬. 엑셀이 어디부터 표인지 몰라 엉뚱한 범위를 잡습니다.

4. 고쳐 봅니다

   커미션을 열 하나로 내려 각 영업점 줄에 붙입니다. 줄은 길어지지만 정렬·필터·피벗이 됩니다.

이렇게 되면 된 것입니다 — 머리글이 한 줄이 되고 정렬이 제대로 걸리면 된 것입니다.

제목을 표 가운데 놓고 싶을 뿐이라면 병합할 필요가 없습니다.

범위를 잡고 Ctrl+1 → 맞춤 → 가로 → 「선택 영역의 가운데로」. 보기는 병합과 똑같은데 칸은 안 합쳐집니다.

정렬도 필터도 그대로 됩니다. 실무에서는 이쪽을 쓰세요.

접었더니 엉뚱한 줄이 남는다면, 소계가 위에 있는데 엑셀은 아래에 있다고 알고 있는 것입니다.

데이터 → 윤곽선 설정 에서 「요약 행이 세부 데이터 아래에 표시」 체크를 끄세요.

1. 2-02 「천만리자전거-1」 을 엽니다

   D5:F5 가 1분기, G5:I5 가 2분기 입니다. D:F 세 열을 잡고 데이터 → 그룹 (Shift+Alt+→).

2. 접었다 폈다 해 봅니다

   위쪽에 생긴 − 단추를 누르면 1~3월이 숨고 분기 값만 남습니다.

3. 2-03 「헬로상가」 시트로 갑니다

   B5:B9 의 제품명이 병합돼 있습니다 — 선풍기 셋, TV 둘.

4. 정렬을 걸어 봅니다

   B4:D14 를 잡고 정렬하면 「병합된 셀 크기가 같아야」 라며 거부당합니다.

5. 병합 대신을 써 봅니다

   병합을 풀고, 제목 B2 는 Ctrl+1 → 맞춤 → 「선택 영역의 가운데로」 로 바꿔 보세요.

이렇게 되면 된 것입니다 — 그룹이 접히고, 병합을 푼 뒤 정렬이 통과하면 된 것입니다.

지금 채워진 것은 수식입니다. 줄 순서가 바뀌면 따라 바뀝니다.

범위를 복사하고 제자리에 선택하여 붙여넣기 → 값 으로 덮으세요. 이걸 빼먹으면 정렬하는 순간 무너집니다.

1. 파일을 엽니다

   B4:D14 표입니다. B열 제품명이 병합돼 있어 B6·B7 이 비어 보입니다.

2. 맨 아랫줄 합계를 먼저 봅니다

   14행에 선풍기 합계가 142 로 나와 있습니다. 실제로는 142+111+151 = 404 여야 합니다.

3. 병합을 풉니다

   B5:B13 을 잡고 병합하고 가운데 맞춤을 다시 눌러 끕니다. 빈 칸이 드러납니다.

4. 빈 칸만 골라 채웁니다

   B5:B13 을 잡은 채 F5 → 옵션 → 빈 셀. 이어서 = 치고 ↑, 그다음 Ctrl+Enter.

5. 값으로 굳힙니다

   B5:B13 복사 → 제자리에 선택하여 붙여넣기 → 값.

이렇게 되면 된 것입니다 — 합계가 142 에서 404 로 바뀌면 된 것입니다.

같은 창에서 목록(드롭다운), 숫자 범위 제한, 날짜 제한 도 걸 수 있습니다. 잘못된 값이 들어오기 전에 막는 것이 고치는 것보다 훨씬 쌉니다.

다음 장의 중복 막기 도 같은 자리에서 합니다.

1. 파일을 엽니다

   B4:E16 표입니다. B열 제품코드(BY-006)와 D열 색상(B,W,P,G)은 영문, C열 제품명과 E열 담당자는 한글입니다.

2. 영문 칸에 IME 를 겁니다

   B5:B16 을 잡고 데이터 → 데이터 유효성 검사 → IME 모드 → 영문. D열도 같이 해 둡니다.

3. 한글 칸에도 겁니다

   C5:C16 과 E5:E16 에는 「한글」 로 지정합니다.

4. 옆으로 옮겨 다니며 쳐 봅니다

   맨 아래 빈 줄에 한 줄을 넣어 보세요. 한/영 키를 안 눌러도 저절로 바뀝니다.

이렇게 되면 된 것입니다 — 칸을 옮길 때마다 입력기가 알아서 바뀌면 된 것입니다.

1. 「조건부서식」 시트를 엽니다

   B4:G11 이 확진자 이동 경로이고, I4 에 「찾을 지역」 칸이 따로 있습니다.

2. I5 에 찾을 지역을 칩니다

   성북구 라고 넣어 보세요.

3. C5:G11 에 규칙을 겁니다

   범위를 잡고 홈 → 조건부 서식 → 새 규칙 → 수식을 사용. 수식은 =ISNUMBER(SEARCH($I$5,C5)).

4. I5 의 단어를 바꿔 봅니다

   종로구 로 바꾸면 규칙을 안 고쳐도 강조가 옮겨 다닙니다.

5. 다른 두 시트와 견줍니다

   「찾기및바꾸기」 는 한 번 칠하고 끝, 「필터」 는 해당 줄만 보기입니다. 바뀌면 따라오는 것은 조건부 서식뿐입니다.

이렇게 되면 된 것입니다 — I5 한 칸만 바꿔 강조가 따라 움직이면 된 것입니다.

Ctrl+1 → 표시 형식 → 사용자 지정 에 넣습니다. 보이는 것만 바뀌고 계산은 원래 값으로 됩니다.

실제로 나눠서 저장하면 안 됩니다. 나중에 합계가 백만 배 틀어집니다. 단위는 제목이나 머리글에 「단위: 백만원」 이라고 적어 두세요.

1. 파일을 엽니다

   B5:J17 표입니다. C5:J5 에 국가, B6 아래로 게임 이름이 있고 J4 에 「(단위 : US$)」 라고 적혀 있습니다.

2. 자릿수를 줄여 봅니다

   C6:J17 을 잡고 Ctrl+1 → 표시 형식 → 사용자 지정 에 #,##0, 를 넣어 보세요. 천 단위가 됩니다.

3. 쉼표를 하나 더 붙입니다

   #,##0,, 로 바꾸면 백만 단위입니다.

4. 값이 안 바뀐 것을 확인합니다

   아무 칸을 누르고 수식 입력줄을 보세요. 원래 값 그대로입니다 — 보이는 것만 바뀐 것입니다.

이렇게 되면 된 것입니다 — 화면은 짧아졌는데 수식 입력줄의 값은 그대로면 된 것입니다.

1. 파일을 엽니다

   B4:G11 표입니다. B열에 항목(면적·임대료·유동인구수…), C4:G4 에 매장(강남점·서초점…)이 있습니다.

2. 전체를 복사합니다

   B4:G11 을 잡고 Ctrl+C.

3. 빈 곳에 눕혀 붙입니다

   B14 쯤에서 오른쪽 클릭 → 선택하여 붙여넣기 → 「행/열 바꿈」 체크.

4. 결과를 봅니다

   이제 매장이 세로, 항목이 가로입니다. 매장끼리 견주기가 훨씬 쉽습니다.

이렇게 되면 된 것입니다 — 6열짜리가 8열로 눕고, 매장 이름이 세로로 서면 된 것입니다.

조건부 서식 → 셀 강조 규칙 → 중복 값 으로 먼저 눈에 띄게 하고, 데이터 → 중복된 항목 제거 로 지웁니다.

제거는 되돌리기 어려우니 반드시 사본에서 하세요.

1. 파일을 엽니다

   F4:H17 이 「발송된 쿠폰 리스트」, B4:D17 이 「사용된 쿠폰」 입니다.

2. B열에 중복 금지를 겁니다

   B5:B17 을 잡고 데이터 → 데이터 유효성 검사 → 사용자 지정. 수식은 =COUNTIF($B$5:$B$17,B5)=1.

3. 오류 메시지를 적어 둡니다

   같은 창의 「오류 메시지」 탭에 「이미 사용된 쿠폰번호입니다」 라고 적습니다.

4. 일부러 겹치게 쳐 봅니다

   빈 줄에 B5 와 같은 쿠폰번호를 쳐 보세요. 막히고 메시지가 뜹니다.

이렇게 되면 된 것입니다 — 같은 번호를 두 번 못 넣게 막히면 된 것입니다.

1. 파일을 엽니다

   2-07 과 같은 표입니다. C6:J17 이 국가 × 게임 매출입니다.

2. 범위에 이름을 붙입니다

   C6:J17 을 잡고 수식 입력줄 왼쪽의 「이름 상자」 에 매출 이라고 치고 Enter.

3. 수식에 써 봅니다

   빈 칸에 =SUM(매출) · =AVERAGE(매출) 을 넣어 보세요. 주소를 몰라도 계산됩니다.

4. 관리 화면을 봅니다

   수식 → 이름 관리자 (Ctrl+F3). 등록된 이름이 보이고, 고치고 지우는 곳입니다.

이렇게 되면 된 것입니다 — =SUM(매출) 이 주소를 쓴 것과 같은 값을 내면 된 것입니다.

560줄을 눈으로 대조하면 반드시 놓칩니다. 빈 열에 =A3=원본기록!A3 처럼 넣고 끌면 다른 곳만 FALSE 가 뜹니다.

조건부 서식으로 FALSE 인 줄을 칠하면 한눈에 보입니다. 나란히 보기는 확인용으로 쓰세요.

1. 파일을 엽니다

   「원본기록」 과 「수정기록」 두 장, 각 563줄입니다. 눈으로 대조하면 반드시 놓칩니다.

2. 나란히 놓습니다

   보기 → 새 창 → 보기 → 모두 정렬 → 세로. 각 창에서 다른 시트를 고릅니다.

3. 같이 굴립니다

   보기 → 나란히 보기 → 동시 스크롤.

4. 수식으로 진짜 찾습니다

   「수정기록」 의 빈 열(예: K3)에 =A3=원본기록!A3 을 넣고 아래로 끕니다.

5. FALSE 를 걸러 냅니다

   그 열에 필터를 걸어 FALSE 만 골라 보세요. 바뀐 줄만 남습니다.

이렇게 되면 된 것입니다 — 563줄 중에서 바뀐 줄만 몇 줄로 추려지면 된 것입니다.

마법사 마지막 단계에서 열마다 서식을 정할 수 있습니다.

제품코드나 사번처럼 0 으로 시작하는 것은 여기서 「텍스트」 로 지정해야 합니다. 안 그러면 007 이 7 이 됩니다.

1. 「과일채소명」 시트 — 너비 고정

   A1:A3 에 무화과오렌지복숭아토마토구아바 처럼 세 글자씩 붙어 있습니다. 구분 기호가 없으니 「너비 고정」 을 씁니다.

2. 나눌 자리를 찍습니다

   데이터 → 텍스트 나누기 → 너비가 일정함. 미리보기에서 세 글자마다 눈금을 클릭합니다.

3. 「스토어매출」 시트 — 구분 기호

   B열 주문번호가 CA-2020-152156 입니다. 빈 열을 두 개 먼저 만들고 나누세요.

4. 구분 기호로 나눕니다

   데이터 → 텍스트 나누기 → 구분 기호로 분리됨 → 기타 에 - 를 넣습니다.

5. 3단계를 그냥 넘기지 마세요

   마지막 단계에서 숫자로 보이는 코드 열은 「텍스트」 로 지정합니다.

이렇게 되면 된 것입니다 — 한 칸이 여러 칸으로 갈라지고, 앞자리 0 이 살아 있으면 된 것입니다.

찾기 창에 Ctrl+J 를 눌러도 아무것도 안 보입니다. 점 하나가 깜빡일 뿐인데 제대로 들어간 것입니다.

안 되면 그 칸을 지우고 다시 눌러 보세요. 여러 번 눌려 있으면 안 찾아집니다.

1. 「요일별출근표」 시트를 엽니다

   C5:G9 를 보세요. 한 칸에 이름이 두 명 씩 들어 있습니다 — 김세민 줄바꿈 정다온.

2. 줄바꿈으로 나눠 봅니다

   C5:C9 를 잡고 데이터 → 텍스트 나누기 → 구분 기호 → 기타 칸에서 Ctrl+J 를 누릅니다.

3. 아무것도 안 보여도 맞습니다

   Ctrl+J 는 화면에 안 보입니다. 점 하나가 깜빡일 뿐인데 제대로 들어간 것입니다.

4. 반대로 합쳐 봅니다

   빈 칸에 =C5&CHAR(10)&D5 를 넣고, 홈 → 맞춤 → 텍스트 줄 바꿈 을 켜 보세요.

이렇게 되면 된 것입니다 — 두 이름이 두 칸으로 갈라지고, 반대로 한 칸에 두 줄로 합쳐지면 된 것입니다.

이제 받은 표를 계산되는 표로 바꿀 수 있습니다. 실무에서 시간을 가장 많이 잡아먹는 일이 이것이었습니다.

다음은 3장입니다. 준비되면 이어서 만들겠습니다.