수메리안 선생님 안녕하세요. 날씨가 추워졌는데 강녕하셨습니까?

INDEX(A1:A9999, ...) 수식이 10번 반복될 때, 엑셀이 A1:A9999라는 범위를 10번 모두 "새롭게 인식하고 계산"하는 비효율이 발생한다고 판단하셨고
이에 따라 반복되는 범위(A1:A9999)를 마치 "변수(Variable)"처럼 하나의 고정된 객체(예: C1 같은 특정 공간)에 담아두어 각 수식이 범위를 매번 새로 읽는 것이 아니라, 이미 정의된 객체를 "참조(Reference)"만 함으로써 계산 속도를 높이고자 합니다.

네. 맞습니다. 제 질문을 정확히 요약해주셨네요. 제가 이런 생각을 하게된 이유는 그동안 시트 여기저기 위치한 값들 중에서 참조할 값들을 어떻게 빨리 찾을수 있을까 궁리하다가 vlookup, counta 등 여럿을 시도해봤는데 그중 가장 나은 것이 INDEX/Match 라는 결론을 냈습니다. 속도체감을 딱히 느끼지는 못하지만 엄청난 대용량일때에는 그나마 가볍고 빠른것 같아요. 그런데 INDEX 수식을 자주 사용하다가 참조횟수가 늘면서 =INDEX(A:A, 3) 도 부하가 걸리니 결국 =INDEX(A1:A9999, 3)  와 같이 범위를 지정해주어 속도향상을 꾀할 수 있다는 힌트를 인공지능으로부터 얻었습니다. 값이 어디 있는 지 명확한 구역 범위로 제한되어 있다면 충분히 이렇게 개선하는 것이 맞을 겁니다. 인공지능이 알려준 첫째 전략으로 나오는 것은 [ 참조범위를 최소화하여 불필요계산을 줄인다 ] 인데요. 아래와 같았습니다.

위와 같이 대답하니 제 결론은 =INDEX(A:A, 3) 라면 먼저 이 함수는 A:A 를 카운트한다는 오해(?) 를 했던 것 같습니다. =INDEX(A1:A9999, 3) 라면 카운트 횟수가 줄어드니 빨라진 결과를 낳게 되구요. 이 둘의 차이는 이것 외에는 없으니 속도차이는 당연히 이 카운트횟수에서 기인한다는 결론을 내게된 건데요. 지금 생각해보니 제가 제대로된 헛발질만 하고 있었다는 것을 실감하네요. 선생님 설명을 듣고보니  INDEX 는 배열로 바로 그 번호로 점프하는 방식을 사용하고 있었다니 아~ ㅠ 제가 머리가 띵해지네요. INDEX를 직관적으로 따지지않고 너무 심도있게 궁리한 것이 아닌가 지금와보니 후회가 듭니다. 

