내가 본질적으로 해결하려고 하는 문제는 A:E 열에서 값을 확인하고 F열에 있는 값이 발견되면 해당 값을 반환하는 VLOOKUP입니다.
VLOOKUP이 작업을 수행하지 못하기 때문에 INDEX-MATCH 구문을 조사했지만 단일 열이 아닌 값 배열에 대해 이 구문을 완료하는 방법에 대해 고민하고 있습니다. 이를 설명하기 위해 아래에 예제 데이터 세트를 만들었습니다.
A------B------C------D------E------F
1------2------3------4------5------Apple
12-----13--------------------------Banana
14---------------------------------Carrot
검사 중인 셀에 1,2,3,4 또는 5가 포함되어 있으면 수식의 결과는 Apple이어야 합니다. 12 또는 13이면 바나나를 반환해야 하고, 마지막으로 14를 포함하면 당근을 반환해야 합니다.
두 번째 부분은 참조되는 셀이 단일 값이 아니라 전체 테이블 자체라는 사실에서 비롯됩니다. 따라서 이 검색은 다양한 값에 따라 여러 번 완료됩니다.
따라서 설명하기 위해 이러한 값이 포함된 다른 테이블(아래)이 있습니다. 시스템에서 각 열과 연결할 "사과, 바나나, 당근" 값 중 어떤 행을 식별하도록 하려고 합니다. 테이블은 아래와 같을 겁니다
안녕------------
1------(애플)----
2------(애플)----
12----(바나나)-
등.-----------------
괄호 안의 값은 수식이 이러한 값을 계산하는 위치입니다.
답변1
당신에겐 다양한 사례가 있습니다. 한 가지 경우를 고려해 봅시다:
열 어딘가ㅏ~을 통해이자형13을 포함하는 셀이 하나뿐입니다. 셀의 내용을 열에 반환합니다.에프같은 줄에.
"도우미" 열을 사용하겠습니다. ~ 안에G1입력하다:
=COUNTIF(A1:E1,13)
그리고 복사해 가세요. 이를 통해 행을 식별할 수 있습니다.
이제 우리는 사용할 수 있습니다일치()/인덱스():
셀을 선택하고 다음을 입력합니다.
=INDEX(F:F,MATCH(1,G:G,0))
"규칙"이 변경되어 행에 13이 하나 이상 있거나 13을 포함하는 여러 행이 있을 수 있는 경우 도우미 열을 수정합니다.
편집#1:
업데이트에 따르면 첫 번째 단계는 하드 코딩된13"도우미" 열의 수식을 가져와서 자체 셀에 넣습니다.(말하다H1). 그런 다음 단일 셀을 변경하여 다양한 사례를 실행할 수 있습니다.
테이블에 사례 수가 많은 경우 매크로를 만들어 각 사례를 설정할 수 있습니다.(업데이트H1)그리고 그 결과를 기록한다.
답변2
@Gary'sStudent와의 자체 연구 및 토론을 바탕으로 제가 사용한 솔루션은 값이 포함될 수 있는 가능한 각 열에 대해 공백 잡기 "IFERROR" 문과 함께 MATCH 수식을 만드는 것이었습니다.
I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.
이제 사용자 혼란/상호작용을 방지하기 위해 이러한 열을 숨길 수 있습니다.
그런 다음 이를 문제의 ROW와 일치해야 하는 단일 값으로 누적하는 인덱스를 만들었습니다. 다시 한번, 테이블에서 값을 찾을 수 없는 경우 이를 빈 값으로 입력하는 검사(첫 번째 SUM)가 있습니다.
N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))
마지막으로 사용자가 중복 데이터를 식별하고 교체/제거할 수 있도록 몇 가지 조건부 서식 수식을 입력했습니다.
A1:E3 Cell contains a blank value [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1 [Formatting Text:White, Background:Red]
H1:N1 =COUNTIF($A$1:$E$3,H1)>1 [Formatting Text:Red, Background:Red]
이는 사용자에게 이 중복 데이터를 제거하라는 신호일 뿐입니다.
답변3
H1의 단일 수식의 경우:
=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))
이는 배열 수식이므로 참조를 데이터 세트의 크기로 제한해야 합니다. 다들 INDEX(E:E,MATCH("ZZZ",F:F))그렇게 하세요. 그러면 텍스트가 있는 F열의 마지막 행이 반환됩니다. 그런 다음 이를 반복할 마지막 행으로 설정합니다.
@Gary'sStudent 방법은 배열 수식을 피하며 필요한 방법일 수 있습니다. 데이터 세트와 수식 수가 증가하면 계산 시간도 늘어납니다. 어느 시점에서 Excel이 충돌하는 경우에도 마찬가지입니다. 일반적으로 이 작업에는 수천 시간이 걸리지만 경고하고 싶습니다.
편집하다
배열 수식을 사용하지 않고 여전히 하나의 수식을 사용하려면 다음을 수행하세요.
=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),IFERROR(MATCH($H1,E:E,0),1050000))),"")
이것은 OP의 답변을 기반으로 하며 해당 방법을 하나의 공식으로 결합했습니다.
이 수식은 중복 항목을 무시하고 숫자가 발견된 첫 번째 행을 반환합니다.
그리고 배열이 아니기 때문에 전체 열 참조는 계산 시간에 해롭지 않습니다.
답변4
다른 방법은 이 "반드시"가 처음에 어떻게 구성되어야 하는지를 나타내는 보조 테이블을 기반으로 합니다. 이렇게 하면 나중에 디버깅하고 변경하기 귀찮은 괴물 방정식을 피할 수 있으며, 조회 열이 5개 있다는 생각과 달리 다양한 열 수를 깔끔하게 해결할 수 있습니다.
위 내용이 Sheet1에 있는 경우 Sheet2를 추가합니다. 그 자리에는 네 개의 기둥이 있습니다. 행, 열, ID, 이름
수식은 다음과 Row같아야 합니다(유사 코드에서 "마지막"은 "sheet2의 위 행에 대해"를 의미함).
=IF(Column = 1, Last row + 1 , Last row)
공식 Column:
=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)
ID및 의 공식 Name:
=INDEX(StartTable, Row, Column)
=INDEX(NameColumn, Row, 1)
그런 다음 이를 입력합니다(기본적으로 row원래 테이블의 행 수가 >개 이상일 때까지).
마지막으로 일반 vlookup 또는 인덱스/일치와 함께 새 테이블을 사용합니다.
장점: 공식이 훨씬 간단하고 사용 및 이해가 더 쉽습니다.
단점: 추가 테이블이 필요하고 테이블 길이를 유지해야 합니다. 성능 측면에서는 값의 전체 "문자열"에 대해 단일 스레드가 거의 필요하기 때문에 위험이 있습니다.
또한 몇 개의 오류 행이 괜찮다면 코드는 다소 간단하고 성능이 더 좋을 수 있습니다. 그런 다음 열 수가 항상 5라고 가정하여 행과 열을 모두 제공할 수 있습니다.