본문 바로가기
슬기로운 생활 눈뜨기

모두가 알아야 할 엑셀 기능 10가지

by 남이철이 2021. 2. 21.
반응형

모두가 알아야 할 엑셀 기능 10가지

 

애덤 레이시, 드보라 애시비

 

 

 

 

 10년간 데이터를 분석, 조작, 시각화하기 위해 설계된 혁신적인 빅데이터 소프트웨어가 앞다퉈 출시됐다. 하지만 어지간한 지식노동자는 아직도 나온 지 30년 된 마이크로소프트 엑셀을 이용해 데이터를 처리한다. 마이크로소프트의 CEO 사티아 나델라는 엑셀이 여전히 최고의 마이크로소프트 상품이라고 주장한다. 전 세계 75000만 명의 지식노동자들도 이 말에 수긍할 것이다.

 

우리는 지난 10년 동안 마이크로소프트 엑셀을 가르치고 시험해 왔다. 화이트칼라 노동자 수백 명을 설문조사한 결과, 직장생활의 10%를 스프레드시트 작업에 할애하고 있었다. 연구개발직이나 금융업에 종사하는 경우 그 수치는 30%, 또는 매일 2.5시간에 달했다.

 

그러니 글로벌 인력의 상당수가 엑셀을 더 능숙히 다루게 되면 어떨지 한번 생각해 보라. 시간이 절약되고 생산성이 향상될 것이다.

 

우리는 지난해 엑셀 전문가의 조언을 받고 수만 건의 테스트 사례와 실사용 데이터를 검토해서 <   The Definitive 100 Most Useful Excel Tips   >을 썼다. 모든 엑셀 기능은 사용 사례가 있지만, 모든 기능을 사용하는 사람은 없다. 500개가 넘는 기능을 간추리다 보니, 대다수 지식노동자에게 정말 유용한 기능은 100가지 남짓했다. 우리는 그중 특히 배우기 쉽고( 2시간 정도), 실제로 생산성을 향상시킬 수 있는 10가지를 선택했다. 다음 10가지 엑셀 기능은 활용도 상위 100개 리스트를 내림차순으로

나열한 것이다. 많은 사람들처럼 다음에 어떤 엑셀 기능을 익혀야 할지 잘 모르겠다면, 기술의 유용성과 배우는 데 드는 시간을 고려해서 2×2 행렬을 적용해 보자.

 

 

선택해 붙여넣기(학습시간 10분)

 

복사해 붙이기는 엑셀에서 가장 단순하고 많이 쓰이는 기능 중 하나다. 하지만 셀의 값만 복사해야 하는데 서식이나 수식까지 그대로 복사하는 경우가 종종 있다.

 

이런 실수를 복구하려면 시간이 꽤 든다. ‘선택해 붙여넣기는 그래서 특별하다. 이 기능을 사용하면 복사한 셀의 어떤 요소를 유지할지 선택할 수 있다. Ctrl+C로 셀을 복사하고, Ctrl+Alt+V(또는 홈 리본 메뉴의 클립보드 섹션으로 가거나, 편집   > 선택해 붙여넣기)를 눌러 선택해 붙여넣기를 불러낸 다음 원하는 요소를 고르면 된다. 선택해 붙여넣기에서 가장 많이 사용되는 값만 붙여넣기의 단축키는 Alt+E+S+V이다.

 

 

행 추가하기(학습시간 2분)

 

기존 행 사이에 새로 여러 행을 삽입해야 할 때가 있다. 이런 경우 단축키(Ctrl, shift, +)가 아주 편리하다. 특히+를 토글 키로 사용하면 여러 행을 추가할 수 있다. 많은 열을 한 번에 삽입하고 싶을 때는 추가하려는 열만큼(예를 들어 5)을 드래그로 선택한 다음 마우스 오른쪽 클릭, 삽입하는 게 더 빠르다.

 

 

플래시 필(학습시간 30분)

 