직관적으로 생각해도 INDEX가 이렇게 무식하게(전체를 카운트한다는게 좀 무식하잖아요) 만들어졌을 리가 없을텐데 왜 인공지능은 저런 대답을 할까? 였구요. 그런데 저는 내심...이럼에도 불구하고 참 엑셀이 빠르기도 빠르네~   감탄스럽기도 하고  1,048,576번째 행까지 검사하는 것이 만만치 않을텐데 어찌 이리도 빠를까? 이게 [ 셀수식은 인터프리터 방식이 아니고 완전 컴파일화 수행되어 com 과 직접 통신하는 방식의 위대함인가? 사실상의 속도 = 0 에 수렴된 극한의 속도향상이라고도 생각하기도 했었습니다. 그런데 이런 셀수식이 100만개가 있다면 만만치 않을텐데~ 어떻게 순식간에 계산이 될까? 라는 의심도 있었습니다.

 INDEX 자체는 범위 크기로 인해 느려지지 않지만 범위를 A:A처럼 너무 크게 잡으면 전체 계산 체인이 커져서
엑셀의 “종속성 트리(dependency tree)” 분석이 느려지는 것입니다,

인공지능이 이런 설명을 해줬어야 했는데, 원리를 가르치지 않고 곁가지 정보로 결론만 알려주니 제가 여기까지 잘못 인도된것 같네요. 

먼저 선생님께서 제시해주신 방법을 시도해 봤습니다. 

이름: MyData
참조 대상: =Sheet1!A$1:Sheet1!A$9999
셀에는
=INDEX(MyData, A$1-1)

아~ 역시 이 방법이 있었군요. MyData 의 정의는 딱 1번만 하고, 셀수식에서 필요할 때마다 불러주기만 하면 되니까요. 네. 제가 원했었던 방식입니다. 범위를 이름으로 정의하는 방식은 익이 들었는데 왜 이걸 생각하지 못했을까? 약방에 감초가 넘쳐나도 정작 제가 아플 땐 그 약초를 달여먹을 생각은 못하고 혼자 끙끙 앓던 바보였네요. 

=LET(
r, Sheet1!$A$1:$A$9999,
INDEX(r, A$1-1)
)

그런데 위 방식은 좀 의문이 듭니다. 저는 선생님의 설명을 듣고 [ C1 셀수식에서 저렇게 r 을 LET 으로 1번 정의해주면 다른 셀 A2 에서는  그냥 r 만 불러주면 되는건가?  LET 없이도.. ] 라는 생각이 문득 들었습니다. VBA 전역변수처럼 셀수식에서도 이쪽저쪽 전역화된 방식이 도입된건가? LET이라는 함수를 알아보니 엑셀365버전부터 생긴 최신기법이라고 나와있더라구요. 그래서 제가 

=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-10))---> A1에서 1번만 r 정의
=INDEX(r,A$1-9)   ---> 이하, 정의(반복동작)할 필요없이 r 만 불러줌
=INDEX(r,A$1-8)
=INDEX(r,A$1-7)
=INDEX(r,A$1-6)
=INDEX(r,A$1-5)
=INDEX(r,A$1-4)
=INDEX(r,A$1-3)
=INDEX(r,A$1-2)
=INDEX(r,A$1-1)

... 이라고 하니 첫번째 수식은 잘 계산이 되고 2번째부터는 수식이 그냥 셀에 보여지네요. 계산값이 보이지 않아요.

여기서 r 변수는 한 번만 평가되어 여러 번 사용해도 재계산이 일어나지 않고,
셀 내부에서 범위를 변수처럼 다룰 수 있어 매우 빠르고 최적화되어 있습니다.

선생님께서도 한번만 평가되어 재계산이 일어나지 않는다고 말씀하셨는 어찌된 일일까요. 여기 오빠두에 올려진 예제파일도 살펴보았습니다. 

=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-10))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-9))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-8))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-7))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-6))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-5))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-4))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-3))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-2))
=LET(r,Sheet1!A$1:Sheet1!A$9999,INDEX(r,A$1-1))

