엑셀 자동 고침으로 함수 단축키 만드는 방법

'자동 고침'으로 XLOOKUP 함수 단축키 등록하기

실무에서는 매번 같은 구조의 템플릿을 받아, 동일한 구조의 수식을 복사 -> 붙여넣어 작업할 때가 많습니다. 그럴 때, 엑셀의 '자동 고침' 기능을 활용하면 자주 쓰는 함수를 원하는 문자로 저장해 두고, 그 문자만 입력해도 함수가 자동으로 완성되도록 만들 수 있습니다.

1. 예제파일을 실행한 후, 판매일자 셀을 클릭하고 아래와 같이 XLOOKUP 함수를 입력합니다. 그러면 조회 대상에서 조회 대상 번호를 바꿀 때마다 판매일자부터 매출금액까지 한 번에 검색되는 보고서가 완성됩니다.

   =XLOOKUP(G2,A:A,B:E)

   
   

   오빠두Tip : XLOOKUP 함수는 엑셀 2021 이후 버전에서 제공됩니다.
2. 이제 이 완성된 수식을 복사한 후, 이번 달, 다음 달에도 반복해서 사용할 수 있는 함수 단축키로 등록합니다.
   
3. 파일 탭을 클릭한 후 옵션으로 이동합니다. 이후, 언어 교정 탭에서 자동고침 옵션을 클릭하면 입력 값과 결과 값을 지정해, 원하는 문자를 입력했을 때 등록된 수식이 자동으로 나오도록 설정할 수 있습니다.
   
4. 입력 값에는 ="검색" 을 입력하고, 결과에는 방금 복사한 수식이 출력되도록 붙여넣습니다.
   
5. 이번에 붙여넣은 XLOOKUP 함수의 경우, 일반적으로 검색 범위와 결과 범위는 고정하고 찾는 값이 바뀝니다. 따라서, 매번 바뀌는 검색할 값의 셀 주소를 지우고 그 자리에 @ 같은 기호를 넣습니다. 이렇게 하면 이후 셀을 직접 선택해 편리하게 넣을 수 있습니다.
   
6. 함수를 모두 입력했으면 추가 -> 확인 버튼을 차례대로 눌러 함수 단축키 등록을 마칩니다.
7. 기존에 작성했던 수식은 지우고, 셀에 등호와 검색(=검색)을 입력한 뒤 엔터를 누르면 안내창이 나타납니다. 이때 다시 한번 엔터를 누르면 "@" (-> 바꿀 셀 주소)가 바로 선택되며, 검색할 대상 셀을 클릭해 함수를 편리하게 입력할 수 있습니다.
   
8. 이렇게 등록한 단축키는 다음 달에도 검색할 셀만 바꿔 클릭하면 동일한 방법으로 보고서를 만들 수 있습니다.

표의 구조적 참조와 GROUPBY로 만드는 자동 집계 보고서

이전에 등록했던 XLOOKUP 함수 단축키의 경우, 셀 주소가 A5, B:B, C:C 와 같이 일반 범위로 고정되어 있어서 양식이 조금만 바뀌어도 잘못 계산될 수 있습니다. 이 때, 참조 범위를 표로 바꾸면 데이터 구조가 바뀔 때에도 안정적으로 사용할 수 있습니다.

1. 예제 파일의 11월 시트로 이동한 뒤 표로 만들 범위를 클릭하고, 단축키 Ctrl+T를 눌러 표 만들기를 실행합니다. 이번 강의에서는 표 이름을 '매출'로 지정했습니다.
   
   
   오빠두Tip : 표 이름은 오른쪽 상단 테이블 디자인 탭의 표 이름 항목에서도 바꿀 수 있습니다.
2. 이제 표를 기준으로 범위를 참조합니다. 표 이름 뒤 대괄호 안에 참조할 머리글을 입력(예: 매출[매출금액]) 하면 표의 특정 열을 동적으로 참조할 수 있으며, 이러한 참조 방식을 구조적 참조라고 부릅니다. 이렇게 참조하면 범위의 위치가 바뀌거나 열이 추가·삭제되어도 머리글을 기준으로 값을 동적으로 불러올 수 있습니다.
   
3. 이제 이 구조적 참조와 GROUPBY 함수를 활용해 집계 보고서를 만듭니다. 시트에서 비어있는 셀을 클릭한 후, 다음과 같이 GROUPBY 함수를 작성합니다.

   =GROUPBY(매출[구분],매출[매출금액],SUM)

   
   

   오빠두Tip : GROUPBY 함수는 엑셀 2024 이후 버전에서 제공됩니다.
4. 이제 완성된 수식을 드래그해서 복사한 뒤 이전가 동일하게 파일 탭 -> 언어 교정 -> 자동고침 옵션으로 이동해 함수 단축키를 등록합니다. 이번에는 "=보고서"라는 함수 단축키로 등록했습니다.
   
5. 이렇게 등록해 두면 새로운 데이터가 추가될 때마다(예: 12월 데이터) 그 범위를 매출이라는 이름의 표로 만든 뒤 보고서 함수를 실행하는 것만으로 집계 보고서를 편리하게 완성할 수 있습니다.
   

범위를 표로 변환할 때 주의할 점

표를 사용할 때 한 가지 주의할 점이 있습니다. 표의 이름은 파일 안에서 한 번만 쓸 수 있기 때문에, 같은 이름을 다른 시트에서 다시 사용할 수 없습니다.

1. 예를 들어 한 시트에서 이미 '매출표'라는 이름의 표를 만들었다면, 다른 시트에서 같은 이름으로 표를 다시 만들 때 오류가 발생하는 것을 확인할 수 있습니다.
   
2. 이러한 경우, 기존 표를 일반 범위로 바꾸면 됩니다. 기존 표를 선택한 후, 우측 상단에 테이블 디자인 탭을 클릭한 후 범위로 변환을 선택하면 일반 범위로 복구됩니다.
   
   
   오빠두Tip : 만약 표의 서식까지 함께 지우고 싶다면, 테이블 디자인에서 스타일을 없음으로 바꾼 다음 범위로 변환하면 서식이 제거된 일반 범위로 바꿀 수 있습니다.
3. 그 다음 새로운 데이터를 다시 같은 이름의 표로 만들어 사용합니다. 기존 표가 일반 범위로 바뀌면서 이를 참조하던 수식도 함께 업데이트되기 때문에, 오류 없이 계속 사용할 수 있습니다.