다음을 통해 공유

Excel 2010 성능: 계산 성능 개선

  • 아티클

요약: 이 문서에서는 계산 기능을 중점적으로 개선함으로써 통합 문서 성능을 향상시키는 방법에 대해 설명합니다. 이 문서는 워크시트를 디자인 및 작성할 때 Excel에서 성능을 개선하는 데 사용할 수 있는 기술에 대한 3개의 문서 모음 중 하나입니다.

Excel에서 성능을 개선하는 방법에 대한 자세한 내용은 Excel 2010 성능: 성능 및 제한 개선 및 Excel 2010 성능: 성능 저하 요인 최적화 팁를 참조하십시오.

마지막 수정 날짜: 2011년 4월 11일 월요일

적용 대상: Excel | Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

이 문서의 내용
개요
계산 속도의 중요성
Excel의 계산 방법 이해
통합 문서, 워크시트 및 범위 계산
계산 옵션 제어
통합 문서의 계산 속도 개선
계산 문제 찾기 및 우선 순위 지정
결론
저자 정보
추가 자료

게시 날짜:  2010년 6월

제공:  MVP 기고자Charles Williams(Decision Models Limited(영문일 수 있음)) │ Allison Bokone(Microsoft Corporation) │ Chad Rothschiller(Microsoft Corporation) │ 저자 정보

목차

개요

Microsoft Office Excel 2007과 Microsoft Excel 2010에서는 "큰 눈금" 개념이 적용되어 사용 가능한 행이 1백만 개, 사용 가능한 열은 1만 6천 개로 늘어났으며 기타 여러 한도 역시 증가하여 이전 버전 Excel에 비해 작성할 수 있는 워크시트 크기가 훨씬 커졌습니다. Excel 2007 또는 Excel 2010의 워크시트 하나에 포함할 수 있는 셀의 수는 이전 버전에 비해 1,000배 이상 늘어났습니다.

이전 버전 Excel에서 대부분의 사용자는 계산 속도가 느린 워크시트를 만들었으며, 큰 워크시트는 대개 작은 워크시트보다 느리게 계산되었습니다. Excel 2007에 "큰 눈금" 개념이 도입되면서 성능이 매우 중요해졌습니다. 정렬, 필터링 등의 데이터 조작 작업과 느린 계산 속도로 인해 사용자가 수행 중인 작업에 집중하기가 어려워, 오류도 더 많이 발생했습니다.

Excel 2007 및 Excel 2010에는 이와 같이 증가된 용량을 처리하는 데 도움이 되는 다양한 기능이 도입되었습니다. 계산을 위해 두 개 이상의 프로세서를 동시에 사용하는 기능과 통합 문서 새로 고침/정렬/열기 등의 일반 데이터 집합 작업을 예로 들 수 있습니다. 또한 다중 스레드 계산을 수행하면 워크시트 계산 시간을 크게 줄일 수 있습니다. 그러나 Excel 계산 속도에 영향을 주는 가장 중요한 요인은 예전과 마찬가지로 워크시트 디자인 및 작성 방식입니다.

계산 속도가 가장 느린 워크시트를 수정하여 계산 속도를 수십, 수백, 수천 배까지 높일 수 있습니다. 이 문서에서는 워크시트의 계산 문제를 파악, 측정 및 개선함으로써 계산 속도를 높이는 방법을 살펴봅니다.

계산 속도의 중요성

계산 속도가 느리면 생산성에 영향을 주며 사용자 오류가 늘어납니다. 응답 시간이 길어지면 사용자 생산성 및 작업 집중도가 떨어집니다.

Excel에는 계산 수행 시기를 제어할 수 있는 두 가지 주 계산 모드가 있습니다.

  • 자동 계산 -  변경 시 수식이 자동으로 다시 계산됩니다.

  • 수동 계산 -  사용자가 F9 키를 누르는 등의 방법으로 요청하는 경우에만 수식이 다시 계산됩니다.

계산 시간이 약 1/10초 미만이면 사용자는 시스템이 즉시 응답한다고 생각합니다. 데이터를 입력할 때도 자동 계산을 사용할 수 있습니다.

계산 시간이 1/10~1초이면 사용자는 응답 시간이 약간 지연된다고 느끼지만 방해 받지 않고 작업을 계속 진행할 수 있습니다.

계산 시간이 길어지면 사용자는 데이터 입력 시 수동 계산으로 전환해야 합니다.

계산 시간이 1~10초이면 사용자가 수동 계산으로 전환할 가능성이 높습니다. 이 시점에서 사용자 오류와 불만 수준이 높아지기 시작하며(특히 반복 작업의 경우), 방해 받지 않고 작업을 원활하게 진행하기가 어려워집니다.

계산 시간이 10초보다 길면 사용자가 더 이상 계산을 계속하지 못하고 응답을 기다리는 동안 대개 다른 작업으로 전환하게 됩니다. 계산이 일련의 작업 중 하나인데 응답 시간이 길어 사용자가 작업 순서를 혼동하면 문제가 발생할 수 있습니다.

Excel의 계산 방법 이해

Excel에서 계산 성능을 개선하려면 사용 가능한 계산 방법 및 계산을 제어하는 방법을 이해해야 합니다.

