엑셀의 INDEX 함수가 배열을 반환할 수 있습니까?

엑셀의 INDEX 함수가 배열을 반환할 수 있습니까?

데이터가 범위 내에 있는 경우A1:A4는 다음과 같습니다.

Apple
Banana
Orange
Strawberry

그리고나서INDEX목록에서 값을 개별적으로 반환하는 데 사용할 수 있습니다.

= INDEX(A1:A4,3)

돌아올 것입니다Orange.

다음과 같은 작업을 효과적으로 수행할 수 있는 유사한 Excel 기능 또는 기능 조합이 있습니까?

= INDEX(A1:A4,{2;3})

합니다.{Banana;Orange}?

이것이 가능합니까(VBA가 없는 것이 좋습니다), 가능하다면 어떻게 해야 합니까?저는 도우미 세포를 사용하더라도 이를 달성하는 방법을 찾는데 어려움을 겪고 있습니다.

라면 다소 을 찾을 수 .MMULT), 를 당황하게 . 왜냐하면, , , , , , 이 데이터가 텍스트이기 때문입니다.MMULT텍스트에서는 작동하지 않습니다.

OFFSET은 아마도 당신이 원하는 기능일 것입니다.

=OFFSET(A1:A4,1,,2)

하지만 당신의 질문에 대답하자면, INDEX는 실제로 배열을 반환하는 데 사용될 수 있습니다.또는 콜론을 사이에 두고 두 개의 INDEX 함수를 사용합니다.

=INDEX(A1:A4,2):INDEX(A1:A4,3)

이는 INDEX가 실제로 셀 참조 또는 번호를 반환하고 Excel이 사용자가 요청하는 컨텍스트에 따라 사용자가 원하는 셀 참조 또는 번호를 결정하기 때문입니다.두 INDEX 함수의 중간에 콜론을 배치하면 Excel은 "콜론...일반적으로 양쪽에 셀 참조가 있습니다."라고 말하고 INDEX를 그렇게 해석합니다.자세한 내용은 http://www.excelhero.com/blog/2011/03/the-imposing-index.html 에서 확인하실 수 있습니다.

저는 사실 OFFSET보다 INDEX를 더 선호합니다. OFFSET은 변동성이 크기 때문입니다. OFFSET은 모자 한 방울에 따라 계속 재계산되고 그 아래쪽 수식도 강제로 동일하게 수행됩니다.이에 대한 자세한 내용은 제 게시물 https://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/ 을 참조하십시오.

실제로 하나의 인덱스만 사용하고 배열을 반환할 수 있지만, 이는 복잡하고 참조 해제라는 것이 필요합니다.다음은 제가 이에 대해 쓰고 있는 책의 내용은 다음과 같습니다.

이 스크린샷의 워크시트에는 데이터라는 이름의 범위가 A2 범위에 할당되어 있습니다.위를 가로질러 E2.이 범위에는 숫자 10, 20, 30, 40 및 50이 포함됩니다.또한 Elements라는 이름의 범위가 A5:B5 범위에 할당됩니다.해당 요소 범위는 A8의 공식을 나타냅니다.데이터 범위에서 표시할 5개의 숫자 중 B8.

A8:B8의 공식을 보면 어레이 정렬 INDEX 함수 {=INDEX(Data,Elements)}임을 알 수 있습니다.이 공식은 "데이터 범위로 이동하여 사용자가 요소 범위에서 선택한 요소를 기준으로 요소를 가져옵니다."라고 말합니다.이 경우 사용자는 다섯 번째와 두 번째 항목을 요청했습니다.그리고 당연히, 그것이 바로 INDEX가 셀 A8에 가져오는 것입니다.B8: 50과 20의 해당 값.

하지만 A11에 나온 것처럼 완벽하게 좋은 INDEX 함수를 사용하여 그 주위에 SUM을 넣으려고 하면 어떤 일이 일어나는지 보세요.50+20은 50과 같지 않다는 잘못된 결과가 50과 같지 않습니다.20살은 어떻게 됐어, 엑셀?

어떤 이유로, 반면에.=INDEX(Data,Elements)어딘가에서 상이한 요소를 기꺼이 가져온 다음 해당 숫자를 범위로 개별적으로 반환합니다. 대신 다른 함수에 해당 숫자를 제공하도록 요청하면 이를 준수하는 것을 꺼립니다.사실, 그것은 매우 꺼림칙해서 첫 번째 요소만 함수에 전달합니다.

따라서 =INDEX(Data, Elements) 기능을 사용하여 다른 작업을 수행하려면 먼저 그리드에 결과를 반환해야 합니다.지루한.하지만 거의 모든 Excel 전문가들은 해결책이 없다고 말할 것입니다.그것은 그냥 있는 그대로이고, 당신은 다른 선택의 여지가 없습니다.

Buuuuuuuuuut, 그들은 틀렸습니다.http://excelxor.com/2014/09/05/index-returning-an-array-of-values/, 게시물에서 신비한 공식 슈퍼히어로 XOR는 INDEX의 결과를 다른 공식에서 직접 사용할 수 있도록 INDEX를 "기준 해제"하는 두 가지 매우 간단한 방법을 설명합니다. 그 방법 중 하나는 위의 A18에 나와 있습니다.요소 인수를 둘러싸도록 비트를 추가하여 INDEX 함수를 약간 수정하면 INDEX가 공을 치는 것으로 나타났습니다.그리고 당신이 해야 할 일은 제가 아래에서 한 것처럼 Elements 인수를 설명하는 것입니다.

