엑셀에서 데이터 가져오기 및 변환 기능을 활용하는 방법을 찾고 계신가요? 그렇다면 이 글이 정답입니다. 때로는 다른 엑셀 통합 문서, 텍스트/CSV 파일, 웹 페이지 등 외부 소스에서 데이터 세트를 불러와야 할 때가 있습니다. 몇 가지 간단한 단계만 거치면 이러한 데이터를 가져와 엑셀 워크시트로 변환할 수 있습니다. 이 글에서는 엑셀에서 데이터를 가져오고 변환하는 4가지 쉬운 방법을 소개합니다.
데이터 가져오기 및 변환이란 무엇인가?
엑셀의 데이터 가져오기 및 변환(Get & Transform) 기능을 사용하면 외부 데이터를 가져오거나 연결한 뒤, 열 삭제, 데이터 형식 변경, 테이블 병합 등 원하는 방식으로 수정할 수 있습니다. 이후에는 해당 쿼리를 기반으로 차트나 보고서를 만들 수도 있습니다. 또한 주기적으로 새로 고침하여 데이터를 최신 상태로 유지할 수 있다는 점도 큰 장점입니다.
엑셀에서 데이터 가져오기 및 변환 예제 4가지
데이터 가져오기 및 변환 그룹을 활용하면 다양한 종류의 외부 데이터 세트를 가져와 편집할 수 있습니다. 여기서는 엑셀 통합 문서, 텍스트/CSV, 웹, 테이블/범위에서 외부 데이터를 가져온 후 새로운 데이터 세트로 변환하는 방법을 알아봅니다.
1. 엑셀 통합 문서에서 데이터 가져오기 및 변환
엑셀 통합 문서에서 데이터를 가져오고 변환하려면 아래 단계를 따라 하세요.
단계:
- 먼저 데이터 탭으로 이동한 후 데이터 가져오기를 클릭하고, 파일에서를 선택한 다음 Excel 통합 문서에서를 클릭합니다.
- 그러면 데이터 가져오기 대화상자가 열립니다.
- 원하는 파일을 선택합니다. 여기서는 "19 jun 2022"라는 이름의 엑셀 파일을 선택했습니다.
- 마지막으로 가져오기를 클릭합니다.
- 이제 내비게이터(Navigator) 창이 열립니다.
- 여기서 "19 jun 2022" 파일에서 Sheet1을 선택합니다.
- 그런 다음 데이터 변환을 클릭합니다.
- 파워 쿼리 편집기(PowerQuery Editor) 창이 나타납니다.
- 이제 첫 번째 행을 삭제하겠습니다.
- 행 줄이기(Reduce Rows)를 클릭하고 행 제거로 이동한 뒤 상위 행 제거를 클릭합니다.
- 상위 행 제거 대화상자가 열리면,
- 행 수(Number of rows) 입력란에 1을 입력합니다.
- 마지막으로 확인을 누릅니다.
- 상위 행이 제거된 데이터 세트는 아래와 같습니다.
- 이제 첫 행을 머리글로 사용을 클릭해 첫 번째 행을 머리글(Header)로 설정합니다.
- 변환이 완료된 데이터 세트입니다. 적용된 단계(APPLIED STEPS) 창에서 진행 과정을 확인할 수 있습니다.
- 이제 데이터 세트를 엑셀에 저장하려면 닫기 및 로드를 클릭합니다.
- 다시 한번 닫기 및 로드를 누릅니다.
마지막으로, 엑셀 통합 문서에서 가져온 변환된 데이터 세트가 새 시트에 나타나는 것을 확인할 수 있습니다.
2. 텍스트/CSV 파일에서 데이터 가져오기 및 변환
두 번째 방법은 텍스트/CSV 파일에서 데이터를 가져오고 변환하는 것입니다. 아래 단계를 따라 직접 해보세요.
단계:
- 먼저 데이터 탭으로 이동한 후 데이터 가져오기를 클릭하고, 파일에서를 선택한 다음 텍스트/CSV에서를 클릭합니다.
- 그러면 데이터 가져오기 대화상자가 나타납니다.
- 원하는 파일을 선택합니다. 여기서는 "19 jun 2022"라는 이름의 텍스트 파일을 선택했습니다.
- 마지막으로 가져오기를 클릭합니다.
- 대화상자가 열리면 데이터 변환을 클릭합니다.
- 이제 "andrew"와 "emma"라는 이름을 수정해 첫 글자를 대문자로 변환하겠습니다.
- 먼저 변환(Transform) 탭에서 텍스트 열을 선택하고, 형식을 클릭한 다음 각 단어의 첫 글자를 대문자로를 선택합니다.
- 이제 데이터가 "Andrew"와 "Emma"로 변환된 것을 확인할 수 있으며, 적용된 단계에서 과정을 확인할 수 있습니다.
- 다음으로, 데이터를 "Teni"로 바꿔보겠습니다.
- 홈 탭으로 이동한 후 값 바꾸기를 클릭합니다.
- 값 바꾸기 창이 열리면,
- 찾을 값(Value to Find) 입력란에 "Teni"를 입력하고, 바꿀 값(Replace With) 입력란에 "Tenis"를 입력합니다.
- 마지막으로 확인을 클릭합니다.
- 이제 데이터가 "Tenis"로 변환된 것을 볼 수 있습니다.
- 다음은 "Teniss"라는 데이터를 바꿔보겠습니다.
- 홈 탭으로 이동한 후 값 바꾸기를 클릭합니다.
- 값 바꾸기 창이 열리면,
- 찾을 값 입력란에 "Teniss"를 입력하고, 바꿀 값 입력란에 "Tenis"를 입력합니다.
- 마지막으로 확인을 클릭합니다.
- 여기서 변환된 데이터 세트와 적용된 단계 창의 과정을 확인할 수 있습니다.
- 이제 데이터 세트를 엑셀에 저장하려면 닫기 및 로드를 클릭합니다.
- 다시 한번 닫기 및 로드를 누릅니다.
마침내 텍스트 파일에서 가져온 변환된 데이터 세트를 얻게 됩니다.
3. 테이블/범위 기능을 활용한 데이터 변환
엑셀에서 테이블/범위(Table/Range)로부터 데이터를 가져와 변환할 수도 있습니다. 여기서는 학생들의 이름(Name), 나이(Age), 그룹(Group), 스포츠(Sports) 정보가 담긴 데이터 범위가 있습니다. 이 데이터를 엑셀로 가져온 후 변환해 보겠습니다. 아래 단계를 따라 해보세요.
단계:
- 먼저 표 안의 임의의 셀을 클릭합니다. 여기서는 C6 셀을 클릭했습니다.
- 그런 다음 데이터 탭으로 이동해 데이터 가져오기를 클릭하고, 기타 원본에서를 선택한 뒤 테이블/범위에서를 클릭합니다.
- 이제 데이터 세트에서 중복 항목을 제거하겠습니다.
- 먼저 홈 탭으로 이동해 행 줄이기를 클릭하고, 행 제거를 클릭한 다음 중복 제거를 선택합니다.
- 아래 화면에서 중복 행이 제거된 것을 확인할 수 있습니다.
- 이제 데이터 세트를 엑셀에 저장하려면 닫기 및 로드를 클릭합니다.
- 다시 한번 닫기 및 로드를 누릅니다.
마지막으로, 테이블/범위 기능을 통해 변환된 데이터 세트를 얻게 됩니다.
4. 파워 쿼리를 활용해 웹에서 데이터 가져오기 및 변환
마지막 방법은 웹에서 데이터를 가져온 후 파워 쿼리(Power Query)를 사용해 엑셀에서 변환하는 것입니다. 아래 단계를 따라 직접 해보세요.
단계:
- 먼저 데이터 탭으로 이동한 후 데이터 가져오기를 클릭하고, 기타 원본에서를 선택한 다음 웹에서를 클릭합니다.
- 웹에서(From Web) 대화상자가 열리면,
- URL 입력란에 사용하려는 웹사이트의 URL을 입력합니다.
- 그런 다음 확인을 누릅니다.
- 내비게이터(Navigator) 대화상자가 열립니다.
- 여기서는 해당 웹사이트에서 Table 0을 선택했습니다. 원하는 테이블을 자유롭게 선택하시면 됩니다.
- 그런 다음 데이터 변환을 클릭합니다.
- 이제 Column1을 삭제하겠습니다.
- 홈 탭으로 이동해 열 관리를 클릭하고, 열 제거를 클릭한 다음 열 제거를 선택합니다.
- 아래 화면에서 Column1이 제거된 것을 확인할 수 있습니다.
- 같은 방법으로 Column2도 제거합니다.
- 이제 첫 행을 머리글로 설정하겠습니다.
- 홈 탭으로 이동한 후 첫 행을 머리글로 사용을 클릭합니다.
- 변환된 데이터 세트와 적용된 단계 창의 과정을 확인할 수 있습니다.
- 이제 데이터 세트를 엑셀에 저장하려면 닫기 및 로드를 클릭합니다.
- 다시 한번 닫기 및 로드를 누릅니다.
마침내 웹에서 가져온 변환된 데이터 세트를 얻게 됩니다.
데이터 가져오기 및 변환 기능을 사용해야 하는 이유
엑셀에서 데이터 가져오기 및 변환 기능을 사용해야 하는 이유는 많습니다. 특히 다음과 같은 작업을 손쉽게 처리할 수 있습니다.
- 데이터 가져오기 및 변환을 사용하면 폴더 전체의 텍스트 파일들을 하나의 데이터 테이블로 가져올 수 있습니다.
- 내보낸 파일을 읽기 쉬운 형식으로 변환할 수 있습니다.
- 수백만 개의 행도 간편하게 파워 피벗(Power Pivot)으로 불러올 수 있습니다.
데이터 가져오기 및 변환 기능의 한계
엑셀에서 백만 개가 넘는 행을 내보내려고 하면 기능상의 한계에 부딪히기 쉽습니다.
- 데이터 가져오기 및 변환으로 수백만 개의 행을 변환한 경우, 그룹화되지 않은 행을 내보내려면 시간이 많이 걸리는 우회 방법이나 꼼수를 써야 할 수밖에 없습니다.
- 또한 데이터 가져오기 및 변환 쿼리를 여러 사용자에게 배포하는 것은 신뢰성이 떨어질 수 있으며, 특히 다양한 데이터 원본과 조인(join)을 사용하는 경우 더욱 그렇습니다.
결국 엑셀은 복잡한 데이터 모델링을 위해 설계된 도구가 아닙니다. 선형 회귀 분석 같은 작업은 빠르게 수행할 수 있지만, 그 이상의 심화 분석에는 더 강력한 플랫폼을 활용해야 합니다.
결론
이번 글에서는 엑셀에서 데이터를 가져오고 변환하는 4가지 방법을 살펴보았습니다. 원하는 결과에 맞게 어떤 방법이든 활용해 보세요. 이 글이 도움이 되었기를 바랍니다. 이해하기 어려운 부분이 있다면 언제든지 댓글로 질문해 주세요. 놓친 다른 방법이 있다면 알려주시기 바랍니다. 더 많은 엑셀 팁이 필요하다면 ExcelDemy를 방문해 주세요. 감사합니다!