Excel 수식 치트 시트: 고급 가이드

기본 사항을 순항하고 중급 문제를 해결했으며 이제 큰 총인 Excel의 고급 수식과 씨름할 준비가 되었습니다! Excel에는 정교한 계산을 위한 많은 고급 기능과 수식이 있으므로 필요한 항목을 정확히 찾아주는 가이드가 있으면 유용합니다.

이 Excel 수식 치트 시트는 고급 예측 수식, 통계 분석, 데이터 조작 기능, 오류 처리 등을 다룹니다.

이 참조는 이러한 고급 기능을 사용하는 방법에 대한 지식을 제공합니다. 각 수식에는 명확한 설명, 구문 및 실용적인 예가 함께 제공되어 중급 Excel 사용자가 고급 고급 사용자가 될 수 있도록 도와줍니다.

치트 시트를 다운로드하여 인쇄하여 편리하게 보관하십시오.

좋아, 시작하자.

Excel-formulas-cheat-sheet-advanced-1 다운로드

먼저 배열 수식을 살펴보겠습니다.

목차

• 배열 수식
  • 1. 독특한 기능
  • 2. 정렬 기능
  • 3. 필터 기능
• Excel 함수 무작위화
  • 1. RANDBETWEEN 함수
  • 2. RANDARRAY 기능
• Microsoft Excel의 고급 예측 수식
  • 1. FORECAST.ETS 기능
  • 2. 트렌드 기능
• 고급 통계 공식
  • 1. 백분위수 함수
  • 2. QUARTILE 기능
• 고급 데이터 분석 및 조작 공식
  • 1. 전치 기능
  • 2. 주파수 기능
  • 3. GETPIVOTDATA 기능
• 고급 오류 처리
• 고급 조회 수식
  • 1. XLOOKUP 기능
  • 2. XMATCH 기능
• 마지막 생각들

배열 수식

초보자 치트 시트는 데이터 를 수동으로 정렬하고 필터링하는 방법을 보여줍니다. 고급 사용자는 배열 수식을 사용하여 프로그래밍 방식으로 이 작업을 수행합니다.

배열 수식을 사용하면 여러 셀에서 동시에 계산을 수행할 수 있습니다. 다음은 세 가지 주요 기능입니다.

1. 고유한

2. 종류

3. 필터

이러한 기능 중 일부는 최신 버전의 Microsoft Excel에서만 사용할 수 있습니다.

1. 독특한 기능

UNIQUE 함수는 범위를 허용하고 고유한 값 목록을 반환합니다.

의류 품목에 대한 판매 데이터가 있다고 가정합니다. 열 A에서 고유 항목을 찾으려면 다음 수식을 사용하십시오.

=고유(A2:A6)

2. 정렬 기능

SORT 함수는 범위의 내용을 정렬합니다. 구문은 다음과 같습니다.

SORT(배열, [정렬 인덱스], [정렬 순서], [열별])

• array : 정렬할 값의 범위.

• sort_index : 정렬할 열(기본값 1)

• sort_order : 오름차순의 경우 1(기본값) 또는 내림차순의 경우 2).

• by_col : 열 기준으로 정렬하려면 TRUE(기본값)이고 행 기준으로 정렬하려면 FALSE입니다.

마지막 세 인수는 선택 사항이며 기본값은 일반적으로 원하는 것입니다.

첫 번째 열을 기준으로 샘플 데이터를 정렬하려면 다음 수식을 사용합니다.

=정렬(A2:C6)

3. 필터 기능

FILTER 함수를 사용하면 특정 조건에서 범위를 필터링할 수 있습니다. 구문은 다음과 같습니다.

=FILTER(배열, 포함, [if_empty])

• array : 필터링할 범위.

• include : 필터링할 값을 결정하는 조건입니다.

• if_empty : 필터링 기준을 충족하는 값이 없는 경우 반환할 항목을 지정합니다(기본값은 "").

샘플 데이터의 행을 필터링하여 판매 금액이 $15보다 큰 경우만 표시한다고 가정합니다. 이 공식을 사용하십시오.

