Excel 함수는 다른 수식을 결합하는 데 익숙해지면 매우 강력할 수 있습니다.한때 불가능해 보였던 일이 갑자기 아이들의 장난이 될 수 있습니다.
한 가지 예는 Excel 데이터 세트에서 가장 가까운 값을 찾는 것입니다.
Excel에는 가장 가까운 위치를 찾을 수 있는 몇 가지 유용한 조회 기능(예: VLOOKUP 및 INDEX MATCH)이 있습니다.가치(아래 예와 같이).
그러나 가장 좋은 점은 이러한 조회 기능을 다른 Excel 기능과 결합하여 훨씬 더 많은 작업을 수행할 수 있다는 것입니다(정렬되지 않은 목록에서 조회 값과 가장 가까운 일치 찾기 포함).
이 자습서에서는 조회 수식을 사용하여 Excel의 조회 값과 가장 가까운 일치 항목을 찾는 방법을 보여 드리겠습니다.
가장 가까운 일치(또는 가장 가까운 일치 값)를 찾아야 하는 다양한 시나리오가 있습니다.
이 기사에서 다룰 예는 다음과 같습니다.
- 매출에 따른 수수료율 찾기
- 최적의 후보자 찾기(가장 가까운 경험을 기반으로)
- 다음 이벤트 날짜 찾기
시작하자!
수수료 찾기(가장 가까운 판매 가치 찾기)
모든 영업 사원에 대한 수수료율을 찾으려는 아래와 같은 데이터 세트가 있다고 가정합니다.
커미션은 판매 가치에 따라 분배됩니다.이것은 오른쪽 표를 사용하여 계산됩니다.
예를 들어, 판매원의 총 판매가 5000이면 수수료는 0%이고 총 판매가 15000이면 수수료는 5%입니다.
수수료율을 얻으려면 판매 가치 바로 아래에서 가장 가까운 판매 범위를 찾아야 합니다.예를 들어, 15000개 판매의 경우 수수료는 10,000(또는 5%)이고 25000개 판매의 경우 수수료율은 20,000(또는 7%)입니다.
가장 가까운 판매 가치를 찾고 수수료율을 얻으려면 VLOOKUP에서 근사 매칭을 사용할 수 있습니다.
다음 공식으로 이를 수행할 수 있습니다.
= VLOOKUP (B2, $ E $ 2 : $ F $ 6,2,1)
이 수식에서 마지막 매개변수는 1이며 근사 조회를 사용하도록 수식에 지시합니다.즉, 수식은 E 열의 판매 값을 통해 조회 값 바로 아래의 값을 찾습니다.
VLOOKUP 공식은 해당 값에 대한 수수료율을 제공합니다.
注意: 이를 위해서는 데이터를 오름차순으로 정렬해야 합니다.
최적의 후보자 찾기(가장 가까운 경험을 기반으로)
위의 예에서 데이터는 오름차순으로 정렬되어야 합니다.그러나 데이터가 정렬되지 않는 경우가 있습니다.
예를 들어 수식 조합을 사용하여 Excel에서 가장 가까운 일치 항목을 찾는 방법을 살펴보겠습니다.
다음은 원하는 값에 가장 가까운 근무 경험을 가진 직원의 이름을 찾아야 하는 데이터 세트의 예입니다.이 경우의 예상 가치는 2.5년입니다.
데이터는 정렬되지 않습니다.또한 가장 가까운 경험은 주어진 경험보다 적거나 많을 수 있습니다.예를 들어, 2년과 3년은 똑같이 가깝습니다(0.5년 차이).
다음은 결과를 제공하는 공식입니다.
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
이 공식의 트릭은 조회 배열과 조회 값을 변경하여 원하는 값과 실제 값 사이의 가장 작은 경험적 차이를 찾는 것입니다.
먼저 수동으로 수행하는 방법을 이해하고 이 공식이 어떻게 작동하는지 설명하겠습니다.
이 작업을 수동으로 수행할 때 B열의 각 셀을 살펴보고 원하는 경험과 원하는 경험 간의 차이를 찾습니다.모든 차이점이 있으면 가장 작은 차이점을 찾아 그 사람의 이름을 얻습니다.
이것이 바로 우리가 이 공식으로 하는 일입니다.
설명하겠습니다.
MATCH 수식의 조회 값은 MIN(ABS(D2-B2:B15))입니다.
이 섹션은 주어진 경험(즉, 2.5년)과 다른 모든 경험 간의 최소 차이를 제공합니다.이 경우 0.3을 반환합니다.
나는 ABS를 사용하여 가장 가까운(주어진 경험보다 많거나 적은) 가장 가까운 것을 찾고 있는지 확인합니다.
이제 이 최소값이 조회 값이 됩니다.
MATCH 함수의 조회 배열은 ABS(D2-$B$2:$B$15)입니다.
이것은 2.5(필요한 경험)를 뺀 숫자의 배열을 제공합니다.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
MATCH 함수는 이 배열에서 가장 가까운 경험을 가진 사람의 이름 위치인 0.3의 위치를 찾습니다.
그런 다음 INDEX 함수는 작업 번호를 사용하여 사람의 이름을 반환합니다.
참고: 동일한 최소 경험을 가진 후보자가 여러 명인 경우 위 공식은 첫 번째로 일치하는 직원의 이름을 제공합니다.
다음 이벤트 날짜 찾기
다음은 조회 수식을 사용하여 현재 날짜를 기준으로 이벤트의 다음 날짜를 찾을 수 있는 또 다른 예입니다.
아래는 이벤트 이름과 이벤트 날짜가 있는 데이터 세트입니다.
내가 원하는 것은 다음 이벤트의 이름과 이 다가오는 이벤트의 이벤트 날짜입니다.
다음은 예정된 이벤트의 이름을 지정하는 공식입니다.
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
다음 공식은 예정된 이벤트 날짜를 제공합니다.
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
이 공식이 어떻게 작동하는지 설명하겠습니다.
이벤트 날짜를 가져오기 위해 MATCH 함수는 B열에서 현재 날짜를 찾습니다.이 경우 정확히 일치하는 항목이 아니라 대략적인 일치 항목을 찾습니다.따라서 MATCH 함수의 마지막 매개변수는 1입니다(룩업 값보다 작거나 같은 가장 큰 값을 찾습니다).
따라서 MATCH 함수는 날짜가 현재 날짜보다 작거나 같은 셀의 위치를 반환합니다.따라서 이 경우 다음 이벤트는 다음 셀에 있습니다(목록이 오름차순으로 정렬되어 있기 때문에).
따라서 다가오는 이벤트 날짜를 얻으려면 MATCH 함수에서 반환된 셀 위치에 하나를 추가하기만 하면 다음 이벤트 날짜의 셀 위치가 표시됩니다.
이 값은 INDEX 함수에 의해 제공됩니다.
이벤트 이름을 얻으려면 동일한 수식을 사용하고 INDEX 함수의 범위를 B열에서 A열로 변경합니다.
친구가 요청했을 때 이 예가 떠올랐습니다.그는 열에 모든 친구/친척의 생일을 나열하고 다음 생일(그리고 그 사람의 이름)이 다가오는지 알고 싶어합니다.
위의 세 가지 예는 조회 수식을 사용하여 Excel에서 가장 가까운 값을 찾는 방법을 보여줍니다.
