엑셀 중급 함수 20가지

로

컴퓨터 강사가 직접 설명합니다.

62

중급 함수

1. VLOOKUP 함수와 XLOOKUP 함수의 차이는 무엇인가요? VLOOKUP은 세로 방향으로 값을 검색하며 참조 열이 왼쪽에 있어야 합니다. XLOOKUP은 검색 방향에 제약이 없고 더 유연하게 사용할 수 있는 최신 함수입니다.

데이터 예시:

A열(이름)B열(점수)
홍길동90
이영희85

수식 예시:

  • =VLOOKUP("홍길동", A2:B3, 2, FALSE) → 90 (A열에서 홍길동 찾고 B열 반환)
  • =XLOOKUP("홍길동", A2:A3, B2:B3) → 90

2. INDEX와 MATCH 함수를 함께 사용하는 이유는? MATCH는 검색 값의 위치(행 번호)를 찾고, INDEX는 그 위치의 값을 반환합니다. VLOOKUP보다 유연하고 왼쪽 열 검색도 가능합니다.

데이터 예시:

A열(이름)B열(부서)
홍길동영업부
김철수총무부

수식 예시:

  • =MATCH("홍길동", A2:A3, 0) → 1 (홍길동은 첫 번째 행)
  • =INDEX(B2:B3, 1) → "영업부"
  • =INDEX(B2:B3, MATCH("홍길동", A2:A3, 0)) → "영업부"

3. 표 형식의 데이터를 동적으로 참조하려면? 엑셀 표(Table)는 데이터가 추가돼도 자동으로 범위가 확장되고, 열 이름으로 쉽게 참조할 수 있습니다.

데이터 예시 (테이블 이름: 매출표):

월금액
1월1,000,000
2월1,200,000

수식 예시:

  • =SUM(매출표[금액]) → 2,200,000

4. 배열 수식이란 무엇이며, 언제 사용하나요? 배열 수식은 여러 값을 동시에 계산할 수 있습니다. 엑셀 365 이상은 자동으로 동적 배열을 반환합니다.

데이터 예시:

A열(수량)B열(단가)
101,000
52,000

수식 예시:

  • =A2:A3 * B2:B3 → {10,000; 10,000}

5. IFERROR 함수는 어떤 상황에서 유용한가요? 오류 발생 시 대체 값을 표시하는 함수로, 사용자에게 더 친절한 결과를 보여줍니다.

데이터 예시:

A열B열
102
50

수식 예시:

  • =IFERROR(A2/B2, "에러") → 5
  • =IFERROR(A3/B3, "에러") → "에러"

6. 조건부 서식에서 수식으로 다중 조건 설정하기 수식을 사용하면 두 개 이상의 조건을 만족할 때만 셀에 형식을 적용할 수 있습니다.

데이터 예시:

A열(점수)B열(출석률)
8095%
8570%

서식 조건 수식:

  • =AND(A2>=80, B2>=0.9) → 1행에 서식 적용

7. 시트 간 데이터 연결 방법은? 다른 시트의 셀을 참조할 땐 시트명을 포함해 시트이름!셀주소 형식으로 작성합니다.

예제:

  • =Sheet2!A1 → Sheet2 시트의 A1 셀 값을 가져옴
  • ='매출 보고서'!B2 → 시트명이 공백 포함일 경우 작은따옴표 사용

8. 고급 필터와 일반 필터의 차이점은? 일반 필터는 간단한 필터링만 가능하지만, 고급 필터는 조건을 범위로 따로 설정하고 OR 조건도 지원합니다.

예시:

  • 일반 필터: '부서'에서 '영업부'만 보기
  • 고급 필터: '영업부' 또는 '기획부' 보기 + 결과를 새 위치에 복사

9. 특정 값이 셀 범위에서 몇 번째 위치에 있는지 찾는 방법은? MATCH 함수는 값의 상대적 위치를 숫자로 반환합니다.

데이터 예시:

A열(이름)
김철수
이영희
박민수

수식 예시:

  • =MATCH("박민수", A2:A4, 0) → 3