=필터(A2:C6, C2:C6>15)

Excel 함수 무작위화

중급 치트는 0과 1 사이의 난수를 생성하는 RAND 함수를 사용하는 방법을 보여줍니다.

고급 Excel 사용자는 랜덤화 기능을 사용하여 샘플 데이터를 빠르게 생성하는 방법을 알고 있습니다.

1. RANDBETWEEN 함수

RANDBETWEEN 함수는 하위 및 상위 숫자를 0과 1이 아닌 다른 것으로 지정할 수 있기 때문에 RAND보다 더 유연합니다.

1에서 100 사이의 숫자로 데이터를 생성하려면 다음 수식을 A1 셀에 입력합니다.

=RANDBETWEEN(1, 100)

그런 다음 셀을 원하는 만큼의 행과 열에 복사합니다. 난수 그리드를 생성하는 데 몇 초가 걸립니다.

2. RANDARRAY 기능

RANDBETWEEN 함수의 수동 복사를 피하는 것이 좋을 것이라고 생각할 수 있습니다. 고급 기능을 사용하려면 최신 버전의 Microsoft Excel에서 새로운 RANDARRAY 기능을 사용할 수 있습니다.

구문은 다음과 같습니다.

RANDARRAY([행], [열], [최소], [최대], [정수])

• 행 : 행 수

• 열 : 열 수

• 최소 : 가장 낮은 숫자

• max : 가장 높은 숫자

• 정수 : 기본값은 TRUE이고 그렇지 않으면 십진수를 사용합니다.

6개의 행과 9개의 열에서 1에서 100 사이의 난수를 생성하려면 다음 수식을 사용합니다.

=RANDARRAY(6, 9, 1, 100, 참)

Microsoft Excel의 고급 예측 수식

Excel의 예측 기능은 기존 데이터 추세를 기반으로 미래 가치를 예측하는 데 사용됩니다. 이러한 기능은 데이터를 기반으로 패턴 및 프로젝트 추세를 식별하는 데 도움이 됩니다.

1. FORECAST.ETS 기능

이전 FORECAST 함수는 Excel 2016에서 새로운 함수 집합으로 대체되었습니다.

원하는 특정 예측 모델을 기반으로 함수를 선택합니다. 예를 들어 FORECAST.ETS 함수는 지수평활 알고리즘을 사용합니다.

구문은 다음과 같습니다.

FORECAST.ETS(target_date, values, timeline)

• target_date : 계산된 값을 원하는 날짜입니다.

• values ​​: 과거 데이터.

• 타임라인 : 날짜 범위

A 열에 1월 1일부터 5일까지의 날짜가 있고 B 열에 판매 금액이 있다고 가정합니다. 이 수식은 다음 판매 금액을 예측합니다.

=FORECAST.ETS("2023년 1월 6일", B2:B6, A2:A6)

2. 트렌드 기능

TREND 함수는 최소 제곱법을 기반으로 일련의 값을 투영합니다. 배열을 반환합니다. 구문은 다음과 같습니다.

TREND(known_y, [known_x], [new_x], [const])

• known_y : y 값의 범위

• known_x : x 값의 범위

• new_x : 계산된 값의 범위

종종 known_y는 데이터 포인트이고 known_x는 날짜입니다.

이전 예제와 동일한 데이터를 사용하여 C2 셀에 아래 수식을 입력할 수 있습니다. 일련의 값이 생성됩니다.

=추세(B2:B6, A2:A6)

고급 통계 공식

고급 통계 기능에는 백분위수 및 사분위수 계산이 포함됩니다. 일부 수학 함수는 이전 버전과의 호환성을 위해 사용할 수 있지만 최신 버전을 사용하는 것이 좋습니다.

1. 백분위수 함수

이 함수는 특정 값 아래로 떨어지는 데이터 포인트의 백분율을 계산합니다. 구문은 다음과 같습니다.

PERCENTILE.INC(배열, k)

• 배열 : 셀 범위