전체 계산 및 다시 계산 종속성

Excel의 스마트 다시 계산 엔진은 각 수식의 참조되는 셀 및 종속성(수식에서 참조하는 셀)과 마지막 계산 이후 변경된 내용을 지속적으로 추적함으로써 계산 시간을 최소화합니다. 그러면 다음 다시 계산 시에는 Excel에서 다음 항목만 다시 계산합니다.

  • 다시 계산해야 하는 것으로 플래그가 지정되거나 변경된 셀, 수식, 값 또는 이름

  • 다시 계산해야 하는 다른 셀, 수식, 이름 또는 값에 종속된 셀

  • 일시적 함수 및 조건부 서식

Excel에서는 이전에 계산한 셀의 값이 계산 시 변경되지 않더라도 이전에 계산한 셀에 종속되는 셀을 계속 계산합니다.

대부분의 경우에는 계산 간에 몇 개의 수식이나 입력 데이터 중 일부분만이 변경되므로, 이러한 스마트 다시 계산 방식을 사용하는 경우 일반적으로 모든 수식의 전체 계산을 수행할 때에 비해 시간이 크게 줄어듭니다.

수동 계산 모드에서는 F9 키를 눌러 이 스마트 다시 계산을 시작할 수 있습니다. Ctrl+Alt+F9를 눌러 모든 수식의 전체 계산을 강제 지정하거나, Shift+Ctrl+Alt+F9를 눌러 전체 종속성 다시 작성 및 전체 계산을 강제 지정할 수도 있습니다.

계산 프로세스

다른 셀을 참조하는 Excel 수식은 참조 대상 셀 앞이나 뒤에 배치할 수 있습니다(정방향 참조 또는 역방향 참조). Excel에서는 셀을 고정된 순서나 행 또는 열별로 계산하지 않기 때문입니다. 대신 Excel은 계산할 모든 수식의 목록(계산 체인)과 각 수식에 대한 종속성 정보를 토대로 하여 계산 순서를 동적으로 결정합니다.

Excel의 각 계산 단계는 다음과 같습니다.

  1. 초기 계산 체인을 작성하고 계산을 시작할 위치를 결정합니다. 이 단계는 통합 문서를 메모리로 로드하면 수행됩니다.

  2. 종속성을 추적하고, 셀을 계산되지 않은 것으로 플래그 지정하고, 계산 체인을 업데이트합니다. 이 단계는 각 셀에 내용을 입력하거나 셀을 변경하면 실행되며 수동 계산 모드에서도 실행됩니다. 일반적으로 매우 빠르게 실행되므로 사용자는 인지할 수 없습니다.

  3. 모든 수식을 계산합니다. 계산 프로세스의 일부분으로 Excel은 계산 체인 순서와 구조를 다시 지정하여 이후의 다시 계산을 최적화합니다.

세 번째 단계는 각 계산 또는 다시 계산 시에 실행됩니다. Excel은 계산 체인의 각 수식을 차례로 계산하지만, 아직 계산되지 않은 하나 이상의 수식에 종속되는 수식은 나중에 다시 계산되도록 체인 뒷부분으로 전송됩니다. 즉, 한 수식이 다시 계산마다 여러 번 계산될 수 있습니다. Excel 2000에서는 각 워크시트에 대해 별도의 계산 체인이 유지 관리되었으며 워크시트는 이름순으로 계산되었습니다. Excel 2002부터는 전체 계산 체인 하나가 생성되었으므로 대부분의 통합 문서에서 계산 속도가 더 빨라졌습니다.

Excel의 계산 프로세스에 대한 자세한 내용은 Microsoft Excel 2002의 다시 계산(영문일 수 있음)을 참조하십시오.

통합 문서를 두 번째로 계산할 때는 첫 번째보다 속도가 훨씬 빠른 경우가 많습니다. 그 이유는 다음과 같습니다.

  • Excel은 보통 변경된 셀 및 해당 종속 항목만 다시 계산합니다.

  • Excel은 계산 순서를 확인하는 데 소요되는 대부분의 시간을 절약할 수 있도록 최신 계산 순서를 저장하여 다시 사용합니다.

  • 코어가 여러 개인 컴퓨터에서 Excel 2007 및 Excel 2010은 이전 계산 결과를 기준으로 하여 계산이 각 코어로 분산되는 방식을 최적화합니다.

  • Excel 세션에서는 Microsoft Windows 및 Excel이 모두 보다 빠른 액세스를 위해 최근 사용한 데이터 및 프로그램을 캐시합니다.

통합 문서, 워크시트 및 범위 계산

다양한 Excel 계산 방법을 활용하여 계산되는 항목을 제어할 수 있습니다.

열려 있는 모든 통합 문서 계산

각 다시 계산 및 전체 계산에서 현재 열려 있는 모든 통합 문서를 계산하고 통합 문서와 워크시트 내/사이의 모든 종속성을 확인합니다.

선택한 워크시트 계산

Shift+F9를 눌러 선택한 워크시트만 다시 계산할 수도 있습니다. 이 경우에는 계산되는 시트의 시트 내 종속성만 확인됩니다.

셀 범위 계산

