배터리의 정전류 요구 사항을 계산하는 스프레드시트가 있습니다. 나는 계산된 값보다 크거나 같은 일정한 전류를 갖는 설정된 부하 지속 시간에서 적합한 배터리를 찾을 수 있기를 원합니다.
내 "로드 기간"(노란색)이 배열에서 열을 선택한 다음 내 조회 함수(녹색)가 계산된 참조 값(파란색)에 대해 >= 값을 찾는 조회 테이블을 사용하려고 합니다. 또한 배열에서 찾은 값과 동일한 행에서 모델을 찾는 또 다른 함수를 원합니다.
로드 기간은 해당 열과 일치하는 드롭다운 메뉴입니다. 현재 배열의 열을 선택하는 셀이 있습니다.
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)
가장 가까운 정전류 값을 찾기 위해 여러 조회 기능을 시도했습니다.
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,N49)
=(HLOOKUP(C46,'Battery Lookup Table'!S56:AW69,O50,TRUE))
이러한 조회 테이블 각각은 동일한 값을 생성했습니다.
또한 VLOOKUP과 HLOOkUP이 해독하는 데 어려움을 겪기 때문에 값을 가장 작은 것부터 가장 큰 것까지 정렬해 보았습니다. 나는 또한 운없이 일정 기간 동안 행으로 교환을 시도했습니다.
모델 번호:=INDEX('Battery Lookup Table'!A4:A36,MATCH(TRUE,'Battery Lookup Table'!M4:M25>=C46,0))
현재 내 조회 기능은 올바른 열로 이동하지만 테이블 하단 근처의 행/값을 자주 사용합니다. 발견된 값 중 가장 가까운 값은 없습니다.
답변1
VLOOKUP값과 일치합니다.첫 번째범위의 열을 검색하고 일치하는 행에 있는 범위의 지정된 열의 값을 전달합니다. 따라서 첫 번째 공식은
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
C46range 의 값과 일치합니다 (귀하의 예에서는 500.8이라고 생각합니다) 'Battery Lookup Table'!B4:B36. 이는 일치하려는 범위가 아니지만 아마도 잘못된 결과를 얻는 이유를 설명할 수 있습니다.
실제로 함수와 일치시킬 올바른 열을 이미 식별했습니다 MATCH. 이 INDEX기능을 사용하여 범위의 일부를 선택할 수 있습니다 . 표현식
INDEX('Battery Lookup Table'!B4:O36,,n)
제공합니다N'번째 열에 함수를 'Battery Lookup Table'!B4:O36넣는 것입니다 .MATCHNC46비교할 수 있는 올바른 값 열을 제공합니다 . 이렇게 하면 다음과 같은 표현식이 생성됩니다.
INDEX('Battery Lookup Table'!B4:O36,,MATCH(C39,'Battery Lookup Table'!B2:O2,0))
내용을 쉽게 따라할 수 있도록 이 표현을 다음과 같이 축약하겠습니다.목록.
이제 다음 두 가지 작업을 수행하고 싶습니다.
- 에서 가장 작은 값을 찾아보세요.목록이는 다음보다 크거나 같음
C46 - 이 값에 해당하는 배터리를 찾으십시오.
여기서 트릭은 사용하는 것이 VLOOKUP()아니라 더 유연한 "사촌" 입니다 INDEX(MATCH()). 범위의 첫 번째 열에 있는 값을 일치시키고 열의 값을 오른쪽으로 전달하는 반면 VLOOKUP, 더 유연한 버전에서는 값이 동일한 열이나 왼쪽의 열에서 나올 수 있습니다.
MATCH또한 세 번째 인수의 값이 -1,0 또는 1인지 여부에 따라 를 사용하여 일치시키는 세 가지 가능한 방법이 있습니다. 가장 간단한 방법은 0 값을 사용하는 완전 일치입니다( 와 일치시킬 때 이미 수행한 것처럼 Battery Lookup Table'!B2:O2) . . -1 값은 조회 범위에서 조회 값보다 크거나 같은 가장 작은 값의 위치를 찾습니다. 이는 귀하의 요구 사항이지만 조회 범위를 다음 위치에 배치해야 합니다.내림차순주문하다. (값 1은 조회 값보다 작거나 같은 조회 범위에서 가장 큰 값의 위치를 찾고 조회 범위가 오름차순이어야 합니다. 이는 요구 사항이 아닙니다.)
모든 값이 Battery Lookup Table오름차순으로 나타나므로 행이 역순으로 표시되도록 데이터를 재구성하면 내림차순 조건을 충족하게 됩니다. 100G99는 데이터의 첫 번째 행이 되고 50G05는 마지막 행이 됩니다. 또한 빈 행의 값은 0으로 처리되므로 내림차순 요구 사항을 위반하여 문제가 발생할 수 있으므로 제거해야 합니다.
표현식
MATCH(C46,list,-1))
에서 위치를 제공합니다.목록찾고 있는 값과 실제 값(녹색 배경 셀에 표시하기 위한)은 다음에서 제공됩니다.
=INDEX(list,MATCH(C46,list,-1))
배터리 모델은 다음에서 제공됩니다.
=INDEX('Battery Lookup Table'!A4:A46,MATCH(C46,list,-1))
다음 표현을 대체하면목록(위에서 언급한) 이 두 공식에 대한 첫 번째 공식의 반복 정도와 MATCH(C46,list,-1)두 공식 모두에 대한 반복 계산으로 인해 번거로운 표현식이 생성됩니다. 이러한 반복은 좋은 습관이 아니므로 가능하면 피해야 합니다.
이를 수행하는 한 가지 방법은 일치하는 열과 행 번호의 값을 워크시트 셀에 저장하는 것입니다. 각각 다음과 같습니다.
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)-로 표시N, 그리고
=MATCH(C46, INDEX('Battery Lookup Table'!B4:B36,,n),-1)-로 표시중
조회 결과는 다음과 같습니다.
INDEX('Battery Lookup Table'!B4:O36,m,n)(필요한 부하 기간 동안 일정한 정격) 및
INDEX('Battery Lookup Table'!A4:A36,m) (해당 배터리 모델 번호)
어디N그리고중계산된 열과 행 번호를 포함하는 두 개의 셀 참조입니다.