• k : 0에서 1까지의 백분위수

열 B에 있는 데이터의 70번째 백분위수를 계산한다고 가정합니다. 다음 수식을 사용합니다.

=PERCENTILE.INC(B2:B6, 0.7)

2. QUARTILE 기능

이 함수는 백분위수 함수의 변형이지만 분기를 사용하여 데이터를 나눕니다. 구문은 다음과 같습니다.

QUARTILE.INC(배열, 쿼트)

• 배열 : 데이터의 범위

• 쿼트 : 25번째 백분위수는 1, 50번째 백분위수는 2, 75번째 백분위수는 3, 최대값은 4입니다.

아래 수식은 A 열에 있는 데이터의 첫 번째 사분위수를 계산합니다.

=QUARTILE.INC(A2:A5, 1)

고급 데이터 분석 및 조작 공식

몇 가지 고급 기능을 사용하여 데이터 형식을 전환하고, 빈도 분포를 분석하고, 피벗 테이블에서 데이터를 추출할 수 있습니다.

1. 바꾸어 놓다

2. 빈도

3. GETPIVOTDATA

1. 전치 기능

경우에 따라 행의 데이터를 열로 이동하거나 그 반대로 이동하려는 경우가 있습니다. 이 작업을 수동으로 수행하거나 TRANSPOSE 함수를 대신 사용할 수 있습니다.

셀 A2, A3 및 A4에 "T-Shirt", "Hoodie" 및 "Jeans" 항목이 있다고 가정합니다. 이것을 열 머리글로 만들고 싶습니다. 이 함수는 단일 행의 값을 반환합니다.

=TRANSPOSE(A2:A4)

2. 주파수 기능

이 함수는 데이터 세트 내 값의 빈도 분포를 계산합니다. 구문은 다음과 같습니다.

빈도(data_array, bins_array)

• data_array : 값의 범위.

• bins_array : 사용할 간격.

B열에 판매 데이터가 있고 다음과 같은 금액을 기반으로 값의 빈도 분포를 분석한다고 가정합니다.

• 20 이하.

• 20에서 80까지.

• 80 이상.

이는 3개의 빈을 나타내며 다음 공식으로 계산할 수 있습니다.

=FREQUENCY(A2:A6, {20,80})

Excel의 빈도 분포에 대한 자세한 내용은 이 비디오를 확인하세요.

3. GETPIVOTDATA 기능

이 기능을 사용하면 피벗 테이블에서 요약된 정보를 추출할 수 있습니다. 구문은 다음과 같습니다.

GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

• data_field : 피벗 테이블에서 검색하려는 데이터 필드 또는 값입니다.

• pivot_table : 피벗 테이블에 대한 참조입니다.

• field1, item1, etc. : 필터링할 필드/항목 쌍입니다.

판매된 품목의 색상을 기반으로 하는 피벗 테이블이 있다고 가정합니다. 빨간색 품목의 판매량을 추출하려면 다음 공식을 사용하십시오.

=GETPIVOTDATA("매출", A1, "색상", "빨간색")

고급 오류 처리

가장 기본적인 Excel 수식도 오류를 생성할 수 있습니다. 중급 사용자는 ISERROR를 사용하여 오류를 처리하는 방법을 알고 있어야 합니다. 고급 사용자는 오류 식별을 위한 ERROR.TYPE 기능에도 익숙해야 합니다.

ERROR.TYPE 함수는 셀 또는 수식 내에서 특정 유형의 오류를 식별하는 데 도움이 됩니다.

#N/A, #VALUE!, #REF! 등과 같은 다양한 오류 유형에 해당하는 숫자 값을 반환합니다.

셀 A1에 오류가 있고 해당 오류 유형을 식별하려고 한다고 가정합니다. 다음 수식은 특정 오류에 해당하는 숫자를 반환합니다.

=오류.유형(A1)

이를 여러 기능과 결합하여 오류 유형에 따라 다르게 대응할 수 있습니다. 다음은 가장 일반적인 오류와 해당 값입니다.

