들어가기 전에
어떤 소프트웨어든 사용하면 할수록 더 많은 유용한 정보와 단축키를 발견하게 되는데 Excel은 더욱 그렇습니다. 수십 년 동안 전세계적으로 가장 널리 사용되는 스프레드시트 프로그램이고, 버전이 업그레이드될 때마다 새로운 함수나 기능들이 추가됩니다. Excel을 능숙하게 사용하기 위해 없어서는 안 될 5가지 기능들 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 5 Microsoft Excel Tricks I Couldn't Live Without
- URL: https://www.howtogeek.com/microsoft-excel-tricks-couldnt-live-without/
1. 동적 배열을 정적 값으로 붙여넣기
FILTER, SEQUENCE, RANDARRAY 등과 같은 함수는 동적 배열을 생성합니다. 동적 배열은 수식을 입력한 위치부터 인접한 셀까지 결과가 확장되는 현상입니다. 이러한 동작을 스필링( spilling )이라고 합니다. 동적 배열은 기존 Ctrl + Shift + Enter(CSE) 배열 수식을 대체하며, 확장된 결과가 포함된 셀 주변에 파란색 선이 표시되면 동적 배열임을 알 수 있습니다.
최신 동적 배열 함수를 포함한 최신 Excel 기능을 사용하려면 Excel 365나 웹용 Excel을 사용해야 합니다.
때로는 동적 배열에 표시되는 값을 계속 재계산하고 업데이트하는 대신 고정하고 싶을 수도 있습니다. 예를 들어, DATE 함수와 SEQUENCE 함수를 사용하여 C9, C10, C11 셀의 매개변수에 따라 날짜의 동적 배열을 만들었는데, 날짜를 더 이상 변경하지 않고 그대로 두고자 합니다.
해당 영역을 범위로 지정하고 Ctrl + C를 눌러 복사합니다.
그런 다음 같은 셀을 선택한 상태에서 Ctrl + Shift + V를 눌러 값만 붙여넣습니다. 날짜는 이전과 동일하게 표시되지만, 셀 중 하나를 선택하면 파란색 선이 표시되지 않고 수식 입력줄에 수식이 아닌 단일 값이 표시되므로 이 방법이 제대로 작동했음을 알 수 있습니다.
이 기술은 동적 배열이 테이블 열로 확장될 수 없기 때문에 데이터를 Excel 테이블로 포맷 하려는 경우에 특히 유용합니다.
2. 데이터 유효성 검사 규칙 즉시 복제하기
복잡한 수식을 사용하여 사용자 지정 데이터 검증 규칙을 만들어서 C열에 입력된 회사 코드가 문자 다음에 숫자 4개로 구성되도록 했다고 가정합니다.
이제 다른 워크시트에서 동일한 데이터 유효성 검사 규칙을 사용하고자 합니다.
하지만 규칙을 다시 입력하면 시간이 너무 오래 걸리고, 셀의 서식이 다르기 때문에 복사해서 붙여넣는 것은 바람직하지 않습니다.
이럴 때 기존 수식을 재정의하거나, 데이터를 수정하거나, 서식을 변경하지 않고도 한 셀이나 범위에서 다른 셀이나 범위로 데이터 유효성 검사 규칙을 복사할 수 있는 방법이 있습니다.
(1) 데이터 유효성 검사 규칙이 이미 포함된 셀 하나 또는 모든 셀을 선택하여 복사합니다.
(2) 규칙을 복제할 셀을 선택하고 Ctrl + Alt + V를 눌러 [선택하여 붙여넣기] 대화 상자를 엽니다.
(3) [유효성 검사] 항목을 선택하고 [확인]을 클릭합니다.
이렇게 하면 데이터 유효성 검사 규칙이 복제되어 다른 모든 항목은 그대로 유지됩니다. 유효한 항목과 유효하지 않은 항목을 입력하여 제대로 작동하는지 확인해 보세요.
3. 범위에서 선행 또는 후행 공백 다듬기
Excel 수식에서 앞뒤 공백을 제거하면 데이터가 자동으로 축소 및 확장되어 수식을 더욱 동적이고 깔끔하게 만들 수 있습니다. 특히 Excel 표와 호환되지 않는 동적 배열 수식에 이 기능이 유용합니다.
이 예제에서는, FILTER 함수를 사용하여 50회 이상 출전한 모든 선수와 해당 선수가 속한 팀을 추출했다고 가정합니다.
선수가 50회 이상 출전하면 소속 팀 색상이 새겨진 유니폼을 받게 됩니다. 각 선수가 어떤 유니폼을 입고 있는지 추적하는 것이 목표입니다. 이를 위해 H2 셀에 동적 XLOOKUP 수식을 사용합니다.
=XLOOKUP(G2:G100, $J$2:$J$7, $K$2:$K$7)
여기서 G2:G100은 플레이어 팀을 포함하는 셀이며, 성장을 허용하기 위해 맨 아래에 추가 셀이 포함되고, $K$2:$K$7은 조회 배열이고, $L$2:$L$7은 반환 배열입니다.
하지만 Enter 키를 누르면 G열의 해당 셀에 팀이 없기 때문에 H12 셀 아래쪽에 #N/A 오류가 나타납니다.
이 문제를 해결하는 한 가지 방법은 XLOOKUP 함수의 if_not_found 인수에 두 개의 큰따옴표를 입력하는 것입니다. 이렇게 하면 오류가 빈 셀로 덮어쓰게 됩니다. 그러나 XLOOKUP 수식은 여전히 현재 데이터 범위를 벗어나는 동적 배열을 반환합니다. 이는 보기에도 깔끔하지 않을 뿐만 아니라 메모리를 낭비합니다.
대신 수식에 신중하게 배치된 마침표를 추가하는 것이 해결책입니다. 이 맥락에서 마침표는 trim ref 연산자로 알려져 있습니다. 콜론 앞에 추가하면 앞쪽 공백을 제거하고, 콜론 뒤에 추가하면 뒤쪽 공백을 제거하며, 둘 다 추가하면 앞뒤 공백을 동시에 제거할 수 있습니다.
트리밍 참조 연산자는 Microsoft 365용 Excel, 웹용 Excel, Excel 모바일 및 태블릿 앱 사용자만 사용할 수 있습니다.
트리밍 참조 연산자는 수식의 콜론 뒤에 삽입되며, 이는 동적 배열이 H11 셀에서 멈춤을 의미합니다.
=XLOOKUP(G2:.G100, $J$2:$J$7, $K$2:$K$7)
하지만 다른 플레이어가 50회 이상 출연하면 FILTER 함수에서 생성된 F열과 G열의 동적 배열이 한 행 아래로 확장되고, 잘린 XLOOKUP 함수에서 생성된 H열의 동적 배열도 같은 작업을 수행합니다.
4. 인쇄 시 열 머리글 반복
ID, 성별, 국가, 부서를 기록한 표가 포함된 Excel 워크시트가 있습니다. 데이터 행 수가 1000개 쯤 된다고 생각해 보세요.
이 표를 인쇄해 보면 열 머리글이 첫 페이지에만 인쇄되고 그 다음 페이지에는 인쇄되지 않습니다. 따라서 첫 페이지를 지나면 인쇄물이 혼란스러울 수 있으며, 각 열이 무엇을 나타내는지 헷갈리게 됩니다. 이 문제를 해결하려면 인쇄물에서 열 머리글을 반복해서 표시하면 됩니다.
(1) [페이지 레이아웃] 탭 - [인쇄 제목]을 선택합니다.
(2) "시트" 탭을 열고 "맨 위에서 반복할 행"이라는 필드에 커서를 놓습니다.
(3) 열 머리글을 클릭하면 Excel에서 자동으로 전체 행에 대한 참조를 입력합니다. 즉, 선택한 행의 모든 내용이 인쇄된 각 페이지에 반복되는 머리글로 간주됩니다.
(4) "확인"을 클릭하여 대화 상자를 닫은 후 Ctrl+P를 누르거나 [파일] - [인쇄]를 클릭하여 인쇄 미리보기를 엽니다. 이제 미리보기를 스크롤하면 각 페이지에 열 머리글이 반복되는 것을 볼 수 있습니다.
마치며
Excel에서 더욱 활기차고 효율성을 높이는 한 가지 방법은 가능한 한 단축키를 사용하는 것 입니다. 단축키를 사용하면 키보드와 마우스를 자주 전환할 필요가 없기 때문입니다. Excel 단축키와 관련해서는 [여기]를 참고하세요.
'Excel' 카테고리의 다른 글
| [강의 예고] 이 4개 함수면 데이터 정리가 놀랍도록 쉬워집니다 (0) | 2026.01.18 |
|---|---|
| 왜 이 엑셀 수식에는 대괄호가 두 개씩 있나요? (0) | 2026.01.13 |
| [강의 예고] 엑셀 누적 계산의 끝판왕, SCAN 함수로 끝내기 (0) | 2026.01.11 |
| 여러분의 두뇌를 뒤흔들(좋은 의미로) 엑셀의 새로운 함수 4가지 (0) | 2026.01.08 |
| 내부적으로 발생하는 일을 알려주는 Excel 함수 사용법 (0) | 2026.01.06 |