Excel에서는 VBA(Visual Basic for Applications) 메서드 Range.Calculate를 사용하여 셀 범위를 계산할 수도 있습니다. Range.Calculate의 동작은 각 Excel 버전에서 크게 달라졌습니다.

  • Excel 2000

    Range.Calculate가 왼쪽에서 오른쪽, 위에서 아래로 계산을 수행하며 종속성은 모두 무시합니다.

  • Excel 2002/Excel 2003

    Range.Calculate가 계산 중인 범위 내의 종속성을 확인합니다.

  • Excel 2007/Excel 2010

    이 두 버전에는 모두 Range Calculate 메서드가 있습니다. Range.Calculate는 Excel 2002 및 Excel 2003에서와 동일하게 작동하지만 Range.CalculateRowMajorOrder는 Excel 2000에서와 동일하게 작동합니다. CalculateRowMajorOrder는 계산 중인 범위 내의 종속성을 확인하지 않으므로 보통 계산 속도가 훨씬 빠르기는 하지만, 계산 결과가 Range.Calculate와 동일하지 않을 수 있으므로 사용 시 주의해야 합니다. 자세한 내용은 Excel 2010 성능: 성능 및 제한 개선을 참조하십시오.

    Range.Calculate는 성능 최적화용으로 가장 유용한 Excel의 도구 중 하나입니다. 이 메서드를 사용하여 여러 수식의 계산 시간을 측정하고 속도를 비교할 수 있기 때문입니다.

일시적 함수

일시적 함수는 참조되는 셀이 변경되지 않은 경우에도 각 다시 계산 시에 항상 다시 계산됩니다. 따라서 일시적 함수를 많이 사용하면 각 다시 계산의 속도가 느려지지만 전체 계산 속도에는 변화가 없습니다. 함수 코드에 Application.Volatile을 포함하면 사용자 정의 함수를 일시적 함수로 만들 수 있습니다.

Excel에서 RAND(), NOW(), TODAY() 등의 기본 제공 함수는 일시적 함수입니다. OFFSET(), CELL(), INDIRECT(), INFO() 등의 함수는 일시적일 수도 있고 그렇지 않을 수도 있습니다.

이전에 일시적 함수로 소개되었던 INDEX(), ROWS(), COLUMNS(), AREAS() 등의 함수는 사실상 일시적 함수가 아닙니다.

일시적 동작

일시적 동작은 다시 계산을 트리거하는 동작으로, 다음과 같은 동작이 포함됩니다.

  • 자동 모드에서 행 또는 열 구분선 클릭

  • 시트에서 행/열/셀 삽입 또는 삭제

  • 정의된 이름 추가/변경/삭제

  • 자동 모드에서 워크시트 위치 변경 또는 워크시트 이름 바꾸기

  • Excel 2003, Excel 2007 또는 Excel 2010에서 행 필터링/숨기기/숨기기 취소

  • 자동 모드에서 통합 문서 열기. 다른 Excel 버전에서 통합 문서를 마지막으로 계산한 경우 통합 문서를 열면 보통 전체 계산이 수행됩니다.

  • 저장 전 다시 계산 옵션이 선택된 경우 수동 모드에서 통합 문서 저장

수식 및 이름 계산 상황

다음 중 하나를 수행하면 수식 또는 수식의 일부분이 수동 계산 모드에서도 즉시 계산됩니다.

  • 수식 입력 또는 편집

  • 함수 마법사를 사용하여 수식 입력 또는 편집

  • 함수 마법사에서 수식을 인수로 입력

  • 수식 입력줄에서 수식을 선택하고 F9 키를 누르거나(실행을 취소하고 수식으로 돌아가려면 Esc 키를 누름) 수식 계산 클릭

다음 조건 중 하나에 해당하는 셀이나 수식을 참조하는(종속되는) 수식은 계산되지 않은 것으로 플래그가 지정됩니다.

  • 셀 또는 수식이 입력됨

  • 셀 또는 수식이 변경됨

  • 셀 또는 수식이 자동 필터 목록에 있으며 조건 드롭다운 목록이 사용하도록 설정됨

  • 셀 또는 수식이 계산되지 않은 것으로 플래그 지정됨

계산되지 않은 것으로 플래그 지정된 수식은 해당 수식을 포함하는 범위, 워크시트, 통합 문서 또는 Excel 인스턴스를 계산하거나 다시 계산할 때 계산됩니다.

정의된 이름이 계산되도록 하는 상황은 셀의 수식이 계산되도록 하는 상황과는 다릅니다.

  • 정의된 이름은 해당 이름을 참조하는 수식이 계산될 때마다 계산됩니다. 따라서 여러 수식에서 하나의 이름을 사용하는 경우 해당 이름이 여러 번 계산됩니다.

  • 수식에서 참조하지 않는 이름은 전체 계산에서도 계산되지 않습니다.

데이터 표

Excel 데이터 표(데이터 탭의 데이터 도구 그룹에서 가상 분석, 데이터 표를 차례로 클릭)를 표 기능(홈 탭의 스타일 그룹에서 표 서식을 클릭하거나 삽입 탭의 표 그룹에서 표 클릭)과 혼동해서는 안 됩니다. Excel 데이터 표는 통합 문서에 대해 다시 계산을 여러 번 수행하며, 각 다시 계산에는 표의 서로 다른 값이 사용됩니다. Excel에서는 먼저 통합 문서를 일반적인 방법으로 계산한 다음 각 행 및 열 값 쌍에 대해 값을 대체하고 다시 계산한 후 결과를 데이터 표에 저장합니다.