1. #없는! (범위에서 공통 셀을 찾을 수 없음)

2. #DIV/0! ( 0으로 나누기 또는 빈 셀)

3. #값! (수식의 부적절한 데이터 유형 또는 인수)

4. #참조! (참조된 셀이 삭제되었거나 순환 참조가 있음 )

5. #이름? (엑셀에서 함수나 범위를 인식하지 못함)

6. #NUM! (잘못된 숫자 값)

7. #N/A (값을 찾을 수 없음)

세 가지 특정 오류 유형을 처리하려고 한다고 가정합니다. 이 수식을 사용하여 유형에 따라 특정 오류 메시지를 표시합니다.

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, "0으로 나누기 오류", IF(ERROR.TYPE(C2)=3, "잘못된 값 오류", IF(ERROR.TYPE(C2 )=7, “값을 찾을 수 없음 오류”, “기타 오류”))), C2)

고급 조회 수식

초급 및 중급 치트 시트는 다양한 조회 기능을 다룹니다 . 다음은 몇 가지 고급 옵션입니다.

1. XLOOKUP

2. 엑스매치

1. XLOOKUP 기능

이 조회 기능을 사용하면 범위에서 값을 검색하고 다른 열이나 범위에서 해당 값을 반환할 수 있습니다.

VLOOKUP 과 같은 단순한 조회 기능보다 더 다양한 기능을 제공합니다 . 구문은 다음과 같습니다.

XLOOKUP(lookup_value, lookup_array, return_array, [일치_모드], [검색_모드], [if_not_found])

• lookup_value : 검색하려는 값입니다.

• lookup_array : 조회 범위입니다.

• return_array : 해당 값을 보여줄 범위.

• match_mode : 정확히 일치(0), 다음으로 작은(1), 다음으로 큰(-1) 또는 와일드카드 일치(2).

• search_mode : -1은 위에서 아래로, 1은 아래에서 위로, 2는 이진 검색입니다.

• if_not_found : 일치하는 항목이 없을 경우 반환할 값을 설정합니다.

의류 품목의 첫 번째 항목에 대한 데이터 범위를 검색하고 판매 금액을 반환한다고 가정합니다. 이 수식은 "Hoodie"라는 텍스트를 찾고 찾은 경우 인접한 셀의 값을 반환합니다.

=XLOOKUP("후드티", A2:A6, B2:B6)

2. XMATCH 기능

이 함수를 사용하면 범위 또는 배열 내에서 지정된 값의 위치를 ​​찾을 수 있습니다. 구문은 다음과 같습니다.

XMATCH(lookup_value, lookup_array, [일치_유형], [검색_모드])

• lookup_value : 찾고자 하는 값.

• lookup_array : 검색할 범위.

• match_type : 정확히 일치(0), 다음으로 작은 값(-1), 다음으로 큰 값(1).

• search_mode : 이진 검색(1) 또는 선형 검색(2).

열 B 내의 범위에서 노란색 항목의 첫 번째 항목을 찾으려고 한다고 가정합니다. 다음 수식을 사용합니다.

=XMATCH("노란색", B2:B6, 0)

마지막 생각들

이 치트 시트는 통계 분석, 조회 수식, 데이터 조작 기술 및 오류 처리 전략에 이르기까지 다양한 기능을 다룹니다.

제공된 예제와 설명은 이러한 고급 공식을 이해하는 데 도움이 되며 경험이 제한된 사람들도 쉽게 사용할 수 있습니다.

Excel 작업에 통합하기 시작하면 Excel 기술을 고급 수준으로 향상시킬 수 있습니다.

하지만 기억하세요. 이 치트 시트는 빙산의 일각에 불과합니다. 창의력을 발휘하고, 이러한 공식을 혼합하고 일치시키고, 고유한 문제를 해결하기 위해 맞춤화할 때 정말 놀라운 일이 발생합니다. Excel은 캔버스와 같으며 이러한 수식은 팔레트입니다. 그러니 계속해서 걸작을 그려보세요!