N(IF({1}, 요소)

이것을 염두에 두고, 당신의 원래 잘못된 행동 공식은 다음과 같습니다.

=SUM(INDEX(Data,Elements))

...복잡하지만 예의바른 이 사랑하는 사람에게 감사드립니다.

=SUM(INDEX(Data, N(IF({1},Elements))))

배열 수식 없이 이러한 유형의 동작을 얻을 수 있습니다.Say D1에서

=IFERROR(INDEX($A$1:$A$4,CHOOSE(ROWS($1:1),2,3)),"")

그리고 아래로 복사합니다.2.3은 내부에 묻혀 있습니다.CHOOSE()기능.

2,3을 임의의 인덱스 집합으로 바꿀 수 있습니다.

맞아요.

업데이트 3

어레이 공식을 사용해야 합니다.

위해서= INDEX(A1:A4,{2;3})글을 쓰다= INDEX(A1:A4,ROW($A$2:$A$3))

배열 공식을 사용하는 경우 (1) 결과를 반환할 셀 범위를 수직으로 선택합니다(예: B1:B2)를 누른 다음 (2)를 누르고 위의 공식을 입력한 다음 (3) + +를 누릅니다.

갱신하다

설명:

제어할 수 있습니다.row_num그리고.column_num의 일부.INDEX어레이 공식을 사용하여 보다 특별하게 원하는 결과를 반환할 수 있습니다.

어레이 공식 결과를 반환하는 방법에는 두 가지가 있습니다.

• (I)

  1. 반환된 결과를 표시할 범위를 선택합니다.
  2. 프레스
  3. 배열 양식 수식을 입력합니다.
  4. 그런 다음 + 를 누릅니다.

예:

=INDEX($A$1:$A$4,SMALL(IF($B$2=$A$1:$A$4,ROW($A$1:$A$4)-ROW($A$1)+1),ROW($A:$A)))

• (II)

  1. 반환된 결과를 표시할 첫 번째 셀에 배열 형식을 입력한 다음 +를 누르고 수식을 확장합니다.

예:

=INDEX($A$1:$A$4,SMALL(IF($B$2=$A$1:$A$4,ROW($A$1:$A$4)-ROW($A$1)+1),ROW(A1)))

결론

INDEX수식은 배열 형식으로 작동합니다.

입력해야 합니다.row_num또는column_num일렬로이를 위해 다음과 같이 적합한 배열 공식을 사용합니다.IF,SMALL,CHOOSE참고:MATCH배열 형식 수식이 아닙니다.

임시 샘플 파일(30일 동안): book.xlsx

슈퍼히어로 XOR에 의해 발견되고 위에서 @jeffreyweir에 의해 언급된 "디레퍼런스"에 기반한 모호한 기술 덕분에, 저는 가까스로 만들었습니다.VLOOKUP()CSE 어레이 공식에서도 작동합니다(누군요.INDEX(MATCH())또는LOOKUP()그래요...)

아래의 세 가지 예에서 목표는 항상 열의 값을 합하는 것입니다.$B:$B에 의해 검색 및 반환됨VLOOKUP()기능.

예 1:

열에서만 텍스트 값 찾기$A:$A

=SOMME(RECHERCHEV(T(SI({1};$A$1:$A$3));$A$1:$B$3;2;0))
=SUM(VLOOKUP(T(IF({1},$A$1:$A$3)),$A$1:$B$3;2;0))

예 2:

열에서만 숫자 값 찾기$A:$A

=SOMME(RECHERCHEV(N(SI({1};$A$1:$A$3));$A$1:$B$3;2;0))
=SUM(VLOOKUP(N(IF({1},$A$1:$A$3)),$A$1:$B$3,2;0))

예 3:

열에서 모든 유형의 값(숫자/텍스트)을 찾는 중$A:$A

=SOMME(CNUM(RECHERCHEV(T(SI({1};TEXTE($A$1:$A$3;"@")));TEXTE($A$1:$B$3;"@");2;0)))
=SUM(VALUE(VLOOKUP(T(IF({1},TEXT($A$1:$A$3,"@"))),TEXT($A$1:$B$3,"@"),2,0)))

참고 1: 배열이 상수인 경우{1}에는 필요하지 않습니다.INDEX(MATCH())해(스칼라)1트릭을 하는 것(또는True등)), 이를 위해 벡터 형태가 필요한 것으로 보입니다.VLOOKUP()해결책

참고 2:N()그리고.T()범위를 "기준 해제"하고 VBA와 같은 문자열/숫자 값 배열로 변환할 수 있는 것으로 보이는 함수는 두 개뿐인 것으로 알고 있습니다.이는 다음과 같은 이상한 동작과 관련이 있을 수 있습니다.

실제로, 다른 CSE 어레이 공식과 마찬가지로, 예상되는 결과는 다음과 같습니다.{A,B,C}그리고.{1,2,3}각각...

언급URL : https://stackoverflow.com/questions/47187863/can-excels-index-function-return-array