대규모 데이터 세트로 작업하는 동안 스프레드시트 속도가 저하됩니다. 수천 개의 행과 복잡한 계산이 포함된 Excel 통합 문서를 관리하는 것은 어렵습니다. 대규모 데이터세트를 사용하려면 스프레드시트를 최적화해야 합니다. 스프레드시트를 최적화하면 성능이 원활해지고 로딩 시간이 빨라지며 효율성이 향상됩니다.
이 기사에서는 스프레드시트를 최적화하여 대규모 통합문서의 성능을 향상시키는 방법에 대해 설명합니다.
1. 불필요한 데이터 제거
통합 문서를 깨끗하게 유지하고 사용하지 않거나 원치 않는 행, 열 및 워크시트를 삭제하세요. 필요한 데이터만 보관하세요.
빈 행 및 열 삭제:
데이터세트에는 불필요한 빈 행과 열이 포함될 수 있습니다. 스페셜로 가기 기능을 이용하면 빠르게 확인하고 삭제할 수 있습니다.
- 데이터 범위를 선택하세요.
- Ctrl + G 누르기>> 특집을 선택하세요.>> 공백을 선택하세요. .
- 삭제 강조표시된 행이나 열.
이렇게 하면 파일 크기가 줄어들고 재계산 속도가 향상됩니다.
2. 수식 최적화
2.1. 휘발성 함수 교체
휘발성 함수는 통합 문서가 열리거나 변경될 때마다 다시 계산됩니다. 가능하다면 휘발성 함수를 비휘발성 함수로 바꾸세요.
다음은 일반적인 휘발성 함수 목록입니다:
- 지금()
- 오늘()
- 랜드()
- 오프셋()
- 간접()
지금을 정적 값으로 교체
현재 날짜와 시간을 표시하기 위해 NOW 기능을 사용하는 대신 키보드 단축키를 사용하십시오. NOW 함수는 모든 변경 사항을 다시 계산합니다.
- Ctrl +;를 사용할 수 있습니다. 현재 날짜를 정적 값으로 삽입합니다.
2.1. 배열 수식 단순화
배열 수식을 효율적으로 사용해야 합니다. 배열 수식은 강력하지만 통합 문서의 속도를 저하시킵니다. 도우미 열을 사용하여 배열 수식 사용을 제한하세요.
SUM과 IF를 별도로 사용하는 대신 SUMIF 함수를 사용할 수 있습니다.
배열 수식:
=SUM(IF(A1:A1000>10, B1:B1000, 0))
대규모 스프레드시트의 경우 SUMIF가 성능과 단순성 측면에서 확실한 승자입니다. 리소스 사용량을 최소화하고 불필요한 복잡성을 방지하며 통합 문서를 깔끔하게 유지합니다.
=SUMIF(A1:A1000, ">10", B1:B1000)
SUMIF를 사용할 수 없는 경우 도우미 열을 사용할 수 있습니다.
최적화가 포함된 도우미 열로 교체:
- 새 열을 삽입하고 다음을 추가하세요.
- 그런 다음 다음 공식을 사용하세요.
이는 배열 수식을 더 작고 리소스 집약적인 단계로 나눕니다.
참조 범위 줄이기:
- 전체 열을 참조하는 대신>> A:A.
- 특정 셀 범위나 정의된 범위를 사용할 수 있습니다.>> A1:A1000.
3. 조건부 서식을 조금만 사용하세요
전체 열에 서식을 적용하는 대신 실제 데이터 범위로 제한하세요.
- 데이터 범위(예:A1:A100)만 선택하세요.
- 여기서 조건부 서식 규칙을 적용하여 불필요한 오버헤드를 피하세요.
4. 외부 링크 줄이기
연결을 통해 외부 통합 문서에서 데이터를 가져오는 대신 파워 쿼리를 사용하여 통합 문서 내의 데이터를 가져오고 변환할 수 있습니다.
- 데이터 탭으로 이동>> 데이터 가져오기를 클릭합니다.>> 파일에서를 선택하세요.>> 통합문서에서를 선택하세요. .
- 필요한 데이터를 현재 파일에 로드합니다.
이렇게 하면 외부 파일에 대한 의존도가 줄어들고 데이터 검색 속도가 빨라집니다.
5. 계산 모드 조정
작은 데이터 세트로 작업하는 경우 자동 계산 옵션을 사용할 수 있습니다. 하지만 복잡한 수식이 포함된 대규모 데이터를 처리하는 경우에는 수동 재계산 설정을 사용하세요.
수식이 많은 통합 문서의 경우:
- 파일로 이동 탭>> 옵션 선택 .
- 수식 선택>> 계산 옵션에서>> 수동을 선택하세요. .
- 확인을 클릭하세요. .
- F9 누르기 필요한 경우에만 다시 계산합니다.
이를 통해 자동 재계산으로 인한 지연 없이 시트 작업을 수행할 수 있습니다.
반복 계산 끄기
불필요한 재계산을 유발할 수 있는 반복 계산을 비활성화할 수도 있습니다.
- 반복 계산 활성화 비활성화 .
- 확인을 클릭하세요. .
6. 데이터 테이블 및 피벗 테이블 사용
동적 요약을 만들려면 요약 테이블을 수동으로 계산하거나 업데이트하는 대신 피벗 테이블 기능을 사용하세요.
- 데이터를 선택하세요.
- 삽입으로 이동 탭>> 피벗 테이블 선택 .
- 필드를 행과 열로 드래그하여 동적 요약을 만듭니다.
피벗 테이블은 필드 목록을 기반으로 합계를 자동 계산합니다. 대규모 데이터세트를 더 빠르게 업데이트하고 더 효율적으로 처리할 수 있습니다.
7. 통합 문서 디자인 최적화
통합 문서를 최적화하려면 전체 열이나 배열 수식을 참조하는 대신 명명된 범위를 사용하여 셀 범위를 동적으로 참조할 수 있습니다.
시간이 지남에 따라 증가하는 판매 데이터 세트로 작업한다고 가정해 보겠습니다. A1:A1000과 같은 고정 범위를 참조하는 대신 동적 명명 범위를 사용하세요.
- 수식으로 이동 탭>> 이름 관리자 선택>> 새로 만들기를 선택하세요. .
- 이름 정의:SalesData .
- 참조: 다음 수식을 삽입하세요:
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
이 공식은 데이터가 증가하거나 제거되는 경우 기존 공식이나 차트를 자동으로 업데이트합니다.
8. 파일 크기 압축
파일을 최적화하려면 이미지 크기와 외부 파일 크기를 줄일 수 있습니다.
통합 문서에 이미지가 포함된 경우:
- 이미지를 선택하세요.
- 사진 형식으로 이동 탭>> 사진 압축을 선택합니다. .
- 낮은 해상도 옵션 선택(예:웹) 또는 이메일 ) 필요한 세부 사항을 손상시키지 않고 파일 크기를 줄입니다.
9. 고급 도구 사용
파워 쿼리 사용: 파워 쿼리 편집기에는 데이터를 가져오고 정리하는 다양한 기본 제공 기능이 있어 복잡한 수식의 필요성이 줄어듭니다.
데이터 모델 활성화: 대규모 데이터세트의 경우 Excel에 내장된 데이터 모델을 사용하여 관계를 만들고 계산을 수행할 수 있습니다.
반복 작업에 VBA 사용: 통합 문서의 복잡성을 줄이기 위해 VBA로 반복적인 프로세스를 자동화하세요.
10. 불필요한 추가 기능 비활성화
정기적으로 필요하지 않은 경우 추가 기능을 활성화하지 마십시오. Excel을 열 때마다 모든 추가 기능이 설치됩니다.
- 파일로 이동 탭>> 옵션 선택 .
- Excel 옵션에서>> 추가 기능을 선택합니다.
- COM 추가 기능 관리에서 필수적이지 않은 추가 기능을 식별하고 비활성화합니다. .
- 사용하지 않는 추가 기능 제거 .
특히 대규모 스프레드시트로 작업할 때 리소스를 확보할 수 있습니다.
11. 정기점검
오류와 끊어진 링크를 지우려면 찾기 및 바꾸기 기능을 활용하세요. 이 기능을 사용하면 이상 현상과 오류를 제거할 수 있습니다.
찾기 및 바꾸기를 사용하여 오류를 지우려면:
- 홈으로 이동 탭>> 찾기 및 선택에서 >> 찾기/바꾸기를 선택하세요. .
- 유형 찾기의 오류 이름 상자.
- 오류를 모두 바꾸거나 오류를 일으키는 링크를 해결하세요.
최적화를 마친 후 통합 문서를 테스트하여 기능에 영향이 없는지 확인하세요.
결론
스프레드시트 최적화는 간단한 작업이므로 큰 통합 문서는 복잡할 필요가 없습니다. 대규모 통합 문서의 성능을 향상시키기 위해 언급된 기술과 실제 단계를 따를 수 있습니다. 수식 최적화, 데이터 크기 축소, 고급 Excel 도구 사용 등 이러한 방법을 사용하면 스프레드시트를 원활하게 실행하는 데 도움이 됩니다.
솔루션이 포함된 무료 고급 Excel 연습을 받아보세요!