데이터 표를 사용하면 편리하게 여러 변형을 계산하고 변형 결과를 확인 및 비교할 수 있습니다. Excel에서 각 계산 시 여러 계산을 자동으로 트리거하지 않도록 하되 표를 제외한 모든 종속 수식은 계속 계산하려면 데이터 표만 수동 계산 옵션을 사용합니다.

계산 옵션 제어

Excel에는 계산 방식을 제어하는 데 사용할 수 있는 다양한 옵션이 있습니다. 리본 메뉴에서 수식 탭의 계산 그룹을 통해 Excel 2010에서 가장 자주 사용되는 옵션을 변경할 수 있습니다.

그림 1. 수식 탭의 계산 그룹

수식 탭의 계산 옵션

더 많은 Excel 2010 계산 옵션을 표시하려면 파일 탭과 옵션을 차례로 클릭하고 Excel 옵션 대화 상자에서 수식 탭을 클릭합니다.

그림 2. Excel 옵션의 수식 탭에 있는 계산 옵션

Backstage 보기의 계산 옵션

대부분의 계산 옵션(자동, 데이터 표만 수동, 수동, 통합 문서를 저장하기 전에 항상 다시 계산) 및 반복 설정(반복 계산 사용, 최대 반복 횟수, 변화 한도값)은 통합 문서 수준이 아닌 응용 프로그램 수준에서 작동합니다. 즉, 열려 있는 모든 통합 문서에 대해 이들 옵션이 동일하게 적용됩니다.

고급 계산 옵션을 찾으려면 파일 탭, 옵션을 차례로 클릭하고 Excel 옵션 대화 상자에서 고급을 클릭한 후에 수식 섹션에서 계산 옵션을 설정합니다.

그림 3. 고급 계산 옵션

Backstage 보기의 고급 계산 옵션

Excel 2007 이전 버전 Excel의 경우에는 도구 메뉴에서 옵션을 선택한 다음 계산 탭을 클릭하면 모든 계산 옵션을 확인할 수 있습니다.

Excel을 시작할 때나 통합 문서가 열려 있지 않은 상태로 Excel이 실행 중일 때 초기 계산 모드는 처음으로 여는 서식 파일이나 추가 기능이 아닌 통합 문서에서 설정됩니다. 즉, 나중에 여는 통합 문서의 계산 설정은 무시됩니다. 물론 언제든지 Excel에서 수동으로 설정을 변경할 수 있습니다. 통합 문서를 저장하면 현재 계산 설정이 통합 문서에 저장됩니다.

자동 계산

자동 계산 모드란 모든 변경을 수행할 때와 통합 문서를 열 때 Excel에서 열려 있는 모든 통합 문서를 자동으로 다시 계산한다는 의미입니다. 일반적으로 자동 모드에서 통합 문서를 열어 Excel에서 다시 계산을 수행할 때는 통합 문서를 저장한 이후로 변경된 내용이 없으므로 다시 계산 과정이 표시되지 않습니다.

통합 문서를 마지막으로 계산할 때 사용했던 버전보다 이후 버전의 Excel에서 통합 문서를 열면 이 계산이 표시될 수 있습니다(예: Excel 2003에서 마지막으로 저장하고 Excel 2007에서 통합 문서를 여는 경우). Excel 계산 엔진은 버전별로 다르므로 Excel에서는 이전 버전 Excel을 사용하여 저장된 통합 문서를 열 때 전체 계산을 수행합니다.

수동 계산

수동 계산 모드란 사용자가 F9 키나 Ctrl+Alt+F9를 눌러 요청할 때나 통합 문서를 저장할 때만 Excel에서 열려 있는 모든 통합 문서를 다시 계산한다는 의미입니다. 다시 계산하는 데 시간이 오래 걸리는 통합 문서의 경우에는 변경 시 작업이 지연되지 않도록 계산을 수동 모드로 설정해야 합니다.

Excel에서는 상태 표시줄에 계산을 표시하여 수동 모드의 통합 문서를 다시 계산해야 함을 알려 줍니다. 또한 통합 문서에 순환 참조가 포함되어 있으며 반복 옵션이 선택되어 있어도 상태 표시줄에 계산이 표시됩니다. 계산은 종속성이 너무 많은 경우에도 표시됩니다. Excel 2007 및 Excel 2010에서는 이러한 종속성 한도가 크게 늘어났습니다. 자세한 내용은 Excel 2010 성능: 성능 및 제한 개선을 참조하십시오.

반복 설정

통합 문서에 순환 참조를 의도적으로 포함한 경우에는 반복 설정을 통해 통합 문서가 다시 계산(반복)되는 최대 횟수 및 수렴 조건(변화 한도값: 중지할 시기)을 제어할 수 있습니다. 일반적으로는 순환 참조가 실수로 포함된 경우 Excel에서 경고를 표시하고 해당 참조를 확인하지 않도록 반복 확인란 선택을 취소해야 합니다.