2013 플래시 필Flash Fill이 생기면서 엑셀은 스스로 생각하는 프로그램이 됐다. 플래시 필은 패턴이 감지되면 데이터를 자동으로 완성하는 기능이다.

 

A 10개 셀에 ‘ABC-00001’부터 ‘ABC-00010’까지 상품번호 목록이 있는데, ‘-’ 다음의 숫자만 필요하다고 해보자. 플래시 필을 사용하면 쉽게 ‘ABC’를 버릴 수 있다. 2013년 이전에도 똑같은 작업이 가능했지만 FIND, LEFT, & 같은 함수 조합을 써야 했다. 하지만 현재의 엑셀은 훨씬 빠르고 놀랍다.

 

첫번째 빈 셀에 ‘00001’을 입력해서 패턴을 만든다. 플래시 필이 켜 있는 경우(파일 옵션, 고급) 아래 셀에 상품번호를 입력하기 시작하면, 플래시 필이 패턴을 인식해서 나머지 상품번호를 자동으로 채워 넣는다. 사용자는 엔터 키로 이를 수락만 하면 된다. 아니면 데이터   >플래시 필을 클릭하거나 Ctrl+E를 통해 수동으로 기능을 사용할 수도 있다.

 

플래시 필은 마법처럼 신기하고, 여러 상황에서 사용할 수 있다. 많은 양의 데이터를 빠르고 정확하게 입력하거나 변경해야 할 때 엄청나게 시간을 절약해 준다. 플래시 필은 정말 입이 떡 벌어지는 기능이다.

 

 

INDEX-MATCH 함수(학습시간 45분)

 

한 열에서 값을 찾아 다른 열에 이 값을 돌려주는 VLOOKUP 함수를 빼면, ‘INDEX’ ‘MATCH’는 엑셀에서 검색할 때 가장 널리 쓰이는 강력한 도구다. 따로 써도 편리하지만, 두 가지를 결합할 때 진정한 힘이 발휘된다. INDEX MATCH를 조합하면 많은 데이터 중에서 필요한 데이터를 정확하고 효율적으로 뽑아낼 수 있다. 이 기능을 완벽히 익히면 동료와 상사에게 엑셀 달인으로 인정받을 뿐만 아니라, 지루하고 따분한 작업을 빠르고 쉽게 해낼 수 있다. 사용법은 다음과 같다.

 

VLOOKUP은 훌륭한 기능이지만 왼쪽에서 오른쪽으로만 값을 검색할 수 있다는 한계가 있다. 검색값은 검색범위에서 왼쪽에 위치해 있어야 한다. INDEX MATCH는 위치에 상관없이 검색범위 어디에서나 값을 검색할 수 있다.

 

상품 목록을 정리한 스프레드시트가 있다고 하자. ‘상품 번호’ ‘수익’ ‘상품명’ ‘수입이라고 이름 붙인 열이 있다. 다른 스프레드시트에는 상품명 목록이 있고, 이제 각 상품이 얼마나 수익을 창출했는지 찾아보려 한다. 이 경우 상품명(검색값)’을 이용해서 수익을 검색해야 하는데, 상품명이 수익의 오른쪽에 있기 때문에 VLOOKUP을 사용할 수 없다. INDEX MATCH를 사용해야 할 완벽한 사례다.

 

수식은 다음과 같다.

=INDEX(수익 열, MATCH(검색값, 상품명 열, 0))

이렇게 하면 쉽게 수식 적용을 이해할 수 있다.

 

=INDEX(반환값을 원하는 열, MATCH(검색값, 검색할 열, ‘0’ 입력)). 0을 입력하면 정확한 값이 검색된다. 미만(-1)과 초과(1)도 검색할 수 있다.)

 

INDEX MATCH는 언뜻 복잡해 보인다. 몇 번 연습이 필요하지만 VLOOKUP보다 활용도가 높고 강력하기 때문에 충분히 고민할 만한 가치가 있다. 확실히 엑셀의 가장 중요한 용도 중 하나다.

 

 

