요약: 이 문서에서는 Microsoft Excel에서 자주 발생하는 여러 성능 관련 문제를 최적화하는 팁에 대해 설명합니다. 이 문서는 워크시트를 디자인 및 작성할 때 Excel에서 성능을 개선하는 데 사용할 수 있는 기술에 대한 3개의 문서 모음 중 하나입니다.
Excel에서 성능을 개선하는 방법에 대한 자세한 내용은 Excel 2010 성능: 계산 성능 개선 및 Excel 2010 성능: 성능 및 제한 개선을 참조하십시오.
마지막 수정 날짜: 2011년 4월 11일 월요일
적용 대상: Excel | Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA
이 문서의 내용
참조 및 링크
사용되는 범위 최소화
추가 데이터 허용
조회
배열 수식 및 SUMPRODUCT
효율적인 함수 사용
빠른 VBA 매크로
Excel 파일 형식 성능 및 크기
통합 문서 열기, 닫기, 저장 및 크기 조정
기타 성능 최적화
결론
저자 정보
추가 자료
게시 날짜: 2010년 6월
제공: .jpg "MVP 기고자")
Charles Williams(Decision Models Limited(영문일 수 있음)) │ Allison Bokone(Microsoft Corporation) │ Chad Rothschiller(Microsoft Corporation) │ 저자 정보
목차
참조 및 링크
다음 섹션에서는 참조 및 링크 유형과 관련하여 성능을 개선하는 방법에 대해 설명합니다.
정방향 참조 및 역방향 참조
수식을 명확하게 지정하고 오류를 방지하려면 수식이 다른 수식이나 셀을 정방향(오른쪽이나 아래)으로 참조하지 않도록 디자인하십시오. 계산을 연기해야 하는 수식이 많은 경우 적절한 계산 순서를 설정하는 데 시간이 더 오래 걸릴 수 있는 통합 문서의 첫 번째 계산과 같은 극단적인 경우를 제외하면, 정방향 참조는 대개 계산 성능에 영향을 주지 않습니다.
반복이 포함된 순환 참조
반복이 포함된 순환 참조의 경우 계산을 여러 차례 수행해야 하므로 계산 속도가 느립니다. 따라서 계산 속도를 높이기 위해 반복 계산이 더 이상 필요하지 않도록 순환 참조를 "해제"하는 경우가 많습니다. 예를 들어 현금 흐름 및 이자 계산에서는 현금 흐름을 이자보다 먼저 계산한 후에 이자를 계산하고, 마지막으로 이자가 포함된 현금 흐름을 계산합니다.
Excel에서는 종속성을 고려하지 않고 시트별로 순환 참조 시트를 계산합니다. 따라서 순환 참조가 둘 이상의 워크시트에 적용되는 경우에는 보통 계산 속도가 느려집니다. 불필요한 계산이 수행되지 않도록 하려면 순환 계산을 워크시트 하나로 이동하거나 워크시트 계산 순서를 최적화하십시오.
반복 계산이 시작되기 전에 Excel에서는 통합 문서를 다시 계산하여 모든 순환 참조 및 해당 종속 항목을 식별해야 합니다. 이 프로세스는 계산 2~3회 반복과 동일합니다.
순환 참조 및 해당 종속 항목이 식별된 후 각 계산을 반복하려면 Excel에서 순환 참조의 모든 셀을 계산해야 할 뿐 아니라 순환 참조 체인의 셀에 종속되는 모든 셀, 그리고 일시적 셀과 그 종속 항목까지 계산해야 합니다. 복잡한 계산에서 순환 참조의 셀을 사용하는 경우에는 이러한 계산을 별도의 닫힌 통합 문서로 분리한 다음 순환 계산이 수렴된 후에 해당 통합 문서를 열어서 다시 계산하는 것이 더 빠를 수 있습니다.
순환 계산의 셀 수와 이러한 셀에 소요되는 계산 시간을 줄이는 것이 중요합니다.
통합 문서 간의 링크
통합 문서 간 링크는 가급적 사용하지 마십시오. 이러한 링크는 연결 속도도 느리고 손상되기 쉬울 뿐 아니라 쉽게 찾아서 수정할 수 없는 경우도 있습니다.
항상은 아니지만 대부분의 경우에는 적은 수의 대형 통합 문서를 사용하는 것이 많은 수의 소형 통합 문서를 사용하는 것보다 효율적입니다. 단, 다시 계산되는 경우가 거의 없어 별도의 통합 문서에 저장하는 것이 적절한 프런트 엔드 계산이 많거나 RAM이 부족한 경우 등은 예외입니다.
닫힌 통합 문서에서 작동하는 단순한 직접 셀 참조를 사용합니다. 이렇게 하면 임의의 통합 문서를 다시 계산할 때 연결된 통합 문서가 모두 다시 계산되는 현상을 방지할 수 있습니다. 또한 Excel이 닫힌 통합 문서에서 읽은 값을 확인할 수도 있는데, 이 기능은 통합 문서 디버깅 및 감사 시에 중요하게 사용되는 경우가 많습니다.
연결된 통합 문서를 반드시 사용해야 하는 경우에는 닫힌 통합 문서가 없도록 모든 통합 문서를 엽니다. 연결 원본 통합 문서를 열기 전에 연결 대상 통합 문서를 먼저 여십시오.
워크시트 간의 링크
워크시트를 많이 사용하면 통합 문서를 보다 쉽게 사용할 수 있지만 일반적으로 다른 워크시트로의 참조를 계산하는 작업은 워크시트 내의 참조에 비해 속도가 느립니다.
Excel 97 및 Excel 2000에서 워크시트와 통합 문서는 개별 계산 체인을 사용하여 이름의 사전순으로 계산됩니다. 따라서 이러한 버전에서는 워크시트 간의 계산 흐름과 일치하는 순서로 워크시트 이름을 지정해야 합니다.
사용되는 범위 최소화
Excel은 메모리를 절약하고 파일 크기를 줄이기 위해 특정 영역에 대한 정보를 해당 영역이 사용되는 워크시트에만 저장합니다. 이러한 방식을 사용되는 범위라고 합니다. 그러나 다양한 편집 및 서식 지정 작업을 수행하면 사용되는 범위가 현재 사용하도록 고려하는 범위를 크게 초과하는 경우가 있습니다. 이로 인해 성능이 저하되고 파일 크기가 제한될 수 있습니다.
Ctrl+End를 눌러 워크시트에서 표시 가능한 사용되는 범위를 확인할 수 있습니다. 범위가 예상보다 초과된 경우에는 마지막으로 사용한 실제 셀 오른쪽과 아래의 모든 행 및 열을 삭제한 다음 통합 문서를 저장해야 합니다. 그 전에 먼저 백업 복사본을 만드십시오. 수식에 포함된 범위가 삭제된 영역으로 확장되거나 삭제된 영역을 참조하는 경우 해당 범위는 크기가 축소되거나 #N/A로 변경됩니다.
추가 데이터 허용
워크시트에 데이터 행이나 열을 자주 추가하는 경우에는 매번 수식을 찾아 변경하는 대신 Excel 수식이 새 데이터 영역을 자동으로 참조하도록 지정하는 방법을 찾아야 합니다.
이렇게 하려면 수식에서 현재 데이터 경계를 초과하여 확장되는 큰 범위를 사용하면 됩니다. 그러나 이 방식을 사용하는 경우 특정 상황에서 계산 효율성이 떨어질 수 있으며, 행과 열을 삭제하면 사용자가 모르는 상태로 범위가 축소될 수 있으므로 수식을 유지 관리하기가 어려워집니다.
구조적 테이블 참조
Excel 2007부터는 구조적 테이블 참조를 사용할 수 있습니다. 이 참조는 참조 대상 테이블이 커지거나 작아지면 자동으로 확장 및 축소됩니다. 이 솔루션에는 다음과 같은 몇 가지 장점이 있습니다.
전체 열 참조, 동적 범위 등의 다른 방식에 비해 성능 면에서 단점이 적습니다.
워크시트 하나에서 여러 데이터 표를 쉽게 사용할 수 있습니다.
표에 포함된 수식도 데이터와 함께 확장 및 축소됩니다.
전체 열 및 행 참조
$A:$A와 같은 전체 열 참조를 사용할 수도 있습니다. 이 참조는 A열의 모든 행을 반환하므로 데이터를 원하는 만큼 추가할 수 있으며 참조에는 데이터가 항상 포함됩니다.
이 솔루션의 경우 다음과 같은 장단점이 있습니다.
대부분의 기본 제공 Excel 기능(SUM, SUMIF)은 열에서 마지막으로 사용된 행을 자동으로 인식하므로 전체 열 참조를 효율적으로 계산합니다. 그러나 SUMPRODUCT와 같은 배열 계산 함수는 전체 열 참조를 처리하지 못하거나 열의 모든 셀을 계산합니다.
사용자 정의 함수는 열에서 마지막으로 사용된 행을 자동으로 인식하지 않으므로 전체 열 참조를 효율적으로 계산하지 못하는 경우가 많습니다. 그러나 마지막으로 사용된 행을 인식하도록 사용자 정의 함수를 쉽게 프로그래밍할 수 있습니다.
워크시트 하나에 데이터 표가 여러 개 있을 때는 전체 열 참조를 사용하기가 어렵습니다.
Excel 2007 이전 버전의 배열 수식은 전체 열 참조를 처리할 수 없습니다. Excel 2007에서는 배열 수식이 전체 열 참조를 처리할 수 있지만, 이 경우 빈 셀을 비롯하여 열의 모든 셀이 계산에 포함됩니다. 특히, Excel 2007부터는 1백만 개의 행을 사용할 수 있으므로 계산 속도가 떨어질 수 있습니다.
동적 범위
명명된 범위의 정의에 OFFSET 및 COUNTA 함수를 사용하면 명명된 범위가 참조하는 영역이 동적으로 확장 및 축소되도록 할 수 있습니다. 예를 들어 정의된 이름을 다음과 같이 작성할 수 있습니다.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
수식에서 동적 범위 이름을 사용하면 새 항목이 포함되도록 범위가 자동으로 확장됩니다.
이 경우 OFFSET은 일시적 함수이므로 항상 다시 계산되고, OFFSET 내의 COUNTA 함수는 여러 행을 확인해야 하므로 성능이 떨어집니다. 수식의 COUNTA 부분을 별도의 셀에 저장한 다음 동적 범위에서 해당 셀을 참조하면 이러한 성능 저하를 최소화할 수 있습니다.
Counts!z1=COUNTA(Sheet1!$A:$A)
DynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
INDIRECT와 같은 함수를 사용하여 동적 범위를 생성할 수도 있습니다. 동적 범위를 사용하는 경우 다음과 같은 장단점이 있습니다.
배열 수식을 통해 수행되는 계산 수를 제한하려는 경우 동적 범위는 정상적으로 작동합니다.
하나의 열에서 여러 동적 범위를 사용하려면 특수 용도의 계산 함수가 필요합니다.
여러 동적 범위를 사용하면 성능이 떨어질 수 있습니다.
조회
조회로 인해 계산에서 중요한 문제가 발생하는 경우가 많습니다. 그러나 여러 가지 방법을 통해 조회 계산 시간을 줄일 수 있습니다. 정확히 일치 옵션을 사용하는 경우 함수 계산 시간은 일치하는 항목이 발견되기 전까지 검사하는 셀 수에 비례합니다. 넓은 범위를 조회하는 경우에는 이 시간이 매우 길어질 수 있습니다.
정렬된 데이터에 대해 VLOOKUP, HLOOKUP 및 MATCH의 유사 일치 옵션을 사용하면 조회 시간이 단축되며 조회 대상 범위의 길이에 따라 조회 시간이 크게 늘어나지 않습니다. 조회 특성은 이진 검색과 동일합니다.
조회 옵션
MATCH, VLOOKUP 및 HLOOKUP의 일치 유형 및 범위 조회 옵션을 파악해야 합니다.
다음 코드 예제에서는 MATCH 함수의 구문을 보여 줍니다. 자세한 내용은 WorksheetFunction 개체의 Match 메서드를 참조하십시오.
MATCH(lookup value, lookup array, matchtype)
Matchtype=1(기본 옵션)은 조회 배열이 오름차순으로 정렬된 경우 조회 값보다 작거나 같은 최대 일치 항목을 반환합니다(유사 일치).
Matchtype=0은 정확한 일치 항목을 요청하며 데이터가 정렬되어 있지 않다고 가정합니다.
Matchtype=-1은 조회 배열이 내림차순으로 정렬된 경우 조회 값보다 크거나 같은 최소 일치 항목을 반환합니다(유사 일치).
다음 코드 예제에서는 VLOOKUP 및 HLOOKUP 함수의 구문을 보여 줍니다. 자세한 내용은 WorksheetFunction 개체의 VLOOKUP 및 HLOOKUP 메서드를 참조하십시오.
VLOOKUP(lookup value, table array, col index num, range-lookup)
HLOOKUP(lookup value, table array, row index num, range-lookup)
Range-lookup=TRUE(기본 옵션)는 조회 값보다 작거나 같은 최대 일치 항목을 반환합니다(유사 일치). 테이블 배열은 오름차순으로 정렬되어 있어야 합니다.
Range-lookup=FALSE는 정확한 일치 항목을 요청하며 데이터가 정렬되어 있지 않다고 가정합니다.
정렬되지 않은 데이터에 대한 조회는 속도가 느리므로 가능하면 수행하지 마십시오. 데이터가 정렬되어 있기는 하지만 정확한 일치 항목을 조회하려는 경우에는 정렬된 데이터(값 누락)를 참조하십시오.
VLOOKUP과 INDEX 및 MATCH 또는 OFFSET 비교
VLOOKUP 대신 INDEX 및 MATCH 함수를 사용하십시오. VLOOKUP은 속도가 약간 빠르고(약 5% 더 빠름) 간편하며 MATCH와 INDEX 또는 OFFSET의 조합에 비해 메모리도 적게 사용합니다. 그러나 MATCH 및 INDEX는 VLOOKUP에 비해 유동적으로 사용할 수 있으므로 시간을 크게 절약할 수 있는 경우가 많습니다. 예를 들어 정확한 MATCH의 결과를 셀에 저장한 다음 여러 INDEX 문에서 다시 사용할 수 있습니다.
INDEX 함수는 일시적 함수가 아니며 속도도 빠르므로 다시 계산이 빠르게 수행됩니다. OFFSET 함수 역시 속도가 빠르지만 일시적 함수이며, 이 함수로 인해 계산 체인을 처리하는 시간이 훨씬 길어지는 경우도 있습니다.
VLOOKUP은 INDEX 및 MATCH로 쉽게 변환할 수 있습니다. 다음의 두 문은 동일한 대답을 반환합니다.
VLOOKUP(A1, Data!$A$2:$F$1000,3,False)
INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)
조회 속도 개선
정확한 일치 조회는 속도가 느릴 수 있으므로 성능 개선을 위해 다음 옵션을 고려할 수 있습니다.
워크시트를 하나만 사용합니다. 같은 시트에 조회와 데이터를 모두 저장하면 속도가 더 빨라집니다.
가능하면 데이터 SORT를 먼저 수행하고(SORT는 속도가 빠름) 유사 일치를 사용합니다.
정확히 일치 조회를 사용해야 하는 경우에는 검사할 셀 범위를 최소한으로 제한합니다. 또한 많은 수의 행이나 열을 참조하는 대신 동적 범위 이름을 사용합니다. 조회의 범위 하한 및 상한을 미리 계산할 수 있는 경우도 있습니다.
정렬된 데이터(값 누락)
많은 행(행 10~20개 이상)을 조회할 때는 유사 일치 두 개를 사용하는 것이 정확히 일치 하나를 사용하는 것보다 속도가 훨씬 빠릅니다.
데이터를 정렬할 수는 있지만 조회 범위에 조회 대상 값이 있는지가 확실하지 않아 유사 일치를 사용할 수 없는 경우에는 다음 수식을 사용하면 됩니다.
IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
VLOOKUP(lookup_val, lookup_array, column, True), "notexist")
이 수식의 첫 부분은 조회 열 자체에 대해 유사 조회를 수행하는 방식으로 작동합니다.
VLOOKUP(lookup_val ,lookup_array,1,True)
조회 열의 대답이 조회 값과 같으면 다음 수식을 사용합니다.
IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,
정확한 일치 항목이 발견되었으므로 유사 조회를 다시 수행할 수 있습니다. 이번에는 원하는 열에서 대답을 반환합니다.
VLOOKUP(lookup_val, lookup_array, column, True)
조회 열의 대답이 조회 값과 일치하지 않으면 해당 값이 누락된 것이며, "noexist"가 반환됩니다.
목록의 최소값보다 작은 값을 조회하는 경우에는 오류가 발생합니다. 이 오류는 IFERROR를 사용하거나 목록에 작은 테스트 값을 추가하는 방식으로 처리할 수 있습니다.
정렬되지 않은 데이터(값 누락)
정렬되지 않은 데이터에 대해 정확히 일치 조회를 사용해야 하는데 조회 값이 있는지가 확실하지 않으면 일치하는 항목이 없는 경우 대개 반환되는 #N/A를 처리해야 합니다. Excel 2007에서는 빠르고 간단한 IFERROR 함수를 사용할 수 있습니다.
IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)
이전 버전의 경우에는 두 개의 조회가 포함된 IF 함수(간단하지만 속도는 느림)를 사용할 수 있습니다.
IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
VLOOKUP(lookupval,table,2,FALSE))
정확한 MATCH를 사용하는 경우 결과를 셀에 저장한 다음 INDEX 수행 전에 결과를 테스트하여 정확한 조회가 이중으로 수행되는 것을 방지할 수 있습니다.
In A1 =MATCH(lookupvalue,lookuparray,0)
In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))
두 셀을 사용할 수 없는 경우에는 COUNTIF를 사용하십시오. 일반적으로 이 함수를 사용하는 것이 정확히 일치 조회를 사용하는 것보다 속도가 빠릅니다.
IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
VLOOKUP(lookupval, table, 2 FALSE))
여러 열에 대한 정확히 일치 조회
저장된 정확한 MATCH를 여러 번 다시 사용할 수 있는 경우가 많습니다. 예를 들어 여러 결과 열에 대해 정확한 조회를 수행하는 경우 VLOOKUP 문을 여러 개 사용하는 대신 MATCH 하나와 INDEX 문 여러 개를 사용하면 시간을 절약할 수 있습니다.
MATCH에 대해 결과를 저장할 열(stored_row)을 하나 더 추가하고 각 결과 열에 대해 다음을 사용합니다.
INDEX(Lookup_Range,stored_row,column_number)
배열 수식에서 VLOOKUP을 사용할 수도 있습니다.
{VLOOKUP(lookupvalue,{4,2},FALSE)}
연속 행 또는 열 집합 조회
단일 조회 작업에서 여러 셀을 반환할 수도 있습니다. 연속하는 여러 열을 조회하려면 배열 수식에서 INDEX 함수를 사용하여 여러 열을 한 번에 반환합니다(열 수로 0을 사용함). INDEX 함수를 사용하여 여러 행을 한 번에 반환할 수도 있습니다.
{INDEX($A$1:$J$1000,stored_row,0)}
위 코드는 이전 MATCH 문을 통해 만들어진 저장된 행에서 A~J열을 반환합니다.
사각형 셀 블록 조회
MATCH 및 OFFSET 함수를 사용하여 사각형 셀 블록을 반환할 수 있습니다.
2차원 조회
두 MATCH 함수(행에 대한 함수와 열에 대한 함수 하나씩)가 포함된 INDEX 함수를 사용하면 테이블의 행과 열을 각각 따로 조회하여 2차원 테이블 조회를 효율적으로 수행할 수 있습니다.
다중 인덱스 조회
큰 워크시트에서는 인덱스를 여러 개 사용해야 하는 경우가 많습니다(예: 특정 국가의 제품 수량 조회). 이렇게 하려면 인덱스를 연결한 다음 연결된 조회 값을 사용하여 조회를 수행하면 됩니다. 그러나 이러한 방식은 다음의 두 가지 이유로 인해 효율성이 떨어집니다.
문자열을 연결할 때는 계산 작업이 많이 수행됩니다.
조회에 큰 범위가 포함됩니다.
따라서 조회의 하위 집합 범위를 계산하는 것이 더 효율적인 경우가 많습니다(예: 국가에 대한 첫 번째 행과 마지막 행을 찾은 다음 해당 범위 내의 제품을 조회하는 등의 방식).
3차원 조회
행과 열 외에 사용할 테이블도 조회하려면 Excel이 테이블을 조회하거나 선택하도록 하는 방식을 중점적으로 고려하여 다음 기술을 사용할 수 있습니다.
조회할 각 테이블(세 번째 차원)이 명명된 구조적 테이블 집합, 범위 이름 또는 범위를 나타내는 텍스트 문자열 테이블로 저장되어 있으면 INDIRECT 또는 CHOOSE 함수를 사용할 수 있습니다.
CHOOSE 및 범위 이름을 사용하는 것도 효율적인 방법입니다. CHOOSE는 일시적 함수는 아니지만 비교적 적은 수의 테이블을 조회하는 데 적합합니다.
INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
위의 예제에서는 TableLookup_Value를 동적으로 사용하여 조회 테이블에 사용할 범위 이름(TableName1, TableName2 등)을 선택합니다.
INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _ MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
이 예제에서는 INDIRECT 함수 및 TableLookup_Value를 사용하여 조회 테이블에 사용할 시트 이름을 동적으로 만듭니다. 이 방법은 간단하면서도 많은 수의 테이블을 처리할 수 있다는 장점이 있습니다. INDIRECT는 일시적 함수이므로 데이터가 변경되지 않아도 매 계산 시 조회가 계산됩니다.
VLOOKUP 함수를 사용하여 테이블에 사용할 텍스트 문자열 또는 시트 이름을 찾은 후에 INDIRECT 함수를 사용하여 찾은 텍스트를 범위로 변환할 수도 있습니다.
INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
개별 테이블을 식별하는 추가 열이 포함된 큰 테이블 하나에 모든 테이블을 집계하는 기술도 사용할 수 있습니다. 이렇게 한 다음 위의 예제에 나와 있는 다중 인덱스 조회 기술을 사용하면 됩니다.
와일드카드 조회
정확히 일치하는 이름을 찾을 때 MATCH, VLOOKUP 및 HLOOKUP 함수에서는 와일드카드 문자 ?(임의의 한 문자) 및 *(문자 없음 또는 임의 수의 문자)를 사용할 수 있습니다. 이 방법을 통해 일치하는 항목이 여러 개 조회되지 않도록 할 수 있는 경우도 있습니다.
배열 수식 및 SUMPRODUCT
배열 수식과 SUMPRODUCT 함수는 매우 유용하지만 사용 시에는 주의해야 합니다. 단일 배열 수식에서 많은 계산을 수행해야 할 수도 있습니다.
배열 수식의 계산 속도를 최적화할 때 핵심적인 사항은 배열 수식에서 계산되는 셀과 식의 수를 가능한 한 최소화해야 한다는 것입니다. 배열 수식은 일시적 수식과 비슷해서 참조 대상 셀 중 하나가 변경되었거나 일시적 셀이거나, 다시 계산된 경우 수식의 모든 셀을 계산하며 계산을 수행하는 데 필요한 모든 가상 셀을 계산합니다.
배열 수식의 계산 속도를 최적화하려면 다음을 수행합니다.
배열 수식의 식 및 범위 참조를 별도의 도우미 열과 행으로 이동합니다. 그러면 Excel에서 스마트 다시 계산 프로세스를 훨씬 더 효율적으로 사용할 수 있습니다.
전체 행이나 필요한 것보다 많은 행/열을 참조하지 마십시오. 배열 수식은 셀이 비어 있거나 사용되지 않아도 수식의 모든 셀 참조를 계산해야 합니다. Excel 2007부터는 1백만 개의 행을 사용할 수 있으므로 전체 열을 참조하는 배열 수식의 경우 계산 속도가 매우 느려질 수 있습니다.
Excel 2007부터는 배열 수식에 의해 계산되는 셀 수를 최소한으로 유지할 수 있는 구조적 참조를 사용합니다.
Excel 2007 이전 버전에서는 가능하면 동적 범위 이름을 사용합니다. 동적 범위 이름은 일시적이기는 하지만 범위 크기를 최소화하므로 사용하는 것이 효율적입니다.
배열 수식이 행과 열을 모두 참조하는 경우에는 주의해야 합니다. 이러한 수식의 경우 사각형 범위가 계산됩니다.
가능하면 해당하는 배열 수식보다 속도가 약간 빠른 SUMPRODUCT를 사용합니다.
조건이 여러 개인 배열 수식 SUM
Excel 2007부터는 가능하면 항상 배열 수식 대신 계산 속도가 훨씬 빠른 SUMIFS, COUNTIFS 및 AVERAGEIFS 함수를 사용해야 합니다.
Excel 2007 이전 버전에서는 여러 조건을 적용한 합을 계산하는 데 배열 수식을 사용하는 경우가 많았습니다. 이러한 계산은 상대적으로 쉽게 수행할 수 있으며, 특히 Excel의 조건부 합계 마법사를 사용하는 경우에는 더욱 쉽지만 대개 속도가 느립니다. 일반적으로는 훨씬 빠른 방법으로 같은 결과를 얻을 수 있습니다. 다중 조건 SUM의 수가 많지 않은 경우에는 DSUM 함수를 사용할 수 있는데, 이 함수는 동일한 배열 수식에 비해 속도가 훨씬 빠릅니다.
배열 수식을 사용해야 하는 경우 계산 속도를 높이는 몇 가지 효율적인 방법은 다음과 같습니다.
동적 범위 이름 또는 구조적 테이블 참조를 사용하여 셀 수를 최소화합니다.
여러 조건을 각 행에 대해 True 또는 False를 반환하는 도우미 수식 열로 분할한 다음 해당 도우미 열을 SUMIF 또는 배열 수식에서 참조합니다. 단일 배열 수식의 경우에는 이 방법을 사용해도 계산 수가 감소하지 않는 것처럼 보이지만, 실제로는 이 방법을 사용하면 대부분의 경우 스마트 다시 계산 프로세스가 도우미 열에서 다시 계산해야 하는 수식만 다시 계산할 수 있습니다.
모든 조건을 단일 조건으로 연결한 다음 SUMIF를 사용할 수 있습니다.
데이터를 정렬할 수 있는 경우에는 행 그룹 수를 계산한 다음 배열 수식이 하위 집합 그룹만 확인하도록 제한하면 효율적입니다.
다중 조건 배열 수식에 SUMPRODUCT 사용
Excel 2007부터는 가능하면 항상 SUMPRODUCT 수식 대신 SUMIFS, COUNTIFS 및 AVERAGEIFS 함수를 사용해야 합니다.
이전 버전의 경우 SUM 배열 수식 대신 SUMPRODUCT 함수를 사용하면 다음과 같은 몇 가지 장점이 있습니다.
SUMPRODUCT의 경우 Ctrl+Shift+Enter를 사용하여 배열로 입력할 필요가 없습니다.
SUMPRODUCT는 보통 속도가 약간 더 빠릅니다(5~10%).
다음과 같이 다중 조건 배열 수식에 대해 SUMPRODUCT를 사용할 수 있습니다.
SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)
이 예제에서 Condition1 및 Condition2는 $A$1:$A$10000<=$Z4와 같은 조건식입니다. 조건식은 숫자가 아닌 True 또는 False를 반환하므로 SUMPRODUCT 함수 내의 숫자로 강제 변환해야 합니다. 이렇게 하려면 빼기 기호 두 개(--)를 사용하거나, 0을 추가(+0)하거나, 1을 곱합니다(*1). --를 사용하는 것이 +0 또는 *1보다 속도가 약간 빠릅니다.
합계를 구할 범위와 조건식에 사용되는 배열 또는 범위의 크기와 모양은 같아야 하며, 이들 배열과 범위는 전체 열을 포함할 수 없습니다.
SUMPRODUCT 내의 항을 쉼표로 구분하지 않고 직접 곱할 수도 있습니다.
SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)
이 방식은 보통 쉼표 구문을 사용하는 것보다 속도가 약간 느리며, 합계를 구할 범위에 텍스트 값이 포함되어 있으면 오류가 발생합니다. 그러나 합계를 구할 범위에 항목을 보다 유동적으로 포함할 수 있습니다(예: 조건에 열이 하나뿐일 때 여러 열 포함).
SUMPRODUCT를 사용하여 범위와 배열 곱하기/더하기 수행
가중 평균 계산과 같이 특정 숫자 범위에 다른 숫자 범위를 곱한 다음 결과의 합을 구해야 하는 경우 SUMPRODUCT에 쉼표 구문을 사용하는 방식의 속도가 배열 입력 SUM에 비해 20~25% 빠를 수 있습니다.
{=SUM($D$2:$D$10301*$E$2:$E$10301)}
=SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
=SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)
위에 나와 있는 세 수식의 결과는 모두 같지만 SUMPRODUCT에 대해 쉼표 수식을 사용하는 세 번째 수식은 나머지 두 수식에 비해 계산에 필요한 시간이 77%에 불과합니다.
배열 및 함수 계산 문제
Excel의 계산 엔진은 범위를 참조하는 배열 수식 및 함수를 사용하도록 최적화되어 있습니다. 그러나 이러한 수식 및 함수가 일반적이지 않은 방식으로 정렬되면 경우에 따라 계산 시간이 크게 길어질 수도 있습니다.
배열 수식 및 범위 함수와 관련된 계산 문제가 발생하면 다음과 같은 항목이 있는지 확인해야 합니다.
부분적으로 겹치는 참조
다른 배열 수식 또는 범위 함수에서 계산되는 셀 블록의 일부분을 참조하는 배열 수식 및 범위 함수. 이러한 현상은 시계열 분석에서 빈번하게 발생할 수 있습니다.
셀을 행 단위로 참조하는 특정 수식 집합을 두 번째 수식 집합이 열 단위로 참조하는 경우
열 블록을 포함하는 단일 행 배열 수식의 대형 집합(각 열 맨 아래에 SUM 함수가 포함됨)
효율적인 함수 사용
함수를 통해 Excel의 기능을 크게 확장할 수 있습니다. 그러나 함수를 사용하는 방식은 계산 시간에 영향을 주는 경우가 많습니다.
범위를 처리하는 함수
SUM, SUMIF, SUMIFS 등 범위를 처리하는 함수의 경우 계산 시간은 합계 또는 개수를 계산하는 데 사용되는 셀의 수에 비례합니다. 사용되지 않은 셀은 확인되지 않으므로 전체 열 참조가 비교적 효율적이지만, 사용되는 셀을 필요한 것보다 많이 포함하지 않도록 해야 합니다. 테이블을 사용하거나 하위 집합 범위 또는 동적 범위를 계산하십시오.
일시적 함수
일시적 함수가 있으면 각 계산에서 다시 계산해야 하는 수식 수가 증가하므로 다시 계산 속도가 느려질 수 있습니다.
보통 OFFSET 대신 INDEX를, INDIRECT 대신 CHOOSE를 사용하면 일시적 함수의 수를 줄일 수 있습니다. 그러나 OFFSET은 속도가 빠른 함수로, 계산을 빠르게 수행하는 독특한 방식으로 사용 가능한 경우가 많습니다.
사용자 정의 함수
C API(XLL 추가 기능 함수)를 사용하며 C 또는 C++로 프로그래밍되는 사용자 정의 함수는 보통 VBA 또는 자동화(XLA 또는 자동화 추가 기능)를 통해 개발되는 사용자 정의 함수에 비해 성능이 우수합니다. 자세한 내용은 Developing Excel 2010 XLLs을 참조하십시오.
XLM 함수 역시 C XLL 추가 기능 함수와 마찬가지로 긴밀하게 연결된 API를 사용하므로 속도가 빠를 수 있습니다. VBA 사용자 정의 함수의 성능은 함수 프로그래밍 및 호출 방식에 크게 좌우됩니다.
속도가 빠른 VBA 사용자 정의 함수
일반적으로는 VBA 사용자 정의 함수를 사용하는 것보다 Excel 수식 계산 및 워크시트 함수를 사용하는 것이 속도가 더 빠릅니다. 각 사용자 정의 함수 호출 시에는 약간의 오버헤드가 발생하며, 정보를 Excel에서 사용자 정의 함수로 전송할 때는 큰 오버헤드가 발생하기 때문입니다. 그러나 사용자 정의 함수를 효율적으로 디자인 및 호출하면 복잡한 배열 수식에 비해 계산을 훨씬 빠르게 수행할 수 있습니다.
Application.Volatile을 불필요하게 추가하는 일이 없도록 워크시트 셀에 대한 모든 참조를 사용자 정의 함수 본문이 아닌 사용자 정의 함수 입력 매개 변수에 포함하십시오.
사용자 정의 함수를 사용하는 수식이 여러 개 필요한 경우에는 수동 계산 모드를 사용하고 해당 계산이 VBA에서 시작되도록 합니다. VBA 사용자 정의 함수에서는 계산이 VBA에서 호출되지 않으면(예: 자동 모드를 사용하거나 수동 모드에서 F9 키를 누르는 경우) 계산 속도가 크게 떨어집니다. 특히 Visual Basic Editor(Alt+F11)가 열려 있거나 현재 Excel 세션에서 VBE를 연 경우에는 속도가 더욱 느려집니다.
다음과 같이 F9 키를 트래핑하여 VBA 계산 서브루틴으로 리디렉션할 수 있습니다. 이 서브루틴을 Thisworkbook 모듈에 추가하십시오.
Private Sub Workbook_Open()
Application.OnKey "{F9}", "Recalc"
End Sub
다음 서브루틴은 표준 모듈에 추가하십시오.
Sub Recalc()
Application.Calculate
MsgBox "hello"
End Sub
자동화 추가 기능(Excel 2002 이후 버전)의 사용자 정의 함수는 통합된 편집기를 사용하지 않으므로 Visual Basic Editor 관련 오버헤드를 발생시키지 않습니다. 자동화 추가 기능에 포함된 Visual Basic 6 사용자 정의 함수의 기타 성능 특성은 VBA 함수와 비슷합니다.
사용자 정의 함수가 범위의 각 셀을 처리하는 경우에는 입력을 범위로 선언하고 배열이 포함된 변형에 범위를 할당한 다음 해당 변형에 대해 루프를 실행합니다. 전체 열 참조를 효율적으로 처리하려면 다음 예제와 같이 입력 범위를 교차 부분에서 사용되는 범위로 나누어 입력 범위 하위 집합을 만들어야 합니다.
Public Function DemoUDF(theInputRange as Range)
Dim vArr as Variant
Dim vCell as Variant
Dim oRange as Range
Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
vArr=oRange
For Each vCell in vArr
If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
Next vCell
End Function
사용자 정의 함수가 워크시트 함수 또는 Excel 개체 모델 메서드를 사용하여 범위를 처리하는 경우에는 일반적으로 모든 데이터를 Excel에서 사용자 정의 함수로 전송하는 것보다 범위를 개체 변수로 저장하는 것이 더 효율적입니다.
Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
col_num As Variant, sorted As Variant, _
NotFound As Variant)
Dim vAnsa As Variant
vAnsa = Application.VLookup(lookup_value, lookup_array, _
col_num, sorted)
If Not IsError(vAnsa) Then
uLOOKUP = vAnsa
Else
uLOOKUP = NotFound
End If
End Function
사용자 정의 함수를 계산 체인 초기에 호출하는 경우 함수에 계산되지 않은 인수를 전달할 수 있습니다. 수식이 포함된 빈 셀에 대해 다음 테스트를 사용하면 사용자 정의 함수 내에서 계산되지 않은 셀을 검색할 수 있습니다.
If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then
각 사용자 정의 함수를 호출할 때와 Excel에서 VBA로 각 데이터를 전송할 때는 시간 오버헤드가 발생합니다. 다중 셀 배열 수식 사용자 정의 함수 하나를 사용하여 여러 함수 호출을 단일 함수(대답 범위를 반환하는 다중 셀 입력 범위 포함)로 결합하면 이러한 오버헤드를 최소화할 수 있는 경우도 있습니다.
SUM 및 SUMIF
Excel의 SUM 및 SUMIF 함수는 많은 수의 셀에 대해 사용되는 경우가 많습니다. 이러한 함수의 계산 시간은 포함되는 셀 수에 비례하므로, 함수가 참조하는 셀의 범위를 최소화해야 합니다.
와일드카드 SUMIF 및 COUNTIF
와일드카드 문자?(임의의 한 문자) 및 *(문자 없음 또는 임의 수의 문자)를 사전순 범위에 대한 SUMIF 및 COUNTIF 조건의 일부분으로 사용할 수 있습니다.
날짜 기간 SUM 및 누적 SUM
날짜 기간 SUM 또는 누적 SUM은 두 가지 방법으로 수행할 수 있습니다. 누적 SUM을 구하려는 숫자가 A열에 있으며 누적 합계는 B열에 포함하려는 경우 다음 중 하나를 수행하면 됩니다.
B열에 =SUM($A$1:$A2) 등의 수식을 작성한 다음 필요한 위치만큼 아래로 끕니다. SUM의 시작 셀은 A1로 고정되지만 마지막 셀은 상대 행 참조이므로 각 행에 대해 자동으로 증가합니다.
B1 셀에는 =$A1, B2 셀에는 =$B1+$A2와 같은 수식을 작성한 다음 필요한 위치만큼 아래로 끕니다. 그러면 행의 숫자를 이전 누적 SUM에 더하여 누적 셀 값이 계산됩니다.
행이 1,000개인 경우 첫 번째 방법을 사용하면 계산이 약 50만 회 수행되지만 두 번째 방법의 경우에는 계산이 약 2천 회만 수행됩니다.
하위 집합 합 구하기
테이블에 대해 정렬된 인덱스가 여러 개 있는 경우(예: 영역 내의 사이트) SUM 또는 SUMIF 함수에서 사용할 행 또는 열의 하위 집합 범위 주소를 동적으로 계산하면 계산 시간을 크게 줄일 수 있습니다.
행이나 열의 하위 집합 범위 주소 계산
각 하위 집합 블록의 행 수를 계산합니다.
각 블록의 개수를 모두 더하여 시작 행을 확인합니다.
시작 행 및 개수 값을 적용한 OFFSET을 사용하여 행의 하위 집합 블록만 포함되는 하위 집합 범위를 SUM 또는 SUMIF로 반환합니다.
소계
SUBTOTAL 함수를 사용하여 필터링된 목록에 대해 SUM을 수행합니다. SUBTOTAL 함수는 SUM과 달리 다음 항목을 무시하므로 유용합니다.
목록 필터링으로 인해 숨겨진 행. Excel 2003부터는 SUBTOTAL이 필터링된 행뿐만 아니라 숨겨진 행을 모두 무시하도록 지정할 수도 있습니다.
다른 SUBTOTAL 함수
D 함수
D 함수 DSUM, DCOUNT, DAVERAGE 등은 동일한 배열 수식에 비해 속도가 훨씬 빠릅니다. 그러나 D 함수를 사용하려면 조건이 별도의 범위에 있어야 하므로 대부분의 경우에는 사용 및 유지 관리가 어렵다는 단점이 있습니다. Excel 2007부터는 D 함수 대신 SUMIFS, COUNTIFS 및 AVERAGEIFS 함수를 사용해야 합니다.
빠른 VBA 매크로
다음 섹션에서는 빠른 VBA 매크로를 만드는 몇 가지 기본적인 팁에 대해 설명합니다.
코드 실행 중에는 반드시 필요한 기능을 제외한 모든 기능 해제
VBA 매크로의 성능을 개선하려면 코드 실행 중에 필요하지 않은 기능을 명시적으로 해제합니다. 코드가 실행된 후에는 다시 계산 또는 다시 그리기를 한 번만 수행하면 되는 경우가 많으므로 기능을 해제하면 성능을 향상시킬 수 있습니다. 코드가 실행되고 나면 기능을 원래 상태로 복원합니다.
일반적으로 VBA 매크로 실행 중에 해제할 수 있는 기능은 다음과 같습니다.
Application.ScreenUpdating 화면 업데이트를 해제합니다. Application.ScreenUpdating이 False로 설정되어 있으면 Excel에서 화면을 다시 그리지 않습니다. 코드가 실행되는 동안에는 화면이 빠르게 업데이트되며 사용자가 각 업데이트를 확인할 필요가 없습니다. 코드가 실행된 후에 화면을 한 번 업데이트하면 성능이 개선됩니다.
Application.DisplayStatusBar 상태 표시줄을 해제합니다. Application.DisplayStatusBar가 False로 설정되어 있으면 Excel에 상태 표시줄이 표시되지 않습니다. 상태 표시줄 설정은 화면 업데이트 설정과는 별개이므로 화면이 업데이트되지 않아도 현재 작업 상태를 표시할 수 있습니다. 그러나 모든 작업의 상태를 표시할 필요가 없는 경우 코드 실행 중에 상태 표시줄을 해제하면 역시 성능이 개선됩니다.
Application.Calculation 수동 계산으로 전환합니다. Application.Calculation이 xlCalculationManual로 설정되어 있으면 Excel에서는 사용자가 계산을 명시적으로 시작할 때만 통합 문서를 계산합니다. 자동 계산 모드에서는 Excel이 계산 시기를 결정합니다. 예를 들어 수식과 관련된 셀 값이 변경될 때마다 수식이 다시 계산됩니다. 계산 모드를 수동으로 전환하는 경우 수식과 연결된 모든 셀이 업데이트될 때까지 기다렸다가 통합 문서를 다시 계산할 수 있습니다. 코드가 실행되는 동안 필요할 때만 통합 문서를 다시 계산하면 성능을 향상시킬 수 있습니다.
Application.EnableEvents 이벤트를 해제합니다. Application.EnableEvents가 False로 설정되어 있으면 Excel에서 이벤트가 발생하지 않습니다. Excel 이벤트를 수신하는 추가 기능이 있는 경우 해당 추가 기능은 이벤트를 기록할 때 컴퓨터의 리소스를 사용합니다. 코드를 실행하는 동안 발생하는 이벤트를 추가 기능이 기록하지 않아도 되는 경우 이벤트를 해제하면 성능이 개선됩니다.
ActiveSheet.DisplayPageBreaks 페이지 나누기를 해제합니다. ActiveSheet.DisplayPageBreaks가 False로 설정되어 있으면 Excel에서 페이지 나누기가 표시되지 않습니다. 코드가 실행되는 동안에는 페이지 나누기를 다시 계산할 필요가 없으며, 코드가 실행된 후에 페이지 나누기를 계산하면 성능이 향상됩니다.
중요
코드가 실행된 후에는 이러한 기능을 원래 상태로 복원해야 합니다.
다음 예제에서는 VBA 매크로가 실행되는 동안 해제할 수 있는 기능을 보여 줍니다.
' Save the current state of Excel settings.
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
' Note: this is a sheet-level setting.
displayPageBreakState = ActiveSheet.DisplayPageBreaks
' Turn off Excel functionality to improve performance.
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' Note: this is a sheet-level setting.
ActiveSheet.DisplayPageBreaks = False
' Insert your code here.
' Restore Excel settings to original state.
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
' Note: this is a sheet-level setting
ActiveSheet.DisplayPageBreaks = displayPageBreaksState
한 번의 작업으로 큰 데이터 블록 읽기 및 쓰기
Excel과 코드 간에 데이터가 전송되는 횟수를 명시적으로 줄여 코드를 최적화합니다. 코드를 한 번에 한 셀씩 반복 실행하여 값을 가져오거나 설정하는 대신, 전체 셀 범위의 값을 한 줄에서 가져오거나 설정합니다. 이렇게 하려면 2차원 배열이 포함된 변형을 사용하여 값을 필요한 대로 저장합니다. 아래 코드 예제에서는 이 두 방법을 비교하여 보여 줍니다.
다음 코드 예제에서는 한 번에 한 셀씩 반복 실행되어 A1:C10000 셀의 값을 가져오고 설정하는 최적화되지 않은 코드를 보여 줍니다. 셀에는 수식이 포함되어 있지 않습니다.
Dim DataRange as Range
Dim Irow as Long
Dim Icol as Integer
Dim MyVar as Double
Set DataRange=Range("A1:C10000")
For Irow=1 to 10000
For icol=1 to 3
' Read the values from the Excel grid 30,000 times.
MyVar=DataRange(Irow,Icol)
If MyVar > 0 then
' Change the value.
MyVar=MyVar*Myvar
' Write the values back into the Excel grid 30,000 times.
DataRange(Irow,Icol)=MyVar
End If
Next Icol
Next Irow
다음 코드 예제에서는 배열을 사용하여 A1:C10000 셀의 값을 동시에 가져오고 설정하는 최적화된 코드를 보여 줍니다. 셀에는 수식이 포함되어 있지 않습니다.
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
' Read all the values at once from the Excel grid and put them into an array.
DataRange = Range("A1:C10000").Value
For Irow = 1 To 10000
For Icol = 1 To 3
MyVar = DataRange(Irow, Icol)
If MyVar > 0 Then
' Change the values in the array.
MyVar=MyVar*Myvar
DataRange(Irow, Icol) = MyVar
End If
Next Icol
Next Irow
' Write all the values back into the range at once.
Range("A1:C10000").Value = DataRange
개체 선택 및 활성화 방지
개체를 선택하여 활성화하는 경우 개체를 직접 참조하는 것보다 많은 처리 작업을 수행해야 합니다. Range 또는 Shape와 같은 개체를 직접 참조하면 성능을 개선할 수 있습니다. 다음 코드 예제에서는 이 두 방법을 비교하여 보여 줍니다.
아래 코드 예제에서는 활성 시트에서 각 도형을 선택한 다음 텍스트를 "Hello"로 변경하는 최적화되지 않은 코드를 보여 줍니다.
For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Select
Selection.Text = "Hello"
Next i
아래 코드 예제에서는 각 도형을 직접 참조하며 텍스트를 "Hello"로 변경하는 최적화된 코드를 보여 줍니다.
For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
Next i
추가 VBA 성능 최적화
아래 목록에는 VBA 코드에서 추가로 사용할 수 있는 성능 최적화 방법이 나와 있습니다.
배열을 Range에 직접 지정하여 결과를 반환합니다.
코드 실행 중에 데이터 형식을 확인(루프에서 여러 번 확인해야 할 수 있음)하는 경우의 오버헤드를 방지하기 위해 명시적 형식으로 변수를 선언합니다.
코드에서 자주 사용하는 간단한 함수의 경우 WorksheetFunction 개체를 사용하는 대신 VBA에서 함수를 직접 구현합니다. 자세한 내용은 속도가 빠른 VBA 사용자 정의 함수를 참조하십시오.
Range.SpecialCells 메서드를 사용하여 코드가 상호 작용하는 셀 수의 범위를 좁힙니다.
XLL SDK의 C API를 사용하여 기능을 구현하는 경우의 성능 개선을 고려합니다. 자세한 내용은 Excel 2010 XLL SDK 설명서를 참조하십시오.
Excel 파일 형식 성능 및 크기
Excel 2007부터는 이전 버전에 비해 훨씬 다양한 파일 형식이 Excel에 포함됩니다. 매크로, 서식 파일, 추가 기능, PDF 및 XPS 파일 형식 변형을 무시하는 경우의 주 형식은 XLS, XLSB 및 XLSX의 세 가지입니다.
XLS 형식
XLS 형식은 이전 버전과 동일한 형식입니다. 이 형식을 사용하는 경우 열은 256개, 행은 65,536개까지만 사용할 수 있습니다. Excel 2007 또는 Excek 2010 통합 문서를 XLS 형식으로 저장하면 호환성 검사가 실행됩니다. 파일 크기는 이전 버전과 거의 비슷하며(일부 추가 정보가 저장될 수 있음) 성능은 이전 버전보다 약간 떨어집니다. XLS 형식에는 Excel에서 셀 계산 순서와 관련하여 수행하는 모든 다중 스레드 최적화가 저장되지 않습니다. 따라서 통합 문서를 XLS 형식으로 저장하고 닫았다가 다시 연 후에 통합 문서 계산을 수행하면 속도가 느려질 수 있습니다.
XLSB 형식
XLSB는 Excel 2007에 처음으로 도입된 이진 형식으로, 많은 이진 파일이 들어 있는 압축 폴더 구조입니다. 이 형식은 XLS 형식보다 훨씬 작지만 압축도는 통합 문서 내용에 따라 크게 달라집니다. 예를 들어 통합 문서가 10개인 경우 크기 감소 비율은 2~8배 정도이며 평균 감소 비율은 4배입니다. Excel 2007부터는 열기 및 저장 속도가 XLS 형식에 비해 약간 느립니다.
XLSX 형식
XLSX는 Excel 2007에 처음으로 도입된 XML 형식으로, Excel 2007부터는 기본 형식으로 사용됩니다. 이 형식은 많은 XML 파일이 들어 있는 압축 폴더로, 파일 이름 확장명을 .zip으로 변경하면 압축 폴더를 열어 해당 콘텐츠를 확인할 수 있습니다. 일반적으로 XLSX 형식은 XLSB 형식보다 더 많은 파일을 작성하지만(평균 1.5배) XLS 파일에 비해 크기는 훨씬 작습니다. 열기 및 저장 시간은 XLSB 파일보다 약간 더 오래 걸립니다.
통합 문서 열기, 닫기, 저장 및 크기 조정
통합 문서 열기/닫기/저장 속도가 계산 속도보가 훨씬 느릴 수도 있는데, 이는 단순히 통합 문서가 크기 때문일 수 있지만 다른 이유가 있을 수도 있습니다.
열기 및 닫기 속도가 느림
통합 문서 중 하나의 열기 및 닫기 속도가 비정상적으로 느린 경우 다음 문제 중 하나가 원인일 수 있습니다.
임시 파일
\Windows\Temp 디렉터리(Microsoft Windows 95, Microsoft Windows 98 및 Microsoft Windows ME) 또는 \Documents and Settings\User Name\Local Settings\Temp 디렉터리(Microsoft Windows 2000 및 Microsoft Windows XP)에는 임시 파일이 누적될 수 있습니다. Excel에서는 통합 문서(특히 열려 있는 통합 문서에 사용되는 컨트롤)에 대해 임시 파일을 만듭니다. 소프트웨어 설치 프로그램에서도 임시 파일을 만듭니다. Excel이 응답하지 않는 경우에는 이러한 임시 파일을 삭제해야 할 수 있습니다.
임시 파일은 너무 많으면 문제를 일으킬 수 있기 때문에 주기적으로 정리해야 합니다. 그러나 소프트웨어를 설치할 때 컴퓨터를 다시 시작해야 하는데 아직 다시 시작하지 않은 경우에는 컴퓨터를 다시 시작한 후에 임시 파일을 삭제해야 합니다.
temp 디렉터리를 쉽게 열려면 Windows 시작 메뉴로 이동하여 시작, 실행을 차례로 클릭한 다음 텍스트 상자에 %temp%를 입력하고 확인을 클릭합니다.
공유 통합 문서의 변경 내용 추적
공유 통합 문서의 변경 내용을 추적하는 경우 통합 문서 파일 크기가 빠르게 증가할 수 있습니다.
조각화된 스왑 파일
Windows 스왑 파일이 있는 디스크의 공간이 충분한지 확인하고 디스크 조각 모음을 주기적으로 수행해야 합니다.
암호 보호 구조의 통합 문서
구조가 암호로 보호된 통합 문서(도구 메뉴에서 보호를 가리킨 다음 통합 문서 보호를 클릭하고 암호(선택 사항) 입력)의 경우 열고 닫는 속도가 선택적 암호를 사용하지 않고 보호된 통합 문서에 비해 훨씬 느립니다.
사용되는 범위 문제
사용되는 범위가 너무 크면 열기 속도가 느려지고 파일 크기가 커질 수 있습니다(특히 높이나 너비가 표준이 아닌 숨겨진 행이나 열로 인해 사용되는 범위가 커지는 경우). 사용되는 범위 문제에 대한 자세한 내용은 사용되는 범위 최소화를 참조하십시오.
워크시트에 컨트롤 수가 많음
워크시트에 확인란, 하이퍼링크 등의 컨트롤이 많으면 사용되는 임시 파일 수도 늘어나므로 통합 문서를 여는 속도가 느려질 수 있습니다. 또한 WAN이나 LAN에서 통합 문서를 열거나 저장할 때 문제가 발생할 수도 있습니다. 이러한 문제가 발생하는 경우에는 통합 문서를 다시 디자인해야 할 수 있습니다.
다른 통합 문서에 대한 링크 수가 많음
가능한 경우 연결 대상 통합 문서를 먼저 연 다음 링크가 포함된 통합 문서를 엽니다. 닫힌 통합 문서에서 링크를 읽는 것보다 통합 문서를 열어 두면 속도가 더 빨라지는 경우가 많습니다.
바이러스 스캐너 설정
일부 바이러스 스캐너 설정으로 인해(특히 서버에서) 문제가 발생하거나 열기/닫기/저장 속도가 느려질 수 있습니다. 이러한 문제가 발생한 것으로 판단되면 바이러스 스캐너를 일시적으로 해제해 보십시오.
계산 속도가 느려 열기/저장 속도가 느려짐
Excel에서 통합 문서를 열거나 저장할 때 다시 계산하는 경우가 있습니다. 통합 문서 계산 시간이 길어 문제가 발생하면 계산을 수동으로 설정하고 저장 전 다시 계산 옵션(도구 메뉴에서 옵션, 계산을 차례로 선택)을 해제해 보십시오.
도구 상자 파일(.xlb)
도구 상자 파일의 크기를 확인합니다. 일반적인 도구 상자 파일의 크기는 10~20KB입니다. Windows 검색에서 *.xlb를 검색하면 XLB 파일을 찾을 수 있습니다. XLB 파일은 사용자마다 고유하며, 도구 상자를 추가/변경/사용자 지정하면 toolbar.xlb 파일이 커질 수 있습니다. 이 파일을 삭제하면 모든 도구 상자 사용자 지정 내용이 제거되므로, 이름을 "toolbar.OLD"로 바꾸는 것이 안전합니다. 다음 번에 Excel을 열면 새 XLB 파일이 만들어집니다.
기타 성능 최적화
다음 섹션에서는 성능을 개선할 수 있는 기타 영역에 대해 설명합니다.
피벗 테이블
피벗 테이블을 사용하면 많은 데이터를 효율적으로 요약할 수 있습니다.
합계(최종 결과)
합계와 소계를 통합 문서의 최종 결과 일부분으로 생성해야 하는 경우 피벗 테이블을 사용해 보십시오.
합계(중간 결과)
피벗 테이블은 요약 보고서를 생성하는 데 효율적이지만, 다음과 같은 조건을 보장할 수 있는 경우가 아니면 계산 체인에서 중간 합계 및 소계로 피벗 테이블 결과를 사용하는 수식을 만들어서는 안 됩니다.
계산 중에 피벗 테이블이 정상적으로 새로 고침된 경우
피벗 테이블이 변경되지 않아 정보가 계속 표시되는 경우
피벗 테이블을 중간 결과로 사용하려는 경우에는 GETPIVOTDATA 함수를 사용하십시오.
조건부 서식 및 데이터 유효성 검사
조건부 서식과 데이터 유효성 검사는 유용한 기능이기는 하지만 많이 사용하면 계산 속도가 크게 느려질 수 있습니다. 셀이 표시되는 경우 각 계산에서, 그리고 조건부 서식이 포함된 셀 표시를 새로 고칠 때 모든 조건부 서식 수식이 계산됩니다. Excel 개체 모델에는 Worksheet.EnableFormatConditionsCalculation 속성이 포함되어 있으므로 조건부 서식 계산을 사용하거나 사용하지 않도록 설정할 수 있습니다.
정의된 이름
정의된 이름은 Excel에서 가장 유용한 기능 중 하나이며 추가 계산 시간이 필요하지 않습니다. 다른 워크시트를 참조하는 이름을 사용하는 경우 계산 프로세스가 더 복잡해집니다. 또한 중첩된 이름(다른 이름을 참조하는 이름)도 가능하면 사용하지 않아야 합니다.
이름은 이름을 참조하는 수식이 계산될 때마다 계산되므로 정의된 이름에는 계산을 많이 수행하는 수식이나 함수를 가급적 포함하지 않아야 합니다. 계산을 많이 수행하는 수식이나 함수를 다른 위치에 있는 별도의 셀에 포함하여 해당 셀을 직접 또는 이름을 사용하여 참조하면 속도가 훨씬 빨라질 수 있습니다.
자주 사용되지 않는 수식
대부분의 통합 문서는 입력 데이터를 계산에 적합한 모양으로 가져오는 작업과 관련되어 있거나 데이터 크기 또는 모양 변경 방지 수단으로 사용되는 수식과 조회를 다수 포함합니다. 특정 수식 블록을 자주 사용하지 않는 경우에는 특수한 값을 복사한 다음 붙여 넣어 해당 수식을 일시적으로 지우거나 자주 열지 않는 별도의 통합 문서에 수식을 저장할 수 있습니다. 수식이 값으로 변환된 것을 확인하지 못해 워크시트 오류가 발생하는 경우가 많으므로, 별도의 통합 문서를 사용하는 방식이 보다 적절할 수 있습니다.
충분한 메모리 사용
32비트 Excel은 최대 2GB RAM을 사용할 수 있지만, Excel을 실행하는 컴퓨터에도 메모리 리소스가 필요합니다. 따라서 컴퓨터의 RAM이 2GB뿐인 경우 일부 메모리는 운영 체제 및 실행 중인 다른 프로그램에 할당되므로 Excel은 2GB를 모두 사용할 수 없게 됩니다. 32비트 컴퓨터에서 Excel 성능을 최적화하려면 RAM이 3GB 이상인 컴퓨터를 사용하는 것이 좋습니다.
64비트 Excel의 경우에는 2GB 제한이 적용되지 않습니다. 자세한 내용은 Excel 2010 성능: 성능 및 제한 개선의 큰 데이터 집합 및 64비트 Excel 섹션을 참조하십시오.
결론
이 문서에서는 흔히 발생하는 문제를 방지하고 성능을 개선하기 위해 링크, 조회, 수식, 함수, VBA 코드 등의 Excel 기능을 최적화하는 방법에 대해 설명했습니다.
저자 정보
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에 대한 자세한 내용은 다음 자료를 참조하십시오.