엑셀 프로그램에는 사용자의 업무 편의를 돕기 위해 정말 수많은 함수들이 내장되어
있습니다. 버전에 따라 조금씩 다르지만 보통 450개가 넘는 방대한 양의
함수들이 등록되어 있습니다. 하지만 현업에서 매일 복잡한 장표를 가공하는
실무자들도 이 많은 함수를 전부 외우고 사용하지는 않습니다. 실제 업무의
성격이나 부서에 따라 주로 사용하는 핵심 함수는 약 20개 안팎으로
압축됩니다. 기본적인 함수 몇 가지만 제대로 다룰 줄 알아도 웬만한 대형
정산서나 기획서 가공 작업은 막힘없이 해결할 수 있습니다. 오늘은 실무에
바로 적용할 수 있는 가장 유용하고 자주 쓰이는 필수 함수들을 알기 쉽게 요약해
드리겠습니다.
1. 조건에 따라 판단하고 오류를 방어하는 필수 논리 함수
논리 함수는 장표 내부에 일정한 규칙이나 기준을 세우고 이에 따라 데이터를
분류할 때 빼놓을 수 없는 도구입니다.
단독으로도 자주 쓰이지만 다른 찾기 함수나 통계 함수와 융합했을 때 엄청난
시너지 효과를 발휘합니다.
| 함수명 | 실무 핵심 기능 |
|---|---|
| =IF(조건, 참일때 값, 거짓일때 값) | 지정한 조건을 검사하여 맞으면 첫 번째 값을, 틀리면 두 번째 값을 셀에 출력합니다. |
| =IFERROR(수식, 오류일때 대체값) | 수식 계산 중 지저분한 에러 기호가 발생하면 내가 지정한 대체 문구나 숫자로 깔끔하게 가려줍니다. |
| =AND(조건1, 조건2, ...) | 나열한 모든 조건이 구체적으로 전부 만족할 때만 최종 참 값을 반환합니다. |
| =OR(조건1, 조건2, ...) | 나열한 여러 조건 중에서 단 하나라도 기준에 만족하면 참 값을 결과로 보여줍니다. |
2. 텍스트 데이터를 내 입맛대로 정돈하는 문자열 함수
외부 시스템에서 고객 명부나 자재 코드를 다운로드하면 문자열이 정돈되지 않아
수식이 깨지는 경우가 많습니다.
이럴 때 텍스트 함수를 활용하면 특정 글자만 분리하거나 대소문자를 일괄 통일하여
가독성을 높일 수 있습니다.
| 함수명 | 실무 핵심 기능 |
|---|---|
| =LEFT(텍스트, 글자수) | 지정한 문자열의 왼쪽 끝에서부터 원하는 개수만큼의 글자를 쏙 잘라옵니다. |
| =RIGHT(텍스트, 글자수) | 지정한 문자열의 오른쪽 끝에서부터 필요한 만큼의 글자를 역순으로 추출합니다. |
| =MID(텍스트, 시작위치, 글자수) | 텍스트의 중간 특정 글자 좌표를 지정해서 원하는 개수만큼 데이터를 잘라옵니다. |
| =LOWER(텍스트) / =UPPER(텍스트) | 영문 단어를 영문 기준에 맞추어 소문자나 대문자로 한 번에 통일하여 정돈합니다. |
| =PROPER(텍스트) | 영문 문자열의 첫 번째 글자만 대문자로 바꾸고 나머지는 소문자로 채워줍니다. |
| =SEARCH(찾을글자, 전체텍스트, 시작위치) | 전체 문장 속에서 내가 찾는 단어가 몇 번째 자리에 숨어있는지 위치 번호를 숫자로 반환합니다. |
3. 업무 시간을 절반으로 줄여주는 핵심 찾기 및 참조 함수
인사팀이나 정산 부서에서 매일 사용하는 가장 비중이 높은 카테고리가 바로 찾기
참조 영역입니다.
흩어져 있는 마스터 테이블에서 필요한 정보만 자석처럼 정확하게 불러오는 역할을
담당합니다.
| 함수명 | 실무 핵심 기능 |
|---|---|
| =VLOOKUP(기준값, 범위, 열번호, 옵션) | 지정한 범위의 첫 열에서 기준값을 검색한 뒤 우측 열에 배치된 데이터를 가져옵니다. |
| =HLOOKUP(기준값, 범위, 행번호, 옵션) | 테이블의 첫 행에서 기준값을 가로로 검색한 뒤 아래쪽 행에 있는 데이터를 추출합니다. |
| =INDEX(범위, 행번호, 열번호) | 지정한 사각형 데이터 범위 내에서 정확히 몇 번째 행, 몇 번째 열에 위치한 값을 집어옵니다. |
| =MATCH(기준값, 범위, 옵션) | 내가 찾는 특정 값이 지정한 범위 내에서 상대적으로 몇 번째 자리에 위치하는지 알려줍니다. |
4. 기본 연산부터 다중 조건 집계까지 책임지는 수학 및 통계 함수
단순히 전체 금액을 더하는 것을 넘어 특정 부서나 특정 금액 이상만 골라 합산하고
평균을 내야 할 때가 많습니다.
뒤에 IF나 IFS가 붙은 다중 조건 함수들을 마스터해 두면 복잡한 피벗 테이블
없이도 원하는 통계를 뚝딱 만들 수 있습니다.
| 함수명 | 실무 핵심 기능 |
|---|---|
| =SUMIFS(합산범위, 조건범위1, 조건1, ...) | 나열한 여러 가지 까다로운 조건들에 완벽히 부합하는 셀들의 판매 금액만 골라 더합니다. |
| =AVERAGEIFS(평균범위, 조건범위1, 조건1, ...) | 지정한 다중 기준 만족 구역을 판독하여 해당 조건에 맞는 셀들의 평균치만 계산합니다. |
| =COUNTIF(범위, 조건) | 지정한 전체 영역 중에서 내가 설정한 특정 조건에 부합하는 셀이 총 몇 개인지 카운트합니다. |
| =COUNTIFS(조건범위1, 조건1, 조건범위2, 조건2, ...) | 복수 개의 다중 조건을 동시에 충족하는 데이터가 장표 내에 총 몇 줄인지 개수를 세어줍니다. |
| =COUNTA(범위) | 숫자와 텍스트 글자를 가리지 않고 범위 내에서 비어있지 않은 모든 셀의 개수를 확인합니다. |
[전문가의 실무 한끗 팁] VLOOKUP 함수의 치명적인 약점을 극복하는 INDEX와 MATCH 중첩 조합
실무에서 가장 대중적으로 쓰이는 VLOOKUP 함수에는 가공 관점에서 치명적인
구조적 결함이 하나 존재합니다.
바로 내가 찾고자 하는 기준값을 마스터 테이블의 반드시 첫 번째 열에 배치해
두어야만 정상 작동한다는 점입니다.
만약 기준값 왼쪽에 위치한 과거 이력 데이터나 코드 번호를 가져와야 하는
상황이라면 함수가 제대로 작동하지 못하고 수식 에러를 뿜어냅니다.
이런 좌측 검색 불가 한계를 완벽하게 극복하고 데이터 무결성을 확보해 주는
치트키가 바로 INDEX와 MATCH 함수의 결합 수식입니다.
수식 입력줄에
=INDEX(가져올데이터범위, MATCH(기준값, 기준값범위, 0))
형태로 공식을 구성해 보세요.
백스테이지에서 MATCH 함수가 기준값의 행 위치 번호를 칼같이 찾아내고 INDEX
함수가 해당 행의 데이터를 왼쪽이든 오른쪽이든 상관없이 자석처럼 정확하게
뽑아다 줍니다.
장표의 열 배치 순서를 억지로 바꾸지 않고도 완벽하게 수식을 자동화할 수 있는
전문가들의 필수 단골 노하우입니다.
5. 장표의 안정성 확보를 위한 최종 값 고정 마감 수칙
알려드린 필수 함수들을 적절히 조합하여 원하는 데이터 통계 레이아웃을
성공적으로 완성했다면 저장 전 사후 보정이 필요합니다.
함수 수식이 실시간으로 너무 많이 작동하고 있으면 행을 정렬하거나 타 부서로
파일을 전송했을 때 수식 주소가 찢어질 위험이 있습니다.
더불어 대량의 동적 배열 연산은 컴퓨터 시스템 리소스를 대량 소모하여 전체 통합
문서의 구동 속도를 저하시키는 원인이 됩니다.
- 함수 가공 처리가 종결된 결과 열 범위 전체를 마우스 드래그로 길게 블록 지정합니다.
- 복사 단축키인 [Ctrl + C]를 누른 뒤 곧바로 그 자리에 붙여넣기 단축키인 [Ctrl + Alt + V]를 조합 타건합니다.
- 화면에 대화상자가 전면에 팝업 되면 목록 옵션 중에서 [값] 라디오 단추를 체크하고 확인을 눌러 마감합니다.
이 마감 처리를 완료해 주어야만 장표를 구동하던 무거운 공식 껍데기가 증발하고
깨끗한 상숫값 데이터로만 화면에 박제됩니다.
사소해 보이는 수식 통제력의 차이가 불필요하게 밤을 새우며 허비하던 지루한 편집
노동 시간을 혁신적으로 줄여줍니다.
오늘 함께 완벽하게 요약한 필수 함수들의 작동 원리를 실무 파일 마감 공정에 적극
반영해 보시고 신속하게 업무를 마무리하여 당당하게 퇴근하시기 바랍니다.