☰

N개의 가장 높은 값의 셀 참조(주소)를 반환하는 함수

tag-icon • tag-icon microsoft-excel worksheet-function
N개의 가장 높은 값의 셀 참조(주소)를 반환하는 함수

저는 선거 결과를 추적하고 선출된 대표자를 반환하기 위해 스프레드시트를 만들고 있습니다. 비례대표제를 적용하고 있습니다.수정된 Sainte-Lague 방법. 선거 결과는 스프레드시트에 입력된 후 주어진 숫자(1.4, 3, 5 등, 해당 법률에 따라 최대 15까지 총 8개의 몫)로 나누어 해당 몫을 얻습니다. 좌석을 분배하기 위해 조건부 서식을 사용하면 이제 스프레드시트에서 가장 높은 8개의 숫자가 강조 표시되어 누가 어떤 위치에 있는지 표시됩니다. 이 모든 것이 의도한 대로 작동합니다.

a) 8개의 성공적인 후보 목록을 반환하고 b) 해당 목록이 숫자가 아닌 셀 참조(예: D7)를 반환하도록 해야 합니다. 나는 이 함수를 사용해 보았고 LARGE기본적으로 =LARGE($C$2:$J$21, ROW()-23). 그러나 이러한 몫에 대한 그리드 좌표를 반환하도록 할 수는 없습니다.

아래 스크린샷은 제가 달성하고자 하는 목표의 모형을 보여줍니다. 녹색으로 강조 표시된 셀은 조건부 서식의 결과로 강조 표시됩니다. 이는 C2:J21에서 가장 큰 8개의 값입니다. 노란색으로 강조 표시된 셀은 제가 달성하고 싶은 것입니다. 확실하게; 셀 참조 C2:J5의 데이터는 수식에 의해 생성됩니다. 마찬가지로 C24:C31의 값은 위에서 녹색으로 강조 표시된 8개 값과 동일합니다. 그들은 생성된 수식입니다. 숨겨진 행 6~21은 본질적으로 행 1~5와 중복됩니다. 스프레드시트를 다음 위치에 업로드했습니다.구글 드라이브

문제의 스프레드시트 스크린샷

답변1

이것은 까다 롭습니다. 우리는 사용할 수 있습니다

=MIN(IF($C$2:$J$21=값, 행($C$2:$J$21)))
첫 번째 행의 번호를 찾으려면value나타납니다. 비슷하게,
=MIN(IF($C$2:$J$21=값, 열($C$2:$J$21)))
첫 번째 열의 번호를 찾습니다.value나타납니다. 위의 내용은 배열 수식입니다.

의 상위 8개 값이 C2:J21고유한 경우 위의 내용을 사용하여 해당 그리드에서 값을 찾을 수 있습니다. 그 다음에

인덱스($A$1:$J$21,행_번호,열_번호)
해당 셀의 색인을 생성하고 다음을 사용할 수 있습니다.
CELL("주소", INDEX(위의))
해당 셀의 행 및 열 주소를 가져옵니다.

그러니 들어가세요

=CELL("주소", INDEX($A$1:$J$21, MIN(IF($C$2:$J$21=C24,ROW($C$2:$J$21))), MIN(IF($C $2:$J$21=C24,COLUMN($C$2:$J$21)))))
셀에 들어가서 + + 를 B24누르고 아래로 끌어서 채웁니다 .CtrlShiftEnterB31

노트:

  • CELL("address", …)절대 주소(달러 기호 포함)를 반환합니다. 원하지 않는 경우에는 다음을 사용할 수 있습니다. 
    =대체(셀(ㅋ ㅋ ㅋ ㅋ ㅋ ㅋ), "$", "")
  • 중복된 값을 잘 처리하지 못합니다. 예를 들어, E38개(외에)도 포함된 경우 둘 다 (첫 번째 행과 첫 번째 열) D4에 있다고 보고합니다 . D3이 문제를 해결하는 것은 매우 어려울 수 있지만 가능합니다.시험 그러한 우연의 일치가 발생했는지 여부를 수동으로 수정할 수 있도록 합니다. 
  • 이 작업은 하나의 셀에서 모두 수행할 수 있지만 행 및 열 값에 보조 열을 사용하면 장기적으로 생활이 단순화될 수 있습니다.

이 답변은 부분적으로이 답변배리 후디니 지음.

관련 정보