함수 xlookup, filter, unique

1. xlookup

https://support.office.com/ko-kr/article/xlookup-%ED%95%A8%EC%88%98-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Office 365용 Excel Office 365용 Mac용 Excel 웹용 Excel

 

Google Spreadsheet에서는 미출시.

 

 

2. filter

https://support.office.com/ko-kr/article/filter-%ED%95%A8%EC%88%98-f4f7cb66-82eb-4767-8f7c-4877ad80c759

Office 365용 Excel Office 365용 Mac용 Excel 웹용 Excel iPad용 Excel iPhone용 Excel Android 태블릿용 Excel Android 휴대폰용 Excel

 

필터링을 함수로!

 

 

3. unique

https://support.office.com/ko-kr/article/unique-%ED%95%A8%EC%88%98-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e

Office 365용 Excel Office 365용 Mac용 Excel 웹용 Excel iPad용 Excel iPhone용 Excel Android 태블릿용 Excel Android 휴대폰용 Excel

 

고유값 반환(중복값 제거)

 

 

1. xlookup

예제 1

이 예제는 위의 비디오에서 가져온 것으로, 간단한 XLOOKUP을 사용 하 여 국가 이름을 조회 한 다음 전화 국가 코드를 반환 합니다. 이 인수에는 lookup_value (F2 셀), lookup_array (범위 B2: B11), return_array (range D2:) 인수가 포함 됩니다. XLOOKUP의 기본값은 정확한 일치 항목 이므로 match_mode 인수는 포함 되지 않습니다.

참고: XLOOKUP은 별도의 조회 및 반환 배열을 사용 하는 것과 다르며, VLOOKUP에서는 단일 표 배열과 열 인덱스 번호를 차례로 사용 합니다. 이 경우 = VLOOKUP (F2, B2:2, 3, FALSE)과 같은 VLOOKUP 수식이 있습니다.

예제 2

이 예제에서는 직원 ID 번호를 기준으로 직원 정보를 조회 합니다. VLOOKUP과 달리 XLOOKUP은 여러 항목이 포함 된 배열을 반환할 수 있으며, 단일 수식에서 직원 이름과 부서 (셀 C5: D14)를 모두 반환할 수 있습니다.

예제 3

이 예제에서는 위의 예제에 if_not_found 인수를 추가 합니다.

예제 4

다음 예에서는 C 열에서 E2 셀에 입력 된 개인 수입에 대해 찾고, B 열에서 일치 하는 세금 급여를 찾습니다. 이 함수는 1로 설정 된 match_mode 인수를 사용 하 여 정확히 일치 하는 값을 찾을 수 없음을 의미 하 고, 그 다음으로 대형 항목이 반환 됩니다.

참고: VLOOKUP과 달리 lookup_array 열은 return_array 열의 오른쪽에 있으며 VLOOKUP은 왼쪽에서 오른쪽 으로만 볼 수 있습니다.

예제 5

그 다음에는 중첩 된 XLOOKUP 함수를 사용 하 여 세로 및 가로 일치를 모두 수행 합니다. 이 경우 먼저 열 B에서 매출 수익 을 찾은 다음 테이블의 맨 위 행 (범위 C5: F5)에서 Qtr1 를 찾아 두 값의 교차 부분에 있는 값을 반환 합니다. 이는 INDEX 및 MATCH 함수를 함께 사용 하는 것과 비슷합니다. XLOOKUP을 사용 하 여 HLOOKUP 함수를 바꿀 수도 있습니다.

D3: F3 셀의 수식은 = XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17))).

예제 6

이 예제에서는 SUM 함수와두 개의 xlookup 함수를 사용 하 여 두 개의 범위에 있는 모든 값의 합계를 계산 합니다. 이 경우 포도, 바나나에 대 한 값의 합계를 계산 하 고 둘 사이에 있는 배를 포함 해야 합니다.