통합 문서의 계산 속도 개선

이 섹션에서는 통합 문서의 계산 속도를 높이기 위해 수행할 수 있는 단계와 방법을 설명합니다.

프로세서 속도 및 다중 코어

대부분의 Excel 버전에서는 프로세서 속도가 빠르면 Excel 계산도 더 빠르게 수행됩니다.

Excel 2007부터는 다중 프로세서 시스템을 지원하는 새로운 기능이 추가되었습니다. Excel 2007에 도입된 다중 스레드 계산 엔진을 통해 Excel에서는 다중 프로세서 시스템을 최대한 활용할 수 있으며, 대부분의 통합 문서에서 성능이 크게 개선될 수 있습니다.

Excel 2010에서는 다중 프로세서 시스템 지원을 위한 새 기능이 추가되었습니다.

자세한 내용은 Excel 2010 성능: 성능 및 제한 개선을 참조하십시오.

RAM

가상 메모리 페이징 파일로의 페이징은 속도가 느리므로 운영 체제, Excel 및 통합 문서용 실제 RAM이 충분해야 합니다. 디스크 작업을 트리거하는 사용자 정의 함수를 실행하고 있지 않은데 계산 중에 하드 디스크 작업량이 매우 많아진다면 RAM이 더 필요합니다.

앞에서 언급한 것처럼 최신 버전 Excel에서는 많은 양의 메모리를 효율적으로 활용할 수 있으며, Excel 2007 및 Excel 2010은 최대 2GB의 통합 문서 메모리를 사용하여 단일 통합 문서 또는 통합 문서 조합을 처리할 수 있습니다. 64비트 Excel은 매우 큰 통합 문서도 처리할 수 있습니다. 자세한 내용은 Excel 2010 성능: 성능 및 제한 개선의 "큰 데이터 집합 및 64비트 Excel" 섹션을 참조하십시오.

효율적인 계산을 위해 필요한 대략적인 RAM은 동시에 열어야 하는 가장 큰 통합 문서 집합을 저장하기에 충분한 RAM + 256MB/512MB(Excel 및 운영 체제용) + 기타 실행 중인 응용 프로그램용의 추가 RAM입니다. Excel에서 사용 중인 메모리의 양은 Windows 작업 관리자에서 확인할 수 있습니다.

그림 4. Excel 메모리 사용량이 표시된 Windows 작업 관리자

Windows 작업 관리자의 Excel 사용 현황

 

자세한 내용은 Excel 2010 성능: 성능 및 제한 개선의 "큰 데이터 집합 및 64비트 Excel" 섹션을 참조하십시오.

계산 시간 측정

통합 문서가 빠르게 계산되도록 하려면 계산 시간을 정확하게 측정할 수 있어야 합니다. 즉, VBA의 Time 함수보다 더 빠르고 정확한 타이머가 필요합니다. 아래 코드 예제에 나와 있는 MICROTIMER() 함수는 시스템 고해상도 타이머에 대한 Windows API 호출을 사용하며, 시간 간격을 마이크로초 단위까지 측정할 수 있습니다. Windows는 멀티태스킹 운영 체제이며 두 번째 계산은 첫 번째 계산보다 속도가 빠르므로 시간을 여러 번 측정해도 각 결과 값이 대개 정확히 일치하지는 않습니다. 시간을 가장 정확하게 측정하려면 계산 작업 시간을 여러 번 측정한 다음 결과의 평균을 구하십시오.

Visual Basic Editor가 VBA 사용자 정의 함수 성능에 어떤 영향을 줄 수 있는지에 대한 자세한 내용은 Excel 2010 성능: 성능 저하 요인 최적화 팁의 속도가 빠른 VBA 사용자 정의 함수 섹션을 참조하십시오.

Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
'

' Returns seconds.
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    '
    MicroTimer = 0

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1                            

' Seconds
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 
End Function

계산 시간을 측정하려면 적절한 계산 메서드를 호출해야 합니다. 이러한 서브루틴은 범위 계산 시간, 시트 또는 열려 있는 모든 통합 문서의 다시 계산 시간 또는 열려 있는 모든 통합 문서의 전체 계산 시간을 제공합니다.

아래 서브루틴과 함수를 모두 표준 VBA 모듈에 복사해야 합니다. VBA 편집기를 열려면 Alt+F11을 누르십시오. 삽입 메뉴에서 모듈을 선택한 다음 코드를 모듈에 복사하면 됩니다.

Sub RangeTimer()
    DoCalcTimer 1
End Sub
Sub SheetTimer()
    DoCalcTimer 2
End Sub
Sub RecalcTimer()
    DoCalcTimer 3
End Sub
Sub FullcalcTimer()
    DoCalcTimer 4
End Sub

Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl

' Initialize
    dTime = MicroTimer              

    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End if
        
        ' Max is used range.

        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If

        ' Include array cells outside selection.

        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then 
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell

        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select

' Get start time.
    dTime = MicroTimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select

' Calculate duration.
    dTime = MicroTimer - dTime
    On Error GoTo 0

    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"

Finish:

    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Calculation = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End Sub

