대용량 엑셀 파일을 다룰 때 알아 두어야 할 것

엑셀이 사무 환경에서 필수 요소가 된 지 오래되었고, 점점 더 다루는 데이터의 양이 많아져 특별한 환경이 아닌 곳에서도 몇 만 건의 데이터를 다루는 경우가 많아졌습니다.

적게는 몇 천 건 또는 몇 만, 몇 십만 건의 데이터를 다룰 때는 데이터를 다루는 방법도 달라져야 합니다.

왜냐하면 엑셀 동작 속도가 느려지고 차지하는 공간도 커져 문제를 일으킬 수 있기 때문입니다.

 

그래서 대용량 엑셀 파일을 다루는 방법에 대해 알아 보겠습니다.

 

실습 파일 다운로드

국립농산물품질관리원 친환경인증 인증품목(2020년3월14일).xlsx

7.06MB

 

강좌에서 사용한 실습 파일은 공공데이터포털(http://data.go.kr)에서 용량 큰 파일을 다운로드 했습니다.

 

CSV 형식의 파일을, 엑셀 파일 형식(*.XLSX)으로 저장하고, 열 너비만 보기 좋게 수정했습니다.

이 데이터는 [A:I] 열까지 8개의 필드로 구성되어 필드 개수가 많은 편은 아니지만 레코드 수가 15만개 이상이 되는 대용량 파일에 해당됩니다. 

 

파일 크기와 서식 사이의 관계

[파일]-[정보]를 선택해 파일 크기를 확인해 보면 7.06MB 입니다.

 

[A1] 셀에 커서를 두고 단축키 <Ctrl + *(애스터리스크)>를 눌러 현재 데이터 범위를 모두 선택합니다.

[홈]-[글꼴]-[테두리]를 선택해 [모든 테두리]를 누릅니다.

단축키 <Ctrl + S>를 눌러 저장합니다.

 

다시 [파일]-[정보]를 선택해 파일 용량을 확인해 보면 7.40MB로 늘어나 있습니다.

셀에 서식을 적용하면 서식이 적용되었다는 정보가 파일에 기록되기 때문에 파일 크기가 커집니다.

 

[E], [F] 열에 쉼표 스타일과 소수 이하 1자리까지 나타내는 표시 형식을 설정하겠습니다.

[E2] 셀에 커서를 두고 <Shift + 오른쪽 화살표>를 눌러 [E2:F2] 셀 범위를 선택합니다.

단축키 <Ctrl + Shift + 아래쪽 화살표>를 눌러 [E2:F150739] 셀 범위를 선택합니다.

[표시 형식]에서 [쉼표 스타일]을 누르고, [자릿수 늘림]을 한 번 누릅니다.

단축키 <Ctrl + S>를 눌러 저장합니다.

 

다시 [파일]-[정보]를 선택해 파일 크기를 확인하니 7.51MB입니다.

서식 작업을 하면 할수록 작업 내역이 기록되어 파일 크기가 늘어납니다.

 

적용된 서식을 원래대로 바꾼 뒤 파일 크기를 확인해 보겠습니다.

[E2:F150739] 셀 범위를 선택한 상태에서 [표시 형식]-[일반]을 선택합니다.

 

[A1] 셀에서 단축키 <Ctrl + *>를 눌러 데이터 범위 전체를 선택한 뒤 [글꼴]-[테두리]에서 [테두리 없음]을 선택합니다.

단축키 <Ctrl + S>를 눌러 저장하고, [홈]-[정보]에서 파일 용량을 확인하면 그대로 7.51MB 입니다.

셀 서식을 원래대로 설정했는데도 파일 크기가 그대로입니다. 

작업을 하면 할수록 파일 크기는 계속 커집니다. 

 

셀 개수가 적다면 서식 작업으로 늘어나는 파일 크기가 별 부담이 없을 수 있지만 데이터가 입력된 셀 개수가 많다면 각각 셀에 적용된 서식 정보 때문에 파일 크기가 눈에 띄게 늘어납니다.

 

다루는 엑셀 파일 크기가 크면 파일을 열거나 작업을 할 때 실행되는 속도는 느려집니다.

 

만약 서식을 적용하기 전 파일 크기로 돌리려면 [파일]-[새로 만들기]를 선택해 [새 통합문서]를 만들고 데이터를 복사해 붙여넣기 하면 됩니다.

 

동작 속도에 영향을 미치는 조건부 서식

이번에는 파일 크기보다는 동작 속도에 영향을 미치는 기능에 대한 것입니다.

 

먼저 [조건부 서식] 기능 입니다.

조건부 서식은 셀 값에 따라 서식을 지정하는 기능으로 셀 값이 변경될 때마다 조건에 해당하는지 여부를 다시 확인합니다.

앞에서와 마찬가지로 셀 개수가 적을 땐 큰 차이가 없지만 셀 개수가 많은 경우엔 엑셀 동작 속도가 눈에 띄게 느려질 수 있습니다.

 

셀 값이 그때 그때 바뀌고, 그럴 때마다 서식이 적용되거나 취소되어야 한다면 조건부 서식 기능을 써야 합니다.

그러나 셀 값에 따라 서식을 적용해야 하지만 셀 값이 바뀌지 않는다면 조건부 서식 대신에 그냥 서식 기능을 적용하는 것이 엑셀 동작 속도를 빠르게 하는 방법입니다.

 

E, F열 데이터에 셀 값이 10,000 이상인 경우 채우기 색을 적용하도록 서식을 적용해 보겠습니다.

 

[E2:F150739] 셀 범위를 선택한 상태에서 [홈]-[조건부 서식]-[셀 강조 규칙]-[기타 규칙]을 선택합니다.

[새 서식 규칙] 대화상자에서 비교연산자는 >=(크거나 같다)를 선택하고 값에 10000을 입력합니다.

[서식] 단추를 누르고 [셀 서식] 대화상자 [채우기] 탭에서 적당한 색을 선택한 뒤 [확인]을 누릅니다.

 [확인]을 눌러 조건부 서식을 실행합니다.

조건부 서식이 적용되었습니다.

 

조건부 서식 조건에 해당하는 셀을 선택해서 채우기 색을 직접 적용하겠습니다.

 

[E1] 셀에 커서를 두고 [데이터]-[필터]를 선택합니다.

[E1] 셀 옆 역삼각형을 눌러 [색 기준 필터]에서 채우기 색이 적용된 항목을 선택합니다.

조건에 해당하는 [E9] 셀에 커서를 두고 단축키 <Ctrl + Shift + 아래쪽 화살표>를 눌러 셀 범위를 선택합니다.

[홈]-[글꼴]-[채우기 색]에서 채우기 색을 선택합니다.

[E1] 셀에서 역삼각형을 눌러 필터 해제를 선택합니다.

같은 방법으로 [F1] 셀 역삼각형을 눌러 [색 기준 필터]에서 채우기 색이 적용된 항목을 선택합니다.

조건에 해당하는 [F12] 셀에 커서를 두고 단축키 <Ctrl + Shift + 아래쪽 화살표>를 눌러 셀 범위를 선택합니다.

[홈]-[글꼴]-[채우기 색]에서 채우기 색을 선택합니다.

 

[F1] 셀에서 역삼각형을 눌러 필터 해제를 선택합니다.

 

조건에 해당하는 셀에 채우기 색을 적용했으므로 조건부 서식을 해제합니다.

[홈]-[조건부 서식]-[규칙 지우기]-[시트 전체에서 규칙 지우기]를 선택합니다.

마지막으로 [데이터]-[필터]를 선택해 필터를 해제 합니다.

 

처음부터 조건에 해당하는 특정 셀에 채우기 색을 적용할 예정이라면 굳이 조건부 서식 기능을 이용하지 않고 필터 기능을 써서 채우기 색을 적용하면 됩니다.

 

강좌에서 이 방법을 소개하는 이유는 조건부 서식 기능을 이용해서 작업을 한 경우에 더 이상 조건부 서식 기능이 필요 없을 때 어떻게 할 것인가를 소개하는 것입니다.

 

셀 값이 바뀌는 것에 따라 채우기 색을 적용하려고 할 때 조건부 서식 기능을 이용합니다.

그런 뒤 시간이 흘러 더 이상 셀 값이 변경되지 않을 때 조건부 서식 기능은 해제하고 셀에 적용된 채우기 색을 그대로 유지하는 방법을 소개하는 겁니다.

셀 값이 바뀌지 않는데 조건부 서식 기능을 계속 쓴다면 엑셀 동작 속도만 느리게 만들기 때문이죠.

 

수식과 값으로 붙여넣기

마지막은 파일 크기와 동작 속도 모두와 관련 있는 부분입니다.

 

바로 수식입니다.

[J2] 셀을 선택해 =E2+F2 수식을 작성합니다.

 채우기 핸들을 더블 클릭해 수식을 채웁니다.

단축키 <Ctrl + S>를 눌러 저장합니다.

[홈]-[정보]에서 파일 크기를 확인해 보니 9.38MB 입니다.

파일 크기는 똑 같지 않을 수 있습니다.

 

수식이 추가되어 공간을 많이 차지하고 있고, 셀 값이 바뀔 때마다 수식을 새로 고치기 때문에 동작 속도도 느리게 됩니다.

셀 값이 바뀌는 경우 새로 고쳐야 하지만 더 이상 셀 값이 바뀌지 않는 상태라면 수식을 그냥 값으로 바꾸면 공간도 덜 차지하고 동작 속도도 빠르게 됩니다.

 

[J2] 셀에 커서를 두고 단축키 <Ctrl + Shift + 아래쪽 화살표>를 눌러 [J2:J150739] 셀 범위를 선택합니다.

단축키 <Ctrl + C>를 눌러 복사하고, 바로 다시 단축키 <Ctrl + Alt + V>를 눌러 [선택하여 붙여넣기] 대화상자를 나타냅니다.

[값]을 선택하고 [확인]을 누릅니다.

겉으로 보이는 모습은 변화가 없지만 수식이 값으로 바뀌었습니다.

단축키 <Ctrl + S>를 눌러 저장합니다.

 

[홈]-[정보]를 선택해 파일 크기를 보면 8.79MB로 줄었습니다.

 

Excel 바이너리 통합(XLSB) 파일 형식으로 저장

파일 크기를 획기적으로 줄이는 방법이 있습니다.

 

파일 형식을 Excel 바이너리 통합(XLSB) 파일 형식으로 바꾸는 것입니다.

 

[홈]-[다른 이름으로 저장]을 선택합니다.

파일 형식을 Excel 바이너리 통합 문서(*.xlsb)를 선택하고 [저장]을 누릅니다.

[홈]-[정보]를 선택해 파일 크기를 확인해 보니 3.89MB 입니다.

파일 크기가 줄어든 만큼 빠르게 동작합니다.

 

XLSB 파일 형식은 엑셀 2007 버전 이상이라면 호환되는 파일 형식 입니다.

다만 엑셀이 아닌 다른 프로그램과 연계해서 사용해야할 경우 다른 프로그램이 인식하지 못하는 문제가 발생합니다.

 

 

 

엑셀에서 다루는 데이터의 크기가 커질수록 여러가지 문제가 발생할 수 있습니다.

소개한 내용 이외에도 개체가 여러 개 사용되었거나 용량 큰 그림 파일을 여러 개 첨부하는 경우에도 파일 크기가 너무 커지거나 처리 속도가 늦어질 수 있습니다.

 

그때그때 상황에 맞게 문제점과 해결 방법을 검색하는 방법을 익혀야 합니다.

 

필요하신 분께 도움이 되길 바라며 강좌 마치겠습니다.

수고하셨습니다.