☰

두 가지 기준(하나는 숫자, 하나는 범위)을 기반으로 한 조회

tag-icon • tag-icon microsoft-excel
두 가지 기준(하나는 숫자, 하나는 범위)을 기반으로 한 조회

두 개의 목록이 있는데, 하나는 다양한 범위에 따른 ID와 백분율이 있고 다른 하나는 ID와 합계가 있습니다. 두 번째 스프레드시트의 ID와 합계를 기준으로 첫 번째 스프레드시트의 백분율을 조회해야 합니다.

ID  Bottom Top  Percentage
1   1      50       0.3
1   51     75       0.4
1   76              0.5
2   1               0.75
3   1      25       1
3   26     100      2
3   101    1000     3
3   1001   2000     4
3   2001            5

Second List
ID  Total Amount
1   100
2   146
3   1256

답변1

첫 번째 테이블이 Sheet1에 있고 두 번째 테이블이 Sheet2에 있고 두 번째 테이블의 금액 열을 채우려고 한다고 가정하면 다음 수식은 Sheet2의 첫 번째 금액 셀(C2 셀)에 대해 이 작업을 수행합니다. 이렇게 하려면 첫 번째 테이블을 ID 열을 기준으로 정렬한 다음 하단 열을 기준으로 정렬해야 합니다. 이 수식은 입력 후 표 2의 끝까지 복사하여 붙여넣을 수 있습니다.

=VLOOKUP(B2, OFFSET(Sheet1!B:D, MATCH(A2, Sheet1!A:A, 0) - 1, 0, COUNTIF(Sheet1!A:A, A2)), 3)

이 작업을 수행하는 데 실제로 Top 열이 필요한 것은 아닙니다.

이 공식을 분석하면 다음을 사용합니다.VLOOKUP함수는 아래쪽 열에서 합계 값보다 작거나 같은 가장 가까운 값을 검색한 다음 동일한 행에 대한 백분율 열의 값을 반환합니다. 또한 올바른 ID와 일치하는 행으로 검색을 제한해야 하며 이는 다음과 같이 수행됩니다.OFFSET기능. 그만큼OFFSET함수는 발견된 첫 번째 일치 ID 행을 사용합니다.MATCH함수와 일치하는 IDS의 총 개수COUNTIF기능.

관련 정보