Excel 2010에서 서브루틴을 실행하려면 Alt+F8을 누르거나 매크로 재생을 클릭합니다. 원하는 서브루틴을 선택하고 실행을 클릭하십시오.

그림 5. 계산 타이머가 표시된 Excel 매크로 창

Excel 매크로 창

계산 문제 찾기 및 우선 순위 지정

계산 속도가 느린 대부분의 통합 문서에서는 보통 소수의 문제 영역이나 문제가 대부분의 계산 시간을 소비하는 경우가 많습니다. 이러한 영역이나 문제가 파악되지 않은 경우 이 섹션에서 설명하는 드릴다운 방식을 통해 해당 영역이나 문제를 찾으십시오. 문제 영역이나 문제의 위치를 모르는 경우에는 각 문제에서 소요되는 계산 시간을 측정하여 문제 제거를 위한 작업 우선 순위를 지정해야 합니다.

문제 확인을 위한 드릴다운 방식

드릴다운 방식에서는 먼저 통합 문서 계산 시간과 각 워크시트 계산 시간을 측정한 다음 속도가 느린 시트의 수식을 차단합니다. 아래의 각 단계를 수행하여 계산 시간을 측정합니다.

드릴다운 방식을 사용하여 문제를 확인하려면

  1. 통합 문서가 하나만 열려 있으며 다른 작업이 실행되고 있지 않은지 확인합니다.

  2. 계산을 수동으로 설정합니다.

  3. 통합 문서 백업 복사본을 만듭니다.

  4. 계산 타이머 매크로가 포함된 통합 문서를 열거나 통합 문서에 해당 매크로를 추가합니다.

  5. 각 워크시트에서 Ctrl+End를 차례로 눌러 사용되는 범위를 확인합니다.

    그러면 마지막으로 사용한 셀을 확인할 수 있습니다. 이 셀의 위치가 적절한 범위를 벗어난 경우에는 초과 열과 행을 삭제한 다음 통합 문서를 저장할 수 있습니다. 자세한 내용은 Excel 2010 성능: 성능 저하 요인 최적화 팁의 사용되는 범위 최소화 섹션을 참조하십시오.

  6. FullCalcTimer 매크로를 실행합니다.

    일반적으로는 통합 문서의 모든 수식을 계산하는 데 걸리는 시간이 가장 깁니다.

  7. RecalcTimer 매크로를 실행합니다.

    일반적으로는 전체 계산 직후의 다시 계산 시간이 가장 짧습니다.

  8. 통합 문서 일시성을 전체 계산 시간 대비 다시 계산 시간의 비율로 계산합니다.

    여기서는 일시적 수식과 계산 체인 계산이 문제가 되는 범위를 측정합니다.

  9. 각 시트를 활성화하고 SheetTimer 매크로를 차례로 실행합니다.

    방금 통합 문서를 다시 계산했으므로 각 워크시트의 다시 계산 시간이 측정됩니다. 그러면 문제가 있는 워크시트를 확인할 수 있습니다.

  10. 선택한 수식 블록에 대해 RangeTimer 매크로를 실행합니다.

    1. 문제가 있는 각 워크시트에서 열이나 행을 블록 몇 개로 나눕니다.

    2. 각 블록을 차례로 선택하고 블록에 대해 RangeTimer 매크로를 실행합니다.

    3. 필요한 경우 각 블록을 더 적은 수의 블록으로 세분화하여 추가로 드릴다운합니다.

  11. 문제 해결을 위한 우선 순위를 지정합니다.

계산 속도 개선 및 문제 줄이기

계산 시간이 많이 소요되는 원인은 수식의 수나 통합 문서의 크기가 아니라 셀 참조 및 계산 작업의 수와 사용 중인 함수의 효율성입니다.

대부분의 워크시트는 절대 참조와 상대 참조가 혼합된 수식을 복사하는 방식으로 생성되므로 보통 수식을 많이 포함하며, 이러한 수식에는 반복/중복되는 계산과 참조가 들어 있습니다.

매우 크고 복잡한 수식과 배열 수식은 가급적 사용하지 마십시오. 일반적으로는 열과 행을 더 많이 포함하고 복잡한 계산은 몇 개만 사용하는 것이 더 효율적입니다. 이렇게 하면 Excel 2010에서 스마트 다시 계산 및 다중 스레드 계산 수행 시 계산을 최적화할 수 있으며, 계산을 더 쉽게 이해하고 디버그할 수 있습니다. 아래에는 통합 문서 계산 속도를 높이기 위한 몇 가지 규칙이 나와 있습니다.

규칙 1: 중복/반복되거나 불필요한 계산 제거

중복 및 반복되거나 불필요한 계산을 확인한 다음, Excel에서 이러한 문제 항목을 계산하고 결과를 생성하는 데 필요한 대략적인 셀 참조 및 계산의 수를 파악합니다. 그런 후에 참조와 계산을 더 적게 사용하여 같은 결과를 얻을 수 있는 방법을 모색합니다.

이 과정에는 보통 다음 단계 중 하나 이상을 수행합니다.

  • 각 수식의 참조 수를 줄입니다.

  • 반복되는 계산을 하나 이상의 도우미 셀로 이동한 다음 원래 수식에서 도우미 셀을 참조합니다.

  • 행과 열을 추가로 사용하여 다른 수식에서 다시 사용할 수 있도록 중간 결과를 한 번 계산한 다음 저장합니다.