더하기 함수(학습시간 2분)

 

엑셀에서 처음 배우는 기능 가운데 하나가 행이나 열을 더하는 것이다. 하지만 행이나 열 끝의 셀을 선택하고 Alt 키를 눌러서 순식간에 더하기 함수SUM를 사용하는 기능도 알고 있었나?

 

 

되돌리기, 되살리기(학습시간 1분)

 

‘되돌리기’와 되살리기 기능은 엑셀의 만능조커 카드다. 엑셀에서 잘못 입력한 내용을 되돌릴 때 Ctrl Z를 쓰고 있지 않다면, 꼭 필요한 기능이니 알아두도록 하자. 그런데 많은 사람이 Ctrl Y가 반대로 되살리기 기능을 한다는 사실은 잘 모른다. 두 가지를 조합해서 쓰면 매우 효과적이며, 작업 절차를 반복하면서 정확한 작업 내용을 찾을 수 있다.

 

 

중복제거(학습시간 10분)

 

‘중복제거’는 정확히 그 이름과 같은 기능을 한다. 즉 어떤 데이터 범위에서 중복된 값을 제거한다. 제거하려는 중복값을 없앤 뒤에는 다른 시트에 입력할 것을 추천한다. 리본 메뉴의 데이터 도구 섹션의 데이터 탭에서 찾을 수 있다.

 

중복값에 강조 표시만 하고 싶을 때는 조건부 서식을 사용한다. 단축키는 Alt H L(또는 홈 리본의 스타일에서 찾기).

 

 

틀 고정(학습시간 15분)

 

큰 데이터 표에서 끝없이 스크롤을 내리다가 어느 열이 어느 열인지 잊어버린 적이 있다면? ‘틀 고정이 답이다. 틀 고정 기능을 사용하면 제일 위 행이나 맨 처음 열, 또는 특정 행과 열을 고정시킬 수 있다. 고정하고 싶은 행과 열을 확인하고, 해당 열의 바로 오른쪽, 해당 행의 바로 아래에 있는 셀을 선택한다. ‘보기 탭의  섹션에서 틀 고정을 찾아보자. 단축키는 Alt W F.

 

 

F4(학습시간 10분)

 

엑셀에서 특별히 만족스러운 F4 사용법은 두 가지다. 먼저 절대참조Absolute Reference를 만드는 것이다. F4를 토글 키로 해서 다양한 옵션을 선택할 수 있다. 거의 아는 사람이 없는 두 번째 기능은 엑셀업무의 생산성을 크게 높일 수 있다. F4는 사용 가능한 경우 마지막 동작을 반복한다. 예를 들어 한 셀에 테두리를 적용했다면, F4를 눌러 다른 셀에 같은 테두리를 적용할 수 있다.

 

 

CTRL+화살표(학습시간 5분)

 

데이터의 마지막으로 가기 위해 스크롤을 내리고 있다면 당장 멈추고 Ctrl+화살표를 사용해 보자! 이 단축키로 간단하게 사용하고 있는 행이나 열의 데이터 마지막으로 갈 수 있다. Ctrl, Shift와 함께 사용하면 순식간에 넓은 범위의 데이터를 강조 표시하거나 선택할 수 있다.

 

주의: 데이터에 빈칸이 있다면 첫 번째 빈칸으로 가게 된다. 마지막 줄로 빨리 가고 싶다면 공란이 없는 데이터 열을 선택하자.

 

 

 10가지 항목 중 몇 가지만 제대로 익혀도 일상업무가 완전히 달라질 것이다. 높은 수준에서 데이터 기반 비즈니스 의사결정을 증명할 때, 혹은 가족이 기다리는 집으로 서둘러 돌아가고 싶을 때, 엑셀 기능을 정확히 숙지하면 빠르고 쉽게 생산성을 극대화할 수 있다.

 

반응형

댓글