E3 셀의 수식은 = SUM (XLOOKUP (B3, B6: B10, E6: E10): XLOOKUP (C3, B6: B10, E6: E10))입니다.

어떻게 작동하나요? XLOOKUP은 범위를 반환 하므로 계산할 때 수식은 다음과 같이 끝납니다 (= SUM ($E $7: $E $9). 이 예와 비슷한 XLOOKUP 수식이 있는 셀을 선택한 다음 수식 >으로 이동 하 여 수식 분석 > 수식을 계산하 고 평가 단추를 눌러 계산을 단계별로 진행 하는 방법을 확인할 수 있습니다.

 

 

2. filter

다음 예제에서는 수식 = FILTER (A5: D20, C5: C20 = H2, "") 를 사용 하 여 셀 a 0에서 선택한 대로 Apple의 모든 레코드를 반환 하 고, 사과가 없으면 빈 문자열 ("")을 반환 합니다.

 

구문

예제

FILTER를 사용하여 여러 조건 반환

이 예제에서는 곱셈 연산자(*)를 사용하여 배열 범위 (A5:D20)에서 제품은 Apple AND 지역은 East인 모든 값을 반환합니다. =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"").

FILTER를 사용하여 여러 조건을 반환하고 정렬

이 예제에서는 앞에서 사용한 FILTER 함수를 SORT 함수와 함께 사용하여 배열 범위 (A5:D20)에서 제품은 Apple AND 지역은 East인 모든 값을 반환한 다음 개수를 내림차순으로 정렬합니다. =SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)

이 예제에서는 FILTER 함수에 덧셈 연산자(+)를 사용하여 배열 범위 (A5:D20)에서 제품은 사과 또는 지역은 동부인 모든 값을 반환한 다음 개수를 내림차순으로 정렬합니다. =SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1).

함수는 하나의 셀에만 존재하고 결과를 인접 셀로 분산하기 때문에 함수에 절대 참조가 필요하지 않다는 것을 볼 수 있습니다.

 

 

3. unique

 

UNIQUE 함수는 목록 또는 범위에서 고유 값의 목록을 반환합니다.

값 목록에서 고유 값 반환

이름 목록에서 고유 이름 반환

참고: 2018 년 9 월 24 일: 고유 함수는 여러 베타 기능 중 하나 이며 현재이 시점에 Office 참가자 의 일부 에서만 사용할 수 있습니다. 향후 몇 개월에 걸쳐 계속해서 이 기능을 최적화해 나갈 계획입니다. 준비가 되면 모든 Office 참가자와 Office 365 구독자에게 이 기능을 공개할 예정입니다.

 

구문

예제

예제 1

이 예제에서는 SORT와 UNIQUE를 함께 사용 하 여 이름에 대 한 고유 목록을 오름차순으로 반환 합니다.

예제 2

이 예제에서는 exactly_once 인수를 TRUE로 설정 하 고, 함수는 서비스를 한 번만 수행한 고객만 반환 합니다. 이 기능은 추가 서비스에 대해 반환 되지 않은 사람을 식별 하 여 연락할 수 있도록 하려는 경우에 유용할 수 있습니다.

예제 3

이 예제에서는 앰퍼샌드 (&)를 사용 하 여 성과 이름을 전체 이름에 연결 합니다. 수식은 A2: A12 및 B2: B12의 전체 이름 범위를 참조 합니다. 이렇게 하면 Excel에서 모든 이름의 배열을 반환할 수 있습니다.

팁: 

• 이름 범위의 서식을 Excel 표로지정 하면 이름을 추가 하거나 제거할 때 수식이 자동으로 업데이트 됩니다.

• 이름 목록을 정렬 하려는 경우 = sort (UNIQUE (B2: B12& "" &A2: A12)) 를 정렬 함수에 추가할 수 있습니다.

예제 4

이 예제에서는 두 열을 비교 하 고 그 사이에 고유한 값만 반환 합니다.