규칙 2: 가장 효율적인 함수 사용

함수 또는 배열 수식과 관련된 문제가 확인되는 경우 같은 결과를 얻을 수 있는 더 효율적인 방법이 있는지를 확인합니다. 예를 들면 다음과 같습니다.

  • 정렬된 데이터를 조회하는 경우 정렬되지 않은 데이터를 조회하는 것보다 효율성이 수십 배에서 수백 배까지 높아질 수 있습니다.

  • VBA 사용자 정의 함수는 대개 Excel의 기본 제공 함수에 비해 속도가 느립니다(적절하게 작성된 VBA 함수는 속도가 빠를 수도 있음).

  • SUM, SUMIF 등의 함수에서 사용되는 셀의 수를 최소화합니다. 계산 시간은 사용되는 셀 수에 비례하며 사용되지 않는 셀은 무시됩니다.

  • 속도가 느린 배열 수식을 사용자 정의 함수로 바꿉니다.

규칙 3: 적절한 스마트 다시 계산 사용

Excel에서 스마트 다시 계산을 효율적으로 사용하면 Excel에서 다시 계산을 수행할 때마다 처리해야 하는 작업의 수가 줄어듭니다. 따라서 다음을 수행하십시오.

  • INDIRECT, OFFSET 등의 일시적 함수는 대체 항목보다 훨씬 효율적인 경우가 아니면 사용하지 않습니다. 보통은 OFFSET을 적절하게 사용하면 속도가 훨씬 더 빠릅니다.

  • 배열 수식 및 함수에서 사용하는 범위의 크기를 최소화합니다.

  • 배열 수식과 매우 큰 수식을 여러 개별 도우미 열과 행으로 분리합니다.

규칙 4: 각 변경 작업 시간 측정 및 테스트

일부 변경 작업의 경우 정상적으로 수행되지 않거나 계산 속도가 예상보다 느릴 수 있습니다. 따라서 다음과 같이 각 변경 작업에 대해 시간을 측정하고 테스트를 수행해야 합니다.

  1. RangeTimer 매크로를 사용하여 변경할 수식의 시간을 측정합니다.

  2. 변경을 수행합니다.

  3. RangeTimer 매크로를 사용하여 변경된 수식의 시간을 측정합니다.

  4. 변경된 수식을 사용해도 올바른 결과가 생성되는지 확인합니다.

규칙 예제

다음 섹션에서는 규칙을 사용하여 계산 속도를 높이는 방법의 예제를 제공합니다.

날짜 기간 합계

숫자 2000개가 포함된 열의 날짜 기간 합계를 계산하려는 경우를 가정해 보겠습니다. A열에 숫자가 있고 B열과 C열에는 날짜 기간 합계가 포함되어야 합니다.

이 경우 효율적 함수인 SUM을 사용하여 수식을 작성할 수 있습니다.

B1=SUM($A$1:$A1)
B2=SUM($A$1:$A2)

그림 6. 날짜 기간 SUM 수식 예제

날짜 기간 SUM 수식 예제

그런 다음 수식을 아래쪽의 B2000 셀까지 복사합니다.

SUM을 통해 합이 계산되는 셀 참조가 총 몇 개인지 확인합니다. B1은 셀 하나를 참조하고, B2000은 2000개의 셀을 참조합니다. 따라서 평균은 셀당 1000개 참조이며 총 참조 수는 2백만 개입니다. 수식 2000개를 선택하고 RangeTimer 매크로를 사용하면 B열의 수식 2000개가 80밀리초에 계산됨을 확인할 수 있습니다. 이러한 계산은 대부분 여러 번 반복됩니다. 즉, SUM은 B2:B2000 범위에 대해 각 수식에서 A2에 A1을 더합니다.

수식을 다음과 같이 작성하면 이러한 중복 항목을 제거할 수 있습니다.

C1=A1
C2=C1+A1

이 수식을 아래쪽의 C2000 셀까지 복사합니다.

이제 합이 계산되는 셀 참조가 총 몇 개인지 확인해 봅니다. 첫 번째 수식을 제외한 각 수식에서 셀 참조 두 개를 사용하므로 총 셀 참조 개수는 1999*2+1=3999개입니다. 즉, 첫 번째 수식에 비해 셀 참조 개수가 1/500이 된 것입니다.

RangeTimer는 C열의 수식 2000개를 계산하는 시간이 3.7밀리초임을 보여 줍니다(B열의 경우 80밀리초). 즉, 수식마다 약간의 오버헤드가 발생하므로 성능 개선 비율은 이론상의 500배가 아닌 80/3.7=22배입니다.

오류 처리

계산을 많이 수행하는 수식에서 오류가 발생하는 경우(정확히 일치 조회에서는 오류가 빈번하게 발생함) 결과를 0으로 표시하려면 다양한 방식으로 코드를 작성할 수 있습니다.

  • 먼저 다음과 같은 단일 수식(속도가 느림)을 작성할 수 있습니다.

    B1=IF(ISERROR(time expensive formula),0,time expensive formula)

  • 다음과 같은 두 개의 수식을 작성하면 속도가 빨라집니다.

    A1=time expensive formula
