들어가기 전에
개인적인 경험에 의하면, 업무에 따라 사용할 수 있는 도구는 다양하지만 항상 사용할 수 있는 도구는 Microsoft Excel입니다. Amazon Redshift, Power BI, Tableau 등에서 사용하는 데이터 형식은 표 형식이라 Excel과 친숙합니다. Excel에서 데이터 작업을 더 빠르게 수행할 수 있는 7가지 요령을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 7 MS Excel Tricks You Must Know as a Data Analyst
- URL: https://medium.datadriveninvestor.com/7-ms-excel-tricks-you-must-know-as-a-data-analyst-ec19f0a035be
1. 빈 셀 채우기
29명의 직원에 대한 부서 및 직원 ID를 설명하는 아래 2열 표를 보세요. 하지만 각 부서 문자열 값은 한 번만 나타납니다. 첫 번째 열이 그룹화 열인 다운로드한 데이터 집합의 일반적인 경우입니다.
3행에서 8행까지 'Analytics' 값을 복사하여 붙여넣고, 10행에서 20행까지 'Human Resources' 값을 복사하여 붙여넣고, 22행에서 30행까지 'Accounting' 값을 복사하여 붙여넣고자 합니다. 다음에 소개할 방법을 모른다면 이런 식으로 할 수 밖에 없습니다.
채우기 핸들(A2 셀의 검은색 십자 표시)을 다음 부서 값까지 드래그합니다. 8행까지 드래그하면 “Analytics”가 8행까지 복사됩니다.
하지만 수천 개의 행으로 구성된 테이블이 있다고 상상해 보세요. 채우기 핸들을 수백 번 드래그하는 것을 할 수 있을까요? 몇 초 만에 작업을 완료하는 방법은 다음과 같습니다.
- 관련 열을 클릭합니다(여기서는 A열).
- [홈] 탭 - [편집] 그룹 - [찾기 및 선택] - [이동 옵션]을 선택합니다.
- [빈 셀] 항목을 선택하고 [확인] 버튼을 누릅니다. 이렇게 하면 지정한 영역에서 빈 셀이 한꺼번에 선택됩니다.
- 등호(=)를 입력한 다음, 첫 번째 부서 값(A2 셀)을 클릭합니다.
- Ctrl + Enter 키를 누릅니다.
- 수식을 제거하려면 A열을 복사한 다음, [홈] 탭 - [붙여넣기] - [값]을 선택합니다.
2. 보이는 셀만 복사하기
일부 행이 숨겨져 있는 Excel 워크시트를 받았다고 가정해 보세요. 분석을 위해 표시된 행을 다른 시트에 복사하고 싶습니다. 보이는 셀을 강조 표시하여 어딘가에 붙여넣으면 숨겨진 셀도 여전히 복사되는 것을 볼 수 있습니다. 이것은 우리가 원하는 것이 아닙니다.
이에 대한 간단한 해결책은 Alt + 세미콜론(;)을 사용하는 것입니다.
- 숨겨진 셀이 있는 영역을 범위로 지정한 다음 Alt + ; 키를 누릅니다.
- Ctrl + C를 눌러 복사합니다.
- 붙여넣을 곳으로 가서 Ctrl + V를 누르면 숨겨진 영역은 제외된 채 복사됩니다.
일반적인 복사/붙여넣기와 비슷하지만, 복사하기 전에 Alt + ; 키를 누른다는 점만 다릅니다.
3. 단일 카테고리를 기반으로 한 초고속 필터링
위에서 사용한 데이터 집합에서 Human Resources에 대한 행만 필터링하려면 어떻게 해야 할까요? 보통은 헤더를 클릭하고 [홈] 탭 - [편집] 그룹 - [정렬 및 필터]로 이동하여 필터를 클릭한 다음, 원하는 열 헤더로 이동하여 필터 버튼을 클릭하고 원하는 값을 선택합니다. 더 나은 방법은 없을까요?
정렬 및 필터 설정을 활성화하지 않고 이 작업을 수행하는 방법은 다음과 같습니다.
- 필터로 사용하려는 값이 포함된 셀을 클릭합니다.
- 마우스 오른쪽 버튼을 누른 다음, 키보드에서 E + V를 누릅니다.
필터링 후 실행 취소(Ctrl + Z)를 하면 필터링되지 않은 이전 보기로 돌아갈 수 있습니다.
4. 데이터를 입력할 때 중복되지 않도록 하기
다른 분석가들이 각 열 아래에 값을 입력하는 Excel 추적기를 만들고 관리하는 임무를 맡았습니다. 사례 번호라는 열은 기본 키가 되어야 하는데, 이는 테이블에 동일한 사례 번호를 가진 행이 두 개 있으면 안 된다는 뜻입니다.
동료가 자신에게 할당된 사건 번호를 입력하기 때문에 오타 오류가 발생할 수 있습니다. 이미 존재하는 114534가 다른 하나는 114535여야 하기 때문에 두 번 나타날 수 있습니다. 열의 각 값이 고유하도록 하려면 어떻게 해야 할까요?
Excel의 데이터 유효성 검사를 사용하면 됩니다. 지금까지 사용했던 시트를 사용하여 이를 시연해 보겠습니다.
- 고유한 값만 입력되어야 하는 영역을 범위로 지정합니다.
- [데이터] 탭 - [데이터 도구] 그룹] - [데이터 유효성 검사]를 선택합니다.
- 데이터 유효성 검사 대화 상자의 설정 탭에서 사용자 지정을 선택하고 "=COUNTIF($B:$B, B2)=1"이라고 입력합니다.
- 필요하다면 오류 경고 탭에 오류 제목과 메시지를 입력합니다.
- 데이터 유효성 검사 대화 상자에서 [확인]을 클릭합니다.
5. 수식이 있는 셀 표시
필자는 이전에 여러 개의 시트와 계산식을 사용하여 기존 Excel 파일을 개선한 적이 있었는데, 그 파일 작성자가 이미 퇴사했기 때문입니다. 이런 종류의 작업에서 가장 먼저 확인할 것은 수식이 있는 셀의 위치입니다.
이렇게 하면 통합 문서를 편집하면서 특정 셀이 같은 시트 또는 다른 시트 내의 다른 셀에 어떻게 종속되어 있는지 이해할 수 있습니다. 이를 위한 간단한 방법은 아래 그림과 같이 수식 도구 모음으로 이동하여 수식 표시를 클릭하는 것입니다. 단축키인 Ctrl + `(백틱)를 눌러도됩니다.
이렇게 하면 수식이 있는 모든 셀의 수식을 보여줍니다. 단축키를 사용하면 더 편리합니다. Ctrl + `(Backtick)를 누르면 시트 내의 모든 수식이 표시됩니다. 일반 보기로 돌아가려면 Ctrl + ` 키를 한 번 더 누릅니다.
6. 참조하는 셀과 참조되는 셀 추적하기
수식이 있는 셀과 관련된 또 다른 빠른 팁이 있습니다. 어떤 셀이 다른 셀의 결과를 계산하는 데 사용되는지 시각적으로 확인하려면 어떻게 해야 할까요?
Excel의 수식 도구 모음에 적합한 도구가 있습니다. [수식] 탭 - [수식 분석] 그룹에서 [참조하는 셀 추적]과 [참조되는 셀 추적]을 사용하면 됩니다.
셀 H2(총 월 예산)의 선례는 셀 F2, F4, F6입니다. 반면 셀 H2에는 종속 항목이 없으므로 다른 셀에서 셀 H2의 값을 사용하지 않습니다. 한편, 셀 F2(분석 급여 합계)의 선행 조건은 모두 열 A와 C 아래의 셀이며 유일한 종속 조건은 셀 H2입니다.
이렇게 시각적으로 살펴보면 특정 셀의 값을 변경하면 다른 셀에 어떤 영향을 미치는지 이해할 수 있습니다.
7. 전치(Transpose) 붙여넣기
두 개의 데이터 집합을 비교하는 작업을 한 적이 있습니다. 두 번째 데이터 집합은 첫 번째 데이터 집합의 수정본입니다. 둘 다 열이 100개가 넘었습니다.
수정된 버전은 대부분의 열 이름을 유지하지만 일부는 약간 수정되었고 일부는 새로운 열입니다. Excel에서 마지막 열까지 오른쪽으로 끝도 없이 스크롤하는 것이 마음에 들지 않았습니다. 그것 보다는 아래로 내려가면서 확인하는 것을 선호하고 그런 방식으로 분석하는 것이 더 편할 것 같았습니다.
Table v1 열이라는 이름의 열을 만들고 그 아래에 100개 이상의 열 이름을 나열하고 싶었습니다. 마찬가지로, 다음 열은 테이블 v2 열이어야 합니다.
이 수수께끼에 대한 간단한 해결책은 열 머리글을 복사한 다음, [선택하여 붙여넣기] - [행/열 바꿈]으로 붙여넣는 것입니다. 아래 GIF는 이 작업을 수행하는 방법을 보여줍니다.
이 기법을 사용하면 앞서 언급한 두 테이블 간의 열 차이를 훨씬 더 빠르게 분석할 수 있었습니다.
마치며
이것 말고도 다른 요령도 많이 있습니다. 위에서 소개한 7가지는 데이터 분석을 위한 기본적인 것들이므로 꼭 익혀두시기 바랍니다.
'Excel' 카테고리의 다른 글
흔히 발생하는 8가지 Excel 오류와 해결 방법 (37) | 2024.12.13 |
---|---|
Excel에서 더 복잡한 논리를 위해 IF와 AND/OR 함수 결합하기 (29) | 2024.12.12 |
다중 시트 피벗 테이블로 Excel 기술 혁신하기 (30) | 2024.12.10 |
Excel "공유 위반" 오류 발생 원인과 해결 방법 5가지 (33) | 2024.12.09 |
완벽한 Excel 인쇄물을 얻기 위한 9가지 팁 (32) | 2024.12.06 |