10. 날짜와 시간을 다루는 대표적인 함수는? 엑셀에서는 날짜 및 시간을 다루는 전용 함수들이 있습니다.

대표 함수 및 예시:

  • =TODAY() → 오늘 날짜 (예: 2024-05-26)
  • =NOW() → 현재 날짜 및 시간
  • =YEAR(A1) → A1의 연도 추출
  • =TEXT(TODAY(), "yyyy-mm-dd") → "2024-05-26"

11. TEXT 함수로 날짜나 숫자 형식 바꾸기 TEXT 함수는 숫자나 날짜를 원하는 형식의 문자열로 바꾸는 데 유용합니다.

예시:

A열(날짜)
2024-05-26

수식 예시:

  • =TEXT(A2, "yyyy년 mm월 dd일") → "2024년 05월 26일"
  • =TEXT(A2, "yyyy-mm") → "2024-05"

12. 조건에 따라 셀 색상을 자동으로 바꾸는 방법은? 조건부 서식을 사용하여 특정 조건을 만족하는 셀에 색상이나 강조 효과를 적용할 수 있습니다.

데이터 예시:

A열(매출액)
500,000
1,200,000

조건 수식:

  • =A2>=1000000 → 1,000,000 이상이면 색상 강조

13. 피벗 테이블에서 슬라이서 기능의 역할은? 슬라이서는 필터 역할을 시각적으로 해주는 도구로, 마우스 클릭으로 항목을 쉽게 필터링할 수 있습니다.

활용 예시:

  • '부서' 항목에 슬라이서를 적용하면 영업부, 총무부 등 원하는 부서만 클릭으로 필터링 가능

14. SUMIFS, COUNTIFS 함수는 언제 사용하나요? 여러 조건을 동시에 만족하는 데이터의 합계나 개수를 계산할 때 사용합니다.

데이터 예시:

A열(부서)B열(매출)
영업부1,000,000
기획부800,000

수식 예시:

  • =SUMIFS(B2:B3, A2:A3, "영업부") → 1,000,000
  • =COUNTIFS(A2:A3, "기획부", B2:B3, ">500000") → 1

15. 중복된 데이터를 제거하는 가장 안전한 방법은? '데이터' 탭의 '중복 제거' 기능을 사용하되, 원본을 복사해 백업한 뒤 실행하는 것이 안전합니다.

예시:

A열(이메일 주소)
test@naver.com
test@naver.com
user@gmail.com

→ 중복 제거 후: test@naver.com, user@gmail.com

16. 엑셀에서 매크로란? 반복적인 작업을 자동으로 실행하기 위한 기능으로, VBA 코드로 구성되어 있으며 매크로 사용 시 .xlsm 확장자 필요

활용 예시:

  • 버튼 클릭 시 보고서 인쇄, 정렬, 필터 자동 실행 등

17. VBA 없이 자동화를 하려면? 빠른 채우기(Flash Fill), 데이터 유효성 검사, 표 자동 확장, 조건부 서식 등 기본 기능을 조합하여 반복 작업을 줄일 수 있습니다.

예시:

  • 이름 열에서 성 자동 추출 → 빠른 채우기 사용

18. OFFSET 함수는 언제 사용되며 주의할 점은? 기준 위치에서 일정한 거리만큼 떨어진 셀이나 범위를 동적으로 참조할 수 있습니다.

데이터 예시:

A열(기준)
기준값
참조값1
참조값2

수식 예시:

  • =OFFSET(A1, 1, 0) → A2 값 참조

주의점: 너무 많은 OFFSET 함수는 계산 속도를 느리게 할 수 있습니다.

19. 여러 시트의 데이터를 하나로 합치려면? 3D 참조 수식 또는 Power Query 기능을 활용할 수 있습니다.

예시:

  • =SUM(Sheet1:Sheet3!B2) → Sheet1부터 Sheet3까지 B2 셀의 합계 계산

20. 실시간으로 특정 셀의 값을 다른 셀에 반영하려면? 단순 참조 수식을 사용하면 원본 셀의 변경이 자동으로 반영됩니다.

예시:

  • A1 셀에 입력한 내용을 B1 셀에 표시하고 싶다면 → =A1

댓글 남기기