B1=IF(ISERROR(A1),0,A1)

  • Excel 2007부터는 빠르고 간편한 단일 수식인 IFERROR 함수를 사용할 수 있습니다.

    B1=IFERROR(time expensive formula,0)

동적 고유 항목 계산

그림 7. 고유 항목 계산을 위한 예제 데이터 목록

고유 데이터 계산 예제

A열에 데이터 행이 11,000개이며 자주 변경되는 목록이 있는 경우 해당 목록의 고유 항목 수를 동적으로 계산(공백 무시)하는 수식을 작성하려면 다음과 같이 여러 방법을 사용할 수 있습니다.

배열 수식

Ctrl+Shift+Enter를 사용하여 다음 배열 수식을 작성할 수 있습니다.

{=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}

RangeTimer에는 이 수식의 계산 시간이 13.8초로 표시됩니다.

SUMPRODUCT

SUMPRODUCT는 보통 동일한 배열 수식보다 계산 속도가 빠릅니다.

=SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&""))

이 수식의 계산 속도는 10.0초입니다. 따라서 성능이 13.8/10.0=1.38배 개선되기는 하지만 만족할 만한 수준은 아닙니다.

사용자 정의 함수

아래 코드 예제에서는 컬렉션 인덱스가 고유해야 한다는 점을 활용하는 VBA 사용자 정의 함수를 보여 줍니다. 이 코드에 사용된 몇 가지 기술에 대한 설명은 Excel 2010 성능: 성능 저하 요인 최적화 팁의 효율적인 함수 사용 섹션에서 사용자 정의 함수 관련 섹션을 참조하십시오.

Public Function COUNTU(theRange As Range) As Variant
    Dim colUniques As New Collection
    Dim vArr As Variant
    Dim vCell As Variant
    Dim vLcell As Variant
    Dim oRng As Range

    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
    vArr = oRng
    On Error Resume Next
    For Each vCell In vArr
    If vCell <> vLcell Then
        If Len(CStr(vCell)) > 0 Then
             colUniques.Add vCell, CStr(vCell)
        End If
    End If
    vLcell = vCell
    Next vCell
    
    COUNTU = colUniques.Count
End Function

이 =COUNTU(A2:A11000) 수식의 계산 시간은 0.061초에 불과하므로 성능 개선 비율은 13.8/0.061=226배입니다.

수식 열 추가

이전 데이터 예제를 살펴보면 데이터가 정렬되어 있음을 확인할 수 있습니다(Excel에서 11,000개의 행을 정렬하는 데 0.5초가 걸림). 이 행의 데이터가 이전 행의 데이터와 같은지 확인하는 수식 열을 추가할 수 있습니다. 수식은 두 데이터가 다르면 1을 반환하고 같으면 0을 반환합니다.

B2 셀에 다음 수식을 추가합니다.

=IF(AND(A2<>"",A2<>A1),1,0)

그런 다음 수식을 아래쪽으로 복사하고 B열의 합을 구하는 수식을 추가합니다.

=SUM(B2:B11000)

이 모든 수식의 전체 계산에는 0.027초가 걸리며, 성능 개선 비율은 13.8/0.027=511배입니다.

결론

Excel 2010에서는 이전 버전에 비해 훨씬 큰 워크시트를 효율적으로 관리할 수 있으며 계산 속도도 더 빨라졌습니다. 큰 워크시트를 만들 때는 작성 방식으로 인해 계산 속도가 느려지는 경우가 많습니다. 계산 속도가 느린 워크시트가 있으면 계산이 진행 중인 동안 사용자가 계산에 집중하기가 어려워지므로 오류가 더 많이 발생합니다.

간단한 몇 가지 기술을 사용하면 계산 속도가 느린 워크시트를 수십 배 또는 수백 배 더 빠르게 계산할 수 있습니다. 워크시트 계산 속도를 높이기 위해 워크시트를 디자인하고 작성할 때 이러한 기술을 적용할 수도 있습니다.

저자 정보

Charles Williams는 1996년에 Decision Models를 설립하여Microsoft Excel 및 관계형 데이터베이스 관련 고급 컨설팅, 의사 결정 지원 솔루션 및 도구를 제공하고 있습니다. Charles는 현재 널리 사용되고 있는 Excel 성능 프로파일러이자 성능 도구 집합인 FastExcel의 작성자이며, 정의된 이름 관리용으로 널리 사용되는 유틸리티인 Name Manager의 공동 작성자이기도 합니다. Excel 계산 성능/방법, 메모리 사용 및 VBA 사용자 정의 함수에 대한 자세한 내용은 Decision Models(영문일 수 있음) 웹 사이트를 참조하십시오.

이 기술 문서는 A23 Consulting(영문일 수 있음)과의 제휴를 통해 작성된 것입니다.

Allison Bokone는 Microsoft Corporation 소속의 Office 팀 프로그래밍 관련 문서 작성자입니다.

Chad Rothschiller는 역시 Microsoft Corporation 소속의 Office 팀 프로그램 관리자입니다.

추가 자료

Excel 2010에 대한 자세한 내용은 다음 자료를 참조하십시오.