근데 이렇게 하면 계산값이 잘 보여지긴 합니다. 그런데 이렇게하면 LET 정의를 10번 한다는 건데요. 이러면 결국 또 느려지지 않나요? 이건 [ Sheet1!A$1:Sheet1!A$9999 라는 반복동작을 줄여 속도향상을 꾀한다는 전략을 세울려고 했었는데, 그걸 반복하지 않지만, LET(r,Sheet1!A$1:Sheet1!A$9999,   ...   라는 새로운 반복동작 ] 이 생겨버렸잖아요. LET 정의를 셀마다 1번씩 총 10번하게 되잖아요. 만약 이걸 A1셀에서 1번만 수행하고 다른 셀에서는 수행할 필요없다면 좋을텐데요. 이런 셀이 100만개 있는 파일에서는 엄청난 속도향상이 생길텐데요. 제 이해가 맞나요?  

선생님께서 더불어 알려주신 LAMBDA 는 좀더 공부를 해야할 것 같습니다. 어쨌든 선생님의 친절하신 설명에 고개숙여 감사드립니다. 좋은 밤 되세요. 

안녕하세요. 네. 저는 개발자가 아닙니다. 저는 은퇴해서 놀다가 1년 전에 조그만 부업일을 시작했는데요. 일을 하다보니, 이런 작업은 탁상용 계산기를 두드리는 것보다 엑셀이라는 걸 두드리면 좀더 나을 것 같다는 생각이 들어 어찌저찌 공부를 시작하게 되어 여기까지 오게된 것 같습니다. 엑셀을 알아가면 알아갈 수록 퍼즐조각을 맞추는 것처럼 재미를 느끼니 아마 이런 기분은 우리 선조님들이 이미 간파하셨던, 배우고 때때로 익히는 재미를 알면 이 또한 기쁘지않을 수 없다는 것을 경험하게 됩니다. 그러나 지식이 일천하여 여러 선배님들에게 조언을 구하려 여기저기를 기웃거리고 있습니다. 심봉사가 앞을 못보더라도 심청이를 안고 알음알음 이웃들에게 길을 물어 젖동냥을 떠나는 심정으로 물어봅니다.  너그럽게 이해해주시면 감사드리겠습니다. 

안녕하세요. 금과옥조와 같은 질타와 댓글에 먼저 고개숙여 감사드립니다. 

제가 무엇을 우선해야 하고 어떤 순서로 문제해결을 해야할 지를 잘 알려주신 것 같습니다. 체계적으로 공부하지 못하고 문제가 생길 때마다 그것을 해결하는 방식은 부분에서 전체를 이해하려는 시도이니 이는 분명히 잘못되었다는 것을 저도 느끼고 있었습니다. 저도 이제서야 INDEX에 대해서 좀 알게 되었는데, 인공지능이 A:A열 보다는 A1:A9999 로 범위를 잡아야 수행속도가 더 빠르다고 대답헤주니, 저는 당연히 INDEX 구동원리 안에는 A열을 카운트하는 과정이 있을 것이라는 추측을 하게된 건데, 이는 잘못된 추측이고 여기서부터 방향이 틀어져버린 것 같습니다.

선생님 말씀처럼 A1:A9999 는 연산도 아니고, 논리식도 아니고, 함수안에 존재하는 내장(?)함수도 아니고, 쉽게 말해 시간잡아먹을 것이 없는, 그냥 참조 그 자체일 뿐인데, 인공지능은 왜 A:A 보다 A1:A9999 가 더 빠르다고 대답할까? 그에 대한 의문도 있었습니다. 선생님 말씀을 들어보니 INDEX 부류, COUNTIF 부류로 분리하여 따져야할 문제인 것 같습니다. 저는 선생님 설명을 듣고 이렇게 이해했는데 맞나요? Criteria Range 에 대한 용어도 찾아보니 제 이해가 맞는 것 같기도 하구요.  COUNTIF 부류들은 A:A 일때는 전체 열을 검사하고, INDEX 부류는 A:A 라도 참조사이즈를 따지지 않는다로 이해했습니다. 그렇다면 결국 첫 원점으로 되돌아가야 할 것 같습니다. 궁리할 필요가 없는 것을 저는 궁리하고 있었다고 봐요. 범위지정 크기를 줄여 INDEX 속도향상을 꾀한다는 전제가 이미 잘못된 것 같아요. INDEX는 참조의 크기를 따지지 않고 배열로 바로 접근하는 방식을 쓰고 있다는 것을 이제 잘 알았습니다.

선생님께서 알려주신 상대참조, 분산배열방식, 동적배열 등에 개념을 명확하게 인식하고 공부해나가야할 것 같습니다. 참 좋은 말씀을 많이 해주신것 같은데 제가 아직 이쪽 지식이 부족하여 뭐라 대답해야할 지 잘 모르겠습니다. 노트에 적어놓아 꼭 나중에 학습하도록 하겠습니다. 제가 부족함이 많다는 걸 느낍니다. 따끔하게 편달해주셔서 감사드립니다.  좋은 주말 보내세요.